MySQL 請選擇合適的列

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

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

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

介紹

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

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

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

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

3、評估查詢性能

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

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

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

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

1、更小通常更好。

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

2、簡單就好。

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

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

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

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

　　盡可能把字段定義為NOT NULL。——可以放置一個(gè)默認(rèn)值，如‘'，0，特殊字符串。

　　原因：

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

　　　　（2）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)，他們在存儲(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對整數(shù)類型定義寬度，比如int（1）和int（22）對于存儲(chǔ)和計(jì)算是一樣的。只規(guī)定了MYSQL的交互工具（如命令行客戶端）用來顯示字符的個(gè)數(shù)。

實(shí)數(shù)

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

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

占用大?。篎LOAT 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并不支持對它進(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只有對小數(shù)進(jìn)行精確計(jì)算的時(shí)候才使用它，如保存金融數(shù)據(jù)。

字符串類型

1、varchar

　?。?）保存可變長字符串。

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

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

　　（3）節(jié)約空間，對性能有幫助。

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

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