MYSQL高级之explain
简介
- 在sql语句前增加
explain
关键字,会展示出sql的执行信息,而不是sql执行的结果,如下
EXPLAIN SELECT * FROM student JOIN score ON student.`id` = score.`student_id`;
返回如下
- 下面我们一次介绍查询结果中代表的含义
查询结果
id
- id你可以看做是执行的顺序,id越大优先级越高,当id相同的时候,则从上往下一次执行
- 如果id为空则从上外下依次执行
select_type
用于表示简单查询还是复杂查询,他有一下几个值
- simple:简单查询,不包含子查询和union查询,如下
EXPLAIN SELECT * FROM student WHERE id = 3;
2. primary:复杂查询的最外层的select
,在有子查询的sql中最外层的查询就是primary
3. union:union语句的第二个select或者说后面的一个,UNION RESULT为合并结果如下
EXPLAIN SELECT * FROM student WHERE id = 3
UNION
SELECT * FROM student WHERE id = 4;
- subquery:表示select中的子查询,不在from语句中
EXPLAIN SELECT * FROM student WHERE id = (SELECT student_id FROM score WHERE id=2);
5. DERIVED:派生表的select的,from后面的子查询,
EXPLAIN SELECT * FROM (
SELECT * FROM score WHERE id IN (1,3,5)
) t WHERE t.id =1;
在某些版本中也会显示为SIMPLE
table
- 表示数据来源于那个表
- 有时候不是真实表的名字,而是一个虚拟表,虚拟表的最后一位是数字,代表id为多少的查询
type
- 连接类型,这个比较困难,且是我们优化的重要关注点,直接反应了我们的sql语句是否高效
- 这个的字段的值比较多,我们主要关注这几个字段system,const,eq_ref,ref,range,index,all
- 性能由好到查依次为:system>const>eq_ref>ref>range>index>all(重要)
system
表仅有一行,这个是const类型的特例,平时不会出现,所以这个字段可以忽略不计
const
- 表示通过索引一次就找到了,const用于比较primary key 或者 unique索引,因为只匹配一行数据,所以很快
- 需要注意的是在查询中用到了
primary key
或者unique
索引,所以说查询到一行数据,不一定就是const
EXPLAIN SELECT * FROM attend_schedule_shift WHERE id = 12;
id一般为主键,所以这里查询的type就是const
eq_ref
唯一性索引扫描,表中只有一条记录预置匹配。一般是两表关联,关联条件中的字段是主键或者是唯一索引
EXPLAIN SELECT * FROM attend_schedule_shift
JOIN attend_schedule_section
ON attend_schedule_shift.id = attend_schedule_section.`sche_shift_id`;
上面sql语句中使用attend_schedule_shift
的主键id去关联其他表,所以attend_schedule_shift
这个表的查询就是eq_ref
ref
- 非唯一索引扫描,返回某个单独值的所有行
- 本质上也是一种索引访问,他返回所有匹配某个单独值的行,他可能会找到多个符合条件的行,所以应该属于查找和扫描的混合体
explain select * from time_attend_data where company_id = 420;
- 复合索引
time_attend_data
里面有一个复合索引是company_id 与date共同组成的 - 只用到了一个索引值
- 这个单独值是
company_id = 420
range
本质上也是一种索引查找,这个索引必须是单独的索引
- 检索给定范围的行,使用一个索引来选择行,key列显示了使用那个索引。
- 一般条件查询中出现了 > 、< 、in、 between等查询,但是出现了也不一定是
explain select * from time_attend_data where `id` between 300 and 600;
index
遍历索引树。通常比ALL快,因为索引文件通常比数据文件小。all和index都是读全表,但是index是从索引中检索的,而all是从硬盘中检索的。
EXPLAIN SELECT `company_id`,`date` FROM time_attend_data;
一般select后跟的直接就是索引
ALL
- 全表扫描
possible_keys
- 显示可能应用在这张表中的索引,但不一定被查询实际使用。如果该类为NULL,那么没有相关的索引。
- 可以通过检查where自己看能否添加一个适当的索引来提高性能
key
- 实际使用的索引。
- 如果
possible_keys
有值,但是key为null,那么这种情况可能是谁表中的数据不多,mysql认为对于当前的查询帮助不大而选择了全表查询 - 如果想要强制使用mysql使用或者忽视
possible_keys
中的索引,那么在查询的时候使用force index
、ignore index
key_len
- 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。
- 一般来说,索引长度越长表示精度越高,效率偏低;长度越短,效率高,但精度就偏低。
- 并不是真正使用索引的长度,是个预估值
索引最大长度为768字节,当长度过大时,mysql会做一个类似最左前缀处理,将前半部分字符提取出做索引。当字段为null时,还需要1个字节去记录。
计算规则
- 字符串
- char(n):n个数字或者字母占n个字节,汉字占3n个字节
- varchar(n):n个数字或者字母占n个字节,汉字占3n+2个字节(+2用来存储字符串长度)
- 数字类型
- tinyint:1字节
- smallint:2字节
- int:4字节
- bigint:8字节
- 时间类型
- date:3字节
- timestamp:4字节
- datetime:8字节
ref
表示哪一列被使用了,常数表示这一列等于某个常数。
rows
大致找到所需记录需要读取的行数。
filter
表示选取的行和读取的行的百分比,100表示选取了100%,80表示读取了80%
extra
这里是一些,额外的比较重要的信息
Using filesort
- 使用了外部索引排序,而不是按照表内的索引顺序进行读取(一般需要优化)
- MYSQL无法利用索引完成的排序 称为文件排序
Using temporary
使用了临时表保存中间结果。常见于排序 order by和分组查询 group by(最好优化)
using index
表示select语句中,使用了覆盖索引,直接从索引中取值,不需要从磁盘中读取数据。
using where
使用了where过滤
using index condition
5.6之后新增的,表示查询的列有费索引的列,先判断索引的条件
相关文章
- mysql索引总结(3)-MySQL聚簇索引和非聚簇索引
- mysql binary like_MYSQL的binary解决mysql数据大小写敏感问题的方法
- 【MySQL高级】MySQL的存储引擎
- 【MySQL高级】Mysql并发参数调整及常用SQL技巧
- MySQL使用初步—mysql数据库的基本命令
- 【MySQL进阶-06】深入理解mysql的内核查询成本计算
- mysql下命令行执行sql脚本
- 通过Navicat for MySQL远程连接的时候报错mysql 1130的解决方法
- 基于Java+Python+MySQL实现的(Web)选题系统【100010569】
- linux MySQL 初始化数据库
- 转发 可设置skip_name_resolve参数 会出现 ERROR 2005 (HY000): Unknown MySQL server host _mysql ...
- 有关Mysql的mysql_store_result函数返回NULL的情况以及其他注意事项
- mysql—MySQL数据库中10位或13位时间戳和标准时间相互转换
- linux备份数据mysql
- 【转】Mysql学习---MySQL悲观锁中的排它锁
- Amoeba for MySQL 非常好用的mysql集群软件
- MySQL 5.7主从复制从零开始设置及全面详解——实现多线程并行同步,解决主从复制延迟问题!
- mysql命令参数详解
- Mysql error.log报错:Error: Table “mysql”.“innodb_table_stats” not found
- mysql窗口函数、Mysql分析函数
- mysql 分情况进行update语句
- Mysql 1290 - The MySQL server is running with the --secure-file-priv option
- MYSQL 5.7修改密码,登录问题