MySQL的存储引擎之InnoDB

InnoDB存储引擎提供了提交、回滚和奔溃恢复的事务能力，它是高可靠性和高性能的平衡。
下图是MySQL 5.7中InnoDB存储引擎的内存和磁盘结构。

使用InnoDB来存表有很多优点：

• 如果服务器因硬件或软件问题而崩溃，无论数据库发生了什么，重启数据库后不需要做任何特别的事情。InnoDB自动完成崩溃前提交的更改，并撤消正在进行但未提交的更改。
• InnoDB存储引擎维护自己的缓冲池，经常使用的数据直接从内存中处理。这个缓存适用于多种类型的信息并加快处理速度。在专用数据库服务器上80%的物理内存通常分配给缓冲池。
• 如果将相关数据拆分为不同的表，则可以设置强制引用的外键。更新或删除主表数据，子表中的相关数据会自动更新或删除。
• 如果数据在磁盘或内存中损坏，checksum机制会提醒。
• 当为每个表设计具有适当主键列的数据库时，操作涉及到这些列会自动优化。引用主键非常快WHERE子句、ORDER BY子句、GROUP BY子句和联接操作中的列。
• 插入、更新和删除通过一种称为更改缓冲的自动机制进行优化。InnoDB不仅允许对同一个表的并发读写访问，还缓存更改后的数据优化磁盘I/O。
• 当相同的行从表中反复访问，称为自适应散列索引的功能将接管使这些查找更快，就好像它们是从散列表中出来的一样。
• 可以压缩表和相关索引。
• 创建和删除索引，对性能和可用性的影响要小得多。
• 可以通过查询INFORMATION_SCHEMA表来了解存储引擎的内部工作情况。
• 可以通过查询Performance Schema表来了解存储引擎的性能详细信息。

InnoDB存储表和索引有两种方式。

• 共享表空间，这种方式创建的表，表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中。
• 多表空间，这种方式创建的表，表结构仍然保存在.frm文件中，每个表的数据和索引单独保存在.ibd中。如果是分区表，每个分区对应单独的.ibd文件。

要使用多表空间方式，需要设置参数innodb_file_per_table=ON，并且重启服务生效，之后新创建的表将按照新方式保存，已存在的表还继续使用原来的方式。

root@database-one 01:13:  [gftest]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

root@database-one 01:13:  [gftest]> show variables like 'innodb_data_%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir  |                        |
+-----------------------+------------------------+
2 rows in set (0.01 sec)

可以看到，笔者测试环境使用的就是多表空间方式。创建表进行验证下。

root@database-one 01:13:  [gftest]> create table testinnodb(id int not null auto_increment,name varchar(10),primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.45 sec)

root@database-one 01:15:  [gftest]> exit
Bye
[root@database-one gftest]# cd /home/mysql/gftest/
[root@database-one gftest]# ls
db.opt        emp_copy.ibd  emp.ibd    myset.ibd  t1.ibd  t2.ibd  t6.frm  testdate.frm  testenum1.frm  testenum.frm  test.frm  testinnodb.frm  testnew.frm  t.frm  vc.frm
emp_copy.frm  emp.frm       myset.frm  t1.frm     t2.frm  t3.frm  t6.ibd  testdate.ibd  testenum1.ibd  testenum.ibd  test.ibd  testinnodb.ibd  testnew.ibd  t.ibd  vc.ibd
[root@database-one gftest]# ls -l testinnodb.*
-rw-r----- 1 mysql mysql  8586 2月  28 01:15 testinnodb.frm
-rw-r----- 1 mysql mysql 98304 2月  28 01:15 testinnodb.ibd
[root@database-one gftest]# 

InnoDB表的自动增长列可以人工插入，但是插入值如果是null或者0，则将实际插入自动增长的值。

root@database-one 01:20:  [gftest]> insert into testinnodb values(1,'wangjun'),(2,'liuyun'),(0,'xiaoli'),(null,'guojing');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

root@database-one 01:22:  [gftest]> select * from testinnodb;
+----+---------+
| id | name    |
+----+---------+
|  1 | wangjun |
|  2 | liuyun  |
|  3 | xiaoli  |
|  4 | guojing |
+----+---------+
4 rows in set (0.00 sec)

对于已经存在的表，要想知道其存储引擎，可以用show create table或show table status查看。

root@database-one 01:24:  [gftest]> show create table testinnodb \G
*************************** 1. row ***************************
       Table: testinnodb
Create Table: CREATE TABLE `testinnodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.03 sec)

root@database-one 01:25:  [gftest]> show table status like 'testinnodb' \G
*************************** 1. row ***************************
           Name: testinnodb
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 7
    Create_time: 2020-02-28 01:15:44
    Update_time: 2020-02-28 01:22:17
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.03 sec)