MySQL45讲 第十讲 MySQL为什么有时候会选错索引？

时间：2024-11-08 07:13:02

文章目录

  • MySQL45讲 第十讲 MySQL为什么有时候会选错索引？
    • 一、问题引入
    • 二、案例分析
      • 案例一
      • 案例二
    • 三、索引选择异常的处理方法
      • 1. 使用`force index`强行选择索引
      • 2. 修改语句引导优化器
      • 3. 新建或删除索引
    • 四、索引统计更新机制及解决索引统计不准确问题
    • 五、总结与思考

MySQL45讲 第十讲 MySQL为什么有时候会选错索引？

一、问题引入

在 MySQL 中，索引选择由优化器负责，其目标是以最小代价执行语句，但有时会选错索引，导致执行速度变慢。

二、案例分析

案例一

  1. 建表与数据插入
    • 创建表t，包含id、a、b字段，分别建立主键索引和a、b字段的普通索引。
    • 使用存储过程插入 10 万行数据，取值按整数递增。
  2. 初始查询情况
    • 执行select * from t where a between 10000 and 20000;，优化器选择索引a，扫描行数 10001 行，执行情况符合预期。
  3. 数据变更后的问题
    • 在session A开启事务，session B删除数据后重新插入 10 万行数据。
    • 此时session B再次执行上述查询，未选择索引a，而是走了全表扫描，扫描 10 万行，执行时间 40 毫秒。而使用force index(a)强制使用索引a时，扫描 10001 行，执行 21 毫秒。
  4. 原因分析
    • MySQL 通过索引的 “区分度”（基数）估算扫描行数，基数越大区分度越好，但采样统计方法导致基数不准。
    • 优化器预估使用索引a时每次从索引a取值后要回主键索引查整行数据，计算代价后认为直接扫描主键索引更快，然而实际执行时间表明此选择并非最优。

案例二

  1. 查询语句
    • select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
  2. 预期与实际索引选择
    • 从条件分析使用索引a扫描行数少应更快，但优化器选择了索引b，扫描行数预估为 50198 行。
  3. 原因分析
    • 优化器认为使用索引b可避免排序（b本身有序），即使扫描行数多也判定代价更小。

三、索引选择异常的处理方法

1. 使用force index强行选择索引

  • 优点：能直接指定索引，使优化器不再评估其他索引执行代价。
  • 缺点：写法不优美，索引改名时语句需修改，可能不兼容其他数据库，且变更及时性差，通常在问题出现后才修改 SQL 语句添加，测试发布过程不够敏捷。

2. 修改语句引导优化器

  • 例如将order by b limit 1改为order by b,a limit 1，使扫描行数成为影响决策的主要条件，诱导优化器选择索引a。但这种修改需根据数据特征和语句逻辑，不具备通用性。

3. 新建或删除索引

  • 新建更合适索引或删除误用索引，但在某些案例中可能难以找到合适的新增索引方法，也可能出现删除不必要索引后优化器重新选择正确索引的情况。

四、索引统计更新机制及解决索引统计不准确问题

  1. 索引统计方式
    • MySQL 采用采样统计方法，InnoDB 默认选择N个数据页统计不同值取平均值后乘以索引页面数得到基数。当变更数据行数超过1/M时自动触发重新统计。
    • 可通过设置innodb_stats_persistent参数选择统计信息存储方式，on表示持久化存储，默认N = 20，M = 10；off表示只存储在内存中，默认N = 8，M = 16。
  2. 解决索引统计不准确问题
    • 使用analyze table命令重新统计索引信息，可解决因索引统计不准确导致的优化器选错索引问题，但优化器还会综合其他因素判断。

五、总结与思考

  1. 优化器可能因索引统计信息不准确或其他因素选错索引，对于索引统计问题可用analyze table解决，对于其他误判情况可采用force index、修改语句或调整索引等方法。

相关文章