MySQL再深入执行计划之trace工具

MySQL 执行查询语句时需要进行的查询成本分析了，而 MySQL 提供的 trace 工具就可以让我们看到 MySQL 计算的查询成本，以及选择索引的大致分析过程。

#一、示例表

-- 示例表
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('itwxe',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('weiwei', 23,'test',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('leilei',23,'dev',NOW());

-- 插入10w条测试数据
drop procedure if exists insert_employees; 
delimiter $$
create procedure insert_employees()        
begin
  declare i int;                    
  set i = 1;                          
  while(i <= 100000)do                 
    insert into employees(name, age, position) values(CONCAT('itwxe', i), rand() * 42 + 18, 'dev');  
    set i = i + 1;                       
  end while;
end$$
delimiter ;
call insert_employees();

#二、trace使用步骤

首先需要说明的是开启 trace 工具会影响 MySQL 性能，所以只能临时分析 SQL 使用，用完之后立即关闭。

使用 trace 工具步骤：

-- 首先开启trace
mysql> set session optimizer_trace="enabled=on", end_markers_in_json=on;
-- 执行查询SQL
mysql> select * from employees where name > 'wei' order by position;
-- 查询trace字段
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

使用完成后关闭 trace 工具：

-- 当分析完SQL，关闭trace
mysql> set session optimizer_trace="enabled=off";

#三、trace字段解析

例如这条语句：

explain select * from employees where name > 'itwxe' order by position;

explain select * from employees where name > 'wei' order by position;

可以看到第一条 SQL 中 possible_keys 有可以使用的索引 idx_name_age_position ，但是最后实际上 MySQL 没有使用 idx_name_age_position ，而是使用了全表扫描；而第二条 SQL 中使用到了 idx_name_age_position 中的 name 列，这是为什么呢？

如果非常了解 MySQL 索引底层数据结构的小伙伴应该就知道因为第二条 SQL name > 'itwxe' 扫描的联合索引 idx_name_age_position 的结果太多，回表成本太大，所以 MySQL 选择了全表扫描。要想看到 MySQL 计算的全表扫描成本和使用 idx_name_age_position 成本就得使用咱们这篇的主角 trace 工具了，以第二条