MySQL 内置的监控工具介绍及使用篇

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

该篇文章主要介绍 MySQL 中内置的一些监控工具
Show Profile监控语句各个阶段所消耗的情况便于 SQL 调优的测量
Show Processlist用于监控数据库的连接信息
Performance Schema通过监视 server 事件来实现监视 server 内部运行情况
Explain如何知晓 SQL 语句的执行结果需要查看 SQL 语句的具体执行流程例如表中的索引是否正常运用查询筛选的行数有多少等

MySQL 官网 sakila 库脚本及数据下载sakila 库以下所有 SQL 测试都以该库数据为例.

Show Profile

MySQL 官网链接https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

如何查看 SQL 具体执行时间步骤如下

查看是否开启0-关闭、1-开启SELECT @@profiling;
开启本地会话监控set profiling=1;
随便查询一张表数据select * from actor;
列表展示之前所执行的 SQL 语句时长show profiles;

列表的条数展示默认是有限制的默认值只展示最新的 15 条数据通过服务端系统参数profiling_history_size 可以进行调整最大值可以调整为 100 条.
如上图查询实际执行时间这个就是当前查询语句执行的时间如果还想要看更细节的展示可以执行如下命令

show profile for query 展示的是上面的 Query_ID 对应的值…

通过上图可以看出第一个开始运行时starting 浪费时间比较多第二个就是我们在发送数据的时候浪费时间也比较多其他时间都是比较少的如果行数据开始变多时较多的时长会在 executing.

摘至 MySQL 官网

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL:显示所有性能信息—>show profile all for query n
  | BLOCK IO:显示块 IO 操作次数—>show profile block io for query n
  | CONTEXT SWITCHES:显示上下文切换次数被动和主动—>show profile context switches for query n
  | CPU:显示用户 CPU 时间、系统 CPU 系统—> show profile cpu for query n 
  | IPC:显示发送和接受的消息数量—>show profile ipc for query n
  | MEMORY:暂未实现
  | PAGE FAULTS:显示页错误数量—>show profile page faults for query n
  | SOURCE:显示源码中函数名称与位置—>show profile source for query n
  | SWAPS:显示 swap 次数—>show profile swaps for query n
}

在低版本 MySQL 还可以使用这样的一些 profile 属性目前包括 8 版本里面也可以进行使用但官方有下面那一句非常重要的话

Note
The SHOW PROFILE and SHOW PROFILES statements are deprecated; expect them to be removed in a future MySQL release. Use the Performance Schema instead; see Section 25.19.1, “Query Profiling Using Performance Schema”.

大致意思show profile 慢慢会被丢弃甚至在未来的 MySQL 版本可能就没有了使用 performance schema 取而代之

注意show profile 在当前会话中生效可使用若当前会话关闭以后分析的信息将会消失掉.

Show Processlist

官网https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

除了可以监控 MySQL 性能之外还有一个非常重要的点就是监控我们数据库的连接执行如下命令show processlist;

show_processlist

Id 是编号User 是说明使用者Host 表示访问的 IP 地址db 表示访问的是哪个数据库command 是运行什么样的命令

sleep线程正在等待客户端发送新的请求
query线程正在执行查询或正在将结果发送给客户端
locked在 mysql 的服务层该线程正在等待表锁
analyzing and statistics线程正在收集存储引擎的统计信息并生成查询的执行计划
Copying to tmp table线程正在执行查询并且将其结果集都复制到一个临时表中
sorting result线程正在对结果集进行排序
sending data线程可能在多个状态之间传送数据或者在生成结果集或者向客户端返回数据
Daemon守护进程等待被唤醒使用

Time 表示运行时间状态 StateInfo 表示一些详细信息

Performance Schema

Performance Schema(性能模块) 官网https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html

介绍

Performance 介绍主要是用来监控我们的数据库执行在一个比较低的级别里面Performance Schema 开启还是有必要的虽然开启之后会消耗一部分系统资源但是损失一点系统资源后面可以更快的定位到问题.

特征

Performance 特征

提供了一种在数据库运行时实时检查 server 内部执行情况方法performance_schema 数据库表使用 performance_schema 存储引擎该数据库主要关注数据库运行过程中的性能相关数据与 information_schema 不同information_schema 主要关注 server 运行过程中的元数据信息
通过监视 server 事件来实现监视 server 内部运行情况”事件“就是 server 内部活动中所做的任何事情以及对应的时间消耗利用这些信息来判断 server 中的相关资源消耗在了哪里一般来说事件可以是函数调用、操作系统的等待、SQL 语句执行的阶段如 SQL 语句执行过程中的 parsing 或 sorting 阶段或者整个 SQL 语句与 SQL 语句集合。事件的采集可以方便的提供 server 中的相关存储引擎对磁盘文件、表 I/O、表锁等资源的同步调用信息
performance_schema 中的事件不同于写入二进制日志中的事件描述数据的修改和事件计划调度程序这是一种存储程序
performance_schema 中的事件只记录在本地 server 的 performance_schema其下的这些表中数据发生变化不会被写入 binlog 中也不会通过复制机制被复制到其他 server 中.
当前活跃事件、历史事件和事件摘要相关的表中记录的信息你能提供某个事件的执行次数、使用时长进而可用于分析某个特定线程、特定对象如 mutex 或 file相关联的活动
performance_schema 存储引擎使用 server 源代码中的”检测点“来实现事件数据的收集对于 performance_schema 实现机制本身的代码没有相关的单独线程来检测这与其他功能如复制或事件计划程序不同
收集的事件数据存储在 performance_schema 数据库的表中这些表可以使用 SELECT 语句查询也可以使用 SQL 语句更新 performance_schema 数据库的表记录如动态修改 performance_schema 的 setup_* 开头的几个配置表但要注意配置表的更改会立即生效这会影响数据收集
performance_schema 表中的数据不会持久化存储在磁盘中而是保存在内存中一旦服务器重启这些数据会丢失包括配置表在内的整个 performance_schema 下的所有数据
MySQL 支持的所有平台中事件监控功能都可用但不同平台中用于统计事件时间开销的计时器类型可能会有所差异

入门

在 MySQL 5.7 版本中性能模式是默认开启的如果想要显示关闭的话需要修改配置文件不能直接修改会报错

在配置文件中修改 performance_schema 属性值ON 表示开启OFF 表示关闭
```
[mysqld]
performance_schema=ON
```

查看 performance_schema 属性

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

查看创建表时的表结构

mysql> show create table setup_consumers;
---------------------------------------------------------------+
| Table           | Create Table                           
| setup_consumers | CREATE TABLE `setup_consumers` (
  `NAME` varchar(64) NOT NULL,
  `ENABLED` enum('YES','NO') NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |
---------------------------------------------------------------+
1 row in set (0.00 sec)

两个基础概念

instruments生产者用于采集 MySQL 中各种各样的操作产生的事件信息对于配置表的配置项我们可以称为监控配置项
consumers消费者对应消费者用于存储来自 instruments 采集的数据对于配置表的配置项我们可以称为消费存储配置项

表分类

performance_schema 库下的表可以按照监视不同的维度进行分组

语句事件记录表这些表记录了语句事件信息

当前语句事件表events_statements_current
历史语句事件表events_statements_history
长语句历史事件表events_statements_history_long
聚合后摘要表summarysummary 表还可以根据账号(account)、主机(host)、程序(program)、线程(thread)、用户(user)和全局(global)再进行细分

mysql> show tables like '%statement%';
+----------------------------------------------------+
| Tables_in_performance_schema (%statement%)         |
+----------------------------------------------------+
| events_statements_current                          |
| events_statements_history                          |
| events_statements_history_long                     |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest                |
| events_statements_summary_by_host_by_event_name    |
| events_statements_summary_by_program               |
| events_statements_summary_by_thread_by_event_name  |
| events_statements_summary_by_user_by_event_name    |
| events_statements_summary_global_by_event_name     |
| prepared_statements_instances                      |
+----------------------------------------------------+
11 rows in set (0.00 sec)

等待事件记录表与语句事件类型相关记录表类似

mysql> show tables like '%wait%';
+-----------------------------------------------+
| Tables_in_performance_schema (%wait%)         |
+-----------------------------------------------+
| events_waits_current                          |
| events_waits_history                          |
| events_waits_history_long                     |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name    |
| events_waits_summary_by_instance              |
| events_waits_summary_by_thread_by_event_name  |
| events_waits_summary_by_user_by_event_name    |
| events_waits_summary_global_by_event_name     |
| table_io_waits_summary_by_index_usage         |
| table_io_waits_summary_by_table               |
| table_lock_waits_summary_by_table             |
+-----------------------------------------------+
12 rows in set (0.00 sec)

阶段事件记录表记录语句执行的阶段事件表

mysql> show tables like '%stage%';
+------------------------------------------------+
| Tables_in_performance_schema (%stage%)         |
+------------------------------------------------+
| events_stages_current                          |
| events_stages_history                          |
| events_stages_history_long                     |
| events_stages_summary_by_account_by_event_name |
| events_stages_summary_by_host_by_event_name    |
| events_stages_summary_by_thread_by_event_name  |
| events_stages_summary_by_user_by_event_name    |
| events_stages_summary_global_by_event_name     |
+------------------------------------------------+
8 rows in set (0.00 sec)

事务事件记录表记录事务相关的事件表

mysql> show tables like '%transaction%';
+------------------------------------------------------+
| Tables_in_performance_schema (%transaction%)         |
+------------------------------------------------------+
| events_transactions_current                          |
| events_transactions_history                          |
| events_transactions_history_long                     |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name    |
| events_transactions_summary_by_thread_by_event_name  |
| events_transactions_summary_by_user_by_event_name    |
| events_transactions_summary_global_by_event_name     |
+------------------------------------------------------+
8 rows in set (0.00 sec)

监控文件系统层调用的表

mysql> show tables like '%file%';
+---------------------------------------+
| Tables_in_performance_schema (%file%) |
+---------------------------------------+
| file_instances                        |
| file_summary_by_event_name            |
| file_summary_by_instance              |
+---------------------------------------+
3 rows in set (0.00 sec)

监视内存使用的表

mysql> show tables like '%memory%';
+-----------------------------------------+
| Tables_in_performance_schema (%memory%) |
+-----------------------------------------+
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name    |
| memory_summary_by_thread_by_event_name  |
| memory_summary_by_user_by_event_name    |
| memory_summary_global_by_event_name     |
+-----------------------------------------+
5 rows in set (0.00 sec)

动态对 performance_schema 进行配置的配置表

mysql> show tables like '%setup%';
+----------------------------------------+
| Tables_in_performance_schema (%setup%) |
+----------------------------------------+
| setup_actors                           |
| setup_consumers                        |
| setup_instruments                      |
| setup_objects                          |
| setup_timers                           |
+----------------------------------------+
5 rows in set (0.00 sec)

简单配置及使用

虽然说 performance_schema 服务默认情况下是开启的但是它里面的一些属性并不是开启的执行如下语句

SELECT * FROM setup_instruments;

setup-instruments

ENABLED 代表这个属性是否开启TIMED 代表计时器是否开启为了能够完整地监控我们的信息都把这些开启一下

打开等待事件的采集器配置项开关需要修改setup_instruments配置表中对应的采集器配置项
```
UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name like 'wait%';
```
打开等待事件的保存表配置开关修改setup_consumers配置表中对应的配置项
```
UPDATE setup_consumers SET ENABLED = 'YES'where name like '%wait%';
```
当配置完成之后可以查看当前 server 正在做什么可以通过查询 events_waits_current 表来得知该表中每个线程只包含一行数据用于显示每个线程的最新监视事件
```
mysql> select * from events_waits_current\G
*************************** 1. row ***************************
      THREAD_ID: 13
       EVENT_ID: 485
   END_EVENT_ID: 485
     EVENT_NAME: wait/synch/mutex/innodb/buf_dblwr_mutex
         SOURCE: 
    TIMER_START: 6364820648454270
      TIMER_END: 6364820648844465
     TIMER_WAIT: 390195
          SPINS: NULL
  OBJECT_SCHEMA: NULL
    OBJECT_NAME: NULL
     INDEX_NAME: NULL
    OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 60264520
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
      OPERATION: lock
NUMBER_OF_BYTES: NULL
          FLAGS: NULL
```
该信息表示线程id为 13 的线程正在等待 buf_dblwr_mutex 锁等待时间为 30880
属性说明
id事件来自哪个线程事件编号是多少
event_name表示检测到的具体的内容
source表示这个检测代码在哪个源文件中以及行号
timer_start表示该事件的开始时间
timer_end表示该事件的结束时间
timer_wait表示该事件总的花费时间
注意_current 表中每个线程只保留一条记录一旦线程完成工作该表中不会再记录该线程的事件信息

select thread_id,event_id,event_name,timer_wait from events_waits_history order by thread_id limit 21;

history 表中记录每个线程应该执行完成的事件信息但每个线程的事件信息只会记录 10 条再多就会被覆盖*_history_long表中记录所有线程的事件信息但总记录数量是 10000超过就会被覆盖掉

SELECT EVENT_NAME,COUNT_STAR FROM events_waits_summary_global_by_event_name  ORDER BY COUNT_STAR DESC LIMIT 10;

summary 表提供所有事件的汇总信息该组中的表以不同的方式汇总事件数据如按用户按主机按线程等等。例如要查看哪些 instruments 占用最多的时间可以通过对 events_waits_summary_global_by_event_name 表的 COUNT_STAR 或SUM_TIMER_WAIT 列进行查询这两列是对事件的记录数执行COUNT*、事件记录的 TIMER_WAIT 列执行SUM 统计而来

select * from file_instances limit 20;

instance 表记录了哪些类型的对象会被检测。这些对象在被server使用时在该表中将会产生一条事件记录例如file_instances表列出了文件I/O操作及其关联文件名

实践操作

基本了解了表的相关信息之后可以通过这些表进行实际的查询操作来进行实际的分析

哪类的 SQL 执行最多

SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

哪类 SQL 的平均响应时间最多

SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC;

哪类 SQL 排序记录数最多

SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC;

哪类 SQL 扫描记录数最多

 SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC;

哪类 SQL 使用临时表最多

 SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC;

哪类 SQL 返回结果集最多

SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC;

哪个表物理 IO 最多
逻辑IO是操作系统发起的IO这个数据可能会放在磁盘上也可能会放在内存里文件系统的 Cache
物理IO是设备驱动发起的IO这个数据最终会落在磁盘上

 SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC;

哪个表逻辑 IO 最多

 SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC

哪个索引访问最多

SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC

哪个索引从来没有用过

 SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;

哪个等待事件消耗时间最多

SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC

剖析某条 SQL 执行情况包括 statement 信息、stege 信息、wait 信息

 SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';

查看每个阶段的时间消耗

 SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;

查看每个阶段的锁等待情况

SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;

MySQL Server 每秒执行的 Query 总量计算方式如下

 Questions=SHOW GLOBAL STATUS LIKE 'Questions';
 Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
 QPS=Questions/Uptime

Explain 执行计划

在企业的应用场景中为了知道优化 SQL 语句的执行需要查看 SQL 语句的具体执行流程以加快 SQL 语句的执行效率.

可以使用 explain+SQL 语句来模拟优化器执行 SQL 查询语句从而知道 MySQL 是如何处理 SQL 语句的.

官网地址 https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

Column	Meaning
id	The `SELECT` identifier
select_type	The `SELECT` type
table	The table for the output row
partitions	The matching partitions
type	The join type
possible_keys	The possible indexes to choose
key	The index actually chosen
key_len	The length of the chosen key
ref	The columns compared to the index
rows	Estimate of rows to be examined
filtered	Percentage of rows filtered by table condition
extra	Additional information

测试脚本及数据

CREATE TABLE `dept`(
    `deptno` INT(2) NOT NULL, 
    `dname` VARCHAR(14),
    `loc` VARCHAR(13),
    CONSTRAINT pk_dept PRIMARY KEY(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 
INSERT INTO dept VALUES (30,'SALES','CHICAGO');  
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

CREATE TABLE `emp` (
    `empno` int(4) NOT NULL PRIMARY KEY,
    `ename` VARCHAR(10),  
    `job` VARCHAR(9),  
    `mgr` int(4),  
    `hiredate` DATE,  
    `sal` float(7,2),  
    `comm` float(7,2),  
    `deptno` int(2),
    CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20); 
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10); 
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20); 
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

CREATE TABLE `bonus`(
    `ename` VARCHAR(10),
    `job` VARCHAR(9),
    `sal` INT, 
    `comm` INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `salgrade` (  
    `grade` int, 
    `losal` int,  
    `hisal` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
INSERT INTO SALGRADE VALUES (1,700,1200); 
INSERT INTO SALGRADE VALUES (2,1201,1400); 
INSERT INTO SALGRADE VALUES (3,1401,2000); 
INSERT INTO SALGRADE VALUES (4,2001,3000); 
INSERT INTO SALGRADE VALUES (5,3001,9999);

id

select 查询的序列号包含一组数字表示查询中执行 select 子句或者操作表的顺序id 号分为三种情况

如果 id 相同那么执行顺序从上而下

EXPLAIN SELECT * FROM emp e JOIN dept d ON e.deptno = d.deptno JOIN salgrade sg ON e.sal BETWEEN sg.losal AND sg.hisal;

如果 id 不同如果是子查询id 的序号会递增id 值越大优先级越高越先被执行
id 相同和不同的同时存在相同的可以认为是一组从上往下顺序执行在所有组中id 值越大优先级越高越先执行

select_type

主要用来分辨查询的类型是普通查询还是联合查询还是子查询

sample简单的查询不包含子查询和 union

explain select * from emp;

primary查询中若包含任何复杂的子查询最外层查询则被标记为 Primary

explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno;

union若第二个 select 出现 union 之后则被标记为 union

explain select * from emp where deptno = 10 union select * from emp where sal >2000;

dependent union跟 union 类似此处的 dependent 表示 union 或 union all 联合而成的结果会受外部影响

explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000);

union result从 union 表获取结果的 select

explain select * from emp where deptno = 10 union select * from emp where sal >2000;

subquery在 select 或者 where 列表中包含子查询

explain select * from emp where sal > (select avg(sal) from emp) ;

UNCACHEABLE SUBQUERY表示子查询的结果不能被缓存

explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);

table

对应行正在访问哪一个表表名或者别名可能是临时表或者 union 合并结果集

如果是具体的表名则表明从实际的物理表中获取数据当然也可以是表的别名
表明是 derivedN 的形式表示使用了 id 为 N 的查询产生的衍生表
当有 union result 的时候表名是 union n1n2 等的形式n1n2 表示参与 union 的 id.

type

type 显示的是访问类型访问类型表示以何种方式去访问我们的数据最容易想的是全表扫描直接暴力的遍历一张表去寻找需要的数据效率非常低下访问的类型有很多效率从最好到最坏依次是

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般情况下得保证查询至少达到 range 级别最好能达到 ref

all全表扫描一般情况下出现这样的 SQL 语句而且数据量比较大时就需要进行优化

explain select * from emp;

index全索引扫描这个比 all 效率要好主要有两种情况1.当前的查询是覆盖索引即我们需要的数据在索引中就可以索取或者是使用了索引进行排序这样就避免数据的重排序.

explain  select empno from emp;

range表示利用索引查询时限制了范围在指定范围内进行查询这样避免了 index 的全索引扫描适用的操作符=<>>>=<<=IS NULLBETWEENLIKEORIN()

explain select * from emp where empno between 7000 and 7500;

index_subquery利用索引来关联子查询不再扫描全表

explain select * from emp where emp.job in (select job from t_job);

unique_subquery该连接类型类似于 index_subquery使用的是唯一索引

 explain select * from emp e where e.deptno in (select distinct deptno from dept);

index_merge在查询过程中需要多个索引组合使用
ref_or_null对于某个字段即需要关联条件也需要 null 值的情况下查询优化器会选择这种访问方式

explain select * from emp e where  e.mgr is null or e.mgr=7369;

ref使用了非唯一性索引进行数据的查找

 create index idx_3 on emp(deptno);
 explain select * from emp e,dept d where e.deptno =d.deptno;

eq_ref使用唯一性索引进行数据查找

explain select * from emp,emp2 where emp.empno = emp2.empno;

const这个表至少有一个匹配行

explain select * from emp where empno = 7369;

system表只有一行记录等于系统表这是 const 类型的特例平时不会出现

possible_keys

显示可以应用在这张表中的索引一个或多个查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询实际使用

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

key

实际使用的索引如果为 null则没有使用索引查询中若使用了覆盖索引则该索引和查询的 select 字段重叠

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

key_len

表示索引中使用的字节数可以通过 key_len 计算查询中使用的索引长度在不损失精度的情况下越短越好.

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

ref

显示索引的哪一列被使用了如果可能的话是一个常数

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

rows

根据表的统计信息及索引使用情况大致估算出找出所需记录需要读取的行数此参数很重要直接反应 SQL 找了多少数据在完成目的情况下越少越好

explain select * from emp;

extra

包含额外的信息

using filesort说明 MySQL 无法利用索引进行排序只能利用排序算法进行排序1~n 或 a-z会消耗额外的位置

explain select * from emp order by sal;

using temporary建立临时表来保存中间结果查询完成之后把临时表删除

explain select ename,count(*) from emp where deptno = 10 group by ename;

using index这个表示当前查询是覆盖索引的直接从索引中读取数据而不用访问数据表如果同时出现 using where 表明索引被用来执行键值的查找如果没有表明索引被用来读取数据而不是真的查找

explain select deptno,count(*) from emp group by deptno limit 10;

using where使用 where 进行条件过滤

explain select * from t_user where id = 1;

using join buffer使用连接缓存出现该情况时效率会急剧下降两张表进行数据关联的列都新增索引这样才会避免使用到连接缓存造成查询效率的下降
impossible wherewhere 语句的结果总是 false

explain select * from emp where empno = 7469;

总结

MySQL 使用作为后端人员是必须牢牢掌握的一门技术而调优的路上是作为后端晋升的必经之路所以先了解 MySQL 本身拥有的监控工具也是必备的当你后面涉及到数据库调优的东西时就可以从这些工具上入手了
当然现在市场上提供了大部分的第三方数据监控工具先学到自带的再应用别的工具时其实也能发现其实就运用这上面的功能和新增了一些业务上比较经常需要监控的指标信息

更多技术文章可以查看vnjohn 个人博客

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

标签: mysql