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

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

2 rows in set (0.00 sec)

檢視innodb_trx

> select *from information_schema.innodb_trx\G

                    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

                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 |