MySql 优化全集

前言:在日常工作中,我们需要对查询较慢的SQL进行分析,这时我们就会用到explain这个命令来查看一个这些SQL语句的执行计划,查看该SQL语句有没有使用上了索引,有没有做全表扫描。

-- 分析SQL
EXPLAIN SELECT * FROM zy_ticket_sw_classify WHERE industry_name = ‘石油开采‘;

MySql 优化全集

 

 

expain出来的信息有10列,分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

描述:
id:选择标识符;如果explain的结果包括多个id值,则数字越大越先执行;而对于相同id的行,则表示从上往下依次执行。
select_type:表示查询的类型;

查询类型 作用
SIMPLE 简单的SELECT语句(不包括UNION操作或子查询操作)
PRIMARY 查询中最外层的SELECT(如两表做UNION或者存在子查询的外层的表操作为PRIMARY,内层的操作为UNION)
UNION 在UNION中的第二个和随后的SELECT被标记为UNION。如果UNION被FROM子句中的子查询包含,那么它的第一个SELECT会被标记为DERIVED。
DEPENDENT UNION UNION中的第二个或后面的查询,依赖了外面的查询;(内层的SELECT语句与外层的SELECT语句有依赖关系)
UNION RESULT UNION操作的结果,id值通常为NULL
SUBQUERY 子查询中第一个SELECT(如果有多个子查询存在)
DEPENDENT SUBQUERY 子查询中第一个SELECT,但依赖于外层的表(如果有多个子查询存在)
DERIVED 被驱动的SELECT子查询(子查询位于FROM子句),MySQL会递归执行并将结果放到一个临时表中。MySQL内部将其称为是Derived table(派生表),因为该临时表是从子查询派生出来的
DEPENDENT DERIVED 派生表,依赖了其他的表
MATERIALIZED 被物化的子查询
UNCACHEABLE SUBQUERY 子查询,对于外层的主表,该子查询不可被物化也就是说结果无法缓存,针对外部查询的每一行重新评估,每次都需要计算(耗时操作);
UNCACHEABLE UNION UNION属于UNCACHEABLE SUBQUERY的第二个或后面的查询;内层的不可被物化的子查询(类似于UNCACHEABLE SUBQUERY)

特别注意:DEPENDENT SUBQUERY;1 会严重消耗性能;2 不会进行子查询,会先进行外部查询,生成结果集,再在内部进行关联查询;3 子查询的执行效率受制于外层查询的记录数 ;4 可以尝试改成join查询。

table:输出结果集的表
partitions:当前查询匹配记录的分区。对于未分区的表,返回null
type:表示表的连接类型;性能从好到坏排序;

连接类型 说明(性能从好到坏排序
system 该表只有一行(相当于系统表),system是const类型的特例
const 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
eq_ref 当使用了索引的全部组成部分,并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型,性能仅次于system及const。
ref 当满足索引的最左前缀规则,或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行,性能也是不错的。
fulltext 全文索引
ref_or_null 该类型类似于ref,但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询
index_merge 此类型表示使用了索引合并优化,表示一个查询里面用到了多个索引
unique_subquery 该类型和eq_ref类似,但是使用了IN查询,且子查询是主键或者唯一索引。
index_subquery 和unique_subquery类似,只是子查询使用的是非唯一索引
range 范围扫描,表示检索了指定范围的行,主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。
index 全索引扫描,和ALL类似,只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时,可使用此类型。
all 全表扫描,性能最差。

possible_keys:展示当前查询可以使用哪些索引,这一列的数据是在优化过程的早期创建的,因此有些索引可能对于后续优化过程是没用的。
key:表示实际使用的索引
key_len:索引字段的长度;由于存储格式,当字段允许为NULL时,key_len比不允许为空时大1字节。
ref:列与索引的比较;如果ref是一个函数,则使用的值是函数的结果。要想查看是哪个函数,可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。
rows:扫描出的行数(估算的行数),数值越小越好。
filtered:按表条件过滤的行百分比,表示符合查询条件的数据百分比,最大100。用rows × filtered可获得和下一张表连接的行数。例如rows = 1000,filtered = 50%,则和下一张表连接的行数是500。
Extra:执行情况的描述和说明;

 

接下来我会针对Extra显示的一些错误信息给出对应的解决方案。

1,Using where; Using join buffer (Block Nested Loop)

 

MySql 优化全集

上一篇:MySQL实战45讲-04 深入浅出索引(下)


下一篇:laravel操作数据库