带你读Mysql技术内幕[4]——什么是表

前言：本文是自己的阅读《Mysql技术内幕——InnoDB存储引擎》的笔记，主要是为了将阅读和实践结合起来，途中会穿插自己的理解及自己工作中的实践。我理解阅读一本经典的书，无论是技术书籍还是生活数据，带着目的去读，知道书讲得是什么，并且结合自己的理解，输出一定的文字。

本篇文章主要详细介绍数据在表中是如何组织和存放的，表是特定实体的数据集合。
本文主要目录如下

1. 索引组织表

在Innodb存储引擎中，数据在表中是按照主键的顺序存储的。如果没有显示的定义主键，则会按照如下方式选择或者创建主键：

1. 判断表中是否有非空的唯一索引(Unique NOT NULL)，如果有，则该列为主键。
2. 如果没有，则会自动创建一个6个字节的指针。
   当表中有多个非空唯一索引时，Innodb选择建表时定义的第一个非空唯一索引作为主键。

2. Innodb逻辑存储结构

下面是Innodb存储的整体结构
整体从高往低依次是：
表 —— 段 —— 区 —— 页

1. 表空间

Innodb存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。可以为每个表建立一个独立的表空间，也可以多个表共享一个表空间，一切取决于是否启用了innodb_file_per_table。但是即使启用了该参数，向undo 信息，插入缓冲索引页、系统事务信息、二次写缓冲等信息还是会存放到共享表空间中。

2. 段

常见的段有数据段、索引端、回滚段。
数据段：即为B+树的叶子节点。
索引段：为B+树的非叶子节点。

3. 区

区是由连续页组成的空间，在任何情况下区的大小都是1M，默认情况下存储引擎页大小为64k，也就是说一个区中有63个连续的页。
当然也可以通过参数innodb_page_size来指定页的大小。

注意：Innodb为了节省空间，对于小表或者零碎的undo字段，刚开始是用32页大小的碎片页去存储，只有碎片页用完了，才去申请64个连续的页空间。

4. 页

页是Innodb磁盘管理的最小单位，默认情况下每个页的大小为16k。
其中页又分为很多种，比如

1. 数据页(B-tree Node)
2. undo页
3. 系统页
4. 事务数据页
5. 插入缓冲位图页

5. 行

Innodb存储引擎数据是按照行存放。

3. Innodb行记录格式

Innodb提供两种存储行记录的方式Compact和Redundant格式。
可以通过如下命令查看表的存储格式：

SHOW TABLE STATUS LIKE 'table_name'\G;

   
   

   
   
1

1. Compact和Redundant行记录格式

其中每一行还会包含两列隐藏的元素，事务Id列和回滚指针列。

主要是为了兼容mysql5.0之前版本的页记录格式。

2. compressed和Dynamic行记录格式

Innodb1.0之后开始引入新的文件格式，compressed和Dynamic。

记住char存储的是字符的大小而不是字节的个数

4. InnoDB数据页结构

该部分主要介绍页结构的组成，篇幅过长，本人也没有完全吃透，不在此处班门弄斧了。

5. 约束

Innodb本身提供以下几种约束：

1. Primary key
2. Unique key
3. Foreign key
4. Default
5. NOT NULL

为什么需要约束？
保证实体的完整性。

怎么创建或者查找约束
创建的方式如下：

1. 表建立的时候创建
2. 利用ALTER TABLE 命令来创建约束

CREATE TABLE `orders` (
  `order_num` int(11) NOT NULL AUTO_INCREMENT,
  `order_date` datetime NOT NULL,
  `cust_id` int(11) NOT NULL,
  -- 在创建表的时候设置约束
  PRIMARY KEY (`order_num`)
) ENGINE=InnoDB AUTO_INCREMENT=20010 DEFAULT CHARSET=utf8;

– 方法2
ALTER TABLE orders ADD UNIQUE KEY uk_id_or (order_num);

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

1. 约束和索引的区别

约束是一个逻辑的概念，保证数据实体的唯一性；索引是一个数据结构，既有逻辑的概念，同时在数据库中还代表物理存储的方式。

6. 分区表

分区是将一个大的表分割成一些小的表关于管理。
如何查看当前数据库是否开启了分区呢？

SHOW VARIABLES LIKE '%partition%'\G;

 
 

 
 
1

Mysql数据库支持的分区类型：

1. RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。
2. LIST分区：对于离散值进行分区
3. HASH分区：根据用户自定义的表达式的值来进行分区。
4. KEY分区：根据MYSQL数据库提供的哈希函数进行分区。

如果一个表中存在主键或者唯一索引时，分区列必须是唯一索引的一部分。

1. RANGE分区

用主键或者唯一索引的一部分来区分，如果id < x，则划分为partition1; id > x,则划分为partition2。

用途一般用于日期的分区：比如订单表按照年分区等。

CREATE TABLE t (
	a int
) ENGINE=INNODB
PARITION BY list(b)(
PARITION p0 values less than (10),
PARITION p0 values less than (20)
);

 
 

 
 
1
2
3
4
5
6
7

2. LIST分区

主要针对于离散的元素进行分区。
比如

CREATE TABLE t (
	a int,
	b int
) ENGINE=INNODB
PARITION BY list(b)(
PARITION p0 values in (1,3,5,7,9),
PARITION p0 values in (2,4,6,8),
);

 
 

 
 
1
2
3
4
5
6
7
8

3. HASH分区

系统自动的帮我们计算分到哪个区，只需要定义好相应的函数即可。

CREATE TABLE t_hash(
	a int ,
	b datatime
) engine=Innodb
partition by hash(year(b)),
paritionts 4;

 
 

 
 
1
2
3
4
5
6

分区时，如何处理NULL值呢？
Mysql默认将null值视为小于任何的一个非null值。也就说当做是最小的值。

总结

该部分主要讲解了，数据在表中是如何存储的，内部的结构是如何组成的，以及约束，分区的介绍。个人感觉这部分整体属于一个比较深层次的内容，推荐读者（如果真的有读者看的话）去看看《Mysql技术内幕-Innodb存储引擎》。