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

表结构如下,里面有500W数据。我没有权限修改这个表,所以请大家给我优化下SQL查询
[code="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;[/code]

符合以下条件的有3W条数据
[code="sql"]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[/code]

我要分页查询出这3W条数据,由于MYSQL分页越往后查询越慢,所以我用了关联查询。该查询在前面2页需要3到4分钟才能返回结果。后面的每页4秒左右就返回了。实在搞不明白为什么。麻烦大家帮忙优化下。每页必须要在10秒内完成。
[code="sql"]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[/code]

12个回答

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

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)

taoshuang521
taoshuang521 没用IN啊。用IN肯定慢啊 。用exists
5 年多之前 回复
wskia
小熊开发 试了,还是慢。主要慢的原因在IN上面
5 年多之前 回复

exists

[code="java"]
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

[/code]
这样试一下?(主要是把子查询修改为内联查询)

M44346460
M44346460 我原来搞移动的系统 ,单表数据量超过7000万条,都没有这么慢。。我怕,估计得从数据库本身做优化了。
5 年多之前 回复
wskia
小熊开发 不行,还是慢
5 年多之前 回复

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

wskia
小熊开发 order by必须要用,这个是淘宝的一个同步数据的SQL,加ORDER BY 才能实现增量同步
5 年多之前 回复

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

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

wskia
小熊开发 不用IN每次一个seller_nick是没问题的。你有更好的方法吗
5 年多之前 回复

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

我觉得吧这种方式应该可以实现...[url=http://www.baidu.com]展开余下内容[/url]

zengjianhua2011
zengjianhua2011 www.baidu.com
5 年多之前 回复
wskia
小熊开发 ?什么方式
5 年多之前 回复

奇怪哈..既然tid是主键,又是表自连,那直接这样写不就好了?
[code="sql"]
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
[/code]

joaboo
joaboo "该查询在前面2页需要3到4分钟才能返回结果。后面的每页4秒左右就返回了" 坦白讲,我看了你的描述,我觉得是索引的问题,因为你order by是desc的,而mysql的索引在某些系统上,不支持反向的读取,你尝试着创建一个降序索引试试 create index idx_jdp_modified_desc on jdp_tb_trade(jdp_modified desc);如果是组合索引的话,应该也可以降序. 而且还有个问题,你想通过join方式来改善大数据分页带来大偏移量,sql貌似也不是这么写的..
5 年多之前 回复
wskia
小熊开发 in太慢 要扫描60W行
5 年多之前 回复
共12条数据 1 尾页
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问