浅析Innodb架构

逻辑结构

• 默认的表空间位置在/var/lib/mysql

架构

内存架构

• show variables like ‘%hash_index%’; 默认是关闭的。

• 查看默认缓冲区大小

mysql> show  variables like '%log_buffer_size%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.03 sec)

mysql> select 16777216 / 1024 / 1024 as 'MB';
+-------------+
| MB          |
+-------------+
| 16.00000000 |
+-------------+

• 查看默认是指刷盘时机，日志在每次提交时写入并刷新磁盘。

mysql> show  variables like '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

磁盘结构

• 查看系统表空间位置,ibdata1表示的就是系统表空间的位置

mysql> show  variables like '%innodb_data_file_path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+

• 查看表的数据保存空间，默认是打开的。

mysql> show  variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

后台线程

• show engine innodb status;