MySQL联合索引是不支持不同排序规则解决方案

最新推荐文章于 2022-05-27 16:54:37 发布

weixin_34116110

最新推荐文章于 2022-05-27 16:54:37 发布

阅读量221

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/b1ack2ephyr/blog/672636

版权

2019独角兽企业重金招聘Python工程师标准>>>

先来看一下表结构，这是一张新闻表：

Table: news_news

Create Table: CREATE TABLE `news_news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `rank` int(11) NOT NULL,
  `news_time` date NOT NULL,
  `publisher` varchar(128) NOT NULL,
  `news_url` varchar(200) NOT NULL,
  `content` longtext NOT NULL,
  `hash_digest` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_digest` (`hash_digest`),
  KEY `news_wild_card_index` (`news_time`,`rank`)
) ENGINE=InnoDB AUTO_INCREMENT=154239 DEFAULT CHARSET=utf8
1 row in set (0.04 sec)

我想查的sql是这样的：

SELECT 
`news_news`.`id`, `news_news`.`title`, 
`news_news`.`rank`, `news_news`.`news_time`, 
`news_news`.`content` 
FROM `news_news` 
WHERE (`news_news`.`news_time` >= '2011-09-01'
       AND `news_news`.`news_time` <= '2016-05-10') 
ORDER BY 
 `news_news`.`news_time` DESC,
 `news_news`.`rank` ASC 
LIMIT 9;

就是根据时间和排序选择出最新，排名最靠前的新闻。经验丰富的DBA估计一看就找出了问题，可是我是菜鸟.....

explian 一把：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: news_news
         type: range
possible_keys: news_wild_card_index
          key: news_wild_card_index
      key_len: 3
          ref: NULL
         rows: 64614
        Extra: Using index condition; Using MRR; Using filesort
1 row in set (0.00 sec)

看到了吧，using Filesort。可我觉得我的联合索引就是应该这样建啊......

确实是应该这样建，但是，MySQL联合索引是不支持不同排序规则。

原因在于,news_wild_card_index使用的时候一个为DESC一个为ASC，排序顺序不同啊，所以最终用上了filesort。

为了用上索引，所以根据自己的业务调整了一下，主要解决思路就是：让news_time,rank都使用desc排序(这显然不符合我的业务逻辑),但是原先的rank更改为1000-rank.实际效果就和最初的需求一致了。但这个解决方案需要在原有的程序做一定的改变，不过性能确实上去了。

所以在原先的表当中做了这样一个update

update news_news set rank=1000-rank where id>0;

当然程序当中也需要做响应的修改。

最终explain的结果:

explain 
SELECT 
`news_news`.`id`, `news_news`.`title`,
 `news_news`.`rank`, `news_news`.`news_time`,
 `news_news`.`content` FROM `news_news` 
WHERE 
(`news_news`.`news_time` >= '2011-09-01' 
AND `news_news`.`news_time` <= '2016-05-10') 
ORDER BY 
`news_news`.`news_time` DESC,
 `news_news`.`rank` DESC 
LIMIT 9\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: news_news
         type: range
possible_keys: search_result_index
          key: search_result_index
      key_len: 3
          ref: NULL
         rows: 45638
        Extra: Using index condition
1 row in set (0.01 sec)

转载于:https://my.oschina.net/b1ack2ephyr/blog/672636