mysql优化–explain分析sql语句执行效率

本文深入解析了Explain命令在数据库性能优化中的应用,通过具体示例介绍了如何利用Explain分析SQL语句的执行效果,选择更优索引及优化查询语句,提升数据库性能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看SQL语句的执行效 果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句。

Explain语法:explain select … from … [where …]

例如:explain select * from news;

输出:

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

下面对各个属性进行了解:

1、id:这是SELECT的查询序列号

2、select_type:select_type就是select的类型,可以有以下几种:

SIMPLE:简单SELECT(不使用UNION或子查询等)

PRIMARY:最外面的SELECT

UNION:UNION中的第二个或后面的SELECT语句

DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询

UNION RESULT:UNION的结果。

SUBQUERY:子查询中的第一个SELECT

DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询

DERIVED:导出表的SELECT(FROM子句的子查询)

3、table:显示这一行的数据是关于哪张表的

4、type:这列最重要,显示了连接使用了哪种类别,有无使用索引,是使用Explain命令分析性能瓶颈的关键项之一。

结果值从好到坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说,得保证查询至少达到range级别,最好能达到ref,否则就可能会出现性能问题。

NULL:mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表

mysql> explain select min(id) from film;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

const, system:mysql能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings 的结果)。用于 primary key 或 unique key 的所有列与常数比较时,所以表最多有一个匹配行,读取1次,速度比较快。

mysql> explain extended select * from (select * from film where id = 1) tmp;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | film       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+

mysql> show warnings;
+-------+------+---------------------------------------------------------------+
| Level | Code | Message                                                       |
+-------+------+---------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from dual |
+-------+------+---------------------------------------------------------------+

eq_ref:primary key 或 unique key 索引的所有部分被连接使用 ,最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了,简单的 select 查询不会出现这种 type。

mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys | key               | key_len | ref                     | rows | Extra       |
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | film_actor | index  | NULL          | idx_film_actor_id | 8       | NULL                    |    3 | Using index |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY       | PRIMARY           | 4       | test.film_actor.film_id |    1 | NULL        |
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+

ref:相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行。

1. 简单 select 查询,name是普通索引(非唯一索引)
mysql> explain select * from film where name = "film1";
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | film  | ref  | idx_name      | idx_name | 33      | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+

2.关联表查询,idx_film_actor_id是film_id和actor_id的联合索引,这里使用到了film_actor的左边前缀film_id部分。
mysql> explain select * from film left join film_actor on film.id = film_actor.film_id;
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
| id | select_type | table      | type  | possible_keys     | key               | key_len | ref          | rows | Extra       |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
|  1 | SIMPLE      | film       | index | NULL              | idx_name          | 33      | NULL         |    3 | Using index |
|  1 | SIMPLE      | film_actor | ref   | idx_film_actor_id | idx_film_actor_id | 4       | test.film.id |    1 | Using index |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+

ref_or_null:类似ref,但是可以搜索值为NULL的行。

mysql> explain select * from film where name = "film1" or name is null;
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type        | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | film  | ref_or_null | idx_name      | idx_name | 33      | const |    2 | Using where; Using index |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+

index_merge:表示使用了索引合并的优化方法。 例如下表:id是主键,tenant_id是普通索引。or 的时候没有用 primary key,而是使用了 primary key(id) 和 tenant_id 索引

mysql> explain select * from role where id = 11011 or tenant_id = 8888;
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type        | possible_keys         | key                   | key_len | ref  | rows | Extra                                           |
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+
|  1 | SIMPLE      | role  | index_merge | PRIMARY,idx_tenant_id | PRIMARY,idx_tenant_id | 4,4     | NULL |  134 | Using union(PRIMARY,idx_tenant_id); Using where |
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+

range:范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

mysql> explain select * from actor where id > 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

index:和ALL一样,不同就是mysql只需扫描索引树,这通常比ALL快一些。

mysql> explain select count(*) from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

ALL即全表扫描,意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了

mysql> 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 |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

5、possible_keys:列指出MySQL能使用哪个索引在该表中找到行

6、key:显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL

7、key_len:显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。使用的索引的长度。在不损失精确性的情况下,长度越短越好

key_len 的计算规则如下: 
字符串

char(n): n 字节长度

varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.

数值类型:

TINYINT: 1字节

SMALLINT: 2字节

MEDIUMINT: 3字节

INT: 4字节

BIGINT: 8字节

时间类型

DATE: 3字节

TIMESTAMP: 4字节

DATETIME: 8字节

字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性. 

8、ref:显示使用哪个列或常数与key一起从表中选择行。

9、rows:显示MySQL认为它执行查询时必须检查的行数。

10、Extra:包含MySQL解决查询的详细信息,也是关键参考项之一。

Distinct
一旦MYSQL找到了与行相联合匹配的行,就不再搜索了

Not exists
MYSQL 优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,

就不再搜索了

Range checked for each

Record(index map:#)
没有找到理想的索引,因此对于从前面表中来的每一 个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort
看 到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来 排序全部行

Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表 的全部的请求列都是同一个索引的部分的时候

Using temporary
看到这个的时候,查询需要优化了。这 里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上

Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index, 这就会发生,或者是查询有问题

其他一些Tip:

  1. 当type 显示为 “index” 时,并且Extra显示为“Using Index”, 表明使用了覆盖索引。
基于数据挖掘的音乐推荐系统设计与实现 需要一个代码说明,不需要论文 采用python语言,django框架,mysql数据库开发 编程环境:pycharm,mysql8.0 系统分为前台+后台模式开发 网站前台: 用户注册, 登录 搜索音乐,音乐欣赏(可以在线进行播放) 用户登陆时选择相关感兴趣的音乐风格 音乐收藏 音乐推荐算法:(重点) 本课题需要大量用户行为(如播放记录、收藏列表)、音乐特征(如音频特征、歌曲元数据)等数据 (1)根据用户之间相似性或关联性,给一个用户推荐与其相似或有关联的其他用户所感兴趣的音乐; (2)根据音乐之间的相似性或关联性,给一个用户推荐与其感兴趣的音乐相似或有关联的其他音乐。 基于用户的推荐和基于物品的推荐 其中基于用户的推荐是基于用户的相似度找出相似相似用户,然后向目标用户推荐其相似用户喜欢的东西(和你类似的人也喜欢**东西); 而基于物品的推荐是基于物品的相似度找出相似的物品做推荐(喜欢该音乐的人还喜欢了**音乐); 管理员 管理员信息管理 注册用户管理,审核 音乐爬虫(爬虫方式爬取网站音乐数据) 音乐信息管理(上传歌曲MP3,以便前台播放) 音乐收藏管理 用户 用户资料修改 我的音乐收藏 完整前后端源码,部署后可正常运行! 环境说明 开发语言:python后端 python版本:3.7 数据库:mysql 5.7+ 数据库工具:Navicat11+ 开发软件:pycharm
MPU6050是一款广泛应用在无人机、机器人和运动设备中的六轴姿态传感器,它集成了三轴陀螺仪和三轴加速度计。这款传感器能够实时监测并提供设备的角速度和线性加速度数据,对于理解物体的动态运动状态至关重要。在Arduino平台上,通过特定的库文件可以方便地与MPU6050进行通信,获取并解析传感器数据。 `MPU6050.cpp`和`MPU6050.h`是Arduino库的关键组成部分。`MPU6050.h`是头文件,包含了定义传感器接口和函数声明。它定义了类`MPU6050`,该类包含了初始化传感器、读取数据等方法。例如,`begin()`函数用于设置传感器的工作模式和I2C地址,`getAcceleration()`和`getGyroscope()`则分别用于获取加速度和角速度数据。 在Arduino项目中,首先需要包含`MPU6050.h`头文件,然后创建`MPU6050`对象,并调用`begin()`函数初始化传感器。之后,可以通过循环调用`getAcceleration()`和`getGyroscope()`来不断更新传感器读数。为了处理这些原始数据,通常还需要进行校准和滤波,以消除噪声和漂移。 I2C通信协议是MPU6050与Arduino交互的基础,它是一种低引脚数的串行通信协议,允许多个设备共享一对数据线。Arduino板上的Wire库提供了I2C通信的底层支持,使得用户无需深入了解通信细节,就能方便地与MPU6050交互。 MPU6050传感器的数据包括加速度(X、Y、Z轴)和角速度(同样为X、Y、Z轴)。加速度数据可以用来计算物体的静态位置和动态运动,而角速度数据则能反映物体转动的速度。结合这两个数据,可以进一步计算出物体的姿态(如角度和角速度变化)。 在嵌入式开发领域,特别是使用STM32微控制器时,也可以找到类似的库来驱动MPU6050。STM32通常具有更强大的处理能力和更多的GPIO口,可以实现更复杂的控制算法。然而,基本的传感器操作流程和数据处理原理与Arduino平台相似。 在实际应用中,除了基本的传感器读取,还可能涉及到温度补偿、低功耗模式设置、DMP(数字运动处理器)功能的利用等高级特性。DMP可以帮助处理传感器数据,实现更高级的运动估计,减轻主控制器的计算负担。 MPU6050是一个强大的六轴传感器,广泛应用于各种需要实时运动追踪的项目中。通过 Arduino 或 STM32 的库文件,开发者可以轻松地与传感器交互,获取并处理数据,实现各种创新应用。博客和其他开源资源是学习和解决问题的重要途径,通过这些资源,开发者可以获得关于MPU6050的详细信息和实践指南
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值