慢SQL查询排查请求：查询耗时超15s、CPU占用超100%

先直接说核心问题：这两个查询慢的根源是缺少针对性的索引，加上分页查询用了低效的OFFSET，再配合老版本数据库可能的配置不足，导致全表扫描、大量磁盘IO和CPU过载。

一、为什么这两个查询这么慢？ #

1. 分页查询的问题 #

你的第一个查询：

SELECT * FROM items WHERE stock = 1 AND hide = 0 ORDER BY id DESC LIMIT 36 OFFSET 33804

• 没有针对stock和hide的索引，数据库只能遍历整个主键索引（id），逐行检查stock和hide的值，这本质是全表扫描。
• OFFSET 33804意味着数据库要先找出前33804+36条符合条件的行，然后丢弃前33804条，只返回最后36条。数据量越大，这个丢弃的过程越耗资源，CPU自然拉满。

2. 筛选信息查询的问题 #

第二个查询：

SELECT MANUFACTURER, sizes, store FROM items WHERE stock = 1 AND hide = 0

• 同样没有针对stock和hide的索引，数据库需要扫描全表，筛选出符合条件的行后，还要回表（从主键索引里取对应的字段值），31万行的规模下，这个过程会产生大量IO和CPU计算。

二、具体优化步骤 #

1. 创建针对性的复合索引 #

这是最立竿见影的优化，直接让查询从全表扫描变成索引扫描：

-- 优化分页查询：覆盖过滤条件+排序字段，避免全表扫描和额外排序
CREATE INDEX idx_stock_hide_id ON items (stock, hide, id DESC);

-- 优化筛选查询：覆盖过滤条件+需要返回的字段（覆盖索引），无需回表
CREATE INDEX idx_stock_hide_filter ON items (stock, hide, MANUFACTURER, sizes, store);

• 第一个索引：数据库可以直接通过stock和hide快速定位符合条件的行，并且索引本身已经按id DESC排序，不需要额外排序，OFFSET也能利用索引快速定位。
• 第二个索引：索引里包含了所有需要返回的字段，数据库不需要再去主键索引里取数据（回表），直接从索引里读取结果，速度会快很多。

2. 用「键集分页」替代OFFSET分页 #

OFFSET在分页靠后时效率极低，推荐改用键集分页（也叫游标分页）：
比如你现在要查第940页，先记住第939页最后一条数据的id（假设是123456），然后修改查询为：

SELECT * FROM items WHERE stock = 1 AND hide = 0 AND id < 123456 ORDER BY id DESC LIMIT 36;

这样数据库可以直接通过索引定位到id < 123456的符合条件的行，不需要扫描前面的33804条数据，分页越靠后，性能提升越明显。

3. 调整InnoDB缓冲池配置 #

你的表数据量有327M，而当前InnoDB索引只有12M，推测innodb_buffer_pool_size可能设置得太小（默认可能只有128M），导致大量数据无法缓存到内存，频繁读磁盘，CPU因为处理IO过载。

• 查看当前配置：SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
• 修改配置：如果服务器是专门跑数据库的，建议把innodb_buffer_pool_size设置为服务器内存的50%-70%（比如服务器有4G内存，就设为2G），修改my.cnf或my.ini后重启数据库。

4. 考虑升级数据库版本 #

你用的MariaDB 10.0.29是比较老的版本（发布于2016年），后续版本对InnoDB的性能有不少优化，比如更好的索引管理、查询优化器改进等，条件允许的话可以升级到稳定的新版本。

三、效果预期 #

创建索引后，两个查询的耗时应该会从十几秒降到毫秒级，CPU占用也会大幅下降。配合键集分页和缓冲池调整，完全可以满足访客的等待需求。

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