目录
1. 索引
MySQL的索引是一种数据结构，它可以帮助数据库高效的查询、更新数据表中的数据。索引通过一定的规则排列数据表中的记录，使得对表的查询可以通过对索引的搜索来加快速度。在应用程序的运行过程中，查询操作的频率远远高于增删改的频率。
MySQL索引类似于书籍的目录，通过指向数据行的位置，可以快速定位和访问表中的数据
笔画索引：一对一；偏旁部首索引；拼音索引
2. B+树
B+树是索引使用的数据结构
B+树是一种经常用于数据库和文件系统等场合的平衡查找树，MySQL索引采用的数据结构
时间复杂度：O(logn)，且可以有效地控制树高
在数据量相同的情况下，可以有效控制树高，即可以使用更少的IO次数找到目标节点，从而提高数据库的效率
B+树的特点：
- 能够保持数据稳定有序，插入与修改有较稳定的时间复杂度
- 非叶子节点仅具有索引作用，不存储数据，所有叶子节点保存真实数据
- 所有叶子节点构成一个有序链表，可以按照key排序的次序依次遍历全部数据
B+树与B树对比：
- 叶节点之间有相互连接的引用，可以通过一个叶节点找到与它相邻的兄弟节点。MySQL在组织叶子节点时使用的是双向链表
- 非叶节点的值都包含在叶结点中。MySQL非叶节点只保存了对子节点的引用，没有保存真实的数据，所有真实的数据都保存在叶结点中
- 对于B+树而言，在相同树高的情况下，查找任一元素的时间复杂度一样，性能均衡
3. MySQL中的页
在.ibd（innodb存储引擎后生成的表空间文件后缀是.ibd）文件中最重要的结构体就是Page(页)，页是内存与磁盘交互的最小单元，默认大小为16KB，每次内存与磁盘的交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的，这样做是因为在使用数据的过程中，根据局部性原理，将来要使用的数据大概率与当前访问的数据在空间上是邻近的，所以一次从磁盘中读取一页的数据放入内存中，当下次查询的数据还在这个页中时就可以从内存中直接读取，从而减少磁盘I/O提高性能。每个页中即使没有数据也会使用16KB的存储空间，同时与索引的B+树中的节点对应。
Linux操作系统中管理文件的最小单位是4KB，MySQL作为一个数据库程序，以4KB大小管理数据显然太少，所以定义了16KB为默认页大小
当从内存中往磁盘里写数据页时，写到一半操作系统挂了，此时MySQL应如何保证数据安全？
在落盘之前通过记录各种日志，保证重启之后可以找到没有落盘的内容
页文件头和页文件尾中包含的信息，如下图所示：
上一页页号和下一页页号这两个属性可以把页与页之间连接起来，形成一个双向链表
通过页号和页大小可以计算出下一页和上一页在磁盘上的偏移量
页主体：
页主体部分是保存真实数据的主要区域，每当创建一个新页，都会自动分配两个行，一个是页内最小行Infimun，另一个是页内最大行Supremun，这两个行并不存储任何和真实信息，而是作为数据行链表的头和尾，第一个数据行有一个记录下一行的地址偏移量的区域next_record将页内所有数据行组成一个单向链表，此时新页的结构如下表所示：
当向一个新页插入数据时，将Infimun连接第一个数据行，最后一行真实数据连接Supremun，这样数据行就构建成了一个单向链表，更多的行数据插入后会按照主键从小到大的顺序进行连接，如下图所示：
页目录：
最小行 单独为一组。数据行的分组，每个分组最多可以容纳8条记录，超过8条时会分裂出来一个新组。最大行永远在最后一个分组中。创建分组时会在页目录创建一个槽，槽的数量=分组的数量
槽会指定对应分组的最后一条记录，同时保存这条记录的主键值
数据页头：
数据页头记录了当前页保存数据相关的信息，如下表所示：
4. B+在MySQL中的应用
非叶子节点保存索引数据，叶子节点保存真实数据，如下图所示：
以查找id为5的记录为例，完整的检索过程如下：
- 首先判断B+树的根结点中的索引记录，此时5<7，应该访问左孩子结点，找到索引页2
- 在索引页 2中判断id的大小，找到与5相等的记录，命中，加载对应数据页
以上的IO过程，加载索引页1->加载索引页2->加载数据页3
5. 索引分类
5.1 主键索引
当在表上定义一个主键PRIMARY KEY时，自动创建索引，索引的值是主键列的值，InnoDB使用它作为聚簇索引（聚集索引）
推荐为每个表定义一个主键，如果没有逻辑上唯一且非空的列或列集可以使用主键，则添加一个自增列
5.2 普通索引
为了提升查询效率，通常为查询频繁的列创建索引，可以包含一个列或多个列
普通索引是最基本的索引类型，没有唯一性限制，可为多列创建组合索引，称为复合索引或组全索引
5.3 唯一索引
当在一个表上定义一个唯一键UNIQUE时，自动创建唯一索引。与普通索引类似，区别在于唯一索引的列不允许有重复值
5.4 全文索引
基于文本列（CHAR、VARCHAR、TEXT列）上创建，以加快对这些列中包含的数据查询和DML操作。用于全文搜索，仅MyISAM和InnoDB引擎支持
5.5 聚集索引
与主键索引是同义词，如果没有为表定义PRIMARY KEY，InnoDB使用第一个UNIQUE和NOT NULL的列作为聚集索引
如果表中没有PRIMARY KEY或合适的UNIQUE索引，InnoDB会为新插入的行生成一个行号并用6字节的ROW_ID字段记录，ROW_ID单增，并使用ROW_ID作为索引
5.6 非聚集索引
聚集索引以外的索引称为非聚集索引或二级索引，二级索引中的每条记录都包含该行的主键列以及二级索引指定的列。InnoDB使用这个主键值来搜索聚集索引中的行，这个过程称为回表查询
非聚集索引的查询过程：
- 通过索引查到叶子节点中的索引记录
- 通过索引记录中的主键值，去主键索引树中找相应的完整记录（回表查询）
select * from student where name='张三';
5.7 索引覆盖
当一个select语句使用了普通索引且查询列表中的列刚好是创建普通索引时的所有或部分列，这时就可以直接返回数据，而不用回表查询，这种现象称为索引覆盖
select name from student where name='张三';
6. 使用索引
6.1 自动创建
当为一张表添加主键约束（Primary key）、外键约束（Foreign key）、唯一约束（Unique）时，MySQL会为对应的列自动创建一个索引。如果表不指定任何约束时，MySQL会自动为每一列生成一个索引并用ROW_ID进行标识
6.2 手动创建
6.2.1 主键索引
-- 创建表时创建主键
create table t1(
id bigint primary key auto_increment,
name varchar(20)
);
-- 创建表时单独指定主键列
create table t2(
id bigint auto_increment,
name varchar(20),
primary key(id)
);
-- 修改表的id列为主键索引
create table t3(
id bigint,
name varchar(20)
);
alter table t3 add primary key(id);
alter table t3 modify id bigint auto_increment;6.2.2 唯一索引
-- 创建表时创建唯一列
create table t1(
id bigint primary key auto_increment,
name varchar(20) unique
);
-- 创建表时单独指定唯一列
create table t2(
id bigint primary key auto_increment,
name varchar(20),
unique (name)
);
-- 修改表的列为唯一索引
create table t3(
id bigint primary key auto_increment,
name varchar(20)
);
alter table t3 add unique (name);6.2.3 普通索引
用MUL表示
-- 创建表时指定索引列
create table t1(
id bigint primary key auto_increment,
name varchar(20) unique,
sno varchar(10),
index(sno)
);
-- 修改表中的列为普通索引
create table t2(
id bigint primary key auto_increment,
name varchar(20),
sno varchar(10)
);
alter table t2 add index (sno);
-- 单独创建索引并指定索引名
create table t3(
id bigint primary key auto_increment,
name varchar(20),
sno varchar(10)
);
-- 为name列创建索引时必须指定索引名
create index index_name on t3(sno);6.3 创建复合索引
创建语法与创建普通索引相同，只不过指定多个列，列与列之间用逗号隔开
-- 创建表时指定索引列
create table t1(
id bigint primary key auto_increment,
name varchar(20),
sno varchar(10),
class_id bigint,
index (sno,class_id)
);
-- 修改表中的列为复合索引
create table t2(
id bigint primary key auto_increment,
name varchar(20),
sno varchar(10),
class_id bigint
);
alter table t2 add index (sno,class_id);
-- 单独创建索引并指定索引名
create table t3(
id bigint primary key auto_increment,
name varchar(20),
sno varchar(10),
class_id bigint
);
create index index_name on t3 (sno,class_id);
6.4 查看索引
方式一：show keys from 表名
-- 方式一：show keys from 表名
show keys from t1;
-- 方式二：
show index from t1;
-- 方式三：desc 表名;
desc t1;6.5 删除索引
6.5.1 主键索引
-- 语法：
alter table 表名 drop primary key;删除test1表中的主键：
此提示是由于自增列的错误，先删除自增属性，然后重新删除主键：
查看结果：
6.5.2 其他索引
-- 语法
alter table 表名 drop index 索引名;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
