mysql修行練級之字元集，資料類型與儲存引擎選擇

標籤：mysql

如何選擇合適的儲存引擎

幾個常用儲存引擎的特點

下面我們重點介紹幾種常用的儲存引擎並對比各個儲存引擎之間的區別和推薦使用方式。

特點MyisamBDBMemoryInnoDBArchive

儲存限制沒有沒有有64TB沒有

事務安全 支援 支援 

鎖機制表鎖頁鎖表鎖行鎖行鎖

B樹索引支援支援支援支援 

雜湊索引 支援支援 

全文索引支援  

叢集索引 支援 

資料緩衝 支援支援 

索引緩衝支援 支援支援 

資料可壓縮支援 支援

空間使用低低N/A高非常低

記憶體使用量低低中等高低

批量插入的速度高高高低非常高

支援外鍵 支援 

最常使用的2種儲存引擎：

Myisam是Mysql的預設儲存引擎。當create建立新表時，未指定新表的儲存引擎時，預設使用Myisam。每個MyISAM在磁碟上儲存成三個檔案。檔案名稱都和表名相同，副檔名分別是.frm（儲存表定義）、.MYD (MYData，儲存資料)、.MYI (MYIndex，儲存索引)。資料檔案和索引檔案可以放置在不同的目錄，平均分布io，獲得更快的速度。   

InnoDB儲存引擎提供了具有提交、復原和崩潰恢複能力的事務安全。但是對比Myisam的儲存引擎，InnoDB寫的處理效率差一些並且會佔用更多的磁碟空間以保留資料和索引。

選擇標準：根據應用特點選擇合適的儲存引擎，對於複雜的應用系統可以根據實際情況選擇多種儲存引擎進行組合。

下面是常用儲存引擎的適用環境：

MyISAM：預設的MySQL外掛程式式儲存引擎，它是在Web、資料倉儲和其他應用環境下最常使用的儲存引擎之一

InnoDB：用於交易處理應用程式，具有眾多特性，包括ACID事務支援。

Memory：將所有資料儲存在RAM中，在需要快速尋找引用和其他類似資料的環境下，可提供極快的訪問。

Merge：允許MySQL DBA或開發人員將一系列等同的MyISAM表以邏輯方式組合在一起，並作為1個對象引用它們。對於諸如資料倉儲等VLDB環境十分適合。

選擇資料類型的基本原則

前提：使用適合儲存引擎。

選擇原則：根據選定的儲存引擎，確定如何選擇合適的資料類型。

下面的選擇方法按儲存引擎分類：

MyISAM 資料存放區引擎和資料列：MyISAM資料表，最好使用固定長度(CHAR)的資料列代替可變長度(VARCHAR)的資料列。

MEMORY儲存引擎和資料列：MEMORY資料表目前都使用固定長度的資料行儲存，因此無論使用CHAR或VARCHAR列都沒有關係。兩者都是作為CHAR類型處理的。

InnoDB 儲存引擎和資料列：建議使用 VARCHAR類型。

對於InnoDB資料表，內部的行儲存格式沒有區分固定長度和可變長度列（所有資料行都使用指向資料列值的頭指標），因此在本質上，使用固定長度的CHAR列不一定比使用可變長度VARCHAR列簡單。因而，主要的效能因素是資料行使用的儲存總量。由於CHAR平均佔用的空間多於VARCHAR，因 此使用VARCHAR來最小化需要處理的資料行的儲存總量和磁碟I/O是比較好的。

下面說一下固定長度資料列與可變長度的資料列。

char與varchar

CHAR和VARCHAR類型類似，但它們儲存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在儲存或檢索過程中不進行大小寫轉換。

下面的表顯示了將各種字串值儲存到CHAR(4)和VARCHAR(4)列後的結果，說明了CHAR和VARCHAR之間的差別：

值CHAR(4)儲存需求VARCHAR(4)儲存需求

‘‘‘    ‘4個位元組‘‘1個位元組

‘ab‘‘ab  ‘4個位元組‘ab ‘3個位元組

‘abcd‘‘abcd‘4個位元組‘abcd‘5個位元組

‘abcdefgh‘‘abcd‘4個位元組‘abcd‘5個位元組

請注意上表中最後一行的值只適用不使用strict 模式時；如果MySQL運行在strict 模式，超過列長度不的值不儲存，並且會出現錯誤。

從CHAR(4)和VARCHAR(4)列檢索的值並不總是相同，因為檢索時從CHAR列刪除了尾部的空格。通過下面的例子說明該差別：

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));

Query OK, 0 rows affected (0.02 sec)

 

mysql> INSERT INTO vc VALUES (‘ab  ‘, ‘ab  ‘);

Query OK, 1 row affected (0.00 sec)

 

mysql> SELECT CONCAT(v, ‘+‘), CONCAT(c, ‘+‘) FROM vc;

+----------------+----------------+

| CONCAT(v, ‘+‘) | CONCAT(c, ‘+‘) |

+----------------+----------------+

| ab  +          | ab+            |

+----------------+----------------+

1 row in set (0.00 sec)

text和blob

在使用text和blob欄位類型時要注意以下幾點，以便更好的發揮資料庫的效能。

①BLOB和TEXT值也會引起自己的一些問題，特別是執行了大量的刪除或更新操作的時候。刪除這種值會在資料表中留下很大的"空洞"，以後填入這些"空洞"的記錄可能長度不同,為了提高效能,建議定期使用 OPTIMIZE TABLE 功能對這類表進行磁碟重組.

②使用合成的（synthetic）索引。合成的索引列在某些時候是有用的。一種辦法是根據其它的列的內容建立一個散列值，並把這個值儲存在單獨的資料列中。接下來你就可以通過檢索散列值找到資料行了。但是，我們要注意這種技術只能用於精確匹配的查詢（散列值對於類似<或>=等範圍搜尋操作符 是沒有用處的）。我們可以使用MD5()函數產生散列值，也可以使用SHA1()或CRC32()，或者使用自己的應用程式邏輯來計算散列值。請記住數值型散列值可以很高效率地儲存。同樣，如果散列演算法產生的字串帶有尾部空格，就不要把它們儲存在CHAR或VARCHAR列中，它們會受到尾部空格去除的影響。

合成的散列索引對於那些BLOB或TEXT資料列特別有用。用散列標識符值尋找的速度比搜尋BLOB列本身的速度快很多。

③在不必要的時候避免檢索大型的BLOB或TEXT值。例如，SELECT *查詢就不是很好的想法，除非你能夠確定作為約束條件的WHERE子句只會找到所需要的資料行。否則，你可能毫無目的地在網路上傳輸大量的值。這也是 BLOB或TEXT標識符資訊儲存在合成的索引列中對我們有所協助的例子。你可以搜尋索引列，決定那些需要的資料行，然後從合格的資料行中檢索BLOB或 TEXT值。

④把BLOB或TEXT列分離到單獨的表中。在某些環境中，如果把這些資料列移動到第二張資料表中，可以讓你把原資料表中 的資料列轉換為固定長度的資料行格式，那麼它就是有意義的。這會減少主表中的片段，使你得到固定長度資料行的效能優勢。它還使你在主要資料表上運行 SELECT *查詢的時候不會通過網路傳輸大量的BLOB或TEXT值。

浮點數與定點數

為了能夠引起大家的重視，在介紹浮點數與定點數以前先讓大家看一個例子：

mysql> CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));

Query OK, 0 rows affected (0.29 sec)

mysql> insert into test values(131072.32,131072.32);

Query OK, 1 row affected (0.07 sec)

mysql> select * from test;

+-----------+-----------+

| c1        | c2        |

+-----------+-----------+

| 131072.31 | 131072.32 |

+-----------+-----------+

1 row in set (0.00 sec)

從上面的例子中我們看到c1列的值由131072.32變成了131072.31，這就是浮點數的不精確性造成的。

在mysql中float、double（或real）是浮點數，decimal（或numberic）是定點數。

浮點數相對於定點數的優點是在長度一定的情況下，浮點數能夠表示更大的資料範圍；它的缺點是會引起精度問題。在今後關於浮點數和定點數的應用中，大家要記住以下幾點：

浮點數存在誤差問題；

對貨幣等對精度敏感的資料，應該用定點數表示或儲存；

編程中，如果用到浮點數，要特別注意誤差問題，並盡量避免做浮點數比較；

要注意浮點數中一些特殊值的處理。

字元集概述

字元集是一套符號和編碼的規則，不論是在oracle資料庫還是在mysql資料庫，都存在字元集的選擇問題，而且如果在資料庫建立階段沒有正確選擇字元集，那麼可能在後期需要更換字元集，而字元集的更換是代價比較高的操作，也存在一定的風險，所以，我們推薦在應用開始階段，就按照需求正確的選擇合適的字元集，避免後期不必要的調整。

Mysql支援的字元集簡介

mysql伺服器可以支援多種字元集（可以用show character set命令查看所有mysql支援的字元集），在同一台伺服器、同一個資料庫、甚至同一個表的不同欄位都可以指定使用不同的字元集，相比oracle等其他資料庫管理系統，在同一個資料庫只能使用相同的字元集，mysql明顯存在更大的靈活性。

mysql的字元集包括字元集（CHARACTER）和校對規則（COLLATION）兩個概念。字元集是用來定義mysql儲存字串的方式，校對規則則是定義了比較字串的方式。字元集和校對規則是一對多的關係, MySQL支援30多種字元集的70多種校對規則。

每個字元集至少對應一個校對規則。可以用SHOW COLLATION LIKE ‘utf8%‘;命令查看相關字元集的校對規則。

Unicode簡述

Unicode是一種編碼規範。我們在這裡簡述一下Unicode編碼產生的曆史。

先從ASCII碼說起，ASCII碼也是一種編碼規範，只不過ASCII碼只能最多表示256個字元，是針對英文產生的，而面對中文、阿拉伯文之類的複雜文字，256個字元顯然是不夠用的。於是各個國家或組織都相繼制定了符合自己語言文字的標準，比如gb2312、big5等等。但是這種各自製定自己的標準的做法顯然是有很多弊端的，於是Unicode編碼規範應運而生。

Unicode也是一種字元編碼方法，不過它是由國際組織設計，可以容納全世界所有語言文字的編碼方案。Unicode的學名是"Universal Multiple-Octet Coded Character Set"，簡稱為UCS。UCS可以看作是"Unicode Character Set"的縮寫。

Unicode有兩套標準UCS-2和UCS-4，前者用2個位元組表示一個字元，後者用4個位元組表示一個字元。以目前常用的UCS-2為例，它可以表示的字元數為2^16=65535，基本上可以容納所有的歐美字元和絕大多數亞洲字元。

怎樣選擇合適的字元集

我們建議在能夠完全滿足應用的前提下，盡量使用小的字元集。因為更小的字元集意味著能夠節省空間的、減少網路傳輸位元組數，同時由於儲存空間的較小間接的提高了系統的效能。

有很多字元集可以儲存漢字，比如utf8、gb2312、gbk、latin1等等，但是常用的是gb2312和gbk。因為gb2312字型檔比gbk字型檔小，有些偏僻字（例如：洺）不能儲存，因此在選擇字元集的時候一定要權衡這些偏僻字在應用出現的幾率以及造成的影響，不能做出肯定回覆的話最好選用gbk。

Mysql字元集的設定

mysql的字元集和校對規則有4個層級的預設設定：伺服器級、資料庫級、表級和欄位級。分別在不同的地方設定，作用也不相同。

伺服器字元集和校對，在mysql服務啟動的時候確定。可以在my.cnf中設定：

    [mysqld]

    default-character-set=utf8

或者在啟動選項中指定：

    mysqld --default-character-set=utf8

或者在編譯的時候指定：

    ./configure --with-charset=utf8

如果沒有特別的指定伺服器字元集，預設使用latin1作為伺服器字元集。上面三種設定的方式都只指定了字元集，沒有指定校對規則，這樣是使用該字元集預設的校對規則，如果要使用該字元集的非預設校對規則，則需要在指定字元集的同時指定校對規則。

可以用show variables like ‘character_set_server‘;命令查詢當前伺服器的字元集和校對規則。

本文出自 “營運者說：從菜鳥到老鳥” 部落格，請務必保留此出處http://liuqunying.blog.51cto.com/3984207/1560964

mysql修行練級之字元集，資料類型與儲存引擎選擇