互联网中慢SQL优化手段

你的名字 2022-12-27 11:27 115阅读 0赞

一个再完美的架构，也会因为一个慢SQL导致系统直接崩溃。

最近在做一个比较有意思的工作，优化系统中的慢SQL，整个过程我是很享受的，往往很多慢SQL都是系统中调用非常频繁的接口，这让我对业务更加熟悉，整个优化过程下来对系统和团队的帮助是非常大的，自己针对这次优化历程总结了一些经验和个人心得，希望可以帮到有需要的小伙伴。

##### 了解MySQL执行过程 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdjaGVuZ21pbmcx_size_16_color_FFFFFF_t_70]  
图片来源于《高性能MySQL》pdf版。从图片中可以清楚的看到：

*  客户端发送一条查询给服务器；
 *  服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段；
 *  服务器段进行SQL解析、预处理，在优化器生成对应的执行计划；
 *  mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询；
 *  将结果返回给客户端。

##### 了解一条SELECT语句是如何执行的 #####

*  SELECT语法如下

(5)SELECT DISTINCT <select_list>                     
    
    (1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>
    
    (2)WHERE <where_predicate>
    
    (3)GROUP BY <group_by_specification>
    
    (4)HAVING <having_predicate>
    
    (6)ORDER BY <order_by_list>
    
    (7)LIMIT n, m

*  SELECT语句的处理过程
    
     *  FROM阶段
     *  WHERE阶段
     *  GROUP BY阶段
     *  HAVING阶段
     *  SELECT阶段
     *  ORDER BY阶段
     *  LIMIT阶段
 *  FROM阶段

一条查询的sql语句先执行的是FROM table负责把数据库的表文件加载到内存中去

*  WHERE阶段

WHERE会把表中的数据进行过滤，取出符合条件的记录行，生成一张临时表

*  GROUP BY阶段

GROUP BY阶段按照指定的列名切分成若干临时表

*  HAVING阶段

该阶段根据HAVING子句中出现的谓词对虚拟表进行分组进行筛选

*  SELECT阶段，这个阶段是投影的过程，处理SELECT子句提到的元素
    
     *  计算SELECT列表中的表达式
     *  若有DISTINCT，则删除重复行
 *  ORDER BY阶段

根据ORDER BY子句中指定的字段进行排序

*  LIMIT阶段

取出指定行的记录，产生临时表，并返回给查询用户

##### 打开MySQL慢查询日志 #####

默认情况下MySQL慢查询日志是没有打开的，我们可以看下MySQL的console。

在这里，`slow_query_log` 指的是慢查询日志是否开启，`slow_query_log_file` 指明了日志所在的位置，`long_query_time` 指的是sql执行时间超过这个时间就是慢SQL（默认是10s）。

##### 善用explain #####

使用`EXPLAIN`关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。`EXPLAIN`可以帮助选择更好的索引和写出更优化的查询语句。在MySQL5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用`EXPLAIN`查看执行计划。通过EXPLAIN，我们可以分析出以下结果：

1. 表的读取顺序
    2. 数据读取操作的操作类型
    3. 哪些索引可以使用
    4. 哪些索引被实际使用
    5. 表之间的引用
    6. 每张表有多少行被优化器查询

*  EXPLAIN语法

EXPLAIN SELECT * FROM table

*  执行计划各字段含义

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdjaGVuZ21pbmcx_size_16_color_FFFFFF_t_70 1]

*  id  
    SELECT识别符。这是SELECT的查询序列号。id相同，执行顺序从上往下，  
    id不同，id值越大，优先级越高，越先执行。
 *  select\_type  
    查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询。
    
     *  simple ——简单的select查询，查询中不包含子查询或者UNION
     *  primary ——查询中若包含任何复杂的子部分，最外层查询被标记
     *  subquery——在select或where列表中包含了子查询
     *  derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
     *  union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived
     *  union result:UNION 的结果
 *  table  
    查询所涉及的表。
 *  partitions  
    版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。
 *  type
    
     *  `system`：表中仅有一行（=系统表）这是const联结类型的一个特例。
     *  `const`：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量
     *  `eq_ref`：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描
     *  `ref`：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体
     *  `range`：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描
     *  `index`：index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。
     *  `all`：遍历全表以找到匹配的行
     *  从好到坏的顺序一次是：`system > const > eq_ref > ref > range > index > all`
 *  possible\_keys  
    查询可能使用到的索引都会在这里列出来
 *  key  
    查询真正使用到的索引，select\_type为index\_merge时，这里可能出现两个以上的索引，其他的select\_type这里只会出现一个。
 *  key\_len  
    表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
 *  ref  
    显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值
 *  rows  
    根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数
 *  filtered  
    查询条件过滤的行数的百分比

##### 索引原则 #####

引用美团技术团队文章：[MySQL索引原理及慢查询优化][MySQL]

*  最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
 *  =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
 *  尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(\*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
 *  索引列不能参与计算，保持列“干净”，比如from\_unixtime(create\_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create\_time = unix\_timestamp(’2014-05-29’)。
 *  尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

##### 优化慢SQL建议 #####

*  尽量规避长索引使用短索引，并标明索引的长度，尽量选择区分度高的列作为索引，建立索引的时候将区分度高的字段放在前面，增加索引命中率
 *  避免大而全的SQL，避免select \*，用什么字段查询什么字段
 *  模糊查询时禁止使用左模糊和全模糊，会导致索引失效
 *  尽量避免连表查询，可以在表中冗余一些字段
 *  in的集合尽量小一点，MySQL默认是200，避免长SQL
 *  正确使用事务，尽量避免大事务
 *  尽量避免循环IO，如果业务是非这样子做不可，需要评估循环次数等
 *  不要指望所有语句都能通过SQL优化
 *  选择正确的驱动表，驱动表不一定是小表；表大，但过滤条件很强，能快速减少关联中间结果也可以选为驱动表
 *  当只要一行数据时使用LIMIT 1
 *  不要过于自信，只针对具体case来优化，而忽略了更复杂的情况
 *  对于读多写少的表，增加索引是一方面，开启缓存才是最有效的

##### 分库分表 #####

分库分表是一个直观解决IO瓶颈、CPU瓶颈、慢SQL的解决方案，但是分库分表之后面临着跨库join的问题、分布式事物的问题、唯一ID的问题和新老数据兼容的问题。

##### 读写分离 #####

使用读写分离的方式，降低数据库的压力，读写分离能有效降低库的压力，但是其并不是银弹，使用时需注意以下问题。

*  主从延迟问题。读写分离后，无可避免的会有延迟问题，所以需要甄别好，哪些业务是对延迟敏感的，这类业务，需要继续查询主库。为尽量避免延迟问题，需注意以下几点：
    
     *  从库的压力，不能过大，如果资源允许，尽量主从的硬件资源相同。
     *  避免使用大事务。
     *  尽量避免大批量的删除、更新操作，尤其是无法使用索引的情况。
 *  业务隔离，不同业务使用不同从库。识别出业务的黄金流程。重点业务与其他非重点业务使用不同的从库进行隔离。

##### 善用缓存 #####

缓存是一个杀手锏，同样也是一个提高系统性能的高效手段，通常我们可以将一些热点数据、不经常更改的配置数据缓存到redis或者本地缓存中。但是缓存在带来便利的同时也带来了数据一致性的问题，如果使用缓存，数据一致性是我们必须解决的问题。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdjaGVuZ21pbmcx_size_16_color_FFFFFF_t_70]: /images/20221120/6bdde7f17b1644f48e24b1961467cf86.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdjaGVuZ21pbmcx_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190823145028435.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdjaGVuZ21pbmcx,size_16,color_FFFFFF,t_70
[MySQL]: https://tech.meituan.com/2014/06/30/mysql-index.html