【Mysql】Mysql8存储引擎优化与锁和事务管理优化

最新推荐文章于 2024-05-20 14:31:07 发布

Coder加油!

最新推荐文章于 2024-05-20 14:31:07 发布

阅读量1.1k

点赞数 17

分类专栏： Mysql 运维自动化运维文章标签： mysql 数据库

本文链接：https://blog.csdn.net/benshu_001/article/details/138134761

版权

运维同时被 3 个专栏收录

94 篇文章 3 订阅

订阅专栏

自动化运维

66 篇文章 3 订阅

订阅专栏

Mysql

3 篇文章 0 订阅

订阅专栏

前一篇博文介绍了Mysql8优化的总体方向，这一篇我们就其中比较重点的内容存储引擎优化与锁和事务管理优化做重点讲解。

一、存储引擎优化

InnoDB 是 MySQL 默认的存储引擎，广泛用于生产环境中，因其支持事务处理、行级锁定和外键等特性而受到青睐。InnoDB 的优化关键在于提高数据处理效率和事务处理速度，确保数据的完整性和并发控制。我们可以从表空间管理、并发控制和缓冲池三个方面来进行优化，具体内容如下：

1. 表空间管理：使用文件每表模式 (`innodb_file_per_table`)

在 MySQL 中，InnoDB 存储引擎的表空间可以配置为两种模式之一：共享表空间模式（所有表数据和索引存储在一个或几个大文件中）和文件每表模式（每个表数据和索引存储在独立的文件中）。

优点：

管理和维护更简单：在文件每表模式中，每个表的数据和索引被存储在独立的文件中。这使得对单个表的备份和恢复变得简单，特别是在大型数据库环境中。
提高性能：当表特别大时，使用独立的文件可以减少文件系统的碎片，同时减少数据库在维护（如优化表或重建索引）时的 I/O 冲突。
更好的空间回收：删除表时，相关的文件可以被直接删除，立即回收空间，而共享表空间模式下，删除表只会在内部标记为空间可重用，并不直接回收。

缺点：

可能增加操作系统的文件数量：对于包含大量小表的数据库，每个表一个文件可能导致操作系统打开文件的数量急剧增加，这可能影响性能。

要启用文件每表模式，可以在 MySQL 配置文件（通常是 my.cnf 或 my.ini）中设置：

[mysqld]
innodb_file_per_table=1

确保在数据库的初始化或较早阶段设置此参数，因为它只影响之后创建的表。

2. 并发控制

innodb_thread_concurrency 和 innodb_concurrency_tickets 是两个与 InnoDB 并发性能相关的参数。

innodb_thread_concurrency：此参数定义了可以同时运行的线程数。设置为 0（默认值）表示没有限制。在系统线程竞争严重的情况下，限制此值有助于减少线程切换的开销，可能提高性能。
innodb_concurrency_tickets：此参数定义了在 InnoDB 内部进行线程切换之前，一个线程可以执行的操作数量。默认值是 5000。增加此值可以减少线程之间的切换，适用于查询需要大量内部操作的情况。

这些参数的调整需要根据具体的服务器配置和负载进行，通常需要通过压力测试来找到最优设置。

3. 缓冲池：调整 InnoDB 缓冲池的大小 (`innodb_buffer_pool_size`)

InnoDB 缓冲池是存储数据和索引的内存区域，其大小是影响 InnoDB 性能的最关键因素之一。

优点：

提高数据访问速度：较大的缓冲池可以存储更多的数据和索引，减少对磁盘的访问，从而加速数据检索。
减少磁盘 I/O：缓冲池中的数据修改（脏页）可以延迟写回到磁盘，集中处理 I/O 操作，减少磁盘 I/O。

设置建议：

通常建议将 innodb_buffer_pool_size 设置为总物理内存的 50%-80%，具体取决于服务器上运行的其他应用程序的内存需求。
在多核处理器上，还可以将缓冲池分为多个部分（通过 innodb_buffer_pool_instances），这样可以减少线程在访问缓冲池时的争用。

例如，如果服务器有 32GB 的 RAM，可以设置：

[mysqld]
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8

这个配置分配了 16GB 内存给 InnoDB 缓冲池，并将其分割为 8 个实例。分割缓冲池可以帮助减少多线程环境中的锁争用，提高并发访问的效率。

4. 缓冲池的细节优化

除了调整大小和实例数，对缓冲池的其他优化包括：
缓冲池预热（Buffer Pool Preloading）：MySQL 允许在服务器启动时加载热点数据到缓冲池，以避免服务器重启后性能下降。这可以通过设置 `innodb_buffer_pool_dump_at_shutdown` 和 `innodb_buffer_pool_load_at_startup` 参数实现。
脏页处理：调整 `innodb_max_dirty_pages_pct`（脏页占缓冲池的最大百分比）和 `innodb_io_capacity`（每秒可处理的 I/O 数）可以优化脏页的刷新策略，平衡系统的写入性能和数据的一致性。

5. 监控和调整

持续监控缓冲池的性能是确保数据库优化的关键。MySQL 提供了多个性能指标来监控缓冲池的状态，如：
- `Innodb_buffer_pool_read_requests`：缓冲池读取的请求数量。
- `Innodb_buffer_pool_reads`：直接从磁盘读取的请求数，这个值越低越好，因为高值意味着缓冲池命中率低。
- `Innodb_buffer_pool_pages_dirty`：缓冲池中脏页的数量，过多的脏页可能会影响性能。

通过分析这些指标，可以调整缓冲池的大小和策略，以适应数据库的实际工作负载。

6. 适应不同的工作负载

不同的应用和工作负载可能需要不同的缓冲池配置。例如，对于读密集型的应用，增加缓冲池的大小可以增加数据的命中率，从而提高查询性能；而对于写密集型的应用，可能需要更频繁地刷新脏页，以保证数据的及时性和减少恢复时间。

7. 使用工具进行调优

利用 MySQL Workbench 等工具可以帮助更容易地监控和调整 MySQL 的配置。这些工具通常提供图形界面来显示性能指标和简化配置过程。

通过这些策略，我们可以确保 InnoDB 存储引擎的缓冲池配置得当，从而最大化数据库的性能和效率。持续的监控和调整根据实际的应用需求和硬件变化是维持数据库性能的关键部分。

二、锁和事务管理优化

在 MySQL 中优化锁和事务管理是提高数据库性能和并发性的关键组成部分。正确的锁策略和事务控制可以显著减少冲突、防止数据不一致，并提高资源的利用效率。以下是一些针对 MySQL 锁和事务管理的优化策略：

1. 选择合适的事务隔离级别

MySQL 支持四种事务隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ（默认级别）、和 SERIALIZABLE。选择合适的隔离级别可以平衡性能和数据一致性的需求：

READ UNCOMMITTED：最低的隔离级别，允许读取未提交的数据（脏读），适用于对一致性要求不高的场景。
READ COMMITTED：允许读取和写入已提交的数据，适合大多数需要避免脏读和不可重复读的应用。
REPEATABLE READ：默认级别，确保在同一事务内读取的数据一致性，防止脏读和不可重复读，但可能发生幻读。
SERIALIZABLE：最高的隔离级别，通过锁定涉及的数据行来模拟事务串行执行，适用于需要极高数据一致性的场景。

调整隔离级别可以通过以下 SQL 命令实现：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2. 理解和优化锁

MySQL 主要使用两种类型的锁：共享锁（读锁）和排他锁（写锁）。优化锁的使用可以减少锁争用和死锁的发生：

避免不必要的锁定：确保事务尽可能短，避免长事务持有锁定时间过长。
使用索引：适当的索引可以减少锁的粒度，从而减少锁冲突。例如，确保 WHERE 子句中的条件列上有索引，可以避免全表扫描引发的锁扩展。
减少锁范围：尽量只锁定需要修改的数据行，而非整个表。

3. 死锁检测与解决

死锁是数据库管理中常见的问题，MySQL 提供了自动死锁检测和解决机制。了解和优化这些特性可以帮助快速解决死锁问题：

死锁日志：通过 SHOW ENGINE INNODB STATUS 命令查看死锁的详细信息，分析和优化导致死锁的查询。
配置死锁超时：通过设置 innodb_lock_wait_timeout 参数来决定事务等待锁的超时时间。

4. 使用乐观锁和悲观锁

根据应用的具体需求，选择乐观锁或悲观锁策略：

乐观锁：适用于冲突较少的环境，通过版本号或时间戳来判断数据在读取和写入期间是否被修改过。
悲观锁：适用于高冲突环境，事务开始时就锁定涉及的数据。

5. 监控和优化事务日志

事务日志是 MySQL 事务处理的重要组成部分，优化日志可以提高事务的处理效率：

6. 优化事务的设计和执行

7. 监控锁与事务性能

8. 应用程序层面的事务管理

最后，事务管理不仅仅是数据库层面的问题。应用程序的设计和逻辑也极大地影响事务的效率和效果：

通过综合考虑数据库配置、查询优化、事务设计和应用程序逻辑，可以有效地提高 MySQL 数据库的事务处理性能和并发处理能力。

适当配置日志文件大小：通过 innodb_log_file_size 设置合理的日志文件大小，以平衡性能和
恢复时间的需求。如果日志文件过小，可能会频繁地进行日志刷新和日志回滚操作，从而影响性能；而过大的日志文件可能增加系统崩溃后的恢复时间。
调整日志缓冲区大小：通过 innodb_log_buffer_size 设置日志缓冲区的大小，可以控制事务日志写入磁盘前在内存中的缓存量。增加日志缓冲区的大小可以减少对磁盘的写操作次数，从而提高事务处理的性能，特别是在高并发的写操作下。
控制日志写入和刷新频率：innodb_flush_log_at_trx_commit 参数控制事务日志的刷新行为。设置为 1 表示每次事务提交都会刷新日志到磁盘，这提供了最高的数据安全性但可能影响性能；设置为 2 表示日志只在每秒刷新到磁盘一次，提高了性能但在系统崩溃时可能丢失最近一秒的数据；设置为 0 表示日志只写入到日志缓冲区，依赖操作系统的缓存机制来刷新数据，这种方式提供最高的性能但数据安全性最低。
最小化事务作用范围：只在必要时开启事务，并尽可能缩短事务的持续时间和作用范围。这可以减少锁定资源的时间，从而减少锁冲突和提高并发性能。
批量操作优化：对于需要修改大量数据的操作，考虑分批次进行，每批次处理一部分数据并提交事务。这样可以避免长时间锁定大量数据，减少对其他事务的影响。
合理使用事务隔离级别：在确保数据一致性的前提下，选择尽可能低的事务隔离级别，以减少锁的使用和提高性能。例如，如果应用可以容忍不可重复读，可以选择 READ COMMITTED 而不是 REPEATABLE READ。
使用性能监控工具：利用 MySQL 的性能监控工具，如 Performance Schema，监控锁的使用情况和事务性能。这可以帮助识别高频发的锁冲突和长事务，以便进行针对性的优化。
分析和优化慢查询：通过慢查询日志分析长时间执行的查询，并优化这些查询或相应的数据库设计，以减少它们对事务性能的影响。
避免应用层的事务误用：例如，不必要的循环事务或错误的事务嵌套可以导致性能问题。确保开发团队了解正确的事务使用方法和最佳实践。
事务的错误处理：合理地处理事务中的错误和异常，确保在遇到错误时能够正确回滚事务，防止数据不一致或资源未正常释放。