當(dāng)面試官讓你聊聊MySQL數(shù)據(jù)庫性能優(yōu)化，你還是只能回答優(yōu)化sql，建索引嗎？
讓我們看看還可以從哪些方面聊聊吧。其實你還可以從網(wǎng)速、數(shù)據(jù)量、數(shù)據(jù)庫日志、內(nèi)存等問題、硬件配置，當(dāng)前占用資源、硬盤碎片或索引碎片等等諸多方面分析聊聊。
?? MySQL優(yōu)化-配置優(yōu)化
1、show variables查看MySQL服務(wù)器配置參數(shù)
1）查看及調(diào)整系統(tǒng)配置變量值
show variables;--查看一些系統(tǒng)配置變量
show variables like 'key_%';
show variables like '%cache%';--查看緩存相關(guān)參數(shù)
show variables like 'innodb_buffer_pool%';--查看緩沖池信息
show variables like 'innodb_file_per_table';--選擇是否將表數(shù)據(jù)和系統(tǒng)表空間獨立成單個文件。
2）查詢緩存相關(guān)參數(shù)：
[圖片上傳失敗...(image-c1ee16-1685674585229)]
2、show status查看MySQL服務(wù)器運行狀態(tài)值
通過下面的命令可以了解MySQL服務(wù)器運行狀態(tài)值。
show status;
show status like 'com_%';
show status like 'innodb_%';
show status like 'connections';
show status like 'slow_queries';
1）調(diào)整max_connections：
MySQL最大連接數(shù)量，默認(rèn)151。
在Linux系統(tǒng)上，如果內(nèi)存足夠且不考慮用戶等待響應(yīng)時間這些問題，MySQL理論上可以支持到萬級連接，但是通常情況下，這個值建議控制在1000以內(nèi)。
2）調(diào)整back_log：
TCP連接的積壓請求隊列大小，通常是max_connections的五分之一，最大不能超過900。
3）調(diào)整table_open_cache：
這個值應(yīng)該設(shè)置為max_connections的N倍，其中N代表每個連接在查詢時打開的表的最大個數(shù)。
4）調(diào)整innodb_lock_wait_timeout：
該參數(shù)可以控制InnoDB事務(wù)等待行鎖的時間，默認(rèn)值是50ms，對于反饋響應(yīng)要求較高的應(yīng)用，可以將這個值調(diào)小避免事務(wù)長時間掛起；
對于后臺任務(wù)，可以將這個值調(diào)大來避免發(fā)生大的回滾操作。
5）調(diào)整innodb_buffer_pool_size：
InnoDB數(shù)據(jù)和索引的內(nèi)存緩沖區(qū)大小，以字節(jié)為單位，這個值設(shè)置得越高，訪問表數(shù)據(jù)需要進(jìn)行的磁盤I/O操作就越少，如果可能甚至可以將該值設(shè)置為物理內(nèi)存大小的80%。
調(diào)優(yōu)參考計算方法：
val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
val > 95% 則考慮增大 innodb_buffer_pool_size， 建議使用物理內(nèi)存的75%
val < 95% 則考慮減小 innodb_buffer_pool_size， 建議設(shè)置為：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (102410241024)
設(shè)置命令：set global innodb_buffer_pool_size = 2097152; //緩沖池字節(jié)大小，單位kb，如果不設(shè)置，默認(rèn)為128M
設(shè)置要根據(jù)自己的實際情況來設(shè)置，如果設(shè)置的值不在合理的范圍內(nèi)，并不是設(shè)置越大越好，可能設(shè)置的數(shù)值太大體現(xiàn)不出優(yōu)化效果，反而造成系統(tǒng)的swap空間被占用，導(dǎo)致操作系統(tǒng)變慢，降低sql查詢性能。
修改配置文件的調(diào)整方法，修改my.cnf配置：
innodb_buffer_pool_size = 2147483648 #設(shè)置2G
innodb_buffer_pool_size = 2G #設(shè)置2G
innodb_buffer_pool_size = 500M #設(shè)置500M
MySQL5.7及以后版本，改參數(shù)時動態(tài)的，修改后，無需重啟MySQL，但是低版本，靜態(tài)的，修改后，需要重啟MySQL。
