网站首页 文章专栏 MySQL-explain实例与profiling分析
MySQL-explain实例与profiling分析
编辑时间:2020-08-05 20:51 作者:小铨 浏览量:80

MySQL expalin 实例

优化操作:
```sql
1、单表:
mysql> explain select count(*),avg(monthsalary)  from customers1 where gender = 0;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | customers1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 948806 |    10.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


首先要做的就是分析结构;通过explain去分析;

从explain分析 type 可以看出来这个时候的SQL是进行了全表的扫描;key(查询实际用到的索引)与possible_keys(指出使用那个索引)key_len(索引的长度) 而extra为useing where

简单来说就是这个查询就是对于整个数据表进行了全表的扫描,没有使用到索引过滤;而主要的过滤条件就是where 也就是gender = 0;

这个时候我们可以建立索引尝试着去优化,对于索引的建立有很多的规则;之后再去细细解释;

根据索引的建立的规则-》把SQL分解

索引应该创建在搜索、排序、归组等操作所涉及的数据列上,旨在输出报告里出现的数据列不是好的候选。 简单点就是在where字句、关联检索中的from字句、order 与 group 字句中出现的数据列最适合用来创建索引。只在select关键字后面输出列清单例出现过的数据列并不是好的候选

select
  count(*),avg(monthsalary) -- 考虑一下
from
  customers1
where
  gender = 0                -- 这个不错

其他规则....过过过 之后细细分解

所以这个时候那么这个时候最合适创建索引的字段就是gender,执行alter

alter table customers1 add index gender(gender);

执行之后这个时候还是需要通过 explain分析一下数据的情况
mysql> explain select count(*),avg(monthsalary)  from customers1 where gender = 0;
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key    | key_len | ref   | rows   | filtered | Extra |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | customers1 | NULL       | ref  | gender        | gender | 1       | const | 652605 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

从分析的结果来看
type | possible_keys | key    | key_len | ref   | rows
ref  | gender        | gender | 1       | const | 652605

使用了刚刚创建的索引,长度为1 并且是为常量的方式查找

但是这里的extra为空,注意这个点;为空 ???先不纠结测试,但是测试的结果很尴尬

mysql> select count(*),avg(monthsalary)  from customers1 where gender = 0;
+----------+------------------+
| count(*) | avg(monthsalary) |
+----------+------------------+
|   651457 |      5004.957393 |
+----------+------------------+
1 row in set (41.58 sec)

并没有起到很好地效果???????? 为什么???? 其实这里的话就设计到了索引的设计以及查找的情况,主要的问题在于 索引回表(之后解释)了导致时间增长,那这个时候怎么优化呢??

思路的话可以分解select 对于count(*),avg(monthsalary)进行单独测试看看效果;找出是那个部分的因素引起的

注意:单独对于select count(*) from customers1 测试一下

mysql> explain select count(*) from customers1 where gender = 0;
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key    | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | customers1 | NULL       | ref  | gender        | gender | 1       | const | 652605 |   100.00 | Using index |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select avg(monthsalary) from customers1 where gender = 0;
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key    | key_len | ref   | rows   | filtered | Extra |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | customers1 | NULL       | ref  | gender        | gender | 1       | const | 652605 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可以看出来是avg(monthsalary)引起的,也就是说我们只需要把 select avg(monthsalary) from customers1 where gender = 0; 这个优化好了那么就可以了其实avg与count一样在MySQL操作的时候也会自动的匹配一个合适的索引,而count的默认匹配索引是主键,但是在我们上面的操作环节中因为给customers1创建了一个索引gender 这个时候count(*)在操作的时候就会以gender作为辅助索引使用。

而在上面的语句中仅仅只是根据 where gender = 0 过滤了查找的内容,但是在进行数据avg的时候这个时候就是需要去进行IO获取数据具体的数据,MySQL在辅助索引操作的时候如果无法从辅助索引中获取数据这个时候就会再去查询一级索引主键根据主键获取数据再做计算;所以为了与更好的进行monthsalary 的avg操作我们应该要给monthsalary建立一个索引

alter table customers1 add index monthsalary(monthsalary);

mysql> explain select avg(monthsalary)  from customers1 ;
+----+-------------+------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | customers1 | NULL       | index | NULL          | monthsalary | 5       | NULL | 1305211 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select avg(monthsalary)  from customers1 where gender = 0;
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key    | key_len | ref   | rows   | filtered | Extra |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | customers1 | NULL       | ref  | gender        | gender | 1       | const | 652605 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

o(▼皿▼メ;)o怎么还是使用了gender索引 (⊙_⊙)? 其实这就是MySQL对于一个表的索引的选择的关系,MySQL只会选择与最合适的哪一个索引而不是使用所有的索引,在上面的查询中最为合适的就是 gender

这里最好的处理就是建立一个 gender 与 monthsalary的联合索引; 删除之前的索引

alter table customers1 drop index monthsalary;
alter table customers1 drop index gender;

alter table customers1 add index gender_monthsalary(gender, monthsalary); => 注意顺序,gender在前

然后测试

mysql> explain select count(*),avg(monthsalary)  from customers1 where gender = 0;
+----+-------------+------------+------------+------+--------------------+--------------------+---------+-------+--------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys      | key                | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+------------+------------+------+--------------------+--------------------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | customers1 | NULL       | ref  | gender_monthsalary | gender_monthsalary | 1       | const | 652605 |   100.00 | Using index |
+----+-------------+------------+------------+------+--------------------+--------------------+---------+-------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

实际执行

mysql> select count(*),avg(monthsalary)  from customers1 where gender = 0;
+----------+------------------+
| count(*) | avg(monthsalary) |
+----------+------------------+
|   651457 |      5004.957393 |
+----------+------------------+
1 row in set (0.17 sec)

explain 优化指标
systme>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

1.const
如果是根据主键或唯一索引 只取出确定的一行数据。是最快的一种。
2.range
索引或主键,在某个范围内时
3.index
仅仅只有索引被扫描
4.all
全表扫描,最令人心痛
explain 语句的基本语法如下 
explain select select_options; 

下面看一下再mysql explain功能中展示各种信息的解释:
id:优化器选定的执行计划中查询的序列号。
select_type:所用的查询类型,主要由以下这集中查询类型。  
  DEPENDENT UNION:子查询中的UNION,且为UNION中从第二个SELECT开始的后面所有SELECT,同样依赖于外部查询的结果集。 . SIMPLE:厨子查询或UNION之外的其他查询。 》explain select * from admin_user where user_id = 1 \G;
  PRIMARY:子查询中最外层查询,注意并不是主键查询。 
  SUBQUERY: 子查询内层查询的第一个SELECT,结果不依赖与外部查询结果集。 》explain select * from role where id = (select role_id from admin_user where user_id = 1) \G; 
  UNCACHEABLE SUBQUERY:结果集无法缓存的子查询 
  UNION:UNION 语句中第二个SELECT开始后面的所有SELECT,第一个SELECT为PRIMARY。  
  UNION RESULT:UNION 中的合并结果。 
table:显示这一步所访问的数据库中的表的名称。 
type:告诉我们对标使用的访问方式,主要包含如下几种类型。 
  all: 全表扫描。 
  const: 读常量,最多只会有一条记录匹配,由于是常量,实际上只需要读一次。 
  eq_ref:最多只会有一条匹配结果,一般是通过主键或唯一键索引来访问 
  fulltext:进行全文索引检索 
  index:全索引扫描 
  index_merge:查询中同时使用两个(或更多)索引,然后对索引结果进行和并,在读取表数据。 
  index_subquery:子查询中的返回结果字段组合是一个索引(或索引组合),但不是一个主键或唯一索引。  
  rang:索引范围扫描。 
  ref:join语句中被驱动表索引引用的查询。 
  ref_or_null:与ref的唯一区别就是使用索引引用的查询之外再增加一个空值的查询。 
  system:系统表,表中只有一行数据: 
  unique_subqery:子查询中的返回结果字段组合式主键或唯一约束。 
Possible_keys:该查询可以利用的索引。如果没有任何索引可以使用,就会显示程null,这项内容对优化索引时的调整非常重要。 
key:优化器从possible_keys中选择使用的索引。 
key_len:被选中使用索引的索引建长度。 
ref:列出是通过常量,还是某个字段的某个字段来过滤的 
rows:优化其通过系统手机的统计信息估算出来的结果集记录条数 
extra:查询中每一步实现的额外细节信息。 
  using index : 出现这个说明mysql使用了覆盖索引,避免访问了表的数据行,效率不错。   
  using where :这说明服务器在存储引擎收到行后讲进行过滤。  
  using temporary :这意味着mysql对查询结果进行排序的时候使用了一张临时表   
  using filesort :这个说明mysql会对数据使用一个外部的索引排序 

注意当出现using temporary 和 using filesort时候说明需要优化操作

profiling分析

要想有画一条query,就需要清楚这条query的性能瓶颈到底在哪里,mysql的profiler是一个使用非常方便的查询诊断分析工具,通过该工具可以获取一条查询在整个执行过程中多种资源的消耗情况,例如内存消耗、I/O消耗和CPU消耗等。

profile的语法规则如下:

show profile [type [, type] ...]
[for query n]
[limit row_count [offset offset]]
其中type参数的可选项含义如下:

all:显示所有的信息
block io:限时输入输出操作阻塞的数量
context switches:显示自动或非自动context switches的数量
cpu:显示系统和用户cpu使用的时间
ipc:显示信息发送和接受的数量
memory:内存的信息
page faults:显示主要的page faults数量
source:显示函数的名称,并且系那是函数所在文件的名字和行数
swaps:显示swap数量
开启profile

MariaDB [community]> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)
在开启了query profiler功能之后,mysql就会自动记录所有执行的query的profile信息。下面执行query

select count(*) from admin_user_copy
select count(*) from purchase_order
通过执行 show profile 命令获取当前系统中保存的多个query的profile的概要信息。

MariaDB [community]> show profiles;
+----------+------------+--------------------------------------+
| Query_ID | Duration   | Query                                |
+----------+------------+--------------------------------------+
|        1 | 1.43076000 | select count(*) from admin_user_copy |
|        2 | 0.35574100 | select count(*) from purchase_order  |
+----------+------------+--------------------------------------+
2 rows in set (0.00 sec)
针对单个query获取详细的profile的信息

在获取概要信息之后就可以根据概要信息中的query_id来获取某一个query在执行过程中的profile信息了

MariaDB [community]> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000069 |
| checking permissions | 0.000019 |
| Opening tables       | 0.000035 |
| After opening tables | 0.000007 |
| System lock          | 0.000005 |
| Table lock           | 0.000014 |
| init                 | 0.000015 |
| optimizing           | 0.000010 |
| statistics           | 0.000019 |
| preparing            | 0.000021 |
| executing            | 0.000006 |
| Sending data         | 1.430456 |
| end                  | 0.000020 |
| query end            | 0.000010 |
| closing tables       | 0.000003 |
| Unlocking tables     | 0.000013 |
| freeing items        | 0.000006 |
| updating status      | 0.000027 |
| cleaning up          | 0.000005 |
+----------------------+----------+

MariaDB [community]> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000069 | 0.000058 |   0.000011 |            0 |             0 |
| checking permissions | 0.000019 | 0.000007 |   0.000011 |            0 |             0 |
| Opening tables       | 0.000035 | 0.000033 |   0.000002 |            0 |             0 |
| After opening tables | 0.000007 | 0.000005 |   0.000002 |            0 |             0 |
| System lock          | 0.000005 | 0.000004 |   0.000001 |            0 |             0 |
| Table lock           | 0.000014 | 0.000008 |   0.000006 |            0 |             0 |
| init                 | 0.000015 | 0.000014 |   0.000002 |            0 |             0 |
| optimizing           | 0.000010 | 0.000007 |   0.000001 |            0 |             0 |
| statistics           | 0.000019 | 0.000018 |   0.000001 |            0 |             0 |
| preparing            | 0.000021 | 0.000019 |   0.000002 |            0 |             0 |
| executing            | 0.000006 | 0.000006 |   0.000002 |            0 |             0 |
| Sending data         | 1.430456 | 1.928391 |   0.113126 |            0 |             0 |
| end                  | 0.000020 | 0.000008 |   0.000013 |            0 |             0 |
| query end            | 0.000010 | 0.000008 |   0.000001 |            0 |             0 |
| closing tables       | 0.000003 | 0.000002 |   0.000002 |            0 |             0 |
| Unlocking tables     | 0.000013 | 0.000012 |   0.000001 |            0 |             0 |
| freeing items        | 0.000006 | 0.000005 |   0.000001 |            0 |             0 |
| updating status      | 0.000027 | 0.000007 |   0.000020 |            0 |             0 |
| cleaning up          | 0.000005 | 0.000003 |   0.000001 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+



    出自:铨程互动

    地址:www.wuhequan.cn

    转载请注明出处!


来说两句吧
最新评论
0.098737s