关于慢sql的分析定位

一、概述

在java项目中最大的隐患项之一就是慢SQL，它影响到服务的稳定性，也是日常工作中经常导致程序的最大隐患，在日常开发中如何避免出现慢SQL，出现了慢SQL应该按照什么思路去解决是我们必须要知道。其优化原则，总体可以归纳为：

• 科学创建索引
• 减少不必要的列查询
• 使用覆盖索引
• 语句改写
• 适当的应用内存进行条件组合分次查询
• 选择合适的列进行排序
• 适当的列冗余，减少关联表
• SQL 拆分，化繁为简
• 适当结合 ES

二、问题分析

**1、编写sql **
在日常开发中，难免会遇到一些复杂的业务场景需要使用一些复杂的sql来解决业务问题，这些sql往往包含了一些复杂的函数、子查询。在项目的初期由于数据量少，不会对数据库造成太大的压力，但慢慢的随着业务的发展和时间的积累这些sql就会渐渐的成为慢sql，对数据库性能产生一定的影响，甚至影响程序正常运行。

对于这种场景，建议开发人员先了解业务场景，梳理清楚其关联关系，把sql拆分成简单的小sql，对应的关联主键加上索引，在内存中关联组合分次查询。同时在单测时，应用增加大数据量场景验证其性能。

** 2、使用explain分析sql**
通过 explain 可以初步定位出 SQL 是否使用索引，使用的索引是否正确，排序是否合理、索引列区分度等情况，通过这些基本就可以定位出绝大部分问题。在使用explain进行sql 分析时，我们最需关注的是以下这几个指标：

• type
• possible_keys
• key
• rows
• extra

2.1 type
type 为索引连接类型，它有下面几种类型的取值，

• system：该表只有一行（相当于系统表），system 是 const 类型的特例
• const：针对主键或唯一索引的等值查询扫描，最多只返回一行数据. const 查询速度非常快，因为它仅仅读取一次即可
• eq_ref：当使用了索引的全部组成部分，并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 才会使用该类型，性能仅次于 system 及 const。
• ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。
• range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有 BETWEEN 子句或 WHERE 子句里有 >、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN () 等操作符。
• index：全索引扫描，和 ALL 类似，只不过 index 是全盘扫描了索引的数据。
• !!#ff0000 all：全表扫描，性能最差。!!

对于这几种类型，性能的好坏排序为：system>const>eq_ref>ref>range>index>ALL

2.2 possible_keys
展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。

2.3 key
表示 MySQL 实际选择的索引，重点需要注意 Using filesort 和 Using temporary，前者代表无法利用索引完成排序操作，数据较少时从内存排序，否则从磁盘排序，后者 MySQL 需要创建一个临时表来保存结果。

2.4 rows
表示sql所查询的行数

2.5 extra
该列显示 MySQL 在查询过程中的一些详细信息。主要包括:

• Using filesort
• Using temporary
• Using index
• Using where
• Using join buffer
• impossible where
• select tables optimized away
• distinct
  在日常问题排查中，我们最需要关注 Using filesort、Using temporary、Using index 这几个指标。

2.5.1 Using filesort（!!#ff0000 重要!!）
改参数说明mysql 会使用一个外部的索引进行排序，而不是按照主表内的索引顺序读取。这种没有按照表内索引完成排序的操作称做为 filesort（文件排序）。
该问题大多数出现在使用复合索引进行查询排序的场景，在索引设计上需要注意最左原则，防止索引失效和索引条件缺失导致的排序问题。
EG：
在项目中我们会有 scrm_custom_field 表，为了满足业务需求，创建了一个复合索引indx_g_id_t_id_sort

但在代码实现时，编写语句为：

EXPLAIN  SELECT * FROM scrm_custom_field sc WHERE  sc.group_id = '10000'   ORDER BY  sc.sort

其执行计划如下：

一、概述

在java项目中最大的隐患项之一就是慢SQL，它影响到服务的稳定性，也是日常工作中经常导致程序的最大隐患，在日常开发中如何避免出现慢SQL，出现了慢SQL应该按照什么思路去解决是我们必须要知道。其优化原则，总体可以归纳为：

• 科学创建索引
• 减少不必要的列查询
• 使用覆盖索引
• 语句改写
• 适当的应用内存进行条件组合分次查询
• 选择合适的列进行排序
• 适当的列冗余，减少关联表
• SQL 拆分，化繁为简
• 适当结合 ES

二、问题分析

**1、编写sql **
在日常开发中，难免会遇到一些复杂的业务场景需要使用一些复杂的sql来解决业务问题，这些sql往往包含了一些复杂的函数、子查询。在项目的初期由于数据量少，不会对数据库造成太大的压力，但慢慢的随着业务的发展和时间的积累这些sql就会渐渐的成为慢sql，对数据库性能产生一定的影响，甚至影响程序正常运行。

对于这种场景，建议开发人员先了解业务场景，梳理清楚其关联关系，把sql拆分成简单的小sql，对应的关联主键加上索引，在内存中关联组合分次查询。同时在单测时，应用增加大数据量场景验证其性能。

** 2、使用explain分析sql**
通过 explain 可以初步定位出 SQL 是否使用索引，使用的索引是否正确，排序是否合理、索引列区分度等情况，通过这些基本就可以定位出绝大部分问题。在使用explain进行sql 分析时，我们最需关注的是以下这几个指标：

• type
• possible_keys
• key
• rows
• extra

2.1 type
type 为索引连接类型，它有下面几种类型的取值，

• system：该表只有一行（相当于系统表），system 是 const 类型的特例
• const：针对主键或唯一索引的等值查询扫描，最多只返回一行数据. const 查询速度非常快，因为它仅仅读取一次即可
• eq_ref：当使用了索引的全部组成部分，并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 才会使用该类型，性能仅次于 system 及 const。
• ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。
• range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有 BETWEEN 子句或 WHERE 子句里有 >、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN () 等操作符。
• index：全索引扫描，和 ALL 类似，只不过 index 是全盘扫描了索引的数据。
• !!#ff0000 all：全表扫描，性能最差。!!

对于这几种类型，性能的好坏排序为：system>const>eq_ref>ref>range>index>ALL

2.2 possible_keys
展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。

2.3 key
表示 MySQL 实际选择的索引，重点需要注意 Using filesort 和 Using temporary，前者代表无法利用索引完成排序操作，数据较少时从内存排序，否则从磁盘排序，后者 MySQL 需要创建一个临时表来保存结果。

2.4 rows
表示sql所查询的行数

2.5 extra
该列显示 MySQL 在查询过程中的一些详细信息。主要包括:

• Using filesort
• Using temporary
• Using index
• Using where
• Using join buffer
• impossible where
• select tables optimized away
• distinct
  在日常问题排查中，我们最需要关注 Using filesort、Using temporary、Using index 这几个指标。

2.5.1 Using filesort（!!#ff0000 重要!!）
改参数说明mysql 会使用一个外部的索引进行排序，而不是按照主表内的索引顺序读取。这种没有按照表内索引完成排序的操作称做为 filesort（文件排序）。
该问题大多数出现在使用复合索引进行查询排序的场景，在索引设计上需要注意最左原则，防止索引失效和索引条件缺失导致的排序问题。
EG：
在项目中我们会有 scrm_custom_field 表，为了满足业务需求，创建了一个复合索引indx_g_id_t_id_sort

但在代码实现时，编写语句为：

EXPLAIN  SELECT * FROM scrm_custom_field sc WHERE  sc.group_id = '10000'   ORDER BY  sc.sort

其执行计划如下：

我们看到了sql 执行确实是使用了复合索引，但是在extra 中却出现了 Using filesort 说明其排序字段没有使用到表内索引，进行多了一次表外排序，降低了sql的执行效率。

!!#ff0000 经过sql改写：!!

EXPLAIN  SELECT * FROM scrm_custom_field sc WHERE sc.group_id = "1111" and  sc.tenant_id= '10000'   ORDER BY  sc.sort

其执行计划：

该sql没有出现外部索引排序的产生，效率也会得到相对应的提升。
!!#ff0000 值得注意的是 Extra 出现 Using index condition 说明，表内的索引 没有得到充分的使用，在该例子中，因为没有用到 id 索引就会出现该信息提示。!!

2.5.2 Using temporary （!!#ff0000 重要!!）
该参数表明sql在执行的过程中使用了临时表来保存中间数据，mysql在对结果排序的时候使用临时表，比如使用 order by 或者group 不要的时候。

EG:

如以下语句：

EXPLAIN SELECT count(client_id) from scrm_customer WHERE id IN (1422023328163434498,1425906759397584898,1425906759569551363) GROUP BY id,tenant_id

从执行计划中可以看出，该表的索引设计及使用不合理，但在这里我们只关注 Using temporary 。由于在分组的时候没有按照复合索引的顺序进行（缺少或者乱序），导致出现临时表和外部排查，从而降低效率。
!!#ff0000 经过sql改写：!!

EXPLAIN SELECT count(client_id) from scrm_customer WHERE id IN (1422023328163434498,1425906759397584898,1425906759569551363) GROUP BY id,union_id,tenant_id

!!#ff0000 分组时按照建立复合索引字段id,union_id,tenant_id的顺序进行，因此没有产生临时表并且也没有使用外部的索引排序，因此效率高。!!

2.5.3 Using index（重要）
该参数表示在sql的 select 语句中使用了覆盖索引，避免了访问表的数据行，执行性能比较好。

• 如果同时出现 using where，表明索引被用来执行索引键值的查找;
• 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

如以下语句：

EXPLAIN SELECT count(id) from scrm_customer WHERE id IN (1422023328163434498,1425906759397584898,1425906759569551363) GROUP BY id,union_id,tenant_id

其执行计划为：

表明索引被用来执行索引键值的查找

PS：!!#ff0000 覆盖索引(Covering Index)!!
当我们使用select语句时只查询添加了索引的字段，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说就是查询列要被所建的索引覆盖。
这里需要值得注意的点是：

• 如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可使用select * 的查询方式

如以下语句：

- selecselect id, name from t_user_info where age=20;

在 t_user_info表中建立组合索引 idx_id_name_age_xxx 包含了(覆盖了)，id,name,age三个字段。
那么在执行上述语句时，直接将建立了索引的列读取出来了，而不需要去查找所在行的其他数据。所以很高效。

2.5.4 Using where
表明使用了where过滤

2.5.5 Using join buffer
出现在当两个连接时驱动表(被连接的表,left join 左边的表。inner join 中数据少的表) 没有索引的情况下。
给驱动表建立索引可解决此问题。且 type 将改变成 ref

2.5.6 impossible where
表示 where 条件没有命中，获取不到任何的数据。

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

2.5.8 distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作