语法
explain +执行的sql语句;
快速开始
- 查看mysql的状态,了解当前mysql server的版本等信息
mysql> status;
mysql Ver 14.14 Distrib 5.7.25, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 12364
Current database: him
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ‘’
Using delimiter: ;
Server version: 5.7.25 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /my/mysql/mariadb/mysql.sock
Uptime: 23 days 12 hours 23 min 43 sec
Threads: 11 Questions: 92512 Slow queries: 0 Opens: 359 Flush tables: 1 Open tables: 345 Queries per second avg: 0.045 - 创建表和插入数据
创建表
CREATE TABLEtest
(
name
varchar(10) NOT NULL,
age
int(10) DEFAULT NULL,
address
varchar(20) DEFAULT NULL,
PRIMARY KEY (name
)
)
插入数据报错
mysql> insert into test values(“zhangsan”,18,“上海”);
ERROR 1366 (HY000): Incorrect string value: ‘\xE4\xB8\x8A\xE6\xB5\xB7’ for column ‘address’ at row 1
字符集不对,没有指定字符集使用的是latin1
mysql> show create table test;
| test | CREATE TABLEtest
(
name
varchar(10) NOT NULL,
age
int(10) DEFAULT NULL,
address
varchar(20) DEFAULT NULL,
PRIMARY KEY (name
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
修改表的字符集
alter table test convert to character set utf8;
现在就可以插入数据了
mysql> insert into test values(“zhangsan”,18,“上海”); - 执行explain语句
mysql> explain insert into test values(“zhangsan”,18,“上海”);
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
| 1 | INSERT | test | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
这里验证了两个问题,执行计划是不会被真的执行,除select语句使用,insert语句也可以使用
mysql> explain update test set name = “lisi” where name = “zhangsan”;
±—±------------±------±-----------±------±--------------±--------±--------±------±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±------±--------------±--------±--------±------±-----±---------±------------+
| 1 | UPDATE | test | NULL | range | PRIMARY | PRIMARY | 32 | const | 1 | 100.00 | Using where |
±—±------------±------±-----------±------±--------------±--------±--------±------±-----±---------±------------+
update语句也可以使用,再次验证explain不会真的去修改数据库
mysql> explain select * from test;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
mysql> explain delete from test;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------------+
| 1 | DELETE | test | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Deleting all rows |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------------+
explain 返回字段解释
- id
id 相同执行顺序由上至下
id 不同,id的值越大优先级越高,越先被执行
id 为NULL时表示一个结果集,常见为union中
例如下面的例子
mysql> explain select uid from user_qq union select uid from user;
±—±-------------±-----------±-----------±------±--------------±-------------±--------±-----±-----±---------±----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------±-----------±-----------±------±--------------±-------------±--------±-----±-----±---------±----------------+
| 1 | PRIMARY | user_qq | NULL | index | NULL | uniq_user_id | 9 | NULL | 6 | 100.00 | Using index |
| 2 | UNION | user | NULL | index | NULL | name | 82 | NULL | 11 | 100.00 | Using index |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
±—±-------------±-----------±-----------±------±--------------±-------------±--------±-----±-----±---------±----------------+ - select_type
由于往往关心的是查询的效率,而explain也是将select这一大类分成了很多种情况- SIMPLE 简单查询(不使用UNION或子查询)
例如 explain select * from test; - PRIMARY 最外层的SELECT语句
- DEPENDENT SUBQUERY 依赖的子查询
mysql> explain select u.*, (select q.openid from user_qq q where u.uid = q.uid) from user u;
±—±-------------------±------±-----------±-----±--------------±-------------±--------±----------±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------------±------±-----------±-----±--------------±-------------±--------±----------±-----±---------±------+
| 1 | PRIMARY | u | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | NULL |
| 2 | DEPENDENT SUBQUERY | q | NULL | ref | uniq_user_id | uniq_user_id | 9 | him.u.uid | 1 | 100.00 | NULL |
±—±-------------------±------±-----------±-----±--------------±-------------±--------±----------±-----±---------±------+ - SUBQUERY 子查询(没有依赖的子查询)
explain select u.*, (select q.openid from user_qq q where q.uid =1) from user u; - UNION 联合查询,UNION后的查询
- UNION RESULT 联合查询的结果
mysql> explain select uid from user union select uid from user_qq;
±—±-------------±-----------±-----------±------±--------------±-------------±--------±-----±-----±---------±----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------±-----------±-----------±------±--------------±-------------±--------±-----±-----±---------±----------------+
| 1 | PRIMARY | user | NULL | index | NULL | name | 82 | NULL | 11 | 100.00 | Using index |
| 2 | UNION | user_qq | NULL | index | NULL | uniq_user_id | 9 | NULL | 6 | 100.00 | Using index |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
±—±-------------±-----------±-----------±------±--------------±-------------±--------±-----±-----±---------±----------------+
- SIMPLE 简单查询(不使用UNION或子查询)
- table 即表的名称,若有别名的则显示别名
- partitions 表分区,物理存储文件可以分几个文件存储,一张表分几个文件来存储
- type 访问类型
system > const > eq_ref > ref > range > index > ALL 查询效率依次降低(实际并非严格)
system 数据库引擎为MyISAM 且只有一条数据
const 命中主键或者唯一索引,且查询条件为常量且要命中
eq_ref join查询中关联的是主键或联合索引例如explain select * from user left join user_qq on user.uid = user_qq.uid;
ref 与const的区别就是这里是普通索引,可能出现重复数据
range 在索引字段上使用 > ,in,bettwen 等范围查询条件
index select中的字段为索引字段
all 全表扫描 - possible_keys 可能使用到的索引
- key 使用到的索引
- key_len 索引的长度
- ref 连接匹配条件
- rows 返回估算的结果集数目,并不是一个准确的值。
- filtered 这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数
- extra 额外信息