Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 一个sql占用内存很高，如何判断是哪个语句的问题
|
username: Jjjjayson_zeng
[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] Query
[Encountered Problem: Problem Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring]
In the slow SQL logs, each SQL statement is displayed individually. You can click on each one to see the execution plan and analyze the SQL.
To see which part is time-consuming, you can take out the SQL and run it with EXPLAIN ANALYZE. The execution info column will show the time.
Which genius wrote this SQL? It’s absolutely amazing.
I’m stunned after reading it.
For a 500-line SQL, it probably relies on business optimization. Is it possible to segment the query or use temporary tables to handle the repetitive parts of the query?
Check the execution plan to see which part takes the longest time to execute; that part is basically the one consuming the most memory.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: │ └─Projection_1941 90570.19 1463031 root time:4m26.6s, loops:1430, Concurrency:5 if(eq(et.atd_attendance_class.classcode, ICN05), et.atdpersonpaycode.payhours, 0)->Column#3104, if(eq(et.atd_attendance_class.classcode, ICN06), et.atdpersonpaycode.payhours, 0)->Column#3105, if(eq(et.atd_attendance_class.classcode, DCN01), et.atdpersonpaycode.payhours, 0)->Column#3106, if(eq(et.atd_attendance_class.classcode, DCN02), et.atdpersonpaycode.payhours, 0)->Column#3107, if(eq(et.atd_attendance_class.classcode, DCN03), et.atdpersonpaycode.payhours, 0)->Column#3108, if(eq(et.atd_attendance_class.classcode, FCN01), et.atdpersonpaycode.payhours, 0)->Column#3109, if(eq(et.atd_attendance_class.classcode, FCN05), et.atdpersonpaycode.payhours, 0)->Column#3110, if(eq(et.atd_attendance_class.classcode, FCN06), et.atdpersonpaycode.payhours, 0)->Column#3111, if(eq(et.atd_attendance_class.classcode, FCN03), et.atdpersonpaycode.payhours, 0)->Column#3112, if(eq(et.atd_attendance_class.classcode, FCN09), et.atdpersonpaycode.payhours, 0)->Column#3113, if(eq(et.atd_attendance_class.classcode, FCN10), et.atdpersonpaycode.payhours, 0)->Column#3114, if(eq(et.atd_attendance_class.classcode, GCN01), et.atdpersonpaycode.payhours, 0)->Column#3115, if(eq(et.atd_attendance_class.classcode, GCN05), et.atdpersonpaycode.payhours, 0)->Column#3116, if(eq(et.atd_attendance_class.classcode, GCN06), et.atdpersonpaycode.payhours, 0)->Column#3117, if(eq(et.atd_attendance_class.classcode, GCN03), et.atdpersonpaycode.payhours, 0)->Column#3118, if(eq(et.atd_attendance_class.classcode, GCN09), et.atdpersonpaycode.payhours, 0)->Column#3119, if(eq(et.atd_attendance_class.classcode, GCN10), et.atdpersonpaycode.payhours, 0)->Column#3120, if(eq(et.atd_attendance_class.classcode, MCN08), et.atdpersonpaycode.payhours, 0)->Column#3121, if(eq(et.atd_attendance_class.classcode, MCN09), et.atdpersonpaycode.payhours, 0)->Column#3122, if(eq(et.atd_attendance_class.classcode, MCN10), et.atdpersonpaycode.payhours, 0)->Column#3123, if(eq(et.atd_attendance_class.classcode, MCN02), et.atdpersonpaycode.payhours, 0)->Column#3124, if(eq(et.atd_attendance_class.classcode, MCN04), et.atdpersonpaycode.payhours, 0)->Column#3125, if(eq(et.atd_attendance_class.classcode, MCN06), et.atdpersonpaycode.payhours, 0)->Column#3126, if(eq(et.atd_attendance_class.classcode, MCN03), et.atdpersonpaycode.payhours, 0)->Column#3127, if(eq(et.atd_attendance_class.classcode, MCN05), et.atdpersonpaycode.payhours, 0)->Column#3128, if(eq(et.atd_attendance_class.classcode, MCN07), et.atdpersonpaycode.payhours, 0)->Column#3129, if(and(gt(et.atdemployeecalendar.calhours, 0), eq(et.atd_attendance_class.classcode, ACN02)), et.atdpersonpaycode.payhours, 0)->Column#3130, if(eq(et.atd_attendance_class.classcode, JCN01), et.atdpersonpaycode.payhours, 0)->Column#3131, if(eq(et.atd_attendance_class.classcode, CCN01), et.atdpersonpaycode.payhours, 0)->Column#3132, if(eq(et.atd_attendance_class.classcode, JCN02), et.atdpersonpaycode.payhours, 0)->Column#3133, if(and(gt(et.atdemployeecalendar.calhours, 0), eq(et.atd_attendance_class.classcode, ACN10)), mul(et.atdpersonpaycode.payhours, 8), 0)->Column#3134, if(and(gt(et.atdemployeecalendar.calhours, 0), eq(et.atd_attendance_class.classcode, ACN11)), mul(et.atdpersonpaycode.payhours, 8), 0)->Column#3135, if(eq(et.atd_attendance_class.classcode, ACN12), et.atdpersonpaycode.payhours, 0)->Column#3136, if(eq(et.atd_attendance_class.classcode, JCN04), et.atdpersonpaycode.payhours, 0)->Column#3137, if(and(gt(et.atdemployeecalendar.calhours, 0), eq(et.atd_attendance_class.classcode, ACN01)), mul(et.atdpersonpaycode.payhours, 8), 0)->Column#3138, if(and(gt(et.atdemployeecalendar.calhours, 0), eq(et.atd_attendance_class.classcode, ACN04)), mul(et.atdpersonpaycode.payhours, 8), 0)->Column#3139, if(and(gt(et.atdemployeecalendar.calhours, 0), eq(et.atd_attendance_class.classcode, ACN15)), mul(et.atdpersonpaycode.payhours, 8), 0)->Column#3140, if(and(gt(et.atdemployeecalendar.calhours, 0), eq(et.atd_attendance_class.classcode, ACN16)), mul(et.atdpersonpaycode.payhours, 8), 0)->Column#3141, if(and(gt(et.atdemployeecalendar.calhours, 0), eq(et.atd_attendance_class.classcode, ACN08)), mul(et.atdpersonpaycode.payhours, 8), 0)->Column#3142, if(and(gt(et.atdemployeecalendar.calhours, 0), eq(et.atd_attendance_class.classcode, ACN09)), mul(et.atdpersonpaycode.payhours, 8), 0)->Column#3143, if(and(gt(et.atdemployeecalendar.calhours, 0), eq(et.atd_attendance_class.classcode, ACN13)), mul(et.atdpersonpaycode.payhours, 8), 0)->Column#3144, if(and(gt(et.atdemployeecalendar.calhours, 0), eq(et.atd_attendance_class.classcode, ACN14)), mul(et.atdpersonpaycode.payhours, 8), 0)->Column#3145, if(and(gt(et.atdemployeecalendar.calhours, 0), eq(et.atd_attendance_class.classcode, ACN03)), mul(et.atdpersonpaycode.payhours, 8), 0)->Column#3146, if(eq(et.atd_attendance_class.classcode, ACN05), et.atdpersonpaycode.payhours, 0)->Column#3147, if(eq(et.atd_attendance_class.classcode, ACN06), et.atdpersonpaycode.payhours, 0)->Column#3148, if(eq(et.atd_attendance_class.classcode, ACN07), et.atdpersonpaycode.payhours, 0)->Column#3149, if(eq(et.atd_attendance_class.classcode, ACN17), et.atdpersonpaycode.payhours, 0)->Column#3150, if(eq(et.atd_attendance_class.classcode, ACN18), et.atdpersonpaycode.payhours, 0)->Column#3151, if(eq(et.atd_attendance_class.classcode, JCN03), et.atdpersonpaycode.payhours, 0)->Column#3152, if(eq(et.atd_attendance_class.classcode, ICN07), et.atdpersonpaycode.payhours, 0)->Column#3153, if(eq(et.atd_attendance_class.classcode, ICN08), et.atdpersonpaycode.payhours, 0)->Column#3154, if(eq(et.atd_attendance_class.classcode, MCN01), et.atdpersonpaycode.payhours, 0)->Column#3155, if(eq(et.atd_attendance_class.classcode, ICN22), et.atdpersonpaycode.payhours, 0)->Column#3156, if(eq(et.atd_attendance_class.classcode, ICN21), et.atdpersonpaycode.payhours, 0)->Column#3157, if(eq(et.atd_attendance_class.classcode, ICN20), et.atdpersonpaycode.payhours, 0)->Column#3158, if(eq(et.atd_attendance_class.classcode, ECN08), et.atdpersonpaycode.payhours, 0)->Column#3159, if(eq(et.atd_attendance_class.classcode, ECN09), et.atdpersonpaycode.payhours, 0)->Column#3160, if(eq(et.atd_attendance_class.classcode, ECN01), et.atdpersonpaycode.payhours, 0)->Column#3161, if(eq(et.atd_attendance_class.classcode, ECN02), et.atdpersonpaycode.payhours, 0)->Column#3162, if(eq(et.atd_attendance_class.classcode, ECN03), et.atdpersonpaycode.payhours, 0)->Column#3163, if(eq(et.atd_attendance_class.classcode, ECN04), et.atdpersonpaycode.payhours, 0)->Column#3164, if(eq(et.atd_attendance_class.classcode, ECN05), et.atdpersonpaycode.payhours, 0)->Column#3165, if(eq(et.atd_attendance_class.classcode, ECN06), et.atdpersonpaycode.payhours, 0)->Column#3166, if(eq(et.atd_attendance_class.classcode, ECN07), et.atdpersonpaycode.payhours, 0)->Column#3167, cast(case(and(in(et.atd_attendance_class.classcode, ACN01, ACN02, ACN03, ACN04, ACN05, ACN06, ACN07, ACN08, ACN09, ACN10, ACN11, ACN12, ACN13, ACN14, ACN15, ACN16, ACN17, ACN18, CCN01, JCN01, JCN02, JCN03, JCN04), gt(et.atdpersonpaycode.payhours, 0)), 1, 0), decimal(20,0) BINARY)->Column#3168, if(eq(et.atd_attendance_class.classcode, ICN16), et.atdpersonpaycode.payhours, 0)->Column#3169, et.psnaccount.truename, et.psnaccount.personid, et.psnaccount.employeeid, et.psnaccount.attendondate, et.psnaccount.dimissiondate, et.orgstdstruct.unitname, et.cpc_position.name, et.orgstdstruct.unitname, et.orgstdstruct.unitname, et.orgstdstruct.unitname, et.organization_property_mapping_towfm4_item.propertyvalue, et.atdpersonpaycode.timecarddate, et.atdpersonapprove.issigned, et.atdpersonapprove.last_signed_user, et.atdpersonapprove.last_signed_dtm, et.atdtimeclass.timeclasscode, et.atdtimeclass.card_model, et.atdtimeclass.attribute, et.atd_localization.simplevalue, et.atdpersontimesheet.timesheetdatefrom, et.atdpersontimesheet.timesheetdateto, et.atdpersontimesheet.timesheettimefrom, et.atdpersontimesheet.timesheettimeto, et.atdpersontimesheet.timesheet_date_from2, et.atdpersontimesheet.timesheet_time_from2, et.atdpersontimesheet.timesheet_date_to2, et.atdpersontimesheet.timesheet_time_to2, et.atdpersontimesheet.timesheetdatefrom, et.atdpersontimesheet.timesheetdateto, et.atdpersontimesheet.timesheettimefrom, et.atdpersontimesheet.timesheettimeto, et.atdpersontimesheet.timesheet_date_from2, et.atdpersontimesheet.timesheet_time_from2, et.atdpersontimesheet.timesheet_date_to2, et.atdpersontimesheet.timesheet_time_to2, Column#867, Column#896, et.atd_shift_group.groupname, et.psnaccount.truename, et.psnaccount.employeeid, schedule_et.schedule_group_person_secondment.startdate, schedule_et.schedule_group_person_secondment.enddate, et.psnaccount.truename, et.psnaccount.employeeid, corehr_et.lookup_items.name, corehr_et.lookup_items.name, Column#1633, corehr_et.lookup_items.name, corehr_et.lookup_items.name, et.atd_person_exception_paycode_confirm.id, et.atd_localization.simplevalue, corehr_et.lookup_items.name, et.psnaccount.personid, et.psnaccount.employeeid, et.psnaccount.truename, et.psnaccount.attendondate, et.psnaccount.dimissiondate, et.atdpersontimesheet.timesheettimefrom, et.atdpersontimesheet.timesheettimeto, et.atdpersonpaycode.timecarddate, et.atd_localization.simplevalue 16.5 MB N/A
│ └─Projection_427 90570.19 1463031 root time:4m23.8s, loops:1430, Concurrency:5 et.psnaccount.truename, et.psnaccount.personid, et.psnaccount.employeeid, et.psnaccount.attendondate, et.psnaccount.dimissiondate, et.orgstdstruct.unitname, et.cpc_position.name, et.orgstdstruct.unitname, et.orgstdstruct.unitname, et.orgstdstruct.unitname, et.organization_property_mapping_towfm4_item.propertyvalue, et.atdpersonpaycode.payhours, et.atdpersonpaycode.timecarddate, et.atd_attendance_class.classcode, et.atdpersonapprove.issigned, et.atdpersonapprove.last_signed_user, et.atdpersonapprove.last_signed_dtm, et.atdemployeecalendar.calhours, et.atdtimeclass.timeclasscode, et.atdtimeclass.card_model, et.atdtimeclass.attribute, et.atd_localization.simplevalue, et.atdpersontimesheet.timesheetdatefrom, et.atdpersontimesheet.timesheetdateto, et.atdpersontimesheet.timesheettimefrom, et.atdpersontimesheet.timesheettimeto, et.atdpersontimesheet.timesheet_date_from2, et.atdpersontimesheet.timesheet_time_from2, et.atdpersontimesheet.timesheet_date_to2, et.atdpersontimesheet.timesheet_time_to2, et.atdpersontimesheet.timesheetdatefrom, et.atdpersontimesheet.timesheetdateto, et.atdpersontimesheet.timesheettimefrom, et.atdpersontimesheet.timesheettimeto, et.atdpersontimesheet.timesheet_date_from2, et.atdpersontimesheet.timesheet_time_from2, et.atdpersontimesheet.timesheet_date_to2, et.atdpersontimesheet.timesheet_time_to2, Column#867, Column#896, et.atd_shift_group.groupname, et.psnaccount.truename, et.psnaccount.employeeid, schedule_et.schedule_group_person_secondment.startdate, schedule_et.schedule_group_person_secondment.enddate, et.psnaccount.truename, et.psnaccount.employeeid, corehr_et.lookup_items.name, corehr_et.lookup_items.name, Column#1633, corehr_et.lookup_items.name, corehr_et.lookup_items.name, et.atd_person_exception_paycode_confirm.id, et.atd_localization.simplevalue, corehr_et.lookup_items.name 12.3 MB N/A
│ └─HashJoin_428 90570.19 1463031 root time:4m24.9s, loops:1430, build_hash_table:{total:54s, fetch:53.9s, build:53.3ms}, probe:{concurrency:5, total:22m13.6s, max:4m26.7s, probe:2m25s, fetch:19m48.5s} left outer join, equal:[eq(et.psnaccount.personid, devicecloud.atd_machine_carddata_processed.person_id) eq(et.atdpersonpaycode.timecarddate, Column#1632)] 15.1 MB 9.15 MB