MySQL自动索引选择机制与优化方法（4/16）

十里桃花舞丶

发布于 2024-04-12 09:08:44

2650

发布于 2024-04-12 09:08:44

文章被收录于专栏：桥路_大数据桥路_大数据

MySQL自动索引选择

MySQL会在某些情况下选择错误索引导致查询性能下降。例如不断地删除历史数据和新增数据的场景。

因为索引选择是由MySQL的优化器决定的，优化器的目标是找到最优的执行方案，最小化执行代价。

在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。除此之外，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

优化器索引选择逻辑

**首先考虑索引基数：**索引基数决定了要不要使用索引，如果索引基数太小，就会弃用索引。当索引基数比较大的时候，要不要走这个索引，还得看扫描行数，回表，子查询等等，最后优化器根据预估的成本决定是否走这个索引。

索引的基数（Cardinality）通常被认为是索引的“区分度”。基数指的是索引值的唯一性的度量，即索引列中不同值的数量。基数高意味着列中的值更加多样化，索引能够更好地区分数据行。相反，基数低则意味着列中的值有很多重复，索引在区分数据行方面的效果就不佳。

一个索引上不同的值越多，这个索引的区分度就越好。例如一个字段是布尔型值只有0,1，另一个字段是枚举型有10个枚举值。分别在两个字段上建索引，布尔型字段索引会把数据分成两部分，枚举型会把数据分成十份，根据索引查找的时候，布尔型选择了一个排除了一半，枚举型选一个会排除9/10，所以枚举型区分度更好。

可以使用 show index 方法，看到一个索引的基数。

但这个基数并不准确，是通过采样进行统计的。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

设置为 on 的时候（默认），表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。当M为20时，变更行数超过1/20会重新进行索引统计。

**然后再看扫描行数，**在MySQL中，预计扫描行数（rows）是优化器在执行查询之前估算的一个值，用来表示执行特定查询语句可能需要检查的数据行数。这个估算对于优化器选择最佳的执行计划至关重要，因为它影响了查询的性能，从而决定查询会走哪个索引。以下是确定预计扫描行数的几个关键因素：

数据分布：优化器会考虑数据的分布情况。如果数据分布不均匀，优化器可能无法准确估算扫描行数，因为它依赖于均匀分布的假设。
表的大小：表的总行数也会影响扫描行数的估算。如果表很大，优化器可能会倾向于高估扫描行数，因为它假设数据分布较为均匀。
查询条件：查询条件的复杂性也会影响扫描行数的估算。简单的范围查询（如a between 1 and 100）通常比复杂的条件（如多列查询和复杂的JOIN操作）更容易估算。
历史执行信息： MySQL可以存储历史执行信息，用于优化器的决策。如果之前的执行表明实际扫描行数与统计信息有显著差异，优化器可能会调整其估算。
参数设置： MySQL中的一些参数，如innodb_stats_on和innodb_stats_persistent，会影响统计信息的更新和存储方式，从而间接影响扫描行数的估算。
优化器的启发式方法：优化器还会使用一些启发式方法来估算扫描行数，这些方法基于经验规则和历史数据。

当优化器估算出扫描行数后，它会根据这个估算值来选择使用全表扫描、索引扫描、索引回表等不同的执行策略。如果预计扫描行数较少，优化器可能倾向于使用索引；如果预计扫描行数较多，优化器可能选择全表扫描或其他策略。

**需要注意的是，这个估算过程并不总是准确的，**特别是在数据分布不均匀或者统计信息过时的情况下。因此，数据库管理员有时需要手动干预，比如使用ANALYZE TABLE命令来更新统计信息，或者使用FORCE INDEX来强制优化器使用特定的索引，甚至删除无必要的索引。

优化策略

因为整个估算的过程是不精确的，可能会导致优化器在索引选择时没有使用索引，或者选择了错误的索引。从而使得SQL执行效率差，尤其是在建立了索引的情况下。

此时可以通过EXPLAIN命令分析SQL执行中，是否在索引选择上没有按照预期。

如果确实在执行过程中，没有使用索引，或者使用了错误的索引，可以使用以下方法尝试解决：

使用analyze table命令可以重新统计索引信息，解决由于统计信息不准确导致的问题。
通过force index语法可以强制MySQL使用特定的索引。

--使用force index时，要考虑SQL的迁移成本。
--使用force index时，确保性能是最优的，因为强制使用某个索引可能会降低查询性能

--在select语句中使用强制索引
SELECT * FROM table_name FORCE INDEX (index_name) WHERE condition;

--在update语句中使用强制索引
UPDATE table_name FORCE INDEX (index_name) SET column1 = value1, column2 = value2 WHERE condition;

--在delete语句中使用强制索引
DELETE FROM table_name FORCE INDEX (index_name) WHERE condition;

修改SQL语句的结构，引导优化器使用期望的索引。
在某些情况下，可以通过增加或删除索引来解决优化器选错索引的问题。

引导方法：

调整查询条件的顺序：
- 优化器在选择索引时会考虑最左前缀原则，即索引中靠前的列在查询条件中出现时，优化器更倾向于选择这个索引。
- 例如，如果有一个查询条件是WHERE a = ? AND b = ?，并且有一个组合索引(a, b)，那么直接使用这个查询条件就能够让优化器选择组合索引。如果查询条件是WHERE b = ? AND a = ?，则可能不会选择这个组合索引。
改变排序规则：
- 如果查询包含ORDER BY子句，优化器可能会选择一个能够满足排序要求的索引，以减少额外的排序操作。
- 例如，如果有一个索引(a, b)，查询条件是WHERE a = ?，并且排序要求是ORDER BY b，那么优化器可能会选择使用这个索引，因为它已经包含了排序所需的列。
使用覆盖索引：
- 覆盖索引是指一个查询可以完全通过索引来得到结果，而不需要访问数据行。
- 如果可能，设计一个覆盖索引来包含查询所需的所有列，这样优化器更可能选择这个索引，因为它可以减少磁盘I/O操作。
避免在索引列上使用函数或计算：
- 优化器可能无法使用索引来加速对列的函数或计算操作。
- 例如，如果有一个索引在列a上，查询条件应该是WHERE a = ?而不是WHERE FUNCTION(a) = ?。
使用索引合并（Index Merge）优化：
- 当查询条件涉及多个索引时，MySQL可以使用索引合并优化来结合这些索引的结果。
- 通过调整查询条件，可以影响优化器是否使用索引合并。