「高性能MySQL阅读笔记」第五章 创建高性能的索引

第五章 创建高性能的索引

5.1 索引基础

5.1.1 索引的类型

• B-Tree 索引
  • B-Tree通常意味着所有的指都是按顺序存储的，并且每一个叶子页到根的距离相同。
  • InnoDB则使用的是B+Tree
  • B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。
  • 可以使用B-Tree索引的查询类型
    • 全职匹配
      • 全职匹配指的是和索引中的所有列进行匹配。
    • 匹配最左前缀
      • 只使用索引的第一列
    • 匹配列前缀
      • 可以只匹配某一列的开头部分
    • 匹配范围值
    • 精确匹配某一列并范围匹配另外一列
      • 第一列全匹配，第二列范围匹配
    • 只访问索引的查询
      • 只访问索引是只查询所需要的字段都在索引中，所以访问索引后可以返回所需要的数据，这种索引叫做覆盖索引。
  • B-Tree索引的限制
    • 如果不是安装索引的最左列开始查找，则无法使用索引
    • 不能跳过索引中的列
    • 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询。
• 哈希索引
  • 哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。
  • 只有Memory引擎显示支持哈希索引
  • 哈希索引的限制
    • 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的指来避免读取行。
    • 哈希索引数据不是按照索引顺序存储的，所以也就无法排序
    • 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
    • 哈希索引只支持等值毕竟查询，包括=、in()、<=>
    • 访问哈希索引的数据非常快，除非有很多哈希冲突
    • 如果哈希冲突很多的话，一些索引维护操作的代价也会很高
• 创建自定义哈希索引
  • select id from url where url=‘http://mysql.com’ and url_crc32=CRC32(‘http://mysql.com’)
• 空间数据索引(R-Tree)
  • MyISAM 表支持空间索引，可以用作地理数据存储。
  • MySQL 的 GIS函数，如MBRCOUNTANIINS()
  • GIS解决方案做得好的是PostgreSQL的PostGIS
• 全文索引
  • 全文索引查询的是文本中国内地关键词
  • 全文索引更类似搜索引擎做的事情，而不是简单的wheret条件匹配
  • 全文索引适用于MATCH AGAINST
• 其他索引类别

5…2 索引的优点

1. 索引大大减少了服务器需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机I/O变为顺序I/O

索引是最好的解决方案吗？

索引并不是最好的解决方案，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外的工作量时，索引才是有效的。对于非常小的表，大部分情况下做全表扫描更高效。对于中、大型的表索引就非常有效。但是对于特大型的表，可以直接使用分区技术。

5.3 高性能的索引策略

5.3.1 独立的索引

“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

5.3.2 前缀索引和索引选择性

5.3.3 多列索引

• 当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
• 当服务需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量的CPU和内存在算法的缓存、排序和合并操作上。
• 更重要的是，优化器不会把这些计算到“查询成本”中，优化器只关心随机页面读取。

5.3.4 选择合适的索引顺序

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。

5.3.5 聚簇索引

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐士定义一个主键来作为聚簇索引。

• 聚集的数据的优点：
  • 可以把相关数据保存在一起
  • 数据访问更快
  • 使用覆盖索引扫描的查询可以直接使用页节点的主键值
• 聚簇索引的一些缺点
  • 聚簇数据最大限度的提高了IO密集型应用的性能， 但如果数据全放到内存中，则访问的顺序就没有那么重要了，聚簇索引也就没有什么优势。
  • 插入速度严重依赖于插入顺序
  • 更新聚簇所以你的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置，可能面临“页分裂”的问题
  • 页分裂会导致表占用更多的磁盘空间
  • 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂数据存储不连续的时间。
  • 二级索引可能比想象中的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
  • 二级索引访问需要两次索引查找，而不是一次。

5.3.6 覆盖索引

5.3.7 使用索引扫描来做排序

5.3.8 压缩（前缀压缩）索引

5.3.9 冗余和重复索引

在一个列上做不同类型的索引，不算事重复索引

5.3.10 未使用的索引

建议考虑删除此类索引

5.3.11 索引和锁

5.4 索引案例学习

5.4.1 支持多种过滤条件

5.4.2 避免多个范围条件

5.4.3 优化排序

5.5 维护索引和表

维护表有三个主要目的：
1. 找到并修复损坏的表
2. 维护准确的索引统计信息
3. 减少碎片

5.5.1 找到并修复损坏的表

1. CHECK TABLE 通常能够找出大多数的表和索引的错误。
2. 可以使用REPAIR TABLE 命令来修复损坏的表
3. 重置表的存储引擎也可以重建表

5.5.2 更新索引统计信息

可以通过运行ANALYZE TABLE 来重新生成统计信息解决这个问题

5.5.3 减少索引和数据的碎片

有三种类型的数据碎片

• 行碎片
  • 这种碎片指的是数据行被存储为多个地方的多个片段中。
• 行间碎片
  • 行间碎片是指逻辑上顺序的也，或者行在磁盘上不是顺序存储的。
• 剩余空间碎片
  • 剩余碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。

可以通过执行optimize table 或者导出再导入的方式来重新整理数据。这对于多数存储引擎是有效的。
对于不支持optimize table命令的存储引擎，可以通过重置存储引擎来重建表。
mysql> ALTER TABLE [table] ENGINE=[engine]

5.6 总结