在安装mysql 5.7.9后,进入data目录下我们会明显发现与5.6的不同，没有了test schema，并且，新增了一个sys schema
[root@liukaiy data]# pwd
/usr/local/mysql/data
[root@liukaiy data]# ls
auto.cnf ib_logfile0 mysql performance_schema
ib_buffer_pool ib_logfile1 mysqld_safe.pid sys
ibdata1 ibtmp1 mysql_upgrade_info
test schema我们就没必要研究了，没意义。现在来谈一下sys schema
mysql5.7.7开始提供 SYS Schema，从SYS database中，可以获取mem page、io 、latch/mutex/lock等各种性能数据，方便做peformance turning 和 troubleshooting。比如可以方便获取2个sql发生 lock block，用户占用的资源等信息。
感受就是，越来越像oracle了。
=============
登录mysql，查看数据库中的schema
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
|
sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> use sys;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
使用show tables可以看到sys schema中的object。sys schema中包含很多描述数据库性能的视图，大部分视图是成对的，其中一个成员没有x$前缀，而另一个是有x$前缀的，两者的内容完全相同，区别是没有x$前缀的视图提供更人性化输出。
查看会话的状态
processlist和session视图
sys schema的
processlist和session视图提供了比show processlist和
INFORMATION_SCHEMA PROCESSLIST 更全面的信息；
session视图去掉了后台线程的信息，只保留了用户会话
mysql> select * from session limit 1\G
*************************** 1. row ***************************
thd_id: 41
conn_id: 15
user:
root@localhost
db: sys
command: Sleep
state: NULL
time: 5833
current_statement: NULL
statement_latency: NULL
progress: NULL
lock_latency: 671.00 us
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 1
tmp_disk_tables: 1
full_scan: YES
last_statement: select * from sys.schema_table_lock_waits
last_statement_latency: 1.21 ms
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 22981
program_name: mysql
1 row in set (0.11 sec)
查看哪些用户占用了大量的资源
mysql> show tables like 'user%';
+-----------------------------------+
| Tables_in_sys (user%) |
+-----------------------------------+
| user_summary |
| user_summary_by_file_io |
| user_summary_by_file_io_type |
| user_summary_by_stages |
| user_summary_by_statement_latency |
| user_summary_by_statement_type |
+-----------------------------------+
6 rows in set (0.00 sec)
mysql>
select * from user_summary\G
*************************** 1. row ***************************
user: root
statements: 3331 //用户执行的SQL语句总数量
statement_latency: 2.86 s //该用户执行SQL总的等待时间，即累计的SQL执行时间
statement_avg_latency: 857.30 us //单次SQL的平均执行时间
table_scans: 67 //全表扫描次数
file_ios: 23626 //io次数
file_io_latency: 1.72 s //io消耗的总时间
current_connections: 1 //该用户的当前连接会话数
total_connections: 9 //该用户连接会话总数
unique_hosts: 1 //用户发起的连接中唯一主机的数量，即从几个主机发起过数据库连接
current_memory: 0 bytes //当前被分配的内存
total_memory_allocated: 0 bytes //总共被分配的内存
*************************** 2. row ***************************
user: background
statements: 0
statement_latency: 0 ps
statement_avg_latency: 0 ps
table_scans: 0
file_ios: 2127
file_io_latency: 161.74 ms
current_connections: 26
total_connections: 30
unique_hosts: 0
current_memory: 0 bytes
total_memory_allocated: 0 bytes
2 rows in set (0.02 sec)
通过上述查询，可以简单看到每个用户占用的连接、内存、io等资源
如果想要进一步查询某项指标的话，可以查看对应的视图，如
mysql>
select * from user_summary_by_statement_latency\G
*************************** 1. row ***************************
user: root
total: 3411
total_latency: 2.87 s
max_latency: 648.56 ms
lock_latency: 515.50 ms
rows_sent: 1477
rows_examined: 23042
rows_affected: 408
full_scans: 68
*************************** 2. row ***************************
user: background
total: 0
total_latency: 0 ps
max_latency: 0 ps
lock_latency: 0 ps
rows_sent: 0
rows_examined: 0
rows_affected: 0
full_scans: 0
2 rows in set (0.01 sec)
这个视图概述了每个用户执行SQL的大体统计信息，本人简单分析如下：
user 用户名
total 该用户总共执行的SQl语句的数量
total_latency 执行SQL总共的等待时间
max_latency 单次执行的最长等待时间
lock_latency 锁等待累计时间
rows_sent 该用户执行SQL累积返回的行数
rows_examined 未执行用户SQL存储引擎读取的总行数
rows_affected 被用户SQL语句影响的总行数
full_scans 该用户执行的SQL中造成全表扫描的总次数
如果想了解各个用户占用的IO带宽，可查询如下视图
mysql>
select * from user_summary_by_file_io\G
*************************** 1. row ***************************
user: root
ios: 23655
io_latency: 1.72 s
*************************** 2. row ***************************
user: background
ios: 2144
io_latency: 162.43 ms
2 rows in set (0.01 sec)
这个视图相对比较简单，列举的是每个用户下的io事件次数，以及io等待的累计时间
除此之外，我们还可以根据
waits_by_user_by_latency视图查看每个用户的等待事件
mysql> desc waits_by_user_by_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| user | varchar(32) | YES | | NULL | |
| event | varchar(128) | NO | | NULL | |
| total | bigint(20) unsigned | NO | | NULL | |
| total_latency | text | YES | | NULL | |
| avg_latency | text | YES | | NULL | |
| max_latency | text | YES | | NULL | |
+---------------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
通过sys schema查询innodb的锁信息
innodb_lock_waits
mysql 5.7.7开始，sys schema中提供了
innodb_lock_waits视图
，通过这个视图，我们可以更直观清晰地查看当前事务间的阻塞关系。在此列举几个受关注的列，如下：
waiting_trx_id 等待事务的ID
waiting_trx_age 等待的时间
waiting_query 等待锁资源的SQL
waiting_lock_mode 请求锁的模式
blocking_trx_id 阻塞事务的ID
blocking_trx_age 阻塞事务被执行的时间
blocking_query 阻塞事务正在执行的SQL
blocking_lock_mode 阻塞的锁模式
locked_table 被加锁的表
locked_index 被加锁的索引
接下来简单模拟两个事务间的阻塞
1)甲会话中执行
mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | liu |
| 2 | kai |
+----+------+
2 rows in set (0.00 sec)
mysql> select * from t1 where id=2 for update;
+----+------+
| id | name |
+----+------+
| 2 | kai |
+----+------+
1 row in set (0.00 sec)
2)乙会话执行如下SQL
mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from liu.t1 where id=2;
3)丙会话中查询事务间阻塞
mysql> select waiting_trx_id,waiting_trx_age,waiting_query,waiting_lock_mode,blocking_trx_id,blocking_trx_age,blocking_query,blocking_lock_mode,locked_table,locked_index from sys.innodb_lock_waits\G
*************************** 1. row ***************************
waiting_trx_id: 1439
waiting_trx_age: 00:07:53
waiting_query: delete from liu.t1 where id=2
waiting_lock_mode: X
blocking_trx_id: 1438
blocking_trx_age: 00:08:11
blocking_query: NULL
blocking_lock_mode: X
locked_table: `liu`.`t1`
locked_index: PRIMARY
1 row in set (0.00 sec)
我们发现，之前需要在information_schema中连接多个视图的查询，现在只需要查询这一个视图就能清晰地获取我们需要的信息了（以前只能通过information_schema中的，InnoDB_trx、INNODB_LOCKS、INNODB_LOCK_WAITS查询）
schema_table_lock_waits
5.7.9中新增的视图，通过这个视图可以查询阻塞会话的metadata lock信息
mysql> desc schema_table_lock_waits;
+------------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------------+---------------------+------+-----+---------+-------+
| object_schema | varchar(64) | YES | | NULL | |
| object_name | varchar(64) | YES | | NULL | |
| waiting_thread_id | bigint(20) unsigned | NO | | NULL | |
| waiting_pid | bigint(20) unsigned | YES | | NULL | |
| waiting_account | text | YES | | NULL | |
| waiting_lock_type | varchar(32) | NO | | NULL | |
| waiting_lock_duration | varchar(32) | NO | | NULL | |
| waiting_query | longtext | YES | | NULL | |
| waiting_query_secs | bigint(20) | YES | | NULL | |
| waiting_query_rows_affected | bigint(20) unsigned | YES | | NULL | |
| waiting_query_rows_examined | bigint(20) unsigned | YES | | NULL | |
| blocking_thread_id | bigint(20) unsigned | NO | | NULL | |
| blocking_pid | bigint(20) unsigned | YES | | NULL | |
| blocking_account | text | YES | | NULL | |
| blocking_lock_type | varchar(32) | NO | | NULL | |
| blocking_lock_duration | varchar(32) | NO | | NULL | |
| sql_kill_blocking_query | varchar(31) | YES | | NULL | |
| sql_kill_blocking_connection | varchar(25) | YES | | NULL | |
+------------------------------+---------------------+------+-----+---------+-------+
18 rows in set (0.00 sec)
每行记录描述的意义一览便知，本文不一一赘述
MySQL5.7 新特性诸多，单单是sys schema这一个特性又岂是三言两语能说明白的，本文只是略微列举一二，希望能引起各位看客朋友们探索的欲望。
