- 从500万条数据中查询，求优化一条SQL语句
-
表结构如下，里面有500W数据。我没有权限修改这个表，所以请大家给我优化下SQL查询
[code="sql"]CREATE TABLE IF NOT EXISTSjdp_tb_trade(tidbigint(20) NOT NULL,statusvarchar(64) DEFAULT NULL,typevarchar(64) DEFAULT NULL,seller_nickvarchar(32) DEFAULT NULL,buyer_nickvarchar(32) DEFAULT NULL,createddatetime DEFAULT NULL,modifieddatetime DEFAULT NULL,jdp_hashcodevarchar(128) DEFAULT NULL,jdp_responsemediumtext,jdp_createddatetime DEFAULT NULL,jdp_modifieddatetime DEFAULT NULL,
PRIMARY KEY (tid),
KEYind_jdp_tb_trade_seller_nick_jdp_modified(seller_nick,jdp_modified),
KEYind_jdp_tb_trade_jdp_modified(jdp_modified),
KEYind_jdp_tb_trade_seller_nick_modified(seller_nick,modified),
KEYind_jdp_tb_trade_modified(modified)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;[/code]符合以下条件的有3W条数据
[code="sql"]SELECT COUNT(*) AS tp_count FROMjdp_tb_tradeWHERE (seller_nickIN ('李心','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, ( SELECTtidFROMjdp_tb_tradeWHERE (seller_nickIN ('李心','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]
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 没用IN啊。用IN肯定慢啊 。用exists
- 接近 6 年之前 回复
[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]
这样试一下？（主要是把子查询修改为内联查询）
是不是没用PreparedStatement？感觉像是头几页mysql生成执行计划，所以慢，后面查询快是因为测试的时候查询条件不变沿用了执行计划，所以快了。换成PreparedStatement+动态参数会不会好点？
-
- zengjianhua2011 www.baidu.com
- 接近 6 年之前 回复
奇怪哈..既然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]
