日志：MySQL 百万级数据表使用了错误索引的解决方法

最新推荐文章于 2025-03-21 20:08:36 发布

原创最新推荐文章于 2025-03-21 20:08:36 发布 · 717 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #索引 #mysql优化

索引同时被 2 个专栏收录

1 篇文章

订阅专栏

mysql

1 篇文章

订阅专栏

本文讲述了作者在处理线上SQL运行缓慢问题时，发现由于错误的索引使用导致查询效率低下。通过尝试调整Cardinality计算、设置max_seeks_for_key变量以及使用FORCE INDEX，最终发现表结构不合理是问题根源。通过字段重构，有效解决了索引误用问题，大幅减少了数据量。

开门见山

今天偶然发现线上一SQL运行缓慢

最慢的时候达到了 30.8 秒。

SQL代码段：

SELECT
	id,
	injection_machine_id,
	value_code,
	`value`,
	sample_time
FROM
	`表` 
WHERE
	injection_machine_id = 26100 
ORDER BY
	sample_time DESC 
	LIMIT 1

经查，数据库中存在完全符合的索引 idx_machine_time（索引顺序为：injection_machine_id，sample_time），但使用的却是 idx_time（单列 sample_time）。

注意：以下尝试在你的机器上都可以尝试一下，对你可能是有用的噢

尝试1

于是我将该表导出（包含结构和数据）放到了我本地测试服务器，EXPLAIN 同样的 SQL，并查看了二者的索引详情后，发现二者的 Cardinality 有较大差别：

Cardinality 的值表示该索引列在整张表中唯一的值的个数，即：
select count(distinct `列`) from `表`
当存在多个可用索引时，且某索引的 Cardinality 除以表行数接近于1（或者说 Cardinality 接近表行数）时，该索引有最大可能被命中，

但由于技术原因，在 innodb 引擎下的 Cardinality 值并不准确（在 MyISAM 引擎下准确）。

* Cardinality 本身的计算公式不在此详述。

默认情况下，计算 Cardinality 的数据页页数有 20 页，设置大一些让 MySQL 采样时可以更准确地计算 Cardinality，具体操作如下：

-- 先查看 innodb_stats_persistent，看是 ON 还是 OFF
show variables like 'innodb_stats_persistent'

-- ON 的时候查看这个，默认是20
show variables like 'innodb_stats_persistent_sample_pages'
-- 设大一点
set global innodb_stats_persistent_sample_pages=40

-- OFF 的时候查看这个，默认是8
show variables like 'innodb_stats_transient_sample_pages'
-- 设大一点
set global innodb_stats_transient_sample_pages=16

接下来要运行以下 SQL，Cardinality 才会触发重新计算：

-- innoDB
analyze table `表`

-- MyISAM
shell> myisamchk -a

innodb_stats_persistent_sample_pages / innodb_stats_transient_sample_pages 不宜设置太大，会导致自动或手动执行 analyze table 变慢

结果

没变化！失败了！

尝试2

官方文档 https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html 中有写道：

Google翻译译文：

也就是说可以设置叫做“max_seeks_for_key”的全局系统变量，可以调节MySQL优化器使用索引概率。

默认值

Windows	`4294967295`
其它系统 32位	`4294967295`
其它系统 64位	`18446744073709551615`

-- 设置最多扫描1000次键，值越小越有可能使用索引
set global max_seeks_for_key=1000

先抛开 max_seeks_for_key 不看，MySQL 优化器一开始先决定你的 SQL 适不适合走索引；如果优化器认为走索引代价更大，那么就会扫描全表。

max_seeks_for_key 的翻译是”查找键的最多次数“，指的是如果扫描全表时，扫描行数已达若干行仍未找到匹配行，那么就使用索引扫描。

但显然这个对我的情况是没有效果的，因为我是”FULL_INDEX_SCAN“（全索引扫描），而不是全表扫描，我的情况是使用了错误的索引😂。

尝试3

官方还提出，如果ANALYZE TABLE仍然不能解决索引用错 / 索引未使用的情况，还有一种方法：

FORCE INDEX

是的，没错，这是最后通牒，又直接又好用。

而且正是因为我的表结构和条件值的不同导致的条数差异极大，会导致 Cardinality 本身计算的结果不准确→导致比较容易选错索引→最终导致我不得不使用FORCE INDEX：

官方关于 Cardinality 描述原文：

黄色记号笔的意思是如果查询的值不一样导致匹配行数差异大的话，建议使用”索引提示“（就是FORCE INDEX等）

除了 FORCE INDEX 之外，参考 https://dev.mysql.com/doc/refman/8.0/en/index-hints.html 查看所有的”索引提示“

最终解决方法

解决方法1

SELECT
	id,
	injection_machine_id,
	value_code,
	`value`,
	sample_time
FROM
	`表` 
FORCE INDEX ( idx_machine_time ) 
WHERE
	injection_machine_id = 26100 
ORDER BY
	sample_time DESC 
	LIMIT 1