用Oracle的眼光来学习MySQL 5.7的sys（下）(r11笔记第25天)
用Oracle的眼光来学习MySQL 5.7的sys（下）(r11笔记第25天)
昨天写了篇分析sys的文章，用Oracle的眼光来学习MySQL 5.7的sys（上）(r11笔记第24天) 收到了一些朋友的反馈，还不错，今天继续努力，再整理一篇。
sys的借鉴意义
今天还和同事偶然聊起sys schema的事情，我觉得有几个地方要值得借鉴。
1）原本需要结合information_schema,performance_schema查询的方式，现在有了视图的方式，显示更加直观
2）sys schema的有些功能在早期版本可能无从查起，或者很难查询，现在这些因为新版本的功能提炼都做出来了
3）如果想好好掌握这些视图的内涵，可以随时查看表的关联关系，对于理解MySQL的运行原理和问题的分析大有帮助，当然这个地方只能点到为止。
按照这种情况，没准以后会直接用sys替代information_schema,performance_schema，没准以后还会出更丰富的功能，类似Oracle中的免费的statspack,还有闭源的AWR,实时的性能数据抓取，自动性能分析和诊断，自动优化任务等，当然纯属猜想，Oracle里也都是这么走过来的。
对于这些视图，我按照名字和类别简单归类
sys下的视图分类
sys下的视图分了哪些层面呢。我简单来总结一下，大体分为一下几个层面
host_summary,这个是服务器层面的，比如里面的视图host_summary_by_file_io
user_summary,这个是用户层级的，比如里面的视图user_summary_by_file_io
innodb，这个是innodb层面的，比如innodb_buffer_stats_by_schema
io，这个是I/O层的统计，比如视图 io_global_by_file_by_bytes
memory，关于内存的使用情况，比如视图memory_by_host_by_current_bytes
schema，关于schema级别的统计信息，比如schema_table_lock_waits
session，关于会话级别的，这个视图少一些，就两个，session和session_ssl_status
statement，关于语句级别的，比如statements_with_errors_or_warnings
wait，关于等待的，这个还是处于起步阶段，等待模型有待完善，目前只有基于io/file, lock/table, io/table这三个方面,提升空间还很大。
其实里面innodb,schema,statement这三部分是格外需要关注的。
sys下的innodb视图
比如innodb部分的视图innodb_lock_waits
我们做个小测试来说明一下。我们开启两个会话。
会话1： start transaction; update test set id=100;
会话2： update test set id=102;
这个时候如果在没有sys的情况下，我们需要查看information_schema.innodb_locks和innodb_trx,有的时候还会查看show engine innodb status来得到一些信息佐证。
查看Innodb_locks
> select *from information_schema.innodb_locks\G
*************************** 1. row ***************************
lock_id: 961672:356:3:2
lock_trx_id: 961672
lock_mode: X
lock_type: RECORD
lock_table: `test`.`test`
lock_index: GEN_CLUST_INDEX
lock_space: 356
lock_page: 3
lock_rec: 2
lock_data: 0x00000011D9EB
*************************** 2. row ***************************
lock_id: 961671:356:3:2
lock_trx_id: 961671
lock_mode: X
lock_type: RECORD
lock_table: `test`.`test`
lock_index: GEN_CLUST_INDEX
lock_space: 356
lock_page: 3
lock_rec: 2
lock_data: 0x00000011D9EB
2 rows in set (0.00 sec)查看innodb_trx
> select *from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 961671
trx_state: RUNNING
trx_started: 2016-12-26 22:25:52
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 1149233
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)面对这些情况，该怎么处理，比如要杀掉会话，可能还会有些模棱两可。我们来看看使用innodb_lock_waits的结果。这个过程语句都给你提供好了，只有1行信息，就是告诉你产生了阻塞，现在可以使用kill的方式终止会话，kill语句都给你提供好了。
> select * from innodb_lock_waits\G
*************************** 1. row ***************************
wait_started: 2016-12-26 22:28:24
wait_age: 00:01:38
wait_age_secs: 98
locked_table: `test`.`test`
locked_index: GEN_CLUST_INDEX
locked_type: RECORD
waiting_trx_id: 961672
waiting_trx_started: 2016-12-26 22:28:24
waiting_trx_age: 00:01:38
waiting_trx_rows_locked: 2
waiting_trx_rows_modified: 0
waiting_pid: 1149284
waiting_query: update test set id=102
waiting_lock_id: 961672:356:3:2
waiting_lock_mode: X
blocking_trx_id: 961671
blocking_pid: 1149233
blocking_query: NULL
blocking_lock_id: 961671:356:3:2
blocking_lock_mode: X
blocking_trx_started: 2016-12-26 22:25:52
blocking_trx_age: 00:04:10
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 1149233
sql_kill_blocking_connection: KILL 1149233
1 row in set (0.01 sec) 当然默认事务还是有一个超时的设置，可以看到确实是update test set id=102阻塞了。已经因为超时取消。
> update test set id=102;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
不过innodb相关的视图确实不多，只有3个，都蛮实用的。
sys下的schema视图
我们继续看看schema层面的视图,这部分内容就很实用了。
schema_auto_increment_columns
schema_index_statistics
schema_object_overview
schema_redundant_indexes
schema_table_lock_waits
schema_table_statistics
schema_table_statistics_with_buffer
schema_tables_with_full_table_scans
schema_unused_indexes如果要查看一个列值溢出的情况，比如是否列的自增值会超出数据类型的限制，这个问题对很多MySQL DBA一直以来都是一个挑战，视图schema_auto_increment_columns就给你包装好了，直接用即可。以下输出略微做了调整。
table_name | column_name |column_type | max_value | auto_increment
------------------+-------------+------------+---------------+-------------
ta_newyear2_back | id |int(11) | 2147483647 |9945076 |
tb_activate_code | id |int(11) | 2147483647 |1851387 |
sys_oper_log | id |int(11) | 2147483647 |126867 |如果一个表的索引没有使用到，以前pt工具也可以做一些分析，现在查个视图就搞定了。当然索引的部分，一方面和采样率也有关系，不是一个绝对的结果。查看schema_unused_indexes的结果如下：
+-----------------+--------------------------------+-----------------------
| object_schema | object_name | index_name
+-----------------+--------------------------------+-----------------------
| activity_log | bfgifts_logininfo | index_cn
| activity_log | bf_sendq_reglog | in_bf_sendq_reglog
| activity_log | db_singlecharge_records | idx_db_singlecharge_re
| activity_log | FFO_RECHARGE_CONS |如果要查看那些表走了全表扫描，性能情况，可以查看schema_tables_with_full_table_scans，查询结果如下，如果数据量本身很大，这个结果就会被放大，值得关注。
+-----------------+--------------------+-------------------+-----------+
| object_schema | object_name | rows_full_scanned | latency |
+-----------------+--------------------+-------------------+-----------+
| mobile_billing | tb_activate_code | 133704990876 | 20.74 h |
| mobile_billing | tb_appkey_config | 56067246 | 5.32 m |
| mobile_billing | tb_goods | 11323673 | 1.20 m |
| mobile_billing | tb_app | 11104405 | 28.86 s |如果查看一些冗余的索引，可以参考 schema_redundant_indexes，删除的SQL语句都给你提供好了。
*************************** 9. row ***************************
table_schema: zzb_test
table_name: tes_activate_list
redundant_index_name: INDEX_SMS_ID
redundant_index_columns: SMS_ID
redundant_index_non_unique: 0
dominant_index_name: PRIMARY
dominant_index_columns: SMS_ID
dominant_index_non_unique: 0
subpart_exists: 0
sql_drop_index: ALTER TABLE `zzb_test`.`sms_activate_list` DROP INDEX `INDEX_SMS_ID`sys下的statement视图
接下来是statement层面的视图，大体有下面的一些
statement_analysis
statements_with_errors_or_warnings
statements_with_full_table_scans
statements_with_runtimes_in_95th_percentile
statements_with_sorting
statements_with_temp_tables
这部分内容对于分析语句的性能还是尤其有用的。
比如查看语句的排序情况，资源使用情况，延时等都会提供出来。
> select *from statements_with_sorting;
+-------------------------------------------------------------------+----------------+------------+---------------
| query | db | exec_count | total_latency
+-------------------------------------------------------------------+----------------+------------+---------------
| SELECT * FROM `tb_goods` WHERE ... TION` ASC , `GOODS_PRICE` ASC | mobile_billing | 26334 | 29.84 s
| SELECT `id` , `appname` , `app ... ? ORDER BY `create_date` DESC | mobile_billing | 13664 | 9.04 s
| SELECT `channel0_` . `id` AS ` ... annel0_` . `create_date` DESC | mobile_billing | 6832 | 4.83 s
在这里SQL语句做了删减，不过大体能看出语句的信息，执行次数和 延时等都可以看到。
对于SQL语句中生成的临时表可以查看statements_with_temp_tables ,比如某一个语句生成的临时表情况，都做了统计。
db | exec_count | total_latency | memory_tmp_tables | disk_tmp_tables
--------+------------+---------------+-------------------+-----------------
test | 27 | 2.36 s | 3442 | 263
backend | 18 | 1.06 s | 1314 |
腾讯云开发者
