mysql 分析全库

转载

我是数据分析师 2025-02-14 20:42:38

文章标签 mysql 分析全库 mysql 主键存储引擎 文章分类 MySQL 数据库

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+树的非索引节点。

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

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

----随着数据量的增大，又分配了新的页块！

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。