MySQL 有where条件时,怎么利用自增ID倒序

Question

描述

首先有张表, 表结构为

CREATE TABLE `article_like` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `article_id` int(10) unsigned NOT NULL COMMENT '文章id',
  `user_id` int(10) unsigned NOT NULL COMMENT '用户id',
  `create_at` datetime NOT NULL COMMENT '点赞时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_article_id_user_id` (`article_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文章点赞表';

一个用户只能对某一篇文章点赞一次，所以对 文章id和用户id 做了唯一索引。

示例数据

INSERT INTO `article_like` (`id`, `article_id`, `user_id`, `create_at`) VALUES ('1', '1', '2', '2018-11-06 17:13:24');
INSERT INTO `article_like` (`id`, `article_id`, `user_id`, `create_at`) VALUES ('2', '1', '9', '2018-11-06 17:13:30');
INSERT INTO `article_like` (`id`, `article_id`, `user_id`, `create_at`) VALUES ('3', '1', '7', '2018-11-06 17:13:36');
INSERT INTO `article_like` (`id`, `article_id`, `user_id`, `create_at`) VALUES ('4', '1', '4', '2018-11-06 17:13:46');
INSERT INTO `article_like` (`id`, `article_id`, `user_id`, `create_at`) VALUES ('5', '1', '1', '2018-11-06 17:13:53');
INSERT INTO `article_like` (`id`, `article_id`, `user_id`, `create_at`) VALUES ('6', '1', '10', '2018-11-06 17:14:09');
INSERT INTO `article_like` (`id`, `article_id`, `user_id`, `create_at`) VALUES ('7', '1', '5', '2018-11-06 17:14:28');

不带排序执行查询

SELECT id,article_id,user_id
FROM article_like 
WHERE article_id = 1 
LIMIT 20 

SQL 执行如下，可以看到没有安装ID排序了，但是看到 explain 分析是用到了索引，这时候前台要展示点赞列表，但是必须是按最新点赞来排序，所以这个查询满足不了需求

explain 分析

用ID排序的查询

SELECT id,article_id,user_id
FROM article_like 
WHERE article_id = 1 
ORDER BY id DESC
LIMIT 20 

可以看到现在查询的数据满足需求了，这时候我们来做一次 explain 分析看看，这次发现虽然命中索引，但是用了文件排序。

问题来了

这次想要问的是这种场景下这个索引该怎么建立，我现在的思路是建立两个索引，一个是以 文章id 和 用户id 做一个唯一索引，然后在建立一个 单独的以 文章id 做索引，但是这样 MySQL还是会选择 之前那个唯一索引，这个问题很多场景都会用到，如果有知道的同志能不能帮忙解惑一下

Answer 1

加个article_id,id,user_id的联合索引

Answer 2

SELECT id,article_id,user_id
FROM article_like 
WHERE article_id = 1 
ORDER BY id DESC
LIMIT 20 

这个查询里使用了article_id 所以Mysql选择了uniq_article_id_user_id这个索引，但是这个索引里并没有包含id，所以order by 不会使用索引，你可以尝试把id也加入索引的第一项，这样可以where语句与ORDER BY语句组合满足最左前缀，就会使用你的索引了。

CREATE TABLE `article_like` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `article_id` int(10) unsigned NOT NULL COMMENT '文章id',
  `user_id` int(10) unsigned NOT NULL COMMENT '用户id',
  `create_at` datetime NOT NULL COMMENT '点赞时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_article_id_user_id` (`id`,`article_id`,`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='文章点赞表';

Answer 3

试下在表名后加上FORCE INDEX(PRIMARY)