MySQL数据库InnoDB存储结构
一、概述
存储引擎称为表类型（说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法）。
MySQL主要有MyISAM、InnoDB存储引擎
1.1 MyISAM存储引擎
这种存储引擎不支持事务，不支持行级锁（支持表锁），只支持并发插入的表锁、主要用高负载的select。
支持3种不同的存储格式，分别是：静态表；动态表；压缩表
静态表：表中的字段都是非表长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时候会按照列的宽度定义不足空格）；
动态表：记录不是固定长度的，这样存储的优点是占用空间相对于较少；缺点：频繁的更新、删除数据容易产生碎片、需要定期对表进行优化，检查，修复。
压缩表：因为每个记录是被单独压缩的，所以只有非常小的访问开支
1.2 InnoDB存储引擎
该存储引擎提供了具有提交、回滚、和崩溃恢复能力的事务安全，支持行级锁、使用了B+Tree索引、支持自动增长列，支持外键约束。
但是对比MyISAM索引，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。
1.3 MEMORY存储引擎
使用存在于内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件，格式是 .frm，该文件只是存储表的结构，而其数据文件，都是存储在内存中，这样有利于对数据的快速处理，提高整个表的处理能力。
memory 因为它的数据是放在内存中的，但是一旦服务关闭，表中的数据就会丢失掉。
MEMORY存储引擎默认使用哈希（HASH）索引，其速度比使用B+Tree型要快。
Hash索引结构：其检索效率非常搞，索引的检索可以一次定位。
B-Tree索引：需要从根节点，最后才能访问到业节点这样多次的IO访问。
所以Hash索引的查询效率要高于B-Tree索引。
虽然Hash索引效率高，但是Hash索引本身由于其特殊性也带来了喝多限制和弊端，功能有限，支持也有限。
二、InnoDB存储架构
2.1.InnoDB Buffer Pool
不仅仅缓存索引数据，还会缓存表的数据，而且完全按照数据文件中数据库块结构信息来缓存。
2.2.Additional Memory Pool
参数是innodb_additional_mem_pool_size,保存数据字典信息，和其他内部数据结构的内存池的大小，单位是byte。
2.3、redo buffer pool
重做日志信息；
innodb_log_buffer_size 的大小，默认8M；
重做日志缓冲区的内容刷新到外部磁盘重做日志文件中的3种情况：
1.Master Thread 每一秒将重做日志缓冲刷新到重做日志文件；
2.每个事务提交的时候将重做日志缓冲区的日志刷新到重做日志中；
3.当重做日志缓冲区的空间小于1/2时候，将日志刷到重做日志文件中。
2.4.二进制日志缓冲区（Binlog Buffer）
主要用来缓存各种数据变更操作所产生的Binary log信息。为了提高性能，MySQL并不是每次将二进制日志写入Log File，二是先将信息写入Binlog Buffer中当满足特定条件后写入Log File。
2.5、Double Write
数据库断电后的数据恢复。
2.6、MySQL的逻辑存储结构
表空间：所有的数据都存放在表空间中；
所有的数据都存放在表空间内，启用了innodb_file_per_table,每张表内的数据可以单独存放到一个表空间内，但这只是数据，索引等其他数据还是存放在共享表空间内。
段：表空间有若干个段组成，常见的有数据段、索引段、回滚段；
innodb是索引聚集表，所以说数据就是索引，索引就是数据。
区：每64个连续的页组成区，因此区的大小正好是1M；
MySQL每次可以申请四个区，依次保证数据的顺序性能。
页：页是Innodb磁盘管理的最小单位，固定大小16K；
大小不可更改。
三、InnoDB配置
3.1 启动innodb存储引擎
mysql> show variables like 'default_storage_engine'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | +------------------------+--------+ 1 row in set (0.01 sec)
在my.cnf中加入 :
default_storage_engine=innodb
重启msyql生效。
3.2 innodb_buffer_pool_size
mysql> show variables like '%innodb_buffer_pool_size%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+ 1 row in set (0.00 sec)
类似oracle的SGA，用于innodb数据和索引的缓存，默认128M，innodb最重要的性能参数；
建议值：不超过物理内存的80%
3.3 innodb_log_buffer_size 日志缓冲区
mysql> show variables like 'innodb_log_buffer_size'; +------------------------+----------+ | Variable_name | Value | +------------------------+----------+ | innodb_log_buffer_size | 16777216 | +------------------------+----------+ 1 row in set (0.01 sec)
3.4 innodb_flush_log_at_trx_commit 控制事务的提交方式，控制日志刷新到硬盘的方式
mysql> show variables like 'innodb_flush_log_at_trx_commit'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+ 1 row in set (0.02 sec)
0: 每秒1次写入log file中，同事会镜像文件系统到磁盘的同步操作，每个事务的提交commit不会从log buffer 到log file，速度块，不安全，事故会丢失1s数据，用户游戏数据库；
1.：每个事务的提交commit会从og buffer 到log file。同时触发文件系统到磁盘的同步操作；
2：每个事务提交commit会从log buffer到log file，不会出发文件系统到磁盘的同步。但每一秒会有一次文件系统到磁盘的同步。
3.5 innodb_log_file_size 指定重做日志大小
日志文件的功能，数据库挂了之后的恢复操作
mysql> show variables like 'innodb_log_file%'; +---------------------------+----------+ | Variable_name | Value | +---------------------------+----------+ | innodb_log_file_size | 50331648 | | innodb_log_files_in_group | 2 | +---------------------------+----------+ 2 rows in set (0.03 sec)
参数设置：
vi my.cnf
innodb_log_file_size=512M
3.6 innodb_data_file_path
innodb_data_file_path用来指定innodb tablespace文件，如果我们不在My.cnf文件中指定innodb_data_home_dir和innodb_data_file_path那么默认会在datadir目录下创建ibdata1 作为innodb tablespace。
mysql> show variables like '%innodb%data%file%path'; +----------------------------+------------------------+ | Variable_name | Value | +----------------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | | innodb_temp_data_file_path | ibtmp1:12M:autoextend | +----------------------------+------------------------+ 2 rows in set (0.01 sec)
如果这个文件设置过小报错，不能直接把这个文件改大，改为1G会报错，可以新加一个文件。
vi my.cnf
innodb_data_file_path=ibdata1:12M;ibdata2:1G:autoextend
四、InnoDB Buffer Pool原理
4.1 基本参数
1）innodb_buffer_pool_size
2）innodb_buffer_pool_instance win 1个，unix 8个，如果内存小于1G，默认1个，将热点打散，提高并发性能；
mysql> show variables like '%innodb_buffer_pool_instance%'; +------------------------------+-------+ | Variable_name | Value | +------------------------------+-------+ | innodb_buffer_pool_instances | 1 | +------------------------------+-------+ 1 row in set (0.02 sec)
3）buffer pool 以页为单位，大小同innodb_page_size一样
mysql> show variables like '%innodb_page_size%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_page_size | 16384 | +------------------+-------+ 1 row in set (0.00 sec)
4.2 buffer pool 组成部分
1） free list：启动时候，有多个16K的空白页，这些页就存在 free list中；
2） LUR list：读取一个数据页的时候，就从free list 中读取一个页，存放数据，并将这页放在LUR list中；
3）flush list：将脏页刷到磁盘
mysql> show engine innodb status\G
4.3 buffer pool的调整
什么时候需要调整
1）主机增加物理内存的时候需要调整；
2）增加性能
mysql> show variablesinnodb_buffer_pool_size'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+ 1 row in set (0.01 sec) mysql> show variables like 'innodb_buffer_pool_instances'; +------------------------------+-------+ | Variable_name | Value | +------------------------------+-------+ | innodb_buffer_pool_instances | 1 | +------------------------------+-------+ 1 row in set (0.00 sec)
innodb_buffer_pool_instances 建议不要超多CPU的核数
五、数据字典
数据字典，数据库的元数据，包括数据库名称、表名、数据类型、结构、访问权限；
六、Innodb表空间管理
mysql :表空间> 段 > 区 > 页 > 行 SPACE>segment>extend>page>row
6.1 MySQL 5.7 有六种表空间
1) systeml tablespace
2) file-per-table tablespace
3) temporary tablespace
4) undo tablespace
5) general tablespace
6) transportable tablespace
6.2 表空间的介绍
innodb包含两种表空间文件模式：
1） 默认的共享表空间；
2） 每个表分离的独立表空间 innodb_file_per_table=1
共享表空间：
官方默认：数据量小可以，数据量大了以后不好控制，性能不好；
独立表空间：
每个表一个文件存储：分散IO，性能有提升，方便维护迁移，
6.3 共享表空间文件
-rw-r-----. 1 mysql mysql 12582912 10月 4 17:17 ibdata1
-rw-r----- 1 mysql mysql 1073741824 10月 4 17:17 ibdata2
独立表空间下：用户的数据和索引放在自己的数据库文件夹下；
共享表空间下：用户的数据和索引放在ibdata文件中；
同时还存储了一下数据：
1）数据字典
2）变更缓冲区
3）双写缓存区 类似raid1模式，double write作用是为了保证数据的写入可靠性，避免部分partial write 的情况
6,4 扩容表空间
重启生效：
vi /etc/my.cnf
innodb_data_file_path=ibdata1:12M;ibdata2:1G;ibdata3:2G:autoextend:max:5G
6.4临时表空间
主要用于临时排序，存放非压缩的临时表
5.7版本之前临时表都放在ibdata
5.7版本之后临时表放在临时表空间
查看参数：
mysql> show variables like '%innodb%tmp%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | innodb_tmpdir | | +---------------+-------+ 1 row in set (0.02 sec)
mysql创建临时表的情况：
union 查询，order by .group by, from 子查询，
创建临时表
mysql> create temporary table hks(id int); Query OK, 0 rows affected (0.00 sec) mysql> select * from hks; Empty set (0.00 sec)
创建临时表的结构数据放在tmp目录下：
-rw-r----- 1 mysql mysql 8556 10月 6 11:05 #sql934_3_0.frm
6.5 general tablespace 一般表空间
创建一般表空间
mysql> create tablespace hl add datafile '/mysql/data/hl1.ibd' engine=innodb; Query OK, 0 rows affected (0.11 sec)
创建表指定表空间：
mysql> create table qh(id int) tablespace hl; Query OK, 0 rows affected (0.03 sec)
表空间之间的迁移：
将表迁入系统表空间：
mysql> alter table qh tablespace=innodb_system; Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0
将表迁入独立表空间：
mysql> alter table qh tablespace=innodb_file_per_table; Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0
6.6 undo tablespace
mysql> show variables like '%undo%'; +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | innodb_max_undo_log_size | 1073741824 | | innodb_undo_directory | ./ | | innodb_undo_log_truncate | OFF | | innodb_undo_logs | 128 | | innodb_undo_tablespaces | 0 | +--------------------------+------------+ 5 rows in set (0.03 sec)
七、Innodb内部结构
查看页的使用情况
[root@localhost data]# systemctl stop mysqld [root@localhost data]# innochecksum --page-type-summary ibdata1 File::ibdata1 ================PAGE TYPE SUMMARY============== #PAGE_COUNT PAGE_TYPE =============================================== 60 Index page 79 Undo log page 11 Inode page 0 Insert buffer free list page 483 Freshly allocated page 8 Insert buffer bitmap 113 System page 1 Transaction system page 8 File Space Header 0 Extent descriptor page 5 BLOB page 0 Compressed BLOB page 0 Other type of page =============================================== Additional information: Undo page type: 43 insert, 36 update, 0 other Undo page state: 0 active, 79 cached, 0 to_free, 0 to_purge, 0 prepared, 0 other
八、Innodb存储格式介绍
文件格式有两种：
Antelope
Barracuda (MySQL 5.7之后)
支持两种行格式：
dynamic:
compressed:
mysql> show variables like '%innodb%format%'; +---------------------------+-----------+ | Variable_name | Value | +---------------------------+-----------+ | innodb_default_row_format | dynamic | | innodb_file_format | Barracuda | | innodb_file_format_check | ON | | innodb_file_format_max | Barracuda | +---------------------------+-----------+ 4 rows in set (0.02 sec)
8.1 compact行记录格式
变长字段长度列表 | NULL标记位 | 记录头信息 | 列1 | 列 2 | 列 3
