MySQL调优系列（五）——索引详解

蔚落 2024-03-22 18:04 53阅读 0赞

### 一、索引的定义 ###

数据库索引就像是书前面的目录，能加快数据库的查询速度。  
索引是一种数据结构，用于帮助我们在大量数据中快速定位到我们想要查找的数据。  
是一种帮助MySQL高效获取数据的**数据结构**。  
ps：大多数情况下，索引查询都是比全表扫描要快的。**但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。**

我们看一下之前一篇文章：[MySQL调优系列（四）——执行计划][MySQL]。  
里面有两段这个描述：  
![在这里插入图片描述][c627a302675848889c3ed5bf76e2ac60.png]  
这个地方要特别注意，因为执行计划可以看到我们的索引的使用。

### 二、索引的优缺点 ###

**优点：**  
1、加大检索速度（减少索引数据量），从数据结构是B+树就能看出。  
2、避免排序（order by，索引已经排好序了）、临时表（查询出来的临时存储的表）  
3、将随机 IO 变成顺序 IO  
4、创建唯一性索引，可以保证数据库表中每一行数据的唯一性。  
**缺点：**  
1、创建索引和维护索引需要耗费许多时间。当对表中的数据进行**增删改**的时候，如果数据有索引，那么索引也需要动态的修改，**会降低 SQL 执行效率**。  
2、索引需要使用物理文件存储，也会耗费一定空间。

### 三、索引的分类 ###

看分类之前，先把索引的数据结构列一下，详情参考：[数据结构之“树”——二叉树、红黑树、B树、B+树、B\*树][B_B_B]。  
![在这里插入图片描述][7f988386b52f4e9ab9df47377440d252.png]  
mysql有三个存储引擎，Innodb和Myisam的数据结构都是B+树（有区别，下面讲），Memory的数据结构是hash表，关于hash表，可参考：[HashMap源码解析（jdk1.8，万字大章，图文并茂）][HashMap_jdk1.8]。  
索引从不同维度，有不同的分类：  
**一般按应用维度区分：**  
1、主键索引：顾名思义，是主键来做索引，数据库会默认给主键列添加索引。  
2、唯一索引：唯一列添加的索引。  
3、普通索引：不是主键，也不是唯一列，又称二级索引或者辅助索引。  
4、组合索引：在某些情况下需要给多个列添加一个索引，此时就组成了组合索引。  
5、全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 es代替。  
6、覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。  
**按底层存储引擎的存储方式分：**  
1、聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。  
2、非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。  
这里先不急详解，我们先看索引的最佳的匹配方式。

### 四、索引匹配方式–组合索引-最左匹配 ###

先建表，如图：  
![在这里插入图片描述][77306ce1120943c8bc02b35d154c2447.png]

创建组合索引，**尽量选择长度比较小的列，占用空间较小。**：

ALTER table suoyin_test add INDEX zuhe(name,area,qq);

![在这里插入图片描述][ceb5825de99d48a2a664687f2681a2b8.png]

#### 1、全值匹配 ####

全值匹配指的是和索引中的所有列进行匹配。**尽可能多使用索引的列。**

explain select * from suoyin_test where name = '1' and area = '23' and qq = 'dev';

![在这里插入图片描述][5de8a12b2e8f480abd5aa28364b8c7eb.png]  
从图中可以看到：  
（1）type为ref：使用了非唯一性索引进行数据的查找。  
（2）possible\_keys和key为zuhe，是当前使用的索引。  
（3）ref是const，const，const，表示索引的三列都被使用了，且索引查找的是一个常量值。

#### 2、匹配最左前缀 ####

只匹配前面的几列。

explain select * from suoyin_test where name = '1' and area = '23';

![在这里插入图片描述][15cfccc408264e9f806ffa470821e0ce.png]

#### 3、匹配列前缀 ####

匹配某一列的开头部分，类似于模糊查询。

explain select * from suoyin_test where name = '1';
    explain select * from suoyin_test where name  like 'A%';
    explain select * from suoyin_test where name  like '%A%';

![在这里插入图片描述][49c118de57f6477982c1ac3e5d42557c.png]  
![在这里插入图片描述][1641df8b243a4aa88b9326adea73bae8.png]  
![在这里插入图片描述][6117e36b0d7346d18cd5e07ace205140.png]  
从上面可以看到，type在一步步的效率从好到坏。  
匹配列前缀可以达到range，避免了index的全索引扫描，但是若是模糊匹配前缀，会导致达到all级别，进行全表扫描，且索引压根没用到。  
所以，有个优化小tips，**查询语句尽量不要模糊匹配前缀。**

#### 4、匹配某个范围值 ####

explain select * from suoyin_test where name > '1';

![在这里插入图片描述][bf87b582a3e9487194f2f8ac3bd493ee.png]

#### 5、精确匹配某一列并范围匹配另外一列 ####

查询第一列的全部和第二列的部分。

explain select * from suoyin_test where name = '1' and area > '23';

![在这里插入图片描述][16685a0e99154e4f85e3bf5ac1931c30.png]

#### 6、按索引顺序匹配–最左匹配详解 ####

**必须要先匹配到第一个列之后才能匹配第二个列，无法直接匹配第二个列的值。**  
就是必须按name、area、qq这种顺序去执行索引查询，比如：

explain select * from suoyin_test where name = '1' and qq > '23';

结果是：  
![在这里插入图片描述][e01b7d11b6a640b6b5e7ee855c4a3bbd.png]  
它只用了name最左匹配，并没有用到qq。  
但是，如果三者都有，可以不管顺序，mysql会自动匹配顺序。

explain select * from suoyin_test where  qq = 11  and area = '23' and name = '1' ;

![在这里插入图片描述][e39cf2422f2146e5ba10efd2b4a6c64b.png]

explain select * from suoyin_test where  qq = 11  and area = '23';

![在这里插入图片描述][c309baaed5254b96b7a1c1eddb7e2574.png]

> 在使用组合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与组合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至组合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >、<）才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。所以，我们在使用组合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

#### 7、 只访问索引的查询 ####

查询的时候只需要访问索引，不需要访问数据行，其实就是覆盖索引。

explain select name,area,qq from suoyin_test where name = '1' and area = '23' and qq = 'dev';

### 五、典型索引详解 ###

#### 1、聚簇索引和非聚簇索引 ####

聚簇索引和非聚簇索引都是B+树的一种，只是存储的数据不同，**B+树是有序的。**

##### （1）聚簇索引 #####

**聚簇索引**即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。  
数据结构如图：  
![在这里插入图片描述][842634aa86b84d8e9178333d20623075.png]  
优缺点：**查询速度极快，更新代价大。**  
如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

##### （2）非聚簇索引 #####

**非聚簇索引**即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。  
**非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。**  
![fd9b0c93cf0747f880b2496512fdbd69.png][]  
组合索引如下：  
![在这里插入图片描述][f552147efa3443bc9c8e68080b13901e.png]  
优缺点：**查询速度较慢（可能触发回表），更新代价小。**

##### （3）回表 #####

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键 id 取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树（主键索引树）。  
但是，有一种情况不会触发回表，那就是覆盖索引。

#### 2、覆盖索引 ####

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为**覆盖索引**。这里可以看出，不需要去多扫描另一颗索引树了。

explain select name,area,qq from suoyin_test where name = '1' and area = '23' and qq = 'dev';

![在这里插入图片描述][ca86ab0ae0d34ac6a717f7814ad9176f.png]

#### 3、索引下推 ####

在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。这么说太笼统，我们从数据库的架构体系开始说。  
首先，从[MySQL调优系列（一）——性能监控][MySQL 1]可以知道，数据库架构是客户端—server层—存储引擎层，索引下推的意思，就是：  
**索引下推表示的是数据筛选的过程下移到存储引擎层来完成，而不是在 server层完成。**  
举个栗子：

explain select * from suoyin_test where name = '1' and area = '23';

**未索引下推：**  
1、从存储引擎层拿到符合name的结果加载到 server 端；  
2、在 server 端对 area字段进行条件筛选。  
**索引下推：**  
1、根据 name 和 age 的结果直接从存储引擎中返回所有符合条件的结果，无需在 server 端做任何的数据筛选工作。  
使用索引下推是能够提升整体查询的效率的，mysql 5.7 版本之后是默认支持的，无需做任何设置。

### 六、索引优化细节 ###

**1、尽量不要使用表达式**

我们先给qq加一个索引，然后查询：

explain select * from suoyin_test where  qq  = 23;

![在这里插入图片描述][d9702265b9964fbf8df7f7aa27bad5ec.png]

explain select * from suoyin_test where  qq+1 = 23;

![在这里插入图片描述][4e3f447f21144b91bf12078c62c963bb.png]  
可以看出，type为all，查询效率降低。  
**2、尽量使用主键查询，可减少回表**  
**3、可以使用前缀索引**

> 有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/\#T到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。  
> 一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

**4、使用索引扫描来做排序**  
创建组合索引的时候默认的索引排序是升序，所以后续用组合索引列进行排序要全部升序或者全部降序才会用到索引。  
如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序。  
extra：using filesort:说明mysql无法利用索引进行排序，**只能利用排序算法进行排序，会消耗额外的位置**。  
extra：null说明用到索引排序了。

explain select * from suoyin_test  ORDER BY qq desc;
    explain select * from suoyin_test  ORDER BY id;

![在这里插入图片描述][398a3ae928104de394fc1f4763260655.png]  
![在这里插入图片描述][e447b305184e4f0f88cb29b7a0716ad4.png]  
**5、union all,in,or都能够使用索引，但是推荐使用in。**  
**6、范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列。比如<><=>=between等。**  
**7、隐式转换会造成索引失效**

> 当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。 当 where  
> 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。

explain select * from suoyin_test where  name = 23;
    explain select * from suoyin_test where  name = '23';

**8、被频繁更新的字段应该慎重建立索引。**  
维护索引的成本是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。  
**9、选择合适的字段创建索引。**  
不为 NULL 的字段、被频繁查询的字段、频繁需要排序的字段、**被经常频繁用于连接的字段**等。  
**10、当需要进行表连接的时候，最好不要超过三张表**，**被经常频繁用于连接的字段**就特别适合索引。  
jion的三个结构如下：（以下来源：https://blog.csdn.net/main\_Scanner01/article/details/123786007）  
（1）Simple Nested-Loop Join(简单嵌套循环匹配)  
![在这里插入图片描述][dedbe74920c6406788d7f870627c5564.png]  
粗暴的进行连接，A每一行连接到非驱动表B的所有行。效率很低，占用内存很大。  
（2）Index Nested-Loop Join（嵌套循环连接）  
![在这里插入图片描述][f6078d69d8e94674b4770fc48458bdfa.png]  
通过外层表匹配条件直接与**内层表索引**进行匹配，避免和内层表每层记录进行比较，这样极大的减少了对内层表的匹配次数。  
条件就是要在内层表加索引，且连接条件是该索引字段，这样就现在A表查询，然后通过索引进行回表查询。  
如果被驱动表加索引，效率是非常高的，但如果索引不是主键索引，所以还得进行一次回表查询。相比，**被驱动表的索引是主键索引，效率会更高。**  
（3）Block Nest-Loop Join（块嵌套循环连接）  
![在这里插入图片描述][ea97a64524304d33976afc3475503f0d.png]  
不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存中操作)，**将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。**  
**11、能使用limit的时候尽量使用limit。  
12、限制每张表上的索引数量（不超过5个最好）。  
13、组合索引避免冗余（（name,qq）和（qq）这两个索引就是冗余索引）。  
14、索引不是越多越好，也不是越早优化越好（业务量上来之后，有了瓶颈再优化也不晚）。**

### 七、索引监控 ###

show status like 'Handler_read%';

![在这里插入图片描述][02d820b7c81b49cc89f2e2d979f99e81.png]

> Handler\_read\_first：读取索引第一个条目的次数，如果该值很高，那表明服务器正在执行很多全索引扫描  
> Handler\_read\_key：通过index获取数据的次数，。如果该值很高，那很好的说明了，对于执行的请求，表采用了适当的索引。  
> Handler\_read\_last：读取索引最后一个条目的次数  
> Handler\_read\_next：通过索引读取下一条数据的次数  
> Handler\_read\_prev：通过索引读取上一条数据的次数  
> Handler\_read\_rnd：从固定位置读取数据的次数  
> Handler\_read\_rnd\_next：从数据节点读取下一条数据的次数，该值很高，表明正在执行很多全表扫描。通常表明表没使用适当的索引或者查询请求没利用现成的索引。

[MySQL]: https://blog.csdn.net/liwangcuihua/article/details/130560503?spm=1001.2014.3001.5502
[c627a302675848889c3ed5bf76e2ac60.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/3d2fe300ab1e493ea9b8024943fe861d.png
[B_B_B]: https://blog.csdn.net/liwangcuihua/article/details/130615803?spm=1001.2014.3001.5501
[7f988386b52f4e9ab9df47377440d252.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/f255fcd00c9a46b196d23d978ca302af.png
[HashMap_jdk1.8]: https://blog.csdn.net/liwangcuihua/article/details/125725559?spm=1001.2014.3001.5501
[77306ce1120943c8bc02b35d154c2447.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/d11bacc41e814358a87232c2c3079759.png
[ceb5825de99d48a2a664687f2681a2b8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/316689c9744f4e31850344974b6868ea.png
[5de8a12b2e8f480abd5aa28364b8c7eb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/20f464d8fd8a42b3b6e9f3b1b947560b.png
[15cfccc408264e9f806ffa470821e0ce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/9f1100821b7346be9dc4d0346c85417c.png
[49c118de57f6477982c1ac3e5d42557c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/cc825b5e5094488c99b3863aa1c7a848.png
[1641df8b243a4aa88b9326adea73bae8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/8b79b3082428438c92e449fe7fcb5e5f.png
[6117e36b0d7346d18cd5e07ace205140.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/6f86d4a601e04e39a33cbbea5b24804b.png
[bf87b582a3e9487194f2f8ac3bd493ee.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/99e619680c2548a991612f0afd460f6a.png
[16685a0e99154e4f85e3bf5ac1931c30.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/9e01e13982e54c1aa8ef4ea9949ad3c5.png
[e01b7d11b6a640b6b5e7ee855c4a3bbd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/500d02f5d767467a8fb5836b30c1bb75.png
[e39cf2422f2146e5ba10efd2b4a6c64b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/b96e7887d4094052938720cf3c569f7b.png
[c309baaed5254b96b7a1c1eddb7e2574.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/1032cf2b2c544f938606d8aec12fd9fc.png
[842634aa86b84d8e9178333d20623075.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/b91f4970492e4f5195a35f97c67d6770.png
[fd9b0c93cf0747f880b2496512fdbd69.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/5349b3d950124bd9ae1d36ab83ff8733.png
[f552147efa3443bc9c8e68080b13901e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/8fec244d751f405a86c1c436915ff063.png
[ca86ab0ae0d34ac6a717f7814ad9176f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/751fd54e9b0047d5b5c72a5670d88d77.png
[MySQL 1]: https://blog.csdn.net/liwangcuihua/article/details/129011620?spm=1001.2014.3001.5501
[d9702265b9964fbf8df7f7aa27bad5ec.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/fb887a6117fc4636a6fc217aec32a960.png
[4e3f447f21144b91bf12078c62c963bb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/95064de4bad44572a6500dceefd5b8ea.png
[398a3ae928104de394fc1f4763260655.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/481a1c30d86241e098b9ca26d5902d26.png
[e447b305184e4f0f88cb29b7a0716ad4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/49b29828929f4191a9727d0b35a14759.png
[dedbe74920c6406788d7f870627c5564.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/baf33159b3e74b809fcbf1b2464da345.png
[f6078d69d8e94674b4770fc48458bdfa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/d86ef3315835497ba6c268f424bf2880.png
[ea97a64524304d33976afc3475503f0d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/0643e0eb6a8043f59c41816e7efd51a3.png
[02d820b7c81b49cc89f2e2d979f99e81.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/53b0f030553242af9601b2cc5a6142fd.png