高性能MySQL——第五章创建高性能的索引

淩亂°似流年 2022-10-15 10:51 151阅读 0赞

什么是索引？  
索引在MySQL中也叫作“键 key”，它是存储引擎用于**快速找到记录**的一种数据结构。  
当表中**数据量越来越大**，索引就非常重要！！

索引优化是对查询性能优化最有效的手段（可以提高几个数量级）。

--------------------

## 索引基础 ##

索引就像一本书的目录，想要理解索引，最简单的方法就是去看看《高性能MySQL》这本书的“索引”部分，找到对应的页码。

在MySQL中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

对于这段sql语句查询：

SELECT first_name FROM sakila.actor WHERE actor_id=5;

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

可以有多个列都为索引，称为联合索引，而MySQL有一种**最左前缀匹配原则**，因此列的顺序十分重要。

> **最左前缀匹配原则**：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

### 索引的类型 ###

索引是在存储引擎层实现的，不在服务器层实现，因此索引的标准根据不同的存储引擎而定。

MySQL支持的索引类型有：

#### B-Tree索引 ####

大部分MySQL存储引擎支持B-Tree索引。

但是底层的存储引擎也可能使用不同的存储结构：

*  NDB集群存储引擎内部使用了**T-Tree**结构
 *  InnoDB使用的是**B+Tree**

并且存储引擎的不同使得B-Tree索引的性能也不同：

*  MyISAM：使用**前缀压缩技术**使得索引更小，并且MyISAM索引通过**数据的物理位置引用**被索引的行
 *  InnoDB按照原数据格式进行存储，并且InnoDB根据**主键**引用被索引的行。

B+Tree索引的抽象表示如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2thcnJ5X3p6ag_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2thcnJ5X3p6ag_size_16_color_FFFFFF_t_70 1]

> 面试时候如何描述B-Tree和B+Tree？
> 
>  *  B-Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。
>  *  B+ Tree 是基于 **B-Tree 和叶子节点顺序访问指针**进行实现，它具有 B-Tree 的平衡性，并且通过顺序访问指针来提高**区间查询**的性能。

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

*  全值匹配  
    全值匹配指的是**和索引中的所有列**进行匹配
 *  匹配最左前缀  
    只使用索引的第一列
 *  匹配列前缀  
    也可以只匹配某一列的值的开头部分
 *  匹配范围值  
    可以匹配一个范围之间的值
 *  精确匹配某一列并范围匹配另外一列
 *  只访问索引的查询  
    查询只需要访问索引，而无须访问数据行

因为索引树上节点是有序的，所以除了按值查找之外，还可用于查询中的ORDER BY（按顺序查找）。

B-Tree 索引的限制

*  如果不是按照索引的最左列开始查找，则无法使用索引
 *  不能跳过索引中的列
 *  如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找

#### 哈希索引 ####

基于哈希表实现，只有**精确匹配索引所有列**的查询才有效，对于区间查询不适合。

存储引擎对所有的索引列计算一个**哈希码**（hash code），哈希索引将所有**哈希码存储在索引中**，同时在哈希表中保存指向每个数据行的指针。

MySQL中只有Memory引擎显式支持哈希索引（默认索引类型），并且是支持非唯一哈希索引的，即使多个列的哈希值相同，索引会以链表方式存放多个记录指针到同一个哈希条目中。

举个例子，加入有如下表：

CREATE TABLE testhash (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    KEY USING HASH(fname)
    ) ENGINE=MEMORY;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2thcnJ5X3p6ag_size_16_color_FFFFFF_t_70 2]  
假设f()返回的值为：

> f(‘Arjen’)= 2323  
> f(‘Baron’)= 7437  
> f(‘Peter’)= 8784  
> f(‘Vadim’)= 2458

则哈希索引的数据结构如下：

即每个哈希码是顺序的，如果我们要查询

SELECT lname FROM testhash WHERE fname='Peter';

首先计算’Peter’的哈希值为8784，然后在哈希表中的值为“指向第3行的指针”，最后再比较第三行的值是否为’Peter’ ，以确保就是要查找的行。

哈希索引的限制：

*  哈希索引只包含哈希值和行指针；
 *  哈希索引无法用于排序；
 *  哈希索引不支持部分索引列匹配查找；
 *  哈希索引只支持等值比较查询；
 *  访问哈希索引数据很快，除非有很多哈希冲突，哈希冲突很多的话，其索引维护操作的代价也会很高。

InnoDB中有一种特殊的“**自适应哈希索引**”。一旦某些索引值用的比较频繁时，会在内存中基于B-Tree索引之上再创建一个哈希索引。

## 索引的优点 ##

索引可以让服务器**快速定位**到表的指定位置。但也有其他的附加作用。总之有如下三个优点：

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

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

如果一个表中数据量很少（非常小的表），那么大部分情况下简单的全表扫描会更高效。  
对于中到大型的表，索引就非常有效。  
但对于特大型的表，建立和使用索引的代价将随之增长。

## 高性能的索引策略 ##

### 独立的列 ###

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2thcnJ5X3p6ag_size_16_color_FFFFFF_t_70]: /images/20221014/94db6605a6b5485c85cccdd42c6f1ee2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2thcnJ5X3p6ag_size_16_color_FFFFFF_t_70 1]: /images/20221014/de2bd395dd5d4994a2d826657ccf02c6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2thcnJ5X3p6ag_size_16_color_FFFFFF_t_70 2]: /images/20221014/ad5ad1fb7ce047ac94e200bcca0e18e4.png