MySQL 請選擇合適的列

2020-01-19 00:00:11

字體：大中小

來源：轉載

供稿：網友

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

介紹

情況：如果你的表結構設計不良或你的索引設計不佳，那么請你優化你的表結構設計和給予合適的索引，這樣你的查詢性能就能提高幾個數量級。――數據越大，索引的價值越能體現出來。

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

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操作，最后放入磁盤)或取值。――這對排序或使用基于內存的臨時表尤其不好。