MySQL Study之--MySQL innodb存储架构 心已赠人 2022-08-02 05:47 159阅读 0赞 **MySQL Study之--MySQL innodb存储架构** Oracle的数据存储有表空间、段、区、块、数据文件;MySQL InnoDB的存储管理也类似,但是MySQL增加了一个共享表空间和独立表空间的概念; 完整的表空间,会被分成如下结构供给InnoDB使用。 ![Center][] **最小单位是page,每个page为16K;64个连续的page组成一个extent; ** **多个extent和page构成一个segment。** **Segment初始时InnoDB会为它分配32个pages,之后根据需要会将extent分配给 segment,单次最多会分配4个extents给segment。** **1、共享表空间** 某一个数据库的所有的表数据,索引文件全部放在一个文件中,默认这个共享表空间的文件路径在data目录下。 默认的文件名为ibdata1 初始化为10M。 由于是默认的方式,就暂且理解为Mysql官方推荐的方式。相对而言所有的数据都在一个(或几个)文件中,比较利于管理,而且在操作的时候只需要open这一个(或几个)文件即可,相对来说代价很低。 **2、独占表空间** 每一个表都将会以独立的文件方式来进行存储,每一个表都有一个.frm表描述文件,还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容。 **案例分析:** \[root@mysrv ~\]\# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \\g. Your MySQL connection id is 6 Server version: 5.6.4-m7-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement. mysql> show variables like 'innodb_file_%table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ 1 row in set (0.00 sec) **查看表空间文件:** \[root@mysrv ~\]\# cd /usr/local/mysql/data/mysql/ \[root@mysrv mysql\]\# ls -l total 30088 \-rw-rw---- 1 mysql mysql 56 Jun 30 17:12 auto.cnf \-rw-rw---- 1 mysql mysql 18874368 Jul 10 10:44 ibdata1 \-rw-rw---- 1 mysql mysql 5242880 Jul 10 10:44 ib\_logfile0 \-rw-rw---- 1 mysql mysql 5242880 Jun 30 17:12 ib\_logfile1 \-rw-rw---- 1 mysql mysql 124 Jul 10 10:44 master.info **修改表空间模式:** **首先编辑my.cnf配置文件:** **\[root@mysrv ~\]\# cat /etc/my.cnf** \[mysqld\] port = 3306 socket = /tmp/mysql.sock skip-external-locking key\_buffer\_size = 16M max\_allowed\_packet = 1M table\_open\_cache = 64 sort\_buffer\_size = 512K net\_buffer\_length = 8K read\_buffer\_size = 256K read\_rnd\_buffer\_size = 512K myisam\_sort\_buffer\_size = 8M datadir=/usr/local/mysql/data/mysql log-bin=mysql-bin **innodb\_file\_per\_table = 1** **2、重新启动mysql server:** mysql> show variables like 'innodb_file_%table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ <span style="color:#ff0000;"><strong>| innodb_file_per_table | ON </strong></span> | +-----------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'innodb_%data%'; +--------------------------+------------------------+ | Variable_name | Value | +--------------------------+------------------------+ | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_stats_on_metadata | ON | +--------------------------+------------------------+ 3 rows in set (0.00 sec) **表空间改为独立模式** **案例:创建table,查看生成的表空间** mysql> use prod; Database changed mysql> show tables; +----------------+ | Tables_in_prod | +----------------+ | t1 | +----------------+ 1 row in set (0.03 sec) mysql> create table t2 as select * from t2; ERROR 1146 (42S02): Table 'prod.t2' doesn't exist mysql> create table t2 as select * from t1; Query OK, 2 rows affected (0.08 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> show tables; +----------------+ | Tables_in_prod | +----------------+ | t1 | | t2 | +----------------+ 2 rows in set (0.00 sec) mysql> select * from t2; +------+------+ | id | name | +------+------+ | 4 | rose | | 5 | john | +------+------+ 2 rows in set (0.00 sec) mysql> create table t3 as select * from t1; Query OK, 2 rows affected (0.08 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> show tables; +----------------+ | Tables_in_prod | +----------------+ | t1 | | t2 | | t3 | +----------------+ 3 rows in set (0.00 sec) mysql> select * from t3; +------+------+ | id | name | +------+------+ | 4 | rose | | 5 | john | +------+------+ 2 rows in set (0.00 sec) mysql> 查看生成的表空间文件: [root@mysrv mysql]# ls -l total 30112 -rw-rw---- 1 mysql mysql 56 Jun 30 17:12 auto.cnf -rw-rw---- 1 mysql mysql 18874368 Jul 10 17:28 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Jul 10 17:28 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Jun 30 17:12 ib_logfile1 -rw-rw---- 1 mysql mysql 124 Jul 10 17:26 master.info drwx--S--- 2 mysql mysql 4096 Jun 30 17:15 mysql -rw-rw---- 1 mysql mysql 114 Jun 30 17:15 mysql-bin.000001 -rw-rw---- 1 mysql mysql 63438 Jun 30 17:15 mysql-bin.000002 -rw-rw---- 1 mysql mysql 1096670 Jun 30 17:15 mysql-bin.000003 -rw-rw---- 1 mysql mysql 252 Jun 30 17:26 mysql-bin.000004 -rw-rw---- 1 mysql mysql 114 Jun 30 17:40 mysql-bin.000005 -rw-rw---- 1 mysql mysql 133 Jun 30 17:51 mysql-bin.000006 -rw-rw---- 1 mysql mysql 114 Jun 30 17:56 mysql-bin.000007 -rw-rw---- 1 mysql mysql 114 Jul 8 15:17 mysql-bin.000008 ..... -rw-rw---- 1 mysql mysql 380 Jul 10 17:26 mysql-bin.index drwx--S--- 2 mysql mysql 4096 Jun 30 17:15 performance_schema drwx--S--- 2 mysql mysql 4096 Jul 10 17:28 prod -rw-rw---- 1 mysql mysql 58 Jul 10 17:26 relay-log.info drwx--S--- 2 mysql mysql 4096 Jun 30 17:15 test [root@mysrv mysql]# cd prod [root@mysrv prod]# ls db.opt t1.frm t2.frm t2.ibd [root@mysrv prod]# ls db.opt t1.frm t2.frm t2.ibd t3.frm t3.ibd [root@mysrv prod]# ls -lh total 232K -rw-rw---- 1 mysql mysql 65 Jun 30 18:14 db.opt -rw-rw---- 1 mysql mysql 8.4K Jun 30 18:15 t1.frm -rw-rw---- 1 mysql mysql 8.4K Jul 10 17:28 t2.frm -rw-rw---- 1 mysql mysql 96K Jul 10 17:28 t2.ibd -rw-rw---- 1 mysql mysql 8.4K Jul 10 17:29 t3.frm -rw-rw---- 1 mysql mysql 96K Jul 10 17:29 t3.ibd **----以上查询可以看出,每一个表都对应一个表空间文件** [Center]: /images/20220731/a69ba94ef98b4ea4911e51963bc03b91.png
还没有评论,来说两句吧...