explain analyze
SELECT
COUNT(*)
FROM
(
SELECT
t1.record_no AS record_no,
t1.platform_statement_no AS platform_statement_no,
claim.platform_claim_no AS platform_claim_no,
claim.cedent_claim_no AS cedent_claim_no,
t1.treaty_statement_type AS treaty_statement_type,
t2.cedent_treaty_no AS cedent_treaty_no,
claim.insured_name AS insured_name,
if(t1.issue_org_code IS NULL, t2.input_org_code, t1.issue_org_code) AS issue_org_code,
if(t1.receive_org_code IS NULL, (SELECT GROUP_CONCAT(t3.org_code SEPARATOR ‘;’) FROM ri_treaty_reinsurer t3 WHERE t3.cedent_treaty_no = t2.cedent_treaty_no AND t3.business_type = ‘0’ AND t3.is_deleted = ‘N’), t1.receive_org_code) AS receive_org_code,
claim.accident_date AS accident_date,
t1.statement_status AS statement_status,
t1.reviewer,
t1.review_level,
t1.id AS statement_id,
‘’ AS statement_normal_id,
t1.gmt_created,
‘’ AS treaty_reinsurer_id,
t2.treaty_cate AS treaty_cate,
t2.insurance_flag AS insurance_flag,
‘’ AS statement_date,
su.nickname AS ‘firstOperator’
FROM
ri_treaty_master t2,
ri_claim_basic claim,
ri_statement t1
LEFT JOIN sys_user su ON
t1.first_operator = su.user_name
AND su.is_deleted = ‘N’
WHERE
t1.treaty_id = t2.id
AND claim.record_no = t1.record_no
AND claim.is_deleted = ‘N’
AND t1.is_deleted = ‘N’
AND t2.is_deleted = ‘N’
AND t1.treaty_statement_type IN (‘1’, ‘2’)
AND t1.input_org_code = ‘Atestxc0005w’
AND t1.statement_status IN (0, 1, 2)
UNION
SELECT
t1.record_no AS record_no,
t5.platform_statement_no AS platform_statement_no,
claim.platform_claim_no AS platform_claim_no,
claim.cedent_claim_no AS cedent_claim_no,
t1.treaty_statement_type AS treaty_statement_type,
t2.cedent_treaty_no AS cedent_treaty_no,
claim.insured_name AS insured_name,
((if(t5.upper_treaty_reinsurer_id IS NULL, t1.input_org_code, t5.upper_enter_org_code))) AS issue_org_code,
t5.enter_org_code AS receive_org_code,
claim.accident_date AS accident_date,
t_state.statement_status AS statement_status,
t5.reviewer,
t5.review_level,
t1.id AS statement_id,
t5.id AS statement_normal_id,
t5.gmt_created,
t5.treaty_reinsurer_id AS treaty_reinsurer_id,
t2.treaty_cate AS treaty_cate,
t2.insurance_flag AS insurance_flag,
t5.to_confirm_date AS statement_date,
su.nickname AS ‘firstOperator’
FROM
ri_statement t1,
ri_treaty_master t2,
ri_statement_normal t5,
ri_claim_basic claim,
ri_statement_normal_state t_state
LEFT JOIN sys_user su ON
t_state.first_operator = su.user_name
AND su.is_deleted = ‘N’
WHERE
t1.treaty_id = t2.id
AND t1.id = t5.statement_id
AND t5.id = t_state.statement_normal_id
AND t1.record_no = claim.record_no
AND claim.is_deleted = ‘N’
AND t5.is_deleted = ‘N’
AND t1.is_deleted = ‘N’
AND t2.is_deleted = ‘N’
AND t1.treaty_statement_type IN (‘1’, ‘2’)
AND (t_state.statement_status IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 12, 13)
AND t_state.org_code = ‘Atestxc0005w’)
AND t5.statement_flag != 2
AND (t5.input_org_code = ‘Atestxc0005w’
OR (t5.enter_org_code = ‘Atestxc0005w’
OR t5.upper_enter_org_code = ‘Atestxc0005w’))
AND (t5.input_org_code = ‘Atestxc0005w’
OR (t5.enter_org_code = ‘Atestxc0005w’
OR t5.upper_enter_org_code = ‘Atestxc0005w’))) record
执行计划
sql大改应该不太现实了，逻辑太复杂了。有的时候我强制索引了，扫的行数少了，但还慢了，就很奇怪。
tidb尽量用with as 写成多个子查询然后再join，效果非常好
1 个赞
索引发一下有哪些吧。
尽量先确保所涉及的库表统计信息是最新的吧，否则explain 的结果也不值得参考啊，再就是能否拆分SQL看看，适合落地的部分先落地或存入临时表，逐步分析~
1 个赞
连表太多了的话，可以设计一些中间表进行数据汇总
1 个赞
看统计信息情况吧，这个影响太大了
1 个赞
这个sql语句涉及到的子查询太多了
改下sql ，涉及or的用with as 写子查询，加uinon 先处理了，再join
IndexReader_232 和 Selection_231 耗时最久（约 985.3ms），是整个查询的主要瓶颈。能不能分批关联呢?