高性能MySQL--第五章 创建高性能索引

1.索引基础

索引（key键），是存储引擎用于快速找到记录的一种数据结构。MySQL只能高效使用索引的最左前缀列。MySQL中索引在存储引擎实现计算多个存储引擎支持同一种索引，其底层实现也可能不同。

1.1索引类型：

B-Tree索引，大多都指这类索引。使用B-Tree数据结构来存储数据。所有值按顺序存储，且每一个叶子到根到距离相同。存储引擎不需要进行全表扫描来获取数据，而是从索引到根节点开始搜索。根节点的槽中存放来指向字节点的指针，存储引擎根据这些指针向下层寻找。这些指针实际上定义来字节点中值的上下限，最终或找到对应的值或不存在。叶子节点的指针指向被索引的数据而非其他节点。树的深度和表的大小相关。B-Tree对索引列顺序组织存储的，适合查找范围数据。索引对多个值排序的依据是CREATE TABLE语句中定义索引时列的顺序。B-Tree索引适用于全键值（全值匹配，和索引中的所有索引列进行匹配）、键值范围（匹配范围值）、键前缀查找（最左前缀，最左的几列键匹配，也可以只匹配某一列值的开头部分）。如果索引中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

哈希索引，基于哈希实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有索引列计算一个哈希码（hash code），哈希码是一个较小的值，不同键值计算出的哈希码不同。只有Memory引擎显式支持哈希索引，是Memory引擎的默认索引类型，Memory引擎也支持B-Tree索引，Memory引擎支持非唯一哈希索引，以列表方式存放多个记录指针到同一个哈希条目。索引自身只需存储对应的哈希值，所以查找速度快。数据不是按照索引值顺序存储，无法用于ORDER BY。不支持部分索引列匹配查找，只支持等值比较查询。InnoDB引擎支持自适应哈希索引（adaptive hash index），当某些索引值被使用得非常频繁时，InnoDB引擎会在内存中基于B-Tree索引之上创建一个哈希索引，如此B-Tree也具有哈希索引的一些特点，如快速的哈希查找，这是完全自动的内部的行为。

空间数据索引（R-Tree），MyISAM表支持空间索引，可以用作地理数据存储。无需前缀查询，会从所有维度来索引数据，使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。

全文索引，查找的是文本中的关键词，而非比较索引中的值。更接近搜索引擎，适用于MATCH AGAINS操作而非WHERE条件操作

2.索引的优点

可以让服务器快速定位到表的指定位置。

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

3.高性能索引策略

3.1独立的列

索引列不能是表达式的一部分，也不能是函数的参数。应始终将索引列单独放在比较符号（><=）的一侧。

3.2前缀索引和索引选择性

索引很长的字符串列，可以模拟哈希列，也可以索引开始的部分字符串，大大节省索引空间，提高索引效率，但会降低索引的选择性。BLOB  TEXT或很长的VARCHAR列必须使用前缀索引。前缀索引可以使索引更小更快，但MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

3.3多列索引

3.4选择合适的索引列顺序

将选择性高的列放到索引的最前列。可能需要根据使用频率高的查询来调整索引列的顺序。

3.5聚簇索引

是一种数据存储方式。InnoDB的聚簇索引是在同一个结构中保存列B-Tree索引和数据行。表有聚簇索引时，它的数据行实际上存放在索引的叶子页，聚簇表示数据行和相邻的键值紧凑地存储在一起。一个表只能有一个聚簇索引。叶子页包含了行的全部数据，但是节点页只包含列索引列。InnoDB通过主键聚集数据。如果没有主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引InnoDB会隐式定义一个主键作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻值的页面可能相距甚远。

优点：（1）可以把相关数据保存在一起。（2）数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此获取数据要更快。（3）使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：（1）虽然最大限度提高了I/O密集型应用的性能，但如果全部数据都存放在内存中，则访问顺序就没那么重要了，聚簇索引也失去了优势。（2）插入速度严重依赖于插入顺序。（3）更新聚簇索引列的代价很高。（4）基于聚簇索引的表在插入新行或主键被更新导致需要移动行的时候，可能面临页分裂问题。页分裂会导致表占用更多的磁盘空间。（5）可能导致全表扫描变慢。（7）二级索引（非聚簇索引）的叶子节点包含列引用行的主键列，可能比想象的要更大。（8）二级索引访问需要两次索引查找。

使用InnoDB应尽可能按主键顺序插入数据，并尽可能使用单调增加的聚簇键的值来插入新行。

3.6覆盖索引

如果一个索引包含（或覆盖）所有需要查询的字段的值，称为覆盖索引。

MySQL不能在索引值中执行like操作。

3.7使用索引扫描来做排序

MySQL可以使用索引既满足排序又用于查找行。

只有当索引列的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能使用索引来对结果做排序。如果查询要关联多张表，只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。最左前缀。如果索引的第一列被指定为一个常数，可以不满足最左前缀的要求。

3.8压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，让更多索引可以放入内存，提高性能。由于使用的是前缀，正序扫描速度还可以，倒序就不太好了。适用于I/O密集型应用，不适用CPU密集型。

3.9冗余和重复索引

MySQL允许在相同列上创建多个索引。重复索引指在相同列上按照相同顺序创建相同类型的索引（要避免）。

3.10未使用的索引

3.11索引和锁

索引可以让查询锁定更少的行，如果查询从不访问不需要的行，就会锁定更少的行。锁定超过需要的行会增加锁争用并减少并发性。

5维护索引和表

（1）找到并修复损坏的表，（2）维护准确的索引统计信息，（3）减少索引和数据碎片

CHECK TABLE通常能找出大部分是表和索引的错误，REPAIR TABLE命令可以修复损坏的表。InnoDB一般不会出现损坏，如果损坏，可能是数据库硬件问题如内存或磁盘问题，通常是管理员操作失误导致。

records_in_range()获取在某个范围里又多少条记录（MyISAM返回精确值，InnoDB返回估算值）。info()返回各种类型的数据，包括索引的基数（每个键值有多少条记录）。

B-Tree索引可能会碎片化，碎片化的索引可能会以很差很无序的方式存储在磁盘上。

行碎片、行间碎片、剩余空间碎片

通过执行OPTIMIZE TABLE或导出再导入的方式来重新整理数据。

6总结

选择索引和编写使用索引的查询时，三个原则：

（1）单行索引是很慢的。特别是在机械硬盘存储中。如果服务器从存储中读取一个数据块只是为了获取其中一行，那就浪费来很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。

（2）按顺序访问范围数据是很快的。第一，顺序I/O不需要多次磁盘寻道，比随机I/O快很多。第二，若服务器能按需要顺序读取数据，就不再需要额外的排序操作，并且GROUP BY查询也无需再做排序和将行按照组进行聚合计算了。

（3）索引覆盖是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就无需再回表查找行了，这避免了大量的单行访问。

经验法则未必适用于实际业务需求。