MySQL Study之--MySQL innodb存储架构

MySQL Study之--MySQL innodb存储架构

     Oracle的数据存储有表空间、段、区、块、数据文件；MySQL InnoDB的存储管理也类似，但是MySQL增加了一个共享表空间和独立表空间的概念；

完整的表空间，会被分成如下结构供给InnoDB使用。

最小单位是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 changedmysql> 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 existmysql> create table t2 as select * from t1;Query OK, 2 rows affected (0.08 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> 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: 0mysql> 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 -ltotal 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.infodrwx--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.indexdrwx--S--- 2 mysql mysql     4096 Jun 30 17:15 performance_schemadrwx--S--- 2 mysql mysql     4096 Jul 10 17:28 prod-rw-rw---- 1 mysql mysql       58 Jul 10 17:26 relay-log.infodrwx--S--- 2 mysql mysql     4096 Jun 30 17:15 test[root@mysrv mysql]# cd prod[root@mysrv prod]# lsdb.opt  t1.frm  t2.frm  t2.ibd[root@mysrv prod]# lsdb.opt  t1.frm  t2.frm  t2.ibd  t3.frm  t3.ibd

[root@mysrv prod]# ls -lhtotal 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

----以上查询可以看出，每一个表都对应一个表空间文件