MySQL開發規範及實用技術表設計索引

1.1：命名形式

Object簡稱+_+功能子產品簡稱+_+功能子產品分功能簡稱

說明：

Object的簡稱如下：

表（Table）：t

視圖（View）：v

存儲程式（Procedure）：p

函數（Function）：f

功能子產品的簡稱，eg：

移動端相關功能的表：mobile

功能子產品分功能簡稱，eg：

移動端客戶登陸資訊表：customer

命名eg：t_mobile_customer

1.2：命名要求

1.2.1: 命名必須為小寫的形式

MySQL有配置參數lower_case_table_names，不可動态更改，linux系統預設為0，即庫表名以實際情況存儲，大小寫敏感。如果是1，以小寫存儲，大小寫不敏感。如果是2，以實際情況存儲，但以小寫比較。大小寫混拼很容易緻使混亂。

1.2.2: 命名之間必須以“_”作為分隔符

1.2.3: 命名使用簡稱必須使用英語名詞的簡稱

1.2.4: 命名必須不超過12個字元

庫名、表名、字段名支援最多64個字元，但為了統一規範、易于辨識以及減少傳輸量，必須不超過12字元。

統一采用InnoDB存儲引擎。

5.5以後的預設引擎，支援事務，行級鎖，更好的恢複性，對高并發，多核，大記憶體，ssd等支援更好。對于我們現有的環境的備份，恢複更加合适。

當然，采用什麼存儲引擎根據業務的需求，就目前我們更适合InnoDB。對MyISAM，Memory暫無特别強烈的需求。

1.1：有關整形類型的字段類型，取值範圍，占用空間

類型

占用空間(位元組)

最小值(signed/Unsigned)

最大值(signed/unsigned)

TINYINT

1

-128

127

255

SMALLINT

2

-32768

32767

65535

MEDIUMINT

3

-8388608

8388607

16777215

INT

4

-2147483648

2147483647

BIGINT

8

-9223372036854775808

9223372036854775807

18446744073709551615

1.2：日期和時間類型

取值範圍（最小值/最大值）

顯示格式

DATETIME

1000-01-01 00:00:00

9999-12-31  23:59:59

YYYY-MM-DD HH:MM:SS

DATE

1000-01-01

9999-12-31

YYYY-MM-DD

TIMESTAMP

1970-01-01 00:00:01UTC

2038-01-19  03:14:07UTC

YYYY-MM-DD  HH:MM:SS

YEAR(4)

1901

2155

YYYY

YEAR(2)

1970

2069

其中：

00~69代表2000~2069

70~99代表1970~1999

YY

TIME

-838:59:59

838:59:59

HH:MM:SS

Or

HHH:MM:SS

有人奇怪為什麼TIME類型的時間可以大于23.因為TIME類型不僅可以用來儲存一天中的時間，也可以用來儲存時間間隔，同時解釋了為什麼TIME類型也可以存在負值。

1.3：字元串類型

CHAR和VARCHAR是最常用的兩種字元集類型。

一般來說：CHAR(N)用來儲存固定長度的字元串，VARCHAR(N)用來儲存變長字元類型。

CHAR類型，N的取值範圍：0~255

VARCHAR類型，N的取值範圍：0~65535

其中N都代表字元長度，而非位元組長度。

1.4：BINARY和VARBINARY

BINARY和VARBINARY存儲的是二進制的字元串，而非字元集型字元串。也就是說，BINARY和VARBINARY沒有字元集的概念，對于排序和比較都是按照二進制值進行對比。

BINARY(N)和VARBINARY(N)中的N指的是位元組長度，而非字元長度。

1.5：高精度類型

DECIMAL和NUMERIC類型在MySQL中被視為相同類型，用于儲存必須為确切精度的值。對于如工資資料類型，當聲明該類型的列，可以(并且通常必須)指定精度和标度，例如：salary DECIMAL(5,2)

在上述的例子中，5是精度，2是标度。精度表示儲存值的主要位數，标度表示小數點後面可以儲存的位數。

1.6：ENUM和SET類型

ENUM和SET類型都是集合類型，不同的是ENUM類型最多可枚舉65536個元素，而SET類型最多枚舉64個元素。

由于MySQL不支援傳統的CHECK限制，是以通過ENUM和SET類型并結合SQL_MODE可以解決一部分問題。eg:對“性别”列的限制。

字段類型的屬性：UNSIGNED和ZEROFILL

1.1：UNSIGNED

UNSIGNED屬性就是将數字類型無符号化。eg:INT的類型範圍是-2147483648~2147483647

而INT UNSIGNED範圍是0~4294967295

1.2: ZEROFILL

ZEROFILL屬性非常有意思，更像是一個顯示的屬性。很多初學者往往對MySQL資料庫中類型數字類型後面的長度值很迷茫。eg：定義字段a int(4) UNSIGNED ZEROFILL;假如向a字段插入數字1，select發現顯示的結果為 a：0001,但是其實際存儲的還是數字1。有關此部分的内容請參考：

<a href="http://lgdvsehome.blog.51cto.com/3360656/1243676">http://lgdvsehome.blog.51cto.com/3360656/1243676</a>

1.1: 建議使用UNSIGNED存儲非負數值。

       理由：同樣的存儲空間，更大的取值範圍。

1.2: 存儲精确浮點數必須使用DECIMAL替代FLOAT和DOUBLE。

a) mysql中的數值類型（不包括整型）：

IEEE754浮點數： float （單精度） ，double 或 real （雙精度）

定點數： decimal 或 numeric

單精度浮點數的有效數字二進制是24位，按十進制來說，是8位；雙精度浮點數的有效數字二進制是53位，按十進制來說，是16 位。一個實數的有效數字超過8位，用單精度浮點數來表示的話，就會産生誤差！同樣，如果一個實數的有效數字超過16位，用雙精度浮點數來表示，也會産生誤差。

b) IEEE754标準的計算機浮點數，在内部是用二進制表示的，但在将一個十進制數轉換為二進制浮點數時，也會造成誤差，原因是不是所有的數都能轉換成有限長度的二進制數。

即一個二進制可以準确轉換成十進制，但一個帶小數的十進制不一定能夠準确地用二進制來表示。

     eg:

        mysql&gt; create table t(value float(10,2));

Query OK, 0 rows affected (0.00 sec)

mysql&gt; insert into t values(131072.67),(131072.68);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates:0  Warnings: 0

mysql&gt; select * from t\G;

*************************** 1. row ***************************

value: 131072.67

*************************** 2. row ***************************

value: 131072.69

2 rows in set (0.01 sec)

1.3：建議使用INT UNSIGNED存儲IPV4。

使用INTUNSIGNED而不是char(15)來存儲ipv4位址，通過MySQL函數inet_ntoa和inet_aton來進行轉化；Ipv6位址目前沒有轉化函數，需要使用DECIMAL或者兩個bigINT來存儲。例如：

 mysql&gt; create table t ( ip INT UNSIGNED );

      mysql&gt;insert into t select INET_ATON('209.207.224.40');

      mysql&gt;select INET_NTOA(ip) from t;

            INET_NTOA(ip):209.207.224.40

1.4:表字元集選擇UTF8

a) 使用utf8字元集，如果是漢字，占3個位元組，但ASCII碼字元還是1個位元組。

b) 統一，不會有轉換産生亂碼風險

c) 其他地區的使用者（美國、印度、台灣）無需安裝簡體中文支援，就能正常看您的文字，并且不會出現亂碼

d) ISO-8859-1編碼(latin1)使用了單位元組内的所有空間，在支援ISO-8859-1的系統中傳輸和存儲其他任何編碼的位元組流都不會被抛棄。即把其他任何編碼的位元組流當作ISO-8859-1編碼看待都沒有問題，儲存的是原封不動的位元組流。

特别說明：就目前我們公司的情況已有環境為gbk，是以請選擇gbk。

1.5：盡量避免使用TEXT和BLOB

僅僅當字元數量可能超過20000個的時候，可以使用TEXT類型來存放字元類資料。所有使用TEXT類型的字段必須和原表進行分拆，與原表主鍵單獨組成另外一個表進行存放。

1.6：明确需求的精度

eg:所有需要精确到天的字段使用DATE，而非DATETIME或TIMESTAMP;

需要精确到秒，使用DATETIME或TIMESTAMP，注意兩者的取值範圍。

1.7：自增字段的類型隻能選擇INT或BIGINT，且明确标示為無符号(UNSIGNED)，除非确實出現了負數。如果其值預估超過42億才選擇BIGINT。

1.8：字段名稱統一使用小寫，且注意避免使用系統保留的關鍵字。

1.1：非唯一索引命名，以idx_字段名_字段名_[字段名]的格式命名

1.2：唯一索引命名，以uniq_字段名_字段名_[字段名]的格式命名

1.3：索引名稱必須為小寫

1.1: 索引中的字段數建議不超過5個

1.2: 單張表的索引數量控制在5個以内

1.3：獨立的列：索引列不能是表達式的一部分，也不能是函數的參數

#我們經常會看到一些查詢不當地使用索引，或者使得MySQL無法使用已有的索引。如果查詢中的列不是獨立的，則MySQL就不會使用索引。我們應該養成簡化WHERE條件的習慣，始終将索引列單獨放在比較符号一側。

eg：一個常見的錯誤：

    mysql&gt;SELECT … WHERE TO_DAYS(CURRENT_DATE) – TO_DAYS(date_col) &lt;= 10;

1.4：對于BLOB，TEXT或者很長的VARCHAR類型的列，必須使用字首索引，因為MySQL不允許索引這些列的完整長度。

#使用字首索引，要注意索引的選着性及MySQL無法使用字首索引做ORDER BY和GROUP BY。

1.5：當出現伺服器對多個索引做相交操作時(通常有多個AND條件)，通常意味着需要一個包含所有相關列的多列索引，而不是多個獨立的單列索引。

#通過EXPLAIN中Extra列，如果發現Using union(idx_1,idx_2),也就是所謂的索引合并時，要考慮使用多列索引。當WHERE條件後出現多個條件時，考慮使用多列索引，而不是為每個單獨的列建立索引。

1.6：選擇合适的索引列順序。

#當不需要考慮排序和分組時，将選擇性最高的列放在前面通常是很好的。

1.7：合理使用覆寫索引。

#設計優秀的索引應該考慮到整個查詢，而不單單是WHERE條件部分。索引确實是一種查找資料的高效方式，但是MySQL也可以使用索引來直接擷取列的資料，這樣就不再需要讀取資料行。如果一個索引包含(或者說覆寫)所有需要查詢的字段的值，我們稱之為“覆寫索引”。

eg:可以考慮在list_1,list_2上加一個多列索引，就可以使用這個索引做覆寫索引。

   mysql&gt;select list_1,list_2 from table_name;

1.8：避免多個範圍條件

#對于範圍條件查詢，MySQL無法再使用範圍列後面的其它索引列，但是對于“多個等值條件查詢”則沒有這個限制。

1.9：備援和重複索引

#MySQL允許在相同列上建立多個索引，無論是有意的還是無意的。MySQL需要單獨維護重複的索引，優化器在優化查詢的時候也需要逐個地進行考慮，這會影響性能。重複索引是指在相同的列上按照相同的順序建立的相同類型的索引。應該避免這樣建立重複索引，發現以後也應該立即移除。eg：

CREATE TABLE test （

id INT NOT NULL PRIMIARY KEY,

a  INT NOT NULL,

UNIQUE(id),

INDEX(id)

)ENGINE=innodb;

一個經驗不足的使用者可能是想建立一個主鍵，先加上唯一限制，然後再加上索引以供查詢使用。事實上，MySQL的唯一限制和主鍵限制都是通過索引實作的，是以，上面的寫法實際上在相同的列上建立了三個重複的索引。

備援索引和重複索引有一些不同。如果建立了索引(A,B)，再建立索引(A)就是備援索引，因為這隻是前一個索引的字首索引。是以索引(A,B)也可以當作索引(A)來使用（這種備援隻是對B-Tree索引來說的）。但是如果再建立索引(B,A)，則不是備援索引，索引(B)也不是。另外其它類型的，如：哈希，全文索引，也不會是B-Tree索引的備援索引。

備援索引通常發生在為表添加新索引的時候。例如，有人可能會增加一個新的索引(A,B)而不是擴充已有的索引(A)。還有一種情況時将一個索引擴充為(A,ID)，其中ID是主鍵，對于InnoDB來說主鍵列已經包含在二級索引中了，是以這也是備援。

大多數情況下都不需要備援索引，應該盡量擴充已有的索引而不是建立新索引。但也有時候處于性能方面的考慮需要備援索引，因為擴充已有的索引會導緻其變得太大，進而影響其它使用該索引的性能。

例如，如果在整數列上有一個索引，現在需要額外增加一個很長的VARCHAR列來擴充該索引，那性能可能會急劇下降。特别是有查詢把這個索引當作覆寫索引，或者這是MyISAM表并且有很多範圍查詢的時候。

另外注意的是，表中的索引越多插入速度會越慢。

1.10：使用索引掃描來做排序

#要注意排序方向；索引的最左字首的要求；同時注意索引列是否為範圍查找。

eg：假設a,b,c建立了一個多列索引(D)，下面這個查詢使用不用的排序方向，但是索引列都是正序排序的，索引（D）不能在此用來做排序操作。

SELECT … WHERE a = ‘常量’ ORDER BYb DESC, c ASC;

eg:下面的查詢不滿足索引的最左字首。

SELECT … WHERE a = ‘xxx’ ORDER BY c;

eg:下面這個查詢在索引列上是範圍條件，是以MySQL無法使用索引的其餘列。

SELECT … WHERE a &gt; ‘xxx’ ORDER BY b,c;

本文轉自 kuchuli 51CTO部落格，原文連結：http://blog.51cto.com/lgdvsehome/1272044，如需轉載請自行聯系原作者