explain extended + show warnings
explain extended:会在 explain 的基础上额外提供一些查询优化的信息。
紧随其后通过 show warnings 命令可以 得到优化后的查询语句,从而看出优化器优化了什么。
额外还有 filtered 列,是一个半分比的值,rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数
(前一个表指 explain 中的id值比当前表id值小的表)。
mysql> show columns from admin;
+------------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| email | char(30) | NO | UNI | NULL | |
| pwd | char(60) | NO | | NULL | |
| token | char(60) | NO | | | |
| id_char | char(35) | NO | UNI | | |
| create_at | timestamp | NO | | CURRENT_TIMESTAMP | |
| updated_at | timestamp | YES | | NULL | |
+------------+-----------+------+-----+-------------------+----------------+
7 rows in set (0.01 sec)
mysql> explain extended select id,email from admin;
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | admin | index | NULL | admin_pk | 120 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `hyperf`.`admin`.`id` AS `id`,`hyperf`.`admin`.`email` AS `email` from `hyperf`.`admin` |
+-------+------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
explain partitions
相比 explain 多了个 partitions 字段,如果查询是基于分区表的话,会显示查询将访问的分区。
在 select 语句之前增加 explain 关键字,
MySQL 会在查询上设置一个标记,执行查询时,会返回执行计划的信息,而不是执行这条SQL
(如果 from 中包含子查询,仍会执行该子查询,将结果放入临时表中)
id列
id列的编号是 select 的序列号,有几个 select 就有几个id,
并且id的顺序是按 select 出现的顺序增长的
select_type
- simple: 简单查询,查询不包含子查询和union
- primary: 复杂查询中最外层的 select
- subquery: 包含在 select 中的子查询(不在 from 子句中)
- derived: 包含在 from 子句中的子查询。
MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义)
- union:在 union 中的第二个和随后的 select
- union result: 从 union 临时表检索结果的 select
table
显示这一行的数据是关于哪张表的
type
显示连接类型(the join type),它描述了找到所需数据使用的扫描方式(由快到慢)
system const eq_ref ref range index all
系统 常量 等值参考 参考 范围 索引树 全部
ref:reference(re,fen,ce)[參考]
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- NULL
mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引
explain select min(id) from table;
在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表
- system
系统表,少量数据,往往不需要进行磁盘IO;
#从mysql的系统表中获取 time_zone ,数据已经加载到内存里,不需要进行磁盘IO
explain select * from mysql.time_zone;
#pkId 为 const, tmp 类型为 system
#mysql能对查询的某部分进行优化并将其转化成一个常量
explain select * from ( select * from user where pkId=1 ) tmp;
mysql能对查询的某部分进行优化并将其转化成一个常量
1.命中主键(primary key)或者唯一(unique)索引
2.被连接的部分是一个常量(const)值,pkId = 1
tip.不要类型转换
- eq_ref
主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
对于前表的每一行(row),后表只有一行被扫描
1.join查询;
2.命中主键(primary key)或者非空唯一(unique not null)索引;
3.等值连接;
1.普通索引
2.对于前表的每一行(row),后表可能有多于一行的数据被扫描
3.常量的连接查询,也由const降级为了ref,因为也可能有多于一行的数据被扫描
- ref_or_null
类似ref,但是可以搜索值为NULL的行。
- index_merge
表示使用了索引合并的优化方法
例如:id是主键,tenant_id是普通索引。or 的时候没有用 primary key,而是使用了 primary key(id) 和 tenant_id 索引
explain select * from role where id = 11011 or tenant_id = 8888;
sql语句 between in, in, >, < 等
需要扫描索引上的全部数据
explain select COUNT(*) from user;
- ALL
全表扫描(full table scan)
possible_keys
显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key
实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引
key_len
使用的索引的长度。在不损失精确性的情况下,长度越短越好
字符串
char(n):n字节长度
varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n + 2
数值类型
tinyint:1字节
smallint:2字节
int:4字节
bigint:8字节
时间类型
date:3字节
timestamp:4字节
datetime:8字节
如果字段允许为 NULL,需要1字节记录是否为 NULL
索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,
将前半部分的字符提取出来做索引。
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数
rows
MYSQL认为必须检查的用来返回请求数据的行数
extra : 解析查询的额外信息
一旦mysql找到了与行相联合匹配的行,就不再搜索了
SQL使用了where条件过滤数据,可以配合 type 优化,若 type 为 all,仍然需要优化
SQL所需要返回的所有列数据均在一棵索引树上,而无需访问实际的行记录,即数据在子节点上,命中索引
确实命中了索引,但不是所有的列数据都在索引树上,还需要访问实际的行记录,命中普通索引
得到所需结果集,需要对所有记录进行文件排序。这类SQL语句性能极差,需要进行优化,
在一个没有建立索引的列上进行了order by,就会触发filesort,按需添加索引即可
需要建立临时表(temporary table)来暂存中间结果。这类SQL语句性能较低,往往也需要进行优化。
典型的,group by和order by同时存在,且作用于不同的字段时,就会建立临时表,以便计算出最终的结果集
- Using join buffer (Block Nested Loop)
需要进行嵌套循环计算,内层和外层的type均为ALL,rows均为4,需要循环进行4*4次计算。
这类SQL语句性能往往也较低,需要进行优化。
典型的,两个关联表join,关联字段均未建立索引,就会出现这种情况。
常见的优化方案是,在关联字段上添加索引,避免每次嵌套循环计算。
profile
1.使用之前先查看当前数据库的版本信息,低版本无法使用.
show version(); 或者 show variables like '%version%'
2.查看profiling
show variables like '%profil%' ;
result:
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES | --只读变量,用于控制是否由系统变量开启或禁用profiling
| profiling | OFF | --开启SQL语句剖析功能
| profiling_history_size | 15 | --设置保留profiling的数目,缺省为15,范围为0至100,为0时将禁用p
show profiles; 查看是否开启,效果同上.
3.查看使用说明
help profile;
4.开启profile
set profiling=1; 赋值时候不要有多余的空格.
5.运行sql,查看对应的profile
select * from test ;
show profiles;
result:
+----------+------------+--------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------+
| 28 | 0.00033575 | select * from test |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------+
分析sql性能,分析的时候可以加上对应的开销字段
show profile [cpu,io][all] for query 28 ;
show profile for query 28 ;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 5.7E-5 |
| checking permissions | 7E-6 |
| Opening tables | 1.7E-5 |
| init | 2.3E-5 |
| System lock | 8E-6 |
| optimizing | 5E-6 |
| statistics | 1.1E-5 |
| preparing | 9E-6 |
| executing | 3E-6 |
| Sending data | 8.8E-5 |
| end | 5E-6 |
| query end | 6E-6 |
| closing tables | 5E-6 |
| freeing items | 7.8E-5 |
| cleaning up | 1.5E-5 |
+----------------------+----------+
6.关闭
set profiling=off;
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。