MariaDB [80Product_12]> explain SELECT * FROM actions 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_merge | ix_actions_type,ix_actions_user_id | ix_actions_user_id,ix_actions_type | 98,152 | NULL | 18127 | Using intersect(ix_actions_user_id,ix_actions_type); Using where; Using filesort |
+------+-------------+---------+-------------+------------------------------------+------------------------------------+---------+------+-------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)MariaDB [80Product_12]> explain SELECT * FROM actions 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 | index | ix_actions_type,ix_actions_user_id | ix_actions_created_at | 10 | NULL | 1013 | Using where |
+------+-------------+---------+-------+------------------------------------+-----------------------+---------+------+------+-------------+
1 row in set (0.00 sec)MariaDB [80Product_12]> SHOW INDEX from actions;
+---------+------------+------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| actions | 0 | PRIMARY | 1 | id | A | 15565863 | NULL | NULL | | BTREE | | |
| actions | 1 | ix_actions_type | 1 | type | A | 3800 | NULL | NULL | | BTREE | | |
| actions | 1 | ix_actions_created_at | 1 | created_at | A | 15565863 | NULL | NULL | | BTREE | | |
| actions | 1 | ix_actions_aggregated_at | 1 | aggregated_at | A | 7782931 | NULL | NULL | YES | BTREE | | |
| actions | 1 | ix_actions_aff_aggregated_at | 1 | aff_aggregated_at | A | 178917 | NULL | NULL | YES | BTREE | | |
| actions | 1 | ix_actions_user_id | 1 | user_id | A | 3113172 | NULL | NULL | | BTREE | | |
+---------+------------+------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
MariaDB [80Product_12]> SELECT @@version;
+-------------------------------+
| @@version |
+-------------------------------+
| 10.0.21-MariaDB-1~precise-log |
+-------------------------------+
1 row in set (0.00 sec)
Во-первых, индекс во втором случае всё-таки используется (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).
Средний
Простой
Средний
