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

Моя цель - предложение широкого ассортимента товаров и услуг на постоянно высоком качестве обслуживания по самым выгодным ценам.

Недавно мне пришлось объяснять это нашим братьям меньшим на работе, и я решил написать текст, который может пригодиться. В конце вы найдете ссылку на полезный скрипт для 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 систем построение длинного запроса может быть куда более эффективным, если количество вариантов указан параметр или нет велико. И уж точно это лучше любимого приема девелоперов, проклинаемого всеми 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,...)

Полезные скрипты для обнаружения 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

Источник: https://habr.com/ru/post/646763/


Интересные статьи

Интересные статьи

Когда создавались первые вычислительные машины, их воспринимали только как дополнение к человеческому разуму. И до недавнего времени так и было. Программисты учили компью...
Привет, с вами Ральф. Продолжаю публикацию решений, отправленных на дорешивание машин с площадки HackTheBox. В данной статье очень много всего. Посмотрим как для удобства совмес...
Предисловие Всем привет! Меня зовут Саша, и я больше шести лет занимаюсь тестированием бэкенда (сервисы Linux и API). Мысль о статье у меня появилась после очередной просьбы знакомого тестировщи...
Радиация есть везде. Есть она и в Москве. И я выгулял свой новый самодельный сцинтилляционный радиометр (о нем, наверное, скоро будет подробная публикация), чтобы выяснить, какие места в Моск...
С версии 12.0 в Bitrix Framework доступно создание резервных копий в автоматическом режиме. Задание параметров автоматического резервного копирования производится в Административной части на странице ...