MySQL索引

---

MySQL索引

0.概述

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构 。

简单来说索引就是一种数据结构，这种数据结构将数据有序的组织起来，根据指定的查找算法来实现高效的查询MySQL中的数据。

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

索引结构	描述
B+Tree	最常见的索引，大部分引擎都支持，也是主流使用的
Hash索引	底层使用哈希表实现，，只有精确匹配索引列的查询才有效，不支持范围查找
R-tree(空间索引)	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文 索引)	是一种通过建立倒排索引,快速匹配文档的方式。类似于 Lucene,Solr,E

不同的存储引擎对索引结构的支持情况：

索引	InnoDB	MyISAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-Tree索引	不支持	支持	不支持
Full-text索引	5.6之后支持	支持	不支持

而B+Tree索引是InnoDB使用的，也就是说B+Tree是现在主流使用的

不同的索引结构

搜索树

搜索树虽然可以保证数据有序，当如果特殊情况也就是数据是以有序的方式进行插入。

将数据以有序方式插入到搜索树那么搜索树就会退化成单链表，那就相当于全表查找效率非常低,所以没有被使用

AVL树

如果使用AVL树来作为索引的数据结构，就算数据插入有序也能保证树的平衡，但又出现一个问题，AVL树是要旋转的，极端情况下可能要一直旋转到根节点的位置，旋转也是需要开销的。

红黑树

特点：

• 每个节点不是红色就是黑色，并且根节点是黑色的
• 不能出现两个连续的红色节点
• 对于每个节点，从该节点到所有后代的叶子节点的简单路径上，都包含相同数目的黑色节点
• 每个叶子节点都是黑色的，这个叶子节点指的叶子节点

通过以上几条性质就能保证最长路径中的节点个数不会超过最短路径节点个数的两倍，因为不能出现两个连续的红色节点。

红黑树存在的问题就是：当数据量非常大的时候是无法控制树的高度的，如果树的高度非常高，则会增加磁盘的I/O次数从而影响查询速度

Hash

哈希索引就是采用一定的Hash算法将键值换算成hash值，映射到哈希表的对应位置，如下图：

对name这一列建立hash索引，链表的节点存储的是name这个值和其所对于数据行的地址。

如果两个或者多个键值，映射到了同一个位置则会发生哈希冲突，使用链表的方式来解决。

hash索引的优缺点：

• 只支持精确查询 =、in，不支持 >=、like范围查询
• 无法利用索引进行排序
• 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

B-Tree

B树是一棵多路平衡搜索树，比如说一个最大度为4的B树，树的每个节点可以存放数据为3，可以存放4个孩子节点的指针。

B-Tree节点定义代码如下

// M叉树
private static final int M = 4;
static class BTreeNode {
    // 节点关键字
    int[] keys;
    // 孩子
    BTreeNode[] subs;
    // 关键字个数
    int keySize;
    // 父节点
    BTreeNode parent;

    public BTreeNode() {
        keys = new int[M];
        // 多给一个方便分裂
        subs = new BTreeNode[M+1];
    }
}

特点：

• 度为4的B树可以存储3个数据和4个孩子节点的指针
• 一旦超过3个孩子节点就会进行分裂，中间元素上提，左右两边元素分裂成新节点
• 在B树中叶子节点和非叶子节点中都会存储数据

B+Tree

B+Tree是B-Tree的一种优化，其在树的叶子节点之间增加了链表指针，并且只在叶子节点存放数据

B+Tree和B-Tree的区别主要有以下几点：

• B+Tree的数据都会在叶子节点出现
• 叶子节点形成了一个单项链表
• 非叶子节点不存储数据，只起到一个索引的作用
• 因为B+树只是在叶子节点存放数据，那么非叶子节点就可以存放更多存放索引，让B+树的高度更低，减少磁盘I/O

而MySQL又对B+树进行了优化，修改了链表指针变成了一个类似于循环双向链表。每一个节点其实就是一个页

索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

聚集索引&非聚集索引

在InnoDB索引中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引	将数据存储和索引放到了一块，索引结构的叶子节点保存了行数据	必须有，而而且只有一个
非聚集索引	将数据和索引分开，索引结构的叶子节点关联的是对于应的主键	可以存在多个

聚集索引

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
• 如果没有主键，或这没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

假设有那么一张表：

聚集索引结构：

主键ID就是聚集索引，叶子节点存储的是每一行的数据

非聚集索引又叫二级索引，假设使用name作为二级索引结构如下：

非聚集索引在叶子节点存储了主键的值

加上我们要执行一条SQL select * from user_table wehre name='C'

这一条SQL会走非聚集索引，先是通过name找对应这一行数据的主键ID，再进行回表查询，也就是再通过主键ID根据聚集去找到name='C'的那一行数据

那么这两条SQL哪个效率更高？

• select * from user_table wehre id= 1
• select * from user_table where name='A'

显然是第一条SQL的效率更高，因为他直接走聚集索引直接找到数据后返回，而第二条SQL走的是非聚集索引，还需要进行回表查询，效率比较低

InnoDB 主键索引的B+Tree高度为多少？

我们指定B+Tree一个节点就是一个页大小16K，假设主键值为BitInt大小为8个字节，InnoDB指针大小为6个字节，一行记录的大小为1k

$n\ast 8+(n+1)\ast 6=16\ast 1024$，最后求出n大约为1170，也就是1170个主键和1171个节点指针。

假设说树的高度为2：

• $1171*16=18736 $
• 也就是可以存放18000左右条记录

假设树的高度为3：

• $1171\ast 1171\ast 16=21,939,856$
• 树的高度为3的B+Tree大于能存2200万条记录

聚集索引和非聚集索引的区别

• 存储的数据不同：聚集索引叶子节点存储了完整的数据，而非聚集索引叶子节点存储的是主键的ID
• 数量限制不同：一张表只能有一个聚集索引，但可以有多个非聚集索引
• 查询效率不同：聚集索引通过主键查询到对于数据直接返回，而非聚集索引查询到主键之后还需要进行回表查询

索引的使用

创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name ;

索引失效情况

联合索引

什么事联合索引？

联合索引就是由数据表中多个字段组成的索引，并安装索引建立的字段顺序来进行排序。

假设有那么一张表：

create table tb_user(
	id int primary key auto_increment comment '主键',
	name varchar(50) not null comment '用户名',
	phone varchar(11) not null comment '手机号',
	email varchar(100) comment '邮箱',
	profession varchar(11) comment '专业',
	age tinyint unsigned comment '年龄',
	gender char(1) comment '性别 , 1: 男, 2: 女',
	status char(1) comment '状态',
	createtime datetime comment '创建时间'
) comment '系统用户表';

使用 用户名，年龄、专业创建联合索引，那么就会按照name,age,profession来进行排序

create index index_name_age_pro on tb_user (name,age,profession);

联合索引例图：

最左匹配原则

如果有多个列建立了联合索引，就要遵循最左匹配原则。

最左匹配原则指的是查询索引的最左列开始并且不跳过索引的列，如果跳过最左列索引就会全部失效，如果跳过中间部分就会导致索引部分失效。

索引生效情况

-- 遵循最左匹配原则，走索引
select * from tb_user where name='吕布' and age=23 and profession='软件工程';
-- 遵循最左匹配原则，走索引
select * from tb_user where name='吕布' and age=23;
-- 遵循最左匹配原则，走索引
select * from tb_user where name='吕布';
-- 遵循最左匹配原则，走索引
select * from tb_user where age=23 and profession='软件工程' and name='吕布';

索引失效情况，下面情况都直接跳过了索引最左边的列就会导致索引索引全部失效

-- 不遵循最左匹配原则
select * from tb_user where age=23 and profession='软件工程';
-- 不遵循最左匹配原则
select * from tb_user where profession='软件工程'
-- 不遵循最左匹配原则
select * from tb_user where age=23 and profession='软件工程';

索引部分失效情况，下面几种情况都是跳过中间部分索引就会导致后面索引失效，也就是索引部分失效

select * from tb_user where name='吕布' and profession='软件工程';

范围查询

在联合索引中，出现范围查询（>,<），范围右侧的列索引失效.

如下SQL条件查询中年龄使用了>和<范围查询都会导致其右侧的索引失效，页就是专业这个索引失效

-- 右侧索引失效
select * from tb_user where name='吕布' and age<23 and profession='软件工程';

所以编写SQL的时候尽量使用>=或者<=进行范围查询

-- 索引全部生效
select * from tb_user where name='吕布' and age>=23 and profession='软件工程';

单列索引失效情况

为手机号字段创建一个索引，注意phone是varchar类型

create index index_phone on tb_user(phone);

索引列运算

不要在索引列上进行运算操作，不然会导致索引失效。

如下的SQL直接使用=比较生效，索引是会生效的

-- 索引生效
select * from tb_user where phone='17799990023';

对索引字列使用函数进行计算也会导致索引失效

-- 对索引列进行字符串截取，索引失效
select * from tb_user where substring(phone,10,2) = '11';

又或者是对主键进行运算，也是会失效的

-- 索引失效
select * from tb_user where id-1>5;

为什么对索引列进行运算索引就会失效呢？并不是运算了就不走索引。

因为索引里保存的是原始的值，如果需要比较的话就得把索引里面的值取出来进行运算再比较。

字符串不加引号

phone是字符串类型但在比较的时候我们使用了数字来进行比较，底层会发生隐式类型转换。

-- 索引失效
select * from tb_user where phone=17799990023;

进行模糊匹配

name建立过联合索引，遵循最左匹配原则，如果是尾部模糊匹配，索引并不会失效，如果是头部模糊匹配，索引会失效。

-- 走索引
select * from tb_user where name like '孙%';
-- 不走索引
select * from tb_user where name like '%空';
-- 不走索引
select * from tb_user where name like '%悟%';

使用or连接条件

使用or的两个条件，or左边的条件中有索引，而后面的条件的列没有索引，索引就会失效

比如我们前面针对phone建立了索引，而status列没有索引，此时索引就会失效。

select * from tb_user where phone='17799990000' or status='0';

想让其生效就要在status列上也建立索引

又比如我们在前面建立的联合索引使用用户名、年龄、专业建立的索引index_name_age_pro，

下面这一条索引也不会走索引，因为or右边的条件并不满足最左匹配原则。

select * from tb_user where phone='17799990000' or age=22;

当or连接的条件，左右两侧字段都有索引时，索引才会生效

数据分布影响

有时候会同一条SQL但是传递的字段值不一致，也有可能导致索引失效。

比如果说user表中的数据如下：

phone列已经建立索引

这里有24条数据，我们执行以下SQL：

-- 查询比吕布手机号大的用户信息，索引失效
select * from tb_user where phone>'17799990001';
-- 查询比典韦手机号大的用户信息，索引生效
select * from tb_user where phone>'17799990020';

因为MySQL在查询的时候，会对走索引的效率和走全表扫描的效率进行评估，如果走全部扫描扫描更快，则放弃索引。因为索引是用来查询少量数据的，如果通过索引查询返回大批量数据，那还不如走全表扫描，此时的索引就会失效。

SQL 提示

当我们已经使用名称、年龄、专业建立了联合索引(name,age,profession)，那如果再给name建立一个普通索引，执行下面的SQL会走哪个索引呢？

select * from tb_user where name='姜子牙';

走哪个索引是由MySQL的优化器来选择的，当然可以指定使用哪个索引

1. use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）

   select * from tb_user use index(index_name) where name='姜子牙';
   

2. ignore index ： 忽略指定的索引

   select * from tb_user ignore index(index_name) where name='姜子牙';
   

3. force index ： 强制使用索引。

   select * from tb_user force index(index_name) where name='姜子牙';
   

覆盖索引

覆盖索引指的是查询时使用了索引，并且需要返回的列也在该索引中能全部找到，不需要进行回表查询。所以尽量不要使用select *来进行查询。

举个例子有这么一张表：

其中name,age,profession是联合索引

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(11)             | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50)         | NO   | MUL | NULL    |                |
| phone      | varchar(11)         | NO   | MUL | NULL    |                |
| email      | varchar(100)        | YES  |     | NULL    |                |
| profession | varchar(11)         | YES  |     | NULL    |                |
| age        | tinyint(3) unsigned | YES  |     | NULL    |                |
| gender     | char(1)             | YES  |     | NULL    |                |
| status     | char(1)             | YES  | MUL | NULL    |                |
| createtime | datetime            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

执行以下SQL并用explain来获取以下两条SQL的执行信息

explain select * from tb_user where name='吕布';
explain select id,name from tb_user where name='吕布';

执行结果：

通过explain获取的SQL执行信息，关注最后一个字段Extra

Extra	含义
Using where; Using Index	查找使用了索引，但是需要的数据都在索引列中能找到，所以不需 要回表查询数据
Using index condition	查找使用了索引，但是需要回表查询数据

我们发现使用select *的SQL语句需要回表查询，而指定查询的不需要。因为指定查询的的列为id 和name，通过name这个联合索引就能获取到全部数据，因为联合索引也就是非聚集索引，叶子节点存储的就是id，只需要一次查询即可。

所以查询尽量不要使用select *，不但性能差并且还有可能导致索引失效。

前缀索引

当我们的索引字段的值比较长（varchar,text）的时候，就会导致索引比较大从而影响索引的性能。这个时候就可以考虑使用前缀索引，通过截取字段的前几个字符来作为索引，这样就可以减少索引空间，从而提升索引效率。

前缀索引选择性：前缀索引选择性指的是不重复的索引值和数据表的记录总数的比值，索引选择性越高则查询效率越高 。唯一索引的选择性是1，这是最好的索引选择性 。

通过一下方法来简单计算前缀索引的选择性：

-- 选则合适的长度
select count(distinct email) / count(*) from tb_user;
select count(disitinct substring(email,1,5)) / count(*) from tb_user;
-- 比如说选则邮箱的前5个字符作为索引
create index email_5_pre_index on tb_user(email(5));

前缀索引也是属于非聚集索引，假设通过一下SQL来进行查询

select * from tb_user where email='17799990@139.com';

他的查询流程如下：

• 先通过17799去索引里查找，查询到叶子节点发现有相同的索引也就是17799
• 那么就拿到叶子节点的索引值也就是主键ID，通过主键去聚集索引里找到对于那一行的数据对比整个email
• 如果相等就会返回，同时继续从前缀索引里向后找有没有17799的值，如果有继续进行回表查询，没有则结束

单例索引&联合索引

• 单列索引：即一个索引只包含单个列。
• 联合索引：即一个索引包含了多个列

假设我们的name和phone字段都建立了单例索引，那么下面这一条SQL，两条索引都会会走吗？

并不是，多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询

select * from tb_user where name='姜子牙' and phone='17799990023';

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，
而非单列索引。

索引设计原则

1. 针对于数据量较大(几十万将近百万级别的数据)，并且查询比较频繁的表建立索引
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高，比如哦说如果对性别这种字段建立索引效率就会比较低
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引，建立前缀索引时需要关注区分度
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

---