MySQL索引你真的会用吗？

❤️相信有一部分朋友一直是用到哪个字段查询就加给哪个字段加索引，因为我之前也是😄，今天这篇文章就结合MySQL底层的优化来讲解下如何创建索引，现在我们就开始吧❤️。

表构成

在InnoDB中，每一张表其实就是多个B+数，即一个主键索引树和多个非主键索引树。
如果不使用索引进行查询，则从主键索引B+树的叶子结点进行遍历

索引类型

主键索引（聚簇索引）

叶子节点存储的是整行的数据

非主键索引（二级索引）

叶子节点存储的是主键的值

回表

基于非主键索引查询，如果要查询的字段不在非主键索引中的非主键字段，就需要再根据主键的值去查询主键索引树，查询出其他字段的值，这个操作叫回表。
​

假设有一张表t_user，主键索引是id，name是非主键索引

create table t_user(
id int primary key, 
age int not null, 
name varchar(16),
index (name))engine=InnoDB;

当我们执行select * from t_user where id=1时，就只需要查询主键索引树；
当我们执行select id from t_user where name='零壹玖'时，就只需要查询name索引树即可；
当我们执行select * from t_user where name='零壹玖'时，就需要先查询name索引树，获取到主键的值，再根据主键的值查询主键索引树，最终查询到这一行的数据。

页分裂

前边已经讲过了B+树的每个叶子节点的大小都是一页，并且叶子节点是有序的，假如插入的值在已有数据中间，那么就需要挪动后边的数据，空出位置，此时，如果这个页已经满了，根据B+数的算法，这就是就需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂。
缺点：

1. 性能问题
2. 影响数据页的利用率
   

页合并

当相邻两个页由于删除了数据，利用率很低之后，就会将数据页做合并。

自增主键和非自增主键的优劣势

自增主键

插入新记录的时候可以不指定主键值，系统会获取当前ID最大值加1作为下一条记录的主键值，这样每次插入都是追加操作，不涉及到挪动其他记录，也不会触发页分裂。

非自增主键（业务主键）

• 不容易保证有序插入，写数据成本较高
• 浪费存储空间：假如业务主键的字段存储的字节数很大，那么每个二级索引的叶子节点占用也就会很大，而如果用整型做主键，则只要4个字节，如果是长整型（bigint）则是8个字节

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间就越小
适用场景：

1. 只有一个索引
2. 该索引必须是唯一索引

索引优化

覆盖索引

假如我们要查询的字段已经在索引树上了，比如主键字段或者是联合索引中的其中一个字段，这颗索引树已经“覆盖”了我们的查询需求，称为覆盖查询。
**优点：**避免了回表的性能消耗
什么时候使用联合索引？
当有一个高频请求，要根据字段a查询字段c，这时候就可以建一个（a,c）的联合索引，避免回表。总之，建立联合索引需要权衡利弊，如果一味的建立联合索引，反而导致存储空间增加，得不偿失。

前缀索引(最左前缀原则)

B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录，这里的“最左前缀”可以是“最左边的几个字符串”，也可以是“最左边的几个字段”。


例如（name,age）索引，当查询name='张三’的人时，可以通过联合索引快速定位；而如果查询name like ‘%张’ 的人时，也能用上这个索引，顺序遍历叶子节点，知道不满足条件为止，可以看到不一定是满足索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。
​

联合索引的索引顺序怎么安排？
首先考虑字段查询频率、查询频率相同的情况下考虑空间占用。
由于有最左前缀匹配原则，那么我们就可以省一个索引，省的索引就是联合索引前边的字段，例如业务经常需要根据a字段查询，并且也会使用a+b查询，那么建立联合索引时把a放到前边就是一个好办法。
但是如果既有经常根据a又有经常根据b查询的业务，这时候就要建立一个a或者b的索引，那就要考虑空间的代价，比如a是一个大字段，b是一个小字段，那么就要尽量减少a索引树的大小，因此建立索引时就建议（a,b）（b），将b索引单独创建，减少大字段a的索引。

索引下推

索引下推是从MySQL5.6开始引入的减少回表次数的优化。
假设目前有张用户表t_user，字段为id，name、age，主键索引为id，二级索引为（name,age）表中有以下几条数据

id	name	age
1	张三	26
2	李四	25
3	张五	24
4	张6	24

假如要筛选表中“姓张的，而且年龄是24岁的人”，SQL语句如下：

select * from t_user where name like '张%' and age=24

这个SQL因为最左前缀匹配原则，会走二级索引，可以找到第一个id=2的记录，那怎么判断age这个条件是否满足呢？

• 在MySQL5.6之前，因为InnoDB的查询接口在5.6之前只支持传“搜索关键字”，InnoDB在执行查询时只能从id=1这行记录开始一个一个回表，到主键索引上找出对应的数据行，再对比字段值，以上这个例子就需要回表3次。
• 从MySQL5.6开始，InnoDB的接口能力做了提升（个人猜测是增加了参数数传递），索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，以上这个例子就只需要回表两次，id=1的记录在遍历联合索引树的时候就判断了age不符合条件，也就不回表了。
  

重建索引

重建语句

alter table T drop index k;
alter table T add index(k);

为什么需要重建索引？
索引可能因为删除，或者页分裂等原因，导致数据空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。
​

重建二级索引和重建主键索引
重建主键不合理，不论是删除主键还是创建主键，都会将整个表重建。可以使用alter table T engine=InnoDB​