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

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

---

目录

简介

索引的优缺点

语法

分类

聚集索引

非聚集索引

索引优化

Explain

不走索引的SQL

覆盖索引

最左前缀

索引下推

Change Buffer

优化建议

---

简介

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

在数据结构系列文章中我们提到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 

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

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