《Mysql應用MySQL數據庫的數據類型和索引》要點：
本文介紹了Mysql應用MySQL數據庫的數據類型和索引，希望對您有用。如果有疑問，可以聯系我們。
?MYSQL學習
本文對MySQL數據類型和索引建立、優化進行整理,現在數據庫引擎默認都是InnoDB的,而且目前MySQL/MariaDB應用于生產環境時候,應該都是用的這個引擎吧.MYSQL學習
?MYSQL學習
1、MySQL的數據類型MYSQL學習
?MYSQL學習
?MYSQL學習
數字類型算是最簡單的了,主要差異在于各個類型的取值范圍大小限制,和對存儲空間字節數的需求.數字類型當然是在滿足情況的條件下越短越好,一方面MySQL每行有65535字節長度的限制,同時更寬的數據類型意味著對CPU、內存、磁盤I/O帶來壓力.MYSQL學習
?MYSQL學習
1.1.1MySQL支持的定點數字類型和占用字節數分別是MYSQL學習
?MYSQL學習
在數據庫設計的時候,常常看到這些整形有個前綴長度,其實這對其類型本身的存儲長度和精度沒有影響,只會關系到某些交互式工具顯示出來的字符個數.MYSQL學習
?MYSQL學習
1.12MySQL支持的浮點(實數)類型和占用字節數為MYSQL學習
?MYSQL學習
| 類型 | 長度 |
|---|---|
|
FLOATMYSQL學習 |
4MYSQL學習 |
|
FLOAT(p) [0,24]MYSQL學習 |
4MYSQL學習 |
|
FLOAT(p) [25,53]MYSQL學習 |
8MYSQL學習 |
|
DOUBLE,REALMYSQL學習 |
8MYSQL學習 |
?MYSQL學習
計算機的浮點運算都是不精確的,如果要實現精確浮點運算,就需要使用DECIMAL類型.MYSQL學習
?MYSQL學習
時間類型MYSQL學習
?MYSQL學習
1.1.3常被使用的是DATE、DATETIME和TIMESTAMP類型,其表示的范圍為：MYSQL學習
?MYSQL學習
DATE：’1000-01-01’ to ‘9999-12-31’
DATETIME：’1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
TIMESTAMP：’1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTCMYSQL學習
?MYSQL學習
TIMESTAMP存儲的范圍比DATETIME要小,但是空間利用率也最高.MySQL支持的時間精度最高為1s,如果更精確的存儲,就必須自己定義存儲格式了.MYSQL學習
?MYSQL學習
?MYSQL學習
MySQL中的字符串類型比較多也比較的復雜,各個字符串類型的差別不僅僅在存儲時候的空間占用,對存取時候字段某位的strip和padding還有差異.MYSQL學習
?MYSQL學習
對于類型CHAR/VARCHAR/TEXT是跟本地字符集相關的,這會影響到實際占用空間的字節數、字符比較等.MYSQL學習
?MYSQL學習
1.2.1CHAR(M)/VARCHAR(M)MYSQL學習
?MYSQL學習
長度限制參數M表示的是本地字符集的字符個數而不是bytes數目,比如對于UTF8編碼,每個本地字符其實際占用的byte長度可能是3或4倍的本地字符長度.比如VARCHAR(255),如果每個本地字符占用兩個字節,那么其需要的存儲空間最大為255x2+2.MYSQL學習
?MYSQL學習
CHAR的M被限制在最大255,而VARCHAR的M理論上受限于Row Size的長度(65,535bytes),且實際存儲時候會附加1~2字節的前綴表示數據實際長度.如果strict SQL模式沒有被打開,那么當插入數據超過聲明長度限制的時候,數據將會被截斷并產生警告信息,在strict SQL模式下將會出錯.MYSQL學習
?MYSQL學習
CHAR類型在存儲的時候,會在右端padding SPACE到指定的M長度,當取該字段的時候,所有末尾的SPACE都將會被strip掉然后返回；VARCHAR不會對進行SPACE進行padding以及strip操作,存儲什么樣的數據就會返回什么樣的數據.MYSQL學習
?MYSQL學習
對于CHAR/VARCHAR/TEXT類型,在進行字符串比較的時候,(SQL語句參數中的字符串)結尾的空格都是不參與比較的,但是對于LIKE語句,檢索的時候結尾的空格是考慮在內的.MYSQL學習
?MYSQL學習
1.2.2BINARY(M)/VARBINARY(M)MYSQL學習
?MYSQL學習
BINARY/VARBINARY在操作的時候,參考的是byte streaming而不是charaset streaming,所以其長度限制參數M表示的是byte數目,在比較的時候也是直接的數字大小比較(而非本地字符集方式比較).MYSQL學習
?MYSQL學習
BINARY在插入的時候,會使用0x00(而非SPACE)padding到長度M,取值的時候不會進行strip尾部空字符的操作(意味著取出來的長度一定是M)；VARBINARY則是保證原樣存取的.MYSQL學習
?MYSQL學習
1.2.3BLOB/TEXTMYSQL學習
?MYSQL學習
分別有TINY/MEDIUM/LONG類型的衍生長度,BLOB是bytes streaming類型的,而TEXT是基于character streaming本地字符集類型的,兩者在存取的時候都不會進行padding和strip操作.MYSQL學習
?MYSQL學習
BLOB/TEXT的關系和之前的VARBINARY/VARCHAR是比較相似的,除了：BLOB/TEXT不能夠有DEFAULT值；BLOB/TEXT在創建索引的時候必須要有prefix length,而前者是可選的；給予TEXT索引需要有前綴長度,而且建立索引會自動padding SPACE到索引長度,所以如果插入的字符前面一樣,只是尾部空字符長度不同,也是會產生相同的索引值.MYSQL學習
?MYSQL學習
1.2.4字符串各個類型占用的空間長度MYSQL學習
?MYSQL學習
| 類型 | 長度 |
|---|---|
|
CHAR(M)MYSQL學習 |
Mxw bytesMYSQL學習 |
|
BINARY(M)MYSQL學習 |
M bytesMYSQL學習 |
|
VARCHAR(M), VARBINARY(M)MYSQL學習 |
L+1/L+2 bytesMYSQL學習 |
|
TINYBLOB, TINYTEXTMYSQL學習 |
L+1 bytesMYSQL學習 |
|
LOB, TEXTMYSQL學習 |
L+2 bytesMYSQL學習 |
|
MEDIUMBLOB, MEDIUMTEXTMYSQL學習 |
L+3 bytesMYSQL學習 |
|
LONGBLOB, LONGTEXTMYSQL學習 |
L+4 bytesMYSQL學習 |
?MYSQL學習
根據官方手冊,CHAR/BINARY及其衍生的類型的數據是存儲在表的行內部(inline)的,而對于BLOB和TEXT類型,每一個字段只占用該行9-12(1~4+8)個字節(用于數據的地址和長度),實際的數據是存儲在Row Buffer之外位置的.所以對于經常訪問的字符串類型,而長度又不是特別的大,還是建議用VARCHAR(M)的數據類型,性能會比TEXT快不少.MYSQL學習
?MYSQL學習
?MYSQL學習
數據庫索引可以用來快速找到需要的行,否則的話MySQL就需要一行一行的遍歷,查詢效率自然相當的低.MYSQL學習
?MYSQL學習
MySQL支持的索引包括PRIMARY KEY、UNIQUE、INDEX、FULLTEXT類型的索引.前面說過,FULLTEXT類型的全文索引在中文下基本是報廢的,在此就不予討論了.MYSQL學習
?MYSQL學習
特別注意的是,對于索引列只能使用單純的列名,而不能是表達式或者函數的一部分,比如age+2、TO_DAYS(date_col),引擎在檢索的時候才能使用索引.MYSQL學習
?MYSQL學習
?MYSQL學習
2.1.1PRIMARY KEYMYSQL學習
?MYSQL學習
在InnoDB內部,表數據是優化主鍵快速查詢而排列分布的,其查找速度是最快的(相當于聚簇索引：該索引中鍵值的邏輯順序決定了表中相應行的物理順序).即使表中沒有適合做主鍵的列,也推薦采用一個自動增長的整數主鍵(代理鍵),那么這個表在增加數據的時候是順序存放的,而且后續在別的表參考該外鍵查詢的時候也會得到優化.本身在設計表的時候,也建議常用的數據額不常用的數據分表存放以增加效率.MYSQL學習
?MYSQL學習
2.1.2INDEX
MYSQL學習
普通索引,對數據沒有約束要求,多行記錄可以包含相同值.無論對于字符串索引,還是多列組合索引,都以及在查詢語句中,都有個最左前綴的原則：MYSQL學習
?MYSQL學習
(1) 對于字符串類型,可以指定索引前綴長度(且對于BLOB/TEXT前綴長度參數是必須的),在InnoDB表中其前綴長度最長是767 bytes,且參數M是用bytes計量的.所以太長的字符串,建立BTree索引浪費比較大,這時候用手動模擬HASH索引是個方法,不過這種方式對字符串無法靈活的使用前綴方式查詢(例如LIKE這類的操作).
?MYSQL學習(2) 在建立多列索引的時候,必須按照從左到右的順序使用全部或部分的索引列,才能充分的使用組合索引,比如：(col1, col2, col3)使用(col1)、(col1, col2)、(col1, col2, col3)有效.在查詢語句中會一直向右匹配直到遇到范圍查詢(>,<,BETWEEN,LIKE)就停止匹配,其后的索引列將不會使用索引來優化查找了.
?MYSQL學習(3) 索引不是建立的越多、越長越好,因為索引除了占用空間之外,對后續數據庫的增加、刪除、修改都有額外的操作來更新索引,所以對索引列和字符串前綴長度,都參考選擇性(Selectivity)這個指標來確定：選擇性定義為不重復的索引值和數據總記錄條數的比值,其選擇性越高,那么索引的查詢效率也越高,對于性別這種參數,建立索引根本沒有意義.MYSQL學習
?MYSQL學習
2.1.3UNIQUEMYSQL學習
?MYSQL學習
UNIQUE索引要求索引是唯一的.對于單列索引,要求該列所有數據都不相同,但允許有NULL值；對于多列的組合索引,要求這些列的組合是唯一的.UNIQUE索引其本身既可以作為索引,實際中也可以用以產生數據約束,防止增加或者修改后產生相同數據.MYSQL學習
?MYSQL學習
2.2.1B+TreeMYSQL學習
?MYSQL學習
該類型的索引中,列記錄都是按照順序排列的,可以優化用于比較或者范圍查找操作(=, >, >=, <, <=, BETWEEN, IN),以及用于(GROUP BY, ORDER BY)操作,而且對于字符串類型的索引,最左前綴字符串也可以充分利用索引,比如LIKE ‘Patrick%’會解釋成 ‘Patrick’ <= key_col < ‘Patricl’.MYSQL學習
?MYSQL學習
2.2.2HASHMYSQL學習
?MYSQL學習
速度更快,不過只能用于 =、<=>、IN操作符；優化器不能用于ORDER BY操作；任何查找操作必須是索引的完整列.MYSQL學習
