MySQL 存储引擎InnoDB最佳实践_数据库_开发者

️‍一、InnoDB存储引擎的特性

MySQL 存储引擎InnoDB最佳实践

DML操作遵循ACID模型，事务具有提交、回滚和崩溃恢复功能，以保护用户数据。事务和锁专题中介绍
如果发生意外而崩溃，无论当时数据库发生了什么，都不需要在重启数据库后执行任何特殊操作。InnoDB 的崩溃恢复功能会自动完成崩溃之前提交的更改，并撤消崩溃前正在进行但未提交的更改，从而允许我们从中断的地方继续执行。备份与恢复专题介绍
支持行级锁，提高了多用户的读取并发性和性能。事务和锁专题中介绍
InnoDB 存储引擎维护了一个自己的缓冲池，访问数据时在内存中缓存表和索引数据，对于经常使用的数据直接从内存中处理，大幅提升了效率。在专用数据库服务器上，通常会将高达 80%的物理内存分配给缓冲池。
InnoDB表优化了基于主键的查询，每个InnoDB表都有一个称为聚簇索引的主键索引，实现通过最少的磁盘I/0完成对主键的查找。索引专题中介绍
为了保持数据完整性，InnoDB 支持 FOREIGN KEY(外键)约束。在进行插入、更新和删除数据时确保相关表之间的一致性
当从表中反复查询相同的行时，自适应哈希索引会自动接管这些查询，此时查询效率和哈希表相同。

如何利用 InnoDB 的特性实现最佳的性能

为表中最频率查询的列(或多个列)指定主键(或复合主键)，如果没有明显的主键，则创建一个自增的列做为主键。
从多个表中根据相同的ID查询数据，建议使用表连接。可以在连接www.devze.com的列上定义外键，并在每个表中使用相同的数据类型声明这些列。添加外键可以确保被引用的列使用索引，从而提高性能。
在每秒提交数百次事务的服务器上，结合存储设备的写入速度，关闭事务的自动提交，通过系统变量 autocommit=0FF 设置。
把相关的DML操作用 START TRANSACTION 和 COMMIT 语句括在一起，分组为事务一起提交或回滚。
不要使用 LOCK TABLES 语句，InnoDB可以在不牺牲可靠性和高性能的情况下处理多个会话同时对一个表进行读写操作。

执行 SHOW ENGINES 语句查看可用的存储引擎时，查找 SUPPORT 列的值为 DEFAULT 的行

MySQL 存储引擎InnoDB最佳实践

使用 SELECT * FROM INFORMATION_SCHEMA.ENGINES \G 查看当前 MySQL 服务器支持的存储引擎及其详细信息的命令

MySQL 存储引擎InnoDB最佳实践

如果InnoDB不是默认的存储引擎，可以通过在命令⾏指定选项 --default-storageengine=InnoDB
或者在选项⽂件的 [mysqld] 节点定义 default-storageengine=InnoDB 并重新启动服务器来设置 InnoDB 存储引擎

MySQL 存储引擎InnoDB最佳实践

由于编程客栈业务实际需要，服务器默认存储引擎不是InnoDB时，想要创建一个InnoDB表，可以在使用 CREATE TABLE 语句创建表时明确指定 InnoDB 存储引擎，当然这样方式也可以指定其他任何支持的存储引擎

CREATE TABLE table_name (
	... 定义字段
) ENGINE = InnoDB; # 指定存储引擎

如果想测试使用其他存储引擎表中的数据在InnoDB表中的工作情况，在确保不影响原始表的情况下，使用以下方式创建一张InnoDB表

CREATE TABLE ... ENGINE=InnoDB AS SELECT * FROM other_engine_table;

# 选择⽬标数据库
use test_db
# 创建⼀个使⽤InnoDB存储引擎的表
CREATE TABLE t_innodb (
	id int(11) PRIMARY KEY AUTO_INCREMENT,
	name varchar(20)
) ENGINE = InnoDB;

当创建一个存储引擎为 InnoDb 的表时python，会在 data_dir/test_db 目录下生成一个用来存储真实数据的物理文件，命名格式为表名.ibd

以当前为例会在 /var/lib/mysql/test_db 目录下生成一个 t_innodb.ibd 的表空间数据文件

MySQL 存储引擎InnoDB最佳实践

表空间文件 t_innodb.ibd 用来存储表中的数据

在MySQL8.0中表结构的信息也保存在.ibd 文件中，可以使用 ibd2sdi 工具提取表定义的具体信息，使用方法: ibd2sdi --dump-file=t_innodb.txt t_innodb.ibd，生成的t_innodb.txt 文件中有对应表的具体描述
sid = Serialized DictionaryInformation 序列化字典信息和
8.0有所不同的是，在MySOL5.X及以前的版本中使用一个后缀为 .frm 的二进制文件来记录和描述表定义的信息

MySQL 存储引擎InnoDB最佳实践