SQL优化连弹三之explain

最新推荐文章于 2024-07-20 22:57:12 发布
最新推荐文章于 2024-07-20 22:57:12 发布
阅读量266 收藏
点赞数
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_37731056/article/details/105975044
版权

MySQL提供了EXPLAIN或DESC命令,获取MYSQL如何执行SELECT语句得信息,包括在SELECT语句执行过程中表如何连接和连接得顺序。

为了方便演示，大家可以下载官方sakila示例数据库文件

mysql> explain select * from film;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
  • select_type表示select的类型,常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION、SUBQUERY(子查询的第一个SELECT)
  • table表示对应的表
  • partitions 表的分区
  • possible_keys 此次查询可能用到的索引
  • key 此次查询确切用的索引
  • key_len 使用到索引字段得长度
  • rows扫描行的数量
  • filtered此次查询条件所过滤数据的百分比
  • extra执行情况的说明和描述
  • type表示MySQL在表中找到所需行的方式，或者叫访问类型。

接下来我们具体根据type进行讲解,其次会根据多角度切入分析具体实际的例子。

TYPE

在这里插入图片描述
从左到右，性能由差变好。

NULL

type=null,MySQL不用访问表或者索引，直接就能够得到结果。

mysql> EXPLAIN select 1 from dual;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

const/system

type=const/system,const是根据主键索引primary key或者唯一索引unique index进行查询,system表示系统只有一条数据，这是一个特殊得const类型。单表中最多一个匹配行，查询起来非常迅速。

(1) film_id为主键索引

mysql> EXPLAIN select * from film where film_id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

(2)连表查询 ,film.film_id和film_text.film_id都为主键索引

mysql> EXPLAIN select * from film INNER JOIN film_text  on film.film_id = film_text.film_id where film.film_id=1;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film      | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | film_text | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)

eq_ref

type=eq_ref,类似于ref,区别在使用索引是唯一索引，简单来说，是根据主键索引primary key或者唯一索引unique index进行查询。

连表查询 ,film.film_id和film_text.film_id都为主键索引

mysql> EXPLAIN select * from film INNER JOIN film_text on film.film_id=film_text.film_id;
+----+-------------+-----------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table     | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-----------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | film      | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                | 1000 |   100.00 | NULL        |
|  1 | SIMPLE      | film_text | NULL       | eq_ref | PRIMARY       | PRIMARY | 2       | sakila.film.film_id |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql在底层会做优化，会自动找出数据项较少的表，先进行全表的扫描，然后再根据主键索引与另外一张表建立连接。

ref非唯一索引扫描

type=ref,使用非唯一索引扫描,返回匹配某个单独值得记录行。

(1)customer_id为非唯一索引

mysql> EXPLAIN select * from payment where customer_id=1;
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | payment | NULL       | ref  | idx_fk_customer_id | idx_fk_customer_id | 2       | const |   32 |   100.00 | NULL  |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

注1：如果匹配值得记录行为所有记录，type会记为All
注2:主键索引为特殊的唯一索引，所以这里用主键去匹配值得时候执行计划返回的type不是ref。

(2)type=ref也常常出现在join操作中：

mysql> EXPLAIN select * from customer INNER JOIN payment on customer.customer_id=payment.customer_id;
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-----------------------------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys      | key                | key_len | ref                         | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-----------------------------+------+----------+-------+
|  1 | SIMPLE      | customer | NULL       | ALL  | PRIMARY            | NULL               | NULL    | NULL                        |  599 |   100.00 | NULL  |
|  1 | SIMPLE      | payment  | NULL       | ref  | idx_fk_customer_id | idx_fk_customer_id | 2       | sakila.customer.customer_id |   26 |   100.00 | NULL  |
+----+-------------+----------+------------+------+--------------------+--------------------+---------+-----------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

可以看到在customer中是全表扫描，在表payment中customer_id作为非唯一索引扫描。

非唯一索引不管是查询单条记录或多条记录分析计划结果都是一样的是ref。

index_merge

index_merge使用了索引合并得到的优化方法。

customer_id为主键，store_id为普通索引。

mysql> EXPLAIN select * from customer where customer_id=1 or store_id=1;
+----+-------------+----------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
| id | select_type | table    | partitions | type        | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                                             |
+----+-------------+----------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
|  1 | SIMPLE      | customer | NULL       | index_merge | PRIMARY,idx_fk_store_id | PRIMARY,idx_fk_store_id | 2,1     | NULL |  330 |   100.00 | Using union(PRIMARY,idx_fk_store_id); Using where |
+----+-------------+----------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

range索引范围扫描

type=range.索引范围扫描,常见与<、<=、>、>=、between等操作符

(1)唯一索引和非唯一索引查询带双操作符

mysql> EXPLAIN select * from payment where customer_id >=300 and customer_id <= 350;
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | payment | NULL       | range | idx_fk_customer_id | idx_fk_customer_id | 2       | NULL | 1350 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

(2)唯一索引带单操作符

mysql> EXPLAIN select * from payment where payment_id > 10;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | payment | NULL       | range | PRIMARY       | PRIMARY | 2       | NULL | 8043 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

(3)字符串索引也支持索引范围扫描，但只支持右%,不支持左%或左右%。

mysql> EXPLAIN select * from film where title like "ACADEMY%";
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | film  | NULL       | range | idx_title     | idx_title | 767     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

(4)还有一种比较特殊的情况，mysql会做优化，转化为范围查询,那就是同字段or查询情况。

mysql> EXPLAIN select * from address where city_id=300 or city_id=576;
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | address | NULL       | range | idx_fk_city_id | idx_fk_city_id | 2       | NULL |    4 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

Index索引全扫描

type=index,索引全扫描，MySQL遍历整个索引来查询匹配的行。

mysql> EXPLAIN select title from film;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | index | NULL          | idx_title | 767     | NULL | 1000 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

ALL全表扫描

type=All,全表扫描，MySQL遍历全表来找到匹配行

mysql> explain select * from film;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

总结

最后有童鞋可能会说你将这么多怎么记？其实也挺好理解的，执行最快的从索引的角度大体为主键->非唯一索引->联合索引(和组合索引不同)->全索引扫描->全表扫描,从数据项的角度大体分为单条记录查询->范围查询->全表查询。

  • 主键、唯一索引的单条记录的查询 const
  • 主键、唯一索引的多条记录的查询 eq_ref
  • 普通索引单条或多条的记录查询 ref
  • 联合索引查询情况 index_merge
  • 范围查询 range
  • 全索引扫描 index
  • 全表扫描 all

最后访问的优化类型要结合实际的分析计划来定，比如说主键单条记录的查询理应type为const,但是整张表只有一条数据，那么type就应该为All。

更多讲解,欢迎关注我的github:
go成神之路

公众号：
在这里插入图片描述

地鼠工程师
关注
专栏目录
SQL语句性能分析之explain
06-18
SQL语句性能分析之explain
explain的用法_MySQL 索引分析除了 EXPLAIN 还有什么方法？
weixin_39907316的博客
11-28 213
作者 | adrninistrat0r责编 | 夕颜出品 | CSDN(ID:CSDNnews)前言对于非数据库开发人员而言，难以对MySQL源码进行分析或调试，接近一个黑盒，但MySQL提供了一些命令及系统状态变量，可对索引及其他内容进行分析。掌握这些方法后，可以尽量深入地了解MySQL的一些实现细节。MySQL索引分析除了EXPLAIN还有什么方法通过以下方法，可以获得MySQL索引相关的数据...
SQL查询全揭秘：从流程到优化，EXPLAIN命令的完美应用
最新发布
qq_51447436的博客
07-20 3444
SQL查询全揭秘：从流程到优化，EXPLAIN命令的完美应用
explain sqlserver 类似,SQL Server相当于MySQL的EXPLAIN
weixin_39695672的博客
12-20 639
I was reading an SQL tutorial which used the keyword EXPLAIN to see how a query is executed. I tried it in SQL Server 2008 with no success.How do I get the equivalent result?解决方案I believe that the EXP...
explain sqlserver 类似_sqlserver 构架与性能优化
weixin_29945371的博客
02-01 497
太阳底下没有新鲜事一、sqlserver 构架结构1、查询优化器三阶段1)、找计划缓存如果找到直接使用2)、简单语句生成0开销的执行计划3)、正式优化 一般情况下优化到开销小于1.0就会停止定义…Select * from sys.dm_os_memory_cache_entries where type=’cachestore_phdr’2、写入数据页1)、惰性写入器(定期检验缓冲区的邻居l领进...
8. 微信小程序在线考试系统的数据库设计与优化技巧
# 1. 微信小程序在线考试系统概述 #### 1.1 考试系统的功能需求分析 在设计微信小程序在线考试系统的数据库之前，首先需要对考试系统的功能需求进行详细分析。考试系统通常包括学生管理、试题管理、考试管理、成绩...
一个程序员的成长之路
weixin_35713159的博客
04-13 1391
equals一般比较对象内容是否相等，而==比较两者的栈中保存的对象堆地址值是否相等，即是否指向同一个对象，equals在object中就存在，如不重写的话两者没有区别，重写时一般使用hashcode方法结合，比较相等首先判断hashcode是否相等，相等再进行真实值的比较，大大降低比较时间，最多两次即可判断。原因：在一个范围中的浮点数个数不是有限的，存在精度问题，如果要进行比较，使用BigDecimal并使用BigDecimal提供的对象方法进行计算，构造时使用String作为入参防止丢失精度。
java工程师笔试面试题
pamguangyou的专栏
02-08 2万+
1. J2EE 是什么？它包括哪些技术？ 解答：从整体上讲，J2EE 是使用 Java 技术开发企业级应用的工业标准，它是 Java 技术不断适应和促进企业级应用过程中的产物.适用于企业级应用的 J2EE,提供一个平台独立的、可移植的、多用户的、安全的和基于标准的企业级平台，从而简化企业应用的开发、管理和部署。J2EE 是一个标准，而不是一个现成的产品。 主要包括以下这些技术： 1) Ser
2021 Java面试真题集锦
热门推荐
GL1765852966的博客
12-26 8万+
目录 … 1 大厂面试的基本流程 17 字节跳动 17 阿里 17 腾讯 18 网易游戏 18 面试前需要准备： 18 面试中可以借鉴的套路 19 用简历争取到更多的面试机会 19 自我介绍时，立即全面抛出技术栈和亮点 20 先介绍项目背景，打消面试官疑问 21 通过说项目管理工具，说明你不仅会写代码 22 用 SPRING BOOT 项目举例，说明你的技术基本面 23 用实例说明你在内存调优方面的经验 24 展示你在数据库调优方面的经验 25 总结前文说辞 26 准备项目说辞时，更可以准备后继面试官的问
【零散知识点总结2】
weixin_44543307的博客
03-09 5654
大部分知识点来源于网络，知道的可以在评论区贴上来源喔 内容涵盖：MySQL、Spring、Spring Boot、Spring Cloud、RabbitMQ、Kafka、 Linux 等技术栈 零散知识点总结2MySQLMySQL 中有哪几种锁？MySQL 中有哪些不同的表格？简述在 MySQL 数据库中 MyISAM 和 InnoDB 的区别 MySQL MySQL 中有哪几种锁？ 1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 2、行级锁：开销大，加锁慢；会出现
explain sqlserver 类似_详解MySQL中EXPLAIN解释命令
weixin_39614704的博客
12-20 2338
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。使用方法，在select语句前加上explain就可以了：如：explainselectsurname,first_nameforma,bwherea.id=b.idEXPLAIN列的解释：table：显示这一行的数据是关于哪张表的type：这是重要的列，显示连接使...
sqlserver类似mysql的explain的命令
A_com_Z的博客
11-16 2918
1.sp_help 表名称 2.sp_column 表名称
explain sqlserver 类似_explain详解 - Access数据库,MySql,Sqlserver,Oracle,sybase,FoxPro
weixin_39632471的博客
12-20 577
explain显示了mysql如何使用索引来处理select语句和连接表。可以帮助选择更好的索引和写出更优化的查询语句。先解析1条sql语句，看出现甚么内容EXPLAINSELECTs.uid,s.username,s.name,f.email,f.mobile,f.phone,f.postalcode,f.addressFROMuchome_spaceASs,uchome_spacefieldA...
MySQL相关面试题总结
小Y在线编码
10-23 502
1、解释一下单列索引和联合索引 单列索引是指在表的某一列上创建索引，联合索引是在多个列上联合创建索引。单列索引可以出现在where条件的任何位置，而联合索引需要按照一定的顺序来写。在多条件查询的时候，联合索引的效率更高，我们联合索引也最多创建两列。 我们创建索引的时候也得考虑到我们这张表的更新频率，如果表里索引比较多的话是比较影响更新速度的，因为创建索引的过程其实就是构建一个二叉树，而每次更新完数据都得重新计算二叉树，所以就影响更新速度。 索引并不是时时都会生效的，比如以下几种情况就能导致索引失效： 如果
explain sqlserver 类似_SQL Server相当于MySQL的EXPLAIN
weixin_39984982的博客
12-20 832
I was reading an SQL tutorial which used the keyword EXPLAIN to see how a query is executed. I tried it in SQL Server 2008 with no success.How do I get the equivalent result?解决方案I believe that the EXP...
explain sqlserver 类似_09-SQLSERVER多表查询
weixin_39832965的博客
02-01 148
一、笛卡尔乘积select * from People,Department此查询结果会将People表的所有数据和Department表的所有数据进行依次排列组合形成新的记录。例如People表有10条记录，Department表有3条记录，则排列组合之后查询结果会有10*3=30条记录。二、简单多表查询此种查询不符合主外建关系的数据不会被显示查询员工信息，同时显示部门名称select * fr...
explain sqlserver 类似_explain分析sql效率的方法
weixin_39712455的博客
12-20 2409
Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效 果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。Explain语法：EXPLAIN tbl_name或：EXPLAIN [EXTENDED] SELECT select_options前者可以得出一个表的字段结构等等，后者主要是给出相关的一...
Sql 检查是否使用索引explain
༺墨༒眉༻
07-02 2604
使用解释函数explain，只需添加在sql语句之前即可：explain select * from sh_member_info where sex = '1'我们只需要注意一个最重要的type 的信息很明显的提现是否用到索引：type结果值从好到坏依次是：system &gt; const &gt; eq_ref &gt; ref &gt; fulltext &gt; ref_or_null ...
SQL优化与诊断：Explain详解
本文主要探讨了SQL优化与诊断的关键技术，特别是通过Explain工具解析查询计划，理解数据库如何执行SQL语句。Explain提供了关于执行编号、查询类型、访问的表、数据读取方式、可能使用的索引、实际使用的索引、索引...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值