【稳定性：数据库】聊聊DB慢查询

一、告警和慢查询

• 治理慢查询的前提，最好建设看板和监控，方便我们能够快速的捞取数据和变化，以及及时的关注告警；
• 看板至少包含：数据库名、数据库类型、查询sql语句的机器IP、查询耗时、慢查询数量、关联的服务或应用、慢查询排序、数据库端口、SQL语句、索引扫描/表扫描、explain信息。
• 当然，直接在MySQL命令终端也是可以查看的，需要开启SlowLog，默认是关闭的，由参数slow_query_log决定。
• MySQL 默认慢查询时间为 100ms, 超过这个时间阈值的 SQL 都会被记录下来。一般情况下 MySQL 的 SQL 响应时间比较稳定, 但是数据库一些正常的内部行为可能会偶尔出现抖动 (最常见的是: 数据库刷脏)。所以我们评估一个实例是否正常一般是 P999 分位的响应时间。

二、慢查询分析和调优思路

找到关键信息：

在慢查询日志里使用explain进行查询语句分析；比较核心要关注的字段一般有：select_type、type、possible_keys、key、rows、Extra等

select_type：select类型

代表表示查询中每个select子句的类型，是简单查询还是联合查询还是子查询，一目了然：

• SIMPLE：简单查询（不使用关联查询或子查询）
• PRIMARY：如果包含关联查询或者子查询，则最外层的查询部分标记primary
• UNION：联合查询（UNION）中第二个及后面的查询
• DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
• UNION RESULT：UNION的结果，union语句中第二个select开始后面所有select
• SUBQUERY：字查询中的第一个查询
• DEPENDENT SUBQUERY ：子查询中的第一个查询，并且依赖外部查询
• DERIVED：派生表的SELECT, FROM子句的子查询
• MATERIALIZED：被物化的子查询
• UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行

table：查询使用的表

type：连接类型

表示MySQL在表中查找所需数据的方式，也称“访问类型”，不要用All，代表全表扫描，是非常差的模式，参考：

• system：查询对象表只有一行数据，且只能用于MyISAM和Memory引擎的表，这是最好的情况
• const：基于主键或唯一索引查询，最多返回一条结果
• eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
• ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
• fulltext：全文检索
• ref_or_null：表连接类型是ref，但进行扫描的索引列中可能包含NULL值
• index_merge：利用多个索引
• unique_subquery：子查询中使用唯一索引
• index_subquery：子查询中使用普通索引
• range：只检索给定范围的行，使用一个索引来选择行
• index：Full Index Scan，index与ALL区别为index类型只遍历索引树
• ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

possible_keys：应该或建议使用的索引

表示MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。这个趋向于指导性作用。

key：实际查询使用到的索引

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

rows：预估扫描了多少行

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。基本表现为实际扫描过的行数。

Extra：MySQL解决查询的详细信息

• Using index：直接访问索引就能够获取到所需要的数据（覆盖索引），不需要通过索引回表。
• Using temporary：为了解决查询,MySQL需要创建一个临时表来容纳结果。
• Using where: 使用了WHERE 子句过滤返回信息。
• Using Index Condition：会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后回表用
  其他条件去过滤这些数据行；
• …

使用规范

一般从两个维度去分析：

1. 语句上有没有优化的空间；
2. 从业务上去考虑（比如取所有数据，业务确实需要如此吗？）；

分析是否有不合理的查询：

以下是基本的准入规范，也是CodeReview 标准。
尽量避免使用select *，join语句使用select * 可能导致只需要访问索引即可完成的查询需要回表取数。
一种是可能取出很多不需要的数据，对于宽表来说，这是灾难；一种是尽可能避免回表，因为取一些根本不需要的数据而回表导致性能低下，是很不合算。

严禁使用select * from t_name，不加任何where条件，道理一样，这样会变成全表全字段扫描。

MySQL中的text类型字段存储：

不与其他普通字段存放在一起,因为读取效率低，也会影响其他轻量字段存取效率。大宽表、大字段表，整体性能也不好。

如果不需要text类型字段，又使用了select *，会让该执行消耗大量io，效率也很低下

在取出字段上可以使用相关函数，但应尽可能避免出现 now() , rand() , sysdate() 等不确定结果的函数，在Where条件中的过滤条件字段上严禁使用任何函数，包括数据类型转换函数。

大量的计算和转换会造成效率低下，这个在索引那边也描述过了。
分页查询语句全部都需要带有排序条件 , 否则很容易引起乱序

用in()/union替换or，效率会好一些，并注意in的个数小于300

ToC业务严禁使用%前缀进行模糊前缀查询。

-- 如下，这种查询会导致扫描表：
select a,b,c from t_name where a like '%name';
-- 可以使用%模糊后缀查询如：
select a,b from t_name where a like 'name%';

尽量避免使用子查询，可以把子查询优化为join操作

通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：

子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；

特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；

由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表。

分页查询，当limit起点较高时，可先用过滤条件进行过滤，offset 进行限制或者带上迭代器ID

-- 如 select a,b,c from t1 limit 10000,20;-- 优化为:select a,b,c from t1 where id>10000 limit 20;

检查是否有不合理的索引使用：

索引区分度（> 0.2）
索引必须创建在索引选择性（区分度）较高的列上，选择性的计算方式为:

selecttivity = count(distinct c_name)/count(*) ;
如果区分度结果小于0.2，则不建议在此列上创建索引，否则大概率会拖慢SQL执行

遵循最左前缀，将索引区分度最高的放在左边
对于确定需要组成组合索引的多个字段，设计时建议将选择性高的字段靠前放。使用时，组合索引的首字段，必须在where条件中，且需要按照最左前缀规则去匹配。
正确理解和计算索引字段的区分度，文中有计算规则，区分度高的索引，可以快速地定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。

禁止使用外键，可以在程序级别来约束完整性
varchar、text类型字段如果需要创建索引，必须使用前缀索引。
前缀索引计算公式如下，calcul_len 是数字，长度为1 ~ c_name字段的最长值，可以逐一比较，对比区分度最高的出来
正确理解和计算前缀索引的字段长度，文中有判断规则，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。

select count(distinct left(c_name,calcul_len))/count(*) from t_name;
单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表，应尽量少建索引，索引建立的原则理论上是多读少写的场景。

(a b c ) => (a) (a b) (a b c)
ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面，形成覆盖索引

联合索引注意最左匹配原则：查询时必须按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。如：

– 如果建立(depno,empname,job)顺序的索引，job是用不到索引的。
depno=1 and empname>‘’ and job=1

应需而取策略，查询记录的时候，不要一上来就使用*，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。

正确判断是否使用联合索引，应避免索引下推（IPC），减少回表操作，提升效率。

避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。

避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。

模糊查询’%value%'会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是’value%'是可以有效利用索引。

索引覆盖排序字段，这样可以减少排序步骤，提升查询效率

尽量的扩展索引，非必要不新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
举例子：比如一个品牌表，建立的的索引如下，一个主键索引，一个唯一索引

PRIMARYKEY (id),UNIQUEKEY uni_brand_define (app_id,define_id)

实际场景中，建议代码交叉评审，当你同事业务代码中的检索语句如下的时候，应建议调整：

select brand_id,brand_name from ds_brand_system where status=? and define_id=? and app_id=?

建议改成如下：

select brand_id,brand_name from ds_brand_system where app_id=? and define_id=? and status=?

虽然说 MySQL的查询优化器会根据实际索引情况进行顺序优化，所以这边不做强制。

但是同等条件下还是按照顺序进行排列，比较清晰，并且节省查询优化器的处理。

四、常见的慢查询和优化方法

回表

优化方法：建立联合索引

多个and同时使用，只命中一个索引

优化方法：建立联合索引

索引失效

优化方法：避免使用“不等于”符号、注意最左查询、避免like语句以"%"开头

某字段使用过多or连接

优化方法：使用in语句进行优化

分页查询offset过大

优化方法：使用id>? limit 10 进行分页查询

同时使用where、order by、limit，命中order by后的索引

优化方法：force_index()

五、其他注意点

• 有时候，即使我们做了优化，也并不是完全无损的，只不过是比优化前情况会好一点，所以一定要进行持续的观察。
• 比如：查询使用了复合索引ixtest，全表扫描，返回数据量很大，我们通过强制使用单索引解决问题，扫描的行数会少大概四分之一，但是会生成临时表，所以得看最终效果如何；