MySQL之索引篇

索引介绍

索引是帮助Mysql高效获取数据的数据结构，就好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。索引底层数据结构存在许多类型，常见的有B+Tree、BTree、红黑树和哈希表等等，Mysql中无论是InnoDB或MyIsam使用的都是B+Tree。

索引的优缺点

优点

• 大大加快数据检索速度，降低IO成本。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

缺点

• 创建索引和维护索引需要时间成本，成本随着数据量增大而增大。
• 索引占用磁盘空间。

使用索引一定会性能提升吗？

不一定，如果数据库数据量量不大的情况下，建立索引不一定会带来性能的提升

索引的数据结构

哈希结构

哈希结构是键值对的结构，如果使用哈希结构存储表数据key作为索引列，而value可以存储行记录。在保证哈希算法能够均匀散列的情况下，Hash表的查询时间复杂度为O(1)；但是当出现哈希冲突时，通常会使用链地址法解决冲突，这样时间复杂度就不一定是O(1)了。

为什么MySQL不适用哈希结构呢

• 哈希查询每次IO只能查询一条数据
• 并且它不支持顺序和范围快速查找，如下图所示，每个数据经过哈希算法计算存入表中位置都是随机的，你无法通过范围快速获取，而是对范围内的每个值进行哈希并获取对应的哈希值才能逐一获取到数据，所以范围查找时还是只能通过扫描全表方式。

BTree&B+Tree

B树也称B-树，B+树是B树的一种变体，两者都是多路平衡查找树，整体总结以下几点区别：

• B树的每个节点存放的都是key和data，而B+树则只有叶子节点存放key和data，其他节点存放key。
• B树的叶子节点都是独立的，而B+树的叶子节点有一个引用链指向与它相邻的叶子结点
• B树的查询相当于对范围内的节点做二分查找，可能还没到叶子节点就结束了，而B+树必须到达叶子节点才能找到data，而且因为B+树叶子节点之间还存在引用链，所以范围查找还是很有优势的。
  B树结构简化版如下：
  

B+树结构简化版：

MySQL5.5前的存储引擎使用的MyISM引擎，5.5后使用的InnoD引擎，而这两个引擎都是使用了B+Tree作为索引结构，但是MyISM引擎中会根据搜索算法查找搜索索引，若指定的key存在，则可以从其data域中获取到数据的地址，然后通过地址读取相应的数据记录，它的索引文件和数据文件是分离的，这也就是我们常说的非聚簇索引。相比之下InnoDB的底层数据结构就比较特殊了，它的数据文件就是索引文件，其文件本身就是按照B+Tree组织的一个索引结构，这棵树的叶子节点的data域保存的都是完整的数据记录。那么InnoDB表数据文件本身就是主索引，这也是常说的聚簇索引。而其余索引则是辅助索引，所以当我们使用主索引查找数据时，可以直接通过key获取到对应的数据；而使用辅助索引查找时，则需要先取出主键的key，然后再根据key去找到对应的数据（即再走一遍主索引）。所以我们在设计表时，不建议使用过长的字段作为主键，也不要使用那么重非单调的字段作为主键。

MyISAM中主键索引

InnoDB中主键索引

索引类型

有这两种类型的索引：

主键索引（Primary Key）

在InnoDB引擎的索引结构就是基于主键索引构成的，主键索引要求索引字段不唯一且不为空。如果设计的表中没有指定表的主键时，InnoDB中会自动检查表中是否有唯一且不允许为空的字段，将其设为主键，否则会自动创建一个64Byte的自增主键。

二级索引

二级索引又称辅助索引，二级索引的叶子结点保存的数据不是当前记录的数据而是当前记录的主键索引，因此我们可以通过二级索引的data域定位到主键的位置，然后通过主键获取到数据。以下索引皆属于二级索引：

• 普通索引：用于提高查询速率，允许在定义索引的列中插入重复值和空值。
• 唯一索引（Union Key）：索引列中的值必须是唯一的，但是允许为空值。
• 前缀索引（Prefix）：前缀索引只适用于字符串类型的列，可以对字符串前N个字符创建索引，这种只取前N个字符的索引相比普通索引使得索引文件小很多。
• 全文索引（Full Text）：全文索引只适用于文本类型（如CHAR、VARCHAR、TEXT）的列，用以加快查询速度，Mysql5.6之前只有MyISAM适用，5.6之后InnoDB也支持。
  

聚簇索引与非聚簇索引

聚簇索引

聚簇索引（Clustered Index）其实就是索引结构和数据一起存放的索引，塔不是一种索引类型，在InnoDB中主键索引就是聚簇索引，它的文件后缀为.ibd。对于InnoDB来说，所有非叶子节点中存放的都是索引，而叶子节点则是存放着索引和索引对应数据。那么使用聚簇索引有以下几个优缺点：
优点：

• 查询速度较快。因为B+树本身是个多路平衡树，并且叶子节点也是有序的，所以我们可以快速定位到索引从而在叶子节点找到数据。相比非聚簇索引少了一次IO。
• 对主键排序查找及范围查询速度比较快。

缺点：

• 依赖有序数据，对于非有序的数据在插入时就需要排序，如果是字符串或UUID又长又难比较的数据插入和查询速度比较慢。
• 更新代价大，因为数据和索引都在同一个文件，如果对索引列进行更新，则需要同步更新索引结构，频繁的操作会带来巨大的开销。

非聚簇索引

非聚簇索引(Non-Clustered Index)则是索引结构和数据分开存放的，也不是一种索引类型，在InnoDB中的二级索引（辅助索引）就是非聚簇索引，在MyISAM中无论是主键还是非主键都非聚簇索引。非聚簇节点的叶子节点存放的不一定是数据的指针，例如二级索引，它存放的就是索引以及索引对应主键的值。通过这个值我们可以通过回表查询到对应的数据。总的来说非聚簇索引有以下几个优缺点：
优点：

• 更新代价比聚簇索引小，因为数据和索引文件是分开存放的，非聚簇索引的叶子节点不存放数据。

缺点：

• 依赖有序数据。
• 查询可能会回表操作。

非聚簇索引一定回表查询吗(覆盖索引)?

不一定会回表，如果查询的列就是索引列，那么直接返回。例如

select name from user where 那么=‘zayton squid’;

如果name字段建立了索引，那么这个索引的key本身就name，所以查询到的name直接返回即可。

覆盖索引和联合索引是什么

覆盖索引

我们查询的字段包含在建立的索引中，这种索引就是覆盖索引；而这种查询操作无需回表就能直接获取到数据的操作就是索引覆盖。而我们所说的回表就使用二级索引查询定位到主键的值，然后通过这个值去主键索引中定位获取到数据，

联合索引

联合索引就是多个字段组合成一个索引，它使用原则是需要遵循最左匹配原则的。

我们举几个覆盖索引的例子

为了演示我们先创建一张user表，然后设置Id为主键，并插入几条数据。

# 创建用户表
create table user (
    id   int(11)  NOT NULL   PRIMARY KEY ,
    name varchar(10) NOT NULL,
    age  int(11) NOT NULL,
    gender varchar(2) DEFAULT NULL,
    index age (age)
) engine=InnoDB ;
# 新增几条数据
insert into user values (1,'章鱼哥',20,1),(2,'海星',22,1),(3,'海绵',25,1),(4,'草履虫',35,0),(5,'螃蟹',34,0);

我们先看一下这条SQL，可以看出查询条件用的是索引name，他希望获取到age在10到25之间的数据。

select * from user where age between 20 and 25;

结合上面的数据表，这条SQL我们可以得到下面一段流程步骤：

由此可以看出这条查询语句读了age索引树的三条记录，并且回表了三次，这样的性能是很差的。
再来看看这条SQL:

select id from user where  age between 20 and 25;

这条SQL的执行流程如下：

这条SQL要查询的刚好是ID，而通过我们的age索引树就可以直接定位到id的值了，无需进行回表，这就是覆盖索引。

再聊一下联合索引的例子

我们基于上面的数据表创建一个（age，name）这两个字段的联合索引。

alter table user add index name_gender(name,gender);

联合索引是遵守最左匹配原则的，当我们建立了联合索引之后，相当于创建了(name)这个单列索引，(name,gender)这个联合索引。所以下面这三种情况都走会索引的。

select * from user where name = '章鱼哥';
select * from user where name = '章鱼哥' and age = 20;
select * from user where age > 18 and name = '章鱼哥';

而这种情况就不会走索引了

select * from user where gender = 1;

最左匹配原则是什么？

最左匹配原则即在使用联合索引时，基于联合索引中字段顺序从左到右依次到查询条件中匹配，直到遇到范围查询（如>、<）才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。例如我们使用字段age、name、gender创建索引，它会先根据age进行排序，如果age一样则比较name，name一样则比较gender，通过这样一个比较机制生成有序的索引结构。这也是为什么直接使用name不走索引，因为name基于age进行排序，我们单纯查询name得到的结果是散列的，无法快速定位到数据。我们可以创建张表实验下上述情况。

create table employee (
    id      int(11)     NOT NULL PRIMARY KEY,
    name    varchar(10) NOT NULL,
    age     int(11)     NOT NULL,
    address varchar(255) default NULL,
    height  double       default NULL,
    gender  varchar(2)   DEFAULT NULL,
    index age (age) using btree ,
    index age_name_address (age,name,address) using btree
) engine = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

然后我们插入五条测试数据

insert into employee values (1, '章鱼哥', 20, '北京',170,1),(2, '海星', 22,  '上海',185,1),(3, '海绵', 25, '广州',176, 1),(4, '草履虫', 35, '深圳',190, 0),(5, '螃蟹', 34, '海边',145, 0);

我们来通过几条SQL来更加深入了解最左匹配原则
首先我们先看看这条SQL，很明显它使用的条件是联合索引中最左边的字段，所以它是走索引的

select gender from employee where age='20';

通过explain命令分析这条SQL，可以看出这个类型是ref，说明它使用到了非唯一性索引扫描。

再来看看我们上面所说的直接使用联合索引中第二个字段的情况，由于其排序结果依赖第一个字段，所以会导致没法利用有序的索引去快速定位。

select gender from employee where name='章鱼哥';

查看执行计划，可以看到type变成了all（全表查询），Extra也变成了using where

再来看看这条SQL，将age放在后面，但是查看执行计划时，仍然走了索引，为什么呢？
因为SQL优化器会优化成索引可以识别的形式，将age排到前面，因为最左匹配原则的定律，这个SQL没有用到第二列，但是用到了最左排序依赖项，所以走了索引。

explain select gender from employee where address = '广州' and age = '25';

看看执行计划

上面我们说了最左匹配原则一旦条件中遇到范围查询（如>、<）就会停止，对此不妨我们举个例子

explain select gender from employee where name = '海绵' and age =25  and address = '广州';

查看执行计划发现很明显是走了索引的

再看看这条

explain select gender from employee where  age >25  and name = '海绵' and address = '广州';

查看执行计划，如下图所示，Extra是Using index condition，type也是range，说明一开始匹配到age就停止进行索引了。

MySQL联合索引一定遵循最左前缀匹配原则吗？

不一定，在MySQL8.0版本中增加了索引跳跃扫描的功能。
对此，我们可以建立一张数据表实验下

create table user(
    id     int          NOT NULL  AUTO_INCREMENT,
    name   varchar(255) NOT NULL,
    gender tinyint       NOT NULL,
    age    int          null,
    PRIMARY KEY  (id),
    KEY gender_age_name(gender, age, name)
) ENGINE = InnoDB;
insert into user values (1, '章鱼哥',false,22),(2, '海星',true,31),(3, '海绵', false,25),(4, '草履虫', false,29),(5, '螃蟹', true,23);

执行SQL查询验证一下:

explain select * from user where age =5 ;

从执行计划中可以看出虽然SQL查询条件只有age字段，但是仍然走了索引。

索引跳跃扫描出现于索引最左列唯一值较少时，即使where条件没有第一列索引，仍然可以用到联合索引。

什么是索引下推？

索引下推是MySQL5.6版本增加的新特性，在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表记录。对此我们可以举个例子，首先创建一张数据表，并创建联合索引(name,age)。

create table `user` (
  ID int primary key,
  `name` varchar(32) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL, 
  `sex` varchar(4) DEFAULT NULL, 
  KEY `name_age`(`name`,`age`)
)engine=InnoDB;

例如索引结构如下所示

我们使用以下查询语句查询

select * from user where name like '海%' and age=10

在`MySQL5.6前没有索引下推机制时，该查询可以对name字段进行匹配，但因为name字段不是等值匹配的，当查询到了海绵、海星这几个字段时，它就会拿着对应的主键值，然后回表到主键索引上找到数据行，然后进行age=10的判断，如下图所示，这就意味着该查询会执行两次回表操作。

而在MySQL5.6引入索引下推机制后，该机制在索引遍历时会对索引中包含的字段进行过滤，将没必要进行回表的数据过滤掉，从而减少回表次数，仍旧以上图为例，匹配完name字段后，直接通过age过滤条件，发现一条都没有符合的，所以不回表了。

聊一聊使用索引的注意事项

为了更好的演示效果，我们不妨建立一张数据表，表中有主键、普通索引、普通列，插入5条实验数据。

create table `example` (
    `id`      int not null auto_increment,
    `name`    varchar(32)  DEFAULT NULL,
    `age`     int(11)      DEFAULT NULL,
    `sex`     varchar(4)   DEFAULT NULL,
    `address` varchar(32)  DEFAULT NULL,
    `email`   varchar(100) DEFAULT NULL,
    primary key (id) using btree ,
    index `name_age`(`name`,`age`) using btree ,
    index idx_sex(sex) using btree
) engine = InnoDB auto_increment = 1 char set = utf8 collate  = utf8_general_ci row_format = dynamic ;

insert into example values (1,'章鱼哥',20,'男','北京','123@163.com');
insert into example values (2,'海绵',22,'男','上海','321@163.com');
insert into example values (3,'螃蟹',28,'女','广州','987@163.com');
insert into example values (4,'海星',31,'男','深圳','567@163.com');
insert into example values (5,'蜗牛',21,'女','北京','547@163.com');
insert into example values (6,'松鼠',33,'女','北京','517@163.com');
insert into example values (7,'草履虫',35,'男','广州','587@163.com');
insert into example values (8,'杂鱼',17,'男','海底','444@163.com');

避免使用select *

explain select * from example where name = '章鱼哥';

且看这条SQL的执行计划

虽说它走了索引，但是不太建议使用，因为它会导致：

避免使用以 % 开头的 LIKE 查询

如下两条SQL，后者走了索引；我们都知道通常索引的数据结构是B+Tree，而索引是有序排列的，当%在右边时，是可以在B+Tree上有序查找，匹配首字母的，所以这种情况可以走索引，而%在最左边时，范围太大了，违背索引有序排列的规则，造成索引失效，进行全表扫描。

explain select * from example where name like  '%牛';
# 走索引，但索引级别比较低
explain select * from example where name like  '蜗%';

使用or导致索引失效

看看这条SQL，我们使用了索引列和普通列作为条件，并且索引列在最左侧，但是SQL没走索引，进行了全表扫描。

explain select * from example where name='蜗牛' or address ='北京';

看看执行计划，type为ALL

这是因为索引列走了索引扫描，而普通列没有创建索引，所以它需要全表扫描，这就相当于需要索引扫描+全表扫描+结果合并，而SQL优化器认为直接一次全表扫描更快，但当or两侧都是索引列时，就会走索引扫描。

不正确使用in导致索引失效

使用in不一定会造成全表扫描，使用in是会走索引的，但当in的取值范围过大则会导致全表扫描。

# 走索引
explain select * from example where name in('章鱼哥');
# 不走索引
explain select * from example where name in('章鱼哥','海绵','螃蟹','海星','蜗牛','松鼠');

第一条SQL执行计划

第二条SQL执行计划

避免在where条件后对索引列进行函数操作

下面两条SQL，第一条索引失效，第二条不失效，是因为索引保存的是索引字段的原始值，而不是经过函数计算的值，因而第二句原始值未被改变，故而第二句走了索引。
在MySQL8.0之后索引新增了函数索引，如果有需要使用函数可以为其建立函数索引。

# 索引失效
explain select * from example where length(name) = 6 ;

# 索引不失效
explain select length(name) from example where name = 6 ;

怎么分析SQL语句是否使用索引

上面频繁使用的Explain命令可以用来分析SQL的执行计划，这样就可以知道是否使用了索引。执行计划指的是一条SQL在经过MySQL查询优化器的优化后具体的执行方式。
Explain输出格式如下：

mysql> explain select * from example;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | example | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

输出列各字段的含义

参考文献

MySQL索引详解

MySQL 索引之覆盖索引，联合索引与索引下推

我说MySQL联合索引遵循最左前缀匹配原则，面试官让我回去等通知

联合索引的最左匹配原则全网都在说的一个错误结论

MySQl 索引之道

MySQL官方文档