前言：

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

​

官方文档地址：

/doc/refman/5.7/en/

​

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

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

​

​

​

版本说明：

不同数据库版本可能对SQL的执行以及部分特性产生巨大的影响，所以在这里明确一下版本

参考书籍版本：5.1或5.5

实战案列实际使用版本：5.7

​

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

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

一.性能优化分析之explain

官方文档地址：/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能干嘛？

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

​

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等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。```sqlmysql> 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

官方文档地址：

/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| | 809 || Opening tables | 0.000019 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | open_tables | | 5815 || init | 0.000012 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | handle_query| | 128 || System lock| 0.000006 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | mysql_lock_tables|| 330 || optimizing | 0.000003 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | JOIN::optimize || 158 || statistics | 0.000009 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | JOIN::optimize || 374 || preparing | 0.000120 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | JOIN::optimize || 482 || executing | 0.000002 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | JOIN::exec | | 126 || Sending data | 0.006010 | 0.015625 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | JOIN::exec | | 202 || end | 0.000006 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | handle_query| | 206 || query end | 0.000007 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | mysql_execute_command || 4959 || closing tables | 0.000005 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | mysql_execute_command || 5018 || freeing items | 0.000065 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | mysql_parse || 5637 || cleaning up| 0.000007 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | dispatch_command|| 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| | 809 || Opening tables | 0.000019 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | open_tables | | 5815 || init | 0.000012 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | handle_query| | 128 || System lock| 0.000006 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | mysql_lock_tables|| 330 || optimizing | 0.000003 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | JOIN::optimize || 158 || statistics | 0.000009 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | JOIN::optimize || 374 || preparing | 0.000120 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | JOIN::optimize || 482 || executing | 0.000002 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | JOIN::exec | | 126 || Sending data | 0.006010 | 0.015625 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | JOIN::exec | | 202 || end | 0.000006 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | handle_query| | 206 || query end | 0.000007 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | mysql_execute_command || 4959 || closing tables | 0.000005 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | mysql_execute_command || 5018 || freeing items | 0.000065 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | mysql_parse || 5637 || cleaning up| 0.000007 | 0.000000 | 0.000000 | NULL |NULL | NULL |NULL |NULL | NULL | NULL | NULL | NULL | dispatch_command|| 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对照表

命令过滤参数对照表

​

SHOW PROFILE返回列的释义

​

5.未来展望

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

但是目前还是可以正常使用的

​

三.使用慢查询

官方文档：

/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日志 只对当前数据库生效，重启后失效,如果有需要 需修改fmysql> 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服务器层检查的行数（不包括存储引擎内部的任何处理）

​