MySQL8.0性能優(yōu)化

點擊上方藍(lán)色字體，選擇“標(biāo)星公眾號”

優(yōu)質(zhì)文章，第一時間送達(dá)

  作者 |  Sol·wang

來源 |  urlify.cn/yArUf2

MySQL8.0 引擎：

來看看MySQL8提供的引擎：

常用引擎：

InnoDB：支持事務(wù)，行級鎖，外鍵，崩潰修復(fù)，多版本并發(fā)控制；讀寫效率相對較差，內(nèi)存使用相對較高，占用數(shù)據(jù)空間相對較大。

MyISAM：不支持事務(wù)，不支持外鍵，僅支持非聚集索引，支持全文索引，僅支持到表級鎖，支持?jǐn)?shù)據(jù)壓縮，占用空間相對小，內(nèi)存使用相對較低，讀寫性能相對極佳。

Memory：依賴于內(nèi)存空間，數(shù)據(jù)處理速度快，僅支持到表級鎖。

應(yīng)用場景：

InnoDB：依賴于 事務(wù)，回滾，并發(fā)，大數(shù)據(jù)量，外鍵，行級鎖 的場景。

MyISAM：過多的大數(shù)據(jù)量的頻繁的查詢優(yōu)勢。

Memory：臨時性的，大數(shù)據(jù)量表的查詢優(yōu)勢。

在創(chuàng)建表的時候，可依據(jù)應(yīng)用場景選擇合適的引擎。

分表 / 拆表 / 分庫 / 分盤

分表：解決單表數(shù)據(jù)量過大的性能瓶頸，小范圍的數(shù)據(jù)處理，避免過多數(shù)據(jù)掃描；可按業(yè)務(wù)類型數(shù)據(jù)、時間跨度等實際場景分表。

拆表：按場景合理的將大表分為多個小表來降低鎖競爭

分庫：分實例分別各自處理，量與性能的分散優(yōu)化處理，欠缺的事務(wù)一致性，可按實際場景合理分庫。

分盤：主要解決磁盤IO瓶頸，多磁盤分散并行運(yùn)行。

索引

索引分類

普通索引：無限制

主鍵索引：表中只能有一個，不能為NULL

唯一索引：值不能重復(fù)

全文索引：僅MyISAM支持，僅支持 char、varchar、text 類型

組合索引：多列一起創(chuàng)建的合并索引，非單列分別創(chuàng)建的索引

所有存儲引擎對每個表至少支持了16個索引。

索引設(shè)計原則：

為經(jīng)常需要排序、分組或聯(lián)合操作的字段建立索引，經(jīng)常需要使用 order by、group by、distinct、uninon 等的操作字段

為經(jīng)常查詢出的列建立索引，為經(jīng)常作為查詢條件的字段建立索引

推薦長度較少的列建索引，推薦列使用固定長度。

過多的索引建立對表數(shù)據(jù)變更操作的性能下降的影響

刪除不再使用或很少使用的索引，減少索引對更新時的影響

索引覆蓋，索引下推，避免回表查詢（以下介紹）

索引命中：

依據(jù)索引查詢，查詢條件常以索引列開始

組合索引的最左原則：必須以組合索引列的首列開始的條件查詢，按序依次。

索引覆蓋，推薦要查詢出的字段全部為索引列。假如頁面列表呈現(xiàn)出個別主要的字段內(nèi)容的場景；具體的詳細(xì)內(nèi)容在詳細(xì)頁呈現(xiàn)，透過主鍵查詢單條數(shù)據(jù)。

避免回表查詢：MySQL首先查出帶索引的列數(shù)據(jù)，再透過主鍵列去查詢非索引列的數(shù)據(jù)信息，把兩次查詢的數(shù)據(jù)組合后返回客戶端。所以推薦索引覆蓋。

腳本優(yōu)化

盡量避免嵌套子查詢，改用JOIN方式。

盡量減少 查詢中的全表掃描次數(shù) ，尤其是對于大表，如采用EXISTS、WHERE的條件順序等。

避免字段以難以理解的方式轉(zhuǎn)換查詢，采用更為合理的轉(zhuǎn)換方式。

去除不必要的括號，避免復(fù)雜邏輯查詢。如 (1=1 and (b>a and b=c)) and a=5 推薦為 b>5 and b=c and a=5

簡化減少WHERE條件范圍區(qū)間的重疊部分。如 (key1 < 'abc' and 1=1) or (key1 < 'bar') or (false) 推薦為 key1 < 'bar'

避免WHERE后用函數(shù)臨時的計算，可事先生成結(jié)果列或虛擬列。

推薦WHERE中首次出現(xiàn)的IS NULL賦予更大的作用，因為MySQL8僅對首次出現(xiàn)的IS NULL做大量優(yōu)化。

某些場景對索引的失效或破壞，F(xiàn)ORCE INDEX：指明優(yōu)先使用的索引并生效；常用于JOIN。

IN的嵌套查詢改為EXISTS的嵌套查詢。

INNER JOIN 時，STRAIGHT_JOIN 指明優(yōu)先檢索的主表，使其特定場景中達(dá)到我們的預(yù)期效果。

被嵌套的查詢更多的篩選和處理，使其減少外表查詢的數(shù)據(jù)基數(shù)。

批量INSERT使用包含多個VALUES列表的語句一次插入多行，量越大效果越明顯。（加大 bulk_insert_buffer_size、Max_allowed_packet、Net_buffer_length 的值，滿足更大量的處理）

推薦默認(rèn)值列，非顯示的插入減少必要的解析。

某些場景下，replace into 的使用，代替 insert/update，成為單一的原子操作。

SQL片段WITH AS的運(yùn)用，Memory Table 的利用。

查詢僅返回需要的字段，避免 *，避免回表查詢；僅返回需要的數(shù)據(jù)量。

InnoDB緩沖池

一個稱為緩沖池的存儲區(qū)域，用于在內(nèi)存中緩存數(shù)據(jù)和索引，利用它將經(jīng)常訪問的數(shù)據(jù)保存在內(nèi)存中，減少了SQL執(zhí)行及磁盤IO的資源消耗。為了更多的需要暫存空間，滿足更大數(shù)據(jù)量的暫存。

參數(shù)設(shè)置：

innodb_buffer_pool_size：緩沖池的承載總量，建議設(shè)為系統(tǒng)內(nèi)存的50%-70%

innodb_buffer_pool_chunk_size：緩沖池每塊大小，默認(rèn)128M

innodb_buffer_pool_instances：多線程緩沖池實例并行運(yùn)行，默認(rèn)1實例，最大64實例

設(shè)置規(guī)則：

innodb_buffer_pool_size = （innodb_buffer_pool_chunk_size * {N}塊 ）* innodb_buffer_pool_instances

也就是說，innodb_buffer_pool_size 必須是 innodb_buffer_pool_chunk_size 的倍數(shù)

舉例說明：

符合的例子：innodb_buffer_pool_size=8G，innodb_buffer_pool_chunk_size=128M，innodb_buffer_pool_instances=16

不符的例子：innodb_buffer_pool_size=9G，innodb_buffer_pool_chunk_size=128M，innodb_buffer_pool_instances=16

對于以上非倍數(shù)的狀況：

MySQL會自動將 innodb_buffer_pool_size 調(diào)整為 innodb_buffer_pool_chunk_size 的倍數(shù)；所以會變?yōu)?innodb_buffer_pool_size = 10G

運(yùn)行機(jī)制：

緩沖區(qū)分為 熱數(shù)據(jù)區(qū) / 冷數(shù)據(jù)區(qū)，兩者空間占比約為 7/3，每區(qū)中的數(shù)據(jù)集依使用頻率按順序依次排列。

當(dāng)一個新的查詢結(jié)果出現(xiàn)后，首先考慮存放到冷數(shù)據(jù)區(qū)，當(dāng)冷數(shù)據(jù)區(qū)的結(jié)果集使用達(dá)到一定頻率，會被改存到熱數(shù)據(jù)區(qū)，使用頻率最好的數(shù)據(jù)集會被存放到熱區(qū)的首位，當(dāng)然也有熱區(qū)轉(zhuǎn)到冷區(qū)的狀況。

MySQL8 去除了 [查詢緩存] query_cache_type、query_cache_size、query_cache_limit

連接池

MySQL連接器中的連接池，用以提高數(shù)據(jù)庫密集型應(yīng)用程序的性能和可擴(kuò)展性。默認(rèn)啟用。MySQL連接器負(fù)責(zé)管理連接池中的多個連接，自動創(chuàng)建、打開、關(guān)閉和破壞連接，多個連接的創(chuàng)建，可滿足多客戶端的頻繁連接，連接的重復(fù)使用獲得最佳性能。
MySQL連接器 每三分鐘運(yùn)行一次后臺作業(yè)，并從池中刪除閑置（未使用）超過三分鐘的連接。池清理釋放客戶端和服務(wù)器端的資源。這是因為在客戶端每個連接都使用一個Socket，而在服務(wù)器端每個連接都使用一個Socket和一個線程。

透過連接字符串參數(shù)對連接池的設(shè)定調(diào)整其性能特點：

• 開啟連接池：Pooling=true，默認(rèn)開啟

• 復(fù)用時重置連接狀態(tài)：ConnectionReset=True

• 保持連接設(shè)置：CacheServerProperties=True

• 連接超時回收(秒)：ConnectionLifeTime=300

• 支持的最大連接數(shù)量：Max Pool Size=100

• 保持最小的連接數(shù)量：Min Pool Size=10

日志

MySQL在運(yùn)行時，會有各種不同日志的記錄，大量的各種類型的日志產(chǎn)生，會對資源的開銷產(chǎn)生嚴(yán)重的影響，必要的時候我們選擇性的開啟。但在生產(chǎn)環(huán)境時，有些日志并不是必須，以下列出MySQL各種日志信息：

• 錯誤日志：啟動、關(guān)閉、運(yùn)行時 產(chǎn)生的異常記錄，建議開啟，設(shè)置 log_error

• 查詢?nèi)罩荆嚎蛻舳诉B接和執(zhí)行的腳本，建議關(guān)閉，設(shè)置 general_log

• 慢查詢?nèi)罩荆河涗洺瑫r的查詢，記錄不適用索引的查詢等，建議關(guān)閉，設(shè)置 slow_query_log

• 二進(jìn)制日志：用于數(shù)據(jù)同步復(fù)制，需發(fā)送的數(shù)據(jù)日志，多用于集群，如需開啟，設(shè)置 log_bin

• 中繼日志：用于數(shù)據(jù)同步復(fù)制時，接收到的數(shù)據(jù)日志，多用于集群，如需開啟，設(shè)置 relay_log