MariaDB小表查询性能优化求助：2600+行查询耗时超预期

针对各慢查询的索引优化 #

1. 评论排序查询（耗时0.87s） #

SELECT JS.id, JS.url_rewrite, JS.title_en 
FROM jsdata JS 
WHERE JS.display=1 AND JS.comments>0 
ORDER BY comments DESC LIMIT 0,10;

当前查询扫描全表，需创建覆盖联合索引，让查询无需回表即可获取结果：

CREATE INDEX idx_display_comments_cover ON jsdata (display, comments DESC, id, url_rewrite, title_en);

索引逻辑：先过滤display=1的行，再按comments降序排序，同时包含查询所需的所有字段，直接从索引返回数据。

2. 浏览量排序查询（耗时0.64s） #

SELECT JS.id, JS.url_rewrite, JS.title_en 
FROM jsdata JS WHERE JS.display=1 AND JS.views>0 
ORDER BY views DESC LIMIT 0,10;

优化为覆盖索引减少查询开销：

CREATE INDEX idx_display_views_cover ON jsdata (display, views DESC, id, url_rewrite, title_en);

3. 分类统计查询（耗时0.66s） #

SELECT COUNT(*) AS sum FROM jsdata JS  WHERE JS.display=1 AND cat_id = 10;

创建联合索引支持过滤与快速统计：

CREATE INDEX idx_display_catid ON jsdata (display, cat_id);

InnoDB可直接通过索引完成COUNT统计，无需扫描全表。

4. 分页查询（耗时1.03s） #

SELECT JS.id, JS.url_rewrite, JS.title_en AS title, JS.description_en AS description, 
       JS.submitday, JS.image, JS.comments, JS.stores, JS.cat_id,  JS.sub_id
FROM jsdata JS 
WHERE JS.display=1  
ORDER BY JS.submitday DESC LIMIT 2220,10;

• 索引优化：创建覆盖索引包含所有查询字段：

  CREATE INDEX idx_display_submitday_cover ON jsdata (display, submitday DESC, id, url_rewrite, title_en, description_en, image, comments, stores, cat_id, sub_id);
  

• 分页逻辑优化：大offset会导致MySQL扫描大量无用行后丢弃，改用键值分页（需记录上一页的submitday和id）：

  SELECT JS.id, JS.url_rewrite, JS.title_en AS title, JS.description_en AS description, 
         JS.submitday, JS.image, JS.comments, JS.stores, JS.cat_id,  JS.sub_id
  FROM jsdata JS 
  WHERE JS.display=1 AND JS.submitday < [上一页最后一条的submitday] AND JS.id < [上一页最后一条的id]
  ORDER BY JS.submitday DESC, id DESC LIMIT 10;
  

  利用索引直接定位起始位置，避免全表扫描。

5. 全量统计查询（耗时1.07s） #

SELECT COUNT(id) AS sum FROM jsdata WHERE display=1;

创建单字段索引支持快速统计：

CREATE INDEX idx_display ON jsdata (display);

通用优化措施 #

• 清理冗余索引：表中PRIMARY KEY (id)与UNIQUE KEY id (id)重复，删除后者：

  ALTER TABLE jsdata DROP INDEX id;
  

• 更新统计信息：让优化器获取准确的表数据分布：

  ANALYZE TABLE jsdata;
  

• 调整InnoDB配置：确保innodb_buffer_pool_size足够缓存整个表（当前表仅2653行，设置为64M以上即可），减少磁盘IO。
• 升级数据库版本：MariaDB 5.5较老旧，升级至10.4+版本可获得更优的查询优化器和性能提升。

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