MySQL數據庫的數據類型和索引

數據庫的數據庫索引對程序員來說是透明的，意味著數據庫建立索引之前和之后，你的SQL語句都可以正常運行，索引的運用只是數據庫引擎工作時候的優化手段。但是，這不是意味著數據庫索引僅僅是數據庫設計和運維者的事情，對于一個程序員如果對數據庫已有的索引有所了解，還是可以大大優化程序員數據庫的查詢和修改語句執行效率的，以免你的低效查詢語句稱為拖累整個系統性能的Black Sheep。

本文對MySQL數據類型和索引建立、優化進行整理，現在數據庫引擎默認都是InnoDB的，而且目前MySQL/MariaDB應用于生產環境時候，應該都是用的這個引擎吧。

1. MySQL的數據類型

1.1 數字類型和時間類型

數字類型算是最簡單的了，主要差異在于各個類型的取值范圍大小限制，和對存儲空間字節數的需求。數字類型當然是在滿足情況的條件下越短越好，一方面MySQL每行有65535字節長度的限制，同時更寬的數據類型意味著對CPU、內存、磁盤I/O帶來壓力。

MySQL支持的定點數字類型和占用字節數分別是

類型	長度
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INTEGER	4
BIGINT	5

在數據庫設計的時候，常常看到這些整形有個前綴長度，其實這對其類型本身的存儲長度和精度沒有影響，只會關系到某些交互式工具顯示出來的字符個數。

MySQL支持的浮點（實數）類型和占用字節數為

類型	長度
FLOAT	4
FLOAT(p) [0,24]	4
FLOAT(p) [25,53]	8
DOUBLE,REAL	8

計算機的浮點運算都是不精確的，如果要實現精確浮點運算，就需要使用DECIMAL類型。

時間類型

常被使用的是DATE、DATETIME和TIMESTAMP類型，其表示的范圍為：

DATE：’1000-01-01’ to ‘9999-12-31’

DATETIME：’1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’

TIMESTAMP：’1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC

TIMESTAMP存儲的范圍比DATETIME要小，但是空間利用率也最高。MySQL支持的時間精度最高為1s，如果更精確的存儲，就必須自己定義存儲格式了。

1.2 字符串類型

MySQL中的字符串類型比較多也比較的復雜，各個字符串類型的差別不僅僅在存儲時候的空間占用，對存取時候字段某位的strip和padding還有差異。

對于類型CHAR/VARCHAR/TEXT是跟本地字符集相關的，這會影響到實際占用空間的字節數、字符比較等。

CHAR(M)/VARCHAR(M)

長度限制參數M表示的是本地字符集的字符個數而不是bytes數目，比如對于UTF8編碼，每個本地字符其實際占用的byte長度可能是3或4倍的本地字符長度。比如VARCHAR(255)，如果每個本地字符占用兩個字節，那么其需要的存儲空間最大為255x2+2。

CHAR的M被限制在最大255，而VARCHAR的M理論上受限于Row Size的長度(65,535bytes)，且實際存儲時候會附加1~2字節的前綴表示數據實際長度。如果strict SQL模式沒有被打開，那么當插入數據超過聲明長度限制的時候，數據將會被截斷并產生警告信息，在strict SQL模式下將會出錯。

CHAR類型在存儲的時候，會在右端padding SPACE到指定的M長度，當取該字段的時候，所有末尾的SPACE都將會被strip掉然后返回；VARCHAR不會對進行SPACE進行padding以及strip操作，存儲什么樣的數據就會返回什么樣的數據。

對于CHAR/VARCHAR/TEXT類型，在進行字符串比較的時候，（SQL語句參數中的字符串）結尾的空格都是不參與比較的，但是對于LIKE語句，檢索的時候結尾的空格是考慮在內的。

BINARY(M)/VARBINARY(M)

BINARY/VARBINARY在操作的時候，參考的是byte streaming而不是charaset streaming，所以其長度限制參數M表示的是byte數目，在比較的時候也是直接的數字大小比較（而非本地字符集方式比較）。

BINARY在插入的時候，會使用0x00（而非SPACE）padding到長度M，取值的時候不會進行strip尾部空字符的操作（意味著取出來的長度一定是M）；VARBINARY則是保證原樣存取的。

BLOB/TEXT

分別有TINY/MEDIUM/LONG類型的衍生長度，BLOB是bytes streaming類型的，而TEXT是基于character streaming本地字符集類型的，兩者在存取的時候都不會進行padding和strip操作。

BLOB/TEXT的關系和之前的VARBINARY/VARCHAR是比較相似的，除了：BLOB/TEXT不能夠有DEFAULT值；BLOB/TEXT在創建索引的時候必須要有prefix length，而前者是可選的；給予TEXT索引需要有前綴長度，而且建立索引會自動padding SPACE到索引長度，所以如果插入的字符前面一樣，只是尾部空字符長度不同，也是會產生相同的索引值。

字符串各個類型占用的空間長度

長度
CHAR(M)	Mxw bytes
BINARY(M)	M bytes
VARCHAR(M), VARBINARY(M)	L+1/L+2 bytes
TINYBLOB, TINYTEXT	L+1 bytes
LOB, TEXT	L+2 bytes
MEDIUMBLOB, MEDIUMTEXT	L+3 bytes
LONGBLOB, LONGTEXT	L+4 bytes

根據官方手冊，CHAR/BINARY及其衍生的類型的數據是存儲在表的行內部(inline)的，而對于BLOB和TEXT類型，每一個字段只占用該行9-12（1~4+8）個字節（用于數據的地址和長度），實際的數據是存儲在Row Buffer之外位置的。所以對于經常訪問的字符串類型，而長度又不是特別的大，還是建議用VARCHAR(M)的數據類型，性能會比TEXT快不少。

2. MySQL數據庫索引

數據庫索引可以用來快速找到需要的行，否則的話MySQL就需要一行一行的遍歷，查詢效率自然相當的低。

MySQL支持的索引包括PRIMARY KEY、UNIQUE、INDEX、FULLTEXT類型的索引。前面說過，FULLTEXT類型的全文索引 在中文下基本是報廢的 ，在此就不予討論了。

特別注意的是，對于索引列只能使用單純的列名，而不能是表達式或者函數的一部分，比如age+2、TO_DAYS(date_col)，引擎在檢索的時候才能使用索引。

2.1 索引的類型

PRIMARY KEY

在InnoDB內部，表數據是優化主鍵快速查詢而排列分布的，其查找速度是最快的（相當于聚簇索引：該索引中鍵值的邏輯順序決定了表中相應行的物理順序）。即使表中沒有適合做主鍵的列，也推薦采用一個自動增長的整數主鍵（代理鍵），那么這個表在增加數據的時候是順序存放的，而且后續在別的表參考該外鍵查詢的時候也會得到優化。本身在設計表的時候，也建議常用的數據額不常用的數據分表存放以增加效率。

INDEX

普通索引，對數據沒有約束要求，多行記錄可以包含相同值。無論對于字符串索引，還是多列組合索引，都以及在查詢語句中，都有個最左前綴的原則：

(1) 對于字符串類型，可以指定索引前綴長度（且對于BLOB/TEXT前綴長度參數是必須的），在InnoDB表中其前綴長度最長是767 bytes，且參數M是用bytes計量的。所以太長的字符串，建立BTree索引浪費比較大，這時候用 手動模擬HASH索引 是個方法，不過這種方式對字符串無法靈活的使用前綴方式查詢（例如LIKE這類的操作）。

(2) 在建立多列索引的時候，必須按照從左到右的順序使用全部或部分的索引列，才能充分的使用組合索引，比如：(col1, col2, col3)使用(col1)、(col1, col2)、(col1, col2, col3)有效。在查詢語句中會一直向右匹配直到遇到范圍查詢(>,<,BETWEEN,LIKE)就停止匹配，其后的索引列將不會使用索引來優化查找了。

(3) 索引不是建立的越多、越長越好，因為索引除了占用空間之外，對后續數據庫的增加、刪除、修改都有額外的操作來更新索引，所以對索引列和字符串前綴長度，都參考選擇性（Selectivity）這個指標來確定：選擇性定義為不重復的索引值和數據總記錄條數的比值，其選擇性越高，那么索引的查詢效率也越高，對于性別這種參數，建立索引根本沒有意義。

• UNIQUE

• UNIQUE索引要求索引是唯一的。對于單列索引，要求該列所有數據都不相同，但允許有NULL值；對于多列的組合索引，要求這些列的組合是唯一的。UNIQUE索引其本身既可以作為索引，實際中也可以用以產生數據約束，防止增加或者修改后產生相同數據。

2.2 B+Tree和HASH

• B+Tree

• 該類型的索引中，列記錄都是按照順序排列的，可以優化用于比較或者范圍查找操作(=, >, >=, <, <=, BETWEEN, IN)，以及用于(GROUP BY, ORDER BY)操作，而且對于字符串類型的索引，最左前綴字符串也可以充分利用索引，比如LIKE ‘Patrick%’會解釋成 ‘Patrick’ <= key_col < ‘Patricl’。

• HASH

• 速度更快，不過只能用于 =、<=>、IN操作符；優化器不能用于ORDER BY操作；任何查找操作必須是索引的完整列。

 

 

 

 

來自：https://mp.weixin.qq.com/s?__biz=MzAwNjA3NDMyOA==&mid=2659762829&idx=4&sn=b390f4af894806af00c4f862b8ccb613&chksm=806e983ab719112cc265d8f1fd3542b993a26117d089a36e6d98d296952bff501db6e2b5685b&scene=0&key=&ascene=7&uin=&devicetype=android-23&version=26031933&nettype=WIFI