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

Question

表结构如下，里面有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;

Answer 1

你的优化想法是挺好的, 可是完全没有用到覆盖索引.

子查询:

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

完全没有用到索引, 因为你order by jdp_modified. 而可能用的索引是:
KEY ind_jdp_tb_trade_seller_nick_jdp_modified (seller_nick,jdp_modified),

建议:
a) 考虑建索引:
(jdp_modified, seller_nick) 试一下;
b) 或者业务允许的话, 按seller_nick排序:　order by seller_nick.

ＰＳ:
"该查询在前面2页需要3到4分钟才能返回结果。后面的每页4秒左右就返回了。"
---　这种情况　我猜　可能是　前面的查询后,　ｍｙｓｑｌ把排序后的结果缓存了,　所以后面的查询快了.

更新:

"表结构没有权限修改，而且SQL语句也必须要这样排序"
-- 那么你的sql完全没必要先去找合适的tid了, 直接:

SELECT t1.jdp_modified,t1.jdp_response FROM jdp_tb_trade t1 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;

这里慢 也很有可能是因为你有一个全表扫描, 你的表数据很大, 粗略估计好几个G吧. 很奇怪为什么mysql没有去走ind_jdp_tb_trade_seller_nick_jdp_modified的覆盖索引, 你可以FORCE INDEX测试一下, 看走这个覆盖索引是不是快一些.

还有就是不知道执行计划里, 这个Impossible WHERE noticed after reading const tables是怎么来的.

Answer 2

对你要查的字段做索引，BTree会大大加快搜索速度