MySQL —— 索引_mysql索引

索引的概念

MySQL的索引是⼀种数据结构，它可以帮助数据库高效地查询、更新数据表中的数据。索引通过
⼀定的规则排列数据表中的记录，使得对表的查询可以通过对索引的搜索来加快速度。

MySQL索引类似于书籍的目录，通过指向数据行的位置，可以快速定位和访问表中的数据，比如
汉语字典的目录（索引）页，我们可以按笔画、偏旁部首、拼音等排序的⽬录（索引）快速查找到需
要的字。

探讨索引的数据结构

我们来一起探讨那种数据结构用来当作索引更适合数据库

首先是哈希表，对于哈希表来说，查找的时间复杂度为 O(1)，十分优秀，但是却不适合数据库，因为哈希表不支持范围查找，而我们数据库是需要支持范围查找的，例如：我们有张成绩表，要查询成绩大于等于60以上的学生时，这时就要求我们数据库能支持**范围查找，**所以哈希表并不适合作索引

---

接下来登场的是二叉搜索树，查找的时间复杂度为O(logN)，但是如果出现极端情况，二叉搜索树是可能会退化成一颗单分支的树，这时候时间复杂度就变成O(N)，还是不理想，这时候大家可能会想到AVL树或者红黑树，AVL树就不说了，旋转次数太多了，在数据库面前，数据量十分庞大，如果旋转，呵呵…
我们来看一下红黑树，虽然保持相对平衡，但是在数据一多的情况下，我们无法保证树到底有多高

为什么要讨论树高呢？
因为数据库的数据是存储在磁盘上的，所以当你需要读取数据的时候，是需要进行磁盘的IO的，磁盘的IO速度是十分慢的，如果IO 次数一高，效率就会低下哎，所以我们应该减少磁盘IO次数，也就是减小树高，那么红黑树就不能满足了

磁盘IO 是制约数据库性能的主要因素

---

既然红黑树不行，那我们可以考虑B树，这时一颗多路平衡树，由于是多路，所以可以降低书高，但是我们的MySQL还是不满意，觉得效率还不是不够高，于是MySQL 就使用B树的变形也就是B+树，我们在前面就知道B+树有一些特点：真实的数据都是保存在叶子节点上的，非叶子结点只是起到一个导航的作用，并且叶子结点是使用双向链表进行连接的，所以在数据库进行范围查找的时候十分方便。

B树示例：

B+树示例：

B+树的时间复杂度是O(logN)，并且可以有效的控制树高

B+树与B树的对比：
1.B+树的叶子结点之间有相互连接的引用，可以通过这个连接找到与其相邻的兄弟节点，mysql 在组织叶子结点时使用的时双向链表
2.非叶子结点的值包涵在叶子结点中，MySQL 非叶子结点只保存了对子结点的引用，没有保存真实的数据，所有真实的数据都是在叶子结点中
3.对于B+树而言，在相同书高的情况下，查找任意元素的时间复杂度都是一样的，性能均衡。
4.相比于 B 树而言，由于 B树的叶子结点或者非叶子结点，都会保存数据，这样会导致一页（一页的大小是优有限的）存储的键值减少， 指针也就跟着减少，在同样保存大量的数据的情况下，B树的树高就会大于 B+树的，导致性能降低

---

注意：MySQL 索引使用 B+ 树结构，在 B+ 树基础上使用的是双向指针连接叶结点

MySQL的页

在 .ibd 文件中最重要的结构体是 Page(页)，页是内存与磁盘交互最小单元，默认大小是 16KB

每一个.ibd 文件由页组成

每次内存与磁盘的交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的，之所以这么做，是因为在使用数据的过程中，根据局部性原理，将来要使用的数据大概率与当前访问的数据在空间上是临近的所以一次从磁盘中读取一页的数据放入内存中，当下次查询的数据还在这个页中的时候，就可以直接从内存中读取，从而减少磁盘IO，以此来提高性能。

局部性原理：
是指程序在执行时呈现出局部性规律，在一段时间内，整个程序的执行仅限于程序中的某⼀部
分。相应地，执行所访问的存储空间也局限于某个内存区域，局部性通常有两种形式：时间局部
性和空间局部性。
时间局部性（Temporal Locality）：如果⼀个信息项正在被访问，那么在近期它很可能还会被再
次访问。
空间局部性（Spatial Locality）：将来要用到的信息大概率与正在使用的信息在空间地址上是临
近的。

每一个页中即使没有数据也会使用 16KB 的存储空间，同时与索引的B+树中的节点对应

我们可以查询MySQL中页的大小：

16384 / 1024 = 16 KB

数据页

数据库由很多种的页，索引页、数据页等等，这里我们讨论数据页：

页头页尾

数据页由页头和页尾：

这里我们关注的是页头里包含上一页页号和下一页页号，通过这两个属性可以把页与与之间链接起来，形成一个双向链表。

---

页主体

页主体部分是保存真实数据的主要区域，每当创建一个新页时，都会自动分配两个行，一个是页内最小行Infinum，另一个是页内最大行Supremun，这两行并不存储任何真实数据，而是最为数据行链表的头和尾【你可以理解为链表的两个空节点，一个是空的头节点，另一个是空的尾节点】

第一个数据行有一个记录下一行的地址偏移量的区域next_record将页内所有的数据行组成了一个单向链表

当向一个新页插入数据时，将Infimun 连接第一个数据行，最后一行真实数据连接Supremun，这样数据行就构成了一个单向链表，当更多的数据行插入之后，会按照主键从小到大的顺序进行连接：

页目录

看到这里，大家是不是又疑惑，单向链表的查找效率不是很慢吗？时间复杂度是O(N)，当然开发数据库的人也想到了，为了提高查找效率，InnoDB采用二分查找来解决查询效率问题，于是他们开发了页目录：

具体的实现方式是，在每一个页中加入一个叫做页目录的结构，将页内包括最大行和最小行在内的数据行进行分组，这里单独约定最小行独自为一组，其他分组最多8条数据，同时将最大行放在最后一个分组中，按照主键从小到大的顺序记录在页目录中，页目录每一个位置称为槽，每一个槽对应一个分组，一旦分组中数据行超过8个的时候，就会分裂出一个新的分组

在后续查找某条数据行时，通过二分查找，就可以找到对应的槽，然后在槽对应的分组中进行最多8条数据的便即可获得目标数据

数据页头

数据页头记录了当前页保存数据相关的信息

B+树在数据库中的应用

非叶子节点保存索引数据，叶子节点保存真实数据

举个例子：查找 id 为7 的数据行，首先通过索引页1，发现 7 = 7 ，进入到索引页2，然后 7 < 9，进入到数据页4，最后找到id 为 7 的数据行，一共进行了三次磁盘IO。

以上的IO过程，加载索引页1–>加载索引页2–>加载数据页3

我们现在来感受B+树的强大：

假设一条数据大小为 1KB，忽略数据页中页头页尾等等非真实数据的内容，也就是说一个数据页可以保存16条数据。

索引页保存的是索引，这里我们以主键索引为例，索引页保存主键值和下一页的地址，主键用bigint 来保存，也就是8字节，地址为6字节，一共就是14字节，页的大小为16KB，那么一个索引页可以保存 16 * 1024 / 14 = 1170 条索引记录

如果B+树树高为三层的话，一条索引记录对应一个数据页，那么三层B+树就可以保存 1170 * 1170 * 16 = 21,902,400 条数据（第一层一个结点，有1170 条索引，那么第二层就有1170个结点，第二层每个结点都有1170条索引），也就是说如果是在两千多万条数据的表中，我们通过三次IO 就可以完成数据的检索了。

索引的分类

主键索引

当在表中定义了一个主键primary key 的时候，MySQL会将其作为索引，这个索引我们称之为主键索引。

我们推荐在每一张表都去创建一个主键，如果没有的话，可以添加一个自增列。

唯一索引

当在表中定义一个唯一键unique 时，MySQL会自动创建索引，这个索引被称之为唯一索引

普通索引

最基本的索引类型，没有唯一性的限制，也就是相比于唯一索引来说，可以存在重复的列

普通索引可以由多列组合组成，这时候可以称为复合索引。

普通索引是由用户自己手动创建的，如果用户觉得某个列查询比较频繁，可以考虑为这一列创建索引。

全文索引

基于文本列（char、varchar）上创建的索引，叫做全文索引。

全文索引可以加快对这列文本列包含的数据查询和DML操作，用于全文搜索，仅有MyISAM 和 InnoDB 引擎支持该索引。

全文索引的本质是倒排索引（记录每个词对文档的映射关系），支持对文本内容的自然语言搜索，允许模糊匹配、关键词权重排序和复杂的语义查询。例如：对文章、评论等大段文本进行关键词搜索

前缀索引

在面对长字符串字段（如 VARCHAR(255)），如果直接构建索引，索引占用的空间会很大，在查询时，也会浪费大量的磁盘 IO，我们可以通过仅索引前几个字符来减少索引体积，同时优化前缀匹配查询。这就是前缀索引

语法：

create index idx_xxx on table_name(column(n));

示例：

create index idx_text on student(text(5));

虽然前缀匹配查询高效，但可能导致重复键值（如多个长字符串前 N 个字符相同），需权衡前缀长度。
我们可以根据索引的选择性来决定，选择性是指不重复的索引值（基数）和数据表记录总数的比值，索引选择性越高则查询效率也就越高，唯一索引的选择性为 1，性能也是最好的

可以通过下面的公式来计算选择性大小：

select count(distinct substring(column, x, x)) / count(*) from table_name)

聚集索引

与主键索引是同义词，也就是说主键索引就是聚集索引，也可以称为聚簇索引。

如果表中没有定义主键primary key ，InnoDB 会使用第一个 unique 和 not null 的列作为聚集索引。

如果表中实在没有primary key 或者合适的 unique 索引，InnoDB 会为新生成的行生成一个行号为 6 字节的 ROW_ID 字段记录，ROW_ID 是单调递增的，并且使用 ROW_ID 作为索引。

注意ROW_ID 是MySQL内部的设置，用户是无法获取的，MySQL使用这个索引是为了更好地创建索引树来管理数据。

非聚集索引

非聚集索引（也可以称为二级索引），就是除了聚集索引以外的索引就是非聚集索引。

二级索引中的每条记录都包含改行的主键列，以及二级索引指定的列。

InnoDB 会使用这个主键值来搜索聚集索引中的行，这个过程称为回表查询

索引覆盖

当一个 select 语句使用了普通索引且查询列表中的列刚好是创建普通索引时的所有或部分列，这时候就可以直接返回数据，就不用进行回表查询，这样的现象被称为索引覆盖

---

简单解释一下：聚集索引创建的索引树是包含完整的真实数据的，非聚集索引创建的索引树是没有包含完整的真实数据但是包含对应的主键值，如果通过非聚集索引查询到的数据不满足用户要求会通过这个主键值来到主键索引树获取完整的数据，这个现象就是回表查询，也就是查了两个索引树

如果通过非聚集索引的索引树能获取到目标数据，就不需要进行回表查询，直接返回数据即可，这就是索引覆盖

举个例子：我们有一张学生表，包含学生的 id ，姓名，班级，其中定义学生的 id 为主键值，姓名字段定义为普通索引，现在我要查询张三的所有信息：

由于姓名就是索引，通过张三到姓名的索引树中查找，随后只能获得主键值id + 姓名为张三的数据，但是我们要的是张三所有的数据，所以数据库就会通过主键值到主键索引树去查找张三的完整的数据，这就是回表查询

如果我们至少想查张三的姓名：select name from student where name = \'张三\'; 这时废话sql ，至少作为演示，数据库通过姓名这个索引树就可以得到张三这个名字，直接返回数据，不需要进行回表查询，这就是索引覆盖

索引的使用

自动创建

当表中定义了主键、外键、唯一键，MySQL都会为其创建对应的索引以及索引树。

如果表中没有任何约束，MySQL回自动为每一列生成一个索引并且使用ROW_ID 进行标识

手动创建

主键索引

1. 在创建表时，直接在字段后面定义主键：

create table test0 (id bigint primary key auto_increment,name varchar(50));

2. 在创建表时，先定义好字段，最后定义主键

create table test0 (id bigint auto_increment,name varchar(50),primary key(id));

3. 创建表之后，我们可以通过修改表来定义主键

create table test0 (id bigint,name varchar(50));alter table test0 add primary key(id);

如果你还想让主键自增，还可以在写下面的语句：

alter table test0 modify id bigint auto_increment;

modify 后面要跟完整的字段定义语句。

唯一索引

1. 在创建表时在定义字段的同时，直接定义唯一键unique

create table test1 (id bigint,name varchar(50) unique);

2. 创建表时，在定义完字段后，单独定义唯一键unique

create table test1 (id bigint,name varchar(50),unique(name));

3. 在创建表后可以通过修改表的字段来定义唯一键

create table test1 (id bigint,name varchar(50));alter table test1 add unqiue(name);

普通索引

1. 创建表时，再创建完字段后，定义普通索引index

create table test2 (id bigint,name varchar(50),index(name));

通过查看表结构，我们得知普通索引的标记为MUL

2. 创建完表后，可以通过修改表的方式添加普通索引index

create table test2 (id bigint,name varchar(50));alter table test2 add index(name);

3. 再创建表后，可以通过 create 语句手动创建普通索引

语法形式：create index index_name on table_name(字段名);

index_name 表示索引名，也就是说你可以自己命名索引。

推荐索引命名为 ind_表名_字段名，这样我们在查询索引的时候，可以得知这是在哪张表的普通索引，并且是哪个字段。

create table test2 (id bigint,name varchar(50));create index ind_test2_name on test2(name);

复合索引

和创建普通主键一样，只是多加一些字段名。这里直接上sql 语句

create table test3(id bigint primary key auto_increment,sn bigint unique,name varchar(50) not null,class_name varchar(20) not null,index(sn,name));

create table test3(id bigint primary key auto_increment,sn bigint unique,name varchar(50) not null,class_name varchar(20) not null);alter table test3 add index (sn,name);

create table test3(id bigint primary key auto_increment,sn bigint unique,name varchar(50) not null,class_name varchar(20) not null);create index ind_test3_sn_name on test3(sn,name);

注意事项

索引的创建应该建立在高频的查询列上，并且数据量比较大，因为数据量小尽管这个是高频查询的列，但是全表扫描会比索引树要更快一些。

索引需要占用额外的空间，每创建一个索引都会创建对应的索引树。

对于表进行插入、删除、更新操作的时候，由于同时会修改索引树，可能会影响到性能。

创建过多或者不合理的索引会导致性能的下降，所以我们要谨慎创建索引。

查看索引

有三种方式查看索引：方式一：show keys from table_name;

我们来看一下上面的表格，Table 表示表名，
Non_unique 表示是否不唯一 ：0 表示否（唯一）1表示是（不唯一）
Seq_in_index 表示在索引中的标号，从 1 开始排号，test3 有一个复合索引（sn, name），所以 name 标号为 2
Column_name 表示对应的字段名
Key_name 表示索引名，主键索引默认索引名为 PRIMARY
Index_type 表示索引的数据结构，这里显示BTREE，说明是B树，其实就是B+树，B+树是B树的变形。

方式二：show index from table_name;

获得的结果集和上面是一样的：

方式三：desc table_name; 这是简单查询索引：

删除索引

删除主键索引：alter table table_name drop primary key;

如果发生上面的报错信息，是因为这个主键值带有自增属性，我们要先删除其自增的属性，然后才能进行删除主键索引的操作：

alter table test3 modify id bigint;

删除了自增属性后，可以进行主键索引的删除：alter table test3 drop primary key;

---

删除其他索引：alter table table_name drop index 索引名;

查看查询语句有没有走索引

以学生表为例：

查看索引：

进行全表数据获取：select * from student;

查看查询语句有没有走索引的语法格式为：explain 查询语句

我们现在来看一下全表数据获取有没有走索引：

介绍一下：select_type 表示查询方式，type 表示访问类型，key 表示索引，possible_keys 表示可能走的索引

由上图可知：查询为简单查询，访问类型为ALL(全表扫描)，key 索引为 NULL，说明没有走索引。

我们来看一下 type:

ALL index range ref eq_ref const,system NULL

从左到右，性能越来越好

ALL：扫描全表
index : 扫描全部索引树
range: 扫描部分索引，在索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于 between、等查询
ref : 使用非唯一索引或非唯一索引前缀进行的查找，不是主键或者不是唯一索引
eq_ref：唯一性索引扫描，遂于每个索引键，表中只要一条记录与之匹配，常见于主键或唯一索引扫描
const，system : 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。system 是const类型的特例，当查询的表只有一行的情况下，使用system
NULL:不用访问表或者索引，直接就能得到结果

我们使用主键来进行查询：


可以看出走的是主键索引，并且进行的是范围查找。

最后我们来看一下Extra:
Using index: 表示使用索引，如果只有 Using index ，说明他没有查询到数据表，只用索引表就完成了这次查询，这个也叫索引覆盖
Using where: 表示条件查询，如果不读取表中的所有数据，或者不是仅仅通过索引树就能获得所许需要的数据的时候，则会出现 Using where

索引的生效与失效

最左前缀法则

如果要使用复合索引，那就需要遵守最左前缀法则

最左前缀法则指的是从索引的最左列开始，并且不跳过索引的列，如果跳过了某一列，索引将会部分失效（后面的字段索引失效）

举个例子：
下面exam 表有两个索引，一个是聚集索引（id）,另一个是复合索引（ind_exam_name_chinese_math）

---

只要复合索引最左的列在查询条件中，就会走这个复合索引，无论查询条件列的先后顺序，只要出现最左匹配的列就可以走这个索引

explain select * from exam where chinese = 67 and name = \'唐三藏\' and math = 98;

---

如果复合索引的最左的列不存在查询条件中，就不会走这个复合索引

explain select * from exam where chinese = 67 and math = 98;

---

如果跳过了某一列，索引将会部分失效（后面的字段索引失效）
下面的示例中，出现name ，符合最左前缀匹配原则，因此会走 复合索引 ind_exam_name_chinese_math，由于chinese 字段没有匹配，因此后面的 math 也会失效，这就是索引的部分失效，你会看到 key_len 相比于上面的完整的索引生效小了一部分，因为这里索引部分失效了，可以计算出 chinese 和 math 索引大小为 6

explain select * from exam where name = \'唐三藏\' and math = 98;

索引失效的情况

使用复合索引，但不满足最左前缀匹配原则，索引失效

---

使用 or 关键字

explain select * from exam where id = 1 or english = 10 or name = \'唐三藏\'; 

可见 id 和 name 索引失效了，这条 sql 没有走索引

由于我们没有建立 english 的索引，导致所有的索引失效，如果要让索引生效，那么你就不能使用没有带索引的列。

explain select * from exam where id = 1 or name = \'唐三藏\'; 

当使用的列都带有索引，即使使用 or 关键字，也可以走索引

---

在索引列上进行了计算

explain select * from exam where id + 1 = 2; 

主键索引失效了

---

在使用模糊查询的时候，开头就使用了 %， 也会导致索引失效

explain select * from exam where name like \'%藏\';

复合索引 ind_exam_name_chinese_math 失效了

---

字段类型不一致

我们的 name 字段是 varchar 类型的

如果我们使用非 varchar 类型的条件进行查询的时候，是会发生索引失效的

explain select * from exam where name = 1;

---

如果mysql觉得全表扫描更快时（数据少）,那么它就不会走索引
这是由MySQL自行决定的

如果你要让 MySQL走你要的索引可以使用以下的语法来指定：

在 from table_name 后面加上下面的其中一个语句即可

user index(index_name) 【建议使用 xxx 索引，MySQL会考虑是否走这个索引】ingore index(index_name) 【不使用 xxx 索引】force index(index_name) 【强制使用 xxx 索引】

示例：
我们再来创建一个新索引：

alter table exam add index(name);

如果不指定索引：

explain select * from exam where chinese = 67 and name = \'唐三藏\' and math = 98;

不指定索引，发现 sql 走的是 复合索引

这次我们来指定使用 name 索引：

explain select * from exam use index(name) where chinese = 67 and name = \'唐三藏\' and math = 98;

忽略索引，不走复合索引，那MySQL只能走 name 单列索引

explain select * from exam ignore index(ind_exam_name_chinese_math) where chinese = 67 and name = \'唐三藏\' and math = 98;

强制走 name 索引：

explain select * from exam force index(name) where chinese = 67 and name = \'唐三藏\' and math = 98;

索引的设计原则

1. 针对数据量大并且查询比较频繁的表需要建立索引
2. 针对常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率就越高
4. 如果是字符串类型的字段，字段的长度较长，可以建立前缀索引
5. 尽量使用复合索引，减少使用单列索引，查询时，复合索引很多时候可以索引覆盖，减少回表查询的频次，提高查询效率
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的空间就越大，维护代价就越大，会影响到增删改的效率
7. 如果索引列不能存储 NULL 值，那么在建表前就要使用 not null 来进行越苏，因为当MySQL的优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询。

小结

创建索引：主键索引(primary key)
唯一索引(unique)
普通索引(index)

创建索引可以在创建表中定义，也可以在表外创建

表外创建索引：alter table table_name add primary key / unique / index 字段名; 还有 普通索引的专属创建形式create index 索引名 on table_name(字段名,字段名...);

修改表的字段：alter table table_name modify 字段定义语句; 通过字段定义语句就可以修改对应的字段属性了。

查看索引的方法：show keys/index from table_name 或者简单查看索引desc table_name;
简单查看索引的结果集的Key: PRI 表示主键索引，UNI表示唯一索引，MUL 表示普通索引

删除索引：alter table table_name (primary key) / (index 索引名);

K6漫画书商城