昨天寫了篇分析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的結果如下：

如果要檢視那些表走了全表掃描，效能情況，可以檢視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 |

用Oracle的眼光來學習MySQL 5.7的sys（下）(r11筆記第25天)

相關文章