一、MySQL 之 InnoDB 总体介绍
InnoDB
是 MySQL 中一个平衡了 高可用
和 搞性能
的通用存储引擎。在 MySQL 8.0 中,除非通过 my.cnf
配置了默认的存储引擎,或者在 CREATE TABLE
语句中显式的通过 ENGINE=xxx
指定了别的存储引擎,InnoDB
将作为 MySQL 的默认存储引擎。
1. InnoDB
的明显优势
- 在
InnoDB
的事务中,DML(Data Manipulation Language)
满足事务的 ACID 原则,支持事务的提交(commit)
和回滚(rollback)
功能,同时基于灾难恢复(crash-recovery)
的能力确保了用户数据的安全性。 - 更小粒度的
行锁
和 Oracle 风格的一致性读(consistent read)
保证了多用户访问时的并发能力,提高了整体性能。 -
InnoDB
在磁盘上的数据存储方式,对主键(primary keys)
查询做了优化。在每一个包含主键索引,准确地说是聚簇索引(clustered index)
,的InnoDB
表中,聚簇索引以一种使得主键查找 IO 次数最少
的方式在磁盘上存放数据。 -
InnoDB
支持外键(foreign key)
限制,进一步保证了数据的完整性(integrity)
。外键限制,在增、删、改时会验证多张表中的数据关联,能够避免不一致的数据产生。
2. InnoDB
具体特性
特性 | 是否支持 |
---|---|
B-Tree 索引 |
支持 |
server层的备份和事实恢复 (point-in-time recovery)
|
支持 |
数据库集群 | 不支持 |
聚簇索引(clustered index)
|
支持 |
压缩数据 | 支持 |
数据缓存 | 支持 |
server层的数据加密 | 支持 |
外键限制 | 支持 |
全文索引 (fulltext index)
|
5.6后支持 |
空间索引 (Geospatial index)
|
5.7后支持 |
Hash索引 | 不支持 |
索引缓存 | 支持 |
锁粒度 | 行 |
MVCC(Multi-Version Concurrency Controll) 多版本并发控制 |
支持 |
server层的复制(replication)
|
支持 |
存储限制 | 64TB |
T-Tree 索引 |
不支持 |
事务 | 支持 |
数据字典的更新统计 | 支持 |
二、表使用 InnoDB
的好处
表使用 InnoDB
存储引擎的好处有:
- 不管服务器由于软件、硬件问题宕机,无论当时 MySQL 上正在执行什么任务,你只需要重新启动 MySQL,
InnoDB
的灾难恢复(crash-recovery)
能力会自动帮你:把宕机时已提交的变更恢复回来
,把未提交的变更舍弃掉
,继续后续的工作,只需要重新启动就可以了。 -
InnoDB
会维护一个自己的内存缓存buffer pool
,把访问过的数据和索引缓存到内存中,而经常访问的数据是直接从内存中获取的。这个内存buffer pool
缓存缓存了很多数据,进一步提升InnoDB
的处理速度。在专门做数据库的服务器上,甚至经常会将80%的内存分配给这个buffer pool
。 - 如果你将相关的数据分开存储到了多张表中,
InnoDB
中的外键(foreign key)
限制将使你能保证数据的完整性(integrity)
。- 更新或删除主表中的数据,子表的更新和删除是由外键限制自动完成的。
- 当直接插入子表时,外键限制会自动检查主表的主键是否存在,直接剔除不完整的插入。
- 当磁盘、内存中的数据被破坏时,
InnoDB
的checksum
机制,会提醒你数据的不完整性。 - 当你在数据库设计时,如果给每张表都设置了一个合适的主键
(primary keys)
,那么涉及到这些主键(primary keys)
的操作都会自动优化,对于使用了主键(primary keys)
的where
order by
group by
join
都将非常快。 -
change buffering
机制会自动优化插入、更新、删除操作。InnoDB不仅允许对同一个表进行并发读写访问,还缓存更改后的数据以优化磁盘I/O。 - 性能的提升不仅仅是支持大表的重查询
(long-running queries)
,当频繁的从一个表获取相同数据时,自适应 hash 索引(Adaptive Hash Index)
将会缓存这样的查询结果,使得查询非常快,就像使用了 hash 索引的表一样。 - 支持多表和索引的压缩
- 使得创建、销毁索引对性能和可用性上的影响变得很小
- 对于清空一个单文件表空间
(file-per-table tablespace)
来说,变得更快。使得磁盘空间释放出来给操作系统用,而不是仅仅从 MySQL 的InnoDB
表空间中释放出来,给InnoDB
表用。 - 使用了动态行结构
(dynamic row format)
,这让InnoDB
对blob
和text
字段的存储和访问上更高效。 - 可以通过查询
INFORMATION_SCHEMA
表,对InnoDB
内部的工作进行监控。 - 可以通过查询
Performance Schema
表,对InnoDB
的性能细节进行监控。 - 可以自由地在一个语句中混合
(mix)
使用InnoDB
和其他存储引擎。例如:可以在一条语句中使用join
合并InnoDB
和memory
存储引擎的数据。 -
InnoDB
在处理大量数据的 CPU 运算上有很好的性能。 - 即使在一个文件最大 2GB 的操作系统上,
InnoDB
的表可以存储大量数据。
三、InnoDB
表的最佳实践
InnoDB
表的最佳实践包含:
- 为每个存在大量查询的表指定一个主键
(primary keys)
,或者选择一个自增类型的字段作为主键(primary keys)
。 - 基于每个表的 id 字段使用
join
操作。出于性能考虑,在 join 的字段上增加外键(foreign key)
限制,并且将每个表中的这个字段设置为同样的数据类型。增加外键(foreign key)
限制保证了不同表中的关联字段可以使用索引,同时使得删除和更新这些字段能影响到所有(有外键(foreign key)
限制的)表,还能保证子表和主表的数据完整性。 - 关闭事务的自动提交
(autocommit)
。每秒钟提交数百个事务将限制InnoDB
的性能。 - 将大量的 DML
(Data Manipulation Language)
语句,分组放到多个事务中,使用start transaction
和commit
包起来。虽然你不想太过频繁的提交事务,但你肯定也不想看到,运行了几个小时的insert
语句,最后没有提交。 - 不要使用
lock table
语句。InnoDB
在不牺牲可用性和高性能的前提下,支持多个会话(session)
同时对同一个表的并发读写。为了排他性(exclusive)
地访问一些行的写入操作,可以使用select ... for update
语句,单独只锁定你需要的行。 - 开启
innodb_file_per_table
选项或者使用普通表空间(general tablespace)
,使得每张表的数据和索引分开存储在不同的文件中,而不是使用系统表空间(system tablespace)
。innodb_file_per_table
默认是开启的。 - 评估是否你的数据、访问方式能从
InnoDB
的表、页(page)
的压缩功能中获益。InnoDB
在不牺牲读写能力的前提下,支持压缩功能。 - 如果在
create table
时使用engine=xxx
有问题的话,可以在启动 MySQL 服务时,通过--sql_mode=NO_ENGINE_SUBSTITUTION
选项禁止创建其他存储引擎的表。
四、确认 InnoDB
是否是默认的存储引擎
可以通过 show engines;
或者 SELECT * FROM INFORMATION_SCHEMA.ENGINES;
来查看 InnoDB
是否是默认的存储引擎。注意 InnoDB
那行的 default
字样。
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> select * from INFORMATION_SCHEMA.ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
五、找个表测试一下 InnoDB
- 如果你没有把
InnoDB
作为默认的存储引擎,可以通过启动参数中增加--default-storage-engine=InnoDB
或者在my.cnf
的[mysqld]
下增加default-storage-engine=innodb
的方式,来检验下你的数据服务、应用是否正常。 - 如果你的应用有依赖其他存储引擎的某个特性,将得到一个
add the ENGINE=other_engine_name clause to the CREATE TABLE statement to avoid the error.
的错误。 - 如果你想看一下某个表在
InnoDB
存储引擎下的具体表,可以通过ALTER TABLE table_name ENGINE=InnoDB;
的方式修改指定表使用的存储引擎。 - 如果你不想破坏原表、或者影响正在运行的应用,可以通过
CREATE TABLE table_xxx_innodb (...) ENGINE=InnoDB AS SELECT * FROM table_xxx;
的方式,制作一个备份表。 - 当你的数据服务是一个集群时,要特别注意
master
和slaves
的版本差异。
网友评论