从SQL Server到MySql(6) : Mysql 索引优

2015-11-07 本文已影响530人沪上最强亚巴顿

1. 索引的优化

考虑表上所有的选项
- 通用的原则: 尽量在选择性高的列上做索引.
- 例外: 对于经常被作为条件的列, 如性别, 最好还是对它创建索引.
- 设计索引时, 不要�只从现在的查询考虑, 还要考虑对查询进行优化.
- 如果为某些查询创建的索引, 会导致另一些查询的销量降低. 应该考虑优化查询.
尽可能重用索引而不是建立大量的组合索引
- 例如: 查询中可能需要(sex, country, age) 和(sex, country, region, age) 这两种索引.
- 限制: Mysql 只能匹配索引的最左前缀.
- 做法: 只建立(country, region,sex, age) 一个索引.
  - 在需要使用到(country, region, age) 索引的查询中, 在where 条件中加入 And sex in ('m','f');
- 局限: 每额外增加一个IN() 条件, 优化器需要做的组合就以指数级增加.
  - 当列过多时, 很可能会大大地降低了查询性能.
  - 老版本的MySql 在IN()组合条件过多时会有很多问题, 如查询优化耗时且浪费内存.
  - 而新版本在组合数超过一定数量后, 就不再进行执行计划评估了, 从而导致Mysql 不能很好地利用索引.
尽可能将需要做范围查询的列放到索引的后面
- 例如: age 这类经常被用来作范围条件(18~25) 的列.
- 限制: 查询只能使用索引的最左前缀, 直到遇到第一个范围条件列.
- 做法: 可以使用IN() 来替代范围查询, 但是并不是所有的范围查询都适用.

损坏的索引会导致查询返回错误的结果或者莫名的主键冲突等问题.
- 当遇到古怪的问题时, 使用Check Table 来检查是否有表损坏.
- 如果有表损坏, 使用Repair Table 来修复表.
- 如果引擎不支持修复命令, 通过一个不做任何操作(no-op) 的Alter 操作来重建表, 也可以达到效果.
- 如果遇到数据损坏, 重要的是要找出是什么原因导致了损坏, 而不是简单地修复. 否则损坏很可能再次发生.

Mysql 优化器通过两个API 来了解存储引擎的索引值分布情况, 来决定如何使用索引.
- records_in_range() 返回一个范围内的记录数.
- info() 返回各种类型的数据, 包括索引的基数(键值的记录数).
如果存储引擎提供的扫描行数信息是不准确的. 优化器会使用索引统计信息来估算扫描行数
- 优化器基于成本模型, 成本的核心指标是查询需要扫描的行数.
- 如果没有统计信息,或者信息不准确, 优化器很可能做出错误的决定.
- 通过Analyze Table来重新生成统计信息.
每种存储引擎的索引统计信息实现方式都不同, 所以需要重新生成的频率和成本也不同.
- MyISAM 将统计信息存储在磁盘中, 重新生成需要进行一次全索引扫描来计算索引基数, 过程中会锁表.
- InnoDB 不在磁盘存储索引统计信息, 而是通过随机的索引访问进行评估并存储在内存中.
- Show Index From 来查看索引的基数.
- 索引的统计信息会在一些情况下自动更新, 如果数据量较大时,可能会造成性能问题.
  - 关闭自动更新后, 需要周期性地运行Analyze Table 来手动更新. 否则会出现糟糕的执行计划.

B-Tree 索引可能会碎片化, 这会降低查询的效率.
- 碎片化的索引会议很差或无序的方式存储在硬盘上.
表的数据存储也可能碎片化.
- 行碎片. 数据行被存储在多个片段中.
- 行间碎片. 逻辑上顺序的页或行, 在硬盘上不是顺序存储的.
- 剩余空间碎片.页中的剩余空间会造成浪费.
通过Optimize Table 或导出再倒入的方式重新整理数据.
- 对于不支持上述命令的存储引擎, 通过一个不做任何操作的Alter 操作来重建表.