大型数据库MySQL慢查询问题：简单查询耗时20秒排查求助

1. 无有效索引导致全表扫描：从慢查询日志可以看到Rows_examined: 79212，与表总数据量一致，说明查询触发了全表扫描，没有命中合适的索引。数据库需要逐行过滤typ和benutzer条件，再对所有符合条件的结果执行排序操作，最终只取前50条，大量无用IO和排序操作导致耗时飙升。
2. 数据库配置不合理：服务器配备10GB内存，但innodb_buffer_pool_size仅设置为512M，若表为InnoDB引擎，过小的缓冲池无法缓存表数据和索引，每次查询都需要读取磁盘，进一步放大了查询耗时。另外query_cache_size设置为512M过大，会引发严重的缓存锁竞争，sort_buffer_size设置为64M也过高，每个连接都会独立分配该内存，容易造成内存浪费甚至溢出。

• 优先添加联合索引（优化效果最显著）：创建覆盖查询条件和排序字段的联合索引，避免全表扫描和额外排序操作，执行以下SQL即可：

ALTER TABLE emailarchiv ADD INDEX idx_typ_benutzer_datum (typ, benutzer, datum DESC);

该索引命中最左前缀原则，过滤完typ和benutzer条件后，同条件下的datum已经按降序排列，可直接取前50条返回，优化后查询耗时可降到毫秒级。

• 调整my.cnf配置：适配10GB内存的服务器配置，修改以下参数后重启MariaDB生效：

# 若表为InnoDB引擎，设置为物理内存的50%-70%，这里建议设为6G
innodb_buffer_pool_size = 6G
# 调小查询缓存，避免锁竞争，也可直接设为0关闭查询缓存
query_cache_size = 64M
query_cache_limit = 2M
# 调小排序缓存，避免内存浪费
sort_buffer_size = 2M

• 可选优化：将SELECT *替换为业务实际需要的字段，若所需字段都包含在联合索引中，可触发覆盖索引，无需回表读取行数据，性能进一步提升。

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