Во-первых, индекс во втором случае всё-таки используется (ix_actions_created_at). Отсутствие «Using index» в колонке Extra означает лишь то, что при чтении из индекса ix_actions_created_at приходится делать дополнительный запрос для получение значений остальных колонок.
Во-вторых, в целом второй план выглядит более оптимальным: меньше строчек для обработки, и что самое главное — отсутствие filesort, т.к. при выборке данных из индекса ix_actions_created_at порядок, заданный в ORDER BY получается автоматически.
У меня нет достаточно данных, чтобы ответить на вопрос «почему тогда второй медленнее?». Если данные не умещаются в память, то дополнительные запросы для получения значений всех колонок действительно будут занимать много времени. Но в первом случае сервер делает то же самое (т.к. нет «Using index»). Одинаковая ли конфигурация? Какова производительность в первом случае, если добавить FORCE INDEX (ix_actions_created_at)? Как изменится производительность во втором случае, если добавить FORCE INDEX (ix_actions_user_id,ix_actions_type)
Было бы интересно посмотреть на вывод EXPLAIN FORMAT=JSON (доступно в MySQL 5.6+, MariaDB 10.1) или ANALYZE FORMAT=JSON. К сожалению, у вас MariaDB 10.0, где нет ни того, ни другого.
Но вопрос, как я понимаю, даже не в этом, а в том, куда обратиться (с этим и подобными вопросами) и куда смотреть. Можно написать в Moscow MySQL User Group: groups.google.com/forum/#!forum/moscow-mysql-user-group
Можно написать в MariaDB (https://launchpad.net/~maria-discuss). Если вы считаете, что нашли баг, его нужно создать в MariaDB JIRA: mariadb.atlassian.net/secure/Dashboard.jspa
Ну и StackOverflow тоже может быть полезным :)
Куда смотреть: я бы попробовал новые функции отладки оптимизатора в MySQL 5.6/5.7 и MariaDB 10.1. Их можно установить хотя бы в тестовых целях. Без них решать проблемы оптимизатора можно только методом проб и ошибок, т.е. проверять гипотезы, меняя конфигурацию и текст запроса по одному изменению за раз.
Машина на которой выполняется запрос одна, т.е. запросы на одной БД с одной конфигурацией. С памятью оперативной там беда.
Добавляю FORCE INDEX (ix_actions_created_at) к первому запросу, который был быстрый, он начинает очень долго думать:
MariaDB [80Product_12]> explain SELECT * FROM actions FORCE INDEX (ix_actions_created_at) WHERE actions.user_id = '11111' AND actions.type = 'BonusDist' ORDER BY actions.created_at DESC LIMIT 3;
+------+-------------+---------+-------+---------------+-----------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+-----------------------+---------+------+------+-------------+
| 1 | SIMPLE | actions | index | NULL | ix_actions_created_at | 10 | NULL | 3 | Using where |
+------+-------------+---------+-------+---------------+-----------------------+---------+------+------+-------------+
Если ко второму запросу добавить FORCE INDEX (ix_actions_user_id,ix_actions_type), то производительность возрастает и становится где то на равне, как первый запрос, без FORCE INDEX (ix_actions_created_at):
MariaDB [80Product_12]> explain SELECT * FROM actions FORCE INDEX (ix_actions_user_id,ix_actions_type) WHERE actions.user_id = '222222' AND actions.type = 'BonusDist' ORDER BY actions.created_at DESC LIMIT 3;
+------+-------------+---------+------+------------------------------------+--------------------+---------+-------+-------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+------------------------------------+--------------------+---------+-------+-------+----------------------------------------------------+
| 1 | SIMPLE | actions | ref | ix_actions_type,ix_actions_user_id | ix_actions_user_id | 98 | const | 46114 | Using index condition; Using where; Using filesort |
+------+-------------+---------+------+------------------------------------+--------------------+---------+-------+-------+----------------------------------------------------+
1 row in set (0.00 sec)
Только что сейчас заметил, у первого запроса нет подходящих под условие данных, у второго есть данные, возможно играет роль тоже.
Думаю, дело вот в чём. Т.к. активный набор данных не умещается в память, дешевле выбрать много лишних строчек (т.е. всех, одовлетворяющих условию WHERE), потом отсортировать по created_id и выдать первые три, чем сканировать created_at полностью, но при этом ибежать сортировки.
К сожалению, оптимизатор в MySQL не учитывает разницу в доступе между закэшированными и незакэшированными данными, поэтому иногда может принимать неверные решения. Хотя это в той или иной мере относится ко всем СУБД (для этого и придуманы хинты).
Подозреваю, что ситауцию может изменить составной индекс по (user_id, type, created_at).
Алексей
