第五章 创建高性能索引

索引优化应该是对查询性能优化最有效的手段了。

一.索引基础

索引可以包含一个或多个列的值。如果索引包含多个列，其顺序非常重要。
因为MYSQL只能高效地使用索引的最左前缀列。

1.索引的类型

在MYSQL中，索引是在存储引擎层而不是服务器层实现的。
下面是MYSQL支持的索引类型：

（1）B-Tree索引

使用B-Tree数据结构来存储数据。
B-Tree意味着每一个叶子页到根的距离相同。
叶子节点的指针指向的是被索引的数据，而不是其他的节点页。
B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。

可以使用B-Tree索引的查询类型：
全键值、键值范围或键前缀查找。

下面是关于B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。
• 不能跳过索引中的列。
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

（2）哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。
MYSQL中，只有Memory引擎显式支持哈希索引。

优点：
紧凑，快

限制：

• 只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
• 不是按照索引值顺序存储的，所以也就无法用于排序。
• 不支持部分索引列匹配查找。
• 只支持等值比较查询。
• 当出现哈希冲突时，必须遍历链表中的所有行指针进行比较，直到找到所有符合条件的行。效率会比较低下。
• 如果哈希冲突很多，一些索引维护操作的代价会很高。

（2）空间数据索引

MyISAM表支持空间索引，可以用作地理数据存储。

（3）全文索引

是一种特殊类型的索引，查找的是文本中的关键词，而不是直接比较索引中的值。

2.索引的优点

索引可以让服务器快速地定位到表的指定位置。
索引的三个优点：
（1）大大减少了服务器需要扫描的数据量
（2）可以帮助服务器避免排序和临时表
（3）可以将随机I/O变为顺序I/O

3.高性能索引策略

（1）独立的列

如果查询中的列不是独立的，则MYSQL不会使用索引。
独立的列指的是索引列不能是表达式的一部分，也不能是函数的参数。

例如：
select actor_id from actor where actor_id+1=5;

无法使用actor_id列的索引。

（2）前缀索引和索引选择性

对于BLOB,TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MYSQL不允许索引这些列的完整长度。

如何创建前缀索引：
alter table city add key (city(7))

前缀索引能使索引更小、更快，但缺点是MYSQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

（3）多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MYSQL的查询性能。

（4）选择合适的索引列顺序

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。

（5）聚蔟索引

聚蔟索引不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚蔟索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚蔟索引时，它的数据行实际上存放在索引的叶子页中。
一个表只能有一个聚蔟索引。

InnoDB将通过主键聚集数据。如果没有定义主键，会选择一个唯一的非空索引代替。如果没有这样的索引，会隐式定义一个主键来作为聚蔟索引。

（6）覆盖索引

MYSQL可以直接使用索引来获取列的数据，这样就不需要读取数据行。
一个索引包含（或者说覆盖）所有要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引的好处：

• 减少数据访问量
• 因为索引是顺序存储的，所以相比读取行，I/O要小的多
• 避免系统调用

覆盖索引必须存储索引列的值，所以MYSQL只能使用B-TREE索引做覆盖索引。

（7）使用索引扫描来做排序

MYSQL有两种方式生成有序的结果：通过排序操作；或者按索引顺序扫描。
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一致时，MYSQL才能够使用索引来对结果做排序。

（8）压缩（前缀压缩）索引

MYISAM使用前缀压缩来减少索引的大小，从而让更多的索引放入内存中。

（9）冗余和重复索引

MYSQL允许在相同列上创建多个索引。
重复索引指的是在相同的列上按照相同的顺序创建的相同类型的索引，应该避免这样创建索引。

有两个索引的缺点是索引成本更高。表中的索引越多插入速度越慢。

（10）索引和锁

索引可以让查询锁定更少的行。

4.索引案例学习

（1）支持多种过滤条件

看看哪些列拥有多种不同的取值，哪些列在WHERE子句中出现的最频繁（sex、country），在有更多不同值的列上加索引的选择性会更好。可以让MYSQL更有效的过滤掉不需要的行。

一个基本原则：尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。

（2）避免多个范围条件

MYSQL无法同时使用多个范围条件索引。

5.维护索引和表

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

（1）找到并修复损坏的表

运行CHECK TABLE来检查是否发生了表损坏，其通常能够找出大多数表和索引的错误。

使用REPAIR TABLE命令来修复损坏的表。

（2）更新索引统计信息

MYSQL的查询优化器会通过两个API来了解存储引擎的分布信息，以决定如何使用索引。第一个API是records_in_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少记录（精确：MYISAM，估算：INNODB）。第二个API是info()，返回各种类型的数据，包括索引的基数（每个键值有多少条记录）。

如果存储引擎向优化器提供的是不精确的数据，或者执行计划太复杂，那么优化器会使用索引统计信息来估算扫描行数。MYSQL优化器使用的是基于成本的模型，衡量成本一个主要的指标是查询需要扫描多少行，如果表的统计信息不准确，则优化器可能做出错误决定。

可以通过ANALYZE TABLE重新生成统计信息。

（3）减少索引和数据的碎片

B-TREE索引可能会碎片化，这会降低查询效率。
可以通过执行OPTIMIZE TABLE或者导出再导入的方式重新整理数据。

6.总结

在选择索引和编写利用这些索引的查询时，有三点原则需要记住：

• 单行访问时很慢的。
• 按顺序访问范围数据是很快的。
• 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有行，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问。