MySQL性能优化
一、Explain分析执行计划
1.explain是什么？
使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈。
2.explain有什么用?
从explain执行计划中可以获取以下信息
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
3.怎么用explain?
Explain+SQL语句
mysql> explain select now();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
4.explain各个字段的解释
1）id
id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
id 情况有三种 ：
id 相同表示加载表的顺序是从上到下。
explain select * from t_user join t_role on t_user.name=t_role.role_name;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
| 1 | SIMPLE | t_role | NULL | eq_ref | unique_role_name | unique_role_name | 768 | test.t_user.name | 1 | 100.00 | Using index condition |
(2)id 不同id值越大，优先级越高，越先被执行。
explain select * from t_user where id= (select id from t_user where name='学生1');
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | t_user | NULL | const | PRIMARY | PRIMARY | 98 | const | 1 | 100.00 | NULL |
| 2 | SUBQUERY | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
(3)id有相同，也有不同，同时存在。
explain select * from (select * from t_user union select * from t_role) a
left join user_role ur on a.id=ur.id
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | NULL |
| 1 | PRIMARY | ur | NULL | eq_ref | PRIMARY | PRIMARY | 4 | a.id | 1 | 100.00 | Using where |
| 2 | DERIVED | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
| 3 | UNION | t_role | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。
2）select_type
表示 SELECT 的类型，常见的取值，如下表所示：从上往下效率越来越低
| select_type | 含义 |
|---|---|
| SIMPLE | 简单的select查询，查询中不包含子查询或者UNION |
| PRIMARY | 查询中若包含任何复杂的子查询，最外层查询标记为该标识 |
| SUBQUERY | 在SELECT 或 WHERE 列表中包含了子查询 |
| DERIVED | 在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中 |
| UNION | 若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED |
| UNION RESULT | 从UNION表获取结果的SELECT |
| (1)simple |
explain select * from t_user;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
(2)primary,subquery
explain select * from t_user where name=(select role_name from t_role where id=1);
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
| 2 | SUBQUERY | t_role | NULL | index | PRIMARY | unique_role_name | 768 | NULL | 5 | 20.00 | Using where; Using index |
(3)derived,union,union result
explain select * from (select * from t_user union select * from t_role) a
left join user_role ur on a.id=ur.id
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | NULL |
| 1 | PRIMARY | ur | NULL | eq_ref | PRIMARY | PRIMARY | 4 | a.id | 1 | 100.00 | Using where |
| 2 | DERIVED | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
| 3 | UNION | t_role | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
需要注意几点:
- MySQL5.7以后版本中对from列表中包含子查询做了优化，新增了derived_merge(派生合并)，可以加快查询效率。所以有时候我们即使from表后有子查询也不会显示derived，原因就是优化合并了。
- 第一条记录的table是
derived2表示的是id为2查询类型为derived的表。 - 因为此例中的union是包含在from表中，所以外层select也就是t_user表被标记为derived。如果是正常的合并语句，外层select应为primary。例如：
explain select * from t_user union select * from t_roleid select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t_user NULL ALL NULL NULL NULL NULL 6 100.00 NULL 2 UNION t_role NULL ALL NULL NULL NULL NULL 5 100.00 NULL NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
3）table
显示这一行的数据是关于哪张表的
4）partitions
版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。
5）type
type 显示的是访问类型，是较为重要的一个指标，可取值为：
| type | 含义 |
|---|---|
| NULL | MySQL不访问任何表,索引，直接返回结果 |
| system | 表只有一行记录(等于系统表),这是const类型的特例，一般不会出现 |
| const | 表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。例如:将主键置于where列表中，MySQL就能将该查询转换为一个常量。const会将"主键"或"唯一"索引的所有部分与常量值进行比较。 |
| eq_ref | 类似ref,区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描 |
| ref | 非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,返回所有匹配某个单独值的所有行(多个) |
| range | 只检索给定返回的行，使用一个索引来选择行。where之后出现between,<,>,in等操作。 |
| index | index与all的区别为index类型只是遍历了索引树，通常比all快,all是遍历数据文件 |
| ALL | 将遍历全表以找到匹配的行 |
结果从最好到最坏依次为:system>const>eq_ref>ref>range>index>all
(1)NULL
explain select now();
| 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 | No tables used |
(2)const：表示通过索引一次就查找到了数据，一般const出现在「唯一索引或者主键索引中使用等值查询」，因为表中只有一条数据匹配，所以查找的速度很快。
explain select * from t_user where id='1'
#发现不加引号仍然能检索出结果但是type会变为all,原因就是id是varchar属性,必须使用引号匹配
#explain select * from t_user where id=1
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t_user | NULL | const | PRIMARY | PRIMARY | 98 | const | 1 | 100.00 | NULL |
(3)eq_ref：表示使用唯一索引或者主键索引扫描作为表链接匹配条件，对于每一个索引键，表中都只有一条记录与之匹配
explain select * from emp left join dept on emp.deptno=dept.deptno;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 100.00 | NULL |
| 1 | SIMPLE | dept | NULL | eq_ref | PRIMARY | PRIMARY | 4 | mytestdb.emp.DEPTNO | 1 | 100.00 | NULL |
(4)ref：ref与eq_ref的区别就是：eq_ref使用的是唯一索引或者主键索引。ref扫描后的结果可能会找到多条符合条件的行数据，例如本例中一个deptno对应emp表中一个部门的人也就是多条记录。
explain select * from dept left join emp on emp.deptno=dept.deptno;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | dept | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 1 | SIMPLE | emp | NULL | ref | FK_DEPTNO | FK_DEPTNO | 5 | mytestdb.dept.DEPTNO | 3 | 100.00 | NULL |
ref本质上是一种索引访问，返回匹配的行。
以下例子看一下使用不同的索引出现的type是什么情况。
mysql> explain select * from emp where empno=7369; #主键索引--const
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp where ename='SMITH'; #唯一索引--const
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | const | i_ename | i_ename | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp where mgr=7788; #普通索引--ref
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | idx_mgr | idx_mgr | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp where deptno=20; #外键的索引--ref
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | FK_DEPTNO | FK_DEPTNO | 5 | const | 5 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp where job='clerk' and sal=1100; #联合索引job_sal--ref
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | job_sal | job_sal | 48 | const,const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp where comm=500; #不使用索引--ALL
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
(5)range：使用索引来检索给定范围的行数据，一般是在where后面使用between、<>、in等查询语句就会出现range。
explain select * from emp where empno>7369
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 13 | 100.00 | Using where |
(6)index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询也就是索引覆盖、可以使用索引排序或者分组的查询。
explain select empno,job,sal from emp order by job asc,sal asc --使用联合索引,索引覆盖不需要回表
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | index | NULL | job_sal | 48 | NULL | 14 | 100.00 | Using index |
我们把排序中的job跟sal调换下位置，就不符合最左匹配了。type还是index，因为我们需要的数据在这个联合索引树上都有。但是Extra列中多出了using filesort,意思就是排序不会使用表内的索引排序了，而是使用外部的索引排序。称为文件排序，效率低。
explain select empno,job,sal from emp order by sal asc,job asc
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | index | NULL | job_sal | 48 | NULL | 14 | 100.00 | Using index; Using filesort |
容易理解错误的地方：type为index不代表肯定就是覆盖索引，只有是extra中出现了Using index才是覆盖索引。比如下边的用索引分组的情况也会出现index。index指的索引全表扫描。
explain select * from emp group by deptno;
(7)ALL：ALL与index的区别就是ALL是从硬盘中读取，而index是从索引文件中读取。ALL全表扫描意味着Mysql会从表的头到尾进行扫描，这时候表示通常需要增加索引来进行优化了，或者说是查询中并没有使用索引作为条件进行查询。
explain select * from emp;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 100.00 | NULL |
6）possible_keys
显示可能应用在这张表中的索引,一个或多个。查询涉及的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
7）key
实际使用的索引，如果为null则没有使用索引。
查询中若使用了覆盖索引，而且没有where条件时，可能possible_keys为null,索引只显示在key中
explain select empno,job,sal from emp;
8）key_len
用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。
计算key_len的方法
| 数据类型 | 所占字节数 |
|---|---|
| 字符串char(n) | n字节长度 |
| 字符串varchar(n) | 2字节存储字符串长度，如果是utf-8，则长度 3n + 2；如果是utf8mb4，则长度为4n+2。 如果字段可以为null，存储null也需要1个字节长度，所以还需要+1。 |
| 数值类型 tinyint | 1字节 |
| smallint | 2字节 |
| int | 4字节 |
| bigint | 8字节 |
| double | 8字节 |
| 时间类型 date | 3字节 |
| timestamp | 4字节 |
| datetime | 8字节 |
然后我们就可以知道job_sal为啥等于48了。
首先job是varchar(9) utf8mb4编码 可以为null =9*4+2+1=39
然后sal是double类型,也可以为null =8+1=9
然后相加一起就是48
9）ref
ref其实指的就是用什么值来检索索引。
(1)如果是使用的常数等值查询，这里会显示const
explain select * from emp where ename='smith';
(2)如果是连接查询，被驱动表(dept)的执行计划这里会显示驱动表(emp)的关联字段
explain select * from emp left join dept on emp.deptno=dept.deptno
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 100.00 | NULL |
| 1 | SIMPLE | dept | NULL | eq_ref | PRIMARY | PRIMARY | 4 | mytestdb.emp.DEPTNO | 1 | 100.00 | NULL |
10）rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
11）filtered
MySQL5.7以后该项成为了默认选项,Filtered表示返回结果的行数占需读取行数的百分比。
12）extra
该字段显示的是sql查询的额外信息，主要有以下几种情况：
Using index、Using where、Using filesort、Using temporary、Using join buffer、Impossible where、Select tables optimized away。
其中如果extra出现Using filesort和Using temporary，是我们优化的重点。Using index是我们尽量保持的情况。
(1)Using index
表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！
Ⅰ.如果同时出现using where，表明索引被用来执行索引键值的查找；
--因为二级索引中叶子结点存储的有主键值,所以查询主键依然会索引覆盖
explain select empno,deptno,job from emp where job like 'cler%';
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | range | job_sal,idx_job_dept | idx_job_dept | 39 | NULL | 4 | 100.00 | Using where; Using index |
Ⅱ.如果没有同时出现using where，表面索引用来读取数据而非执行查找动作
explain select empno,deptno,job from emp;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | index | NULL | idx_job_dept | 44 | NULL | 14 | 100.00 | Using index |
(2)Using index condition
查找使用了索引，但是需要回表查询数据(表示虽然用到了索引但是没有用索引覆盖的情况)
(3)Using filesort
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作成为“文件排序”
Ⅰ.没有使用索引排序的情况,extra中会出现using filesort
explain select * from emp where job='clerk' order by hiredate
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | ref | job_sal,idx_job_dept | job_sal | 39 | const | 4 | 100.00 | Using filesort |
上述情况我们只需要建立一个job_hiredate的联合索引就可以使用索引排序了
Ⅱ.使用索引排序的情况,extra中就没有using filesort
explain select * from emp where job='clerk' order by hiredate
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | ref | idx_job_dept,idx_job_hiredate | idx_job_hiredate | 39 | const | 4 | 100.00 | NULL |
(4)Using temporary
用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低。
Ⅰ.使用临时表的情况。因为job是范围查询，虽然我们建立了联合索引idx_job_dept。但是必须对job等值查询的时候，也就是说job相同的情况下deptno才是严格有序的。所以以下sql语句不会用到索引排序。所以出现了using temporary。
explain select * from emp where job in ('clerk','manager') group by deptno
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | range | idx_job_dept,idx_job_hiredate | idx_job_dept | 39 | NULL | 7 | 100.00 | Using index condition; Using temporary |
Ⅱ.那我们排序的时候先按照job排,再按照deptno排。这样就能保证job相等，deptno也是严格有序。符合最左匹配原则就能用到索引排序。就不会使用临时表了，extra也就不会出现using temporary。
explain select * from emp where job in ('clerk','manager') group by job,deptno;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | range | idx_job_dept,idx_job_hiredate | idx_job_dept | 39 | NULL | 7 | 100.00 | Using index condition |
(5)Using where
表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition。
ICP特性具体可以参考:https://blog.csdn.net/lemon618/article/details/77649537
explain select * from emp where job like 'cler' and deptno=20
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | range | idx_job_dept,idx_job_hiredate | idx_job_dept | 44 | NULL | 2 | 100.00 | Using index condition |
以上例子就是，如果没有使用索引下推优化，会先根据job找出4条符合条件的主键值，然后回表4次得到4条记录，然后再根据deptno条件在server层中过滤出，返回符合条件的2个结果。
如果有了索引下推优化之后，直接会根据job,deptno整体从存储引擎中做数据检索，这样只会拿到2个id值，就只需要回2次表。
(6)using join buffer
Using join buffer表示使用连接缓存。
它强调在获取连接条件时，并没有使用索引，而是使用连接缓冲区来存储中间结果，若是出现该值，一般说明需要添加索引来进行优化了。
(7)impossible where
会出现在where后的条件一直为false的情况下，这种可以忽视，比较少出现
explain select * from emp where 1=0
(8)select tables optimized away
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
explain select min(job) from emp
(9)distinct
优化distinct，在找到第一匹配的元组后即停止找同样值的工作
二、索引优化案例
(1)索引单表优化案例
找出job是clerk,薪水高于1100的奖金最多的用户信息
explain select * from emp where job='clerk' and sal>1100 order by comm desc limit 1
首先我们尝试去建立job,sal,comm的索引。虽然使用了索引 type是range,但是extra出现了Using filesort,相当于使用了外部的索引排序，效率低。
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | range | idx_job_sal_comm | idx_job_sal_comm | 48 | NULL | 2 | 100.00 | Using index condition; Using filesort |
发生以上情况原因是因为sal>1100这种范围检索会导致联合索引中后面的comm索引失效。所以我们删除之前的索引，只建立job,comm的索引。这样job等值查询的情况下，comm就是严格有序的了。
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | ref | idx_job_comm | idx_job_comm | 39 | const | 4 | 33.33 | Using where; Backward index scan |
经过索引上的优化，我们发生type从range变成ref了,而且也使用了表内的索引排序Using filesort也消失了。
(2)索引两表优化案例
explain select * from class left join book on class.card=book.card
首先两张表都不加索引的情况。发现都是全表扫描
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (hash join) |
我们假设先给class表中的card加索引。发现左表的type变成index，就是遍历了整个索引树，没有走数据文件，这个例子只是凑巧，因为就两个字段一个主键一个索引列。所以覆盖索引了。大多数情况下这里应该还是ALL。
alter table class add index idx_class_card(card);
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | class | NULL | index | NULL | idx_class_card | 4 | NULL | 20 | 100.00 | Using index |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (hash join) |
我们删除之前的索引,给book表中的card加索引。结果是驱动表type变成ref，rows也减少了很多，优化比较明显
alter table class add index idx_class_card(card);
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | idx_book_card | idx_book_card | 4 | optimize.class.card | 1 | 100.00 | Using index |
总结:
以上原因是左连接的特性决定的。left join条件用于确定如何从右表搜索行,左边一定都有，所以右边是我们的关键点，所以一定要给右边的表加索引。
如果是右连接的话刚好相反，right join条件用于确定如何从左表搜索行，右边一定都有,所以左边表一定要加索引。
#右连接,我们给左边的表加索引
alter table class add index idx_class_card(card);
explain select * from class right join book on class.card=book.card
如果是内连接inner join的话，我们给哪个表建立索引都行，SQL语句会自动优化，让有索引的表成为驱动表。
explain select * from class inner join book on class.card=book.card
如果工作当中，我们索引都是DBA创建的，我们要根据创建好的索引，找到最佳的表的关联顺序。而不是从我们的SQL语句去创建合适的索引。
(3)索引三表优化案例
explain select * from class left join book on class.card=book.card
left join phone on book.card=phone.card
如果我们都不添加索引的情况
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | phone | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (hash join) |
我们给phone表和book表建立索引就能让后两行的type都是ref,而且总的rows也不错。
alter table book add index idx_book_card(card);
alter table phone add index idx_phone_card(card);
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | idx_book_card | idx_book_card | 4 | optimize.class.card | 1 | 100.00 | Using index |
| 1 | SIMPLE | phone | NULL | ref | idx_phone_card | idx_phone_card | 4 | optimize.book.card | 1 | 100.00 | Using index |
三、索引的设计原则和失效情况
索引的设计原则
适合建立索引的情况：
- 主键自动建立唯一索引
- 频繁作为查询的条件的字段应该创建索引
- 查询中与其他表关联的字段，外键关系建立索引
- 尽量的去扩展索引，少创建单独的索引。
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
- 查询中统计或者分组字段适合建立索引
不适合建立索引的情况:
- 频繁更新的字段不适合创建索引
- 不作为查询条件的字段不适合创建索引
- 数据重复多且分布平均的表字段不适合布创建索引，因为建立索引没有实际太大效果
- 表记录太少不适合创建索引
- 经常增删改的表不适合创建索引
索引失效情况
早起版本可能失效的情况更多，本次测试不做声明都是8.0版本情况下测试的
(1)like以通配符开头会导致索引失效全表扫描
--开头有通配符,索引失效
explain select * from emp where ename like "%s%"
解决办法就是使用覆盖索引
select empno,ename from emp where ename like '%s%';
(2)隐式类型转换，索引字段与条件或关联字段的类型不一致
例如我们一个表的主键id是varchar类型，而你查找的时候没有加单引号，就会导致索引失效全表扫描
-- 无法使用索引
EXPLAIN select * from test_idx where id = 1;
-- 可以使用索引
EXPLAIN select * from test_idx where id = '1';
(3)条件中对索引列进行运算或使用函数
这里指的是对索引的列进行运算或者使用函数，而不是对查询条件的值使用了函数或者公式。
-- 无法使用索引
explain select * from emp where substr(empno,1,4)=7369
explain select * from emp where empno-1=7368;
-- 可以使用索引
explain select * from emp where empno = 7370-1;
explain select * from emp where empno = concat(73,69)
(4)使用or可能会引起索引失效
用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
explain select * from emp where empno=7369 or comm=888
(5)违反最左前缀法则,会导致右边字段的索引失效
假设有联合索引index(a,b,c)
| Where语句 | 索引是否被使用 |
|---|---|
| 1.where a=3 | 使用了a |
| 2.where a=3 and b=5 | 使用了a,b |
| 3.where a=3 and b=5 and c=4 | 使用了a,b,c |
| 4.where b=3或者where b=3 and c=4或者where c=4 | 索引失效 |
| 5.where a=3 and c=5 | 使用了a,但是c不可以,因为a右边是b |
| 6.where a=3 and b>4 and c=5 | 使用了a,b 由于c在b的范围之后失效 |
| 7.whera a=3 and b like ‘kk%’ and c=4 | a,b,c都使用到 |
| 8.where a=3 and b like ‘%kk%’ and c=4 | 只用到a |
| 9.where a=3 and b like ‘%kk’ and c=4 | 只用到a |
| 10.where a=3 and b like ‘k%kk%’ and c=4 | 用到了a,b,c |
我们对7和10补充说明。
b like ‘kk%’不应该是范围查询吗？那么c索引应该失效，为什么c索引会用到？
我们先测试下结果
alter table index idx_job_ename_sal(job,ename,sal)
explain select * from emp where job='clerk' and ename like 's%' and sal>1000
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | range | idx_ename,idx_job_ename_sale | idx_job_ename_sale | 90 | NULL | 1 | 33.33 | Using index condition |
发现索引的字节长度是90，经计算length=94+2+104+3+8+1=90。确实使用了sal的索引
具体解释
(6)is NULL,is NOT NULL 有时索引失效
explain select * from emp where job is not null;
explain select * from emp where job is null;
MySQL底层会自动判断，如果全表扫描快，则直接使用全表扫描，不走索引。条件返回值占总数据的比例比较小，就有可能使用索引。
(7)NOT IN索引失效
explain select * from emp where job not in ('clerk','SALESMAN','MANAGER')
四、查询优化补充(exists in,order by,group by)
exists in
EXISTS语句可以理解为将主查询的数据放到子查询中做条件验证，根据验证结果来决定主查询的数据结果是否得以保留
--EXISTS语法
select ... from table where exists(subquery)
EXISTS需要注意的几点：
EXISTS语句只返回true或者false,因此子查询中的select …可以是任意的，可以是select *也可以是select 1还可以是select 3,官方说法是实际执行会忽然select清单，因此没有区别。EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优具体问题具体分析。
优化原则：小表驱动大表，即小的数据集驱动大的数据集。
explain select * from dept where deptno in (select deptno from emp)
-- 上面的SQL语句等价于下面的SQL语句
explain select * from dept where exists (select 1 from emp where emp.deptno=dept.deptno)
当B表的数据集小于A表的时候，使用in优于exists
当B表的数据集大于A表的时候，使用exists优于in
why?
因为in是根据B表检索出的数据匹配A表，而exists是根据A表数据放到B表中做条件验证。
因此根据小表驱动大表原则，当B表记录相对少的时候用in,当A表记录相对少的时候用exists。
order by
MySQL支持二种方式的排序，FileSort和Index，Index效率高。它指MySQL扫描索引本身完成排序。FileSort方式效率较低。ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。
使用索引排序的情况
假设有key a_b_c(a,b,c)
如下情况能使用索引排序
-- order by能使用索引排序:排序字段符合索引最左匹配原则
order by a;
order by a,b;
order by a,b,c;
order by a desc,b desc,c desc
-- 如果where使用索引的最左前缀定义为常量,则order by能使用索引
where a = const order by b,c
where a = const and b = const order by c
where a = const and b > const order by b,c
如下情况不能使用索引排序
order by a asc,b desc,c desc --排序不一致
where g = const order by b,c --丢失a索引
where a = const order by c --丢失b索引
where a = const order by b,c,d --排序字段中出现非索引列d
where a > const order by b,c --a是范围查询后面的索引失效
where a in (...) order by b,c --in也是范围查询同上
有个特殊情况,如果员工表emp有联合索引(sal,hiredate)
---- 这不符合索引最左前缀匹配原则所以不会
explain select * from emp where hiredate>'1987-04-19' order by sal
-- 覆盖索引,使用了index排序
explain select empno,hiredate,sal from emp where hiredate>'1987-04-19' order by sal
特殊情况的原因分析：
- 第一条SQL语句查询条件hiredate不符合最左前缀匹配，所以就不会用索引，更别谈用索引排序了。
- 但是如果出现了覆盖索引，即使我们查询条件不符合最左匹配也会使用索引，因为我们要从索引树遍历数据type为
index比全表扫描all效率要高一些。就跟避免like ‘%%’使用全表扫描情况相似的。explain各个版本差异不小但是统一的地方就是覆盖索引情况肯定会用到索引。 - 既然用到索引了就一定会索引排序吗？当然还要看排序字段符合不符合最左前缀匹配，如下图所示。
FileSort有两种算法：
双路排序:MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输。也就是说先从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
单路排序:取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二张改进的算法，就是单路排序。从磁盘读取查询需要的所有列，按照orderby列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
单路排序也会存在问题：
在sort_buffer中,单路排序比双路排序要多占用很多空间，因为单路排序是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序(创建tmp文件,多路合并)，排完再取sort_buffer容量大小，再此排序…从而造成多次I/O。
本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。
总结：
1.尽量使用Index排序
Index排序的满足条件:使用where子句与order by子句的条件列组合满足索引最左前列匹配原则。
2.如果要使用filesort排序，需要考虑以下三点
(1)order by时的select *是一个大忌，一定要query只需要的字段，这点非常重要。
- 首先当Query的字段大小总和小于
max_length_for_sort_date而且排序字段不是TEXT|BLOB类型时，才会使用改进的算法单路排序，否则会用老算法多路排序。 - 两种算法都有可能超出
sort_buffer的容量，超出之后会创建tmp文件进行合并排序，导致多次I/O，但是单路排序算法的风险更大一些，因为单路排序是把所有的Query字段取出
(2)尝试提高sort_buffer_size
不管使用单路排序还是多路排序，提高这个参数都会提高效率，当然要根据系统的能力去提高，因为这个参数是针对每个进程的。
(3)尝试提高max_length_for_sort_data
提高这个参数会增加使用单路排序的概率。但是如果设的太高，数据总量超出sort_buffer_size的概率也会增大，明显的症状是高的磁盘I/O活动和低的处理器使用率
group by
(1)group by实质是先排序后进行分组，会产生临时表。遵照索引建的最左前缀匹配原则，情况跟order by基本一样。使用了索引排序就不会产生临时表了。
(2)当无法使用索引列的时候,尝试增大max_length_for_sort_data参数的设置，增大sort_buffer_size参数的设置
(3)where高于having,能写在where限定的条件就不要去having限定了。
-- 假设员工表有联合索引idx_deptno_job_mgr(deptno,job,mgr)
-- 不会出现Using temporary的情况
explain select * from emp group by deptno
explain select * from emp where deptno=20 group by job,mgr
explain select * from emp where deptno>20 group by deptno,job
-- 会出现Using temporary的情况
explain select * from emp group by job
explain select * from emp where deptno>20 group by job
-- 特殊情况不会出现Using temporary
explain select * from emp where job in ('clerk','salesman') group by deptno;
跟上边order by情况不太一样。但是两边解决的前提都是要使用索引，只是order by要想在where条件不满足使用索引的情况下，需要使用覆盖索引来强制使用索引。也就是说这里的索引覆盖是为了使用索引。
但是group by不太一样。如果group by的字段只出现索引的列并符合最左前缀匹配，即使where条件不满足使用索引的情况，也会去使用索引。例如
explain select * from emp group by deptno;
没有where条件也会走索引。所以这个特殊语句走索引，再加上按照第一个字段deptno排序分组，所以不会出现Using temporary
五、慢查询日志
1.慢查询日志是什么？
MySQL慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的SQL语句。
2.怎么开启慢查询日志？
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
--查看是否开启慢查询
show variables like '%slow_query_log%'
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/d8fa1c4dd675-slow.log |
+---------------------+--------------------------------------+
使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。
-- 手动开启慢查询日志功能
set global slow_query_log=1;
3.什么样的SQL会被记录到慢查询中？
查看SQL响应时间超过多少秒会被记录的慢查询日志中
show variables like '%long_query_time%';
--默认是10秒
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
自己设置响应时间的阈值，需要重新连接MySQL生效
--手动设置为3秒，需要重新连接MySQL生效
set global long_query_time=3;
--重新连接MySQL生效
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
4.查看当前系统中有多少条慢查询记录?
mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 2 |
+---------------+-------+
5.如何配置慢查询永久生效？
以上命令行配置方法当MySQL重启后就会失效。如果想要配置永久生效，就必须修改配置文件my.cnf，然后重启MySQL服务器
-- my.cnf文件中[mysqld]下配置:
#开启慢查询时间
slow_query_log=1;
#指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log
slow_query_log_file=/var/lib/mysql/d8fa1c4dd675-slow.log;
#设置响应时间的阈值
long_query_time=3;
#日志记录到文件中
long_output=FILE;
当然，如果不是调优需要的话，一般不建议开启慢查询，因为或多或少会带来一定的性能影响。
6.慢查询日志结果分析
我们可以使用tail -f指令实时查看慢查询日志
tail -f d8fa1c4dd675-slow.log
# Time: 2022-03-04T10:51:50.452105Z -->时间
# User@Host: root[root] @ localhost [] Id: 7 -->使用的用户以及用户的id
# Query_time: 4.011471 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 -->检索需要的时间
SET timestamp=1632912710; -->当前时间的时间戳
select sleep(4); -->记录的SQL语句
7.日志分析工具mysqldumpshow
生产环境中，如果要手工分析日志，查找、分析SQL显然是个体力活，MySQL提供一个日志分析工具mysqldumpshow
#查看mysqldumpshow帮助信息
mysqldumpshou --help
-s:是表示按何种方式排序，有以下7种
c:是访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-g:后边搭配一个正则表达式,大小写不敏感的
-t:后面要添加一个数字,表示返回前面多少条数据
工作中常用的例子参考
//得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log
//得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/xxx-slow.log
//得到按照时间排序得到前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/xxx-slow.log
//另外建议在使用这些命令时结合|和more使用，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log | more
8.全局查询日志
永远不要在生产环境开启这个功能。它会将所有的SQL语句插入到一张表中。
命令行开启
-- 命令行配置
set global general_log=1;
set global log_output='TABLE';
-- 此后你所编写的SQL语句将会记录到mysql库里的general_log表
-- 可以查看此张表
select * from mysql.general_log;
配置开启，在mysql的my.cnf中设置如下
#开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE
六、show profiles
1.show profiles是什么？
是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量。
2.如何使用？
默认情况下参数处于关闭状态，显示15条SQL，先查询是否开启
show variables like 'profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
设置profiling参数为ON
set profiling=1; --或者 set profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)
show variables like 'profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.00 sec)
3.如何使用？
接下来随便运行几条sql，并使用 show profiles 命令，将展示刚才运行的SQL语句。参数很好理解，不做解释了。
mysql> show profiles;
+----------+------------+----------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------+
| 1 | 0.00027275 | select * from emp where id=1 |
| 2 | 0.50672100 | select * from emp group by deptno |
| 3 | 1.79722200 | select * from emp group by ename |
| 4 | 0.11758800 | select * from emp where deptno=100 |
| 5 | 0.98564400 | select * from emp where job='salesman' |
| 6 | 0.11325000 | select * from emp where job='salesman' limit 15000 |
| 7 | 0.04324600 | select * from emp where comm='400' limit 15000 |
+----------+------------+----------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)
诊断SQLshow profile cpu,block io for query Query_ID
下面分析指定的SQL语句，使用如下命令(3是上面查出来的Query_ID，cpu和block io代表cpu的处理时间和io的时间)：
mysql> show profile cpu,block io for query 3;
+---------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+---------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000046 | 0.000042 | 0.000000 | 0 | 0 |
| checking permissions | 0.000008 | 0.000007 | 0.000000 | 0 | 0 |
| Opening tables | 0.000016 | 0.000015 | 0.000000 | 0 | 0 |
| init | 0.000018 | 0.000018 | 0.000000 | 0 | 0 |
| System lock | 0.000007 | 0.000007 | 0.000000 | 0 | 0 |
| optimizing | 0.000004 | 0.000005 | 0.000000 | 0 | 0 |
| statistics | 0.000012 | 0.000012 | 0.000000 | 0 | 0 |
| preparing | 0.000009 | 0.000008 | 0.000000 | 0 | 0 |
| Creating tmp table | 0.000023 | 0.000023 | 0.000000 | 0 | 0 |
| Sorting result | 0.000005 | 0.000005 | 0.000000 | 0 | 0 |
| executing | 0.000003 | 0.000003 | 0.000000 | 0 | 0 |
| Sending data | 0.144196 | 0.141112 | 0.001065 | 384 | 0 |
| converting HEAP to ondisk | 0.227643 | 0.226074 | 0.000000 | 464 | 0 |
| Sending data | 0.785938 | 0.778753 | 0.000047 | 192 | 0 |
| Creating sort index | 0.635323 | 0.345698 | 0.065121 | 14920 | 190584 |
| end | 0.000918 | 0.000006 | 0.000013 | 0 | 0 |
| query end | 0.000018 | 0.000006 | 0.000012 | 0 | 0 |
| removing tmp table | 0.002964 | 0.000220 | 0.000450 | 440 | 0 |
| query end | 0.000014 | 0.000003 | 0.000006 | 0 | 0 |
| closing tables | 0.000016 | 0.000005 | 0.000010 | 0 | 0 |
| freeing items | 0.000026 | 0.000009 | 0.000018 | 0 | 0 |
| cleaning up | 0.000019 | 0.000006 | 0.000013 | 0 | 0 |
+---------------------------+----------+----------+------------+--------------+---------------+
其中cpu和block io两个参数比较重要，还有其他参数，如下：
| type | desc |
|---|---|
| ALL | 显示所有的开销信息 |
| BLOCK IO | 显示与IO相关的开销 |
| CONTEXT SWITCHES | 上下文切换相关开销 |
| CPU | 与CPU相关的开销 |
| IPC | 显示发送和接受相关的开销信息 |
| MEMORY | 显示内存相关的开销信息 |
| PAGE FAULTS | 显示页面错误相关开销信息 |
| SOURCE | 显示和Source_function，SOURCE_file，SOURCE_line 相关的开销信息 |
| SWAPS | 显示交换次数相关的开销信息 |
4.参数的分析
在show profile 的时候有一个字段叫status，几个重要的参数如下
| 状态 | 描述 |
|---|---|
| System lock | 确认是由于哪个锁引起的，通常是因为MySQL或InnoDB内核级的锁引起的建议：如果耗时较大再关注即可，一般情况下都还好 |
| Sending data | 从server端发送数据到客户端，也有可能是接收存储引擎层返回的数据再发送给客户端，数据量很大时尤其经常能看见。 备注：Sending Data不是网络发送，是从硬盘读取，发送到网络是Writing to net。 建议：通过索引或加上LIMIT，减少需要扫描并且发送给客户端的数据量 |
| Sorting result | 正在对结果进行排序，类似Creating sort index，不过是正常表，而不是在内存表中进行排序建议：创建适当的索引 |
| Table lock | 表级锁，没什么好说的，要么是因为MyISAM引擎表级锁，要么是其他情况显式锁表 |
| create sort index | 当前的SELECT中需要用到临时表在进行ORDER BY排序。 建议：创建适当的索引 |
| checking query cache for querychecking privileges on cachedsending cached result to clienstoring result in query cache | 和query cache相关的状态，已经多次强烈建议关闭 |
除了上述几个字段，如果Status出现了如下几个字段，说明SQL性能问题很严重。
| 状态 | 描述 |
|---|---|
| converting HEAP to MyISAM | 查询结果太大，内存不够用，往磁盘上存储了。 |
| Creating tmp table | 创建临时表，首先拷贝原有数据到临时表，用完后再删除临时表， 在数据量很大的情况下，异常的耗性能。 |
| Copying to tmp table on disk | 把内存中的临时表复制到磁盘，也相当的耗费性能。 |
| locked | 锁表了。 |
440
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
