MySQL 資料庫規範--開發篇

1.sql語句編寫

 1.執行大的delete、update、insert操作要慎重，特别是對業務繁忙的系統，要盡量避免對線上業務産生影響。

解決辦法是：大操作切割為小操作，使用limit子句限制每次操作的記錄數，也可以利用一些日期字段基于更小粒度的時間範圍進行操作。

 2.避免使用select * 語句，select語句之用于擷取需要的字段。

 3.使用預編譯語句，可以提高性能并且防範 sql注入 攻擊。

 4.一般情況下update,delete 語句中不要使用limit。

 5.where 條件語句中必須使用合适的類型，避免mysql進行隐式轉換。

 6.insert into 必須顯式指明字段名稱，不要使用insert into table()。 

 7.避免在sql 語句中進行數學運算或函數運算，避免将業務邏輯和資料存儲耦合在一起。 

 8.insert 語句如果使用批量送出，如insert into table values(),()...那麼values 的個數不應過多。一次性送出過多記錄，會導緻i/o緊張，出現慢查詢。

 9.避免使用存儲過程、觸發器、函數等，這些特性會将業務邏輯與資料庫耦合在一起，并且mysql的存儲過程，觸發器，函數中可能存在bug。

 10.盡量避免使用子查詢，連接配接。盡量将子查詢轉化為連接配接查詢，mysql 查詢優化器會優化連接配接查詢，但連接配接的表要盡可能的少，如果很多，可以考慮反範式設計。即對設計階段做一些改造。 

 11.使用合理的sql語句以減少與資料庫的互動次數。 

 12.建議使用合理的分頁技術以提高操作效率。

2.explain

工具的使用

1.使用 explain 工具可以确認執行計劃是否良好，查詢是否走了合理的索引。 

2.不同版本mysql 優化器各有不同，一些優化規則随着版本的發展可能有變化， 查詢的執行計劃随着資料的變化也可能發生變化，這類情況就需要使用explain 來驗證自己的判斷。

執行如下腳本，觀察控制台輸出

注意資料表使用如下腳本：

table name = test、column1 = id、column2 = name.

執行結果如下所示：

下面詳細闡述explain 輸出的各項内容：

id:  包含一組數字，表示查詢中執行 select子句 或操作表的順序。如果 id 相同，則執行順序由上到下。

select_type:

表示查詢中每個 select 子句的類型(是簡單還是複雜)輸出結果類似如下： 1.simple 查詢中不包含子查詢或者union 2.primary 查詢中若包含任何複雜子查詢，最外層查詢被标記為primary 3.subquery 在select 或 where 清單中包含了子查詢，則該查詢被标記為subquery 4.derived 在from清單中包含的子查詢被标記為derived(衍生) 5.union 若第二個select出現在union之後，則被标記為derived。 6.union result 從union表中擷取結果的select将被标記為 union result。 select_type 隻需要了解分類即可，這個資訊并不是最有價值的。

type:最有價值資訊之一

type表示 mysql 在表中找到所需行的方式，又稱為“通路類型”，常見的類型如下所示： all、index、range、ref、eq_ref、const,system,null 以上類型，由左至右，由最差到最好。 all: full table scan,mysql 将周遊全表以找到比對的行。 index:full index scan,index 與 all 差別為index類型隻周遊索引樹。假設表中有主鍵字段id,則select id from table_name；type即為full index scan。 range:索引掃描範圍，對索引的掃描開始于某一點，傳回比對的域或行，常見于between、<、>等的查詢。 ref:非唯一性索引掃描，将傳回比對某個單獨值得所有行。常見于使用非唯一索引或唯一索引的非唯一字首的查找。 eq_ref:唯一性索引掃描，對于每個索引鍵表中隻有一條記錄與之比對。常見于主鍵或唯一索引掃描。 const、system:當mysql對查詢的某部分進行優化，并轉化為一個常量時，可使用這些類型進行通路。如果主鍵置于where清單中，mysql就能将該查詢轉換為一個常量，system是const 的一個特例，當查詢的表隻有一行的情況下，即可使用system。 null:mysql 在優化過程中分解語句，執行時甚至不用通路表或索引，舉例如下： explain select from (select from t1 where id = 1)d1;

possible_keys

possible_keys 将指出mysql能使用哪個索引在表中找到行，查詢涉及的字段上若存在索引，則該索引将被列出，但不一定會被查詢使用。

key:最有價值資訊之二

key 将顯示mysql在查詢中實際使用到的索引，若沒有使用索引，則顯示為null。查詢中若使用到了覆寫索引，則該索引僅僅出現在 key 清單中，possible_keys中并不顯示。

key_len

key_len表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度。

ref

ref表示上述表的連接配接比對條件，即哪些列或常亮被用于查找索引列上的值。

rows:最有價值資訊之三

rows 表示mysql根據表統計資訊及索引選用的情況， 估算查找所需記錄需要讀取的行數。使用到索引一般情況下會使得rows的值降低。

extra:最有價值資訊之四

extra 包含不适合在其他列中顯示但十分重要的額外資訊。可能包如下4種資訊。 1.using index 該值表示相應的select操作中使用到了覆寫索引，包含滿足查詢需要的資料的索引稱為覆寫索引。 2.using where 如果查詢未能使用索引，則using where 的作用隻是提醒我們 mysql 将用where 子句來過濾結果集。 3.using temporary 表示mysql需要使用臨時表來存儲結果集，常見于order by 與 group by，事實上group by會進行隐式的order by。 如果我們在group by 時利用索引分組(其實包含排序的過程)排序，則可以提高性能，因為不會此時查詢輸出裡沒有了using temporary,using filesort。

4.using filesort

using filesort 即檔案排序，mysql 中将無法使用索引完成的排序操作，稱為檔案排序。

上文篇幅有點長，但都是必須了解的概念。最有價值資訊是我們判斷sql語句執行是否高效的基準，了解四個最有價值資訊是最重要的。

繼續示範explain的使用，使用上文的評判标準來看下語句的執行效率：

1.主鍵查詢

上述為主鍵查詢的explain資訊 type ＝ const 效率很高 key ＝ primary 實際使用的索引為主鍵 rows ＝ 1 查找的記錄數為1 extra = null ,沒有任何額外資訊 總體來說，性能是極高。

2.主鍵範圍查詢

type ＝range 範圍查詢，效率不是最低 rows ＝ 7 查找的記錄數為7 extra = using where ,最終使用where 做結果集過濾，未使用到覆寫索引。 總體來說，性能是很高。

3.未帶索引查詢

如上圖所示，name 并未做索引。 type ＝all full table scan 全表查詢 key ＝null 未使用索引 rows ＝ 7 10資料庫中所有記錄 總體來說，性能極差。（這也是我司内部deviceid接口出問題的終極原因）。

4.未帶索引的分組查詢

extra = using where ,最終使用where 做結果集過濾，未使用到覆寫索引。并使用到了temporary,filesort 臨時表與檔案查詢。 總體來說，性能極差。

5.帶索引的分組查詢

我們現在為4與3中 name 建立索引，再來看看分析結果 建立索引腳本如下 alter table test add index idx_name(name);

再運作3 和 4中的查詢語句 結果如下圖所示：

type ＝ref 非唯一索引掃描，效率不是最低 key ＝name 實際使用的索引name索引（注意：idx_name與name都是在name字段上建立的索引）。 extra = using index ,最終使用到覆寫索引。 總體來說，查詢性能是極高的。

type ＝range 範圍掃描，效率不是最低 extra = using index ,最終使用where 做結果集過濾，使用到覆寫索引。

當然上述示範比較簡單，也不是非常具備實戰色彩，對于explain的使用，我們還應在更多的資料庫操作場景中多多使用，這是sql調優的利器。為我們後期的調優減輕了負擔，可以說如果在這一步做好了sql腳本的設計，那麼後期關于 sql調優 問題會非常少。

作者： mark_rock 

連結：http://www.imooc.com/article/details/id/17287

來源：慕課網