С чем кушать Irregular Selectivity в MSSQL и не только / Habr

Недавно мне пришлось объяснять это нашим братьям меньшим на работе, и я решил написать текст, который может пригодиться. В конце вы найдете ссылку на полезный скрипт для MSSQL, а также Postgres и MySQL. Если тема вам знакома, перематывайте к последней главе.

В идеальном мире, если в таблице миллион записей, а разных значений например, 100K, то на каждое значение приходится по 10 записей. Но что делать, если в список ваших значений затесалось особое значение, например, NULL, пробел или 'n/a'? Для SQL optimizier это головная боль. Для вас тоже.

Как такие значения вообще образуются?

Выделю пять причин (может, вы знаете больше?)

Такова жизнь - n/a, пустая строка итд. Те же нелегальные иммигранты, у которых нет SSN.

Умолчание. По своей сути мало отличается от предыдущего пункта

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

Следствие роста. Мы завели в базе 'department id' на будущее, когда будет много отделов, но очень долго был только один, и только вот стали появляться другие

Искусcтвенно сгенеренные данные. Чаще всего бывает при генерации данных для базы в DEV при лени разработчиков. Это потом аукнется на PROD.

Наиболее проблемными являются случаи кит и рыбешка и следствие роста, которые похожи друг на друга. Мы, как покажем ниже, можем сделать ветку для 'n/a', но категорически не хочется делать hardcode id 'кита' в коде, тем более, что в разных инсталляциях продукта для разных клиентов статистики могут быть совершенно разными.

Сделаем демо пример

create table Unlucky (
  n int identity primary key, 
  VAL varchar(32), 
  somethingElse varchar(128))
GO
set nocount on
declare @n int=1000000
while @n>0 begin 
  set @n=@n-1 
  insert into Unlucky select convert(varchar,@n/10)+'val',
    convert(varchar,@n)+convert(varchar,@n)
    +convert(varchar,@n)+convert(varchar,@n)
  end
GO
update Unlucky set VAL='n/a' where n%2=1
GO
create index VAL on Unlucky (val)
GO

В табличке миллион записей, но половина имеет val='n/a', для остальных записей у нас 5 записей на значение:

Потестируем

Как и ожидалось, SQL server достаточно умный и использует статистики для получения оптимального плана. Но давайте погрузим все это в процедуру:

create procedure GetVal @val varchar(32) as 
  select * from Unlucky where val=@val
GO

Пока все выглядит прeкрасно, потому что SQL server способен проверить значение параметра, когда значение передается явно, как константа. Это называется parameter sniffing.

А теперь о грустном.

Но parameter sniffing легко запутать.

И вот уже в обоих случаях используется index seek. А если первой вызовется процедура с n/a:

То есть кто первым встал, того и тапки. После перезапуска SQL server, после изменения статистики или в произвольный момент времени план SQL server может 'застрять' в 'неправильном' положении. Приходится хвататься за ~~молоток~~ freeproccache. Такая ситуация называется poisoned execution plan.

И как с этим бороться?

Первый подход в кодировании отдельной ветки для особого значения или значений:

create procedure SmartGetVal @val varchar(32) as 
  if @val='n/a' 
    select * from Unlucky where val='n/a' -- not @val!!!
  else
    select * from Unlucky where val=@val
GO

Второй подход заключается в использовании динамического SQL:

create procedure DynGetVal @val varchar(32) as 
  declare @sql varchar(1000)
  set @sql='select * from Unlucky where val='''+@val+''''
  exec(@sql)
GO

Второй подход надо использовать с осторожностью (SQL injection, возможные проблемы с правами на таблицы итд), С другой стороны для OLAP/Reporting систем построение длинного запроса может быть куда более эффективным, если количество вариантов указан параметр или нет велико. Кроме того, это позволяет использовать индекс с WHERE (см. ниже)

И уж точно это лучше любимого приема девелоперов, проклинаемого всеми DBA:

where ...
  and (@userid IS NULL or userid=@userid)
  and (@companyid IS NULL or companyid=@companyid)
  and (@deptid IS NULL or deptid=@deptid)

Также обратите внимание на query hints OPTION(RECOMPILE) - это может подойти для простых кверей но тратить 100-500ms на компиляцию длинных каждый раз не стоит.

Также обратите внимание на хинты OPTION(OPTIMIZE FOR UNKNOWN) и OPTION (OPTIMIZE FOR @var=value,...)

Также имеет смысл создать индекс, который не содержит частых значений:

drop index Unlucky.VAL
create index VAL on Unlucky (val) where val<>'n/a'

Но увы - процедура SmartGetVal прекратит работать оптимально, во второй ветке SQL не понимает, что можно использовать индекс, так как в@valточно нет значения 'n/a', и заставить его это сделать невозможно:

alter procedure SmartGetVal @val varchar(32) as 
  if @val='n/a' 
    select * from Unlucky where val='n/a' -- not @val!!!
  else
    select * from Unlucky with (index=val) where val=@val 
GO

Процедура создасться нормально но потом получим:

Msg 8622, Level 16, State 1, Procedure SmartGetVal, Line 5 [Batch Start Line 47]
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Без HINT вторая ветка будет использовать table scan. Динамический SQL же будет работать оптимально.

Полезные скрипты для обнаружения irregular selectivity

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

Первые колонки в комментариях не нуждаются, предпоследняя рассчитывается так: выбираем самое частое значение и смотрим, сколько на него приходится записей. Последняя колонка - то же самое в процентах. Если вы видите числа более 30%, то это кандидат на проблему.

Вы также увидите колонки с малой селективностью, которые индексировать, вероятно, не стоит.

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

'standalone' скрипт для MSSQL, измените имя таблицы в первой строке

declare @tab varchar(128) = 'tablename'
declare @s varchar(128), @sql varchar(max), @rows bigint
select @rows=max(rowcnt) from sysindexes where id=OBJECT_ID(@tab)
if @rows=0 set @rows=1
create table #res (s varchar(128), cnt int)
create table #tp (s varchar(128), topper int)
DECLARE cols CURSOR FOR select name from syscolumns where id=OBJECT_ID(@tab) 
  and xtype in (48,52,56,127,167,231,239,175,108) and length<=256
OPEN cols;
FETCH NEXT FROM cols into @s;
WHILE @@FETCH_STATUS = 0
BEGIN
  set @sql='insert into #res select '''+@s+''',(select count(distinct ['+@s+']) from ['+@tab+'])'
  print @sql
  exec (@sql)
  set @sql='insert into #tp select '''+@s+''',(select top 1 count(*) from ['+@tab+'] group by ['+@s+'] order by 1 desc)'
  print @sql
  exec (@sql)
  FETCH NEXT FROM cols into @s;
END
CLOSE cols;
DEALLOCATE cols;
select #res.s as [Column], cnt as DistinctValues, 
    @rows/case when cnt=0 then 1 else cnt end as RowsPerValueAvg, 
    topper as RecordsInMostFreqVal, 
    convert(money,topper*100./@rows) as PctInTop 
    from #res
  inner join #tp on #tp.s=#res.s
  order by 2 desc
drop table #res
drop table #tp

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