[[332335]]

本文轉(zhuǎn)載自微信公眾號「 Java建設(shè)者」，作者cxuan 。轉(zhuǎn)載本文請聯(lián)系Java建設(shè)者公眾號。

我們在 MySQL 入門篇主要介紹了基本的 SQL 命令、數(shù)據(jù)類型和函數(shù)，在具備以上知識后，你就可以進行 MySQL 的開發(fā)工作了，但是如果要成為一個合格的開發(fā)人員，你還要具備一些更高級的技能，下面我們就來探討一下 MySQL 都需要哪些高級的技能。

MySQL 存儲引擎

存儲引擎概述

數(shù)據(jù)庫最核心的一點就是用來存儲數(shù)據(jù)，數(shù)據(jù)存儲就避免不了和磁盤打交道。那么數(shù)據(jù)以哪種方式進行存儲，如何存儲是存儲的關(guān)鍵所在。所以存儲引擎就相當(dāng)于是數(shù)據(jù)存儲的發(fā)動機，來驅(qū)動數(shù)據(jù)在磁盤層面進行存儲。

MySQL 的架構(gòu)可以按照三層模式來理解

存儲引擎也是 MySQL 的組建，它是一種軟件，它所能做的和支持的功能主要有

• 并發(fā)
• 支持事務(wù)
• 完整性約束
• 物理存儲
• 支持索引
• 性能幫助

MySQL 默認支持多種存儲引擎，來適用不同數(shù)據(jù)庫應(yīng)用，用戶可以根據(jù)需要選擇合適的存儲引擎，下面是 MySQL 支持的存儲引擎

• MyISAM
• InnoDB
• BDB
• MEMORY
• MERGE
• EXAMPLE
• NDB Cluster
• ARCHIVE
• CSV
• BLACKHOLE
• FEDERATED

默認情況下，如果創(chuàng)建表不指定存儲引擎，會使用默認的存儲引擎，如果要修改默認的存儲引擎，那么就可以在參數(shù)文件中設(shè)置 default-table-type，能夠查看當(dāng)前的存儲引擎

show variables like 'table_type'; 

奇怪，為什么沒有了呢?網(wǎng)上求證一下，在 5.5.3 取消了這個參數(shù)

可以通過下面兩種方法查詢當(dāng)前數(shù)據(jù)庫支持的存儲引擎

show engines \g 

在創(chuàng)建新表的時候，可以通過增加 ENGINE 關(guān)鍵字設(shè)置新建表的存儲引擎。

create table cxuan002(id int(10),name varchar(20)) engine = MyISAM; 

上圖我們指定了 MyISAM 的存儲引擎。

如果你不知道表的存儲引擎怎么辦?你可以通過 show create table 來查看

如果不指定存儲引擎的話，從MySQL 5.1 版本之后，MySQL 的默認內(nèi)置存儲引擎已經(jīng)是 InnoDB了。建一張表看一下

如上圖所示，我們沒有指定默認的存儲引擎，下面查看一下表

可以看到，默認的存儲引擎是 InnoDB。

如果你的存儲引擎想要更換，可以使用

alter table cxuan003 engine = myisam; 

來更換，更換完成后會顯示 「0 rows affected」 ，但其實已經(jīng)操作成功

我們使用 show create table 查看一下表的 sql 就知道

存儲引擎特性

下面會介紹幾個常用的存儲引擎以及它的基本特性，這些存儲引擎是 MyISAM、InnoDB、MEMORY 和 MERGE

MyISAM

在 5.1 版本之前，MyISAM 是 MySQL 的默認存儲引擎，MyISAM 并發(fā)性比較差，使用的場景比較少，主要特點是

•  不支持事務(wù)操作，ACID 的特性也就不存在了，這一設(shè)計是為了性能和效率考慮的。
• 不支持外鍵操作，如果強行增加外鍵，MySQL 不會報錯，只不過外鍵不起作用。
• MyISAM 默認的鎖粒度是表級鎖，所以并發(fā)性能比較差，加鎖比較快，鎖沖突比較少，不太容易發(fā)生死鎖的情況。
• MyISAM 會在磁盤上存儲三個文件，文件名和表名相同，擴展名分別是 .frm(存儲表定義)、.MYD(MYData,存儲數(shù)據(jù))、MYI(MyIndex,存儲索引)。這里需要特別注意的是 MyISAM 只緩存索引文件，并不緩存數(shù)據(jù)文件。
• MyISAM 支持的索引類型有 全局索引(Full-Text)、B-Tree 索引、R-Tree 索引
   

Full-Text 索引：它的出現(xiàn)是為了解決針對文本的模糊查詢效率較低的問題。

B-Tree 索引：所有的索引節(jié)點都按照平衡樹的數(shù)據(jù)結(jié)構(gòu)來存儲，所有的索引數(shù)據(jù)節(jié)點都在葉節(jié)點

R-Tree索引：它的存儲方式和 B-Tree 索引有一些區(qū)別，主要設(shè)計用于存儲空間和多維數(shù)據(jù)的字段做索引,目前的 MySQL 版本僅支持 geometry 類型的字段作索引，相對于 BTREE，RTREE 的優(yōu)勢在于范圍查找。

數(shù)據(jù)庫所在主機如果宕機，MyISAM 的數(shù)據(jù)文件容易損壞，而且難以恢復(fù)。

增刪改查性能方面：SELECT 性能較高，適用于查詢較多的情況

InnoDB

自從 MySQL 5.1 之后，默認的存儲引擎變成了 InnoDB 存儲引擎，相對于 MyISAM，InnoDB 存儲引擎有了較大的改變，它的主要特點是

• 支持事務(wù)操作，具有事務(wù) ACID 隔離特性，默認的隔離級別是可重復(fù)讀(repetable-read)、通過MVCC(并發(fā)版本控制)來實現(xiàn)的。能夠解決臟讀和不可重復(fù)讀的問題。
• InnoDB 支持外鍵操作。
• InnoDB 默認的鎖粒度行級鎖，并發(fā)性能比較好，會發(fā)生死鎖的情況。
• 和 MyISAM 一樣的是，InnoDB 存儲引擎也有 .frm文件存儲表結(jié)構(gòu) 定義，但是不同的是，InnoDB 的表數(shù)據(jù)與索引數(shù)據(jù)是存儲在一起的，都位于 B+ 數(shù)的葉子節(jié)點上，而 MyISAM 的表數(shù)據(jù)和索引數(shù)據(jù)是分開的。
• InnoDB 有安全的日志文件，這個日志文件用于恢復(fù)因數(shù)據(jù)庫崩潰或其他情況導(dǎo)致的數(shù)據(jù)丟失問題，保證數(shù)據(jù)的一致性。
• InnoDB 和 MyISAM 支持的索引類型相同，但具體實現(xiàn)因為文件結(jié)構(gòu)的不同有很大差異。
• 增刪改查性能方面，果執(zhí)行大量的增刪改操作，推薦使用 InnoDB 存儲引擎，它在刪除操作時是對行刪除，不會重建表。

MEMOR

YMEMORY 存儲引擎使用存在內(nèi)存中的內(nèi)容來創(chuàng)建表。每個 MEMORY 表實際只對應(yīng)一個磁盤文件，格式是 .frm。MEMORY 類型的表訪問速度很快，因為其數(shù)據(jù)是存放在內(nèi)存中。默認使用 HASH 索引。

MERGE

MERGE 存儲引擎是一組 MyISAM 表的組合，MERGE 表本身沒有數(shù)據(jù)，對 MERGE 類型的表進行查詢、更新、刪除的操作，實際上是對內(nèi)部的 MyISAM 表進行的。MERGE 表在磁盤上保留兩個文件，一個是 .frm 文件存儲表定義、一個是 .MRG 文件存儲 MERGE 表的組成等。

選擇合適的存儲引擎

在實際開發(fā)過程中，我們往往會根據(jù)應(yīng)用特點選擇合適的存儲引擎。

• MyISAM：如果應(yīng)用程序通常以檢索為主，只有少量的插入、更新和刪除操作，并且對事物的完整性、并發(fā)程度不是很高的話，通常建議選擇 MyISAM 存儲引擎。
• InnoDB：如果使用到外鍵、需要并發(fā)程度較高，數(shù)據(jù)一致性要求較高，那么通常選擇 InnoDB 引擎，一般互聯(lián)網(wǎng)大廠對并發(fā)和數(shù)據(jù)完整性要求較高，所以一般都使用 InnoDB 存儲引擎。
• MEMORY：MEMORY 存儲引擎將所有數(shù)據(jù)保存在內(nèi)存中，在需要快速定位下能夠提供及其迅速的訪問。MEMORY 通常用于更新不太頻繁的小表，用于快速訪問取得結(jié)果。
• MERGE：MERGE 的內(nèi)部是使用 MyISAM 表，MERGE 表的優(yōu)點在于可以突破對單個 MyISAM 表大小的限制，并且通過將不同的表分布在多個磁盤上， 可以有效地改善 MERGE 表的訪問效率。

選擇合適的數(shù)據(jù)類型

我們會經(jīng)常遇見的一個問題就是，在建表時如何選擇合適的數(shù)據(jù)類型，通常選擇合適的數(shù)據(jù)類型能夠提高性能、減少不必要的麻煩，下面我們就來一起探討一下，如何選擇合適的數(shù)據(jù)類型。

CHAR 和 VARCHAR 的選擇

char 和 varchar 是我們經(jīng)常要用到的兩個存儲字符串的數(shù)據(jù)類型，char 一般存儲定長的字符串，它屬于固定長度的字符類型，比如下面

可以看到，不管你的值寫的是什么，一旦指定了 char 字符的長度，如果你的字符串長度不夠指定字符的長度的話，那么就用空格來填補，如果超過字符串長度的話，只存儲指定字符長度的字符。

❝這里注意一點：如果 MySQL 使用了非 嚴格模式的話，上面表格最后一行是可以存儲的。如果 MySQL 使用了 嚴格模式 的話，那么表格上面最后一行存儲會報錯。❞

如果使用了 varchar 字符類型，我們來看一下例子

可以看到，如果使用 varchar 的話，那么存儲的字節(jié)將根據(jù)實際的值進行存儲。你可能會疑惑為什么 varchar 的長度是 5 ，但是卻需要存儲 3 個字節(jié)或者 6 個字節(jié)，這是因為使用 varchar 數(shù)據(jù)類型進行存儲時，默認會在最后增加一個字符串長度，占用1個字節(jié)(如果列聲明的長度超過255，則使用兩個字節(jié))。varchar 不會填充空余的字符串。

一般使用 char 來存儲定長的字符串，比如「身份證號、手機號、郵箱等」;使用 varchar 來存儲不定長的字符串。由于 char 長度是固定的，所以它的處理速度要比 VARCHAR 快很多，但是缺點是浪費存儲空間，但是隨著 MySQL 版本的不斷演進，varchar 數(shù)據(jù)類型的性能也在不斷改進和提高，所以在許多應(yīng)用中，VARCHAR 類型更多的被使用。

在 MySQL 中，不同的存儲引擎對 CHAR 和 VARCHAR 的使用原則也有不同

• MyISAM：建議使用固定長度的數(shù)據(jù)列替代可變長度的數(shù)據(jù)列，也就是 CHAR
• MEMORY：使用固定長度進行處理、CHAR 和 VARCHAR 都會被當(dāng)作 CHAR 處理
• InnoDB：建議使用 VARCHAR 類型

TEXT 與 BLOB

一般在保存較少的文本的時候，我們會選擇 CHAR 和 VARCHAR，在保存大數(shù)據(jù)量的文本時，我們往往選擇 TEXT 和 BLOB;TEXT 和 BLOB 的主要差別是 BLOB 能夠保存二進制數(shù)據(jù);而 TEXT 只能保存字符數(shù)據(jù)，TEXT 往下細分有

• TEXT
• MEDIUMTEXT
• LONGTEXT

BLOB 往下細分有

• BLOB
• MEDIUMBLOB
• LONGBLOB

三種，它們最主要的區(qū)別就是存儲文本長度不同和存儲字節(jié)不同，用戶應(yīng)該根據(jù)實際情況選擇滿足需求的最小存儲類型，下面主要對 BLOB 和 TEXT 存在一些問題進行介紹

TEXT 和 BLOB 在刪除數(shù)據(jù)后會存在一些性能上的問題，為了提高性能，建議使用 OPTIMIZE TABLE 功能對表進行碎片整理。

也可以使用合成索引來提高文本字段(BLOB 和 TEXT)的查詢性能。合成索引就是根據(jù)大文本(BLOB 和 TEXT)字段的內(nèi)容建立一個散列值，把這個值存在對應(yīng)列中，這樣就能夠根據(jù)散列值查找到對應(yīng)的數(shù)據(jù)行。一般使用散列算法比如 md5() 和 SHA1() ，如果散列算法生成的字符串帶有尾部空格，就不要把它們存在 CHAR 和 VARCHAR 中，下面我們就來看一下這種使用方式

首先創(chuàng)建一張表，表中記錄 blob 字段和 hash 值

向 cxuan005 中插入數(shù)據(jù)，其中 hash 值作為 info 的散列值。

然后再插入兩條數(shù)據(jù)

插入一條 info 為 cxuan005 的數(shù)據(jù)

如果想要查詢 info 為 cxuan005 的數(shù)據(jù)，可以通過查詢 hash 列來進行查詢

這是合成索引的例子，如果要對 BLOB 進行模糊查詢的話，就要使用前綴索引。

其他優(yōu)化 BLOB 和 TEXT 的方式：

• 非必要的時候不要檢索 BLOB 和 TEXT 索引
• 把 BLOB 或 TEXT 列分離到單獨的表中。

浮點數(shù)和定點數(shù)的選擇

浮點數(shù)指的就是含有小數(shù)的值，浮點數(shù)插入到指定列中超過指定精度后，浮點數(shù)會四舍五入，MySQL 中的浮點數(shù)指的就是 float 和 double，定點數(shù)指的是 decimal，定點數(shù)能夠更加精確的保存和顯示數(shù)據(jù)。下面通過一個示例講解一下浮點數(shù)精確性問題

首先創(chuàng)建一個表 cxuan006 ，只為了測試浮點數(shù)問題，所以這里我們選擇的數(shù)據(jù)類型是 float

然后分別插入兩條數(shù)據(jù)

然后執(zhí)行查詢，可以看到查詢出來的兩條數(shù)據(jù)執(zhí)行的舍入不同

為了清晰的看清楚浮點數(shù)與定點數(shù)的精度問題，再來看一個例子

先修改 cxuan006 的兩個字段為相同的長度和小數(shù)位數(shù)

然后插入兩條數(shù)據(jù)

執(zhí)行查詢操作，可以發(fā)現(xiàn)，浮點數(shù)相較于定點數(shù)來說，會產(chǎn)生誤差

日期類型選擇

在 MySQL 中，用來表示日期類型的有 「DATE、TIME、DATETIME、TIMESTAMP」，在

138 張圖帶你 MySQL 入門

這篇文中介紹過了日期類型的區(qū)別，我們這里就不再闡述了。下面主要介紹一下選擇

• TIMESTAMP 和時區(qū)相關(guān)，更能反映當(dāng)前時間，如果記錄的日期需要讓不同時區(qū)的人使用，最好使用 TIMESTAMP。
• DATE 用于表示年月日，如果實際應(yīng)用值需要保存年月日的話就可以使用 DATE。
• TIME 用于表示時分秒，如果實際應(yīng)用值需要保存時分秒的話就可以使用 TIME。
• YEAR 用于表示年份，YEAR 有 2 位(最好使用4位)和 4 位格式的年。默認是4位。如果實際應(yīng)用只保存年份，那么用 1 bytes 保存 YEAR 類型完全可以。不但能夠節(jié)約存儲空間，還能提高表的操作效率。

MySQL 字符集

下面來認識一下 MySQL 字符集，簡單來說字符集就是一套文字符號和編碼、比較規(guī)則的集合。1960 年美國標(biāo)準(zhǔn)化組織 ANSI 發(fā)布了第一個計算機字符集，就是著名的 ASCII(American Standard Code for Information Interchange) 。自從 ASCII 編碼后，每個國家、國際組織都研究了一套自己的字符集，比如 ISO-8859-1、GBK 等。

但是每個國家都使用自己的字符集為移植性帶來了很大的困難。所以，為了統(tǒng)一字符編碼，國際標(biāo)準(zhǔn)化組織(ISO) 指定了統(tǒng)一的字符標(biāo)準(zhǔn) - Unicode 編碼，它容納了幾乎所有的字符編碼。下面是一些常見的字符編碼

對數(shù)據(jù)庫來說，字符集是很重要的，因為數(shù)據(jù)庫存儲的數(shù)據(jù)大多數(shù)都是各種文字，字符集對數(shù)據(jù)庫的存儲、性能、系統(tǒng)的移植來說都非常重要。

MySQL 支持多種字符集，可以使用 show character set; 來查看所有可用的字符集

或者使用

select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;

來查看。

使用 information_schema.character_set 來查看字符集和校對規(guī)則。