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

1. 索引的优化

1.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() 来替代范围查询, 但是并不是所有的范围查询都适用.

1.2 避免多个范围条件

• 列表和范围查询
  • 列表查询: IN(1,3,5) ; 范围查询: id>45.
  • Mysql 区分不来这两种类型, explain 的结果中type 字段都为range.
  • 但是, Mysql 不能再使用范围列后面的其他索引, 而对于列表查询没有这个限制.
  • 在遇到有连续的范围查询时, 设法把除最后一列外的范围列查询转化为列表查询.
  • 未来: 如果Mysql 实现了松散索引扫描, 那么就能在一个索引上使用多个范围条件. 也就不需要该技巧.

2. 维护索引和表

2.1 找到并修复顺坏的表

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

2.2 更新索引统计信息

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

2.3 减少索引和数据的碎片

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

3. MICS

• 三个原则
  • 单行访问是很慢的. 读取的块应该包含尽可能多所需要的行.
  • 按顺序访问范围数据是很快的.
  • 索引覆盖查询是很快的.
• 像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回值时不定的易变的.
• 当只要一行数据时使用limit 1.
  • 优化: 数据库引擎会在找到第一条数据后立即停止搜索.
• 为经常用来做搜索的字段建立索引.
• 表join 时两边的列应该拥有相同的类型,并且是被建立过索引的.
• 永远为每张表设置一个ID.
  • 即使该表已经有唯一的字段. int 自增的ID 性能更好.
  • 关联表的外键是个例外.
• 把IP 地址存储unsigned int.