MySQL SQL语句分析与优化

这是我参与8月更文挑战的第5天，活动详情查看：8月更文挑战

由于开发中会使用到数据库,而mysql又是最经常使用的一种数据库,因为它开源免费,性能也很优秀,然而平始中最影响查询效率的就是开发人员编写的SQL语句不当,导致数据量过大而引起效率问题,接下来教大家常用的SQL分析与优化方法!

Explain

mysql中自带了explain关键字可以分析当前查询语句的性能分析,使用方法也非常简单,只需要在select语句前加上explain,然后再执行,这样返回结果不会返回原来是sql语句结果,而是返回这条SQL的分析结果。

测试sql

explain select * from sys_user
复制代码

结果:

上面是我们简单的一个测试,可以看到结果并不是sys_user表里的数据,而是对这个sql的一个分析.

id:id列的编号是select的序列号,有几个select就会有几个id,这id的顺序是表示执行顺服,id越大则优先级越高,并且id相同的话则是从上往下执行,id如果为NULL则是最后执行。
select_type:表示对应的select是查询类型,主要有以下几种:
1. simple：简单查询。查询不包含子查询和union
2. primary：复杂查询中在最外层的select
3. subquery：包含在select中的子查询(不在from子句中)
4. derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表,derived翻译过来就是如此
5. union：在 union 中的第二个和随后的 select语句
table:表示正在操作的表,当是from子查询的时候,会变成的格式,表示当前查询依赖 id=N 的查询,于是先查询id=N的,当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。
type:官网中是连接类型的意思,在《高性能mysql》一书中叫做访问类型,整体来说访问类型更准确,主要类型有以下几种:
1. NULL: 表示在执行语句的时候没有使用到表或者索引
2. const,system:mysql能对查询的某部分进行优化并将其转化成一个常量。用于primary key或unique key 的所有列与常数比较时,所以表最多有一个匹配行，读取1次,速度比较快。system是const的特例，表里只有当查询的表只有一行的时候为system
3.eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。
4.range：范围扫描通常出现在 in(), between,> ,<, >= 等操作中。
5.index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些
6. ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点,速度是最慢的。

最优到最差：system > const > eq_ref > ref > range > index > ALL
key:这一列显示mysql实际采用哪个索引来优化对该表的访问。
possible_keys:这一列显示查询可能使用哪些索引来查找。出现 possible_keys 有列,而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
key_len:这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列
ref:这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量）,字段名
row:估计要读取并检测的行数,不是最终结果集的行数
Extra:这行能告诉我们当前查询的一些情况,常出现的有以下几种:
1. Using index：使用覆盖索引
2. Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
3. Using index condition：查询的列不完全被索引覆盖
4. Using temporary：mysql需要创建一张临时表来处理查询
5. Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序
6. Select tables optimized away：使用某些聚合函数（如 max）来访问存在索引的某个字段的时候