MySQL Execution Plan--Index Merge导致查询预估行数不准问题

INDEX MERGE导致的查询预估行数不准问题

问题描述

某业务集群实例每秒读取记录数长时间很高(每秒超过150万)：

抓取135秒全量慢日志分析,按照预估行数排序得到：

****************************************************************************************************
执行占比:    69.14% (按sum_examined_rows)
指纹SQL:    use f_scp_dashboard; select * from persistence_info where '?' = '?' and app_code = '?' and
status = '?' ;
指纹MD5:    939b039584a4343cc75c0aa0ccc2285d
示例SQL:    use f_scp_dashboard; select * from persistence_info where 1=1 and app_code = 'b_order_user'
 and status = 1;
执行次数:    74992
平均/汇总预估行数:    8 / 655830
平均/汇总影响行数:    0 / 0
平均/汇总查询时间:    0 / 40536
平均/汇总返回行数:    8 / 624469
平均/汇总阻塞时间:    0 / 0
平均/汇总返回字节:    4862 / 364640686
****************************************************************************************************
执行占比:    20.02% (按sum_examined_rows)
指纹SQL:    use f_scp_dashboard; select * from persistence_info where '?' = '?' and app_code = '?' and
type = '?' order by status desc, last_modify desc;
指纹MD5:    2bcd29c02cf62604d405b739ffdfb4a6
示例SQL:    use f_scp_dashboard; select * from persistence_info where 1=1 and app_code = 'f_pangolin_se
arch_post' and type = 2 order by status desc, last_modify desc;
执行次数:    7477
平均/汇总预估行数:    25 / 189911
平均/汇总影响行数:    0 / 0
平均/汇总查询时间:    0 / 282
平均/汇总返回行数:    2 / 20570
平均/汇总阻塞时间:    0 / 0
平均/汇总返回字节:    2582 / 19308247
****************************************************************************************************
执行占比:    4.69% (按sum_examined_rows)
指纹SQL:    use qmall_insurance_db; select * from ins_product_detail where ins_product_no = '?' limit '?' ;
指纹MD5:    df4d994d0e13f88ed271d9218745db1f
示例SQL:    use qmall_insurance_db; select * from ins_product_detail where ins_product_no = '060014qunar_50' limit 1;
执行次数:    291
平均/汇总预估行数:    152 / 44449
平均/汇总影响行数:    0 / 0
平均/汇总查询时间:    0 / 1
平均/汇总返回行数:    1 / 291
平均/汇总阻塞时间:    0 / 0
平均/汇总返回字节:    3064 / 891804

按照慢SQL统计，MySQL实例135秒内扫描记录约为94万行，而MySQL实例级别统计到的扫描记录约为135*150万=20250万，相差约215倍。

根据平均预估行数来看，TOP 3的SQL都正常使用索引。由于第一条SQL的执行频率较高，查看其执行计划为：

# 查看执行计划
EXPLAIN SELECT *
FROM persistence_info 
WHERE app_code = 'f_inter_twell' 
AND STATUS = 1;
# 执行计划输出
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: persistence_info
   partitions: NULL
         type: index_merge
possible_keys: idx_status,idx_app_code,idx_app_code_type
          key: idx_app_code,idx_status
      key_len: 202,1
          ref: NULL
         rows: 20
     filtered: Using intersect(idx_app_code,idx_status); Using where 
        Extra: NULL

表persistence_info结构为：

CREATE TABLE `persistence_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `app_code` varchar(50) NOT NULL DEFAULT '' COMMENT 'app_code',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '规则状态',
  `type` tinyint(10) NOT NULL DEFAULT '0' COMMENT '类型',
  ....移除非相关数据列...
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_app_code` (`app_code`),
  KEY `idx_app_code_type` (`app_code`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=217613 DEFAULT CHARSET=utf8mb4 COMMENT='规则存储表'

由于执行计划使用intersect，那么查询相当于：

SELECT id
FROM persistence_info 
WHERE app_code = 'f_inter_twell'
INTERSECT
SELECT id
FROM persistence_info 
WHERE STATUS = 1

分别查看两个子查询的执行计划：

EXPLAIN SELECT id
FROM persistence_info 
WHERE app_code = 'f_inter_twell'
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: persistence_info
   partitions: NULL
         type: ref
possible_keys: idx_app_code_type,idx_app_code
          key: idx_app_code
      key_len: 200
          ref: const
         rows: 73
     filtered: 100.00
        Extra: Using index
        
        
EXPLAIN SELECT id
FROM persistence_info 
WHERE STATUS = 1
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: persistence_info
   partitions: NULL
         type: ref
possible_keys: idx_status
          key: idx_status
      key_len: 1
          ref: const
         rows: 5448
     filtered: 100.00
        Extra: Using index

因此理论上查询预估行数应该为5448(使用idx_status索引)+73(使用idx_app_code)+20(INTERSECT操作后主键查找)=5541行，而MySQL返回的预估行数为22，相差250倍，与使用慢查询日志计算出的215倍相近。

查询优化

由于查询使用INTERSECT方式会在索引idx_status上扫描大量记录，可通过组合索引方式来避免INDEX MERGE操作：

ALTER TABLE f_scp_dashboard.persistence_info ADD INDEX idx_app_code_status(app_code,status);

添加索引后执行计划为：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: persistence_info
   partitions: NULL
         type: ref
possible_keys: idx_status,idx_app_code_type,idx_app_code_status
          key: idx_app_code_status
      key_len: 203
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL

MySQL实例级别的读记录行数明显下降：