mysql高级2.3：性能分析

这篇文章写一下MySQL的性能分析：MySQL瓶颈、Explain和索引失效

MySQL常见瓶颈

CPU：CPU在饱和的时候一般发生在数据表数据装入内存或从磁盘中读取数据的时候
IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：通过top free iostat wmstat来查看系统的性能状态

Explain

是什么（查看执行计划）

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句。从而知道MySQL是如何处理的SQL预计的，分析你得查询或是表结构的性能瓶颈

能干嘛

能看到下面一些信息：

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

怎么玩

Explain + SQL语句
执行计划包含的信息：
- id
- select_type
- table
- paratitions
- type
- possible_keys
- key
- key_len
- ref
- rows
- filtered
- Extra

id

select 查询的序列号，包含一组数字，表示查询中执行的select 子句或操作表的顺序

3种情况：

id相同：执行顺序由上到下
id不同：如果是子查询，id的序列号会递增，id值越大，优先级越高，越先被执行
id相同不同：就是同时存在

select_type

有哪些？

SIMPLE PRIMARY SUBQUERY DERIVED UNION UNION RESULT

表示查询的类型，主要用于区别普通查询、联合查询、子查询等的复合查询

`SIMPLE`

简单的select查询，查询中不含有子查询或者 UNION

`PRIMARY`

查询中包含任何复杂查询的子部分，最外层查询被标记为

`SUBQUERY`

在SELECT或WHERE列表中包含了子查询

`DERIVED`

在FROM列表中包含的子查询被标记为 DERIVED（衍生），MySQL会递归这些子查询并把结果放在临时表中

`UNION`

若第二个SELECT出现在UNION之后，则被标记为UNION

若UNION包含在FROM子句的子查询中，外层SELECT被标记为：DERIVED

`UNION RESULT`

从UNION表中获取结果的RESULT

type

type所显示的是查询使用了哪种类型，type包含的类型包括如下的几种：

从最好到最差依次是：

1	system > const > eq_ref > ref > range > index > All

一般来说，得保证查询至少达到range级别，最好能达到ref。

`system`

表中只有一条记录（等于系统表），这是const类型的特例，平时不会出现，一般可以忽略。

`const`

表示通过索引一次就找到了，const用于比较 primary key 或者 unique 索引，因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL就能将该查询转换成一个常量。

`eq_ref`

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一性索引扫描。

`ref`

非唯一索引扫描，返回匹配某个单独值的所有行。

本质上也是一种索引访问，它返回所有匹配某个单独的值的行。

然而，它可能会找到多个符合条件的，所以它应该属于查找和扫描的混合体。

`range`

只检索给定范围的行，使用一个索引来选择行，key列显示了使用了哪个索引，一般就是在你的WHERE语句中出现了between、>、<、in等查询，比全表扫描好，只需开始与索引某一点，而结束于另一点。

`index`

Full Index Scan，index与All的区别为index类型只遍历索引树，这通常比All快，因为索引文件通常比数据文件小（也就是说虽然All和index都会读全表，但index是从索引中读取的，而All是从硬盘中读取的）。

`All`

Full Table Scan，将遍历全表以找到匹配的行。

possible_keys

显示可能应用在这张表上的索引，一个或多个，查询涉及到的字段，若存在索引，则索引被列出，但不一定被查询实际使用。

key

实际使用的索引。

注意：若key为NULL，则没有使用索引。（其中原因包括没有建立索引或索引失效）

若查询中使用了覆盖索引（select 后要查询的字段刚好和建立的索引字段完全相同），则该索引进出现在key列表中。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精度的情况下，长度越短越好。

key_len显示的值为索引字段的最大可能长度，并非实际长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要的读取的行数。

Extra

包含不适合在其他列中显示但有十分重要的额外信息。

Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作称为文件排序。
Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序，order by和group by
Using index：表示对应的select操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效率不错
- 如果同时出现了using where，表明索引被用来执行索引键值的查找
- 如果同时出现了using where，表明索引被用来读取数据而非执行查找动作
Using where：表明使用了where过滤
using join buffer：使用了连接缓存
impossible where：where子句的值总是false，不能用来获取任何元祖
select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段完成优化。
distinct：优化distinct操作，在找到第一匹配的元祖后即找到同样值的动作

索引失效

建表SQL

CREATE TABLE `mysql_advanced`.`staffs`( 
    `id` INT NOT NULL AUTO_INCREMENT, 
    `name` VARCHAR(20), `age` INT, 
    `pos` VARCHAR(20), 
    `add_time` DATETIME, 
    PRIMARY KEY (`id`) 
); 
INSERT INTO `staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('1', 'z3', '22', 'manager', '2020-12-14 18:56:30');
INSERT INTO `staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('2', 'July', '23', 'dev', '2020-12-14 18:56:30');
INSERT INTO `staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('3', '2000', '23', 'dev', '2020-12-14 18:56:30');

# 创建索引
create index idx_name_age_pos on staffs(name,age,pos);