mysql - 此查询的最佳索引是什么？

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

标签： mysqlsqlindexing

Answer 1

对于此查询：

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

最优指数为(username, session_id, time_created desc)。前两列可以按任意顺序排列。