Mysql資料庫最佳化的方法總結（必看）

本篇文章給大家帶來的內容是關於Mysql資料庫最佳化的方法總結，有一定的參考價值，有需要的朋友可以參考一下，希望對你有所協助。

學無止境，資料庫最佳化分為方方面面，在這裡，我進行了比較全的總結，分享給正在工作或者學習的同行們。

資料庫的最佳化分為以下七大方面：

1、表的設計要符合三範式（適當的反三範式也可以）；

2、添加適當的索引，索引對查詢速度影響很大，必須添加索引（主鍵索引，唯一索引，普通索引，全文索引）；

3、添加適當預存程序，觸發器，事務等；

4、讀寫分離（主從資料庫）；

5、對sql語句的一些最佳化，（查詢執行速度比較慢的sql語句）；

6、分表分區（分表：把一張大表分成多張表。分區：把一張表裡面的分配到不同的地區儲存）；

7、對mysql伺服器硬體的升級操作。

接下來我將具體講解最佳化的方式。

一、三範式

第一範式：

原子性：表裡面的欄位不能再分割，只要是關係型資料庫，就天然的自動滿足第一範式

關係型資料庫（有行和列的概念）：mysql、sql server、oracle、db2、infomix、sybase、postgresql，在設計時，先有庫->表->欄位->具體記錄（內容）：在儲存資料時，要設計欄位。

非關係型資料庫（泛指nosql資料庫）：memcache、redis、momgodb等。

第二範式：

一個表中沒有完全相同的記錄，通過一個主鍵即能解決

第三範式：

表中不能儲存冗餘資料

反三範式設計：

相簿表

ID	相簿名稱	相簿瀏覽量
1	生活照	100
2	工作照	100

照片表

ID	照片名稱	相簿ID	瀏覽量
1	我的小狗	1	49
2	我的小貓	1	51
3	我的同事	2	100

如果要算一個相簿的瀏覽量，我們可以在相簿表中添加相簿瀏覽量欄位，瀏覽照片的時候同時更新相簿瀏覽量。

二、開啟慢查詢

Mysql慢查詢預設是關閉的，預設記錄超過10秒的sql語句。

1.查看慢查詢記錄時間：

show variables like ‘long_query_time’;

2.修改慢查詢時間：

set long_query_time=2;

3.通過如下的一個函數來進行測試：

benchmark(count,expr)   函數可以測試執行count次expr操作需要的時間

三、建立索引

1、主鍵索引的特點：

(1)一個表中最多隻有一個主鍵索引

(2)一個主鍵索引可以指向多個列

(3)主鍵索引的列，不能有重複的值，也不能有null

(4)主鍵索引的效率高。

2、唯一索引的特點：

(1)一個表中可以有多個唯一索引

(2)一個唯一索引可以指向多個列，

(3)如果在唯一索引上，沒有指定not null，則該列可以為空白，同時可以有多個null,

(4)唯一索引的效率較高。

3、普通索引：

使用普通索引主要是提高查詢效率

4、全文索引

mysql內建的全文索引mysql5.5不支援中文，支援英文，同時要求表的儲存引擎是myisam。如果希望支援中文，有兩個方案，

(1)使用aphinx中文版coreseek (來替代全文索引)

(2)外掛程式mysqlcft。

添加索引主要的問題：

(1)較頻繁的作為查詢條件欄位應該建立索引，唯一性太差的欄位不適合單獨建立索引，即使頻繁作為查詢條件，更新非常頻繁的欄位不適合建立索引

(2)不會出現在WHERE子句中欄位不該建立索，索引是由代價的，雖然是查詢速度提高了，但是，會影響增該刪的效率。而且索引檔案會佔用空間。

四、分表、分區

垂直分表（內容主表+附加表）：

內容主表：儲存各種資料的一些公用資訊，比如資料的名稱，添加時間等，

可以使用多個附加表，附加表格儲存體一些資料的獨特的資訊。

主要原因：是內容主表裡面的資料訪問比較頻繁。

特點：表結構不同

水平分表：

將表資料存在不同的表中。

特點：表結構相同

分區：

就是把一個表格儲存體到磁碟不同地區，仍然是一張表。

基本的概念：

(1)Range（範圍）–這種模式允許將資料劃分不同範圍。例如可以將一個表通過年份劃分成若干個分區。

(2)List（預定義列表）–這種模式允許系統通過預定義的列表的值來對資料進行分割。

(3)Hash（雜湊）–這中模式允許通過對錶的一個或多個列的Hash Key進行計算，最後通過這個Hash碼不同數值對應的資料區域進行分區。例如可以建立一個對錶主鍵進行分區的表。

(4)Key（索引值）-上面Hash模式的一種延伸，這裡的Hash Key是MySQL系統產生的。

分區表的限制：

(1)只能對資料表的整型列進行分區，或者資料列可以通過分區函數轉化成整型列。

(2)最大分區數目不能超過1024。

(3)如果含有唯一索引或者主鍵，則分區列必須包含在所有的唯一索引或者主鍵在內。

(4)按日期進行分區很非常適合，因為很多日期函數可以用。但是對於字串來說合適的分區函數不太多。

五、並發處理的鎖機制

鎖機制：在執行時，只有一個使用者獲得鎖，其他使用者處於阻塞狀態，需要等待解鎖。

mysql 的鎖有以下幾種形式：

表級鎖：開銷小，加鎖快，發生鎖衝突的機率最高，並發度最低。myisam引擎屬於這種類型。

行級鎖：開銷大，加鎖慢，發生鎖衝突的機率最低，並發度也最高。innodb屬於這種類型。

表鎖的示範：

1.對myisam表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放後，才會執行其他進程的操作。

2.表添加讀鎖後，其他進程對該表只能查詢操作，修改時會被阻塞。

3.當前進程，能夠執行查詢操作，不能執行修改操作。不能對沒有鎖定的表進行操作。

4.鎖表的文法：

lock table 表名 read|write

5.也可以鎖定多個表

6.對myisam表的寫操作（加寫鎖），會阻塞其他進程對鎖定表的任何操作，不能讀寫，

7.表加寫鎖後，則只有當前進程對鎖定的表，可以執行任何操作。其他進程的操作會被阻塞。

行鎖的示範：

1.innodb儲存引擎是通過給索引上的索引項目加鎖來實現的，這就意味著：只有通過索引條件檢索資料，innodb才會使用行級鎖，否則，innodb使用表鎖。

2.開啟行鎖後，當前進程在針對某條記錄執行操作時，其他進程不能操作和當前進程相同id的記錄。

php裡面有檔案鎖，在實際的項目中多數使用檔案鎖，因為表鎖，會阻塞，當對一些表添加寫鎖後，其他進程就不能操作了。這樣會阻塞整個網站，會拖慢網站的速度。

相關推薦：