当我运行以下查询时,我看到所检查的预期行为40
EXPLAIN SELECT s.* FROM subscription s
WHERE s.current_period_end_date <= NOW()
AND s.status in ('A', 'T')
AND s.period_end_action in ('R','C')
ORDER BY s._id ASC limit 20;
+----+-------------+-------+-------+--------------------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+--------------------------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | s | index | status,current_period_end_date | PRIMARY | 4 | NULL | 40 | Using where |
+----+-------------+-------+-------+--------------------------------+---------+---------+------+------+-------------+
但是,当我运行这个简单地将AND s.period_end_action in ('R','C')
更改为AND s.period_end_action = 'C'
的查询时,我看到所检查的预期行为611
EXPLAIN SELECT s.* FROM subscription s
WHERE s.current_period_end_date <= NOW()
AND s.status in ('A', 'T')
AND s.period_end_action = 'C'
ORDER BY s._id ASC limit 20;
+----+-------------+-------+-------+--------------------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+--------------------------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | s | index | status,current_period_end_date | PRIMARY | 4 | NULL | 611 | Using where |
+----+-------------+-------+-------+--------------------------------+---------+---------+------+------+-------------+
我在订阅表上有以下索引:
_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
INDEX(status, period_end_action),
INDEX(current_period_end_date),
有什么想法吗?我不明白为什么删除其中一个period_end_action值会导致检查的行数大幅增加?
(我同意其他人的观点,EXPLAIN
通常具有糟糕的行估计。(
事实上,这些数字可能是合理的(尽管我对此表示怀疑(。优化器决定在这两种情况下都进行表扫描。period_end_action
选项较少的查询可能需要进一步扫描才能获得20行。这是因为它倾向于使用任何一个二级索引。
这些索引更有可能帮助您的第二次查询:
INDEX(period_end_action, _id)
INDEX(period_end_action, status)
INDEX(period_end_action, current_period_end_date)
最佳索引是,通常从=
测试的任何列开始。
由于您的第一个查询没有这样的东西,Optimizer可能决定按_id
顺序扫描,这样就可以避免ORDER BY
强制要求的"排序"。