Comments 25
В последнее время, всё чаще, использую для индексирования хранимые вью (MS SQL). Из плюсов наглядность, агрегации и группировки. Буду признателен если, кто поделиться минусами данного подхода.
Многочисленные ограничения на indexed view
Невозможность alter table, которые там используются, из-за schemabinding. Конечно, view можно пересоздать, но если записей очень много, это долго
Это да. Ограничения принял как данность, в принципе они логичные. Хотя обязательное использование count_big для аггрегации количества - это по моему лишнее.
Вот сейчас вспомнил неприятный момент с хранимыми вью - нужно обязательно приписывать with(noexpand) при выборке из них. Вью может не подхватиться, хотя указана явно, и раскрыться в запрос.
ещё индексы на вьюхах в онлайне не построить.
Аналогичное писал на примере PostgreSQL с пояснительными картинками: https://habr.com/ru/company/tensor/blog/488104/
Еще добавлю пример исключения для низкоселективных полей, когда они используются для сортировки - например, по иерархии "папки в начале": idx(parent::int, isbranch::bool) -> ... WHERE parent = $1 ORDER BY isbranch DESC LIMIT 20;
Создается ощущение что индексы все бывают только B-tree. В оракле, например, для низкоселективных полей есть bitmap. Наверняка в этих системах тоже есть аналоги какие-то. И рассуждать о применимости индекса только из селективности как-то однобоко.
Вот эта часть из статьи как раз подходит к битмап-индексам: "Селективность колонки мала, но селективность набора многих колонок высока. Если все эти колонки используются в WHERE, то такой индекс будет полезен."
Сила битмап-индексов в том, что они хорошо сочетаются друг с другом (а маски нулей и единиц, соответсвенно, накладываются). А недостаток – слишком много строк блокируется при модификации данных, поэтому такие индексы, как правило, не используют в OLTP-системах, а только в хранилищах.
Если в колонке дата/время не на последнем месте, то проверьте
Добавлю от себя исключение из недавнего на работе: если индекс не для сравнения, а для сортировки, то он вполне может начинаться с даты. `ORDER BY date, id` может быть использован для досортировки данных с одинаковой датой (это полезно, например, для пагинации, чтобы одни и те же строки не попали на разные страницы)
Есть три замечания по поводу PostgreSQL: 1) колонку с низкой селективностью, по которой используется предикат в запросе, очень часто надо добавлять не в индекс, а в предикат частичного индекса (при этом уменьшается размер индекса); 2) если неселективная колонка не меняет значения и значения не слишком перемешаны в хипе, может помочь brin-индекс (особенно начиная с 14й версии, в которой появились multirange), в худшем случае, он не сильно много места и ресурсов на поддержку отъест, по сравнению с другими типами (но лучше проверять, на сколько он хорош в конкретном случае); 3) Postgres, никто ведь не пишет "MS SQ".
У MS SQL Server есть отвратительная особенность: при отключенном версионировании( по разным причинам) сервер делает Lock на чтение данных! В этих случаях используется обходной путь чтобы избежать блокировок строятся индексы с кучей колонок в include секции. Выглядит уродливо, но альтернативы в виде read uncommited еще хуже.
Напишите пожалуйста подробнее. Что значит отключение версионирования? И какой лок накладывается? Впервые слышу про эту проблему...
На самом деле там идёт firehorse, как это вроде называют майкрософтовцы, а не Лок.
Своя терминология в MS продуктах это отдельная песня. Но я имел ввиду именно lock т.к. письмо которое приходит после deadlock где одна транзакция меняет данные, а вторая читает данные содержит следующий текст
A deadlock occurred on server NNNNNNN. Full details are available in the SQLdm Desktop Client.
Хотя вполне может быть что одна команда разработчиков MS SQL Server называет firehorse, в то время как другая lock.
Скорее всего это про READ_COMMITTED_SNAPSHOT - с ним читается копия c одним локом SCH-S, без него - ставятся page / row S-локи, блокирующие изменения.
Речь идёт про включение версионирования строк следующей командой:
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON
Без это команды прочитать строку во второй транзакции можно с разными хинтами
- WITH(NOLOCK) читает грязные данные (read uncommited)
- WITH(READPAST) пропускает заблокированные строки.
При SET READ_COMMITTED_SNAPSHOT OFF поведение read commited можно получить только через индексное чтение (когда данные из таблицы не читаются совсем)
Я правильно понимаю что мы говорим о двух сессиях, первая обновляет строку, вторая читает ее в обычном read committed?
-- table definition
create table dbo.test1 (
id int primary key clustered
, data1 varchar(100)
, data2 char(100)
, data3 nchar(100)
)
go
create index idx_test1_allincluded
on dbo.test1 ( id ) include ( data1, data2, data3 )
go
insert into dbo.test1 ( id, data1, data2, data3 )
values (1, 'data1 1', 'data2 1', 'data3 1')
, (2, 'data1 2', 'data2 2', 'data3 2')
, (3, 'data1 3', 'data2 3', 'data3 3')
go
-- session 1
update dbo.test1 set data1 = 'new data 1' where id = 2
go
-- session 2
select * from dbo.test1 where id = 2
goЕсли да, то в данном случае не важно есть ли дополнительный индекс или нет, т.к. эксклюзивная блокировка ключа (id=2) накладывается и в кластерном индексе и в дополнительном некластерном. Вторая сессия будет ждать завершения первой в любом случае, и с дополнительным индеком и без него.
Как раз вариант будет работать без блокировки если удалить кластерный индекс
select * from dbo.test1 with(INDEX(idx_test1_allincluded)) where id = 2MS SQL умеет индексное чтение когда данные достаються только из индекса. В этом случаее блокировки нету. Уровень изолированности read_commited
Не работает так, как вы описываете. Без кластерного индекса такое же поведение.
Все также висит X (exclusive) блокировка на некластерном индексе во время апдейта, и она не совместима с S (shared) блокировкой при чтении, вторая сессия ждет завершения первой.
Может быть описываемое вами поведение это просто баг/фича в какой-нибудь старой версии sql server?
Тестировалось на:
Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01
Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit)
on Windows 10 Pro 10.0 <X64> (Build 19044: ) 
При обосновании подхода "как не надо индексировать" прежде всего стоит сказать, что скорость select всегда достигается за счет ущерба в скорости update, insert и delete
Вот MySQL не умеет нормально использовать несколько индексов в одном запросе.
А noSQL умеют.
Как это достигается?
А noSQL умеют.
а какую именно БД вы имеете в виду? какого-то эталонного NoSQL не существует.
и какие именно запросы?
вот такие вроде все современные мейнстримовые sql (sqlite не в счёт) умеют обрабатывать с использованием двух индексов:
SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;ищутся подходящие записи по одному индексу, ищутся по второму, и возвращается пересечение результатов.
другое дело, что такой путь редко бывает оптимальным, потому планировщик нечасто его выбирает.
смотрите, нужно пройтись по всем значениям, попавшим под одно условие, потом под второе условие, и потом построить пересечение.
проще прикинуть под какое из условий попадает меньше результатов, и потом для каждого из этих результатов проверить другое условие.
btw, в первый раз с техникой использования нескольких индексов я встретился в foxpro (ещё под dos):
https://www.levelextreme.com/Home/ShowHeader?Activator=23&ID=8109
насколько я знаю, с тех пор ничего принципиально нового не придумали, серебряной пули, позволяющей эффективно использовать два индекса в одном запросе, так и нет.
или составной индекс, или использование партицирования в роли одного из индексов.
Как не надо индексировать