Форум пользователей MySQL

rgbeast написал:

в случае ORDER BY col, col2 тоже используется ключ col

В том-то и дело, что нет: при добавлении в сортировку второй колонки запрос перестаёт использовать индекс!

mysql> SHOW CREATE TABLE goods\G
*************************** 1. row ***************************
       Table: goods
Create Table: CREATE TABLE `goods` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type_id` smallint(6) NOT NULL,
  ...
  PRIMARY KEY (`id`),
  ...
  ) ENGINE=MyISAM 
1 row in set (0.00 sec)


mysql> EXPLAIN SELECT id, type_id FROM goods ORDER BY id LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 10
        Extra:
1 row in set (0.00 sec)


mysql> EXPLAIN SELECT id, type_id FROM goods ORDER BY id, type_id LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 364
        Extra: Using filesort
1 row in set (0.00 sec)

Меня смущает, что не используется левая часть индекса (хотя могла бы: если надо 10 записей из 1000 и известно, что первые 5 записей имеют одно значение колонки col1, а вторые 7 записей имеют другое, то незачем лезть за следующими, поскольку при сортировки они все равно будут отброшены).

Но главным образом смущает это потому, что это очень коварно: есть привычка составлять запросы, исходя из того, что будет использоваться хотя бы левая их часть. Например, WHERE a = ... and b IN (...) ORDER BY c будет использовать индекс (a,b). А ORDER BY a, b, c — не будет.

Представь себе, что у тебя a выбирает два блока по 1000 строк. Будет ли
эффективнее пересортировывать внутри блока или целиком?

Так я же не предлагаю пересортировывать внутри блока. Я
предлагаю лишь выбрать эти блоки по a. Ведь легче выбрать два
блока по 1000 строк и потом их все вместе отсортировать, чем сортировать прямо сразу, но при этом читать миллион. (Или нет?)

какой смысл выбирать их по а, если потом надо будет выбрать все остальные
строки

Вот именно, что не надо!
Попробую еще раз, попонятней.
Есть 1000 строк.
Запрос ORDER BY A, B LIMIT 10
При этом есть ключ по A.
Смотрим в ключ. Видим первые 5 строк. У них значение A одно и то же, следовательно как они там точно отсортированы будут — мы не знаем. Но они точно будут идти первыми (хотя и непонятно, как они будут внутри себя отсортированы по B, это и не важно), поэтому мы их забираем.
Смотрим дальше. Видим еще, например, 7 строк. У них всех тоже одно и то же значение A. И эти 7 строк будут явно идти после предыдущих 5 (поскольку первичная сортировка по A), но точно перед какими-либо еще из оставшихся в таблице. Т.к. нам надо 10 строк, а у нас есть только 5 — забираем эти 7 (придется забрать все 7, т.к. про col2 внутри них мы ничего не знаем).
Теперь у нас есть 12 строк. 12 > 10, поэтому больше нам строк не надо. Теперь мы идем за col2 для этих 12 строк (а не для всех 1000) и сортируем 12 строк окончательно.

MySQL же просто читает все 1000 строк, сортирует их все и только потом делает вывод о том, какие строки отдать. Да, 1000 — последовательное чтение вместо выдергиваний по индексу. Но сортировать-то надо будет всю тысячу (а если миллион? каково процессору будет?)

Надо.

Не понимаю, зачем?
Мы выбрали все строки с двумя значениями col1, которые являются экстремальными (т.е. самыми большими или самыми маленькими из всех имеющихся; у нас в ключе отсортировано, и мы это знаем наверняка).
Это значит, что другие строки ни при каких условиях вперед этих в результат запроса не залезут.
При этом имеющихся строк нам уже хватает.
Зачем нам вообще остальные записи тогда трогать?

Как я понимаю, ты, в целом, хотел сказать следующее: "существуют случаи, когда сортировка в индексе не позволяет узнать достаточно о порядке следовании записей, и все равно быстрее сразу читать таблицу".

Согласен с этим.
Но!
Когда запрос WHERE a BETWEEN ..., такие случаи тоже существуют.
На то у оптимизатора есть варианты: если по ключу отбираются пятьсот тысяч записей из миллиона, то быстрее сканировать таблицу, если же из миллиона отбираются десять записей - вытаскивать по ключу. Это в случае WHERE.
В случае ORDER BY принцип такой: если по ключу сортируются и отбираются пятьсот тысяч записей из миллиона (и остальные пятьсот тысяч не нужны), а нужно десять - сканировать таблицу, если по ключу сортируются и отбираются десять записей из миллиона, и нужно десять - все равно сканировать таблицу.

Не понимаю, почему в случае WHERE по двум колонкам, где одна не в индексе, можно им пользоваться, а в случае ORDER BY — нет.

Можно я влезу в ваш разговор?
SELECT SQL_NO_CACHE * FROM `temp` WHERE `a` IN (3,4)  ORDER BY `sort` DESC LIMIT 10
Регулярное мучение мое, т.к. все время filesort
1 миллион записей, на которые делаются filesort. Против того, что алгоритм не умеет обходить N деревьев-это жестоко.
Вот SELECT SQL_NO_CACHE * FROM `temp` WHERE (`a`=3) ORDER BY `sort` DESC LIMIT 10
Находится вход, получается поддерево. Все ок, все быстро.
SELECT SQL_NO_CACHE * FROM `temp` WHERE `a` IN (3,4)  ORDER BY `sort` DESC LIMIT 10
Находится два входа, получаются два поддерева, ставим курсоры в две вершины поддерева и спускаемся. добавляется 10 сравнений, все.
10 дополнительных операций сравнения против filesort для 1 млн.

Отредактированно relax (30.04.2011 10:48:59)

индекс то один -составной (`a`,`sort`). и он вполне подходил, если бы руки у них дошли  сделать как надо.
И больше одного индекса может использоваться- вспомните merge
а решение я знаю. Что не отменяет того факта, что это-знатный изврат.
кстати, писать надо UNION ALL а то лишняя операция проверки уникальности.