Yeats' Wiki

外观

性能反模式——LIKE 前导通配、无 LIMIT 排序与深度分页 ​

技术栈：MyBatis + MySQL 适合谁读：需要治理手写 SQL 中慢查询与全表扫描风险的开发者

1. 问题背景 ​

注入和 select * 之外，最磨人的是性能反模式：LIKE '%xxx%' 前导通配、ORDER BY 没 LIMIT、GROUP BY 列过多、COUNT(*)、深分页。单个看不致命，但集中在大表和设备历史数据上，就是慢查询和数据库压力的源头。

2. LIKE 前导通配 %xxx% ​

2.1 为什么索引会失效 ​

数据库的索引（B+Tree）本质是有序排列的。前缀匹配 LIKE 'abc%' 能顺着有序性快速定位；但前导通配 LIKE '%abc%' 开头是未知的，数据库不知道从树的根节点往哪边走，只能一行一行全表扫描。

text
索引树（有序）
  根
  ├─ a-c → abc...   ← 前缀匹配 'abc%' 能顺着走下去，命中
  └─ d-f
前导通配 '%abc%'：开头未知，树根不知往哪走 → 只能一行行全表扫描

2.2 问题代码 ​

xml
<select id="selectMenuList" resultMap="BaseResultMap">
    <include refid="selectMenuVo"/>
    <where>
        <if test="nameLike != null and nameLike != ''">
            AND name like concat('%', #{nameLike}, '%')   <!-- 索引失效 -->
        </if>
    </where>
    order by parent_id, order_num
</select>

2.3 EXPLAIN 对比 ​

前导通配时，EXPLAIN 的 type 通常是 ALL（全表扫描），key 为 NULL：

text
改造前：type=ALL,    key=NULL,     rows=102400   ← 扫了 10 万行
改造后：type=range,  key=idx_name, rows=12       ← 只扫 12 行

2.4 修复 ​

  • 业务上能收敛就收敛：若场景允许后缀匹配，如按编码前缀搜，改成 LIKE CONCAT(#{name}, '%')，能命中索引
  • 建合适的索引：对固定列可用 MySQL 8.0 的函数索引，或把搜索列冗余成反转串建索引做后缀匹配
  • 上专用检索：海量文本的模糊搜索，迁移到 Elasticsearch 或全文索引 MATCH ... AGAINST
  • 兜底：实在避免不了，务必加 LIMIT 并评估数据量

3. ORDER BY 无 LIMIT ​

列表或统计类查询 ORDER BY ... 不带 LIMIT，结果集很大时，数据库要做全量排序，可能用临时表落盘，内存和 CPU 开销陡增。

xml
<!-- 设备类型分布统计：ORDER BY 无 LIMIT -->
SELECT it.type_name, COUNT(i.device_id) AS total
FROM sensor_type it LEFT JOIN device i ON it.type_id = i.type_id
GROUP BY it.type_id
ORDER BY total DESC

修复：

  • 列表查询必须分页：ORDER BY ... LIMIT #{pageSize} OFFSET #{offset}
  • 统计类确认上界：像设备类型分布这种枚举值有限、结果集固定的，加注释说明结果集有界，或显式 LIMIT 100 兜底

4. GROUP BY 列过多 ​

有的语句 GROUP BY 了 22 个列，这是典型的为了通过 ONLY_FULL_GROUP_BY 校验而把列全塞进去：

xml
SELECT i.*, z.zone_name, it.type_name
FROM device i
JOIN sys_user_zone uz ON i.zone_id = uz.zone_id
...
GROUP BY i.device_id, i.device_name, i.device_mac, i.type_id, ... (22 列)
ORDER BY i.device_id DESC

GROUP BY 列越多，分组键越大、越用不上索引、临时表越膨胀。这里其实只想按设备去重，却把几乎所有展示列都塞进 GROUP BY。

修复：GROUP BY 只放分组维度，如 i.device_id，其余展示列用聚合函数，或先子查询去重再 JOIN 取属性：

xml
SELECT i.device_id, i.device_name, i.device_mac, i.type_id,
       z.zone_name, it.type_name
FROM (
    SELECT DISTINCT device_id FROM device WHERE ...   <!-- 先按维度去重 -->
) d
JOIN device i ON i.device_id = d.device_id
JOIN sys_user_zone uz ON i.zone_id = uz.zone_id
JOIN sensor_type it ON i.type_id = it.type_id
...

5. COUNT(*) 与深分页 ​

5.1 COUNT(*) ​

大表上，尤其带 JOIN 的 COUNT(*) 会触发全表或全索引扫描。修复：

  • 覆盖索引：让 COUNT 走最小索引，SELECT COUNT(*) FROM t WHERE status = ? 配合 (status) 索引
  • 缓存计数：高频统计用 Redis 增量维护，避免每次实时 COUNT
  • 近似统计：非精确场景用 EXPLAIN 的估算行数或分区统计

5.2 深分页：LIMIT 100000, 20 的坑 ​

LIMIT 100000, 20 仍会先扫前 10 万行再丢弃，越翻越慢。用延迟关联或游标分页：

xml
<!-- 旧代码（已删除） -->
SELECT * FROM sensor_data ORDER BY collect_time DESC LIMIT #{offset}, #{pageSize}

<!-- 新代码：先按索引取 id，再回表取明细 -->
SELECT s.* FROM sensor_data s
INNER JOIN (
    SELECT id FROM sensor_data
    WHERE device_id = #{deviceId}
    ORDER BY collect_time DESC
    LIMIT #{offset}, #{pageSize}
) t ON s.id = t.id

或者用游标：WHERE collect_time < #{lastTime} ORDER BY collect_time DESC LIMIT 20，每次翻页只扫少量行。

6. 注意事项 ​

  1. 加索引要和 SQL 改造配套。前导通配改成前缀匹配后，记得建对应索引，否则只是换了写法没解决性能
  2. 分页不是万能。深分页要用延迟关联或游标，别只加 LIMIT
  3. 先量后改。用慢查询日志或 EXPLAIN 确认哪些语句真的慢，优先改最慢的，避免无谓改动引入风险

7. 总结 ​

反模式表现危害修复
前导通配 LIKElike '%x%'索引失效、全表扫前缀匹配/ES/全文索引
ORDER BY 无 LIMIT列表/统计无分页全量排序、内存暴涨必加分页/有界兜底
GROUP BY 过多列全列分组临时表膨胀、索引失效只分维度列 + 子查询
COUNT(*)大表实时计数全扫描覆盖索引/缓存计数
深分页LIMIT 100000,20扫前 N 行丢弃延迟关联/游标

最后一篇讲死代码与接口一致性，它们不直接拖性能，却会误导维护、掩盖真正的注入点。