MySQL数据库修改小众参数解决大众问题

俊才

发布于 2025-03-24 16:57:07

6100

代码可运行

文章被收录于专栏：数据库干货铺数据库干货铺

运行总次数：0

代码可运行

MySQL数据库中的SQL执行的时候经常会遇到未按预期走索引从而导致SQL执行时间长的情况出现。本文通过实际案例演示如何通过不修改SQL脚本而是通过修改数据库的参数来解决的案例。

1. 基础信息

数据库版本：MySQL5.7.30 (percona分支)

表结构信息如下

因包含字段较多，只截取部分重要字段
CREATE TABLE `tb1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  c3 varchar(50) NOT NULL COMMENT '',
  c1 varchar(20) NOT NULL COMMENT '',
  c2 varchar(30) NOT NULL COMMENT '',
  c4 tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  c6 datetime NOT NULL COMMENT '',
  c5 datetime NOT NULL COMMENT '',
  c7 varchar(10) DEFAULT '' COMMENT '',
  'c20' text ,
  PRIMARY KEY (`id`),
  KEY `idx_c1_c2` (c1,c2) USING BTREE,
  KEY `idx_c3` (c3),
  KEY `idx_c1_c4` (c1,c4),
  KEY `idx_c1_c5` (c1,c5),
  KEY `idx_c6_c7_c4` (c6,c7,c4) USING BTREE,
  KEY `idx_c7_c2_c6` (c7,c2,c6)
) ENGINE=InnoDB AUTO_INCREMENT=76579517 DEFAULT CHARSET=utf8

索引统计信息如下

+------+-----------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table| Non_unique| Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb1 |          0 | PRIMARY      |            1 | id          | A         |    32237890 |     NULL | NULL   |      | BTREE      |         |               |
| tb1 |          1 | idx_c1_c2    |            1 | c1          | A         |      246510 |     NULL | NULL   |      | BTREE      |         |               |
| tb1 |          1 | idx_c1_c2    |            2 | c2          | A         |      558882 |     NULL | NULL   |      | BTREE      |         |               |
| tb1 |          1 | idx_c3       |            1 | c3          | A         |    32237890 |     NULL | NULL   |      | BTREE      |         |               |
| tb1 |          1 | idx_c1_c4    |            1 | c1          | A         |      567771 |     NULL | NULL   |      | BTREE      |         |               |
| tb1 |          1 | idx_c1_c4    |            2 | c4          | A         |      450892 |     NULL | NULL   |      | BTREE      |         |               |
| tb1 |          1 | idx_c1_c5    |            1 | c1          | A         |      260380 |     NULL | NULL   |      | BTREE      |         |               |
| tb1 |          1 | idx_c1_c5    |            2 | c5          | A         |    32237890 |     NULL | NULL   |      | BTREE      |         |               |
| tb1 |          1 | idx_c6_c7_c4 |            1 | c6          | A         |    15031719 |     NULL | NULL   |      | BTREE      |         |               |
| tb1 |          1 | idx_c6_c7_c4 |            2 | c7          | A         |    21172686 |     NULL | NULL   | YES  | BTREE      |         |               |
| tb1 |          1 | idx_c6_c7_c4 |            3 | c4          | A         |    22562920 |     NULL | NULL   |      | BTREE      |         |               |
| tb1 |          1 | idx_c7_c2_c6 |            1 | c7          | A         |        9330 |     NULL | NULL   | YES  | BTREE      |         |               |
| tb1 |          1 | idx_c7_c2_c6 |            2 | c2          | A         |       53700 |     NULL | NULL   |      | BTREE      |         |               |
| tb1 |          1 | idx_c7_c2_c6 |            3 | c6          | A         |    22523070 |     NULL | NULL   |      | BTREE      |         |               |
+------------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

实际数据量约4千万。

已出现的慢SQL,最大耗时超过10mins

 select a.* from tb1 a where a.c1 = '123' and c4 in (0, 3) and c5 >=DATE_SUB('2025-03-21 14:40:14', INTERVAL 15 DAY) order by id  limit 100;

执行计划如下

 +----+-------------+-------+------------+-------+--------------------------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys                   | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------------------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | index | idx_c1_c2,idx_c1_c4,idx_c1_c5   | PRIMARY | 8       | NULL | 21978 |     0.11 | Using where |
+----+-------------+-------+------------+-------+---------------------------------+---------+---------+------+-------+----------+-------------+

2. 原因分析

简而言之以上SQL不走其他索引的原因如下：

主键索引通常是聚集索引，在InnoDB中，表的数据是按照主键顺序存储的。当执行ORDER BY id时，优化器可能认为使用主键索引可以避免额外的排序，因为数据已经按主键顺序存储了。所以如果查询中带有ORDER BY主键字段，优化器可能会倾向于使用主键索引，尤其是当有其他条件过滤后，如果结果集较小，可能更高效。

只不过本次优化器的判断有点小失误，实际上使用上述其他索引（例如idx_c1_c2,idx_c1_c4,idx_c1_c5 ）中的任意一个都比走PRIMARY耗时更低。

3. 常规优化方式

2.1 修改SQL语句

原SQL语句可以有多种修改方式，最简单的方式便是去掉order by id，即改为

 select a.* from tb1 a where a.c1 = '123' and c4 in (0, 3) and c5 >=DATE_SUB('2025-03-21 14:40:14', INTERVAL 15 DAY)  limit 100;

修改后执行计划如下：

 +----+-------------+-------+-----------+-------+--------------------------------+------------+---------+------+-------+----------+-------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                   | key       | key_len | ref  | rows  | filtered | Extra                               |
+----+-------------+-------+------------+-------+---------------------------------+-----------+---------+------+-------+----------+-------------------------------------+
|  1 | SIMPLE      | a     | NULL       | range | idx_c1_c2,idx_c1_c4,idx_c1_c5   | idx_c1_c4 | 63       | NULL |158207 |    33.33 | Using index condition; Using where|
+----+-------------+-------+------------+-------+---------------------------------+-----------+---------+------+-------+----------+-------------------------------------+

可见修改后执行计划明显变优。

当然也可以有其他的优化方式，例如忽略主键索引、强制走其他索引等，但是选择顺位相对靠后一点。

2.2 修改索引

还有一种方式是修改索引，这也是比较常用的方式，例如添加一个c1_c4_c5的组合索引

alter table tb1 add key idx_c1_c4_c5(c1,c4,c5);

修改后原SQL即使不修改也会走此组合索引，效率也会提升的更明显。

但是：如果数据量很大时（例如本表），添加索引耗时较久，且会导致数据库IO加大，主从延迟等情况。如需操作可以使用pt-osc等工具在业务低谷时进行。

另外，在MySQL8.0中，还可以修改索引的可见或隐藏来解决一些问题，本案例不适用。

2.3 归档数据

因本案例的表部分数据可以归档，因此可以归档数据，降低本表数据量来解决

2.4 参数调整

optimizer_switch ：常规调整的参数是optimizer_switch ，例如关闭index_merge，打开mrr、关闭batched_key_access等。本案例通过尝试均未能改变执行计划

sort_buffer：当sortbuffer不足时，可以调整sort buffer解决，本案例依旧未生效。

max_length_for_sort_data： 修改max_length_for_sort_data参数，也是为了解决排序问题（MySQL8.0此参数在实际优化过程中有变化，此处不再赘述）

当然还有其他的参数也可以调整进行尝试，此处省略

3. 本案例主角：max_seeks_for_key

参数简介：

max_seeks_for_key通过限制优化器假设的索引扫描最大搜索次数，间接控制查询计划的选择。例如，即使某个索引的实际基数（cardinality）较低（即重复值较多），若将此参数设置为较低值（如100），优化器会认为“通过索引最多只需100次键值搜索即可完成查询”，从而更倾向于选择索引扫描而非全表扫描。其默认值很大，相当于优化器完全依赖索引的统计信息（如基数）估算扫描成本，不对搜索次数做额外限制。

适用场景：

当表中存在低基数字段（如性别字段）或优化器因统计信息不准确而错误选择全表扫描时，通过调整此参数可强制优化器优先使用索引，尤其在以下情况：

索引实际效率高于优化器估算值（例如大表中通过索引快速定位少量数据全表扫描
因磁盘I/O或数据量过大导致性能瓶颈。

本案例调整演示

该参数使用的很小众，但本案例正好适用，例如：

mysql> set max_seeks_for_key=100;
Query OK, 0 rows affected (0.00 sec)

修改后执行计划如下：

 +----+-------------+-------+-----------+-------+--------------------------------+------------+---------+------+-------+----------+---------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                   | key       | key_len | ref  | rows  | filtered | Extra                                             |
+----+-------------+-------+------------+-------+---------------------------------+-----------+---------+------+-------+----------+---------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | range | idx_c1_c2,idx_c1_c4,idx_c1_c5   | idx_c1_c2 | 62       | NULL |524552 |    6.67 | Using index condition; Using where; Using filesort|
+----+-------------+-------+------------+-------+---------------------------------+-----------+---------+------+-------+----------+---------------------------------------------------+

可见，虽然调整后虽然选择的索引依然不是最优的，但是已经相对较快了。优化后执行时间不到1s。

因此可以在添加组合索引及数据归档清理前临时调整该参数临时解决。

想要全局生效需要修改全局参数