以下为本人学习中的个人理解,辅以笔记,如有错误,请以正确知识为准。
一、使用explain查询SQL的执行计划
二、执行计划的字段解释与举例
大白话:
解释下不容易理解的dependent union/dependent subquery
explain select * from film_category
where film_id in(select film_id from film union all select film_id from film_actor) AS dr
其中,绿色字体的type为dependent subquery,蓝色字体的type为dependent union。
type列(重点):
大白话:system效率是最高的,这里是Innodb引擎,所以显示为ALL。
修改t4表的引擎
大白话:const仅次于system。
大白话:const -> eq_ref, eq_ref效率仅次于const,效率也是非常高的。
大白话:eq_ref -> ref,走唯一索引或主键的优于普通索引的。
上面通过“性能 棒”查询到了对应的数据,下面再通过“性 棒”查询
大白话:这里有个细节需要注意,因为解析器用的是ngram解析器,ngram_token_size为2,也就是说匹配的每个字段对应的值字符长度为2,匹配值的时候,字段匹配值的字符长度至少为2。
修改ngram_token_size,要在my.cnf中修改:
修改后全文检索
未创建普通索引前
name字段创建普通索引,看效果:
大白话:红色的是常用的,最好能达到range,index和ALL必须优化。
extra列(重点哦):
大白话:查询的字段是*(所有字段),不是每个字段上都有索引(不是每个字段都被索引覆盖到了),所以type为null,这里查询条件id是有索引的,但是查询的是*,所以必须再回到数据表查询数据字段。
大白话:film_id字段有索引,查询的时候,不需要回表查询,直接通过索引就可以获取数据。
大白话:first_name字段没有索引,所以extra为Using where。
大白话:t3表,有一个复合索引字段为id,name,现在查询的时候查询条件通过name去查询,name是索引的索引列字段之一,但不是索引的前导列(前导列,索引的第一个字段),而查询字段name自己是没有索引的,这个时候,就会出现Using where;Using index。
EXPLAIN select * from school where deleted_at > 0 and deleted_at < 1608789134452
#1 SIMPLE school range idx_estatus idx_estatus 8 6 100.00 Using index condition
大白话:查询条件字段查询的是范围的(不是等于的),且有存在于索引字段范围内,但不完全覆盖索引列字段。
大白话:查询列的字段没有索引,且需要一些额外操作,比如distinct、group by,这时extra的类型为Using temporary。
using filesort:mysql会对结果使用一个外部索引排序,而不是按索引顺序从表里读取行。此时mysql会根据连接类型浏览所有符合条件的记录,并保存顺序关键字和行指针,然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
大白话:就是需要对结果进行额外排序,而非根据索引顺序进行排序,这里的using filesor,并非一定是创建临时文件进行数据托盘排序,而是先在内存进行排序,内存(参考join buffer大小)不够,则创建临时文件落盘排序。
大白话:给and的每一个字段单独加索引,查询时对使用到的索引执行同步扫描分别获取数据集,并对所获得的数据集取交集。
大白话:给or的每一个字段单独加索引(重点,只能or两个字段,多了就不行了),查询时对使用到的索引执行同步扫描分别获取数据集,并对所获得的数据集取并集。
using sort_union和using sort_intersection: 用and和or查询信息量大时,先查询主键,然后进行排序合并后返回结果集
大白话:出现在用and和or查询信息量大时,先查询索引,然后进行排序合并后,才能读取记录并返回。
内表数据量要比较大,才可能看到,这里就没演示出来了。