當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL 请选择合适的列！

發布時間：2024/8/26 数据库 27 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL 请选择合适的列！小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

思維導圖點擊圖片，可查看大圖。

介紹情況：如果你的表結構設計不良或你的索引設計不佳，那么請你優化你的表結構設計和給予合適的索引，這樣你的查詢性能就能提高幾個數量級。——數據越大，索引的價值越能體現出來。我們要提高性能，需要考慮的因素： 1、設計架構 2、設計索引 3、評估查詢性能

今天要講的是表列的設計，暫不談索引設計。我會在下一章講索引設計。選擇數據類型選擇正確的數據類型，對于提高性能至關重要。下面給出幾種原則，有利于幫助你選擇何種類型。 1、更小通常更好。使用最小的數據類型。——更少的磁盤空間，內存和CPU緩存。而且需要的CPU的周期也更少。 2、簡單就好。整數代價小于字符。——因為字符集和排序規則使字符比較更復雜。 1>mysql內建類型（如timestamp,date)優于使用字符串保存。 2>使用整數保存ip地址。 3、盡量避免NULL——如果計劃對列進行索引，盡量避免把列設置為NULL 盡可能把字段定義為NOT NULL。——可以放置一個默認值，如‘’，0，特殊字符串。原因：（1）MYSQL難以優化NULL列。NULL列會使索引，索引統計和值更加復雜。（2）NULL列需要更多的存儲空間，還需要在MYSQL內部進行特殊處理。（3）NULL列加索引，每條記錄都需要一個額外的字節，還導致MyISAM中固定大小的索引變成可變大小的索引。

決定列的數據類型，我們應該遵循下面兩步。

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

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

　　很多數據類型能夠保存同類型的數據，但是我們要發現，他們在存儲的范圍，精度和物理空間之間的差別（磁盤或內存空間）。如：datetime和timestamp能保存同樣類型的數據：日期和時間。——timestamp使用datetime一半的空間;能保存時區;擁有特殊的自動更新能力。?

品味數據類型整數 1、存儲類型——數據范圍為-2^(n-1)到2^(n-1)-1，這里的n是所需存儲空間的位數。

?　　類型名稱	占用位數　　?	?　　數據范圍
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屬性表示不允許負數，并大致把正上限提高了一倍。如TINYINT UNSIGNED保存的范圍為0到255而不是-127到128

3、MYSQL對整數類型定義寬度，比如int（1）和int（22）對于存儲和計算是一樣的。只規定了MYSQL的交互工具（如命令行客戶端）用來顯示字符的個數。

實數實數有分數部分（小數部分）。存儲類型：FLOAT和DOUBLE，DECIMAL。占用大小：FLOAT 4個字節，DOUBLE 8個字節。DECIMAL受到MYSQL版本影響，早期版本254個數字，5.0以上65個數字。區別：1、FLOAT和DOUBLE支持標準浮點運算進行近似計算。 2、DECIMAL進行DECIMAL運算，CPU并不支持對它進行直接計算。浮點運算會快一點，因為計算直接在CPU上進行。 3、DECIMAL只是一個存儲格式，在計算時會被轉換為DOUBLE類型。 4、DECIMAL(18,9)使用9個字節，小數點前4個字節，小數點1個字節，小數點后4個字節。 5、DECIMAL只有對小數進行精確計算的時候才使用它，如保存金融數據。

字符串類型 1、varchar （1）保存可變長字符串。理解：比固定長度占用更少的存儲空間，因為它只占用自己需要的空間。例外情況：使用ROW_FORMAT=FIXED創建的MyISAM表，它為每行使用固定長度的空間，可能會造成浪費。（2）存儲長度信息。如果定義的列小于或等于255，則使用1個字節存儲長度值，假設使用latin1字符集，如varchar（10）將占用11個字節的存儲空間。反過來，varchar（1000），則占用1002個字節的存儲空間。（3）節約空間，對性能有幫助。（4）5.0版本以上，無論是取值還是保存，MySQL都會保留字符串末尾的空格。 只分配真正需要的空間
使用varchar(5)和varchar(200)保存'hello'占用空間是一樣的。——這里應該指的是磁盤上的空間。
那么使用較短列有何優勢？——巨大的優勢
　　較大的列會使用更多的內存，因為MySQL通常會分配固定大小的內存塊（如varchar(200)會用200個字符大小的內存空間）來保存值(然后對值進行trim操作，最后放入磁盤)或取值。——這對排序或使用基于內存的臨時表尤其不好。

2、char （1）固定長度。（2）保存值時，去掉末尾的空格。

? ? ? ? ? ?? 咱們再看看varchar

（3）char常用于很短字符串或長度近似相同的字符串的時候很有用。如存儲用戶密碼的MD5哈希值，它的長度總是一樣的。 char優于varchar的地方？ 1>> 對于經常改變的值，char優于varchar，因為固定長度行不容易產生碎片。——當最長長度遠大于平均長度，并且很少發生更新的時候，通常適合使用varchar。 2>>對于很短的列，char的效率也是高于varchar的。如對于單字節字符集（如latin1)，char(1)只會占用1個字節，而varchar(2)會占用2個字節（有一個字節用來存儲長度的信息）。

3、text 用于保存大量數據。（1）InnoDB在它們較大的時候會使用“外部”存儲區域來進行保存。——所以需要足夠的外部存儲空間來保存實際的值。（2）排序方式不同于其他字符類型，不會按照完整長度進行排序，而只是按照max_sort_length規定的前若干個字節進行排序。

4、使用ENUM代替字符串類型（1）ENUM列可以存儲65 535個不同的字符串。（2）以緊湊方式保存。根據列表中值的數量，把它們壓縮到1到2個字節中。（3）MySQL在內部把每個值都保存為整數，以表示值在列表中的位置。（4）保留了一份“查找表”，來表示整數和字符串在表的.frm文件中的映射關系。（5）ENUM字符列是固定的，添加、刪除字符串須使用ALTER TABLE。（6）使用案例：權限表中使用ENUM來保存Y值和N值。使用方法：

? ? ? ? ? ???

在對enum列使用order by的時候，是按數字排序的，而不是字符串排序。

日期和時間類型

DATETIME：保存大范圍的值。封裝格式：YYYYMMDDHHMMSS。——與時區無關，使用8字節存儲空間。

TIMESTAMP：保存自1970年1月1日午夜（格林尼治標準時間）以來的秒數。——使用4字節存儲空間。

通常使用TIMESTAMP，它比DATETIME更節約空間。有時人們把Unix的時間戳保存為整數值，但是這通常沒有任何好處。——這種格式處理起來不太方便，我們并不推薦它。

? 經驗交談 1、我們在為列選擇數據類型的時候，不僅要考慮存儲類型大小，還要考慮MySQL如何對它們進行計算和比較。例如：MySQL在內部把ENUM和SET類型保存為整數，但是在比較的時候把它們轉換為字符串。 2、我們要在相關表中使用同樣的類型，類型之間要精確匹配，包括諸如UNSIGNED這樣的屬性。 3、混合不同的數據類型會導致性能問題，即使沒有性能問題，隱式的類型轉換也能導致難以察覺的錯誤。 4、選擇最小的數據類型要考慮將來留出的增長空間。如，中國的省份，我們知道不會有成千上萬個，因此不必用INT。TINYINT就足夠了，它比INT小3字節。 5、整數通常是最佳的數據類型，因為它速度快，并且能使用AUTO_INCREMENT。 6、要盡可能避免字符串做為列的數據類型，因為它們占用了很多空間并且通常必整數類型要慢。MyISAM默認情況下為字符串使用了壓縮索引，這使查找更為緩慢。總結若有錯誤，望請大俠指教一、二，不勝感激！參考文獻:《高性能MYSQL》推薦思維導圖點擊圖片，可查看大圖。