MySQL笔记(4)---表

1.前言

  上一章记录了MySQL中的一些文件组成,以及相关作用和参数配置,本章开始记录深层次的存储结构,以便更好理解MySQL的设计。

2.索引组织表

  InnoDB中,表都是根据主键顺序组织存放的,这种方式称为索引组织表。每个表都有一个主键,没有主键会按照一定规则选择或创建主键:

    判断表中是否有非空唯一索引,有则为主键,多个按建表时定义顺序的第一个作为主键,这个意思并不是SQL语句的顺序。

    没有,自动创建一个6字节的指针,_rowid,通过这个字段可以查看主键(对复合主键无能为力)

3.InnoDB逻辑存储结构

  前几章说了InnoDB的一些概念,比如共享表空间ibdata1,单表表空间,页,区等,这里对其逻辑结构进行详细记录说明。

  InnoDB的数据被逻辑地存放在一个空间中,称为表空间。表空间又由段(segment)、区(extent)、页(page)组成,页有时也被称为块(block)。

3.1 表空间

  用户启动参数innodb_file_per_table,每张表数据单独放到一个表空间。只存放数据,索引和缓冲bitmap页。其他数据如回滚信息,插入缓冲索引页,系统事务信息,二次写缓冲都还是存放在共享表空间中。打开一个手动提交的事务,修改表数据,可以观察到共享表空间大小增大,这就说明undo页存放在共享表空间中,执行回滚并不会变小,只是标记这些undo页可用而已。

3.2 段

  表是由段组成的,常见的段有数据段、索引段、回滚段等。InnoDB是索引组织结构,所以数据就是索引,数据段就是B+树的叶子节点,索引段就是B+树的非叶子节点。回滚段较特殊。

3.3 区

  区是由连续的页组成的,大小固定1MB,为了保证页的连续性,InnoDB一次会申请4~5个区。默认情况,页大小16KB,1个区里面有64个连续的页。

  1.0版本开始有压缩页,每个页大小可以通过参数KEY_BLOCK_SIZE设置为2K、4K、8K,对应区的页数为512,256,128。

  1.2版本增加参数innodb_page_size,将默认的页大小设置为4K、8K,页中的数据库不是压缩。区的大小始终是1MB。

  在为每张表创建表空间的时候,会发现创建的表默认大小是96KB,区不是固定1MB吗?这是因为在每个段开始的时候,会使用32个页大小的碎片页,使用完了之后才会申请64个页,减少磁盘开销。

 3.4 页

  InnoDB页是磁盘管理的最小单位,默认大小16kb。1.2版本开始,可以通过参数innodb_page_size将页的大小设置成4K、8K、16K。设置完成,所有页的大小都是innodb_page_size。不能对其再次修改,除非通过mysqldump导入和导出操作产生新的库。

  常见的页类型有:

    数据页、undo页、系统页、事务数据页、插入缓冲bitmap、插入空闲free列表页、未压缩的二进制大对象页、压缩的二进制大对象页。

3.5 行

  InnoDB存储引擎是面向列的,也就是说数据是按行进行存放的。每页最多放16KB / 2~200行的数据,即7992行。

4. InnoDB行记录格式

  页中保存一行行的数据,以链表的形式存储。在1.0版本之前,提供了Compact和Redundant两种格式来存放记录,后者是为了兼容之前版本保留的。通过SHOW TABLE STATUS LIKE ”table_name"查看当前表使用的格式。row_format属性表示当前使用的行记录结构。创建表的时候可以指定:create table name engine=xxx row_format=xxx

4.1 Compact格式

  MySQL5.0版本引入,目标是高效地存储数据。简单来说,一个页存放的数据越多,性能越高。具体格式如下:

  变长字段长度:列的长度小于255,使用1字节存放,大于255,使用2字节。因为varchar最大65535。是按列顺序的逆序存放其长度的。

  NULL标志位:表明列中是否有NULL值。有为1。1个字节

  记录头信息:5个字节,40位

    1~2:未知

    3:该行是否已被删除

    4:为1,如果该记录是预先被定义为最小的记录

    5~8:该记录拥有的记录数

    9~21:索引堆中该条记录的排序记录

    22~24:记录类型:000普通,001B+树节点指针,010 Infimum, 011 Supermum, 1xx保留

    25~40:页中下一条记录的相对位置。(这个就是链表的原因)

  最后就是实际列的数据了,NULL不占空间,只有NULL标志位。还有两个隐藏列:事务ID列和回滚指针列,分别是6字节和7字节。没有主键,会产生rowid,6个字节。在记录头后面,列数据前面。

  char类型空间未用完,会填充0x20。

4.2 Redundant行记录格式

  偏移列表,同样是按字段列的逆序放置。长度小于255,用1字节表示,大于255用2字节表示。与长度列表不同的是,这里定位的是偏移量,后者的偏移量等于前者的+前者保存的长度

  头信息:6个字节,48位

    1~2:未知

    3:该行是否删除

    4:为1,如果该记录是预先被定义为最小的记录

    5~8:该记录拥有的记录数

    9~21:索引堆中该条记录的索引号

    22~31:记录中的列数量

    32:偏移列表为1字节还是2字节

    33~48:下一条记录的位置

  列数量使用了10个字节记录,所以mysql支持的最大列数是2^10-1=1023列。

  这种格式varchar的NULL是不占空间的,但是char需要占用空间。

4.3 行溢出数据

  InnoDB可以将一条记录中的某些数据存储在数据页之外,比如BLOB、LOG这类大对象列。BLOB可以不将数据放入溢出页面,但是varchar列数据却也可能被放在溢出数据。

  varchar虽然使用2个字节记录长度,理论上可以存放65535字节,但是实际创建的时候不支持这么大的,因为还有其他开销,最大长度应该是65532.(2个字节记录长度,1个字节用于NULL)

  另外要注意的是65532针对的字符类型是Latin1的,如果是GBK或者UTF8又会失败。

  VARCHAR(N)指的是字符的长度,而最大支持是指字节,所以GBK的话大概是32766,UTF-8是21845超过这个值且数据库SQL_MODE没有设置成严格,会警告,并自动转为text类型。

  此外,VARCHAR指的是一条记录中所有VARCHAR字段的总长度限制。比如一张表里有3个varchar字段,每个长度22000,总长度就660000了,这样创建是会失败的。

  InnoDB中一页是16KB,16384字节,怎么放下varchar呢。一般情况数据是存放在页类型为B-tree node中,但是发生行溢出时,数据放在页类型为Uncompress BLOB页中。前面一部分空间存放数据,后面通过一个偏移量指向溢出页。问题来了,多长的varchar会保存在页中,从多长开始保存在BLOB页中:

  首先要清楚页的存储结构是B+树,如果只能存放一条记录,那么就没有意义了,所以一条记录超过页大小的时候,就会被放入溢出页中。如果能放入两条记录的话,大小阈值是多少才会被放入BLOB中呢,大概是8098。两条这么长的varchar记录依旧会在一页之中。另外,Text和BloB的数据类型也不是总是放在Uncompressed BLOB Page中的,和之前一样,要保证一个页存放两条记录。

4.4 Compressed和Dynamic行记录格式

  1.0版本开始引入新的文件格式,以前的Compact和Redundant格式称为Antelope文件格式,新的是Barracuda:Compressed和Dynamic两种格式。

  新的格式对于存放BLOB中的数据采取了完全的行溢出方式,只存放20个字节的指针,数据都放在Off Page中,之前的格式会存放768个前缀字节。Compressed行记录格式的另一个功能就是,存储在其中的行数据会以zlib的算法进行压缩,因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行有效的存储。

4.5 CHAR的行结构存储

  从4.1开始CHR(N)中的N指的是字符的长度,不是字节的长度了。因此CHAR类型的内部存储可能不是定长的数据。所以在内部将CHAR也视为变长字符类型,这就意味着变长长度列表中会记录CHAR数据类型的长度,对于未占满长度的字符还是填充0x20.

5.InnoDB数据页结构

  数据页由7个部分组成:File Header文件头、Page Header页头、Infimum和Supermum Records、User Record行记录、Free Space空闲空间、Page Directory页目录、File Trailer文件结尾信息。其中头尾大小是固定的,分别是38、56、8字节,这些空间用来记录一些该页的信息,如checksum,数据页在B+树索引的层数等。其它的是实际的行记录存储空间,是动态的。

5.1 File Header

  由8个部分构成,共38个字节:

    FIL_PAGE_SPACE_OR_CHECKSUM  4字节  mysql4.0.14之前,该值为0,后面代表checksum的值

    FIL_PAGE_OFFSET           4字节  表空间页的偏移量。如独立表空间大小为1GB,该页16KB,一共65536个页,这个就表示该页在所有页中的位置。若表空间ID是10,那么searchKey(10,1)就是查找表中的第二个页。

    FIL_PAGE_PREV           4字节  当前页的上一个页,B+树决定了叶子节点必须是双向列表

    FIL_PAGE_NEXT           4字节  当前页的下一个页

    FIL_PAGE_LSN            8字节  该页最后被修改的日志序列位置LSN

    FIL_PAGE_TYPE           2字节  InnoDB存储引擎页类型,0x45BF代表存放的是页数据

        0x0002  undo log 页

        0x0003  索引节点

        0x0004  insert buffer空闲列表

        0x0000  新分配的页

        0x0005  insert buffer bitmap

        0x0006  系统页

        0x0007  事务系统数据

        0x0008  File Space Header

        0x0009  扩展描述页

        0x000A  BLOB页

    FIL_PAGE_FILE_FLUSH_LSN     8字节   该值仅在系统表空间的一个页中定义,代表文件至少被更新到了该LSN值。独立表都是0

    FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID  4字节  从MySQL4.1开始该值表示页属于哪个表空间。

5.2 Page Header

  该部分用来记录数据页的状态信息,由14个部分组成,共占用56字节。

    PAGE_N_DIR_SLOTS    2    在Page Directory(页目录)中Slot(槽)数,‘4.4.5 Page Directory’小节中会介绍

    PAGE_HEAP_TOP       2    堆中第一个记录的指针,记录在页中是根据堆形式存放的

    PAGE_N_HEAP         2    堆中的记录数。一共占用2字节,但是第15位表示行记录格式

    PAGE_FREE         2    执向可重用空间的首指针

    PAGE_GARBAGE      2    已删除记录的字节数,即行记录结构中delete flag为1的记录大小的总数

    PAGE_LAST_INSERT    2     最后插入记录的位置

    PAGE_DIRECTION      2     最后插入的放向,可能是0x01 left、0x02 right、0x03 same rec、0x04 same page、0x05 no direction

    PAGE_N_DIRECTION    2     一个方向连续插入记录的数量

    PAGE_N_RECS        2     该页中记录的数量

    PAGE_MAX_TRX_ID     8      修改当前页的最大事务ID,注意该值仅在Secondary Index中定义

    PAGE_LEVEL         2      当前页在索引树中的位置,0x00代表叶节点,即叶节点总是在第0层

    PAGE_INDEX_ID      8      索引ID,表示当前页属于哪个索引

    PAGE_BTR_SEG_LEAF     10    B+树数据页非叶节点所在段的segment header。注意该值仅在B+树的ROOT页定义

    PAGE_BTR_SEG_TOP    10    B+树数据页所在段的segment header.注意该值仅在B+树的Root页定义

5.3 Infimum和Supermum Record

  每个数据页由两个虚拟的行记录,用来限定记录的边界。Infimum记录是比该页中任何主键都要小的值,Supermum是比任何可能大的值都大的值。在页创建的时候被建立,并且不会被删除。

5.4 User Record和Free Space

  User Record就是实际存储记录的内容。

  Free Space就是空闲空间,链表结构。

5.5 Page Directory

  页目录保存了记录的相对位置,这里放的是页的相对位置,不是偏移量。这些记录指针有时被称为槽或者目录槽。不是每个记录都有槽的,这是一个稀疏目录,一个槽中可能包含多个记录。在槽中按照索引键值顺序存放,这样可以使用二叉查找迅速找到记录的指针,但是由于是稀疏目录,所以只能查到一个初略的结果,还需要通过reocorder header中的next_record来继续查找。

  B+树不能定位到具体记录,只能定位到记录所保存的页,然后将页加载到内存,通过Page Directory进行二叉查找定位到具体记录。

5.6 File Tailer

  这个用于校验数据是否完整写入磁盘,占用8个字节,前4个表示checksum,后四个与File Header的FIL_PAGE_LSN相同。

  该校验会对系统有些开销,innodb_checksums可以关闭这个校验。

  MySQL5.6.6版本开始新增了参数innodb_checksum_algorithm,用来控制检测checksum的算法,默认crc32。可设置的有:innodb、crc32、none、strict_innodb、strict_crc32、strict_none。

  innodb算法是用于兼容之前版本的checksum检测方式,crc32是5.6.6版本引进的新的checksum算法,性能比innodb高。用strict算法保存,低版本的MySQL库无法读取这些页。none不检测。启动strict_crc32是最快的,他不会对innodb和crc32算法进行两次检测,但是低版本无法使用。

6. Named File Formats机制

  随着存储引擎的发展,新的页数据结构有时用于支持新的功能特性。比如前面提到的表压缩功能等。这些数据结构和之前的版本并不兼容,所以在1.0版本开始,InnoDB存储通过Named File Formats机制来解决不同版本下页结构兼容性问题。

  1.0版本之前的称为Antelope,之后的为Barracuda,新版的文件格式总是包含旧版的内容。SHOW VARIABLES LIKE ’innodb_file_format'

7 约束

 7.1 数据完整性约束

  关系型数据库本身保证存储数据的完整性,提供了约束机制,通常有3种形式:

    1.实体完整性:保证表中有一个主键,可以定义Primary Key,Unique Key。

    2.域完整性:保证数据每列满足特定条件。以下途径可以保证:选择合适的数据类型保证数据值满足条件,外键约束,编写触发器,用DEFAULT。

    3.参照完整性:保证两张表之间的关系,外键。

  InnoDB提供了以下几种约束:

    Primary Key

    Unique Key

    Foreign Key

    Default

    NOT NULL

7.2 约束的创建和查找

  创建方式:

    表建立时定义

    ALTER TABLE命令创建约束

  对于唯一索引约束Unique Key,用户还可以通过命令CREATE UNIQUE INDEX来创建。

  对主键约束,名为PRIMARY,对于Unique Key约束,约束名和列名一样。Foreign Key约束似乎会有一个比较神秘的默认名称。

7.3 约束和索引的区别

  创建约束的方法不就是创建索引吗,有什么区别?约束是一个逻辑的概念,用来保证数据的完整性。索引是一个数据结构,有逻辑的概念,也代表着物理存储方式。

7.4 对错误数据的约束

  在某些默认设置下,MySQL数据库允许非法的或不正确的插入数据或更新,或者在内部转换成一个合法的值,如向NOT NULL字段插入一个NULL值,数据库会将其改为0再进行插入。这个是通过设置sql_mode完成的。SET sql_mode = "STRICT_TRANS_TABLES",这个类型对输入进行了约束。

7.5 ENUM和SET约束

  MySQL不支持传统的CHECK约束,但是可以通过ENUM和SET类型解决部分需求。比如表上有一个性别类型,范围只有male或者female,这个时候可以使用ENUM约束。创建表的时候sex字段为ENUM('male','female')。只支持离散数据类型,对连续的无能为力。

7.6 触发器约束

  MySQL5.1开始触发器相对稳定。CREATE TRIGGER,只有super权限的用户才能执行。最多可以为一个表创建6个触发器,即分别为INSERT、UPDATE、DELETE的before和after各定义一个。当前MySQL只支持FOR EACH ROW的触发方式,即按每行记录进行触发。

  CREATE

  [DEFOMER = {user | CURRENT_USER}]

  TRIGGER trigger_name BEFORE | AFTER   INSERT|UPDATE|DELETE

  ON table_name FOR EACH ROW trigger_stmt。

  通过触发器可以实现一些不支持的特性,比如CHECK约束,物化视图,高级复制,审计等特性。比如买东西,每次都是减的,但是如果用户输入了一个负数,就变成加了。这个时候就可以通过触发器来完成这个判断了。

7.7 外键约束

  外键用来保证参照完整性,MySQL数据库的MyISAM引擎本身不支持外键,对外键的定义只是一个注释作用。

  [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

    CASCADE表示父表发生DELETE或UPDATE操作时,对应子表数据也进行DELETE或UPDATE操作。

    SET NULL 表示父表发生DELETE或UPDATE操作时,子表设置NULL

    NO ACTION表示子表有相关联记录,抛出异常,不允许操作

    RESTRICT表示父类发生DELETE或UPDATE操作时,抛出错误,不允许这类操作。

  Oracle数据库有一种延时检查的外键约束,检查在SQL执行之后进行,但MySQL都是即时检查,所以NO ACTION和RESTRICT没区别。默认使用RESTRICT。

  MySQL会自动为外键创建索引,避免死锁问题。

8 视图

  MySQL中视图是一个命名的虚表,它由一个SQL查询来定义,可以当做表使用。与持久表不同,视图没有实际的物理存储。

8.1 视图的作用

  视图的主要作用之一是用做一个抽象装置,程序本身不需要关心基表的结构,只需要按照视图定义来取数据或更新数据,一定程度上起到了安全层的作用。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]

  虽然视图是虚拟表,但是可以对其进行更新,本质上就是更新基本表。可能更新失败,带上WITH CHECK OPTION会失败的时候抛出异常。

8.2 物化视图

  Oracle支持物化视图,即该视图不是基于基表的虚表,而是根据基表实际存在的实表,存储在非易失的存储设备上。

  物化视图用于预先计算并保存多表的链接或者聚集等耗时较多的SQL操作结果。这样,执行复杂查询时,就可以避免进行这些耗时的操作。Oracle中创建方法有两种:

    BUILD IMMEDIATE:默认方式,在创建的时候就生成数据

    BUILD DEFERRED:根据需要生成数据

  查询重写就是会自动判断是否能通过查询物化视图得到最终结果,如果可以就避免了复杂的SQL操作。

  物化视图的刷新是指基表发生了DML操作后,物化视图何时采用哪种方式和基表进行同步:

    ON DEMAND:在用户需要的时候进行刷新

    ON COMMIT:对基表的操作提交时同步进行

  刷新的方式有4种:

    FAST:增量刷新,只刷新自上次刷新后的修改

    COMPLETE:全量刷新

    FORCE:刷新的时候会去判断,能快速刷新使用FAST,不能使用COMPLETE.

    NEVER:不刷新

  MySQL不支持物化视图,可以自己实现,比如定时将一个表导入另一个表。这个就是ON DEMAND类型。这自然是COMPLETE刷新方式,要实现FAST就比较复杂了,统计上次刷新的位置。实现ON COMMIT就复杂了,可以通过触发器。

  查询重写功能MySQL就无能为力了,需要在应用端进行控制。

9 分区表

 9.1 分区概述

  分区功能不是在存储引擎层完成的,所以InnoDB、MyISAM、NDB等都支持,但是也有不支持的,比如:CSV、FEDORATED、MERGE等。

  MySQL5.1版本添加了对分区的支持。这个过程是一个将表或索引分解为多个更小、更可管理的部分。从逻辑上讲,只有一个表或者一个索引,但是在物理上这个表或者索引可能由数十个物理区组成,这样就可以对各个部分处理的能力了。

  MySQL支持的分区类型是水平分区,不支持垂直分区。分区是局部分区索引,一个分区中既存放了数据又存放了索引,全局分区指的是数据存放在各个分区,但是索引存放在一个对象中。目前MySQL不支持全局分区。通过SHOW VARIABLES LIKE '%partition%'查看是否启用了分区。也可以使用SHOW PLUGINS查看。

  分区不一定会让数据库更快,可能会提高某些SQL的性能,但是分区主要是用于数据库的高可用性管理。在OLTP应用中,对分区的使用应该非常小心。

  MySQL数据库支持的分区类型有:

    RANGE分区:行数据基于属于一个给定连续区间的列值被放入分区。MySQL5.5开始支持RANGE COLUMNS的分区

    LIST分区:和RANGE分区类型,只是LIST分区面向的是离散的值。MySQL5.5开始支持LIST COLUMNS分区

    HASH分区:根据用户自定义的表达式的返回值来进行分区,返回值不能为负数。

    KEY分区:根据MySQL数据库提供的哈希函数进行分区。

  不论创建什么类型的分区,如果表中存在主键或者唯一索引时,分区列必须是唯一索引的一个组成部分。比如唯一索引是a,b,创建PARTITION BY hash(c)就不行,c不是a,b的一个组成部分。如果表没有主键,唯一索引,可以指定任何一个列为分区列。

  分区的目的是将数据分割,查询的范围就小了,这样速度就快了,所以要考虑表执行的SQL是否适合分区,万一分区却扫描了全表,那么就得不偿失了。

9.2 分区类型

9.2.1 RANGE分区

  Partition By Range(id) (

    Partition p0 values less than (10),

    Partition p1 values less than (20)

  );

  id小于10的插入p0,大于等于10,小于等于20的插入p1。

  启用分区后,物理磁盘上就不是一个ibd文件了,会有各个分区的ibd文件。

  另外,由于没有定义大于等于20的情况,插入这个会抛出异常。对于这种情况可以写成

    ALTER TABLE t ADD PARTITION( partition p2 values less than maxvalue);

  RANGE分区主要用于日期列的分区,例如对于销售类的表,可以根据年来分区存放销售记录。Partition by range (year(date)) (partition p2008 values less than(2009));

  这样的好处在于我们删除08年的数据只需要:alter table sales drop partition p2008;

  另一方面,查询08年的数据也只会查这个分区。EXPLAIN  PARTITIONS,但是如果查询08和09年的就会跨分区了,这个是不好的操作。

  如果按月进行分区,实际上查询并不会只查那个月的,优化器不一定会根据分区选择。因为对RANGE分区的查询,优化器只对YEAR()、TO_DAYS()、TO_SECONDS()、UNIX_TIMESTAMP()这类函数进行优化选择,所以如果按月查询,要写成:

    Partition By range(To_DAYS(date)) (

      Partition p201001 values less than (to_days('2010-02-01'))

    );

9.2.2 LIST 分区

  LIST分区和RANGE很相似,但是值是离散的,而不是连续的。range中使用values less than,list中使用values in,毕竟值是离散的。如果插入的值不在定义中,也会抛出异常。

  此外对于多行插入中存在未定义的值,MyISAM会将之前的数据都插入,遇到未定义的之后的不插入。InnoDB都不插入。

  partition by list(b) (

    partition p0 values in (1,2,3,4,5)

  );

9.2.3 hash分区

  hash分区的目的是将数据均匀的分布到预先定义的各个分区中,保证各分区的数据量大致都一样。hash分区中,数据库自动完成这个操作。

    Partition by hash(expr) expr是一个返回整型的表达式,后面还要跟上一个partitions num,表示分多少个区。

  MySQL还支持一种LENEAR HASH。优点在于增加、删除、合并、拆分分区更加快捷。缺点在于各分区的数据分布可能不大均衡。

9.2.4 KEY分区

  KEY与HASH相似,不同在于HASH分区使用用户定义的函数进行分区,KEY分区使用MySQL数据库提供的函数进行分区。对于NDB Cluster引擎,使用MD5函数分区。对于其他引擎,使用内部的hash函数。

9.2.5 COLUMNS分区

  前面介绍的分区条件都要是整型,不是整型要通过函数转变成整型,比如YEAR等。COLUMNS分区可以直接使用非整型的数据进行分区,分区根据类型直接比较而得。

  COLUMNS分区支持以下的数据类型:

    所有的整型类型:FLOAT和DECIMAL不支持

    日期类型:DATE和DATETIME,其余不支持

    字符串类型:如CHAR、VARCHAR、BINARY、VARBINARY。BLOB和TEXT不支持。

9.3 子分区

  子分区是在分区的基础上再进行分区,也称复合分区。SUBPARTITION。

  子分区要注意:

    1.每个子分区的数量必须相同

    2.要在一个分区表的任何分区上使用SUBPARTITION来明确定义任何子分区,就必须定义所有的子分区。

      每个SUBPARTITION子句必须包括子分区的一个名字。

      子分区不能重名。

9.4 分区中的NULL值

  mysql允许使用NULL值做分区,把NULL看做比所有非NULL都小的值。这个操作和order by操作是一样的。所以对不同的分区类型,对NULL的操作也不同。

  range分区中,会放在最左边的分区,

  list分区,要显示指出放在哪个分区,PARTITION p0 VALUES in (NULL)

  hash和key分区和前两个不同,任何函数都将含有NULL的记录返回为0.

9.5 分区和性能

  使用分区不一定会带来查询速度的提升,要明确其使用环境。

  数据库应用分为两类:OLTP(在线事务处理),比如Blog、电子商务、网络游戏。

            OLAP(在线分析处理),比如数据仓库、数据集市。

  对后者会有性能的提高,因为大部分查询要频繁的扫描一张很大的表。

  对于OLTP的应用,分区就要非常小心了。通常只需要返回几条记录,对B+树而言,大概需要2~3次磁盘IO,所以本身可以很好完成操作,不需要分区支持,设计不好反而会带来严重的性能问题。

9.6 在表和分区间交换数据

  MySQL5.6开始支持ALTER TABLE ... EXCHANGE PARTITION语法,支持分区的数据与非分区的表中数据进行交换。如果非分区表数据为空,等于将分区的数据移动到非分区。若分区表中数据为空,等于将外部表数据导入分区中。

  满足以下条件才行:

    表和分区表有相同的表结构,但表中不能有分区

    非分区表中的数据必须在交换的分区定义内

    被交换的表不能含有外键,或者其他的表含有对该表的外键引用

    用户处理ALTER INSERT 和CREATE权限外,还需要DROP权限

  还要注意:

    使用过程中,触发器不会被触发

    AUTO_INCREMENT列会被重置。

posted @ 2018-07-07 09:52  dark_saber  阅读(396)  评论(0编辑  收藏  举报