MySQL之 index merge 走错索引案例

浏览数：17 / 时间：2015年06月12日

条件：MySQL 版本：percona server 5.5.18

  • sql优化案例一：

xxx@xx 5.5.18-log cayenne 11:30:37>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 = ‘sgzs6721‘ and credit_status_code = ‘SXZT001‘ ) or( certificate_no = ‘140109198407210019‘ and product_code = ‘1‘ and credit_status_code = ‘SXZT001‘ )\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: credit_amount
         type: index_merge
possible_keys: memberid_productcode_credit_code_idx,certno_productcode_credit_code_idx
          key: memberid_productcode_credit_code_idx,certno_productcode_credit_code_idx
      key_len: 758,399
          ref: NULL
         rows: 2
        Extra: Using union(memberid_productcode_credit_code_idx,certno_productcode_credit_code_idx); Using where
1 row in set (0.00 sec)

#

xxx@xx 5.5.18-log cayenne 11:30:06>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 = ‘sgzs6721‘ and credit_status_code = ‘SXZT001‘ ) or( certificate_no = ‘140109198407210019‘ and product_code = ‘1‘ and credit_status_code = ‘SXZT001‘ ) or grant_credit_task_id<2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: credit_amount
         type: index_merge
possible_keys: grant_task_id_index,memberid_productcode_credit_code_idx,certno_productcode_credit_code_idx
          key: memberid_productcode_credit_code_idx,certno_productcode_credit_code_idx,grant_task_id_index
      key_len: 758,399,9
          ref: NULL
         rows: 3
        Extra: Using sort_union(memberid_productcode_credit_code_idx,certno_productcode_credit_code_idx,grant_task_id_index); Using where
1 row in set (0.01 sec)

#此时range 和 index merge 一起使用了，但是mysql 不是这样的 http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html

Before MySQL 5.6.6, if a range scan is possible on some key, the optimizer will not consider using Index Merge Union or  Index Merge Sort-Union algorithms. For example, consider this query:

SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

3.index merge的bug：

-----------------------------------------------------------------------------------------

KEY `topic_id` (`topic_id`),
KEY `forum_id` (`forum_id`)
KEY `tid_fid` (`topic_id`,`forum_id`)

SELECT count(*) FROM phpbb_posts WHERE topic_id = 110126 AND forum_id = 19;

mysql> EXPLAIN SELECT post_id, topic_id, forum_id FROM phpbb_posts WHERE topic_id = 110126 AND forum_id = 19 ORDER BY post_time ASC LIMIT 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: phpbb_posts
         type: index_merge
possible_keys: topic_id,tid_post_time,tid_fid,fid_tid_post_time,forum_id
          key: topic_id,forum_id
      key_len: 3,3
          ref: NULL
         rows: 1592
        Extra: Using intersect(topic_id,forum_id); Using where; Using filesort
1 row in set (0.29 sec)
#
bug详见： http://bugs.mysql.com/bug.php?id=65274 在5.6.7 已经修复了。
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，防止优化器走错索引。

本文出自 “My DBA life” 博客，请务必保留此出处http://huanghualiang.blog.51cto.com/6782683/1566424

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

标签： mysql、ndex mergemysql、ndex merge

相关文章

随机文章

您可能还喜欢

您可能还喜欢