电商运营推广的方式和渠道有哪些,su搜索引擎优化,最大的中文搜索引擎,wordpress可以上传文件吗文章目录 第四章 表1 索引组织表2 InnoDB逻辑存储结构2.1 表空间2.2 段2.3 区2.4 页2.5 行2.6 拓展#xff1a;MySQL的varchar(n)能存储几个字符#xff1f;占多少字节#xff1f; 3 InnoDB行记录格式4 文件格式5 约束5.1 数据完整性5.2 约束的创建和查找5.3 约束和索引的区… 文章目录 第四章 表1 索引组织表2 InnoDB逻辑存储结构2.1 表空间2.2 段2.3 区2.4 页2.5 行2.6 拓展MySQL的varchar(n)能存储几个字符占多少字节 3 InnoDB行记录格式4 文件格式5 约束5.1 数据完整性5.2 约束的创建和查找5.3 约束和索引的区别5.4 对错误数据的约束5.5 ENUM和SET约束5.6 触发器与约束5.7 外键约束 6 视图6.1 视图的作用6.2 物化视图 7 分区表7.1 分区概述7.2 分区类型range分区LIST分区Hash分区Key分区Columns分区 7.3 子分区7.4 分区中的null值7.5 分区和性能7.6 表和分区间的数据交换分区表数据导入非分区表非分区表数据导入分区表 第四章 表 本章节从InnoDB存储引擎表的逻辑存储及实现开始进行介绍然后将重点分析表的物理存储特征即数据在表中是如何组织和存放的。简单来说表就是关于特定实体的数据集合这也是关系型数据库模型的核心。
1 索引组织表 在InnoDB存储引擎中表都是根据主键顺序组织存放的这种存储方式的表称为索引组织表(index organized table)。在InnoDB存储引擎表中每张表都有个主键(primary key)如果在创建表时没有显式的定义主键则InnoDB存储引擎会按如下方式选择或创建主键
首先判断表中是否有非空的唯一索引(unique not null)如果有则该列即为主键。如果不符合上述条件InnoDB存储引擎自动创建一个6字节大小的指针。 当表中有多个非空唯一索引时InnoDB存储引擎将选择建表时第一个定义的非空唯一索引作为主键。如下所示
案例一
# 1.创建表uniq字段依次是a,b,c,d
# 索引顺序依次是b,c,d
mysql create table uniq (- a int not null,- b int null,- c int not null,- d int not null,- unique key (b),- unique key (c),- unique key (d)- );
Query OK, 0 rows affected (0.14 sec)# 2.插入数据
mysql insert into uniq values(1,2,3,4),(5,6,7,8),(9,10,11,12);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0# 3._rowid可以显示表的主键
mysql select a,b,c,d,_rowid from uniq;
-------------------------
| a | b | c | d | _rowid |
-------------------------
| 1 | 2 | 3 | 4 | 3 |
| 5 | 6 | 7 | 8 | 7 |
| 9 | 10 | 11 | 12 | 11 |
-------------------------
3 rows in set (0.00 sec)案例二
# 1.创建表uniq2字段依次是a,b,c,d
# 索引依次是b,d,c
mysql create table uniq2 (- a int not null,- b int null,- c int not null,- d int not null,- unique key (b),- unique key (d),- unique key (c)- );
Query OK, 0 rows affected (0.06 sec)# 2.插入数据
mysql insert into uniq2 values(1,2,3,4),(5,6,7,8),(9,10,11,12);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0# 3._rowid可以显示表的主键
mysql select a,b,c,d,_rowid from uniq2;
-------------------------
| a | b | c | d | _rowid |
-------------------------
| 1 | 2 | 3 | 4 | 4 |
| 5 | 6 | 7 | 8 | 8 |
| 9 | 10 | 11 | 12 | 12 |
-------------------------
3 rows in set (0.00 sec)通过案例一和案例二结果对比我们发现案例一的主键是字段c也就是在uniq表中字段c是第一个非空唯一索引字段。案例二的主键是字段d也就是在uniq2表中字段d是第一个非空唯一索引字段。两则建表语句中字段顺序是相同的但是索引的顺序是相同的。所以证实了主键的选择是根据定义索引的顺序而不是建表时列的顺序。 除此之外需要注意的是通过_rowid查看主键的方法只适用于单列为主键的情况比如当多列作为主键时就无法查询。
# 1.创建表uniq3a,b作为联合主键
mysql create table uniq3 (- a int ,- b int ,- primary key (a,b)- ) engineInnoDB;
Query OK, 0 rows affected (0.04 sec)# 2.插入顺序
mysql insert into uniq3 values(1,1),(2,2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0# 3.查询主键失败
mysql select a,b,_rowid from uniq3;
ERROR 1054 (42S22): Unknown column _rowid in field list2 InnoDB逻辑存储结构 从InnoDB存储引擎的逻辑存储结构看所有数据都被逻辑地存放在一个空间中称之为表空间(tablespace)。表空间又由段(segment)、区(extent)、页(page)组成。页在一些文档中有时也称为块(block)InnoDB存储引擎的逻辑存储结构大致如下。 2.1 表空间 表空间可以看做是InnoDB存储引擎逻辑结构的最高层所有的数据都存放在表空间中。在默认情况下InnoDB存储引擎有一个共享表空间ibdata1,即所有数据都存放在这个表空间内。如果用户启用了参数innodb_file_per_table则每张表的数据可以单独放到一个表空间内。 如果启用了innodb_file_per_table的参数需要注意的是每张表的表空间内存放的只是数据、索引和插入缓存Bitmap页其他类的数据如回滚(undo)信息插入缓存索引页、系统事务信息二次写缓存(Double write buffer)等还是存放在原来的共享表空间内。 可以使用py_innodb_page_info小工具来查看表空间中各页的类型和信息。在使用时需要注意的是总共有三个文件分别是include.py、mylib.py、py_innodb_page_info.py放在同一目录即可。
GitHub下载
# 1.python文件
[rootzxy_master py_innodb_page_type]# ll
total 12
-rw-r--r-- 1 root root 1257 Feb 23 10:06 include.py
-rw-r--r-- 1 root root 2179 Feb 23 10:07 mylib.py
-rw-r--r-- 1 root root 234 Feb 23 09:59 py_innodb_page_info.py# 2.表空间统计查看
[rootzxy_master py_innodb_page_type]# python py_innodb_page_info.py /var/lib/mysql/ibdata1
# 总页数
Total number of page: 4864:
# 插入缓存
Insert Buffer Bitmap: 1
# 系统页
System Page: 123
# 事务系统页
Transaction system Page: 2
# 可用页
Freshly Allocated Page: 4326
# Undo页
Undo Log Page: 288
# segment节点页
File Segment inode: 8
# 数据页
B-tree Node: 114
# 表空间标题页
File Space Header: 2# 3.详细表空间内容查看
[rootzxy_master py_innodb_page_type]# python -v py_innodb_page_info.py /var/lib/mysql/ibdata1
......
2.2 段 表空间是由各个段组成常见的段有数据段、索引段、回滚段等。 其中leaf node segment是B树的叶子节点即数据段。Non-leaf node segment是B树的非索引节点即索引段。Roolback segment是回滚段。 在InnoDB引擎中对段的管理都是由引擎自身所完成的DBA不能也没有必要对其进行控制。这和Oracle对数据库中自动段的管理(ASSM)类似从一定程度上简化了DBA对段的管理。 2.3 区 区由连续页组成的空间在任何情况下每个区的大小都为1MB。为保证页的连续性InnoDB存储引擎一次从磁盘申请4-5个区。在默认情况下InnoDB存储引擎的页大小为16KB即一个区共有64个连续的页。 在InnoDB 1.2.x版本新增参数innodb_page_size后通过该参数可以将默认页的大小设置为4K、8K但是页中的数据库不是压缩。这时区中的页的数量通用页为256、128。总之不论页的大小怎么变化区的大小总是1M。
mysql show variables like innodb_page_size;
-------------------------
| Variable_name | Value |
-------------------------
| innodb_page_size | 16384 |
-------------------------
1 row in set (0.00 sec) 当启用参数innodb_file_per_table后创建的表默认是96KB。
这时就引起了思考 一个区有64个页一个页16KB那么创建的表至少应该是1MB才对为什么这里创建的表才96KB。
# 1.为方便测试t2字段类型为varchar(7000)能保证一个页最多存放2条记录
mysql create table page_test(- t1 int not null auto_increment,- t2 varchar(7000),- primary key (t1)- ) engineinnodb;
Query OK, 0 rows affected (0.04 sec)# 2.通过查看表空间文件可以发现初始表空间大小默认96KB
mysql system ls -lh /var/lib/mysql/zxy/page_test.ibd
-rw-r----- 1 mysql mysql 96K Feb 23 11:00 /var/lib/mysql/zxy/page_test.ibd 其实是每个段开始时先用32个页大小的碎片页(fragement page)来存放数据在使用完这些页后才开始64个连续页的申请。这样做的目的是对于一些小表或者是undo这类的段可以在开始申请较少的空间节省磁盘容量的开销。
测试一插入两条数据表空间大小、B树变化
结论1经过测试发现表大小未发生变化
# 1.插入第一条
mysql insert into page_test select null,repeat(a,7000);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0# 2.插入第二条
mysql insert into page_test select null,repeat(a,7000);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0# 3.查看大小
mysql system ls -lh /var/lib/mysql/zxy/page_test.ibd
-rw-r----- 1 mysql mysql 96K Feb 23 11:26 /var/lib/mysql/zxy/page_test.ibd
结论2 通过py_innodb_page_info.py工具参数-v查看表空间详情。可以看到page offset是3的页这个是数据页。page level表示所在索引层0表示叶子节点。因为两条数据未满16KB所以当前所有记录都在一个页中因此没有非叶节点。但是如果再插入一条数据就会产生非叶节点。
[rootzxy_master py_innodb_page_type]# python py_innodb_page_info.py -v /var/lib/mysql/zxy/page_test.ibd
page offset 00000000, page type File Space Header
page offset 00000001, page type Insert Buffer Bitmap
page offset 00000002, page type File Segment inode
page offset 00000003, page type B-tree Node, page level 0000
page offset 00000000, page type Freshly Allocated Page
page offset 00000000, page type Freshly Allocated Page
Total number of page: 6:
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 1
File Segment inode: 1
测试二再次插入一条数据后表空间大小、B树变化
结论1经过测试发现表大小未发生变化
mysql insert into page_test select null,repeat(a,7000);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql system ls -lh /var/lib/mysql/zxy/page_test.ibd
-rw-r----- 1 mysql mysql 96K Feb 23 11:34 /var/lib/mysql/zxy/page_test.ibd
结论2可以看到page offset为3的页的page level由之前的0变为了1这时虽然新增的记录导致了B树的分裂操作但这个页的类型还是B-tree Node。
[rootzxy_master py_innodb_page_type]# python py_innodb_page_info.py -v /var/lib/mysql/zxy/page_test.ibd
page offset 00000000, page type File Space Header
page offset 00000001, page type Insert Buffer Bitmap
page offset 00000002, page type File Segment inode
page offset 00000003, page type B-tree Node, page level 0001
page offset 00000004, page type B-tree Node, page level 0000
page offset 00000005, page type B-tree Node, page level 0000
Total number of page: 6:
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 3
File Segment inode: 1
测试三再次插入60条数据后表空间大小、B树变化
结论1表空间大小变化
mysql DELIMITER mysql create procedure load_t1(count int unsigned)- begin- declare s int unsigned default 1;- declare c varchar(7000) default repeat(a,7000);- while s count DO- insert into page_test select null,c;- set s s1;- end while;- end;-
Query OK, 0 rows affected (0.01 sec)mysql delimiter ;mysql call load_t1(60);
Query OK, 1 row affected (0.67 sec)# 可以看到现在有63条数据后表空间的大小还是小于1MB即表示数据空间的申请还是通过碎片页而不是通过64个连续页的区。
mysql system ls -lh /var/lib/mysql/zxy/page_test.ibd;
-rw-r----- 1 mysql mysql 592K Feb 23 13:50 /var/lib/mysql/zxy/page_test.ibd
结论2 可以观察到B-tree Node页一共有33个除去一个page level为1的非叶节点一共有32个page level为0的页也就是说对于数据段已经有32个碎片页了。之后如果再申请空间肯定就是按照64个页大小进行增长了。
[rootzxy_master py_innodb_page_type]# python py_innodb_page_info.py -v /var/lib/mysql/zxy/page_test.ibd
page offset 00000000, page type File Space Header
page offset 00000001, page type Insert Buffer Bitmap
page offset 00000002, page type File Segment inode
page offset 00000003, page type B-tree Node, page level 0001
page offset 00000004, page type B-tree Node, page level 0000
page offset 00000005, page type B-tree Node, page level 0000
page offset 00000006, page type B-tree Node, page level 0000
page offset 00000007, page type B-tree Node, page level 0000
page offset 00000008, page type B-tree Node, page level 0000
page offset 00000009, page type B-tree Node, page level 0000
page offset 0000000a, page type B-tree Node, page level 0000
page offset 0000000b, page type B-tree Node, page level 0000
page offset 0000000c, page type B-tree Node, page level 0000
page offset 0000000d, page type B-tree Node, page level 0000
page offset 0000000e, page type B-tree Node, page level 0000
page offset 0000000f, page type B-tree Node, page level 0000
page offset 00000010, page type B-tree Node, page level 0000
page offset 00000011, page type B-tree Node, page level 0000
page offset 00000012, page type B-tree Node, page level 0000
page offset 00000013, page type B-tree Node, page level 0000
page offset 00000014, page type B-tree Node, page level 0000
page offset 00000015, page type B-tree Node, page level 0000
page offset 00000016, page type B-tree Node, page level 0000
page offset 00000017, page type B-tree Node, page level 0000
page offset 00000018, page type B-tree Node, page level 0000
page offset 00000019, page type B-tree Node, page level 0000
page offset 0000001a, page type B-tree Node, page level 0000
page offset 0000001b, page type B-tree Node, page level 0000
page offset 0000001c, page type B-tree Node, page level 0000
page offset 0000001d, page type B-tree Node, page level 0000
page offset 0000001e, page type B-tree Node, page level 0000
page offset 0000001f, page type B-tree Node, page level 0000
page offset 00000020, page type B-tree Node, page level 0000
page offset 00000021, page type B-tree Node, page level 0000
page offset 00000022, page type B-tree Node, page level 0000
page offset 00000023, page type B-tree Node, page level 0000
page offset 00000000, page type Freshly Allocated Page
Total number of page: 37:
Freshly Allocated Page: 1
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 33
File Segment inode: 1测试四继续插入60条数据后表空间大小、B树变化
结论1表空间大小增加明细
mysql call load_t1(60);
Query OK, 1 row affected (0.68 sec)mysql system ls -lh /var/lib/mysql/zxy/page_test.ibd;
-rw-r----- 1 mysql mysql 9.0M Feb 23 13:59 /var/lib/mysql/zxy/page_test.ibd
结论2出现较多freshly allocated page空闲页。
[rootzxy_master py_innodb_page_type]# python py_innodb_page_info.py -v /var/lib/mysql/zxy/page_test.ibd
.......
Total number of page: 576:
Freshly Allocated Page: 510
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 63
File Segment inode: 1
2.4 页 同大多数数据库一样InnoDB有页(Page)的概念(也可以称为块)页是InnoDB磁盘管理的最小单位。在InnoDB存储引擎中默认每个页的大小为16KB。从InnoDB 1.2.x版本开始可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。若设置完成则所有表中页的大小都为innodb_page_size不可以对其进行再次修改。除非通过mysqldump导入和导出操作产生新的库。
在InnoDB存储引擎中常见的页类型有
数据页B-tree Nodeundo页 undo log Page系统页System Page事务数据页Transcation system Page插入缓存位图页Insert Buffer Bitmap插入缓存空闲列表页Insert Buffer Free List未压缩的二进制大对象页Uncompressed BLOB Page压缩的二进制大对象页compressed BLOB Page
2.5 行 InnoDB存储引擎是面向列的也就是说数据是按行存放的。每个页存放的行记录也是有硬性定义的饿最多存放16KB/2-200行的记录。 每页最少存储2行记录用链表连接气力啊否则会失去B树的意义。数据大的行记录如大字符串、TEXT、BLOB对象都是采用行溢出数据存储。不同的行格式、存储方式不同。
2.6 拓展MySQL的varchar(n)能存储几个字符占多少字节
网上各种说法不一只能简单测试一下结果如下
MySQL的varchar(n)无论是英文字符还是中文都可以插入n个字符但是实际存储的大小不同。
当字符集是utf8时一个英文占1个字节一个中文占3个字节。
当字符集是gbk时一个英文占1个字节一个中文占2个字节。
当字符是latin1时一个英文占1个字节。
一、UTF-8
# 1.innodb引擎utf8字符集
mysql create table char_utf8(- t1 int not null auto_increment,- t2 varchar(10) null,- primary key (t1)- ) engineinnodb default charsetutf8;
Query OK, 0 rows affected (0.05 sec)# 2.字段t2插入10个英文字符 成功
mysql insert into char_utf8(t2) values(aaaaaaaaaa);
Query OK, 1 row affected (0.01 sec)# 3.字段t2插入11个英文字符 失败
mysql insert into char_utf8(t2) values(aaaaaaaaaaa);
ERROR 1406 (22001): Data too long for column t2 at row 1# 4.字段t2插入10个中文字符 成功
mysql insert into char_utf8(t2) values(测试测试测试测试测试);
Query OK, 1 row affected (0.01 sec)# 5.字段t2插入11个中文字符 失败
mysql insert into char_utf8(t2) values(测试测试测试测试测试测);
ERROR 1406 (22001): Data too long for column t2 at row 1# 6.一个英文字符占1个字节一个中文字符占3个字节
mysql select t2,length(t2) from char_utf8 ;
--------------------------------------------
| t2 | length(t2) |
--------------------------------------------
| aaaaaaaaaa | 10 |
| 测试测试测试测试测试 | 30 |
--------------------------------------------
2 rows in set (0.00 sec)
二、GBK
# 1.innodb引擎gbk字符集
mysql create table char_gbk(- t1 int not null auto_increment,- t2 varchar(10) null,- primary key (t1)- ) engineinnodb default charsetgbk;
Query OK, 0 rows affected (0.04 sec)# 2.插入10个英文字符 成功
mysql insert into char_gbk(t2) values(aaaaaaaaaa);
Query OK, 1 row affected (0.02 sec)
# 3.插入11个英文字符 失败
mysql insert into char_gbk(t2) values(aaaaaaaaaaa);
ERROR 1406 (22001): Data too long for column t2 at row 1
# 4.插入10个中文字符 成功
mysql insert into char_gbk(t2) values(测试测试测试测试测试);
Query OK, 1 row affected (0.01 sec)
# 5.插入11个中文字符 失败
mysql insert into char_gbk(t2) values(测试测试测试测试测试测);
ERROR 1406 (22001): Data too long for column t2 at row 1
# 6.一个英文字符占1个字节一个中文字符占2个字节
mysql select t2,length(t2) from char_gbk;
--------------------------------------------
| t2 | length(t2) |
--------------------------------------------
| aaaaaaaaaa | 10 |
| 测试测试测试测试测试 | 20 |
--------------------------------------------
2 rows in set (0.00 sec)
三、Latin1
# 1.Innodb引擎Latin1字符集
mysql create table char_latin1(- t1 int not null auto_increment,- t2 varchar(10) null,- primary key (t1)- ) engineinnodb default charsetlatin1;
Query OK, 0 rows affected (0.06 sec)
# 2.插入10个英文字符 成功
mysql insert into char_latin1(t2) values(aaaaaaaaaa);
Query OK, 1 row affected (0.01 sec)
# 3.插入11个英文字符 失败
mysql insert into char_latin1(t2) values(aaaaaaaaaaa);
ERROR 1406 (22001): Data too long for column t2 at row 1
# 4.插入中文字符 失败 不支持
mysql insert into char_latin1(t2) values(测试测试测试测试测试);
ERROR 1366 (HY000): Incorrect string value: \xE6\xB5\x8B\xE8\xAF\x95... for column t2 at row 1
# 5.一个英文字符占1字节
mysql select t2,length(t2) from char_latin1;
------------------------
| t2 | length(t2) |
------------------------
| aaaaaaaaaa | 10 |
------------------------
1 row in set (0.00 sec)
3 InnoDB行记录格式 InnoDB存储引擎和大多数数据库意义(如Oracle和Micrisoft SQL Server数据库)记录是以行的形式存储的。这意味着页中保存着表中一行行的数据。InnoDB存储引擎设计了4种不同类型的行格式分别是Compact,Redundant,Dynamic,Compressed行格式。 可以通过命令select innodb_default_row_format;查看默认的行格式
mysql select innodb_default_row_format;
-----------------------------
| innodb_default_row_format |
-----------------------------
| dynamic |
-----------------------------
1 row in set (0.00 sec) 也可以查看当前某表的行格式
# 可以看到ROW_format行是Dynamic
mysql show table status like zxy\G;
*************************** 1. row ***************************Name: zxyEngine: InnoDBVersion: 10Row_format: DynamicRows: 12Avg_row_length: 1365Data_length: 16384
Max_data_length: 0Index_length: 0Data_free: 0Auto_increment: NULLCreate_time: 2022-10-08 16:51:21Update_time: 2023-02-08 09:55:59Check_time: NULLCollation: latin1_swedish_ciChecksum: NULLCreate_options:Comment:
1 row in set (0.00 sec) 也可以修改表的行格式
# 1.将表的行格式修改为compact
mysql alter table zxy row_formatcompact;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0# 2.查看zxy表的存储格式ROW_format是Compact
mysql show table status like zxy\G;
*************************** 1. row ***************************Name: zxyEngine: InnoDBVersion: 10Row_format: CompactRows: 12Avg_row_length: 1365Data_length: 16384
Max_data_length: 0Index_length: 0Data_free: 0Auto_increment: NULLCreate_time: 2023-02-24 09:43:32Update_time: NULLCheck_time: NULLCollation: latin1_swedish_ciChecksum: NULLCreate_options: row_formatCOMPACTComment:
1 row in set (0.00 sec)ERROR:
No query specified
4 文件格式 参数inodb_file_format用来指定文件格式可以通过下面的方式来查看当前所使用的InnoDB存储引擎的文件格式。
mysql select version\G;
*************************** 1. row ***************************
version: 5.7.38-log
1 row in set (0.00 sec)ERROR:
No query specifiedmysql show variables like innodb_version\G;
*************************** 1. row ***************************
Variable_name: innodb_versionValue: 5.7.38
1 row in set (0.00 sec)ERROR:
No query specifiedmysql show variables like innodb_file_format\G;
*************************** 1. row ***************************
Variable_name: innodb_file_formatValue: Barracuda
1 row in set (0.01 sec)ERROR:
No query specified 参数innodb_file_format_check用来检测当前InnoDB存储引擎文件格式的支持度该值默认为ON如果出现不支持的文件格式用户可能在错误日志文件中看到类似如下错误
# 1.查看支持度
mysql show variables like innodb_file_format_check;
---------------------------------
| Variable_name | Value |
---------------------------------
| innodb_file_format_check | ON |
---------------------------------
1 row in set (0.00 sec)# 2.错误信息
Innodb: Warning: the system tablespace is in a file format
that this version doesnt support.
5 约束
5.1 数据完整性 关系型数据库系统和文件系统的一个不同点是关系数据库本身能保证存储数据的完整性不需要应用程序的控制而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供约束(constraint)机制该机制提供了一条强大而简易的途径来保证数据库中数据的完整性。一般来说数据完整性有以下三种形式:
**1.实体完整性**保证表中有一个主键。在InnoDB存储引擎表中用户可以通过定义Primary Key和Unique Key约束来保证实体的完整性。用户还可以通过编写一个触发器来保证数据完整性。**2.域完整性**保证数据每列的值满足特定的条件。在InnoDB存储引擎表中域完整性可以通过以下几种途径保证。 选择合适的数据类型确保一个数据值满足特定条件外键(Foreign Key)约束编写触发器还可以考虑用default约束作为强制域完整性的一个方面 **3.参照完整性**保证两张表之间的关系。InnoDB存储引擎支持外键因此允许用户定义外键以强制参照完整性也可以通过编写触发器以强制执行。
对于InnoDB引擎本身提供了以下几种约束
Primary KeyUnique KeyForegin KeyDefaultNOT NULL
5.2 约束的创建和查找
约束的创建可以采用以下两种方式
表建立时就进行约束定义利用ALTER TABLE命令进行创建约束 对于Unique Key(唯一索引)的约束用户还可以通过命令create unique index来建立。
Primary Key
## 1.创建表id是Primary Key
mysql create table constraint_test(- id int,- primary key (id)- ) engineinnodb;
Query OK, 0 rows affected (0.05 sec)## 2.查询元数据信息可以发现
## Primary Key的约束名是Primary
mysql select constraint_name,constraint_type- from information_schema.table_constraints- where table_schemazxy and table_nameconstraint_test\G;
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY
Unique Key 方式一alter table...add unique key... 添加 # 1.添加字段name
mysql alter table constraint_test add column name varchar(25);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 2.对name添加unique key并指定约束名为uk_name
mysql alter table constraint_test add unique key uk_name (name);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 3.查询元数据
mysql select constraint_name,constraint_type- from information_schema.table_constraints- where table_schemazxy and table_nameconstraint_test\G;
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY
*************************** 2. row ***************************
constraint_name: uk_name
constraint_type: UNIQUE 方式二create unique index ...添加 mysql alter table constraint_test add column sex varchar(25) ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql create unique index un_sex on constraint_test(sex);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql select constraint_name,constraint_type- from information_schema.table_constraints- where table_schemazxy and table_nameconstraint_test\G;
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY
*************************** 2. row ***************************
constraint_name: uk_name
constraint_type: UNIQUE
*************************** 3. row ***************************
constraint_name: un_sex
constraint_type: UNIQUE
3 rows in set (0.00 sec)Foregin Key
## 1.创建外键表constraint_foreign
# id为primary key
# test_id为constraint_test的外键
mysql create table constraint_foreign(- id int ,- test_id int ,- name varchar(25) ,- primary key (id),- foreign key (test_id) references constraint_test(id)- );# 2.查看元数据信息
mysql select constraint_name,constraint_type- from information_schema.table_constraints- where table_schemazxy and table_nameconstraint_foreign\G;
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY
*************************** 2. row ***************************
constraint_name: constraint_foreign_ibfk_1
constraint_type: FOREIGN KEY
2 rows in set (0.00 sec)# 3.用户还可以通过information_constraints查看表的外键属性
mysql select *- from information_schema.referential_constraints- where constraint_schemazxy and table_name constraint_foreign\G;
*************************** 1. row ***************************CONSTRAINT_CATALOG: defCONSTRAINT_SCHEMA: zxyCONSTRAINT_NAME: constraint_foreign_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: defUNIQUE_CONSTRAINT_SCHEMA: zxyUNIQUE_CONSTRAINT_NAME: PRIMARYMATCH_OPTION: NONEUPDATE_RULE: RESTRICTDELETE_RULE: RESTRICTTABLE_NAME: constraint_foreignREFERENCED_TABLE_NAME: constraint_test
1 row in set (0.00 sec)
5.3 约束和索引的区别 我们可以看到在使用Primary Key和Unique Key的时候就是正常创建索引的方法。那么约束和索引有什么区别呢 用户创建一个唯一索引就是创建了一个唯一的约束。但是约束和索引的概念有所不同约束更像是一个逻辑的概念用户保证数据的完整性而索引是一个数据结构既有逻辑上的概念在数据库中还代表着物理存储的方式。
5.4 对错误数据的约束 在某些默认设置下MySQL数据库允许非法的或不正确的数据的插入或更新又或者可以在数据库内部将其转化为一个合法的值。比如向NOT NULL的字段插入一个NULL值MySQL数据库会将其更改为0再进行插入因此数据库本身没有对数据的正确性进行约束。 但是在插入非法的数据或不正确的数据的时候会根据数据库的sql_mode来判断是报错还是警告。
# 1.查看sql_mode类型
mysql select sql_mode\G;
*************************** 1. row ***************************
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)# 2.查看当前会话的sql_mode类型
mysql select session.sql_mode\G;
*************************** 1. row ***************************
session.sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)# 3.查看全局的sql_mode类型
mysql select global.sql_mode\G;
*************************** 1. row ***************************
global.sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec) 此时设置有sql_mode因此插入非法或不正确的数据会直接报错
mysql create table constraint_null (- id int not null,- date date not null- );
Query OK, 0 rows affected (0.04 sec)mysql insert into constraint_null select null,2023-02-30;
ERROR 1048 (23000): Column id cannot be null 设置sql_mode为空此时向NOT NULL的列插入了一个NULL值同时向date列插入一个不合法的日期。这时候就没有报错信息只是显示了警告(warning)。通过指令show warnings可以查看警告的信息。
mysql set session sql_mode ;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql insert into constraint_null select null,2023-02-30;
Query OK, 1 row affected, 2 warnings (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 2mysql show warnings\G;
*************************** 1. row ***************************Level: WarningCode: 1048
Message: Column id cannot be null
*************************** 2. row ***************************Level: WarningCode: 1264
Message: Out of range value for column date at row 1
2 rows in set (0.00 sec)mysql select * from constraint_null;
----------------
| id | date |
----------------
| 0 | 0000-00-00 |
----------------
1 row in set (0.00 sec)
5.5 ENUM和SET约束 MySQL数据库不支持传统的CHECK约束但是通过ENUM和SET类型可以解决部分这样的约束需求。例如表上有一个性别类型规定域的范围只能是male或female在这种情况下用户可以通过ENUM类型来进行约束。
mysql create table enum_test (- id int ,- sex enum(male,female)- );
Query OK, 0 rows affected (0.02 sec)mysql insert into enum_test values(1,male),(2,female);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql insert into enum_test values(1,test);
ERROR 1265 (01000): Data truncated for column sex at row 1 如上所示对非法的输入值进行了约束但是只限于对离散数值的约束对于传统check约束支持的连续值的范围或更复杂的约束ENUM和SET类型还是无能为力这时用户需要通过触发器来实现对于值域的约束。
5.6 触发器与约束 触发器的作用是在执行INSERT、UPDATE、DELETE命令前后自动调用SQL命令或存储过程。 触发器的创建命令是CREATE TRIGGER只有具备Super权限的MySQL数据库用户才可以执行这条命令。
CREATE
[DEFINER {user | current_user}]
TRIGGER trigger_name DEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name FOR EACH ROW 最多可以为一个表建立6个触发器即分别是INSERT、UPDATE、DELETE的BEFORE和AFTER各定义一个。BEFORE和AFTER代表触发器发生的时间表示在每行操作的之前还是之后发生。MySQL只支持行级触发器for each row,不支持语句级触发器for each statement。 通过触发器用户可以实现MySQL数据库本身并不支持的一些特性比如对于传统check约束的支持物化视图、高级复制、审计等特性。这里先关注触发器对于约束的支持。
案例 加入有张用户消费表每次用户购买一样物品后其金额都是减的若这时残生了负值的操作这样用户的金额不减反增
mysql create table usercash(- userid int not null,- cash int unsigned not null- );
Query OK, 0 rows affected (0.02 sec)mysql insert into usercash values(1,1000);
Query OK, 1 row affected (0.01 sec)mysql update usercash- set cashcash-(-20)- where userid1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from usercash;
--------------
| userid | cash |
--------------
| 1 | 1020 |
--------------
1 row in set (0.00 sec) 如上所示SQL语句对于数据库来说是没有任何问题的可以正常的运行不会报错。但是从业务逻辑上讲这是绝对错误的。所以可以通过触发器来约束这个行为
# 1.创建存储错误业务数据表
mysql 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- );
Query OK, 0 rows affected (0.02 sec)# 2.创建触发器
mysql delimiter
mysql create trigger trigger_user_cash_updatebefore before update- on usercash- 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;-
Query OK, 0 rows affected (0.01 sec)
mysql delimiter ;# 3.业务数据负数情况
mysql update usercash- set cash cash - (-20)- where userid 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0# 4.查看错误业务数据是否影响业务
mysql select * from usercash;
--------------
| userid | cash |
--------------
| 1 | 1020 |
--------------
1 row in set (0.00 sec)# 5.查看错误日志表
mysql select * from usercash_err_log;
-----------------------------------------------------------------
| userid | old_cash | new_cash | user | time |
-----------------------------------------------------------------
| 1 | 1020 | 1040 | rootlocalhost | 2023-03-09 16:44:36 |
-----------------------------------------------------------------
1 row in set (0.00 sec) 可以看到对于异常的数据更新通过触发器将其保存到usercash_err_log。此外该触发器还记录了操作该SQL语句的用户及时间。通过上述例子可以发现创建触发器也是实现约束的一种手段和方法。
5.7 外键约束 外键用来保证参照完整性MySQL数据库的MyISAM存储引擎本身并不支持外键对于外键的定义只是起到一个注释的作用。而InnoDB存储引擎则完整支持外键约束。
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...)
REFERENCES tb1_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
referece_optio:
RESTRICT | CASCADE |SET NULL|NO ACTION 用户可以在执行CREATE TABLE时就添加外键也可以在表创建后ALTER TABLE命令来添加。一个简单的外键的创建示例如下
mysql create table parent(- id int not null,- primary key (id)- ) engineinnodb;
Query OK, 0 rows affected (0.02 sec)mysql create table child(- id int ,- parent_id int,- foreign key (parent_id) references parent(id)- ) engineinnodb;
Query OK, 0 rows affected (0.02 sec) 一般来说称被引用的表为父表引用的表为子表。外键定义时的ON DELETE和ON UPDATE表示在对父表进行DELETE和UPDATE操作时对子表所做的操作可定义的子表操作有 CASCADE CASCADE表示当父表发生DELETE或UPDATE操作时对相应的子表中数据进行DELETE或UPDATE操作。 SET NULL SET NULL表示当父表发生DELETE或UPDATE操作时相应的子表中的数据被更新为NULL值。 NO ACTION NO ACTION表示当父表发生DELETE或UPDATE操作时抛出错误不允许这类操作发生 RESTRICT RESTRICT表示当父表发生DELETE或UPDATE操作时抛出错误不允许这类操作发生。 如果定义外键没有指定ON DELETE或ON UPDATERESTRICT就是默认的外键设置。 在其他数据库中如Oracle数据库有一种称为延时检查(deferred check)的外键约束即检查在SQL语句运行完成后再进行。而目前MySQL数据库的外键约束都是即时检查(immediate check)因此从上面的定义可以看出在MySQL数据库中NO ACTION和RESTRICT功能都是相同的。 在Oracle数据库中对于建立外键的列一定不要忘记给这个列加上一个索引。而InnoDB存储引擎在外键建立时会自动对该列加一个索引这和SQL Server数据库做法一样。因此可以很好的避免外键列上无索引而导致死锁的问题。例如上述的例子表child创建时只定义了外键并没有手动指定parent_id列为索引但是通过命令show create table可以发现InnoDB引擎自动为外键约束的列parent_id添加了索引
mysql show create table child\G;
*************************** 1. row ***************************Table: child
Create Table: CREATE TABLE child (id int(11) DEFAULT NULL,parent_id int(11) DEFAULT NULL,KEY parent_id (parent_id),CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id)
) ENGINEInnoDB DEFAULT CHARSETutf8
1 row in set (0.00 sec) 对于参照完整性约束外键能起到一个非常好的作用。但是对数据的导入操作时外键往往导致在外键约束的检查上花费大量时间。因为MySQL数据库的外键是即时检查的所以对导入的每一行都会进行外键检查。但是在导入过程中忽视外键的检查如
mysql set foreign_key_checks 0;
Query OK, 0 rows affected (0.00 sec)
...
加载数据
...
mysql set foreign_key_checks 1;
Query OK, 0 rows affected (0.00 sec)
6 视图 在MySQL数据库中视图是作为一个虚表存在是由一个SQL查询定义的可以当作表使用。与持久表不同的是视图中的数据没有实际的物理存储。
6.1 视图的作用 视图在数据库中可以被作为一个抽象的装置特别是对于一些应用程序程序本身是不需要关注基础表的结构只需要按照视图定义来取数据或更新数据。 视图虽然是基于基础表的虚拟表但是用户可以对某些视图进行更新操作其本质还是通过更新视图来更新基础表。 在创建视图时如果添加with check option则当不满足修改条件时修改视图会报错。
# 1.查询基础表
mysql select * from zxy;
------------------------
| id | name | testtime |
------------------------
| 1 | zxy | 2023-03-10 |
| 2 | zxy | 2023-03-11 |
------------------------
2 rows in set (0.00 sec)# 2.创建视图with check option;
mysql create view v_zxy as select * from zxy with check option;
Query OK, 0 rows affected (0.00 sec)# 3.修改视图
mysql update v_zxy set nameZXY where id 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0# 4.检查视图修改结果
mysql select * from v_zxy;
------------------------
| id | name | testtime |
------------------------
| 1 | ZXY | 2023-03-10 |
| 2 | zxy | 2023-03-11 |
------------------------
2 rows in set (0.00 sec)# 5.检查基础表修改结果
mysql select * from zxy;
------------------------
| id | name | testtime |
------------------------
| 1 | ZXY | 2023-03-10 |
| 2 | zxy | 2023-03-11 |
------------------------
2 rows in set (0.00 sec)
如果想要查看基础表和视图的元数据信息可以访问information_schema下的TABLES和VIEWS表 TABLES select *
from information_schema.TABLES
where table_typeBASE TABLE
and table_schemadatabase()
and table_namezxy\G;mysql select *- from information_schema.TABLES- where table_typeBASE TABLE- and table_schemadatabase()- and table_namezxy\G;
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: zxyTABLE_NAME: zxyTABLE_TYPE: BASE TABLEENGINE: InnoDBVERSION: 10ROW_FORMAT: CompactTABLE_ROWS: 3AVG_ROW_LENGTH: 5461DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0INDEX_LENGTH: 0DATA_FREE: 0AUTO_INCREMENT: NULLCREATE_TIME: 2023-02-26 15:21:16UPDATE_TIME: 2023-03-13 16:52:22CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ciCHECKSUM: NULLCREATE_OPTIONS: row_formatCOMPACTTABLE_COMMENT:
1 row in set (0.00 sec) VIEWS select *
from information_schema.VIEWS
where table_schemadatabase()
and table_namev_zxy\G;mysql select *- from information_schema.VIEWS- where table_schemadatabase()- and table_namev_zxy\G;
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: zxyTABLE_NAME: v_zxyVIEW_DEFINITION: select zxy.zxy.id AS id,zxy.zxy.name AS name,zxy.zxy.testtime AS testtime from zxy.zxyCHECK_OPTION: CASCADEDIS_UPDATABLE: YESDEFINER: root%SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.00 sec)
6.2 物化视图 有些数据库支持物化视图比如Oracle、postgres。物化视图不是虚表而是基于基础表实际存在的实表。物化视图可以预先计算并保存多表连接的SQL结果。
在Oracle数据库中物化视图的创建方式包括以下两种 build immediate 默认的方式在创建物化视图时就生成数据 build deferred 创建物化视图时不生成数据以后根据需求再生成
物化视图的刷新的条件是当基础表发生了DML操作后物化视图何时采用何种方式进行同步更新刷新的模式有两种 on demand 用户需要的时候刷新 on commit 基础表DML提交的同时刷新
刷新的方法有四种 fast fast刷新采用增量刷新只刷新自上次刷新后进行的修改 complete 对物化视图完全刷新 force 数据后在刷新时判断是否可以进行快速刷新如果可以则采用fast否则采用complete方式 never 物化视图永不刷新
MySQL只有虚表视图如果想实现物化视图的功能。全量刷新的话可以创建实表作为物化视图通过写存储过程定时刷新。增量刷新的话需要利用触发器记录基础表的更改操作然后进行增量刷新。
7 分区表
7.1 分区概述 分区功能不是在存储引擎层完成的常见的InnoDB、MyISAM、NDB都支持CSV、MERGE、FEDORATED这些不支持。在使用分区功能之前应了解当前选择的存储引擎对分区功能的支持怎么样。 MySQL5.1开始支持分区。分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言从逻辑上来讲只有一个表或一个索引但是在物理上这个表可能由数十个物理分区组成。每个分区都是独立的对象可以独自处理也可以作为 一个更大对象的一部分处理。 MySQL支持水平分区不支持垂直分区。此外MySQL数据库分区是局部分区索引一个分区既存放数据又存放索引。 tips: 水平分区将同一个表中不同行分配到不同物理文件中 垂直分区将同一个表不同列的记录分配到不同物理文件中 局部分区一个分区既存放数据又存放索引 全局分区数据存放在各个分区中但是所有数据的索引放在一个对象中 MySQL数据库支持以下类型的分区
range分区行数据基于属于一个给定连续区间的列值被放入分区。list分区和range分区类型只是list分区面向的是离散的值。hash分区根据用户自定义的表达式的返回值来分区返回值不能为负数。key分区根据MySQL数据库提供的哈希函数进行分区
不论创建何种类型的分区如果表中存在主键或唯一索引时分区列必须是唯一索引的一个组成部分。
7.2 分区类型
range分区
1.创建分区
mysql create table sales_p(- money int unsigned not null,- date datetime- ) engineinnodb- partition by range(year(date)) (- partition p2020 values less than (2021),- partition p2021 values less than (2022),- partition pothers values less than maxvalue- );
Query OK, 0 rows affected (0.06 sec)
2.插入数据测试
mysql insert into sales_p values(100,2020-01-01),(200,2021-01-01),(300,2022-01-01);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
3.查看所有分区
mysql select * from information_schema.partitions- where table_schemadatabase() and table_namesales_p\G;
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: zxyTABLE_NAME: sales_pPARTITION_NAME: p2020SUBPARTITION_NAME: NULLPARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULLPARTITION_METHOD: RANGESUBPARTITION_METHOD: NULLPARTITION_EXPRESSION: year(date)SUBPARTITION_EXPRESSION: NULLPARTITION_DESCRIPTION: 2021TABLE_ROWS: 1AVG_ROW_LENGTH: 16384DATA_LENGTH: 16384MAX_DATA_LENGTH: NULLINDEX_LENGTH: 0DATA_FREE: 0CREATE_TIME: 2023-03-14 10:15:57UPDATE_TIME: 2023-03-14 10:17:01CHECK_TIME: NULLCHECKSUM: NULLPARTITION_COMMENT:NODEGROUP: defaultTABLESPACE_NAME: NULL
*************************** 2. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: zxyTABLE_NAME: sales_pPARTITION_NAME: p2021SUBPARTITION_NAME: NULLPARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULLPARTITION_METHOD: RANGESUBPARTITION_METHOD: NULLPARTITION_EXPRESSION: year(date)SUBPARTITION_EXPRESSION: NULLPARTITION_DESCRIPTION: 2022TABLE_ROWS: 1AVG_ROW_LENGTH: 16384DATA_LENGTH: 16384MAX_DATA_LENGTH: NULLINDEX_LENGTH: 0DATA_FREE: 0CREATE_TIME: 2023-03-14 10:15:57UPDATE_TIME: 2023-03-14 10:17:01CHECK_TIME: NULLCHECKSUM: NULLPARTITION_COMMENT:NODEGROUP: defaultTABLESPACE_NAME: NULL
*************************** 3. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: zxyTABLE_NAME: sales_pPARTITION_NAME: pothersSUBPARTITION_NAME: NULLPARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: NULLPARTITION_METHOD: RANGESUBPARTITION_METHOD: NULLPARTITION_EXPRESSION: year(date)SUBPARTITION_EXPRESSION: NULLPARTITION_DESCRIPTION: MAXVALUETABLE_ROWS: 1AVG_ROW_LENGTH: 16384DATA_LENGTH: 16384MAX_DATA_LENGTH: NULLINDEX_LENGTH: 0DATA_FREE: 0CREATE_TIME: 2023-03-14 10:15:57UPDATE_TIME: 2023-03-14 10:17:01CHECK_TIME: NULLCHECKSUM: NULLPARTITION_COMMENT:NODEGROUP: defaultTABLESPACE_NAME: NULL
3 rows in set (0.00 sec)
4.查询测试
# 1.因为是按照日期分区的所有按照money去查询的话还是会查询所有的分区数据
mysql explain partitions select * from sales_p where money 100\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: sales_ppartitions: p2020,p2021,potherstype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 3filtered: 33.33Extra: Using where
1 row in set, 2 warnings (0.00 sec)ERROR:
No query specified# 2.当根据日期查询的时候自动会去查询p2021分区的数据提高查询速度
mysql explain partitions select * from sales_p where date 2021-01-01\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: sales_ppartitions: p2021type: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 1filtered: 100.00Extra: Using where
1 row in set, 2 warnings (0.00 sec)ERROR:
No query specified5.ibd文件
# 使用分区后ibd文件也分为了如下几个
[rootzxy zxy]# ls | grep sales_p
sales_p.frm
sales_p#P#p2020.ibd
sales_p#P#p2021.ibd
sales_p#P#pothers.ibd
6.注意事项
对于Range分区的查询优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()、UNIX_TIMESTAMP()这类函数进行优化选择
LIST分区
1.创建分区
mysql create table list_p(- a int,- b int- )engineinnodb- partition by list(b) (- partition p0 values in (0,2,4,6,8),- partition p1 values in (1,3,5,7,9)- );
Query OK, 0 rows affected (0.03 sec)
2.插入数据
mysql insert into list_p values(1,1),(2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
3.查询所有分区
mysql select table_name,partition_name,table_rows- from information_schema.partitions- where table_namelist_p and table_schemadatabase()\G;
*************************** 1. row ***************************table_name: list_p
partition_name: p0table_rows: 1
*************************** 2. row ***************************table_name: list_p
partition_name: p1table_rows: 1
2 rows in set (0.00 sec)
4.异常测试
# 因为innodb引擎是事务的所以一旦有一个任务失败为保持事务一致会回滚。
mysql insert into list_p values(3,3),(4,4),(10,10),(5,5);
ERROR 1526 (HY000): Table has no partition for value 10mysql select * from list_p;
------------
| a | b |
------------
| 2 | 2 |
| 1 | 1 |
------------
2 rows in set (0.00 sec)
5.MyISAM异常测试
# 1.创建myisam分区表
mysql create table list_myisam_p(- a int,- b int- ) enginemyisam- partition by list(b) (- partition p0 values in (0,2,4,6,8),- partition p1 values in (1,3,5,7,9)- );
Query OK, 0 rows affected, 1 warning (0.01 sec)# 2.模拟插入数据
mysql insert into list_myisam_p values(3,3),(4,4),(10,10),(5,5);
ERROR 1526 (HY000): Table has no partition for value 10# 3.可以发现就算其中一条插入失败了之前的数据还是能正常插入表中
mysql select * from list_myisam_p;
------------
| a | b |
------------
| 4 | 4 |
| 3 | 3 |
------------
2 rows in set (0.01 sec)
Hash分区 hash分区的目的是将数据均匀的分布到预先定义的各个分区中保证各分区的数据量大致一样。在range和list分区中必须明确指定一个给定的列值或列值集合应该保存在哪个分区。而Hash分区MySQL会自动完成这些工作用户所要做的只是基于将要进行哈希分区的列值指定一个列值或表达式以及指定被分区的表将要被分割的分区数量。 创建Hash分区可以在创建表的时候添加partition by hash(expr)子句其中expr可以是字段的列明。如果需要指定有多少个分区可以使用partitions num来表示要分割成分区的数量。如果不指定的话默认分区数量是1。 1.Hash分区 MOD(data,num),data是分区的依据值num是分区的数量 创建hash分区 mysql create table hash_p(- a int ,- b datetime- )engineinnodb- partition by hash (year(b))- partitions 4;
Query OK, 0 rows affected (0.07 sec) 插入数据 mysql insert into hash_p values(1,2022-01-01);
Query OK, 1 row affected (0.01 sec) 预测分区 使用计算规则MOD(year(‘2022-01-01’),4) MOD(2022,4)2 通过计算可知该条数据应该会被分配到p2分区 查看分区 通过元数据结果查询可知该数据被分配到p2分区 mysql select table_name,partition_name,table_rows- from information_schema.partitions- where table_schemadatabase() and table_namehash_p\G;
*************************** 1. row ***************************table_name: hash_p
partition_name: p0table_rows: 0
*************************** 2. row ***************************table_name: hash_p
partition_name: p1table_rows: 0
*************************** 3. row ***************************table_name: hash_p
partition_name: p2table_rows: 1
*************************** 4. row ***************************table_name: hash_p
partition_name: p3table_rows: 0
4 rows in set (0.00 sec) 2.linear hash分区 V power(2,ceiling(log(2,um))) N data(V-1) linear hash分区的优点在于增加、删除、合并和拆分分区将变得更加快捷有利于处理含有大量数据的表 创建分区 mysql create table linear_hash_p(- a int,- b datetime- ) engineinnodb- partition by linear hash (year(b))- partitions 4;
Query OK, 0 rows affected (0.07 sec) 插入数据 mysql insert into linear_hash_p values(1,2022-01-01);
Query OK, 1 row affected (0.01 sec) 预测分区 V power(2,ceiling(log(2,4))) power(2,2) 4 N year(2022-01-01)(V-1) 2022 3 2 所有该数据应该被分配到p2分区 查看分区 mysql select table_name,partition_name,table_rows- from information_schema.partitions- where table_schemadatabase() and table_namelinear_hash_p\G;
*************************** 1. row ***************************table_name: linear_hash_p
partition_name: p0table_rows: 0
*************************** 2. row ***************************table_name: linear_hash_p
partition_name: p1table_rows: 0
*************************** 3. row ***************************table_name: linear_hash_p
partition_name: p2table_rows: 1
*************************** 4. row ***************************table_name: linear_hash_p
partition_name: p3table_rows: 0
4 rows in set (0.00 sec)
Key分区 key分区和hash分区相似不同之处在于hash分区使用用户定义的函数进行分区key分区使用MySQL数据库提供的函数进行分区。对于NDB Cluster引擎MySQL数据库使用MD5函数来分区。对于其他存储引擎MySQL数据库使用其内部的哈希函数这些函数基于与pasword()一样的运算法则。如
mysql create table key_p(- a int,- b datetime- ) engineinnodb- partition by key (b)- partitions 4;
Query OK, 0 rows affected (0.07 sec)
Columns分区 range、list、hash和key这四种分区分区的条件是数据必须是整型如果不是整型那么应该转换为整型如year(),to_days(),month()等函数。columns分区可以视为是range分区和list分区的进化。columns分区可以直接使用非整型的数据进行分区分区根据类型直接比较而得不需要转换为整型。除此之外range columns可以对多个列值进行分区。
columns分区支持以下数据类型
所有整型类型如int,smallint,tinyint,bigint。 而 float和decimal不支持。日期类型如date和datetime。其余得日期类型不支持字符串类型如char,varhcar,binary,varbinary。blob和text类型不予支持。
所以对于日期类型得分区不需要再使用year(),to_days()这些函数转换了直接可以使用columns。如
# range columns分区
mysql create table range_columns_p (- a int,- b datetime- ) engineinnodb- partition by range columns(b) (- partition p1 values less than (2022-01-01),- partition p2 values less than (2023-01-01)- );
Query OK, 0 rows affected (0.04 sec)# list columns分区
mysql create table list_columns_p (- a int,- b varchar(25)- ) engineinnodb- partition by list columns(b) (- partition p1 values in (hello,world),- partition p2 values in (ni,hao)- )- ;
Query OK, 0 rows affected (0.03 sec)
7.3 子分区 子分区是在分区的基础上再进行分区有时也称这种分区为复合分区。MySQL数据库允许在range和list分区的基础上再进行hash和key分区如
父分区使用range按照年分区子分区使用hash按照天分区
mysql create table sub_p (- a int ,- b date- ) engineinnodb- partition by range(year(b))- subpartition by hash(to_days(b))- subpartitions 2 (- partition p0 values less than (2020),- partition p1 values less than (2021),- partition p2 values less than maxvalue- );
Query OK, 0 rows affected (0.09 sec)
父分区三个子分区两个则共有分区六个
[rootzxy zxy]# ls | grep sub_p
sub_p.frm
sub_p#P#p0#SP#p0sp0.ibd
sub_p#P#p0#SP#p0sp1.ibd
sub_p#P#p1#SP#p1sp0.ibd
sub_p#P#p1#SP#p1sp1.ibd
sub_p#P#p2#SP#p2sp0.ibd
sub_p#P#p2#SP#p2sp1.ibd
子分区还可以写成如下格式不过需要注意的是六个分区的名称必须是唯一的。
create table sub_p2 (
a int,
b date
) engineinnodb
partition by range(year(b))
subpartition by hash(to_days(b)) (partition p0 values less than(2020)(subpartition s0,subpartition s1),partition p1 values less than(2021)(subpartition s2,subpartition s3),partition p2 values less than maxvalue(subpartition s4,subpartition s5)
);
7.4 分区中的null值 MySQL数据库允许null值做分区但是处理方法与其他数据库可能完全不同。MySQL数据库的分区总是视NULL值小于任何一个非NULL的值这和MySQL数据库处理NULL值得order by操作是一样得。因此对于不同得分区类型MySQL数据库对于NULL值得处理也是各不相同。 range分区 如果插入NULL值则放置在最左边的分区。 list分区 如果要插入NULL值需要指定分区存储NULL值 例如partition p0 values in (1,3,5,7,NULL) hash分区 任何分区函数都会将含有NULL的值记录返回为0 key分区 任何分区函数都会将含有NULL的值记录返回为0
7.5 分区和性能 对表设计分区是否会提升性能 首先数据库根据应用主要分为两类一类是OLTP事务处理。一类是OLAP分析处理。不过在实际的应用中也可能存在既有OLTP又有OLAP的情况。 对于OLAP而言通常是数据仓库、数据集市这些往往存储大量的数据那么通过分区确实可以提高查询性能。 然而对于OLTP能否提高性能却不一定。OLTP对应的系统通常是通过索引查询某些数据不会经常的扫描表。而根据B树索引的原理可知对于一张大表一般的B树需要2-3次的磁盘IO。因此即使不在分区的帮助下也能很好的完成操作。相反的是如果没有合理的建立分区也会影响查询的性能。 比如一个storage表有1000万数据我根据id进行hash分区分了10个区。每个区平均有100万数据。如果我根据id进行查询某条数据那么只需要检索对于的分区即可。但是如果我根据其他字段查询某条数据他就会检索所有分区也就会造成20-30次磁盘IO影响查询效率。
7.6 表和分区间的数据交换 MySQL5.6后支持alter table ... exchange partition语法可以进行分区表和非分区表的数据交换。
不过使用这个语法需要满足以下条件
要交换的表和分区表的表结构一样要交换的表不能有分区非分区表中的数据必须在交换分区定义内被交换的表不能有外键或其他表对该表外键的引用用户除了需要alter,insert,create权限外还需要drop权限还需要注意的是 使用该语句不会触发交换表和被交换表上的触发器auto_increment列将被重置
分区表数据导入非分区表 1.创建分区表 mysql create table exchange_p1 (- a int,- b date- ) engineinnodb- partition by range(year(b)) (- partition p0 values less than (2021),- partition p1 values less than (2022),- partition p2 values less than maxvalue- );
Query OK, 0 rows affected (0.06 sec) 2.分区表插入数据 mysql insert into exchange_p1 values(1,2020-01-01),(2,2021-01-01),(3,2022-01-01);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 03.检查各分区数据 mysql select table_name,partition_name,table_rows- from information_schema.partitions- where table_schemadatabase() and table_name exchange_p1\G;
*************************** 1. row ***************************table_name: exchange_p1
partition_name: p0table_rows: 1
*************************** 2. row ***************************table_name: exchange_p1
partition_name: p1table_rows: 1
*************************** 3. row ***************************table_name: exchange_p1
partition_name: p2table_rows: 1
3 rows in set (0.00 sec)4.创建相同结构非分区表 # 1.创建相同结构的表
mysql create table exchange_p2 like exchange_p1;
Query OK, 0 rows affected (0.06 sec)# 2.删除该表上分区
mysql alter table exchange_p2 remove partitioning;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 05.将分区表p2分区数据导入非分区表 # 1.将分区表的p2分区的数据导入到非分区表中
mysql alter table exchange_p1 exchange partition p2 with table exchange_p2;
Query OK, 0 rows affected (0.03 sec)mysql select * from exchange_p2;
------------------
| a | b |
------------------
| 3 | 2022-01-01 |
------------------
1 row in set (0.00 sec)非分区表数据导入分区表 1.创建非分区表 mysql create table exchange_p3 (- a int,- b date- ) engineinnodb;
Query OK, 0 rows affected (0.02 sec)2.插入数据 mysql insert into exchange_p3 values(3,2022-01-01);
Query OK, 1 rows affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql select * from exchange_p3;
------------------
| a | b |
------------------
| 3 | 2022-01-01 |
------------------
1 rows in set (0.00 sec)3.创建分区表 mysql create table exchange_p4 (- a int,- b date- ) engineinnodb- partition by range(year(b)) (- partition p0 values less than (2021),- partition p1 values less than (2022),- partition p2 values less than maxvalue- );
Query OK, 0 rows affected (0.05 sec)4.导入数据到分区表 # 1.导入数据到分区表
mysql alter table exchange_p4 exchange partition p2 with table exchange_p3;
Query OK, 0 rows affected (0.03 sec)# 2.查看分区表数据已经导入成功只有符合分区条件时才能导入成功
mysql select * from exchange_p4;
------------------
| a | b |
------------------
| 3 | 2022-01-01 |
------------------
1 row in set (0.00 sec)# 3.查看分区表分区情况
mysql select table_name,partition_name,table_rows from information_schema.partitions where table_schemadatabase() and table_name exchange_p4\G
*************************** 1. row ***************************table_name: exchange_p4
partition_name: p0table_rows: 0
*************************** 2. row ***************************table_name: exchange_p4
partition_name: p1table_rows: 0
*************************** 3. row ***************************table_name: exchange_p4
partition_name: p2table_rows: 1
3 rows in set (0.00 sec)
