请教一个sql的优化方法

OceanBase 技术问题
#1

ob：社区版 4.3.5.3

今天遇到一个sql，执行时间要2秒多

SELECT
b.set_name,
c.station_name,
( SELECT nick_name FROM t_cust_reg f WHERE f.cust_id = a.cust_id LIMIT 1 ) nick_name,
e.mobile,
d.code_info,
a.create_time,
a.finish_time,
a.state,
a.err_msg,
a.pay_time
FROM
t_cust_ops a,
t_set b,
t_station c,
t_fault_code d,
t_cust e
WHERE
a.set_id = b.set_id
AND a.station_id = c.station_id
AND a.fault_code = d.CODE
AND a.cust_id = e.cust_id
ORDER BY create_time DESC LIMIT 10,10

尝试所用涉及到的字段单独建索引，建复合索引，作用都不大。
后来从a开始逐个表增加，发现最后e表加上来以后，速度就慢了，

索引没有什么作用，个人感觉非常奇怪，请高人指教，多谢！！！

2 个赞
#3

explain extended 发下执行计划呢

#4

set ob_enable_show_trace=on;
SELECT
b.set_name,
c.station_name,
( SELECT nick_name FROM t_cust_reg f WHERE f.cust_id = a.cust_id LIMIT 1 ) nick_name,
e.mobile,
d.code_info,
a.create_time,
a.finish_time,
a.state,
a.err_msg,
a.pay_time
FROM
t_cust_ops a,
t_set b,
t_station c,
t_fault_code d,
t_cust e
WHERE
a.set_id = b.set_id
AND a.station_id = c.station_id
AND a.fault_code = d.CODE
AND a.cust_id = e.cust_id
ORDER BY create_time DESC LIMIT 10,10;

show trace;

#5

±----------------------------------±--------------------------------------±-------------±-------------±----------±--------------------±--------------------±------±--------±--------------------+
| set_name | station_name | nick_name | mobile | code_info | create_time | finish_time | state | err_msg | pay_time |
±----------------------------------±--------------------------------------±-------------±-------------±----------±--------------------±--------------------±------±--------±--------------------+
| 攀枝花学院西苑校区 | 笃行楼1号机 | NULL | 13568641768 | 卡纸 | 2025-04-10 21:50:46 | 2025-04-10 21:53:01 | 30 | | 2025-04-10 21:53:09 |
| 成都工业学院 | 图书馆一层大厅楼梯旁3号机 | NULL | W13547588979 | 卡纸 | 2025-03-04 08:54:46 | 2025-03-25 10:07:26 | 21 | NULL | NULL |
| 河海大学常州校区 | 13-14号楼大厅 | NULL | W13092069869 | 卡纸 | 2025-01-08 13:25:54 | 2025-01-08 13:30:53 | 30 | | 2025-01-08 13:30:55 |
| 山东师范大学长清湖校区 | 梅苑1舍 | NULL | 17397876799 | 卡纸 | 2024-12-25 16:20:03 | 2024-12-25 16:20:17 | 30 | | 2024-12-25 16:20:40 |
| 河海大学常州校区 | 47-48号楼大厅 | NULL | W13912585299 | 卡纸 | 2024-12-24 17:44:35 | 2024-12-24 17:44:38 | 30 | | 2024-12-24 17:44:44 |
| 山东农业大学北校区 | 电子阅览室一层 | 微信用户 | 18349940947 | 卡纸 | 2024-12-24 17:28:25 | 2024-12-24 17:28:59 | 30 | | 2024-12-24 17:29:14 |
| 金陵科技学院江宁校区 | 图书馆四楼电梯旁2号机 | NULL | 15151045975 | 卡纸 | 2024-12-24 15:21:47 | 2024-12-24 15:22:27 | 30 | | 2024-12-24 15:22:30 |
| 遵义医科大学 | 心静楼 | NULL | 18212208238 | 卡纸 | 2024-12-21 07:42:47 | 2024-12-21 07:48:47 | 11 | NULL | NULL |
| 河北经贸大学 | 21舍大厅 | NULL | 13111314547 | 卡纸 | 2024-12-18 21:24:34 | 2024-12-18 21:24:36 | 30 | | 2024-12-18 21:25:00 |
| 河北经贸大学 | 20舍大厅 | NULL | 15373209802 | 卡纸 | 2024-12-18 16:57:54 | 2024-12-18 17:03:57 | 11 | NULL | NULL |
±----------------------------------±--------------------------------------±-------------±-------------±----------±--------------------±--------------------±------±--------±--------------------+
10 rows in set (2.834 sec)

obclient(rd_read@woqu)[cprint]>
obclient(rd_read@woqu)[cprint]> show trace;
±----------------------------------------------------------±---------------------------±------------+
| Operation | StartTime | ElapseTime |
±----------------------------------------------------------±---------------------------±------------+
| obclient | 2025-09-16 18:44:07.116960 | 2834.199 ms |
| └── com_query_process | 2025-09-16 18:44:07.117105 | 2833.924 ms |
| └── mpquery_single_stmt | 2025-09-16 18:44:07.117108 | 2833.901 ms |
| ├── sql_compile | 2025-09-16 18:44:07.117111 | 17.529 ms |
| │ ├── pc_get_plan | 2025-09-16 18:44:07.117114 | 0.008 ms |
| │ └── hard_parse | 2025-09-16 18:44:07.117173 | 17.457 ms |
| │ ├── parse | 2025-09-16 18:44:07.117174 | 0.213 ms |
| │ ├── resolve | 2025-09-16 18:44:07.117421 | 0.662 ms |
| │ ├── rewrite | 2025-09-16 18:44:07.118169 | 0.734 ms |
| │ ├── optimize | 2025-09-16 18:44:07.118938 | 14.749 ms |
| │ │ ├── inner_execute_read | 2025-09-16 18:44:07.121540 | 1.088 ms |
| │ │ │ ├── sql_compile | 2025-09-16 18:44:07.121556 | 0.069 ms |
| │ │ │ │ └── pc_get_plan | 2025-09-16 18:44:07.121559 | 0.038 ms |
| │ │ │ ├── open | 2025-09-16 18:44:07.121666 | 0.024 ms |
| │ │ │ ├── do_local_das_task | 2025-09-16 18:44:07.121717 | 0.018 ms |
| │ │ │ └── do_local_das_task | 2025-09-16 18:44:07.121809 | 0.061 ms |
| │ │ └── close | 2025-09-16 18:44:07.123010 | 0.076 ms |
| │ │ ├── close_das_task | 2025-09-16 18:44:07.123011 | 0.006 ms |
| │ │ ├── close_das_task | 2025-09-16 18:44:07.123047 | 0.015 ms |
| │ │ └── end_transaction | 2025-09-16 18:44:07.123075 | 0.002 ms |
| │ ├── code_generate | 2025-09-16 18:44:07.133717 | 0.379 ms |
| │ └── pc_add_plan | 2025-09-16 18:44:07.134543 | 0.064 ms |
| └── sql_execute | 2025-09-16 18:44:07.134654 | 2816.209 ms |
| ├── open | 2025-09-16 18:44:07.134655 | 0.154 ms |
| ├── response_result | 2025-09-16 18:44:07.134818 | 2815.730 ms |
| │ ├── px_schedule | 2025-09-16 18:44:07.134951 | 0.155 ms |
| │ │ └── px_task | 2025-09-16 18:44:07.136318 | 0.803 ms |
| │ │ ├── do_local_das_task | 2025-09-16 18:44:07.136341 | 0.060 ms |
| │ │ └── close_das_task | 2025-09-16 18:44:07.137020 | 0.011 ms |
| │ ├── px_schedule | 2025-09-16 18:44:07.137379 | 0.000 ms |
| │ ├── do_local_das_task | 2025-09-16 18:44:07.137493 | 0.030 ms |
| │ ├── px_schedule | 2025-09-16 18:44:07.137667 | 0.133 ms |
| │ │ └── px_task | 2025-09-16 18:44:07.139776 | 15.110 ms |
| │ │ ├── do_local_das_task | 2025-09-16 18:44:07.139803 | 0.131 ms |
| │ │ └── close_das_task | 2025-09-16 18:44:07.154786 | 0.015 ms |
| │ ├── px_schedule | 2025-09-16 18:44:07.155014 | 0.000 ms |
| │ ├── px_schedule | 2025-09-16 18:44:07.155361 | 0.103 ms |
| │ │ └── px_task | 2025-09-16 18:44:07.155709 | 29.509 ms |
| │ │ ├── do_local_das_task | 2025-09-16 18:44:07.155727 | 0.028 ms |
| │ │ └── close_das_task | 2025-09-16 18:44:07.185177 | 0.008 ms |
| │ ├── px_schedule | 2025-09-16 18:44:07.185486 | 0.000 ms |
| │ ├── do_local_das_task | 2025-09-16 18:44:07.189483 | 0.075 ms |
| │ └── do_local_das_task | 2025-09-16 18:44:09.950190 | 0.084 ms |
| └── close | 2025-09-16 18:44:09.950573 | 0.282 ms |
| ├── close_das_task | 2025-09-16 18:44:09.950592 | 0.014 ms |
| ├── close_das_task | 2025-09-16 18:44:09.950643 | 0.015 ms |
| ├── close_das_task | 2025-09-16 18:44:09.950701 | 0.024 ms |
| └── end_transaction | 2025-09-16 18:44:09.950832 | 0.001 ms |
±----------------------------------------------------------±---------------------------±------------+
48 rows in set (0.099 sec)

#6

刚才贴的内容看起来有点乱，放到文本文件里面了

ob-trace.txt (8.9 KB)

#7

explain extended SELECT
b.set_name,
c.station_name,
( SELECT nick_name FROM t_cust_reg f WHERE f.cust_id = a.cust_id LIMIT 1 ) nick_name,
e.mobile,
d.code_info,
a.create_time,
a.finish_time,
a.state,
a.err_msg,
a.pay_time
FROM
t_cust_ops a,
t_set b,
t_station c,
t_fault_code d,
t_cust e
WHERE
a.set_id = b.set_id
AND a.station_id = c.station_id
AND a.fault_code = d.CODE
AND a.cust_id = e.cust_id
ORDER BY create_time DESC LIMIT 10,10;

#8

sql-explain.txt (38.8 KB)
请看附件，谢谢！

#9

SELECT

/test20250916/

b.set_name,

c.station_name,

( SELECT nick_name FROM t_cust_reg f WHERE f.cust_id = a.cust_id LIMIT 1 ) nick_name,

e.mobile,

d.code_info,

a.create_time,

a.finish_time,

a.state,

a.err_msg,

a.pay_time

FROM

t_cust_ops a,

t_set b,

t_station c,

t_fault_code d,

t_cust e

WHERE

a.set_id = b.set_id

AND a.station_id = c.station_id

AND a.fault_code = d.CODE

AND a.cust_id = e.cust_id

ORDER BY create_time DESC LIMIT 10,10;

select

concat(‘select DBMS_XPLAN.DISPLAY_CURSOR(’,char(39),plan_id,char(39),’,’,char(39),‘ADVANCED’,char(39),’,’,char(39),svr_ip,char(39),’,’,char(39),svr_port,char(39),’,’,char(39),tenant_id,char(39),’)’) sql_explain,

,query_sql

from oceanbase.gv$ob_sql_audit where query_sql like ‘%test20250916%’;

#10

真实的执行计划也查下

#11

image
image2902×1262 214 KB

#12

请稍等

#13

exec-plan.txt (18.2 KB)
请看附件，谢谢！

#14

select DBMS_XPLAN.DISPLAY_CURSOR(‘0’,‘ADVANCED’,‘172.17.172.84’,‘2882’,‘1002’) ；

#15

这个执行结果发下

#16

image
image909×146 23.5 KB

没有输出

#17

稍等 有输出

#18

DISPLAY_CURSOR.txt (5.2 KB)

请看附件，谢谢！

#19

SELECT /+dynamic_sampling(1)/
b.set_name,
c.station_name,
( SELECT nick_name FROM t_cust_reg f WHERE f.cust_id = a.cust_id LIMIT 1 ) nick_name,
e.mobile,
d.code_info,
a.create_time,
a.finish_time,
a.state,
a.err_msg,
a.pay_time
FROM
t_cust_ops a,
t_set b,
t_station c,
t_fault_code d,
t_cust e
WHERE
a.set_id = b.set_id
AND a.station_id = c.station_id
AND a.fault_code = d.CODE
AND a.cust_id = e.cust_id
ORDER BY create_time DESC LIMIT 10,10

#20

按这个执行速度快了，500毫秒左右

#21

select DBMS_XPLAN.DISPLAY_CURSOR(‘511849’,‘ADVANCED’,‘172.17.172.84’,‘2882’,‘1002’);刚才那个提供错了，执行下这个