MySQL 服务器上负责对表中数据的读取和写入工作的部分是存储引擎,比如 InnoDB、MyISAM、Memory 等等,不同的存储引擎一般是由不同的人为实现不同的特性而开发的,目前OLTP业务的表如果是使用 MySQL 一般都会使用 InnoDB 引擎,这也是默认的表引擎。
为了能说明 InnoDB 引擎的原理,我们必须先搞清楚 InnoDB 的存储结构,通过这些存储结构才能实现 InnoDB 的事务特性。
首先我们来看看 InnoDB 表的一行数据是如何存储的。InnoDB是一个持久化的存储引擎,也就是数据都是保存在磁盘上面的。但是读写数据,对数据处理,这些是发生在内存中。也就是数据需要从磁盘读取到内存。那么这个读取是如何读取呢?如果处理哪条数据,就读取哪一条到内存中,这样效率也太低了。因为每条数据都是一个硬盘寻址读取,我们要减少这个硬盘寻址读取的次数,可以考虑一块一块的读取数据,这样,我们很可能下次请求需要的数据就已经在内存中了,就省去了从硬盘读取。基于这个思想,InnoDB 将一个表的数据划分成了若干页(pages),这些页通过 B-Tree 索引联系起来。每一页大小默认为 16384 Bytes 也就是 16KB(配置为 innodb_page_size
)。
同时,这个 B-Tree 索引就是我们经常听到的聚簇索引(Clustered Index),如果表有主键,那么主键索引就是这个聚簇索引。通过上面的描述,这个索引的节点是包含所有行所有列数据的(就是刚刚我们提到的页)。其他的二级索引的节点只是有指向主键的指针。
对于比较大的字段,例如 Text 类型的字段,如果也存在于这个聚簇索引上,那这个节点数据就会过大,会一下子读取很多页出来,这样读取效率会降低(例如在我们没有想读取这个 Text 列的请求情况下)。所以,InnoDB 对于变长字段,一般倾向于将他们存储在其他地方。至于怎么存储,这个还和 InnoDB **行格式(InnoDB Row Format)**有关。行格式一共有四种:Compact、Redundant、Dynamic和Compressed。
我们可以在创建或修改表的语句中指定行格式:
CREATE TABLE 表 (
)ROW_FORMAT=行格式;
ALTER TABLE 表 ROW_FORMAT=行格式;
Compact 行格式存储
我们来创建一个包含几乎所有基本数据类型的表,其他的例如 geometry,timestamp 等等,也是基于 double 还有 bigint 而来的, text、json、blob等类型,一般不与行数据一起存储,我们之后再说:
create table record_test_1 (
id bigint,
score double,
name char(4),
content varchar(8),
extra varchar(16)
)row_format=compact;
插入如下几条记录:
INSERT INTO `record_test_1`(`id`, `score`, `name`, `content`, `extra`) VALUES (1, 78.5, 'hash', 'wodetian', 'nidetiantadetian');
INSERT INTO `record_test_1`(`id`, `score`, `name`, `content`, `extra`) VALUES (65536, 17983.9812, 'zhx', 'shin', 'nosuke');
INSERT INTO `record_test_1`(`id`, `score`, `name`, `content`, `extra`) VALUES (NULL, -669.996, 'aa', NULL, NULL);
INSERT INTO `record_test_1`(`id`, `score`, `name`, `content`, `extra`) VALUES (2048, NULL, NULL, 'c', 'jun');
目前表结构:
+-------+------------+------+----------+------------------+
| id | score | name | content | extra |
+-------+------------+------+----------+------------------+
| 1 | 78.5 | hash | wodetian | nidetiantadetian |
| 65536 | 17983.9812 | zhx | shin | nosuke |
| NULL | -669.996 | aa | NULL | NULL |
| 2048 | NULL | NULL | c | jun |
+-------+------------+------+----------+------------------+
查看底层存储文件:record_test_1.ibd
,用16进制编辑器打开,我这里使用的是Notepad++
和他的HEX-Editor
插件。可以找到如下的数据域(可能会有其中 mysql 生成的行数据不一样,但是我们创建的行数据内容应该是一样的,而且数据长度应该是一摸一样的,可以搜索其中的字符找到这些数据):
我们这里先直接给出这些数据代表的意义,让大家直观感受下:
变长字段长度列表:10 08
Null值列表&#x