此查询的最佳索引是什么

Question

我在MYSQL 8中有下表：

create table session
(
ID                           bigint unsigned auto_increment
primary key,
session_id                   varchar(255)                                         null,
username                     varchar(255)                                         null,
session_status               varchar(255)                                         null,
session_time                 int                                                  null,    
time_created                 int                                                  null,
time_last_updated            int                                                  null,
time_ended                   int                                                  null,
date_created                 date                                                 null,
);

我正在执行以下语句：

select * from session where username = VALUE and session_id = VALUE order by time_created desc

表加速查询的最佳索引是什么？

EXPLAIN查询告诉我有两个潜在的索引，它们是：

create index username_3
on session (username, time_created);
create index username_session_id_time_created_desc
on session (username, session_id, time_created desc);

我本以为索引"username_session\id_time_created_desc"会被选中，但EXPLAIN声明说索引"username_3"会被选择。

编辑*

SHOW CREATE TABLE会话的结果：

CREATE TABLE `session` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`session_id` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`username` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`session_status` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`session_time` int(11) DEFAULT NULL,
`time_created` int(11) DEFAULT NULL,
`time_last_updated` int(11) DEFAULT NULL,
`time_ended` int(11) DEFAULT NULL,
`date_created` date DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `username_3` (`username`,`time_created`),
KEY `username_session_id_time_created_desc` (`username`,`session_id`,`time_created`)
) ENGINE=InnoDB AUTO_INCREMENT=76149265 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

EXPLAIN声明结果：

select_type: SIMPLE
type: ref
possible_keys: username_3,username_session_id_time_created_desc
key: username_3
key_len: 768
ref: const
rows: 1
Extra: Using where

Answer 1

对于此查询：

select *
from session
where username = %s and session_id = %s
order by time_created desc

最佳指标为CCD_ 1。前两列可以按任意顺序排列。

Answer 2

首先我以为你有错别字，因为你写了

create index username_session_id_time_created_desc on session (username, session_id, time_created desc);

但是您的创建表显示

KEY `username_session_id_time_created_desc` (`username`,`session_id`,`time_created`)

而不是

KEY `username_session_id_time_created_desc` (`username`,`session_id`,`time_created` DESC)

但是，我现在认为它在MySQL 8.0中使用username_3是因为backward_index_scan(我没有全部阅读(

如何判断：在简单的EXPLAIN中没有filesort，所以优化器一定可以使用username_3索引对结果集进行排序。(如果你从任何索引中删除time_created，你会看到Using filesort。如果你没有使用MySQL 8.0，可能在你的版本中也可以使用username_3索引进行排序。(

5.7中的Fiddle显示"key": "username_session_id_time_created_desc",：有时好，但并不总是。。。它可能取决于索引长度(字段长度(。

而在MySQL 8.0中，它显示了(username, session_id, time_created desc)0和"backward_index_scan": true,

由于只有3列索引，它显示出较低的查询成本，那么为什么要选择其他索引呢？

我的猜测是，2列索引要短得多，而且由于可以进行反向索引扫描，所以排序很便宜，优化器仍然希望拥有更少的IO和更多的计算。