MySQL（一）、InnoDB索引原理及优化

淡淡的烟草味﹌ 2022-11-26 07:47 239阅读 0赞

MySQL的索引可以大大提高MySQL的检索速度，在大厂的面试中也多会问关于索引和事务相关的问题，本篇我们来详细介绍MySQL中的索引及其使用和优化。

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

**目录**

简介

索引的优缺点

语法

分类

聚集索引

非聚集索引

索引优化

Explain

不走索引的SQL

覆盖索引

最左前缀

索引下推

Change Buffer

优化建议

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

# 简介 #

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

在[数据结构系列文章][Link 1]中我们提到B+树这种数据结构，在MySQL中InnoDB存储引擎的索引底层就是使用B+树来实现的，由于MySQL5.1之前默认引擎是MyISAM，之后就是InnoDB了，所以我们本篇也主要围绕InnoDB的索引来介绍，在后面的文章中再对比两个引擎的区别。

## **索引的优缺点** ##

**优点**：加快数据的检索速度；

**缺点**：索引需要占物理空间；执行DML时由于索引需要动态维护，会降低数据写入速度；创建索引和维护索引要耗费时间，并随着数据量的增加而增加耗时；

## **语法** ##

**创建索引**

CREATE INDEX indexName ON table_name (column_name);

**添加索引**

ALTER table tableName ADD INDEX indexName(columnName);

**创建表的时候指定索引**

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
    );

**删除索引**

DROP INDEX [indexName] ON mytable;

**查看索引**

SHOW INDEX FROM table_name;

# 分类 #

**逻辑角度**

**普通索引**：最基本的索引，没有任何限制；

**唯一索引**：索引列的值必须唯一，允许有空值，如果是组合索引，则列值的组合必须唯一；

**主键索引**：特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值；

**联合索引**：也叫复合索引，由多个字段建立的索引，能够加速复合查询条件的检索；

**全文索引**：可以针对值中的某个关键字进行索引查询，但效率较低，且不支持中文；

**物理存储角度**

**聚集索引**（Clustered Index）：聚集索引决定数据在磁盘上的物理排序，一个表只能有一个聚集索引。

**非聚集索引**（Non-clustered Index）：非聚集索引并不决定数据在磁盘上的物理排序，索引上只包含被建立索引的数据，以及一个行定位符 row-locator，这个行定位符，可以理解为一个聚集索引物理排序的指针，通过该指针可以找到行的数据。

> InnoDB索引分类：[https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html][https_dev.mysql.com_doc_refman_8.0_en_innodb-index-types.html]

从数据结构上看，**聚集索引的树的叶子节点存储整行数据，而非聚集索引的树的叶子节点只存储其对应的主键的值**，因此非聚集索引也被称为二级索引。同时由于非聚集索引树上并没有完整的数据，当查询走的是非聚集索引时，会在非聚集索引树上找到其对应的主键后，再返回到聚集索引树上根据主键获取所需得数据，这个过程称为**回表**。

提问：为什么在非聚集索引的叶子节点存储对应主键的值而在聚集索引叶子节点才存储行数据呢？

答：其实是为了保证数据一致性、提升写效率和空间利用率 。当修改数据时只需要在聚集索引树上修改对应的数据即可，由于非聚集索引指向聚集索引，因此无需在非聚集索引上再做任何的操作。

## **聚集索引** ##

InnoDB中，聚集索引就是主键索引，即当建表的时候定义了主键，则主键就是该表的聚集索引，如果没有创建主键，为了保证数据在磁盘上的物理有序及回表的正常使用，MySQL会使用row\_id列生成一个隐式的聚簇索引，数据会按照row\_id进行排序。因此为了提升索引效率和空间利用率，InnoDB引擎的表必须要有主键（即使没有也会隐式生成一列），且最好是整型的自增主键。

由于B+树自平衡会进行页分裂和合并，在大量操作之后会在很多页中留下空余的空间，浪费空间，我们可以使用下面的命令进行碎片整理，重新整理在聚簇索引上的数据和索引：

ALTER TABLE tbl_name ENGINE= InnoDB；

## 非聚集索引 ##

非聚集索引也被称为二级索引，非聚集索引的树的叶子节点只存储其对应的主键的值。

# 索引优化 #

## Explain ##

使用explain命令查看执行计划，通过执行计划来分析SQL是否走了索引，借此可以帮助我们选择更好的索引和写出更优化的查询语句。

explain select * from csdn where uname='wyk';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70][]

**select\_type：**  
\- simple:简单select（不使用union或子查询）。  
\- primary:最外面的select。  
\- union:union中的第二个或后面的select语句。  
\- dependent union:union中的第二个或后面的select语句，取决于外面的查询。  
\- union result:union的结果。  
\- subquery:子查询中的第一个select。  
\- dependent subquery:子查询中的第一个select，取决于外面的查询。  
\- derived:导出表的select（from子句的子查询）。

**table:** 数据是关于哪张表的 。

**type:**  显示连接使用了哪种类型。从最好到最差的连接类型为system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > range > index > ALL，const代表一次命中，ALL代表扫描了全表才确定结果。一般来说，得保证查询至少达到range级别,最好能达到ref。

**possible\_keys:** 指出MySQL能使用哪个索引在该表中找到行。如果是null表示没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。

**key:** 实际使用到的索引。如果是null则表示没有使用索引。如果为primary的话，表示使用了主键索引。

**key\_len:** 最长的索引宽度。如果键值是null，长度就是null。在不损失精确性的情况下，长度越短越好。

**ref:** 显示哪个字段或常数与key一起被使用。

**rows:** 表示mysql要遍历多少数据才能找到，在innodb上是不准确的。

**Extra:** 执行状态说明。当出现"Using filesort"或"Using temporary"时候就表示查询需要进行优化。

## **不走索引的SQL** ##

-- 1.所有索引列参与了计算，不使用索引
    SELECT `sname` FROM `stu` WHERE `age`+10=30; 
    
    -- 2.索引列使用了函数运算，不会使用索引 
    SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; 
    
    -- 3.模糊查询时，索引列后面加%，走索引 
    SELECT * FROM `csdn` WHERE `uname` LIKE 'Rick%' 
    
    -- 4.模糊查询时，索引列前面加%，不走索引 
    SELECT * FROM `csdn` WHERE `uname` LIKE "%Rick%" 
    ------------------------------------------------
    CREATE TABLE `csdn` (
        `uname` char(10) not null,
        `uage` char(10),
        `company` char(10),
        PRIMARY KEY (uname),
        INDEX(uage)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- 5.字符串与数字比较，不使用索引; 
    EXPLAIN SELECT * FROM `csdn` WHERE `uage`="28" -- 走索引 
    EXPLAIN SELECT * FROM `csdn` WHERE `uage`=28   -- 不走索引 
    
    -- 6.如果条件中有or，即使条件中有的列带索引也不会使用，因此建议避免使用or关键字
    select * from csdn where uname='wang,yikai' or age='28' or company='csdn'; 
    
    -- 7.如果mysql预估使用全表扫描要比使用索引快，则不使用索引
    -- 8.正则表达式不使用索引

## 覆盖索引 ##

覆盖索引不是索引的类型而是一种查询效果，在使用覆盖索引时，Explain查看执行计划时Extra列会出现"Using index"，覆盖索引可以减少树的搜索次数，只需要读取索引，而无需读表，极大减少数据访问量显著的提升查询性能。

意思是该查询所需的数据在非聚簇索引树上就可以得到该查询所有想要的数据，因此当需要的数据都在索引列上的时候不要用类似select \*的方式去查询其他无关的字段。如：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 1][]

## 最左前缀 ##

MySQL中建立多列索引（联合索引）有**最左前缀**的原则，即最左优先。

最左前缀：[https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html][https_dev.mysql.com_doc_refman_8.0_en_multiple-column-indexes.html]

如：

*  索引为(col1,col2)，表示已经对(col1)、(col1,col2)建立了索引；
 *  索引为(col1,col2,col3)，表示已经对(col1)、(col1,col2)、(col1,col2,col3)建立了索引；

这是由于B+树中的关键字是复合的数据结构，如上面的例子(col1,col2,col3)，B+树按照从左到右的顺序来建立搜索树，当拿(val1,val2,val3)数据进行检索的时候，B+树会优先比较 val1 来确定下一步的搜索方向，如果 val1 相同再依次比较 val2 和 val3，最后得到检索的数据；但当 (val2,val3) 进行检索的时候，B+树不知道第一步该查哪个节点；同样的，当(val1,val3)进行检索时，B+树可以用 val1 指定搜索方向，但由于没有col2的值，所以只能遍历col1=val1的所有索引节点，然后再匹配col3=val3 的数据；

也正因如此，为了使用到最左前缀原则，在建立联合索引的时候，建议将区分度高的放在左边，提升SQL效率。

> SQL的where语句中字段的顺序不需要和联合索引中所定义的字段顺序一致，MySQL查询优化器会自动调整顺序：
> 
> 如：索引(col1,col2,col3)，SQL命令中where col3=xx and col1=yy and col2=zz 也是会按照col1,col2,col3的顺序进行索引查询的。

## 索引下推 ##

MySQL 5.6 引入的**索引下推**（Index Condition Pushdown)简称**ICP**，在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的次数。

索引下推：[https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html][https_dev.mysql.com_doc_refman_8.0_en_index-condition-pushdown-optimization.html]

如：csdn表(id,uname,uage,ucompany) 的索引列为 (uname,uage)，查询语句为 *select \* from csdn where uname like 'wyk%' and uage='10';  *根据最左前缀原则，使用了联合索引(uname,uage)，性能高于全表扫描。

**无ICP**: 在非聚集索引上找到所有uname匹配'wyk%'的主键ID然后进行回表，从聚集索引上找到数据行，再根据uage='10'过滤数据；

**有ICP**：在非聚集索引上找到所有uname匹配'wyk%'且uage=10的主键ID进行回表，然后从聚集索引上找到数据行；

显而易见，在有ICP的情况下，将可以在二级索引（非聚集索引）上进行的过滤提前过滤，减少了回表的操作，大大的提升了查询效率。

默认情况下，索引下推是开启的，使用下面的命令选择将其开启或关闭（建议开启）：

SET optimizer_switch = 'index_condition_pushdown=off';
    SET optimizer_switch = 'index_condition_pushdown=on';

## Change Buffer ##

与聚簇索引不同，二级索引通常不是唯一的，并且插入二级索引的顺序相对随机。Change buffer的主要目的是将对二级索引的数据操作缓存下来，以此减少二级索引的随机IO，并达到操作合并的效果。MySQL5.5之前的版本中，由于只支持缓存insert操作，所以最初叫做Insert Buffer，后来的版本中支持了更多的操作类型缓存，才改叫Change Buffer。

Change Buffer：[https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html][https_dev.mysql.com_doc_refman_8.0_en_innodb-change-buffer.html]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 2][]

将Change Buffer中的操作应用到磁盘数据页的操作被称为**Merge** 操作：

*  系统空闲时会定期将更新的索引页写入磁盘；
 *  访问数据页时会触发Merge；
 *  数据库关闭时会先进行Merge；

> 由于唯一索引的每次操作都会判断唯一性，在写入之前就已经将数据页读入缓存，而无需使用Change Buffer，所以唯一索引是不支持Change Buffer的，只有二级索引支持。

Change Buffer 用的是 Buffer Pool 里的内存，因此不能无限增大。通过参数** *innodb\_change\_buffer\_max\_size*** 进行设置：

-- 25表示Change Buffer最多占用Buffer Pool的25%
    set @@global.innodb_change_buffer_max_size = 25;
    show variables like  'innodb_change_buffer_max_size';

也正是因为Change Buffer占用了Buffer Pool， 减少了可用于缓存数据页的内存，根据实际场景，我们可以选择对哪些操作应用Change Buffer，使用参数***innodb\_change\_buffering***进行设置：

set @@global.innodb_change_buffering = 'all';
    show variables like  'innodb_change_buffering';
    -- 默认值：所有操作
    all
    
    -- 不缓冲任何操作
    none
    
    -- 仅缓冲插入操作
    inserts
    
    
    -- 仅缓冲删除标记操作
    deletes
    
    
    -- 缓冲插入和删除标记操作
    changes
    
    
    -- 缓冲在后台发生的物理删除操作
    purges

## 优化建议 ##

摘自阿里巴巴开发手册：

**1. 在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。**

*  说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(\*)的区分度来确定。

**2. 严禁左模糊或者全模糊查询，如果需要请通过搜索引擎Lucene解决。**

*  说明：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

**3. 如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file\_sort的情况，影响查询性能。**

*  正例：where a=? and b=? order by c; 索引：a\_b\_c
 *  反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a\_b无法排序。

**4. 利用覆盖索引来进行查询操作，避免回表。**

*  说明：如果一本书需要知道第11章是什么标题，会翻开第11章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。
 *  正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用explain的结果，extra列会出现：using index。

**5. 建组合索引的时候，区分度最高的在最左边。**

*  正例：如果where a=? and b=? ，a列的几乎接近于唯一值，那么只需要单建idx\_a索引即可。
 *  说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where a>? and b=? 那么即使a的区分度更高，也必须把b放在索引的最前列。

**6. 防止因字段类型不同造成的隐式转换，导致索引失效，如where 字符串类型字段=整型值。**

希望本文对你有帮助，请点个赞鼓励一下作者吧~ 谢谢！

[Link 1]: https://blog.csdn.net/wsdc0521/article/details/107800168
[https_dev.mysql.com_doc_refman_8.0_en_innodb-index-types.html]: https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70]: /images/20221124/2d95a8a5385140ea9a18672b96ae9bcc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 1]: /images/20221124/ba567acab04041498341dc0fbc854fd2.png
[https_dev.mysql.com_doc_refman_8.0_en_multiple-column-indexes.html]: https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html
[https_dev.mysql.com_doc_refman_8.0_en_index-condition-pushdown-optimization.html]: https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html
[https_dev.mysql.com_doc_refman_8.0_en_innodb-change-buffer.html]: https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 2]: /images/20221124/2cb06c620ec6495aa6aa7b5784a48f37.png