MySQL查询因filesort性能下降，求索引优化方案

我有一张包含超过1.9亿条记录的notification表，表结构如下：

CREATE TABLE notification (
  _id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

  recipient CHAR(11) NOT NULL,
  recipient_group CHAR(11),
  topic VARCHAR(25) NOT NULL,
  identifier VARCHAR(60) NOT NULL,
  timestamp TIMESTAMP(3) NOT NULL,
  type VARCHAR(255) NOT NULL,
  actioned BIT NOT NULL DEFAULT 0,
  expiry_timestamp TIMESTAMP DEFAULT NULL,

  INDEX recipient_recipient_group_timestamp_id (recipient, recipient_group, timestamp DESC, _id DESC),
  INDEX topic_identifier (topic, identifier),
  INDEX expiry_timestamp (expiry_timestamp),
  UNIQUE recipient_recipient_group_topic_identifier (recipient, recipient_group, topic, identifier)
) CHARACTER SET ascii COLLATE ascii_bin;

我需要查询某一收件人属于指定组的、基于timestamp的所有通知，执行的查询语句如下：

explain
select * from notification
where (recipient = 'recipient' and (recipient_group = 'group' or recipient_group is null)
    and (expiry_timestamp > {ts '2018-06-26 08:00:00.0'} or expiry_timestamp is null)
    and timestamp > {ts '1970-01-01 00:00:00.0'} and type in ('TYPE'))
order by timestamp desc, _id desc limit 10;

当用户的通知数量较多时，该查询性能较差，因为MySQL在对timestamp和_id排序时使用了filesort。执行计划如下：

+----+-------------+--------------+------------+-------------+----------------------------------------------------------------------------------------------------+--------------------------------------------+---------+-------------+------+----------+----------------------------------------------------+
| id | select_type | table        | partitions | type        | possible_keys                                                                                      | key                                        | key_len | ref         | rows | filtered | Extra                                              |
+----+-------------+--------------+------------+-------------+----------------------------------------------------------------------------------------------------+--------------------------------------------+---------+-------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | notification | NULL       | ref_or_null | recipient_recipient_group_topic_identifier,recipient_recipient_group_timestamp_id,expiry_timestamp | recipient_recipient_group_topic_identifier | 23      | const,const |    2 |     5.01 | Using index condition; Using where; Using filesort |
+----+-------------+--------------+------------+-------------+----------------------------------------------------------------------------------------------------+--------------------------------------------+---------+-------------+------+----------+----------------------------------------------------+

请问是否可以通过添加或修改索引来提升查询性能？

补充：若移除where条件中的“or recipient_group is null”，MySQL会使用recipient_recipient_group_timestamp_id索引。

1. 拆分查询并合并结果 #

由于recipient_group = 'group'和recipient_group is null的OR条件导致现有复合索引无法高效发挥作用，最可靠的方式是把原查询拆成两个独立子查询，分别处理两种情况，再用UNION ALL合并结果后排序：

(SELECT * FROM notification
 WHERE recipient = 'recipient'
   AND recipient_group = 'group'
   AND (expiry_timestamp > {ts '2018-06-26 08:00:00.0'} OR expiry_timestamp IS NULL)
   AND timestamp > {ts '1970-01-01 00:00:00.0'}
   AND type IN ('TYPE')
 ORDER BY timestamp DESC, _id DESC LIMIT 10)
UNION ALL
(SELECT * FROM notification
 WHERE recipient = 'recipient'
   AND recipient_group IS NULL
   AND (expiry_timestamp > {ts '2018-06-26 08:00:00.0'} OR expiry_timestamp IS NULL)
   AND timestamp > {ts '1970-01-01 00:00:00.0'}
   AND type IN ('TYPE')
 ORDER BY timestamp DESC, _id DESC LIMIT 10)
ORDER BY timestamp DESC, _id DESC LIMIT 10;

每个子查询都能直接利用recipient_recipient_group_timestamp_id索引，避免filesort；最后合并后最多只有20条数据，排序的性能开销可以忽略。

2. 新增针对性复合索引（MySQL 8.0+适用） #

如果不想拆分查询，可以针对recipient+type+排序字段的组合建覆盖索引，同时包含expiry_timestamp避免回表：

CREATE INDEX idx_recipient_type_ts_id_expiry ON notification (
    recipient,
    type,
    timestamp DESC,
    _id DESC
) INCLUDE (recipient_group, expiry_timestamp);

或者针对recipient_group IS NULL的场景单独建函数索引：

CREATE INDEX idx_recipient_null_group_ts_id ON notification (
    recipient,
    (recipient_group IS NULL),
    timestamp DESC,
    _id DESC
) INCLUDE (type, expiry_timestamp);

这类索引能帮助优化器快速定位符合条件的数据，同时直接利用索引顺序完成排序。

3. 调整现有索引为覆盖索引 #

把现有的recipient_recipient_group_timestamp_id索引扩展为覆盖索引，包含查询需要的type和expiry_timestamp字段：

DROP INDEX recipient_recipient_group_timestamp_id ON notification;
CREATE INDEX idx_recipient_group_ts_id_type_expiry ON notification (
    recipient,
    recipient_group,
    timestamp DESC,
    _id DESC
) INCLUDE (type, expiry_timestamp);

这样即使需要过滤type和expiry_timestamp，也不需要回表查询，但由于OR条件的存在，优化器不一定会选择该索引，所以拆分查询的方式稳定性更高。

内容的提问来源于stack exchange，提问作者Lakmal