第五章 读【创建高性能索引】有感

一、索引的类型

索引有很多种类型，为不同的场景提供更好的性能。不同存储引擎的索引工作方式也不一样，也并不是所有的存储引擎都支持所有类型的索引。

1、B-Tree索引

https://www.cs.usfca.edu/~galles/visualization/BTree.html

B-Tree是满足下列条件的数据结构：

d为大于1的一个正整数，称为B-Tree的度。

h为一个正整数，称为B-Tree的高度。

每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。

每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。

所有叶节点具有相同的深度，等于树高h。

key和指针互相间隔，节点两端是指针。

一个节点中的key从左到右非递减排列。

所有节点组成树结构。

每个指针要么为null，要么指向另外一个节点。

B-Tree特征：

对索引列是按顺序存储（数字顺序、字母顺序、时间顺序）的，所以很适合查询范围数据。

B-Tree索引的限制：

如果不是按照索引最左列开始查询，则无法使用索引；

不能跳过索引中的列；

如果查询中有某个列的查询范围，则其右边所有列都无法使用索引优化查询。

不同的存储引擎实现B-Tree的方式也不一样。MyISam使用前缀压缩技术使得索引更小，InnoDB则按原数据格式进行存储。再如MyISam索引时通过数据的物理位置引用被索引的行，InnoDB则是根据主键引用被索引的行。

2、哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。哈希索引将所有哈希码存储到索引中，同时在哈希表中保存指向每个数据行的指针。

哈希索引特点：

索引自身只需保存哈希码，查询速度非常快。

哈希索引限制：

哈希索引只包含哈希值和行指针，而不存在字段值，所以不能使用索引中的值来避免读行；

不能按索引值顺序存储，无法用于排序；

不支持索引列匹配查询，只支持等值比较查询

其他索引类型：T-Tree索引（空间数据索引）、全文索引等

二、索引的优点

基础优点：快速定位到表的指定位置。根据索引的数据结构不同，还有一些附加优点。

索引优点：

大大减少了服务器需要扫描的数据量；

可以帮助服务器避免排序和临时表；

可以将随机IO变成顺序IO。

判断一个索引是否符合某个查询的“三星系统”：

一星：索引将相关的记录放到一起；

二星：索引中数据的顺序和查询的排列顺序一致；

三星：索引中的列包含查询中需要的全部列

三、高性能的索引策略

高效地选择和使用索引有很多方式。

1、独立的列：值索引列不能是表达式的一部分，也不能是函数的参数；

2、前缀索引和索引选择性

3、多列索引

4、选择合适的索引列顺序

5、聚簇索引：不是一种索引类型，而是一种数据存储方法，在同一结构中保存了B-Tree索引和数据行

6、覆盖索引：设计优秀的索引应该需要考虑到整个查询，不单单是where条件部分。覆盖索引包含了所有需要查询的字段的值（不需要回表查询）

7、使用索引扫描来排序

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

9、冗余和重复索引

10、未使用的索引

11、索引和锁：InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少行锁的数量。

四、索引案例

1、支持多种过滤条件

2、避免多个范围条件

尽可能将范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列；

无法使用范围列后面的其他索引列

3、优化排序

将排序列添加到索引中。如果数据量很大，可以限制用户能够翻页的数量，或使用延迟管理（如：先查主键id，然后分页，再查详情）

五、维护索引和表

1、找到并修复损坏的表：通过是由系统崩坏造成的，可以使用CHECK TABLE来检查

2、更新索引统计信息：如果统计信息不准确，优化器可能会做成错误的决定

3、减少索引和数据碎片

碎片类型：行碎片、行间碎片、剩余空间碎片

索引碎片可以通过先删除索引，再重建索引的方法清除索引碎片化。可以通过重建表的方式清除数据碎片。