MYSQL中的索引

灰太狼 2021-12-09 07:15 259阅读 0赞

### MYSQL中的索引 ###

*  引论
 *  索引的类型
 *   *  B-Tree索引
     *  哈希索引
 *  索引的优点的适用场合

内容整理自《高性能MySQL》

# 引论 #

**索引是什么？**  
索引是存储用于**快速找到记录**的一种数据结构。

**索引的作用？**  
索引对良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。  
索引优化是**对查询优化最有效的手段**。索引能轻易将查询性能提高几个数量级。

**在MySQL中索引如何使用？**  
在MySQL中存储引擎**先在索引中**找到对应值，然后**根据匹配的索引记录**找到对应的数据行。  
例如对如下查询：

mysql> SELECT first_name FROM sakila.actor WHERE actor_id = 5;

如果在actor\_id列上建有索引，则MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。

（思考：对actor\_id建索引，就是说可以根据一个表中的actor\_id项建一棵查找树，在这棵树中找到actor\_id = 5的结点。然后呢，如何找到其对应的数据行吗？数据行是和这个索引树放在一起吗？如果是的话，那根据不同属性建索引，岂不是要建很多包含整个表数据的树，这样会很耗费空间吧？如果不是的话，应该如何找到对应的数据呢？（看完索引部分要回答自己这些问题））

索引可以包含一个或多个列的值，即**可以根据多个列建索引**。如果索引包含多个列，列的顺序很重，MySQL只能**高效地使用索引的最左前缀列**。

大概了解了索引是什么，为什么要用索引以及索引大致的使用方式之后，我们看看MySQL支持的索引类型（即索引的数据结构）。

# 索引的类型 #

在MySQL中，索引是在**存储引擎层**而不是服务器层实现的。所以没有统一的索引标准，也就是说不同搜索引擎索引的工作方式并不一样，底层实现也可能不同。

## B-Tree索引 ##

当人们谈论索引的时候，如果没有特别指明类型，那多半说的是B-Tree索引。  
实际上很多存储引擎使用的是B+Tree。**InnoDB使用的是B+Tree**。

存储引擎以不同的方式使用B-Tree索引。例如：

*  MyISAM使用**前缀压缩技术**使得索引更小，但InnoDB则**按照原数据格式**进行存储。
 *  MyISAM索引通过数据的**物理位置**引用被索引的行，而InnoDB则根据**主键**引用被索引的行。（看不懂）

下图展示了B+树索引的抽象表示：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70][]

**B+树的基本结构**

*  非叶节点的槽中存放了**指向子节点**的指针，这些指针实际上定义了子节点页中值的上限和下限。
 *  叶节点的指针指向**被索引的数据**。
 *  不同叶子页也通过指针相连起来。
 *  树的深度和表的大小直接相关。
 *  B-Tree通常意味着**所有的值都是按照顺序存储**的，并且**每一个叶子页到根的距离相同**。

（至此可以回答上面的一个疑问：叶节点指向了被索引的数据，所以只要按照索引查找，如果符合条件的数据存在，那么就能找到匹配的数据了。）

**为什么B-Tree索引能加快访问数据的速度？**

存储引擎**不再需要进行全表扫描**来获取需要的数据，取而代之的是从索引的根结点开始搜索。存储引擎根据指向下层的指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点。

B-Tree **对索引列是顺序存储**的，所以很适合查找范围数据。例如，找到所有以I-K开头的名字这样的查找效率会非常高。  
（什么叫对索引列是顺序存储的？具体表现形式是什么？看不懂）

对下面的数据表：  
![20190716183824272.png][]  
对表中的每一行数据，索引列中包含了last\_name、first\_name和dob列的值，下图展示了该索引是如何组织数据的存储：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70 1][]  
索引对多个值进行排序的依据是`CREATE TABLE`语句中定义索引时列的顺序。

**可以使用B-Tree索引的查询类型：**

B-Tree索引适用于**全键值**、**键值范围**或**键前缀查找**。  
其中键前缀查找只适用于根据**最左前缀**的查找。  
具体如下：

*  **全值匹配**：指和索引中所有列进行匹配。eg.找“名字为Cuba Allen，出生日期为1960-01-01的人”。
 *  **匹配最左前缀**：和索引中的最左列匹配。eg.找所有姓为Allen的人。
 *  **匹配列前缀**：也可以只匹配某一列的值的开头部分。eg.找所有以J开头的姓的人。
 *  **匹配范围值**：eg.找姓在Allen和Barrymore之间的人。
 *  **精确匹配某一列并范围匹配另一列**：eg.找姓是Allen且名字是K开头的人。
 *  **只访问索引的查询**：即查询只需要访问索引，而无需访问数据行。

所以索引树中的节点是有序的，所以除了**按值查找**之外，索引**还可以用于查询中的ORDER\_BY操作**。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以只要ORDER\_BY子句满足前面列出的查询类型，则这个索引也可以满足对应的排序需求。

> ORDER BY 语句用于**根据指定的列**对结果集进行**排序**。  
> ORDER BY 语句默认按照升序对记录进行排序。  
> 如果希望按照降序对记录进行排序，可以使用 DESC 关键字。

**B-Tree 索引的限制：**

*  如果不是按照索引的最左列开始查找，则无法使用索引。eg.无法直接找名字是Bill的人，或者某个特定生日的人。
 *  不能跳过索引中的列。eg.无法找姓是Allen且生日是1960-01-01的人。
 *  如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

这些限制都和**索引的列的顺序**有关。在优化性能的时候，可能需要使用**相同的列**但**顺序不同**的索引来**满足不同类型的查询需求。**  
这些限制不是B-Tree本身导致的，而是MySQL优化器和存储引擎使用索引的方式导致的。（嗯哼？）

## 哈希索引 ##

这一部分已经看完了，比较好懂，后面再记录。  
其他索引类别就不介绍了。

大概了解了索引是什么，索引是如何组织的，以及索引是怎样工作的，下面来总结一下索引的优点和适用场合。

# 索引的优点的适用场合 #

前面已经讲过，索引可以**让服务器快速地定位到表的指定位置**。但这并不是索引的唯一作用，根据创建索引的数据结构的不同，索引也有一些其他的附加作用。

对B-Tree索引来说：

*  按照顺序存储数据，所以MySQL可以用来做 ORDER BY 和 GROUP BY 操作。
 *  因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。
 *  因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。

总结下来索引有如下三个优点：

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

**索引是最好的解决方案吗？**

索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。

*  对于非常小的表，大部分情况下简单的全表扫描更高效。
 *  对于中型到大型表，索引非常有效。
 *  对于特大型表，建立和使用索引的代价将随之增长。这种情况下，得直接区分出查询需要的一组数据，而不是一条记录一条记录的匹配。可以使用分区技术。

（没有写多大算小、中、大、特大，自己需要找点资料大概了解一下）

今天先暂时这样，火速刷题去。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70]: /images/20211208/7efcae3be9814b63bf207b484ca8b59c.png
[20190716183824272.png]: /images/20211208/cceab9fd24f24c329d28cf65ff0f9b21.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70 1]: /images/20211208/327d985c60d44c9c946658e9d32f4e30.png