使用order by和limit by子句优化mysql查询

Question

我有一个包含成员审计信息的表。

我有一个查询，比如：

select columnsname from tablename 
where member id =? 
order by audit_date desc, audit_time desc 
limit 0,150

执行此查询需要很长时间。我要查询的表包含大约20到30亿条记录。

我不能删除order by子句，因为我希望按照审计日期和审计时间的降序进行记录。我也不能删除limit by子句，因为我需要分页功能。

我已经对成员id、audit_date和audit_time应用了索引。

有没有人可以帮助我优化这个查询，或者建议我可以在Java层中应用的其他方法来实现这个功能？

下面是我的explain输出。左侧是列名，右侧是列名的值。

┌───────────────┬──────────────────────────────────┐
│ Column Name   │ Column Value                     │
├───────────────┼──────────────────────────────────┤
│ ID            │ 1                                │
│ Select Type   │ simple                           │
│ table         │ member_audit_info                │
│ type          │ ref                              │
│ possible_keys │ member_audit_info_memebrid_index │
│ key           │ member_audit_info_memebrid_index │
│ ref           │ const                            │
│ rows          │ 208988                           │
│ extra         │ Using where,Using filesort       │
└───────────────┴──────────────────────────────────┘

Answer 1

使用同时包含日期和时间部分的单列TIMESTAMP或DATETIME，并基于该单列进行排序，这可能会提高查询的性能。

示例查询：

select columnsname from tablename 
where member_id =? 
order by audit_timestamp desc 
limit 0,150

注释：(关于查询性能的更多要点)

此限制列大小应为indexed.

• fetch required columns

• reduce
• (pagination size)