innodb_buffer_pool_size在线缩小操作

最新推荐文章于 2025-03-14 04:09:26 发布
最新推荐文章于 2025-03-14 04:09:26 发布
阅读量319 收藏
点赞数 2
分类专栏： MySQL 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/halou90/article/details/141506994
版权

一、背景

        测试数据库内存32G，只有MySQL数据库，但是innodb_buffer_pool_size设置了24G，导致经常出现lack of memory问题、lack of swap问题。

        因为使用了MySQL5.7.36版本，利用innodb_buffer_pool_size参数值可在线调整的新特性，尝试修改。

二、innodb_buffer_pool_size在线缩小实验

        2.1、不能直接将一个值赋予innodb_buffer_pool_size

        2.2需要使用相乘的方法修改。

在线缩小innodb_buffer_pool_size完成。

《MySQL DBA： InnoDB_doublewrite/ChangeBuffer/AHI/FNP》
yexiang优快云的专栏
05-26 257
dd
调整MySQL InnoDB缓冲池大小的方法（无需重启MySQL进程）
BjmmVue的博客
09-26 754
在MySQL数据库中，InnoDB是一种常用的存储引擎，它使用缓冲池（Buffer Pool）来提高读取和写入性能。通过调整InnoDB缓冲池的大小，我们可以优化系统的性能。至此，您已经成功地在线调整了MySQL InnoDB缓冲池的大小，而无需重启MySQL进程。请注意，在高负载环境中进行缓冲池大小的调整可能会对数据库性能产生一定影响。替换为您希望设置的新的缓冲池大小。在调整InnoDB缓冲池大小之前，我们需要获取当前的缓冲池大小。希望本文对您有所帮助！确保新的缓冲池大小与您在第2步中设置的值相匹配。
MySQL 8.0中InnoDB buffer pool size进度更透明
老叶茶馆
02-05 444
MySQL 8.0 up up up~从MySQL 5.7开始，支持在线动态调整 innodb buffer pool，并为此新增了一个状态变量 Innodb_buffer_pool_resize_status，可以通过观察它了解调整buffer pool过程中的一些状态，例如 Resizing also other hash tables. 或 Completed resizing buffer...
mysql参数之innodb_buffer_pool_size大小设置
ciliting2867的博客
08-24 242
该参数定义了数据缓冲区buffer pool大小，类似于oracle的db_cache_size mysql> show global variables like 'innodb_buffer_pool_size'...
MySQL · 特性分析 · innodb_buffer_pool_size在线修改
weixin_34200628的博客
03-22 824
InnoDB Buffer Pool缓存了表数据和二级索引在内存中，提高数据库效率，因此设置innodb_buffer_pool_size到合理数值对实例性能影响很大。当size设置偏小，会导致数据库大量直接磁盘的访问，而设置过大会导致实例占用内存太多，容易发生OOM。在MySQL 5.7之前innodb_buffer_pool_size的修改需要重启实...
MySQL 5.7在线调整innodb buffer pool size操作
Andy12306的博客
08-02 308
【代码】MySQL 5.7在线调整innodb buffer pool size操作。
MySQL 的 innodb_buffer_pool_size 参数配置指南
最新发布
HONEY MOOSE
03-14 1254
是MySQL InnoDB存储引擎最重要的参数之一，它直接影响数据库的性能。这个参数决定了InnoDB缓存表数据和索引数据的内存大小。
浅析在线调整 innodb_buffer_pool_size
weixin_42050250的博客
05-01 2199
浅析在线调整 innodb_buffer_pool_size 作者：zhou mysql版本：5.7 先介绍一下 buffer pool: 在innodb存储引擎中数据访问以page为单位，page也是innodb管理数据库的最小磁盘单位，每个page的默认大小为16KB(可以通过参数innodb_page_size进行调整，在5.7增加了对32KB和64KB的大小支持，在此之前的版本支持4KB...
在线调整InnoDB Buffer Pool Size
diyoumie0391的博客
03-20 361
InnoDB Buffer Pool主要是用来缓存数据表和索引数据的内存区域,它的默认值为134217728字节(128MB)。最大值取决于CPU架构;32位系统上的最大值为4294967295(232-1)，64位系统上的最大值为18446744073709551615(264-1)。在32位系统上，CPU体系结构和操作系统的实际最大大小可能低于标准的最大大小。...
MySQL设置innodb_buffer_pool_size（InnoDB缓冲池Buffer Pool）大小
学亮编程手记
10-04 3147
在MySQL中，InnoDB存储引擎使用缓冲池（Buffer Pool）来缓存表数据和索引数据，以提高数据库的性能。设置合适的缓冲池大小对于优化MySQL性能至关重要。
MySQL5.7.5X在线调整innodb_buffer_pool_size参数
weixin_39802217的博客
03-27 1251
MySQL5.7.5以前，调整innodb_buffer_pool_size需要重启mysql进程才可以生效，建议业务低峰时间执行，在MySQL 5.7.5版本后，innodb_buffer_pool_size参数的值可以动态的设置，可以通过innodb_buffer_pool_chunk_size参数配置块的大小，Innodb_buffer_pool_resize_status状态变量记录了从调...
mysql5.7动态调整buffer pool
cdrcsy的专栏
09-19 2686
块更改：InnoDB缓冲池大小调整 自MySQL 5.7.5以来，我们已经能够动态调整InnoDB缓冲池的大小。这个新功能还引入了一个新变量 - innodb_buffer_pool_chunk_size - 它定义了缓冲池被放大或缩小的块大小。此变量不是动态的，如果配置不正确，可能会导致不希望出现的情况。 先来看看innodb_buffer_pool_size，innodb_buffer_p...
mysql之innodb存储引擎
givenchy_yzl的博客
07-12 1020
innodb存储引擎 一、mysql组织架构 根据以上结构，详解如下 1、连接层 1.验证用户的身份，用户名密码是否匹配 2.提供两种连接方式（TCP/IP连接、socket连接） 3.连接层提供了一个与sql层交互的线程 2、SQL层 1.接收连接层传过来的SQL语句 2.验证执行的SQL语法 3.验证SQL的语义（DDL,DML,DQL,DCL） 4.解析器：解析SQL语句，生成执行计划 5.优化器：将解析器传来的执行计划选择最优的一条执行 6.执行器：将最优的一条执行 6.1 与存储引擎层建立交互的
MySQL5.7 常见调优参数速查
fangmeng1997的博客
09-30 1016
max_connections：允许客户端并发连接的最大数量，默认值是151，一般将该参数设置为500-2000 max_connect_errors：如果客户端尝试连接的错误数量超过这个参数设置的值，则服务器不再接受新的客户端连接。可以通过清空主机的缓存来解除服务器的这种阻止新连接的状态，通过FLUSH HOSTS或mysqladmin flush-hosts命令来清空缓存。这个参数的默认值是100，一般将该参数设置为100000。 interactive_timeout：Mysql关闭交互连接前的等待时
mysql缓存(innodb_buffer_pool_size)在线调整细说
congshe1570的博客
11-02 1365
5.7以后innodb内存(innodb_buffer_pool_size)可以在线调整.这对于dba来说.挺不错的,但调整需要注意: 不是随意调小或者调大的,是遵循一定规则的.在此,5.7引入了innodb_buf...
推荐在线设置 innodb_buffer_pool_size
wang_xingg的专栏
12-30 407
set global innodb_buffer_pool_size=1572864000,原来缓冲的数据文件不会丢失
mysql innodb_buffer_pool_size 修改
dale
04-06 2498
mysql innodb_buffer_pool_size 修改
mysql SELECT * FROM `th_prokey` WHERE `passage_id` = 2 AND `lunxun_status` = 1 AND `in_type` = 2 AND `user_id` = 7 查询大数据2秒多 如何优化
02-18
<think>好的，用户现在有一个MySQL查询需要优化，执行时间超过2秒，查询语句是SELECT * FROM `th_prokey` WHERE `passage_id` = 2 AND `lunxun_status` = 1 AND `in_type` = 2 AND `user_id` = 7。首先，我需要分析可能的原因和解决方案。 首先，用户提到查询大数据用了2秒多，说明数据量可能比较大。首先应该检查是否有合适的索引。用户的条件是四个字段：passage_id、lunxun_status、in_type、user_id，都是等值查询。所以，组合索引可能有效。需要确认这四个字段是否有单独的索引，或者是否存在一个包含这些字段的组合索引。通常来说，组合索引的顺序很重要，应该将选择性高的字段放在前面。比如，如果user_id的选择性更高，那么放在前面可以减少扫描的行数。 接下来，可能需要查看执行计划，使用EXPLAIN来查看查询是否使用了正确的索引，以及是否有全表扫描的情况。如果发现没有使用索引或者使用了不合适的索引，就需要调整索引策略。 另外，如果表中的数据量确实非常大，即使有索引，也可能需要进一步优化。比如，确认索引的选择性是否足够高。如果某些字段的值重复率很高，索引的效果可能不佳。这时候可能需要考虑其他优化手段，比如分区表，或者分库分表，但分区表需要MySQL版本支持，并且需要根据业务需求来决定。 然后，考虑查询是否需要返回所有字段。如果用户使用的是SELECT *，而实际只需要部分字段，可以改为只查询需要的字段，减少数据传输的开销，虽然这可能对查询时间影响不大，但也能提升性能。 另外，检查表的结构，看看字段的数据类型是否合适。比如，如果某些字段是VARCHAR但存储的是数字，可能会影响索引的效率。或者，是否有不必要的字段导致行宽过大，影响存储引擎的读取效率。 还要考虑服务器的配置，比如InnoDB缓冲池的大小是否足够，如果缓冲池太小，无法缓存常用数据，会导致磁盘IO增加，影响查询速度。不过这可能属于硬件或配置层面的优化，用户可能更关注查询本身的优化。 如果索引已经优化，但查询仍然慢，可能需要考虑是否有锁竞争的情况，或者事务隔离级别导致的问题。但根据用户的描述，可能主要是查询本身的问题，而不是并发问题。 另外，如果数据量持续增长，可能需要定期清理旧数据，或者使用归档表来减少主表的数据量。不过这也需要根据业务需求来定。 总结下来，主要的优化步骤应该是：检查并添加合适的组合索引，分析执行计划，调整查询语句，优化表结构和服务器配置。其中，添加组合索引可能是最直接有效的方法。需要建议用户使用EXPLAIN分析查询，并根据结果调整索引。针对</think> SQL 查询 `SELECT * FROM th_prokey WHERE passage_id=2 AND lunxun_status=1 AND in_type=2 AND user_id=7` 的优化，以下是系统性解决方案： --- ### **1. 索引优化（核心手段）** #### **现状分析** - 查询条件涉及 4 个字段：`passage_id`, `lunxun_status`, `in_type`, `user_id` - 若未建立复合索引，单字段索引可能无法高效过滤数据，导致全表扫描 #### **优化方案** - **创建复合索引**（关键步骤）： ```sql ALTER TABLE th_prokey ADD INDEX idx_compound (user_id, passage_id, in_type, lunxun_status); ``` **索引设计原理**： 1. **最左前缀原则**：将高区分度字段（如 `user_id`）放在最左，快速缩小数据范围 2. **覆盖索引**：若查询仅需索引字段，可避免回表（但 `SELECT *` 需回表） 3. 4 个条件字段全放入索引，确保索引覆盖 WHERE 子句 - **替代方案**（可选）： ```sql -- 若 user_id 过滤性极高，可单独索引 ALTER TABLE th_prokey ADD INDEX idx_user (user_id); -- 配合其他字段索引 ALTER TABLE th_prokey ADD INDEX idx_passage (passage_id); ``` --- ### **2. 执行计划验证** 使用 `EXPLAIN` 确认索引是否生效： ```sql EXPLAIN SELECT * FROM th_prokey WHERE passage_id=2 AND lunxun_status=1 AND in_type=2 AND user_id=7; ``` **关键指标检查**： - `type`: 应为 `ref` 或 `range`（若为 `ALL` 表示全表扫描） - `key`: 显示实际使用的索引名称 - `rows`: 预估扫描行数（应明显小于表总行数） --- ### **3. 查询语句优化** - **避免 `SELECT *`** 明确指定所需字段，减少数据传输和回表开销： ```sql SELECT field1, field2 FROM th_prokey WHERE ...; ``` - **强制索引（调试用）** 若优化器未选择最优索引，可手动指定： ```sql SELECT * FROM th_prokey FORCE INDEX (idx_compound) -- 强制使用复合索引 WHERE ...; ``` --- ### **4. 表结构优化** - **字段类型检查** 确保 `WHERE` 条件字段类型与比较值类型一致： ```sql SHOW CREATE TABLE th_prokey; -- 检查字段类型 ``` - **数据分区**（适用于超大表） 按时间或 `user_id` 分区，减少单次查询范围： ```sql ALTER TABLE th_prokey PARTITION BY HASH(user_id) PARTITIONS 10; ``` --- ### **5. 服务器配置调优** - **调整 InnoDB 缓冲池** 确保 `innodb_buffer_pool_size` 足够缓存热数据（建议设置为物理内存的 70%~80%） ```ini # my.cnf innodb_buffer_pool_size = 4G ``` - **优化查询缓存** 若数据更新不频繁，可启用查询缓存（注意 MySQL 8.0 已移除该功能）： ```ini query_cache_type = 1 query_cache_size = 64M ``` --- ### **6. 进阶优化策略** - **读写分离** 将查询路由到只读副本，降低主库压力 - **异步批处理** 若业务允许，合并多次查询为批量查询（如缓存用户近期数据） - **冷热数据分离** 将历史数据归档到归档表，保持主表数据量可控 --- ### **性能对比示例** | 优化阶段 | 预估扫描行数 | 执行时间 | |--|----------| | 无索引（全表扫描）| 1,000,000 | 2.3s | | 单字段索引 | 50,000 | 0.8s | | 复合索引 | 200 | 0.02s | --- ### **总结** 1. **优先创建复合索引**：覆盖 `user_id + passage_id + in_type + lunxun_status` 2. **验证执行计划**：确保索引实际生效 3. **避免全字段查询**：减少不必要的数据传输 4. **定期维护表结构**：清理碎片，更新统计信息 5. **硬件资源匹配**：确保内存和磁盘性能满足数据规模 通过上述优化，2秒查询通常可降至 **毫秒级**。若数据量持续增长（如亿级），建议结合分库分表或分布式数据库方案。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值