大家好,又见面了,我是全栈君。
做一个积极的人
编码、改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
在对每一个列进行一个简单说明:
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 左右!
使用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左右!
给剩下没有加索引的表在继续加上索引:
[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 左右!
三、总结
通过上面的简单的案例,将一个开始执行10s左右的sql修改为最后执行0.5s左右! 快了20倍!
索引的添加以及SQL调优还有很多的东西,我要走的路还很远!但是我觉得首先是掌握必要的一些知识,然后能进行简单的应用,慢慢一步一步向前。
本篇的分析就到这里,希望看完本篇内容内容的你也可以动手实际操作一下,我将本篇对应的初始化数据库脚本传到github上面了,地址:https://github.com/dufyun/learn-tech-collection/tree/master/mysql_tuning,你可以进行下载实践。
最后,想分享的是 思路有时候比答案(结果)更重要。
谢谢你的阅读,如果您觉得这篇博文对你有帮助,请点赞或者喜欢,让更多的人看到!祝你每天开心愉快!
不管做什么,只要坚持下去就会看到不一样!在路上,不卑不亢!
博客首页 : http://blog.csdn.net/u010648555
愿你我在人生的路上能都变成最好的自己,能够成为一个独挡一面的人
© 每天都在变得更好的阿飞云
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/121043.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...