创建高性能的索引

创建高性能的索引

索引（MySQL中也叫“键（key）”），是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易的将查询性能提高几个数量级。

5.1 索引基础

在MySQL中，先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。（先在索引上按照键值查找，然后返回所有包含该值的数据行）

索引可以包含一个或者多个列的值。如果包含多个列，那么列的顺序也十分重要（最左前缀）

5.1.1 索引的类型

在MySQL中，索引是在 存储引擎层而不是服务层实现的。不同存储引擎的索引工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。

B-Tree 索引

之所以能加快访问数据的速度，因为索引引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始搜索。通过比较节点页的值和要查找的值可以找到合适的指针进去下层子节点，这些指针实际上定义了子节点页中值的上限和下限。

索引查询有效：

1. 全值匹配。和索引中的所有列进行匹配
2. 匹配最左前缀。从左开始，匹配索引列
3. 匹配列前缀。可以匹配某一列的值的开头部分。
4. 匹配范围值。
5. 精确匹配某一列，并且范围匹配另一列
6. 只访问索引的查询。即查询只需要访问索引，而无需访问数据行。

如果order by满足前面列的集中查询类型，那么这个索引也可以满足对应的排序需求。

索引限制：

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

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，不同键值的行计算出的哈希码也不一样。

因为索引自身只需要存储对应的哈希值，所以索引的结构十分紧凑，所以查找非常快。然后，哈希索引也有它的限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能用所以中的值来避免读取行。内存的速度快，可以忽略这点。
• 哈希索引数据不是安卓索引顺序存储的，所以无法排序
• 哈希索引也不支持部分索引列匹配查找，因为索引始终是使用索引列的全部内容来计算哈希值。
• 哈希索引只支持等值比较，不支持范围查询
• 访问哈希索引的数据非常快，除非很多哈希冲突。当出现哈希冲突时候，必须遍历链表中所有的行指针，逐行比较，直到找到所有符合条件的数据
• 如果哈希冲突很多的话，一些索引维护操作的代价会很大。

对于很长的列建立索引，可以去掉，采用crc32 建索引，查询的时候，加上原来的，性能高非常多。

空间数据索引

MyISAM表支持空间索引，可以用作地理数据存储。必须使用GIS相关函数来维护数据，支持不完善。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引更类似搜索引擎做的事情，而不是简单的where条件匹配。

5.3 索引的优点

索引可让服务器快速定位到表的指定位置。B-Tree索引，按照顺序存储数据，所以可以用来做order by和group by操作。索引中存储了实际的列值，所以某些查询只是有索引就可以完成查询。总结优点如下：

1. 索引大大减少服务器需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引将随机IO变成顺序IO
1
2
3

索引是最好的解决方案嘛：

索引并总是最好的工具。只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时候，索引才有效。非常小的表，大部分情况全盘扫描更快。中到大型表，索引非常有效。特大型表，建立和使用索引的代价随之增长，可以采用分区。

5.3 高性能的索引策略

5.3.1 独立的列

索引不能是表达式的一部分，也不能是函数的参数。

5.3.2 前缀索引和索引选择性

有时候索引列是很长的字符列，这会让索引变得大且慢。怎么办？

​ 通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。选择性越高，可以过滤掉的数据越多，唯一索引选择性是1，性能最好。

5.3.3 多列索引

多列单独的索引，使用的时候可能会索引合并，但是性能不见得提高。索引合并有时候是一种优化的结果，更多的时候说明了索引建的很糟糕：

• 做and操作时，通常意味着一个包含所有相关列的多列索引，而不是多个独立的单列索引
• 做or操作时候，需要耗费大量的cpu和内存再算法的缓存、排序和合并上
• 优化器不会讲这些计算到“查询成本”中

5.3.4 选择合适的索引顺序

将选择性最高的列放在索引最前列。不考虑排序和分组时，选择性最高的列放在最亲am通常是最好的。这时候索引的作用只用于优化where条件的查找。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HzJAwMA6-1666946137546)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20221027105949091.png)]

选择性更高。

5.3.5 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但是InnoDB的聚簇索引实际上在同一个结构中保存了B-Treee索引和数据行。

当表有聚簇所用时，它的数据行实际上存放在索引的叶子页（leaf page）中。“聚簇”表示数据行和相邻的键值紧凑的存储再一起。

InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。**

这棵树的叶节点data域保存了完整的数据记录。

MyISAM索引文件和数据文件是分离的索引文件仅保存数据记录的地址

如果没有主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB回隐式的定义一个主键来作为聚簇索引。

聚集的数据有一些重要的优点：

• 可以将相关的数据保存在一起。例如邮件，通过用户id聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，每封邮件都可能导致一次磁盘IO
• 数据访问更快。聚簇索引将索引和数据保存再同一个B-Tree中。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

• 聚簇索引最大限度提高了io密集型应用的性能，如果数据都放进内存，则访问顺序没那么重要了
• 插入速度严重依赖于插入顺序。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇 索引的表插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。（当行的主键值要求必须将这一行插入到某个已满的页时，存储引擎必须将该页分裂成两个页来容纳，这是一次页分裂操作，回导致占用更多磁盘空间）
• 聚簇索引可能导致全表扫描变慢，尤其行比价稀疏，或者由于列分裂导致数据存储不连续的时候
• 二级索引（非聚簇索引）可能比想象中要大，因为在二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。（二级索引叶子节点保存的不是只想行的物理的指针，而是行的主键值）

InnoDB的二级索引和聚簇索引很不相同。在InnoDB中，聚簇索引“就是”表，二级索引的叶子节点中存储的是主键（继续查找需要的值），和索引列值。

在InnoDB表中按照主键顺序插入行

如果使用的表没什么数据需要聚集，可以定义一个代理键作为主键，与应用无关，可以使用自增列。数据行是按照顺序写入，对于根据主键做 关联操作的性能会更好。

避免随机的聚簇索引，例如uuid作为聚簇索引非常糟糕，会使得聚簇索引插入变得完全随机，这是最坏情况，使得数据没有任何聚集特性。

对于uuid插入，不像是自增长那样，磁盘能顺序的存储。因为新行的主键值不一定比之前插入的大，所以InnoDB无法简单地总是把新行插入到索引的后面，需要查找新的合适的位置-通常是已有数据的中间位置-并且分配空间。带来额外的工作切数据分配不优化。

5.3.6 覆盖索引

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

查询只需要扫描覆盖索引而无需回表，好处：

• 索引条目通常小于数据行大小，如果只需要读取索引，回极大减少数据访问量。
• 索引是按照顺序存储的（至少单页是如此），所以对于io密集型的范围查询会比随机从持平读取每一行数据的io要少得多。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点保存了行的主键值，如果二级索引能够覆盖查询，可以避免对主键索引的二次查询。
• 一些存储引擎如MyISAM在内存中只缓存了索引，数据依赖操作系统缓存，因此访问数据需要一次系统调用。

覆盖索引必须存储列的值，哈希索引，空间索引，全文索引都不存储列的值。

5.3.7 使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作，或者通过按照索引顺序扫描。explain出来的type列的值是“index”，则说明MySQL使用了索引扫描来排序。

注意：覆盖索引不能全部使用，需要回表操作，基本是随机io，因此按照索引顺序读取数据通常比顺序全表扫描慢。

只有当索引的列顺序和order by字句的顺序完全一致，并且所有列的排序方向都一样时，才能用索引排序。order by 满足最左匹配原则，但是前列如果为常量的时候，如果where子句或者join子句中对这些列指定了常量，可以弥补索引的不足。最左前缀有一些特殊情况。

1. 如果第一列是常量条件，使用第二列排序，两列组合在一起，就形成了索引最左前缀
2. 第一列是条件，排序用的第一第二列也可以

5.3.8 压缩（前缀压缩）索引

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

5.3.9 冗余和重复索引

MySQL允许再相同的列上创建多个索引，需要单独的维护重复的索引，优化器查询的时候也需要考虑，影响性能。

大多数情况下都不需要冗余索引，应该尽量扩展已经有的索引而不是创建新的索引。

5.3.10 未使用的索引

永远未使用的索引是累赘，建议删除。

5.3.11 索引和锁

索引可以让查询锁定更少的行。InnoDB只有再访问行的时候才会对其进行加锁，而索引可以减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行次啊有效，返回给服务层后，服务器才能用where 子句。

extra: using where ;using index ,表明MySQL服务器将存储引擎返回行以后再应用where过滤条件。