单表扫描,MySQL索引选择不正确 并 详细解析OPTIMIZER_TRACE格式

单表扫描，MySQL索引选择不正确 并 详细解析OPTIMIZER_TRACE格式  

 

 

 

一 表结构如下: 

MySQL  5.5.30  5.6.20 版本, 表大概有815万行

CREATE TABLE t_audit_operate_log (
  Fid bigint(16) AUTO_INCREMENT,
  Fcreate_time int(10) unsigned NOT NULL DEFAULT '0',
  Fuser varchar(50) DEFAULT '',
  Fip bigint(16) DEFAULT NULL,
  Foperate_object_id bigint(20) DEFAULT '0',
  PRIMARY KEY (Fid),
  KEY indx_ctime (Fcreate_time),
  KEY indx_user (Fuser),
  KEY indx_objid (Foperate_object_id),
  KEY indx_ip (Fip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行查询:

mysql> explain select count(*) from t_audit_operate_log where Fuser='XX@XX.com' and Fcreate_time>=1407081600 and Fcreate_time<=1407427199\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t_audit_operate_log

type: ref

possible_keys: indx_ctime,indx_user

key: indx_user

key_len: 153

ref: const

rows: 2007326

Extra: Using where

 

发现,使用了一个不合适的索引, 不是很理想，于是改成指定索引：

mysql> explain select count(*) from t_audit_operate_log use index(indx_ctime) where Fuser='CY6016@cyou-inc.com' and Fcreate_time>=1407081600 and Fcreate_time<=1407427199\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t_audit_operate_log

type: range

possible_keys: indx_ctime

key: indx_ctime

key_len: 5

ref: NULL

rows: 670092

Extra: Using where

实际执行耗时，后者比前者快了接近10

问题: 很奇怪，优化器为何不选择使用 indx_ctime 索引，而选择了明显会扫描更多行的 indx_user 索引。

分析2个索引的数据量如下:  两个条件的唯一性对比：

select count(*) from t_audit_operate_log where Fuser='XX@XX.com';
+----------+
| count(*) |
+----------+
| 1238382 | 
+----------+

select count(*) from t_audit_operate_log where Fcreate_time>=1407254400 and Fcreate_time<=1407427199;
+----------+
| count(*) |
+----------+
| 198920 | 
+----------+

显然,使用索引indx_ctime好于indx_user,但MySQL却选择了indx_user. 为什么?

于是,使用 OPTIMIZER_TRACE进一步探索.

 

二  OPTIMIZER_TRACE的过程说明

以本处事例简要说明OPTIMIZER_TRACE的过程.

 

 

{\
  "steps": [\
    {\
      "join_preparation": {\  ---优化准备工作
        "select#": 1,\
        "steps": [\
          {\
            "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `t_audit_operate_log` where ((`t_audit_operate_log`.`Fuser` = 'XX@XX.com') and (`t_audit_operate_log`.`Fcreate_time` >= 1407081600) and (`t_audit_operate_log`.`Fcreate_time` <= 1407427199))"\
          }\
        ] /* steps */\
      } /* join_preparation */\
    },\
    {\
      "join_optimization": {\  ---优化工作的主要阶段,包括逻辑优化和物理优化两个阶段
        "select#": 1,\
        "steps": [\  ---优化工作的主要阶段, 逻辑优化阶段
          {\
            "condition_processing": {\  ---逻辑优化,条件化简
              "condition": "WHERE",\
              "original_condition": "((`t_audit_operate_log`.`Fuser` = 'XX@XX.com') and (`t_audit_operate_log`.`Fcreate_time` >= 1407081600) and (`t_audit_operate_log`.`Fcreate_time` <= 1407427199))",\
              "steps": [\
                {\
                  "transformation": "equality_propagation",\  ---逻辑优化,条件化简,等式处理
                  "resulting_condition": "((`t_audit_operate_log`.`Fuser` = 'XX@XX.com') and (`t_audit_operate_log`.`Fcreate_time` >= 1407081600) and (`t_audit_operate_log`.`Fcreate_time` <= 1407427199))"\
                },\
                {\
                  "transformation": "constant_propagation",\  ---逻辑优化,条件化简,常量处理
                  "resulting_condition": "((`t_audit_operate_log`.`Fuser` = 'XX@XX.com') and (`t_audit_operate_log`.`Fcreate_time` >= 1407081600) and (`t_audit_operate_log`.`Fcreate_time` <= 1407427199))"\
                },\
                {\
                  "transformation": "trivial_condition_removal",\  ---逻辑优化,条件化简,条件去除
                  "resulting_condition": "((`t_audit_operate_log`.`Fuser` = 'XX@XX.com') and (`t_audit_operate_log`.`Fcreate_time` >= 1407081600) and (`t_audit_operate_log`.`Fcreate_time` <= 1407427199))"\
                }\
              ] /* steps */\
            } /* condition_processing */\
          },\  ---逻辑优化,条件化简,结束
          {\
            "table_dependencies": [\  ---逻辑优化, 找出表之间的相互依赖关系. 非直接可用的优化方式.
              {\
                "table": "`t_audit_operate_log`",\
                "row_may_be_null": false,\
                "map_bit": 0,\
                "depends_on_map_bits": [\
                ] /* depends_on_map_bits */\
              }\
            ] /* table_dependencies */\
          },\
          {\
            "ref_optimizer_key_uses":