1

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 字段,如果查询是基于分区表的话,会显示查询将访问的分区。

explain sql

在 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;
  • const 常量连接
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.等值连接;
  • ref 非主键非唯一索引等值扫描
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;
  • range 范围扫描
sql语句 between in, in, >, < 等
  • index 索引树扫描
需要扫描索引上的全部数据
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 : 解析查询的额外信息
  • distinct
一旦mysql找到了与行相联合匹配的行,就不再搜索了
  • Using where
SQL使用了where条件过滤数据,可以配合 type 优化,若 type 为 all,仍然需要优化
  • Using index
SQL所需要返回的所有列数据均在一棵索引树上,而无需访问实际的行记录,即数据在子节点上,命中索引
  • Using index condition
确实命中了索引,但不是所有的列数据都在索引树上,还需要访问实际的行记录,命中普通索引
  • Using filesort
得到所需结果集,需要对所有记录进行文件排序。这类SQL语句性能极差,需要进行优化,
在一个没有建立索引的列上进行了order by,就会触发filesort,按需添加索引即可
  • Using temporary
需要建立临时表(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;    

风尘斯文
306 声望13 粉丝

宠辱不惊,蓄势以待。莫问前路,风尘斯文。