查詢優(yōu)化

• 避免全表掃描：盡量使用索引來避免全表掃描，例如在WHERE子句中使用索引列進行過濾。
• 優(yōu)化子查詢：子查詢可能會導致性能問題，可以考慮使用JOIN來替代子查詢。
• 減少不必要的列：在查詢時只選擇需要的列，避免使用SELECT *。

配置參數(shù)調(diào)整

• 調(diào)整內(nèi)存分配：根據(jù)服務器的硬件資源和業(yè)務需求，調(diào)整innodb_buffer_pool_size、key_buffer_size等參數(shù)，以提高緩存命中率。
• 調(diào)整日志參數(shù)：根據(jù)業(yè)務需求調(diào)整log_bin、innodb_log_file_size等參數(shù)，以平衡數(shù)據(jù)安全性和性能。

硬件優(yōu)化

• 使用高速存儲設備：如 SSD 可以顯著提高磁盤 I/O 性能。
• 增加內(nèi)存：足夠的內(nèi)存可以減少磁盤 I/O，提高查詢性能。

MySQL中的系統(tǒng)庫

1.3.sys系統(tǒng)庫

1.3.1.sys使用須知

sys系統(tǒng)庫支持MySQL 5.6或更高版本，不支持MySQL 5.5.x及以下版本。

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

因為sys系統(tǒng)庫提供了一些代替直接訪問performance_schema的視圖，所以必須啟用performance_schema（將performance_schema系統(tǒng)參數(shù)設置為ON），sys系統(tǒng)庫的大部分功能才能正常使用。

同時要完全訪問sys系統(tǒng)庫，用戶必須具有以下數(shù)據(jù)庫的管理員權限。

如果要充分使用sys系統(tǒng)庫的功能，則必須啟用某些performance_schema的功能。比如：

啟用所有的wait instruments：

CALL sys.ps_setup_enable_instrument('wait');

啟用所有事件類型的current表：

CALL sys.ps_setup_enable_consumer('current');

注意： performance_schema的默認配置就可以滿足sys系統(tǒng)庫的大部分數(shù)據(jù)收集功能。啟用所有需要功能會對性能產(chǎn)生一定的影響，因此最好僅啟用所需的配置。

1.3.2.sys系統(tǒng)庫使用

如果使用了USE語句切換默認數(shù)據(jù)庫，那么就可以直接使用sys系統(tǒng)庫下的視圖進行查詢，就像查詢某個庫下的表一樣操作。也可以使用db_name.view_name、db_name.procedure_name、db_name.func_name等方式，在不指定默認數(shù)據(jù)庫的情況下訪問sys 系統(tǒng)庫中的對象（這叫作名稱限定對象引用）。

在sys系統(tǒng)庫下包含很多視圖，它們以各種方式對performance_schema表進行聚合計算展示。這些視圖大部分是成對出現(xiàn)的，兩個視圖名稱相同，但有一個視圖是帶 x $前綴的.$

host_summary_by_file_io和 x$host_summary_by_file_io

代表按照主機進行匯總統(tǒng)計的文件I/O性能數(shù)據(jù)，兩個視圖訪問的數(shù)據(jù)源是相同的，但是在創(chuàng)建視圖的語句中，不帶x$前綴的視圖顯示的是相關數(shù)值經(jīng)過單位換算后的數(shù)據(jù)（單位是毫秒、秒、分鐘、小時、天等），帶 x$ 前綴的視圖顯示的是原始的數(shù)據(jù)（單位是皮秒）。

1.3.3.查看慢SQL語句慢在哪里

如果我們頻繁地在慢查詢?nèi)罩局邪l(fā)現(xiàn)某個語句執(zhí)行緩慢，且在表結(jié)構(gòu)、索引結(jié)構(gòu)、統(tǒng)計信息中都無法找出原因時，則可以利用sys系統(tǒng)庫中的撒手锏：sys.session視圖結(jié)合performance_schema的等待事件來找出癥結(jié)所在。那么session視圖有什么用呢？使用它可以查看當前用戶會話的進程列表信息，看看當前進程到底再干什么，注意，這個視圖在MySQL 5.7.9中才出現(xiàn)。

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

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

然后模擬一下：

一個session中執(zhí)行

select sleep(30);

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

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

查詢表的增、刪、改、查數(shù)據(jù)量和I/O耗時統(tǒng)計

select * from schema_table_statistics_with_buffer\G

1.3.4.小結(jié)

除此之外，通過sys還可以查詢查看InnoDB緩沖池中的熱點數(shù)據(jù)、查看是否有事務鎖等待、查看未使用的，冗余索引、查看哪些語句使用了全表掃描等等。

具體可以參考官網(wǎng)：MySQL :: MySQL 5.7 Reference Manual :: 26 MySQL sys Schema

1.4.information_schema

1.4.1.什么是information_schema

information_schema提供了對數(shù)據(jù)庫元數(shù)據(jù)、統(tǒng)計信息以及有關MySQL Server信息的訪問（例如：數(shù)據(jù)庫名或表名、字段的數(shù)據(jù)類型和訪問權限等）。該庫中保存的信息也可以稱為MySQL的數(shù)據(jù)字典或系統(tǒng)目錄。

在每個MySQL 實例中都有一個獨立的information_schema，用來存儲MySQL實例中所有其他數(shù)據(jù)庫的基本信息。information_schema庫下包含多個只讀表（非持久表），所以在磁盤中的數(shù)據(jù)目錄下沒有對應的關聯(lián)文件，且不能對這些表設置觸發(fā)器。雖然在查詢時可以使用USE語句將默認數(shù)據(jù)庫設置為information_schema，但該庫下的所有表是只讀的，不能執(zhí)行INSERT、UPDATE、DELETE等數(shù)據(jù)變更操作。

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

注意：根據(jù)MySQL版本的不同，表的個數(shù)和存放是有所不同的。在MySQL 5.6版本中總共有59個表，在MySQL 5.7版本中，該schema下總共有61個表，

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

information_schema下的所有表使用的都是Memory和InnoDB存儲引擎，且都是臨時表，不是持久表，在數(shù)據(jù)庫重啟之后這些數(shù)據(jù)會丟失。在MySQL 的4個系統(tǒng)庫中，information_schema也是唯一一個在文件系統(tǒng)上沒有對應庫表的目錄和文件的系統(tǒng)庫。

1.4.2.information_schema表分類

Server層的統(tǒng)計信息字典表

（1）COLUMNS

提供查詢表中的列（字段）信息。

（2）KEY_COLUMN_USAGE

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

該表中的信息包含主鍵、唯一索引、外鍵等約束信息，例如：所在的庫表列名、引用的庫表列名等。該表中的信息與TABLE_CONSTRAINTS表中記錄的信息有些類似，但TABLE_CONSTRAINTS表中沒有記錄約束引用的庫表列信息，而KEY_COLUMN_USAGE表中卻記錄了TABLE_CONSTRAINTS表中所沒有的約束類型。

（3）REFERENTIAL_CONSTRAINTS

提供查詢關于外鍵約束的一些信息。

（4）STATISTICS

提供查詢關于索引的一些統(tǒng)計信息，一個索引對應一行記錄。

（5）TABLE_CONSTRAINTS

提供查詢與表相關的約束信息。

（6）FILES

提供查詢與MySQL的數(shù)據(jù)表空間文件相關的信息。

（7）ENGINES

提供查詢MySQL Server支持的引擎相關信息。

（8）TABLESPACES

提供查詢關于活躍表空間的相關信息（主要記錄的是NDB存儲引擎的表空間信息）。

注意：該表不提供有關InnoDB存儲引擎的表空間信息。對于InnoDB表空間的元數(shù)據(jù)信息，請查詢INNODB_SYS_TABLESPACES表和INNODB_SYS_DATAFILES表。另外，從MySQL 5.7.8開始，INFORMATION_SCHEMA.FILES表也提供查詢InnoDB表空間的元數(shù)據(jù)信息。

（9）SCHEMATA

提供查詢MySQL Server中的數(shù)據(jù)庫列表信息，一個schema就代表一個數(shù)據(jù)庫。

Server層的表級別對象字典表

（1）VIEWS

提供查詢數(shù)據(jù)庫中的視圖相關信息。查詢該表的賬戶需要擁有show view權限。

（2）TRIGGERS

提供查詢關于某個數(shù)據(jù)庫下的觸發(fā)器相關信息。

（3）TABLES

提供查詢與數(shù)據(jù)庫內(nèi)的表相關的基本信息。

（4）ROUTINES

提供查詢關于存儲過程和存儲函數(shù)的信息（不包括用戶自定義函數(shù)）。該表中的信息與mysql.proc中記錄的信息相對應（如果該表中有值的話）。

（5）PARTITIONS

提供查詢關于分區(qū)表的信息。

（6）EVENTS

提供查詢與計劃任務事件相關的信息。

（7）PARAMETERS

提供有關存儲過程和函數(shù)的參數(shù)信息，以及有關存儲函數(shù)的返回值信息。這些參數(shù)信息與mysql.proc表中的param_list列記錄的內(nèi)容類似。

Server層的混雜信息字典表

（1）GLOBAL_STATUS、GLOBAL_VARIABLES、SESSION_STATUS、

SESSION_VARIABLES

提供查詢?nèi)?、會話級別的狀態(tài)變量與系統(tǒng)變量信息。

（2）OPTIMIZER_TRACE

提供優(yōu)化程序跟蹤功能產(chǎn)生的信息。

跟蹤功能默認是關閉的，使用optimizer_trace系統(tǒng)變量啟用跟蹤功能。如果開啟該功能，則每個會話只能跟蹤它自己執(zhí)行的語句，不能看到其他會話執(zhí)行的語句，且每個會話只能記錄最后一條跟蹤的SQL語句。

（3）PLUGINS

提供查詢關于MySQL Server支持哪些插件的信息。

（4）PROCESSLIST

提供查詢一些關于線程運行過程中的狀態(tài)信息。

（5）PROFILING

提供查詢關于語句性能分析的信息。其記錄內(nèi)容對應于SHOW PROFILES和SHOW PROFILE語句產(chǎn)生的信息。該表只有在會話變量 profiling=1時才會記錄語句性能分析信息，否則該表不記錄。

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

（6）CHARACTER_SETS

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

（7）COLLATIONS

提供查詢MySQL Server支持的可用校對規(guī)則。

（8）COLLATION_CHARACTER_SET_APPLICABILITY

提供查詢MySQL Server中哪種字符集適用于什么校對規(guī)則。查詢結(jié)果集相當于從SHOW COLLATION獲得的結(jié)果集的前兩個字段值。目前并沒有發(fā)現(xiàn)該表有太大的作用。

（9）COLUMN_PRIVILEGES

提供查詢關于列（字段）的權限信息，表中的內(nèi)容來自mysql.column_priv列權限表（需要針對一個表的列單獨授權之后才會有內(nèi)容）。

（10）SCHEMA_PRIVILEGES

提供查詢關于庫級別的權限信息，每種類型的庫級別權限記錄一行信息，該表中的信息來自mysql.db表。

（11）TABLE_PRIVILEGES

提供查詢關于表級別的權限信息，該表中的內(nèi)容來自mysql.tables_priv表。

（12）USER_PRIVILEGES

提供查詢?nèi)謾嘞薜男畔ⅲ摫碇械男畔碜詍ysql.user表。

10.2.4　InnoDB層的系統(tǒng)字典表

（1）INNODB_SYS_DATAFILES

提供查詢InnoDB所有表空間類型文件的元數(shù)據(jù)（內(nèi)部使用的表空間ID和表空間文件的路徑信息），包括獨立表空間、常規(guī)表空間、系統(tǒng)表空間、臨時表空間和undo空間（如果開啟了獨立undo空間的話）。

該表中的信息等同于InnoDB數(shù)據(jù)字典內(nèi)部SYS_DATAFILES表的信息。

（2）INNODB_SYS_VIRTUAL

提供查詢有關InnoDB虛擬生成列和與之關聯(lián)的列的元數(shù)據(jù)信息，等同于InnoDB數(shù)據(jù)字典內(nèi)部SYS_VIRTUAL表的信息。該表中展示的行信息是與虛擬生成列相關聯(lián)列的每個列的信息。

（3）INNODB_SYS_INDEXES

提供查詢有關InnoDB索引的元數(shù)據(jù)信息，等同于InnoDB數(shù)據(jù)字典內(nèi)部SYS_INDEXES表中的信息。

（4）INNODB_SYS_TABLES

提供查詢有關InnoDB表的元數(shù)據(jù)信息，等同于InnoDB數(shù)據(jù)字典內(nèi)部SYS_TABLES表的信息。

（5）INNODB_SYS_FIELDS

提供查詢有關InnoDB索引鍵列（字段）的元數(shù)據(jù)信息，等同于InnoDB數(shù)據(jù)字典內(nèi)部SYS_FIELDS表的信息。

（6）INNODB_SYS_TABLESPACES

提供查詢有關InnoDB獨立表空間和普通表空間的元數(shù)據(jù)信息（也包含了全文索引表空間），等同于InnoDB數(shù)據(jù)字典內(nèi)部SYS_TABLESPACES表的信息。

（7）INNODB_SYS_FOREIGN_COLS

提供查詢有關InnoDB外鍵列的狀態(tài)信息，等同于InnoDB數(shù)據(jù)字典內(nèi)部

SYS_FOREIGN_COLS表的信息。

（8）INNODB_SYS_COLUMNS

提供查詢有關InnoDB表列的元數(shù)據(jù)信息，等同于InnoDB數(shù)據(jù)字典內(nèi)部

SYS_COLUMNS表的信息。

（9）INNODB_SYS_FOREIGN

提供查詢有關InnoDB外鍵的元數(shù)據(jù)信息，等同于InnoDB數(shù)據(jù)字典內(nèi)部SYS_FOREIGN表的信息。

（10）INNODB_SYS_TABLESTATS

提供查詢有關InnoDB表的較低級別的狀態(tài)信息視圖。 MySQL優(yōu)化器會使用這些統(tǒng)計信息數(shù)據(jù)來計算并確定在查詢InnoDB表時要使用哪個索引。這些信息保存在內(nèi)存中的數(shù)據(jù)結(jié)構(gòu)中，與存儲在磁盤上的數(shù)據(jù)無對應關系。在InnoDB內(nèi)部也無對應的系統(tǒng)表。

InnoDB層的鎖、事務、統(tǒng)計信息字典表

（1）INNODB_LOCKS

提供查詢InnoDB引擎中事務正在請求的且同時被其他事務阻塞的鎖信息（即沒有發(fā)生不同事務之間鎖等待的鎖信息，在這里是查看不到的。例如，當只有一個事務時，無法查看到該事務所加的鎖信息）。該表中的內(nèi)容可用于診斷高并發(fā)下的鎖爭用信息。

（2）INNODB_TRX

提供查詢當前在InnoDB引擎中執(zhí)行的每個事務（不包括只讀事務）的信息，包括事務是否正在等待鎖、事務什么時間點開始，以及事務正在執(zhí)行的SQL語句文本信息等（如果有SQL語句的話）。

（3）INNODB_BUFFER_PAGE_LRU

提供查詢緩沖池中的頁面信息。與INNODB_BUFFER_PAGE表不同，INNODB_BUFFER_PAGE_LRU表保存有關InnoDB緩沖池中的頁如何進入LRU鏈表，以及在緩沖池不夠用時確定需要從中逐出哪些頁的信息。

（4）INNODB_LOCK_WAITS

提供查詢InnoDB事務的鎖等待信息。如果查詢該表為空，則表示無鎖等待信息；如果查詢該表中有記錄，則說明存在鎖等待，表中的每一行記錄表示一個鎖等待關系。在一個鎖等待關系中包含：一個等待鎖（即，正在請求獲得鎖）的事務及其正在等待的鎖等信息、一個持有鎖（這里指的是發(fā)生鎖等待事務正在請求的鎖）的事務及其所持有的鎖等信息。

（5）INNODB_TEMP_TABLE_INFO

提供查詢有關在InnoDB實例中當前處于活動狀態(tài)的用戶（只對已建立連接的用戶有效，斷開的用戶連接對應的臨時表會被自動刪除）創(chuàng)建的InnoDB臨時表的信息。它不提供查詢優(yōu)化器使用的內(nèi)部InnoDB臨時表的信息。該表在首次查詢時創(chuàng)建。

（6）INNODB_BUFFER_PAGE

提供查詢關于緩沖池中的頁相關信息。

（7）INNODB_METRICS

提供查詢InnoDB更為詳細的性能信息，是對InnoDB的performance_schema的補充。通過對該表的查詢，可用于檢查InnoDB的整體健康狀況，也可用于診斷性能瓶頸、資源短缺和應用程序的問題等。

（8）INNODB_BUFFER_POOL_STATS

提供查詢一些InnoDB緩沖池中的狀態(tài)信息，該表中記錄的信息與SHOW ENGINEINNODB STATUS語句輸出的緩沖池統(tǒng)計部分信息類似。另外，InnoDB緩沖池的一些狀態(tài)變量也提供了部分相同的值。

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

這兩個表中的數(shù)據(jù)包含了與壓縮的InnoDB表頁有關的操作狀態(tài)信息。表中記錄的數(shù)據(jù)為測量數(shù)據(jù)庫中的InnoDB表壓縮的有效性提供參考。

（2）INNODB_CMP_PER_INDEX和INNODB_CMP_PER_INDEX_RESET

這兩個表中記錄了與InnoDB壓縮表數(shù)據(jù)和索引相關的操作狀態(tài)信息，對數(shù)據(jù)庫、表、索引的每個組合使用不同的統(tǒng)計信息，以便為評估特定表的壓縮性能和實用性提供參考數(shù)據(jù)。

（3）INNODB_CMPMEM和INNODB_CMPMEM_RESET

這兩個表中記錄了InnoDB緩沖池中壓縮頁的狀態(tài)信息，為測量數(shù)據(jù)庫中InnoDB表壓縮的有效性提供參考。

1.4.3.information_schema應用

查看索引列的信息

INNODB_SYS_FIELDS表提供查詢有關InnoDB索引列（字段）的元數(shù)據(jù)信息，等同于InnoDB數(shù)據(jù)字典中SYS_FIELDS表的信息。

INNODB_SYS_INDEXES表提供查詢有關InnoDB索引的元數(shù)據(jù)信息，等同于InnoDB數(shù)據(jù)字典內(nèi)部SYS_INDEXES表中的信息。

INNODB_SYS_TABLES表提供查詢有關InnoDB表的元數(shù)據(jù)信息，等同于InnoDB數(shù)據(jù)字典中SYS_TABLES表的信息。

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

則可以使用如下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';

結(jié)果中的列都很好理解，唯一需要額外解釋的是i_type(INNODB_SYS_INDEXES.type)，它是表示索引類型的數(shù)字ID：

0 =二級索引

1=集群索引

2 =唯一索引

3 =主鍵索引

32 =全文索引

64 =空間索引

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

到此這篇關于MySQL中的系統(tǒng)庫(sys系統(tǒng)庫、information_schema)調(diào)優(yōu)方法的文章就介紹到這了,更多相關mysql sys系統(tǒng)庫 information_schema介紹內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！