我有以下疑问：
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;我有以下键和索引：
表1.id主键。
表\u 1上的索引。列\u条件\u 1
表1上的索引。列2
表1.col\u条件1和表1.col\u条件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 | |
+--+----+-------------+---------+--------+---------------------------------------------------------------------+-----------------------+---------+------------+----------+-----------------------+--+表1有~60 mm的记录，表2有~4 mm的记录。
查询需要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毫秒内返回一个结果。
我的问题是，当同时使用三个where条件时，为什么查询要花费大量的时间（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
3条答案
按热度按时间wvyml7n51#
OR是性能杀手。有时使用
UNION而不是OR可以加快查询速度。也许在一个案例中，5000人“接近”合并表的开头，但在另一个案例中则不然。
使用
LIMIT没有ORDER BY这是可疑的。因为pk是唯一的密钥，所以声明也是多余的
id_UNIQUE.INDEX(a)当你也有INDEX(a,b).如果只有4个值，
IN (1, 2)可能比NOT IN (3, 4).两张table共用一个主键是不常见的。为什么你们的关系是1:1？
如果我们能看到真实的列名，我们可能会有进一步的了解。
zdwk9cvp2#
像这样的问题往往需要尝试的东西和测试，看看他们有多好的工作。
因此，从以下几点开始：
逻辑推理为什么这等同于您的查询：您的原始查询的where语句
(table_2.id is NULL OR table_1.date_col > table_2.date_col)可以概括为“仅包括没有表2记录或表2记录早于（或等于）表1记录的表1记录。我的查询版本使用反联接来排除所有表\u 1记录，如果它们存在于早于（或等于）表\u 1记录的表\u 2中。
索引
有许多可能的复合索引可以帮助这个查询。以下是几点：
对于表2：
(id,date_col)对于表1：(col_condition_1,id,date_col,col_condition_2)请尝试我的查询和索引，并报告结果（包括解释计划）。mi7gmzs63#
尝试将现有的sql分为两部分，并查看每个部分的执行时间。这将很有希望给你什么部分是造成缓慢的原因：
第1部分：
第二部分（注意这里的内部连接）：
我想第二部分会花更长的时间。在这方面，我认为表1和表2的date\u coll索引都会有所帮助。
我认为综合指数对你的选择毫无帮助。
这意味着很难诊断为什么这三种情况一起会对性能造成如此严重的影响。这似乎与您的数据分布有关。不确定mysql，但在oracle中，这些表上的统计数据集合会起作用。
希望有帮助。