《高性能MySQL》3：创建高性能的索引

一、索引基础

1.1、什么是索引

索引也叫“键key”（可以理解成书本的索引），是存储引擎用于快速查找记录的一种数据结构，

是存储引擎层而不是服务层实现的，不同存储引擎索引方式不同（即使多个存储引擎支持同一种类型索引，其底层实现可能也不一样的）。

1.2、索引优点

（1）可以让服务器快速定位到表的指定位置，减少了服务器需要扫描的数据量；

（2）如常见B-Tree按顺序存储数据，帮助服务器避免排序和临时表；

（3）将随机I/O变为顺序I/O。

1.3、索引的类型

1.3.1、B-Tree索引

它使用B-Tree数据结构来存储数据。

（实际上很多存储引擎使用的是B+Tree索引（InnoDB用的）：即每一个叶子节点都包含指向 下一个叶子节点的指针，从而方便叶子节点的范围遍历）

B-Tree意味着所有值都是按顺序存储的，并且每一个叶子页到跟的距离相同，下面展示InnoDB的B-Tree索引是如何工作的:

（1）B-Tree原理：

——B-Tree能 加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引根节点开始搜索；

——根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针指向下一层查找；

——通过比较 节点叶的值 和 要查找的值 可以找到合适的指针进入下一层子节点；

——最终存储引擎要么找到对应的值，要么记录不存在。

——因为按照顺序存储，所以B-Tree将相关列值存在一起，也可以做ORDER BY和GROUP BY操作。

（2）B-Tree索引对如下类型查询有效：

        全值匹配：索引中的所有列进行匹配

        匹配最左前缀：使用索引的第一列

        匹配列前缀：也可以只匹配某一列值的开头部分

        匹配范围值：可以查找A和B之间的信息

        精确匹配某一列并范围匹配另外一列：

        只访问索引的查询：

（3）B-Tree索引的限制：

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

       不能跳过索引中的列：

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

1.3.2、哈希索引

（1）哈希索引原理：

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

在MySQL中只有Memory存储引擎支持哈希索引，也是它的默认索引类型。

（2）InnoDB引擎有个特殊功能叫“自适应哈希索引”：

当InnoDB注意到某些索引值被使用得非常频繁时，会在内存中基于B-Tree索引之上再创建一个哈希索引，让B-Tree索引也具有哈希索引的一些优点，比如快速哈希查找。

如何解决自适应哈希索引引起不定时MySQL崩溃重启？

....待更新

（3）哈希索引的限制：

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

        哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序；

        不支持部分索引列匹配查找；

        只支持等值比较查询，包括=、IN（）、<=>；

        访问哈希索引非常快，除非有很多冲突

（4）哈希索引的使用示例

示例1：例如需要存储大量的URL，并需要根据URL进行搜索查找。如果使用B-Tree来存储URL，存储的内容会很大，因为URL本身很长，正常情况下查询会按如下查询：

优化后的查询：删除原来URL列上的索引，而新增一个被索引的url_crc列，使用CRC32做哈希：

因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc猎德索引来完成查找，即使有多个记录有相同的索引值，查找仍然很快，只需要根据哈希值做快速的整数比较就能找到索引条目，然后一一比较返回对应的行。

1.3.3、空间数据索引R-Tree

和B-Tree索引不同，这类索引无需前缀查询，它会从所有维度来索引数据，必须使用GIS相关函数来维护数据

1.3.4、全文索引

查找的是文本中的关键词，而不是直接比较索引中的值，类似于搜索引擎做的事而不是WHERE条件匹配。适合用于match against 操作而不是普通WHERE条件操作。

 

1.4、索引是最好的解决方案吗？什么情况下使用索引？

（1）对于小表大多数情况下全表扫描更高效；

（2）对于大到中型表索引很有效；

（3）对于特大型表，索引代价很大，需要一种技术区分查询需要的一组数据，而不是一条记录一条记录地匹配，——例如可以使用分区分表技术；

——还可以建立一个“元数据信息表”，用来查询需要用到的某些特性，例如执行那些需要聚合多个应用分布在多个表的数据查询，则需要记录“哪个用户的信息存储在哪个表中”的元素局，这样查询时就可以直接忽略那些不包含执行用户信息的表；

——对于TB级别数据，定位单条记录意义不大，会使用块级元数据技术替代索引。

 

二、高性能的索引策略

2.1、独立的列

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

错误示例1：下面查询无法使用actor_id的索引：

SELECT actor_id  FROM sakila.actor WHERE actor_id+1=5;

 原因分析：WHERE 表达式等价于actor_id=4，但是MySQL无法自动解析这个方程式。应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。

错误示例2：索引不能是函数的参数

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

 有时候索引很长字符串，如前面提到的URL使用哈希索引。但是如果贼长的比如BLOB、TEXT、VARCHAR类型的列，MySQL不允许索引完整长度，这时需要使用“前缀索引”。

什么是前缀索引：通常可以索引开始的部分字符，这样可以节约索引空间，从而提高索引效率，但使用前缀索引会降低索引选择性。

什么是索引选择性：不重复的索引值（基数）和数据表的记录总数的比值，选择性高的索引可以让MySQL过滤更多的行。唯一索引选择性是1也是性能最好 的索引选择性。

如何选择前缀长度：

要选择足够长的前缀保证较高完整性，同时又不能太长以便节约空间，前缀的“基数”应该接近于完整列的“基数”。需要找到最常见值的列表，然后和最常见的前缀列表进行比较。

2.3、多列索引

（1）多列索引的常见错误

a.为每列创建独立的索引——“把WHERE条件里面的列都建上索引”的错误观念导致的，可以优化但不是绝对

5.0版本引入了“索引合并”，可以使用表上多个单列索引来定位指定的行，并将结果合并，有三个变种：OR联合、AND联合、前两者的合并。

但是当需要使用索引合并时意味着索引设计得很糟糕：

• 多个AND时：意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引；
• 多个OR时：需要耗费大量CPU和内存在算法的缓存、排序、合并操作上。

当看到有索引合并时如何优化？——检查查询和表结构看是否已经是最优的，尽量减少使用“索引合并”

b.按照错误的顺序创建多列索引

2.4、选择合适的索引列顺序（仅针对B-Tree,只有它有顺序）

（1）为什么要注意索引顺序

要满足符合列顺序的ORDER BY、GROUP BY 等字句的查询需求，因为索引顺序依次是按照最左列、第二列、第三列...这样排序。

（2）如何选择索引顺序

将选择性最高的列放在索引最前列

2.5、聚簇索引（并不是单独的索引类型）

（1）什么是聚簇索引：

因为无法同时把数据行存放在两个不同地方所以一个表只有一个聚簇索引。它不是一种单独的索引类型，而是一种数据存储方式，在同一个结构 中保存了B-Tree索引和数据行。

（2）聚簇索引优点：

a、可以把相关数据保存在一起——例如实现电子邮件时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数数据页就能获取某个用户全部邮件，如果没有使用则每封邮件都可能导致一次磁盘I/O。

b、数据访问更快——聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引获取数据更快

c、使用覆盖索引扫描的查询可以直接使用页节点中的主键值对。

（2）聚簇索引缺点：

a、插入速度严重依赖插入顺序

b、更新聚簇索引列代价很高

c、导致全表扫描变慢

d、二级索引（非聚簇索引）访问需要两次索引查找，因为二级索引的叶子节点包含了引用行的主键列

2.6、覆盖索引

（1）什么是覆盖索引：

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

（2）覆盖索引使用场景

通常根据WHERE条件来创建合适索引，但还需要考虑整个查询不单单是WHERE部分，MySQL可以使用索引直接获取列的数据，这样就不用读取数据行了，如果索引叶子节点中已经包含要查询的数据，就没必要回表查询了吗，也就是“扫描只需要扫索引无需扫描表”，性能大增。

（3）覆盖索引优点：

a、只需要读取索引，可减少访问量提高性能；

b、一些存储引擎比如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，所以访问数据要一次系统调用，但访问索引不用所以更快；

c、对InnoDB的聚簇索引特别有用，InnoDB的二级索引在叶子节点中保存了行的主键值，如果二级主键能够覆盖查询，可以避免对主键索引的二次查询。

（4）哪些情况不能用覆盖索引：

a、MySQL大多情况不能在索引中执行LIKE操作，这是底层存储引擎API限制的。可以在索引中做最左匹配的LIKE比较，因为该操作可以转换为简单的比较操作（如LIKE  abc）,但是不能是通配符开头的LIKE查询（如LIKE %、LIKE  _abc、LIKE  [] ），反过来不要用LIKE 的列做索引。这种情况下MySQL不能读取索引值只能提取数据行的值。

2.7、使用索引扫描来做排序

设置索引时使列的顺序和ORDER BY的顺序一致，并且所有列的排序方向一样，MySQL才能使用索引类对结果进行排序；

如果查询关联多张表，则只有ORDER BY字句引用的字段全部为第一个表时才能用索引做排序

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

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

2.9、冗余和重复索引

MySQL允许在相同列上创建多个索引。但是影响性能。

（1）什么是重复索引：指在相同列上按照相同顺序创建相同类型的索引；

创建索引时有个严重的误区：创建一个主键后，先加上唯一限制，然后再加上索引以供查询。其实MySQL的唯一限制和主键限制都是通过索引实现的，上面做法实际上在相同列上创建了三个重复的索引，没必要这么做，除非在同一列上创建不同类型索引满足不同的查询需求。

（2）什么是冗余索引：如果创建了索引（A,B）再创建索引（A）就是冗余索引，因为（A）只是前一个索引的前缀索引，索引（A,B）也可以当做索引（A）使用——只是针对B-Tree引擎而言。

2.10、索引和锁

虽然InnoDB的行锁效率高但是锁定行的时候仍然带来额外开销，同时锁定超过需要的行会增加锁竞争减少并发性。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，让查询锁定更少的行，从而减少锁的数量。

三、索引案例学习

（1）支持多种过滤条件

要看哪一列拥有很多不同的取值，哪些列在WHERE字句中出现得最频繁，就在这样的列上创建索引。

（2）避免多个范围条件

假如有一个last_online列并希望通过下面查询显示在过去几周上线过的用户：

原因：MySQL可以使用last_online列索引或者age列索引，但是无法同时使用他们。无法同时使用多范围条件组合索引。

（3）优化排序

假如一个查询匹配上百万行数据，并且WHERE子句只有sex列，如何排序？

对于sex等选择性非常低的列可以增加一些特殊索引来做排序，例如创建（sex，rating）索引用于下面的查询，这个查询用到了ORDER BY 和LIMIT如果不用索引会很慢：

如果要分页，翻页翻到比较靠后时查询比较慢，随着偏移量增加MySQL需要花费大量时间扫描需要丢弃的数据：

如何优化：可以通过延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行，从而减少MySQL扫描安歇需要丢弃的行数：

 

 

上一篇：https://blog.csdn.net/RuiKe1400360107/article/details/103778112

下一篇：https://blog.csdn.net/RuiKe1400360107/article/details/103963462

  参考资料：《高性能MySQL 第三版》

 

### 若对你有帮助的话，欢迎点赞！评论！+关注！