MySQL - InnoDB 存储引擎优化 及 事务优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL - InnoDB 存储引擎优化 及 事务优化
     InnoDB 存储引擎和 MyISAM 存储引擎最大区别主要有 4点:
     1. 缓存机制;
     2. 事务支持;
     3. 锁定实现;
     4. 数据存储方式差异;

      修改表引擎:
     mysql> ALTER TABLE mytable ENGIN = INNODB:


     InnoDB 缓存相关优化:
  • innodb_buffer_pool_size 的合适设置
    • InnoDB 存储引擎的缓存机制和 MyISAM 的最大区别就在于 InnoDB 不仅仅缓存索引,同时还会缓存实际的数据。所以, InnoDB 存储引擎可以使用更多的内存来缓存数据库的相关信息。
    • innodb_buffer_pool_size 参数用来设置 InnoDB 最主要的 Buffer(InnoDB Buffer Pool)的大小,缓存用户表及索引数据的最主要缓存空间,对 InnoDB 整体性能影响也最大。
    • InnoDB 的 Buffer Pool 简单设置为整个系统物理内存的 50%~80%之间,具体设置多大要根据实际环境技术,例如一台单独 MySQL 主机, 物理内存 8G, MySQL 最大连接数为 500,同事还是用了 MyISAM 存储引擎,这时整体内存分配如下:
      • 系统使用,假设预留 800MB;
      • 线程独享,最大约为 2GB = 500 * (1MB + 1MB + 1MB + 512KB + 512KB),组成,(sort_buffer_size: 1MB、join_buffer_size:1MB、read_buffer_size:1MB、read_rnd_buffer_size:512KB、thread_statck:512KB)
      • MyISAM Key Cache:假设大概 1.5GB。
      • InnoDB Buffer Pool 最大可用: 8GB - 800MB - 2GB - 1.5GB = 3.7 GB
    • 生产环境 InnoDB 存储引擎哟啊根据 Buffer Pool 实时信息作出进一步分析。
                a3ad6c645bbcd23f33205282f8e74df7890b3dd8
    • 上图可看出 总共有 512 pages, 有 163 个是 Free 状态, 有 349 个 page 有数据, read 请求 4087次,其中有 316次请求使用物理磁盘获取,InnoDB Buffer Pool 的 Read 命中率大概:  (4087-316)/ 4087 * 100% = 92.26%
    • Innodb_buffer_pool_read_ahead_rnd:记录进行随机读的时候产生的预读次数;
    • Innodb_buffer_pool_read_ahead_seq:记录连续读的时候产生的预读次数;
    • 预读 -- 在一些高端存储才会有,简单来说,就是通过分析数据请求的特点来自动判断客户端在请求当前数据块之后可能会继续请求的数据块。通过该自动判断,存储引擎可能会一次性将当前请求的数据库和后面可能请求的下一个(或者几个)数据库全部读出,以期望通过这种方式减少磁盘 IO 次数,提高 IO  性能 。
  • innodb_log_buffer_size 参数的使用
    • 设置 InnoDB 的 Log Buffer 大小,系统默认 1MB。 Log Buffer 的主要作用就是 缓冲 Log 数据,提高写 Log 的 IO 性能。一般来说,如果系统不是写负载非常高而且以大事务居多的话, 8MB 以内大小足够。
    • 也可以通过 系统状态 参数提供的性能统计数据来分析 Log 的使用情况
            4f80a300d0929e5336252b783661dc9422d1eeeb
  • innodb_additional_mem_pool_size 参数理解
    • 设置用户存放 InnoDB 的字典信息和其他内部结构所需要的内存空间。所以, InnoDB 表越多,需要的空间自然也就越大,系统默认 1MB。
    • 一个常规的 几百个 InnoDB 表的 MySQL ,如果不是每个表都是上百个字段, 20MB 内存已经足够了。
    • 该参数对系统整体性能并无太大影响,设置超过实际所需存储,只是浪费内存而已。

      Double Write Buffer 
          Double Write Buffer 是 InnoDB 所使用的一种较为独特的文件 Flush 实现技术,主要作用是在减少文件同步次数提高 IO 性能的情况下,提高系统崩溃(Crash)或断电情况下的安全性,避免写入的数据不完整。

      Adaptive Hash Index
          Adaptive Hash index 的目的并不是为了改善磁盘 IO 性能,而是为了提高 Buffer Pool 中的数据访问效率,也就是给 Buffer Pool中的数据做索引。

     
      事务优化
    
   脏读:指当一个事物正在访问数据,并且对数据进行修改,而这种修改没有提交到数据库中,这时,另外一个事物也访问这个数据,然后使用了这个数据。
    不可重复读:指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据不一样,因此成为不可重复读。
    幻读:指当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改了这个表中的数据,如想表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还没有修改的数据行,就好像发生了幻觉一样。

   选择合适的事务隔离级别
  • READ UNCOMMITTED
    • 常被称为 Dirty Reads(脏读),事务上的最低隔离级别。
  • READ COMMITTED
    • 这一隔离级别下不会出现 Dirty Reads(脏读),但是可能出现 Non-Repeatable Reads(不可重复读)和 Phantom Reads(幻读)
  • REPEATABLE READ
    • 是 InnoDB默认的事务隔离级别。 这一隔离级别下不会垂涎 Dirty Reads,也不会出现 Non-Repeateable Reads,但可能会出现 Phantom Reads。
  • SERIALIZABLE
    • 标准事务隔离级别中的最高级。这一隔离级别下, Phantom Reads 也不会出现。
          对于高并发应用来说,为了尽可能保证数据的一致性,避免并发可能带来的数据不一致,自然是事务隔离级别越高越好。但是,对于 InnoDB 来说,所使用的事务隔离级别越高,实现复杂度自然就会更高,所要做的事情也会更高,整体性能也就更差。虽然 InnoDB 存储引擎默认的事务隔离级别是 REPEATABLE READ,但实际上在大部分应用中,只需要 READ COMMITED 的事务隔离级别就可以满足需求了。

     事务与 IO 的关系及优化
     
          InnoDB 修改数据操作,实际上修改的是Buffer Pool中的数据,并不是一个事务提交后就将 Buffer Pool 中被修改的数据同步到磁盘上,而是通过记录到事务日志中,在连续写入磁盘。

     控制 InnoDB 事务日志刷新方式参数: innodb_flush_log_at_trx_commit :
  • innodb_flush_log_at_trx_commit  = 0, InnoDB 中的 Log Thread 每隔 1秒将 log buffer 中的数据写入文件,同时还会通知文件系统进行与文件同步的 flush操作,保证数据确实已经写入磁盘。
  • innodb_flush_log_at_trx_commit = 1, InnoDB 默认设置。每次事务的结束都会出发 Log Thread 将 Log Buffer 中的数据写入文件、并通知文件系统同步文件。这个设置最安全,能够保证不论是 MySQL 崩溃、OS崩溃还是主机断电都不会丢失任何已经提交的数据。
  • innodb_flush_log_at_trx_commit = 2, 每次事务结束的时候将数据写入事务日志,仅仅是调用了文件系统的文件写入操作。而文件系统都是有缓存机制的,所以 Log Thread 的写入并不能保证内容已经写入到物理磁盘完成持久化的动作。文件系统什么时候会将缓存中的数据同步到物理磁盘、文件, Log Thread 就完全不知道,所以,当设置 2 的时候, MySQL 崩溃并不会造成数据的丢失,但是 OS 崩溃或主机断电后可能丢失的数据量就完全控制在文件上了。
          以上分析,设置1 最安全,由于 IO 同步操作多,所以,性能最低。设置 0 ,则每秒一次同步,性能相对高一下。如果设置 2 ,性能可能是三种最好的。但是也可能出现故障后丢失数据最多的一种。如果 OS 足够稳定,主键硬件设备足够好,且主机断电系统而已足够安全,可将设置为2 ,让系统整体性能尽可能高。  【 建议设置为 2

      修改回话的隔离级别:
          mysql> SET  SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;

      数据存储优化

     理解 InnoDB 数据及索引文件存储格式
  • Page
    • InnoDB 存储引擎中的所有数据,不论是表还是索引,或是存储引擎自己的各种结构,都以 page 作为最小物理单位来存放,每个 page 默认大小 16KB。
  • extent
    • extend 是多个连续 page 组成的一个物理存储单位。一般来说,每个 extend 为 64 个page。
  • segment
    • segment 在 InnoDB 粗才能引擎中实际上也代表 “ file",每个 segment 由一个或多个 extent 组成,而且每个 segment 都存放同一种数据。一般来说,每个表数据会存放于一个单独的 segment 中,实际上也就是每个 聚簇索引会存放一个单独的 segment 中。
  • tablespace
    • InnoDB中最大的物理结构单位,由多个 segment 组成。
     查看 InnoDB 表空间的使用情况:
      aa3659ff25b152073ae284d3a4fb199033073b0e
          

     对性能有较大影响的 4 个要点:
  • 为了尽量减小 secondary index 的大小,提高访问效率,作为主键的字段所占用的存储空间越小越好,最好是 INTEGER 类型。当然这并不是绝对的,字符串类型的数据同样可以作为 InnoDB表的主键。
  • 创建表的时候尽量自己制定相关的主键,让数据按照预设的顺序排序存放,以提高特定条件下的访问效率。
  • 尽可能不要在主键上进行更新操作,减少因为主键值的变化带来的数据的移动。
  • 尽可能提供后主键条件进行查询。
     分散IO 提升磁盘相应:
          由于 InnoDB 和其他非事务存储引擎相比,在记录数据文件的同事还记录相应的事务日志(Transaction Log), 相当于增加了整体的 IO 量。虽然事务日志是以完全顺序的方式写入磁盘,但总是会有一定的IO消耗,所以对没有使用REID 的存储系统来说,建议将数据文件和事务日志文件分别存放于不同的物理磁盘以降低磁盘的相互争用,提高整体 IO 性能。
  • innodb_log_group_home_dir:指定InnnDB 日志存放位置;
  • innodb_data_home_dir : 指定 InnoDB 数据文件存放位置;
  • innodb_autoextend_increment:设置自行控制表空间文件每次增加的大小;


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
MySQL底层概述—9.ACID与事务
本文介绍了数据库事务的ACID特性(原子性、一致性、隔离性、持久性),以及事务控制的演进过程,包括排队、排它锁、读写锁和MVCC(多版本并发控制)。文章详细解释了每个特性的含义及其在MySQL中的实现方式,并探讨了事务隔离级别的类型及其实现机制。重点内容包括:ACID特性(原子性、持久性、隔离性和一致性的定义及其实现方式)、事务控制演进(从简单的全局排队到复杂的MVCC,逐步提升并发性能)、MVCC机制(通过undo log多版本链和Read View实现高效并发控制)、事务隔离级别(析了四种隔离级别(读未提交、读已提交、可重复读、可串行化)的特点及适用场景)、隔离级别与锁的关系。
MySQL底层概述—10.InnoDB锁机制
本文介绍了:锁概述、锁分类、全局锁实战、表级锁(偏读)实战、行级锁升级表级锁实战、间隙锁实战、临键锁实战、幻读演示和解决、行级锁(偏写)优化建议、乐观锁实战、行锁原理分析、死锁与解决方案
107 24
MySQL底层概述—10.InnoDB锁机制
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
103 22
MySQL底层概述—8.JOIN排序索引优化
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
122 15
MySQL底层概述—7.优化原则及慢查询
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分,包括表空间、段、区、页和行。表空间是最高逻辑层,包含多个段;段由若干个区组成,每个区包含64个连续的页,页用于存储多条行记录。文章还详细解析了Page结构,分为通用部分(文件头与文件尾)、数据记录部分和页目录部分。此外,文中探讨了行记录格式,包括四种行格式(Redundant、Compact、Dynamic和Compressed),重点介绍了Compact行记录格式及其溢出机制。最后,文章解释了不同行格式的特点及应用场景,帮助理解InnoDB存储引擎的工作原理。
MySQL底层概述—4.InnoDB数据文件
MySQL底层概述—3.InnoDB线程模型
InnoDB存储引擎采用多线程模型,包含多个后台线程以处理不同任务。主要线程包括:IO Thread负责读写数据页和日志;Purge Thread回收已提交事务的undo日志;Page Cleaner Thread刷新脏页并清理redo日志;Master Thread调度其他线程,定时刷新脏页、回收undo日志、写入redo日志和合并写缓冲。各线程协同工作,确保数据一致性和高效性能。
MySQL底层概述—3.InnoDB线程模型
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
43 9
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
90 9
MYSQL支持的存储引擎有哪些, 有什么区别
MYSQL存储引擎有很多, 常用的就二种 : MyISAM和InnerDB , 者两种存储引擎的区别 ; ● MyISAM支持256TB的数据存储 , InnerDB只支持64TB的数据存储 ● MyISAM 不支持事务 , InnerDB支持事务 ● MyISAM 不支持外键 , InnerDB支持外键