美文网首页[翻译] MySQL 8.0 官方文档
[翻译] MySQL 之 InnoDB 特性和最佳实践

[翻译] MySQL 之 InnoDB 特性和最佳实践

作者: 猿来是八阿哥 | 来源:发表于2020-03-13 16:44 被阅读0次
    mysql

    官方文档:15.1 Introduction to InnoDB

    一、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)
      • 更新或删除主表中的数据,子表的更新和删除是由外键限制自动完成的。
      • 当直接插入子表时,外键限制会自动检查主表的主键是否存在,直接剔除不完整的插入。
    • 当磁盘、内存中的数据被破坏时,InnoDBchecksum 机制,会提醒你数据的不完整性。
    • 当你在数据库设计时,如果给每张表都设置了一个合适的主键 (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),这让 InnoDBblobtext 字段的存储和访问上更高效。
    • 可以通过查询 INFORMATION_SCHEMA 表,对 InnoDB 内部的工作进行监控。
    • 可以通过查询 Performance Schema 表,对 InnoDB 的性能细节进行监控。
    • 可以自由地在一个语句中混合 (mix) 使用 InnoDB 和其他存储引擎。例如:可以在一条语句中使用 join 合并 InnoDBmemory 存储引擎的数据。
    • 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 transactioncommit 包起来。虽然你不想太过频繁的提交事务,但你肯定也不想看到,运行了几个小时的 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; 的方式,制作一个备份表。
    • 当你的数据服务是一个集群时,要特别注意 masterslaves 的版本差异。

    相关文章

      网友评论

        本文标题:[翻译] MySQL 之 InnoDB 特性和最佳实践

        本文链接:https://www.haomeiwen.com/subject/iakdshtx.html