Mysql数据库10万条数据多表联合查询速度过慢解决方案

最新推荐文章于 2025-03-14 07:00:00 发布
最新推荐文章于 2025-03-14 07:00:00 发布
阅读量1.6w 收藏 35
点赞数 10
分类专栏： 项目随记 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_41348754/article/details/105422383
版权

点我，查看原文

问题描述

今天在写项目时发现之前好好的查询接口突然挂了🤔，检查后发现原来是有人往数据里新增了10万条数据，以至于Mysql语句执行的特别慢就不行了。

原因

原因也简单这个接口原本是一张组织机构表关联区域表、组织标准类型表、币种表、行业分类标准表等多张表的联合查询。按照下图的写法已经写了limit分页只取10条，可Mysql确实会很认真的先帮你把10万条数据的外键关系先建立起来，然后在拿最上面的10条给你。emmmmm……🐴🥚
在这里插入图片描述
所以数据量一旦多起来后，Mysql的遍历查询结果就不尽人意，10万条语句这一次查询差不多就要3分多钟。

解决思路

解决思路也很简单，我目前想到的做法就是在Mysql与关联表进行关联操作之前，先通过分页、查询条件、约束等减小它的范围。简单点说就是，先查再建视图。

实际步骤

直接放优化后的语句叭
在这里插入图片描述

效果展示

最后再看看优化后的速度
在这里插入图片描述
0.083秒。恩……真香！🎉

干 MySQL 两千万数据的大表优化解决过程，三种厉害的解决方案
程序员白楠楠的博客
08-08 485
问题概述 方案概述 方案一详细说明：优化现有mysql数据库 方案二详细说明：升级数据库，换一个100%兼容mysql的数据库 方案三详细说明：去掉mysql，换大数据引擎处理数据 问题概述 使用阿里云rds for MySQL数据库（就是MySQL5.6版本），有个用户上网记录表6个月的数据量近2000万，保留最近一年的数据量达到4000万，查询速度极慢，日常卡死。严重影响业务。 问题前提：老系统，当时设计系统的人大概是大学没毕业，表设计和sql语句写的不仅仅是垃圾，简直无法直视。原开发人员都已离职.
mysql十万条信息检索慢_MySQL的查询性能优化(1)
weixin_35422396的博客
02-18 1317
使用EXPLAIN语句检查SQL语句 当你在一条SELECT语句前放上关键词EXPLAIN，MySQL解释它将如何处理SELECT，提供有关表如何联结和以什么次序联结的信息。 借助于EXPLAIN，你可以知道你什么时候必须为表加入索引以得到一个使用索引找到记录的更快的SELECT。 EXPLA使用EXPLAIN语句检查SQL语句当你在一条SELECT语句前放上关键词EXPLAIN，MySQL解释它...
面向程序员的数据库访问性能优化法则
MKing's Blog
12-06 12万+
<br />面向程序员的数据库访问性能优化法则<br /> <br />特别说明：<br />1、  本文只是面对数据库应用开发的程序员，不适合专业DBA，DBA在数据库性能优化方面需要了解更多的知识；<br />2、  本文许多示例及概念是基于Oracle数据库描述，对于其它关系型数据库也可以参考，但许多观点不适合于KV数据库或内存数据库或者是基于SSD技术的数据库；<br />3、  本文未深入数据库优化中最核心的执行计划分析技术。<br /> <br />读者对像：<br />开发人员：如果你是做数据
MySQL连接较慢原因分析及解决措施
大数据、数据仓库、数据治理、数据开发、数据库等相关数据方面经验分享~
03-14 1592
在使用MySQL的时候经常遇到**连接比较缓慢**的问题，总结了一下原因和结局措施
多表联合查询速度巨慢解决办法之一
xiangzhihao优快云的博客
09-15 3万+
在实际工作当中,有一次碰到了多表联合查询但两分钟才出来结果,但A表和B表都不算大,A表只有五万多条数据,B表只有两万条数据,从中查出符合条件的数据并不需耗时那么长.最后经组长指导才发现A表没有建立索引,最后用CREATE INDEX  索引名 ON 表名(字段名) 为A表添加了一个普通索引之后才解决了问题.         对索引的解释,我觉得                 http://w
【MySQL】才 10W 条数据就查询超时了！！！
qq_32460431的博客
07-07 1389
研究一下在MySQL中关联关系表的设计
mysql多数据表关联查询慢问题解决方案
xia_2017的博客
07-14 1万+
遇到一个问题，mysql库下表A有几万条数据；表B也有几万条数据，结果两表关联的时候，查询超级慢，30s内没反应。 解决方案： 1. 加索引，但索引不要乱加，两表关联，必须要创建的索引是两表的管理字段 e.g select * from A t1 left join B t2 on t1.did= t2.did and t1.uid = t2.uid 这种情况下，一定要给A表的uid和did创建索引，如果uid值是唯一那就创建唯一所引UNIQUE;否则就创建normal索引。 基本上加索引之后，.
mysql 两表联合查询数据多了很慢
yehaifanxing的专栏
08-24 1万+
1、不要直接去关联表名查询，在关联表外加个select查询。 select * from tab1 a inner join (select * from tab2) b on a.fld1=b.fld2  2、主表有条件先select一遍主表，关联表有条件最好放最后where后（注意：条件放最后和放关联表中查询有时是不一样的） select * from （select * from ta...
sql执行报错---＞1094 unknow thread id
sias_Amazing的博客
10-16 6071
全表查询数据查询失败（大概有180万条） 报错信息 sql执行报错—>1094 什么unknow thread id 优化并解决 具体原因是一次查询超时，导致sql未执行完并且报错 优化sql 分页查询并且一次只查询1000进行处理，本身就是定时任务，所以耗时并不影响 图片: ...
MySQL高频基本面试问题整理
热门推荐
张彦峰的博客
04-07 173万+
总结大厂MySQL高频基本面试问题整理，用于快速查缺补漏
Java 与 MySQL 数据库交互实战：优化海量数据查询，打破性能瓶颈枷锁
威哥说编程
12-04 636
在高并发和大数据量的应用场景中，数据库的性能优化至关重要。通过合理配置数据库连接池、精确设计和使用索引、优化SQL 查询，并借助慢查询排查等手段，我们能够有效提高系统的响应速度和吞吐量，打破数据库查询的性能瓶颈。对于金融交易流水、物流订单等复杂的业务场景，通过精细化的优化，能够确保数据库在高负载下仍然保持良好的性能，为业务系统提供强有力的支撑。
MySQL两千万数据的大表优化解决过程，提供三种解决方案
KHOST的博客
02-22 1209
问题概述 使用阿里云rds for MySQL数据库（就是MySQL5.6版本），有个用户上网记录表6个月的数据量近2000万，保留最近一年的数据量达到4000万，查询速度极慢，日常卡死。严重影响业务。 问题前提：老系统，当时设计系统的人大概是大学没毕业，表设计和sql语句写的不仅仅是垃圾，简直无法直视。原开发人员都已离职，到我来维护，这就是传说中的维护不了就跑路，然后我就是掉坑的那个！！！ ...
实战 2000w 数据大表的优化过程，提供三种解决方案
m0_71777195的博客
02-22 1511
当我们业务数据库表中的数据越来越多，如果你也和我遇到了以下类似场景，那让我们一起来解决这个问题数据的插入,查询时长较长后续业务需求的扩展 在表中新增字段 影响较大表中的数据并不是所有的都为有效数据 需求只查询时间区间内的。
mysql 怎么查询超过10w数据
weixin_40664962的博客
07-28 371
MySQL 查询超过10万数据的优化方案 在处理大量数据时，MySQL数据库的性能可能会受到影响。本文将提供一个针对查询超过10万数据的优化方案，包括索引优化、查询语句优化、分页查询等策略。 1. 索引优化 索引是提高数据库查询性能的关键。对于大数据量的表，合理地创建和使用索引可以显著提高查询速度。 1.1 创建索引 对于...
几十张表关联查询效率慢
weixin_49186526的博客
03-10 2610
首先你会想到，给表加索引，那么mysql会给主键自动建立索引吗? 会的，当然会。 在我们查询的业务表操作的时候，表业务数据庞大起来的时候，以及left join多的时候，甚至多表关联到几十张表的时候，查询是慢到外婆家里去了。 这时候，只需要给表join查询的字段，及表结构，进行索引优化，即可解决这个慢的问题。 一，首先利用explain 关键字对查询的SQL进行分析。 # type=ALL，全表扫描，MySQL遍历全表来找到匹配行 # type=index，索引全扫描，MySQL遍历整个索引来查询
Mysql十万级数据分页查询速度优化
Swallow的博客
04-27 4135
最近遇到的一个线上案例。表数据量大概30w左右，单表分页查询，查最后一页就会耗时极慢 单表，带查询条件查询最后一页时 那么，问题来了，为什么查到后面会变慢呢 mysql在进行分页查询的时候 select * from a limit x,y 首先会先查询前x+y条数据，然后将前x条丢弃 所以，当x的值很大的时候，效率就会变低。 那么怎么解决呢 关联子查询可以快速定位需要获取的id段，再进行关联查询就可以提高查询效率 对于以上查询优化，可以将sql改写 很明显，同样的查询条件
mysql进行大数据量查询,速度慢及解决方式
慕迟的轻博客
12-19 2万+
自述 在项目开发中我们经常会使用左连接进行查询,可能库表中的数据量并不大,所以在查询时感觉不到,如果表中的数据达到了十万条乃至百万条,并且查询的时候列的数据重复量也较大,这个时候进行左连接查询,时间可能达到死亡时间(即前端请求超时时间);这个时候造成的原因可能有: 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、项目的吞吐量达不到，形成了瓶颈效应 3、查询出的数据量过...
分分钟解决MySQL查询速度慢与性能差
weixin_34088838的博客
03-18 1545
阅读本文大概需要 6 分钟。一、什么影响了数据库查询速度1.1 影响数据库查询速度的四个因素1.2 风险分析QPS： QueriesPerSecond意思是“每秒查询率”，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。TPS： 是 TransactionsPerSecond的缩写，也就是事务数/秒。它是软件测试结果的测量单位。客户机在发送请求时开...
mysql多表联查使用排序之后执行速度变慢
quequnlong的博客
08-24 1842
经常在开发中会对数据库表进行多表关联查询，同时也会根据字段排序，在实际开发中根据时间字段排序是经常会出现的，在数据量少的情况下通常不会发现查询速度慢的问题，一旦数据变多就会发现查询起来的速度很慢，就好比今天我就接到一个需求就是优化查询，一开始以为只是多表关联查询引起的，结果发现是时间排序导致的，就如下图加了时间排序时的语句和执行时间select可以看到所花费足足10秒多去掉时间排序后select可以看到一秒不到就执行完了，由此可见执行慢就是时间排序引起的问题，那么如何解决呢。
批量保存数据过大时
最新发布
04-02
<think>好的，用户问的是如何处理批量保存数据过大的问题，特别是数据库性能优化和批量插入限制。首先，我需要回忆之前学过的相关知识，以及参考提供的引用内容。 根据引用[2]，批量插入性能优化很重要，尤其是在数据量大的情况下。引用[3]提到了批量插入的具体方法，比如JDBC的批量处理配置，还有分页处理数据。用户可能遇到的问题可能包括插入时间过长、数据库负载高，甚至事务超时等。 首先，我需要考虑分批次插入。用户提到“批量保存数据过大”，所以一次性插入大量数据肯定不行，分批次处理是关键。比如，每1000条提交一次，这样可以减少事务的大小，避免锁表和内存溢出。引用[3]里也提到了批量插入的参数配置，比如rewriteBatchedStatements参数，这个对MySQL特别有效。 然后是使用批量处理API，比如JDBC的addBatch和executeBatch，这样能减少网络开销，提升效率。同时，ORM框架如MyBatis的foreach标签需要注意参数数量，过多的参数会导致SQL语句过长，可能需要调整数据库的max_allowed_packet设置，这点在引用[3]的JDBC配置里提到过。 接下来是禁用索引和约束。在插入前暂时关闭索引和外键检查，插入后再重建，这对大量数据插入很有效。但要注意，这可能会影响其他操作，需要评估业务场景是否允许。 事务管理方面，自动提交会频繁刷盘，改为手动提交，分批提交事务，可以降低I/O压力。引用[1]提到硬件和数据库配置优化，比如SSD和内存，虽然用户可能无法直接改变硬件，但可以建议调整innodb_buffer_pool_size等参数。 另外，使用LOAD DATA INFILE或者批量插入工具，比如MySQL的这个命令，比普通INSERT快很多，适合极端情况。但可能需要文件导出，用户的应用是否支持需要确认。 还有，调整数据库参数，比如增大max_allowed_packet，调整JDBC的批量大小，这些在引用[3]都有例子。比如，设置useServerPrepStmts和cachePrepStmts可以提升预处理语句的效率。 监控和分析性能也很重要，引用[1]提到持续监控，用户可能需要使用慢查询日志或监控工具找出瓶颈。 潜在的问题需要考虑，比如分批次的大小选择，太大可能还是慢，太小则增加次数。还有ORM框架的批量操作支持情况，可能需要原生JDBC。另外，高并发下的锁竞争，可能需要选择低峰期执行批量插入。 总结起来，步骤应该是分批次处理、使用批量API、优化数据库配置、禁用索引/约束、合理事务管理，以及可能的工具使用。需要把这些点组织清晰，给出具体例子和参数设置，让用户能一步步操作。同时，结合引用中的内容，比如引用[3]中的JDBC配置和分页方法，来支持建议的可行性。</think>### 如何优化批量保存数据过大的问题？ 处理批量保存数据过大时的性能问题，需从**数据库配置**、**插入策略**、**代码优化**和**硬件资源**四方面综合优化。以下是具体解决方案： --- ### 一、分批次插入（核心优化） 1. **限制单次插入量** - 将数据拆分为多个小批次（如每批1000条），避免单次事务过大导致内存溢出或锁表。 - 示例代码（Java）： ```java int batchSize = 1000; for (int i = 0; i < dataList.size(); i += batchSize) { List<Data> subList = dataList.subList(i, Math.min(i + batchSize, dataList.size())); jdbcTemplate.batchUpdate("INSERT INTO table (col1, col2) VALUES (?, ?)", subList); } ``` - 分批次可减少事务日志刷盘频率，降低I/O压力[^2][^3]。 2. **动态调整批次大小** - 根据数据库响应时间和硬件性能动态调整批次大小（如500~5000条），避免固定值导致性能波动。 --- ### 二、使用批量处理API 1. **JDBC批量操作** - 启用JDBC批量处理参数（如MySQL的`rewriteBatchedStatements=true`），合并多次插入为单次网络请求。 - 配置示例（JDBC URL）： ``` jdbc:mysql://host:3306/db?rewriteBatchedStatements=true&useServerPrepStmts=true&cachePrepStmts=true ``` - 此配置可提升批量插入速度2~10倍。 2. **ORM框架优化** - MyBatis中避免使用`<foreach>`拼接过长SQL，改用`BATCH`执行模式： ```xml <insert id="batchInsert" parameterType="java.util.List"> INSERT INTO table (col1, col2) VALUES <foreach collection="list" item="item" separator=","> (#{item.col1}, #{item.col2}) </foreach> </insert> ``` - 注意：单条SQL长度需小于`max_allowed_packet`（默认4MB）。 --- ### 三、数据库配置优化 1. **临时禁用索引和约束** - 插入前关闭索引更新和外键检查： ```sql ALTER TABLE table_name DISABLE KEYS; SET FOREIGN_KEY_CHECKS=0; ``` - 插入完成后重建索引： ```sql ALTER TABLE table_name ENABLE KEYS; SET FOREIGN_KEY_CHECKS=1; ``` - 此方法可减少插入时的索引维护开销。 2. **调整事务提交策略** - 关闭自动提交（`autocommit=0`），每批次提交一次事务，减少日志刷盘次数。 3. **关键参数调优** - 增大`innodb_buffer_pool_size`（占用内存70%~80%） - 调整`max_allowed_packet`（匹配批次数据大小） - 设置`innodb_flush_log_at_trx_commit=2`（批量插入期间降低日志持久化强度） --- ### 四、替代方案 1. **文件导入工具** - 使用`LOAD DATA INFILE`（MySQL）或`COPY`（PostgreSQL）直接导入CSV文件，速度比INSERT快10~100倍。 ```sql LOAD DATA INFILE '/path/data.csv' INTO TABLE table FIELDS TERMINATED BY ','; ``` 2. **异步写入** - 将数据写入消息队列（如Kafka），由消费者异步批量插入，避免阻塞主业务。 --- ### 五、监控与验证 1. **性能分析工具** - 使用`SHOW PROCESSLIST`监控数据库线程状态 - 通过慢查询日志（`slow_query_log`）定位瓶颈语句 2. **压力测试** - 逐步增加批次大小，观察吞吐量和资源占用曲线，找到最优值。 --- ### 注意事项 - **避免长事务**：单批次操作时间不宜超过30秒，防止锁竞争。 - **错误回滚**：需实现失败批次的重试机制。 - **资源隔离**：生产环境中建议使用读写分离或专用从库执行批量任务。 通过上述优化，可显著降低批量插入时间。例如某案例中，10万条数据插入时间从120秒优化至3秒[^3]。 ---
评论 7
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值