1. 为什么创建一个 InnoDB 表只分配了 96K 而不是 1M?
2. 解析第 2 行记录格式?(用下面的表定义和数据做测试)
- mysql> create table gyj_t3 (id int,name1 varchar(10),name2 varchar(10),name3 varchar(10),name4 varchar(10),name5 varchar(10));
- Query OK, 0 rows affected (0.11 sec)
- mysql> insert into gyj_t3 (id,name1,name2,name3,name4) values(1,A,BB,CCC,DDDD);
- Query OK, 1 row affected (0.03 sec)
- mysql> insert into gyj_t3 values(3,aaaaaaaaaa,bbbbbbbbbb,ccccc,dddddd,e);
- Query OK, 1 row affected (0.06 sec)
- mysql> commit;
- Query OK, 0 rows affected (0.00 sec)
- mysql> insert into gyj_t3 values(4,aaaaaaaaaa,bbbbbbbbbb,null,dddddd,e);
- Query OK, 1 row affected (0.05 sec)
- mysql> commit;
- Query OK, 0 rows affected (0.00 sec)
- mysql> SELECT * FROM gyj_t3;
- +------+------------+------------+-------+--------+-------+
- | id | name1 | name2 | name3 | name4 | name5 |
- +------+------------+------------+-------+--------+-------+
- | 1 | A | BB | CCC | DDDD | NULL |
- | 1 | aaaaaaaaaa | bbbbbbbbbb | ccccc | | NULL |
- | 3 | aaaaaaaaaa | bbbbbbbbbb | ccccc | dddddd | e |
- | 4 | aaaaaaaaaa | bbbbbbbbbb | NULL | dddddd | e |
- +------+------------+------------+-------+--------+-------+
数据
3. 详细描述 commit 命令发出后, binlog 日志从内存写到磁盘的过程序?
1. 为什么创建一个 InnoDB 表只分配了 96K 而不是 1M?
innodb 在给数据分配存储空间时, 首先给 32 个的不连续的 page
一个 extent 区是 1M
如果 32 个 page 不够用, 再分配一段连续的 64 个 page, 也就是 16384*64=1M, 此后每次分配空间都是 1M 的整数倍
目的:
节省空间
小的 undo 32 个 page 就够了
问题: linux 下查看一个 ibd 文件, 大小是 96K, 也就是 6 个页, 那么 32 个页不是一次性给完而是用多少给多少?
- -rw-rw----. 1 mysql mysql 8.6K Feb 12 08:38 t3.frm
- -rw-rw----. 1 mysql mysql 96K Feb 12 08:38 t3.ibd
32 个 page 是 524288 字节, 差不多是 512KB, 插入数据使文件达到 500 多 kb 时, 下一次增长为 1M 的整数倍
2. 解析第 2 行记录格式?(用下面的表定义和数据做测试)
- +------+------------+------------+-------+--------+-------+
- | id | name1 | name2 | name3 | name4 | name5 |
- +------+------------+------------+-------+--------+-------+
- | 1 | A | BB | CCC | DDDD | NULL |
- | 1 | aaaaaaaaaa | bbbbbbbbbb | ccccc | | NULL |
- | 4 | aaaaaaaaaa | bbbbbbbbbb | ccccc | dddddd | e |
- | 4 | aaaaaaaaaa | bbbbbbbbbb | NULL | dddddd | e |
- +------+------------+------------+-------+--------+-------+
- create table t3 (id int,name1 varchar(10),name2 varchar(10),name3 varchar(10),name4 varchar(10),name5 varchar(10));
- insert into t3 (id,name1,name2,name3,name4) values(1,A,BB,CCC,DDDD);
- insert into t3 (id,name1,name2,name3,name4) values(1,aaaaaaaaaa,bbbbbbbbbb,ccccc,);
- insert into t3 (id,name1,name2,name3,name4,name5) values(4,aaaaaaaaaa,bbbbbbbbbb,ccccc,dddddd,e);
- insert into t3 (id,name1,name2,name4,name5) values(4,aaaaaaaaaa,bbbbbbbbbb,dddddd,e);
NULL 不占空间
也不占空间
问题: NULL 如何标志哪些列是 NULL 的?
MySQL 技术内幕 InnoDB 存储引擎 106 页及网上大部分都是提了一下, 参考 http://blog.csdn.net/linux_ever/article/details/64124868
第三行有 NULL 值, 因为 NULL 标志位不再是 00 而是 06, 转换成二进制 00000110, 为 1 的值表示第 2 列和第 3 列的数据为 NULL 其后存储列数据的部分, 没有存储 NULL 列, 而只存储了第 1 列和第 4 列的非 NULL 的值
- *************************** 3. row ***************************
- t1: d
- t2: NULL
- t3: NULL
- t4: fff
- 3 rows in set (0.00 sec)
这里的第 2 和第 3 列为 NULL,NULL 标志位为 06, 二进制是 00000110, 是怎么算出来 23 列的?
本次作业的第 12 行数据第 5 列为 NULL, 标志位是 20, 二进制是 00100000
第 4 行数据第 4 列为 NULL, 标志位是 08, 二进制是 00001000
int 长度是 4 字节, 其他可变长 name 长度是 10 字节, 用多少长多少
- https://www.cnblogs.com/wade-luffy/p/6289183.html
- http://blog.csdn.net/beiigang/article/details/42175995
ibd 文件格式如下
内容 | 16 进制地址 |
---|---|
File Space Header | 0x0008 |
Insert Buffer Bitmap | 0x0005 |
File Segment Inode | 0x0003 |
Used Page | 0x45BF |
…… | 第 N-4 个块 |
Used Page | 第 N-3 个块 |
Free Page | 第 N-2 个块 |
…… | 第 N-2 个块 |
Free Page | 第 N-2 个块 |
- https://dev.mysql.com/doc/internals/en/innodb-page-overview.html
- https://www.cnblogs.com/crossapply/p/5455620.html
行记录格式
compressed 压缩页, 默认 16K, 可以压缩成 8K4: alter table tabname row_format=compressed,key_block_size=8;
Dynamic 存储 LOB/TEXT 字段, 由 20 字节 + 偏移量组成, 本身不存储数据, 偏移量指向数据
Compact 默认行记录格式, 超出行记录长度时, 由前缀 768 字节 + 偏移量组成, 本身存储部分数据, 偏移量指向数据
Redundant 废弃
获取 ibd 十六进制文本
- hexdump -C -v t3.ibd > /tmp/t3.ibd.txt
- [root@docker01 tmp]# cat t3.ibd.txt |head -n 2
- 00000000 24 0a 12 cf 00 00 00 00 00 00 00 00 00 00 00 00 |$...............|
- 00000010 00 00 00 00 00 1a 25 e3 00 08 00 00 00 00 00 00 |......%.........|
- [root@docker01 tmp]# cat t3.ibd.txt |head -n 2048| tail -n 1024 |head -n 2
- 00004000 99 5f 47 61 00 00 00 01 00 00 00 00 00 00 00 00 |._Ga............|
- 00004010 00 00 00 00 00 1a 16 27 00 05 00 00 00 00 00 00 |...............|
- [root@docker01 tmp]# cat t3.ibd.txt |head -n 3072| tail -n 1024 |head -n 2
- 00008000 c2 25 6e 67 00 00 00 02 00 00 00 00 00 00 00 00 |.%ng............|
- 00008010 00 00 00 00 00 1a 25 e3 00 03 00 00 00 00 00 00 |......%.........|
- [root@docker01 tmp]# cat t3.ibd.txt |head -n 4096| tail -n 1024 |head -n 2
- 0000c000 48 81 1b 2c 00 00 00 03 ff ff ff ff ff ff ff ff |H..,............|
- 0000c010 00 00 00 00 00 1a 3e 2e 45 bf 00 00 00 00 00 00 |......>.E.......|
截取第 4 页
- 0000c000 48 81 1b 2c 00 00 00 03 ff ff ff ff ff ff ff ff |H..,............|
- 0000c010 00 00 00 00 00 1a 3e 2e 45 bf 00 00 00 00 00 00 |......>.E.......|
- 0000c020 00 00 00 00 00 0d 00 02 01 5b 80 06 00 00 00 00 |.........[......|
- 0000c030 01 29 00 02 00 03 00 04 00 00 00 00 00 00 00 00 |.)..............|
- 0000c040 00 00 00 00 00 00 00 00 00 1b 00 00 00 0d 00 00 |................|
- 0000c050 00 02 00 f2 00 00 00 0d 00 00 00 02 00 32 01 00 |.............2..|
- 0000c060 02 00 1f 69 6e 66 69 6d 75 6d 00 05 00 0b 00 00 |...infimum......|
- 0000c070 73 75 70 72 65 6d 75 6d 04 03 02 01 20 00 00 10 |supremum.... ...|
- 0000c080 00 2b 00 00 00 00 03 10 00 00 00 00 0b 52 b7 00 |.+...........R..|
- 0000c090 00 01 6b 01 10 80 00 00 01 41 42 42 43 43 43 44 |..k......ABBCCCD|
- 0000c0a0 44 44 44 00 05 0a 0a 20 00 00 18 00 3b 00 00 00 |DDD.... ....;...|
- 0000c0b0 00 03 11 00 00 00 00 0b 52 b7 00 00 01 6b 01 1e |........R....k..|
- 0000c0c0 80 00 00 01 61 61 61 61 61 61 61 61 61 61 62 62 |....aaaaaaaaaabb|
- 0000c0d0 62 62 62 62 62 62 62 62 63 63 63 63 63 01 06 05 |bbbbbbbbccccc...|
- 0000c0e0 0a 0a 00 00 00 20 00 41 00 00 00 00 03 12 00 00 |..... .A........|
- 0000c0f0 00 00 0b 52 b7 00 00 01 6b 01 2c 80 00 00 04 61 |...R....k.,....a|
- 0000c100 61 61 61 61 61 61 61 61 61 62 62 62 62 62 62 62 |aaaaaaaaabbbbbbb|
- 0000c110 62 62 62 63 63 63 63 63 64 64 64 64 64 64 65 01 |bbbcccccdddddde.|
- 0000c120 06 0a 0a 08 00 00 28 ff 47 00 00 00 00 03 13 00 |......(.G.......|
- 0000c130 00 00 00 0b 52 b7 00 00 01 6b 01 3a 80 00 00 04 |....R....k.:....|
- 0000c140 61 61 61 61 61 61 61 61 61 61 62 62 62 62 62 62 |aaaaaaaaaabbbbbb|
- 0000c150 62 62 62 62 64 64 64 64 64 64 65 00 00 00 00 00 |bbbbdddddde.....|
- .....
- 0000fff0 00 00 00 00 00 70 00 63 8b 8a 26 91 00 1a 3e 2e |.....p.c..&...>.|
- hexdump
3. 详细描述 commit 命令发出后, binlog 日志从内存写到磁盘的过程序?
先做 write 操作
日志会被 write 到每个线程对应的文件句柄的缓存中, 也就是标准的 IO 缓存中
每个线程会缓存到自己的 IO 缓存中, 每个线程产生的日志其他线程是不可见的
此时发生宕机, 日志丢失, 脏块丢失
再做 flush 操作
将私有缓存中的日志写到公共可见的操作系统文件缓存, 此时不同线程都可以看到其他线程的日志内容
此时宕机, 依然丢失数据
最后做 sync 持久化
将日志从内存中写到硬盘
sync_binlog 1 多少次事务一起写 binlog
innodb_flush_log_at_trx_commit 1 写 redolog
索引:
主键索引
非空唯一索引
都没有的话走 6 字节的 rowid, 但是不能作为 where 过滤条件
index organized table 索引组织表: 数据就在叶子节点上, 检索数据不需要回行
回行, 先找到索引所在位置, 根据索引再一次找到数据 二级索引辅助索引, 叶子节点只存储索引信息, 叶子上带着的信息指向数据所在的主键索引的位置
innodb 逻辑存储结构: tablespace - segement - extent - page - row
表空间是否独立 innodb_file_per_table, 默认为 ON
如果是独立表空间, 一张表一个表空间
如果是 off, 就全部放在 ibdata1 里面了
查看表 ID ,0 代表的是系统表空间 select * from innodb_sys_tables
tablespace 指的是共享表空间 ibdata1, 存储的是 undo 插入缓冲索引页系统事务 double write buffer, 因为写 undo 原因, 大小随时变化
ib_logfile, 每个表自己的空间: 数据 (跟主键索引一起存放) 辅助索引插入缓存 bitmap
插入缓冲
主键索引和数据放在一起, 其他索引放在索引页
往辅助索引页写东西时, 每个被写入的页先放在 buffer 存着
一旦有需要被写入的索引页读入了内存, 这是把 buffer 里存着的东西一起写进去
segement 段
段等于表, 段是数据的物理存储形式, 表是数据的逻辑定义
数据段, 聚簇索引, 数据段在 B-tree 的叶子结点
索引段, 非叶子结点就是索引
extent 区
一个 extent 是 1M, 是分配空间的最小单位
1M 由 64 个连续的 page 组成, 一个 page 是 16K
innodb 为保证 extent 连续, 一次申请多个
一个 extent 由多个 page 组成
page 页, 类似存储块大小
最小的 IO 单位
page 默认大小是 16K innodb_page_size = 16384
ROW 行
事务 ID 回滚指针数据索引
innodb 文件结构
表的组成:
tabname.ibd 表数据
File Space Header
tabname.frm 表结构
hexdump -C -v tabname.ibd 一个 page 16K,16 进制一行有 16 个字节, 那么一个 page 就有 1024 行 查看第一个块
hexdump -C -v tabname.ibd |head -n 1024 | head -n 2
2 块
hexdump -C -v tabname.ibd |head -n 2048 |tail -n 1024
3 块
hexdump -C -v tabname.ibd |head -n 3072 |tail -n 1024
来源: http://www.bubuko.com/infodetail-2497078.html