[TOC] ## 索引组织表 根据主键顺序存放的这种存储方式的表称为索引组织表。 选择主键或者创建主键： 第一个非空唯一索引（Unique NOT NULL）为主键。如果没有则自动创建一个6字节大小的指针。 ***** ## 逻辑存储结构 InnoDB逻辑存储结构：所有数据都被逻辑地存放在一个空间中,称为表空间。表空间又由段（）、区（extent）、页（page）组成。  ### 表空间 逻辑存储结构的最高层，所有数据保存在表空间中。 InnoDB存储引擎默认有一个表共享空间ibdata1，所有数据放在这个表空间内。 如果启用参数innodb_file_per_table，则每张表数据可以单独放到一个表空间内。 启用之后：数据、索引、插入缓冲Bitmap页存放在表空间。其他类数据，如回滚（undo）信息等放在原来的共享表空间中。 ### 段 表空间由各个段组成，常见的段有数据段、索引段、回滚段等。 ### 区 区有连续页组成的空间，在任何情况下每个区的大小都为1MB。为了页的连续性，InnoDB存储引擎一次申请4~5个区。引擎页的大小为16KB，即一个区中一共有64个连续页。 InnoDB1.2.x 参数innodb_page_size，通过该参数设置默认页的大小4K、8k。 ### 页 页也称为块，页是磁盘管理的最小单位。默认每个页的大小16KB。若innodb_page_size设置完成，不可以再次进行修改，除非通过mysqldump 导入和导出操作来产生新的库。 常见页类型： （1）数据页 （2） undo 页 （3）系统页 （4）事务数据页 （5）插入缓冲位图页 （6）插入缓冲空闲列表页 （7）未压缩的二进制大对象页 （8）压缩的二进制大对象页 ### 行 InnoDB存储引擎是面向列的（row -oriented），也就说数据是按行进行存放的。 ***** ## InnoDB行记录格式 InnoDB记录是以行的形式存储的。由Compact 和 Redundant 两种格式存放记录数据。 ### Compact 行记录格式 一个页面存放的行数据越多,其性能就越高。  由图观察 Compact行记录格式的首部是一个非NULL变长字段长度列表，并且按照列顺序逆序放置的其长度： （1）列长度小于255字节，用1表示； （2）大于255字节用2字节表示； 变长字段的长度最大不能超过2字节。  在Compact 行记录格式NULL不占用存储空间。 ### Redundant 行记录格式 Redundant 是Mysql5.0版本之前的行记录存储方式  redundant的存储格式为 首部是一个字段长度偏移列表（每个字段占用的字节长度及其相应的位移），同样是按照列的顺序逆序放置，当列的长度小于255字节，用1字节表示，若大于 255个字节，用2字节表>示。第二个部分为记录头信息（record header），不同与compact行格式，它的行格式固定占用6个字节，最后的部分就是实际存储的每个列的数据，NULL不占该部分任何数据，但是 char中如果有NULL值则需要占用相应的字节，另外注意,每行数据除了用户定义的列外，还有两个隐藏列，事务ID（6字节），会滚指针列（7字节）， 若INNODB表没有定义,Primay key,那么每行会增加一个6字节的rowid，如果有，怎有4个字节的索引字段。  ### 行溢出数据 VARCHAR最大长度65535字节 但是InnoDB有其他的开开销。在SQL_MODE严格模式实际测试发现VARCHAR类型的最大长度为65532。***这里的65532是字节不是字符` 在utf-8最大的只有21845字节 而在GBK是32767字节。*** 如果没有则会报一个警告：warning则会自动转换成TEXT， 一般情况下，InnoDB存储引擎的数据都是存放在页类型为B-tree node中，但是发送溢出时，数据存放在也类型为Uncompress BLOB页中。 ### Compressed 和 Dynamic 行记录格式 InnoDB 1.0.x开始引入新的文件格式，以前Compact 和 Redundant 格式称为Antelope文件格式，新文件格式称为Barracuda文件格式。Barracuda文件格式拥有两种新的行记录格式：Compressed 和 Dynamic。  Compressed 行记录格式的另一个功能是：存储在其中的行数据会议zlib的算法进行压缩。因此对BLOB、TEXT、VARCHAR这类的大长度数据进行很好的存储. ### CHAR 的行结构存储 CHAR是存储固定长度的字符类型。 MySQL4.1版本开始 CHR（N）中的N指的是字符的长度,在不同的字符集下,CHAR类型列内部存储都可能不是定长的数据。 ***** ## InnoDB 数据页结构 页是InnoDB存储引擎管理数据库的最小磁盘单位。页类型为B-tree node的页，存放的即是表中行的实际数据了。 InnoDB数据页由以下七个部分组成，如图所示： File Header（文件头）。 Page Header（页头）。 Infimun+Supremum Records。 User Records（用户记录，即行记录）。 Free Space（空闲空间）。 Page Directory（页目录）。 File Trailer（文件结尾信息）。 __File Header、Page Header、File Trailer的大小是固定的，用来标示该页的一些信息，如Checksum、数据所在索引层等。__ 其余部分为实际的行记录存储空间，因此大小是动态的。其余部分为实际的行记录存储空间，因此大小是动态的。  ### File Header File Header用来记录页的一些头信息，由如下8个部分组成，共占用38个字节，如表   ### Page Header 接着File Header部分的是Page Header，用来记录数据页的状态信息，由以下14个部分组成，共占用56个字节。  ### Infimun+Supremum Records __在InnoDB存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。Infimum记录是比该页中任何主键值都要小的值，Supremum指比任何可能大的值还要大的值。这两个值在页创建时被建立，并且在任何情况下不会被删除。__ 在Compact行格式和Redundant行格式下，两者占用的字节数各不相同。  ### User Records与FreeSpace User Records即实际存储行记录的内容。再次强调，InnoDB存储引擎表总是B+树索引组织的。 Free Space指的就是空闲空间，同样也是个链表数据结构。当一条记录被删除后，该空间会被加入空闲链表中。 ### Page Directory __Page Directory（页目录）中存放了记录的相对位置（注意，这里存放的是页相对位置，而不是偏移量），有些时候这些记录指针称为Slots（槽）或者目录槽（Directory Slots）。__ 与其他数据库系统不同的是，InnoDB并不是每个记录拥有一个槽，InnoDB存储引擎的槽是一个稀疏目录（sparse directory），即一个槽中可能属于（belong to）多个记录，最少属于4条记录，最多属于8条记录。 Slots中记录按照键顺序存放，这样可以利用二叉查找迅速找到记录的指针。假设我们有（'i'，'d'，'c'，'b'，'e'，'g'，'l'，'h'，'f'，'j'，'k'，'a'），同时假设一个槽中包含4条记录，则Slots中的记录可能是（'a'，'e'，'i'）。 由于InnoDB存储引擎中Slots是稀疏目录，二叉查找的结果只是一个粗略的结果，所以InnoDB必须通过recorder header中的next_record来继续查找相关记录。同时，slots很好地解释了recorder header中的n_owned值的含义，即还有多少记录需要查找，因为这些记录并不包括在slots中。 需要牢记的是，__B+树索引本身并不能找到具体的一条记录，B+树索引能找到只是该记录所在的页。数据库把页载入内存，然后通过Page Directory再进行二叉查找。只不过二叉查找的时间复杂度很低，同时内存中的查找很快，因此通常我们忽略了这部分查找所用的时间。__ ### File Trailer 为了保证页能够完整地写入磁盘（如可能发生的写入过程中磁盘损坏、机器宕机等原因），InnoDB存储引擎的页中设置了File Trailer部分。File Trailer只有一个FIL_PAGE_END_LSN部分，占用8个字节。前4个字节代表该页的checksum值，最后4个字节和File Header中的FIL_PAGE_LSN相同。通过这两个值来和File Header中的FIL_PAGE_SPACE_OR_CHKSUM和FIL_PAGE_LSN值进行比较，看是否一致（checksum的比较需要通过InnoDB的checksum函数来进行比较，不是简单的等值比较），以此来保证页的完整性（not corrupted）。 ## InnoDB数据页结构示例分析 首先我们建立一张表，并导入一定量的数据： ~~~ drop table if exists t; mysql -> create table t (a int unsigned not null auto_increment，b char(10),primary key(a))ENGINE=InnoDB CHARSET=UTF-8; mysql ->delimiter$$ 　　->create procedure load_t(count int unsigned) 　->begin 　　　　->set@c=0; 　　　　　　->while@c＜count do 　　　　　　　　->insert into t select null,repeat(char(97+rand()*26),10); 　　　　　　　　->set@c=@c+1; 　　　　　　->end while; 　　　　->end; ->$$ mysql ->delimiter; mysql ->call load_t(100); mysql ->select * from t limit 10; ~~~ 接着我们用工具`py_innodb_page_info`来分析`t.ibd, py_innodb_page_info.py -v t.ibd`  看到第四个页（page offset 3）是数据页，通过hexdump来分析t.ibd文件，打开整理得到的十六进制文件，数据页在0x0000c000（16K*3=0xc000）处开始： 先来分析前面File Header的38个字节： 52 1b 24 00数据页的Checksum值。 00 00 00 03页的偏移量，从0开始。 ff ff ff ff前一个页，因为只有当前一个数据页，所以这里为0xffffffff。 ff ff ff ff下一个页，因为只有当前一个数据页，所以这里为0xffffffff。 00 00 00 0a 6a e0 ac 93页的LSN。 45 bf页类型，0x45bf代表数据页。 00 00 00 00 00 00 00这里暂时不管该值。 00 00 00 dc表空间的SPACE ID。 先不急着看下面的Page Header部分，我们来看File Trailer部分。因为File Trailer通过比较File Header部分来保证页写入的完整性。 95 ae 5d 39 Checksum值，该值通过checksum函数和File Header部分的checksum值进行比较。 6a e0 ac 93注意到该值和File Header部分页的LSN后4个值相等。 接着我们来分析56个字节的Page Header部分，对于数据页而言，Page Header部分保存了该页中行记录的大量细节信息。分析后可得： Page Header（56 bytes）： PAGE_N_DIR_SLOTS=0x001a PAGE_HEAP_TOP=0x0dc0 PAGE_N_HEAP=0x8066 PAGE_FREE=0x0000 PAGE_GARBAGE=0x0000 PAGE_LAST_INSERT=0x0da5 PAGE_DIRECTION=0x0002 PAGE_N_DIRECTION=0x0063 PAGE_N_RECS=0x0064 PAGE_MAX_TRX_ID=0x0000000000000000 PAGE_LEVEL=00 00 PAGE_INDEX_ID=0x00000000000001ba PAGE_BTR_SEG_LEAF=0x000000dc0000000200f2 PAGE_BTR_SEG_TOP=0x000000dc000000020032 PAGE_N_DIR_SLOTS=0x001a，代表Page Directory有26个槽，每个槽占用2个字节。 我们可以从0x0000ffc4到0x0000fff7找到如下内容： ~~~ 0000ffc0 00 00 00 00 00 70 0d 1d 0c 95 0c 0d 0b 85 0a fd|……p…… 0000ffd0 0a 75 09 ed 09 65 08 dd 08 55 07 cd 07 45 06 bd|.u……e……U……E.. 0000ffe0 06 35 05 ad 05 25 04 9d 04 15 03 8d 03 05 02 7d|.5……%……} 0000fff0 01 f5 01 6d 00 e5 00 63 95 ae 5d 39 6a e0 ac 93|……m……c..]9j…… ~~~ PAGE_HEAP_TOP=0x0dc0代表空闲空间开始位置的偏移量，即0xc000+0x0dc0=0xcdc0处开始，我们观察这个位置的情况，可以发现这的确是最后一行的结束，接下去的部分都是空闲空间了： ~~~ 0000cdb0 00 00 00 2d 01 10 70 70 70 70 70 70 70 70 70 70|……-..pppppppppp 0000cdc0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00|…… 0000cdd0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00|…… 0000cde0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00|…… ~~~ PAGE_N_HEAP=0x8066，当行记录格式为Compact时，初始值为0x0802，当行格式为Redundant时，初始值是2。其实这些值表示页初始时就已经有Infinimun和Supremum的伪记录行，0x8066-0x8002=0x64，代表该页中实际的记录有100条记录。 PAGE_FREE=0x0000代表删除的记录数，因为这里我们没有进行过删除操作，所以这里的值为0。 PAGE_GARBAGE=0x0000，代表删除的记录字节为0，同样因为我们没有进行过删除操作，所以这里的值依然为0。 PAGE_LAST_INSERT=0x0da5，表示页最后插入的位置的偏移量，即最后的插入位置应该在0xc0000+0x0da5=0xcda5，查看该位置： ~~~ 0000cda0 00 03 28 f2 cb 00 00 00 64 00 00 00 51 6e 4e 80|..（……d……QnN. 0000cdb0 00 00 00 2d 01 10 70 70 70 70 70 70 70 70 70 70|……-..pppppppppp 0000cdc0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00|…… ~~~ 可以看到，最后这的确是最后插入a列值为100的行记录，但是这次直接指向了行记录的内容，而不是指向行记录的变长字段长度的列表位置。 PAGE_DIRECTION=0x0002，因为我们是通过自增长的方式进行行记录的插入，所以PAGE_DIRECTION的方向是向右。 PAGE_N_DIRECTION=0x0063，表示一个方向连续插入记录的数量，因为我们是以自增长的方式插入了100条记录，因此该值为99。 PAGE_N_RECS=0x0064，表示该页的行记录数为100，注意该值与PAGE_N_HEAP的比较，PAGE_N_HEAP包含两个伪行记录，并且是通过有符号的方式记录的，因此值为0x8066。 PAGE_LEVEL=0x00，代表该页为叶子节点。因为数据量目前较少，因此当前B+树索引只有一层。B+数叶子层总是为0x00。 PAGE_INDEX_ID=0x00000000000001ba，索引ID。 上面就是数据页的Page Header部分了，接下去就是存放的行记录了，前面提到过InnoDB存储引擎有2个伪记录行，用来限定行记录的边界，我们接着往下看： ~~~ 0000c050 00 02 00 f2 00 00 00 dc 00 00 00 02 00 32 01 00|……2.. 0000c060 02 00 1c 69 6e 66 69 6d 75 6d 00 05 00 0b 00 00|……infimum…… 0000c070 73 75 70 72 65 6d 75 6d 0a 00 00 00 10 00 22 00|supremum……". ~~~ 观察0xc05E到0xc077，这里存放的就是这两个伪行记录，InnoDB存储引擎设置伪行只有一个列，且类型是Char（8）。伪行记录的读取方式和一般的行记录并无不同，我们整理后可以得到如下的结果： ~~~ #Infimum伪行记录 00 02 00 1c/*recorder header*/ 6e 66 69 6d 75 6d 00/*只有一个列的伪行记录，记录内容就是Infimum（多了一个0x00字节） */ #Supremum伪行记录 00 0b 00 00/*recorder header*/ 75 70 72 65 6d 75 6d/*只有一个列的伪行记录，记录内容就是Supremum*/ ~~~ 我们来分析infimum行记录的recorder header部分，最后2个字节位00 1c表示下一个记录的位置的偏移量，即当前行记录内容的位置0xc063+0x001c，得到0xc07f。0xc07f应该很熟悉了，我们前面的分析的行记录结构都是从这个位置开始。我们来看一下： ~~~ 0000c070 73 75 70 72 65 6d 75 6d 0a 00 00 00 10 00 22 00|supremum……". 0000c080 00 00 01 00 00 00 51 6d eb 80 00 00 00 2d 01 10|……Qm……-.. 0000c090 64 64 64 64 64 64 64 64 64 64 0a 00 00 00 18 00|dddddddddd…… 0000c0a0 22 00 00 00 02 00 00 00 51 6d ec 80 00 00 00 2d|"……Qm……- 可以看到这就是第一条实际行记录内容的位置了，如果整理后可以得到： /*第一条行记录*/ 00 00 01/*因为我们建表时设定了主键，这里ROWID即位列a的值1*/ 00 00 51 6d eb/*Transaction ID*/ 00 00 00 2d 01 10/*Roll Pointer*/ 64 64 64 64 64 64 64 64 64/*b列的值'aaaaaaaaaa'*/ ~~~ 这和我们查表得到的数据是一致的：select a,b,hex(b) from t order by a limit 1; 通过recorder header最后2个字节记录的下一行记录的偏移量，我们就可以得到该页中所有的行记录；通过page header的PAGE_PREV，PAGE_NEXT就可以知道上一个页和下个页的位置。这样，我们就能读到整张表所有的行记录数据。 最后我们来分析Page Directory，前面我们已经提到了从0x0000ffc4到0x0000fff7是当前页的Page Directory，如下： ~~~ 0000ffc0 00 00 00 00 00 70 0d 1d 0c 95 0c 0d 0b 85 0a fd|……p…… 0000ffd0 0a 75 09 ed 09 65 08 dd 08 55 07 cd 07 45 06 bd|.u……e……U……E.. 0000ffe0 06 35 05 ad 05 25 04 9d 04 15 03 8d 03 05 02 7d|.5……%……} 0000fff0 01 f5 01 6d 00 e5 00 63 95 ae 5d 39 6a e0 ac 93|……m……c..]9j…… ~~~ 需要注意的是，Page Directory是逆序存放的，每个槽2个字节。因此我们可以看到：00 63是最初行的相对位置，即0xc063；0070就是最后一行记录的相对位置，即0xc070。我们发现，这就是前面我们分析的infimum和supremum的伪行记录。Page Directory槽中的数据都是按照主键的顺序存放，因此找具体的行就需要通过部分进行。前面已经提到，InnoDB存储引擎的槽是稀疏的，还需通过recorder header的n_owned进行进一步的判断。如，我们要找主键a为5的记录，通过二叉查找Page Directory的槽，我们找到记录的相对位置在00 e5处，找到行记录的实际位置0xc0e5： ~~~ 0000c0e0 04 00 28 00 22 00 00 00 04 00 00 00 51 6d ee 80|..（."……Qm.. 0000c0f0 00 00 00 2d 01 10 69 69 69 69 69 69 69 69 69 69|……-..iiiiiiiiii 0000c100 0a 00 00 00 30 00 22 00 00 00 05 00 00 00 51 6d|……0."……Qm 0000c110 ef 80 00 00 00 2d 01 10 6e 6e 6e 6e 6e 6e 6e 6e|……-..nnnnnnnn 0000c120 6e 6e 0a 00 00 00 38 00 22 00 00 00 06 00 00 00|nn……8."…… 0000c130 51 6d f0 80 00 00 00 2d 01 10 71 71 71 71 71 71|Qm……-..qqqqqq 0000c140 71 71 71 71 0a 00 00 00 40 00 22 00 00 00 07 00|qqqq……@."…… ~~~ 可以看到第一行的记录是4不是我们要找的5，但是我们看前面的5个字节的recordheader，04 00 28 00 22，找到4～8位表示n_owned值的部分，该值为4，表示该记录有4个记录，因此还需要进一步查找。通过recorder和ader最后2个字节的偏移量0x0022，找到下一条记录的位置0xc107，这才是我们要找的主键为5的记录。 ***** ## Named File Formats 机制 随着InnoDB存储引擎的发展，新的页数据结构有时用来支持新的功能特性。比如前面提到的InnoDB Plugin，提供了新的页数据结构来支持表压缩功能，完全溢出的（Off page）大变长字符类型字段的存储。这些新的页数据结构和之前版本的页并不兼容。因此从InnoDB Plugin版本开始，InnoDB存储引擎通过Named File Formats机制来解决不同版本下页结构兼容性的问题。 InnoDB Plugin将1.0.x之前版本的文件格式（file format）定义为Antelope，将这个版本支持的文件格式定义为Barracuda。新的文件格式总是包含于之前的版本的页格式。下图显示了Barracuda文件格式和Antelope文件格式之间的关系，Antelope文件格式有Compact和Redudant的行格式，Barracuda文件格式即包括了Antelope所有的文件格式，另外新加入了前面我们已经提到过的Compressed何Dynamic行格式。  在InnoDB Plugin的官方手册中提到，未来版本的InnoDB存储引擎还将引入的新的文件格式，文件格式的名称取自动物的名字（这个学Apple？），并按照字母排序进行命名。翻阅源代码，发现目前已经定义好的文件格式有： ~~~ /**List of animal names representing file format.*/ static const char*file_format_name_map[]={ "Antelope"， "Barracuda"， "Cheetah"， "Dragon"， "Elk"， "Fox"， "Gazelle"， "Hornet"， "Impala"， "Jaguar"， "Kangaroo"， "Leopard"， "Moose"， "Nautilus"， "Ocelot"， "Porpoise"， "Quail"， "Rabbit"， "Shark"， "Tiger"， "Urchin"， "Viper"， "Whale"， "Xenops"， "Yak"， "Zebra" }； ~~~ 参数innodb_file_format用来指定文件格式，可以通过下面的方式查看当前所使用的InnoDB存储引擎的文件格式： `show variables like 'version'\G` `show variables like 'innodb_version'\G` `show variables like 'innodb_file_format'\G` `show variables like 'innodb_file_format_check'\G` 参数innodb_file_format_check用来检测当前InnoDB存储引擎文件格式的支持度，该值默认为ON，如果出现不支持的文件格式，你可能在错误日志文件中看到类似如下的错误： ~~~ InnoDB：Warning：the system tablespace is in a file format that this version doesn't support ~~~ ***** ## 约束 ### 数据完整性 关系型数据库系统和文件系统的一个不同点是，关系数据库本身能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。几乎所有的关系型数据库都提供了约束（constraint）机制，约束提供了一条强大而简易的途径来保证数据库中的数据完整性，数据完整性有三种形式： 1.实体完整性　保证表中有一个主键。在InnoDB存储引擎表中，我们可以通过定义Primary Key或者Unique Key约束来保证实体的完整性。或者我们还可以通过编写一个触发器来保证数据完整性。 2.域完整性　保证数据的值满足特定的条件。在InnoDB存储引擎表中，域完整性可以通过以下几种途径来保证：选择合适的数据类型可以确保一个数据值满足特定条件，外键（Foreign Key）约束，编写触发器，还可以考虑用DEFAULT约束作为强制域完整性的一个方面。 3.参照完整性　保证两张表之间的关系。InnoDB存储引擎支持外键，因此允许用户定义外键以强制参照完整性，也可以通过编写触发器以强制执行。 对于InnoDB存储引擎而言，提供了五种约束：Primary Key，Unique Key，Foreign Key，Default，NOT NULL ### 约束的创建和查找 约束的创建可以采用以下两个方式： （1）表建立时就进行约束定义 （2）利用ALTER TABLE 命令来进行创建约束 对于Unique Key的约束，我们还可以通过Create Unique Index来进行建立。对于主键约束而言，其默认约束名为PRIMARY KEY。而对于Unique Key约束而言，默认约束名和列名一样，当然可以人为的指定一个名字。对于Foreign Key约束，似乎会有一个比较神秘的默认名称。下面是一个简单的创建表的语句，表上有一个主键和一个唯一键： `create table u (id int,name varchar(20),id_card char(18),primary key(id),unique key(name));` `select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='mytest' and table_name='u'\G;` ~~~ ***************************1.row*************************** constraint_name：PRIMARY constraint_type：PRIMARY KEY ***************************2.row*************************** constraint_name：name constraint_type：FOREIGN KEY ~~~ 可以看到约束名就如之前所说的，主键的约束名为PRIMARY，唯一索引的默认约束名与列名各相同。当然用户还可以通过 ALTER TABLE 来创建约束，并且可以定义用户所希望的约束名，如下: `ALTER TABLE u ADD UNIQUE KET uk_id_card (id_card);` 再通过:`select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='mytest' and table_name='u'\G;` 查询信息  Foreign Key约束： 创建表p： `CREATE TABLE p id INT,u_id INT, PRIMARY KEY (id),FOREIGN KEY (u_id) REFERENCES p(id);` `select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='mytest' and table_name='p'\G;` ~~~ ***************************1.row*************************** constraint_name：PRIMARY constraint_type：PRIMARY KEY ***************************2.row*************************** constraint_name：p_ibfk_1 constraint_type：FOREIGN KEY ~~~ 这里我们通过information_schema架构下的表TABLE_CONSTRAINTS来查看当前MySQL库下所有的约束。对于Foreign Key的约束的定义，我们还可以通过查看表REFERENTIAL_CONSTRAINTS，并且可以详细地了解外键的属性，如： `select * from information_schema.REFERENTIAL_CONSTRAINTS where constraint_schema='mytest'\G;` ~~~ ***************************1.row*************************** CONSTRAINT_CATALOG：NULL CONSTRAINT_SCHEMA：test2 CONSTRAINT_NAME：p_ibfk_1 UNIQUE_CONSTRAINT_CATALOG：NULL UNIQUE_CONSTRAINT_SCHEMA：test2 UNIQUE_CONSTRAINT_NAME：PRIMARY MATCH_OPTION：NONE UPDATE_RULE：RESTRICT DELETE_RULE：RESTRICT TABLE_NAME：p REFERENCED_TABLE_NAME：p ~~~ ### 约束与索引的区别 约束是一个逻辑的概念,用来保证数据的完整性,而索引是一个数据结构,既有逻辑上的概念,在数据库还代表着物理存储的方式。 ### 对错误数据的约束 默认情况下，MySQL数据库允许非法或者不正确数据的插入或更新，或者内部将其转化为一个合法的值，如对于NOT NULL的字段插入一个NULL值，会将其更改为0再进行插入，因此本身没有对数据的正确性进行约束。 ### ENUM和 SET约束 MySQL数据库不支持传统的CHECK约束,但是通过ENUM 和SET 类型可以解决部分的约束请求。 `create table a (id int not null,date date not null);` `insert into a select NULL,'2009-02-30';` `show warnings;` ~~~ ***************************1.row*************************** Level：Warning Code：1048 Message：Column'id'cannot be null ***************************2.row*************************** Level：Warning Code：1265 Message：Data truncated for column'date' at row 1 select * from a; +----+-------------+ |id|date |0|0000-00-00 +----+-------------+ ~~~ 对于NOT NULL的列我插入了一个NULL值，同时插入了一个非法日期'2009-02-30'，MySQL都没有报错，而是显示了警告（warning）。__如果我们想约束对于非法数据的插入或更新，MySQL是提示报错而不是警告，那么我们应该设置参数sql_mode__，用来严格审核输入的参数，如： `set sql_mode='strict_trans_tables'; ` ~~~ insert into a select NULL,'2009-02-30'; ERROR 1048（23000）：Column'id'cannot be null insert into a select 1,'2009-02-30'; ERROR 1292（22007）：Incorrect date value：'2009-02-30'for column'date'at row 1 ~~~ 这次对非法的输入值进行了约束，但是只限于对离散数值的约束，对于传统CHECK约束支持的连续值的范围约束或更复杂的约束，ENUM和SET类型还是无能为力，这时用户需要通过触发器实现对于值域的约束。 ### 触发器与约束 完整性约束通常也可以使用触发器来实现，触发器的作用是在INSERT、DELETE和UPDATE命令之前或之后自动调用SQL命令或者存储过程。MySQL 5.0对于触发器的实现还不是非常完善，限制比较多；而从MySQL 5.1开始，触发器已经相对稳定，功能也较之前有了大幅的提高。 创建触发器的命令是CREATE TRIGGER，只有具备Super权限的MySQL用户才可以执行这条命令： ~~~ CREATE [DEFINER={user|CURRENT_USER}] TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE ON tbl_name FOR EACH ROW trigger_stmt ~~~ 最多可以为一个表建立5个触发器，即分别为INSERT、UPDATE、DELETE的BEFORE和AFTER各定义一个。BEFORE和AFTER代表触发器发生的时间，表示是在每行操作的之前发生还是之后发生。当前MySQL只支持FOR EACH ROW的触发方式，即按每行记录进行触发，不支持如DB2的FOR EACH STATEMENT的触发方式。 通过触发器，我们可以实现MySQL数据库本身并不支持的一些特性，如对于传统CHECK约束的支持、物化视图、高级复制、审计等特性。这里我们先关注触发器对于约束的支持。 我们考虑用户消费表，每次用户购买一样物品后其金额都是减的，若这时有不怀好意的人做了类似减去一个负值的操作，这样的话用户的钱没减少反而会不断地增加。 ~~~ create table usercash(userid int,cash int unsigned not null); insert into usercash select 1,1000; update usercash 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 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; $$ delete from usercash; insert into usercash select 1,1000; update usercash set cash=cash-(-20) where userid=1; select * from usercash; +--------+-------+ |userid|cash |1|1000 +--------+-------+ select * from usercash_err_log; +--------+------------+------------+------------------- |userid|old_cash|new_cash|user|time |1|1000|1020|root@localhost|2009-11-06 11：49：49 +--------+------------+------------+------------------ ~~~ 我们创建了一张表用来记录错误数值更新的日志，首先判断新旧值之间的差值，正常情况下消费总是减的，因此新值应该总是小于原来的值，因此对于大于原值的数据，我们判断为非法的输入，将cash值设定为原来的值。 ### 外键约束 外键用来保证参照完整性，MySQL默认的MyISAM存储引擎本身并不支持外键，对于外键的定义只是起到一个注释的作用。InnoDB存储引擎则完整支持外键约束。外键的定义如下： ~~~ [CONSTRAINT[symbol]] FOREIGN KEY [index_name](index_col_name,……) REFERENCES tbl_name (index_col_name,……) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT|CASCADE|SET NULL|NO ACTION ~~~ 我们可以在CREATE TABLE时就添加外键，也可以在表创建后通过ALTER TABLE命令来添加。 一个简单的外键的创建示例如下： ~~~ CREATE TABLE parent( 　　id INT NOT NULL, 　　PRIMARY KEY(id) )ENGINE=INNODB； CREATE TABLE child( 　　id INT, 　　parent_id INT, 　　index par_ind(parent_id), 　　FOREIGN KEY(parent_id) REFERENCES parent(id) )ENGINE=INNODB； ~~~ 一般来说，我们称被引用的表为父表，另一个引用的表为子表。外键定义为，ON DELETE和ON UPDATE表示父表做DELETE和UPDATE操作时子表所做的操作。可定义的子表操作有： （1）CASCADE：当父表发生DELETE或UPDATE操作时，相应的子表中的数据也被DELETE或UPDATE。 （2）SET NULL：当父表发生DELETE或UPDATE操作时，相应的子表中的数据被更新为NULL值。当然，子表中相对应的列必须允许NULL值。 （3）NO ACTION：当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。 （4）RESTRICT：当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。如果定义外键时没有指定ON （5）DELETE或ON UPDATE，这就是默认的外键设置。在Oracle中，有一种称为延时检查（deferred check）的外键约束，而目前MySQL的约束都是即时检查（immediate check）的，因此从上面的定义可以看出，在MySQL数据库中NO ACTION和RESTRICT的功能是相同的。 在Oracle数据库中，外键通常被人忽视的地方是，对于建立外键的列，一定不要忘记给这个列加上一个索引。而InnoDB存储引擎在外键建立时会自动地对该列加一个索引，这和Microsoft SQL Server数据库的做法一样。因此可以很好地避免外键列上无索引而导致的死锁问题的产生。 对于参照完整性约束，外键能起到一个非常好的作用。但是对于数据的导入操作，外键往往导致大量时间花费在外键约束的检查上，因为MySQL的外键是即时检查的，因此导入的每一行都会进行外键检查。但是我们可以在导入过程中忽视外键的检查，如： ~~~ SET foreign_key_checks=0; LOAD DATA…… SET foreign_key_checks=1; ~~~ ***** ## 视图 视图（View）是一个命名的虚表，它由一个查询来定义，可以当做表使用。与持久表（permanent table）不同的是，视图中的数据没有物理表现形式。 ### 视图的作用 视图在数据库中发挥着重要的作用。视图的主要用途之一是被用做一个抽象装置，特别是对于一些应用程序，程序本身不需要关心基表（base table）的结构，只需要按照视图定义来获取数据或者更新数据，因此，视图同时在一定程度上起到一个安全层的作用。 MySQL从5.0版本开始支持视图，创建视图的语法如下： ~~~ CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] [DEFINER={user|CURRENT_USER}] [SQL SECURITY{DEFINER|INVOKER}] VIEW view_name[(column_list)] AS select_statement [WITH[CASCADED|LOCAL]CHECK OPTION] ~~~ 虽然视图是基于基表的一个虚拟表，但是我们可以对某些视图进行更新操作，其实就是通过视图的定义来更新基本表，我们称可以进行更新操作的视图为可更新视图（updatable view）。视图定义中的WITH CHECK OPTION就是指对于可更新的视图，更新的值是否需要检查。 我们先看个例子： `create table t(id int);` `create view v_t as select * from t where t＜10;` ERROR 1054（42S22）：Unknown column't'in'where clause' create view v_t as select * from t where id＜10; insert into v_t select 20; select * from v_t； 我们创建了一个id＜10的视图，但是往里插入了id为20的值，插入操作并没有报错，但是我们查询视图还是没有能查到数据。 接着我们更改一下视图的定义，加上WITH CHECK OPTION： `alter view v_t as select * from t where id＜10 with check option;` insert into v_t select 20; ERROR 1369（HY000）：CHECK OPTION failed'mytest.v_t' 这次MySQL数据库会对更新视图插入的数据进行检查，对于不满足视图定义条件的，将会抛出一个异常，不允许数据的更新。 MysQL DBA一个常用的命令是show tables，会显示出当前数据库下的表，视图是虚表，同样被作为表而显示出来， 我们来看前面的例子：`show tables;` show tables命令把表t和视图v_t都显示出来了。如果我们只想查看当前数据库下的基表，可以通过information_schema架构下的TABLE表来查询，并搜索表类型为BASE TABLE的表，如： select * from information_schema.TABLES where table_type='BASE TABLE' and table_schema=database(); 要想查看视图的一些元数据（meta data），可以访问information_schema架构下的VIEWS表，该表给出了视图的详细信息，包括视图定义者（definer）、定义内容、是否是可更新视图、字符集等。如我们查询VIEWS表，可得： `select * from information_schema.VIEWS where table_schema=database();` ### 物化视图. Oracle数据库支持物化视图--改视图不是基于基表的虚表,而是根据基表实际存在的实表，即物化视图的数据存储在非易失的存储设备上。物化视图的好处是对于一些复杂的统计类查询能够直接查出结果。该视图也称为索引视图。 在Oracle数据库创建方式： （1）BUILD IMMEDIATE 是默认的创建方式，在创建物化视图的时候就生成数据。 （2）而BUILD DEFERRED 则在创建物化视图时不生成数据，以后根据需要再生成数据。 查询重写是指当对物化视图的基表进行查询时，数据库会自动判断能否通过查询物化视图来直接得到最终结果，如果可以，则避免聚集或者链接等这类较为复杂的SQL操作，直接从已经计算好的物化视图中得到所需的数据。 物化视图刷新是值当基表发生了DML操作后,物化视图何时采用哪种方式和基表进行同步。刷新模式有两种： （1）ON DEMAND 意味着物化视图在用户需要的时候进行刷新， （2） ON COMMIT 意味着物化视图对基表的DML操作提交的同时进行刷新。 刷新方法有四种： （1）FAST 刷新采用增量刷新，只刷新自上次刷新以后进行的修改。 （2）COMPLETE刷新是对整个物化视图进行完全刷新。 （3）FORCE刷新则数据库在刷新是会去判断是否可以进行快速刷新，如果可以采用FAST 否则采用COMPLETE的方式。 （4）NEVER是指物化视图不进行任何刷新。 MYSQL数据库本身不支持物化视图。但是用户可以通过机制来实现物化视图功能。通过ON DEMAND的物化视图例如： 创建表 ~~~ MySQL [qiushibaike]> create table Orders -> ( -> order_id int unsigned not null auto_increment, -> product_name varchar(30) not null, -> price decimal(8,2) not null, -> amount smallint not null, -> primary key (order_id) -> )engine=InnoDB; Query OK, 0 rows affected (1.72 sec) ~~~ 插入数据: ~~~ MySQL [qiushibaike]> insert into Orders VALUES -> (null,'CPU',135.5,1), -> (null,'Memory',48.2,3), -> (null,'CPU',125.6,3), -> (null,'CPU',105.3,4) -> ; ~~~ 查询数据: ~~~ MySQL [qiushibaike]> select * from Orders\G; *************************** 1. row *************************** order_id: 1 product_name: CPU price: 135.50 amount: 1 *************************** 2. row *************************** order_id: 2 product_name: Memory price: 48.20 amount: 3 *************************** 3. row *************************** order_id: 3 product_name: CPU price: 125.60 amount: 3 *************************** 4. row *************************** order_id: 4 product_name: CPU price: 105.30 amount: 4 4 rows in set (0.01 sec) ERROR: No query specified ~~~ 接着建立一张物化视图的基表，用来统计每件物品的信息，如： ~~~ MySQL [qiushibaike]> create table Orders_MV( -> product_name varchar(30) not null, -> price_sum decimal(8,2) not null, -> amount_sum int not null, -> price_avg float not null -> , orders_cut int not null, -> unique index(product_name) -> ); MySQL [qiushibaike]> insert into Orders_MV -> select product_name, -> SUM(price),SUM(amount),AVG(price),COUNT(*) -> FROM Orders -> GROUP BY product_name; Query OK, 2 rows affected (0.45 sec) Records: 2 Duplicates: 0 Warnings: 0 MySQL [qiushibaike]> select * from Orders_MV\G; *************************** 1. row *************************** product_name: CPU price_sum: 366.40 amount_sum: 8 price_avg: 122.133 orders_cut: 3 *************************** 2. row *************************** product_name: Memory price_sum: 48.20 amount_sum: 3 price_avg: 48.2 orders_cut: 1 2 rows in set (0.05 sec) ERROR: No query specified ~~~ 通过以上的方式就实现ON DEMAND的物化视 但是 __每次如果数据更新 都要先清空Orders_MV视图表 在插入数据__ 。当然这是COMPLETE,要实现FAST的方式 需要记住上次统计时order_id的位置。 但是,如果要实现ON COMMIT 的物化视图，就不像上面这么简单了。在Oracle数据库中是通过物化视图日志实现的，显然MySQL数据库没有这个日志，不过可以通过触发器同样达到这个目的。如下： ~~~ DELIMITER $$ CREATE TRIGGER tgr_Orders_insert after insert on Orders for each row BEGIN set @old_price_sum = 0; set @old_amount_sum = 0; set @old_price_avg = 0; set @old_orders_cnt = 0; select ifnull(price_sum,0), ifnull(amount_sum,0),ifnull(price_avg,0),ifnull(orders_cut,0) from Orders_MV where product_name = NEW.product_name into @old_price_sum,@old_amount_sum,@old_price_avg,@old_orders_cnt; set @new_price_sum=@old_price_sum + NEW.price; set @new_amount_sum = @old_amount_sum + NEW.amount; set @new_orders_cnt = @old_orders_cnt +1; set @new_price_avg = @new_price_sum/@new_orders_cnt; replace into Orders_MV VALUES(NEW.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt ); END; $$ DELIMITER ; ~~~ 上述代码创建了一个insert触发器,每次insert操作都会重新统计表Orders_MV中的数据.接着运行一下的查找查询数据; `insert into Orders values (NULL,'SSD',299,3);` ~~~ MySQL [qiushibaike]> select * from Orders_MV\G *************************** 1. row *************************** product_name: CPU price_sum: 366.40 amount_sum: 8 price_avg: 122.133 orders_cut: 3 *************************** 2. row *************************** product_name: Memory price_sum: 48.20 amount_sum: 3 price_avg: 48.2 orders_cut: 1 *************************** 3. row *************************** product_name: SSD price_sum: 299.00 amount_sum: 3 price_avg: 299 orders_cut: 1 3 rows in set (0.00 sec) ~~~ 还需要update和delect 触发器 ***** ## 分区表 ### 分区概述 查询分区是否启动`show variables like '%partition%'\G;`或者`SHOW PLUGINS\G`  分区主要用于数据库高可用性的管理。并不是启动分区，数据库就会运行得更快。 分区类型 （1）RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。MySQL5.5开始支持RANGE COLUMNS的分区. （2）LIST分区：和RANGE分区类型，只是LIST分区面向的是离散的值。MySQL5.5开始支持LIST COLUMNS的分区. （3）HASH分区：根据用户自定义的表达式的返回值进行分区，返回值不能为负数。 （4）KET分区：根据MySQL数据库提供的哈希函数来进行分区。 无聊创建何种类型的分区，如果表中存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。 建表：唯一索引允许为空。分区列只要是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列。 如果没有主键可以指定任意一个列进行分区列。 #### RANGE分区 第一种类型是RANGE分区，也是最常用的一种分区类型。 下面的CREATE TABLE语句创建了一个id列的区间分区表。当id小于10时，数据插入p0分区。当id大于等于10小于20时，插入p1分区： create table t(id int) engine=innodb 　　partition by range(id)( 　　partition p0 values less than(10), 　　partition p1 values less than(20)); 查看表在磁盘上的物理文件，启用分区之后，表不再由一个ibd文件组成了，而是由建立分区时的各个分区ibd文件组成，如下所示的t#P#p0.ibd，t#P#p1.ibd： system ls -lh /usr/local/mysql/data/test2/t* -rw-rw----1 mysql mysql 8.4K 7月31 14：11/usr/local/mysql/data/test2/t.frm -rw-rw----1 mysql mysql 28 7月31 14：11/usr/local/mysql/data/test2/t.par -rw-rw----1 mysql mysql 96K 7月31 14：12/usr/local/mysql/data/test2/t#P#p0.ibd -rw-rw----1 mysql mysql 96K 7月31 14：12/usr/local/mysql/data/test2/t#P#p1.ibd 接着插入如下数据： insert into t select 9; insert into t select 10; insert into t select 15; 因为表t根据列id进行分区，因此数据是根据id列的值的范围存放在不同的物理文件中的. 可以通过查询information_schema架构下的PARTITIONS表来查看每个分区的具体信息： select * from information_schema.PARTITIONS where table_schema=database() and table_name='t'\G; TABLE_ROWS列反映了每个分区中记录的数量。由于之前向表中插入了9、10、15三条记录，因此可以看到，当前分区p0中有1条记录、分区p1中有2条记录。PARTITION_METHOD表示分区的类型，这里显示的是RANGE。 对于表t，因为我们定义了分区，因此对于插入的值应该严格遵守分区的定义，当插入一个不在分区中定义的值时，MySQL数据库会抛出一个异常。 如下所示，我们向表t中插入30这个值： insert into t select 30; ERROR 1526（HY000）：Table has no partition for value 30 对于上述问题，我们可以对分区添加一个MAXVALUE值的分区。MAXVALUE可以理解为正无穷，因此所有大于等于20并且小于MAXVALUE的值放入p2分区： alter table t add partition(partition p2 values less than maxvalue); insert into t select 30; RANGE分区主要用于日期列的分区，如对于销售类的表，可以根据年来分区存放销售记录，如以下所示的分区表sales： 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) ); insert into sales select 100,'2008-01-01'; insert into sales select 100,'2008-02-01'; insert into sales select 200,'2008-01-02'; insert into sales select 100,'2009-03-01'; insert into sales select 200,'2010-03-01'; 这样创建的好处是，便于对sales这张表的管理。如果我们要删除2008年的数据，就不需要执行DELETE FROM sales WHERE date＞='2008-01-01'and date＜'2009-01-01'，而只需删除2008年数据所在的分区即可： alter table sales drop partition p2008; 这样创建的另一个好处是，可以加快某些查询的操作。如果我们只需要查询2008年整年的销售额： explain partitions select * from sales where date＞='2008-01-01' and date＜='2008-12-31'\G; 通过EXPLAIN PARTITION命令我们可以发现，在上述语句中，SQL优化器只需要去搜索p2008这个分区，而不会去搜索所有的分区，因此大大提高了执行的速度。 需要注意的是，如果执行下列语句，结果是一样的，但是优化器的选择又会不同了： explain partitions select * from sales where date＞='2008-01-01' and date＜'2009-01-01'\G; 这次条件改为date＜'2009-01-01'而不是date＜='2008-12-31'时，优化器会选择搜索p2008和p2009两个分区，这是我们不希望看到的。因此对于启用分区，你应该根据分区的特性来编写最优的SQL语句。 对于sales这张分区表，我曾看到过另一种分区函数，设计者的原意是想可以按照每年每月来进行分区，如： ~~~~ create table sales( 　　money int unsigned not null, 　　date datetime )engine=innodb partition by range(YEAR(date)*100+MONTH(date))( partition p201001 values less than (201002), partition p201002 values less than (201003), partition p201003 values less than (201004) ); ~~~~ 但是在执行SQL语句时开发人员会发现，优化器不会根据分区进行选择，即使他们编写的SQL语句已经符合了分区的要求，如： `explain partitions select * from sales where date＞='2010-01-01' and date＜='2010-01-31'\G；` ~~~~ ***************************1.row*************************** id：1 select_type：SIMPLE table：sales partitions：p201001，p201002，p201003 type：ALL possible_keys：NULL key：NULL key_len：NULL ref：NULL rows：4 Extra：Using where row in set（0.00 sec） ~~~~ 可以看到优化对分区p201001、p201002、p201003都进行了搜索。产生这个问题的主要原因是，对于RANGE分区的查询，优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()、UNIX_TIMESTAMP()这类函数进行优化选择，因此对于上述的要求，需要将分区函数改为TO_DAYS，如： ~~~~ create table sales( 　　money int unsigned not null, 　　date datetime )engine=innodb partition by range(to_days(date))( partition p201001 values less than(to_days('2010-02-01')), partition p201002 values less than(to_days('2010-03-01')), partition p201003 values less than(to_days('2010-04-01')) ); ~~~~ 这时再进行相同类型的查询，优化器就可以对特定的分区进行查询了： `explain partitions select * from sales where date＞='2010-01-01' and date＜='2010-01-31'\G;` ***** #### LIST分区 LIST分区和RANGE分区非常相似，只是分区列的值是离散的，而非连续的。如： ~~~~ create table t( 　　a int, 　　b int )engine=innodb partition by list(b)( partition p0 values in (1,3,5,7,9), partition p1 values in (0,2,4,6,8) ); ~~~~ 不同于RANGE分区中定义的VALUES LESS THAN语句，LIST分区使用VALUES IN，所以每个分区的值是离散的，只能是定义的值。如我们往表中插入一些数据： ~~~~ insert into t select 1,1; insert into t select 1,2; insert into t select 1,3; insert into t select 1,4; select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_name='t' and table_schema=database(); ~~~~ 如果插入的值不在分区的定义中，MySQL数据库同样会抛出异常： `insert into t select 1,10;` ERROR 1526（HY000）：Table has no partition for value 10 在用INSERT插入多个行数据的过程中遇到分区未定义的值时，MyISAM和InnoDB存储引擎的处理完全不同。MyISAM引擎会将之前的行数据都插入，但之后的数据不会被插入。而InnoDB存储引擎将其视为一个事务，因此没有任何数据插入。 先对MyISAM存储引擎进行演示，如： ~~~~ create table t( 　　a int, 　　b int )engine=myisam partition by list(b)( partition p0 values in (1,3,5,7,9), partition p1 values in (0,2,4,6,8) ); insert into t values(1,2),(2,4),(6,10),(5,3); ERROR 1526（HY000）：Table has no partition for value 10 select * from t; ~~~~ 可以看到对于插入的（6，10）记录没有成功，但是之前的（1，2），（2，4）记录都已经插入成功了。 而同一张表，存储引擎换成InnoDB，则结果完全不同： ~~~~ truncate table t; alter table t engine=innodb; insert into t values(1,2),(2,4),(6,10),(5,3); ERROR 1526（HY000）：Table has no partition for value 10 select * from t; Empty set（0.00 sec） ~~~~ 可以看到同样在插入(6,10)记录是报错，但是没有任何一条记录被插入表t中。因此在使用分区时，也需要对不同存储引擎支持的事务特性进行考虑。 #### HASH分区 HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致都是一样的。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。 要使用HASH分区来分割一个表，要在CREATE TABLE语句上添加一个“PARTITION BY HASH(expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。 下面的例子创建了一个HASH分区的表t，按日期列b进行分区： ~~~~ create table t_hash( 　　a int, 　　b datetime )engine=innodb partition by hash(YEAR(b)) partitions 4; ~~~~ 如果将一个列b为2010-04-01这个记录插入表t_hash中，那么保存该条记录的分区确定如下。 ~~~~ MOD(YEAR('2010-04-01'),4) =MOD(2010,4) =2 ~~~~ 因此会放入分区2中，我们可以按如下方法来验证： `insert into t_hash select 1,'2010-04-01';` `select table_name,partition_name,table_rows from information_schema. PARTITIONS where table_schema=database() and table_name='t_hash';` 可以看到p2分区有1条记录。当然这个例子中并不能把数据均匀地分布到各个分区中，因为分区是按照YEAR函数，因此这个值本身可以视为是离散的。如果对于连续的值进行HASH分区，如自增长的主键，则可以很好地将数据进行平均分布。 MySQL数据库还支持一种称为LINEAR HASH的分区，它使用一个更加复杂的算法来确定新行插入已经分区的表中的位置。它的语法和HASH分区的语法相似，只是将关键字HASH改为LINEAR HASH。下面创建一个LINEAR HASH的分区表t_linear_hash，它和之前的表t_hash相似，只是分区类型不同： ~~~~ create table t_linear_hash( 　　a int, 　　b datetime )engine=innodb partition by linear hash(year(b)) partition by 4; ~~~~ 同样插入‘2010-04-01’的记录，这次MySQL数据库根据以下的方法来进行分区的判断： （1）取大于分区数量4的下一个2的幂值V，V=POWER(2,CEILING(LOG(2,num)))=4; （2）所在分区N=YEAR('2010-04-01')&(V-1)=2。 虽然还是在分区2，但是计算的方法和之前的HASH分区完全不同。接着进行插入实际数据的验证： `insert into t_linear_hash select 1,'2010-04-01';` `select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='t_linear_hash';` LINEAR HASH分区的优点在于，增加、删除、合并和拆分分区将变得更加快捷，这有利于处理含有大量数据的表；它的缺点在于，与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡。 #### KEY分区 KEY分区和HASH分区相似；不同在于，HASH分区使用用户定义的函数进行分区，KEY分区使用MySQL数据库提供的函数进行分区。NDB Cluster引擎使用MD5函数来分区，对于其他存储引擎，MySQL数据库使用其内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。如： ~~~~ create table t_key( 　　a int, 　　b datetime )engine=innodb partition by key(b) partitions 4; ~~~~ 在KEY分区中使用关键字LINEAR，和在HASH分区中具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到的，而不是通过模数算法。 #### COLUMNS分区 RANGE、LIST、HASH和KEY这四种分区中，分区的条件必须是整型（interger），如果不是整型，那应该需要通过函数将其转化为整型，如YEAR()、TO_DAYS()、MONTH()等函数。MySQL数据库5.5版本开始支持COLUMNS分区，可视为RANGE分区和LIST分区的一种进化。COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。其次，RANGE COLUMNS分区可以对多个列的值进行分区。 COLUMNS分区支持以下的数据类型： 所有的整型类型，如INT、SMALLINT、TINYINT、BIGINT。FLOAT和DECIMAL则不予支持。 日期类型，如DATE和DATETIME。其余的日期类型不予支持。 字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不予支持。 对于日期类型的分区，我们不再需要YEAR()和TO_DAYS()函数了，而直接可以使用COLUMNS，如： ~~~~ create table t_columns_range( 　　a int, 　　b datetime )engine=innodb PARTITION BY RANGE COLUMNS(b)( partition p0 values less than('2009-01-01'), partition p1 values less than('2010-01-01') ); ~~~~ 同样，可以直接使用字符串的分区： ~~~~ CREATE TABLE customers_1( 　　first_name VARCHAR(25), 　　last_name VARCHAR(25), 　　street_1 VARCHAR(30), 　　street_2 VARCHAR(30), 　　city VARCHAR(15), 　　renewal DATE ) PARTITION BY LIST COLUMNS(city)( PARTITION pRegion_1 VALUES IN ('Oskarshamn','Högsby','Mönster˚as'), PARTITION pRegion_2 VALUES IN ('Vimmerby','Hultsfred','Västervik'), PARTITION pRegion_3 VALUES IN ('Nössjö','Eksjö','Vetlanda'), PARTITION pRegion_4 VALUES IN ('Uppvidinge','Alvesta','Växjo') ); 对于RANGE COLUMNS分区，可以使用多个列进行分区，如： CREATE TABLE rcx( 　　a INT, 　　b INT, 　　c CHAR(3), 　　d INT ) PARTITION BY RANGE COLUMNS(a,d,c)( PARTITION p0 VALUES LESS THAN(5,10,'ggg'), PARTITION p1 VALUES LESS THAN(10,20,'mmmm'), PARTITION p2 VALUES LESS THAN(15,30,'sss'), PARTITION p3 VALUES LESS THAN(MAXVALUE,MAXVALUE,MAXVALUE) ); ~~~~ MySQL数据库版本5.5.0开始支持COLUMNS分区，对于之前的RANGE和LIST分区，我们应该可以用RANGE COLUMNS和LIST COLUMNS分区进行很好的代替。 ### 子分区 子分区（subpartitioning）是在分区的基础上再进行分区，有时也称这种分区为复合分区(composite partitioning)。MySQL数据库允许在RANGE和LIST的分区上再进行HASH或者是KEY的子分区，如： ~~~~ CREATE TABLE ts(a INT,b DATE)engine=innodb PARTITION BY RANGE(YEAR(b)) SUBPARTITION BY HASH(TO_DAYS(b)) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ); system ls -lh /usr/local/mysql/data/test2/ts* -rw-rw----1 mysql mysql 8.4K Aug 1 15：50/usr/local/mysql/data/test2/ts.frm -rw-rw----1 mysql mysql 96 Aug 1 15：50/usr/local/mysql/data/test2/ts.par -rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p0#SP#p0sp0.ibd -rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p0#SP#p0sp1.ibd -rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p1#SP#p1sp0.ibd -rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p1#SP#p1sp1.ibd -rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p2#SP#p2sp0.ibd -rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p2#SP#p2sp1.ibd ~~~~ 表ts先根据b列进行了RANGE分区，然后又再进行了一次HASH分区，所以分区的数量应该为（3×2=）6个，这通过查看物理磁盘上的文件也可以得到证实。 我们也可以通过使用SUBPARTITION语法来显式指出各个子分区的名称，同样对上述的ts表： ~~~~ CREATE TABLE ts(a INT,b DATE) PARTITION BY RANGE(YEAR(b)) SUBPARTITION BY HASH(TO_DAYS(b))( PARTITION p0 VALUES LESS THAN(1990)( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN(2000)( SUBPARTITION s2, SUBPARTITION s3 ), PARTITION p2 VALUES LESS THAN MAXVALUE( SUBPARTITION s4, SUBPARTITION s5 ) ); ~~~~ 子分区的建立需要注意以下几个问题： 每个子分区的数量必须相同。 如果在一个分区表上的任何分区上使用SUBPARTITION来明确定义任何子分区，那么就必须定义所有的子分区。 因此下面的创建语句是错误的。 ~~~~ CREATE TABLE ts(a INT,b DATE) PARTITION BY RANGE(YEAR(b)) SUBPARTITION BY HASH(TO_DAYS(b))( PARTITION p0 VALUES LESS THAN(1990)( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN(2000), PARTITION p2 VALUES LESS THAN MAXVALUE( SUBPARTITION s2, SUBPARTITION s3 ) ); ERROR 1064（42000）：Wrong number of subpartitions defined，mismatch with previous setting near' PARTITION p2 VALUES LESS THAN MAXVALUE( SUBPARTITION s2, SUBPARTITION s3 ) ~~~~ 　　3.每个SUBPARTITION子句必须包括子分区的一个名称。 　　4.在每个分区内，子分区的名称必须是唯一的。 因此下面的创建语句是错误的。 ~~~~ CREATE TABLE ts(a INT,b DATE) 　　PARTITION BY RANGE(YEAR(b)) 　　SUBPARTITION BY HASH(TO_DAYS(b))( 　　PARTITION p0 VALUES LESS THAN(1990)( 　　SUBPARTITION s0, 　　SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN(2000)( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p2 VALUES LESS THAN MAXVALUE( SUBPARTITION s0, SUBPARTITION s1 ) ); ERROR 1517（HY000）：Duplicate partition name s0 ~~~~ 子分区可以用于特别大的表，在多个磁盘间分别分配数据和索引。假设有6个磁盘，分别为/disk0、/disk1、/disk2等。现在考虑下面的例子： ~~~~ CREATE TABLE ts(a INT,b DATE) ENGINE=MYISAM PARTITION BY RANGE(YEAR(b)) SUBPARTITION BY HASH(TO_DAYS(b))( PARTITION p0 VALUES LESS THAN(2000)( SUBPARTITION s0 DATA DIRECTORY='/disk0/data' INDEX DIRECTORY='/disk0/idx', SUBPARTITION s1 DATA DIRECTORY='/disk1/data' INDEX DIRECTORY='/disk1/idx' ), PARTITION p1 VALUES LESS THAN(2010)( SUBPARTITION s2 DATA DIRECTORY='/disk2/data' INDEX DIRECTORY='/disk2/idx', SUBPARTITION s3 DATA DIRECTORY='/disk3/data' INDEX DIRECTORY='/disk3/idx' ), PARTITION p2 VALUES LESS THAN MAXVALUE( SUBPARTITION s4 DATA DIRECTORY='/disk4/data' INDEX DIRECTORY='/disk4/idx', SUBPARTITION s5 DATA DIRECTORY='/disk5/data' INDEX DIRECTORY='/disk5/idx' ) ); ~~~~ 但是InnoDB存储引擎会忽略DATA DIRECTORY和INDEX DIRECTORY语法，因此上述分区表的数据和索引文件分开放置对其是无效的：   ### 分区中的NULL值 MySQL数据库允许对NULL值做分区，但是处理的方法和Oracle数据库完全不同。MYSQL数据库的分区总是把NULL值视为小于任何一个非NULL值，这和MySQL数据库中对于NULL的ORDER BY的排序是一样的。因此对于不同的分区类型，MySQL数据库对于NULL值的处理是不一样的。 对于RANGE分区，如果对于分区列插入了NULL值，则MySQL数据库会将该值放入最左边的分区（这和Oracle数据库完全不同，Oracle数据库会将NULL值放入MAXVALUE分区中）。例如： ~~~~ create table t_range( 　　a int, 　　b int )engine=innodb partition by range(b)( partition p0 values less than(10), partition p1 values less than(20), partition p2 values less than maxvalue ); ~~~~ 接着往表中插入（1，1）和（1，NULL）两条数据，并观察每个分区中记录的数量： `insert into t_range select 1,1;` `insert into t_range select 1,NULL;` `select * from t_range\G;` `select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='t_range'\G;` 可以看到两条数据都放入了p0分区，也就是说明了RANGE分区下，NULL值会放入最左边的分区中。另外需要注意的是，如果删除p0这个分区，你删除的是小于10的记录，并且还有NULL值的记录，这点非常重要。 LIST分区下要使用NULL值，则必须显式地指出哪个分区中放入NULL值，否则会报错，如： ~~~~ create table t_list( 　　a int, 　　b int)engine=innodb partition by list(b)( partition p0 values in (1,3,5,7,9), partition p1 values in (0,2,4,6,8) ); insert into t_list select 1,NULL; ERROR 1526（HY000）：Table has no partition for value NULL ~~~~ 若p0分区允许NULL值，则插入不会报错： ~~~~ create table t_list( 　　a int, 　　b int)engine=innodb partition by list(b)( partition p0 values in (1,3,5,7,9,NULL), partition p1 values in (0,2,4,6,8) ); insert into t_list select 1,NULL; select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='t_list'; ~~~~ HASH和KEY分区对于NULL的处理方式，和RANGE分区、LIST分区不一样。任何分区函数都会将含有NULL值的记录返回为0。如： ~~~~ create table t_hash( 　　a int, 　　b int)engine=innodb partition by hash(b) partitions 4; ~~~~ `insert into t_hash select 1,0;` `insert into t_hash select 1,NULL;` `select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='t_hash';` ~~~~ ***************************1.row*************************** table_name：t_hash partition_name：p0 table_rows：2 ~~~~ ### 分区和性能 数据库的应用分为两类： 一类是OLTP（在线事务处理），如博客、电子商务、网络游戏等； 一类是OLAP（在线分析处理），如数据仓库、数据集市。 在一个实际的应用环境中，可能既有OLTP的应用，也有OLAP的应用。如网络游戏中，玩家操作的游戏数据库应用就是OLTP的，但是游戏厂商可能需要对游戏产生的日志进行分析，通过分析得到的结果来更好地服务于游戏、预测玩家的行为等，而这却是OLAP的应用。 对于OLAP的应用，分区的确可以很好地提高查询的性能，因为OLAP应用的大多数查询需要频繁地扫描一张很大的表。假设有一张1亿行的表，其中有一个时间戳属性列。你的查询需要从这张表中获取一年的数据。如果按时间戳进行分区，则只需要扫描相应的分区即可。 对于OLTP的应用，分区应该非常小心。在这种应用下，不可能会获取一张大表中10%的数据，大部分都是通过索引返回几条记录即可。而根据B+树索引的原理可知，对于一张大表，一般的B+树需要2～3次的磁盘IO（到现在我都没看到过4层的B+树索引）。因此B+树可以很好地完成操作，不需要分区的帮助，并且设计不好的分区会带来严重的性能问题。 很多开发团队会认为含有1000万行的表是一张非常巨大的表，所以他们往往会选择采用分区，如对主键做10个HASH的分区，这样每个分区就只有100万行的数据了，因此查询应该变得更快了，如SELECT * FROM TABLE WHERE PK=@pk。但是有没有考虑过这样一个问题：100万行和1000万行的数据本身构成的B+树的层次都是一样的，可能都是2层？那么上述走主键分区的索引并不会带来性能的提高。是的，即使1000万行的B+树的高度是3，100万行的B+树的高度是2，那么上述走主键分区的索引可以避免1次IO，从而提高查询的效率。嗯，这没问题，但是这张表只有主键索引，而没有任何其他的列需要查询？如果还有类似如下的语句SQL：SELECT * FROM TABLE WHERE KEY=@key，这时对于KEY的查询需要扫描所有的10个分区，即使每个分区的查询开销为2次IO，则一共需要20次IO。而对于原来单表的设计，对于KEY的查询还是2～3次IO。 如下表Profile，根据主键ID进行了HASH分区，HASH分区的数量为10，表Profile有接近1000万行的数据： ~~~~ CREATE TABLE 'Profile'( 　　'id' int(11) NOT NULL AUTO_INCREMENT, 　　'nickname' varchar(20) NOT NULL DEFAULT'', 　　'password' varchar(32) NOT NULL DEFAULT'', 　　'sex' char(1)NOT NULL DEFAULT'', 　　'rdate' date NOT NULL DEFAULT '0000-00-00', 　　PRIMARY KEY('id'), 　　KEY 'nickname' ('nickname') ）ENGINE=InnoDB partition by hash(id) partitions 10； select count（nickname）from Profile； count（1）：9999248 ~~~~ 因为是根据HASH分区的，因此每个区分的记录数大致是相同的，即数据分布比较均匀： ~~~~ select table_name，partition_name，table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='Profile'; ~~~~ 注意：即使是根据自增长主键进行的HASH分区，也不能保证分区数据的均匀。因为插入的自增长ID并非总是连续的，如果该主键值因为某种原因被回滚了，则该值将不会再次被自动使用。 如果进行主键的查询，可以发现分区的确是有意义的： `explain partitions select * from Profile where id=1\G;` 可以发现只寻找了p1分区。 但是对于表Profile中nickname列索引的查询，EXPLAIN PARTITIONS则会得到如下的结果： `explain partitions select * from Profile where nickname='david'\G;` 可以看到，MySQL数据库会搜索所有分区，因此查询速度会慢很多，比较上述的语句： `select * from Profile where nickname='david'\G;` 上述简单的索引查找语句竟然需要1.05秒，这显然是因为搜索所有分区的关系，实际的IO执行了20～30次，在未分区的同样结构和大小的表上执行上述SQL语句，只需要0.26秒。 因此对于使用InnoDB存储引擎作为OLTP应用的表，在使用分区时应该十分小心，设计时要确认数据的访问模式，否则在OLTP应用下分区可能不仅不会带来查询速度的提高，反而可能会使你的应用执行得更慢。 ### 表与分区交换数据 MySQL5.6 开始支持 ALTER TABLE *** EXCGABGE PARTITION 语法。该语法允许分区或者子分区的数据与另一个非分区的表中的数据进行交换。如果非分区表中的数据为空，那么相当于将分区中的数据移动到非分区表中。若分区表没有数据这将外部数据移动到分区表中。 ALTER TABLE *** EXCGABGE PARTITION 语法，必须满足条件： （1）要交换的表需和分区表有着相同的表结构，但是表不能含有分区 （2）在非分区表中数据必须在交换分区定义内 （3）被交换的表中不能含有外键，或者其他的表含有对该表的外键引用 （4）用户除了需要alter、insert和 create 权限外，还需要DROP的权限 （5）使用该语句是，不会触发交换表和被交换表上的触发器 （6）AUTO_INCREMENT 列将被重置 列如: `create table e ( id int not null , fname varchar(30), lname varchar(30) ) partition by range (id) ( partition p0 values less than (50),partition p1 values less than (100),partition p2 values less than (150),partition p3 values less than (MAXVALUE));` ~~~~ MySQL [库名]> insert into e values -> (1669,'jim','smith'), -> (337,'mary','jones'), -> (16,'frank','white'), -> (2005,"linda",'black'); ~~~~ e2与e结构一样 但是e2不能分区 `create table e2 like e;` 删除分区:` alter table e2 remove partitioning; ` 观察分区表的数据: `SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';`  将e的分区p0的数据移动到表e2中: `alter table e exchange partition p0 with table e2;` 再查询数据: `SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';`  查询e2的数据 `select * from e2;`