Mysql最佳化策略

標籤：範圍查詢   而且   索引   post   提高   思路   地方   表示   基本   

一、建表原則：

1、表的最佳化與類型選擇

　　（1）定長與變長相分離。

　　（2）根據使用頻率建立主表及副表（將不常用的欄位放入副表中：比如使用者表，將使用者家庭地址等詳細資料放入附表，當需要查詢詳情，再點擊查詢）。

（3）在滿足資料庫“三範式”的前提下，採用“反三範式”，合理加入冗餘欄位。該思路是以空間換時間，比如減少頻繁求和、頻繁級聯等，保證系統效能。

2、列選擇（資料類型選擇）

（1）欄位類型選擇：整型>date，time>enum、char>varchar>blob、text。

（2）對於整型和char型，優先選擇整型，比如採用整型或字元型表示性別時，優先選擇tinyint型，因為選擇char(1)可能會考慮到字元集和校對集。

（3）enum：內部採用整型儲存，起到約束的作用。

（4）char與varchar：char定長，varchar不定長，雖然varchar會比char省空間，但是由於修改時會根據長度不同，會引起‘行遷移’(Row Migration)現象，而這造成多餘的I/O，是資料庫設計和調整中要儘力避免的，所以對於頻繁修改的資料盡量使用char代替varchar，以確保效能。

二、索引最佳化（核心）：

索引可以提高查詢、排序、以及分組速度。

1、 索引分類：betree索引、hash索引。

（1）Betree索引：一個樹形結構索引，頁節點記錄指向資料的資訊，快速定位元據。

（2）Hash索引：運行在記憶體中，只能在memory表中使用。在磁碟中給一個空間，通過複雜運算擷取磁碟的地址，當擷取資料時再利用函數運算，直接擷取地址拿到資料，直接命中查詢速度快。

Hash索引缺點缺點：

1）可能會存在地址衝突：採用拉鏈演算法解決。

2）隨機地址，磁碟空洞。

3）範圍查詢無法最佳化。因為地址是隨機的，所以無法範圍最佳化。

4）無法運用首碼索引（利用到建立索引欄位的一部分），排序無法最佳化。因為需要回行拿資料。

2、索引類型：獨立索引、聯合索引

（1）聯合索引是多個列聯合起來加上同一索引，列順序區分，即滿足“左首碼”要求，從左至右依次滿足，若中間有斷裂，則後邊的不能利用。

3、索引建立：

（1）普通索引

              這是最基本的索引，它沒有任何限制。

              建立索引：

              CREATE INDEX indexName ON mytable(username(length));如果是CHAR VARCHAR類型，length可以小於欄位實際長度；如果是BLOB和TEXT類型，必須指定 length，下同。

               刪除索引：

              DROP INDEX [indexName] ON mytable;

       （2）唯一索引

              它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是複合式索引，則列值的組合必須唯一。

              建立索引:

              CREATE UNIQUE INDEX indexName ON mytable(username(length));

       （3）主鍵索引

              它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時建立主鍵索引：

               CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID) );

       3、betree索引分類：根據引擎不同分為聚簇索引和非聚簇索引

       （1）非聚簇索引（myisam引擎 ）：索引樹和資料分開，只在分葉節點下放索引值以及指向行資料的引用。

       （2）聚簇索引（innodb引擎）：聚簇索引是對磁碟上實際資料重新組織以按指定的一個或多個列的值排序的演算法。特點是儲存資料的順序和索引順序一致。既儲存索引值又儲存行資料（資料放在主鍵索引下，若無主鍵索引則放在非空索引下，若無非空索引則直接在內部建立一個row存允許存取資料），不用回行擷取資料。該索引次級索引指向對主鍵索引的引用。

       聚簇索引好處：不用回行，快速擷取資料；缺點：若資料不規則則會造成頁分裂（資料大，主鍵索引不規則）。

4、索引覆蓋：如果索引中已經包含所查詢的欄位，則不用回行，直接從索引值擷取資料，稱為索引覆蓋。若索引中欄位不完全包含所查詢的欄位，則要回行。

5、索引建立原則：命中頻繁、區分度高、長度小、盡量覆蓋常用查詢欄位。

       一般在實際工作中，可以根據調研，及作業記錄，統計資料，進行索引建立。

7.對於左首碼區分度不高的欄位（比如地址https://www.cnblogs.com，中的：“https://www.”）：採用倒序、偽雜湊等方法。

       偽雜湊：新增一個欄位，利用crc32演算法（雜湊演算法）將，需要建立索引的欄位算成一個整數，然後給整數加索引，然後查詢的時候給需要查詢的資料先採用crc32計算，然後查詢crc32欄位的資料。

8.索引與排序：需要排序的欄位盡量與索引一致，索引本來是有序的，而且在所索引上排序速度加快。

       9.重複索引與冗餘索引：

（1）重複索引：兩個索引完全相同：無意義。

（2）冗餘索引：兩個索引有交叉的地方。

三、SQL語句最佳化

1、sql語句花費時間：等待時間與執行時間（尋找、取出、傳輸少）。

（1）按資料拆分成多次查詢，比如分頁查詢。

（2）按索引查、排序、分組，資料最好可以走索引覆蓋。

（3）對不要求絕對精準的資料，可不精確查詢，

（4）傳輸：避免在資料查詢中使用select * 查詢，需要哪些欄位，就查那些欄位。

2、sql語句效能判別：採用Explain對sql語句進行解釋：

Id：

Select_type:

Table:

Type: all  、index、range、ref（引用）、const（常量）、system、null

Ref(引用)：

Extra：

四、limit最佳化；

1、業務解決：翻頁頁數限制

2、技術解決：先沿著索引，獲得合格id，然後根據id查詢。

Mysql最佳化策略