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

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

wskia
10
0 0 3

采纳的答案

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

onlylau
618
1 2 47

添加评论

0 0

数据多还是得用索引

2014年4月26日 00:52

java_frog
27
0 0 6

添加评论

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

pincs
20
0 0 0

添加评论

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

joaboo
107
1 1 11

2条评论

0 0

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

2014年4月23日 17:35

feinimoshu199347
200
0 1 25

2条评论

0 0

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

2014年4月23日 15:52

liufeixiaowei
30
0 0 0

添加评论

0 0

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

2014年4月23日 13:11

hycx227
30
0 0 0

1条评论

0 0

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

2014年4月23日 10:27

hattah
0
0 0 0

添加评论

0 0

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

2014年4月23日 08:35

liujun_wangyi
10
0 0 0

1条评论

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

freezingsky
159
1 1 16

2条评论

0 0

exists

2014年4月22日 22:00

lirihong
0
0 0 1

添加评论

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

lirihong
0
0 0 1

2条评论

已解决问题

未解决问题

排行榜

查看全部排名>>