MySQL之 index merge 走错索引案例
时间：2022-03-14 01:22
条件：MySQL 版本：percona server 5.5.18
- sql优化案例二：
二.因为其他索引的影响导致优化器不走index_merge：
xxx@xx 5.5.18-log cayenne 05:37:47>desc select id, grant_credit_task_id, product_id, product_code, user_id, member_id, user_credit_money, product_credit_money, real_product_credit_money, credit_start_date, credit_end_date, credit_expire, repayment_way_id, repayment_way, rate_type_id, rate_type, float_way_id, float_way, float_percent, do_credit_rate, free_rate_day, installment_fees, delay_payment_fine_float, installment_fess_float, delay_payment_fine, task_type_code, task_type, status_code, status, bulid_way, create_user_id, create_user, create_time, start_time, finish_time, import_batch_id, credit_status_code, credit_status, check_type, check_type_code, fail_type, fail_type_code, effective_reason_code, effective_reason, amount_type_code, amount_type, product_name, product_type, product_type_code, user_type, certificate_type, certificate_no, approver_id, approver_name, update_time, is_activated, activated_time from credit_amount WHERE ( product_code = ‘1‘ and member_id = ‘chgq2008‘ and credit_status_code = ‘SXZT001‘ ) or( certificate_no = ‘152822198003100051‘ and product_code = ‘1‘ and credit_status_code = ‘SXZT001‘ )\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: credit_amount
type: ref
possible_keys: memberid_productcode_credit_code_idx,certno_productcode_credit_code_idx,certificateno_idx,creditstatuscode_creditenddate_idx,productcode_updatetime_idx
key: creditstatuscode_creditenddate_idx
key_len: 153
ref: const
rows: 5960990
Extra: Using where
1 row in set (0.00 sec)
此时并没有index_merge，因为有索引creditstatuscode_creditenddate_idx，而or的前后都有列 credit_status_code，索引如下：
| credit_amount | 1 | memberid_productcode_credit_code_idx | 1 | member_id | A | 11921980 | NULL | NULL | | BTREE | | | | credit_amount | 1 | memberid_productcode_credit_code_idx | 2 | product_code | A | 11921980 | NULL | NULL | YES | BTREE | | | | credit_amount | 1 | memberid_productcode_credit_code_idx | 3 | credit_status_code | A | 11921980 | NULL | NULL | YES | BTREE | | | | credit_amount | 1 | certno_productcode_credit_code_idx | 1 | certificate_no | A | 1490247 | NULL | NULL | YES | BTREE | | | | credit_amount | 1 | certno_productcode_credit_code_idx | 2 | product_code | A | 1490247 | NULL | NULL | YES | BTREE | | | | credit_amount | 1 | certno_productcode_credit_code_idx | 3 | credit_status_code | A | 1490247 | | credit_amount | 1 | creditstatuscode_creditenddate_idx | 1 | credit_status_code | A | 18 | NULL | NULL | YES | BTREE | | | | credit_amount | 1 | creditstatuscode_creditenddate_idx | 2 | credit_end_date | A | 18 |
改成union all 后是可以走union all索引的：
xxx@xx 5.5.18-log cayenne 05:40:47>desc select id, grant_credit_task_id, product_id, product_code, user_id, member_id, user_credit_money, product_credit_money, real_product_credit_money, credit_start_date, credit_end_date, credit_expire, repayment_way_id, repayment_way, rate_type_id, rate_type, float_way_id, float_way, float_percent, do_credit_rate, free_rate_day, installment_fees, delay_payment_fine_float, installment_fess_float, delay_payment_fine, task_type_code, task_type, status_code, status, bulid_way, create_user_id, create_user, create_time, start_time, finish_time, import_batch_id, credit_status_code, credit_status, check_type, check_type_code, fail_type, fail_type_code, effective_reason_code, effective_reason, amount_type_code, amount_type, product_name, product_type, product_type_code, user_type, certificate_type, certificate_no, approver_id, approver_name, update_time, is_activated, activated_time from credit_amount WHERE ( product_code = ‘1‘ and member_id = ‘chgq2008‘ and credit_status_code = ‘SXZT001‘ ) union all select id, grant_credit_task_id, product_id, product_code, user_id, member_id, user_credit_money, product_credit_money, real_product_credit_money, credit_start_date, credit_end_date, credit_expire, repayment_way_id, repayment_way, rate_type_id, rate_type, float_way_id, float_way, float_percent, do_credit_rate, free_rate_day, installment_fees, delay_payment_fine_float, installment_fess_float, delay_payment_fine, task_type_code, task_type, status_code, status, bulid_way, create_user_id, create_user, create_time, start_time, finish_time, import_batch_id, credit_status_code, credit_status, check_type, check_type_code, fail_type, fail_type_code, effective_reason_code, effective_reason, amount_type_code, amount_type, product_name, product_type, product_type_code, user_type, certificate_type, certificate_no, approver_id, approver_name, update_time, is_activated, activated_time from credit_amount WHERE ( certificate_no = ‘152822198003100051‘ and product_code = ‘1‘ and credit_status_code = ‘SXZT001‘ )\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: credit_amount
type: ref
possible_keys: memberid_productcode_credit_code_idx,creditstatuscode_creditenddate_idx,productcode_updatetime_idx
key: memberid_productcode_credit_code_idx
key_len: 758
ref: const,const,const
rows: 1
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: UNION
table: credit_amount
type: ref
possible_keys: certno_productcode_credit_code_idx,certificateno_idx,creditstatuscode_creditenddate_idx,productcode_updatetime_idx
key: certno_productcode_credit_code_idx
key_len: 399
ref: const,const,const
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: NULL
select_type: UNION RESULT
table: <union1,2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
3 rows in set (0.00 sec)
总结：以后同样的这种sql 要用union all，防止优化器走错索引。
本文出自 “” 博客，请务必保留此出处
