數據庫調優-數據庫優化

數據庫優化

如何發現復雜的SQL有問題？ 一個個去explain嗎？你有沒有這樣的困惑，開發代碼運行順暢絲滑，上生產了卻卡的一逼？

哈哈，相信大家都會遇到這樣的問題！

sql

復制代碼

# 舉個栗子：查詢 20 秒 explain select tk.id,ts.* from t_goods ts LEFT JOIN t_sku tk ON tk.id=ts.id where ts.id>100 order by ts.price;

2.1 慢查詢日志

數據庫查詢快慢是影響項目性能的一大因素，對于數據庫，我們除了要優化 SQL，更重要的是得先找到需要優化的SQL。

MySQL數據庫“ 慢查詢日志 ”功能，用來記錄查詢時間超過某個設定值的SQL語句，這將極大程度幫助我們快速定位到癥結所在，以便對癥下藥。

嗯嗯，好像很有道理喔！！！

至于查詢時間的多少才算慢，每個項目、業務都有不同的要求。MySQL的慢查詢日志功能默認是關閉的，需要手動開啟。

查看是否開啟慢查詢功能

sql

復制代碼

# 查看是否開啟慢查詢日志 show variables like '%slow_query%'; show variables like 'long_query_time%';

【slow_query_log】：是否開啟慢查詢日志， 1 為開啟， 0 為關閉。
【log-slow-queries】：舊版（5.6以下版本）MySQL數據庫慢查詢日志存儲路徑。可以不設置該參數，系統則會默認給一個缺省的文件host_name-slow.log
【slow-query-log-file】：新版（5.6及以上版本）MySQL數據庫慢查詢日志存儲路徑。可以不設置該參數，系統則會默認給一個缺省的文件host_name-slow.log
【long_query_time】：慢查詢閾值，當查詢時間多于設定的閾值時，記錄日志，【單位為秒】。

開啟慢查詢功能

注意：打開慢查詢日志可能會對系統性能有一點點影響，如果你的MySQL是主-從結構，可以考慮打開其中一臺從服務器的慢查詢日志，這樣既可以監控慢查詢，對系統性能影響又小。

哈哈，這個大多數人都不會考慮到，還不拿個小本本記下來？

ini

復制代碼

# 開啟慢查詢日志 set global slow_query_log=on; ? # 大于 1 秒鐘的數據記錄到慢日志中，如果設置為默認 0 ，則會有大量的信息存儲在磁盤中，磁盤很容易滿掉 set global long_query_time=1; ? # 記錄沒有索引的查詢。 set global log_queries_not_using_indexes=on;

慢查詢日志

ini

復制代碼

# Time: 2022-07-29T23:59:41.539068Z # User@Host: root[root] @ [192.168.4.1] Id: 10 # Query_time: 2.000222 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1621900781; SELECT SLEEP(2);

日志解析：

第一行,SQL查詢執行的具體時間

第二行,執行SQL查詢的連接信息，用戶和連接IP

第三行,記錄了一些我們比較有用的信息，如下解析

scss

復制代碼

Query_time,這條SQL執行的時間,越長則越慢 Lock_time,在MySQL服務器階段(不是在存儲引擎階段)等待表鎖時間 Rows_sent,查詢返回的行數 Rows_examined,查詢檢查的行數，越長就當然越費時間

第四行,設置時間戳，沒有實際意義，只是和第一行對應執行時間。
第五行及后面所有行（第二個# Time:之前）,執行的sql語句記錄信息，因為sql可能會很長。

2.2 連接數max_connections

同時連接客戶端的最大數量，默認值 151 ，最小值1.

連接數導致問題：ERROR 1040，TooManyConnections原因如下

第一：訪問確實太高，MySQL有點扛不住了，考慮擴容
第二：MySQL的max_connection配置少了

csharp

復制代碼

# 查看 max_connections show global variables like 'max_connections' # 設置 max_connections（立即生效重啟后失效） set global max_connections=800;

sql

復制代碼

# 這臺MySQL服務器最大連接數是 256 ，然后查詢一下服務器使用過的最大連接數： show global status like 'Max_used_connections';

erlang

復制代碼

# MySQL服務器過去的最大連接數是 245 ，沒有達到服務器連接數上限 256 ，應該沒有出現 1040 錯誤，比較理想的設置是：Max_used_connections / max_connections * 100% ≈ 85% 最大連接數占上限連接數的85%左右，如果發現比例在10%以下，MySQL服務器連接數上限設置的過高了。

這里：最大使用過連接數/最大連接數 ，這個比值保持在85%，就比較理想。

干貨喔！！！

2.3 線程使用情況

如果我們在MySQL服務器配置文件中設置了thread_cache_size，當客戶端斷開之后，服務器處理此客戶的線程將會緩存起來以響應下一個客戶而不是銷毀(前提是緩存數未達上限)。

根據測試發現，以上服務器線程緩存thread_cache_size沒有進行設置，或者設置過小，MySQL服務器一直在創建線程銷毀線程。增加這個值可以改善系統性能。通過比較 Connections 和 Threads_created狀態的變量，可以看到這個變量的作用。

Threads_created表示創建過的線程數，如果發現Threads_created值過大的話，表明MySQL服務器一直在創建線程，這也是比較耗資源，可以適當增加配置文件中thread_cache_size值，查詢服務器thread_cache_size配置：

sql

復制代碼

# 查詢線程使用情況 show global status like 'Thread%'; # 查詢線程緩存 show variables like 'thread_cache_size'; # 增加thread_cache_size的值 set global thread_cache_size = 64;

根據物理內存建議設置規則如下：

1G ---> 8
2G ---> 16
3G ---> 32
大于3G ---> 64

又是一大干貨喔！！！

2.4 數據庫優化-結構優化

一個好的數據庫設計方案對于數據庫的性能往往會起到事半功倍的效果。這句話是什么意思呢？

就是說我們的數據庫優化不僅僅要局限于查詢優化，要從這塊跳出來做好最開始的設計優化，如果你這個主要設計是不合理的這些個查詢優化效果也只是杯水車薪。

需要考慮數據冗余、查詢和更新的速度、字段的數據類型是否合理等多方面的內容。

2.6.1. 將字段很多的表分解成多個表（分表）

對于字段較多的表，如果有些字段的使用頻率很低，可以將這些字段分離出來形成新表。

因為當一個表的數據量很大時，會由于使用頻率低的字段的存在而變慢。

項目實戰的時候會將一個完全信息的表里面的數據拆分出來形成多個新表每個新表負責那一塊的數據查詢然后這個拆分是定時的

2.6.2. 增加中間表

對于需要經常聯合查詢的表，可以建立中間表以提高查詢效率。通過建立中間表，將需要通過聯合查詢的數據插入到中間表中，然后將原來的聯合查詢改為對中間表的查詢。

通常都是在統計當中有使用啊，每次統計報表的時候都是離線統計啊，后臺有有一個線程對你這統計結果查詢號放入一個中間表，然后你對這個中間表查詢就行了。

2.6.3. 增加冗余字段

設計數據表時應盡量遵循范式理論的規約，盡可能的減少冗余字段，讓數據庫設計看起來精致、優雅。

但是，合理的加入冗余字段可以提高查詢速度。

表的規范化程度越高，表和表之間的關系越多，需要連接查詢的情況也就越多，性能也就越差。

注意：

冗余字段的值在一個表中修改了，就要想辦法在其他表中更新，否則就會導致數據不一致的問題。

2.7 MySQL其他的配置參數【my.conf/my.ini文件】

通過優化MySQL的參數可以提高資源利用率，從而達到提高MySQL服務器性能的目的。

MySQL的配置參數都在my.conf或者my.ini文件的[mysqld]組中，常用的參數如下：

ini

復制代碼

# 01-緩沖區，將數據保存在內存中，保證從內存讀取數據。建議innodb_buffer_pool_size設置為總內存大小的3/4或者4/5. innodb_buffer_pool_size= ? # 02-降低磁盤寫入次數。推薦 innodb_log_file_size 設置為 0.25 * innodb_buffer_pool_size innodb_log_file_size= ? # 03-表示緩沖池字節大小。推薦值為物理內存的50%~80%。 innodb_buffer_pool_size= ? # 04-用來控制redo log刷新到磁盤的策略。 innodb_flush_log_at_trx_commit=1 ? # 05-每提交 1 次事務同步寫到磁盤中，可以設置為n。 sync_binlog=1 ? # 06-臟頁占innodb_buffer_pool_size的比例時，觸發刷臟頁到磁盤。推薦值為25%~50%。 innodb_max_dirty_pages_pct=30 ? # 07-后臺進程最大IO性能指標。默認 200 ，如果SSD，調整為5000~20000 innodb_io_capacity=200 ? # 08-指定innodb共享表空間文件的大小。 innodb_data_file_path ? # 09-慢查詢日志的閾值設置，單位秒。 long_qurey_time=0.3 ? # 10-mysql復制的形式，row為MySQL8.0的默認形式。 binlog_format=row ? # 11-調高該參數則應降低interactive_timeout、wait_timeout的值。 max_connections=200 ? # 12-過大，實例恢復時間長；過小，造成日志切換頻繁。 innodb_log_file_size ? # 13-全量日志建議關閉。默認關閉。 general_log=0