MySQL之indexmerge走错索引案例

条件: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,防止优化器走错索引。

更多相关文章
  • 原sql:select count(*) from mpay_order mpayOrder inner join mrecharge_order_info orderinfo on mpayOrder.order_num = orderinfo.order_num inner join mpay_ ...
  • window下用主机名登录MySQL数据库出现报错解决方案
    分享一个工作中的小案例:windows主机上用SQLyog使用主机名登录MySQL数据库(Linux系统上搭建),结果报错,因为经常在windows上使用主机名互相访问资源.远程登录等.以为Linux上应该也可以,所以就开始研究.通过查找资料及多方帮助终于解决并搞清楚原因.希望大家不要重蹈覆辙. 故 ...
  • 昨天在试运行环境做系统升级,突然间SSH连接断开,尝试进行连接一直报错,后来重启我的电脑再次进行连接,依然报错,奇怪的测试人员访问公司的liferay界面可以正常访问,后来给部门同事打电话问他们今天有没有对试运行环境做过什么修改,两个同事都说是今天没有对试运行服务器做过任何操作,我自己也没有做过什么 ...
  • 实现MySQL表数据全量索引和增量索引,基于Solr DIH组件实现起来比较简单,只需要重复使用Solr的DIH(Data Import Handler)组件,对data-config.xml进行简单的修改即可.Solr DIH组件的实现类为org.apache.solr.handler.datai ...
  • SqlServer查询性能优化之走出索引的误区
    据了解绝大多数开发人员对于索引的理解都是一知半解,局限于大多数日常工作没有机会.也什么没有必要去关心.了解索引,实在哪天某个查询太慢了找到查询条件建个索引就ok,哪天又有个查询慢了,再建立个索引就是,或者干脆把整个查询SQL直接发给DBA,让DBA直接帮忙优化了,所以造成的状况就是开发人员对于索引的 ...
  •    隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给mysql,这样会导致索引失效. 错误的例子:select * from test where tu_mdn= ...
  •     实例:一般id作为表的主键,在存在unique key 的情况下,索引的建立有两种方式:     1.primary key 与 unique 并存     2.将 unique key 转变为primary key .测试:    表 uah   Primary key  (id), UN ...
  •  1.下载mysql-5.1.36.tar.gz,并且解压. tar -xzvf mysql-5.1.36.tar.gz2.添加mysql组,新建mysql用户 groupadd mysql useradd -g mysql mysql3.进入解压后的目录下,进行编译安装 ./configure - ...
一周排行
  • ERP实施实记(六)之生管课
    今天我们按例到生管课进行调研分析,首先我们了解一下我们公司生管课的主要职责:1.下达生产单 ...
  • 想让用户登录XenApp和应用程序打开完全不需要等待吗?想知道如何提高存储性能让XenApp & XenDesktop运行的更快并且减少80%的存储投资吗?这一切都在XenApp& XenDeskto ...
  • 用Xamarin实现园友的:Android浮动小球与开机自启动
    前两天看园子里有筒子写了个 Android浮动小球与开机自启动  , 感觉这种被 360 ...
  • 当映射网络驱动器到文件服务器时,发现有些文件夹可以被映射,有些文件夹却不能被映射. 点击"确认"后,看到以下提示: 原因 解决方案 · 再重新建立映射\ http://ray5.4.blog.16 ...
  • linux下线程控制
        线程是计算机中独立运行的最小单位,运行时占用很少的系统资源.在多进程情况下,每个进 ...
  • 张霞,福道诚壹咨询公司首席顾问,原阿里巴巴人力资源副总裁.先后工作于摩托罗拉.微软中国.Towers Perrin.2004年加入阿里巴巴,先后任职薪酬福利部总监.集团副总裁,曾负责B2B中国事业部人力资源.集团组织 ...
  • Linux自带了ntp服务 -- /etc/init.d/ntpd,这个服务不仅可以设置让本机和某台/某些机器做时间同步,他本身还可以扮演一个time 的角色,让其他机器和他同步时间,配置文件就是/etc/ntp.c ...
  • 有啊当年之败恰恰是流量为王的哀伤
    有啊明明这是另一个搜索巨头谷歌在美国走通的路,为何在中国,百度就没能复制成功呢?水龙头在手 ...
  • 最全线性表函数(打印,初始化,后插氛围,后删,前插,前删,在某位中插入,从某位向后寻找,删除某位,删除某元素,删除表中全部某元素,冒泡排序,选择排序,二分查找)//**************fun.h******* ...
  •    云计算平台及其特性       云计算平台可以划分为3类:以数据存储为主的存储型云平台,以数据处理为主的计算型云平台以及计算和数据存储处理兼顾的综合云计,算平台.商业化云计算平台(1)微软技术特性:整合其所用软 ...