《Mysql實例MySQL 請選擇合適的列》要點(diǎn)：
本文介紹了Mysql實例MySQL 請選擇合適的列，希望對您有用。如果有疑問，可以聯(lián)系我們。
思維導(dǎo)圖
點(diǎn)擊圖片,可查看大圖.
MYSQL學(xué)習(xí)
介紹
?
情況：如果你的表結(jié)構(gòu)設(shè)計不良或你的索引設(shè)計不佳,那么請你優(yōu)化你的表結(jié)構(gòu)設(shè)計和給予合適的索引,這樣你的查詢性能就能提高幾個數(shù)量級.――數(shù)據(jù)越大,索引的價值越能體現(xiàn)出來.
?
我們要提高性能,需要考慮的因素：
1、設(shè)計架構(gòu)
2、設(shè)計索引
3、評估查詢性能
?
MYSQL學(xué)習(xí)
今天要講的是表列的設(shè)計,暫不談索引設(shè)計.我會在下一章講索引設(shè)計.
?
選擇數(shù)據(jù)類型
?
選擇正確的數(shù)據(jù)類型,對于提高性能至關(guān)重要.
下面給出幾種原則,有利于幫助你選擇何種類型.
?
1、更小通常更好.
使用最小的數(shù)據(jù)類型.――更少的磁盤空間,內(nèi)存和CPU緩存.而且需要的CPU的周期也更少.
?
2、簡單就好.
整數(shù)代價小于字符.――因為字符集和排序規(guī)則使字符比較更復(fù)雜.
1>mysql內(nèi)建類型(如timestamp,date)優(yōu)于使用字符串保存.
2>使用整數(shù)保存ip地址.
?
3、盡量避免NULL――如果計劃對列進(jìn)行索引,盡量避免把列設(shè)置為NULL
盡可能把字段定義為NOT NULL.――可以放置一個默認(rèn)值,如‘',0,特殊字符串.
原因：
(1)MYSQL難以優(yōu)化NULL列.NULL列會使索引,索引統(tǒng)計和值更加復(fù)雜.
(2)NULL列需要更多的存儲空間,還需要在MYSQL內(nèi)部進(jìn)行特殊處理.
(3)NULL列加索引,每條記錄都需要一個額外的字節(jié),還導(dǎo)致MyISAM中固定大小的索引變成可變大小的索引.
?
MYSQL學(xué)習(xí)
決定列的數(shù)據(jù)類型,我們應(yīng)該遵循下面兩步.MYSQL學(xué)習(xí)
第一步、大致決定數(shù)據(jù)類型.――判斷是數(shù)字,字符串還是時間等.這通常很直觀.MYSQL學(xué)習(xí)
第二步、確定特定的類型.MYSQL學(xué)習(xí)
很多數(shù)據(jù)類型能夠保存同類型的數(shù)據(jù),但是我們要發(fā)現(xiàn),他們在存儲的范圍,精度和物理空間之間的差別(磁盤或內(nèi)存空間).如：datetime和timestamp能保存同樣類型的數(shù)據(jù)：日期和時間.――timestamp使用datetime一半的空間;能保存時區(qū);擁有特殊的自動更新能力.?MYSQL學(xué)習(xí)
MYSQL學(xué)習(xí)
品味數(shù)據(jù)類型
?
整數(shù)
?
1、存儲類型――數(shù)據(jù)范圍為-2^(n-1)到2^(n-1)-1,這里的n是所需存儲空間的位數(shù).
? 類型名稱 占用位數(shù) ?? 數(shù)據(jù)范圍 TINYINT? 8? -2^7 ~ 2^7-1? SMALLINT? 16 ? -2^15~2^15-1 ? MEDIUMINT ? 24 ? -2^23~2^23-1? INT? 32? -2^31~2^23-1? BIGINT ?64?? -2^63~2^63-1
?2、unsigned屬性表示不允許負(fù)數(shù),并大致把正上限提高了一倍.如TINYINT UNSIGNED保存的范圍為0到255而不是-127到128MYSQL學(xué)習(xí)
?MYSQL學(xué)習(xí)
3、MYSQL對整數(shù)類型定義寬度,比如int(1)和int(22)對于存儲和計算是一樣的.只規(guī)定了MYSQL的交互工具(如命令行客戶端)用來顯示字符的個數(shù).MYSQL學(xué)習(xí)
?MYSQL學(xué)習(xí)
MYSQL學(xué)習(xí)
實數(shù)
?
實數(shù)有分?jǐn)?shù)部分(小數(shù)部分).
存儲類型：FLOAT和DOUBLE,DECIMAL.
占用大小：FLOAT 4個字節(jié),DOUBLE 8個字節(jié).DECIMAL受到MYSQL版本影響,早期版本254個數(shù)字,5.0以上65個數(shù)字.
區(qū)別：1、FLOAT和DOUBLE支持標(biāo)準(zhǔn)浮點(diǎn)運(yùn)算進(jìn)行近似計算.
2、DECIMAL進(jìn)行DECIMAL運(yùn)算,CPU并不支持對它進(jìn)行直接計算.浮點(diǎn)運(yùn)算會快一點(diǎn),因為計算直接在CPU上進(jìn)行.
? 3、DECIMAL只是一個存儲格式,在計算時會被轉(zhuǎn)換為DOUBLE類型.
4、DECIMAL(18,9)使用9個字節(jié),小數(shù)點(diǎn)前4個字節(jié),小數(shù)點(diǎn)1個字節(jié),小數(shù)點(diǎn)后4個字節(jié).
5、DECIMAL只有對小數(shù)進(jìn)行精確計算的時候才使用它,如保存金融數(shù)據(jù).
?
MYSQL學(xué)習(xí)
?
字符串類型
?
1、varchar
(1)保存可變長字符串.
理解：比固定長度占用更少的存儲空間,因為它只占用自己需要的空間.例外情況：使用ROW_FORMAT=FIXED創(chuàng)建的MyISAM表,它為每行使用固定長度的空間,可能會造成浪費(fèi).
(2)存儲長度信息.如果定義的列小于或等于255,則使用1個字節(jié)存儲長度值,假設(shè)使用latin1字符集,如varchar(10)將占用11個字節(jié)的存儲空間.反過來,varchar(1000),則占用1002個字節(jié)的存儲空間.
(3)節(jié)約空間,對性能有幫助.
(4)5.0版本以上,無論是取值還是保存,MySQL都會保留字符串末尾的空格.
只分配真正需要的空間
使用varchar(5)和varchar(200)保存'hello'占用空間是一樣的.――這里應(yīng)該指的是磁盤上的空間.
那么使用較短列有何優(yōu)勢?――巨大的優(yōu)勢
較大的列會使用更多的內(nèi)存,因為MySQL通常會分配固定大小的內(nèi)存塊(如varchar(200)會用200個字符大小的內(nèi)存空間)來保存值(然后對值進(jìn)行trim操作,最后放入磁盤)或取值.――這對排序或使用基于內(nèi)存的臨時表尤其不好.
MYSQL學(xué)習(xí)
2、char
(1)固定長度.
(2)保存值時,去掉末尾的空格.
MYSQL學(xué)習(xí)
? ? ? ? ? ?
? 咱們再看看varcharMYSQL學(xué)習(xí)
MYSQL學(xué)習(xí)
(3)char常用于很短字符串或長度近似相同的字符串的時候很有用.如存儲用戶密碼的MD5哈希值,它的長度總是一樣的.
char優(yōu)于varchar的地方?
1>> 對于經(jīng)常改變的值,char優(yōu)于varchar,因為固定長度行不容易產(chǎn)生碎片.――當(dāng)最長長度遠(yuǎn)大于平均長度,并且很少發(fā)生更新的時候,通常適合使用varchar.
2>>對于很短的列,char的效率也是高于varchar的.如對于單字節(jié)字符集(如latin1),char(1)只會占用1個字節(jié),而varchar(2)會占用2個字節(jié)(有一個字節(jié)用來存儲長度的信息).
?
MYSQL學(xué)習(xí)
?
?3、text
用于保存大量數(shù)據(jù).
(1)InnoDB在它們較大的時候會使用“外部”存儲區(qū)域來進(jìn)行保存.――所以需要足夠的外部存儲空間來保存實際的值.
(2)排序方式不同于其他字符類型,不會按照完整長度進(jìn)行排序,而只是按照max_sort_length規(guī)定的前若干個字節(jié)進(jìn)行排序.
?
MYSQL學(xué)習(xí)
MYSQL學(xué)習(xí)
4、使用ENUM代替字符串類型
(1)ENUM列可以存儲65 535個不同的字符串.
(2)以緊湊方式保存.根據(jù)列表中值的數(shù)量,把它們壓縮到1到2個字節(jié)中.
(3)MySQL在內(nèi)部把每個值都保存為整數(shù),以表示值在列表中的位置.
(4)保留了一份“查找表”,來表示整數(shù)和字符串在表的.frm文件中的映射關(guān)系.
(5)ENUM字符列是固定的,添加、刪除字符串須使用ALTER TABLE.
(6)使用案例：權(quán)限表中使用ENUM來保存Y值和N值.
?使用方法：
MYSQL學(xué)習(xí)
? ? ? ? ? ???MYSQL學(xué)習(xí)
MYSQL學(xué)習(xí)
在對enum列使用order by的時候,是按數(shù)字排序的,而不是字符串排序.MYSQL學(xué)習(xí)
MYSQL學(xué)習(xí)
日期和時間類型MYSQL學(xué)習(xí)
DATETIME：保存大范圍的值.封裝格式：YYYYMMDDHHMMSS.――與時區(qū)無關(guān),使用8字節(jié)存儲空間.
TIMESTAMP：保存自1970年1月1日午夜(格林尼治標(biāo)準(zhǔn)時間)以來的秒數(shù).――使用4字節(jié)存儲空間.MYSQL學(xué)習(xí)
通常使用TIMESTAMP,它比DATETIME更節(jié)約空間.有時人們把Unix的時間戳保存為整數(shù)值,但是這通常沒有任何好處.――這種格式處理起來不太方便,我們并不推薦它.MYSQL學(xué)習(xí)
?經(jīng)驗交談
?
1、我們在為列選擇數(shù)據(jù)類型的時候,不僅要考慮存儲類型大小,還要考慮MySQL如何對它們進(jìn)行計算和比較.例如：MySQL在內(nèi)部把ENUM和SET類型保存為整數(shù),但是在比較的時候把它們轉(zhuǎn)換為字符串.
?
2、我們要在相關(guān)表中使用同樣的類型,類型之間要精確匹配,包括諸如UNSIGNED這樣的屬性.
?
3、混合不同的數(shù)據(jù)類型會導(dǎo)致性能問題,即使沒有性能問題,隱式的類型轉(zhuǎn)換也能導(dǎo)致難以察覺的錯誤.
?
4、選擇最小的數(shù)據(jù)類型要考慮將來留出的增長空間.如,中國的省份,我們知道不會有成千上萬個,因此不必用INT.TINYINT就足夠了,它比INT小3字節(jié).
?
5、整數(shù)通常是最佳的數(shù)據(jù)類型,因為它速度快,并且能使用AUTO_INCREMENT.
?
6、要盡可能避免字符串做為列的數(shù)據(jù)類型,因為它們占用了很多空間并且通常必整數(shù)類型要慢.MyISAM默認(rèn)情況下為字符串使用了壓縮索引,這使查找更為緩慢.
?
總結(jié)
?
若有錯誤,望請大俠指教一、二,不勝感激!
?
參考文獻(xiàn):《高性能MYSQL》
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/6224.html
