mysql底层数据结构

数据结构

mysql常用数据结构有hash、BTree、B+Tree树，通过选用合理的数据结构可以尽量减少mysql对磁盘I/O的操作，从而提升数据库整体性能。查询都是索引操作，一般来说索引都是非常大的，所以mysql将索引存储在磁盘中，当我们利用索引查询时会将对应的索引加载到内存中，而并不能将所有索引数据都一次性加载到内存中（如果都加载到内存中，内存或许会被撑爆），所以减少I/O操作对性能提升起到至关重要的作用。一下介绍一下mysql中常用的数据结构。

hash

hsah索引通过计算hash值存储索引位置，底层数据结构类似于数组+链表，hash索引检索数据可以说是最快的一种，如下图：

hash索引通过计算hash值定位数据所在磁盘位置，所以查询索引只需要执行一次磁盘I/O操作，所以说hash索引是最快的一种。
hash的特点：

• 对索引的key只需要进行一次hash计算就可定位出数据所在位置。
• 很多时候hash比B+Tree索引更高效。
• hash索引只能进行 “=” “in” 等操作，无法进行范围比较。
• 易发生hash冲突问题。

BTree

BTree树又称为多路搜索树，其原理为每一个叶子节点保存索引字段，并在叶子节点下保存一个对应磁盘文件地址，如下图：

mysql会分配一块尽可能大的磁盘空间来存储索引，每两个索引之间通过指针指向另外一块磁盘地址，这样可以通过二分查找等算法快速找到对应的索引数据减少树的深度。但是mysql并没有使用该数据结构。
BTree树特点：

• 叶节点具有相同的深度，叶节点的指针为空。
• 每一个叶节点数据不重复。
• 节点中的数据索引从左到右递增。

B+Tree

B+Tree是BTree的变种，主要的区别为该树非叶子节点不存储data，只存储索引（冗余），叶子节点通过指针连接前后数据节点，如下图：

B+Tree树所有非叶子节点只存储冗余索引，这样做的目的是为了一页数据中尽可能的保存更多的索引数据，mysql中默认一页数据大小为16kb，可以通过mysql命令查询页数据大小：SHOW GLOBAL STATUS LIKE 'innodb_page_size';，下面讲解一下为什么默认设置一页数据大小16kb，并且计算一下一页数据能存储多少个索引，计算如下：
如使用bigint为索引类型，一个bigint类型占用8个字节，两个索引之间的指针在mysql底层c语言占用6个字节，所以一页数据大小为：
16 * 1024 / (8 + 6) = 1170；
所以一页数据大概可以放下1170个索引，而一个叶子节点的data数据一般不超过1kb，所以一页叶子节点大概可以放下16个数据，那么3层树就可以放下：
1170 * 1170 * 16 = 21902400；
21902400行数据，这个时候如果我们查询一个索引为36的数据，mysql只需要查询3次就能查询出来数据，速度是相当快的。而且在mysql中会将根节点放入常驻内存中，所以查询速度极快。
B+Tree树特点：

• 非叶子节点只保存索引数据（冗余），不保存data数据，可以放更多数据。
• 叶子节点包含所以索引字段。
• 叶子节点通过指针连接，提升区间访问性能。

mysql最终选用B+Tree做为索引数据结构，树的高度取决于每一页可存储的索引个数，当一页数据存储更多的索引这个树的高度就会越小，检索数据速度就会越快。而且B+Tree对于范围查找有很好的支持，因为所有节点按从左到右的升序进行排序，这时只需要定位到范围中的边界值就能通过叶子节点的前后指针进行数据查找。对与min()、max()函数速度也是极快，原因还是叶子节点是排好序的所以可以直接取出最大值和最小值。

索引

索引是帮助mysql高效检索数据的排好序的数据结构。

索引分类

按数据结构分类：B+Tree索引、Hash索引、Full-text索引。
按物理存储分类：聚集索引、非聚集索引（也叫二级索引、辅助索引）。
按字段特性分类：主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)。
按字段个数分类：单列索引、联合索引（也叫复合索引、组合索引）。

B+TREE、Hash、Full-text

B+Tree和Hash索引以上已讲。Full-text暂未深入了解。

聚集索引、非聚集索引

聚集索引和非聚集索引也可以称为聚簇索引和非聚簇索引。其主要区别在Myisam存储引擎和innodb存储引擎中。

myisam

myiasm中使用的是非聚集索引，可以通过磁盘文件发现使用myiasm存储引擎的数据表在磁盘中的文件，通过创建一个myisam表查看其磁盘中的文件，sql如下：

CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  `name` varchar(64) CHARACTER SET sjis NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1
2
3
4
5
6

查看磁盘mysql安装目录下的data目录中的sql_test（我这里在sql_test数据库下创建的test1表）目录，如下：

可以看到test1表一共生成了3个磁盘文件分别是test1.frm、test1.MYD、test1.MYI这三个文件作用如下：

• .frm文件：该文件保存test1表的元数据和定义等信息。
• .MYD文件：该文件保存test1表中的所有数据。
• .MYI文件：该文件保存test1表中的所有索引信息。
  通过以上三个文件可以发现，在mysiam存储引擎中表数据和表索引分别用两个文件存储，这种就称为非聚簇索引。我们在通过索引查询数据时需要先查询.MYI文件，在索引树中查询到索引对应的数据所在磁盘地址，拿到磁盘地址后再在.MYD文件中查询对应的数据。

innodb

innodb使用聚集索引和非聚集索引，通过磁盘文件可以看到和myisam文件的区别，创建一个test2表使用innodb存储引擎，sql如下：

CREATE TABLE `test2` (
  `id` int(11) NOT NULL,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1
2
3
4
5
6

查看磁盘mysql安装目录下的data目录中的sql_test（我这里在sql_test数据库下创建的test2表）目录，如下：

这里我们可以看到innodb存储引擎和myisam存储引擎的区别，innodb存储引擎只有两个表文件，分别是test2.frm、test2.ibd，这两个文件作用如下：

• .frm文件：和myisam存储引擎一样，保存表的元数据和定义等信息。
• .ibd文件：这个文件中主要保存表的数据和索引数据。

innodb会使用聚集索引和非聚集索引，主要分为一下两种情况：

1. 表中使用主键索引
   这时就是聚集索引，因为索引树中叶子节点对应的data信息保存的是该条主键对应的一整行数据。
2. 表中使用联合索引
   这是就是非聚集索引，因为在联合索引创建的索引树中叶子节点对应的data信息保存的是主键id，当使用联合索引查询数据时只能检索到对应的主键id，通过拿到主键id去聚集索引中再次查询才能查询出实际需要的数据。

一句话讲聚集索引和非聚集索引：聚集索引中数据和索引保存在一起，非聚集索引数据和索引没有保存在一起。

主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)

主键索引(PRIMARY KEY)

数据表的主键列使用的就是主键索引。
一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

通过创建表时创建主键索引，sql如下：

CREATE TABLE `test_sql`.`test3`  (
  `id` int NOT NULL,
  `name` varchar(64) NOT NULL,
  `balance` int NOT NULL,
  `position` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) # 主键索引
);
1
2
3
4
5
6
7

通过ALTER TABLE创建，sql如下：

ALTER TABLE tableName ADD PRIMARY KEY(`column`);
# 实际语句
ALTER TABLE test3 ADD PRIMARY KEY(id);
1
2
3

唯一索引(UNIQUE)

唯一索引表示数据库表中一个字段或多个字段的唯一性，在这些字段中不能有相同的数据，否则会报错。

通过创建表时创建唯一索引，sql如下：

CREATE TABLE `test_sql`.`test4`  (
  `id` int NOT NULL,
  `name` varchar(64) NOT NULL,
  `identity` varchar(18) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `unique_identity` # 唯一索引
`(`identity`)
);
1
2
3
4
5
6
7
8

通过create unique index创建，sql如下：

CREATE UNIQUE INDEX indexName ON tableName(`column`);
# 实际语句
CREATE UNIQUE INDEX unique_identity ON test4(`identity`);
1
2
3

通过alter table创建，sql如下：

ALTER TABLE tableName ADD  UNIQUE KEY indexName (`column`);
# 实际语句
ALTER TABLE test4 ADD  UNIQUE KEY unique_identity (`identity`);
1
2
3

普通索引(INDEX)

在某一列上创建一个普通索引来提升sql检索速度。

通过创建表的时候创建，sql如下：

CREATE TABLE `test4` (
  `id` int(11) NOT NULL,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `identity` varchar(18) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_itentity` (`identity`) USING BTREE # 普通索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1
2
3
4
5
6
7

通过create index创建，sql如下：

CREATE INDEX indexName ON tableName(`column`)
# 实际语句
CREATE INDEX idx_identity ON test4(`identity`)
1
2
3

通过alter table创建，sql如下：

ALTER TABLE tableName ADD INDEX indexName (`column`);
# 实际语句
ALTER TABLE test4 ADD INDEX idx_identity(`identity`);
1
2
3

全文索引(FULLTEXT)

在数据库中常用的查询方式一般是 等价，范围方式。当然也有LIKE %的模糊查询，虽然用不到索引，在文本内容比较少时是比较合适，但是对于大量的文本数据检索，全文索引在大量的数据面前，能比 LIKE % 快很多，速度不是一个数量级。所以总结下来，索引全文索引就是为这种场景设计的。

通过创建表的时候创建，sql如下：

CREATE TABLE `test_sql`.`Untitled`  (
  `id` int NOT NULL,
  `name` varchar(64) NOT NULL,
  `article
` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT INDEX `full_article` # 全文索引 
`(`article`) USING BTREE
);
1
2
3
4
5
6
7
8
9

通过create index创建，sql如下：

CREATE FULLTEXT INDEX indexName ON tableName (`column`);
# 实际语句
CREATE FULLTEXT INDEX idx_article ON test5(`article`);
1
2
3

通过alter table创建，sql如下：

ALTER TABLE tableName ADD FULLTEXT INDEX indexName (`column`);
# 实际语句
ALTER TABLE test5 ADD FULLTEXT INDEX idx_article(`article`);
1
2
3

单列索引、联合索引

单列索引表示创建索引只有一列，联合索引表示创建多列组合一起的索引，如下：

# 单列索引
ALTER TABLE test4 ADD  UNIQUE KEY unique_identity (`identity`);
# 联合索引
ALTER TABLE test4 ADD  UNIQUE KEY unique_identity (`identity`,'name');
1
2
3
4

以上为个人学习总结，如有错误请大神指教，感谢。