MySQL高级讲座篇之：驾驭Performance Schema：从底层事件监控到性能瓶颈的精准定位。

嘿，大家好！我是今天的主讲人，咱们今天要聊聊MySQL的Performance Schema，一个能帮你从底层监控数据库，精准定位性能瓶颈的超级工具。别怕，听起来高大上，其实用起来也就那么回事儿，保证你们听完能立马上手。

开场白：你的数据库是黑盒吗？

有没有遇到过这种情况：数据库突然慢下来，CPU飙升，你却像个无头苍蝇一样到处乱撞，不知道问题出在哪儿？ 慢查询日志？ 索引问题？ 锁等待？ 一通操作猛如虎，一看效果原地杵。

Performance Schema就是来拯救你的。 它可以让你像医生一样，给你的数据库做个全身检查，从CPU、内存、IO，到SQL语句的执行，每个环节都看得清清楚楚。从此告别盲猜，用数据说话！

第一部分：Performance Schema是什么？它能干啥？

Performance Schema是MySQL 5.5版本引入的一个性能监控工具，它收集了MySQL服务器运行时的各种底层事件信息，比如：

• SQL语句的执行时间： 谁执行了哪些SQL，花了多少时间？
• 锁的等待情况： 哪些线程在等待锁，等了多久？
• IO操作： 哪些文件被读取或写入，花了多少时间？
• 内存分配： 哪些组件在分配内存，分配了多少？
• 连接信息： 谁连接到了数据库，连接状态如何？

然后，它把这些信息存储在一些预定义的表中，你可以像查询普通表一样，查询这些信息，分析数据库的性能瓶颈。

Performance Schema的优势：

• 细粒度监控： 相比于慢查询日志，Performance Schema可以提供更细粒度的信息，比如函数调用、存储过程执行等等。
• 实时性： Performance Schema可以实时地监控数据库的运行状态，让你第一时间发现问题。
• 可配置性： 你可以根据自己的需求，配置Performance Schema监控哪些事件，以及收集多少信息。
• 标准化： Performance Schema的数据存储在标准的MySQL表中，你可以使用SQL语句进行分析，方便快捷。

第二部分：Performance Schema的基本概念

要用好Performance Schema，需要了解几个基本概念：

• Event（事件）： Performance Schema监控的最小单位，比如一条SQL语句的执行、一个锁的等待、一次IO操作等等。
• Instrument（检测器）： 用于收集特定类型事件信息的模块，比如statement检测器用于收集SQL语句的执行信息，wait检测器用于收集锁的等待信息。
• Consumer（消费者）： 用于存储收集到的事件信息的表，比如events_statements_summary_by_digest表用于存储SQL语句执行的汇总信息。
• Setup Objects（设置对象）： 用于控制哪些Instrument被启用，以及收集哪些事件信息。

举个例子：

想象一下，你是一家餐厅的老板，Performance Schema就像你的监控系统：

• Event： 顾客点餐、厨师炒菜、服务员上菜等等，都是一个个事件。
• Instrument： 你的监控摄像头，分别监控厨房、前台、仓库等等。
• Consumer： 你的监控录像，记录了每个摄像头拍摄到的画面。
• Setup Objects： 你的监控策略，决定哪些摄像头开启，哪些摄像头关闭，以及录像的保存时间。

第三部分：Performance Schema的配置与启用

默认情况下，Performance Schema是启用的，但可能没有收集所有的事件信息。你需要根据自己的需求，配置Performance Schema。

1. 检查Performance Schema是否启用：

SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'performance_schema';

如果结果是ON，说明Performance Schema已经启用。如果是OFF，你需要启用它：

SET GLOBAL performance_schema = ON;

注意： 修改performance_schema的值需要重启MySQL服务才能生效。

2. 配置Instrument：

使用setup_instruments表来配置哪些Instrument被启用。例如，启用所有statement类型的Instrument：

UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name LIKE 'statement/%';

3. 配置Consumer：

使用setup_consumers表来配置哪些Consumer被启用。例如，启用所有events_statements_summary_by_digest类型的Consumer：

UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name LIKE 'events_statements_summary_by_digest';

4. 配置Threads（可选）：

使用setup_threads表来配置Performance Schema监控哪些线程。默认情况下，Performance Schema会监控所有的线程。如果你只想监控特定的线程，可以修改setup_threads表。

例如，只监控用户名为'root'的线程：

UPDATE performance_schema.setup_threads SET enabled = 'YES' WHERE NAME LIKE '%root%';

重要提示：

• 启用过多的Instrument和Consumer可能会增加数据库的开销，影响性能。所以，要根据自己的需求，谨慎配置。
• Performance Schema的数据存储在内存中，重启MySQL服务后会丢失。如果你需要持久化存储这些数据，可以使用MySQL Enterprise Monitor或其他监控工具。

第四部分：Performance Schema常用表介绍

Performance Schema有很多表，但常用的也就那么几个。下面介绍一些常用的表：

表名	描述
events_statements_current	记录当前正在执行的SQL语句的详细信息，包括执行时间、锁等待时间等等。
events_statements_history	记录最近执行的SQL语句的详细信息，数量有限，先入先出。
events_statements_history_long	记录更长时间内执行的SQL语句的详细信息，数量有限，先入先出。
events_statements_summary_by_digest	按照SQL语句的摘要（digest）进行分组，统计SQL语句的执行次数、总执行时间、平均执行时间等等。这个表非常有用，可以用来找到执行最慢的SQL语句。
events_waits_current	记录当前正在等待的锁的信息，包括等待的锁类型、等待时间等等。
events_waits_history	记录最近发生的锁等待事件的信息，数量有限，先入先出。
events_waits_history_long	记录更长时间内发生的锁等待事件的信息，数量有限，先入先出。
events_transactions_current	记录当前正在执行的事务的信息，包括事务的隔离级别、开始时间等等。
events_transactions_history	记录最近执行的事务的信息，数量有限，先入先出。
events_transactions_history_long	记录更长时间内执行的事务的信息，数量有限，先入先出。
threads	记录当前连接到MySQL服务器的线程的信息，包括线程ID、用户名、主机名、状态等等。
memory_summary_global_by_event_name	按照事件名称进行分组，统计全局内存的使用情况，可以用来找到内存泄漏的根源。
file_summary_by_event_name	按照事件名称进行分组，统计文件的IO操作情况，可以用来找到IO瓶颈。

第五部分：Performance Schema实战案例

光说不练假把式，下面我们来看几个实战案例：

案例一：找到执行最慢的SQL语句

使用events_statements_summary_by_digest表可以找到执行最慢的SQL语句。

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT / 1000000000000 AS total_latency,
    AVG_TIMER_WAIT / 1000000000000 AS avg_latency
FROM
    performance_schema.events_statements_summary_by_digest
ORDER BY
    SUM_TIMER_WAIT DESC
LIMIT 10;

这条SQL语句会返回执行时间最长的10条SQL语句的摘要（digest）、执行次数、总执行时间、平均执行时间。

DIGEST_TEXT： 是SQL语句的摘要，可以用来识别相似的SQL语句。

COUNT_STAR： 是SQL语句的执行次数。

SUM_TIMER_WAIT： 是SQL语句的总执行时间，单位是皮秒（picosecond）。

AVG_TIMER_WAIT： 是SQL语句的平均执行时间，单位是皮秒（picosecond）。

优化建议：

• 如果发现某个SQL语句的执行时间很长，可以考虑优化SQL语句，比如添加索引、重写SQL语句等等。
• 如果发现某个SQL语句的执行次数很多，可以考虑减少SQL语句的执行次数，比如使用缓存、优化业务逻辑等等。

案例二：找到锁等待时间最长的线程

使用events_waits_current表可以找到锁等待时间最长的线程。

SELECT
    THREAD_ID,
    EVENT_NAME,
    SOURCE,
    TIMER_WAIT / 1000000000000 AS wait_latency
FROM
    performance_schema.events_waits_current
ORDER BY
    TIMER_WAIT DESC
LIMIT 10;

这条SQL语句会返回锁等待时间最长的10个线程的ID、事件名称、来源、等待时间。

THREAD_ID： 是线程的ID。

EVENT_NAME： 是等待的事件名称，比如wait/synch/mutex/sql/TC_LOG_mutex表示等待TC_LOG_mutex互斥锁。

SOURCE： 是等待事件的来源，比如sql/tc_log.cc:163表示等待事件发生在sql/tc_log.cc文件的第163行。

TIMER_WAIT： 是等待时间，单位是皮秒（picosecond）。

优化建议：

• 如果发现某个线程的锁等待时间很长，可以考虑优化锁的使用，比如减少锁的竞争、使用更细粒度的锁等等。
• 如果发现某个线程一直在等待同一个锁，可以考虑检查是否有死锁发生。

案例三：找到内存使用最多的组件

使用memory_summary_global_by_event_name表可以找到内存使用最多的组件。

SELECT
    EVENT_NAME,
    COUNT_ALLOC,
    COUNT_FREE,
    SUM_NUMBER_OF_BYTES_ALLOCATED,
    SUM_NUMBER_OF_BYTES_FREE
FROM
    performance_schema.memory_summary_global_by_event_name
ORDER BY
    SUM_NUMBER_OF_BYTES_ALLOCATED DESC
LIMIT 10;

这条SQL语句会返回内存使用最多的10个组件的名称、分配次数、释放次数、总分配字节数、总释放字节数。

EVENT_NAME： 是事件名称，可以用来识别不同的组件。

COUNT_ALLOC： 是分配次数。

COUNT_FREE： 是释放次数。

SUM_NUMBER_OF_BYTES_ALLOCATED： 是总分配字节数。

SUM_NUMBER_OF_BYTES_FREE： 是总释放字节数。

优化建议：

• 如果发现某个组件的内存使用量很大，可以考虑优化该组件的内存使用，比如减少内存分配、及时释放内存等等。
• 如果发现某个组件的内存分配次数远大于释放次数，可能存在内存泄漏。

案例四：查看某个SQL语句的详细执行信息

首先，找到SQL语句的DIGEST，例如通过案例一的查询。 然后，使用下面的SQL语句查看该SQL语句的详细执行信息：

SELECT
    event_id,
    timer_start,
    timer_end,
    sql_text
FROM
    performance_schema.events_statements_history_long
WHERE
    DIGEST = '你的SQL语句的DIGEST'
ORDER BY
    timer_start;

这条语句会显示该SQL语句的历史执行记录，包括每次执行的开始时间、结束时间以及完整的SQL文本。 这可以帮助你了解SQL语句的执行频率和执行时间变化。

案例五：分析慢查询的IO情况

SELECT
    fs.FILE_NAME,
    SUM(fs.COUNT_READ) AS total_reads,
    SUM(fs.COUNT_WRITE) AS total_writes,
    SUM(fs.SUM_NUMBER_OF_BYTES_READ) AS total_bytes_read,
    SUM(fs.SUM_NUMBER_OF_BYTES_WRITE) AS total_bytes_written
FROM
    performance_schema.file_summary_by_event_name fs
WHERE
    fs.EVENT_NAME LIKE 'wait/io/file/%'
GROUP BY
    fs.FILE_NAME
ORDER BY
    total_bytes_read DESC
LIMIT 10;

这条SQL语句会显示读取数据最多的文件，可以用来找到IO瓶颈。

第六部分：Performance Schema的注意事项

• 性能开销： 启用Performance Schema会带来一定的性能开销，尤其是在高并发的场景下。因此，要根据自己的需求，谨慎配置Performance Schema。
• 数据存储： Performance Schema的数据存储在内存中，重启MySQL服务后会丢失。如果你需要持久化存储这些数据，可以使用MySQL Enterprise Monitor或其他监控工具。
• 权限： 访问Performance Schema的表需要PROCESS权限。

第七部分：总结与展望

Performance Schema是一个强大的性能监控工具，可以帮助你从底层监控MySQL数据库，精准定位性能瓶颈。虽然学习曲线稍微陡峭，但是一旦掌握，你会发现它能帮你解决很多疑难杂症。

希望今天的讲座能帮助大家更好地理解和使用Performance Schema。记住，数据是最好的老师，善用Performance Schema，你就能成为MySQL性能优化的专家！

Q&A环节

接下来是大家提问环节，有什么问题尽管问，我会尽力解答。 别客气，问得越多，收获越大！ 咱们一起把MySQL玩转起来！