今天在准备MySQL培训材料的时候, 对线上的一个SQL做Tuning, 发现一个简单但是Performance很差的SQL(已经对原SQL进行简化):
SELECT *
FROM
`order`.`order` a
JOIN
`system`.`company` c ON a.`company_id` = c.`company_id` ORDER BY a.order_no
LIMIT 20;
|
她的执行计划:
执行结果: 184环境 41.216s.
简单的两个表Join, 20行数据, 41.216s, 而这个SQL的复杂版本其实就是我们的order list, 这个是每天都要执行很多遍的.
其实从执行计划我们已经可以预料出, 这个SQL的性能会很差, 因为使用了临时表, 使用了filesort, 这两个都是极耗费资源的操作.
但是看到这个结果, 我很是奇怪:
我们知道凡有order by的, 都尽量将order by条件所在表作为前导表, 我们的执行计划中order表已经是前导表, 而order by的条件也是order表的主键order_no, 至于company表, 大家都知道, 很小的一个表,几条数据而已. 在怎么样MySQL都不应该选择这样去执行呀!!
我们看一下另外一个SQL:
SELECT *
FROM `order`.`order` a
JOIN `common`.`currency` b ON a.`order_currency` = b.`currency_code`
ORDER BY a.order_no
LIMIT 20;
|
执行时间: 0s.
同样是小表, 同样是拿在order表没有index的order_currency去关联, 执行结果却差别如此之大!!!
百思不得其解, MySQL会如此的Stupid!!
但是下面的操作稍微让我有点释怀(为了测试我创建了另外一个表test.company):
CREATE TABLE test.company AS SELECT
company_id, `name` FROM system.company;
ALTER TABLE test.company ADD PRIMARY
KEY(company_id);
Select count(*) from system.company; -- 5条数据
|
我将SQL改为串test.company, 执行计划以及执行结果是一样的.
INSERT INTO test.company(company_id,
`name`) VALUES(6, 'aaaaa'); |
看执行计划, 没有变.
INSERT INTO test.company(company_id,
`name`) VALUES(7, 'bbbb’); |
然后, 我再来看执行计划, 奇迹出现啦!!
对此, 我的理解是:
MySQL会根据键的选择性来选择是不是走索引, 当某个字段的选择性超过某一个数值(一般说是20%)的时候, 如果以这个字段为条件扫描吗MySQL会选择全表扫描, 而不是该字段上的索引. 我们这个SQL, 一开始, 5条数据, 用company_id作为主键, 选择性是20%, 加到6条, 选择性是16.67%, 可能MySQL觉得这个数值还是不够小, 加到7条的时候, 选择性下降到14.28%,
MySQL认为这个值够了, 可以走索引了.
对此, 我验证一下:
DROP TABLE test.company;
CREATE TABLE test.company AS SELECT
company_id, `name` FROM system.company;
ALTER TABLE test.company ADD PRIMARY
KEY(company_id);
EXPLAIN
SELECT *
FROM
`order`.`order` a
JOIN
`test`.`company` c FORCE INDEX(PRIMARY) ON a.`company_id` = c.`company_id` ORDER BY a.order_no
LIMIT 20;
|
我们看执行计划:
果然, 我们强制MySQL在对company表扫描的时候, 走其主键索引, 于是整个执行都快了!
(只是为什么从表的索引选择会影响主表的索引选择?)
同样的, 我强制走主表的索引, 也达到了一样的效果:
EXPLAIN
SELECT *
FROM
`order`.`order` a FORCE INDEX(PRIMARY)
JOIN
`test`.`company` c ON a.`company_id` = c.`company_id` ORDER BY a.order_no
LIMIT 20;
|
结论 & 提醒: 大家在写SQL的时候, 需要警惕那种很小的微型表, 因为他们的索引字段选择性不够, 导致MySQL在扫描这些表的时候选择全表扫描, 从而会影响整个SQL的执行计划, 导致其他表的索引也不能正常使用!!
