MySQL 請(qǐng)選擇合適的列

更新時(shí)間：2012年05月28日 00:40:38 作者：

如果你的表結(jié)構(gòu)設(shè)計(jì)不良或你的索引設(shè)計(jì)不佳，那么請(qǐng)你優(yōu)化你的表結(jié)構(gòu)設(shè)計(jì)和給予合適的索引，這樣你的查詢性能就能提高幾個(gè)數(shù)量級(jí)。——數(shù)據(jù)越大，索引的價(jià)值越能體現(xiàn)出來(lái)

思維導(dǎo)圖
點(diǎn)擊圖片，可查看大圖。

介紹

情況：如果你的表結(jié)構(gòu)設(shè)計(jì)不良或你的索引設(shè)計(jì)不佳，那么請(qǐng)你優(yōu)化你的表結(jié)構(gòu)設(shè)計(jì)和給予合適的索引，這樣你的查詢性能就能提高幾個(gè)數(shù)量級(jí)?！獢?shù)據(jù)越大，索引的價(jià)值越能體現(xiàn)出來(lái)。

我們要提高性能，需要考慮的因素：

1、設(shè)計(jì)架構(gòu)

2、設(shè)計(jì)索引

3、評(píng)估查詢性能

今天要講的是表列的設(shè)計(jì)，暫不談索引設(shè)計(jì)。我會(huì)在下一章講索引設(shè)計(jì)。

選擇數(shù)據(jù)類型

選擇正確的數(shù)據(jù)類型，對(duì)于提高性能至關(guān)重要。

下面給出幾種原則，有利于幫助你選擇何種類型。

1、更小通常更好。

　　使用最小的數(shù)據(jù)類型?！俚拇疟P空間，內(nèi)存和CPU緩存。而且需要的CPU的周期也更少。

2、簡(jiǎn)單就好。

　　整數(shù)代價(jià)小于字符?！?yàn)樽址团判蛞?guī)則使字符比較更復(fù)雜。

　　1>mysql內(nèi)建類型（如timestamp,date)優(yōu)于使用字符串保存。

　　2>使用整數(shù)保存ip地址。

3、盡量避免NULL——如果計(jì)劃對(duì)列進(jìn)行索引，盡量避免把列設(shè)置為NULL

　　盡可能把字段定義為NOT NULL?！梢苑胖靡粋€(gè)默認(rèn)值，如‘'，0，特殊字符串。

　　原因：

　　　?。?）MYSQL難以優(yōu)化NULL列。NULL列會(huì)使索引，索引統(tǒng)計(jì)和值更加復(fù)雜。

　　　?。?）NULL列需要更多的存儲(chǔ)空間，還需要在MYSQL內(nèi)部進(jìn)行特殊處理。

　　　?。?）NULL列加索引，每條記錄都需要一個(gè)額外的字節(jié)，還導(dǎo)致MyISAM中固定大小的索引變成可變大小的索引。

決定列的數(shù)據(jù)類型，我們應(yīng)該遵循下面兩步。

第一步、大致決定數(shù)據(jù)類型。——判斷是數(shù)字，字符串還是時(shí)間等。這通常很直觀。

第二步、確定特定的類型。

　　很多數(shù)據(jù)類型能夠保存同類型的數(shù)據(jù)，但是我們要發(fā)現(xiàn)，他們?cè)?STRONG>存儲(chǔ)的范圍，精度和物理空間之間的差別（磁盤或內(nèi)存空間）。如：datetime和timestamp能保存同樣類型的數(shù)據(jù)：日期和時(shí)間。——timestamp使用datetime一半的空間;能保存時(shí)區(qū);擁有特殊的自動(dòng)更新能力。

品味數(shù)據(jù)類型

整數(shù)

1、存儲(chǔ)類型——數(shù)據(jù)范圍為-2^(n-1)到2^(n-1)-1，這里的n是所需存儲(chǔ)空間的位數(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到128

3、MYSQL對(duì)整數(shù)類型定義寬度，比如int（1）和int（22）對(duì)于存儲(chǔ)和計(jì)算是一樣的。只規(guī)定了MYSQL的交互工具（如命令行客戶端）用來(lái)顯示字符的個(gè)數(shù)。

實(shí)數(shù)

實(shí)數(shù)有分?jǐn)?shù)部分（小數(shù)部分）。

存儲(chǔ)類型：FLOAT和DOUBLE，DECIMAL。

占用大小：FLOAT 4個(gè)字節(jié)，DOUBLE 8個(gè)字節(jié)。DECIMAL受到MYSQL版本影響，早期版本254個(gè)數(shù)字，5.0以上65個(gè)數(shù)字。

區(qū)別：1、FLOAT和DOUBLE支持標(biāo)準(zhǔn)浮點(diǎn)運(yùn)算進(jìn)行近似計(jì)算。

　　　2、DECIMAL進(jìn)行DECIMAL運(yùn)算，CPU并不支持對(duì)它進(jìn)行直接計(jì)算。浮點(diǎn)運(yùn)算會(huì)快一點(diǎn)，因?yàn)橛?jì)算直接在CPU上進(jìn)行。

　　 3、DECIMAL只是一個(gè)存儲(chǔ)格式，在計(jì)算時(shí)會(huì)被轉(zhuǎn)換為DOUBLE類型。

　　　4、DECIMAL(18,9)使用9個(gè)字節(jié)，小數(shù)點(diǎn)前4個(gè)字節(jié)，小數(shù)點(diǎn)1個(gè)字節(jié)，小數(shù)點(diǎn)后4個(gè)字節(jié)。

　　　5、DECIMAL只有對(duì)小數(shù)進(jìn)行精確計(jì)算的時(shí)候才使用它，如保存金融數(shù)據(jù)。

字符串類型

1、varchar

　?。?）保存可變長(zhǎng)字符串。

　　　　理解：比固定長(zhǎng)度占用更少的存儲(chǔ)空間，因?yàn)樗徽加米约盒枰目臻g。例外情況：使用ROW_FORMAT=FIXED創(chuàng)建的MyISAM表，它為每行使用固定長(zhǎng)度的空間，可能會(huì)造成浪費(fèi)。

　?。?）存儲(chǔ)長(zhǎng)度信息。如果定義的列小于或等于255，則使用1個(gè)字節(jié)存儲(chǔ)長(zhǎng)度值，假設(shè)使用latin1字符集，如varchar（10）將占用11個(gè)字節(jié)的存儲(chǔ)空間。反過(guò)來(lái)，varchar（1000），則占用1002個(gè)字節(jié)的存儲(chǔ)空間。

　?。?）節(jié)約空間，對(duì)性能有幫助。

　?。?）5.0版本以上，無(wú)論是取值還是保存，MySQL都會(huì)保留字符串末尾的空格。

只分配真正需要的空間
使用varchar(5)和varchar(200)保存'hello'占用空間是一樣的。——這里應(yīng)該指的是磁盤上的空間。
那么使用較短列有何優(yōu)勢(shì)？——巨大的優(yōu)勢(shì)
　　較大的列會(huì)使用更多的內(nèi)存，因?yàn)镸ySQL通常會(huì)分配固定大小的內(nèi)存塊（如varchar(200)會(huì)用200個(gè)字符大小的內(nèi)存空間）來(lái)保存值(然后對(duì)值進(jìn)行trim操作，最后放入磁盤)或取值?！@對(duì)排序或使用基于內(nèi)存的臨時(shí)表尤其不好。

2、char

　?。?）固定長(zhǎng)度。

　?。?）保存值時(shí)，去掉末尾的空格。

咱們?cè)倏纯磛archar

　　（3）char常用于很短字符串或長(zhǎng)度近似相同的字符串的時(shí)候很有用。如存儲(chǔ)用戶密碼的MD5哈希值，它的長(zhǎng)度總是一樣的。

　　char優(yōu)于varchar的地方？

　　1>> 對(duì)于經(jīng)常改變的值，char優(yōu)于varchar，因?yàn)楣潭ㄩL(zhǎng)度行不容易產(chǎn)生碎片?！?dāng)最長(zhǎng)長(zhǎng)度遠(yuǎn)大于平均長(zhǎng)度，并且很少發(fā)生更新的時(shí)候，通常適合使用varchar。

　　2>>對(duì)于很短的列，char的效率也是高于varchar的。如對(duì)于單字節(jié)字符集（如latin1)，char(1)只會(huì)占用1個(gè)字節(jié)，而varchar(2)會(huì)占用2個(gè)字節(jié)（有一個(gè)字節(jié)用來(lái)存儲(chǔ)長(zhǎng)度的信息）。

3、text

用于保存大量數(shù)據(jù)。

　　（1）InnoDB在它們較大的時(shí)候會(huì)使用“外部”存儲(chǔ)區(qū)域來(lái)進(jìn)行保存。——所以需要足夠的外部存儲(chǔ)空間來(lái)保存實(shí)際的值。

　?。?）排序方式不同于其他字符類型，不會(huì)按照完整長(zhǎng)度進(jìn)行排序，而只是按照max_sort_length規(guī)定的前若干個(gè)字節(jié)進(jìn)行排序。

4、使用ENUM代替字符串類型

　?。?）ENUM列可以存儲(chǔ)65 535個(gè)不同的字符串。

　?。?）以緊湊方式保存。根據(jù)列表中值的數(shù)量，把它們壓縮到1到2個(gè)字節(jié)中。

　?。?）MySQL在內(nèi)部把每個(gè)值都保存為整數(shù)，以表示值在列表中的位置。

　?。?）保留了一份“查找表”，來(lái)表示整數(shù)和字符串在表的.frm文件中的映射關(guān)系。

　?。?）ENUM字符列是固定的，添加、刪除字符串須使用ALTER TABLE。

　?。?）使用案例：權(quán)限表中使用ENUM來(lái)保存Y值和N值。

使用方法：

在對(duì)enum列使用order by的時(shí)候，是按數(shù)字排序的，而不是字符串排序。

日期和時(shí)間類型

DATETIME：保存大范圍的值。封裝格式：YYYYMMDDHHMMSS?！c時(shí)區(qū)無(wú)關(guān)，使用8字節(jié)存儲(chǔ)空間。

TIMESTAMP：保存自1970年1月1日午夜（格林尼治標(biāo)準(zhǔn)時(shí)間）以來(lái)的秒數(shù)?！褂?字節(jié)存儲(chǔ)空間。

通常使用TIMESTAMP，它比DATETIME更節(jié)約空間。有時(shí)人們把Unix的時(shí)間戳保存為整數(shù)值，但是這通常沒(méi)有任何好處?！@種格式處理起來(lái)不太方便，我們并不推薦它。

經(jīng)驗(yàn)交談

　　1、我們?cè)跒榱羞x擇數(shù)據(jù)類型的時(shí)候，不僅要考慮存儲(chǔ)類型大小，還要考慮MySQL如何對(duì)它們進(jìn)行計(jì)算和比較。例如：MySQL在內(nèi)部把ENUM和SET類型保存為整數(shù)，但是在比較的時(shí)候把它們轉(zhuǎn)換為字符串。

　　2、我們要在相關(guān)表中使用同樣的類型，類型之間要精確匹配，包括諸如UNSIGNED這樣的屬性。

　　3、混合不同的數(shù)據(jù)類型會(huì)導(dǎo)致性能問(wèn)題，即使沒(méi)有性能問(wèn)題，隱式的類型轉(zhuǎn)換也能導(dǎo)致難以察覺(jué)的錯(cuò)誤。

　　4、選擇最小的數(shù)據(jù)類型要考慮將來(lái)留出的增長(zhǎng)空間。如，中國(guó)的省份，我們知道不會(huì)有成千上萬(wàn)個(gè)，因此不必用INT。TINYINT就足夠了，它比INT小3字節(jié)。

　　5、整數(shù)通常是最佳的數(shù)據(jù)類型，因?yàn)樗俣瓤?，并且能使用AUTO_INCREMENT。

　　6、要盡可能避免字符串做為列的數(shù)據(jù)類型，因?yàn)樗鼈冋加昧撕芏嗫臻g并且通常必整數(shù)類型要慢。MyISAM默認(rèn)情況下為字符串使用了壓縮索引，這使查找更為緩慢。

總結(jié)

若有錯(cuò)誤，望請(qǐng)大俠指教一、二，不勝感激！

參考文獻(xiàn):《高性能MYSQL》

您可能感興趣的文章: