mysql优化 - explain

最新推荐文章于 2024-09-02 16:02:45 发布

辣条涨价了

最新推荐文章于 2024-09-02 16:02:45 发布

阅读量142

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/IT_tyro/article/details/111414768

版权

mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

explain

explain可以模拟优化器执行sql查询语句，从而知道mysql是如何处理我们的sql的。分析查询语句或者表结构的性能瓶颈。

语法：Explain + SQL 语句;
如：Explain select * from user; 会生成如下 SQL 分析结果，下面详细对每个字段进行详解

explain select * from actor;

+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |  204
 |       |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

id :select 查询的序列号，包含一组数字，表示查询中select字句或者操作表的顺序。
1.id相同：可以认为是一组，执行顺序由上而下。
2.id不同，子查询的序号会递增，id序号越大，优先级越高，越先被执行。子查询优先级高于主查询。
3.id相同又不同：id大的先执行，id相同的由上至下顺序执行。
衍生=derived
select_type:

select_type	info
simple	简单的select查询，查询中不包括自子查询和union
primary	包含复杂的子查询，最外层的查询为primary，最后加载
subquery	select或者from中包含子查询
derived	查询结果放在临时表中的表被标记为derive的，在from列表中包含的子查询被标记为derived，mysql会递归执行这些子查询，把结果保存在临时表中
union	若第二个select出现在union后，则被标记为union;若union包含在from字句的子查询中，外层select则被标记为derived
union result	两个查询集合的union

table :表
type(访问类型） :
常见的type：

type	info
system	表只有一行记录，等于系统表，这是const的特例，工作中一般不会出现
const	将主键索引或者唯一索引放到 where 条件中查询，MySQL 可以将查询条件转变成一个常量，只匹配一行数据，索引一次就找到数据了
eq_ref	唯一索引扫描，对于每个索引建，表中只有一条记录与之对应。常见于主键索引和唯一索引
ref	不是主键索引，也不是唯一索引，就是普通的索引，可能会返回多个符合条件的行。
range	体现在对某个索引进行区间范围检索，一般出现在 where 条件中的 between、and、<、>、in 等范围查找中。
index	将所有的索引树都遍历一遍，查找到符合条件的行。索引文件比数据文件还是要小很多，所以比不用索引全表扫描还是要快很多。
all	全表扫描

至少达到range级别，最好能达到ref级别。

possible keys : 显示这张表可能用到的索引，一个或者多个。查询涉及到的字段上若存在索引，则索引将会被列出，但不一定被实际查询使用。
key : 实际使用的索引，如果为NULL，则：1.索引失效 2.没有索引
若查询中使用了覆盖索引，则索引进出现在key列表中。
key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，长度越小，B+树中一个节点存的索引相对越多，减少树的深度，可以减少IO次数。
索引字段的可能的最大长度，并非是实际长度。key_len 是根据表定义得出，并不是表内检测出的。

-- show variables like '%charcter_set%'; 数据库和服务器的字符集
SHOW VARIABLES LIKE 'character_set%'; 
-- show table status from 库名 like '%表名%'；显示表的字符集
SHOW TABLE STATUS FROM sakila LIKE '%actor%';

utf8 char(n)里一个字符包含3个字节,总字节为3*n;

utf8 varchar(n)里一个字符包含3个字节,总字节为3*n+2;

utf8mb4 char(n)里一个字符包含4个字节，总字节为4*n;

utf8mb4 varchar(n)里一个字符包含4个字节，总字节为4*n+2;

/*DDL 信息*/------------
CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=205 DEFAULT CHARSET=utf8mb4

   id  select_type  table   type    possible_keys  key                  key_len  ref       rows  Extra        
------  -----------  ------  ------  -------------  -------------------  -------  ------  ------  -------------
     1  SIMPLE       actor   index   (NULL)         idx_actor_last_name  182      (NULL)     204  Using index  
     -- 字符集为utf8mb4,每个字符可以包含4个字节。key_len 182=4*45+2

ref : 显示哪一列被使用了，或者哪些列和哪些常量用于查找索引。
rows : 根据表信息统计以及索引的使用情况，大致估算说要找到所需记录需要读取的行数，rows 越小越好。
extra : 包含不适合在其他列显示但很重要的信息。


using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作，成为’文件排序’。尽快优化！
using temporary	使用了临时表保存中间结果，mysql在对结果排序时使用临时表，常见于排序order by和分组查询group by。临时表大大降低了系统性能。
using index	表示相应的查询操作使用了覆盖索引，避免了回表。如果同时出现了using where,表示索引用来进行索引值的查找；没有出现using where则表示只用来读取数据，而不是用去查询
using where	索引用来进行索引值的查找
using join buffer	使用了连接缓存
impossible where	where字句的值总是false,不能用来获取任何元组
select tables optimized away	在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct	优化 distinct，在找到第一匹配的元组后即停止找同样值的工作