wskia
小熊开发
2014-04-22 17:17
采纳率: 100%
浏览 717
已采纳

从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条回答 默认 最新

  • iteye_7115
    iteye_7115 2014-04-23 11:18
    已采纳

    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

    点赞 评论
  • taoshuang521
    taoshuang521 2014-04-22 21:59

    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 2014-04-22 22:00

    exists

    点赞 评论
  • M44346460
    M44346460 2014-04-22 23:12

    [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]
    这样试一下？（主要是把子查询修改为内联查询）

    点赞 评论
  • liujun_wangyi
    liujun_wangyi 2014-04-23 08:35

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

    点赞 评论
  • weixin_42283261
    weixin_42283261 2014-04-23 10:27

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

    点赞 评论
  • jackli2006
    壹灯大师 2014-04-23 13:11

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

    点赞 评论
  • liufeixiaowei
    流沙飞雪 2014-04-23 15:52

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

    点赞 评论
  • ck504323276
    ck504323276 2014-04-23 17:35

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

    点赞 评论
  • joaboo
    joaboo 2014-04-24 12:57

    奇怪哈..既然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]

    点赞 评论
  • iteye_12060
    iteye_12060 2014-04-24 16:22

    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 );

    点赞 评论
  • futureghr
    瑞哥 2014-04-26 00:52

    数据多还是得用索引

    点赞 评论
提交

相关推荐