ITPub博客

首页 > 数据库 > MySQL > mysql 表

mysql 表

原创 MySQL 作者:wwjfeng 时间:2018-04-14 21:21:01 0 删除 编辑

一.索引组织表
如果在创建表时没有定义主键,则innodb存储引擎会按如下方式选择或创建主键
-首先判断表中是否有非空的唯一索引,如果有则该列即为主键,当表中有多个非空唯一索引时,innodb存储引擎将选择建表时第一个定义的非空唯一索引为主键。
-如果不符合上述条件,innodb存储引擎自动创建一个6字节大小的指针。

点击(此处)折叠或打开

  1. 由于b为允许为null的唯一索引,所以该表的主键为d
  2. create table wwj.z(
  3.   a int not null,
  4.   b int null,
  5.   c int not null,
  6.   d int not null,
  7.   unique key (b),
  8.   unique key (d),
  9.   unique key (c)
  10. );
  11. insert into wwj.z select 1,2,3,4;
  12. insert into wwj.z select 5,6,7,8;
  13. insert into wwj.z select 9,10,11,12;

  14. mysql> select a,b,c,d,_rowid from wwj.z;
  15. +---+------+----+----+--------+
  16. | a | b | c | d | _rowid |
  17. +---+------+----+----+--------+
  18. | 1 | 2 | 3 | 4 | 4 |
  19. | 5 | 6 | 7 | 8 | 8 |
  20. | 9 | 10 | 11 | 12 | 12 |
  21. +---+------+----+----+--------+

二.innodb逻辑存储结构
1.表空间
默认为所有数据都存放在共享表空间中。如果开启参数innodb_file_per_table之后,则每张表的数据将会放在单独的表空间中。
如果启用了innodb_file_per_table,则每张表的表空间内存放的只是数据,索引,和插入缓冲的bitmap页。其他类的数据,如插入缓冲索引页,系统事务信息,二次写缓冲,还是存放在原来的共享表空间中。

2.段
表空间是由各个段组成的,常见的段有数据段,索引段,回滚段等。
段的管理都是由引擎自身所完成,DBA不能也没有必要对其进行控制。

3.区
区是由连续页组成的空间,在任何情况下每个区的大小都为1m。
为了保证区中页的连续性,innodb存储引擎一次从磁盘申请4~5个区。默认情况innodb存储引擎页的大小为16kb,即一个区中一共有64个连续的页。
当用户启用参数innodb_file_er_table后,创建的表默认大小是96kb,而不是区的默认大小1m。这是因为在每个段开始时,先用32个页大小的碎片页来存放数据,使用完这些碎片页后,才是64个连续页的申请。这样做的目的是,对于一些小表,或者是undo这类的段,可以在开始时申请较少的空间,节省磁盘容量的开销。

4.页
innodb磁盘管理的最小单位
常见的页类型有:
数据页
undo页
系统页
事务数据页
插入缓冲位图页
插入缓冲空闲列表页
未压缩的二进制大对象页
压缩的二进制大对象页

5.行


三.innodb行记录格式
1.compact行记录格式
5.0以后引入,其设计目的是高效的存储数据,简单来说,一个页中存放的行数据越多,其性能越高。

2.redundant
5.0之前的行记录存储格式

3.行溢出数据
行存放varchar类型的数据最大长度和为65532字节。
建表时varchar(N)表示的是字符长度。

例如:下面建表语句将会报错
create table haha22(
 a varchar(65532)
) charset=utf8 engine=innodb;
ERROR 1074 (42000): Column length too big for column 'a' (max = 21845); use BLOB or TEXT instead

下面语句可以正常执行
create table haha22(
 a varchar(1)
) charset=utf8 engine=innodb;
insert into wwj.haha22 values ('阳');

行溢出数据存储图:


那么多长的varchar是保存在单个数据页中的,从多长开始又会保存在blob页呢?
可以这样进行思考:innodb存储引擎表是索引组织的,即B+Tree结构,这样每个页中至少应该有两条行记录,因此如果页中只能存放下一条记录,那么innodb存储引擎会自动将行数据存放到溢出页中。
经过实验发现当行的长度为8098字节时,可以在页中放入两条记录,并且不用扩展至blob页。
对于TEXT 和 BLOB数据类型也是如此,如果在数据页中能存放至少2条记录,则将不会将记录扩展至blob页。


4.新的文件格式barracuda
该文件格式下拥有两种新的行记录格式:compressed 和 dynamic
新的记录格式对于存放在blob中的数据采用了完全的行溢出的方式,在数据页中只存放20个字节的指针,实际数据都存放在off page中,而之前的compact 和 redundant两种格式会存放768个前缀字节。

compressed行记录格式的另一个功能就是,存储在其中的行数据会以zlib的算法进行压缩。因此对于blob,text,varchar这类大长度类型的数据能够进行非常有效的存储。

参数innodb_file_format用来指定文件格式

四.约束
约束类型
primary key
unique key
foreign key
default
not null

五.分区表
1.mysql支持以下几种类型的分区:
range分区
list分区
hash分区
key分区
* 如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分.如果建表时没有指定主键,唯一索引,可以指定任何一个列为分区列。
* mysql分区表中的索引都是局部分区索引,不支持全局索引


2.分区类型
(1).range分区
create table wwj.sales(
 money int,
    date1 datetime
) engine=innodb
partition by range(year(date1)) (
 partition p2008 values less than (year('2009-01-01')),
    partition p2009 values less than (year('2010-01-01')),
    partition p2010 values less than (year('2011-01-01'))
);
优化器只能对year(),to_days(),to_seconds(),unix_timestamp()这类函数进行优化选择

(2).list 分区
create table wwj.tt(
 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)
);

(3).hash 分区
目的是将数据均匀的分布到预先定义的各个分区中,保证各分区的数据量大致都是一样的
--普通hash分区
create table wwj.qqq(
 a int,
    b datetime
) engine=innodb
partition by hash (year(b)) 
partitions 4;
分配记录的算法如下:
mod(year('2010-04-01'),4) = mod(2010,4) =2
因此记录会放在p2分区中

--linear hash分区
create table wwj.qqq(
 a int,
    b datetime
) engine=innodb
partition by hash (year(b)) 
partitions 4;
分配记录的算法如下:
取大于分区数量4的下一个2的幂值V V=POWER(2,CEILING(LOG(2,num)))=4
所在分区N=YEAR('2010-04-01')&(V-1)=2
因此记录会放在p2分区中

linear hash分区的优点在于,增加,删除,合并,拆分分区将变得更加快捷,有利于处理含有大量数据的表。
缺点在于数据分布可能不太均衡。

(4).key分区
和hash分区相似,不同之处在于hash分区使用用户定义的函数进行分区,key分区使用mysql数据库提供的函数进行分区。
create table wwj.www(
 a int,
    b datetime
) engine=innodb
partition by key (b) 
partitions 4;

(5).columns 分区
前面的4种分区中,分区的条件是:数据必须是整形,如果不是整形,则需要通过函数将其转化为整形。
columns分区可以直接使用非整形的数据进行分区,分区根据类型直接比较而得,不需要转化为整形,并且支持多个列的值进行分区。
columns支持以下数据类型:
int,smallint,tinyint,bigint,date,datetime,char,varchar,binary,varbinary

参考书籍:
MySQL技术内幕:InnoDB存储引擎(第2版)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15412087/viewspace-2152949/,如需转载,请注明出处,否则将追究法律责任。

上一篇: mysql 索引
请登录后发表评论 登录
全部评论

注册时间:2010-03-27

  • 博文量
    138
  • 访问量
    58007