Process处理时间太长,怎么优化

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。

  • 【TiDB 版本】: V2.1.1
  • 【问题描述】: tidb 日志文件找到slow_query 标签的事情了,发现 cost time和wait time和process time时间都有长有短,数据库CPU跑满,我贴出来几调日志,请查看:

2019/11/15 10:51:49.415 adapter.go:379: [warning] [SLOW_QUERY] cost_time:2.401268339s process_time:24.418s wait_time:6.152s request_count:281 total_keys:1884681 processed_keys:1883328 suc c:true con:5575114 user:dmcp@172.17.244.234 txn_start_ts:412558637652508675 database:customerdb table_ids:[561,789,795,557,559],index_ids:[6],sql:SELECT DISTINCT phone.phone_no mobile, phone.customer_id id, phone.platform_id platformId, c., auth.certificates_type certificatesType, auth.sex,auth.age, cr.employee_certi_no emp loyeeIdCardNo, platform.risk_evaluate riskEvaluate FROM tb_customer_phone AS phone LEFT JOIN tb_customer_platform AS platform ON (platform.customer_id = phone.customer_id AND p hone.platform_id = platform.platform_id) INNER JOIN ( select customer_id customerId, customer_name customerName, customer_type customerType, create_time createTime, certificate_no certificatesNo from tb_customer_base UNION ALL select customer_id customerId, customer_name customerName, customer_type customerType, create _time createTime, certificate_no certificatesNo from tb_customer_base_register )c ON phone.customer_id = c.customerId LEFT JOIN tb_customer_auth AS auth ON phone.custome r_id = auth.customer_id LEFT JOIN tb_customer_relationship AS cr ON (phone.customer_id = cr.customer_id AND cr.relationship_id = 2) WHERE phone.id IN ( 1712356 ,
1712068 , 1741759 , 1712048 , 1710177 , 1710166 , 1673275 , 1672488 , 1672368 , 1644075 , 1644064 , 1644060 ,
1644056 , 1670626 , 1643424 ) ORDER BY phone.create_time DESC, phone.id DESC 2019/11/15 10:51:56.487 adapter.go:379: [warning] [SLOW_QUERY] cost_time:2.034577013s process_time:22.773s wait_time:3.618s request_count:280 total_keys:1884625 processed_keys:1883286 suc c:true con:5575114 user:dmcp@172.17.244.234 txn_start_ts:412558639605481509 database:customerdb table_ids:[561,789,795,557,559],index_ids:[6],sql:SELECT DISTINCT phone.phone_no mobile, phone.customer_id id, phone.platform_id platformId, c.
, auth.certificates_type certificatesType, auth.sex,auth.age, cr.employee_certi_no emp loyeeIdCardNo, platform.risk_evaluate riskEvaluate FROM tb_customer_phone AS phone LEFT JOIN tb_customer_platform AS platform ON (platform.customer_id = phone.customer_id AND p hone.platform_id = platform.platform_id) INNER JOIN ( select customer_id customerId, customer_name customerName, customer_type customerType, create_time createTime, certificate_no certificatesNo from tb_customer_base UNION ALL select customer_id customerId, customer_name customerName, customer_type customerType, create time createTime, certificate_no certificatesNo from tb_customer_base_register )c ON phone.customer_id = c.customerId LEFT JOIN tb_customer_auth AS auth ON phone.custome r_id = auth.customer_id LEFT JOIN tb_customer_relationship AS cr ON (phone.customer_id = cr.customer_id AND cr.relationship_id = 2) WHERE phone.id IN ( 1712356 )
AND INSTR(c.customerName, ‘韩’) > 0 ORDER BY phone.create_time DESC, phone.id DESC 2019/11/15 10:53:41.945 adapter.go:379: [warning] [SLOW_QUERY] cost_time:3.092729423s process_time:25.92s wait_time:9.014s request_count:281 total_keys:1884644 processed_keys:1883298 succ :true con:4176406 user:dmcp@172.17.244.219 txn_start_ts:412558666973315073 database:customerdb table_ids:[561,789,795,557,559],index_ids:[6],sql:SELECT DISTINCT phone.phone_no mobile, phone.customer_id id, phone.platform_id platformId, c.*, auth.certificates_type certificatesType, auth.sex,auth.age, cr.employee_certi_no empl oyeeIdCardNo, platform.risk_evaluate riskEvaluate FROM tb_customer_phone AS phone LEFT JOIN tb_customer_platform AS platform ON (platform.customer_id = phone.customer_id AND ph one.platform_id = platform.platform_id) INNER JOIN ( select customer_id customerId, customer_name customerName, customer_type customerType, create_time createTime,
certificate_no certificatesNo from tb_customer_base UNION ALL select customer_id customerId, customer_name customerName, customer_type customerType, create
time createTime, certificate_no certificatesNo from tb_customer_base_register )c ON phone.customer_id = c.customerId LEFT JOIN tb_customer_auth AS auth ON phone.customer id = auth.customer_id LEFT JOIN tb_customer_relationship AS cr ON (phone.customer_id = cr.customer_id AND cr.relationship_id = 2) WHERE phone.id IN ( 1640275 ,
1640274 , 1633531 , 1633389 , 7544 ) AND INSTR(c.customerName, ‘张’) > 0 ORDER BY phone.create_time DESC, phone.id DESC 2019/11/15 10:54:42.964 adapter.go:379: [warning] [SLOW_QUERY] cost_time:2.213468663s process_time:3.276s wait_time:1.357s request_count:151 total_keys:263892 processed_keys:183698 succ:t rue con:5582723 user:dmcp@172.17.244.234 txn_start_ts:412558683200028673 database:employeedb table_ids:[577],sql:"select\n\t\t e.id, e.business_modified_by, e.employee_code, e.name, \n\t \t\te.business_modified_time, e.eng_name, e.email, e.backup_mail, \n\t\t\te.id_number, e.passport_number, e.birthday, e.age, e.constellation, \n\t\te.blood_type, e.gender, e.nation, e.id
photo, e.political_status, e.marry_category, \n\t\t\te.last_school, e.major, e.nationality, e.office_tel, e.birthplace, e.regist_address,\n \t\te.postal_code, e.home_address, e.education_ level, e.graduate_date, e.home_phone,\n e.business_address, e.mobile_phone, e.emergency_contact_phone, e.emergency_contact, e.emergency_contact_relationship, \ne.picture_url, e.weixin, e. qq, e.msn, e.personal_homepage, e.speciality, e.skype, e.g_talk, e.domicile_type, e.residence_address, \ne.join_party_date, e.work_date, e.region, e.about_me, e.user_mark, e.bind_msn, e.i s_deleted, e.approval_status, e.id_type, e.user_id,\n e.created_by, e.created_time, e.modified_by, e.modified_time, e.std_is_deleted, e.synopsis, e.work_date_year, e.work_date_month, e.wo rk_date_day,\n e.lu_salary_profile, e.order_code, e.invite_for_activation, e.blog, e.work_email, e.resume_information,er.job_number jobNumber,er.oid_department,ej.code,ej.name jobLevel,ej .code jobCode, ep.name employeePosition,ep.code positionCode, er.entry_date entryDate,\n\t\t er.oid_job_position jobPosition,epw.employee_pwd employeePwd,epw.is_deleted isDeleted,er. employee_status employeeStatus,\n\t\t o.p_o_id_org_admin_name_tree_path organizationAllPath,o.name deptName,o.organization_code organizationCode,o.oid,o.full_name fullName,\n\t\t et.nam e employeeTypeName\n\t\tfrom employee e\n\t\tleft join employee_pwd epw on e.user_id = epw.user_id\n\t\tjoin employee_record er on e.user_id = er.user_id\n\t\tleft join employee_job ej on er.oid_job_post = ej.oid\n\t\tleft join employee_position ep on er.oid_job_position = ep.oid\n\t\tleft join employee_type et ON er.employment_type = et.oid AND et.is_deleted =0 AND et.st d_is_deleted = 0 AND et.status = 1\n\t\tleft join organization o ON er.oid_department = o.oid \n\t\twhere e.is_deleted=0 AND e.std_is_deleted = 0 and er.is_delete"(len:2339) 2019/11/15 10:54:55.044 adapter.go:379: [warning] [SLOW_QUERY] cost_time:2.212905455s process_time:23.415s wait_time:3.904s request_count:280 total_keys:1884703 processed_keys:1883343 suc c:true con:5626963 user:dmcp@172.17.244.233 txn_start_ts:412558686371971073 database:customerdb table_ids:[561,789,795,557,559],index_ids:[6],sql:SELECT DISTINCT phone.phone_no mobile, phone.customer_id id, phone.platform_id platformId, c.*, auth.certificates_type certificatesType, auth.sex,auth.age, cr.employee_certi_no emp loyeeIdCardNo, platform.risk_evaluate riskEvaluate FROM tb_customer_phone AS phone LEFT JOIN tb_customer_platform AS platform ON (platform.customer_id = phone.customer_id AND p hone.platform_id = platform.platform_id) INNER JOIN ( select customer_id customerId, customer_name customerName, customer_type customerType, create_time createTime, certificate_no certificatesNo from tb_customer_base UNION ALL select customer_id customerId, customer_name customerName, customer_type customerType, create _time createTime, certificate_no certificatesNo from tb_customer_base_register )c ON phone.customer_id = c.customerId LEFT JOIN tb_customer_auth AS auth ON phone.custome r_id = auth.customer_id LEFT JOIN tb_customer_relationship AS cr ON (phone.customer_id = cr.customer_id AND cr.relationship_id = 2) WHERE phone.id IN ( 1648924 ,
1670367 , 1643821 , 1643591 , 1642691 , 1662356 , 1633990 , 1631542 , 1658151 , 1658147 , 1631262 , 1658145 ,
1658142 , 1657724 , 1656623 , 1628981 , 1655701 , 1628790 , 1655394 , 1620800 ) ORDER BY phone.create_time DESC, phone.id DESC 2019/11/15 10:55:12.463 adapter.go:379: [warning] [SLOW_QUERY] cost_time:2.316875202s process_time:3.462s wait_time:104ms request_count:8 total_keys:800971 processed_keys:799971 succ:true con:5619891 user:dmcp@172.17.244.215 txn_start_ts:412558690907062275 database:customerdb table_ids:[561,789,795,557],index_ids:[3],sql:SELECT DISTINCT cp.customer_id customerId,c.custome r_name customerName,c.customer_type customerType,cp.platform_id platformId,cp.phone_no mobile,ca.certificates_type certificateType, ca.certificates_no certificatesNo, cr.relationship_id relationshipType,cr.employee_certi_no advisorIdCard, cr.employee_name advisorName,cr.employee_mobile advisorMobile, IFNULL(ca.sex, c.sex) sex ,IFNULL(birth_day, c.birthday) birthday, c.e xtend1,c.marriages,ca.auth_state authState,cp.create_time createTime FROM tb_customer_phone cp INNER JOIN ( SELECT customer_id,customer_name,sex,marriages,customer_type,birthday, extend1 FROM tb_customer_base UNION ALL SELECT customer_id,customer_name,sex,marriages,customer_type,birthday,extend1 FROM tb_customer_base_register )c ON cp.customer_id = c.custo mer_id LEFT JOIN tb_customer_relationship cr ON cp.customer_id = cr.customer_id LEFT JOIN tb_customer_auth ca ON cp.customer_id = ca.customer_id WHERE cp.platform_id = ‘4’
AND cp.phone_no = ‘BA813416A5B907F4ED8E6C8A563AFA93’ ORDER BY cp.id DESC 2019/11/15 10:55:35.122 adapter.go:379: [warning] [SLOW_QUERY] cost_time:2.083635939s process_time:2.684s wait_time:2.871s request_count:29 total_keys:577898 processed_keys:577044 succ:tr ue con:5619891 user:dmcp@172.17.244.215 txn_start_ts:412558696910159873 database:customerdb table_ids:[559,789,795],index_ids:[6],sql:SELECT DISTINCT cp.id FROM tb_customer_phone cp
INNER JOIN tb_customer_relationship cr ON cp.customer_id = cr.customer_id LEFT JOIN ( SELECT customer_id,customer_name,create_time FROM tb_customer_base UNION ALL SELECT customer_id,customer_name,create_time FROM tb_customer_base_register )c on cr.customer_id = c.customer_id WHERE cr.employee_certi_no = ‘CC31C2A37D83FC25D8CCAD953714D5705C1F8EE8 915D822D’ AND cr.relationship_id = 2 AND INSTR(c.customer_name, ‘w’) > 0 ORDER BY cp.create_time DESC,cp.id DESC limit 0,20 2019/11/15 10:55:36.787 adapter.go:379: [warning] [SLOW_QUERY] cost_time:2.024085251s process_time:2.815s wait_time:1.074s request_count:18 total_keys:577635 processed_keys:576798 succ:tr ue con:4176406 user:dmcp@172.17.244.219 txn_start_ts:412558697355804673 database:customerdb table_ids:[559,789,795],index_ids:[6],sql:SELECT COUNT(DISTINCT cp.id) FROM ( SELECT cus tomer_id, employee_certi_no,relationship_id FROM tb_customer_relationship WHERE employee_certi_no = ‘CC31C2A37D83FC25D8CCAD953714D5705C1F8EE8915D822D’ AND relationship_id = 2 )cr LEFT JOIN tb_customer_phone cp ON cr.customer_id = cp.customer_id LEFT JOIN ( SELECT customer_id,customer_name FROM tb_customer_base UNION ALL SELECT custome r_id,customer_name FROM tb_customer_base_register )c on cp.customer_id = c.customer_id WHERE cr.employee_certi_no = ‘CC31C2A37D83FC25D8CCAD953714D5705C1F8EE8915D822D’ A ND cr.relationship_id = 2 AND INSTR(c.customer_name, ‘wang’) > 0 2019/11/15 10:55:37.349 adapter.go:379: [warning] [SLOW_QUERY] cost_time:2.140245965s process_time:2.579s wait_time:3.877s request_count:23 total_keys:581217 processed_keys:579486 succ:tr ue con:5619891 user:dmcp@172.17.244.215 txn_start_ts:412558697473769473 database:customerdb table_ids:[559,789,795],index_ids:[6],sql:SELECT COUNT(DISTINCT cp.id) FROM ( SELECT cus tomer_id, employee_certi_no,relationship_id FROM tb_customer_relationship WHERE employee_certi_no = ‘FF032570A809F7BC2FB71A831B62DFE19A334868611333A2’ AND relationship_id = 2 )cr LEFT JOIN tb_customer_phone cp ON cr.customer_id = cp.customer_id LEFT JOIN ( SELECT customer_id,customer_name FROM tb_customer_base UNION ALL SELECT custome r_id,customer_name FROM tb_customer_base_register )c on cp.customer_id = c.customer_id WHERE cr.employee_certi_no = ‘FF032570A809F7BC2FB71A831B62DFE19A334868611333A2’ A ND cr.relationship_id = 2 AND INSTR(c.customer_name, ‘何雨tao’) > 0

请给一个分析结果,如果sql优化,怎么优化,请给一个建议方案。

麻烦发一下表结构(含索引),及 SQL 执行计划。

[quote=“yuelu86, post:1, topic:1783”] cost time和wait time和process time [/quote]你能先解释一下这三个名词的意义吗?

cost time ,wait time ,process time 你能先解释一下这是三个名词的意义吗?

您好,可以在官网搜索 慢日志,查看具体参数的含义。