MySQL-系統庫講解

語言: CN / TW / HK

1.MySQL中的系統庫

1.1.系統庫簡介

MySQL有幾個系統資料庫,這幾個資料庫包含了MySQL伺服器執行過程中所需的一些資訊以及一些執行狀態資訊,我們現在稍微瞭解一下。

image.png

performance_schema

這個資料庫裡主要儲存MySQL伺服器執行過程中的一些狀態資訊,算是對MySQL伺服器的一個性能監控。包括統計最近執行了哪些語句,在執行過程的每個階段都花費了多長時間,記憶體的使用情況等等資訊。

information_schema

這個資料庫儲存著MySQL伺服器維護的所有其他資料庫的資訊,比如有哪些表、哪些檢視、哪些觸發器、哪些列、哪些索引。這些是一些描述性資訊,稱之為元資料。

sys

這個資料庫通過檢視的形式把information_schema和performance_schema結合起來,讓程式設計師可以更方便的瞭解MySQL伺服器的一些效能資訊。

mysql

主要儲存了MySQL的使用者賬戶和許可權資訊,還有一些儲存過程、事件的定義資訊,一些執行過程中產生的日誌資訊,一些幫助資訊以及時區資訊等。

1.2.performance_schema

1.2.1.什麼是performance_schema

MySQL的performance_schema 是執行在較低級別的用於監控MySQL Server執行過程中的資源消耗、資源等待等情況的一個功能特性,它具有以下特點。

執行在較低級別:採集的東西相對比較底層,比如磁碟檔案、表I/O、表鎖等等。

• performance_schema提供了一種在資料庫執行時實時檢查Server內部執行情況的方法。performance_schema 資料庫中的表使用performance_schema儲存引擎。該資料庫主要關注資料庫執行過程中的效能相關資料。

• performance_schema通過監視Server的事件來實現監視其內部執行情況,“事件”就是在Server內部活動中所做的任何事情以及對應的時間消耗,利用這些資訊來判斷Server中的相關資源被消耗在哪裡。一般來說,事件可以是函式呼叫、作業系統的等待、SQL語句執行的階段[如SQL語句執行過程中的parsing(解析)或sorting(排序)階段]或者整個SQL語句的集合。採集事件可以方便地提供Server中的相關儲存引擎對磁碟檔案、表I/O、表鎖等資源的同步呼叫資訊。

• 當前活躍事件、歷史事件和事件摘要相關表中記錄的資訊,能提供某個事件的執行次數、使用時長,進而可用於分析與某個特定執行緒、特定物件(如mutex或file)相關聯的活動。

• performance_schema儲存引擎使用Server原始碼中的“檢測點”來實現事件資料的收集。對於performance_schema實現機制本身的程式碼沒有相關的單獨執行緒來檢測,這與其他功能(如複製或事件計劃程式)不同。

收集到的事件資料被儲存在performance_schema資料庫的表中。對於這些表可以使用SELECT語句查詢,也可以使用SQL語句更新performance_schema資料庫中的表記錄(比如動態修改performance_schema的以“setup_”開頭的配置表,但要注意,配置表的更改會立即生效,這會影響資料收集)。

• performance_schema的表中資料不會持久化儲存在磁碟中,而是儲存在記憶體中,一旦伺服器重啟,這些資料就會丟失(包括配置表在內的整個performance_schema下的所有資料)。

1.2.2.performance_schema使用

通過上面介紹,相信你對於什麼是performance_schema這個問題了解得更清晰了。下面開始介紹performance_schema的使用。

1.2.3.檢查當前資料庫版本是否支援

performance_schema被視為儲存引擎,如果該引擎可用,則應該在

INFORMATION_SCHEMA.ENGINES表或show engines語句的輸出中可以看到它的Support欄位值為YES,如下所示。

select * from INFORMATION_SCHEMA.ENGINES; show engines;

image.png

image.png

當我們看到performance_schema對應的Support欄位值為YES時,就表示當前的資料庫版本是支援performance_schema的。但確認了資料庫例項支援performance_schema儲存引擎就可以使用了嗎?NO,很遺憾,performance_schema在MySQL 5.6及之前的版本中預設沒有啟用,在MySQL 5.7及之後的版本中才修改為預設啟用。

mysqld啟動之後,通過如下語句檢視performance_schema啟用是否生效(值為ON表示performance_schema已初始化成功且可以使用了;值為OFF表示在啟用performance_schema時發生某些錯誤,可以檢視錯誤日誌進行排查)。

show variables like 'performance_schema';

image.png

(如果要顯式啟用或關閉 performance_schema ,則需要使用引數performance_schema=ON|OFF來設定,並在my.cnf中進行配置。注意 : 該引數為只讀引數,需要在例項啟動之前設定才生效)

現在,可以通過查詢INFORMATION_SCHEMA.TABLES表中與performance_schema儲存引擎相關的元資料,或者在performance_schema庫下使用show tables語句來了解其存在哪些表。

使用show tables語句來查詢有哪些performance_schema引擎表。

現在,我們知道了在當前版本中,performance_schema庫下一共有87個表,

image.png

image.png

那麼這些表都用於存放什麼資料呢?我們如何使用它們來查詢資料呢?先來看看這些表是如何分類的。

1.2.4.performance_schema表的分類

performance_schema庫下的表可以按照監視的不同維度進行分組,例如:按照不同的資料庫物件進行分組、按照不同的事件型別進行分組,或者按照事件型別分組之後,再進一步按照賬號、主機、程式、執行緒、使用者等進行細分。

下面介紹按照事件型別分組記錄效能事件資料的表。

• 語句事件記錄表:記錄語句事件資訊的表,包括:events_statements_current(當前語句事件表)、events_statements_history(歷史語句事件表)、events_statements_history_long(長語句歷史事件表)以及一些summary表(聚合後的摘要表)。其中,summary表還可以根據賬號(account)、主機(host)、程式(program)、執行緒(thread)、使用者(user)和全域性(global)再進行細分。

show tables like 'events_statement%';

image.pngimage.png

• 等待事件記錄表:與語句事件記錄表類似。

show tables like 'events_wait%';

image.png

• 階段事件記錄表:記錄語句執行階段事件的表,與語句事件記錄表類似。

show tables like 'events_stage%';

image.png

• 事務事件記錄表:記錄與事務相關的事件的表,與語句事件記錄表類似。

sql show tables like 'events_transaction%';

image.png

• 監視檔案系統層呼叫的表:

sql show tables like '%file%';

image.png

• 監視記憶體使用的表:

show tables like '%memory%';

image.png

• 動態對performance_schema進行配置的配置表:

show tables like '%setup%';

image.png

現在,我們已經大概知道了performance_schema中主要表的分類,但如何使用這些表來提供效能事件資料呢?

1.2.5.performance_schema簡單配置與使用

當資料庫初始化完成並啟動時,並非所有的instruments(在採集配置項的配置表中,每一項都有一個開關欄位,或為YES,或為NO)和consumers(與採集配置項類似,也有一個對應的事件型別儲存表配置項,為YES表示對應的表儲存效能資料,為NO表示對應的表不儲存效能資料)都啟用了,所以預設不會收集所有的事件。

可能你想檢測的事件並沒有開啟,需要進行設定。可以使用如下兩條語句開啟對應的instruments和consumers,我們以配置監測等待事件資料為例進行說明。

開啟等待事件的採集器配置項開關,需要修改setup_instruments 配置表中對應的採集器配置項。

update setup_instruments set enabled='yes',timed='yes' where name like 'wait%';

image.png

開啟等待事件的儲存表配置項開關,修改setup_consumers 配置表中對應的配置項。

update setup_consumers set enabled='yes' where name like 'wait%';

image.png

配置好之後,我們就可以檢視Server當前正在做什麼了。可以通過查詢events_waits_current表來得知,該表中每個執行緒只包含一行資料,用於顯示每個執行緒的最新監視事件(正在做的事情)。

_current表中每個執行緒只保留一條記錄,且一旦執行緒完成工作,該表中就不會再記錄該執行緒的事件資訊了。_history表中記錄每個執行緒已經執行完成的事件資訊,但每個執行緒的事件資訊只記錄10條,再多就會被覆蓋掉。*_history_long表中記錄所有執行緒的事件資訊,但總記錄數量是10000行,超過會被覆蓋掉。

summary表提供所有事件的彙總資訊。該組中的表以不同的方式彙總事件資料(如:按使用者、按主機、按執行緒等彙總)。

1.2.6.檢視最近執行失敗的SQL語句

使用程式碼對資料庫的某些操作(比如:使用Java的ORM框架操作資料庫)報出語法錯誤,但是程式碼並沒有記錄SQL語句文字的功能,在MySQL資料庫層能否檢視到具體的SQL語句文字,看看是否哪裡寫錯了?這個時候,大多數人首先想到的就是去檢視錯誤日誌。很遺憾,對於SQL語句的語法錯誤,錯誤日誌並不會記錄。

實際上,在performance_schema的語句事件記錄表中針對每一條語句的執行狀態都記錄了較為詳細的資訊,例如:events_statements_表和events_statements_summary_by_digest表(events_statements_表記錄了語句所有的執行錯誤資訊,而events_statements_summary_by_digest表只記錄了語句在執行過程中發生錯誤的語句記錄統計資訊,不記錄具體的錯誤型別,例如:不記錄語法錯誤類的資訊)。下面看看如何使用這兩個表查詢語句發生錯誤的語句資訊。

首先,我們模擬一條語法錯誤的SQL語句,使用events_statements_history_long表或events_statements_history表查詢發生語法錯誤的SQL語句:

image.png

然後,查詢events_statements_history表中錯誤號為1064的記錄

select * from events_statements_history where mysql_errno=1064\G

image.png

如果不知道錯誤號是多少,可以查詢發生錯誤次數不為0的語句記錄,在裡邊找到SQL_TEXT和MESSAGE_TEXT欄位(提示資訊為語法錯誤的就是它)。

1.2.7.檢視最近的事務執行資訊

我們可以通過慢查詢日誌查詢到一條語句的執行總時長,但是如果資料庫中存在著一些大事務在執行過程中回滾了,或者在執行過程中異常中止,這個時候慢查詢日誌就愛莫能助了,這時我們可以藉助performance_schema的events_transactions_*表來檢視與事務相關的記錄,在這些表中詳細記錄了是否有事務被回滾、活躍(長時間未提交的事務也屬於活躍事務)或已提交等資訊。

首先需要進行配置啟用,事務事件預設並未啟用

update setup_instruments set enabled='yes',timed='yes' where name like 'transaction%';

update setup_consumers set enabled='yes' where name like '%transaction%';

image.png

現在我們開啟一個新會話(會話2)用於執行事務,並模擬事務回滾。

image.png

查詢活躍事務,活躍事務表示當前正在執行的事務事件,需要從events_transactions_current表中查詢。

下圖中可以看到有一條記錄,代表當前活躍的事務事件。

image.png

會話2中回滾事務:

image.png

查詢事務事件當前表(events_transactions_current)和事務事件歷史記錄表(events_transactions_history)

可以看到在兩表中都記錄了一行事務事件資訊,執行緒ID為30的執行緒執行了一個事務,事務狀態為ROLLED BACK。

image.png

image.png

image.png

但是當我們關閉會話2以後,事務事件當前表中(events_transactions_current)的記錄就消失了。

image.png

要查詢的話需要去(events_transactions_history_long)表中查

image.png

image.png

1.2.8.小結

當然performance_schema的用途不止我們上面說到過的這些,它還能提供比如檢視SQL語句執行階段和進度資訊、MySQL叢集下複製功能檢視複製報錯詳情等等。

具體可以參考官網:MySQL :: MySQL 5.7 Reference Manual :: 25 MySQL Performance Schema

1.3.sys系統庫

1.3.1.sys使用須知

sys系統庫支援MySQL 5.6或更高版本,不支援MySQL 5.5.x及以下版本。

sys系統庫通常都是提供給專業的DBA人員排查一些特定問題使用的,其下所涉及的各項查詢或多或少都會對效能有一定的影響。

因為sys系統庫提供了一些代替直接訪問performance_schema的檢視,所以必須啟用performance_schema(將performance_schema系統引數設定為ON),sys系統庫的大部分功能才能正常使用。

同時要完全訪問sys系統庫,使用者必須具有以下資料庫的管理員許可權。

如果要充分使用sys系統庫的功能,則必須啟用某些performance_schema的功能。比如:

啟用所有的wait instruments:

CALL sys.ps_setup_enable_instrument('wait');

image.png

啟用所有事件型別的current表:

CALL sys.ps_setup_enable_consumer('current');

image.png

注意: performance_schema的預設配置就可以滿足sys系統庫的大部分資料收集功能。啟用所有需要功能會對效能產生一定的影響,因此最好僅啟用所需的配置。

1.3.2.sys系統庫使用

如果使用了USE語句切換預設資料庫,那麼就可以直接使用sys系統庫下的檢視進行查詢,就像查詢某個庫下的表一樣操作。也可以使用db_name.view_name、db_name.procedure_name、db_name.func_name等方式,在不指定預設資料庫的情況下訪問sys 系統庫中的物件(這叫作名稱限定物件引用)。

image.png

在sys系統庫下包含很多檢視,它們以各種方式對performance_schema表進行聚合計算展示。這些檢視大部分是成對出現的,兩個檢視名稱相同,但有一個檢視是帶 x $字首的.$

host_summary_by_file_io和 x$host_summary_by_file_io

代表按照主機進行彙總統計的檔案I/O效能資料,兩個檢視訪問的資料來源是相同的,但是在建立檢視的語句中,不帶x$字首的檢視顯示的是相關數值經過單位換算後的資料(單位是毫秒、秒、分鐘、小時、天等),帶 x$ 字首的檢視顯示的是原始的資料(單位是皮秒)。image.png

image.png

image.png

1.3.3.檢視慢SQL語句慢在哪裡

如果我們頻繁地在慢查詢日誌中發現某個語句執行緩慢,且在表結構、索引結構、統計資訊中都無法找出原因時,則可以利用sys系統庫中的撒手鐗:sys.session檢視結合performance_schema的等待事件來找出癥結所在。那麼session檢視有什麼用呢?使用它可以檢視當前使用者會話的程序列表資訊,看看當前程序到底再幹什麼,注意,這個檢視在MySQL 5.7.9中才出現。

image.png

首先需要啟用與等待事件相關功能:

call sys.ps_setup_enable_instrument('wait'); call sys.ps_setup_enable_consumer('wait');

image.png

然後模擬一下:

一個session中執行

select sleep(30);

另外一個session中在sys庫中查詢:

select * from session where command='query' and conn_id !=connection_id()\G

image.png

image.png

查詢表的增、刪、改、查資料量和I/O耗時統計

select * from schema_table_statistics_with_buffer\G

image.png

1.3.4.小結

除此之外,通過sys還可以查詢檢視InnoDB緩衝池中的熱點資料、檢視是否有事務鎖等待、檢視未使用的,冗餘索引、檢視哪些語句使用了全表掃描等等。

具體可以參考官網:MySQL :: MySQL 5.7 Reference Manual :: 26 MySQL sys Schema

1.4.information_schema

1.4.1.什麼是information_schema

information_schema提供了對資料庫元資料、統計資訊以及有關MySQL Server資訊的訪問(例如:資料庫名或表名、欄位的資料型別和訪問許可權等)。該庫中儲存的資訊也可以稱為MySQL的資料字典或系統目錄。

在每個MySQL 例項中都有一個獨立的information_schema,用來儲存MySQL例項中所有其他資料庫的基本資訊。information_schema庫下包含多個只讀表(非持久表),所以在磁碟中的資料目錄下沒有對應的關聯檔案,且不能對這些表設定觸發器。雖然在查詢時可以使用USE語句將預設資料庫設定為information_schema,但該庫下的所有表是隻讀的,不能執行INSERT、UPDATE、DELETE等資料變更操作。

針對information_schema下的表的查詢操作可以替代一些SHOW查詢語句(例如:SHOW DATABASES、SHOW TABLES等)。

注意:根據MySQL版本的不同,表的個數和存放是有所不同的。在MySQL 5.6版本中總共有59個表,在MySQL 5.7版本中,該schema下總共有61個表,

image.pngimage.png

MySQL 8.0版本中,該schema下的資料字典表(包含部分原Memory引擎臨時表)都遷移到了mysql schema下,且在mysql schema下這些資料字典表被隱藏,無法直接訪問,需要通過information_schema下的同名表進行訪問。

information_schema下的所有表使用的都是Memory和InnoDB儲存引擎,且都是臨時表,不是持久表,在資料庫重啟之後這些資料會丟失。在MySQL 的4個系統庫中,information_schema也是唯一一個在檔案系統上沒有對應庫表的目錄和檔案的系統庫。

1.4.2.information_schema表分類

Server層的統計資訊字典表

(1)COLUMNS

• 提供查詢表中的列(欄位)資訊。

(2)KEY_COLUMN_USAGE

• 提供查詢哪些索引列存在約束條件。

• 該表中的資訊包含主鍵、唯一索引、外來鍵等約束資訊,例如:所在的庫表列名、引用的庫表列名等。該表中的資訊與TABLE_CONSTRAINTS表中記錄的資訊有些類似,但TABLE_CONSTRAINTS表中沒有記錄約束引用的庫表列資訊,而KEY_COLUMN_USAGE表中卻記錄了TABLE_CONSTRAINTS表中所沒有的約束型別。

(3)REFERENTIAL_CONSTRAINTS

• 提供查詢關於外來鍵約束的一些資訊。

(4)STATISTICS

• 提供查詢關於索引的一些統計資訊,一個索引對應一行記錄。

(5)TABLE_CONSTRAINTS

• 提供查詢與表相關的約束資訊。

(6)FILES

• 提供查詢與MySQL的資料表空間檔案相關的資訊。

(7)ENGINES

• 提供查詢MySQL Server支援的引擎相關資訊。

(8)TABLESPACES

• 提供查詢關於活躍表空間的相關資訊(主要記錄的是NDB儲存引擎的表空間資訊)。

• 注意:該表不提供有關InnoDB儲存引擎的表空間資訊。對於InnoDB表空間的元資料資訊,請查詢INNODB_SYS_TABLESPACES表和INNODB_SYS_DATAFILES表。另外,從MySQL 5.7.8開始,INFORMATION_SCHEMA.FILES表也提供查詢InnoDB表空間的元資料資訊。

(9)SCHEMATA

• 提供查詢MySQL Server中的資料庫列表資訊,一個schema就代表一個數據庫。

Server層的表級別物件字典表

(1)VIEWS

• 提供查詢資料庫中的檢視相關資訊。查詢該表的賬戶需要擁有show view許可權。

(2)TRIGGERS

• 提供查詢關於某個資料庫下的觸發器相關資訊。

(3)TABLES

• 提供查詢與資料庫內的表相關的基本資訊。

(4)ROUTINES

• 提供查詢關於儲存過程和儲存函式的資訊(不包括使用者自定義函式)。該表中的資訊與mysql.proc中記錄的資訊相對應(如果該表中有值的話)。

(5)PARTITIONS

• 提供查詢關於分割槽表的資訊。

(6)EVENTS

• 提供查詢與計劃任務事件相關的資訊。

(7)PARAMETERS

• 提供有關儲存過程和函式的引數資訊,以及有關儲存函式的返回值資訊。這些引數資訊與mysql.proc表中的param_list列記錄的內容類似。

Server層的混雜資訊字典表

(1)GLOBAL_STATUS、GLOBAL_VARIABLES、SESSION_STATUS、

SESSION_VARIABLES

• 提供查詢全域性、會話級別的狀態變數與系統變數資訊。

(2)OPTIMIZER_TRACE

• 提供優化程式跟蹤功能產生的資訊。

• 跟蹤功能預設是關閉的,使用optimizer_trace系統變數啟用跟蹤功能。如果開啟該功能,則每個會話只能跟蹤它自己執行的語句,不能看到其他會話執行的語句,且每個會話只能記錄最後一條跟蹤的SQL語句。

(3)PLUGINS

• 提供查詢關於MySQL Server支援哪些外掛的資訊。

(4)PROCESSLIST

• 提供查詢一些關於執行緒執行過程中的狀態資訊。

(5)PROFILING

• 提供查詢關於語句效能分析的資訊。其記錄內容對應於SHOW PROFILES和SHOW PROFILE語句產生的資訊。該表只有在會話變數 profiling=1時才會記錄語句效能分析資訊,否則該表不記錄。

• 注意:從MySQL 5.7.2開始,此表不再推薦使用,在未來的MySQL版本中刪除,改用Performance Schema代替。

(6)CHARACTER_SETS

• 提供查詢MySQL Server支援的可用字符集。

(7)COLLATIONS

• 提供查詢MySQL Server支援的可用校對規則。

(8)COLLATION_CHARACTER_SET_APPLICABILITY

• 提供查詢MySQL Server中哪種字符集適用於什麼校對規則。查詢結果集相當於從SHOW COLLATION獲得的結果集的前兩個欄位值。目前並沒有發現該表有太大的作用。

(9)COLUMN_PRIVILEGES

• 提供查詢關於列(欄位)的許可權資訊,表中的內容來自mysql.column_priv列許可權表(需要針對一個表的列單獨授權之後才會有內容)。

(10)SCHEMA_PRIVILEGES

• 提供查詢關於庫級別的許可權資訊,每種型別的庫級別許可權記錄一行資訊,該表中的資訊來自mysql.db表。

(11)TABLE_PRIVILEGES

• 提供查詢關於表級別的許可權資訊,該表中的內容來自mysql.tables_priv表。

(12)USER_PRIVILEGES

• 提供查詢全域性許可權的資訊,該表中的資訊來自mysql.user表。

10.2.4 InnoDB層的系統字典表

(1)INNODB_SYS_DATAFILES

• 提供查詢InnoDB所有表空間型別檔案的元資料(內部使用的表空間ID和表空間檔案的路徑資訊),包括獨立表空間、常規表空間、系統表空間、臨時表空間和undo空間(如果開啟了獨立undo空間的話)。

• 該表中的資訊等同於InnoDB資料字典內部SYS_DATAFILES表的資訊。

(2)INNODB_SYS_VIRTUAL

• 提供查詢有關InnoDB虛擬生成列和與之關聯的列的元資料資訊,等同於InnoDB資料字典內部SYS_VIRTUAL表的資訊。該表中展示的行資訊是與虛擬生成列相關聯列的每個列的資訊。

(3)INNODB_SYS_INDEXES

• 提供查詢有關InnoDB索引的元資料資訊,等同於InnoDB資料字典內部SYS_INDEXES表中的資訊。

(4)INNODB_SYS_TABLES

• 提供查詢有關InnoDB表的元資料資訊,等同於InnoDB資料字典內部SYS_TABLES表的資訊。

(5)INNODB_SYS_FIELDS

• 提供查詢有關InnoDB索引鍵列(欄位)的元資料資訊,等同於InnoDB資料字典內部SYS_FIELDS表的資訊。

(6)INNODB_SYS_TABLESPACES

• 提供查詢有關InnoDB獨立表空間和普通表空間的元資料資訊(也包含了全文索引表空間),等同於InnoDB資料字典內部SYS_TABLESPACES表的資訊。

(7)INNODB_SYS_FOREIGN_COLS

• 提供查詢有關InnoDB外來鍵列的狀態資訊,等同於InnoDB資料字典內部

SYS_FOREIGN_COLS表的資訊。

(8)INNODB_SYS_COLUMNS

• 提供查詢有關InnoDB表列的元資料資訊,等同於InnoDB資料字典內部

SYS_COLUMNS表的資訊。

(9)INNODB_SYS_FOREIGN

• 提供查詢有關InnoDB外來鍵的元資料資訊,等同於InnoDB資料字典內部SYS_FOREIGN表的資訊。

(10)INNODB_SYS_TABLESTATS

• 提供查詢有關InnoDB表的較低級別的狀態資訊檢視。 MySQL優化器會使用這些統計資訊資料來計算並確定在查詢InnoDB表時要使用哪個索引。這些資訊儲存在記憶體中的資料結構中,與儲存在磁碟上的資料無對應關係。在InnoDB內部也無對應的系統表。

InnoDB層的鎖、事務、統計資訊字典表

(1)INNODB_LOCKS

• 提供查詢InnoDB引擎中事務正在請求的且同時被其他事務阻塞的鎖資訊(即沒有發生不同事務之間鎖等待的鎖資訊,在這裡是檢視不到的。例如,當只有一個事務時,無法檢視到該事務所加的鎖資訊)。該表中的內容可用於診斷高併發下的鎖爭用資訊。

(2)INNODB_TRX

• 提供查詢當前在InnoDB引擎中執行的每個事務(不包括只讀事務)的資訊,包括事務是否正在等待鎖、事務什麼時間點開始,以及事務正在執行的SQL語句文字資訊等(如果有SQL語句的話)。

(3)INNODB_BUFFER_PAGE_LRU

• 提供查詢緩衝池中的頁面資訊。與INNODB_BUFFER_PAGE表不同,INNODB_BUFFER_PAGE_LRU表儲存有關InnoDB緩衝池中的頁如何進入LRU連結串列,以及在緩衝池不夠用時確定需要從中逐出哪些頁的資訊。

(4)INNODB_LOCK_WAITS

• 提供查詢InnoDB事務的鎖等待資訊。如果查詢該表為空,則表示無鎖等待資訊;如果查詢該表中有記錄,則說明存在鎖等待,表中的每一行記錄表示一個鎖等待關係。在一個鎖等待關係中包含:一個等待鎖(即,正在請求獲得鎖)的事務及其正在等待的鎖等資訊、一個持有鎖(這裡指的是發生鎖等待事務正在請求的鎖)的事務及其所持有的鎖等資訊。

(5)INNODB_TEMP_TABLE_INFO

• 提供查詢有關在InnoDB例項中當前處於活動狀態的使用者(只對已建立連線的使用者有效,斷開的使用者連線對應的臨時表會被自動刪除)建立的InnoDB臨時表的資訊。它不提供查詢優化器使用的內部InnoDB臨時表的資訊。該表在首次查詢時建立。

(6)INNODB_BUFFER_PAGE

• 提供查詢關於緩衝池中的頁相關資訊。

(7)INNODB_METRICS

• 提供查詢InnoDB更為詳細的效能資訊,是對InnoDB的performance_schema的補充。通過對該表的查詢,可用於檢查InnoDB的整體健康狀況,也可用於診斷效能瓶頸、資源短缺和應用程式的問題等。

(8)INNODB_BUFFER_POOL_STATS

• 提供查詢一些InnoDB緩衝池中的狀態資訊,該表中記錄的資訊與SHOW ENGINEINNODB STATUS語句輸出的緩衝池統計部分資訊類似。另外,InnoDB緩衝池的一些狀態變數也提供了部分相同的值。

InnoDB層的全文索引字典表

(1)INNODB_FT_CONFIG

(2)INNODB_FT_BEING_DELETED

(3)INNODB_FT_DELETED

(4)INNODB_FT_DEFAULT_STOPWORD

(5)INNODB_FT_INDEX_TABLE

InnoDB層的壓縮相關字典表

(1)INNODB_CMP和INNODB_CMP_RESET

• 這兩個表中的資料包含了與壓縮的InnoDB表頁有關的操作狀態資訊。表中記錄的資料為測量資料庫中的InnoDB表壓縮的有效性提供參考。

(2)INNODB_CMP_PER_INDEX和INNODB_CMP_PER_INDEX_RESET

• 這兩個表中記錄了與InnoDB壓縮表資料和索引相關的操作狀態資訊,對資料庫、表、索引的每個組合使用不同的統計資訊,以便為評估特定表的壓縮效能和實用性提供參考資料。

(3)INNODB_CMPMEM和INNODB_CMPMEM_RESET

• 這兩個表中記錄了InnoDB緩衝池中壓縮頁的狀態資訊,為測量資料庫中InnoDB表壓縮的有效性提供參考。

1.4.3.information_schema應用

檢視索引列的資訊

INNODB_SYS_FIELDS表提供查詢有關InnoDB索引列(欄位)的元資料資訊,等同於InnoDB資料字典中SYS_FIELDS表的資訊。

INNODB_SYS_INDEXES表提供查詢有關InnoDB索引的元資料資訊,等同於InnoDB資料字典內部SYS_INDEXES表中的資訊。

INNODB_SYS_TABLES表提供查詢有關InnoDB表的元資料資訊,等同於InnoDB資料字典中SYS_TABLES表的資訊。

假設需要查詢lijin庫下的InnoDB表order_exp的索引列名稱、組成和索引列順序等相關資訊,

image.png

image.png

則可以使用如下SQL語句進行查詢

SELECT t. NAME AS d_t_name, i. NAME AS i_name, i.type AS i_type, i.N_FIELDS AS i_column_numbers, f. NAME AS i_column_name, f.pos AS i_position FROM INNODB_SYS_TABLES AS t JOIN INNODB_SYS_INDEXES AS i ON t.TABLE_ID = i.TABLE_ID LEFT JOIN INNODB_SYS_FIELDS AS f ON i.INDEX_ID = f.INDEX_ID WHERE t. NAME = 'lijin/order_exp';

image.png

結果中的列都很好理解,唯一需要額外解釋的是i_type(INNODB_SYS_INDEXES.type),它是表示索引型別的數字ID:

0 =二級索引

1=叢集索引

2 =唯一索引

3 =主鍵索引

32 =全文索引

64 =空間索引

128 =包含虛擬生成列的二級索引。

1.5.Mysql中mysql系統庫

1.5.1.許可權系統表

因為許可權管理是DBA的職責,所以對於這個部分的表,我們大概瞭解下即可。在mysql系統庫中,MySQL訪問許可權系統表,放在mysql庫中,主要包含如下幾個表。

image.png

• user:包含使用者賬戶、全域性許可權和其他非許可權列表(安全配置欄位和資源控制欄位)。

• db:資料庫級別的許可權表。該表中記錄的許可權資訊代表使用者是否可以使用這些許可權來訪問被授予訪問的資料庫下的所有物件(表或儲存程式)。

• tables_priv:表級別的許可權表。

• columns_priv:欄位級別的許可權表。

• procs_priv:儲存過程和函式許可權表。

• proxies_priv:代理使用者許可權表。

提示:

要更改許可權表的內容,應該使用賬號管理語句(如: CREATE USER GRANT REVOKE等)來間接修改,不建議直接使用DML語句修改許可權表。

(grant,revoke語句執行後會變更許可權表中相關記錄,同時會更新記憶體中記錄使用者許可權的相關物件。dml語句直接修改許可權表只是修改了表中許可權資訊,需要執行flush privileges;來更新記憶體中儲存使用者許可權的相關物件)

1.5.2.統計資訊表

持久化統計功能是通過將記憶體中的統計資料儲存到磁碟中,使其在資料庫重啟時可以快速重新讀入這些統計資訊而不用重新執行統計,從而使得查詢優化器可以利用這些持久化的統計資訊準確地選擇執行計劃(如果沒有這些持久化的統計資訊,那麼資料庫重啟之後記憶體中的統計資訊將會丟失,下一次訪問到某庫某表時,需要重新計算統計資訊,並且重新計算可能會因為估算值的差異導致查詢計劃發生變更,從而導致查詢效能發生變化)。

如何啟用統計資訊的持久化功能呢?當innodb_stats_persistent = ON時全域性的開啟統計資訊的持久化功能,預設是開啟的,

show variables like 'innodb_stats_persistent';

如果要單獨關閉某個表的持久化統計功能,則可以通過ALTER TABLE tbl_name STATS_PERSISTENT = 0語句來修改。

1.5.2.1.innodb_table_stats

innodb_table_stats表提供查詢與表資料相關的統計資訊。

select * from innodb_table_stats where table_name = 'order_exp'\G

image.png

database_name:資料庫名稱。

• table_name:表名、分割槽名或子分割槽名。

• last_update:表示InnoDB上次更新統計資訊行的時間。

• n_rows:表中的估算資料記錄行數。

• clustered_index_size:主鍵索引的大小,以頁為單位的估算數值。

• sum_of_other_index_sizes:其他(非主鍵)索引的總大小,以頁為單位的估算數值。

1.5.2.2.innodb_index_stats

innodb_index_stats表提供查詢與索引相關的統計資訊。

select * from innodb_index_stats where table_name = 'order_exp';

image.png

表字段含義如下。

• database_name:資料庫名稱。

• table_name:表名、分割槽表名、子分割槽表名。

• index_name:索引名稱。

• last_update:表示InnoDB上次更新統計資訊行的時間。

• stat_name:統計資訊名稱,其對應的統計資訊值儲存在stat_value欄位中。

• stat_value:儲存統計資訊名稱stat_name欄位對應的統計資訊值。

• sample_size:stat_value欄位中提供的統計資訊估計值的取樣頁數。

• stat_description:統計資訊名稱stat_name欄位中指定的統計資訊的說明。

從表的查詢資料中可以看到:

• stat_name欄位一共有如下幾個統計值。

■ size:當stat_name欄位為size值時,stat_value欄位值表示索引中的總頁數量。

■ n_leaf_pages:當stat_name欄位為n_leaf_pages值時,stat_value欄位值表示索引葉子頁的數量。

■ n_diff_pfxNN:NN代表數字(例如01、02等)。當stat_name欄位為n_diff_pfxNN值時,stat_value欄位值表示索引的first column(即索引的最前索引列,從索引定義順序的第一個列開始)列的唯一值數量。例如:當NN為01時,stat_value欄位值就表示索引的第一個列的唯一值數量;當NN為02時,stat_value欄位值就表示索引的第一個和第二個列組合的唯一值數量,依此類推。此外,在stat_name = n_diff_pfxNN的情況下,stat_description欄位顯示一個以逗號分隔的計算索引統計資訊欄位的列表。

• 從index_name欄位值為PRIMARY資料行的stat_description欄位的描述資訊“id”中可以看出,主鍵索引的統計資訊只包括建立主鍵索引時顯式指定的列。

• 從index_name欄位值為u_idx_day_status資料行的stat_description欄位的描述資訊“insert_time,order_status,expire_time”中可以看出,唯一索引的統計資訊只包括建立唯一索引時顯式指定的列。

• 從index_name欄位值為idx_order_no資料行的stat_description欄位的描述資訊“order_no,id”中可以看出,普通索引(非唯一的輔助索引)的統計資訊包括了顯式定義的列和主鍵列。

注意,上述的描述中出現的諸如葉子頁,索引的最前索引列等等,這些東西在索引章節有講解,這裡不再闡述。

1.5.3.日誌記錄表

MySQL的日誌系統包含:普通查詢日誌、慢查詢日誌、錯誤日誌(記錄伺服器啟動時、執行中、停止時的錯誤資訊)、二進位制日誌(記錄伺服器執行過程中資料變更的邏輯日誌)、中繼日誌(記錄從庫I/O執行緒從主庫獲取的主庫資料變更日誌)、DDL日誌(記錄DDL語句執行時的元資料變更資訊。在MySQL 5.7中只支援寫入檔案中,在MySQL 8.0中支援寫入innodb_ddl_log表中。在MySQL5.7中,只有普通查詢日誌、慢查詢日誌支援寫入表中(也支援寫入檔案中),可以通過log_output=TABLE設定儲存到mysql.general_log表和mysql.slow_log表中,其他日誌型別在MySQL 5.7中只支援寫入檔案中。

1.5.3.1. general_log

general_log表提供查詢普通SQL語句的執行記錄資訊,用於檢視客戶端到底在伺服器上執行了什麼SQL語句。

預設不開啟

show variables like 'general_log';

image.png

開啟

set global log_output='TABLE'; -- 'TABLE,FILE'表示同時輸出到表和檔案 set global general_log=on; show variables like 'general_log';

image.png

任意執行一個查詢後

image.png

select * from mysql.general_log\G

image.png

image.png

1.5.3.2. slow_log

slow_log表提供查詢執行時間超過long_query_time設定值的SQL語句、未使用索引的語句(需要開啟引數log_queries_not_using_indexes=ON)或者管理語句(需要開啟引數log_slow_admin_statements=ON)。

show variables like 'log_queries_not_using_indexes'; show variables like 'log_slow_admin_statements';

image.png

image.png

開啟

set global log_queries_not_using_indexes=on; set global log_slow_admin_statements=on; show variables like 'log_queries_not_using_indexes'; show variables like 'log_slow_admin_statements';

image.png

我們已經知道慢查詢日誌可以幫助定位可能存在問題的SQL語句,從而進行SQL語句層面的優化。但是預設值為關閉的,需要我們手動開啟。

show VARIABLES like 'slow_query_log';

image.png

set GLOBAL slow_query_log=1;

開啟1,關閉0

但是多慢算慢?MySQL中可以設定一個閾值,將執行時間超過該值的所有SQL語句都記錄到慢查詢日誌中。long_query_time引數就是這個閾值。預設值為10,代表10秒。

show VARIABLES like '%long_query_time%';

當然也可以設定

set global long_query_time=0;

預設10秒,這裡為了演示方便設定為0

image.png

然後我們測試一把,隨便寫一個SQL

image.png

select * from mysql.slow_log\G

image.png

1.5.4.InnoDB中的統計資料

我們前邊嘮叨查詢成本的時候經常用到一些統計資料,比如通過SHOW TABLE STATUS可以看到關於表的統計資料,通過SHOW INDEX可以看到關於索引的統計資料,那麼這些統計資料是怎麼來的呢?它們是以什麼方式收集的呢?

1.5.4.1 統計資料儲存方式

InnoDB提供了兩種儲存統計資料的方式:

永久性的統計資料,這種統計資料儲存在磁碟上,也就是伺服器重啟之後這些統計資料還在。

非永久性的統計資料,這種統計資料儲存在記憶體中,當伺服器關閉時這些這些統計資料就都被清除掉了,等到伺服器重啟之後,在某些適當的場景下才會重新收集這些統計資料。

MySQL給我們提供了系統變數innodb_stats_persistent來控制到底採用哪種方式去儲存統計資料。在MySQL 5.6.6之前,innodb_stats_persistent的值預設是OFF,也就是說InnoDB的統計資料預設是儲存到記憶體的,之後的版本中innodb_stats_persistent的值預設是ON,也就是統計資料預設被儲存到磁碟中。

SHOW VARIABLES LIKE 'innodb_stats_persistent';

image.png

不過最近的MySQL版本都基本不用基於記憶體的非永久性統計資料了,所以我們也就不深入研究。

不過InnoDB預設是以表為單位來收集和儲存統計資料的,也就是說我們可以把某些表的統計資料(以及該表的索引統計資料)儲存在磁碟上,把另一些表的統計資料儲存在記憶體中。怎麼做到的呢?我們可以在建立和修改表的時候通過指定STATS_PERSISTENT屬性來指明該表的統計資料儲存方式:

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);

ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);

當STATS_PERSISTENT=1時,表明我們想把該表的統計資料永久的儲存到磁碟上,當STATS_PERSISTENT=0時,表明我們想把該表的統計資料臨時的儲存到記憶體中。如果我們在建立表時未指定STATS_PERSISTENT屬性,那預設採用系統變數innodb_stats_persistent的值作為該屬性的值。

1.5.4.2 基於磁碟的永久性統計資料

當我們選擇把某個表以及該表索引的統計資料存放到磁碟上時,實際上是把這些統計資料儲存到了兩個表裡:

SHOW TABLES FROM mysql LIKE 'innodb%';

image.png

可以看到,這兩個表都位於mysql系統資料庫下邊,其中:

innodb_table_stats儲存了關於表的統計資料,每一條記錄對應著一個表的統計資料。

innodb_index_stats儲存了關於索引的統計資料,每一條記錄對應著一個索引的一個統計項的統計資料。

innodb_table_stats

直接看一下這個innodb_table_stats表中的各個列都是幹嘛的:

image.png

database_name 資料庫名

table_name 表名

last_update 本條記錄最後更新時間

n_rows表中記錄的條數

clustered_index_size 表的聚簇索引佔用的頁面數量

sum_of_other_index_sizes 表的其他索引佔用的頁面數量

我們直接看一下這個表裡的內容:

SELECT * FROM mysql.innodb_table_stats;

image.png

幾個重要統計資訊項的值如下:

n_rows的值是10350,表明order_exp表中大約有10350條記錄,注意這個資料是估計值。

clustered_index_size的值是97,表明order_exp表的聚簇索引佔用97個頁面,這個值是也是一個估計值。

sum_of_other_index_sizes的值是81,表明order_exp表的其他索引一共佔用81個頁面,這個值是也是一個估計值。

n_rows統計項的收集

InnoDB統計一個表中有多少行記錄是這樣的:

按照一定演算法(並不是純粹隨機的)選取幾個葉子節點頁面,計算每個頁面中主鍵值記錄數量,然後計算平均一個頁面中主鍵值的記錄數量乘以全部葉子節點的數量就算是該表的n_rows值。

可以看出來這個n_rows值精確與否取決於統計時取樣的頁面數量,MySQL用名為innodb_stats_persistent_sample_pages的系統變數來控制使用永久性的統計資料時,計算統計資料時取樣的頁面數量。該值設定的越大,統計出的n_rows值越精確,但是統計耗時也就最久;該值設定的越小,統計出的n_rows值越不精確,但是統計耗時特別少。所以在實際使用是需要我們去權衡利弊,該系統變數的預設值是20。

InnoDB預設是以表為單位來收集和儲存統計資料的,我們也可以單獨設定某個表的取樣頁面的數量,設定方式就是在建立或修改表的時候通過指定STATS_SAMPLE_PAGES屬性來指明該表的統計資料儲存方式:

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_SAMPLE_PAGES = 具體的取樣頁面數量;

ALTER TABLE 表名 Engine=InnoDB, STATS_SAMPLE_PAGES = 具體的取樣頁面數量;

如果我們在建立表的語句中並沒有指定STATS_SAMPLE_PAGES屬性的話,將預設使用系統變數innodb_stats_persistent_sample_pages的值作為該屬性的值。

clustered_index_size和sum_of_other_index_sizes統計項的收集牽涉到很具體的InnoDB表空間的知識和儲存頁面資料的細節,我們就不深入講解了。

innodb_index_stats

直接看一下這個innodb_index_stats表中的各個列都是幹嘛的:

desc mysql.innodb_index_stats;

欄位名描述

database_name 資料庫名

table_name 表名

index_name 索引名

last_update 本條記錄最後更新時間

stat_name 統計項的名稱

stat_value 對應的統計項的值

sample_size 為生成統計資料而取樣的頁面數量

stat_description 對應的統計項的描述

innodb_index_stats表的每條記錄代表著一個索引的一個統計項。可能這會大家有些懵逼這個統計項到底指什麼,彆著急,我們直接看一下關於order_exp表的索引統計資料都有些什麼:

SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'order_exp';

image.png

先檢視index_name列,這個列說明該記錄是哪個索引的統計資訊,從結果中我們可以看出來,PRIMARY索引(也就是主鍵)佔了3條記錄,idx_expire_time索引佔了6條記錄。

針對index_name列相同的記錄,stat_name表示針對該索引的統計項名稱,stat_value展示的是該索引在該統計項上的值,stat_description指的是來描述該統計項的含義的。我們來具體看一下一個索引都有哪些統計項:

n_leaf_pages:表示該索引的葉子節點佔用多少頁面。

size:表示該索引共佔用多少頁面。

n_diff_pfxNN:表示對應的索引列不重複的值有多少。其中的NN長得有點兒怪呀,啥意思呢?

其實NN可以被替換為01、02、03... 這樣的數字。比如對於u_idx_day_status來說:

n_diff_pfx01表示的是統計insert_time這單單一個列不重複的值有多少。

n_diff_pfx02表示的是統計insert_time,order_status這兩個列組合起來不重複的值有多少。

n_diff_pfx03表示的是統計insert_time,order_status,expire_time這三個列組合起來不重複的值有多少。

n_diff_pfx04表示的是統計key_pare1、key_pare2、expire_time、id這四個列組合起來不重複的值有多少。

對於普通的二級索引,並不能保證它的索引列值是唯一的,比如對於idx_order_no來說,key1列就可能有很多值重複的記錄。此時只有在索引列上加上主鍵值才可以區分兩條索引列值都一樣的二級索引記錄。

對於主鍵和唯一二級索引則沒有這個問題,它們本身就可以保證索引列值的不重複,所以也不需要再統計一遍在索引列後加上主鍵值的不重複值有多少。比如u_idx_day_statu和idx_order_no。

在計算某些索引列中包含多少不重複值時,需要對一些葉子節點頁面進行取樣,sample_size列就表明了取樣的頁面數量是多少。

對於有多個列的聯合索引來說,取樣的頁面數量是:innodb_stats_persistent_sample_pages × 索引列的個數。

image.png

當需要取樣的頁面數量大於該索引的葉子節點數量的話,就直接採用全表掃描來統計索引列的不重複值數量了。所以大家可以在查詢結果中看到不同索引對應的size列的值可能是不同的。

定期更新統計資料

隨著我們不斷的對錶進行增刪改操作,表中的資料也一直在變化,innodb_table_stats和innodb_index_stats表裡的統計資料也在變化。MySQL提供瞭如下兩種更新統計資料的方式:

開啟innodb_stats_auto_recalc。

系統變數innodb_stats_auto_recalc決定著伺服器是否自動重新計算統計資料,它的預設值是ON,也就是該功能預設是開啟的。每個表都維護了一個變數,該變數記錄著對該表進行增刪改的記錄條數,如果發生變動的記錄數量超過了表大小的10%,並且自動重新計算統計資料的功能是開啟的,那麼伺服器會重新進行一次統計資料的計算,並且更新innodb_table_stats和innodb_index_stats表。不過自動重新計算統計資料的過程是非同步發生的,也就是即使表中變動的記錄數超過了10%,自動重新計算統計資料也不會立即發生,可能會延遲幾秒才會進行計算。

再一次強調,InnoDB預設是以表為單位來收集和儲存統計資料的,我們也可以單獨為某個表設定是否自動重新計算統計數的屬性,設定方式就是在建立或修改表的時候通過指定STATS_AUTO_RECALC屬性來指明該表的統計資料儲存方式:

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_AUTO_RECALC = (1|0);

ALTER TABLE 表名 Engine=InnoDB, STATS_AUTO_RECALC = (1|0);

當STATS_AUTO_RECALC=1時,表明我們想讓該表自動重新計算統計資料,當STATS_AUTO_RECALC=0時,表明不想讓該表自動重新計算統計資料。如果我們在建立表時未指定STATS_AUTO_RECALC屬性,那預設採用系統變數innodb_stats_auto_recalc的值作為該屬性的值。

手動呼叫ANALYZE TABLE語句來更新統計資訊

如果innodb_stats_auto_recalc系統變數的值為OFF的話,我們也可以手動呼叫ANALYZE TABLE語句來重新計算統計資料,比如我們可以這樣更新關於order_exp表的統計資料:

ANALYZE TABLE order_exp;

image.png

ANALYZE TABLE語句會立即重新計算統計資料,也就是這個過程是同步的,在表中索引多或者取樣頁面特別多時這個過程可能會特別慢最好在業務不是很繁忙的時候再執行。

手動更新innodb_table_stats和innodb_index_stats表

其實innodb_table_stats和innodb_index_stats表就相當於一個普通的表一樣,我們能對它們做增刪改查操作。這也就意味著我們可以手動更新某個表或者索引的統計資料。比如說我們想把order_exp表關於行數的統計資料更改一下可以這麼做:

步驟一:更新innodb_table_stats表。

步驟二:讓MySQL查詢優化器重新載入我們更改過的資料。

更新完innodb_table_stats只是單純的修改了一個表的資料,需要讓MySQL查詢優化器重新載入我們更改過的資料,執行下邊的命令就可以了:

```

```

FLUSH TABLE order_exp;

持續創作,加速成長!這是我參與「掘金日新計劃 · 10 月更文挑戰」的第22天,點選檢視活動詳情