MySQL查询执行过慢引发API超时，如何优化提升查询运行速度？

优化方案 #

核心问题根因 #

从EXPLAIN执行结果和SQL写法来看，性能瓶颈主要来自以下几点：

1. SQL_CALC_FOUND_ROWS会忽略LIMIT限制，扫描所有符合条件的行来统计总条数，海量数据下开销极高
2. 主查询tbl_master使用的索引过滤性极差，仅12.5%的行符合最终筛选条件，需要大量回表过滤
3. 子查询中的tbl_irctc_refund_settled_txns表无可用索引，触发全表扫描
4. NOT IN子查询的执行效率远低于关联查询，且存在逻辑冗余（子查询不需要额外关联tbl_master）

具体优化措施 #

• 移除SQL_CALC_FOUND_ROWS，如果需要总条数单独执行计数查询，不要和数据查询合并
• 把NOT IN子查询替换为LEFT JOIN + IS NULL的写法，同时去掉子查询冗余的tbl_master关联
• 新增两个联合索引提升查询效率：
  1. 针对主表tbl_master：ALTER TABLE tbl_master ADD INDEX idx_profile_status_txn_date(PROFILEID, TXN_STATUS, txn, MERCHANT_TXN_DATE_TIME);
  2. 针对退款表tbl_irctc_refund_settled_txns：ALTER TABLE tbl_irctc_refund_settled_txns ADD INDEX idx_cancel_date_txnid(CANCELLATION_DATE, txnid);

优化后SQL #

SELECT 
   tm.TXNID,
   tm.MERCHANT,
   tm.AMOUNT,
   tm.MERCHANT_TXN_DATE_TIME,
   tm.TXN_TYPE,
   CONCAT(PG_COMPANY,'-',cm.CHANNEL) AS BANK
FROM 
   tbl_master AS tm 
   JOIN tbl_pg_rates AS c ON c.merchant_channel_pg_id=tm.merchant_channel_pg_id
   INNER JOIN tbl_pg_master AS cpm ON c.channel_pg_id=cpm.channel_pg_id
   INNER JOIN tbl_channel_master AS cm ON cpm.CHANNELID=cm.CHANNELID 
   INNER JOIN tbl_payment_gateway_master AS pgm ON cpm.PGID=pgm.PGID 
   -- 替换NOT IN的左连接
   LEFT JOIN `tbl_irctc_refund_settled_txns` iref 
     ON tm.txnid = iref.txnid 
     AND iref.CANCELLATION_DATE>='2021-09-01'
     AND iref.CANCELLATION_DATE<='2021-09-16'
WHERE 
   tm.MERCHANT_TXN_DATE_TIME>=UNIX_TIMESTAMP('2021-09-01 00:00:00')
   AND tm.MERCHANT_TXN_DATE_TIME<=UNIX_TIMESTAMP('2021-09-16 23:59:59') 
   AND tm.txn IN('netbnk','pg','ppc','upi')
   AND tm.PROFILEID=28688 
   AND tm.TXN_STATUS=1 
   -- 过滤掉存在退款记录的交易
   AND iref.txnid IS NULL
LIMIT 0,2000;

如果确实需要获取符合条件的总条数，单独执行以下计数查询：

SELECT COUNT(*) AS total
FROM 
   tbl_master AS tm 
   LEFT JOIN `tbl_irctc_refund_settled_txns` iref 
     ON tm.txnid = iref.txnid 
     AND iref.CANCELLATION_DATE>='2021-09-01'
     AND iref.CANCELLATION_DATE<='2021-09-16'
WHERE 
   tm.MERCHANT_TXN_DATE_TIME>=UNIX_TIMESTAMP('2021-09-01 00:00:00')
   AND tm.MERCHANT_TXN_DATE_TIME<=UNIX_TIMESTAMP('2021-09-16 23:59:59') 
   AND tm.txn IN('netbnk','pg','ppc','upi')
   AND tm.PROFILEID=28688 
   AND tm.TXN_STATUS=1 
   AND iref.txnid IS NULL;

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