更進(jìn)一步學(xué)習(xí)MySQL

138 張圖帶你 MySQL 入門

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

MySQL 存儲(chǔ)引擎

存儲(chǔ)引擎概述

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

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

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

并發(fā)

支持事務(wù)

完整性約束

物理存儲(chǔ)

支持索引

性能幫助

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

MyISAM

InnoDB

BDB

MEMORY

MERGE

EXAMPLE

NDB Cluster

ARCHIVE

CSV

BLACKHOLE

FEDERATED

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

show variables like 'table_type';

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

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

show engines g

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

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

上圖我們指定了 MyISAM 的存儲(chǔ)引擎。

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

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

如上圖所示，我們沒有指定默認(rèn)的存儲(chǔ)引擎，下面查看一下表

可以看到，默認(rèn)的存儲(chǔ)引擎是 InnoDB。

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

alter table cxuan003 engine = myisam;

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

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

存儲(chǔ)引擎特性

下面會(huì)介紹幾個(gè)常用的存儲(chǔ)引擎以及它的基本特性，這些存儲(chǔ)引擎是 **MyISAM、InnoDB、MEMORY 和 MERGE **

MyISAM

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

不支持事務(wù)操作，ACID 的特性也就不存在了，這一設(shè)計(jì)是為了性能和效率考慮的。

不支持外鍵操作，如果強(qiáng)行增加外鍵，MySQL 不會(huì)報(bào)錯(cuò)，只不過外鍵不起作用。

MyISAM 默認(rèn)的鎖粒度是表級(jí)鎖，所以并發(fā)性能比較差，加鎖比較快，鎖沖突比較少，不太容易發(fā)生死鎖的情況。

MyISAM 會(huì)在磁盤上存儲(chǔ)三個(gè)文件，文件名和表名相同，擴(kuò)展名分別是 .frm(存儲(chǔ)表定義)、.MYD(MYData,存儲(chǔ)數(shù)據(jù))、MYI(MyIndex,存儲(chǔ)索引)。這里需要特別注意的是 MyISAM 只緩存索引文件，并不緩存數(shù)據(jù)文件。

MyISAM 支持的索引類型有 全局索引(Full-Text)、B-Tree 索引、R-Tree 索引

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

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

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

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

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

InnoDB

自從 MySQL 5.1 之后，默認(rèn)的存儲(chǔ)引擎變成了 InnoDB 存儲(chǔ)引擎，相對(duì)于 MyISAM，InnoDB 存儲(chǔ)引擎有了較大的改變，它的主要特點(diǎn)是

支持事務(wù)操作，具有事務(wù) ACID 隔離特性，默認(rèn)的隔離級(jí)別是可重復(fù)讀(repetable-read)、通過MVCC（并發(fā)版本控制）來實(shí)現(xiàn)的。能夠解決臟讀和不可重復(fù)讀的問題。

InnoDB 支持外鍵操作。

InnoDB 默認(rèn)的鎖粒度行級(jí)鎖，并發(fā)性能比較好，會(huì)發(fā)生死鎖的情況。

和 MyISAM 一樣的是，InnoDB 存儲(chǔ)引擎也有 .frm文件存儲(chǔ)表結(jié)構(gòu) 定義，但是不同的是，InnoDB 的表數(shù)據(jù)與索引數(shù)據(jù)是存儲(chǔ)在一起的，都位于 B+ 數(shù)的葉子節(jié)點(diǎn)上，而 MyISAM 的表數(shù)據(jù)和索引數(shù)據(jù)是分開的。

InnoDB 有安全的日志文件，這個(gè)日志文件用于恢復(fù)因數(shù)據(jù)庫(kù)崩潰或其他情況導(dǎo)致的數(shù)據(jù)丟失問題，保證數(shù)據(jù)的一致性。

InnoDB 和 MyISAM 支持的索引類型相同，但具體實(shí)現(xiàn)因?yàn)槲募Y(jié)構(gòu)的不同有很大差異。

增刪改查性能方面，果執(zhí)行大量的增刪改操作，推薦使用 InnoDB 存儲(chǔ)引擎，它在刪除操作時(shí)是對(duì)行刪除，不會(huì)重建表。

MEMORY

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

MERGE

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

選擇合適的存儲(chǔ)引擎

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

MyISAM：如果應(yīng)用程序通常以檢索為主，只有少量的插入、更新和刪除操作，并且對(duì)事物的完整性、并發(fā)程度不是很高的話，通常建議選擇 MyISAM 存儲(chǔ)引擎。

InnoDB：如果使用到外鍵、需要并發(fā)程度較高，數(shù)據(jù)一致性要求較高，那么通常選擇 InnoDB 引擎，一般互聯(lián)網(wǎng)大廠對(duì)并發(fā)和數(shù)據(jù)完整性要求較高，所以一般都使用 InnoDB 存儲(chǔ)引擎。

MEMORY：MEMORY 存儲(chǔ)引擎將所有數(shù)據(jù)保存在內(nèi)存中，在需要快速定位下能夠提供及其迅速的訪問。MEMORY 通常用于更新不太頻繁的小表，用于快速訪問取得結(jié)果。

MERGE：MERGE 的內(nèi)部是使用 MyISAM 表，MERGE 表的優(yōu)點(diǎn)在于可以突破對(duì)單個(gè) MyISAM 表大小的限制，并且通過將不同的表分布在多個(gè)磁盤上， 可以有效地改善 MERGE 表的訪問效率。

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

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

CHAR 和 VARCHAR 的選擇

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

值	char(5)	存儲(chǔ)字節(jié)
''	' '	5個(gè)字節(jié)
'cx'	'cx '	5個(gè)字節(jié)
'cxuan'	'cxuan'	5個(gè)字節(jié)
'cxuan007'	'cxuan'	5個(gè)字節(jié)

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

?

這里注意一點(diǎn)：如果 MySQL 使用了非 嚴(yán)格模式的話，上面表格最后一行是可以存儲(chǔ)的。如果 MySQL 使用了 嚴(yán)格模式 的話，那么表格上面最后一行存儲(chǔ)會(huì)報(bào)錯(cuò)。

?

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

值	varchar(5)	存儲(chǔ)字節(jié)
''	''	1個(gè)字節(jié)
'cx'	'cx '	3個(gè)字節(jié)
'cxuan'	'cxuan'	6個(gè)字節(jié)
'cxuan007'	'cxuan'	6個(gè)字節(jié)

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

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

在 MySQL 中，不同的存儲(chǔ)引擎對(duì) CHAR 和 VARCHAR 的使用原則也有不同

MyISAM：建議使用固定長(zhǎng)度的數(shù)據(jù)列替代可變長(zhǎng)度的數(shù)據(jù)列，也就是 CHAR

MEMORY：使用固定長(zhǎng)度進(jìn)行處理、CHAR 和 VARCHAR 都會(huì)被當(dāng)作 CHAR 處理

InnoDB：建議使用 VARCHAR 類型

TEXT 與 BLOB

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

TEXT

MEDIUMTEXT

LONGTEXT

BLOB 往下細(xì)分有

BLOB

MEDIUMBLOB

LONGBLOB

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

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

也可以使用合成索引來提高文本字段（BLOB 和 TEXT）的查詢性能。合成索引就是根據(jù)大文本（BLOB 和 TEXT）字段的內(nèi)容建立一個(gè)散列值，把這個(gè)值存在對(duì)應(yīng)列中，這樣就能夠根據(jù)散列值查找到對(duì)應(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 列來進(jìn)行查詢

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

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

非必要的時(shí)候不要檢索 BLOB 和 TEXT 索引

把 BLOB 或 TEXT 列分離到單獨(dú)的表中。

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

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

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

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

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

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

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

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

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

日期類型選擇

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

138 張圖帶你 MySQL 入門

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

TIMESTAMP 和時(shí)區(qū)相關(guān)，更能反映當(dāng)前時(shí)間，如果記錄的日期需要讓不同時(shí)區(qū)的人使用，最好使用 TIMESTAMP。

DATE 用于表示年月日，如果實(shí)際應(yīng)用值需要保存年月日的話就可以使用 DATE。

TIME 用于表示時(shí)分秒，如果實(shí)際應(yīng)用值需要保存時(shí)分秒的話就可以使用 TIME。

YEAR 用于表示年份，YEAR 有 2 位（最好使用4位）和 4 位格式的年。默認(rèn)是4位。如果實(shí)際應(yīng)用只保存年份，那么用 1 bytes 保存 YEAR 類型完全可以。不但能夠節(jié)約存儲(chǔ)空間，還能提高表的操作效率。

MySQL 字符集

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

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

字符集	是否定長(zhǎng)	編碼方式
ASCII	是	單字節(jié) 7 位編碼
ISO-8859-1	是	單字節(jié) 8 位編碼
GBK	是	雙字節(jié)編碼
UTF-8	否	1 - 4 字節(jié)編碼
UTF-16	否	2 字節(jié)或 4 字節(jié)編碼
UTF-32	是	4 字節(jié)編碼

對(duì)數(shù)據(jù)庫(kù)來說，字符集是很重要的，因?yàn)閿?shù)據(jù)庫(kù)存儲(chǔ)的數(shù)據(jù)大多數(shù)都是各種文字，字符集對(duì)數(shù)據(jù)庫(kù)的存儲(chǔ)、性能、系統(tǒng)的移植來說都非常重要。

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

或者使用

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

來查看。

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

索引的設(shè)計(jì)和使用

我們上面介紹到了索引的幾種類型并對(duì)不同的索引類型做了闡述，闡明了優(yōu)缺點(diǎn)等等，下面我們從設(shè)計(jì)角度來聊一下索引，關(guān)于索引，你必須要知道的一點(diǎn)就是：「索引是數(shù)據(jù)庫(kù)用來提高性能的最常用工具」。

索引概述

所有的 MySQL 類型都可以進(jìn)行索引，對(duì)相關(guān)列使用索引是提高 SELECT 查詢性能的最佳途徑。MyISAM 和 InnoDB 都是使用 BTREE 作為索引，MySQL 5 不支持函數(shù)索引，但是支持 前綴索引。

前綴索引顧名思義就是對(duì)列字段的前綴做索引，前綴索引的長(zhǎng)度和存儲(chǔ)引擎有關(guān)系。MyISAM 前綴索引的長(zhǎng)度支持到 1000 字節(jié)，InnoDB 前綴索引的長(zhǎng)度支持到 767 字節(jié)，索引值重復(fù)性越低，查詢效率也就越高。

在 MySQL 中，主要有下面這幾種索引

全局索引(FULLTEXT)：全局索引，目前只有 MyISAM 引擎支持全局索引，它的出現(xiàn)是為了解決針對(duì)文本的模糊查詢效率較低的問題，并且只限于 CHAR、VARCHAR 和 TEXT 列。

哈希索引(HASH)：哈希索引是 MySQL 中用到的唯一 key-value 鍵值對(duì)的數(shù)據(jù)結(jié)構(gòu)，很適合作為索引。HASH 索引具有一次定位的好處，不需要像樹那樣逐個(gè)節(jié)點(diǎn)查找，但是這種查找適合應(yīng)用于查找單個(gè)鍵的情況，對(duì)于范圍查找，HASH 索引的性能就會(huì)很低。默認(rèn)情況下，MEMORY 存儲(chǔ)引擎使用 HASH 索引，但也支持 BTREE 索引。

B-Tree 索引：B 就是 Balance 的意思，BTree 是一種平衡樹，它有很多變種，最常見的就是 B+ Tree，它被 MySQL 廣泛使用。

R-Tree 索引：R-Tree 在 MySQL 很少使用，僅支持 geometry 數(shù)據(jù)類型，支持該類型的存儲(chǔ)引擎只有MyISAM、BDb、InnoDb、NDb、Archive幾種，相對(duì)于 B-Tree 來說，R-Tree 的優(yōu)勢(shì)在于范圍查找。

索引可以在創(chuàng)建表的時(shí)候進(jìn)行創(chuàng)建，也可以單獨(dú)創(chuàng)建，下面我們采用單獨(dú)創(chuàng)建的方式，我們?cè)?cxuan004 上創(chuàng)建前綴索引

我們使用 explain 進(jìn)行分析，可以看到 cxuan004 使用索引的情況

如果不想使用索引，可以刪除索引，索引的刪除語法是

索引設(shè)計(jì)原則

創(chuàng)建索引的時(shí)候，要盡量考慮以下原則，便于提升索引的使用效率。

選擇索引位置，選擇索引最合適的位置是出現(xiàn)在 where 語句中的列，而不是 select 關(guān)鍵字后的選擇列表中的列。

選擇使用唯一索引，顧名思義，唯一索引的值是唯一的，可以更快速的確定某條記錄，例如學(xué)生的學(xué)號(hào)就適合使用唯一性索引，而學(xué)生的性別則不適合使用，因?yàn)椴还芩阉髂膫€(gè)值，都差不多有一半的行。

為經(jīng)常使用的字段建立索引，如果某個(gè)字段經(jīng)常用作查詢條件，那么這個(gè)字段的查詢速度在極大程度上影響整個(gè)表的查詢速度，因此為這樣的字段建立索引，可以提高整個(gè)表的查詢速度。

不要過度索引，限制索引數(shù)目，索引的數(shù)目不是越多越好，每個(gè)索引都會(huì)占據(jù)磁盤空間，索引越多，需要的磁盤空間就越大。

盡量使用前綴索引，如果索引的值很長(zhǎng)，那么查詢速度會(huì)受到影響，這個(gè)時(shí)候應(yīng)該使用前綴索引，對(duì)列的某幾個(gè)字符進(jìn)行索引，可以提高檢索效率。

利用最左前綴，在創(chuàng)建一個(gè) n 列的索引時(shí)，實(shí)際上是創(chuàng)建了 MySQL 可利用的 n 個(gè)索引。多列索引可以起到幾個(gè)索引的作用，利用索引最左邊的列來匹配行，這樣的列稱為最左前綴。

對(duì)于使用 InnoDB 存儲(chǔ)引擎的表來說，記錄會(huì)按照一定的順序保存。如果有明確的主鍵定義，那么會(huì)按照主鍵的順序進(jìn)行保存；如果沒有主鍵，但是有唯一索引，那么就按照唯一索引的順序進(jìn)行保存。如果既沒有主鍵又沒有唯一索引，那么表中會(huì)自動(dòng)生成一個(gè)內(nèi)部列，按照這個(gè)列的順序進(jìn)行保存。一般來說，使用主鍵的順序是最快的

刪除不再使用或者很少使用的索引

視圖

MySQL 從 5.0 開始就提供了視圖功能，下面我們對(duì)視圖功能進(jìn)行介紹。

什么是視圖

視圖的英文名稱是 view，它是一種虛擬存在的表。視圖對(duì)于用戶來說是透明的，它并不在數(shù)據(jù)庫(kù)中實(shí)際存在，視圖是使用數(shù)據(jù)庫(kù)行和列動(dòng)態(tài)組成的表，那么視圖相對(duì)于數(shù)據(jù)庫(kù)表來說，優(yōu)勢(shì)體現(xiàn)在哪里？

視圖相對(duì)于普通的表來說，優(yōu)勢(shì)包含下面這幾項(xiàng)

使用視圖可以簡(jiǎn)化操作：使用視圖我們不用關(guān)注表結(jié)構(gòu)的定義，我們可以把經(jīng)常使用的數(shù)據(jù)集合定義成視圖，這樣能夠簡(jiǎn)化操作。

安全性：用戶對(duì)視圖不可以隨意的更改和刪除，可以保證數(shù)據(jù)的安全性。

數(shù)據(jù)獨(dú)立性：一旦視圖的結(jié)構(gòu) 確定了， 可以屏蔽表結(jié)構(gòu)變化對(duì)用戶的影響， 數(shù)據(jù)庫(kù)表增加列對(duì)視圖沒有影響；具有一定的獨(dú)立性

對(duì)視圖的操作

視圖的操作包括創(chuàng)建或者修改視圖、刪除視圖以及查看視圖定義。

創(chuàng)建或修改視圖

使用 create view 來創(chuàng)建視圖

為了演示功能，我們先創(chuàng)建一張表 product 表，有三個(gè)字段，id，name，price，下面是建表語句

create table product(id int(11),name varchar(20),price float(10,2));

然后我們向其中插入幾條數(shù)據(jù)

insert into product values(1, "apple","3.5"),(2,"banana","4.2"),(3,"melon","1.2");

插入完成后的表結(jié)構(gòu)如下

然后我們創(chuàng)建視圖

create view v1 as select * from product;

然后我們查看一下 v1 視圖的結(jié)構(gòu)

可以看到我們把 product 中的數(shù)據(jù)放在了視圖中，也相當(dāng)于是創(chuàng)建了一個(gè) product 的副本，只不過這個(gè)副本跟表無關(guān)。

視圖使用

show tables;

也能看到所有的視圖。

刪除視圖的語法是

drop view v1;

能夠直接進(jìn)行刪除。

視圖還有其他操作，比如查詢操作

你還可以使用

describe v1;

查看表結(jié)構(gòu)

更新視圖

update v1 set name = "grape" where id = 1;

存儲(chǔ)過程

MySQL 從 5.0 開始起就支持存儲(chǔ)過程和函數(shù)了。

那么什么是存儲(chǔ)過程呢？

「存儲(chǔ)過程是在數(shù)據(jù)庫(kù)系統(tǒng)中完成一組特定功能的 SQL 語句集」，它存儲(chǔ)在數(shù)據(jù)庫(kù)系統(tǒng)中，一次編譯后永久有效。那么使用存儲(chǔ)過程有什么優(yōu)點(diǎn)呢？

使用存儲(chǔ)過程具有可封裝性，能夠隱藏復(fù)雜的 SQL 邏輯。

存儲(chǔ)過程可以接收參數(shù)，并返回結(jié)果

存儲(chǔ)過程性能非常高，一般用于批量執(zhí)行語句

使用存儲(chǔ)過程有什么缺點(diǎn)？

存儲(chǔ)過程編寫復(fù)雜

存儲(chǔ)過程對(duì)數(shù)據(jù)庫(kù)的依賴性比較強(qiáng)，可移植性比較差

存儲(chǔ)過程使用

存儲(chǔ)過程創(chuàng)建

在認(rèn)識(shí)到存儲(chǔ)過程是什么之后，我們就來使用一下存儲(chǔ)過程，這里需要先了解一個(gè)小技巧，也就是 delimiter 的用法，delimiter 用于自定義結(jié)束符，什么意思呢，如果你使用

delimiter $$

的話，那么你在 sql 語句末使用 ; 是不能使 SQL 語句執(zhí)行的，不信？我們可以看下

可以看到，我們?cè)?SQL 語句的行末使用了 ; 但是我們卻沒有看到執(zhí)行結(jié)果。下面我們使用

delimiter ;

恢復(fù)默認(rèn)的執(zhí)行條件再來看下

我們創(chuàng)建存儲(chǔ)過程首先要把 ; 替換為 $$，下面是一個(gè)存儲(chǔ)過程的創(chuàng)建語句

mysql> delimiter $$ mysql> create procedure sp_product() -> begin -> select * from product; -> end $$

存儲(chǔ)過程實(shí)際上是一種函數(shù)，所以創(chuàng)建完畢后，我們可以使用 call 方法來調(diào)用這個(gè)存儲(chǔ)過程

因?yàn)槲覀兩厦娑x了使用 delimiter $$ 來結(jié)尾，所以這里也應(yīng)該使用。

存儲(chǔ)過程也可以接受參數(shù)，比如我們定義一種接收參數(shù)的情況

然后我們使用 call 調(diào)用這個(gè)存儲(chǔ)過程

可以看到，當(dāng)我們調(diào)用 id = 2 的時(shí)候，存儲(chǔ)過程的 SQL 語句相當(dāng)于是

select * from product where id = 2;

所以只查詢出 id = 2 的結(jié)果。

存儲(chǔ)過程刪除

一次只能刪除一個(gè)存儲(chǔ)過程，刪除存儲(chǔ)過程的語法如下

drop procedure sp_product ;

直接使用 sp_product 就可以了，不用加 ()。

存儲(chǔ)過程查看

存儲(chǔ)過程創(chuàng)建后，用戶可能需要需要查看存儲(chǔ)過程的狀態(tài)等信息，便于了解存儲(chǔ)過程的基本情況

我們可以使用

show create procedure proc_name;

變量的使用

在 MySQL 中，變量可分為兩大類，即系統(tǒng)變量和用戶變量，這是一種粗略的分法。但是根據(jù)實(shí)際應(yīng)用又被細(xì)化為四種類型，即局部變量、用戶變量、會(huì)話變量和全局變量。

用戶變量

用戶變量是基于會(huì)話變量實(shí)現(xiàn)的，可以暫存，用戶變量與連接有關(guān)，也就是說一個(gè)客戶端定義的變量不能被其他客戶端使用看到。當(dāng)客戶端退出時(shí)，鏈接會(huì)自動(dòng)釋放。我們可以使用 set 語句設(shè)置一個(gè)變量

set @myId = "cxuan";

然后使用 select 查詢條件可以查詢出我們剛剛設(shè)置的用戶變量

用戶變量是和客戶端有關(guān)系，當(dāng)我們退出后，這個(gè)變量會(huì)自動(dòng)消失，現(xiàn)在我們退出客戶端

exit

現(xiàn)在我們重新登陸客戶端，再次使用 select 條件查詢

發(fā)現(xiàn)已經(jīng)沒有這個(gè) @myId 了。

局部變量

MySQL 中的局部變量與 Java 很類似 ，Java 中的局部變量是 Java 所在的方法或者代碼塊，而 MySQL 中的局部變量作用域是所在的存儲(chǔ)過程。MySQL 局部變量使用 declare 來聲明。

會(huì)話變量

服務(wù)器會(huì)為每個(gè)連接的客戶端維護(hù)一個(gè)會(huì)話變量?？梢允褂?/p>

show session variables;

顯示所有的會(huì)話變量。

我們可以手動(dòng)設(shè)置會(huì)話變量

set session auto_increment_increment=1; 或者使用 set @@session.auto_increment_increment=2;

然后進(jìn)行查詢，查詢會(huì)話變量使用

或者使用

全局變量

當(dāng)服務(wù)啟動(dòng)時(shí)，它將所有全局變量初始化為默認(rèn)值。其作用域?yàn)?server 的整個(gè)生命周期。

可以使用

show global variables;

查看全局變量

可以使用下面這兩種方式設(shè)置全局變量

set global sql_warnings=ON; -- global不能省略 /** 或者 **/ set @@global.sql_warnings=OFF;

查詢?nèi)肿兞繒r(shí)，可以使用

或者是

MySQL 流程語句介紹

MySQL 支持下面這些控制語句

IF

IF 用于實(shí)現(xiàn)邏輯判斷，滿足不同條件執(zhí)行不同的 SQL 語句

IF ... THEN ...

CASE

CASE 實(shí)現(xiàn)比 IF 稍微復(fù)雜，語法如下

CASE ... WHEN ... THEN... ... END CASE

CASE 語句也可以使用 IF 來完成

LOOP

LOOP 用于實(shí)現(xiàn)簡(jiǎn)單的循環(huán)

label:LOOP ... END LOOP label;

如果 ... 中不寫 SQL 語句的話，那么就是一個(gè)簡(jiǎn)單的死循環(huán)語句

LEAVE

用來表示從標(biāo)注的流程構(gòu)造中退出，通常和 BEGIN...END 或者循環(huán)一起使用

ITERATE

ITERATE 語句必須用在循環(huán)中，作用是跳過當(dāng)前循環(huán)的剩下的語句，直接進(jìn)入下一輪循環(huán)。

REPEAT

帶有條件的循環(huán)控制語句，當(dāng)滿足條件的時(shí)候退出循環(huán)。

REPEAT ... UNTIL END REPEAT;

WHILE

WHILE 語句表示的含義和 REPEAT 相差無幾，WHILE 循環(huán)和 REPEAT 循環(huán)的區(qū)別在于：WHILE 是滿足條件才執(zhí)行循環(huán)，REPEAT 是滿足條件退出循環(huán)；

觸發(fā)器

MySQL 從 5.0 開始支持觸發(fā)器，觸發(fā)器一般作用在表上，在滿足定義條件時(shí)觸發(fā)，并執(zhí)行觸發(fā)器中定義的語句集合，下面我們就來一起認(rèn)識(shí)一下觸發(fā)器。

舉個(gè)例子來認(rèn)識(shí)一下觸發(fā)器：比如你有一個(gè)日志表和金額表，你每錄入一筆金額就要進(jìn)行日志表的記錄，你會(huì)怎么樣？同時(shí)在金額表和日志表插入數(shù)據(jù)嗎？如果有了觸發(fā)器，你可以直接在金額表錄入數(shù)據(jù)，日志表會(huì)自動(dòng)插入一條日志記錄，當(dāng)然，觸發(fā)器不僅只有新增操作，還有更新和刪除操作。

創(chuàng)建觸發(fā)器

我們可以用如下的方式創(chuàng)建觸發(fā)器

create trigger triggername triggertime triggerevent on tbname for each row triggerstmt

上面涉及到幾個(gè)參數(shù)，我知道你有點(diǎn)懵逼，解釋一下。

triggername：這個(gè)指的就是觸發(fā)器的名字

triggertime：這個(gè)指的就是觸發(fā)器觸發(fā)時(shí)機(jī)，是 BEFORE 還是 AFTER

triggerevent: 這個(gè)指的就是觸發(fā)器觸發(fā)事件，一共有三種事件：「INSERT、UPDATE 或者 DELETE」。

tbname：這個(gè)參數(shù)指的是觸發(fā)器創(chuàng)建的表名，在哪個(gè)表上創(chuàng)建

triggerstmt: 觸發(fā)器的程序體，也就是 SQL 語句

所以，可以創(chuàng)建六種觸發(fā)器

「BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE」

上面的 for each now 表示任何一條記錄上的操作都會(huì)觸發(fā)觸發(fā)器。

下面我們通過一個(gè)例子來演示一下觸發(fā)器的操作

我們還是用上面的 procuct 表做例子，我們創(chuàng)建一個(gè) product_info 產(chǎn)品信息表。

create table product_info(p_info varchar(20));

然后我們創(chuàng)建一個(gè) trigger

我們?cè)?product 表中插入一條數(shù)據(jù)

insert into product values(4,"pineapple",15.3);

我們進(jìn)行 select 查詢，可以看到現(xiàn)在 product 表中有四條數(shù)據(jù)

我們沒有向 product_info 表中插入數(shù)據(jù)，現(xiàn)在我們來看一下 product_info 表中，我們預(yù)想到是有數(shù)據(jù)的，具體來看下

這條數(shù)據(jù)是什么時(shí)候插入的呢？我們?cè)趧?chuàng)建觸發(fā)器 tg_pinfo 的時(shí)候插入了的這條數(shù)據(jù)。

刪除觸發(fā)器

觸發(fā)器可以使用 drop 進(jìn)行刪除，具體刪除語法如下

drop trigger tg_pinfo;

和刪除表的語法是一樣的

查看觸發(fā)器

我們經(jīng)常會(huì)查看觸發(fā)器，可以通過執(zhí)行 show triggers 命令查看觸發(fā)器的狀態(tài)、語法等信息。

另一種查詢方式是查詢表中的 information_schema.triggers 表，這個(gè)可以查詢指定觸發(fā)器的指定信息，操作起來方便很多

觸發(fā)器的作用

在添加一條數(shù)據(jù)前，檢查數(shù)據(jù)是否合理，例如檢查郵件格式是否正確

刪除數(shù)據(jù)后，相當(dāng)于數(shù)據(jù)備份的作用

可以記錄數(shù)據(jù)庫(kù)的操作日志，也可以作為表的執(zhí)行軌跡

?

注意：觸發(fā)器的使用有兩個(gè)限制

觸發(fā)程序不能調(diào)用將數(shù)據(jù)返回客戶端的存儲(chǔ)程序。也不能使用 CALL 語句的動(dòng)態(tài) SQL 語句。

不能在觸發(fā)器中開始和結(jié)束語句，例如 START TRANSACTION

責(zé)任編輯：xj

原文標(biāo)題：47 張圖帶你 MySQL 進(jìn)階?。?！

文章出處：【微信公眾號(hào)：數(shù)據(jù)分析與開發(fā)】歡迎添加關(guān)注！文章轉(zhuǎn)載請(qǐng)注明出處。