【MySQL】选错索引的原因分析

最新推荐文章于 2025-02-22 11:05:49 发布
最新推荐文章于 2025-02-22 11:05:49 发布
阅读量643 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： MySQL 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/HiJamesChen/article/details/120546262
本文探讨了MySQL在选择索引时可能出现错误的原因，包括Explain显示的rows值不准确、Candinality的估算误差以及全表扫描的选择。解决方法包括重新统计索引信息、使用`FORCE INDEX`以及优化SQL语句。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

文章目录

一. 背景

1. MySQL怎样选择索引？

  1. Server层的优化器决定使用哪个索引
  2. 根据语句扫描的行数、是否排序、是否存在临时表来判断选择哪个索引

2. Candinality

  1. 一个索引上不同值的个数，称为"基数"，即Candinality。基数越大，索引的区分度越好
  2. MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数，这个统计信息就是索引的"区分度"。
    在这里插入图片描述

3. 采样统计

  1. 采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
  2. 而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候(M是10则表示十分之一)，会自动触发重新做一次索引统计。

二. 原因

1. Explain

最低0.47元/天 解锁文章
MySQL为什么有时候会选错索引？
爱分享的淘金达人
01-02 326
前面我们介绍过索引，你已经知道了在 MySQL 中一张表其实是可以支持多个索引的。但是，你写 SQL 语句的时候，并没有主动指定使用哪个索引。也就是说，使用哪个索引是由 MySQL 来确定的。不知道你有没有碰到过这种情况，一条本来可以执行得很快的语句，却由于 MySQL 选错了索引，而导致执行速度变得很慢？
MySQL——选错索引导致的线上慢查询事故
庄小焱
04-07 440
采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。所以在不加强制索引的情况下，优化器选择了主键索引，因为它觉得主键索引扫描行数少，而且不需要额外的排序操作，主键索引天生有序。这样也有一定的弊端，比如我这个表到了8000w数据，建立索引非常耗时，而且通常索引就有3.4个g，如果无限制的用索引解决问题，可能会带来新的问题。高耦合，这种语句写在代码里，会变得难以维护，如果索引名变化了，或者没有这个索引了，代码就要反复修改。
mysql and 索引吗_mysql竟然也会选错索引
weixin_39842475的博客
01-27 360
我们都知道一张表可以建很多索引，我们在执行一次查询时并没有执行使用哪个索引，是谁在帮助我们决定使用什么索引？是mysql优化器在判断该使用什么索引，优化器判断的有的时候未必是正确的。优化器的逻辑优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。所谓小的代价衡包括扫描的行数、是否使用临时表、是否排序等因素。扫描行数是怎么判断的?MySQL在真正开始执行语句之前，并不能精确地知...
mysql实战10 | MySQL为什么有时候会选错索引？
weixin_34375251的博客
06-14 322
前面我们介绍过索引，你已经知道了在 MySQL 中一张表其实是可以支持多个索引的。但是，你写 SQL 语句的时候，并没有主动指定使用哪个索引。也就是说，使用哪个索引是由 MySQL 来确定的。不知道你有没有碰到过这种情况，一条本来可以执行得很快的语句，却由于 MySQL 选错了索引，而导致执行速度变得很慢？我们一起来看一个例子吧。我们先建一个简单的表，表里有 a、b 两个字段，并分别建上索引：CR...
10 | MySQL为什么有时候会选错索引？
weixin_33980459的博客
01-24 172
前面我们介绍过索引，你已经知道了在MySQL中一张表其实是可以支持多个索引的。但是，你写SQL语句的时候，并没有主动指定使用哪个索引。也就是说，使用哪个索引是由MySQL来确定的。 不知道你有没有碰到过这种情况，一条本来可以执行得很快的语句，却由于MySQL选错了索引，而导致执行速度变得很慢？ 我们一起来看一个例子吧。 我们先建一个简单的表，表里有a、b两个字段，并分别建上索引： CREA...
MySQL 索引选错的情况
DDghost的博客
10-23 883
本文总结了一些 MySQL 索引选错的情况、选错的原因以及对应的解决方案。
MYSQL查询优化器选错了索引怎么办
有时间就写写代码
02-22 637
使用强制指定索引。使用USE INDEX或来影响索引选择。更新表的统计信息或重建索引。优化查询结构，确保相关的列有索引。使用EXPLAIN分析查询计划，了解优化器选择索引的原因。
【MySQL实战45讲10】MySQL为什么有时候会选错索引
RuaaaHaHa的博客
11-25 820
优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。如果使用索引 b 进行查询，那么就是扫描索引 b 的最后 50001 个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描 50001 行。采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。MySQL是通过采样统计来得到索引的基数的，虽然把整张表一行行统计，可以得到精确的结果，但是代价太高了，索引选择了采样统计。
10.MySQL为什么有时候会选错索引？1
08-03
MySQL数据库在执行SQL查询时，会根据查询条件和表结构来选择最合适的索引，以提高查询效率。...当发现优化器选错索引时，应深入分析原因，可能需要调整统计信息、优化查询语句或者考虑更合适的数据库设计和操作策略。
MySQL实战45讲学习笔记：MySQL为什么有时候会选错索引？（第10讲）
weixin_30235225的博客
10-02 890
一 、本节内容概要 前面我们介绍过索引，你已经知道了在 MySQL 中一张表其实是可以支持多个索引的。但是，你写 SQL 语句的时候，并没有主动指定使用哪个索引。也就是说，使用哪个索引是由MySQL 来确定的。 不知道你有没有碰到过这种情况，一条本来可以执行得很快的语句，却由于 MySQL 选错了索引，而导致执行速度变得很慢？ 我们一起来看一个例子吧。 我们先建一个简单的表，表...
Mysql会选错索引
zebphper的博客
06-17 202
实例： delete from t；//整表删除 call idata(); //执行存储过程 select * from t where a between 10000 and 20000; 这里没有用到a索引。为什么？ 优化器选择索引的依据：扫描行数、是否使用临时表、是否排序（order by有没有用上） 基数：索引上不同值的个数，基数越大，索引区分度越好（相当于唯一索引，查到当...
Mysql 索引选择错误
熊孩子的博客
07-26 549
1：MySQL选错索引，啥意思？ 我们认为使用A索引检索的速度会更快的，但是MySQL没有使用(使用了B)，决定使用什么索引是由Server层的优化器来决定的，它也是想选择最佳的方案来检索数据的，不过没有银弹，只要是人写的程序都是存在bug的。 2：MySQL为啥会选错索引？ 优化器认为使用那个索引检索数据的速度比较快是一个需要各种因素综合评估的事情，比如：是否使用临时表、是否排序、扫描的行数多少（采用采样评估得到）、回表的次数等。 索引的创建是非常的耗时的，因为需要真正的建索引的过程，但是删除索引却不需要
MySQL索引（三）：选错索引
热爱数据库的小胖
12-04 1061
优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。
MySQL · 最佳实践 · 一个“异常”的索引选择
weixin_34015566的博客
12-21 141
背景 在处理一个用户性能问题的时候, 发现有一个全表扫描语句, 上下文如下: 这是很奇怪的, Tips: MySQL在执行条件判断时,若参数类型与字段类型不匹配, 则会作类型转换, 符合转换规则的, 转换完成后可以利用索引 而当参数为字符串,字段类型为整型时, 这个转换是成立的, 比如这个case. 因此我们有必要查一下是什么原因. 分析 在优...
MySQL为什么有时候会选错索引
skye_fly的博客
07-28 507
当一条语句中涉及多个索引时，MySQL会选择使用哪个索引，但是有时候它选择的索引执行速度不是最优的（也就是优化器得到的优化结果不为最优） 解决方案1：如果你知道使用哪个索引更好，可以使用**force index(a)**来指定sql语句强行使用某个索引，使用explain来查看sql执行情况 explain select * from t force index(a) where a between 10000 and 20000; 解决方案2：使用analyze table t指令（具体看下面
MySQL 选错索引
记录点点滴滴
03-23 751
优化器选择索引的目的 : 找到最优的执行方案，并用最小的代价去执行语句。方法4 ：与业务开发沟通，删除错误选择的索引，让优化器选择正确的索引。扫描行数是根据统计信息来估算记录数。
MySQL实战45讲——10|MySQL为什么有时候会选错索引？
qq_51931826的博客
03-30 423
MySQL实战45讲——10|MySQL为什么有时候会选错索引？
MySQL 索引 - 选取索引[选错索引]
"代码"的日常搬运
05-22 5802
1.应用场景 弄清楚索引的本质， 以及如何使用索引，能帮助我们解决一些性能问题 2.学习/操作 大家都知道，mysql 一个表中可以创建多个索引，但是在执行一条查询语句的时候，mysql 只能选一个索引，如果我们没有指定 mysql 使用某个索引，那么就是由 mysql 的优化器来决定要使用哪个索引了，然而，mysql 也是会有选错的时候。 前面的文章，我们有介...
MySql为什么选错了索引
yueloveme的博客
10-31 197
案例 线上环境，mysql，`选错索引`sql执行超过20分钟，导致cpu飙升到95% 解决 强制走正确索引-------并非最佳选择 select * from table_name force index(index_name) 复盘 1.mysql是如何选择索引的 扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少，explain 可以看到mysql预估的扫描航速 rows 索引统计值 ，也叫索引区分度，是mysql的一个预估值，可以用sho
MySQL会选错索引，如何解决
最新发布
03-11
<think>嗯，用户想解决MySQL查询优化器选择错误索引的问题。首先我得回忆一下MySQL是怎么选择索引的。根据引用[1]，优化器是基于成本模型来选择索引的，但有时候它可能选错，导致查询变慢。用户需要知道具体的方法和建议来纠正这种情况。 首先，可能要考虑强制使用索引。我记得FORCE INDEX或者USE INDEX可以在查询中指定索引，但用户需要知道具体的语法和适用场景。不过要提醒他们不能滥用，因为索引可能随着数据变化而不再合适。 然后，优化统计信息也很重要。ANALYZE TABLE可以更新统计信息，这可能会帮助优化器做出更好的选择。引用[3]提到key_len显示使用的键长度，统计信息不准确的话，key_len可能有问题，导致优化器误判。 接下来是调整索引结构。可能需要添加更合适的索引，或者删除冗余的，减少优化器的选择困难。比如联合索引或者覆盖索引，这样优化器可能会更倾向于使用它们。 另外，查询重写可能有效。比如将子查询改成连接查询，引用[4]中的例子就是这种情况。或者调整WHERE条件的顺序，让索引更有效。 还有优化器提示，比如像/*+ INDEX() */这样的提示，但需要确认MySQL版本是否支持。同时，调整优化器参数，比如optimizer_switch，可能会有帮助，但需要谨慎处理。 监控和分析执行计划是必须的，用EXPLAIN和EXPLAIN ANALYZE查看实际使用的索引和成本。如果发现选错，就需要进一步分析。 数据分布的问题也很关键。如果某些值的数据分布不均匀，优化器可能错误估计行数，这时候可能需要用直方图或者手动干预。 最后，极端情况下可以删除误导性的索引，或者调整隔离级别，减少锁竞争对索引选择的影响。不过这些都是最后的手段，需要测试后再实施。 用户可能需要具体的步骤，比如如何强制索引，如何分析统计信息，或者如何调整查询语句。还要考虑不同情况下的适用方法，比如数据量大的时候可能需要不同的策略。要确保建议全面且实用，同时引用相关的内容来支持每个建议。</think>### 解决 MySQL 查询优化器选择错误索引的方法和建议 #### 一、问题根源分析 MySQL 查询优化器选择索引时基于成本模型，会评估全表扫描和索引扫描的成本，选择成本最低的方式[^1]。但在以下场景可能出错： 1. **统计信息不准确**：索引的基数（Cardinality）或数据分布统计不更新，导致成本估算偏差[^3]。 2. **复杂查询条件**：涉及多列、范围查询或函数时，优化器难以准确估算过滤性。 3. **索引冗余或冲突**：存在多个候选索引时，优化器可能误判最优选择[^2]。 --- #### 二、解决方案与建议 ##### 1. **强制指定索引** 通过 `FORCE INDEX` 或 `USE INDEX` 显式指定索引： ```sql SELECT * FROM table_name FORCE INDEX (index_name) WHERE condition; ``` - **适用场景**：明确知道某个索引更优且长期稳定。 - **风险**：数据分布变化后可能失效，需定期验证。 ##### 2. **更新统计信息** 手动更新表的统计信息： ```sql ANALYZE TABLE table_name; ``` - **原理**：优化器依赖 `STATISTICS` 表中的数据分布信息，更新后可能修正成本估算。 ##### 3. **优化索引结构** - **删除冗余索引**：减少优化器的选择干扰。 - **创建覆盖索引**：包含查询所需的所有字段，减少回表操作。例如： ```sql CREATE INDEX idx_covering ON table_name (col1, col2, col3); ``` ##### 4. **查询重写优化** - **简化条件**：避免在 WHERE 子句中使用函数或复杂表达式。 - **拆分复杂查询**：将子查询转换为连接查询（如引用[4]中的优化方法）。 ##### 5. **使用优化器提示** 通过注释语法提示优化器： ```sql SELECT /*+ INDEX(table_name index_name) */ * FROM table_name WHERE condition; ``` - **注意**：需确认 MySQL 版本支持（8.0 及以上）。 ##### 6. **调整优化器参数** 修改 `optimizer_switch` 参数，关闭某些优化策略： ```sql SET optimizer_switch = 'index_merge=off'; ``` - **适用场景**：优化器错误选择索引合并（Index Merge）时。 ##### 7. **监控与验证** 使用 `EXPLAIN` 和 `EXPLAIN ANALYZE` 对比不同索引的执行计划： ```sql EXPLAIN SELECT * FROM table_name WHERE condition; ``` - **关注字段**：`possible_keys`（候选索引）、`key`（实际选择索引）、`rows`（估算扫描行数）[^3]。 ##### 8. **处理数据倾斜** - **直方图统计**（MySQL 8.0+）： ```sql ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name; ``` - **手动干预**：对分布不均的字段使用条件过滤或分片查询。 --- #### 三、关键操作流程图 ``` 更新统计信息 → 分析执行计划 → 优化索引/查询 → 验证性能提升 ``` #### 四、注意事项 - **谨慎删除索引**：先通过 `EXPLAIN` 确认索引是否被使用。 - **隔离测试**：在非生产环境验证优化效果。 - **长期监控**：数据量变化后重新评估索引策略。 ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值