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

已于 2023-08-01 23:14:36 修改
阅读量5.4k 收藏 14
点赞数 4
分类专栏： MySQL 文章标签： mysql 数据库 sql
于 2023-01-31 14:41:51 首次发布
vnjohn
本文链接：https://blog.csdn.net/vnjohn/article/details/128813814
版权

在这里插入图片描述

🔭 嗨，您好 👋 我是 vnjohn，在互联网企业担任 Java 开发，CSDN 优质创作者
📖 推荐专栏：Spring、MySQL、Nacos、Java，后续其他专栏会持续优化更新迭代
🌲文章所在专栏：MySQL
🤔 我当前正在学习微服务领域、云原生领域、消息中间件等架构、原理知识
💬 向我询问任何您想要的东西，ID：vnjohn
🔥觉得博主文章写的还 OK，能够帮助到您的，感谢三连支持博客🙏
😄 代词: vnjohn
⚡ 有趣的事实：音乐、跑步、电影、游戏

目录

前言

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

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

image-20210720012828800

通过上图可以看出，第一个开始运行时，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 是运行什么样的命令：

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

Time 表示运行时间，状态 State，Info 表示一些详细信息

Performance Schema

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

介绍

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

特征

Performance 特征

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

实践操作

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

  1. 哪类的 SQL 执行最多？
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
  1. 哪类 SQL 的平均响应时间最多？
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC;
  1. 哪类 SQL 排序记录数最多？
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC;
  1. 哪类 SQL 扫描记录数最多？
 SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC;
  1. 哪类 SQL 使用临时表最多？
 SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC;
  1. 哪类 SQL 返回结果集最多？
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC;
  1. 哪个表物理 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;
  1. 哪个表逻辑 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
  1. 哪个索引访问最多？
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
  1. 哪个索引从来没有用过？
 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;
  1. 哪个等待事件消耗时间最多？
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
  1. 剖析某条 SQL 执行情况，包括 statement 信息、stege 信息、wait 信息
 SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
  1. 查看每个阶段的时间消耗
 SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
  1. 查看每个阶段的锁等待情况
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;
  1. 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

ColumnMeaning
idThe SELECT identifier
select_typeThe SELECT type
tableThe table for the output row
partitionsThe matching partitions
typeThe join type
possible_keysThe possible indexes to choose
keyThe index actually chosen
key_lenThe length of the chosen key
refThe columns compared to the index
rowsEstimate of rows to be examined
filteredPercentage of rows filtered by table condition
extraAdditional 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 号分为三种情况👇

  1. 如果 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;

  1. 如果 id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

  2. 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 合并结果集

  1. 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
  2. 表明是 derivedN 的形式，表示使用了 id 为 N 的查询产生的衍生表
  3. 当有 union result 的时候，表名是 union n1，n2 等的形式，n1，n2 表示参与 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 NULL，BETWEEN，LIKE，OR，IN()
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 where：where 语句的结果总是 false
explain select * from emp where empno = 7469;

总结

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

MySQL 专栏高质量博文如下：

构建优化之城：MySQL 数据建模、数据类型优化与索引常识全面解析

MySQL 数据结构优化与索引细节解析：打造高效数据库的优化秘笈

MySQL 数据访问与查询优化：提升性能的实战策略和解耦优化技巧

深度解析 MySQL 事务、隔离级别和 MVCC 机制：构建高效并发的数据交响乐

MySQL 日志体系解析：保障数据一致性与恢复的三位英雄：Redo Log、Undo Log、Bin Log

解析 MySQL 锁机制：共享锁、排它锁、间隙锁、意向锁等，保障数据安全与高并发的秘密武器

如果觉得博文不错，关注我 vnjohn，后续会有更多实战、源码、架构干货分享！

推荐专栏：Spring、MySQL，订阅一波不再迷路

大家的「关注❤️ + 点赞👍 + 收藏⭐」就是我创作的最大动力！谢谢大家的支持，我们下文见！

vnjohn
关注
专栏目录
MySQL性能监控与分析工具
java专栏
07-24 856
📚 MySQL提供了多种内置和第三方工具来监控和分析性能。这些工具可以帮助我们监控数据库的状态、查询效率、系统资源使用情况等。
mysql监控工具
jc_benben的专栏
11-07 1233
1、Spotlight on mysql　　客户端工具，有漂亮的ui界面，可以监控到mysql的io、qcache、连接数、buffer pool等等，还有预警的功能    当然还有其他的相应监控比如Spotlight on linux2、Lepus-天兔[用的较多]　　一个开源的国产监控平台，可以监控到mysql的慢查询、 qcache、连接数、buffer pool等等，可以同时监控多台，配置...
Mysql性能监控工具
01-28
Mysql性能监控工具（MONyog-5.5.8-0.i386.tar.gz）
mysql性能优化-日志与监控
最新发布
Flying_Fish_roe的博客
09-09 942
日志与监控在 MySQL 性能优化中扮演了至关重要的角色。通过启用并分析 MySQL 的多种日志（如错误日志、慢查询日志、查询日志等），开发者可以快速识别数据库中的性能瓶颈。而结合监控工具（如 Prometheus + Grafana、PMM 等），可以实时跟踪数据库的性能状况，确保数据库运行的稳定性。
Mysql监控工具–mytop
系统运维
11-27 254
Andyguo 2012-11-27 mytop 是一个类似 Linux 下的 top 命令风格的 MySQL 监控工具，可以监控当前的连接用户和正在执行的命令。 一Mytop安装 1. 安装TermReadKey 下载地址：http://search.cpan.org/CPAN/authors/id/J/JS/JSTOWE/TermReadKey-2.30.tar.gz ...
tcp连接数修改工具_两款免费的MySQL性能监控工具
weixin_39954569的博客
12-15 341
MySQL管理和性能监控工具，能帮助你监控MySQL服务器，以及微调其性能和速度。众所周知，MySQL是功能强大的开源数据库系统，可让企业运行一系列不同的应用，如网站和ERP存储系统。MySQL是任何企业的关键资源，因为它可以保持应用的正常运行。而你需要找到合适的MySQL监控工具来确保数据库始终处于良好状态。以下推荐两款，免费、开源的工具。Zenoss MySQL Database ZenPac...
mysql监控程序_Mysql监控工具小集合
weixin_31586521的博客
01-21 606
介绍一些常见的Mysql监控工具。CactiCacti是 一套基于PHP,MySQL,SNMP及RRDTool开发的网络流量监测图形分析工具。它通过snmpget来获取数据，使用 RRDtool绘画图形，而且你完全可以不需要了解RRDtool复杂的参数。它提供了非常强大的数据和用户管理功能，可以指定每一个用户能查看树状结 构、host以及任何一张图，还可以与LDAP结合进行用户验证，同时也能自己增...
监控MySQL的工具
qq_46130027的博客
07-26 2012
这是一组流行的开源工具，可以通过Prometheus监控MySQL数据库，然后使用Grafana创建漂亮的仪表板进行可视化。由Oracle提供的商业工具，提供实时和历史性能监控，包括查询性能、服务器状态、数据库复制等。虽然它是一个通用的网络监控工具，但可以使用插件来监控MySQL数据库的各种指标。类似于Nagios，是一个通用的网络监控工具，但也支持MySQL数据库监控。这是一个基于命令行的工具，用于实时监控MySQL数据库的性能。，提供对MySQL数据库的性能和状态的实时监控。
Druid数据库连接池及内置监控的配置和使用.zip
09-23
Druid是一个高效、强大且灵活的数据库连接池组件，它由阿里巴巴开源并广泛应用于各种Java Web项目中。...通过阅读提供的资源“2019-6-27_Druid数据库连接池及内置监控的配置和使用”，您可以获得更详尽的实践指导。
适用于 MySQL 的 Amazon RDS 和 MariaDB 的监控和警报工具以及最佳实践
07-21
- **内置工具**：Amazon RDS 提供了一系列内置工具用于监控数据库实例的健康状况和性能表现。 - **CloudWatch 命名空间**：利用 AWS CloudWatch 这一服务收集和监控 RDS 实例的性能指标。 - **CloudWatch 警报与...
MySQL 效能监控工具
08-18
MySQL 效能监控工具--mysqlreport
详解MySQL监控工具 mysql-monitor
09-08
主要介绍了解MySQL监控工具 mysql-monitor的相关知识，本文通过实例代码给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友参考下吧
mysql数据库监控
01-14
mysql数据库监控，显示基本的监控信息：cpu、内存、慢查询。
MySQLGlance:轻量级的mysql性能监控工具-开源
05-31
MySQLGlance 是一个轻量级的 mysql 性能监控工具，由 parnassusdata.com 开发。 它只会关注最重要的指标。 您无需任何先决条件即可轻松运行它。 它会要求很少的权限：将 performance_schema.* 上的选择授予 $USERNAME; 授予选择，在 sys.* 上执行给 $USERNAME； *.* 上的授权过程给 $USERNAME； 同花顺特权； MySQLGlance 是免费软件，您可以随意使用。
推荐一款革命性的MySQL监控神器：MySQL Monitor
gitblog_00053的博客
06-12 518
推荐一款革命性的MySQL监控神器：MySQL Monitor 去发现同类优质开源项目:https://gitcode.com/ 项目介绍 在这个信息化时代，数据库作为企业的核心资产之一，其稳定性和性能至关重要。对于业务研发团队而言，一款高效、直观的数据库监控工具能够极大提升工作效率与响应速度。MySQL Monitor应运而生，这是一款专为研发人员打造的图形化监控工具，它不仅具备强大的监控功能，...
mysql自带的监控工具_什么mysql监控工具好，麻烦大家推荐一下！
weixin_31495637的博客
01-19 573
展开全部首先介绍下 pt-stalk，它是 Percona-Toolkit 工具包中的一个工具，说起 PT 工具包大家都不陌生，平e5a48de588b662616964757a686964616f31333433653363时常用的 pt-query-digest、 pt-online-schema-change 等工具都是出自于这个工具包，这里就不多介绍了。pt-stalk 的主要功能是在出现...
mysql性能监控，配置文件优化
weixin_33877885的博客
03-04 326
2019独角兽企业重金招聘Python工程师标准>>> ...
windows下配置mysql数据库监视工具Mysqlreport
reblue520的专栏
11-24 600
该工具除了可以监控本机Mysql数据库外，也可以监控远程服务器mysql数据库 需要的工具: 1：perl脚本解析工具安装: http://www.activestate.com/activeperl/  2：mysqlreport的perl脚本代码: http://hackmysql.com/mysqlreport 3：单独安装apa...
mysql 内置监控工具
11-17
MySQL提供了多种内置监控工具，以下是其中的几个： 1. SHOW STATUS命令：该命令可以显示MySQL服务器的各种状态信息，例如连接数、查询数、缓存命中率等等。可以通过定期执行该命令并将结果记录到日志文件中，然后...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

vnjohn

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值