Объясните про MRR?

Question

[Генри]

Оптимизирую сайт. Таблица 80тыс записей.
Вот такой запрос кушает более 500мс.:

SELECT * FROM `posts`
WHERE `category` = "2"  OR `category` = "3"  OR `category` = "6"  OR `category` = "7"  OR `category` = "8"  OR `category` = "9" OR `category` = "11"  OR `category` = "45"
LIMIT 0, 100;

Проверяю EXPLAIN'ом, MySQL возвращает:

+----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra                            |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------+
|  1 | SIMPLE      | posts    | NULL       | range | category      | category | 1       | NULL | 13516 |   100.00 | Using index condition; Using MRR |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------+

Почитал про MRR тут: dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html
Но, то ли мой английский подводит, то ли я просто ни чего не понял.

Может кто то понятно объяснить? Если можно с примером, типа так хорошо а так плохо.

Answer 1

[Melkij]

Вторичные индексы InnoDB, как известно, ссылаются на значение первичного ключа, а физически на дисках данные лежат рядом с первичным ключом и отсортированы по по этому самому первичному ключу. Значит, если в индексе написано, что вам нужны id 9, 6, 50, 8 и 7, выгоднее их читать с диска не в таком порядке, а пересортировать и прочитать двумя запросами: 6-9 и 50. Так получаем 2 запроса случайного чтения вместо 5. Это весьма полезно для HDD с медленной механикой, но ещё и приносит свои, хоть и более скромные, дивиденды для SSD - они хоть и на порядки быстрее HDD в случайном чтении, но и им тоже удобнее последовательное, а не случайное чтение.

Вот MRR этим как раз и занимается. Сначала получает список необходимых ключей от индекса, сортирует этот список и запрашивает у диска не по одной записи в случайных местах, а более крупными последовательными блоками.
Замечу, что MRR вступает в дело, если надо много чего прочитать с диска. Т.е. холодное чтение, данных для этой выборки по большей части нет в памяти. Разумеется, это далеко не быстрая штука.

И ещё момент: у вас в запросе нет order by, но есть limit - это значит, что вам всё равно, какие именно строки выбрать. Mysql в таком случае будет возвращать любые подходящие строки в любом порядке на своё усмотрение.

Comments

[Генри]

Спасибо, стало боле-менее понятно что это такое. Про order by знаю, специально убрал его из тестового запроса, чтобы не отвлекаться.
Вот только не совсем понял как его "Using". В переключателях стоит: mrr=on . Вместо category=2 or category=3 or ... category=9 сделать (category>=2 and category<=9)? Или нет?

[Melkij]

В каком смысле, как его "Using"? Автоматическая оптимизация выполнения, вроде index intersect. Если запрос выполняется часто, то тюнить настройки памяти, чтобы необходимые данные были в кэше, а не читались с диска. Если explain с прогретой машинки, значит данные в буфера катастрофически не влезают и вытесняются слишком быстро.

[Генри]

Данные с локального тестового сервера. Теперь понятно. Спасибо.