摘要：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)
© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。