MySQL高性能实战——part3——分析SQL,定位慢SQL(性能优化的前提)

前言：
此文借鉴《MySQL高性能》一书，还有MySQL官方文档，笔者将通过自身的一些实战经验和阅读习惯对本书进行一个总结，整理，归纳出企业级开发中常用的优化案列和部分概念！！
​

官方文档地址：
https://dev.mysql.com/doc/refman/5.7/en/
​

本文：由于数据库知识庞大且复杂，笔者将以，以下几个模块进行拆分解读，请大家按需自取！！！

1. MySQL高性能实战——part1——MySQL架构与历史
2. MySQL高性能实战——part2——Schema和数据类型优化
3. MySQL高性能实战——part3——分析SQL,定位慢SQL(性能优化的前提)
4. MySQL高性能实战——part4——高性能索引使用
5. MySQL高性能实战——part5——查询性能优化
6. MySQL主从复制与读写分离，分库分表

​

​

​

版本说明：
不同数据库版本可能对SQL的执行以及部分特性产生巨大的影响，所以在这里明确一下版本
参考书籍版本：5.1或5.5
实战案列实际使用版本：5.7
​

MySQL高性能实战——part3——分析SQL,定位慢SQL(性能优化的前提)

这一章节主要讲述，我们在日常的开发任务中，在编写SQL的过程中怎么分析我们的SQL的性能屏障，以及该如何优化，以及上线之后怎么去定位我们系统在实际运行中，耗时比较长的SQL并进行想对应的优化！主要是这2部分！

一.性能优化分析之explain

官方文档地址：https://dev.mysql.com/doc/refman/5.7/en/execution-plan-information.html

1.explain是什么？

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

来我们看看官方的说法：

• EXPLAIN与 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句一起使用。
• 当EXPLAIN与可解释语句一起使用时，MySQL 显示来自优化器的有关语句执行计划的信息。也就是说，MySQL 解释了它将如何处理该语句，包括有关表如何连接以及以何种顺序连接的信息。有关使用 EXPLAIN获取执行计划信息的信息，请参阅第 8.8.2 节，“EXPLAIN 输出格式”。
• 在 EXPLAIN帮助下，您可以查看应该在哪里为表添加索引，以便通过使用索引查找行来更快地执行语句。
• 您还可以使用 EXPLAIN检查优化器是否以最佳顺序连接表

​

2.explain能干嘛？

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

​

3.explain怎么使用

EXPLAIN+SQL语句
​

列如：

mysql> explain
    -> select *
    -> from index_test
    -> where id =1;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | index_test | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

​

发现加上explain关键字之后，并没有输出查询结果，而是一大堆看不懂的东西，接下来的部分，会对加上explain关键字的SQL语句返回结果进行分析，并读懂Mysql在实际运行时，是怎么去执行SQL语句的，并以此为基础进行优化！
​

​

4.explain语法解析，怎么看懂explain的返回结果

在演示之前这里先提供创表语句，已方便大家的学习和测试，假如目前已知我们有以下几个表

create table student(
id bigint unsigned primary key auto_increment,
name varchar(50) ,
age tinyint unsigned
)charset ='utf8',engine =innodb;


create table course(
 id bigint unsigned primary key auto_increment,
 name varchar(50) ,
 teacher_id bigint unsigned
)charset ='utf8',engine =innodb;


create table teacher(
    id bigint unsigned primary key auto_increment,
    name varchar(50)
)charset ='utf8',engine =innodb;

create table score(
    id bigint unsigned primary key auto_increment,
    student_id bigint unsigned,
    score double,
    course_id bigint unsigned,
    test_time datetime
)charset ='utf8',engine =innodb;

1.explain之id分析（表的读取顺序）

案列1：（id相同）
查询成绩对应的学生，课程和老师

mysql> explain
    -> select *
    -> from score,student,course,teacher
    -> where score.student_id = student.id and score.course_id = course.id and course.teacher_id=teacher.id;
+----+-------------+---------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                           | rows | filtered | Extra       |
+----+-------------+---------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------------+
|  1 | SIMPLE      | score   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                          |    1 |   100.00 | Using where |
|  1 | SIMPLE      | course  | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | mysql_study.score.course_id   |    1 |   100.00 | Using where |
|  1 | SIMPLE      | teacher | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | mysql_study.course.teacher_id |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | student | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | mysql_study.score.student_id  |    1 |   100.00 | NULL        |
+----+-------------+---------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------------+

这个时候我们关注id列，id的大小都是一致的，id相同从上往下依次执行，也就是说会先查询score表的全部数据，再连接course表，然后teacher表，最后student表
​

​

案列2：（id不同）
查询名称为tony的老师，所教授的课程，的全部考试成绩

mysql> explain
    -> select *
    -> from score
    -> where course_id =(select id from course where teacher_id =  (select id from teacher where name ='tony'));
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | score   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | course  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
|  3 | SUBQUERY    | teacher | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

这个时候我们关注id列，**id值越大优先级越高，越被先执行，**所以是先查询对应的老师，再找到老师对应的课程，再通过课程获取全部成绩
​

案列3：（id有相同，有不同)
查询课程为mysql的，并且学生的年龄大于18的，学生考试成绩

mysql>  explain
    ->  select *
    ->  from score
    ->  where student_id in (select id  from  student where age>18)
    ->  and  course_id = (select id from course where name ='mysql' );
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                          | rows | filtered | Extra       |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-------------+
|  1 | PRIMARY     | score   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                         |    1 |   100.00 | Using where |
|  1 | PRIMARY     | student | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | mysql_study.score.student_id |    1 |    33.33 | Using where |
|  3 | SUBQUERY    | course  | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                         |    1 |   100.00 | Using where |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-------------+

这个时候我们关注id列，**id不同部分，id值越大优先级越高，越先执行！，id相同部分，可认为是一组，从上往下执行！，**所以是先查询对应的课程，再找到老师对应的成绩，最后查询对应的学生
​

​

​

2.explain之select_type（查询类型）

select_type简介:


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


1.SIMPLE —>简单的select查询,查询中不包含子查询或者UNION

mysql> explain
    -> select * from score where student_id=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | score | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

2.PRIMARY —>查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

mysql> explain
    -> select *
    -> from score
    -> where course_id =(select id from course where teacher_id =  (select id from teacher where name ='tony'));
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | score   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | course  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
|  3 | SUBQUERY    | teacher | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

3.SUBQUERY —>在SELECT或WHERE列表中包含了子查询
见PRIMARY的案列


4.DERIVED —>在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表里。


5.UNION —>若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERTVED


6.UNION RESULT —>从UNION表获取结果的SELECT

mysql> explain
    -> select *
    -> from score
    -> where student_id='1'
    -> union
    -> select *
    -> from score
    -> where student_id='2';
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | score      | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where     |
|  2 | UNION        | score      | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

3.explain之table简介（查询对应的表）

显示这一行的数据是关于哪张表的
​

4.6explain之type（访问类型）

简介：
访问类型排列，访问类型决定查询的过滤数据的多少


最详细的访问类型，最好到最差排列：
system > const > eq_ref > ref > fultext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL


常用的访问类型，最好到最差排列：
system>const>eq_ref>ref>range>index>ALL


一般来说至少到达range级别，最好到达ref级别


1.system
表只有一行记录（等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计

	mysql> explain
    -> select *
    -> from mysql.proxies_priv;
+----+-------------+--------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table        | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | proxies_priv | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+--------+---------------+------+---------+------+------+----------+-------+

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

mysql> explain
    -> select *
    -> from student
    -> where id=1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

​

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

mysql> explain
    -> select *
    -> from score
    -> left join  student
    -> on student.id=score.student_id;
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                          | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-------+
|  1 | SIMPLE      | score   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                         |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | student | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | mysql_study.score.student_id |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-------+

**4.ref**

**非唯一性索引扫描,返回匹配某个单独值的所有行**，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体 ```sql create index score_index on score (score);

mysql> explain
-> select *
-> from score where score=1;
±—±------------±------±-----------±-----±--------------±------------±--------±------±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±------------±--------±------±-----±---------±------+
| 1 | SIMPLE | score | NULL | ref | score_index | score_index | 9 | const | 1 | 100.00 | NULL |
±—±------------±------±-----------±-----±--------------±------------±--------±------±-----±---------±------+

<br />
<br />**5.rangee**<br />检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
```sql
mysql> explain
    -> select *
    -> from  score
    -> where score between 60 and 90;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | score | NULL       | range | score_index   | score_index | 9       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+

**6.index**

Full Index Scan，**index与ALL区别为index类型只遍历索引树**。这通常比ALL快，因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表，**但index是从索引中读取的**，而all是从硬盘中读的)，也可以理解为使用了覆盖索引 ```sql mysql> explain -> select id,score -> from score; +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | score | NULL | index | NULL | score_index | 9 | NULL | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ ```

**7.all**
Full Table Scan，**将遍历全表以找到匹配的行** ```sql mysql> explain -> select * -> from score; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | score | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ ```

**注：index和all的区别就是在于查询字段是否建立索引！**
**​**

​

​

5.explain之possible_key和key（可能使用索引，实际索引使用）

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


2.key
实际使用的索引。如果为NULL，则没有使用索引
查询中若使用了覆盖索引，此表建立的索引和查询的select字段重叠那么使用覆盖索引

mysql> explain
    -> select id,score
    -> from  score;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | score | NULL       | index | NULL          | score_index | 9       | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+

可能用到的索引为null，实际使用的索引score_index
​

​

6.explain之key_len(索引中使用的字节数)

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的了
**案列见上 **
​

​

7.ref（哪列的索引被使用了）

mysql> explain
    -> select *
    -> from score
    -> left join  student
    -> on student.id=score.student_id;
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                          | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-------+
|  1 | SIMPLE      | score   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                         |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | student | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | mysql_study.score.student_id |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-------+

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

​

8.row(大致估算出找到所需的记录所需要读取的行数)

全表一共4条数据

#没有索引时，全表扫描
mysql> explain
    -> select *
    -> from student
    -> where name ='波及';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

#有主键索引，一次就能直接匹配
mysql> explain
    -> select *
    -> from student
    -> where id=2;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

​

​

9.extra(包含不适合在其他列中显示但十分重要的额外信息)

1.Using file sort（文件排序）
说明mysql会对数据使用一个进行排序​
在排序字段上创建索引可以避免Using file sort(因为mysql的索引是排好序的数据结构，所以在排序时会用到我们的索引)

mysql> explain
    -> select *
    -> from student
    -> order by age;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+

​

2.Using temporary（临时表）
使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
也可以在分组的字段上创建索引来避免Using temporary

mysql> explain
    -> select *
    -> from course
    -> group by teacher_id;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | course | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using temporary; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+

**3.USING index（覆盖索引，nice）**

表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错!

如果同时出现using where，表明索引被用来执行索引键值的查找;

白话就是，你创建了哪个索引列就用到了哪些索引列 ```sql mysql> explain -> select id,score -> from score; +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | score | NULL | index | NULL | score_index | 9 | NULL | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ ``` 覆盖索引的详情见，索引篇
​

4.using where（where过滤）
表明使用了where过滤

mysql> explain
    -> select *
    -> from student
    -> where name ='波及';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

5.using join buffer(连接缓存)
使用了连接缓存，一般在连表的时候出现


6.impossible where(where条件冲突)
where子句的值总是false，不能用来获取任何元组，条件冲突

mysql> explain
    -> select *
    -> from score
    -> where id =1 and  id !=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

7.select tables optimized away
在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者
对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

mysql> explain
    -> select max(score)
    -> from score;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

二.性能优化分析之showprofile

官方文档地址：
https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

1.show profile是什么？

指示在当前会话过程中，执行的语句的资源使用情况
​

通俗易懂的来讲就是：
当我们使用数据库连接工具与数据库进行交互式，每个交互页面，都是基于数据库连接的会话，
使用showprofile可以特别详细的看到SQL在整个执行的过程中的所产生的耗时，也就是执行的语句的资源使用情况

2.show profile能干什么？

结合expalin，已帮助我们进行优化的决策！
​

下面来看一段使用showprofile，分析SQL语句的资源使用情况：
这里我们看到一条SQL在执行期间所有步骤以及对应的执行时间，我们SQL执行慢，是慢在哪一步，CPU资源不够，还是IO读写慢等等

+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| Status               | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function       | Source_file          | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| starting             | 0.000058 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | NULL                  | NULL                 |        NULL |
| checking permissions | 0.000006 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | check_access          | sql_authorization.cc |         809 |
| Opening tables       | 0.000019 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | open_tables           | sql_base.cc          |        5815 |
| init                 | 0.000012 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | handle_query          | sql_select.cc        |         128 |
| System lock          | 0.000006 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_lock_tables     | lock.cc              |         330 |
| optimizing           | 0.000003 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | JOIN::optimize        | sql_optimizer.cc     |         158 |
| statistics           | 0.000009 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | JOIN::optimize        | sql_optimizer.cc     |         374 |
| preparing            | 0.000120 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | JOIN::optimize        | sql_optimizer.cc     |         482 |
| executing            | 0.000002 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | JOIN::exec            | sql_executor.cc      |         126 |
| Sending data         | 0.006010 | 0.015625 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | JOIN::exec            | sql_executor.cc      |         202 |
| end                  | 0.000006 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | handle_query          | sql_select.cc        |         206 |
| query end            | 0.000007 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_execute_command | sql_parse.cc         |        4959 |
| closing tables       | 0.000005 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_execute_command | sql_parse.cc         |        5018 |
| freeing items        | 0.000065 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_parse           | sql_parse.cc         |        5637 |
| cleaning up          | 0.000007 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | dispatch_command      | sql_parse.cc         |        1933 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+

3.show profile怎么使用？

#1.查询此功能是否开启  （0未开启，1开启）
mysql> SELECT @@profiling; 
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+

#2.关闭 showprofile功能 
mysql> SET profiling = 0;

#3.开启 showprofile  
mysql> SET profiling = 1;

#4.使用showprofile 查看最近执行的sql  默认15条 最大100条  
mysql>SHOW PROFILES;
+----------+------------+----------------------------+
| Query_ID | Duration   | Query                      |
+----------+------------+----------------------------+
|        1 | 0.00015050 | SELECT @@profiling         |
|        2 | 0.00069475 | select * from a limit 1000 |
|        3 | 0.00633200 | select * from a            |
+----------+------------+----------------------------+


#5.查询 SHOW PROFILES 命令可以看SQL执行的条数据 默认15条 最大100条  
mysql> show variables like 'profiling_history_size';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling_history_size | 15    |
+------------------------+-------+

#6.设置 SHOW PROFILES 命令可以看SQL执行的条数据
mysql> set profiling_history_size =30;
Query OK, 0 rows affected, 1 warning (0.00 sec)

#7. 使用SHOW PROFILES 针对某一个查询，根据Query_ID查看具体的执行sql的耗时 
#对于列的解释，会在下面的笔记有对照表，提供参考 
SHOW PROFILE ALL FOR QUERY 3;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| Status               | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function       | Source_file          | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| starting             | 0.000058 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | NULL                  | NULL                 |        NULL |
| checking permissions | 0.000006 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | check_access          | sql_authorization.cc |         809 |
| Opening tables       | 0.000019 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | open_tables           | sql_base.cc          |        5815 |
| init                 | 0.000012 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | handle_query          | sql_select.cc        |         128 |
| System lock          | 0.000006 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_lock_tables     | lock.cc              |         330 |
| optimizing           | 0.000003 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | JOIN::optimize        | sql_optimizer.cc     |         158 |
| statistics           | 0.000009 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | JOIN::optimize        | sql_optimizer.cc     |         374 |
| preparing            | 0.000120 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | JOIN::optimize        | sql_optimizer.cc     |         482 |
| executing            | 0.000002 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | JOIN::exec            | sql_executor.cc      |         126 |
| Sending data         | 0.006010 | 0.015625 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | JOIN::exec            | sql_executor.cc      |         202 |
| end                  | 0.000006 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | handle_query          | sql_select.cc        |         206 |
| query end            | 0.000007 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_execute_command | sql_parse.cc         |        4959 |
| closing tables       | 0.000005 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_execute_command | sql_parse.cc         |        5018 |
| freeing items        | 0.000065 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_parse           | sql_parse.cc         |        5637 |
| cleaning up          | 0.000007 | 0.000000 |   0.000000 |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | dispatch_command      | sql_parse.cc         |        1933 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+

#8.在使用SHOW PROFILES时 会显示很多列 帮助我们去分析判断，但是我们可能只需要部分数据 只需要我们关注的列即可  也可以进行筛选
#更多过滤参数 见下面笔记 对照表  
#案列一：显示用户和系统 CPU 使用时间
mysql> SHOW PROFILE CPU  FOR QUERY 3;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000058 | 0.000000 |   0.000000 |
| checking permissions | 0.000006 | 0.000000 |   0.000000 |
| Opening tables       | 0.000019 | 0.000000 |   0.000000 |
| init                 | 0.000012 | 0.000000 |   0.000000 |
| System lock          | 0.000006 | 0.000000 |   0.000000 |
| optimizing           | 0.000003 | 0.000000 |   0.000000 |
| statistics           | 0.000009 | 0.000000 |   0.000000 |
| preparing            | 0.000120 | 0.000000 |   0.000000 |
| executing            | 0.000002 | 0.000000 |   0.000000 |
| Sending data         | 0.006010 | 0.015625 |   0.000000 |
| end                  | 0.000006 | 0.000000 |   0.000000 |
| query end            | 0.000007 | 0.000000 |   0.000000 |
| closing tables       | 0.000005 | 0.000000 |   0.000000 |
| freeing items        | 0.000065 | 0.000000 |   0.000000 |
| cleaning up          | 0.000007 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+

#案列二：IO 显示io输入和输出操作的计数
mysql> SHOW PROFILE BLOCK IO  FOR QUERY 3;
+----------------------+----------+--------------+---------------+
| Status               | Duration | Block_ops_in | Block_ops_out |
+----------------------+----------+--------------+---------------+
| starting             | 0.000058 |         NULL |          NULL |
| checking permissions | 0.000006 |         NULL |          NULL |
| Opening tables       | 0.000019 |         NULL |          NULL |
| init                 | 0.000012 |         NULL |          NULL |
| System lock          | 0.000006 |         NULL |          NULL |
| optimizing           | 0.000003 |         NULL |          NULL |
| statistics           | 0.000009 |         NULL |          NULL |
| preparing            | 0.000120 |         NULL |          NULL |
| executing            | 0.000002 |         NULL |          NULL |
| Sending data         | 0.006010 |         NULL |          NULL |
| end                  | 0.000006 |         NULL |          NULL |
| query end            | 0.000007 |         NULL |          NULL |
| closing tables       | 0.000005 |         NULL |          NULL |
| freeing items        | 0.000065 |         NULL |          NULL |
| cleaning up          | 0.000007 |         NULL |          NULL |
+----------------------+----------+--------------+---------------+

​

4.show profile对照表

命令过滤参数对照表

参数编码	解释
ALL	显示所有信息
BLOCK IO	显示IO输入和输出操作的计数
CPU	显示用户和系统 CPU 使用时间
IPC	显示发送和接收消息的计数
PAGE FAULTS	显示主要和次要页面错误的计数
SOURCE	显示源代码中的函数名称，以及函数所在文件的名称和行号
SWAPS	显示交换计数

​

SHOW PROFILE返回列的释义

​

列名	释义
QUERY_ID	数字语句标识符
STATE	分析状态
DURATION	语句执行在给定状态保持多长时间，以秒为单位
CPU_USER ，CPU_SYSTEM	用户和系统 CPU 使用，以秒为单位
CONTEXT_VOLUNTARY, CONTEXT_INVOLUNTARY	发生了多少自愿和非自愿的上下文切换
BLOCK_OPS_IN, BLOCK_OPS_OUT	IO输入和输出操作的数量。
MESSAGES_SENT, MESSAGES_RECEIVED	发送和接收的通信消息数
PAGE_FAULTS_MAJOR, PAGE_FAULTS_MINOR	主要和次要页面错误的数量。
SWAPS	发生了多少次交换
SOURCE_FUNCTION, SOURCE_FILE, 和 SOURCE_LINE	指示分析状态在源代码中执行位置的信息

5.未来展望

SHOW PROFILE** 和 **SHOW PROFILES**语句已弃用 ；期望它们在未来的 MySQL 版本中被删除。改用 性能模式 ；请参阅 **第 25.19.1 节，“使用性能模式进行查询分析”。
但是目前还是可以正常使用的
​

三.使用慢查询

官方文档：
https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
​

1.什么是慢查询？

**慢查询日志由执行时间超过 **long_query_time几秒 SQL 语句。慢查询日志可用于查找需要很长时间执行的查询，因此是优化的候选者。
获取初始锁的时间不计入执行时间。mysqld在执行完所有锁之后，会在慢查询日志中写入一条语句，因此日志顺序可能与执行顺序不同。

2.判断或开启慢查询日志，设置慢查询日志的存储路径

#查询慢sql日志是否开启
mysql> show VARIABLES like '%slow_query_log%';
+---------------------+-----------------------+
| Variable_name       | Value                 |
+---------------------+-----------------------+
| slow_query_log      | ON                    |
| slow_query_log_file | LT5CG13149V2-slow.log |
+---------------------+-----------------------+

#开启慢sql日志  只对当前数据库生效，重启后失效,如果有需要 需修改my.cnf
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

#设置慢查询日志的存储路径
set global slow_query_log_file='C:\Program Files\MySQL\MySQL Server 5.7';
show global VARIABLES like '%slow_query_log_file%';

​

3.模拟慢查询，并记录到慢查询日志

mysql慢sql日志 默认是关闭的 需要手动开启

#mysql判断为慢sql的执行时间  默认10s  （注：是大于10s的） （会话的）
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

#mysql判断为慢sql的执行时间  默认10s  （注：是大于10s的） （系统的）
mysql> show global variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

#设置慢查询的记录的阀值,也就是SQL执行多少s，算慢查询 （这里设置为3s）
mysql> set  global  long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

#模拟慢查询
mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+
1 row in set (5.02 sec)

#查询查询SQL的条数
show global status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 4     |
+---------------+-------+

​

4.慢查询日志的内容分析

• Query_time
  • _duration_语句执行时间，以秒为单位。
• Lock_time
  • _duration_获取锁的时间（以秒为单位）。
• Rows_sent
  • _N_发送到客户端的行数。
• Rows_examined
  • 服务器层检查的行数（不包括存储引擎内部的任何处理）

​