建表语句
CREATE TABLE `user` (
`id` int NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
CREATE TABLE `game` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3
CREATE TABLE `game_user` (
`id` int NOT NULL,
`game_id` int NOT NULL,
`user_id` int NOT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_game_user_id` (`game_id`,`user_id`),
KEY `index_remark` (`remark`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
在执行sql的时候,前面加上explain,就能得到sql的执行情况,
mysql> explain select * from user;
简单查询的执行情况,如下图
可以发现,explain里有
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
这些字段
1.id
表示select 的序列号,有几个select语句就有几个id
id列的值越大,越先执行,如果id相同,从上到下依次执行,id为null时最后执行
2.select_type
表示对应的查询sql语句是什么类型的
(1)simple:简单查询,不包含子查询和union
(2)primary:复杂查询中最外层的 select
(3)subquery:子查询,where后面
(4)derived:from后面的查询。MySQL会将结果存放在一个临时表中,也称为派生表# 关闭mysql的衍生表的合并优化 mysql> set session optimizer_switch='derived_merge=off'; mysql> explain select (select 1 from user where id = 1) from (select * from game where id = 1) a;
(5)union:在 union 后面的查询mysql> explain select id,name from user union all select id,name from game;
3.table列
表示 explain 的每个查询语句正在查询的是哪个表。
如上图
4.type列
表示查询语句的类型,一般sql优化,就是优化这个字段,优化从优到差是
system > const > eq_ref > ref > range > index > ALL
一般查询至少要达到range级别,最好达到ref级别
- (1)NULL
mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。
如下查询索引最小值,在mysql优化阶段就能得到结果,不用再查询表和索引mysql> explain select min(id) from user;
- (2)const, system
mysql能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings 的结果)。用于primary key 或 unique key 的所有列与常数比较时,所以表最多有一个匹配行,读取1次,速度比较快。system是const的特例,表里只有一条元组匹配时为system,mysql> explain select * from (select * from user where id = 1) a;
- (3)eq_ref
使用主键索引或者唯一索引连接使用 ,只会返回一条符合条件的记录。mysql> explain select * from game_user left join game on game_user.game_id = game.id;
(4)ref
使用普通索引,可能会找到多个符合条件的行。(5)range
使用一个索引来检索给定范围的行。如in(), between ,> ,<, >= 等mysql> explain select * from user where id>0;
- (6)index
直接对二级索引的叶子节点遍历和扫描,速度还是比较慢的,,但是比ALL快,二级索引比聚簇索引小。
查询存在二级索引的game表mysql> explain select * from game;
- (7)ALL
全表扫描你的聚簇索引的所有叶子节点。
查询不存在二级索引的user表mysql> explain select * from user;
5.possible_keys列
表示mysql认为这条查询语句可能使用的索引有哪些。
6.key列
表示mysql实际查询中,使用了哪些索引.
可能会出现possible_keys有值,但是key无值的情况,这种是由于mysql觉的不使用索引的时候查询反而快一点,自动进行了优化。
如果想强制mysql使用或忽视possible_keys列中的索引,在查询中使用 force index、ignore index。
7.key_len列
表示mysql在索引里使用的字节数,可以算出具体使用了索引中的哪些列。
- key_len计算规则如下:
类型 | 长度 |
---|---|
字符串 | char(n):3n varchar(n):3n + 2 ,加的2字节用来存储字符串长度,因为varchar是变长字符串 |
数值类型 | tinyint:1字节 smallint:2字节 int:4字节 bigint:8字节 |
时间类型 | date:3字节 timestamp:4字节 datetime:8字节 |
注意:如果字段允许为 NULL,需要1字节记录是否为 NULL
索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引。
示例:mysql> explain select * from game where name='game0';
如下,key_len值为33,是因为使用了索引idx_name,name是varchar(10)类型的,根据上面的规则,3*10+2=32,又由于name字段允许为null,所以32+1=32
8.ref列
表示在key列记录的索引中,所用到的列或常量,值为const(常量)或者字段名(例:EXPLAINTEST.game_user.game_id)
9.rows列
表示mysql估计要扫描的行数,不是实际行数。
10.Extra列
表示额外的信息。常见的重要值如下:
- (1)Using index:使用覆盖索引
覆盖索引定义:如果select后面查询的字段都可以从key字段里的索引树中获取,extra里就会有using index;就是整个查询结果只通过一个索引树就能拿到结果,不需要回表,索引树包含要查询的全部字段mysql> explain select name from game where name=1;
- (2)Using filesort:当Query中包含 order by 操作,而且无法利用索引完成的排序操作称为“文件排序”,数据较小时从内存排序,否则需要在磁盘完成排序。需要使用索引优化。
user表name未使用索引
mysql> explain select * from user order by name;
game表name使用了索引mysql> explain select * from game order by name;
- (3)Using where:使用 where 语句来处理结果,并且查询的列未被索引覆盖
mysql> explain select * from user where name = '1';
- (4)Using index condition:查询的列不完全被索引覆盖,where条件中是联合索引的一部分条件;
mysql> explain select * from game_user where game_id>1;
- (5)Using temporary:创建来了一张临时表来处理查询。需要使用索引优化。
user表没有name索引
mysql> explain select distinct name from user;
game表有name索引mysql> explain select distinct name from game;
- 6)Select tables optimized away:使用某些聚合函数(比如 max、min)来访问存在索引的某个字段
mysql> explain select min(id) from user;