428
在Oracle專家眼中，MySQL sys Schema是怎樣一種存在?
作者介紹
楊建榮，DBAplus社群聯合發起人。現就職於搜狐暢遊，Oracle ACE-A、YEP成員，超7年數據庫開發和運維經驗，擅長電信數據業務、數據庫遷移和性能調優。持Oracle 10G OCP，OCM，MySQL OCP認證，《Oracle DBA工作筆記》作者。
sys Schema的初衷
MySQL的數據字典經曆了幾個階段的演進，MySQL4.1 提供了information_schema 數據字典，一些基礎元數據可以通過SQL來查詢得到。
MySQL5.5 提供了performance_schema 性能引擎，可以通過參數performance_schema來開啟/關閉，說實話，看起來是有些難度。
MySQL5.7 提供了 sys Schema，這個新特性包含了一係列的存儲過程、自定義函數以及視圖來幫助我們快速的了解係統的元數據信息，當然自MySQL 5.7.7推出以來，讓很多MySQL DBA不大適應，而我看到這個sys庫的時候，第一感覺是越發和Oracle像了，不是裏麵的內容像，而是很多設計的方式越來相似。所以按照這種方式，我感覺離AWR這樣的工具推出也不遠了。
對於實時全麵的抓取性能信息，MySQL依舊還在不斷進步的路上。因為開源，所以有很多非常不錯的工具，產品推出。myawr算是其中的一個，現在看來當初的設計方式和現在sys庫很有相似之處，感興趣的可以自行搜索查看。
sys Schema的借鑒意義
對於sys Schema，我覺得對DBA來說，有幾個地方值得借鑒。
-
原本需要結合information_schema，performance_schema查詢的方式，現在有了視圖的方式，把一些優化和診斷信息信息通過視圖的方式匯總起來，顯示更加直觀
-
sys Schema的有些功能在早期版本可能無從查起，或者很難查詢，現在這些因為新版本的功能提煉都做出來了
-
如果想好好掌握這些視圖的內涵，可以隨時查看表的關聯關係，對於理解MySQL的運行原理和問題的分析大有幫助，當然這個地方隻能點到為止。
按照這種情況，沒準以後會直接把sys完全獨立出來，替代information_schema，performance_schema，沒準以後還會出更豐富的功能，類似Oracle中的免費的statspack，還有閉源的AWR，實時的性能數據抓取，自動性能分析和診斷，自動優化任務等，當然隻是我的猜想，根據我的認知，Oracle裏也是這麼走過來的。
對於sys Schema的學習，我是基於5.7.13-6這個版本，是用Oracle的眼光來學習的，準備好了嗎，老司機開車了。
化繁為簡，sys下的對象分布情況
sys下的對象分布其實信息量很大，除了我們關心的視圖和表以外，還有函數，存儲過程和觸發器。這些信息可以通過sys下的視圖schema_object_overview來查看。
sys下唯一的表
如果你觀察仔細其實會發現裏麵的table隻有一個，那就是sys_config，使用命令show tables顯示出來的除了這個表都是視圖。
這個視圖有什麼特別之處呢。
可以看到裏麵是一個基礎參數的設置，比如一些範圍，基數的設置。而且值得一提的是這個表裏設置了幾個觸發器，對這個表的DML操作都會觸發裏麵的數據級聯變化。
sys_config的作用其實和Oracle AWR裏麵的設置非常相似，Oracle中是使用dba_hist_wr_control來得到。
然後我們繼續查看，還是使用show tables來看，會看到整個sys下的表/視圖有101個，其中x$開頭的對象有48個，所以簡單換算一下，裏麵的表/視圖有53個。
x$視圖
x$的視圖是什麼意思，通過Oracle的角度來看，就很容易理解，意思是相通的。在Oracle中，數據字典分為兩種類型，一類是數據字典表，像dba_tables這樣的，基表都是tab$這種的表，數據是存放在係統表空間system下的，這些信息在MySQL中就有些類似information_schema下的數據字典，而另外一類數據字典是動態性能視圖，Oracle是以v$開頭的，比如v$session，它的基表是x$開頭的“內存表”，在MySQL sys中也是類似的意思，隻是這些信息MySQL都毫無保留的開放出來了。按照官方的說法，x$的信息是沒有經過格式化的，比如下麵的兩個視圖對比。
x$的視圖的定義如下：
可以看到數據類型也有一些差別。如果是時間字段，在x$視圖中可能精度是picosecond（皮秒，萬億分之一秒），而在普通視圖中，就會格式化為秒。
sys下的session視圖
我們抽取一個視圖來看，就session吧，輸出和show processlist命令如出一轍，我們來看看它的實現。使用show create view session可以看到引用的基表為`sys`.`processlist`，我們繼續查看sys.processlist，可以發現它的基表是performance_schema下的`events_waits_current`，`events_stages_current`，`events_statements_current`，`events_transactions_current`，`session_connect_attrs`和sys下的基表`x$memory_by_thread_by_current_bytes` ，通過引用的這些視圖其實可以看到也分了很多的層麵。
而在Oracle中，因為主要是多進程多線程的實現方式（windows平台是單進程多線程），所以會有獨立的v$session和v$process兩個視圖，兩者通過內存地址的方式映射，所以在專用服務器模式下，就可以通過進程找到會話，或者通過會話找到進程，對於排查性能問題大有裨益。
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這三個方麵，提升空間還很大。
等待模型在Oracle中有一種流行的診斷方法論OWI，也就是Oracle Wait Interface。
OWI的信息會讓調優變得更理性，更符合應用的場景。關於等待事件，Oracle的不同版本中也有著很顯著的變化。
最初Oracle 7.0中有104個等待事件，8.0中有140多個等待事件，Oracle 8i中有220多個等待事件，9i中有400多個等待事件，10g中有800多個等待事件，11g有1 100多個。隨著等待事件的逐步完善，也能夠反映出對於問題的診斷粒度越來越細化。
當然sys的使用其實還是比較靈活的，在5.6及以上版本都可以，是完全獨立的。和Oracle裏麵的statspack，AWR非常相似。
裏麵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
查看innodb_trx
麵對這些情況，該怎麼處理，比如要殺掉會話，可能還會有些模棱兩可。
我們來看看使用innodb_lock_waits的結果。這個過程語句都給你提供好了，隻有1行信息，就是告訴你產生了阻塞，現在可以使用kill的方式終止會話，kill語句都給你提供好了。
當然默認事務還是有一個超時的設置，可以看到確實是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就給你包裝好了，直接用即可。以下輸出略微做了調整。
如果一個表的索引沒有使用到，以前pt工具也可以做一些分析，現在查個視圖就搞定了。當然索引的部分，一方麵和采樣率也有關係，不是一個絕對的結果。查看schema_unused_indexes的結果如下：
如果要查看那些表走了全表掃描，性能情況，可以查看schema_tables_with_full_table_scans，查詢結果如下，如果數據量本身很大，這個結果就會被放大，值得關注。
如果查看一些冗餘的索引，可以參考 schema_redundant_indexes，刪除的SQL語句都給你提供好了。
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
這部分內容對於分析語句的性能還是尤其有用的。
比如查看語句的排序情況，資源使用情況，延時等都會提供出來。
在這裏SQL語句做了刪減，不過大體能看出語句的信息，執行次數和 延時等都可以看到。
對於SQL語句中生成的臨時表可以查看statements_with_temp_tables ，比如某一個語句生成的臨時表情況，都做了統計。
最後來說說sys的備份和重建工作，如果查看sys的版本，可以使用視圖version來得到。可見是把它當做一個獨立的組件一樣來維護的。
如果要導出，可以使用 mysqlpump sys > sys_dump.sql 或者mysqldump --databases --routines sys > sys_dump.sql來得到sys的創建語句，如果需要重建則更簡單 mysql<sys_dump.sql 即可。
原文發布時間為：2016-12-30
本文來自雲棲社區合作夥伴DBAplus
最後更新：2017-05-13 08:43:02