Имеет ли значение порядок составного индекса базы данных, если первый столбец запрашивается только с помощью операции равенства?
Вопрос: если у меня есть таблица Orders с индексом (UserId, CreatedTime), имеет ли значение добавление DESC к CreatedTime, если я хочу перечислить заказы только одного пользователя?
Например:
SELECT *
FROM Orders -- Also potentially have row limit
WHERE UserId = @UserId
ORDER BY CreatedTime DESC;
Чего я не понимаю, так это того, что если ответ положительный, то почему БД не может просто начать снизу и идти вверх, поскольку она уже знает диапазон @UserId?
Кроме того, я работаю как с SQL Server, так и с SQLite, поэтому мне было бы интересно узнать, будет ли ответ отличаться для каждой СУБД.
Я до сих пор не очень понимаю Индексы SQL Server - по возрастанию или по убыванию, какая разница? и, видимо, в MongoDB, это не имеет значения (Имеет ли значение порядок индексов в MongoDB?).
Возвращаясь к приведенному выше запросу, даже если бы мне пришлось перечислить всех пользователей и соответствующие им порядки в порядке убывания CreatedTime, почему база данных не может сделать это:
- Для пользователя №1 строки расположены с №1 по №10, поэтому извлекайте строки с №1 по №10 в обратном порядке.
- Для пользователя №2 строки расположены с №11 по №13, поэтому извлекайте строки с №11 по №13 в обратном порядке.
- ...
Я даже спросил у ИИ, и он просто сказал мне, что извлечение снизу вверх происходит медленнее, без дальнейших объяснений, даже когда я пытался нажать на него.
В SQL Server здесь нет большой разницы. Листовые страницы индекса представляют собой двусвязный список, поэтому его можно перемещать как вперед, так и назад. Однако обратное движение не может быть распараллелено. Если вы посмотрите на план выполнения, он сообщит вам, находится ли в свойствах оператора поиска (при условии, что вы получили его с *), является ли он заказанным, и направление сканирования, если да.
@ThomA извините, я на самом деле не проектировщик баз данных, а в основном программист .NET, но иногда проектирование БД является частью моей работы. На самом деле у меня нет инструментов и знаний для их сравнительного анализа. В моем приложении .NET никакой существенной разницы не наблюдается, но я думаю, что данные моих тестов слишком «идеальны».
@MartinSmith ах, я вижу параллелизм, указанный в качестве причины в связанной статье. Извините, как программист, но мне просто трудно понять, чем движение назад отличается от движения вперед. Думаю, есть веская причина, почему они этого не делают.
Я думаю, единственная причина в том, что они так и не удосужились реализовать это. Так что это не особо веская причина
Вам следует задавать только одну СУБД на каждый вопрос.
Обратите внимание, что обратное сканирование, на которое ссылается @MartinSmith, всегда противоположно направлению индекса. Итак, если индекс DESC и запрос DESC, это не противоположное направление. Только если запрос и индекс разные.
Ответы 1
В SQLite индекс будет использоваться независимо от того, сортируете ли вы по CreatedTime по возрастанию или убыванию.
Данный:
create table orders (
user_id integer,
created_time integer
);
create index orders_index on orders (user_id, created_time);
Планы запросов для обоих типов идентичны:
sqlite> explain query plan select * from orders where user_id = 1 order by created_time;
QUERY PLAN
`--SEARCH orders USING COVERING INDEX orders_index (user_id=?)
sqlite> explain query plan select * from orders where user_id = 1 order by created_time desc;
QUERY PLAN
`--SEARCH orders USING COVERING INDEX orders_index (user_id=?)
Если вместо этого вы выполняете запрос диапазона по user_id, а затем сортируете по user_id, created_time, дополнительная временная таблица будет использоваться только в том случае, если created_time отсортировано в порядке убывания:
sqlite> explain query plan select * from orders where user_id > 1 order by user_id, created_time;
QUERY PLAN
`--SEARCH orders USING COVERING INDEX orders_index (user_id>?)
sqlite> explain query plan select * from orders where user_id > 1 order by user_id, created_time desc;
QUERY PLAN
|--SEARCH orders USING COVERING INDEX orders_index (user_id>?)
`--USE TEMP B-TREE FOR LAST TERM OF ORDER BY
Для этого запроса вам понадобится индекс для created_time desc:
create index orders_index_2 on orders (user_id, created_time desc);
=>
sqlite> explain query plan select * from orders where user_id > 1 order by user_id, created_time desc;
QUERY PLAN
`--SEARCH orders USING COVERING INDEX orders_index_2 (user_id>?)
Это очень интересно. Благодаря вам я знаю запрос explain query plan. Тем не менее мне любопытно, зачем нужна временная таблица.
Временная таблица необходима для where user_id > 1 order by user_id, created_time desc, поскольку индекс включен user_id, created_time (оба по возрастанию), а вы запрашиваете результаты в другом порядке user_id asc, created_time desc. Механизм базы данных будет сканировать вперед, чтобы найти user_id > 1, поэтому created_time также будет возрастать, что необходимо исправить с помощью другой сортировки.
Обратите внимание: если вы запросите оба столбца в порядке убывания, временная таблица исчезнет из плана запроса: select * from orders where user_id > 1 order by user_id desc, created_time desc потому что база данных теперь может выполнить обратное сканирование по этому индексу.
Что, это так странно, лол. Извините, для меня это не имеет смысла. Если он может сканировать вверх (когда у обоих есть DESC), почему он не может сканировать вверх в поисках CreatedTime только тогда, когда точно знает, где находится каждый UserId?
@LukeVo индекс сортируется по user_id asc, created_time asc. Если вы пройдете его вперед, вы получите оба значения по возрастанию, если вы пройдете по нему назад, вы получите оба значения по убыванию, но вы не можете пройти с одним значением по возрастанию и одним по убыванию. Обратите внимание, что индекс будет использоваться для запроса по возрастанию + убыванию для фильтрации пользователей, но для сортировки второго столбца по убыванию потребуется использовать временную таблицу.
Что вы обнаружили, протестировав и проверив планы запросов?