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 工具了,以第二条