新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  SEO  推广  媒体  移动
财经百科  股票  知识  理财  财务  金融
教育考试  育儿  小学  高考  考研  留学
您当前的位置:首页 > IT百科 > 数据库 > 百科

如何分析一条sql的性能?

时间:2019-06-14 10:29:48  来源:  作者:

这篇文章将给大家介绍如何使用 explain 来分析一条 sql 

如何分析一条sql的性能?

 

网上其实已经有非常多的文章都很详细的介绍了 explain 的使用,这篇文章将实例和原理结合起来,尽量让你有更好的理解,相信我,认真看完你应该会有特别的收获。

explain 翻译过来就是解释的意思, 在 MySQL 里被称作执行计划,即可以通过该命令看出 mysql 在经过优化器分析后决定要如何执行该条 sql 。

说到优化器,再多说一句,mysql 内置了一个强大的优化器,优化器的主要任务就是把你写的 sql 再给优化一下,尽可能以更低成本去执行,比如扫描更少的行数,避免排序等。执行一条sql语句都经历了什么? 我在前面的文章中有介绍过优化器相关的。

你可能会问,一般在什么时候会要用 explain 呢,大多数情况下都是从 mysql 的慢查询日志中揪出来一些查询效率比较慢的 sql 来使用 explain 分析,也有的是就是在对 mysql 进行优化的时候,比如添加索引,通过 explain 来分析添加的索引能否被命中,还有的就是在业务开发的时候,在满足需求的情况下,你可能需要通过 explain 来选择一个更高效的 sql。

那么 explain 该怎么用呢,很简单,直接在 sql 前面加上 explain 就行了,如下所示。

mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 100332 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+

1 row in set (0.04 sec)

可以看到,explain 会返回约 10 个字段,不同版本返回的字段有些许差异,每个字段都代表着具体的意义,这篇文章我不打算把每个字段都详细的介绍一遍,东西比较多,怕你也不容易记住,不如先把几个重要的字段好好理解了。

其中 type、key、rows、Extra 这几个字段我认为是比较重要的,我们接下来通过具体的实例来帮你更好的理解这几个字段的含义。

首先有必要简单介绍下这几个字段的字面意思。

type 表示 mysql 访问数据的方式,常见的有全表扫描(all)、遍历索引(index)、区间查询(range)、常量或等值查询(ref、eq_ref)、主键等值查询(const)、当表中只有一条记录时(system)。下面是效率从最好到最差的一个排序。

system > const > eq_ref > ref > range > index > all

key 表示查询过程实际会用到的索引名称。

rows 表示查询过程中可能需要扫描的行数,这个数据不一定准确,是mysql 抽样统计的一个数据。

Extra 表示一些额外的信息,通常会显示是否使用了索引,是否需要排序,是否会用到临时表等。

好了,接下来正式开始实例分析。

还是沿用前面文章中创建的存储引擎创建一个测试表,我们这里插入 10 w 条测试数据,表结构如下:

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

然后看下面这条查询语句,注意这个表目前只有一个主键索引,还没有创建普通索引。

mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 100332 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+

1 row in set (0.04 sec)

其中 type 值为 ALL,表示全表扫描了,大家注意看到 rows 这个字段显示有 100332 条,实际上我们一共才 10w 条数据,所以这个字段只是 mysql 的一个预估,并不一定准确。这种全表扫描的效率非常低,是需要重点被优化的。

接下来我们分别给字段 a 和 b 添加普通索引,然后再看下添加索引后的几条 sql 。

mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t | 0 | PRIMARY | 1 | id | A | 100332 | NULL | NULL | | BTREE | | |
| t | 1 | a_index | 1 | a | A | 100332 | NULL | NULL | YES | BTREE | | |
| t | 1 | b_index | 1 | b | A | 100332 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> explain select * from t where a > 1000;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | ALL | a_index | NULL | NULL | NULL | 100332 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

上面这条 sql 看起来是不是有点疑惑呢,type 竟然显示刚刚不是给字段 a 添加索引了么,而且 possible_keys 也显示了有 a_index 可用,但是 key 显示 null,表示 mysql 实际上并不会使用 a 索引,这是为啥?

这里是因为 select * 的话还需要回到主键索引上查找 b 字段,这个过程叫回表,这条语句会筛选出 9w 条满足条件的数据,也就是说这 9w 条数据都需要回表操作,全表扫描都才 10w 条数据,所以在 mysql 的优化器看来还不如直接全表扫描得了,至少还免去了回表过程了。

当然也不是说只要有回表操作就不会命中索引,用不用索引关键还在于 mysql 认为哪种查询代价更低,我们把上面的 sql 中 where 条件再稍微改造一下。

mysql> explain select * from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+

1 row in set (0.00 sec)

这回 type 值为 range 了,key 为 a_index ,表示命中了 a 索引,是一个不错的选择,是因为满足这条 sql 条件的只有 1000 条数据,mysql 认为 1000 条数据就算回表也要比全表扫描的代价低,所以说 mysql 其实是个很聪明的家伙。

我们还可以看到 Extra 字段中值为 Using index condition,这个意思是指用到了索引,但是需要回表,再看下面这个语句。

mysql> explain select a from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

1 row in set (0.00 sec)

这个 Extra 中的值为 Using where; Using index ,表示查询用到了索引,且要查询的字段在索引中就能拿到,不需要回表,显然这种效率比上面的要高,所以不要轻易写 select * ,只查询业务需要的字段即可,这样可以尽可能避免回表。

再来看一个需要排序的。

mysql> explain select a from t where a > 99000 order by b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | t | range | a_index | a_index | 5 | NULL | 999 | Using index condition; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

这个 Extra 中返回了一个 Using filesort,意味着需要排序,这种是需要重点优化的的,也就是说查到数据后,还需要 mysql 在内存中对其进行排序,你要知道索引本身就是有序的,所以一般来讲要尽量利用索引的有序性,比如像下面这样写。

mysql> explain select a from t where a > 99990 order by a;
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t | range | a_index,ab_index | a_index | 5 | NULL | 10 | Using where; Using index |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+

1 row in set (0.00 sec)

我们再创建一个复合索引看看。

mysql> alter table t add index ab_index(a,b);

Query OK, 0 rows affected (0.19 sec)

Records: 0 Duplicates: 0 Warnings: 0

复制代码

mysql> explain select * from t where a > 1000;
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
| 1 | SIMPLE | t | range | a_index,ab_index | ab_index | 5 | NULL | 50166 | Using where; Using index |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+

1 row in set (0.00 sec)

这条 sql 刚刚在上面也有讲到过,在没有创建复合索引的时候,是走的全表扫描,现在其实是利用了覆盖索引,同样是免去了回表过程,即在 (ab_index) 索引上就能找出要查询的字段。

这篇文章通过几个实例介绍了如何使用 explain 分析一条 sql 的执行计划,也提到了一些常见的索引优化,事实上还有更多的可能性,你也可以自己去写一个 sql ,然后使用 explain 分析,看看有哪些是可以被优化的。
 



Tags:sql   点击:()  评论:()
声明:本站部分内容来自互联网,内容观点仅代表作者本人,如有任何版权侵犯请与我们联系,我们将立即删除。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
SQL注入就是攻击者在前端的表单输入中,或者 API 的传参时,按照 SQL 的语法,人为地加入一段代码,改变原有的SQL 逻辑,来跳过验证,篡改或者删除数据库,达到攻击者的目的的过程。SQL注...【详细内容】
2019-11-27   sql  点击:(0)  评论:(0)  加入收藏
最近一直在使用SQLServer数据库,里面的查询等操作与MySQL有所不同,所以还是需要多花点时间学习,今天Leader在我这里指导工作,现场纯熟地操作了一番数据库,着实让我佩服,简单记录一...【详细内容】
2019-11-27   sql  点击:(1)  评论:(0)  加入收藏
本文记录的是在CentOS 7下安装与配置jdk-8u162的过程。一、下载jdk-8u162版本链接地址:官方地址二、上传jdk到centos下三、检查当前linux系统上是否有jdk,linux命令:rpm -qa |...【详细内容】
2019-11-27   sql  点击:(0)  评论:(0)  加入收藏
1、MyCAT基础架构准备MyCAT基础架构图 摘自oldguo1.1 MyCAT基础架构准备1.1.1 环境准备:两台虚拟机 db01(10.0.0.51) db02(10.0.0.52) 每台创建四个mysql实例:3307 3308 3309 33...【详细内容】
2019-11-26   sql  点击:(2)  评论:(0)  加入收藏
MySQL中7个查询命令的优先级:FROM --> WHERE --> GROUP BY --> HAVING -->SELECT --> ORDER BY --> LIMIT==============================================================...【详细内容】
2019-11-26   sql  点击:(2)  评论:(0)  加入收藏
欢迎关注头条号:Java小野猫实践中如何优化MySQL实践中,MySQL的优化主要涉及SQL语句及索引的优化、数据表结构的优化、系统配置的优化和硬件的优化四个方面,如下图所示: SQL语...【详细内容】
2019-11-26   sql  点击:(3)  评论:(0)  加入收藏
1.拉取CentOS镜像由于CentOS7在Docker上有一个DBUS(进程间通信机制)的BUG,所以这里建议下载CentOS6的镜像。只需在镜像名后面加上TAG既可docker pull imagine10255/centos6-l...【详细内容】
2019-11-26   sql  点击:(3)  评论:(0)  加入收藏
概述这篇文章主要针对刚入门的开发,一般半个小时是可以学完的,当然要理解的话估计不止30分钟,对于初学者来说只需满足自己需求可以增删改查等简易的维护即可。下面介绍下MySQL...【详细内容】
2019-11-26   sql  点击:(2)  评论:(0)  加入收藏
phpMyAdmin工具在导入WordPress的MySQL数据库时报错了,吓死宝宝了,还以为压缩文件名必须以 .[格式].[压缩方式]结尾。如:.sql.zip压缩格式的问题呢,百度搜索一番后发现是由于...【详细内容】
2019-11-26   sql  点击:(1)  评论:(0)  加入收藏
1 复制概述Mysql内建的复制功能是构建大型,高性能应用程序的基础。将Mysql的数据分布到多个系统上去,这种分布的机制,是通过将Mysql的某一台主机的数据复制到其它主机(slaves)上,...【详细内容】
2019-11-25   sql  点击:(1)  评论:(0)  加入收藏
-视图:view--概念:是数据库中的虚拟表,直接你属于数据库中,由多张表中的数据组合而成--语法:/*create view 视图名称assql语句...........*/ /*--优点:视图可以让用户或者程...【详细内容】
2019-11-22   sql  点击:(3)  评论:(0)  加入收藏
修改MySQL最大连接数有两个方法。一个是直接在命令行中修改,另一个是配置文件第一种:命令行修改。mysql -uroot -pmysql>show variables like 'max_connections';(查...【详细内容】
2019-11-21   sql  点击:(3)  评论:(0)  加入收藏
老张我在刚开始学习数据库的时候,没少走弯路。经常会遇到各种稀奇古怪的 error 信息,遇到报错会很慌张,急需一个解决问题的办法。跟无头苍蝇一样,会不加思索地把错误粘到百度上,希望赶紧查找一下有没有好的处理问题的方法...【详细内容】
2019-11-21   sql  点击:(2)  评论:(0)  加入收藏
[client]#默认连接端口                port = 3306#用于本地连接的socket套接字,一般linux下有用socket = /data/mysqldata/3306/mysql.sock#客户端编码de...【详细内容】
2019-11-20   sql  点击:(5)  评论:(0)  加入收藏
1、 sqlmap介绍sqlmap是一款支持MySQL, Oracle,PostgreSQL, Microsoft SQL Server, Microsoft Access, IBM DB2, SQLite, Firebird,Sybase和SAP MaxDB等数据库的各种安全漏...【详细内容】
2019-11-20   sql  点击:(4)  评论:(0)  加入收藏
这篇文章主要是认识一下mysql中的变量,本来是不准备整理的,但是发现后面的存储过程等等在实际用的时候还挺多。mysql里面的变量你可以和java中的变量进行对比理解。主要分为两...【详细内容】
2019-11-20   sql  点击:(3)  评论:(0)  加入收藏
这篇文章主要是从mysql数据库的逻辑架构来认识掌握mysql的原理。只要是稍微有一点计算机的相关知识相信都能看明白。一、笼统的逻辑架构先给出一张逻辑架构图,这张图是让你从...【详细内容】
2019-11-20   sql  点击:(4)  评论:(0)  加入收藏
概述文章有点长哟,需要耐心看哟数据库系统是现代商业世界有序稳定运行的基石,数据和数据承载的交易事件的结果不会因系统故障而损伤。数据库系统的核心技术是事务处理机制,也是...【详细内容】
2019-11-20   sql  点击:(1)  评论:(0)  加入收藏
概述今天主要介绍一下mysql 中 时间函数now() current_timestamp() 和 sysdate() 以及三者之间的比较。now()、current_timestamp() 和 sysdate()在mysql中有三个时间函数用...【详细内容】
2019-11-19   sql  点击:(2)  评论:(0)  加入收藏
1 初识索引索引在MySQL中也叫是一种“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发...【详细内容】
2019-11-18   sql  点击:(6)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条