個人主頁：道友老李
歡迎加入社區：道友老李的學習社區

MySQL性能調優

MySQL 性能調優是一個復雜且多維度的過程，下面從數據庫設計、查詢優化、配置參數調整、硬件優化幾個方面為你介紹相關的調優方法。

數據庫設計優化

• 合理設計表結構：確保表結構遵循數據庫設計范式，減少數據冗余，同時要根據實際業務需求靈活調整，避免過度范式化導致的查詢復雜度過高。
• 選擇合適的數據類型：使用合適的數據類型可以減少存儲空間，提高查詢性能。例如，對于固定長度的字符串使用CHAR，對于可變長度的字符串使用VARCHAR；對于整數類型，根據取值范圍選擇合適的類型，如TINYINT、SMALLINT等。
• 建立適當的索引：索引可以加快數據的查找速度，但過多的索引會增加寫操作的開銷，因此需要根據查詢需求建立適當的索引。例如，對于經常用于WHERE子句、JOIN條件和ORDER BY子句的列，可以考慮創建索引。

查詢優化

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

配置參數調整

• 調整內存分配：根據服務器的硬件資源和業務需求，調整innodb_buffer_pool_size、key_buffer_size等參數，以提高緩存命中率。
• 調整日志參數：根據業務需求調整log_bin、innodb_log_file_size等參數，以平衡數據安全性和性能。

硬件優化

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

MySQL中的系統庫

1.5.Mysql中mysql系統庫

1.5.1.權限系統表

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

?　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

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';

表字段含義如下。

?　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';

開啟

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

任意執行一個查詢后

select * from mysql.general_log\G

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';

開啟

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';

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

show VARIABLES like 'slow_query_log';

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

然后我們測試一把，隨便寫一個SQL

select * from mysql.slow_log\G

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';

不過最近的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%';

可以看到，這兩個表都位于mysql系統數據庫下邊，其中：

innodb_table_stats存儲了關于表的統計數據，每一條記錄對應著一個表的統計數據。

innodb_index_stats存儲了關于索引的統計數據，每一條記錄對應著一個索引的一個統計項的統計數據。

innodb_table_stats

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

database_name 數據庫名

table_name 表名

last_update 本條記錄最后更新時間

n_rows表中記錄的條數

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

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

我們直接看一下這個表里的內容：

SELECT * FROM mysql.innodb_table_stats;

幾個重要統計信息項的值如下：

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';

先查看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 × 索引列的個數。

當需要采樣的頁面數量大于該索引的葉子節點數量的話，就直接采用全表掃描來統計索引列的不重復值數量了。所以大家可以在查詢結果中看到不同索引對應的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;

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;