Почему в индексированной таблице быстрее только чтение, а не запись?

Индексы используются только для ускорения операторов SELECT. Для INSERT, UPDATE и DELETE ваши операторы будут выполняться медленнее, чем обычно, из-за необходимости обновления индекса как части оператора.

Возможно, мне следует уточнить по пункту UPDATE/DELETE. Это правда, что операторы будут работать медленнее из-за изменения индекса, добавленного к накладным расходам, однако начальная часть поиска (WHERE) оператора UPDATE и DELETE может быть ускорена из-за индекса. По сути, в любом месте, где используется предложение WHERE, и вы ссылаетесь на индексированные поля, часть выбора записи этого оператора должна несколько увеличиться.

Кроме того, если инструкция UPDATE не изменяет ни один из столбцов, являющихся частью индекса, то вы не должны наблюдать дополнительного замедления, поскольку индекс не обновляется.

Потому что для индексов требуется дополнительное место на диске. Индексы увеличивают объем данных, которые необходимо зарегистрировать и записать в базу данных. Индексы снижают скорость записи. Когда столбец, охватываемый индексом, обновляется, этот индекс также должен быть обновлен. Точно так же любое удаление или вставка требует обновления соответствующих индексов.

Дисковое пространство и штрафы за запись индексов — вот почему вам нужно быть осторожным при создании индексов.

Тем не менее, обновления неиндексированных столбцов могут повысить производительность с помощью индексов.

Этот:

UPDATE Table SET NonIndexedColumn = 'Value' WHERE IndexedKey = 'KeyValue'

Будет быстрее, чем это:

UPDATE Table SET IndexedColumn = 'Value' WHERE IndexedKey = 'KeyValue'

Но два приведенных выше, вероятно, будут быстрее, чем это, в любой таблице разумного размера:

UPDATE Table SET NonIndexedColumn = 'Value' WHERE NonIndexedKey = 'KeyValue'

Удаление, особенно одиночное удаление, также может выполняться быстрее, даже если таблицу и индексы необходимо обновить. Это просто потому, что механизм запросов может быстрее найти целевые строки. То есть можно быстрее прочитать индекс, найти строку, удалить строку и обновить индекс, чем сканировать всю таблицу в поисках правильных строк и удалять соответствующие. Однако даже в этом случае будет больше данных для записи; просто стоимость операций ввода-вывода при сканировании всей таблицы может быть довольно высокой по сравнению с индексом.

Наконец, теоретически ключ кластеризации, который распределяет вставки по нескольким страницам диска, может позволить системе поддерживать большее количество одновременных вставок, поскольку вставки обычно требуют блокировки страниц для функционирования, но это несколько необычная ситуация, и она может привести к ухудшению производительности чтения из-за фрагментации ваших кластеризованных индексов.

INSERT и DELETE должны обновлять каждый индекс для таблицы (и кучу, если нет кластеризованного индекса), чтобы поддерживать согласованность. UPDATEs может обойтись обновлением меньшего количества индексов, в зависимости от того, какие столбцы были затронуты обновлением (поскольку необходимо обновить только те индексы, которые индексируют/включают эти столбцы)

SELECT, с другой стороны, только читает, поэтому, если индекс содержит все столбцы, требуемые SELECT, доступ должен быть только к этому индексу. Мы знаем, что данные в этом индексе точны точно, потому что операции модификации необходимы для поддержания этой согласованности.