MySQL 資料庫規範--開發篇

目錄

1.sql語句編寫

2.explain 工具的使用--重點

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 工具的使用

explain工具的作用

1.使用 explain 工具可以确認執行計劃是否良好，查詢是否走了合理的索引。
2.不同版本MySQL 優化器各有不同，一些優化規則随着版本的發展可能有變化，
  查詢的執行計劃随着資料的變化也可能發生變化，這類情況就需要使用explain 來驗證自己的判斷。
           

explain 工具實操

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

explain select name from test where id = 32;
           

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

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;
           

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

執行結果如下所示：

螢幕快照 2017-03-30 上午10.36.34.png

下面詳細闡述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.主鍵範圍查詢

主鍵範圍查詢

上述為主鍵範圍查詢的explain資訊

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);

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調優 問題會非常少。

部落格搬家：[大坤的個人部落格] http://markfork.com/

)

歡迎評論哦~