必读,sql加索引调优案例和explain extended说明

做一个积极的人编码、改bug、提升自己我有一个乐园,面向编程,春暖花开!昨天分享了Mysql中的 explain 命令,使用 explain 来分析 select 语句的运行效果,如 :explain可以获得select语句使用的索引情况、排序的情况等等。链接:顺便提到了explain extended,有小伙伴留言说想知道一些explain extended,那今天就在简单讲解一下。…

大家好,又见面了,我是全栈君。

做一个积极的人
编码、改bug、提升自己
我有一个乐园,面向编程,春暖花开!

昨天分享了Mysql中的 explain 命令,使用 explain 来分析 select 语句的运行效果,如 :explain可以获得select语句使用的索引情况、排序的情况等等。链接:顺便提到了explain extended,有小伙伴留言说想知道一些explain extended,那今天就在简单讲解一下。

一、explain extended说明

我昨天的文章使用explain extended是在mysql5.6版本执行的,所以用explain extended查看执行计划会比explain多一列 filtered如果你是用的mysql5.7的话,那默认explain 就会输出 filtered 这一列,不需要使用explain extended了。

-- 查询mysql版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.24    |
+-----------+
1 row in set

MySQL 5.7 Reference Manual

在对每一个列进行一个简单说明:

Column Meaning
id SELECT标识符
select_type SELECT类型
table SELECT类型
partitions 匹配的分区
type 连接类型
possible_keys 可供选择的索引
key 实际选择的索引
key_len 所选key的长度
ref 列与索引进行比较
rows 估计要检查的行
filtered 按表条件过滤的行的百分比
Extra 附加/额外信息

filtered 这一列的解释

  • 筛选列指示将按表条件筛选的表行的估计百分比。 最大值为100,这意味着不会对行进行过滤。 值从100开始减少表示过滤量增加。 rows显示检查的估计行数,rows×filtered显示将与下表连接的行数。 例如,如果rows 是1000且filtered为50.00(50%),则使用下表连接的行数为1000×50%= 500。

二、调优案例,一定要看哦!

四张表,分别为:

  • camera :相机

  • unit :区域

  • task

  • task_relation

四张表的数据信息如下(刚开始没有加额外索引), 只有camera的数据量相对较大,其他一般!

-- camera 表信息
mysql> select count(*) from camera;
+----------+
| count(*) |
+----------+
|    52668 |
+----------+

-- unit 表信息
mysql> select count(*) from unit;
+----------+
| count(*) |
+----------+
|      227 |
+----------+

-- task 表信息
mysql> select count(*) from task;
+----------+
| count(*) |
+----------+
|      771 |
+----------+

-- task_relation 表信息
mysql> select count(*) from task_relation;
+----------+
| count(*) |
+----------+
|       44 |
+----------+


--- 查询索引,Key_name都是主键,如unit 、task、task_relation
mysql> show indexes from camera;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| camera |          0 | PRIMARY  |            1 | id          | A         |       51744 | NULL     | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set

1、没有加任何索引情况

有一个查询的sql,看一下如何进行在没有任何额外索引的情况下,执行耗时是多少?(请忽略这条sql具体是什么含义)

SELECT
	temp.id,
	temp.brandname,
	temp.ip,
	temp.address,
	temp. NAME,
	temp.url,
	temp.serialnumber,
	temp.thumbNail,
	temp.region,
	temp.create_time,
	temp.taskId,
	vt.isvalid
FROM
	(
		SELECT
			c.id AS id,
			c.brandname AS brandname,
			c.ip AS ip,
			c.address AS address,
			c.`name` AS NAME,
			c.url AS url,
			r.serialnumber AS serialnumber,
			c.thumb_nail AS thumbNail,
			t.unit_name AS region,
			c.create_time,
			r.serialnumber AS taskId
		FROM
			camera c
		LEFT JOIN task_relation r ON c.id = r.camera_file_id
		LEFT JOIN unit t ON t.unit_identity = c.region
	) temp
LEFT JOIN task vt ON temp.serialnumber = vt.serialnumber
WHERE
	1 = 1
ORDER BY
	temp.create_time DESC,
	temp. NAME DESC

执行耗时结果为: 10s 左右!

必读,sql加索引调优案例和explain extended说明

使用explain分析如下:

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 52116 |      100 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | r     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    44 |      100 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   227 |      100 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | vt    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   729 |      100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
4 rows in set

发现联接类型type为ALL,根据上一篇的介绍:

ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。

2、给关联查询的字段加上索引

使用第一篇 添加索引的方法,分别为查询语句中on关联的字段加上索引,如下:

-- 创建普通索引命令之一
ALTER TABLE table_name ADD INDEX index_name(col_name);
-- 创建相应的索引

mysql> ALTER TABLE task_relation ADD INDEX index_camera_file_id(camera_file_id);
Query OK, 0 rows affected
mysql> ALTER TABLE unit ADD INDEX index_unit_identity(unit_identity);
Query OK, 0 rows affected

创建到两个索引的时候,使用Explain看一下,发现type就有ref了,possible_keys 和key都有索引了。

+----+-------------+-------+------------+------+----------------------+----------------------+---------+---------------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys        | key                  | key_len | ref           | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+---------------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL                 | NULL                 | NULL    | NULL          | 52116 |      100 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | r     | NULL       | ref  | index_camera_file_id | index_camera_file_id | 8       | test.c.id     |     1 |      100 | NULL                                               |
|  1 | SIMPLE      | t     | NULL       | ref  | index_unit_identity  | index_unit_identity  | 99      | test.c.region |     1 |      100 | NULL                                               |
|  1 | SIMPLE      | vt    | NULL       | ALL  | NULL                 | NULL                 | NULL    | NULL          |   729 |      100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+---------------+-------+----------+----------------------------------------------------+
4 rows in set

验证一下添加索引的效果,此时进行一次查询,耗时为 6S左右!

cost_time02

给剩下没有加索引的表在继续加上索引:

[SQL]ALTER TABLE camera ADD INDEX index_region(region);
受影响的行: 0
时间: 2.277s
[SQL]ALTER TABLE task ADD INDEX index_serialnumber(serialnumber);
受影响的行: 0
时间: 0.435s

:给camera加索引花费的时间较大。如果前期知道是大表的话,一定要设计索引,否则当数据量特别大的时候,加索引就麻烦了。

3、索引全部创建完后

在创建所有查询关联的字段索引后,在执行explian进行分析,如下:

+----+-------------+-------+------------+------+----------------------+----------------------+---------+---------------------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys        | key                  | key_len | ref                 | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+---------------------+-------+----------+----------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL                 | NULL                 | NULL    | NULL                | 52116 |      100 | Using filesort |
|  1 | SIMPLE      | r     | NULL       | ref  | index_camera_file_id | index_camera_file_id | 8       | test.c.id           |     1 |      100 | NULL           |
|  1 | SIMPLE      | t     | NULL       | ref  | index_unit_identity  | index_unit_identity  | 99      | test.c.region       |     1 |      100 | NULL           |
|  1 | SIMPLE      | vt    | NULL       | ref  | index_serialnumber   | index_serialnumber   | 194     | test.r.serialnumber |     1 |      100 | NULL           |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+---------------------+-------+----------+----------------+
4 rows in set

执行sql,查询耗时 0.5 s 左右!

cost_time03

三、总结

通过上面的简单的案例,将一个开始执行10s左右的sql修改为最后执行0.5s左右! 快了20倍!

索引的添加以及SQL调优还有很多的东西,我要走的路还很远!但是我觉得首先是掌握必要的一些知识,然后能进行简单的应用,慢慢一步一步向前。

本篇的分析就到这里,希望看完本篇内容内容的你也可以动手实际操作一下,我将本篇对应的初始化数据库脚本传到github上面了,地址:https://github.com/dufyun/learn-tech-collection/tree/master/mysql_tuning,你可以进行下载实践。

最后,想分享的是 思路有时候比答案(结果)更重要。


谢谢你的阅读,如果您觉得这篇博文对你有帮助,请点赞或者喜欢,让更多的人看到!祝你每天开心愉快!


不管做什么,只要坚持下去就会看到不一样!在路上,不卑不亢!

博客首页 : http://blog.csdn.net/u010648555

愿你我在人生的路上能都变成最好的自己,能够成为一个独挡一面的人
必读,sql加索引调优案例和explain extended说明

© 每天都在变得更好的阿飞云

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/121043.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 数据仓库建模方法详解视频_三维建模流程步骤

    数据仓库建模方法详解视频_三维建模流程步骤数据仓库建模方法详解

  • emule最新服务器地址,关于emule 服务器列表

    emule最新服务器地址,关于emule 服务器列表关于emule服务器列表我给你几个服务器列表吧。打开选项—务器。然后点右边的“列表”。把以下网址复制进去,保存。然后把启动时自动更新服务器列表连接到服务器时更新服务器列表安全连接打上勾。所谓静态服务器,就是你把服务器优先值设定为高,然后下次登录时系统会自动先尝试你选定的服务器。Kad网络(KadNetwork)是一个完全分散的文件共享网络,它不被中心服务器使用。它执行KademliaP2P…

  • 一篇文章彻底搞懂浅拷贝和深拷贝的区别_深拷贝和浅拷贝的题

    一篇文章彻底搞懂浅拷贝和深拷贝的区别_深拷贝和浅拷贝的题强烈推荐30个原生JavaScript的demo,包括canvas时钟特效、自定义视频播放器、搜索栏快速匹配、fetch访问资源、console调试技巧等,先fork后学习,详见点击打开链接,欢迎点赞~~~谢谢,共同进步学习!【javascript】详解javaScript的深拷贝目录浅谈深拷贝和浅拷贝 深拷贝和浅拷贝的区别 为什么要使用深拷贝? 深拷贝的要求程度…

  • 国内能用的国际邮箱推荐哪个?公司邮箱号码大全「建议收藏」

    国内能用的国际邮箱推荐哪个?公司邮箱号码大全「建议收藏」在网上看到大家一直在讨论国际邮箱哪个好用,TOM、163、gmail等,在国内用哪个邮箱呢?外贸公司需要跟客户沟通合同信息、物流货代公司需实时沟通货柜清关进展、电商公司采购出口需跟国外办事处或客户沟通、软件信息公司老板是老外,也必须用国际邮箱,这些使用邮箱的人90%都会在国内用邮箱,所以在国内能的国际邮箱是刚需。国内能用的国际邮箱推荐常用个人邮箱,但进了公司用企业邮箱才发现真的不一样。TOM企业邮箱是我在大学里看到老师用过的,现在公司分配了一个给我,之前的个人邮箱Facebook验证码都收不到,公司的这

  • 10设置精美的免费网站后台管理系统模板

    10设置精美的免费网站后台管理系统模板

  • pycharm svn设置_新手配置交换机详细教程

    pycharm svn设置_新手配置交换机详细教程1.打开pycharm设置配置svn.exe的地址Seting-versioncontrol-subversion按照红色填入svn.exe的路径2.在配置的时候找不到svn.exe的方法原因是在默认安装svn的时候忽略了安装svn.exe2.1在卸载或更改程序中重新安装一下svn2.2按照图片进行操作完成后需要重新打开pycharm设置,就可以找到svn.exe3.配置自动化脚本svn的地址导出的时候可以选择放置的位置如D盘4.注意事项pycharm的使用和本地使用没有太

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号