mysql在資料庫級別進行優化,mysql資料庫性能優化詳解

本文目錄一覽：

• 1、mysql 優化包括哪些內容？
• 2、mysql資料庫如何優化？誰能給出點具體的解決方案？
• 3、超詳細MySQL資料庫優化
• 4、MySQL資料庫性能優化之分區分表分庫

mysql 優化包括哪些內容？

mysql的優化大的有兩方面：

1、配置優化

配置的優化其實包含兩個方面的：操作系統內核的優化和mysql配置文件的優化

1)系統內核的優化對專用的mysql伺服器來說，無非是內存實用、連接數、超時處理、TCP處理等方面的優化，根據自己的硬體配置來進行優化，這裡不多講；

2)mysql配置的優化，一般來說包含：IO處理的常用參數、最大連接數設置、緩存使用參數的設置、慢日誌的參數的設置、innodb相關參數的設置等，如果有主從關係在設置主從同步的相關參數即可，網上的相關配置文件很多，大同小異，常用的設置大多修改這些差不多就夠用了。

2、sql語句的優化

1）  盡量稍作計算

Mysql的作用是用來存取數據的，不是做計算的，做計算的話可以用其他方法去實現，mysql做計算是很耗資源的。

2）盡量少 join

MySQL 的優勢在於簡單，但這在某些方面其實也是其劣勢。MySQL 優化器效率高，但是由於其統計信息的量有限，優化器工作過程出現偏差的可能性也就更多。對於複雜的多表 Join，一方面由於其優化器受限，再者在 Join 這方面所下的功夫還不夠，所以性能表現離 Oracle 等關係型資料庫前輩還是有一定距離。但如果是簡單的單表查詢，這一差距就會極小甚至在有些場景下要優於這些資料庫前輩

3）盡量少排序

排序操作會消耗較多的 CPU 資源，所以減少排序可以在緩存命中率高等 IO 能力足夠的場景下會較大影響 SQL的響應時間。

對於MySQL來說，減少排序有多種辦法，比如：

通過利用索引來排序的方式進行優化

減少參與排序的記錄條數

非必要不對數據進行排序

4）盡量避免 select *

在數據量少並且訪問量不大的情況下，select * 沒有什麼影響，但是量級達到一定級別的時候，在執行效率和IO資源的使用上，還是有很大關係的，用什麼欄位取什麼欄位，減少不必要的資源浪費。

5）盡量用 join 代替子查詢

雖然 Join 性能並不佳，但是和 MySQL 的子查詢比起來還是有非常大的性能優勢。MySQL 的子查詢執行計劃一直存在較大的問題，雖然這個問題已經存在多年，但是到目前已經發布的所有穩定版本中都普遍存在，一直沒有太大改善。雖然官方也在很早就承認這一問題，並且承諾儘快解決，但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題。

mysql資料庫如何優化？誰能給出點具體的解決方案？

1、explain：解釋sql的執行計劃，後邊的sql不執行

2、explain partitions ：用於查看存在分區的表的執行計劃

3、explain extended：待驗證

4、show warnings:

5、show create table:查看錶的詳細的創建語句，便於用戶對錶進行優化

6、show indexes :產看錶的所有索引，show indexes from table_name，同樣也可以從information_schema.statistics表中獲得同樣的信息。cardinality列很重要，表示數據量。

7、show tables status: 查看資料庫表的底層大小以及表結構，同樣可以從information_schema.tables表中獲得底層表的信息。

8、show [global|session]status:可以查看mysql伺服器當前內部狀態信息。可以幫助卻行mysql伺服器的負載的各種指標。默認是session。同information_schema.global_status和information_schema.session_status

9、show [global|session] variables ：查看當前mysql系統變數的值，其中一些值能影響到sql語句的執行方式。同information_schema.global_variables和information_schema.session_variables;

10、information_schema:包含的表的數量和mysql的版本有關係。

超詳細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多。