一个MySQL order by优化案例

 

今天一个用户反馈SQL执行比较慢记录下分析过程：

 1.用户的SQL 

    SELECT * FROM ie_article_25 WHERE status = 3  AND addtime < 1284078469 order by addtime desc LIMIT 0, 1 \G

 

 2. 查看执行计划

   mysql> explain SELECT * FROM ie_article_25    WHERE status = 3  AND addtime < 1284078469  LIMIT 0, 1 \G

   *************************** 1. row ***************************

           id: 1

     select_type: SIMPLE

         table: ie_article_25

         type: ref

   possible_keys: addtime,IDX_STATUS_ADDTIME,IDX_Status,IDX_STATUS_CATID

          key: IDX_STATUS_ADDTIME

       key_len: 1

          ref: const

         rows: 7980

        Extra: Using index condition

  发现走了索引IDX_STATUS_ADDTIME，走了这个索引应该可以过滤掉数据同时可以避免排序了，按理说不应该有性能问题了。

 

  3.测试

  手动执行了一下"SELECT * FROM ie_article_25 WHERE status = 3  AND addtime < 1284078469 order by addtime desc LIMIT 0, 1"  执行时间为1.5s

然后又执行了下"SELECT * FROM ie_article_25  force index(addtime)  WHERE status = 3  AND addtime < 1284078469 order by addtime desc  LIMIT 0, 1 \G" 

时间竟然为0.04s。到这里猜测IDX_STATUS_ADDTIME只走了status索引，其实通过执行计划还是可以看出来的key_len: 1这个是因为status的定义为tinyint(1)。

 

  4.猜测

  其实还是不能理解为何没有使用addtime字段，这里where使用的是小于号，而且order by是desc只能猜测是否是索引信息没有更新的问题。

执行analyse table ie_article_25;更新表信息。

 

 再重新执行explain 原来的SQL发现执行计划果然变了，现在采用的是addtime索引了，执行的速度顺便变成了0.04s

*************************** 1. row ***************************

           id: 1

     select_type: SIMPLE

         table: ie_article_25

         type: range

   possible_keys: addtime,IDX_STATUS_ADDTIME,IDX_Status,IDX_STATUS_CATID

          key: addtime

       key_len: 4

          ref: NULL

         rows: 275648

        Extra: Using index condition; Using where

1 row in set (0.04 sec)

 

  5.结论

  这个执行计划还可以更优一点，走这个IDX_STATUS_ADDTIME索引，用force index(IDX_STATUS_ADDTIME)发现执行时间变了0.03s了。

 SELECT * FROM ie_article_25 force index(IDX_STATUS_ADDTIME)   WHERE status = 3  AND addtime < 1284078469  order by addtime desc LIMIT 0, 1

 

用户的部分表结构：

CREATE TABLE `ie_article_25` (

  `itemid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `addtime` int(10) unsigned NOT NULL DEFAULT '0',

  `status` tinyint(1) NOT NULL DEFAULT '0',

  PRIMARY KEY (`itemid`),

  KEY `addtime` (`addtime`),

  KEY `IDX_STATUS_ADDTIME` (`status`,`addtime`),

  KEY `IDX_CA_ST_AD` (`catid`,`status`,`addtime`),

  KEY `IDX_Status` (`status`),

  KEY `IDX_STATUS_CATID` (`status`,`catid`),

) ENGINE=MyISAM