各位观众老爷，晚上好！我是今天的主讲人，咱们今儿个聊聊MySQL的sys schema，尤其是如何用它来监控活跃会话和锁信息。这玩意儿啊，就像你家里的监控摄像头，帮你随时掌握数据库的“家里情况”，关键时刻还能抓“小偷”。
开场白：为啥要关注活跃会话和锁？
想象一下，你开了一家餐厅，生意火爆，但突然客人开始抱怨上菜慢，甚至有人吵起来了。原因可能有很多：
- 会话过多： 厨房太小，厨师不够，导致订单积压。
- 锁冲突： 两个服务员同时抢着点同一桌的菜，互相卡住了。
数据库也一样，过多的活跃会话会消耗服务器资源，导致查询变慢；锁冲突则会阻塞事务，影响并发性能。所以，监控这些信息，就像餐厅经理监控客流量和后厨情况一样，是保证数据库高效运行的关键。
sys schema就是MySQL提供的一套方便快捷的“监控工具”，它基于Performance Schema，提供了大量预定义的视图，让我们可以轻松地查看各种系统信息。
第一幕：sys schema初体验
首先，确认你的MySQL版本支持sys schema。一般来说，MySQL 5.7及以上版本都自带。
可以通过以下SQL语句来验证：
SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'sys';如果返回sys，那就说明sys schema已经安装好了。如果没有，那就需要手动安装。具体安装方法可以参考MySQL官方文档，这里咱们就不展开说了。
接下来，让我们来简单浏览一下sys schema里都有啥宝贝：
SHOW TABLES FROM sys;你会看到一堆以sys开头的表或视图，比如sys_config、processlist、schema_table_statistics等等。这些视图都提供了不同的系统信息，咱们今天主要关注的是和活跃会话和锁相关的视图。
第二幕：监控活跃会话
sys.processlist视图是监控活跃会话的利器，它提供了当前MySQL服务器上所有连接的信息，包括连接ID、用户、主机、数据库、命令、状态、持续时间等等。
先来个简单的查询，看看现在有哪些连接：
SELECT * FROM sys.processlist;你会看到一个表格，包含了所有连接的详细信息。但是，这么多列，我们真正关心的是哪些呢？
user： 连接的用户是谁，是谁在“捣乱”。host： 连接来自哪个主机，是本地还是远程。db： 连接正在使用的数据库，问题可能出在哪里。command： 连接正在执行的命令，是查询还是更新。state： 连接的当前状态，是等待锁还是正在执行。time： 连接已经持续的时间，运行时间过长的连接可能是罪魁祸首。info： 连接正在执行的SQL语句，看看谁在跑慢查询。
为了更方便地查看活跃会话，我们可以自定义查询语句，只显示我们关心的列：
SELECT
user,
host,
db,
command,
state,
time,
info
FROM
sys.processlist
WHERE
command != 'Sleep' -- 排除空闲连接
ORDER BY
time DESC; -- 按照持续时间降序排列这条SQL语句会显示所有非空闲的连接，并按照持续时间降序排列。这样，我们就可以快速找到运行时间最长的连接，看看是不是有慢查询或者死循环。
进阶技巧：找出占用CPU最多的会话
sys.processlist虽然好用，但它只能看到连接的持续时间，无法直接看到CPU占用率。要找出占用CPU最多的会话，我们需要借助Performance Schema的processlist表，然后和sys.processlist关联查询。
SELECT
p.user,
p.host,
p.db,
p.command,
p.state,
p.time,
p.info,
ps.cpu_time
FROM
sys.processlist p
JOIN
performance_schema.threads t ON p.thd_id = t.thread_id
JOIN
performance_schema.thread_summary_global_by_event_name ps ON t.thread_id = ps.thread_id
WHERE
p.command != 'Sleep'
ORDER BY
ps.cpu_time DESC
LIMIT 10;这条SQL语句会显示占用CPU时间最多的10个会话，并显示它们的详细信息。注意，要执行这条语句，你需要开启Performance Schema的thread_summary_global_by_event_name消费者。
第三幕：监控锁信息
锁是数据库并发控制的重要机制，但如果锁使用不当，就会导致锁冲突，阻塞事务，影响性能。sys schema提供了多个视图来监控锁信息，其中最常用的是sys.innodb_lock_waits。
SELECT * FROM sys.innodb_lock_waits;这个视图会显示所有正在等待锁的事务的信息，包括：
waiting_trx_id： 正在等待锁的事务ID。waiting_lock_id： 正在等待的锁ID。waiting_pid： 正在等待的进程ID。blocking_trx_id： 持有锁的事务ID。blocking_lock_id： 持有的锁ID。blocking_pid： 持有锁的进程ID。lock_mode： 锁模式（比如X表示排他锁，S表示共享锁）。lock_type： 锁类型（比如TABLE表示表锁，RECORD表示行锁）。table_schema： 表的schema。table_name： 表名。waiting_query： 正在等待锁的事务执行的SQL语句。blocking_query： 持有锁的事务执行的SQL语句。wait_started： 等待开始的时间。wait_age： 等待的时间。
通过这个视图，我们可以快速找到导致锁冲突的事务，并分析原因。
实战案例：解决死锁问题
假设我们发现sys.innodb_lock_waits视图中有死锁信息，两个事务互相等待对方释放锁，导致程序卡死。
SELECT * FROM sys.innodb_lock_waits;假设我们看到以下信息：
| waiting_trx_id | blocking_trx_id | waiting_query | blocking_query | table_name |
|---|---|---|---|---|
| 123 | 456 | UPDATE products SET price = 10 WHERE id = 1; | UPDATE orders SET status = ‘Shipped’ WHERE id = 1; | products |
| 456 | 123 | UPDATE products SET stock = 100 WHERE id = 1; | UPDATE products SET price = 10 WHERE id = 1; | products |
从这个信息可以看出，事务123正在等待事务456释放products表的锁，而事务456也在等待事务123释放products表的锁，这就形成了一个死锁。
解决死锁的方法有很多，比如：
- 调整SQL语句的执行顺序： 尽量让所有事务按照相同的顺序访问资源，避免循环等待。
- 使用更细粒度的锁： 尽量使用行锁代替表锁，减少锁冲突的可能性。
- 设置锁等待超时时间： 如果事务等待锁的时间超过了超时时间，就自动回滚，避免长时间阻塞。
- 手动kill掉其中一个事务： 这是最简单粗暴的方法，但可能会导致数据不一致，需要谨慎使用。
在这个例子中，我们可以尝试调整SQL语句的执行顺序，让两个事务先更新orders表，再更新products表。或者，我们可以手动kill掉其中一个事务，让另一个事务继续执行。
进阶技巧：找到持有锁时间最长的事务
sys.innodb_lock_waits只能看到正在等待锁的事务，无法看到持有锁时间最长的事务。要找到持有锁时间最长的事务，我们需要借助Performance Schema的events_waits_current表。
SELECT
ew.object_schema,
ew.object_name,
ew.index_name,
ew.event_name,
ew.source,
ew.timer_wait,
ew.lock_time,
p.user,
p.host,
p.db,
p.command,
p.state,
p.time,
p.info
FROM
performance_schema.events_waits_current ew
JOIN
performance_schema.threads t ON ew.thread_id = t.thread_id
JOIN
sys.processlist p ON t.processlist_id = p.id
WHERE
ew.event_name LIKE 'wait/lock/table/innodb%'
ORDER BY
ew.timer_wait DESC
LIMIT 10;这条SQL语句会显示持有InnoDB表锁时间最长的10个事务，并显示它们的详细信息。通过分析这些事务，我们可以找到可能导致锁冲突的原因。
第四幕：其他实用视图
除了sys.processlist和sys.innodb_lock_waits，sys schema还提供了很多其他实用的视图，可以帮助我们监控数据库的性能：
sys.schema_table_statistics： 显示每个表的统计信息，包括行数、大小、索引大小等等。sys.schema_table_statistics_with_buffer： 类似sys.schema_table_statistics，但包含了缓存中的数据。sys.statement_analysis： 显示SQL语句的统计信息，包括执行次数、平均执行时间、最大执行时间等等。sys.io_global_by_wait_by_bytes： 显示I/O等待的信息，可以帮助我们找到I/O瓶颈。
这些视图都提供了丰富的信息，可以帮助我们全面了解数据库的运行状况。
总结：sys schema，数据库的贴身管家
sys schema就像MySQL的贴身管家，它提供了丰富的系统信息，可以帮助我们监控活跃会话、锁信息、表统计信息、SQL语句统计信息等等。通过使用sys schema，我们可以及时发现和解决数据库性能问题，保证数据库的高效运行。
希望今天的讲座能对大家有所帮助。记住，sys schema不是万能的，它只是一个工具。要真正掌握数据库性能优化的技巧，还需要不断学习和实践。
感谢大家的观看，下次再见！