EXPLAIN执行计划
执行计划简介
执行计划是指一条SQL语句在经过MySQL查询优化器的优化会后，具体的执行方式。MySQL为我们提供了EXPLAIN语句，来获取执行计划的相关信息。需要注意的是，EXPLAIN语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。
执行计划支持SELECT, DELETE, INSERT, REPLACE以及 UPDATE语句。对于SELECT语句，EXPLAIN会生成扩展信息，这些信息可以通过紧随EXPLAIN语句之后的SHOW WARNINGS语句显示。
执行计划的输出格式
| 列 | json名称 | 含义 |
|---|---|---|
| id | select_id | 每个SELECT语句都会对应一个唯一的标识符id |
| select_type | None |
SELECT关键字对应的查询类型 |
| table | table_name | 每行输出的表名 |
| partitions | partitions | 匹配的分区 |
| type | access_type | 表的访问方法 |
| possible_keys | possible_keys | 可能用到的索引 |
| key | key | 实际用到的索引 |
| key_len | key_length | 实际用到的索引长度 |
| ref | ref | 当使用索引等值查询时，与索引作比较的列或常量 |
| rows | rows | 预计要读取的行数 |
| filtered | filtered | 按表条件过滤后，留存的记录数的百分比（过滤后的行数/过滤前行数 ） |
| extra | None | 附加信息 |
创建测试用表及并生成数据
- 创建测试用表
表结构如下所示，实际使用中会创建u1，u2两张表，两张表结构一致，除id列外，其它字段随机插入。
CREATE TABLE u1 (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100),
card_no INT,
school VARCHAR(100),
city VARCHAR(100),
district VARCHAR(100),
street VARCHAR(100),
hoppy VARCHAR(100),
PRIMARY KEY (id),
KEY idx_name (name),
UNIQUE KEY idx_card_no (card_no),
KEY idx_school (school),
KEY idx_address(city, district, street)
) Engine=InnoDB CHARSET=utf8;
- 生成数据
先定义一个生成随机数的函数rand_string:
DELIMITER $$
DROP FUNCTION IF EXISTS rand_string$$
CREATE FUNCTION `rand_string`(num INT) RETURNS varchar(255) CHARSET UTF8
BEGIN
DECLARE origin_str char(52) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str varchar(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < num DO
SET return_str = CONCAT(return_str, SUBSTRING(origin_str , FLOOR(1 + RAND()*52 ),1));
SET i = i +1;
END WHILE;
RETURN return_str;
END $$
DELIMITER ;
创建存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS gen_user_data$$
CREATE PROCEDURE `gen_user_data`(num INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= num DO
INSERT INTO u1(id, name, card_no, school, city, district, street, hoppy) VALUES(i, rand_string(1+FLOOR(RAND()*100)), 10000000+i, rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)));
INSERT INTO u2(id, name, card_no, school, city, district, street, hoppy) VALUES(i, rand_string(1+FLOOR(RAND()*100)), 10000000+i, rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)));
SET i = i +1;
END WHILE;
END $$
DELIMITER ;
调用存储过程，生成数据
CALL gen_user_data(10000);
执行计划输出各列详解
id
查询语句中每个SELECT关键字，都会有一个对应的id。
最简单的查询如下所示，由于只有一个SELECT关键字，所以只有id为1的记录。
mysql> EXPLAIN SELECT * FROM u1 WHERE name = "abc";
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | u1 | NULL | ref | idx_name | idx_name | 303 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----