怎么理解并把握mysql的表
发布时间：2021-12-21 11:19:48 所属栏目：MySql教程 来源：互联网
导读：本篇内容介绍了怎么理解并掌握mysql的表的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 一.索引组织表 如果在创建表时没有定义主键，则innodb存储引擎
|
本篇内容介绍了“怎么理解并掌握mysql的表”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 一.索引组织表 如果在创建表时没有定义主键，则innodb存储引擎会按如下方式选择或创建主键 -首先判断表中是否有非空的唯一索引，如果有则该列即为主键，当表中有多个非空唯一索引时，innodb存储引擎将选择建表时第一个定义的非空唯一索引为主键。 -如果不符合上述条件，innodb存储引擎自动创建一个6字节大小的指针。 点击(此处)折叠或打开 由于b为允许为null的唯一索引，所以该表的主键为d create table wwj.z( a int not null, b int null, c int not null, d int not null, unique key (b), unique key (d), unique key (c) ); insert into wwj.z select 1,2,3,4; insert into wwj.z select 5,6,7,8; insert into wwj.z select 9,10,11,12; mysql> select a,b,c,d,_rowid from wwj.z; +---+------+----+----+--------+ | a | b | c | d | _rowid | +---+------+----+----+--------+ | 1 | 2 | 3 | 4 | 4 | | 5 | 6 | 7 | 8 | 8 | | 9 | 10 | 11 | 12 | 12 | +---+------+----+----+--------+ 二.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.行 怎么理解并掌握mysql的表 三.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的表”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！ （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
