У меня есть следующий запрос:
SELECT table_1.id
FROM
table_1
LEFT JOIN table_2 ON (table_1.id = table_2.id)
WHERE
table_1.col_condition_1 = 0
AND table_1.col_condition_2 NOT IN (3, 4)
AND (table_2.id is NULL OR table_1.date_col > table_2.date_col)
LIMIT 5000;
И у меня есть следующие ключи и индексы:
- первичный ключ table_1.id.
- индекс в table_1.col_condition_1
- индекс в table_1.col_condition_2
- составной индекс для table_1.col_condition_1 и table_1.col_condition_2
Подбираются правильные индексы. Запрос объяснить:
+--+----+-------------+---------+--------+---------------------------------------------------------------------+-----------------------+---------+------------+----------+-----------------------+--+
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+--+----+-------------+---------+--------+---------------------------------------------------------------------+-----------------------+---------+------------+----------+-----------------------+--+
| | 1 | SIMPLE | table_1 | range | "the composite index", col_condition_1 index ,col_condition_2 index | "the composite index" | 7 | | 11819433 | Using index condition | |
| | 1 | SIMPLE | table_2 | eq_ref | PRIMARY,id_UNIQUE | PRIMARY | 8 | table_1.id | 1 | Using where | |
+--+----+-------------+---------+--------+---------------------------------------------------------------------+-----------------------+---------+------------+----------+-----------------------+--+
table_1 содержит ~60 млн записей, а table_2 — ~4 млн записей.
Запросу требуется 60 секунд, чтобы вернуть результат.
Что интересно, так это:
SELECT table_1.id
FROM
table_1
LEFT JOIN table_2 ON (table_1.id = table_2.id)
WHERE
table_1.col_condition_1 = 0
AND table_1.col_condition_2 NOT IN (3, 4)
LIMIT 5000;
занимает 145 мс, чтобы вернуть результат, и имеет те же индексы, что и первый запрос.
SELECT table_1.id
FROM
table_1
LEFT JOIN table_2 ON (table_1.id = table_2.id)
WHERE
table_1.col_condition_1 = 0
AND (table_2.id is NULL OR table_1.date_col > table_2.date_col)
LIMIT 5000;
занимает 174 мс, чтобы вернуть результат.
Запрос объяснить:
+----+-------------+---------+--------+---------------------------------------------------------------------+-----------------+---------+------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+---------------------------------------------------------------------+-----------------+---------+------------+----------+-------------+
| 1 | SIMPLE | table_1 | ref | "the composite index", col_condition_1 index ,col_condition_2 index | col_condition_1 | 2 | const | 30381842 | NULL |
| 1 | SIMPLE | table_2 | eq_ref | PRIMARY,id_UNIQUE | PRIMARY | 8 | table_1.id | 1 | Using where |
+----+-------------+---------+--------+---------------------------------------------------------------------+-----------------+---------+------------+----------+-------------+
А также
SELECT table_1.id
FROM
table_1
LEFT JOIN table_2 ON (table_1.id = table_2.id)
WHERE
table_1.col_condition_2 NOT IN (3, 4)
AND (table_2.id is NULL OR table_1.date_col > table_2.date_col)
LIMIT 5000;
занимает около 1 секунды, чтобы вернуть результат.
Запрос объяснить:
+----+-------------+---------+--------+---------------------------------------------------------------------+-----------------+---------+------------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+---------------------------------------------------------------------+-----------------+---------+------------+----------+-----------------------+
| 1 | SIMPLE | table_1 | range | "the composite index", col_condition_1 index ,col_condition_2 index | col_condition_2 | 5 | | 36254294 | Using index condition |
| 1 | SIMPLE | table_2 | eq_ref | PRIMARY,id_UNIQUE | PRIMARY | 8 | table_1.id | 1 | Using where |
+----+-------------+---------+--------+---------------------------------------------------------------------+-----------------+---------+------------+----------+-----------------------+
Также, когда я использую каждое условие where отдельно, запрос возвращает результат через ~ 100 мс.
Мой вопрос заключается в том, почему запросу требуется значительное количество времени (60 секунд), чтобы вернуть результат при совместном использовании трех условий where, даже если кажется, что используются правильные индексы и выполняется запрос с любыми двумя из трех условий. также возвращает результат за гораздо меньшее время.
Кроме того, есть ли способ оптимизировать этот запрос?
Спасибо.
ИЗМЕНИТЬ:
создать таблицы:
Таблица 1:
CREATE TABLE `table_1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col_condition_1` tinyint(1) DEFAULT '0',
`col_condition_2` int(11) DEFAULT NULL,
`date_col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `compositeidx` (`col_condition_1`,`col_condition_2`),
KEY `col_condition_1_idx` (`col_condition_1`),
KEY `col_condition_2_idx` (`col_condition_2`)
) ENGINE=InnoDB AUTO_INCREMENT=68272192 DEFAULT CHARSET=utf8
Таблица 2:
CREATE TABLE `table_2` (
`id` bigint(20) NOT NULL,
`date_col` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
