慢SQL优化

前言

从过往的项目经历来看，百分之九十的系统卡顿问题都出在数据库层，而数据库层最常见的问题就是慢查询SQL，此篇聊一些慢SQL的优化经验。

MySQL架构

在做SQL优化前得了解MySQL的大概架构

逻辑架构可以分为三层：连接层、服务层和引擎层， 服务层负责SQL语法解析、预处理、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

SQL语句的执行流程可以简单分为以下几个步骤：

1. 连接器：客户端连接到MySQL服务器，连接器负责验证客户端的身份和权限，如果通过验证，就建立一个连接，并从权限表中读取该用户的所有权限信息。

2. 语法分析：在执行SQL语句之前，MySQL需要对SQL语句进行语法分析。语法分析器会检查SQL语句是否符合语法规则，并将其转换为一棵语法树。如果SQL语句不符合语法规则，MySQL将会返回一个错误消息。语义分析是在语法分析之后进行的。它会检查SQL语句是否符合语义规则，并将其转换为一棵语义树。语义分析器会检查表名、列名、数据类型等信息是否正确。如果SQL语句不符合语义规则，MySQL将会返回一个错误消息。

3. 查询优化：在语义分析之后，MySQL会对SQL语句进行查询优化。查询优化器会分析查询语句，并生成一个最优的执行计划。执行计划是指MySQL执行SQL语句的具体步骤，包括使用哪些索引、如何连接表等。

4. 执行SQL语句：在查询优化之后，MySQL会执行SQL语句。执行器会按照执行计划的步骤，逐步执行SQL语句。执行器会根据查询语句，从磁盘读取数据，并将其存储在内存中。然后，执行器会对数据进行排序、分组、聚合等操作，最终生成查询结果。

写出规范的SQL

• select语句中需要哪些字段就查哪些字段，避免select *；

• 如果查询的字段有索引，where语句中的索引条件尽量覆盖该索引字段，避免回表二次查询；

• 尽量使用单表查询，数据关联通过代码层in条件查询后转成map映射，设计表的时候也应该考虑数据适当冗余存储，避免一次查询需要跨多个表；

• 如果是避免不了的多表关联，尽量不要超过3张表，关联语句使用原生SQL, 不要在代码层通过ORM框架映射，难以维护和优化；

• 涉及到分组count的SQL语句, 可以把分组条件拆出来，并且加上日期等条件， 循环分批次查询，避免分组，提高效率；

• 全表count的需求难以避免，可以建立专门的表存储对应表前一天的count值，实时数据只需要用历史数据加上当日增量的数据即可得到准确值。

建立合理的索引

有些字段在业务开发过程中就发现频繁使用的时候可以提前建立索引，而大部分索引其实是业务上线一段时间，表中积累一定数据量后我们结合慢SQL监控，SQL执行频次新增的。

• 只对查询特别频繁，核心业务sql建议索引，其他如后台统计，执行次数较少的需求通过把数据导入数仓、大数据平台统计；

• where条件中有多个查询条件时，考虑给这些字段建立联合索引，索引的顺序遵循区分度越高的排在越前面；

• 索引的数量不能太多，特别是增删比较频繁的表，过多的索引会影响数据写入性能。上限标准和数据库对应的硬件配置，业务特点有关系，具体根据实际业务表现来判断，当表里索引已经很多了，而新的业务还需要建立新的索引，老的索引又都不能减少时，考虑把表横向拆分或者修改业务的实现方式；

• 对于区分度较低的字段，如果数据基数足够大，该字段每个值对应的数据分布比较均衡，也可以考虑建立索引，效率也会高很多。

• 通过查看表索引的数据分布情况，判断索引构建是否合理

show index from t_c_market_order;

可以看到，表的索引信息会展示13个相关字段，简单解释一下各字段含义：

Table：表名

Non_unique：是否是唯一索引

Key_name：索引名称，如果名字相同则表明是同一个索引，而并不是重复，比如上图中的第三、四、五条数据，索引名称都是idx_market，其实是一个联合索引

Seq_in_index：索引中的列序列号，从1开始。上图中的三、四、五条数据，Seq_in_index分别是1、2、3，就是表明在联合索引中的顺序，我们就能推断出联合索引中索引的前后顺序

Column_name：索引的列名

Collation：指的是列以什么方式存储在索引中，大概意思就是字符序

Cardinality：基数，表示索引中唯一值的数目的估计值。我们知道某个字段的重复值越少越适合建索引，所以我们一般都是根据Cardinality来判断索引是否具有高选择性，如果这个值非常小，那就需要重新评估这个字段是否适合建立索引。

Sub_part：前置索引的意思，如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL

Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案

Null：如果列含有NULL，则含有YES

Index_type：索引类型

Comment Index_comment： 备注信息

SQL优化

查询慢SQL信息

• 通过EXPLAIN 关键字查询SQL的执行计划

EXPLAIN SELECT * from t_c_market_order where `status` = 0;

EXPLAIN SELECT * from t_c_market_order where order_no = '202205290817230486';

我们可以看到，一个执行计划会展示12个相关的字段,下面我们对主要字段以及这些字段常见的值进行解释:

id

含义：是一组数字，表示的是查询中执行select子句或者是操作表的顺序

规则：

1. id不相同的，id值越大越先执行

2. id相同的,从上到下顺序执行

select_type

常见的值以及描述如下

table

涉及的表

type（关键字段）

这列很重要,显示了连接使用哪种类型,有无使用索引， 常见的值从最好到最差如下 system > const > eq_ref > ref > range > index > all

各值的描述如下

possible_keys

表示可能用到的索引

key

表示最终用到的索引

key_len

用到的索引字段长度，这项可以为我们建立索引时设置的长度做一个指导

ref

显示索引的哪一列被使用了，有时候会是一个常量：表示哪些列或常量被用于查找索引列上的值

rows

需要扫描的行数，扫描数据越少，效率越高，上面第二个示例 根据唯一索引查询只需扫描一行即得结果。

filtered

查询结果的行数占上面rows的百分比

Extra(关键字段)

这一列也很重要,主要展示额外的信息说明,能够给出让我们深入理解执行计划进一步的细节信息

常见的值及描述如下

不走预期索引的原因

• where条件查询条件的值类型和索引类型不一致，比如索引的类型是int，而条件中的值是字符串类型，数据库会将该值隐式转换成int类型而导致无法使用索引；

• where条件中的字段顺序不符合最左前缀匹配规则，高版本mysql优化器会自动优化查询字段顺序以匹配索引顺序，低版本可能会有问题，为了规范最好是按照索引建立的顺序来写；

• sql优化器计算出的全表扫描成本低于走索引，或者两个索引存在冲突，其中一个索引计算出的代价小于我们预期的指定索引，这个时候可以使用 force index(索引名称）强制走指定索引，也可以考虑将冲突索引做一个合并优化。

优化原则

1. 建立合适索引字段，避免索引冲突，减少优化器计算索引成本消耗的时间；

2. type字段最好是ref级别以上；

3. Extra列避免出现Using temporary、Using filesort（文件排序）；

优化思路

针对原则1和2

• 查询条件后过滤掉的数据尽量多， 组合索引尽可能全覆盖

• 避免重排序，索引默认已排序过，排序字段尽可能使用索引字段

• 避免回表

针对原则3

创建如下表

用户表

CREATE TABLE `t_user`  (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `group_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name`(`name`) USING BTREE,
  INDEX `idx_group_id`(`group_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1240277101395107842 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

分组表

CREATE TABLE `t_group`  (
  `id` bigint(20) NOT NULL,
  `group_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

创建存储过程插入50w条测试数据

DELIMITER $$

CREATE PROCEDURE InsertTestData()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE vName VARCHAR(255);
    DECLARE vAge INT;
    DECLARE vGroupId BIGINT;

    START TRANSACTION;
    WHILE i <= 500000 DO
        SET vName = CONCAT('User', i);
        SET vAge = FLOOR(20 + (RAND() * 40)); -- 随机生成20到60之间的年龄
        SET vGroupId = FLOOR(1 + (RAND() * 100)); -- 随机生成1到100之间的group_id

        INSERT INTO t_user (name, age, group_id)
        VALUES (vName, vAge, vGroupId);

        SET i = i + 1;

        -- 每插入1000条记录提交一次，防止长事务占用过多资源
        IF (i MOD 1000) = 0 THEN
            COMMIT;
            START TRANSACTION;
        END IF;
    END WHILE;
    COMMIT;
END$$

DELIMITER ;


call InsertTestData();

Using filesort

1. order by 的字段不在where条件中

下面这条sql会出现Using filesort

EXPLAIN select * from t_user where group_id = 67 and age = 49 order by name;

而下面这条sql不会

EXPLAIN select * from t_user where group_id = 67 and age = 49 order by group_id;

2. 组合索引跨列

给t_user表创建索引(name,age,group_id), 删除name索引

下面这条sql排序会出现Using filesort

EXPLAIN select * from t_user where name= 'User1' order by group_id;

而下面这条不会

EXPLAIN select * from t_user where name= 'User1' order by age;

因为第一条语句order by跳过了age，使用group_id, 没法直接使用已经排序好的组合索引

3. Mysql8.0以前，Group by会默认根据作用字段（Group by的后接字段）对结果进行排序，在能利用索引的情况下，Group by不需要额外进行排序操作；但当无法利用索引排序时，Mysql优化器就不得不选择通过使用临时表然后再排序。当临时结果集的大小超出系统设置临时表大小时，Mysql会将临时表数据copy到磁盘上面再进行操作，语句的执行效率会变得极低, 所以需要在语句后面加上order by null取消排序。 8.0以后这个隐式排序被弃用。

Using temporary

删除组合索引，新增name、age两个索引

临时表的出现对性能影响是很大的,主要会出现在以下情况中

1. 分组字段不在where条件后面, 下面这条sql会出现Using temporary

EXPLAIN SELECT count(1), age from t_user where group_id = 67 and name= 'User1' group by age

2. 分组字段在where条件后面，但是最终走的索引不是group by后的字段索引，下面这条sql会出现Using temporary

EXPLAIN SELECT count(1), age from t_user where  name = 'User1' and age in (67,15) group by age

而下面这条sql不会

EXPLAIN SELECT count(1), age from t_user where  name = 'User1' and age in (67) group by age

对比2中的两条sql，第一条age字段后面跟的in条件有两个值，第二条只有一个，由于成本估算导致的索引选择不同，就决定了临时表的使用与否，所以尽量保证where查询走的索引字段和group by字段相同。

2. 表连接中，order by的列不是驱动表中的

如下sql会使用临时表

explain select * from t_user t1 left join t_group t2 on t1.group_id = t2.id order by t2.id;

因为此处是t1驱动t2，根据join的原理，loop的顺序是走t1，而排序需要走t2，排序只能在loop结束后使用创建临时表完成，所以连接查询的时候，排序字段使用驱动表的字段。

总结

以上记录了一些SQL优化的经验， SQL优化是一项比较常见，也比较精细的工作，不同业务场景会遇到不一样的问题，处理方法也不一样， 没有统一的银弹，只有根据具体问题进行分析、测试，才能得出最为理想的结果。