5.3 创建高性能的索引
索引类型
- B-Tree 对索引列顺序组织存储，所以适合查找范围数据。
支持的查询：全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另一列、只访问索引的查询、排序；
限制：非最左列不匹配、不能跳过索引中的列、某列范围查询其余索引无法使用。
- 哈希索引 只有精确匹配索引所有列的查询才有效。
优势：索引只存储哈希值，结构紧凑查询速度快；
限制：不存储索引值需要读取行、无法用于排序、不支持部分索引列匹配、只支持等值比较查询、可能会发生哈希冲突、哈希冲突较多时索引维护操作代价啊高。
备注：InnoDB内部会自动创建自适应哈希索引
- R-Tree
-
全文索引
高性能的索引策略
-
独立的列 索引列不能是表达式的部分
-
前缀索引和索引的选择性 索引很长的字符列（BLOB、TEXT或很长的varchar）可以索引开始的部分字符
优点：索引更小，检索速度更快
缺点：无法使用前缀索引做GROUP BY、ORDER BY和覆盖扫描
- 多列索引
- 当查询出现对多个索引做相交操作时可能意味着需要一个包含所有相关列的多列索引，而不是多个单列索引
- 当查询出现多个索引做联合操作时（EXPLAIN的Extra包含Using union）可能会耗费更多的CPU和内存资源
- EXPLAIN的type列中包含index_merge则表示使用了索引合并，可以使用IGNORE INDEX让优化器忽略掉某些索引
- 选择合适的索引列顺序（适用于B-Tree）
- 经验法则，当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。
备注：经验法则考虑的是全局基数和选择性而不是某个具体查询，因此实际使用中还应该考虑表中值的分布情况，因查询制宜。
- 聚簇索引
- 如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引
优点：
- 可以把相关的数据保存在一起
- 数据访问更快（索引和数据保存在同一个B-Tree中）
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
缺点：
- 聚簇数据最大限度地提高了I/O密集型应用的性能，如果数据全部存储在内存中则无此优势
- 插入速度严重依赖于插入顺序
- 更新聚簇索引列的代价很高
- 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候可能面临“页分裂”问题
- 聚簇索引可能会导致全表扫描变慢
顺序主键
- 非循序主键可能造成的问题
- 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB需要重新从磁盘中读取目标页至内存，导致大量随机I/O
- 因为写入是乱序的所以InnoDB需要做页分裂操作，导致需要移动大量数据
- 页分裂导致每页数据稀疏最终出现数据碎片
备注：可使用OPTIMIZE TABLE重建表并优化页填充
- 顺序主键可能造成的问题
- 并发插入可能导致主键上界成为“热点”，出现间隙锁竞争
- AUTO_INCREMENT的锁机制成为“热点”
覆盖索引
一个索引包含所有需要查询的字段的值
- 查询的字段按需读取，当读取的字段全部包含在某个索引中则为覆盖索引，此时存储引擎不需要回表查找行（随机I/O）
- Mysql在索引中不能做以通配符开头的LIKE查询
- EXPLAIN的Extra列中包含有Using index则表示为索引覆盖查询
使用索引扫描来做排序
- EXPLAIN的type列中包含index则表示使用了索引扫描来做排序
- 只有当索引的列顺序和ORDER BY的顺序完全一致，并且所有列的排序方向（正序或倒序）都一样时才可以使用索引来对结果做排序
- 如果前导列为常量（例如WHERE a=’1’）则ORDER BY子句可以不满足索引的最左前缀要求
冗余和重复索引
- 对于InnoDB主键列已经包含在二级索引中，因此一般而言索引无需重复包含主键
- 冗余索引用于满足不同查询场景，但会影响写操作的性能
未使用的索引
- Percona Server或MariaDB打开userstates服务器变量，服务器运行一段时间后查询INFORMATION_SCHEMA.INDEXS_TATISTICS可以获取每个索引的使用频率
- Percona Toolkit中的pt-index-usage可以读取查询日志分析索引使用和查询报告
索引和锁
- EXPLAN的Extra列包含Using where则表示存储引擎返回行以后再应用WHERE过滤条件
- 索引可以减少需要锁定的行
5.4 索引案例学习
- 尽可能将需要范围查询的列放到索引后面，以便优化器使用尽可能多的索引列
- 对于范围条件查询，Mysql无法使用范围列后面的其他索引列，但多等值查询则无此限制