У меня есть следующие две таблицы в MySQL (упрощенный).
clicks
(InnoDB)- Contains around about 70,000,000 records
- Имеет индекс в столбце
date_added
- Имеет столбец
link_id
, который ссылается на запись в таблицеlinks
links
(MyISAM)- Contains far fewer records, around about 65,000
Я пытаюсь выполнить некоторые аналитические запросы, используя эти таблицы. Мне нужно получить некоторые данные о кликах, которые произошли в течение двух указанных дат, при применении некоторых других выбранных пользователем фильтров с использованием других таблиц и объединении их в таблицу ссылок.
Однако мой вопрос вращается вокруг использования индексов. Когда я запускаю следующий запрос:
SELECT
COUNT(1)
FROM
clicks
WHERE
date_added >= '2016-11-01 00:00:00'
AND date_added <= '2016-11-03 23:59:59';
Я получаю ответ через 1,40 секунды. Используя EXPLAIN
, я обнаружил, что MySQL использует индекс столбца date_added
, как и ожидалось.
EXPLAIN SELECT COUNT(1) FROM clicks WHERE date_added >= '2016-11-01 00:00:00' AND date_added <= '2016-11-16 23:59:59';
+----+-------------+--------+-------+---------------+------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------------+---------+------+---------+--------------------------+
| 1 | SIMPLE | clicks | range | date_added | date_added | 4 | NULL | 1559288 | Using where; Using index |
+----+-------------+--------+-------+---------------+------------+---------+------+---------+--------------------------+
Однако, когда я LEFT JOIN
в своей таблице links
обнаружил, что выполнение запроса занимает гораздо больше времени:
SELECT
COUNT(1) AS clicks
FROM
clicks AS c
LEFT JOIN links AS l ON l.id = c.link_id
WHERE
c.date_added >= '2016-11-01 00:00:00'
AND c.date_added <= '2016-11-16 23:59:59';
Который завершился за 6,50 сек. Используя EXPLAIN
, я обнаружил, что индекс не использовался для столбца date_added
:
EXPLAIN SELECT COUNT(1) AS clicks FROM clicks AS c LEFT JOIN links AS l ON l.id = c.link_id WHERE c.date_added >= '2016-11-01 00:00:00' AND c.date_added <= '2016-11-16 23:59:59';
+----+-------------+-------+--------+---------------+------------+---------+---------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------------+---------+---------------+---------+-------------+
| 1 | SIMPLE | c | range | date_added | date_added | 4 | NULL | 6613278 | Using where |
| 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 4 | c.link_id | 1 | Using index |
+----+-------------+-------+--------+---------------+------------+---------+---------------+---------+-------------+
Как видите, индекс не используется для столбца date_added
в большей таблице и, кажется, занимает гораздо больше времени. Кажется, это становится еще хуже, когда я присоединяюсь к другим столам.
Кто-нибудь знает, почему это происходит, или я могу что-нибудь сделать, чтобы заставить его использовать индекс в столбце date_added
в таблице кликов?
Изменить
Я только что попытался получить свою статистику из базы данных, используя другой метод. Первый шаг в моем методе заключается в извлечении определенного набора link_id
s из таблицы кликов. Я обнаружил, что снова вижу ту же проблему, без JOIN. Индекс не используется:
Мой запрос:
SELECT
DISTINCT(link_id) AS link_id
FROM
clicks
WHERE
date_added >= '2016-11-01 00:00:00'
AND date_added <= '2016-12-05 10:16:00'
Этот запрос занял почти минуту. Я выполнил EXPLAIN
для этого и обнаружил, что запрос не использует индекс, как я ожидал:
+----+-------------+---------+-------+---------------+----------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------+---------+------+----------+-------------+
| 1 | SIMPLE | clicks | index | date_added | link_id | 4 | NULL | 79786609 | Using where |
+----+-------------+---------+-------+---------------+----------+---------+------+----------+-------------+
Я ожидал, что он будет использовать индекс date_added
для фильтрации результирующего набора, а затем извлечет отдельные значения link_id
. Любая идея, почему это происходит? У меня есть индекс link_id
, а также date_added
.
INDEX(link_id)
не поможет. - person Rick James   schedule 03.12.2016SHOW CREATE TABLE
. - person Rick James   schedule 03.12.2016clicks
и MyISAM для своей таблицыlinks
. - person Jonathon   schedule 05.12.2016date_added
использовался даже в случае 2-го запроса. То, чтоref
равно нулю, не означает, что индекс не использовался. На самом деле, это совершенно нормально для индекса, который используется для поиска диапазона. - person Shadow   schedule 05.12.2016