实战SQL分析
执行计划1:主键doc_id查询
上图,显示根据主键查询
执行计划2 新加d.is_deleted为无删除查询
执行计划
上图是只追加一个表的删除查询条件
执行计划3 新加s.is_deleted为无删除查询
执行计划
上图又追加 另一个表的是否删除状态的条件
注意:
执行计划4 Between and 和 IN
EXPLAIN SELECT * FROM doc_items WHERE link_type BETWEEN 1 AND 6;
EXPLAIN SELECT count(*) FROM knowledge_doc WHERE review_status IN (0,1,2);
在查询小数据量体下,IN和BEWTEEN AND效率不是太明显,从filtered字段值30 比 11.11
在查询近5W数据量体下,IN比较BEWTEEN AND效率就开始明显了
EXPLAIN SELECT * FROM doc_items WHERE link_type BETWEEN 1 AND 6;
EXPLAIN SELECT * FROM doc_items WHERE link_type IN (1,2,3,4,5,6);
所以、个人写SQL比较多的用IN,而比较少的用BETWEEN AND
执行计划5 查询字段是否是索引字段
EXPLAIN SELECT item_id FROM doc_items;
查询的字段是索引字段,则会命中索引
猜测,如果查询字段是索引字段,where条件字段是非索引字段,此SQL还会走索引吗?
验证以上猜测:
EXPLAIN SELECT item_id FROM doc_items WHERE link_type = 1;
结果:显示ALL 为全表扫描,未命中索引
猜测2 :where 字段加上索引比对查询时间和执行计划
EXPLAIN SELECT item_id FROM doc_items WHERE link_type = 6;
同样的SQL,数据量体在近3W查询结果中,在没加索引的情况下查询是300ms左右,加完索引后的查询效率,80ms左右
Extra: 很多额外的信息会显示到这个字段,上图是null,说明没有任何额外信息
-
Using index
覆盖索引扫描,表示查询在索引树中就可查找所需数据,不用扫描表数据文件,往往说明性能不错
猜测3:对比where为索引字段,查询字段为非索引字段和索引字段,耗时对比
SELECT link_value FROM doc_items WHERE link_type = 6;
查询字段加入索引后:
对比结果,没什么变化,此猜测没什么意义
联合索引:最左前缀原则
对比两张图,可看出possible_keys是能适合使用的索引key,但是查询效率没什么明显的变化,所以说,有些索引在
possible_keys
中出现,但并不表示此索引会真正被MySQL使用到。效率也没有明显提升。MySQL在查询时具体使用哪些索引,由
key
字段决定。
组合索引直接命中第一个有效
EXPLAIN SELECT doc_id FROM doc_items WHERE link_type = 6 AND is_deleted = 1;
rows 由5W+ 降至 2.4W+ 效率直接提升
查询条件和联合索引字段一致时的执行计划:
结论,联合索引,符合最左匹配原则,同时也符合全匹配原则即所有联合字段查询。
或者,多余字段加入后,联合索引也是有效的,只是filtered比例会有所下降。
总结
后续实战中继续添翼。。