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

于 2015-08-13 16:16:09 发布
阅读量596 收藏 1
点赞数
分类专栏： 数据库技术 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lqx0405/article/details/47613413
版权

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> select * from t1;
Empty set (0.00 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create procedure p001()
    ->  begin
    ->  declare i int ;
    ->  set i=0;
    ->  while i < 10001 do
    ->  insert into t1 values (i,concat('usr',i));
    ->  set i=i+1;
    ->  end while;
    ->  end;
    ->  //
Query OK, 0 rows affected (0.14 sec)

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
----随着数据量的增大，又分配了新的页块！
客居天涯
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值