ob: 社区版 4.3.5.3 ，mysql模式
最近有个sql发现执行时间长：
SELECT * FROM (
SELECT a.order_name, a.order_id, a.cust_id, a.use_type, a.state, a.create_time, a.state_date, a.dev_id,
a.exec_print_flag, a.checked_flag, a.station_ver, g.pay_method, g.amount, g.bean_amt, g.channel_amt,
(SELECT mobile FROM t_cust cust WHERE cust.cust_id = a.cust_id) mobile,
d.station_id, d.station_name, d.merchant_id, d.set_id, e.merchant_name, e.merchant_short_name, g.return_amt AS return_fee,
s.set_name, a.checked_ops_name, a.checked_time, a.print_mode,
(SELECT w.total_set_name FROM t_total_set w WHERE w.total_set_id = s.total_set_id) total_set_name,
(SELECT SUM(g.printed_page) FROM t_order_file g WHERE g.order_id = a.order_id) printed_page,
(SELECT SUM(CEILING(g.page_num / g.shrink_plate_num) * g.copy_num) FROM t_order_file g WHERE g.order_id = a.order_id) page_num,
(SELECT SUM(g.if_color) FROM t_order_file g WHERE g.order_id = a.order_id) if_color_num,
(SELECT COUNT(1) FROM t_order_file g WHERE g.order_id = a.order_id) order_count, ‘’ AS startPage, ‘’ AS endPage,
(SELECT GROUP_CONCAT(CONCAT(t.file_id, ‘:’, t.err_msg) SEPARATOR ‘;$$$’) FROM t_order_file t WHERE t.order_id = a.order_id AND err_msg != ‘’) errMsgConcat,
(SELECT GROUP_CONCAT(CONCAT(‘退款描述’, t.return_id, ‘:’, t.problem_desc) SEPARATOR ‘;$$$’) FROM t_order_return t WHERE t.order_id = a.order_id AND problem_desc != ‘’) problemDescConcat
FROM t_order a
LEFT JOIN t_station d ON a.station_id = d.station_id
LEFT JOIN t_device c ON a.dev_id = c.dev_id
LEFT JOIN t_set s ON a.set_id = s.set_id
LEFT JOIN t_merchant e ON s.merchant_id = e.merchant_id
LEFT JOIN t_pay g ON a.pay_id = g.pay_id
WHERE 1 = 1 AND a.create_time >= ‘2025-09-10 00:00:00’ AND a.create_time <= ‘2025-09-10 23:59:59’
AND (EXISTS (SELECT 1 FROM t_order_file s WHERE s.order_id = a.order_id AND s.err_msg LIKE CONCAT(’%’, ‘白纸’, ‘%’)) OR EXISTS (SELECT 1 FROM t_order_return s WHERE s.order_id = a.order_id AND s.problem_desc LIKE CONCAT(’%’, ‘白纸’, ‘%’)))
AND a.station_id IS NOT NULL)
耗时4秒多
于是想做优化，新建了一张表t_order_file_msg 把原来t_order_file的order_file_id,order_id,err_msg3个字段拿过来，然后把t_order_file中err_msg有值的记录导入新表，
这样做like的时候，记录数就少很多
新的sql是：
SELECT * FROM (
SELECT a.order_name, a.order_id, a.cust_id, a.use_type, a.state, a.create_time, a.state_date, a.dev_id,
a.exec_print_flag, a.checked_flag, a.station_ver, g.pay_method, g.amount, g.bean_amt, g.channel_amt,
(SELECT mobile FROM t_cust cust WHERE cust.cust_id = a.cust_id) mobile,
d.station_id, d.station_name, d.merchant_id, d.set_id, e.merchant_name, e.merchant_short_name, g.return_amt AS return_fee,
s.set_name, a.checked_ops_name, a.checked_time, a.print_mode,
(SELECT w.total_set_name FROM t_total_set w WHERE w.total_set_id = s.total_set_id) total_set_name,
(SELECT SUM(g.printed_page) FROM t_order_file g WHERE g.order_id = a.order_id) printed_page,
(SELECT SUM(CEILING(g.page_num / g.shrink_plate_num) * g.copy_num) FROM t_order_file g WHERE g.order_id = a.order_id) page_num,
(SELECT SUM(g.if_color) FROM t_order_file g WHERE g.order_id = a.order_id) if_color_num,
(SELECT COUNT(1) FROM t_order_file g WHERE g.order_id = a.order_id) order_count, ‘’ AS startPage, ‘’ AS endPage,
(SELECT GROUP_CONCAT(CONCAT(t.file_id, ‘:’, t.err_msg) SEPARATOR ‘;$$$’) FROM t_order_file t WHERE t.order_id = a.order_id AND err_msg != ‘’) errMsgConcat,
(SELECT GROUP_CONCAT(CONCAT(‘退款描述’, t.return_id, ‘:’, t.problem_desc) SEPARATOR ‘;$$$’) FROM t_order_return t WHERE t.order_id = a.order_id AND problem_desc != ‘’) problemDescConcat
FROM t_order a
LEFT JOIN t_station d ON a.station_id = d.station_id
LEFT JOIN t_device c ON a.dev_id = c.dev_id
LEFT JOIN t_set s ON a.set_id = s.set_id
LEFT JOIN t_merchant e ON s.merchant_id = e.merchant_id
LEFT JOIN t_pay g ON a.pay_id = g.pay_id
WHERE 1 = 1 AND a.create_time >= ‘2025-09-10 00:00:00’ AND a.create_time <= ‘2025-09-10 23:59:59’
AND (EXISTS (SELECT 1 FROM t_order_file_msg s WHERE s.order_id = a.order_id AND s.err_msg LIKE CONCAT(’%’, ‘白纸’, ‘%’)) OR EXISTS (SELECT 1 FROM t_order_return s WHERE s.order_id = a.order_id AND s.problem_desc LIKE CONCAT(’%’, ‘白纸’, ‘%’)))
AND a.station_id IS NOT NULL)
只是把
SELECT 1 FROM t_order_file s WHERE s.order_id = a.order_id AND s.err_msg LIKE CONCAT(’%’, ‘白纸’, ‘%’)
改为
SELECT 1 FROM t_order_file_msg s WHERE s.order_id = a.order_id AND s.err_msg LIKE CONCAT(’%’, ‘白纸’, ‘%’)
结果每次都是报超时：
查询：SELECT * FROM ( SELECT a.order_name, a.order_id, a.cust_id, a.use_type, a.state, a.create_time, a.state_date, a.dev_id, a.exec_p…
错误代码： 4012
Timeout, query has reached the maximum query timeout: 50000000(us), maybe you can adjust the session variable ob_query_timeout or query_timeout hint, and try again.