[MySQL#11] 索引底层(2) | B+树 | 索引的CURD | 全文索引

目录

1.B+树的特点

索引结构

复盘

其他数据结构的对比

B树与B+树总结

聚簇索引与非聚簇索引

辅助索引

2. 索引操作

主键索引

1. 创建主键索引

第一种方式

第二种方式

第三种方式

2. 查询索引

第一种方法

第二种方法

第三种方法

3. 删除索引

删除主键索引

删除其他索引

4. 特点

唯一索引

1. 创建

第一种方式

第二种方式

第三种方式

2. 删除唯一索引

3. 特点

普通索引

第一种方式

第二种方式

第三种方式

9. 普通索引的特点

复合索引

多列索引的创建

复合索引的工作原理

复合索引的应用场景

索引创建原则

索引总结

3.全文索引

创建

查询与全文索引

总结

---

1.B+树的特点

叶子节点保存有数据，非叶子节点不要数据

• 非叶子节点：不存数据，只存储目录项，可以存储更多的目录项。
• 目录页：一个目录页可以管理更多的叶子Page，使树更“矮胖”，减少I/O次数，提高效率。

叶子节点全部用链表级联起来

• 链表级联：叶子节点用链表级联，便于进行范围查找，提高查询效率。

索引结构

• InnoDB的索引结构：MySQL InnoDB存储引擎使用B+树作为索引结构。
• 主键索引：默认情况下，如果没有指定主键，MySQL会自动生成一个隐藏列作为主键。
• 普通索引：用户可以建立其他列的索引，这些索引也是B+树结构。

复盘

• Page分类：Page分为目录页和数据页。目录页只放各个下级Page的最小键值。
• 查找过程：自顶向下查找，只需加载部分目录页到内存，大大减少I/O次数。
• 索引构建：构建索引就是在MySQL内存中构建B+树，以指定列作为key值。

其他数据结构的对比

• 链表：线性遍历，效率低。
• 二叉搜索树：可能退化成线性结构，效率不稳定。
• AVL & 红黑树：虽然平衡，但树高较高，I/O次数多。
• Hash：适合点查询，但在范围查找方面表现不佳。

B+ vs B

B树

B+树

• B+树非叶子节点不存数据，数据都在叶子节点，并且所有叶子节点用链式结构连接起来。
• 而B树每一个节点内既包含目录项又包含数据，所有B树除了叶子节点有数据路上节点也会包含数据。还有B树的叶子节点是没有被链式结构连接起来的。

那为什么mysql没有使用B树而用的B+呢？

第一，mysql认为如果给非叶子节点增加数据，也就意味着单个page里能够保存的目录项变少了，意味着一个页目录所能管理的子目录子page就变少了，一旦变少了，在逻辑上这棵B树会比B+树更高一些更瘦一些，也就意味从根道叶子节点搜索的时候，要经过更多的节点要经历更多次IO，算法和IO带来的成本，永远都是IO带来的成本更高的。

第二，B树的叶子节点没有相连，也就意味着想进行范围查找，依旧要重新遍历这颗B树。而一旦重新遍历B树也就注定在遍历的时候需要每次查B树，可能有些page并不在内存里，又需要在进行IO，同时每次查效率也很慢，不像B+树找到起始位置线性遍历，一定拿到的是有效范围内所有数据。

B树与B+树总结

• B树：每个节点内既包含目录项又包含数据，树高较高，I/O次数多。
• B+树：非叶子节点不存数据，树更矮，I/O次数少，叶子节点用链表级联，便于范围查找。

例：演示一个Max.Degree=3 的B+树

数据结构演示链接

聚簇索引与非聚簇索引

非聚簇索引

• MyISAM存储引擎，索引和数据分离，适合某些场景。
• MyISAM 引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为 MyISAM表的主索引， Col1 为主键。

聚簇索引

• InnoDB存储引擎，数据和索引放在一起，提高查询效率。

下面我们见一下现象，文件结构

InnoDB：

• test1.frm：表结构数据。
• test1.ibd：主键索引和用户数据。

MyISAM（分离）：

CREATE TABLE myisam_test (id INT PRIMARY KEY
) ENGINE=MyISAM;

• test2.frm：表结构数据。
• test2.MYD：数据记录。
• test2.MYI：索引 数据指针。

辅助索引

• InnoDB：辅助索引的叶子节点只包含对应记录的主键值，需要进行回表查询。
• MyISAM：辅助索引和主键索引类似，叶子节点包含数据记录的地址。

注意：

• 所以一张表没有指明任何主键，mysql默认会给表添加默认主键也会以B+树结构呈现，只不过我没有设立主键就只能线性遍历。
• 如果我们指明主键默认我们的表会配上主键索引，会以我们自己设置的主键为key值设立主键索引。如果我们指明主键索引，未来还想给其他列设置索引，我们可以手动添加。
• 添加之后会在mysql内部重新构建B+树，以MyISAM为例会指向记录，如果是InnoDB保存的是主键值方便我们快速索引。换句话说，
• 一个表可能会建立主键索引或者其他普通索引，不管建立任何索引最终在mysql中一张表可能会有多颗B+树。
• 索引语法上分三类：主键索引、唯一键索引、普通索引，但其实宏观上就两类一个是主键索引，指明就用主键的没有指明就用默认的。一个是普通索引，包括唯一索引。

---

2. 索引操作

主键索引

1. 创建主键索引

第一种方式

在创建表时直接指定 primary key

create table test1(id int primary key, name varchar(30));

• 说明：在字段名后指定 primary key，MySQL会根据该列构建主键索引。

第二种方式

在创建表的最后指定某列或某几列为主键索引

create table test2(id int, name varchar(30), primary key(id));

• 说明：在表定义的最后指定某列或某几列为主键。

第三种方式

创建表以后再添加主键

create table test3(id int, name varchar(30));
alter table test3 add primary key(id);

• 说明：先创建表，再通过 alter table 添加主键。

2. 查询索引

第一种方法

使用 show keys from 表名

show keys from test1;

第二种方法

使用 show index from 表名

show index from test1;

第三种方法

使用 desc 表名

desc test1;

• 说明：信息较为简略，主要用于查看表结构。

3. 删除索引

删除主键索引

• 第一种方法

alter table 表名 drop primary key;

删除其他索引

• 第二种方法

alter table 表名 drop index 索引名;

• 第三种方法

drop index 素引名 on 表名;

4. 特点

• 一个表中最多有一个主键索引
• 主键索引的效率高（主键不可重复）
• 创建主键索引的列，值不能为null，且不能重复
• 主键索引的列基本上是int

---

唯一索引

1. 创建

第一种方式

• 在表定义时指定 unique 唯一属性

create table test4(id int primary key, name varchar(30) unique);

第二种方式

• 在表定义的最后指定某列或某几列为 unique

create table test5(id int primary key, name varchar(30), unique(name));

第三种方式

• 创建表以后再添加唯一键

create table test6(id int primary key, name varchar(30));
alter table test6 add unique(name);

• 说明：添加唯一键后，表中会有两个B+树，一个是主键索引，另一个是以指定列构建的唯一索引。

2. 删除唯一索引

• 使用 alter table 删除索引

alter table 表名 drop index 索引名;

• 说明：唯一索引的删除方式与普通索引相同，不能使用 drop unique。

为什么说这个呢，我们发现主键很特殊，构建是 add primary key，删除是 drop primary key 这没问题。

• 但是删除唯一键不能用drop unique，用的是drop index。
• 未来你会发现我们删除普通索引用的也是drop index。
• 说明unique索引本身也是一个普通索引。只不过指明它是uniqe是为了照顾表中的约束关系。
• 其实在索引层面，普通索引和唯一键索引都是一般索引。
• 最特殊的就是主键索引。

3. 特点

• 一个表中可以有多个唯一索引（唯一是指 无重复数据）
• 查询效率高
• 如果在某一列建立唯一索引，必须保证这列不能有重复数据
• 如果一个唯一索引上指定 not null，等价于主键索引

---

普通索引

创建

第一种方式

• 在表定义的最后指定某列为索引

create table test8(id int primary key,name varchar(20),email varchar(30),index(name)
);

第二种方式

• 创建完表以后指定某列为普通索引

create table test9(id int primary key,name varchar(20),email varchar(30)
);
alter table test9 add index(name);

• 说明：普通索引和唯一索引在结构上没有区别，都是B+树。

第三种方式

• 创建一个索引名为 idx_name 的索引

create table test10(id int primary key,name varchar(20),email varchar(30)
);
create index idx_name on test10(name);

9. 普通索引的特点

• 一个表中可以有多个普通索引，普通索引在实际开发中用得较多
• 如果某列需要创建索引，但该列有重复值，应使用普通索引

---

复合索引

多列索引的创建

问题：创建索引时只能在某一列创建吗？如果表中有多列信息，是否可以创建以多列为key值的索引结构？

答案：可以！

示例：

create table test11(id int primary key,name varchar(20),email varchar(30)
);
create index idx_name_email on test11(name, email);

解释：

• 创建复合索引：我们以 name 和 email 两列建立索引。
• 索引数量：创建复合索引后，表中显示有三个索引，但这并不意味着新增了两个B+树。

复合索引的工作原理

• 单一B+树：复合索引实际上只构建了一颗B+树，而不是两颗。
• 键值组合：这颗B+树的键值是由 name 和 email 两列值组合而成。
• 搜索条件：在搜索时，这两列必须同时满足条件才能找到目标记录。

示例：

• 索引名称：复合索引的名称默认为 idx_name_email，以多列中的第一列 name 作为索引名称。
• 删除索引：删除复合索引时，只需一次操作即可删除整个复合索引。

alter table test11 drop index idx_name_email;

复合索引的应用场景

何时使用复合索引：

• 避免回表：InnoDB普通索引的叶子节点放的是表的主键的key值，这意味着需要回表查询。但如果以 name 和 email 构建复合索引，未来高频查询时，可以直接通过 name 和 email 查找，数据本身就在这颗复合索引的B+树里，无需回表。
• 索引覆盖：如果查询条件和返回值都在复合索引的列中，可以直接从索引中获取数据，无需回表，这种情况称为索引覆盖。
• 最左匹配原则：MySQL在索引匹配时是从左侧开始向右匹配。例如，可以按 name 或 name 和 email 查找，但不能直接按 email 查找。

示例：

• 查询姓名和QQ号：

create table test12(id int primary key,name varchar(20),qq varchar(20)
);
create index idx_name_qq on test12(name, qq);

• • 查询：通过 name 查找 qq 号，可以直接从复合索引中获取数据，无需回表。

select qq from test12 where name = '张三';

索引创建原则

1. 频繁作为查询条件的字段：应该创建索引。

2. 唯一性太差的字段：不适合单独创建索引，即使频繁作为查询条件。

• 示例：给性别打上索引，但性别只有男和女，构建出的B+树并不优秀。

3. 更新非常频繁的字段：不适合作创建索引。

• 示例：考试信息更改太频繁，索引创建出来是为了方便查询，频繁修改不仅影响数据，还会调整索引结构。

4. 不会出现在 where 子句中的字段：不应创建索引。

• 示例：某些字段从未在 where 子句中出现，创建索引没有意义。

适合创建索引：

• 高频读取
• 低频修改
• 唯一性高
• 避免冗余：避免在唯一性差或更新频繁的字段上创建索引。

索引总结

• 主键索引：一个表中最多一个，效率高，值不能为null且不能重复。
• 唯一索引：一个表中可以有多个，查询效率高，值不能重复。
• 普通索引：一个表中可以有多个，适用于有重复值的列。
• 复合索引：将多列值放在一起充当键值，构建B+树，查找时必须满足多列值一致。

---

3.全文索引

B+树索引：

• 键值字段：通常较短，如 id、qq 等。
• 用途：用于快速找到一条记录或记录中的某一列或几列。

全文索引：

• 键值字段：通常较长，如文章内容，每行内容可能包含数千个字符。
• 用途：用于在长文本中查找特定的内容，而不仅仅是找到一条记录。

创建

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但有以下要求：

• 存储引擎：必须是MyISAM。
• 语言支持：默认支持英文，不支持中文。如果需要对中文进行全文检索，可以使用sphinx的中文版(coreseek)。

示例：

CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,FULLTEXT (title, body)
) engine=MyISAM;

插入数据：

INSERT INTO articles (title, body) VALUES('MySQL Tutorial', 'DBMS stands for DataBase ...'),('How To Use MySQL Well', 'After you went through a ...'),('Optimizing MySQL', 'In this tutorial we will show ...'),('1001 MySQL Tricks', '1. Never run mysqld as root. 2. ...'),('MySQL vs. YourSQL', 'In the following database comparison ...'),('MySQL Security', 'When configured properly, MySQL ...');

查询与全文索引

普通查询：

select * from articles where body like '%database%';

• 问题：虽然查询出数据，但没有使用到全文索引。
• 检查：可以使用 explain 工具查看查询是否使用了索引。

使用全文索引查询：

select * from articles where match(title, body) against ('database');

设置：

• match：匹配条件。
• against：匹配的关键字，这里是 database。

检查：

explain select * from articles where match(title, body) against ('database');

解释：

• type：fulltext 表示使用了全文索引。
• key：表示使用了哪个索引。

总结

• 全文索引：用于在长文本中查找特定内容，特别适用于文章或大量文字的字段。
• 创建：需要使用 FULLTEXT 关键字，并且表的存储引擎必须是MyISAM。
• 查询：使用 match 和 against 关键字进行全文索引查询，可以显著提高查询效率。