|
思維導(dǎo)圖
點擊圖片，可查看大圖。
介紹
情況：如果你的表結(jié)構(gòu)設(shè)計不良或你的索引設(shè)計不佳，那么請你優(yōu)化你的表結(jié)構(gòu)設(shè)計和給予合適的索引，這樣你的查詢性能就能提高幾個數(shù)量級。——數(shù)據(jù)越大，索引的價值越能體現(xiàn)出來。
我們要提高性能，需要考慮的因素：
1、設(shè)計架構(gòu)
2、設(shè)計索引
3、評估查詢性能
今天要講的是表列的設(shè)計，暫不談索引設(shè)計。我會在下一章講索引設(shè)計。
選擇數(shù)據(jù)類型
選擇正確的數(shù)據(jù)類型，對于提高性能至關(guān)重要。
下面給出幾種原則，有利于幫助你選擇何種類型。
1、更小通常更好。
使用最小的數(shù)據(jù)類型?！俚拇疟P空間，內(nèi)存和CPU緩存。而且需要的CPU的周期也更少。
2、簡單就好。
整數(shù)代價小于字符?！驗樽址团判蛞?guī)則使字符比較更復(fù)雜。
1>mysql內(nèi)建類型（如timestamp,date)優(yōu)于使用字符串保存。
2>使用整數(shù)保存ip地址。
3、盡量避免NULL——如果計劃對列進行索引，盡量避免把列設(shè)置為NULL
盡可能把字段定義為NOT NULL?！梢苑胖靡粋€默認值，如‘’，0，特殊字符串。
原因：
?。?）MYSQL難以優(yōu)化NULL列。NULL列會使索引，索引統(tǒng)計和值更加復(fù)雜。
?。?）NULL列需要更多的存儲空間，還需要在MYSQL內(nèi)部進行特殊處理。
（3）NULL列加索引，每條記錄都需要一個額外的字節(jié)，還導(dǎo)致MyISAM中固定大小的索引變成可變大小的索引。
決定列的數(shù)據(jù)類型，我們應(yīng)該遵循下面兩步。 第一步、大致決定數(shù)據(jù)類型?！袛嗍菙?shù)字，字符串還是時間等。這通常很直觀。 第二步、確定特定的類型。 很多數(shù)據(jù)類型能夠保存同類型的數(shù)據(jù)，但是我們要發(fā)現(xiàn)，他們在存儲的范圍，精度和物理空間之間的差別（磁盤或內(nèi)存空間）。如：datetime和timestamp能保存同樣類型的數(shù)據(jù)：日期和時間?！猼imestamp使用datetime一半的空間;能保存時區(qū);擁有特殊的自動更新能力。
品味數(shù)據(jù)類型
整數(shù)
1、存儲類型——數(shù)據(jù)范圍為-2^(n-1)到2^(n-1)-1，這里的n是所需存儲空間的位數(shù)。
2、unsigned屬性表示不允許負數(shù)，并大致把正上限提高了一倍。如TINYINT UNSIGNED保存的范圍為0到255而不是-127到128
3、MySQL對整數(shù)類型定義寬度，比如int（1）和int（22）對于存儲和計算是一樣的。只規(guī)定了MYSQL的交互工具（如命令行客戶端）用來顯示字符的個數(shù)。
實數(shù)
實數(shù)有分數(shù)部分（小數(shù)部分）。
存儲類型：FLOAT和DOUBLE，DECIMAL。
占用大?。篎LOAT 4個字節(jié)，DOUBLE 8個字節(jié)。DECIMAL受到MYSQL版本影響，早期版本254個數(shù)字，5.0以上65個數(shù)字。
區(qū)別：1、FLOAT和DOUBLE支持標(biāo)準(zhǔn)浮點運算進行近似計算。
2、DECIMAL進行DECIMAL運算，CPU并不支持對它進行直接計算。浮點運算會快一點，因為計算直接在CPU上進行。
3、DECIMAL只是一個存儲格式，在計算時會被轉(zhuǎn)換為DOUBLE類型。
4、DECIMAL(18,9)使用9個字節(jié)，小數(shù)點前4個字節(jié)，小數(shù)點1個字節(jié)，小數(shù)點后4個字節(jié)。
5、DECIMAL只有對小數(shù)進行精確計算的時候才使用它，如保存金融數(shù)據(jù)。
字符串類型
1、varchar
（1）保存可變長字符串。
理解：比固定長度占用更少的存儲空間，因為它只占用自己需要的空間。例外情況：使用ROW_FORMAT=FIXED創(chuàng)建的MyISAM表，它為每行使用固定長度的空間，可能會造成浪費。
?。?）存儲長度信息。如果定義的列小于或等于255，則使用1個字節(jié)存儲長度值，假設(shè)使用latin1字符集，如varchar（10）將占用11個字節(jié)的存儲空間。反過來，varchar（1000），則占用1002個字節(jié)的存儲空間。
?。?）節(jié)約空間，對性能有幫助。
?。?）5.0版本以上，無論是取值還是保存，MySQL都會保留字符串末尾的空格。
只分配真正需要的空間
2、char
?。?）固定長度。
?。?）保存值時，去掉末尾的空格。
?。?）char常用于很短字符串或長度近似相同的字符串的時候很有用。如存儲用戶密碼的MD5哈希值，它的長度總是一樣的。
char優(yōu)于varchar的地方？
1>> 對于經(jīng)常改變的值，char優(yōu)于varchar，因為固定長度行不容易產(chǎn)生碎片?！?dāng)最長長度遠大于平均長度，并且很少發(fā)生更新的時候，通常適合使用varchar。
2>>對于很短的列，char的效率也是高于varchar的。如對于單字節(jié)字符集（如latin1)，char(1)只會占用1個字節(jié)，而varchar(2)會占用2個字節(jié)（有一個字節(jié)用來存儲長度的信息）。
3、text
用于保存大量數(shù)據(jù)。
（1）InnoDB在它們較大的時候會使用“外部”存儲區(qū)域來進行保存。——所以需要足夠的外部存儲空間來保存實際的值。
?。?）排序方式不同于其他字符類型，不會按照完整長度進行排序，而只是按照max_sort_length規(guī)定的前若干個字節(jié)進行排序。
4、使用ENUM代替字符串類型
?。?）ENUM列可以存儲65 535個不同的字符串。
?。?）以緊湊方式保存。根據(jù)列表中值的數(shù)量，把它們壓縮到1到2個字節(jié)中。
?。?）MySQL在內(nèi)部把每個值都保存為整數(shù)，以表示值在列表中的位置。
?。?）保留了一份“查找表”，來表示整數(shù)和字符串在表的.frm文件中的映射關(guān)系。
?。?）ENUM字符列是固定的，添加、刪除字符串須使用ALTER TABLE。
?。?）使用案例：權(quán)限表中使用ENUM來保存Y值和N值。
使用方法：
在對enum列使用order by的時候，是按數(shù)字排序的，而不是字符串排序。
日期和時間類型
DATETIME：保存大范圍的值。封裝格式：YYYYMMDDHHMMSS?！c時區(qū)無關(guān)，使用8字節(jié)存儲空間。 TIMESTAMP：保存自1970年1月1日午夜（格林尼治標(biāo)準(zhǔn)時間）以來的秒數(shù)。——使用4字節(jié)存儲空間。
經(jīng)驗交談
1、我們在為列選擇數(shù)據(jù)類型的時候，不僅要考慮存儲類型大小，還要考慮MySQL如何對它們進行計算和比較。例如：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默認情況下為字符串使用了壓縮索引，這使查找更為緩慢。
總結(jié)
若有錯誤，望請大俠指教一、二，不勝感激！
參考文獻:《高性能MYSQL》
推薦
 原文地址：http://www.cnblogs.com/baochuan/archive/2012/05/23/2513224.html
|
|
|
咱們再看看varchar
