五、创建高性能的索引
5.1 索引基础
索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的。
1）索引的类型
B-Tree类型
全值匹配：指的是和索引中的所有列进行匹配
匹配最左前缀：即只使用索引的第一列
匹配列前缀：只匹配某一列的值得开头部分
匹配范围值
精确匹配某一列并范围匹配另外一列
只访问索引的查询
限制：
- 如果不是按照索引的最左列开始查找，则无法使用索引
- 不能跳过索引中的列
- 如果查询中有某个列的范围查询
2）哈希索引
InnoDB引擎有一个特殊的功能叫做 "自适应哈希索引"。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于 B-Tree 索引之上再创建一个哈希索引，这样就让 B-Tree 索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果有必要，完全可以关闭该功能
3）全文索引
是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。
5.2 索引的优点
- 索引大大减少了服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机 I/O 变为顺序 I/O
5.3 高性能的索引策略
1）独立的列
指索引列不能是表达式的一部分，也不能是函数的参数
2）前缀索引和索引选择性
通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高效率。但这样也会降低索引的选择性。
索引的选择性是指，不重复的索引值（也成为基数）和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的 "基数" 应该接近于完整列的 "基数"。
select count(distinct city)/count(*) from city;
如果前缀的选择性能够接近 0.031，基本上就可用了。
alter table city add key(city(7));
前缀索引是一种能够使索引更小。更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。
有时候做后缀索引也有用途，MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器来维护这种索引。
3）多列索引
很多人对多列索引的理解都不强，一个常见的错误就是，为每个列创建独立的索引。
4）选择合适的索引列顺序
将选择性最高的列放到索引最前列（在某些场景可能有帮助，但通常不如避免随机IO和排序那么重要，考虑问题需要更全面）
5）聚簇索引
并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行。
当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。术语 "聚簇" 表示数据行和临街的键值紧凑地存储在一起。
如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。InnoDB只聚集在同一个页面中的记录。
优点：
- 可以把相关数据保存在一起
- 数据访问更快
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
缺点：
- 插入速度严重依赖于插入顺序
- 更新聚簇索引列的代价很高
- 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临 "页分裂" 的问题。
- 聚簇索引可能导致全表稻苗变慢，尤其是行比较稀疏
- 二级索引可能比想象中要更大
- 二级索引访问需要两次索引查找，而不是一次（二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值）
聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列
6）覆盖索引
索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？
优点：
- 索引条目远小于数据行大小
- 索引是按照列值顺序存储的，所以对于 I/O 密集的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多
- 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询
5.6 总结
在选择索引和编写利用这些索引的查询时，有如下三个原则始终需要记住：
- 单行访问是很慢的
- 按顺序访问范围数据是很快的
- 索引覆盖查询是最快的
