wwstory

[InnoDB 学习3] 表

 
  • 2020年 08月12日

索引组织表

在InnoDB存储引擎中，表是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。每个表都有主键，如果创建表时，没有显式地定义主键，则会如下创建：

  • 首先判断表中是否有非空的唯一索引（Unique NOT NULL）,如果有，则该列即为主键。
  • 若不满足上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。

  • 当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键。（主键的选择是定义索引的顺序，而不是建表时列的顺序）

例：

create table test_selectprimarykey_t(
a int not null,
b int null,
c int not null,
d int not null,
unique key(b),
unique key(d),
unique key(c)
);

insert into test_selectprimarykey_t select 1, 2, 3, 4;
insert into test_selectprimarykey_t select 10, 20, 30, 1018;

select a, b, c, d, _rowid from test_selectprimarykey_t;

如上，尽管b是unique key，但由于是运行为空，不会被选为主键。其次，c虽然在d前面，但是在定义索引的unique key的顺序下选择的，所以d被作为主键。

建表也可以如下方式：

create table test_selectprimarykey_t3(
a int not null,
b int unique null,
c int unique not null,
d int unique not null
);

_rowid不能查看对多列组成的主键。

create table test_multiprimarykey_t(
a int,
b int,
primary key(a, b)
)engine=InnoDB;

innodb逻辑存储结构

所有的数据都被存在一个空间中，称为表空间（tablespace）。

表空间由以下组成：段（segment）、区（extent）、页（page）。（页有时也被成为块（block））

表空间

默认有个共享表空间ibdata1，所有数据都存放在这个表空间内。

如果用户启用了参数innodb_file_per_table（现在默认是启动的），则每张表内的数据可以单独放到一个表空间内。每张表的表空间内存放的只是数据、索引、插入缓冲Bitmap页。其它类的数据，如回滚（undo）、插入缓冲索引页、系统事务、二次写缓冲（double write buffer）等，还是存在原来的共享表空间内。

mysql> show variables like 'innodb_file_per_table';

InnoDB不会在执行rollback时，回收表空间，但会自动判断undo信息是否还需要，如果不需要，将会将这些空间标记为可用空间，供下次undo使用。

段

表空间由各个段组成：数据段、索引段、回滚段等。

InnoDB存储引擎表是索引组成的，因此数据即索引，索引即数据。那么数据段即为B+树的叶子节点（leaf node segment），索引段即为B+树的非叶节点（non-leaf node segment）。

区

区是由连续组成的空间，在任何情况下每个区的大小都为1MB。为了表示区里面的页中的连续性，InnoDB存储引擎一次从磁盘申请4～5个区。

注意：每个段开始时，先用32个页大小的碎片页（fragment page）来存放数据，在使用完这些页后，才是64个连续页的申请。

这样做的目的，对于一些小表，或者undo这类段，可以在开始时申请较少的空间，节省磁盘容量开销。

当开始时所有记录都在一个页中时，没有非叶节点，只有叶子节点，如果数据使用完碎片页后，就会产生一个非叶节点（B+数的分裂操作）。

页

页是InnoDB磁盘管理的最小单位。默认16KB，即64个页。

InnoDB 1.0.x版本引入的压缩页，可设置页大小为4K、8K、16K，对应区的数量为512、256、128。

页类型：

  • 数据页（B-tree Node）
  • undo（undo log Page）
  • 系统页（System Page）
  • 事务数据页（Transaction System Page）
  • 插入缓冲位图页（Insert Buffer Bitmap）
  • 插入缓冲空闲列表页（Insert Buffer Free List）
  • 未压缩的二进制大对象页（Uncompressed BLOB Page）
  • 压缩的二进制大对象页（Compressed BLOB Page）

行

数据是按行进行存放的。

约束

约束的创建可以通过：

  • 表建立时进行约束定义。
  • 利用ALTER TABLE命令进行创建约束。

Primary Key、Unique Key的约束

对错误数据的约束。默认设置下，不允许非法或不正确的数据插入或更新。旧版本可以在数据库内部转化为一个合法的值。

ENUM约束

create table test_enum_t(
id int,
sex ENUM('male', 'female')
);

触发器与约束

对于连续值，范围约束和复杂的约束，需要通过触发器实现。

是在执行INSERT、DELETE、UPDATE命令之前（BEFORE）、之后（AFTER）自动调用SQL命令或存储过程。

例如：插入之前进行负值判断。

create table user_cash_t(
userid int,
cash int unsigned not null
);

insert into user_cash_t select 1, 1000;

update user_cash_t set cash=cash-(-20) where userid=1;

添加新表做日志记录，使用触发器约束。

# 创建表用作触发器的日志记录
create table usercash_err_log(
userid int not null,
old_cash int unsigned not null,
new_cash int unsigned not null,
user varchar(30),
time DATETIME
);

# 添加触发器作约束
delimiter$$
create trigger tgr_usercash_update before update on user_cash_t
for each row
begin
if new.cash-old.cash > 0 then
insert into usercash_err_log select old.userid, old.cash, new.cash, USER(), NOW();
set new.cash=old.cash;
end if;
end;
$$

消费后的钱应该小于原来的值，否则将cash改回原来的值。

外键约束

-- 父表
create table parent_t(
id int primary key);

-- 子表
create table child_t(
id int,
parent_id int,
foreign key(parent_id) references parent_t(id));

外键定义时的ON DELETE和ON UPDATE表示在对父表进行DELETE和UPDATE操作时，对子表所做的操作。

当父表发生DELETE或UPDATE操作时，子表操作有：

  • CASCADE（级联）：对应的子表也进行DELETE或UPDATE操作。
  • SET NULL：子表中的数据被更新为NULL，但子表中对应的列必须允许为NULL。
  • NO ACTION：抛出错误，不允许操作。
  • RESTRICT（限制）：抛出错误，不允许操作。

InnoDB存储引擎会在建立外键时，自动对该列加索引，避免外键列无索引导致的死锁问题。

MySQL的外键是即时检查的，没导入一行都会进行检查。（可以通过设置进行忽视 set foreign_key_checks=0）

视图

与持久表不同的是，视图中的数据没有实际的物理存储。

一定程度上起到安全层的作用。

可以向视图插入数据。

create view v_t as select * from user_t where id<10;

insert into v_t select 100, 'yww';

对于不满足条件的数据也可以插入。

可以通过with check option增加检查，对不满足视图定义条件的，将会抛出异常，不运行更新。

-- with check option
create view v_t as select * from user_t where id<10
with check option;

-- error
insert into v_t select 100, 'yww';

分区表

MySQL数据库支持水平分区，不支持垂直分区。

分区是局部分区索引，即一个分区中既存放了数据，又存放了索引。

目前还不支持全局分区，即数据放各分区，索引放一个对象中。

支持的分区类型：

  • RANGE分区：基于给定的连续区间。
  • LIST分区：想对于RANGE，这只是离散的值。
  • HASH分区：根据用户自定义的表达式的返回值进行分区。（返回值不能为负）
  • KEY分区：根据MySQL数据库提供的哈希函数进行分区。

注： 1.表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

-- error
create table part_t1(
col1 int not null,
col2 date not null,
col3 int not null,
col4 int not null,
unique key(col1, col2)
)
partition by hash(col3)
partitions 4;

2.唯一索引可以允许NULL值。

create table part_t5(
col1 int null,
col2 date null,
col3 int null,
col4 int null,
unique key(col1, col3)
)
partition by hash(col3)
partitions 4;

3.建表时，没有指定主键，唯一索引，可以指定任何一个列为分区列。

create table part_t5(
col1 int null,
col2 date null,
col3 int null,
col4 int null
)
partition by hash(col3)
partitions 4;

例：对时间分区

create table sales(
money int UNSIGNED NOT NULL,
date DATETIME
)engine=innodb
partition by range(YEAR(date))(
partition p2008 values less than(2009),
partition p2009 values less than(2010),
partition p2010 values less than(2011)
);

删除表时，可以直接删除所在分区即可。

alter table sales drop partition p2008;

可以通过添加explain partitions方式在查询时，看出数据来自于那个分区。

explain partitions
select * from sales where date>='2008-01-01' and date<='2008-12-31';

注

约束和索引的区别？

例如Primary Key和Unique Key的约束，这不也是通常创建索引的方法吗？那么约束和索引有什么区别呢？

约束和索引的概念不同，

约束是逻辑的概念，用来保证数据的完整性。

而索引是一个数据结构，没有逻辑上的概念，在数据库中还代表着物理存储的方式。

分区后，查询速度一定会快吗？

数据库应用分为2类：

  • OLTP（在线事务处理），如电子商务、blog、网络游戏等;
  • OLAP（在线分析处理），如数据仓库。

对于OLAP应用，分区的确可以很好的提高查询的性能，避免从一张很大的表里取数据。

然而对于OLTP应用，分区应该非常小心，通常，不可能会获取大表中10%的数据，大部分都是通过索引返回几条记录即可。根据B+树索引的原理可知，对于大表，一般的B+树需要2～3次的磁盘IO。因此B+树可以很好的完成操作，不需要分区的帮助。并且设计不好，会带来严重的性能问题。

例如：对主键进行hash分10个区，进行主键查询时，的确可以增加速度，只在一个分区查询，2～3次IO。而对其它字段查询时，则会在所有分区都查询，会进行20~30次IO操作。

况且如果数据量只有1000w，对于B+树而言，不分区也只是2～3次IO操作。

搜索