前言
主要介绍 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;
Id 是编号，User 是说明使用者，Host 表示访问的 IP 地址，db 表示访问的是哪个数据库，command 是运行什么样的命令：
- sleep：线程正在等待客户端发送新的请求
- query：线程正在执行查询或正在将结果发送给客户端
- locked：在 mysql 的服务层，该线程正在等待表锁
- analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
- Copying to tmp table：线程正在执行查询，并且将其结果集都复制到一个临时表中
- sorting result：线程正在对结果集进行排序
- sending data：线程可能在多个状态之间传送数据，或者在生成结果集或者向客户端返回数据
- Daemon：守护进程，等待被唤醒使用
Time 表示运行时间，状态 State，Info 表示一些详细信息
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
- 聚合后摘要表：
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)
