动手点关注干货不迷路

背景介绍

从系统设计角度看，一个系统从设计搭建到数据逐步增长，SQL 执行效率可能会出现劣化，为继续支撑业务发展，我们需要对慢 SQL 进行分析和优化，严峻的情况下甚至需要对整个系统进行重构。所以我们往往需要在系统设计前对业务进行充分调研、遵守系统设计规范，在系统运行时定期结合当前业务发展情况进行系统瓶颈的分析。

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

本文仅讨论 MySQL-InnoDB 的情况。

优化方式

SQL 语句执行效率的主要因素

  • 数据量

    • SQL 执行后返回给客户端的数据量的大小；

    • 数据量越大需要扫描的 I/O 次数越多，数据库服务器的 IO 更容易成为瓶颈。

  • 取数据的方式

    • 数据在缓存中还是在磁盘上；

    • 是否能够通过全局索引快速寻址；

    • 是否结合谓词条件命中全局索引加速扫描。

  • 数据加工的方式

    • 排序、子查询、聚合、关联等，一般需要先把数据取到临时表中，再对数据进行加工；

    • 对于数据量比较多的计算，会消耗大量计算节点的 CPU 资源，让数据加工变得更加缓慢；

    • 是否选择了合适的 join 方式

优化思路

  • 减少数据扫描（减少磁盘访问）

    • 尽量在查询中加入一些可以提前过滤数据的谓词条件，比如按照时间过滤数据等，可以减少数据的扫描量，对查询更友好；

    • 在扫描大表数据时是否可以命中索引，减少回表代价，避免全表扫描。

  • 返回更少数据（减少网络传输或磁盘访问）

  • 减少交互次数（减少网络传输）

    • 将数据存放在更快的地方

    • 某条查询涉及到大表，无法进一步优化，如果返回的数据量不大且变化频率不高但访问频率很高，此时应该考虑将返回的数据放在应用端的缓存当中或者 Redis 这样的缓存当中，以提高存取速度。

  • 减少服务器 CPU 开销（减少 CPU 及内存开销）

  • 避免大事务操作

  • 利用更多资源（增加资源）

优化案例

数据分页优化

select*fromtable_demowheretype=?limit?,?;

优化方式一：偏移 id

lastId=0ormin(id)do{select*fromtable_demowheretype=?andid>{#lastId}limit?;lastId=max(id)}while(isNotEmpty)

优化方式二：分段查询

该方式较方式一的优点在于可并行查询，每个分段查询互不依赖；较方式一的缺点在于较依赖数据的连续性，若数据过于分散，代价较高。

minId=min(id)maxId=max(id)for(inti=minId;i<=maxId;i+=pageSize){select*fromtable_demowheretype=?andidbetweeniandi+pageSize;}

优化 GROUP BY

提高 GROUP BY 语句的效率, 可以通过将不需要的记录在 GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多。

低效:

selectjob,avg(sal)fromtable_demogroupbyjobhavingjob=‘manager'

高效:

selectjob,avg(sal)fromtable_demowherejob=‘manager'groupbyjob

范围查询

联合索引中如果有某个列存在范围（大于小于）查询，其右边的列是否还有意义？

explainselectcount(1)fromstatementwhereorg_code='1012'andtrade_date_time>='2019-05-0100:00:00'andtrade_date_time='2019-05-0100:00:00'andtrade_date_time='2019-05-0100:00:00'andtrade_date_time<='2020-05-0100:00:00'

  • 使用单键索引 trade_date_time 的情况下

    • 从索引里找到所有 trade_date_time 在’2019-05-01′ 到’2020-05-01′ 区间的主键 id。假设有 100 万个。

    • 对这些 id 进行排序（为的是在下面一步回表操作中优化 I/O 操作，因为很多挨得近的主键可能一次磁盘 I/O 就都取到了）

    • 回表，查出 100 万行记录，然后逐个扫描，筛选出 org_code=’1020’的行记录

  • 使用联合索引 trade_date_time, org_code -联合索引 trade_date_time, org_code 底层结构推导如下：

以查找 trade_date_time >=’2019-05-01′ and trade_date_time <='2020-05-01' and org_code='1020'为例：

  1. 在范围查找的时候,直接找到最大,最小的值,然后进行链表遍历，故仅能用到 trade_date_time 的索引，无法使用到 org_code 索引

  2. 基于 MySQL5.6+的索引下推特性，虽然 org_code 字段无法使用到索引树，但是可以用于过滤回表的主键 id 数。

小结：对于该 case, 索引效果[org_code,trade_date_time] > [trade_date_time, org_code]>[trade_date_time]。实际业务场景中，检索条件中 trade_date_time 基本上肯定会出现，但 org_code 却不一定，故索引的设计还需要结合实际业务需求。

优化 Order by

索引：

KEY`idx_account_trade_date_time`(`account_number`,`trade_date_time`),KEY`idx_trade_date_times`(`trade_date_time`)KEY`idx_createtime`(`create_time`),

慢 SQL:

SELECTid,....,creator,modifier,create_time,update_timeFROMstatementWHERE(account_number='XXX'ANDcreate_time>='2022-04-2406:03:44'ANDcreate_time<='2022-04-2408:03:44'ANDdc_flag='C')ORDERBYtrade_date_timeDESC,idDESCLIMIT0,1000;

优化前：SQL 执行超时被 kill 了

SELECTid,....,creator,modifier,create_time,update_timeFROMstatementWHERE(account_number='XXX'ANDcreate_time>='2022-04-2406:03:44'ANDcreate_time<='2022-04-2408:03:44'ANDdc_flag='C')ORDERBYcreate_timeDESC,idDESCLIMIT0,1000;

优化后：执行总行数为:6 行，耗时 34ms。

MySQL使不使用索引与所查列无关，只与索引本身，where条件，order by 字段，group by 字段有关。索引的作用一个是查找，一个是排序。

业务拆分

select*fromorderwherestatus='S'andupdate_time<now-5minlimit500

拆分优化：

随着业务数据的增长 status=’S’的数据基本占据数据的 90%以上，此时该条件无法走索引。我们可以结合业务特征，对数据获取按日期进行拆分。

date=now;minDate=now-10dayswhile(date>minDate){select*fromorderwhereorder_date={#date}andstatus='S'andupdate_time<now-5minlimit500date=data+1}

数据库结构优化

  1. 范式优化：表的设计合理化（符合 3NF），比如消除冗余（节省空间）；

  2. 反范式优化：比如适当加冗余等（减少 join）

  3. 拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘 I/O，一个精心设置的分区可以将数据传输对磁盘 I/O 竞争均匀地分散开。对数据量大的表可采取此方法，可按月建表分区。

SQL 语句优化

SQL 检查状态及分数计算逻辑

  1. 尽量避免使用子查询

  2. 用 IN 来替换 OR

  3. 读取适当的记录 LIMIT M,N，而不要读多余的记录

  4. 禁止不必要的 Order By 排序

  5. 总和查询可以禁止排重用 union all

  6. 避免随机取记录

  7. 将多次插入换成批量 Insert 插入

  8. 只返回必要的列，用具体的字段列表代替 select * 语句

  9. 区分 in 和 exists

  10. 优化 Group By 语句

  11. 尽量使用数字型字段

  12. 优化 Join 语句

大表优化

  • 分库分表（水平、垂直）

  • 读写分离

  • 数据定期归档

原理剖析

MySQL 逻辑架构图：

索引的优缺点

优点

  • 提高查询语句的执行效率，减少 IO 操作的次数

  • 创建唯一性索引，可以保证数据库表中每一行数据的唯一性

  • 加了索引的列会进行排序，在使用分组和排序子句进行查询时，可以显著减少查询中分组和排序的时间

缺点

  • 索引需要占物理空间

  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

  • 当对表中的数据进行增删改查时，索引也要动态的维护，这样就降低了数据的更新效率

索引的数据结构

主键索引

普通索引

组合索引

索引页结构

索引页由七部分组成，其中 Infimum 和 Supremum 也属于记录，只不过是虚拟记录，这里为了与用户记录区分开，还是决定将两者拆开。

数据行格式：

MySQL 有 4 种存储格式：

  1. Compact

  2. Redundant （5.0 版本以前用，已废弃）

  3. Dynamic （MySQL5.7 默认格式）

  4. Compressed

Dynamic 行存储格式下，对于处理行溢出（当一个字段存储长度过大时，会发生行溢出）时，仅存放溢出页内存地址。

索引的设计原则

哪些情况适合建索引

  • 数据又数值有唯一性的限制

  • 频繁作为 where 条件的字段

  • 经常使用 group by 和 order by 的字段，既有 group by 又有 order by 的字段时，建议建联合索引

  • 经常作为 update 或 delete 条件的字段

  • 经常需要 distinct 的字段

  • 多表连接时的字段建议创建索引，也有注意事项

    • 连接表数量最好不要超过 3 张，每增加一张表就相当于增加了一次嵌套循环，数量级增长会非常快

    • 对多表查询时的 where 条件创建索引

    • 对连接字段创建索引，并且数据类型保持一致

  • 在确定数据范围的情况下尽量使用数据类型较小的，因为索引会也会占用空间

  • 对字符串创建索引时建议使用字符串的前缀作为索引

  • 这样做的好处是：

    • 能节省索引的空间，

    • 虽然不能精确定位，但是能够定位到相同的前缀，然后通过主键查询完整的字符串，这样既能节省空间，又减少了字符串的比较时间，还能解决排序问题。

  • 区分度高（散列性高）的字段适合作为索引。

  • 在多个字段需要创建索引的情况下，联合索引优先于单值索引。使用最频繁的列作为索引的最左侧 。

哪些情况下不需要使用索引

  • 在 where 条件中用不到的字段不需要。

  • 数据量小的不需要建索引，比如数据少于 1000 条。

  • 由大量重复数据的列上不要建索引，比如性别字段中只有男和女时。

  • 避免在经常更新的表或字段中创建过多的索引。

  • 不建议主键使用无序的值作为索引，比如 uuid。

  • 不要定义冗余或重复的索引

  • 例如：已经创建了联合索引 key(id,name)后就不需要再单独建一个 key(id)的索引

索引优化之 MRR

例如有一张表 user，主键 id，普通字段 age，为 age 创建非聚集索引，有一条查询语句 select* user from table where age > 18;(注意查询语句中的结果是*)

在 MySQL5.5 以及之前的版本中如何查询呢？先通过非聚集索引查询到 age>18 的第一条数据，获取到了主键 id；然后根据非聚集索引中的叶子节点存储的主键 id 去聚集索引中查询行数据；根据 age>18 的数据条数每次查询聚集索引，这个过程叫做回表。

上述的步骤有什么缺点呢？如何 age>18 的数据非常多，那么每次回表都需要经过 3 次 IO(假设 B+树的高度是 3)，那么会导致查询效率过低。

在 MySQL5.6 时针对上述问题进行了优化，优化器先查询到 age>3 的所有数据的主键 id，对所有主键的 id 进行排序，排序的结果缓存到 read_rnd_buffer，然后通过排好序的主键在聚簇索引中进行查询。

如果两个主键的范围相近，在同一个数据页中就可以之间按照顺序获取，那么磁盘 io 的过程将会大大降低。这个优化的过程就叫做 Multi Range Read(MRR) 多返回查询。

索引下推

假设有索引(name, age), 执行 SQL: select * from tuser where name like ‘张%’ and age=10;

MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到，由于联合索引中包含列，所以存储引擎直接在联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

索引下推使用条件

  • 只能用于range、 ref、 eq_ref、ref_or_null访问方法；

  • 只能用于InnoDB和 MyISAM存储引擎及其分区表；

  • 对存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

索引下推的目的是为了减少回表次数，也就是要减少 IO 操作。对于的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

  • 引用了子查询的条件不能下推；

  • 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

思考：

  1. MySQL 一张表到底能存多少数据？

  2. 为什么要控制单行数据大小?

  3. 优化案例 4 中优化前的 SQL 为什么走不到索引？

总结

抛开数据库硬件层面，数据库表设计、索引设计、业务代码逻辑、分库分表策略、数据归档策略都对 SQL 执行效率有影响，我们只有在整个设计、开发、运维阶段保持高度敏感、追求极致，才能让我们系统的可用性、伸缩性不会随着业务增长而劣化。

参考资料

  1. https://help.aliyun.com/document_detail/311122.html

  2. https://blog.csdn.net/qq_32099833/article/details/123150701

  3. https://www.cnblogs.com/tufujie/p/9413852.html