MySQL運維的一款利器sys schema

MySQL運維中有很多的工具，就好比你進了一個雜貨鋪，各種運維中的小技巧幾乎都有一些小工具， 走一圈發現真是琳琅滿目，可見MySQL充分享受了開源社群的紅利。

而比較特別的一點是MySQL社群中，官方提供的工具在以前不會成為市面上的拳頭產品，有些甚至維護一段之後就沒有深入維護了。所以走一圈發現，MySQL很多實用成熟的工具都來自於社群，而官方推出的一些工具不是因為企業版的限制就是功能的不成熟而被有限使用。

當然不一而論，在早些年我可以說這些話，但是在5.7開始，你會明顯發現官方的投入已經很大了，官方推出的社群版迭代很快，已經早早超越了兩個流行的分支Percona和MariaDB,尤其是MGR的推出，算是讓MySQL在這方面有了不少的亮點和話語權。

我們繼續回到工具，還是工具，工具是DBA的一把瑞士軍刀，其實對我們來說，我們也不希望有一大把的小工具，如果有太多的工具，太多的瑞士軍刀，我們的行李箱就會很沉重，哪些都需要，哪些都要單獨安裝，所以我們有時候希望做減法。如果官方有而且做得不錯，那毫無疑問是我們首選的方案了。

所以在此我要推薦MySQL 的sys schema。這是在5.7推出的一個特性。算是對以前的performance_schema和information_schema的一個抽象層。

有的同學可能會問，這都是MySQL 5.7的特性了，我們的很多環境使用的依舊是5.5,5.6，這個特性對我們來說有什麼意義，如果只是這樣想，只能說明你只是意識到了這個問題而已，但是對於解決問題沒有實質性建議。

我們來簡單說下sys schema裡面的一些小細節。

之前發表在DBAplus社群的一篇文章可供參考：

在Oracle專家眼中，MySQL sys Schema是怎樣一種存在?

我對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這三個方面，提升空間還很大。

所以這確實是一個寶貝，讓我們對原本難以入手的效能問題有了一些很清晰的定位方式。

比如我很看好的一個功能是自增列的溢位問題檢測，如果能夠提前發現，那麼對於運維工作會從被動變為主動。

在MySQL 5.7中我會這樣來用：

select *from schema_auto_increment_columns;

如果自增列值快要溢位（我們可以設定一個閾值，比如90%之類的），我們可以通過檢視的方式來很容易定位到。

如果要檢視這個檢視的具體實現就會發現邏輯遠比我們想象的複雜，檢視的定義如下：

select `information_schema`.`COLUMNS`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`COLUMNS`.`TABLE_NAME` AS `table_name`,`information_schema`.`COLUMNS`.`COLUMN_NAME` AS `column_name`,`information_schema`.`COLUMNS`.`DATA_TYPE` AS `data_type`,`information_schema`.`COLUMNS`.`COLUMN_TYPE` AS `column_type`,(locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) = 0) AS `is_signed`,(locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0) AS `is_unsigned`,((case `information_schema`.`COLUMNS`.`DATA_TYPE` when 'tinyint' then 255 when 'smallint' then 65535 when 'mediumint' then 16777215 when 'int' then 4294967295 when 'bigint' then 18446744073709551615 end) >> if((locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0),0,1)) AS `max_value`,`information_schema`.`TABLES`.`AUTO_INCREMENT` AS `auto_increment`,(`information_schema`.`TABLES`.`AUTO_INCREMENT` / ((case `information_schema`.`COLUMNS`.`DATA_TYPE` when 'tinyint' then 255 when 'smallint' then 65535 when 'mediumint' then 16777215 when 'int' then 4294967295 when 'bigint' then 18446744073709551615 end) >> if((locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0),0,1))) AS `auto_increment_ratio`

from (`INFORMATION_SCHEMA`.`COLUMNS`

join `INFORMATION_SCHEMA`.`TABLES`

on(((`information_schema`.`COLUMNS`.`TABLE_SCHEMA` = `information_schema`.`TABLES`.`TABLE_SCHEMA`)

and (`information_schema`.`COLUMNS`.`TABLE_NAME` = `information_schema`.`TABLES`.`TABLE_NAME`))))

where ((`information_schema`.`COLUMNS`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','performance_schema')) and (`information_schema`.`TABLES`.`TABLE_TYPE` = 'BASE TABLE') and (`information_schema`.`COLUMNS`.`EXTRA` = 'auto_increment'))

order by (`information_schema`.`TABLES`.`AUTO_INCREMENT` / ((case `information_schema`.`COLUMNS`.`DATA_TYPE` when 'tinyint' then 255 when 'smallint' then 65535 when 'mediumint' then 16777215 when 'int' then 4294967295 when 'bigint' then 18446744073709551615 end) >> if((locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0),0,1))) desc,((case `information_schema`.`COLUMNS`.`DATA_TYPE` when 'tinyint' then 255 when 'smallint' then 65535 when 'mediumint' then 16777215 when 'int' then 4294967295 when 'bigint' then 18446744073709551615 end) >> if((locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0),0,1))

裡面引用了performance_schema和information_schema的資料字典。

所以有一個很重要的問題，5.7的檢視內容是否可以照搬到5.5來用呢。

顯然不是一個完全肯定的答案，因為5.7中的information_schema中的表有72個，而5.5中只有37個，大部分的差異是在InnoDB的資料字典上。

但是巧的是對於檢視schema_auto_increment_columns的實現來說，5.5的邏輯已經足夠了。

所以儘管我們使用的5.5的版本，我們依舊可以享用這個5.7的特性帶來的福利，當然不是簡單的拿來主義，對於這列操作，我更希望是放在從庫來執行，提前測試避免不必要的麻煩。所以很多問題都能夠有定位的方法，對於解決問題就會如虎添翼。

所以順著這個思路走下去，你會發現有很多工作待發掘。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2152313/，如需轉載，請註明出處，否則將追究法律責任。