我是靠谱客的博主 风趣招牌，最近开发中收集的这篇文章主要介绍MySQL高性能实战——part3——分析SQL,定位慢SQL(性能优化的前提)MySQL高性能实战——part3——分析SQL,定位慢SQL(性能优化的前提)，觉得挺不错的，现在分享给大家，希望可以做个参考。
概述
前言：
此文借鉴《MySQL高性能》一书，还有MySQL官方文档，笔者将通过自身的一些实战经验和阅读习惯对本书进行一个总结，整理，归纳出企业级开发中常用的优化案列和部分概念！！
官方文档地址：
https://dev.mysql.com/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
官方文档地址：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能干嘛？
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
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 FilesMySQLMySQL 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
- 服务器层检查的行数（不包括存储引擎内部的任何处理）
最后
以上就是风趣招牌为你收集整理的MySQL高性能实战——part3——分析SQL,定位慢SQL(性能优化的前提)MySQL高性能实战——part3——分析SQL,定位慢SQL(性能优化的前提)的全部内容，希望文章能够帮你解决MySQL高性能实战——part3——分析SQL,定位慢SQL(性能优化的前提)MySQL高性能实战——part3——分析SQL,定位慢SQL(性能优化的前提)所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
![[MySQL优化] -- 如何定位效率较低的SQL](/uploads/reation/bcimg1.png)
发表评论 取消回复