Как не надо индексировать

Развитие происходит по спирали: когда-то люди не умели правильно индексировать, потом (в основном) научились, потом пришли noSQL и все снова забыли знание древних. Что вы будете делать, когда последние из старых DBA отплывут в Валинор?

Снова и снова и сталкиваюсь с полным набором антипаттернов индексирования. Я их перечислю, но! Для каждого антипаттерна есть исключения, когда именно это и стоит делать. Поэтому кликбейтно сформулированное правило верно лишь в 95% случаях, но если вы хотите копнуть глубже, то прочитайте про исключения.

И в конце полезные скрипты для MSSQL, Postgres и MySQL - хотя статья фокусируется на опыте MS SQL

Все автоматически созданные индексы - глупость

Речь идет об индексах созданных по рекомендации MSSQL, с именами типа _dta_index_mytab_11_1172915250__K1_K26_8_11_28_46_90

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

Подобные рекомендации - подсказка, что неплохо бы разобраться, а что плохо с самим запросом. Как правило INCLUDE - это отчаянная попытка MS SQL уменьшить объем данных для Full Scan, 'вынеся' эти данные из основной таблицы в более маленький индекс с INCLUDE. Чем создавать такой индекс, посмотрите, а откуда вообще идут table scans.

Исключение: конечно, возможно, что забыт самый очевидный индекс. Но скорее всего на PROD при эксплуатации все очевидные индексы уже были созданы.

Тупое следование таким рекомендациям приводит к следующей проблеме

Таблица с более чем 15 индексами - что-то здесь не так

Я видел таблицы с 40 и более индексами, созданными по принципу 'кашу маслом не испортишь'. Большинство их было создано автоматически.

Конечно, один индекс не сильно замедлит insert/update/delete, но десятки замедлят существенно, особенно на больших таблицах.

Исключение: разумеется, можно придумать таблицу, у которой много 'dimensions' по естественным причинам. Но тогда следует проверить следующий пункт

Индекс по колонке с низкой селективностью бесполезен

Часто видишь, как индексируют поля типа isCancelled bit, ObjectType smallint, sex int (всего 56, или сколько там сейчас значений), DepartmentId в фирме с одним Department итд. Здесь много 'но' и все они важны:

Исключение 1. Иногда колонка с низкой селективностью может быть 'прицеплена' к колонке с высокой. Путь у объекта есть id int, и side char(1) 'плечо' правое и левое, 'L','R'. если 'плечо' всегда и часто указывается в запросах:

... WHERE id=12345 and side='L'

то вполне поможет индекс по (id,side) - но не (side,id) если side иногда не указывается.

Исключение 2. Иногда статус объекта несбалансирован, пусть у нас есть поле IsProcessed. У большинства объектов IsProcessed=1, а нам надо часто искать объекты для процессинга по isProcessed=0. Тогда имеет смысл создать индекс:

create index ... on ... (IsProcessed) WHERE (IsProcessed=0)

У нас будет маленький быстрый индекс по необработанным документам. Искать по нему надо так: IsProcessed=0 (а не IsProcessed=@status, где @status=0)

Исключение 3. Селективность колонки мала, но селективность набора многих колонок высока. Если все эти колонки используются в WHERE, то такой индекс будет полезен.

А как же FOREIGN KEY? пусть у нас есть Departments, которые сделаны для общности, и на обозримое время их 1 штука? Ведь рекомендуется все FOREIGN KEYs 'обеспечивать' индексами?

Как и всякая рекомендация она имеет границы. Если у вас таблица с количеством записей под миллиард, то зачем бедной базе при каждом insert менять этот бессмысленный индекс об одном значении? Как часто он нужен? Как часто вы удаляете departments? Пусть там раз в год будет table scan.

Нарушает какие-то принципы дизайна? Да, как и любая денормализация для performance.

Индекс по более чем 3 колонкам - красный флаг

Это следствие из предыдущего пункта. Если вы посмотрите на этот индекс то обнаружите, что либо он создан автоматически, либо лишь некоторые колонки достаточно селективны. (см. Исключения 1 и 3 в предыдущем пункте).

Кстати, селективность колонок можно проверить в MSSQL, Postgres, MySQL вот так.

Если в колонке дата/время не на последнем месте, то проверьте

Речь идет об индексах типа (DT datetime + SomeUseful ...), то есть колонка datetime не одинока, но она не на последнем месте.

Datetime часто ищутся не по равенству, а по диапазонам. В запросе:

... WHERE DT>='20211201' and DT<'20211205' and SomethingUseful=12345

данный индекс может быть использован лишь частично - только его первая часть, по DT. Проверка на SomethingUseful производится отдельно. В обратном порядке (SomethingUseful + DT) образуется непрерывный диапазон (12345,'20211201') ... (12345,'29211205') и индекс используется целиком

Исключение: у даты 'обрубается' время и она сравнивается на равенство.

Индексы не должны 'включаться' друг в друга

Если есть индекс по (A,B,C), то индекс по (A,B) излишен. Если указано WHERE A=... and B=... SQL может использовать начало индекса по (A,B,C)

Исключение: тем не менее, индекс по (A,B) короче и SQL будет его предпочитать, если условие по колонке C не указано. То же верно и для индексов с одинаковым началом и разными списками INCLUDE (здесь важно, от каких lookups 'спасает' INCLUDE)

Полезные скрипты

Для MS SQL предлагаю скрипт, который пройдет по всем индексам и представит отчет:

Показываются лишь колонки, которые участвуют хотя бы в одном индексе (вне INCLUDE), а число показывает порядковый номер в индексе. Чем старшее позиция, тем более блеклый синий цвет.

Вас наверное удивляет, что результат выглядит как HTML таблица? Потому что это часть большого проекта. Впрочем, для MSSQL вы можете использовать скрипт отдельно.

set nocount on 
declare @name varchar(128)='tablename'
select I.name as iname,IC.index_column_id, C.name as colname
  into #i
  from sys.index_columns IC, sys.indexes I, sys.columns C
  where IC.object_id=object_id(@name) and I.object_id=IC.object_id and I.index_id=IC.index_id and C.object_id=I.object_id and C.column_id=IC.column_id
declare @sql varchar(8000)='create table ##p (iname varchar(128) collate database_default', @c varchar(128), @blanks varchar(4000)= ''

-- create table
DECLARE servers CURSOR FOR select name from sys.columns where object_id=object_id(@name) and name in (select colname from #i) order by column_id
OPEN servers;  
FETCH NEXT FROM servers into @c  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  set @sql=@sql+' , ['+@c+'] varchar(2)'
  set @blanks=@blanks+','''''
  FETCH NEXT FROM servers into @c
END  
CLOSE servers;  
DEALLOCATE servers;
set @sql=@sql+')'
exec(@sql)

--select * from #i
set @sql='insert into ##p select distinct iname'+@blanks+' from #i '
exec(@sql)

-- create table
DECLARE servers CURSOR FOR select name from sys.columns where object_id=object_id(@name) and name in (select colname from #i) order by column_id
OPEN servers;  
FETCH NEXT FROM servers into @c  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  set @sql='update ##p set ['+@c+']=convert(varchar,index_column_id) from #i where #i.colname='''+@c+''' and #i.iname=##p.iname'
  print @sql
  exec(@sql)
  FETCH NEXT FROM servers into @c
END  
CLOSE servers;  
DEALLOCATE servers;

select * from ##p
--select iname,count(*) from #i group by iname order by 2
drop table #i
drop table ##p

Для Postgre и MySQL есть версии в самих модулях, скачайте их по ссылкам и ищите файл с именем *indexing. , равно как и для MSSQL