问答首页 → 综合技术
0 0

从500万条数据中查询，求优化一条SQL语句10

表结构如下，里面有500W数据。我没有权限修改这个表，所以请大家给我优化下SQL查询 
CREATE TABLE IF NOT EXISTS `jdp_tb_trade` (
  `tid` bigint(20) NOT NULL,
  `status` varchar(64) DEFAULT NULL,
  `type` varchar(64) DEFAULT NULL,
  `seller_nick` varchar(32) DEFAULT NULL,
  `buyer_nick` varchar(32) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `jdp_hashcode` varchar(128) DEFAULT NULL,
  `jdp_response` mediumtext,
  `jdp_created` datetime DEFAULT NULL,
  `jdp_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`tid`),
  KEY `ind_jdp_tb_trade_seller_nick_jdp_modified` (`seller_nick`,`jdp_modified`),
  KEY `ind_jdp_tb_trade_jdp_modified` (`jdp_modified`),
  KEY `ind_jdp_tb_trade_seller_nick_modified` (`seller_nick`,`modified`),
  KEY `ind_jdp_tb_trade_modified` (`modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


符合以下条件的有3W条数据 
SELECT COUNT(*) AS tp_count FROM `jdp_tb_trade` WHERE ( `seller_nick` IN ('李心','zhixian50','陈鹏','雪儿','稀饭','婷婷','七七') ) AND ( (`jdp_modified` > '2007-11-30 09:52:39') AND (`jdp_modified` <= '2014-04-21 22:31:13')  ) LIMIT 1


我要分页查询出这3W条数据，由于MYSQL分页越往后查询越慢，所以我用了关联查询。该查询在前面2页需要3到4分钟才能返回结果。后面的每页4秒左右就返回了。实在搞不明白为什么。麻烦大家帮忙优化下。每页必须要在10秒内完成。 
SELECT  t1.jdp_modified,t1.jdp_response FROM jdp_tb_trade t1, ( SELECT `tid` FROM `jdp_tb_trade` WHERE ( `seller_nick` IN ('李心','zhixian50','陈鹏','雪儿','稀饭','婷婷','七七') ) AND ( (`jdp_modified` > '2007-11-30 09:52:39') AND (`jdp_modified` <= '2014-04-21 22:31:13')  ) ORDER BY jdp_modified desc LIMIT 0,200   ) t2 WHERE t1.tid=t2.tid
问题补充：慢的原因主要在in上面，目前我将in用UNION ALL代替，能基本达到要求。各位还有什么好方法
MySQL数据库 
2014年4月22日 17:17

12个答案 按时间排序 按投票排序

0 0

采纳的答案

SELECT * FROM (
SELECT  t1.tid FROM jdp_tb_trade t1
where t1.seller_nick IN ('李心','zhixian50','陈鹏','雪儿','稀饭','婷婷','七七')
and t1.jdp_modified > '2007-11-30 09:52:39'
and t1.jdp_modified <= '2014-04-21 22:31:13'
ORDER BY jdp_modified desc
LIMIT 0,200 ) a inner join jdp_tb_trade b on a.tid = b.tid

给你些参考链接，希望有所帮助：
http://laiguowei2004.blog.163.com/blog/static/36829000201162765456254/
http://www.111cn.net/database/mysql/50921.htm

2014年4月23日 11:18
0 0

数据多还是得用索引

2014年4月26日 00:52
0 0

SELECT  jdp_modified,jdp_response FROM jdp_tb_trade WHERE tid IN (select tid FROM jdp_tb_trade  WHERE `seller_nick` IN ('李心','zhixian50','陈鹏','雪儿','稀饭','婷婷','七七') 
AND `jdp_modified` > '2007-11-30 09:52:39' 
AND `jdp_modified` <= '2014-04-21 22:31:13' 
ORDER BY jdp_modified desc LIMIT 0,200  );

2014年4月24日 16:22
0 0

奇怪哈..既然tid是主键,又是表自连,那直接这样写不就好了? 

SELECT jdp_modified, jdp_response FROM `jdp_tb_trade` 
WHERE `seller_nick` IN ('李心','zhixian50','陈鹏','雪儿','稀饭','婷婷','七七')
AND `jdp_modified` > '2007-11-30 09:52:39'
AND `jdp_modified` <= '2014-04-21 22:31:13'
ORDER BY jdp_modified desc LIMIT 0,200

2014年4月24日 12:57
0 0

我觉得吧这种方式应该可以实现...展开余下内容

2014年4月23日 17:35
0 0

你为啥不把后面的条件放在前面，可以走索引，缩小范围，然后再查in

2014年4月23日 15:52
0 0

你是在搞聚石塔吧，为什么要IN

2014年4月23日 13:11
0 0

是不是没用PreparedStatement？感觉像是头几页mysql生成执行计划，所以慢，后面查询快是因为测试的时候查询条件不变沿用了执行计划，所以快了。换成PreparedStatement+动态参数会不会好点？

2014年4月23日 10:27
0 0

没有必要不要用order by，order by 比较耗时，用exists 性能比in要好一些

2014年4月23日 08:35
0 0 

SELECT  t1.jdp_modified,t1.jdp_response FROM jdp_tb_trade t1, jdp_tb_trade t2
where t2.`seller_nick` IN ('李心','zhixian50','陈鹏','雪儿','稀饭','婷婷','七七') ) AND ( (t2.`jdp_modified` > '2007-11-30 09:52:39') AND (t2.`jdp_modified` <= '2014-04-21 22:31:13')  ) and t1.tid=t2.tid 
ORDER BY t1.jdp_modified desc LIMIT 0,200

这样试一下？（主要是把子查询修改为内联查询）

2014年4月22日 23:12
0 0

exists

2014年4月22日 22:00
0 0

SELECT  t1.jdp_modified,t1.jdp_response FROM jdp_tb_trade t1 where exsits
(SELECT * FROM jdp_tb_trade t2 WHERE ( t2.seller_nick IN ('李心','zhixian50','陈鹏','雪儿','稀饭','婷婷','七七'))
AND ( (t2.jdp_modified > '2007-11-30 09:52:39')
AND (t2.jdp_modified <= '2014-04-21 22:31:13')) and 1.tid=t2.tid ORDER BY t2.jdp_modified desc LIMIT 0,200)

2014年4月22日 21:59

相关推荐

    java一次性查询处理几百万数据解决方法

    - 每次查询结果返回后，遍历`ResultSet`对象，对每一条数据进行处理。 - 为防止内存溢出，可以设置每次处理的数据量上限，并将处理结果定期写入文件或其他持久化存储中。 4. **资源管理**： - 在操作完成后及时...

    SpringBoot使用mybatis批量新增500万数据到mysql数据库Demo

    1. **批量插入（Batch Insertion）**：Mybatis支持批处理，可以一次性提交多条SQL语句，减少与数据库的交互次数，提高效率。在Mybatis的映射文件中，可以通过`&lt;insert&gt;`标签的`useGeneratedKeys`和`keyProperty`属性...

    【MySQL】SQL优化(九).pdf

    当需要一次性向数据库表中插入多条记录时，可以选择批量插入多条数据来代替一条一条插入。这样可以减少与数据库的连接次数，从而降低网络传输带来的性能损失。批量插入的数量不宜过多，一般建议不超过500~1000条。...

    10万条数据下MySQL批量更新6种方式的性能测试.zip

    通过实际测试对比了Spring Boot中6种MySQL批量更新方式的效率，并详细记录了每种方法在处理500,1000,5000,10000,50000,100000条数据的平均时间、最小时间和最大时间。包括MyBatis-Plus提供的批量更新,JdbcTemplate...

    sql争霸决赛试题

    - **分区表**：考虑到每月有近500万条记录，可以对`TelRecord`表进行时间分区，按月或按年存储数据，减少单表数据量，提高查询效率。 - **汇总表**：建立一个汇总表，预计算每个用户的通话总时长，定期更新，减少...

    使用SQL语句去掉重复的记录【两种方法】

    对于500万条数据，这种方法可以在10分钟内完成去重，而方法一可能需要4小时。 此外，还可以使用窗口函数`ROW_NUMBER()`进行去重，这在某些数据库系统中更为灵活。例如，以下方法一和方法二展示了如何结合`ROW_...

    Python从数据库读取大量数据批量写入文件的方法

    - 在执行SQL查询时，确保SQL语句的正确性，避免出现语法错误或逻辑错误。 - 使用`with`语句来打开文件可以确保文件在操作完成后自动关闭，即使在处理过程中发生异常也不例外。 - 如果数据量非常大，建议进一步优化...

    数据库数据查询实验报告.docx

    实验主要目标是让学生掌握不同类型的SQL查询语句，包括但不限于单表查询、多表连接查询、嵌套查询和集合查询等。此外，还要求学生熟练运用条件表达式来设置检索条件，并利用聚合函数进行统计分析。 #### 实验环境...

    C#程序开发范例宝典（第2版）.part13

    一部久享盛誉的程序开发宝典。精选570个典型范例，全面覆盖实用和热点技术，涉及面广，实用性强源于实际项目开发，帮助读者短时间掌握更多实用技术，提高编程水平范例经过精心编排，重点、难点突出，易学易懂书后...

    C#程序开发范例宝典（第2版）.part08

    一部久享盛誉的程序开发宝典。精选570个典型范例，全面覆盖实用和热点技术，涉及面广，实用性强源于实际项目开发，帮助读者短时间掌握更多实用技术，提高编程水平范例经过精心编排，重点、难点突出，易学易懂书后...

    C#程序开发范例宝典（第2版）.part02

    一部久享盛誉的程序开发宝典。精选570个典型范例，全面覆盖实用和热点技术，涉及面广，实用性强源于实际项目开发，帮助读者短时间掌握更多实用技术，提高编程水平范例经过精心编排，重点、难点突出，易学易懂书后...

    C#程序开发范例宝典（第2版）.part12

    一部久享盛誉的程序开发宝典。精选570个典型范例，全面覆盖实用和热点技术，涉及面广，实用性强源于实际项目开发，帮助读者短时间掌握更多实用技术，提高编程水平范例经过精心编排，重点、难点突出，易学易懂书后...

Magicbox
Ask-but
我的问答 FAQ | 勋章

相关推荐

已解决问题

未解决问题

排行榜

Global site tag (gtag.js) - Google Analytics