mysql數據庫性能優化專題（mysql高性能優化）

本文目錄一覽：

• 1、怎樣優化“mysql數據庫”來提高“mysql性能”？
• 2、北大青鳥java培訓：mysql數據庫的優化方法？
• 3、超詳細MySQL數據庫優化
• 4、MySQL數據庫性能優化之分區分表分庫
• 5、MySQL性能調優 – 你必須了解的15個重要變量

怎樣優化“mysql數據庫”來提高“mysql性能”？

優化“mysql數據庫”來提高“mysql性能”的方法有：

1、選取最適用的字段屬性。

MySQL可以很好的支持大數據量的存取，但是一般說來，數據庫中的表越小，在它上面執行的查詢也就會越快。因此，在創建表的時候，為了獲得更好的性能，我們可以將表中字段的寬度設得儘可能小。

2、使用連接（JOIN）來代替子查詢(Sub-Queries)。

MySQL從4.1開始支持SQL的子查詢。這個技術可以使用SELECT語句來創建一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。

3、使用聯合(UNION)來代替手動創建的臨時表。  

MySQL 從4.0的版本開始支持UNION查詢，它可以把需要使用臨時表的兩條或更多的SELECT查詢合併的一個查詢中。在客戶端的查詢會話結束的時候，臨時表會被自動刪除，從而保證數據庫整齊、高效。

4、事務。

要把某個數據同時插入兩個相關聯的表中，可能會出現這樣的情況：第一個表中成功更新後，數據庫突然出現意外狀況，造成第二個表中的操作沒有完成，這樣，就會造成數據的不完整，甚至會破壞數據庫中的數據。要避免這種情況，就應該使用事務，它的作用是：要麼語句塊中每條語句都操作成功，要麼都失敗。

5、鎖定表。

儘管事務是維護數據庫完整性的一個非常好的方法，但卻因為它的獨佔性，有時會影響數據庫的性能，尤其是在很大的應用系統中。由於在事務執行的過程中，數據庫將會被鎖定，因此其它的用戶請求只能暫時等待直到該事務結束。

6、使用外鍵。

鎖定表的方法可以維護數據的完整性，但是它卻不能保證數據的關聯性。這個時候我們就可以使用外鍵。

7、使用索引 

索引是提高數據庫性能的常用方法，它可以令數據庫服務器以比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當中包含有MAX(), MIN()和ORDERBY這些命令的時候，性能提高更為明顯。

8、優化的查詢語句 

絕大多數情況下，使用索引可以提高查詢的速度，但如果SQL語句使用不恰當的話，索引將無法發揮它應有的作用。

北大青鳥java培訓：mysql數據庫的優化方法？

我們都知道，服務器數據庫的開發一般都是通過java或者是PHP語言來編程實現的，而為了提高我們數據庫的運行速度和效率，數據庫優化也成為了我們每日的工作重點，今天，福建IT培訓就一起來了解一下mysql服務器數據庫的優化方法。

為什麼要了解索引真實案例案例一：大學有段時間學習爬蟲，爬取了知乎300w用戶答題數據，存儲到mysql數據中。

那時不了解索引，一條簡單的“根據用戶名搜索全部回答的sql“需要執行半分鐘左右，完全滿足不了正常的使用。

案例二：近線上應用的數據庫頻頻出現多條慢sql風險提示，而工作以來，對數據庫優化方面所知甚少。

例如一個用戶數據頁面需要執行很多次數據庫查詢，性能很慢，通過增加超時時間勉強可以訪問，但是性能上需要優化。

索引的優點合適的索引，可以大大減小mysql服務器掃描的數據量，避免內存排序和臨時表，提高應用程序的查詢性能。

索引的類型mysql數據中有多種索引類型，primarykey，unique，normal，但底層存儲的數據結構都是BTREE;有些存儲引擎還提供hash索引，全文索引。

BTREE是常見的優化要面對的索引結構，都是基於BTREE的討論。

B-TREE查詢數據簡單暴力的方式是遍歷所有記錄;如果數據不重複，就可以通過組織成一顆排序二叉樹，通過二分查找算法來查詢，大大提高查詢性能。

而BTREE是一種更強大的排序樹，支持多個分支，高度更低，數據的插入、刪除、更新更快。

現代數據庫的索引文件和文件系統的文件塊都被組織成BTREE。

btree的每個節點都包含有key，data和只想子節點指針。

btree有度的概念d=1。

假設btree的度為d，則每個內部節點可以有n=[d+1，2d+1)個key，n+1個子節點指針。

樹的大高度為h=Logb[(N+1)/2]。

索引和文件系統中，B-TREE的節點常設計成接近一個內存頁大小(也是磁盤扇區大小)，且樹的度非常大。

這樣磁盤I/O的次數，就等於樹的高度h。

假設b=100，一百萬個節點的樹，h將只有3層。

即，只有3次磁盤I/O就可以查找完畢，性能非常高。

索引查詢建立索引後，合適的查詢語句才能大發揮索引的優勢。

另外，由於查詢優化器可以解析客戶端的sql語句，會調整sql的查詢語句的條件順序去匹配合適的索引。

超詳細MySQL數據庫優化

數據庫優化一方面是找出系統的瓶頸,提高MySQL數據庫的整體性能,而另一方面需要合理的結構設計和參數調整,以提高用戶的相應速度,同時還要儘可能的節約系統資源,以便讓系統提供更大的負荷.

1. 優化一覽圖

2. 優化

筆者將優化分為了兩大類,軟優化和硬優化,軟優化一般是操作數據庫即可,而硬優化則是操作服務器硬件及參數設置.

2.1 軟優化

2.1.1 查詢語句優化

1.首先我們可以用EXPLAIN或DESCRIBE(簡寫:DESC)命令分析一條查詢語句的執行信息.

2.例:

顯示:

其中會顯示索引和查詢數據讀取數據條數等信息.

2.1.2 優化子查詢

在MySQL中,盡量使用JOIN來代替子查詢.因為子查詢需要嵌套查詢,嵌套查詢時會建立一張臨時表,臨時表的建立和刪除都會有較大的系統開銷,而連接查詢不會創建臨時表,因此效率比嵌套子查詢高.

2.1.3 使用索引

索引是提高數據庫查詢速度最重要的方法之一,關於索引可以參高筆者MySQL數據庫索引一文,介紹比較詳細,此處記錄使用索引的三大注意事項:

2.1.4 分解表

對於字段較多的表,如果某些字段使用頻率較低,此時應當,將其分離出來從而形成新的表,

2.1.5 中間表

對於將大量連接查詢的表可以創建中間表,從而減少在查詢時造成的連接耗時.

2.1.6 增加冗餘字段

類似於創建中間表,增加冗餘也是為了減少連接查詢.

2.1.7 分析表,,檢查表,優化表

分析表主要是分析表中關鍵字的分布,檢查表主要是檢查表中是否存在錯誤,優化表主要是消除刪除或更新造成的表空間浪費.

1. 分析表: 使用 ANALYZE 關鍵字,如ANALYZE TABLE user;

2. 檢查表: 使用 CHECK關鍵字,如CHECK TABLE user [option]

option 只對MyISAM有效,共五個參數值:

3. 優化表:使用OPTIMIZE關鍵字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不寫入日誌.,優化表只對VARCHAR,BLOB和TEXT有效,通過OPTIMIZE TABLE語句可以消除文件碎片,在執行過程中會加上只讀鎖.

2.2 硬優化

2.2.1 硬件三件套

1.配置多核心和頻率高的cpu,多核心可以執行多個線程.

2.配置大內存,提高內存,即可提高緩存區容量,因此能減少磁盤I/O時間,從而提高響應速度.

3.配置高速磁盤或合理分布磁盤:高速磁盤提高I/O,分布磁盤能提高並行操作的能力.

2.2.2 優化數據庫參數

優化數據庫參數可以提高資源利用率,從而提高MySQL服務器性能.MySQL服務的配置參數都在my.cnf或my.ini,下面列出性能影響較大的幾個參數.

2.2.3 分庫分表

因為數據庫壓力過大，首先一個問題就是高峰期系統性能可能會降低，因為數據庫負載過高對性能會有影響。另外一個，壓力過大把你的數據庫給搞掛了怎麼辦？所以此時你必須得對系統做分庫分表 + 讀寫分離，也就是把一個庫拆分為多個庫，部署在多個數據庫服務上，這時作為主庫承載寫入請求。然後每個主庫都掛載至少一個從庫，由從庫來承載讀請求。

2.2.4 緩存集群

如果用戶量越來越大，此時你可以不停的加機器，比如說系統層面不停加機器，就可以承載更高的並發請求。然後數據庫層面如果寫入並發越來越高，就擴容加數據庫服務器，通過分庫分表是可以支持擴容機器的，如果數據庫層面的讀並發越來越高，就擴容加更多的從庫。但是這裡有一個很大的問題：數據庫其實本身不是用來承載高並發請求的，所以通常來說，數據庫單機每秒承載的並發就在幾千的數量級，而且數據庫使用的機器都是比較高配置，比較昂貴的機器，成本很高。如果你就是簡單的不停的加機器，其實是不對的。所以在高並發架構里通常都有緩存這個環節，緩存系統的設計就是為了承載高並發而生。所以單機承載的並發量都在每秒幾萬，甚至每秒數十萬，對高並發的承載能力比數據庫系統要高出一到兩個數量級。所以你完全可以根據系統的業務特性，對那種寫少讀多的請求，引入緩存集群。具體來說，就是在寫數據庫的時候同時寫一份數據到緩存集群里，然後用緩存集群來承載大部分的讀請求。這樣的話，通過緩存集群，就可以用更少的機器資源承載更高的並發。

一個完整而複雜的高並發系統架構中，一定會包含：各種複雜的自研基礎架構系統。各種精妙的架構設計.因此一篇小文頂多具有拋磚引玉的效果,但是數據庫優化的思想差不多就這些了.

MySQL數據庫性能優化之分區分表分庫

分表是分散數據庫壓力的好方法。

分表，最直白的意思，就是將一個表結構分為多個表，然後，可以再同一個庫里，也可以放到不同的庫。

當然，首先要知道什麼情況下，才需要分表。個人覺得單表記錄條數達到百萬到千萬級別時就要使用分表了。

分表的分類

**1、縱向分表**

將本來可以在同一個表的內容，人為劃分為多個表。（所謂的本來，是指按照關係型數據庫的第三範式要求，是應該在同一個表的。）

分表理由：根據數據的活躍度進行分離，（因為不同活躍的數據，處理方式是不同的）

案例：

對於一個博客系統，文章標題，作者，分類，創建時間等，是變化頻率慢，查詢次數多，而且最好有很好的實時性的數據，我們把它叫做冷數據。而博客的瀏覽量，回複數等，類似的統計信息，或者別的變化頻率比較高的數據，我們把它叫做活躍數據。所以，在進行數據庫結構設計的時候，就應該考慮分表，首先是縱向分表的處理。

這樣縱向分表後：

首先存儲引擎的使用不同，冷數據使用MyIsam 可以有更好的查詢數據。活躍數據，可以使用Innodb ,可以有更好的更新速度。

其次，對冷數據進行更多的從庫配置，因為更多的操作時查詢，這樣來加快查詢速度。對熱數據，可以相對有更多的主庫的橫向分表處理。

其實，對於一些特殊的活躍數據，也可以考慮使用memcache ,redis之類的緩存，等累計到一定量再去更新數據庫。或者mongodb 一類的nosql 數據庫，這裡只是舉例，就先不說這個。

**2、橫向分表**

字面意思，就可以看出來，是把大的表結構，橫向切割為同樣結構的不同表，如，用戶信息表，user_1,user_2等。表結構是完全一樣，但是，根據某些特定的規則來劃分的表，如根據用戶ID來取模劃分。

分表理由：根據數據量的規模來劃分，保證單表的容量不會太大，從而來保證單表的查詢等處理能力。

案例：同上面的例子，博客系統。當博客的量達到很大時候，就應該採取橫向分割來降低每個單表的壓力，來提升性能。例如博客的冷數據表，假如分為100個表，當同時有100萬個用戶在瀏覽時，如果是單表的話，會進行100萬次請求，而現在分表後，就可能是每個表進行1萬個數據的請求（因為，不可能絕對的平均，只是假設），這樣壓力就降低了很多很多。

延伸：為什麼要分表和分區？

日常開發中我們經常會遇到大表的情況，所謂的大表是指存儲了百萬級乃至千萬級條記錄的表。這樣的表過於龐大，導致數據庫在查詢和插入的時候耗時太長，性能低下，如果涉及聯合查詢的情況，性能會更加糟糕。分表和表分區的目的就是減少數據庫的負擔，提高數據庫的效率，通常點來講就是提高表的增刪改查效率。

什麼是分表？

分表是將一個大表按照一定的規則分解成多張具有獨立存儲空間的實體表，我們可以稱為子表，每個表都對應三個文件，MYD數據文件，.MYI索引文件，.frm表結構文件。這些子表可以分布在同一塊磁盤上，也可以在不同的機器上。app讀寫的時候根據事先定義好的規則得到對應的子表名，然後去操作它。

什麼是分區？

分區和分表相似，都是按照規則分解表。不同在於分表將大表分解為若干個獨立的實體表，而分區是將數據分段劃分在多個位置存放，可以是同一塊磁盤也可以在不同的機器。分區後，表面上還是一張表，但數據散列到多個位置了。app讀寫的時候操作的還是大表名字，db自動去組織分區的數據。

**MySQL分表和分區有什麼聯繫呢？**

1、都能提高mysql的性高，在高並髮狀態下都有一個良好的表現。

2、分表和分區不矛盾，可以相互配合的，對於那些大訪問量，並且表數據比較多的表，我們可以採取分表和分區結合的方式（如果merge這種分表方式，不能和分區配合的話，可以用其他的分表試），訪問量不大，但是表數據很多的表，我們可以採取分區的方式等。

3、分表技術是比較麻煩的，需要手動去創建子表，app服務端讀寫時候需要計算子表名。採用merge好一些，但也要創建子表和配置子表間的union關係。

4、表分區相對於分表，操作方便，不需要創建子表。

我們知道對於大型的互聯網應用，數據庫單表的數據量可能達到千萬甚至上億級別，同時面臨這高並發的壓力。Master-Slave結構只能對數據庫的讀能力進行擴展，寫操作還是集中在Master中，Master並不能無限制的掛接Slave庫，如果需要對數據庫的吞吐能力進行進一步的擴展，可以考慮採用分庫分表的策略。

**1、分表**

在分表之前，首先要選中合適的分表策略（以哪個字典為分表字段，需要將數據分為多少張表），使數據能夠均衡的分布在多張表中，並且不影響正常的查詢。在企業級應用中，往往使用org_id(組織主鍵)做為分表字段，在互聯網應用中往往是userid。在確定分表策略後，當數據進行存儲及查詢時，需要確定到哪張表裡去查找數據，

數據存放的數據表 = 分表字段的內容 % 分表數量

**2、分庫**

分表能夠解決單表數據量過大帶來的查詢效率下降的問題，但是不能給數據庫的並發訪問帶來質的提升，面對高並發的寫訪問，當Master無法承擔高並發的寫入請求時，不管如何擴展Slave服務器，都沒有意義了。我們通過對數據庫進行拆分，來提高數據庫的寫入能力，即所謂的分庫。分庫採用對關鍵字取模的方式，對數據庫進行路由。

數據存放的數據庫=分庫字段的內容%數據庫的數量

**3、即分表又分庫**

數據庫分表可以解決單表海量數據的查詢性能問題，分庫可以解決單台數據庫的並發訪問壓力問題。

當數據庫同時面臨海量數據存儲和高並發訪問的時候，需要同時採取分表和分庫策略。一般分表分庫策略如下：

中間變量 = 關鍵字%（數據庫數量*單庫數據表數量）

庫 = 取整（中間變量/單庫數據表數量）

表 = （中間變量%單庫數據表數量）

實例：

1、分庫分表

很明顯，一個主表（也就是很重要的表，例如用戶表）無限制的增長勢必嚴重影響性能，分庫與分表是一個很不錯的解決途徑，也就是性能優化途徑，現在的案例是我們有一個1000多萬條記錄的用戶表members,查詢起來非常之慢，同事的做法是將其散列到100個表中，分別從members0到members99，然後根據mid分發記錄到這些表中，牛逼的代碼大概是這樣子：

複製代碼 代碼如下:

?php

for($i=0;$i 100; $i++ ){

//echo “CREATE TABLE db2.members{$i} LIKE db1.members

“;

echo “INSERT INTO members{$i} SELECT * FROM members WHERE mid%100={$i}

“;

}

?

2、不停機修改mysql表結構

同樣還是members表，前期設計的表結構不盡合理，隨着數據庫不斷運行，其冗餘數據也是增長巨大，同事使用了下面的方法來處理：

先創建一個臨時表：

/*創建臨時表*/

CREATE TABLE members_tmp LIKE members

然後修改members_tmp的表結構為新結構，接着使用上面那個for循環來導出數據，因為1000萬的數據一次性導出是不對的，mid是主鍵，一個區間一個區間的導，基本是一次導出5萬條吧，這裡略去了

接着重命名將新表替換上去：

/*這是個頗為經典的語句哈*/

RENAME TABLE members TO members_bak,members_tmp TO members;

就是這樣，基本可以做到無損失，無需停機更新表結構，但實際上RENAME期間表是被鎖死的，所以選擇在線少的時候操作是一個技巧。經過這個操作，使得原先8G多的表，一下子變成了2G多。

MySQL性能調優 – 你必須了解的15個重要變量

前言:

MYSQL 應該是最流行了 WEB 後端數據庫。雖然 NOSQL 最近越來越多的被提到，但是相信大部分架構師還是會選擇 MYSQL 來做數據存儲。本文作者總結梳理MySQL性能調優的15個重要變量，又不足需要補充的還望大佬指出。

1.DEFAULT_STORAGE_ENGINE

如果你已經在用MySQL 5.6或者5.7，並且你的數據表都是InnoDB，那麼表示你已經設置好了。如果沒有，確保把你的錶轉換為InnoDB並且設置default_storage_engine為InnoDB。

為什麼？簡而言之，因為InnoDB是MySQL(包括Percona Server和MariaDB)最好的存儲引擎 – 它支持事務，高並發，有着非常好的性能表現(當配置正確時)。這裡有詳細的版本介紹為什麼

2.INNODB_BUFFER_POOL_SIZE

這個是InnoDB最重要變量。實際上，如果你的主要存儲引擎是InnoDB，那麼對於你，這個變量對於MySQL是最重要的。

基本上，innodb_buffer_pool_size指定了MySQL應該分配給InnoDB緩衝池多少內存，InnoDB緩衝池用來存儲緩存的數據，二級索引，臟數據(已經被更改但沒有刷新到硬盤的數據)以及各種內部結構如自適應哈希索引。

根據經驗，在一個獨立的MySQL服務器應該分配給MySQL整個機器總內存的80%。如果你的MySQL運行在一個共享服務器，或者你想知道InnoDB緩衝池大小是否正確設置，詳細請看這裡。

3.INNODB_LOG_FILE_SIZE

InnoDB重做日誌文件的設置在MySQL社區也叫做事務日誌。直到MySQL 5.6.8事務日誌默認值innodb_log_file_size=5M是唯一最大的InnoDB性能殺手。從MySQL 5.6.8開始，默認值提升到48M,但對於許多稍繁忙的系統，還遠遠要低。

根據經驗，你應該設置的日誌大小能在你服務器繁忙時能存儲1-2小時的寫入量。如果不想這麼麻煩，那麼設置1-2G的大小會讓你的性能有一個不錯的表現。這個變量也相當重要，更詳細的介紹請看這裡。

當然，如果你有大量的大事務更改，那麼，更改比默認innodb日誌緩衝大小更大的值會對你的性能有一定的提高，但是你使用的是autocommit，或者你的事務更改小於幾k，那還是保持默認的值吧。

4.INNODB_FLUSH_LOG_AT_TRX_COMMIT

默認下，innodb_flush_log_at_trx_commit設置為1表示InnoDB在每次事務提交後立即刷新同步數據到硬盤。如果你使用autocommit，那麼你的每一個INSERT, UPDATE或DELETE語句都是一個事務提交。

同步是一個昂貴的操作(特別是當你沒有寫回緩存時)，因為它涉及對硬盤的實際同步物理寫入。所以如果可能，並不建議使用默認值。

兩個可選的值是0和2:

* 0表示刷新到硬盤，但不同步(提交事務時沒有實際的IO操作)

* 2表示不刷新和不同步(也沒有實際的IO操作)

所以你如果設置它為0或2，則同步操作每秒執行一次。所以明顯的缺點是你可能會丟失上一秒的提交數據。具體來說，你的事務已經提交了，但服務器馬上斷電了，那麼你的提交相當於沒有發生過。

顯示的，對於金融機構，如銀行，這是無法忍受的。不過對於大多數網站，可以設置為innodb_flush_log_at_trx_commit=0|2，即使服務器最終崩潰也沒有什麼大問題。畢竟，僅僅在幾年前有許多網站還是用MyISAM，當崩潰時會丟失30s的數據(更不要提那令人抓狂的慢修復進程)。

那麼，0和2之間的實際區別是什麼？性能明顯的差異是可以忽略不計，因為刷新到操作系統緩存的操作是非常快的。所以很明顯應該設置為0，萬一MySQL崩潰(不是整個機器)，你不會丟失任何數據，因為數據已經在OS緩存，最終還是會同步到硬盤的。

5.SYNC_BINLOG

已經有大量的文檔寫到sync_binlog，以及它和innodb_flush_log_at_trx_commit的關係，下面我們來簡單的介紹下：

a) 如果你的服務器沒有設置從服務器，而且你不做備份，那麼設置sync_binlog=0將對性能有好處。

b) 如果你有從服務器並且做備份，但你不介意當主服務器崩潰時在二進制日誌丟失一些事件，那麼為了更好的性能還是設置為sync_binlog=0.

c) 如果你有從服務器並且備份，你非常在意從服務器的一致性，以及能及時恢復到一個時間點(通過使用最新的一致性備份和二進制日誌將數據庫恢復到特定時間點的能力)，那麼你應該設置innodb_flush_log_at_trx_commit=1，並且需要認真考慮使用sync_binlog=1。

問題是sync_binlog=1代價比較高 – 現在每個事務也要同步一次到硬盤。你可能會想為什麼不把兩次同步合併成一次，想法正確 – 新版本的MySQL(5.6和5.7，MariaDB和Percona Server)已經能合併提交，那麼在這種情況下sync_binlog=1的操作也不是這麼昂貴了，但在舊的mysql版本中仍然會對性能有很大影響。

6.INNODB_FLUSH_METHOD

將innodb_flush_method設置為O_DIRECT以避免雙重緩衝.唯一一種情況你不應該使用O_DIRECT是當你操作系統不支持時。但如果你運行的是Linux，使用O_DIRECT來激活直接IO。

不用直接IO，雙重緩衝將會發生，因為所有的數據庫更改首先會寫入到OS緩存然後才同步到硬盤 – 所以InnoDB緩衝池和OS緩存會同時持有一份相同的數據。特別是如果你的緩衝池限制為總內存的50%，那意味着在寫密集的環境中你可能會浪費高達50%的內存。如果沒有限制為50%，服務器可能由於OS緩存的高壓力會使用到swap。

簡單地說，設置為innodb_flush_method=O_DIRECT。

7.INNODB_BUFFER_POOL_INSTANCES

MySQL 5.5引入了緩衝實例作為減小內部鎖爭用來提高MySQL吞吐量的手段。

在5.5版本這個對提升吞吐量幫助很小，然後在MySQL 5.6版本這個提升就非常大了，所以在MySQL5.5中你可能會保守地設置innodb_buffer_pool_instances=4，在MySQL 5.6和5.7中你可以設置為8-16個緩衝池實例。

你設置後觀察會覺得性能提高不大，但在大多數高負載情況下，它應該會有不錯的表現。

對了，不要指望這個設置能減少你單個查詢的響應時間。這個是在高並發負載的服務器上才看得出區別。比如多個線程同時做許多事情。

8.INNODB_THREAD_CONCURRENCY

InnoDB有一種方法來控制並行執行的線程數 – 我們稱為並發控制機制。大部分是由innodb_thread_concurrency值來控制的。如果設置為0，並發控制就關閉了，因此InnoDB會立即處理所有進來的請求(儘可能多的)。

在你有32CPU核心且只有4個請求時會沒什麼問題。不過想像下你只有4CPU核心和32個請求時 – 如果你讓32個請求同時處理，你這個自找麻煩。因為這些32個請求只有4 CPU核心，顯然地會比平常慢至少8倍(實際上是大於8倍)，而然這些請求每個都有自己的外部和內部鎖，這有很大可能堆積請求。

下面介紹如何更改這個變量，在mysql命令行提示符執行：

對於大多數工作負載和服務器，設置為8是一個好開端，然後你可以根據服務器達到了這個限制而資源使用率利用不足時逐漸增加。可以通過show engine innodb status\G來查看目前查詢處理情況，查找類似如下行：

9.SKIP_NAME_RESOLVE

這一項不得不提及，因為仍然有很多人沒有添加這一項。你應該添加skip_name_resolve來避免連接時DNS解析。

大多數情況下你更改這個會沒有什麼感覺，因為大多數情況下DNS服務器解析會非常快。不過當DNS服務器失敗時，它會出現在你服務器上出現“unauthenticated connections” ，而就是為什麼所有的請求都突然開始慢下來了。

所以不要等到這種事情發生才更改。現在添加這個變量並且避免基於主機名的授權。

10.INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX

* innodb_io_capacity：用來當刷新臟數據時，控制MySQL每秒執行的寫IO量。

* innodb_io_capacity_max: 在壓力下，控制當刷新臟數據時MySQL每秒執行的寫IO量

首先，這與讀取無關 – SELECT查詢執行的操作。對於讀操作，MySQL會盡最大可能處理並返回結果。至於寫操作，MySQL在後台會循環刷新，在每一個循環會檢查有多少數據需要刷新，並且不會用超過innodb_io_capacity指定的數來做刷新操作。這也包括更改緩衝區合併（在它們刷新到磁盤之前，更改緩衝區是輔助臟頁存儲的關鍵）。

第二，我需要解釋一下什麼叫“在壓力下”，MySQL中稱為”緊急情況”，是當MySQL在後台刷新時，它需要刷新一些數據為了讓新的寫操作進來。然後，MySQL會用到innodb_io_capacity_max。

那麼，應該設置innodb_io_capacity和innodb_io_capacity_max為什麼呢？

最好的方法是測量你的存儲設置的隨機寫吞吐量，然後給innodb_io_capacity_max設置為你的設備能達到的最大IOPS。innodb_io_capacity就設置為它的50-75%，特別是你的系統主要是寫操作時。

通常你可以預測你的系統的IOPS是多少。例如由8 15k硬盤組成的RAID10能做大約每秒1000隨機寫操作，所以你可以設置innodb_io_capacity=600和innodb_io_capacity_max=1000。許多廉價企業SSD可以做4,000-10,000 IOPS等。

這個值設置得不完美問題不大。但是，要注意默認的200和400會限制你的寫吞吐量，因此你可能偶爾會捕捉到刷新進程。如果出現這種情況，可能是已經達到你硬盤的寫IO吞吐量，或者這個值設置得太小限制了吞吐量。

11.INNODB_STATS_ON_METADATA

如果你跑的是MySQL 5.6或5.7，你不需要更改innodb_stats_on_metadata的默認值，因為它已經設置正確了。

不過在MySQL 5.5或5.1，強烈建議關閉這個變量 – 如果是開啟，像命令show table status會立即查詢INFORMATION_SCHEMA而不是等幾秒再執行，這會使用到額外的IO操作。

從5.1.32版本開始，這個是動態變量，意味着你不需要重啟MySQL服務器來關閉它。

12.INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN INNODB_BUFFER_POOL_LOAD_AT_STARTUP

innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup這兩個變量與性能無關，不過如果你偶爾重啟mysql服務器(如生效配置)，那麼就有關。當兩個都激活時，MySQL緩衝池的內容(更具體地說，是緩存頁)在停止MySQL時存儲到一個文件。當你下次啟動MySQL時，它會在後台啟動一個線程來加載緩衝池的內容以提高預熱速度到3-5倍。

兩件事：

第一，它實際上沒有在關閉時複製緩衝池內容到文件，僅僅是複製表空間ID和頁面ID – 足夠的信息來定位硬盤上的頁面了。然後它就能以大量的順序讀非常快速的加載那些頁面，而不是需要成千上萬的小隨機讀。

第二，啟動時是在後台加載內容，因為MySQL不需要等到緩衝池內容加載完成再開始接受請求(所以看起來不會有什麼影響)。

從MySQL 5.7.7開始，默認只有25%的緩衝池頁面在mysql關閉時存儲到文件，但是你可以控制這個值 – 使用innodb_buffer_pool_dump_pct，建議75-100。

這個特性從MySQL 5.6才開始支持。

13.INNODB_ADAPTIVE_HASH_INDEX_PARTS

如果你運行着一個大量SELECT查詢的MySQL服務器(並且已經儘可能優化)，那麼自適應哈希索引將下你的下一個瓶頸。自適應哈希索引是InnoDB內部維護的動態索引，可以提高最常用的查詢模式的性能。這個特性可以重啟服務器關閉，不過默認下在mysql的所有版本開啟。

這個技術非常複雜，在大多數情況下它會對大多數類型的查詢直到加速的作用。不過，當你有太多的查詢往數據庫，在某一個點上它會花過多的時間等待AHI鎖和閂鎖。

如果你的是MySQL 5.7，沒有這個問題 – innodb_adaptive_hash_index_parts默認設置為8，所以自適應哈希索引被切割為8個分區，因為不存在全局互斥。

不過在mysql 5.7前的版本，沒有AHI分區數量的控制。換句話說，有一個全局互斥鎖來保護AHI，可能導致你的select查詢經常撞牆。

所以如果你運行的是5.1或5.6，並且有大量的select查詢，最簡單的方案就是切換成同一版本的Percona Server來激活AHI分區。

14.QUERY_CACHE_TYPE

如果人認為查詢緩存效果很好，肯定應該使用它。好吧，有時候是有用的。不過這個只在你在低負載時有用，特別是在低負載下大多數是讀取，小量寫或者沒有。

如果是那樣的情況，設置query_cache_type=ON和query_cache_size=256M就好了。不過記住不能把256M設置更高的值了，否則會由於查詢緩存失效時，導致引起嚴重的服務器停頓。

如果你的MySQL服務器高負載動作，建議設置query_cache_size=0和query_cache_type=OFF，並重啟服務器生效。那樣Mysql就會停止在所有的查詢使用查詢緩存互斥鎖。

15.TABLE_OPEN_CACHE_INSTANCES

從MySQL 5.6.6開始，表緩存能分割到多個分區。

表緩存用來存放目前已打開表的列表，當每一個表打開或關閉互斥體就被鎖定 – 即使這是一個隱式臨時表。使用多個分區絕對減少了潛在的爭用。

從MySQL 5.7.8開始，table_open_cache_instances=16是默認的配置。

歡迎做Java的工程師朋友們私信我資料免費獲取免費的Java架構學習資料（裡面有高可用、高並發、高性能及分布式、Jvm性能調優、Spring源碼，MyBatis，Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多個知識點的架構資料）

其中覆蓋了互聯網的方方面面，期間碰到各種產品各種場景下的各種問題，很值得大家借鑒和學習，擴展自己的技術廣度和知識面。