01
前言
本篇来讲述一下数据库进阶方面的知识，那就是 索引（index） ， 索引不管是在面试中，还是在实际开发过程中，都是重中之重，索引用的好，能够帮助你在公司里升职加薪。
本篇文章内容包含MySQL调优核心知识点： explain执行计划、索引失效场景、索引覆盖、索引下推 等，干货满满！
02
什么是索引
索引是数据库管理系统中用于提高数据检索效率的一种数据结构。它可以帮助快速定位到表中的特定数据行，类似于书籍的目录，允许直接跳转到感兴趣的内容，而不需要逐页浏览整个书籍。
在数据库中，索引通常包含以下几个关键特性：
数据结构：索引通常采用B树（B-Tree）、B+树、哈希表等数据结构来组织数据，这些结构能够高效地支持查找、插入和删除操作。
关键字：索引是基于表中的一个或多个列（字段）创建的，这些列被称为索引的关键字或索引列。索引会按照关键字值的顺序来存储数据。
指针：索引中的每个条目不仅包含关键字值，还包含指向实际数据行的指针。通过这些指针，数据库能够直接访问到对应的数据行。
唯一性：索引可以是唯一的，也可以不是。唯一索引确保了索引列中的每个值都是唯一的，从而防止了表中出现重复的数据行。
索引分类
索引的类型和用途包括：
主键索引 ：自动创建在主键字段上，确保了主键的唯一性和数据的完整性。
唯一索引 ：用于确保索引列中的值是唯一的，但不一定是主键。
普通索引 ：用于提高查询性能，但不保证索引列值的唯一性。
全文索引 ：用于全文搜索，允许在文本数据中进行复杂的搜索。
复合索引 ：基于表中的多个列创建，适用于同时基于这些列的查询。
注：在InnoDB引擎中，主键索引就是聚簇索引，非聚簇索引指的是非主键索引（辅助索引，二级索引），默认都是基于B+树实现的。
聚簇索引和非聚簇索引的区别
存储方式不同：
聚簇索引（Clustered Index） ：在聚簇索引中，索引的叶节点直接包含表中的实际数据行。这意味着数据行是根据聚簇索引的顺序物理存储在磁盘上的。因此，一个表只能有一个聚簇索引，因为数据行只能有一种物理排序方式。
非聚簇索引（Non-Clustered Index） ：非聚簇索引的叶节点不包含数据行，而是包含索引键值和指向数据行的指针。数据行的物理存储顺序与索引顺序无关。一个表可以有多个非聚簇索引，每个索引都是基于不同的列或列组合。下图为它们的结构图，查询一个名叫张三的过程。
数量限制：
一个表只能有一个聚簇索引，但可以有多个非聚簇索引。
性能影响：
聚簇索引通常对范围查询和排序操作有利，而非聚簇索引则更适合点查询（即查询特定值）。
数据移动：
创建或删除聚簇索引可能会导致大量数据的物理移动，这可能会是一个耗时的操作。而非聚簇索引的创建和删除通常不会导致数据行的物理移动。
03
SQL调优
介绍了完索引的基本概念后，那么在实际运用过程中，索引使用了就会提高性能吗，不一定的，因为索引可能会失效。通过explain来分析SQL到底走索引没有。
什么字段适合创建索引
先来分析一下什么字段适合创建索引，首先，毋庸置疑的是主键（主表的唯一id）和外键（用于主表和外表建立联系的字段）是肯定要创建索引的。
其次是一些参与查询比较多的字段，也适合创建索引，提高查询效率，还有经常参与排序的字段，比如订单的创建日期，添加索引后能够加快排序的效率。
对于一些区分度不是很高的，就不适合创建索引了，反而会占用资源，比如性别字段，只有1或者0。
对于大字段，也不适合创建索引，比如类型为text的大文本，创建索引后需要扫描更多的数据块，反而更慢。
explain执行计划
explain SQL语句
MySQL的执行计划来查看索引是否生效，主要观察几个点:
type(访问类型) :这个属性显示了查询使用的访问方法，例如ALL、index、range等。当查询使用索引时，这个属性通常会显示为index或range ，表示查询使用了索引访问。如果这个值是ALL，则表示查询执行了全表扫描，没有使用索引。
key(使用的索引) :这个属性显示了查询使用的索引，如果查询使用了索引，则会显示索引的名称。如果这个值是NULL，则表示查询没有使用索引。
rows (扫描的行数) :这个属性显示了查询扫描的行数，即查询返回的行数，需要评估下扫描量。
索引失效的场景
下面来总结几个常见的索引失效场景：
（1）不符合最左前缀
在user表创建了一个(name,age)的联合索引，但是在sql中的语句如下
select * from user where age = 23;
显然是不满足最左前缀匹配原则，所以索引失效，导致全表扫描。
优化后，生效的sql如下：
select * from user where age = 23 and name = '张三'; #sql执行器会进行优化，先后顺序不影响
select * from user where name = '张三' and age = 23;
select * from user where name = '张三';
（2）索引使用了运算或者函数
现在user表中，name和age都是索引列，下面sql会导致索引失效
select * from user where age + 2 = 24; //age参与运算，导致索引失效
select * from user where LOWER(name) = 'JACK' //name字段使用了转大写函数，索引失效
（3）like首位使用了%
因为索引是从左到右进行排序查找的，如果首位使用了%模糊查询，就直接导致全表扫描了。
select * from user where name like '%武%'
（4）or的不规范使用
现在user表只有一个age为索引列，下面的sql会导致索引失效。因为or关键字前后都必须保证是索引才能生效。
select * from user where age = 23 or name = '王五';
还有一种是or前后出现><这种范围查询，也会失效。
select * from user where age > 2 or age < 25;
（5）类型转换
user表中id为bigint类型，然而在sql查询时，使用了''包裹，sql会触发隐式转换为bigint类型，这时索引不会生效。
select * from user where id = '78';
（6）不同字段进行比较
直接拿两个不同的字段进行比较，索引直接失效
select * from user where id > age;
（7）使用了!=、<>
在MySQL中，!=、<>都是不等于的意思，如果索引列使用了这些关键字，会导致索引失效。
select * from user where name !='张三';
（8）使用了is not null
注意使用了 is not null 会直接导致失效，而使用 is null则不会。因为当使用 is not null条件时，数据库系统需要扫描整个索引，找出所有不为null的值，这可能会导致索引失效。使用is null条件不会导致索引失效，因为对于索引而言，查找null值的行与查找其他任何特定值的行都没有本质区别。在大多数情况下， null值都会被索引包括在内。
索引覆盖
索引覆盖是指二级索引中包含了所需查询的所有字段，无须回表，从而使查询通过访问二级索引而不需要访问表数据（主键索引）。
这里需要用到上面的结构图，可以看到sql查询首先是经过二级索引（辅助索引）再到主键索引中查询数据的，那么如果我想要的数据在二级索引中就拿到了，就不需要再去主键索引中查了。
比如现在有一个user表，字段分别有：id、name、age、address、birthday、create_time
其中name,age,address都是索引列
下面这个sql就是索引覆盖，select后的字段都是索引列，在二级索引就可以查询出结果，无须再回表查询索引
select id,name,age,address from user; //索引覆盖，无须回表
覆盖索引的优点：
减少I/O操作:因为查询可以直接从索引中获取所有需要的数据，避免了访问实际表的数据页，从而减少了I/O操作。
提高查询速度:索引比表数据更紧凑，因此从索引中读取数据比从表中读取要快。
减少内存占用:只需要读取索引页而不是表数据页，可以减少内存占用。
索引下推
索引下推是一种减少回表查询，提高查询效率的技术。通过将部分带有索引的条件交由存储引擎层进行过滤操作，从而减少了IO(本该由 Server层做操作，交由存储引擎层因此叫做下推)。 前提：索引下推是应用在联合索引上的。
现在有一个用户表，字段分别为：id、name、age、address，现在创建了一个联合索引，（age,address）年龄和地址的联合索引。
现在有一个查询需求，就是查询年龄在18-26岁的，并且地址为河南省的用户数据。
select * from user
where age BETWEEN 18 AND 26 and address = '河南省';
假如没有索引下推，我们先来分析一下这条sql的执行过程：
（1）通过age字段找到了多条匹配的数据，假如找到了5条，分别为
【1 河南省】，【3 江西省】，【5 河南省】，【6 北京市】，【7 河南省】
（2）对每条数据，再通过id主键回表，到主键索引查询数据，一共需要回表5次。
（3）再将数据交给Server层，并将address = '河南省' 数据过滤出来得到最终的数据行。
那么现在我们引入索引下推，再来分析一下sql的执行过程：
（1）通过age字段找到了多条匹配的数据，假如找到了5条，分别为
【1 河南省】，【3 江西省】，【5 河南省】，【6 北京市】，【7 河南省】
（2）因为联合索引包含address，存储引擎通过address='河南省'来进行过滤数据，得到【1 河南省】，【5 河南省】，【7 河南省】三条数据。
（3）最后拿到这三条数据的id主键进行回表查询得到数据行，回表次数变为了3次。
注意事项：
索引下推在MySQL5.6及以后的版本支持，InnoDB和MyISAM这两个存储引擎都生效。
如果查询中引用了子查询索引下推可能不会生效，具体看explain.
使用了函数或表达式索引下推也不能生效，这个和是否能命中索引的条件是一样的。
使用了聚簇索引(主键)查询，索引下推也不会生效，因为其是对于非聚簇索引来进行减少回表次数。