MySQL Study之--MySQL innodb引擎表存储分析

MySQL Study之--MySQL innodb引擎表存储分析

innodb存储引擎表类型：

　　innodb表类似oracle的IOT表(索引组织表-indexorganized table)，在innodb表中每张表都会有一个主键，如果在创建表时没有显示的定义主键则innodb如按照如下方式选择或者创建主键。
　　首先表中是否有唯一非空索引(unique not null)，如果有则该列即为主键。

　　不符合上述条件，innodb存储引擎会自动创建一个6字节大小的指针，rowid().

※ 对于启用了innodb_file_per_table 的参数选项之后，在每个表对应的.idb文件内只是存放了数据、索引和插入缓冲，而撤销（undo）信息，系统事务信息，二次写缓冲等还是存放在了原来的共享表空间内。

※ 数据段即B+树的叶节点，索引段即为B+树的非索引节点。

※ InnoDB存储引擎的管理是由引擎本身完成的，表空间是由分散的页和段组成。

※ 区由64个连续的页组成，每个页大小为16K，即每个区大小为1MB，创建新表时，先有32页大小的碎片页存放数据，使用完后才是区的申请，（InnoDB最多每次申请4个区，保证数据的顺序性能）

※ 页类型有：数据页、Undo页、系统页、事务数据页、插入缓冲位图页、以及插入缓冲空闲列表页。

案例分析：

测试一：

1、在table里insert数据

mysql> delimiter ;

执行存储过程，插入数据：

mysql> call p001();
Query OK, 1 row affected (1 min 5.56 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

查看tablespace管理方式：

mysql> show variables like '%per%';
+--------------------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------------------+-------+
| innodb_file_per_table | ON | ；；独立表空间管理模式

分析表的存储（未建立主键：）
[root@mysql2 innodb]# ./py_innodb_page_info.py -v /var/lib/mysql/prod/t1.ibd |more
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0001>
page offset 00000004, page type <B-tree Node>, page level <0000>
page offset 00000005, page type <B-tree Node>, page level <0000>
page offset 00000006, page type <B-tree Node>, page level <0000>
page offset 00000007, page type <B-tree Node>, page level <0000>
page offset 00000008, page type <B-tree Node>, page level <0000>
page offset 00000009, page type <B-tree Node>, page level <0000>
page offset 0000000a, page type <B-tree Node>, page level <0000>
page offset 0000000b, page type <B-tree Node>, page level <0000>
page offset 0000000c, page type <B-tree Node>, page level <0000>
page offset 0000000d, page type <B-tree Node>, page level <0000>
page offset 0000000e, page type <B-tree Node>, page level <0000>
page offset 0000000f, page type <B-tree Node>, page level <0000>
page offset 00000010, page type <B-tree Node>, page level <0000>
page offset 00000011, page type <B-tree Node>, page level <0000>
page offset 00000012, page type <B-tree Node>, page level <0000>
page offset 00000013, page type <B-tree Node>, page level <0000>
page offset 00000014, page type <B-tree Node>, page level <0000>
page offset 00000015, page type <B-tree Node>, page level <0000>
page offset 00000016, page type <B-tree Node>, page level <0000>
page offset 00000017, page type <B-tree Node>, page level <0000>
page offset 00000018, page type <B-tree Node>, page level <0000>
page offset 00000019, page type <B-tree Node>, page level <0000>
page offset 0000001a, page type <B-tree Node>, page level <0000>
page offset 0000001b, page type <B-tree Node>, page level <0000>
page offset 0000001c, page type <B-tree Node>, page level <0000>
page offset 0000001d, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 31:
Freshly Allocated Page: 1
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 27
File Segment inode: 1

注释：

1、其中level 0000，为索引的叶子节点

2、 level 0001为索引根节点

3、Total number of page: 总页数
4、Freshly Allocated Page:可用页
5、Insert Buffer Bitmap:插入缓存位图页
6、Insert Buffer Free List:插入缓存空闲列表页
7、B-tree Node:数据页
8、Uncompressed BLOB Page:二进制大对象页，存放溢出行的页，即溢出页

建立主键：
mysql> alter table t1 add primary key (id);
Query OK, 10001 rows affected (1.26 sec)
Records: 10001 Duplicates: 0 Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

查看table存储结构：
[root@mysql2 innodb]# ./py_innodb_page_info.py -v /var/lib/mysql/prod/t1.ibd |more
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0001>
page offset 00000004, page type <B-tree Node>, page level <0000>
page offset 00000005, page type <B-tree Node>, page level <0000>
page offset 00000006, page type <B-tree Node>, page level <0000>
page offset 00000007, page type <B-tree Node>, page level <0000>
page offset 00000008, page type <B-tree Node>, page level <0000>
page offset 00000009, page type <B-tree Node>, page level <0000>
page offset 0000000a, page type <B-tree Node>, page level <0000>
page offset 0000000b, page type <B-tree Node>, page level <0000>
page offset 0000000c, page type <B-tree Node>, page level <0000>
page offset 0000000d, page type <B-tree Node>, page level <0000>
page offset 0000000e, page type <B-tree Node>, page level <0000>
page offset 0000000f, page type <B-tree Node>, page level <0000>
page offset 00000010, page type <B-tree Node>, page level <0000>
page offset 00000011, page type <B-tree Node>, page level <0000>
page offset 00000012, page type <B-tree Node>, page level <0000>
page offset 00000013, page type <B-tree Node>, page level <0000>
page offset 00000014, page type <B-tree Node>, page level <0000>
page offset 00000015, page type <B-tree Node>, page level <0000>
page offset 00000016, page type <B-tree Node>, page level <0000>
page offset 00000017, page type <B-tree Node>, page level <0000>
page offset 00000018, page type <B-tree Node>, page level <0000>
page offset 00000019, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 27:
Freshly Allocated Page: 1
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 23
File Segment inode: 1

查看访问执行路径：
mysql> explain select * from t1 where id=10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: NULL
1 row in set (0.01 sec)

分析表：

mysql> analyze table t1;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| prod.t1 | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.06 sec)

mysql> explain select * from t1 where id=10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: NULL
1 row in set (0.01 sec)

测试二：

建立新表：

mysql> create table t2 as select * from t1 order by 1 limit 10;
Query OK, 10 rows affected (0.08 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from t2;
+----+------+
| id | name |
+----+------+
| 0 | usr0 |
| 1 | usr1 |
| 2 | usr2 |
| 3 | usr3 |
| 4 | usr4 |
| 5 | usr5 |
| 6 | usr6 |
| 7 | usr7 |
| 8 | usr8 |
| 9 | usr9 |
+----+------+
10 rows in set (0.00 sec)

mysql> explain select * from t2 where id=1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from t2 where id=1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where
1 row in set (0.00 sec)

采用独立的tablespace存储方式，每个table都会生成一个ibd文件

[root@mysql2 innodb]# ls /var/lib/mysql/prod/
db.opt t1.frm t1.ibd t2.frm t2.ibd
[root@mysql2 innodb]# ./py_innodb_page_info.py -v /var/lib/mysql/prod/t2.ibd |more
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 6:
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 1
File Segment inode: 1
----整个表只有一个叶子节点

mysql> alter table t2 add primary key(id);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

插入新的数据：
mysql> delimiter //
mysql> create procedure p002()
-> begin
-> declare i int ;
-> set i=10;
-> while i < 1001 do
-> insert into t2 values (i,concat('usr',i));
-> set i=i+1;
-> end while;
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call p002();

Query OK, 1 row affected (0.13 sec)

mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 1001 |
+----------+
1 row in set (0.00 sec)

查看table的存储结构：
[root@mysql2 innodb]# ./py_innodb_page_info.py -v /var/lib/mysql/prod/t2.ibd |more
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0000>
page offset 00000004, page type <B-tree Node>, page level <0000>
page offset 00000005, page type <B-tree Node>, page level <0000>
page offset 00000006, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 9:
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 4
File Segment inode: 1
----随着数据量的增大，又分配了新的页块！

秒客网

MySQL Study之--MySQL innodb引擎表存储分析

相关文章