14.1 InnoDB简介
InnoDB是一个平衡了高可用和高性能的通用存储引擎.在Mysql 5.7中已经作为默认的存储引擎使用.
关键优势:
- DML操作遵循ACID原则,具有事务的提交,回滚和崩溃恢复能力
- 行级锁和Oracle风格的一致性读保证了多用户并发和性能
InnoDB在硬盘上基于主键来管理数据用来优化查询.每一个InnoDB表都有一个主键索引被称作聚簇索引来组织数据,在主键查找的时候可以最小化I/O.- 为了保持数据完整性,
InnoDB支持外键约束.在外键作用下,inserts,updates和deletes操作会被检查以确保在不同的表间不会出现数据不一致.
InnoDB存储引擎特性:
| 特性 | 是否支持 |
|---|---|
| B树索引 | 支持 |
| 备份/时间点恢复(在服务器实现而不是在存储引擎中) | 支持 |
| 集群数据库支持 | 不支持 |
| 聚簇索引 | 支持 |
| 压缩数据 | 支持 |
| 数据缓存 | 支持 |
| 数据加密 | 支持(通过在服务器中的加密功能实现;在MySQL5.7之后支持数据静态表空间加密) |
| 外键支持 | 支持 |
| 全文搜索索引 | 支持(InnoDB在MySQL5.6之后支持) |
| 地理空间数据类型支持 | 支持 |
| 地理空间索引支持 | MySQL5.7之后支持 |
| Hash索引 | 不支持 |
| 索引缓存 | 支持 |
| 锁粒度 | 行级 |
| MVCC(并发控制) | 支持 |
| 复制支持(主从) | 支持 |
| 存储限制 | 64TB |
| T树索引 | 不支持 |
| 事务 | 支持 |
| 更新数据字典统计信息 | 支持 |
14.1.1 使用InnoDB表的优势
- 如果你的服务器因为硬件或软件原因崩溃了,不管那时候数据库发生了什么,在重启数据库之后你不需要做任何特殊操作.InnoDB的崩溃恢复机制会自动完成崩溃之前的一切变动并且撤销没有被提交的改动.
- InnoDB存储引擎维护自己的缓存池,在数据被访问的时候缓存表和索引数据到主存中.频繁使用的数据会直接从内存中读取.此缓存支持多种类型的信息并加快处理过程.在专用的数据库服务器上,通常将80%的物理内存分配给缓存池.
- 如果你把有关联的数据拆分到不同的表中,你可以设置外键(foreign keys)来确保引用完整性.更新或删除数据时,其他表中相关的数据会被自动地更新或删除.如果尝试在辅助表中插入数据而主表中没有相对应的数据在主表中,“坏”数据会被自动”踢”出.
- 如果数据在磁盘或内存中损坏,警告机制会在你使用问题数据之前发出警告.
- 当你在设计数据库的时候为每张表使用了合适的主键,涉及到这些列的操作会被自动地优化.在where子句,order by子句,group by子句和join操作里引用主键是非常快的.
- inserts,updates和deletes操作会被一个叫做
change buffering的机制自动组织.InnoDB不仅允许对同一张表的并发读写,还会缓存更改的数据来优化磁盘I/O. - 性能优势不仅限于对巨型表的长时间查询.当相同的行从一张表中被一遍遍访问的时候,一个叫做
Adaptive Hash Index的特性会接手来使得这种查找变得更快,就好像是从一张Hash表中读出来的. - 你可以压缩表和相关联的索引
- 你可以创建和删除索引,这对性能和可用性的影响小得多.
- 截断每个表的文件表空间非常快并且可以释放磁盘空间以供操作系统重用,而不是释放系统表空间中只有InnoDB才能重用的空间.
- 使用
DYNAMIC行格式之后,对BLOB和长文本字段的存储布局效率更好了. - 你可以通过查询
INFORMATION_SCHEMA表监控存储引擎的内部工作情况. - 你可以通过查询
Performance Schema表监控存储引擎的性能细节. - 你可以将InnoDB表与其他MySQL存储引擎中的表自由混合,甚至在同一个语句中.
- InnoDB为处理大量数据时的CPU效率和最强性能设计.
- InnoDB表可以处理大量数据,甚至在操作系统的文件大小限制为2GB的情况下.
14.1.2 InnoDB表最佳实践
- 使用查询频率最高的列或几列为每个表指定主键,没有明显主键就使用一个自增值
- 从多表中基于他们相同的ID值提取数据的时候使用joins.为了最快的join性能,在join的列上定义主键并且在每个表中为这些列声明相同的数据类型.添加外键确保被引用的列被索引来提高性能.外键也会传播删除或更新操作到所有受影响的表,并且防止在父表没有对应ID的情况下向子表插入数据.
- 关闭自动提交(autocommit).每秒提交上百次会影响性能(受限于你的存储设备的写速度).
- 把相关的DML操作组织成事务,用
START TRANSACTION和COMMIT语句包围它们.虽然你不想提交得太频繁,但是你也不希望发出大量的INSERT,UPDATE或DELETE语句运行好几个小时而不提交. - 不要使用LOCK TABLES语句.InnoDB可以在不牺牲可靠性和高性能的情况下处理多个会话对一张表的同时读和写.要获取对一些行的独占的写权限,使用
SELECT ... FOR UPDATE语法来锁住你想要更新的行. - 启用
innodb_file_per_table选项或者使用通用表空间来放置表的数据和索引到单独的文件中来代替系统表空间(system tablespace).innodb_file_per_table选项默认启用. - 评估你的数据和访问模式是否受益于InnoDB表或页面压缩功能,你可以在不牺牲读/写功能的情况下压缩InnoDB表.
- 使用
--sql_mode=NO_ENGINE_SUBSTITUTION选项启动你的服务器来防止CREATE TABLE的ENGINE=子句存在问题的时候使用不同的存储引擎创建表.
14.1.3 确认InnoDB是默认的存储引擎
mysql> SHOW ENGINES;mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES;
14.1.5 关闭InnoDB
Oracle建议将InnoDB作为典型数据库应用程序的首选存储引擎,从运行在本地系统的单用户wiki和博客,到推到性能极限的高端应用程序.在MySQL 5.7中,InnoDB是新表的默认存储引擎.