MySQL索引常见问题

一. 概述

1.1 索引的分类

按「数据结构」分类： B+tree索引、Hash索引、Full-text索引
按「物理存储」分类： 聚簇索引（一般是主键索引）、非聚簇索引
按「字段特性」分类： 主键索引（是聚簇索引）、唯一索引、普通索引、前缀索引

1.2 索引的优缺点

优点：（引入）
1.减少扫描量，大大加快检索速度；
2.将随机I/O变成顺序I/O，提高查询效率；
3.保证每一行数据的唯一性；

缺点：
1.创建索引和维护索引都有一定的开销，这种时间随着数据量的增加而增大；
2.占物理内存；
3.对于特别小的表，全表扫描更高效；
4.如果数据重复多，建立索引则用处不大；

二. B+tree索引 ★

(1) 聚簇索引

MySQL 5.5之前默认引擎为MyISAM ，5.5之后为InnoDB；两种引擎都是用B+数结构的索引，两者的实现方式不太一样。

InnoDB 引擎中，其数据文件本身就是索引文件，为聚簇索引；
而 MyISAM中，索引文件和数据文件是分离的，为非聚簇索引；

创建：

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：
①如果有主键，【默认】会使用主键作为聚簇索引的索引键（key）；
②如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
③在上面两个都没有的情况下，InnoDB 将自动生成一个 隐式自增 ROW_ID 作为聚簇索引的索引键（key）；

结构：

1. B+Tree 是一种多叉树，非叶子节点只放索引，叶子节点才存放数据（和索引），而且每个叶子节点里的数据是按主键顺序存放的；
   每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息；
2. 叶子节点形成一个双向链表；
   
   B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足（通过区间对比查询），这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

为什么使用B+tree 作为索引的数据结构？（优势）

1. 非叶子节点只存索引，同样内存情况下可以存更多的key，就能更快找到目标节点；
2. 叶子节点是由一个双向链表连接，可以进行 范围查找，即可以向链表两边遍历，就能找到接近的值而不需要再从根节点开始查询，以此减少磁盘I/O；

B+Tree vs B Tree

B+树： （多叉树+链表）
B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高；
1.由于B+树在非叶子结点上不包含真正的数据，只当做索引使用，因此在内存相同的情况下，能够存放更多的key，所以查询效率很高； / 而B树的非叶子节点存储key和value；
2.由于叶子节点是双向链表，所以便于范围查找和搜索；/ B树不能进行范围查找；
3.B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为B+ 树的层级更少，查询一个数据的磁盘 I/O 依然维持在 3-4次。

B树：
优点：
由于B树的每一个节点都包含key和value，因此我们根据key查找value时，只需要找到key所在的位置，就能找到value。
缺点：
1.存的是key+value，相同内存下存的key要少一些，检索效率不如B+树；
2.B 树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树；

B+Tree vs 二叉树

二叉树的每个节点的儿子节点个数只能是 2 个，二叉树检索到目标数据所经历的磁盘 I/O 次数要多很多；
B+树在千万级数据量下也只需要3-4层，只需要3-4次I/O操作就能查到数据；

B+Tree vs Hash表

Hash 在做等值查询的时候效率快，搜索复杂度为 O(1)。
但是 Hash 表不能做范围查询，它更适合做等值查询，当出现范围查看场景时复杂度会退化为O(n)

(2) 聚簇索引和非聚簇索引

B+Tree索引在实现上又分为 聚簇索引和非聚簇索引；

主要区别：数据和索引是否分开存储；

聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快;

优点：
聚簇索引对于主键的排序查找和范围查找速度非常快
缺点：
插入数据的速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能

对于InnoDB表，我们一般都会定义一个自增的ID列为主键
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

聚簇索引是主键索引吗？

聚簇索引一般是主键索引，有主键时会使用【默认】主键作为聚簇索引，
如果表中没有指定主键，则选择表中第一个不允许为null的字段的唯一索引；
如果还没有就使用Innodb隐藏的 ROW_ID 作为聚簇索引，是个6字节的自增；

缺少主键导致的问题 ？

1.使用不了主键索引，查询会进行全表扫描，效率大大降低
2.影响数据插入性能，插入数据需要生成ROW_ID，而生成的ROW_ID是全局共享的，并发会导致锁竞争，影响性能；

(3) 非聚簇索引（二级索引）

非聚簇索引的 B+Tree 的 叶子节点存放的是主键值，而不是实际数据 ！（索引和数据不在同一个B+树）

原理：
①先在【二级索引】中找到主键值（叶子节点）；
②再通过主键值再去主键索引的B+树中找对应的数据；

我们日常工作中，根据实际情况自行添加的索引都是辅助索引 ！，

回表：
如果查询的数据能在二级索引里(辅助索引字段+主键字段)查询的到，那么就不需要回表，只需要查一个B+树，这个过程就是 覆盖索引。

如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，再通过主键索引中的 B+Tree 树查询到对应的叶子节点，这个过程就是回表，也就是说要查两个 B+Tree 才能查到数据。

如： 当二级索引存的是product_no，需要查主键相关联的值就需要回表：
select * from product where product_no = '0002';
就会先查找二级索引0002，再根据二级索引对应的主键值，去找主键索引对应的数据，即回表；
而当查询：
select id from product where product_no = '0002';
查询的值就是二级索引以及主键索引，此时就不需要回表；

三.其他索引

3.1 主键索引

innoDB的每个表有且仅有一个聚簇索引， 有主键时InnoDB会自动将主键索引作为聚簇索引，没有主键的话会生产一个隐藏的 ROW_ID 完成聚簇索引 ；

这个关于主键的索引完全是由InnoDB存储引擎自动生成的，不需要我们显式地书写创建索引的语句。这个索引叫做主键索引，又叫做聚簇索引。

即在innoDB下，主键索引就是聚簇索引 !

 create table test(id int PRIMARY KEY,name varchar(255)  );

3.2 唯一索引

唯一索引建立在 UNIQUE 字段（唯一约束）上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
在创建表时，创建唯一索引的方式如下：

CREATE UNIQUE INDEX index_name ON table_name（索引字段）；

主键索引和唯一索引的区别？

1. 主键不允许空值；唯一索引允许空值；
2. 主键只能有一个；唯一索引可以有多个；
3. 主键产生唯一的聚集索引，唯一索引产生唯一的非聚集索引；
4. 主键可以被其他表引用为外键，而唯一索引不能；

3.3 普通索引

又有单列索引、联合索引；

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE;

CREATE INDEX index_name ON table_name(索引字段1，索引字段2);

删除索引：

drop INDEX index_name on table_name;

3.4 前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引 ；
使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率；

CREATE INDEX index_name ON table_name(name(5));

3.5 联合索引

通过将多个字段组合成一个索引，该索引就被称为联合索引。
例：
通过将多个字段组合成一个索引，该索引就被称为联合索引，
比如将商品表中的 product_no 和 name 字段组合成联合索引(product_no, name)

CREATE INDEX index_test  ON table(product_no, name);

非叶子节点用两个字段的值作为 B+Tree 的 key ,
当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 相同的情况下再按 name 字段比较
联合索引查询的 B+Tree 是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。因此，使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

四. 创建索引的方式 ？

方式一：建表时建立索引

• 创建主键索引（聚簇索引）：

 create table test(id int PRIMARY KEY,name varchar(255)  );

• 创建普通索引：

create table test(id int,name varchar(255),INDEX index_name(id)  )

• 创建唯一索引：

create table test(id int,name varchar(255),UNIQUE INDEX index_name(id)  );

• 创建前缀索引：

create table test(id int,name varchar(255),INDEX index_name(name(5))  );

• 创建联合索引：

create table test(id int,name varchar(255),INDEX index_name(id,name)  );

方式二：建表后建立索引
对应方式一：

create INDEX index_name on test(id);

create UNIQUE INDEX index_name on test(id);

create INDEX index_name on test(name(5));

create INDEX index_name on test(id,name);

五. 索引的设计原则 ？

首先，设计索引的目的是提高查询效率；
但是索引也是有缺点的，比如：需要占用物理空间，数量越大，占用空间越大；
创建索引和维护索引有时间开销，频繁更新也会影响性能；

什么时候适合用索引？

1.字段有唯一性限制的，比如商品编码、学号id；
2.经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度；
3.经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

1.WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
2.字段中存在大量重复数据，不需要创建索引;
3.表数据太少也不需要，还不如全表扫描；
4.频繁更新的字段不用创建索引，维护索引是有成本的，频繁重建索引会影响数据库性能

其他索引设计 / 优化的原则

主键索引最好是自增的：
innoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，如果我们使用自增主键，那么每次插入的新数据就会按顺序追加到当前索引节点的位置，不需要移动之前的数据，效率高！

使用前缀索引：
较长的字符串可以指定一个较短的前缀索引，可以减小索引项的大小，增加一个索引页中存储的索引值个数，涉及到的磁盘I/O较少，有效提高索引的查询速度。

索引最好设置为 NOT NULL：
为了更好的利用索引，索引列要设置为 NOT NULL 约束
索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂；

覆盖索引避免回表：
能从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作，减少I/O操作；
如：可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

防止索引失效；

索引失效及解决方法：

• 当使用模糊匹配的时候，% 放在开头就会索引失效 ！也就是 like ‘%xx’ 或者 like '%xx%'这两种方式（左模糊、全模糊）；

• 在 where 子句中，对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；--------事先做好计算，避免对索引做计算；

• 对于【联合索引】，where 过滤条件要使用索引，必须按照索引建立的顺序，依次满足即 最左匹配原则，一旦跳过某个字段，索引失效；--------调整索引的顺序

• 在 where 子句中，使用OR查询的部分字段没有索引；--------两个字段都建立索引

• 在 where 子句中，单列索引无法存储NULL值，即在where后使用的在索引列上使用 IS NULL时会索引失效；--------将所有字段都是用not null 非空约束

• 在where后使用了 ！= 操作，导致索引失效；

参考：
https://xiaolincoding.com/mysql/index/index_interview.html#%E7%B4%A2%E5%BC%95%E7%9A%84%E5%88%86%E7%B1%BB