请教，这个语句如何优化？

wenyi · 2026 年9 月 2 日 09:05

表table:t_registering_aier_pay_refunds为什么全扫？
各表索引信息如下

wenyi · 2026 年9 月 2 日 09:10

mysql里面，主键索引匹配，很快

Raymond · 2026 年9 月 2 日 13:20

在tidb 执行的是hash join,mysql 里面是index join

kang · 2026 年9 月 3 日 00:30

看截图信息有限，先给几个排查方向：

检查关联字段的字符集和排序规则是否一致，不一致会导致索引失效。用 SHOW CREATE TABLE 对比下两表相关列。
确认驱动表是否选对，小表驱动大表。如果 t_registering_aier_pay_refunds 是事实表且数据量大，试试加 STRAIGHT_JOIN 强制驱动顺序。

TiDBer_milan · 2026 年9 月 3 日 00:32

来学习一下

wenyi · 2026 年9 月 3 日 00:33

像风一样的男子 · 2026 年9 月 3 日 00:56

ai的建议非常好

看了一下表结构和 SQL，这个查询的主要瓶颈不在 JOIN，而在 t_registering_pay_orders 上没有包含 created_at 的索引。逐条分析：

问题诊断

过滤 + 排序走不了同一个索引（核心问题）

WHERE 用了 hospital_id + created_at 范围 + status，ORDER BY 用 created_at DESC
现有索引只有 hospital_id 单列：MySQL 只能按医院筛出整月的数据，再对 created_at 做 filesort，最后才 LIMIT 20。医院单量大的话，排序代价非常高。

SELECT * 拖了大量 JSON 大字段：patient_info、doctor_info、order_info、pay_info、coupon_info、shipment_info……十几个 JSON 列，且三表都有 id/created_at/updated_at/order_no 同名列，结果集里也容易出歧义。
类型不匹配的隐患：t_registering_pay_orders.refund_id 是 varchar(40)，而 t_registering_aier_pay_refunds.id 是 bigint。JOIN 时发生隐式转换（数值化），虽然走 LIMIT 后每行仍是 PK 查找，但这是设计层面的脏点，极端情况（空串、非纯数字）会匹配错误。

优化方案

1. 加复合索引（最关键，一条就够）

sql

ALTER TABLE t_registering_pay_orders 
  ADD INDEX idx_hosp_created_status (hospital_id, created_at, status);

设计思路：

hospital_id 等值 → created_at 范围 + 倒序扫描（天然满足 ORDER BY created_at DESC，无 filesort）
status 放第三列 → 状态过滤在索引内完成（覆盖过滤），不匹配的行不用回表
找够 20 条立即停止扫描，扫描量从“整月订单”降到“约 20 行附近”

补充：如果该医院这 3 种状态的订单占比很低（比如全月大多行状态都不在 IN 列表里），可改用 (hospital_id, status, created_at)，按 3 个状态各走一段倒序范围再取 top 20，两个方案二选一，用 EXPLAIN 验证。一般业务上 FINISHED/CANCELLED 是大头，前者更优。

2. SQL 改写（显式列 + 表别名）

sql

SELECT
    o.id, o.order_no, o.created_at, o.pay_at, o.status, o.pay_status,
    o.total_price, o.pay_price, o.refund_no, o.refund_at, o.pay_type,
    a.id          AS account_id,
    a.name        AS account_name,
    a.phone       AS account_phone,
    r.id          AS refund_id,
    r.out_refund_no, r.refund_amount, r.status AS refund_status, r.refund_at AS refund_real_at
FROM t_registering_pay_orders o
JOIN t_auth_accounts a 
     ON a.id = o.account_id          -- PK 关联，无需处理
LEFT JOIN t_registering_aier_pay_refunds r 
     ON r.id = o.refund_id           -- 注意 varchar ↔ bigint 类型不一致
WHERE o.hospital_id = 1739
  AND o.created_at >= '2026-08-04 00:00:00'
  AND o.created_at <  '2026-09-03 00:00:00'
  AND o.status IN ('CONFIRM_FAIL', 'FINISHED', 'CANCELLED')
ORDER BY o.created_at DESC
LIMIT 20;

JOIN 两个表都走主键点查（每行 ≤1 次 PK lookup，总共 ≤20 次），这部分本来就不是瓶颈，保持即可。

3. 验证

sql

EXPLAIN ANALYZE SELECT ... ;  -- MySQL 8.0+

加索引后应看到：o 表 key = idx_hosp_created_status，Extra 里不再出现 Using filesort，扫描行数（rows）降到几十以内；两个 JOIN 均为 eq_ref。

4. 建议顺手修的两个隐患

refund_id varchar(40) ↔ id bigint 类型对齐：要么把 refund_id 改成 bigint（需确认该列是否存过非数字值，先 SELECT refund_id FROM t_registering_pay_orders WHERE refund_id IS NOT NULL AND refund_id NOT REGEXP '^[0-9]+ 排查），要么关联时显式 r.id = CAST(o.refund_id AS UNSIGNED)。
时间跨度整月的查询建议按 created_at 分页时带上 created_at 游标（WHERE created_at < 上一页最后一条）而不是 LIMIT offset，越翻越深也不会变慢。

一句话总结：加 (hospital_id, created_at, status) 复合索引，让“按医院过滤 + 时间倒序 + LIMIT 20”变成一次索引倒序扫描立即截断；同时把 SELECT * 换成显式列，避免拖十几个 JSON 大字段。 LIMIT 10;排查），要么关联时显式DISCOURSE_PLACEHOLDER_41`。

时间跨度整月的查询建议按 DISCOURSE_PLACEHOLDER_42 分页时带上 DISCOURSE_PLACEHOLDER_43 游标（DISCOURSE_PLACEHOLDER_44）而不是 DISCOURSE_PLACEHOLDER_45，越翻越深也不会变慢。

一句话总结：加 DISCOURSE_PLACEHOLDER_46 复合索引，让“按医院过滤 + 时间倒序 + LIMIT 20”变成一次索引倒序扫描立即截断；同时把 DISCOURSE_PLACEHOLDER_47 换成显式列，避免拖十几个 JSON 大字段。

wbslxw · 2026 年9 月 3 日 08:17

全表扫描一般就两个原因：一是关联或过滤条件用的列在这张表上没有可用索引，二是优化器选了 Hash Join 本来就会整表读。你这里执行计划变了，变成hash join

一只大芒果 · 2026 年9 月 3 日 09:15

| refund_id | varchar(40) | YES | MUL | NULL | |
| id | bigint | NO | PRI | NULL | auto_increment |
做了隐士类型转换了，索引没有派上用场，走了全表扫描

zhanggame1 · 2026 年9 月 3 日 09:37

t_registering_pay_orders.refund_id = t_registering_aier_pay_refunds.id

检查这两个字段是主键吗，字段类型是啥