mysql 最佳化(一)

標籤：

資料庫的操作越來越成為整個應用的瓶頸,mysql最佳化是提高應用效能的重中之重,今天來講講最近研究的mysql 的一些效能最佳化

Mysql的效能最佳化

(一) 開啟查詢快取最佳化的你查詢速度

如何開啟mysql的查詢快取?你的mysql資料庫是否支援mysql查詢快取?? (查詢快取是一把雙刃劍,這裡就不多說了)

查詢快取的工作流程:

1. 伺服器接收SQL,以SQL和一些其他條件為key尋找緩衝表(額外效能消耗)

2. 如果找到了緩衝,則直接返回緩衝(效能提升)

3. 如果沒有找到緩衝,則執行SQL查詢,包括原來的SQL解析等.

4. 執行完SQL查詢結果以後,將SQL查詢結果存入緩衝表(額外效能消耗)

開啟命令列終端 輸入 show variables like "%query_cache%"; 查看你是否開啟緩衝

這裡的參數

have_query_cache: 你的mysql版本是否支援查詢快取

query_cache_size : 緩衝使用的總記憶體空間大小,單位是位元組,這個值必須是1024的整數倍,否則MySQL實際分配可能跟這個數值不同

query_cache_type: 緩衝的方式 有三個值 1) OFF: 關閉  2) ON: 總是開啟 3) DEMAND: 只有明確寫了SQL_CACHE的查詢才會吸入緩衝

query_cache_min_res_unit: 分配記憶體塊時的最小單位大小

如果你的 query_cache_type =0  or query_chache_size =0 那麼表示沒有開啟緩衝,可以修改設定檔來開啟

當有很多相同的查詢被執行了多次的時候，這些查詢結果會被放到一個緩衝中，這樣，後續的相同的查詢就不用動作表 而直接存取緩衝結果了。

如果你的查詢條件有包含一些mysql的內建函數 比如 有時間 now() ,rand()等,那麼講不會緩衝.

比較一下下面我執行的sql語句,當我開啟查詢快取的時候 第一次執行所消耗的時間跟第二次執行所消耗的時間 以及相同查詢語義但是大小寫不一樣.(sql語句絕對相等)

 

 

(2) EXPLAIN你的查詢語句

EXPLAIN關鍵字能夠讓你知道索引的使用,如何搜尋資料的,掃描行數等等

可以協助你分析你SELECT 語句的瓶頸因此可以最佳化你的SELECT語句

選擇一個複雜的sql語句

可以看到mysql是怎麼樣處理你的sql語句

select_type: 有三個參數(simple,primary, union,dependent union,union result)  simple 它表示簡單的select,沒有union和子查詢(這裡只介紹simple)

table : 出自哪一張表

type :顯示的訪問類型,從效能最好到最壞以此是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

表中只有一行;const類型的特例

 

2)const:  表中最有有一行匹配,const使用者比較primary key或者unique索引,因為只有一行,所以很快

 

3)eq_ref :   mysql手冊是這樣說的:"對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯結類型，除了const類型。它用在一個索引的所有部分被聯結使用並且索引是UNIQUE或PRIMARY KEY"。即比較帶索引的列

4)ref : 對於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。這裡的索引不包括 primary和unique.

5)rang : 給定範圍內的索引,如 EXPLAIN SELECT * FROM user WHERE id IN (1,5) 或者是 BETWEEN

6)ALL : 全表掃描

possible_key : 顯示 使用的哪個索引在該表中找到行 

key : 該查詢時所用到的索引

key_len : 使用索引的長度

ref : ref列顯示使用哪個列或常數與key一起從表中選擇行。

rows : 顯示查詢時掃描的行數,值越大越不好,所以根據這個可以判斷mysql語句的好壞以及建立索引最佳化

extra: 額外的資訊

可以根據EXPLAIN你SELECT的查詢語句 進行相關的最佳化

 

(3) 為你的表合理的建立索引

這裡為什麼是合理呢,索引不是建得越多就越好,索引太多 對於 UPDATE DELETE INSERT 的效率都會有影響,

上面提到的EXPLAIN SELECT 語句我們可以進行分析

在table user(及table a)中 它的掃描行數是 180207行.而且是全表掃描,沒有用到索引,

在命令列中我們來執行以下該sql,查詢的時間是0.63sec

我們可以給user表中的school_id加個索引 CREATE INDEX schoolIndex ON `user`(school_id);

 

這時來看一下查詢的時間和EXPLAIN SELECT 語句,

 

 總結:可以很明顯的看出 執行時間大大減少了,而且在EXPLAIN中可以看到 type相比於之前的ALL 現在是ref (索引) ,row也相比於180207行到2385行 效能大大的提升了許多

另外需要注意的是:當你的WHERE 後面的條件是 a.name like %陳%; 這樣是不會的查詢語句 就算你給name加一個索引 也會沒有意義.

1、建立多表（三個表或以上）相關性檢視時，如果是主表和副表都有的欄位，盡量使用主表的欄位（特別是主表的主鍵）
2、副表的欄位（無論是普通欄位還是主鍵、索引欄位）作為查詢條件對查詢都沒有協助，都需進行全表檢索

 

(4)如果查詢一條資料的時候使用limit

舉個例子 :  SELECT * FROM user WHERE name=‘vDobgB‘;

當你知道 name=‘vDobgB‘在資料庫中只有一條資料的時候使用limit會大大提升效率,這個時候mysql找到該行的時候就會返回這條資料,而不會繼續往下尋找

 

(5)在join表的時候 串連條件的欄位類型,應當一致,並且將其索引

如果你的應用中使用到了很多表串連查詢,應該確認表與表串連欄位已經建立了索引,並且兩個欄位類型是一致的.

向我上面兩表串連的欄位類型都是int類型,且已經加了索引.如果你要把DECIMAL(小數)類型欄位和int(整形)類型的欄位串連在一起,那麼Mysql就無法使用它們的索引

 

(6)避免使用 SELECT *

從資料庫裡讀出越多的資料，那麼查詢就會變得越慢。並且，如果你的資料庫伺服器和WEB 伺服器是兩台獨立的服務器的話，這還會增加網路傳輸的負載。

應該養成,需要什麼資料就拿什麼資料

 

 

(7)建立主鍵索引 即id

為每一個表都建立主鍵索引 id,而且這個id還是 AUTO_INCREMENT 最好是INT類型 ,

如果你有一張表name是唯一的,並且你給name這個欄位設立為主鍵,這樣效率會減低,因為使用VARCHAR類型的主鍵低於INT類型.

而且，在MySQL 資料引擎下，還有一些操作需要使用主鍵，在這些情況下，主鍵的效能和設定變得非常重要，比如:mysql的分表, 叢集等

 

(8)某些情況下使用ENUM而不是VARCHAR (但是也有一些人說慎用ENUM類型)

如果你的表中的某個欄位 例如: 省份,而這個欄位經常出現的且只會出現的只有 廣東省,福建省,海南省等國家的所有省份.

那麼你應該給該欄位的類型應該是ENUM而不是VARCHAR.

ENUM 類型是非常快和緊湊的。在實際上，其儲存的是TINYINT，但其外表上顯示為字串。

例如，指定為 ENUM("one", "two", "three") 的一個列，可以有下面所顯示的任一值。每個值的索引值也如下所示：

值	索引值
NULL	NULL
""	0
"one"	1
"two"	2
"three"	3

 

 

 

 

 

 

(9) 使用PROCEDURE ANALYSE()取得建議

 

 其中 optimal_fieldtype會推薦我們使用怎麼樣的資料類型,當表中資料了越大的時候,就越準確,但是不一定是完全準確的,你需要思考..哈哈哈哈

 

 (10)建立表的時候使用NOT NULL,而且盡量給表設定預設值

NULL 需要額外的空間,mysql的上的文檔是這麼說的

 如果你的表的欄位是int 那麼應該給預設值 DEFAULT 0 ,如果是varchar類型 DEFAULT ‘ ‘

 

mysql的部分最佳化先暫時講這麼多,如果有疑惑的或者是有其他見解的歡迎評論..

今天php7發布了,php7的效能相比於原來提高了百分之40%-200%.

mysql 最佳化(一)