[踩坑] Mysql Order by 使用 有索引的单一字段 , 其他的索引失效

踩坑教训 · 2023-05-10

记录一下 今天遇到个问题. 查了查网上的说法 .

先描述问题

表结构

CREATE TABLE `table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'UID',
  `type` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '1 2',
   `t_time` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'UID',    
  `create_time` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '毫秒时间戳',
  PRIMARY KEY (`id`),
  KEY `idx_utc` (`uid`,`type`,`create_time`),
    KEY `idx_time` (`t_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

查询

-- 唯一索引
  SELECT * FROM `table` WHERE `uid` = 1 and `type`=1 and create_time > 1676009369000 ORDER BY `id` DESC  LIMIT 0, 50;

-- 普通索引
  SELECT * FROM `table` WHERE `uid` = 1 and `type`=1 and create_time > 1676009369000 ORDER BY `id` DESC  LIMIT 0, 50;

上面的SQL 可以看到 idx_utc 索引字段都已经使用了. 但order by 使用了主键索引与唯一索引字段. 两条SQL 都没使用到 idx_utc .

查了一圈, 很多文章都是在说 order by 对主键索引排序会用到索引,其他的索引失效

具体点的原因 看到一篇文章感觉 上 好理解一点, 内容如下 :

如果有这么一条sql:

select * from table where a=1 and b>10 order by c;

如果想在abc三列上去建立一个复合索引,那么如何建立???

acb列上建立索引? ? ?

不推荐这样建立索引,这样的情况相当于在ac列上建立索引!!!

实际上推荐的是在ab上建立索引。

mysql语句的各个子句中。where子句是筛选数据,order by子句是排序数据。order by排序where子句筛选后的数据集,若where子句使用的是索引访问类型(explaintype列)为index或以上的类型,那么数据集是以索引的顺序的有序集合。若order by筛选的顺序正好是索引的顺序,那么才能优化索引。

where语句和order by语句处理数据时机不同。order by的索引最左前缀和where子句最左前缀不能相互影响。即acb复合索引中:whereab索引,order去走c索引的情况是不存在的。

即若建立acb索引,那么where会使用a索引进行筛选,因为where不存在c列条件,那么不能使用b列进行筛选,该索引等效于ac列。

where语句和order by语句处理数据时机不同。order by的索引最左前缀和where子句最左前缀不能相互影响。即acb复合索引中:where走ab索引,order去走c索引的情况是不存在的。

看考资料 :

一文搞懂MySQL索引(清晰明了)
MySql性能(7)—MySql对order by排序优化
mysql
Theme Jasmine by Kent Liao