为什么要做SQL语句优化
a.分析SQL的执行计划 : explain ,可以模拟SQL优化器执行SQL语句,从而让开发人员 知道自己编写的SQL状况
b.MySQL查询优化其会干扰我们的优化
查询执行计划: explain +SQL语句
参数
id : 编号
select_type :查询类型
table :表
type :类型
possible_keys :预测用到的索引
key :实际使用的索引
key_len :实际使用索引的长度
ref :表之间的引用
rows :通过索引查询到的数据量
Extra :额外的信息
1.id(每个select会生成一个id)
a.id值相同,从上往下 顺序执行。
通过调节表的执行顺序,可降低中间过渡表所占用的内存,遵循小表在前的原则进行查询(笛卡尔积)。
b.id值不同:id值越大越优先查询 (本质:在嵌套子查询时,先查id值大的内层 再查id值小的外层)
2.select_type:查询类型
PRIMARY:包含子查询SQL中的 主查询 (最外层)
SUBQUERY:包含子查询SQL中的 子查询 (非最外层)
simple:简单查询(不包含子查询、union)
derived:衍生查询(使用到了临时表)
3.type:索引类型、类型
system>const>eq_ref>ref>range>index>all ,要对type进行优化的前提:有索引
其中:system,const只是理想情况;实际能达到 ref>range
system(忽略): 只有一条数据的系统表 ;或 衍生表只有一条数据的主查询
const:仅仅能查到一条数据的SQL ,用于Primary key 或unique索引 (类型 与索引类型有关)
eq_ref:唯一性索引:对于每个索引键的查询,返回匹配唯一行数据(有且只有1个,不能多 、不能0)
ref:非唯一性索引,对于每个索引键的查询,返回匹配的所有行(0,多)
range:检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效 ,从而转为 无索引all)
index:查询全部索引中数据
all:查询全部表中的数据
注意:
system/const: 结果只有一条数据
eq_ref:结果多条;但是每条数据是唯一的 ;
ref:结果多条;但是每条数据是是0或多条 ;
4.possible_keys :可能用到的索引,是一种预测,不准。
如果 possible_key/key是NULL,则说明没用索引
5.key :实际使用到的索引
6.key_len :索引的长度 ;
作用:用于判断复合索引是否被完全使用 ,如果索引长度与实际使用索引一致则说明索引未失效。
其中:utf8:一个字符占3个字节;
gbk:1个字符2个字节
latin:1个字符1个字节
如果索引字段可以为Null,则会使用1个字节用于标识;
7.Extra:
(i).using filesort : 性能消耗大;需要“额外”的一次排序(查询) 。常见于 order by 语句中。排序:先查询
对于单索引, 如果排序和查找是同一个字段,则不会出现using filesort;如果排序和查找不是同一个字段,则会出现using filesort;
复合索引:不能跨列(最佳左前缀)
避免方式: where和order by 按照复合索引的顺序使用,不要跨列或无序使用。
(ii). using temporary:性能损耗大 ,用到了临时表。一般出现在group by 语句中。
避免方式:查询那些列,就根据那些列 group by .
(iii). using index :性能提升; 索引覆盖(覆盖索引)。原因:不读取原文件,只从索引文件中获取数据 (不需要回表查询),只要使用到的列 全部都在索引中,就是索引覆盖using index。既:所有查询的列均是索引
(iii).using where (需要回表查询)