【MySQL 数据宝典】【存储引擎】- InnoDB 常用参数的优化技巧

最新推荐文章于 2025-05-27 18:19:13 发布

黄金时代的架构之路

最新推荐文章于 2025-05-27 18:19:13 发布

阅读量1.6k

点赞数 33

分类专栏： MySQL 数据宝典文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/fvdfsdafdsafs/article/details/138153060

版权

本文聚焦MySQL数据库参数优化，涵盖内存、日志和IO线程三方面。内存参数涉及Buffer Pool、InnoDB缓存性能、Page管理和Change Buffer；日志参数包括缓冲区和文件配置；IO线程参数有query_cache_size等多个参数。给出各参数优化建议，助于提升数据库性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、参数优化

1.1 内存参数优化

1.1.1 Buffer Pool参数优化

缓冲池内存大小配置

一个大的日志缓冲区允许大量的事务在提交之前不写日志到磁盘。因此，如果你有很多事务的更新，插入或删除操作，通过设置这个参数会大量的减少磁盘I/O的次数数。
建议: 在专用数据库服务器上，可以将缓冲池大小设置为服务器物理内存的60% - 80%.

查看缓冲池大小

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

mysql> select 134217728 / 1024 / 1024;
+-------------------------+
| 134217728 / 1024 / 1024 |
+-------------------------+
|            128.00000000 |
+-------------------------+

在线调整InnoDB缓冲池大小
innodb_buffer_pool_size可以动态设置，允许在不重新启动服务器的情况下调整缓冲池的大小.

mysql> SET GLOBAL innodb_buffer_pool_size = 268435456; -- 512
Query OK, 0 rows affected (0.10 sec)

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+

监控在线调整缓冲池的进度

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------------------------+
| Variable_name                    | Value                                                        |
+----------------------------------+----------------------------------------------------------------------+
| Innodb_buffer_pool_resize_status | Size did not change (old size = new size = 268435456. Nothing to do. |
+----------------------------------+----------------------------------------------------------------------+

配置多个buffer pool实例

当buffer pool的大小是GB级别时，将一个buffer pool分割成几个独立的实例能降低多个线程同时读写缓存页的竞争性而提高并发性。
通过innodb_buffer_pool_instances参数可以调整实例个数。如果有多个实例，则缓存的数据页会随机放置到任意的实例中，且每个实例都有独立的buffer pool所有的特性。
buffer pool 可以存放多个 instance，每个instance由多个chunk组成。instance的数量范围和chunk的总数量范围分别为1-64，1-1000。
Innodb_buffer_pool_instances 的默认值是1，最大可以调整成64

mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+

chunk(块)大小配置

增大或减小缓冲池大小时，将以chunk的形式执行操作.chunk 大小由 innodb_buffer_pool_chunk_size 决定引入chunk 是为了方便在线修改缓冲池大小，修改时以 chunk 为单位拷贝 buffer pool。

mysql> show variables like 'innodb_buffer_pool_chunk_size';
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_buffer_pool_chunk_size | 134217728

最低0.47元/天解锁文章