高性能mysql

5 创建高性能的索引

5.1 索引基础

5.1.1 索引的类型

• B-Tree
• 哈希索引
• 空间数据索引
• 全文索引
• 其他索引类别

5.2 索引的优点

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

读书推荐《数据库索引设计和优化》Tapio Lahdenmaki

5.3 高性能的索引策略

5.3.1 独立的列

where查询条件中的索引列不要进行计算，直接进行选择

5.3.2 前缀索引和索引选择性

索引选择性 ： cardinality/#T (及不重复的索引列/记录总数)
前缀索引的选择：
1.选择选择性接近于整列的选择性的最小前缀即可
2.只看平均选择性是不够的，需要看前缀的最坏选择性

前缀索引的优缺点：
优点：前缀索引更小，更快
缺点：前缀索引无法用作ORDER BY,GROUP BY

5.3.3 多列索引

索引合并的AND：往往意味着需要建立多列索引
索引合并的OR: 可以优化成UNION的形式

5.3.4 选择合适的索引列顺序

将选择性高的索引列放在前面（cardinality,数据基数）

5.3.5 聚簇索引

优点：
1.相关数据保存在一起。
2.数据访问更快
3.覆盖索引扫描的查询可以直接使用页结点中的主键值
缺点：
1.插入速度严重依赖于插入顺序
2.更新聚簇索引列的代价很高
3.基于聚簇索引插入新行，或者基于主键更新导致移动行的时候，可能会出现页分裂问题，导致占用更多磁盘
4.聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

注意：最好避免随机聚簇索引，特别是对于io密集型的应用；随机主键毕竟插入时间更长，占用空间也更大
原因:1.可能需要触发调页机制；2.写入是乱序的，频繁进行页分裂操作；3.频繁的页分裂导致产生大量碎片

5.3.6 覆盖索引

好处：
1.能极大的减少数据访问量
2.因为索引是按照列值顺序存储的，范围查询会比从磁盘读取每一行的io少得多
3.Innodb的覆盖索引能够减少二次索引的二次查找

延迟关联：1.可以用来进行大分页查询；2.可以配合覆盖索引进行过滤查找，避免直接查表的操作

5.3.7 使用索引扫描来做排序

1.索引无法覆盖需要查询的全部列的时候，按照索引查询需要回表，此时由于随机io会比全表扫描更慢
2.只有当索引列的顺序和ORDER BY的顺序完全一致时，并且所有列的排序都一样时，才能使用索引来进行排序
3.当关联多表时，只有orderby 引用的子句全部来自于第一张表，才能做索引查询

5.3.8 压缩（前缀压缩）索引

MyISM 提供的压缩索引能够使用更少的空间，但是导致索引无法进行二分查找

5.3.9 冗余和重复索引

MyISM 提供的压缩索引可能导致联合索引相比单个索引的性能明显下降