慢SQL治理方法论

  • Z时代
  • 2024-01-10
  • 分类：综合

database

本文介绍了笔者实际工作中慢SQL治理的方法论，1、发现：如何发现慢SQL。2、定位：如何定位到慢SQL写在哪。3、分析：遇到慢SQL时的分析思路。4、解决：慢SQL的解决思路。

@

目录
  • 一、背景
  • 二、发现
  • 三、定位
  • 四、分析
    • 4.1 索引层面分析
    • 4.2 业务层面分析
  • 五、解决
    • 5.1 SQL优化
      • 5.1.1索引优化
      • 5.1.2 子查询优化
      • 5.1.3 分页优化
      • 5.1.4 Using filesort文件排序优化
        • 解决
    • 5.2 业务改造
      • 5.2.1 总量显示优化
      • 5.2.1 关联表优化
    • 5.3 减少数据
  • 总结

一、背景

  从业务的角度来看：慢SQL会导致产品用户体验差，会减低用户对产品的好感度。

  从数据库的角度来看：慢SQL会影响数据库的性能，每个SQL执行都需要消耗一定的I/O资源。假设总资源是100，有一条慢SQL占用了30的资源共计1分钟。那么在这1分钟时间内，其他SQL能够分配的资源总量就是70，如此循环，当资源分配完的时候，所有新的SQL执行将会排队等待。

二、发现

  在治理慢SQL前我们需要知道哪些SQL是慢SQL，即明确治理的对象。MySQL本身提供了慢查询日志，当SQL耗时超过指定阈值的时候，会将SQL记录到慢查询日志文件中，用户能够从慢查询日志文件中提取出慢SQL。

  MySQL是可以动态开启慢查询日志，即线上的服务器没有开启慢日志，重启后会失效。为防止线上业务受影响，可以先这样修改，同时将my.cnf配置文件补上配置项即可。

  • 查看配置

    • slow_query_log 是否启用慢查询日志

    • long_query_time 慢查询阈值

    • slow_query_log_file 慢查询日志文件slow.log位置

    show VARIABLES like "%query%";

  • 开启慢查询日志

  MySQL数据库默认不启动慢查询日志，需要手动设置，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响

# 开启慢查询日志
set global slow_query_log=ON;
# 慢查询阈值
set global long_query_time=1;
# 慢查询日志文件
set global slow_query_log_file=/tmp/mysql_slow.log

三、定位

  我们通过慢查询日志提取出慢SQL，将这些慢SQL按不同的应用进行区分并整理一份文档，再定位到对应应用的代码，在文档中记录慢SQL应用在什么业务中，运行在什么场景中（定时任务、在线实时查询等）。

四、分析

  接下来是根据整理的文档，对这些慢SQL做一些分析，找出慢SQL产生的原因。

4.1 索引层面分析

  使用explain命令输出SQL的执行计划，透过执行计划我们可以了解慢SQL的执行细节。

Mysql中的执行计划各列说明。

  • id: 按照sql语法解析后分层后的编号

  • type：执行计划中指定表使用的访问路径方式。

  这是个非常重要的字段，也是我们判断一个SQL执行效率的主要依据（以下只列举常见的几种）。

  依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

   - system：从系统表读一行。这是const联接类型的一个特例。

   - const：表最多有一个匹配行,它将在查询开始时被读取。const用于常数值比较PRIMARY KEY或UNIQUE索引的所有部分

  - eq_ref：它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。

   - ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

- ref是我们日常开发中较为常见的情况，也是原则上期望要达到的级别，查询命中到索引。

  - range：索引范围扫描

   - index：只扫描索引树，不需要回表查询。在这种情况下，explain 的 Extra 列的结果是 Using index

   - all：全表扫描。

  • possible_keys：查找表中的行时可选择的索引。

  • key：显示MySQL实际决定使用的索引。

  • key_len：显示MySQL使用索引键的长度，就是此次查询所选择的索引长度有多少字节。

  • ref：ref字段标识哪些字段或者常量被用来和key配合从表中查询记录出来，如果为NULL表示没有

  • rows：该列表明MySQL估计要读取并检查的行数，需要注意的是，这个不是结果集里的行数。

  • filtered：它指返回结果的行数（MySQL层where过滤生效的数据量）占需要扫描到的行数（rows列的值）的百分比，一般来说越高越好，越低证明查询代价越高。

  • Extra：该列包含MySQL解决查询的详细信息（以下只列举常见的几种）。

    • Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

    • Using index：这个值重点强调了只需要使用索引就可以满足查询表的要求，不需要直接访问表数据了，一般表示使用了覆盖索引。

    • Using temporary：这个值表示使用了内部临时表（基于内存的表）。这种情况通常发生在查询时包含了group by和order by子句，或者来自不同表的列使用了distinct。

    • Using where：where条件查询，通常using where表示优化器需要通过索引回表查询数据

4.2 业务层面分析

根据具体的业务场景进行考虑

  • 查询条件是否都是必须的

  • 查询时间范围可否缩短

  • 表里面存在的一些大字段，根据实际情况缩短字段长度

    • 如果不是每次都必须获取的话，可以考虑从当前表拆出去，主表都是小字段，子表维护大字段，这样效率会更高

五、解决

5.1 SQL优化

5.1.1索引优化

  1. 左前缀原则：索引了多个列时，查询时必须从最左列开始，不能跳过，否则索引失效

  2. 在使用不等于符号时(!=，<>)会索引失效

  3. 使用is not null会索引失效，但is null 不会

  4. like模糊查询中以通配符开头会索引失效

  5. 使用or时，左右两边的字段都需要加上索引，否则索引失效

  6. 在索引列上使用函数会索引失效

  7. 避免隐式类型转换-字符串类型字段不加单引号索引失效

  8. 有时候MySQL优化器选择的索引不一定是最优的，可以通过FORCE INDEX(idx_order_id)强制要求走某个索引，当然，必须保证这个索引以后不能被删除，不然就是个BUG

5.1.2 子查询优化

select something from user_table 
where id in (select user_id from order_table where xxx=yyyy);

  MySQL从5.7开始优化器对子查询进行了优化，会自动转换为join再执行，而对于5.7以下版本的MySQL 我们建议把子查询改成join的方式：

 select a.something
 from user_table a, order_table b
 where a.id=b.user_id
 and b.xxx=yyyy;

5.1.3 分页优化

  Limit中分页查询会随着pageNo增大而变缓慢，通过子查询+表连接解决

select * from mytbl order by id limit 100000,10 改进后的SQL语句如下：

select * from mytbl 
where id >= ( select id from mytbl order by id limit 100000,1 ) 
limit 10
# 或者
select * from mytbl 
inner join (select id from mytbl order by id limit 100000,10) as tmp 
on tmp.id=ori.id;

5.1.4 Using filesort文件排序优化

  orders建立了idx_ppo_created_at索引，使用EXPLAIN进行分析

EXPLAIN
SELECT id,
       temp_id,
       pos_id,
       `type`,
       member_id,
       temp_status,
       money_amount,
       trans_amount,
       return_trans_amount,
       coupon_id,
       cash_points,
       is_cancel,
       is_auto_cancel,
       is_compensate,
       is_multi_equity,
       company_id,
       store_id,
       store_type,
       source_orders,
       return_order_id,
       created_at,
       updated_at
FROM orders
WHERE 1 = 1
  AND `created_at` >= "2021-08-27 00:00:00"
  AND `created_at` <= "2021-08-27 23:59:59"
  AND `type` = 0
  AND `is_cancel` = 0
  AND `temp_status` = 0
ORDER BY id DESC
LIMIT 0,100


  我们可以看到Extra列出现了Using filesort，说明MySQL会对数据使用一个外部的索引排序, 而不是按照表内索引顺序进行读取。

解决

  因为索引的叶子节点数据是根据 created_at 有序的，我们可以利用这一点来避免排序。

  我们将 ORDER BY id DESC 换成 ORDER BY created_at DESC，查看执行计划，Using filesort 已消失：

5.2 业务改造

  如果SQL本身的性能已经到达极限了，但是耗时仍然很长。这时候，我们可以业务角度着手，看看在业务上能不能做一些变通、妥协。

5.2.1 总量显示优化


  如上图所示，我们在做分页时，页底都会展示符合条件的记录总数，以及分页页数。数据量少的时候，不会带来性能问题，但如果数据量较大，这个计算总量的count() 本身就不会太快，再加上每次打开页面都要计算一次的话，那这样会就带性能问题了，同时也会拖慢页面打开速度。

  如果业务上允许，当数据量少时，精确显示，当数据量过大后，用户对真实数据不敏感时，那我们就可以通过 1000+ 等模糊的方式进行初步显示，减少不必要的扫描，同时也可以让用户首次打开时达到加速效果。

原SQL：select count() from table where xxx=yyy;

调整后：select count(*) from (select id from table where xxx=yyy limit 1000);

5.2.1 关联表优化

  由于业务很复杂，某条SQL关联了很多表，导致表关联时的匹配耗时很长。这时候可以看看能不能将多关联SQL改成较少表的关联，使用代码方式进行关联，但是会增加请求次数。

5.3 减少数据

  如果单表行数超过500万行或者单表容量超过2GB，SQL再怎么优化还是会慢，这个时候就要做数据拆分，这属于架构层面的变动，影响的面很大，除了慢SQL本身之外，其他的相关SQL也可能会被“波及”到。这种慢SQL治理的手段，能够一定程度上解决慢SQL问题。减少作用数据的方式有：

  • 垂直拆分

  • 水平拆分

  • 综合拆分（垂直+水平）

总结

本文介绍了实际工作中慢SQL治理的方法论

1、发现：如何发现慢SQL。

2、定位：如何定位到慢SQL写在哪。

3、分析：遇到慢SQL时的分析思路。

4、解决：慢SQL的解决思路。

  SQL优化本身是一个比较复杂的问题，上面所列举的，只是部份优化的案例，但所有优化的思路都是尽可能的减少SQL在执行中过程中扫描数据块的次数，只要遵从这一核心思想，SQL优化并不是什么太难的事情。

  以上就是今天要讲的内容，本文是笔者实际治理过程中的一些总结和心得，如有不正之处，还请指正。

以上是 慢SQL治理方法论 的全部内容， 来源链接： utcz.com/z/536370.html

回到顶部