记一次 SQL 优化

MySQL 生产记录
发布日期:   2024-06-26
更新日期:   2024-10-10
文章字数:   2.4k
阅读时长:   10 分
阅读次数:  

1 背景

我们的数据库中配置了一套慢 SQL 的监控(这里存在 SQL 本身不慢, 但是触发某些场景, 比如 filesort 等也会被采集), 会不定时的输出一批需要排查的 SQL, 下面挑了几条比较有意思的进行分享。

2 table_1

表结构:

CEATE TABLE `table_1` (
    
    column_1,
    column_2,
    column_3,
    column_4,
    time_column_5

    KEY `idx_001`(`column_3`),
    KEY `idx_002`(`column_2`, `column_3`, `time_column_5`)
)

SQL:

select column_1 from table_1 where column_2 and column_3 and time_column_5 > ? order by time_column_5 desc

执行计划:

select_type table partitions type possible_keys key key_len ref rows filtered Extra
SIMPLE table_1 range idx_001,idx_002 idx_002 133 1 5 Using index condition

通过分析执行过程, 可以发现这条 SQL 本身已经是 range 同时基本走到符合条件的索引 idx_002 了。
因为这条 SQL 只需要在查询出一个 column_1 字段, 如果还想再进一步优化的话, 可以直接将这个字段添加到 idx_002 的索引, 直接让这条 SQL 在索引树中处理, 不回表查询。

修改 idx_002 的索引如下 column_2, column_3, column_1, time_column_4

select_type table partitions type possible_keys key key_len ref rows filtered Extra
SIMPLE table_1 range idx_001, idx_002 idx_002 133 1 5 Using where, Backward index scan, Using index

可以发现 Extra 中, 整条 SQL 为 Using index, 只使用到了索引树, 同时利用到了 MySQL8 的新特性 backward index scan (MySQL 8 对字段倒序排序做的一种优化, 同样是直接在索引树上操作, 不回表处理)。

3 table_2

表结构:

CEATE TABLE `table_2` (
    
    column_1,
    column_2,
    column_3,
    column_4,
    time_column_5

    KEY `idx_001`(`column_3`),
    KEY `idx_002`(`column_2`, `column_3`, `time_column_5`)
    KEY `idx_003`(`column_4`, `time_column_5`)
)

SQL:

select column_1 from table_2 where (column_2 in (?+) or column_4 in (?+)) and time_column_5 > ? and time_column_5 <= ?

执行计划:

select_type table partitions type possible_keys key key_len ref rows filtered Extra
SIMPLE table_2 index_merge idx_001, idx_002, idx_003 idx_002 128128 17907 0.11 Using sort_union(idx_002, idx_003); Using where

index_merge: 分别通过对两个独立的 index 进行过滤之后，将过滤之后的结果聚合在一起，然后在返回结果集
sort_union: 简单理解: 使用到了 or, 回表捞到需要的数据，合并后再排序

column_2 和 column_4 都有各种适合的索引, 尝试通过 union all 将 or 替换掉

select column_1 from table_2 where column_2 in (?+) and time_column_5 > ? and time_column_5 <= ?
union all
select column_1 from table_2 where column_4 in (?+) and time_column_5 > ? and time_column_5 <= ?
select_type table partitions type possible_keys key key_len ref rows filtered Extra
SIMPLE table_2 range idx_002 idx_002 133 6323 1 Using index condition; Using where
SIMPLE table_2 range idx_003 idx_00 133 5 1 Using index condition; Using where

观察 rows 预测扫描的行数少了, 同时 Extra 中切换到了 使用索引 + 回表查询

4 table_3

表结构: 

CEATE TABLE `table_2` (
    
    column_1,
    column_2,
    char_column_3,
    time_column_4

    KEY `idx_001`(`char_column_3`, `time_column_4`)
)

SQL:

select column_1, char_column_3, time_column_4 from table_3 where char_column_3 in (?+) order by time_column_4 desc lmit ?, ?

执行计划:

select_type table partitions type possible_keys key key_len ref rows filtered Extra
SIMPLE table_3 range idx_001 idx_001 128 4 5 Using index condition; Using where; Using filesort

这条 SQL 主要是因为使用到了 filesort。
通过索引 idx_001 可以看出 查询的条件 char_column_3 和 time_column_4 都是在索引里面的, 理而导致 Using filesort 的原因是因为 char_column_3 的条件是 in

尝试将 char_column_3 的条件修改为 =, 执行计划如下

select_type table partitions type possible_keys key key_len ref rows filtered Extra
SIMPLE table_3 ref idx_001 idx_001 128 const 4 5 Using index condition; Using where

in 导致索引中的 time_column_4 失效原因:
索引是有序的, 通过索引找到的数据, 理论上也是有序的。
比如表中当前有数据 (1, 1), (2, 2), (3, 3), (1,5) (1,3)。
通过 char_column_3 in 查询出来的数据为 (1, 1), (1,3), (1,5), (2, 2), (3, 3), 可以发现是按照 char_column_3 排序好了。
但是现在我们需要的是按照 time_column_4 进行排序, 那么就在用 char_column_3 查询出来的数据后再进行多一次排序, 就导致了 filesort 的出现。

尝试去掉 filesort, 建立 idx_02(time_column_4, char_column_3) 的组合索引, 同时强制走这个索引 (通过尝试, 发现 MySQL 的优化器分析走旧索引比较好)

select column_1, char_column_3, time_column_4 from table_3 force index(idx_02) where char_column_3 in (?+) order by time_column_4 desc lmit ?, ?
select_type table partitions type possible_keys key key_len ref rows filtered Extra
SIMPLE table_3 ref idx_001 idx_001 128 const 15 0.33 Using where

可以发现 filesort 去掉了, 但是对应的 rows 查询条数上涨了。
结论: 当前 SQL 暂时这样, 不修改。

5 table_4

表结构: 

CEATE TABLE `table_4` (
    
    column_1,
    column_2,
    column_3
)

SQL: 

select * from table_4 where column_2 = ? and column_3 = ?

执行计划:

select_type table partitions type possible_keys key key_len ref rows filtered Extra
SIMPLE table_4 ALL 128 const 1026 0.2 Using where

走到了 ALL, 整张表数据量也在 900+ 左右, 可能初期设想时不会有那么多数据, 所以没加索引。
现在走动了 ALL, 第一时间想到的是给查询的 2 个字段加上索引, 那么直接加上吗？

先分析一下 column_2 和 column_3 各自的分布。

select count(1), column_2 from table_4 group by column_2
count(1) column_2
10 2
901 3
7 4
1 5
12 6
1 7
1 8
2 9 
select count(1), column_3 from table_4 group by column_3
count(1) column_3
9 1
64 2
861 3

可以发现 column_2 和 column_3 应该都是枚举值 (一开始可能考虑到都是枚举所以没加索引吧)。
回到代码中查看 SQL 的调用链, 发现调用的地方就 2 个, 查询的条件 column_2 主要在 (6,7,8), 而 column_3 则是 2。

虽然平常我们都知道枚举类型的字段不要加索引，因为区分度不高。
但是在某些情况下，还是建议建立索引的。举个例子: 有一张大表, 发送给客户的短信信息和状态, 表中有个字段存储的是当前这条短信是否发送给客户了，0: 未发送, 1: 已发送。
短信发送成功后, 会将状态修改为 1: 已发送。 基于这种情况, 这张大表中的未发送的数据量和远远小于已发送的数据量, 同时平时查询的时候也都几乎是查询未发送的, 这时候就可以给这个枚举值字段加上索引, 因为通过未发送这种情况可以筛选掉很多的数据量。

所以给 column_2 和 column_3 建立一个组合索引, 同时因为 column_2 的区分度更高, 所以将 column_2 放在 column_1 的前面。

6 table_5

表结构: 

CEATE TABLE `table_5` (
    
    column_1,
    column_2,
    column_3

    KEY idx_001(column_1)
)

SQL: 

select * from table_5 where column_1 = ? and column_2 = ? order by convert(column_1 using gbk)

执行计划:

select_type table partitions type possible_keys key key_len ref rows filtered Extra
SIMPLE table_5 ref idx_001 idx_001 152 const 1 5 Using index condition; Using where; Using filesort

同时是因为 filesort, 这里可以将排序的字段 column_1 加入到 idx 索引中。
但是因为 column_1 使用到了索引, 最终只会导致加的这个字段不起作用, 那么

  1. 去掉这个函数, 排序而已, 对数据的准确性没有影响, 但是排序的的顺序和生产的不一致
  2. 将这个排序移到代码中进行

7 table_6

CEATE TABLE `table_6` (
    
    column_1,
    column_2,
    column_3,
    is_deleted

    KEY idx_001(column_1, is_deleted),
    KEY idx_002(column_2, is_deleted)
)

当前表数据量 80155064, 使用了逻辑删除, 未删除:已删除 = 5:3 左右 

select column_1, column_2, column_3 from table_6 where column_1 in (?+) and is_deleted = 0
select_type table partitions type possible_keys key key_len ref rows filtered Extra
SIMPLE table_6 range idx_001 idx_001 303 2 10 Using index condition; Using where 
select column_1, column_2, column_3 from table_6 where column_2 in (?+) and is_deleted = 0
select_type table partitions type possible_keys key key_len ref rows filtered Extra
SIMPLE table_6 range idx_002 idx_002 153 2 10 Using index condition; Using where

2 条 SQL 的执行时间都是秒级别, 但是通过分析可以发现走的索引都很准确了, 通过调整索引的方式不太合适了。
那么有别的方式优化吗? 逻辑删除 –> 已经删除的数据还有保存的意义吗? –> 清除(或迁移到另一张表), 减轻表的数据量, 也能达到优化的效果。

7.1 本地尝试清除数据

7.1.1 初始数据

总数据量 未删除 已删除
10000001 5002602 4997399

表内存情况

Name Engine Version Row_formant Rows Avg_row_length Data_length Max_data_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options
table_6 InnoDb 10 Dynamic 9657234 160 1547698176 (1476.00M) 3801726976 (3625.60M) 4194304 10000003 2024-06-20 14:50:34 2024-06-20 16:27:10 NULL utf8_general_ci NULL

备注:
date_length: 数据的大小
index_length: 索引的大小
data_free: 碎片空间的大小

7.1.2 继续往表追加数据

向表中追加 1529500 条数据

总数据量 未删除 已删除
11529501 5766861 5762640
Name Engine Version Row_formant Rows Avg_row_length Data_length Max_data_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options
table_6 InnoDb 10 Dynamic 11422530 156 1785724928 (1703.00M) 0 4484366336 (4276.62M) 5242880 11529503 2024-06-20 14:50:34 2024-06-20 16:53:30 NULL utf8_general_ci NULL

7.1.3 尝试清除表中一半逻辑删除的数据

DELETE from table_6 where id <= '5764751' and is_deleted = 1
总数据量 未删除 已删除
8648665 5766861 2881804
Name Engine Version Row_formant Rows Avg_row_length Data_length Max_data_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options
table_6 InnoDb 10 Dynamic 8610990 207 1785724928 (1703.00M) 0 4484366336 (4276.62M) 42991616 11529503 2024-06-20 14:50:34 2024-06-20 17:14:58 NULL utf8_general_ci NULL

可以看到虽然删除了表中的部分数据, 但是实际占用的空间没有变化。 这时 InnoDB 内部的设计, 将删除的数据的位置标记为删除的, 后续有新的数据新增进来时, 就复用这个位置。
如果要强制进行空间的整理, 可以通过 alter table 表明 engine=innodb; 的方式进行整理, 但是这个会很耗时。

7.1.4 清除表中所有逻辑删除的数据

DELETE from table_6 where is_deleted = 1
总数据量 未删除 已删除
5766861 5766861 0

通过 alter 手动整理空间

alter table table_6 engine=innodb;

表内存情况

Name Engine Version Row_formant Rows Avg_row_length Data_length Max_data_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options
table_6 InnoDb 10 Dynamic 5713155 178 1021296640 (973.98M) 0 1571340288 (1498.54M) 3145728 11529503 2024-06-20 14:50:34 NULL NULL utf8_general_ci NULL

完整的清除数据和整理了碎片空间。

注: 最终落实到生产(保留前 3 个月的数据, 将 3 个月前的数据迁移到另一张表)

  目录