《Mysql應(yīng)用MySQL數(shù)據(jù)庫的數(shù)據(jù)類型和索引》要點：
本文介紹了Mysql應(yīng)用MySQL數(shù)據(jù)庫的數(shù)據(jù)類型和索引，希望對您有用。如果有疑問，可以聯(lián)系我們。

數(shù)據(jù)庫的數(shù)據(jù)庫索引對程序員來說是透明的,意味著數(shù)據(jù)庫建立索引之前和之后,你的SQL語句都可以正常運行,索引的運用只是數(shù)據(jù)庫引擎工作時候的優(yōu)化手段.但是,這不是意味著數(shù)據(jù)庫索引僅僅是數(shù)據(jù)庫設(shè)計和運維者的事情,對于一個程序員如果對數(shù)據(jù)庫已有的索引有所了解,還是可以大大優(yōu)化程序員數(shù)據(jù)庫的查詢和修改語句執(zhí)行效率的,以免你的低效查詢語句稱為拖累整個系統(tǒng)性能的Black Sheep.

?MYSQL學(xué)習(xí)

本文對MySQL數(shù)據(jù)類型和索引建立、優(yōu)化進行整理,現(xiàn)在數(shù)據(jù)庫引擎默認(rèn)都是InnoDB的,而且目前MySQL/MariaDB應(yīng)用于生產(chǎn)環(huán)境時候,應(yīng)該都是用的這個引擎吧.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

1、MySQL的數(shù)據(jù)類型MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

1.1 數(shù)字類型和時間類型

?MYSQL學(xué)習(xí)

數(shù)字類型算是最簡單的了,主要差異在于各個類型的取值范圍大小限制,和對存儲空間字節(jié)數(shù)的需求.數(shù)字類型當(dāng)然是在滿足情況的條件下越短越好,一方面MySQL每行有65535字節(jié)長度的限制,同時更寬的數(shù)據(jù)類型意味著對CPU、內(nèi)存、磁盤I/O帶來壓力.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

1.1.1MySQL支持的定點數(shù)字類型和占用字節(jié)數(shù)分別是MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

在數(shù)據(jù)庫設(shè)計的時候,常常看到這些整形有個前綴長度,其實這對其類型本身的存儲長度和精度沒有影響,只會關(guān)系到某些交互式工具顯示出來的字符個數(shù).MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

1.12MySQL支持的浮點(實數(shù))類型和占用字節(jié)數(shù)為MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

類型	長度
FLOATMYSQL學(xué)習(xí)	4MYSQL學(xué)習(xí)
FLOAT(p) [0,24]MYSQL學(xué)習(xí)	4MYSQL學(xué)習(xí)
FLOAT(p) [25,53]MYSQL學(xué)習(xí)	8MYSQL學(xué)習(xí)
DOUBLE,REALMYSQL學(xué)習(xí)	8MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

計算機的浮點運算都是不精確的,如果要實現(xiàn)精確浮點運算,就需要使用DECIMAL類型.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

時間類型MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

1.1.3常被使用的是DATE、DATETIME和TIMESTAMP類型,其表示的范圍為：MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

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學(xué)習(xí)

?MYSQL學(xué)習(xí)

TIMESTAMP存儲的范圍比DATETIME要小,但是空間利用率也最高.MySQL支持的時間精度最高為1s,如果更精確的存儲,就必須自己定義存儲格式了.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

1.2 字符串類型

?MYSQL學(xué)習(xí)

MySQL中的字符串類型比較多也比較的復(fù)雜,各個字符串類型的差別不僅僅在存儲時候的空間占用,對存取時候字段某位的strip和padding還有差異.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

對于類型CHAR/VARCHAR/TEXT是跟本地字符集相關(guān)的,這會影響到實際占用空間的字節(jié)數(shù)、字符比較等.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

1.2.1CHAR(M)/VARCHAR(M)MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

長度限制參數(shù)M表示的是本地字符集的字符個數(shù)而不是bytes數(shù)目,比如對于UTF8編碼,每個本地字符其實際占用的byte長度可能是3或4倍的本地字符長度.比如VARCHAR(255),如果每個本地字符占用兩個字節(jié),那么其需要的存儲空間最大為255x2+2.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

CHAR的M被限制在最大255,而VARCHAR的M理論上受限于Row Size的長度(65,535bytes),且實際存儲時候會附加1~2字節(jié)的前綴表示數(shù)據(jù)實際長度.如果strict SQL模式?jīng)]有被打開,那么當(dāng)插入數(shù)據(jù)超過聲明長度限制的時候,數(shù)據(jù)將會被截斷并產(chǎn)生警告信息,在strict SQL模式下將會出錯.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

CHAR類型在存儲的時候,會在右端padding SPACE到指定的M長度,當(dāng)取該字段的時候,所有末尾的SPACE都將會被strip掉然后返回；VARCHAR不會對進行SPACE進行padding以及strip操作,存儲什么樣的數(shù)據(jù)就會返回什么樣的數(shù)據(jù).MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

對于CHAR/VARCHAR/TEXT類型,在進行字符串比較的時候,(SQL語句參數(shù)中的字符串)結(jié)尾的空格都是不參與比較的,但是對于LIKE語句,檢索的時候結(jié)尾的空格是考慮在內(nèi)的.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

1.2.2BINARY(M)/VARBINARY(M)MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

BINARY/VARBINARY在操作的時候,參考的是byte streaming而不是charaset streaming,所以其長度限制參數(shù)M表示的是byte數(shù)目,在比較的時候也是直接的數(shù)字大小比較(而非本地字符集方式比較).MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

BINARY在插入的時候,會使用0x00(而非SPACE)padding到長度M,取值的時候不會進行strip尾部空字符的操作(意味著取出來的長度一定是M)；VARBINARY則是保證原樣存取的.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

1.2.3BLOB/TEXTMYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

分別有TINY/MEDIUM/LONG類型的衍生長度,BLOB是bytes streaming類型的,而TEXT是基于character streaming本地字符集類型的,兩者在存取的時候都不會進行padding和strip操作.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

BLOB/TEXT的關(guān)系和之前的VARBINARY/VARCHAR是比較相似的,除了：BLOB/TEXT不能夠有DEFAULT值；BLOB/TEXT在創(chuàng)建索引的時候必須要有prefix length,而前者是可選的；給予TEXT索引需要有前綴長度,而且建立索引會自動padding SPACE到索引長度,所以如果插入的字符前面一樣,只是尾部空字符長度不同,也是會產(chǎn)生相同的索引值.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

1.2.4字符串各個類型占用的空間長度MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

類型	長度
CHAR(M)MYSQL學(xué)習(xí)	Mxw bytesMYSQL學(xué)習(xí)
BINARY(M)MYSQL學(xué)習(xí)	M bytesMYSQL學(xué)習(xí)
VARCHAR(M), VARBINARY(M)MYSQL學(xué)習(xí)	L+1/L+2 bytesMYSQL學(xué)習(xí)
TINYBLOB, TINYTEXTMYSQL學(xué)習(xí)	L+1 bytesMYSQL學(xué)習(xí)
LOB, TEXTMYSQL學(xué)習(xí)	L+2 bytesMYSQL學(xué)習(xí)
MEDIUMBLOB, MEDIUMTEXTMYSQL學(xué)習(xí)	L+3 bytesMYSQL學(xué)習(xí)
LONGBLOB, LONGTEXTMYSQL學(xué)習(xí)	L+4 bytesMYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

根據(jù)官方手冊,CHAR/BINARY及其衍生的類型的數(shù)據(jù)是存儲在表的行內(nèi)部(inline)的,而對于BLOB和TEXT類型,每一個字段只占用該行9-12(1~4+8)個字節(jié)(用于數(shù)據(jù)的地址和長度),實際的數(shù)據(jù)是存儲在Row Buffer之外位置的.所以對于經(jīng)常訪問的字符串類型,而長度又不是特別的大,還是建議用VARCHAR(M)的數(shù)據(jù)類型,性能會比TEXT快不少.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

2、MySQL數(shù)據(jù)庫索引

?MYSQL學(xué)習(xí)

數(shù)據(jù)庫索引可以用來快速找到需要的行,否則的話MySQL就需要一行一行的遍歷,查詢效率自然相當(dāng)?shù)牡?MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

MySQL支持的索引包括PRIMARY KEY、UNIQUE、INDEX、FULLTEXT類型的索引.前面說過,FULLTEXT類型的全文索引在中文下基本是報廢的,在此就不予討論了.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

特別注意的是,對于索引列只能使用單純的列名,而不能是表達式或者函數(shù)的一部分,比如age+2、TO_DAYS(date_col),引擎在檢索的時候才能使用索引.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

2.1 索引的類型

?MYSQL學(xué)習(xí)

2.1.1PRIMARY KEYMYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

在InnoDB內(nèi)部,表數(shù)據(jù)是優(yōu)化主鍵快速查詢而排列分布的,其查找速度是最快的(相當(dāng)于聚簇索引：該索引中鍵值的邏輯順序決定了表中相應(yīng)行的物理順序).即使表中沒有適合做主鍵的列,也推薦采用一個自動增長的整數(shù)主鍵(代理鍵),那么這個表在增加數(shù)據(jù)的時候是順序存放的,而且后續(xù)在別的表參考該外鍵查詢的時候也會得到優(yōu)化.本身在設(shè)計表的時候,也建議常用的數(shù)據(jù)額不常用的數(shù)據(jù)分表存放以增加效率.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

2.1.2INDEX
　　MYSQL學(xué)習(xí)

普通索引,對數(shù)據(jù)沒有約束要求,多行記錄可以包含相同值.無論對于字符串索引,還是多列組合索引,都以及在查詢語句中,都有個最左前綴的原則：MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

(1) 對于字符串類型,可以指定索引前綴長度(且對于BLOB/TEXT前綴長度參數(shù)是必須的),在InnoDB表中其前綴長度最長是767 bytes,且參數(shù)M是用bytes計量的.所以太長的字符串,建立BTree索引浪費比較大,這時候用手動模擬HASH索引是個方法,不過這種方式對字符串無法靈活的使用前綴方式查詢(例如LIKE這類的操作).
?MYSQL學(xué)習(xí)

(2) 在建立多列索引的時候,必須按照從左到右的順序使用全部或部分的索引列,才能充分的使用組合索引,比如：(col1, col2, col3)使用(col1)、(col1, col2)、(col1, col2, col3)有效.在查詢語句中會一直向右匹配直到遇到范圍查詢(>,<,BETWEEN,LIKE)就停止匹配,其后的索引列將不會使用索引來優(yōu)化查找了.
?MYSQL學(xué)習(xí)

(3) 索引不是建立的越多、越長越好,因為索引除了占用空間之外,對后續(xù)數(shù)據(jù)庫的增加、刪除、修改都有額外的操作來更新索引,所以對索引列和字符串前綴長度,都參考選擇性(Selectivity)這個指標(biāo)來確定：選擇性定義為不重復(fù)的索引值和數(shù)據(jù)總記錄條數(shù)的比值,其選擇性越高,那么索引的查詢效率也越高,對于性別這種參數(shù),建立索引根本沒有意義.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

2.1.3UNIQUEMYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

UNIQUE索引要求索引是唯一的.對于單列索引,要求該列所有數(shù)據(jù)都不相同,但允許有NULL值；對于多列的組合索引,要求這些列的組合是唯一的.UNIQUE索引其本身既可以作為索引,實際中也可以用以產(chǎn)生數(shù)據(jù)約束,防止增加或者修改后產(chǎn)生相同數(shù)據(jù).MYSQL學(xué)習(xí)

?

2.2 B+Tree和HASH

?MYSQL學(xué)習(xí)

2.2.1B+TreeMYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

該類型的索引中,列記錄都是按照順序排列的,可以優(yōu)化用于比較或者范圍查找操作(=, >, >=, <, <=, BETWEEN, IN),以及用于(GROUP BY, ORDER BY)操作,而且對于字符串類型的索引,最左前綴字符串也可以充分利用索引,比如LIKE ‘Patrick%’會解釋成 ‘Patrick’ <= key_col < ‘Patricl’.MYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

2.2.2HASHMYSQL學(xué)習(xí)

?MYSQL學(xué)習(xí)

速度更快,不過只能用于 =、<=>、IN操作符；優(yōu)化器不能用于ORDER BY操作；任何查找操作必須是索引的完整列.MYSQL學(xué)習(xí)

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/6161.html