MariaDB 4GB级InnoDB表SELECT查询耗时过高的优化咨询

• 当前查询27-60秒的耗时完全不正常，针对仅返回1条最新记录的需求，优化后应达到毫秒级、最多不超过200ms的水平。
• 核心瓶颈不是锁等待，也不是SSD磁盘性能不足，本质是缺少匹配查询的索引，导致数据库被迫做全表扫描+磁盘随机读。

1. 现有索引完全不匹配你的查询逻辑
   你的表当前仅存在两个索引：
   • 主键索引id（自增）
   • 外键索引user_id
     对于WHERE type = 'PURCHASE' ORDER BY created_at DESC LIMIT 1的查询，没有任何可用索引，MariaDB的执行路径只能是：

   沿着主键聚簇索引全量扫描整个表，逐行判断type是否等于PURCHASE，把所有匹配的记录收集后按created_at做内存排序，再取第一条。
   由于绝大多数数据都不是PURCHASE类型，这个过程可能需要扫描数百万到上千万行数据；加上表本身4.1G，远超过4G内存实例的InnoDB缓冲池容量，大部分扫描的表页都需要从通用SSD磁盘读取，随机读的IO开销直接把查询耗时拉到几十秒。

2. 并发INSERT的影响是次要放大效应，不是锁等待

   InnoDB的普通SELECT为MVCC快照读，不会请求行锁，也不会等待并发INSERT的锁资源。你观察到的写入时查询更慢的现象，本质是大量新写入的记录会挤占缓冲池空间，把旧的表页挤出内存，导致扫描时必须从磁盘读数据，进一步放大了IO开销。
   只有在SERIALIZABLE隔离级别下，SELECT才会加共享锁等待写锁，常规生产环境的REPEATABLE READ隔离级别下不存在这个问题。

直接创建匹配查询逻辑的联合索引，这是投入产出比最高的改动，支持Online无锁建索引，执行过程不会阻塞线上业务：

-- 生产环境无锁建索引，不阻塞业务读写
ALTER TABLE `tablename` 
ADD INDEX `idx_type_created` (`type`, `created_at` DESC),
ALGORITHM=INPLACE, LOCK=NONE;

这个索引的作用原理：

• 先按type做等值匹配定位，索引中type='PURCHASE'的所有记录是按照created_at倒序存储的
• 数据库不需要扫描全表、也不需要做额外排序，直接读取该索引区间的第一行，就能拿到最新的PURCHASE记录对应的主键id
• 拿着这个主键ID回表读取完整的记录字段（data），整个过程最多只需要读取2-3个数据页，完全在内存中完成，耗时自然降到毫秒级。

建完索引后可以执行EXPLAIN验证执行计划，应该看到type为ref、key为idx_type_created、扫描行数为个位数，不会出现Using filesort的提示，说明索引已生效。

注意：不建议把data（longtext类型）加入这个联合索引。大字段存储会让索引体积膨胀数倍，反而降低性能，因为longtext的溢出页本身就不是顺序存储的，回表读取的开销极小，完全可以接受。

4G内存的云实例默认的MariaDB配置通常偏保守，调整后可以进一步减少磁盘IO：

• 将innodb_buffer_pool_size调整为2G，把更多热点索引、数据页放在内存中，减少磁盘读取。4G内存的实例中，该配置不会导致OOM：系统预留1G内存，剩下1G留给MySQL的连接、临时表开销，2G给InnoDB缓冲池是安全值。
• 不要将type字段的长度保留为varchar(255)：由于type是枚举类固定取值，将其长度缩小到varchar(32)甚至tinyint枚举类型，可以进一步缩小索引体积，提升索引的扫描效率。

• 随着表持续增大，可以按created_at做范围分区，进一步缩小单分区的扫描范围
• 如果PURCHASE类型的记录占比极低，可以将这类记录单独拆分到独立关联表，减少主表的体积

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