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

野性酷女 2022-08-04 00:42 255阅读 0赞

**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  
**----随着数据量的增大，又分配了新的页块！**