MySQL高級(jí)

一、索引

1.1 索引概述

MySQL官方對(duì)索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（有序）。在數(shù)據(jù)之外，數(shù)據(jù)庫(kù)系統(tǒng)還維護(hù)著滿(mǎn)足特定查找宣發(fā)的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。如下圖所示：

左邊是數(shù)據(jù)表，一共有2列7條記錄，最左邊的是數(shù)據(jù)記錄的物理地址（注意：邏輯上相鄰的記錄在磁盤(pán)上野并不一定是物理相鄰的）。為了加快Col2的查找，可以維護(hù)一個(gè)右邊所示的二叉查找樹(shù)，每個(gè)節(jié)點(diǎn)分別包含索引鍵值和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針，這樣就可以運(yùn)用二叉查找快速獲取到相應(yīng)的數(shù)據(jù)。

一般來(lái)說(shuō)索引本身野很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)在磁盤(pán)上。索引是數(shù)據(jù)庫(kù)中用來(lái)提高性能的最常用的工具。

1.2 索引的優(yōu)勢(shì)和劣勢(shì)

優(yōu)勢(shì)：

1. 類(lèi)似于書(shū)籍的目錄索引，可以提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫(kù)的IO成本
2. 通過(guò)索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低CPU消耗

劣勢(shì)：

1. 實(shí)際上索引也是一張表，該表中保存了主鍵與索引字段，并指向?qū)嶓w類(lèi)的記錄，所以索引列也是要占用空間的
2. 雖然索引大大提高了查詢(xún)效率，但同時(shí)也降低了更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE、DELETE，因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段，都會(huì)調(diào)整因?yàn)楦滤鶐?lái)的鍵值變化后的索引信息

1.3 索引的數(shù)據(jù)結(jié)構(gòu)

索引是在MySQL的存儲(chǔ)引擎層中實(shí)現(xiàn)的，而不是在服務(wù)器層實(shí)現(xiàn)的。所以每種存儲(chǔ)引擎的索引不一定完全相同，也不是所有的存儲(chǔ)引擎都支持所有的索引類(lèi)型。MySQL目前提供了以下4種索引：

• BTREE索引：最常見(jiàn)的索引類(lèi)型，大部分索引都支持B樹(shù)索引（默認(rèn)）
• HASH索引：只有Memory引擎支持，使用場(chǎng)景簡(jiǎn)單
• R-tree索引（空間索引）：空間索引是MyISAM引擎的一個(gè)特殊索引類(lèi)型，主要用于地理空間數(shù)據(jù)類(lèi)型
• Full-text（全文索引）：全文索引也是MyISAM的一個(gè)特殊索引類(lèi)型，主要用于全文索引，InnoDB從MySQL5.6開(kāi)始支持全文索引

我們平常所說(shuō)的索引，如果沒(méi)有特別指明，都是B+樹(shù)（MySQL中的BTREE索引就是B+樹(shù)，多路搜索樹(shù)）結(jié)構(gòu)組織的索引。其中聚集索引、復(fù)合索引、前綴索引、唯一索引默認(rèn)都是使用B+tree索引，統(tǒng)稱(chēng)為索引。

1.3.1 BTREE結(jié)構(gòu)

BTREE又叫多路平衡搜索樹(shù)，一顆m叉的BTREE特性如下：

• 樹(shù)種每個(gè)節(jié)點(diǎn)最多包含m個(gè)孩子
• 除根節(jié)點(diǎn)與葉子節(jié)點(diǎn)外，每個(gè)節(jié)點(diǎn)至少有[ceil(m/2)]個(gè)孩子
• 若根節(jié)點(diǎn)不是葉子節(jié)點(diǎn)，則至少有兩個(gè)孩子
• 所有的葉子節(jié)點(diǎn)都在同一層
• 每個(gè)非葉子節(jié)點(diǎn)（葉子節(jié)點(diǎn)沒(méi)有指針，因?yàn)闆](méi)有子節(jié)點(diǎn)）由n個(gè)key（我們實(shí)際存入的數(shù)據(jù)）與n+1個(gè)指針（指向該節(jié)點(diǎn)的子節(jié)點(diǎn)）組成，其中[ceil(m/2)-1] <= n <= m-1

以5叉BTREE為例，每個(gè)節(jié)點(diǎn)key的數(shù)量：公式推導(dǎo)[[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <= 4，當(dāng)n>4時(shí)，中間節(jié)點(diǎn)分裂到父節(jié)點(diǎn)，兩邊節(jié)點(diǎn)分裂。

插入 C N G A H E K Q M F W L T Z D P R X Y S為例，演變過(guò)程如下：

1. 插入前四個(gè)字母 C N G A

2. 插入H，此時(shí)因?yàn)椴迦際后n>4，所以中間元素G向上分裂到父節(jié)點(diǎn)

3. 插入E K Q不需要分裂

4. 插入M，此時(shí)M成為左子節(jié)點(diǎn)的中間元素，需要向上分裂到父節(jié)點(diǎn)，因?yàn)镸比G大，所以在G的右邊

5. 插入F W L T 不需要分裂

6. 插入Z，此時(shí)T成為最右邊的子節(jié)點(diǎn)的中間元素，需要向上分裂到父節(jié)點(diǎn)中

7. 插入D，此時(shí)D成為最左邊子節(jié)點(diǎn)的中間元素，需要向上分裂到父節(jié)點(diǎn)中。然后插入P R X Y不需要分裂

8. 最后插入S，S需要插入到NPQR節(jié)點(diǎn)，此時(shí)Q元素成為中間元素，需要向上分裂到父節(jié)點(diǎn)，Q分裂到父節(jié)點(diǎn)后，父節(jié)點(diǎn)為DGMQT，此時(shí)M為中間元素，需要繼續(xù)向上分裂到新的父節(jié)點(diǎn)

到此，該BTREE就已經(jīng)構(gòu)建完成了，B樹(shù)和二叉樹(shù)相比，查詢(xún)數(shù)據(jù)的效率更高，因?yàn)閷?duì)于相同的數(shù)據(jù)量來(lái)說(shuō)，B樹(shù)的層級(jí)結(jié)構(gòu)比二叉樹(shù)小，因此搜索速度更快

1.3.2 B+TREE結(jié)構(gòu)

B+樹(shù)為B樹(shù)的變種，B+樹(shù)和B樹(shù)的區(qū)別為：

1. n叉B+樹(shù)最多含有n個(gè)key，而B(niǎo)樹(shù)最多含有n-1個(gè)key
2. B+樹(shù)的葉子節(jié)點(diǎn)保存所有的key信息，依key大小順序排列
3. 所有的非葉子節(jié)點(diǎn)都可以看作是key的索引部分

1.3.3 MySQL中的B+TREE

MySQL索引數(shù)據(jù)結(jié)構(gòu)對(duì)經(jīng)典的B+樹(shù)進(jìn)行了優(yōu)化。在原B+樹(shù)的基礎(chǔ)上，增加一個(gè)指向相鄰葉子節(jié)點(diǎn)的鏈表指針，就形成了帶有順序指針的B+樹(shù)，提高區(qū)間訪(fǎng)問(wèn)性能。鏈表指針的作用是便于區(qū)間查找，比如要查找主鍵為9~15之間的數(shù)據(jù)，從磁盤(pán)塊4有鏈表指針指向磁盤(pán)塊5，在查找完主鍵9后，就不用再?gòu)拇疟P(pán)塊1開(kāi)始找索引，直接根據(jù)鏈表指針找到磁盤(pán)塊5即可。

MySQL中的B+樹(shù)示意圖：

1.4 索引分類(lèi)

• 單值索引（普通索引）：即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引
• 唯一索引：索引列的值必須唯一，但允許有空值；如果是組合唯一索引則組合值必須唯一
• 主鍵索引：與唯一索引的區(qū)別是不允許有空值
• 復(fù)合索引：即一個(gè)索引包含多個(gè)列
• 全文索引：對(duì)文本的內(nèi)容進(jìn)行分詞，進(jìn)行搜索（全文索引主要用于解決模糊查詢(xún)效率低的問(wèn)題）

1.5 索引語(yǔ)法

索引在創(chuàng)建表的時(shí)候，可以同時(shí)創(chuàng)建，也可以隨時(shí)增加新的索引。

準(zhǔn)備表：

CREATE TABLE city (
    city_id int(11) NOT NULL AUTO_INCREMENT,
    citi_name varchar(50) NOT NULL,
    country_id int(11) NOT NULL,
    PRIMARY KEY (city_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE country (
    country_id int(11) NOT NULL AUTO_INCREMENT,
    country_name varchar(50) NOT NULL,
    PRIMARY KEY (country_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO city (city_id, city_name, country_id) values(1, '西安', 1);
INSERT INTO city (city_id, city_name, country_id) values(2, '紐約', 2);
INSERT INTO city (city_id, city_name, country_id) values(3, '北京', 1);
INSERT INTO city (city_id, city_name, country_id) values(4, '上海', 1);

INSERT INTO country (country_id, city_name) values(1, '中國(guó)');
INSERT INTO country (country_id, city_name) values(2, '美國(guó)');

1.5.1 創(chuàng)建索引

基本語(yǔ)法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 # CREATE后可以指定索引的類(lèi)型（可以不指定，默認(rèn)普通索引）、索引的名稱(chēng)
[USING index_type] # 可以指定索引的數(shù)據(jù)結(jié)構(gòu)，如果不指定默認(rèn)使用的是B+樹(shù)索引
ON 表名(表中的列名, ...) # 然后指定對(duì)哪張表的哪些字段創(chuàng)建索引

index_col_name: column_name[(length)][ASC|DESC]

實(shí)例：為city表中的city_name字段創(chuàng)建索引

CREATE INDEX idx_city_name ON city(city_name);

注意：在MySQL中如果一個(gè)字段是主鍵，則該字段默認(rèn)為主鍵索引，索引名稱(chēng)就是PRIMARY，不用再為該字段創(chuàng)建索引

1.5.2 查看索引

基本語(yǔ)法：

SHOW INDEX FROM 表名; # 查看指定表的所有索引
# 這條語(yǔ)句的查詢(xún)結(jié)果一行就表示這張表的一個(gè)索引

1.5.3 刪除索引

基本語(yǔ)法：

DROP INDEX 索引名 ON 表名;

1.5.4 ALTER指令

通過(guò)ALTER指令修改表的同時(shí)也可以指定索引

# 1. 為該表添加一個(gè)主鍵，主鍵默認(rèn)創(chuàng)建主鍵索引
ALTER TABLE 表名 ADD PRIMARY KEY(列名);

# 2. 為該表創(chuàng)建一個(gè)唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名(列名); # 這里的列名可以是多個(gè)，多個(gè)列名即為組合唯一索引

# 3. 為該表創(chuàng)建一個(gè)普通索引
ALTER TABLE 表名 ADD INDEX 索引名(列名); # 這里的列名可以是多個(gè)，多個(gè)列名即為組合索引

# 4. 為該表創(chuàng)建一個(gè)全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名(列名); # 這里的列名可以是多個(gè)，多個(gè)列名即為組合全文索引

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

索引的設(shè)計(jì)可以遵循一些已有的原則，創(chuàng)建索引的時(shí)候請(qǐng)盡量考慮符合這些原則，便于提升索引的使用效率，更高效的使用索引。

1. 對(duì)查詢(xún)頻次較高，且數(shù)據(jù)量比較大的表建立索引。
2. 索引字段的選擇，最佳候選列應(yīng)當(dāng)從WHERE子句的條件中提取，如果WHERE子句中的組合比較多，那么應(yīng)當(dāng)挑選最常用、過(guò)濾效果最好的列的組合。
3. 使用唯一索引時(shí)，區(qū)分度越高，索引的檢索效率也就越高。
4. 索引可以有效的提升查詢(xún)數(shù)據(jù)的效率，但索引數(shù)量不是多多益善，索引越多，維護(hù)索引的代價(jià)自然也就越大。對(duì)于插入、更新、刪除等DML操作比較頻繁的表來(lái)說(shuō)，索引過(guò)多，會(huì)引入相當(dāng)高的維護(hù)代價(jià)，降低DML操作的效率，增加相應(yīng)操作的時(shí)間消耗。另外，索引過(guò)多的話(huà)，MySQL也會(huì)犯選擇困難，雖然最終仍然胡找到一個(gè)可用的索引，但無(wú)疑提高了選擇的代價(jià)。
5. 使用短索引，索引創(chuàng)建之后也是使用硬盤(pán)來(lái)存儲(chǔ)的，因此提升索引訪(fǎng)問(wèn)的I/O效率，也可以提升總體的訪(fǎng)問(wèn)效率。假如構(gòu)成索引的字段總長(zhǎng)度比較短，那么在給定大小的存儲(chǔ)塊內(nèi)可以存儲(chǔ)更多的索引值，相應(yīng)的可以有效的提升MySQL訪(fǎng)問(wèn)索引的I/O效率。
6. 利用最左前綴（針對(duì)組合索引），N個(gè)列組合而成的組合索引，那么相當(dāng)于是創(chuàng)建了N個(gè)索引，如果查詢(xún)時(shí)WHERE子句中使用了組成該索引的前幾個(gè)字段，那么這條查詢(xún)SQL可以利用組合索引來(lái)提高查詢(xún)效率。

# 創(chuàng)建組合索引
CREATE INDEX idx_name_email_status ON tb_seller(name, email, status);

# 根據(jù)最左前綴原則就相當(dāng)于：
#   對(duì)name字段創(chuàng)建索引；
#   對(duì)name，email字段創(chuàng)建組合索引；
#   對(duì)name，email，status字段創(chuàng)建組合索引；
# 如果WHERE子句中使用了上面三種組合都會(huì)使用到索引查詢(xún)，如果WHERE子句使用了name、status的組合（或email、status的組合）（或單獨(dú)email、單獨(dú)status），則不會(huì)用到索引

二、存儲(chǔ)過(guò)程和函數(shù)

2.1 存儲(chǔ)過(guò)程和函數(shù)概述

存儲(chǔ)過(guò)程和函數(shù)是事先經(jīng)過(guò)編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段SQL語(yǔ)句的集合，調(diào)用存儲(chǔ)過(guò)程和函數(shù)可以簡(jiǎn)化應(yīng)用開(kāi)發(fā)人員的很多工作，減少數(shù)據(jù)在數(shù)據(jù)庫(kù)和應(yīng)用服務(wù)器之間的傳輸，對(duì)于提高數(shù)據(jù)處理的效率是有好處的（比如存儲(chǔ)過(guò)程中封裝了N條SQL語(yǔ)句，單獨(dú)執(zhí)行這N條SQL語(yǔ)句的話(huà)，就需要和數(shù)據(jù)庫(kù)服務(wù)器進(jìn)行N次交互，而執(zhí)行封裝了這N條SQL語(yǔ)句的存儲(chǔ)過(guò)程的話(huà)只需要和數(shù)據(jù)庫(kù)服務(wù)器進(jìn)行1次交互即可）。

存儲(chǔ)過(guò)程和函數(shù)的區(qū)別在于：函數(shù)必須有返回值，而存儲(chǔ)過(guò)程沒(méi)有。

可以這樣理解：函數(shù)是一個(gè)有返回值的存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程是一個(gè)沒(méi)有返回值的函數(shù)。

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

CREATE PROCEDURE 存儲(chǔ)過(guò)程名([參數(shù)[,...]])
BEGIN
    -- SQL語(yǔ)句
END;

tips：由于MySQL中默認(rèn)的分隔符為分號(hào)";"，在begin和end中間的SQL語(yǔ)句以分號(hào)結(jié)尾后就不會(huì)運(yùn)行到END，這時(shí)我們可以用DELIMITER關(guān)鍵字來(lái)修改MySQL的分隔符，等存儲(chǔ)過(guò)程創(chuàng)建完畢后再修改回分號(hào)。

例：

DELIMITER $  # 將分隔符替換為$

CREATE PROCEDURE pro_test1()
BEGIN
    SELECT * FROM city_table;
END$

DELIMITER ; # 替換回分號(hào)

2.3 調(diào)用存儲(chǔ)過(guò)程

CALL 存儲(chǔ)過(guò)程名();

2.4 查看存儲(chǔ)過(guò)程

-- 1. 查詢(xún)某數(shù)據(jù)庫(kù)中的所有存儲(chǔ)過(guò)程
SELECT name FROM mysql.proc WHERE db='test_db';

-- 2. 查詢(xún)存儲(chǔ)過(guò)程的狀態(tài)信息
SHOW PROCEDURE status;

-- 3. 查詢(xún)某個(gè)存儲(chǔ)過(guò)程的定義
SHOW CREATE PROCEDURE pro_test1;

2.5 刪除存儲(chǔ)過(guò)程

DROP PROCEDURE [IF EXISTS] 存儲(chǔ)過(guò)程名;

2.6 存儲(chǔ)過(guò)程的語(yǔ)法

存儲(chǔ)過(guò)程是可以編程的，這就意味著可以使用變量、表達(dá)式、控制結(jié)構(gòu)，使SQL語(yǔ)句擁有過(guò)程化語(yǔ)言的特點(diǎn)，來(lái)完成比較復(fù)雜的功能。

2.6.1 變量

• DECLARE

通過(guò)DECLARE可以定義一個(gè)局部變量，該變量的作用范圍只能在BEGIN ... END塊中。

DECLARE var_name[,...] TYPE [DEFAULT value]  # 可以同時(shí)聲明多個(gè)變量

示例：

DELIMITER $  # 將分隔符替換為$

CREATE PROCEDURE pro_test2()
BEGIN
    DECLARE num int default 1; # 在BEGIN ... END塊中定義一個(gè)名為num的變量，數(shù)據(jù)類(lèi)型為int，默認(rèn)值為1
    SELECT * FROM city_table WHERE city_id = num;
END$

DELIMITER ; # 替換回分號(hào)

• SET

直接賦值使用SET，可以將常量或者表達(dá)式賦值給變量。

SET var_name = expr [, var_name2 = expr2 ...]  # 可以同時(shí)為多個(gè)變量賦值

示例：

DELIMITER $  # 將分隔符替換為$

CREATE PROCEDURE pro_test3()
BEGIN
    DECLARE name varchar(20);
    SET name = '北京';
    SELECT * FROM city_table WHERE city_name = name;
END$

DELIMITER ; # 替換回分號(hào)

也可以通過(guò)SELECT ... INTO 的方式進(jìn)行變量賦值操作。表示將查詢(xún)得到的結(jié)果賦值給變量。

示例：

DELIMITER $  # 將分隔符替換為$

CREATE PROCEDURE pro_test3()
BEGIN
    DECLARE num int;
    SELECT COUNT(*) INTO num FROM city_table;
    SELECT CONCAT('city表中記錄數(shù)為：', num);
END$

DELIMITER ; # 替換回分號(hào)

注意：如果數(shù)據(jù)庫(kù)中表的數(shù)據(jù)有變化，則通過(guò)SELECT ... INTO方式賦的變量值也會(huì)有變化

2.6.2 if條件判斷

語(yǔ)法結(jié)構(gòu)：

IF 判斷條件 THEN 判斷條件成立執(zhí)行的SQL語(yǔ)句
[ELSEIF 判斷條件2 THEN 判斷條件2成立執(zhí)行的SQL語(yǔ)句]
[ELSE 以上判斷條件都不成立執(zhí)行的SQL語(yǔ)句]
END IF;

示例：

DELIMITER $  # 將分隔符替換為$

CREATE PROCEDURE pro_test4()
BEGIN 
    DECLARE height int;
    DECLARE description varchar(50) default '';
    SET height = 175;
    IF height >= 180 THEN
        SET description = '身材高挑';
    ELSEIF height >= 170 AND height < 180 THEN  # 多個(gè)判斷條件之間用 AND、OR 連接
        SET description = '標(biāo)準(zhǔn)身材';
    ELSE
        SET description = '一般身材';
    END IF;
    SELECT CONCAT('身高：', height, ' 對(duì)應(yīng)的身材類(lèi)型為：', description);
END$

DELIMITER ; # 替換回分號(hào)

2.6.3 傳遞參數(shù)

在創(chuàng)建存儲(chǔ)過(guò)程的時(shí)候指定傳遞的參數(shù)，語(yǔ)法格式：

CREATE PROCEDURE 存儲(chǔ)過(guò)程名([IN/OUT/INOUT] 參數(shù)名 參數(shù)的數(shù)據(jù)類(lèi)型)

# IN: 表示該參數(shù)可以作為輸入，也就是需要調(diào)用存儲(chǔ)過(guò)程時(shí)傳入值，存儲(chǔ)過(guò)程的參數(shù)默認(rèn)為IN
# OUT：表示該參數(shù)作為輸出，也就是該參數(shù)可以作為返回值
# INOUT：表示該參數(shù)既是傳入值，也是返回值

• IN - 輸入?yún)?shù)

示例：

DELIMITER $  # 將分隔符替換為$

CREATE PROCEDURE pro_test5(IN height int)  # height作為輸入?yún)?shù)傳遞進(jìn)存儲(chǔ)過(guò)程
BEGIN 
    DECLARE description varchar(50) default '';
    SET height = 175;
    IF height >= 180 THEN
        SET description = '身材高挑';
    ELSEIF height >= 170 AND height < 180 THEN  # 多個(gè)判斷條件之間用 AND、OR 連接
        SET description = '標(biāo)準(zhǔn)身材';
    ELSE
        SET description = '一般身材';
    END IF;
    SELECT CONCAT('身高：', height, ' 對(duì)應(yīng)的身材類(lèi)型為：', description);
END$

DELIMITER ; # 替換回分號(hào)

• OUT - 輸出參數(shù)

示例：

DELIMITER $  # 將分隔符替換為$

CREATE PROCEDURE pro_test5(IN height int, OUT description)  # height作為輸入?yún)?shù)傳遞進(jìn)存儲(chǔ)過(guò)程 
                        # description作為輸該存儲(chǔ)過(guò)程的返回值，注意存儲(chǔ)過(guò)程的返回值不需要用return來(lái)返回
BEGIN 
    SET height = 175;
    IF height >= 180 THEN
        SET description = '身材高挑';
    ELSEIF height >= 170 AND height < 180 THEN  # 多個(gè)判斷條件之間用 AND、OR 連接
        SET description = '標(biāo)準(zhǔn)身材';
    ELSE
        SET description = '一般身材';
    END IF;
END$

DELIMITER ; # 替換回分號(hào)

# 調(diào)用存儲(chǔ)過(guò)程，獲取返回值
# mysql中使用@來(lái)定義用戶(hù)會(huì)話(huà)變量，代表在整個(gè)會(huì)話(huà)過(guò)程中都有用的一個(gè)變量
# 這里定義一個(gè)@description變量來(lái)接收存儲(chǔ)過(guò)程pro_test5的返回值
call pro_test5(178, @description);

# 使用該變量
select @description;

注意：

1. mysql中使用@來(lái)定義用戶(hù)會(huì)話(huà)變量，代表在整個(gè)會(huì)話(huà)過(guò)程中都有用的一個(gè)變量，當(dāng)此次用戶(hù)會(huì)話(huà)關(guān)閉（即SQL連接斷開(kāi)，則用戶(hù)會(huì)話(huà)變量失效）。
2. mysql中還可以使用@@來(lái)定義系統(tǒng)變量，即用戶(hù)會(huì)話(huà)關(guān)閉后依然有效的變量。
3. 在mysql中（不是存儲(chǔ)過(guò)程中）定義變量必須使用@或者@@，如果直接使用SET name = 'aaa'; 會(huì)報(bào)錯(cuò)

• INOUT - 輸入輸出參數(shù)

示例：

DELIMITER $  # 將分隔符替換為$

CREATE PROCEDURE pro_test6(INOUT height int)  # height作為輸入輸出參數(shù)傳遞進(jìn)存儲(chǔ)過(guò)程
BEGIN 
    SET height = height + 10;
END$

DELIMITER ; # 替換回分號(hào)


# 這時(shí)候不能直接傳常量進(jìn)存儲(chǔ)過(guò)程，因?yàn)榇鎯?chǔ)過(guò)程需要一個(gè)輸入輸出參數(shù)
# 必須先定義一個(gè)變量，用來(lái)接收存儲(chǔ)過(guò)程的返回值
# 同時(shí)定義這個(gè)變量的值，來(lái)傳遞進(jìn)存儲(chǔ)過(guò)程
SET @height = 165;
# 調(diào)用存儲(chǔ)過(guò)程
CALL pro_test6(@height)
# 查看存儲(chǔ)過(guò)程的返回結(jié)果
SELECT @height;

2.6.4 case結(jié)構(gòu)

語(yǔ)法結(jié)構(gòu)：

# 方式一：
CASE `值`
    WHEN `等于的值`THEN `SQL語(yǔ)句`
    [WHEN `等于的值` THEN `SQL語(yǔ)句`]
    [ELSE `SQL語(yǔ)句`]
END CASE;

# 方式二：
CASE
    WHEN `條件表達(dá)式` THEN `SQL語(yǔ)句`
    [WHEN `條件表達(dá)式` THEN `SQL語(yǔ)句`]
    [ELSE `SQL語(yǔ)句`]
END CASE;

示例：

-- 給定一個(gè)月份，計(jì)算出所在的季度

DELIMITER $  # 將分隔符替換為$

CREATE PROCEDURE pro_test7(in mon int)
BEGIN
    DECLARE result varchar(10);
    -- 這里用方式二，因?yàn)榉绞揭恢荒芘袛嗟戎?，這里需要判斷不等值
    CASE 
        WHEN mon >= 1 AND mon <= 3 THEN
            SET result = '第一季度';
        WHEN mon >= 4 AND mon <= 6 THEN
            SET result = '第二季度';
        WHEN mon >= 7 AND mon <= 9 THEN
            SET result = '第三季度';
        ELSE 
            SET result = '第四季度';
    SELECT CONCAT('傳遞的月份為：', mon, ' 所在的季度為：', result) AS content;
END$

DELIMITER ; # 替換回分號(hào)

2.6.5 循環(huán)結(jié)構(gòu)

2.6.5.1 while循環(huán)

語(yǔ)法結(jié)構(gòu)：

-- 只要條件表達(dá)式成立，就執(zhí)行DO里面的SQL語(yǔ)句
WHILE `條件表達(dá)式` DO
    `SQL語(yǔ)句`
END WHILE;

示例：

-- 計(jì)算從1加到n的值

DELIMITER $  # 將分隔符替換為$

CREATE PROCEDURE pro_test8(in n int)
BEGIN
    DECLARE total int default 0;
    DECLARE num int default 1;
    
    WHILE start <= n DO 
        SET total = total + num;
        SET num = num + 1;
    END WHILE;
    
    SELECT CONCAT('輸入的值為：', n, ' 計(jì)算的結(jié)果為：', total);
END$

DELIMITER ; # 替換回分號(hào)

2.6.5.2 repeat循環(huán)

語(yǔ)法結(jié)構(gòu)：

-- 只要條件表達(dá)式成立就退出循環(huán)
REPEAT
    `SQL語(yǔ)句`
    UNTIL `條件表達(dá)式`
END REPEAT;

示例：

-- 計(jì)算從1加到n的值

DELIMITER $  # 將分隔符替換為$

CREATE PROCEDURE pro_test8(in n int)
BEGIN
    DECLARE total int default 0;
    DECLARE num int default 1;
    
    REPEAT
        SET total = total + num;
        SET num = num + 1;
    UNTIL num > n  # 注意：UNTIL后不加分號(hào)
    END REPEAT;
    
    SELECT CONCAT('輸入的值為：', n, ' 計(jì)算的結(jié)果為：', total);
END$

DELIMITER ; # 替換回分號(hào)

2.6.5.3 loop循環(huán)

語(yǔ)法結(jié)構(gòu)：

-- 退出循環(huán)的條件需要使用其他語(yǔ)句來(lái)定義，通常使用LEAVE語(yǔ)句實(shí)現(xiàn)
[begin_label:] LOOP # 聲明當(dāng)前LOOP循環(huán)的別名
    `SQL語(yǔ)句`
END LOOP [end_label] 

-- 如果不在SQL語(yǔ)句中增加退出循環(huán)的語(yǔ)句，則可以使用LOOP語(yǔ)句來(lái)實(shí)現(xiàn)簡(jiǎn)單的死循環(huán)

2.6.6 leave語(yǔ)句

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

示例：

-- 計(jì)算從1加到n的值

DELIMITER $  # 將分隔符替換為$

CREATE PROCEDURE pro_test8(in n int)
BEGIN
    DECLARE total int default 0;
    DECLARE num int default 1;
    
    c: LOOP # 給循環(huán)起的別名為c
        SET total = total + num;
        SET num = num + 1;
        IF num > n THEN
            LEAVE c;  # LEAVE c; 表示退出循環(huán)c
        END IF;
    END LOOP c;
    
    SELECT CONCAT('輸入的值為：', n, ' 計(jì)算的結(jié)果為：', total);
END$

DELIMITER ; # 替換回分號(hào)

2.6.7 游標(biāo)/光標(biāo)

游標(biāo)（也叫光標(biāo)）是用來(lái)存儲(chǔ)查詢(xún)結(jié)果集的數(shù)據(jù)類(lèi)型，在存儲(chǔ)過(guò)程和函數(shù)中可以使用游標(biāo)對(duì)結(jié)果集進(jìn)行循環(huán)的處理。游標(biāo)的使用包括光標(biāo)的聲明、OPEN、FETCH和CLOSE，其語(yǔ)法分別如下：

聲明游標(biāo)：

# 如果不輸入游標(biāo)類(lèi)型默認(rèn)局部游標(biāo)
DECLARE 游標(biāo)名 CURSOR [游標(biāo)類(lèi)型] FOR 查詢(xún)語(yǔ)句;

OPEN游標(biāo)（想要遍歷游標(biāo)就得先打開(kāi)游標(biāo)）：

OPEN 游標(biāo)名;

FETCH游標(biāo)（即遍歷游標(biāo)）：

# 調(diào)用一次FETCH就獲取到查詢(xún)語(yǔ)句的一行結(jié)果
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n] FROM 游標(biāo)名 INTO 變量名1, 變量名2, ...
# 如果不輸入FETCH的類(lèi)型，則默認(rèn)為NEXT
# NEXT：下一行；PRIOR：上一行；FIRST：第一行；LAST：最后一行；ABSOLUTE n：第n行
# INTO后面跟著變量名表示，把查詢(xún)語(yǔ)句的查詢(xún)結(jié)果放到對(duì)于的變量中（按照查詢(xún)結(jié)果從左到右的順序?qū)?yīng)）

CLOSE游標(biāo)（即關(guān)閉/釋放游標(biāo)）：

CLOSE 游標(biāo)名;

示例：

-- 查詢(xún)emp表中的數(shù)據(jù)，并逐行獲取進(jìn)行展示

DELIMITER $  # 將分隔符替換為$

CREATE PROCEDURE pro_test9()
BEGIN
    # 定義變量，用來(lái)接收游標(biāo)的結(jié)果
    DECLARE e_id int(11);
    DECLARE e_name varchar(50);
    DECLARE e_age int(11);
    DECLARE e_salary int(11);
    # 這里聲明一個(gè)變量用作循環(huán)退出的條件判斷
    DECLARE has_data int DEFAULT 1;
    
    # 聲明游標(biāo)
    DECLARE emp_result CURSOR FOR SELECT * FROM emp;
    # 這里定義一個(gè)句柄，即游標(biāo)查詢(xún)不到數(shù)據(jù)時(shí)觸發(fā)（這句話(huà)必須寫(xiě)在聲明游標(biāo)的下面，否則無(wú)法表示這個(gè)句柄對(duì)應(yīng)哪個(gè)游標(biāo)）
    # 如果不這樣做的話(huà)，當(dāng)游標(biāo)拿不到數(shù)據(jù)的時(shí)候會(huì)報(bào)錯(cuò)
    # 還有一種思路是先用 SELECT COUNT(*) FROM emp; 獲取到查詢(xún)語(yǔ)句數(shù)據(jù)的總條數(shù)
    DECLARE EXIT HANDLER FOR NOT FOUND SET has_data = 0;
    # 打開(kāi)游標(biāo)
    OPEN emp_result;
    
    REPEAT
        FETCH emp_result into e_id, e_name, e_age, e_salary;
        SELECT CONCAT('ID：', e_id, '，姓名：', e_name, '，年齡：', e_age, '，薪資：'); # 使用游標(biāo)獲得的結(jié)果
        UNTIL has_data = 0 # 定義循環(huán)退出條件
    END REPEAT;
    
    #關(guān)閉游標(biāo)
    CLOSE emp_result;
END$


DELIMITER ; # 替換回分號(hào)

2.7 存儲(chǔ)函數(shù)

存儲(chǔ)函數(shù)就是有返回值的存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程就是沒(méi)有返回值的存儲(chǔ)函數(shù)。存儲(chǔ)函數(shù)雖然沒(méi)有返回值，但是有out變量可以輸出結(jié)果，所以存儲(chǔ)函數(shù)可以做的事，存儲(chǔ)過(guò)程也能做。

語(yǔ)法結(jié)構(gòu)：

CREATE FUNCTION 存儲(chǔ)函數(shù)名([參數(shù)名 參數(shù)類(lèi)型])
RETURNS 返回值類(lèi)型
BEGIN
    ...
END;

示例：

-- 定義一個(gè)存儲(chǔ)函數(shù)，返回滿(mǎn)足條件的總記錄數(shù)

DELIMITER $  # 將分隔符替換為$

CREATE FUNCTION function_test1(countryId int)
RETURNS int
BEGIN
    DECLARE cnum int;
    # 這里用INTO 將一行一列的查詢(xún)結(jié)果直接賦值給變量cnum
    SELECT COUNT(*) INTO cnum FROM city WHERE country_id = countryId;
    
    # 直接返回變量
    return cnum;
END$


DELIMITER ; # 替換回分號(hào)


-- 存儲(chǔ)函數(shù)的調(diào)用（不再使用call，因?yàn)閏all調(diào)用的是存儲(chǔ)過(guò)程，而存儲(chǔ)函數(shù)有返回值，直接使用SELECT語(yǔ)句調(diào)用即可）

SELECT function_test1();

三、觸發(fā)器

3.1 介紹

觸發(fā)器是與表有關(guān)的數(shù)據(jù)庫(kù)對(duì)象，指在insert/update/delete之前或之后，觸發(fā)并執(zhí)行觸發(fā)器中定義的SQL語(yǔ)句集合。觸發(fā)器的這種特性可以協(xié)助應(yīng)用在數(shù)據(jù)庫(kù)端確保數(shù)據(jù)的完整性、日志記錄、數(shù)據(jù)校驗(yàn)等操作。

使用別名NEW和OLD來(lái)引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容，這與其他的數(shù)據(jù)庫(kù)是相似的。MySQL的觸發(fā)器還只支持行級(jí)觸發(fā)，不支持語(yǔ)句級(jí)觸發(fā)，ORACLE兩者都支持。

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

語(yǔ)法結(jié)構(gòu)：

CREATE TRIGGER 觸發(fā)器名
BEFORE或AFTER INSERT或UPDATE或DELETE
ON 表名
[FOR EACH ROW] -- 加上這句則表示創(chuàng)建的是行級(jí)觸發(fā)器

BEGIN
    觸發(fā)器中的SQL語(yǔ)句;
END;

示例：

通過(guò)觸發(fā)器記錄emp_t表的數(shù)據(jù)變更日志，包含增、刪、改；

step1：創(chuàng)建一張日志表

CREATE TABLE emp_t_logs(
    id int(11) primary key auto_increment,
    operation varchar(20) not null comment '操作類(lèi)型， insert/update/delete',
    operation_time datetime not null comment '操作時(shí)間',
    operation_id int(11) not null comment '操作表的ID',
    operation_params varchar(500) comment '操作參數(shù)，記錄插入后數(shù)據(jù)、更新前后數(shù)據(jù)、刪除前數(shù)據(jù)'
)engine=innodb default charset=utf8;

step2：創(chuàng)建insert型觸發(fā)器，完成插入數(shù)據(jù)后的日志記錄

-- 創(chuàng)建insert型觸發(fā)器，完成插入數(shù)據(jù)時(shí)的日志記錄

DELIMITER $  # 將分隔符替換為$

create trigger emp_t_insert_trigger
after insert
on emp_t
for each row
begin
    insert into emp_t_logs(operation, operation_time, operation_id, operation_params)
    values('insert', current_time, NEW.id, 
          concat('插入后（id: ', NEW.id,', name: ', NEW.name,
                 ', age: ', NEW.age,', salary: ', NEW.salary,'）')); # 這里的NEW是插入后的那一行數(shù)據(jù)
end$

DELIMITER ; # 替換回分號(hào)

step3：創(chuàng)建update型觸發(fā)器，完成修改數(shù)據(jù)后的日志記錄

-- 創(chuàng)建update型觸發(fā)器，完成修改數(shù)據(jù)后的日志記錄

DELIMITER $  # 將分隔符替換為$

create trigger emp_t_update_trigger
after update
on emp_t
for each row
begin
    insert into emp_t_logs(operation, operation_time, operation_id, operation_params)
    values('update', current_time, NEW.id, 
          concat('修改前（id: ', OLD.id,', name: ', OLD.name,
                 ', age: ', OLD.age,', salary: ', OLD.salary,'），修改后（id: ',
                 NEW.id,', name: ', NEW.name,', age: ', NEW.age,', salary: ',
                 NEW.salary,'）')); # 這里的OLD是修改前的那一行數(shù)據(jù), NEW是修改后的那一行數(shù)據(jù)
end$

DELIMITER ; # 替換回分號(hào)

step4：創(chuàng)建delete型觸發(fā)器，完成刪除數(shù)據(jù)后的日志記錄

-- 創(chuàng)建delete型觸發(fā)器，完成刪除數(shù)據(jù)后的日志記錄

DELIMITER $  # 將分隔符替換為$

create trigger emp_t_delete_trigger
after delete
on emp_t
for each row
begin
    insert into emp_t_logs(operation, operation_time, operation_id, operation_params)
    values('delete', current_time, NEW.id, 
          concat('刪除前（id: ', OLD.id,', name: ', OLD.name,
                 ', age: ', OLD.age,', salary: ', OLD.salary,'）')); # 這里的OLD是刪除前的那一行數(shù)據(jù)
end$

DELIMITER ; # 替換回分號(hào)

step4：測(cè)試

insert into emp_t(name, age, salary) values('小明', 18, 10000);
update emp_t set name='小紅', age=19, salary=8000 where id=1;
delete from emp_t where id=1;
# 操作后查詢(xún)?nèi)罩颈?select * from emp_t_logs;

5.3 刪除觸發(fā)器

語(yǔ)法結(jié)構(gòu)：

drop trigger [數(shù)據(jù)庫(kù)名.]觸發(fā)器名;

-- 如果沒(méi)有指定數(shù)據(jù)庫(kù)名，則默認(rèn)當(dāng)前數(shù)據(jù)庫(kù)

5.4 查看觸發(fā)器

語(yǔ)法結(jié)構(gòu)：

-- 查看數(shù)據(jù)庫(kù)中所有的觸發(fā)器
show triggers;

四、存儲(chǔ)引擎

4.1 存儲(chǔ)引擎概述

和大多數(shù)數(shù)據(jù)庫(kù)不同，MySQL中有一個(gè)存儲(chǔ)引擎的概念，針對(duì)不同的存儲(chǔ)需求可以選擇最優(yōu)的存儲(chǔ)引擎。

存儲(chǔ)引擎就是存儲(chǔ)數(shù)據(jù)，建立索引，更新查詢(xún)數(shù)據(jù)等等技術(shù)的實(shí)現(xiàn)方式。存儲(chǔ)引擎是基于表的，而不是基于庫(kù)的。所以存儲(chǔ)引擎也可以被稱(chēng)為表類(lèi)型。

Oracle、SqlServer等數(shù)據(jù)庫(kù)只有一種存儲(chǔ)引擎，MySQL提供了插件式的存儲(chǔ)引擎架構(gòu)。所以MySQL存在多種存儲(chǔ)引擎，可以根據(jù)需要使用相應(yīng)的引擎，或者編寫(xiě)存儲(chǔ)引擎。

MySQL5.0支持的存儲(chǔ)引擎包含：InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB支持事務(wù)、有行級(jí)鎖、支持外鍵。

可以通過(guò)指令 show engines ，來(lái)查詢(xún)當(dāng)前數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎。

創(chuàng)建新表時(shí)如果不指定存儲(chǔ)引擎，那么系統(tǒng)就會(huì)使用默認(rèn)的存儲(chǔ)引擎，MySQL5.5之前的默認(rèn)存儲(chǔ)引擎是MyISAM，5.5之后是InnoDB。

4.2 各種存儲(chǔ)引擎特性

4.2.1 InnoDB的特性

InnoDB是MySQL默認(rèn)的存儲(chǔ)引擎。InnoDB存儲(chǔ)引擎提供了具有提交、回滾、崩潰恢復(fù)能力的事務(wù)安全。但是對(duì)比MyISAM的存儲(chǔ)引擎，InnoDB寫(xiě)的處理效率差一些，并且會(huì)占用更多的磁盤(pán)空間以保留數(shù)據(jù)和索引。

InnoDB存儲(chǔ)引擎不同于其他存儲(chǔ)引擎的特點(diǎn)：

• 事務(wù)控制
• 外鍵約束：InnoDB是所有MySQL引擎中唯一支持外鍵的存儲(chǔ)引擎，在創(chuàng)建外鍵的時(shí)候，要求父表必須有對(duì)應(yīng)的索引，子表在創(chuàng)建外鍵的時(shí)候，也會(huì)自動(dòng)的創(chuàng)建對(duì)應(yīng)的索引。

下面兩張表中，country_innodb是父表，country_id為主鍵索引，city_innodb是子表，country_id字段為外鍵，對(duì)應(yīng)于country_innodb表的主鍵country_id。

-- 父表
create table country_innodb (
    country_id int primary key auto_increment,
    country_name varchar(100)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 子表
create table city_innodb (
    city_id int primary key auto_increment,
    city_name varchar(50),
    country_id int,
    key idx_fk_country_id(country_id),
    # 定義了外鍵country_id和另一張表country_innodb的country_id相關(guān)聯(lián)
    constraint 'fx_city_country' foreign key(country_id) references country_innodb(country_id) 
    # 定義刪除主表數(shù)據(jù)時(shí)，如果子表有關(guān)聯(lián)記錄則不刪除
    on delete restrict
    # 定義更新主表數(shù)據(jù)時(shí)，如果子表有關(guān)聯(lián)記錄，更新子表記錄
    on update cascade
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入數(shù)據(jù)
insert into country_innodb values(null, 'China'), (null, 'America'), (null. 'Japan'); # 批量插入寫(xiě)法
insert into city_innodb values(null, 'Xian', 1), (null, 'NewYork', 2), (null, 'Beijing', 1);

-- 驗(yàn)證刪除
# 會(huì)提示刪除失敗，因?yàn)樵O(shè)置了外鍵關(guān)聯(lián)的刪除限制
delete from country_innodb where country_id=2; 

-- 驗(yàn)證更新
# 子表原來(lái)country_id=1的值也會(huì)被更新為country_id=100，因?yàn)樵O(shè)置了外鍵關(guān)聯(lián)的更新
update country_innodb set country_id = 100 where country_id = 1; 

• 存儲(chǔ)方式

Linux中MySQL數(shù)據(jù)默認(rèn)存放在 /var/lib/mysql 目錄下。InnoDB存儲(chǔ)表和引擎有以下兩種方式：

1. 使用共享表空間存儲(chǔ)，這種方式創(chuàng)建的表其表結(jié)構(gòu)保存在.frm文件中，數(shù)據(jù)和索引保存在innodb_data_home_dir和innodb_data_file_path定義的表空間中，可以是多個(gè)文件。
2. 使用多表空間存儲(chǔ)，這種方式創(chuàng)建的表其表結(jié)構(gòu)仍然保存在.frm文件中，但是每個(gè)表的數(shù)據(jù)和索引單獨(dú)保存在.ibd文件中

4.2.2 MyISAM

MyISAM不支持事務(wù)、也不支持外鍵，其優(yōu)勢(shì)是訪(fǎng)問(wèn)的速度快，度事務(wù)的完整性沒(méi)有要求或者以SELECT、INSERT為主的應(yīng)用基本上都可以使用這個(gè)引擎來(lái)創(chuàng)建表。有以下兩個(gè)比較重要的特點(diǎn)：

• 不支持事務(wù)
• 文件存儲(chǔ)方式

每一個(gè)使用MyISAM為引擎創(chuàng)建的表在磁盤(pán)上存儲(chǔ)成3個(gè)文件，其文件名都和表名相同，但拓展名分別是：

.frm（存儲(chǔ)表結(jié)構(gòu)）

.MYD（存儲(chǔ)數(shù)據(jù)）

.MYI（存儲(chǔ)索引）

4.3 存儲(chǔ)引擎的選擇

在選擇存儲(chǔ)引擎時(shí)，應(yīng)該根據(jù)應(yīng)用系統(tǒng)的特點(diǎn)選擇合適的存儲(chǔ)引擎。對(duì)于復(fù)雜的應(yīng)用系統(tǒng)，還可以根據(jù)實(shí)際情況選擇多種存儲(chǔ)引擎進(jìn)行結(jié)合。以下是幾種常用的存儲(chǔ)引擎的使用環(huán)境。

• InnoDB：是MySQL默認(rèn)的存儲(chǔ)引擎，用于事務(wù)處理應(yīng)用程序，支持外鍵。如果應(yīng)用對(duì)事務(wù)的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作處理插入和查詢(xún)以外，還包含很多更新、刪除操作，那么InnoDB存儲(chǔ)引擎是比較合適的選擇。InnoDB存儲(chǔ)引擎除了有效的降低由于刪除和更新導(dǎo)致的鎖定，還可以確保事務(wù)的完整提交和回滾，對(duì)于類(lèi)似于計(jì)費(fèi)系統(tǒng)或財(cái)務(wù)系統(tǒng)等對(duì)數(shù)據(jù)準(zhǔn)確性要求比較高的系統(tǒng)，InnoDB是最合適的選擇。
• MyISAM：如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對(duì)事務(wù)的完整性、并發(fā)性要求不是很高，那么選擇這個(gè)存儲(chǔ)引擎是非常合適的。
• MEMORY：將所有數(shù)據(jù)緩存在內(nèi)存中，在需要快速定位記錄和其他類(lèi)似數(shù)據(jù)環(huán)境下，可以提供極快的訪(fǎng)問(wèn)。MEMORY的缺陷就是對(duì)表的大小有限制，太大的表無(wú)法緩存在內(nèi)存中，其次是要確保表的數(shù)據(jù)可以恢復(fù)，數(shù)據(jù)庫(kù)異常終止后，表中的數(shù)據(jù)是可以恢復(fù)的。MEMORY表通常用于更新不太頻繁的小表，用以快速得到訪(fǎng)問(wèn)結(jié)果。
• MERGE：用于將一系列等同的MyISAM表以邏輯方式組合在一起，并作為一個(gè)對(duì)象引用他們。MERGE表的優(yōu)點(diǎn)在于可以突破對(duì)單個(gè)MyISAM表的大小限制，并且通過(guò)將不同的表分布在多個(gè)磁盤(pán)上，可以有效的改善MERGE表的訪(fǎng)問(wèn)效率。這對(duì)于存儲(chǔ)諸如數(shù)據(jù)倉(cāng)儲(chǔ)等VLDB環(huán)境十分合適。

五、優(yōu)化SQL步驟

在應(yīng)用的開(kāi)發(fā)過(guò)程中，由于初期數(shù)據(jù)量小，開(kāi)發(fā)人員寫(xiě)SQL語(yǔ)句的時(shí)候更重視功能上的實(shí)現(xiàn)，但是當(dāng)應(yīng)用系統(tǒng)正式上線(xiàn)后，隨著生產(chǎn)數(shù)據(jù)量的急劇增長(zhǎng)，很多SQL語(yǔ)句開(kāi)始逐漸顯露出性能的問(wèn)題，對(duì)生產(chǎn)的影響也越來(lái)越大，此時(shí)這些有問(wèn)題的SQL語(yǔ)句就成為整個(gè)系統(tǒng)性能的瓶頸，因此我們必須對(duì)它們進(jìn)行優(yōu)化。

5.1 查看SQL執(zhí)行頻率

MySQL客戶(hù)端連接成功后，通過(guò) show [session|global] status 命令可以提供服務(wù)器狀態(tài)信息。show [session|global] status 可以根據(jù)需要加上參數(shù)"session"或者"global"來(lái)顯示session級(jí)（當(dāng)前連接）的統(tǒng)計(jì)結(jié)果和global級(jí)（自數(shù)據(jù)庫(kù)上次啟動(dòng)至今）的統(tǒng)計(jì)結(jié)果。如果不寫(xiě)，默認(rèn)使用的參數(shù)是"session"

-- 例：下面的命令可以得到當(dāng)前session中所有操作的統(tǒng)計(jì)值（統(tǒng)計(jì)的是所有存儲(chǔ)引擎的表）

show status like 'Com_______';
# 比如查詢(xún)結(jié)果中的"Com_insert"表示當(dāng)前連接的插入次數(shù)， "Com_select"表示當(dāng)前連接的查詢(xún)次數(shù)

-- 例：下面的命令可以得到Innodb行級(jí)相關(guān)的信息

show status like 'Innodb_rows_%';
# 比如查詢(xún)結(jié)果中的"Innodb_rows_read"表示已經(jīng)從以Innodb為引擎的表中讀取的數(shù)據(jù)行數(shù)
# "Innodb_rows_inserted"表示以Innodb為引擎的表已經(jīng)插入了多少條數(shù)據(jù)

我們通常比較關(guān)心的是以下的統(tǒng)計(jì)參數(shù)

5.2 定位低效率執(zhí)行的SQL語(yǔ)句

可以通過(guò)以下兩種方式定位執(zhí)行效率較低的SQL語(yǔ)句

• 慢查詢(xún)?nèi)罩荆和ㄟ^(guò)慢查詢(xún)?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的SQL語(yǔ)句，用 --log-slow-queries[=file_name] 選項(xiàng)啟動(dòng)時(shí)，MySQL會(huì)寫(xiě)一個(gè)包含所有執(zhí)行時(shí)間超過(guò)long_query_time秒的SQL語(yǔ)句的日志文件。

• show processlist：慢查詢(xún)?nèi)罩驹诓樵?xún)結(jié)束以后才記錄，所以在應(yīng)用反應(yīng)執(zhí)行效率出現(xiàn)問(wèn)題的時(shí)候查詢(xún)慢查詢(xún)?nèi)罩静⒉荒芏ㄎ粏?wèn)題，可以使用 show processlist 命令查看當(dāng)前MySQL在進(jìn)行的線(xiàn)程，包括線(xiàn)程的狀態(tài)、是否鎖表等，可以實(shí)時(shí)地查看SQL的執(zhí)行情況，同時(shí)對(duì)一些鎖表操作進(jìn)行優(yōu)化。

  show processlist的各字段含義：

  id：用戶(hù)登錄MySQL時(shí)，系統(tǒng)分配的"connection_id"，可以使用函數(shù)connection_id()查看

  user：顯示當(dāng)前用戶(hù)。如果不是root用戶(hù)，這個(gè)命令就只顯示用戶(hù)權(quán)限范圍的SQL語(yǔ)句

  host：顯示這個(gè)語(yǔ)句是從哪個(gè)ip的哪個(gè)端口上發(fā)的，可以用來(lái)跟蹤出現(xiàn)問(wèn)題語(yǔ)句的用戶(hù)

  db：顯示這個(gè)進(jìn)程目前連接的是哪個(gè)數(shù)據(jù)庫(kù)

  command：顯示當(dāng)前連接的執(zhí)行命令，一般取值為Sleep（休眠：表示客戶(hù)端無(wú)任何操作）、Query（表示正在查詢(xún)）、Connect（表示客戶(hù)端正在連接）等

  time：顯示截止到此次 show processlist 執(zhí)行的時(shí)候，這條語(yǔ)句已經(jīng)執(zhí)行了多少秒

  state：顯示使用當(dāng)前連接的SQL語(yǔ)句的狀態(tài)，很重要的列。state描述的是語(yǔ)句執(zhí)行中的某一個(gè)狀態(tài)。一個(gè)SQL語(yǔ)句，以查詢(xún)?yōu)槔赡苄枰?jīng)過(guò)copy to tmp table、sorting result、sending data等狀態(tài)才可以完成

  info：顯示具體的SQL語(yǔ)句，是判斷問(wèn)題語(yǔ)句的一個(gè)重要依據(jù)

5.3 explain分析執(zhí)行計(jì)劃

通過(guò)以上步驟查詢(xún)到效率低的SQL語(yǔ)句后，可以通過(guò) explain SQL語(yǔ)句 或者 desc SQL語(yǔ)句 命令獲取MySQL如何執(zhí)行select語(yǔ)句的信息，包括在select語(yǔ)句執(zhí)行過(guò)程中表如何連接和連接的順序。

查詢(xún)SQL語(yǔ)句的執(zhí)行計(jì)劃：

explain select * from tb_item where id = 1;

explain查詢(xún)結(jié)果字段含義：

5.3.1 explain的id字段

id字段是select查詢(xún)的序列號(hào)，是一組數(shù)字，表示的是查詢(xún)中執(zhí)行的select子句或者是操作表的順序。

id的情況有三種：

1. id都是相同的表示加載表的順序是從上到下

explain select * from t_role r, t_user u, user_role ur where r.id=ur.role_id and u.id=ur.user_id;

2. id不同，則id值越大，優(yōu)先級(jí)越高，越先被執(zhí)行

explain select * from t_role where id = 
    (select role_id from user_role where user_id = 
        (select id from t_user where username = 'stu1')
    );

3. id有相同，也有不同，同時(shí)存在。id相同的可以認(rèn)為是一組，從上往下順序執(zhí)行；在所有的組中，id的值越大，優(yōu)先級(jí)越高，越先執(zhí)行

explain select * from t_role r, 
    (select * from user_role ur where ur.user_id = '2') a
where r.id = a.role_id;

5.3.2 explain的select_type字段

表示SELECT的類(lèi)型，常見(jiàn)的取值如下（從上到下，效率越來(lái)越低）

-- SIMPLE
explain select * from t_user;

-- PRIMARY、SUBQUERY(子查詢(xún)?cè)趙here或select列表中)
explain select * from t_user where id = (select id from user_role where role_id = '9');

-- PRIMARY、DERIVED（子查詢(xún)?cè)趂rom列表中，這時(shí)候子查詢(xún)的結(jié)果存放在一張臨時(shí)表中）
explain select a.* from (select * from t_user where id in ('1','2')) a;

-- PRIMARY、UNION、UNION RESULT(這時(shí)候還有一個(gè)id是NULL的UNION RESULT)
explain select * from t_user where id = '1' union select * from t_user where id = '2';

5.3.3 explain的table字段

table指的是當(dāng)前這條語(yǔ)句查詢(xún)的數(shù)據(jù)來(lái)自于哪張表

-- PRIMARY、SUBQUERY(子查詢(xún)?cè)趙here或select列表中，這時(shí)候SUBQUERY對(duì)應(yīng)的table是user_role)
explain select * from t_user where id = (select id from user_role where role_id = '9');

-- PRIMARY、DERIVED（子查詢(xún)?cè)趂rom列表中，這時(shí)候DERIVED對(duì)應(yīng)的table是一張臨時(shí)表derived2）
-- 這里的2表示是explain結(jié)果中id=2的臨時(shí)表
explain select a.* from (select * from t_user where id in ('1','2')) a;

5.3.4 explain的type字段

type字段顯示的是訪(fǎng)問(wèn)類(lèi)型，是較為重要的一個(gè)指標(biāo)，可取值為：

（從上到下，效率越來(lái)越低，一般來(lái)說(shuō)我們需要保證查詢(xún)至少達(dá)到range級(jí)別，最好達(dá)到ref ）

-- const
explain select * from t_user where id = '1'; # 主鍵索引
explain select * from t_user where username = 'stu1'; # 唯一索引

-- eq_ref
explain select * from t_user u, t_role r where u.id = r.id; # 主鍵索引關(guān)聯(lián)，只返回一條數(shù)據(jù)

-- ref
explain select * from t_user where name = 'a'; # 非唯一索引

-- index 
explain select id from t_user; # 查詢(xún)索引的id，即遍歷了整個(gè)索引樹(shù)

-- ALL
explain select * from t_user where psw = 'a1a1a'; # 非索引的查詢(xún)，遍歷了全表

5.3.5 explain中key相關(guān)的字段

explain中key相關(guān)的字段有三個(gè)

• possible_keys：顯示這條查詢(xún)語(yǔ)句可能用到的索引
• key：實(shí)際用到的索引，如果為NULL，則表示這條查詢(xún)語(yǔ)句沒(méi)有走索引
• key_len：表示索引中使用的字節(jié)數(shù)，該值為索引字段最大可能的長(zhǎng)度，并非實(shí)際使用的長(zhǎng)度，在不損失精確性的前提下，長(zhǎng)度越短越好

5.3.6 explain的rows字段

表示掃描行的數(shù)量

-- rows的值為1，因?yàn)楦鶕?jù)主鍵索引查詢(xún)，只掃描了一行數(shù)據(jù)
explain select * from t_user where id = '1'; 
-- rows的值為6，因?yàn)闆](méi)有走索引查詢(xún)，需要全表掃描
explain select * from t_user where psw = 'aaaaa';

5.3.7 explain的Extra字段

顯示其他的額外的執(zhí)行計(jì)劃信息。通常需要關(guān)注的取值如下：

（出現(xiàn)了前面兩個(gè)通常要考慮性能優(yōu)化）

-- Using filesort
explain select * from t_user order by psw; 
# 因?yàn)閜sw字段不是索引，所以這里不通過(guò)索引排序，而是通過(guò)掃描整個(gè)數(shù)據(jù)文件進(jìn)行排序
#（如果這里通過(guò)索引排序則Extra為NULL）
# 優(yōu)化方法：對(duì)排序字段加索引

-- Using temporary
explain select * from t_user group by psw;
# 優(yōu)化方法：對(duì)分組依據(jù)字段加索引

5.4 show profile分析SQL

MySQL從5.0.37版本開(kāi)始增加了對(duì) show profiles 和 show profile 語(yǔ)句的支持。show profiles 能夠在做SQL優(yōu)化時(shí)幫助我們了解時(shí)間都耗費(fèi)到哪里去了。

通過(guò) have_profiling 參數(shù)，能夠看到當(dāng)前MySQL是否支持profile。

select @@have_profiling;
# 返回 YES 則表示支持, @@是用來(lái)獲取系統(tǒng)變量

默認(rèn)profiling是關(guān)閉的，可以通過(guò)set語(yǔ)句在Session級(jí)別開(kāi)啟profiling。

-- 查看profiling是否開(kāi)啟
select @@profiling;
# 返回 0 表示關(guān)閉

-- 開(kāi)啟profiling
set profiling=1;

開(kāi)啟profiling后，可以通過(guò) show profiles 命令查看在此次Session中查詢(xún)語(yǔ)句的耗時(shí)，即開(kāi)啟profiling后會(huì)記錄每條查詢(xún)語(yǔ)句的查詢(xún)時(shí)間。

select * from t_user;
select count(*) from tb_item;
select * from tb_item where title = 'abc';
# 執(zhí)行完上面三條語(yǔ)句后再執(zhí)行show profiles, 就可以得到上面三條語(yǔ)句的查詢(xún)耗時(shí)
show profiles;

通過(guò) show profiles 查詢(xún)到所有查詢(xún)語(yǔ)句的耗時(shí)后，返回結(jié)果中有一個(gè)Query_ID的字段，可以通過(guò)

show profile for query Query_ID 來(lái)查看指定Query_ID的查詢(xún)語(yǔ)句再每個(gè)階段耗時(shí)分別是多少

show profile for query 2;

show profile 語(yǔ)句返回兩個(gè)字段，Status表示階段，Duration表示階段的耗時(shí)

Sending data 狀態(tài)表示MySQL線(xiàn)程開(kāi)始訪(fǎng)問(wèn)數(shù)據(jù)行并把結(jié)果返回給客戶(hù)端，而不僅僅是返回給客戶(hù)端。由于在 Sending data 狀態(tài)下，MySQL線(xiàn)程往往需要做大量的磁盤(pán)讀取操作，所以經(jīng)常是查詢(xún)的各狀態(tài)中耗時(shí)最長(zhǎng)的狀態(tài)。

在獲取到最耗時(shí)間的線(xiàn)程狀態(tài)后，MySQL支持進(jìn)一步選擇all、cpu、block io、context switch、page faults等明細(xì)類(lèi)型類(lèi)查看MySQL在使用什么資源上耗費(fèi)了過(guò)高的時(shí)間。例如，選擇查看CPU的耗費(fèi)時(shí)間：

show profile cpu for query 2;

5.5 trace分析優(yōu)化器執(zhí)行計(jì)劃

MySQL5.6提供了對(duì)SQL的跟蹤trace，通過(guò)trace文件能夠進(jìn)一步了解為什么優(yōu)化器選擇A計(jì)劃，而不是選擇B計(jì)劃。

打開(kāi)trace，設(shè)置格式為JSON，并設(shè)置trace最大能夠使用的內(nèi)存大小，避免解析過(guò)程中因?yàn)槟J(rèn)內(nèi)存過(guò)小而不能完整展示。

set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

執(zhí)行SQL語(yǔ)句：

select * from tb_item where id <= 4;

最后，檢查information_schema.optimizer_trace就可以知道MySQL是如何執(zhí)行SQL的：

select * from information_schema.optimizer_trace;

六、索引的使用

索引是數(shù)據(jù)庫(kù)優(yōu)化最常用也是最重要的手段之一，通過(guò)索引通常可以幫助用戶(hù)解決大多數(shù)的MySQL的性能優(yōu)化問(wèn)題。

6.1 驗(yàn)證索引提升查詢(xún)效率

在準(zhǔn)備好的表結(jié)構(gòu)tb_item中，一共存儲(chǔ)了300萬(wàn)條記錄。

A. 根據(jù)ID查詢(xún)

select * from tb_item where id = 1999;

查詢(xún)速度很快，接近0s，主要的原因是因?yàn)閕d是主鍵，有索引。

B. 根據(jù)NAME查詢(xún)

select * from tb_item where name='abc';

查詢(xún)速度很慢，10s，是因?yàn)閚ame不是主鍵，需要走全表掃描查詢(xún)

查看SQL語(yǔ)句的執(zhí)行計(jì)劃可知

explain select * from tb_item where name='abc';

處理方案，針對(duì)name字段，創(chuàng)建索引：

create index idx_item_name on tb_item(name);
# 此時(shí)數(shù)據(jù)庫(kù)底層在對(duì)300萬(wàn)條記錄進(jìn)行重構(gòu)索引，耗時(shí)較久

創(chuàng)建好name的索引后再次查詢(xún)：

select * from tb_item where name='abc';

此時(shí)查詢(xún)速度很快，接近0s，因?yàn)閚ame也是索引，不用再走全表掃描

6.2 索引的使用

創(chuàng)建索引不一定能提高查詢(xún)的效率，如何正確的使用索引，最主要的是要避免索引失效。

環(huán)境準(zhǔn)備：

-- 先對(duì)tb_seller表的 name, status, address字段創(chuàng)建聯(lián)合索引
create index idx_seller_name_sta_addr on tb_seller(name, status, address);

避免索引失效的方法：

1. 全值匹配。對(duì)索引中所有列都指定具體值，即查詢(xún)條件中聯(lián)合索引的所有列都指定值。

explain select * from tb_seller where name='小米' and status='1' and address='北京市';

2. 最左前綴法則。如果索引是聯(lián)合索引，要遵循最左前綴法則。即如果查詢(xún)條件只需要聯(lián)合索引的部分列，一定要從創(chuàng)建聯(lián)合索引時(shí)最左邊的列開(kāi)始使用

-- 匹配了最左前綴法則，索引生效
explain select * from tb_seller where name='小米';

explain select * from tb_seller where name='小米' and status='1';

explain select * from tb_seller where name='小米' and address='北京市';


-- 違反最左前綴法則，索引失效
explain select * from tb_seller where status='1';

explain select * from tb_seller where address='北京市';

explain select * from tb_seller where status='1' and address='北京市';

3. 范圍查詢(xún)右邊的列不走索引（范圍查詢(xún)條件之后的字段索引失效）。即查詢(xún)條件中如果有指定范圍的條件應(yīng)該放在最后，不然該條件后面的條件就不能走索引

explain select * from tb_seller where name='小米' and status > '1' and address='北京市';
# 此時(shí)走的是name和status兩個(gè)字段的聯(lián)合索引，而address因?yàn)樵诜秶鷹l件之后，所以address這個(gè)條件沒(méi)有走索引

4. 不要在索引列上進(jìn)行運(yùn)算操作，否則索引失效

explain select * from tb_seller where substring(name, 3, 2) = '科技';
# 字符串操作也屬于運(yùn)算

5. 字符串不加單引號(hào)會(huì)造成索引失效

explain select * from tb_seller status = 1;
# 雖然status列的類(lèi)型是字符串，但如果都是數(shù)字不加單引號(hào)MySQL底層也會(huì)將1做隱式類(lèi)型轉(zhuǎn)換然后再進(jìn)行匹配
# 但是這樣做會(huì)造成索引失效，原因是隱式類(lèi)型轉(zhuǎn)換實(shí)際上是對(duì)字段進(jìn)行運(yùn)算操作

6. 盡量使用覆蓋索引，避免 select *，即只查詢(xún)索引中包含的列。超出了索引覆蓋的情況雖然也走了索引，但效率會(huì)降低，原因是使用覆蓋索引的情況可以直接拿到索引對(duì)應(yīng)的數(shù)據(jù)，但超出了覆蓋索引時(shí)，就需要通過(guò)索引到數(shù)據(jù)表中去取值

-- 使用覆蓋索引
explain select name from tb_seller where name='小米' and status='1' and address='北京市';

explain select name, status from tb_seller where name='小米' and status='1' and address='北京市';

explain select name, status, address from tb_seller where name='小米' and status='1' and address='北京市';

# 此時(shí)Extra列的結(jié)果是Using where; Using index 表示直接從索引中取值

-- 超出索引覆蓋
explain select name, status, address, password 
from tb_seller where name='小米' and status='1' and address='北京市';

explain select * from tb_seller where name='小米' and status='1' and address='北京市';

# 此時(shí)Extra列的結(jié)果是Using index condition 表示只是用了索引作為查詢(xún)條件，但具體的數(shù)據(jù)還是得通過(guò)索引到數(shù)據(jù)表中獲取

7. 用or分割開(kāi)的條件，如果or前的條件中的列有索引，而后面的列中沒(méi)有索引，那么涉及索引的列都不會(huì)被用到

-- 用or分割，沒(méi)有走索引
explain select * from tb_seller where name='小米科技' or password='aaa';

-- 用and分割，走查詢(xún)條件中的索引字段會(huì)走索引，只有非索引字段不走索引
explain select * from tb_seller where name='小米科技' and password='aaa';
# 此時(shí)name條件走索引，password條件不走索引

8. 以%開(kāi)頭的 like 模糊查詢(xún)會(huì)導(dǎo)致索引失效。

   如果僅僅是尾部模糊匹配，索引不會(huì)失效，如果是頭部模糊匹配，索引失效。

-- 尾部模糊匹配，索引不會(huì)失效
explain select * from tb_seller where name like '小米%';

-- 帶了頭部模糊匹配的，索引失效
explain select * from tb_seller where name like '%小米';

explain select * from tb_seller where name like '%小米%'

解決方法：使用覆蓋索引來(lái)進(jìn)行查詢(xún)

explain select sellerid, name, status, address from tb_seller where name like '%小米%';
# sellerid雖然不屬于聯(lián)合索引中的列，但sellerid是主鍵，主鍵自帶索引，所以可以用作覆蓋索引查詢(xún)的列
# 此時(shí)雖然用了頭部模糊匹配，但還是會(huì)走索引

9. 如果MySQL評(píng)估，此時(shí)使用索引的效率要比走全表掃描更慢，則不會(huì)使用索引

-- 走全表掃描
explain select * from tb_seller where name='蘋(píng)果';

-- 走索引
explain select * from tb_seller where name='小米科技';

# 同樣的SQL語(yǔ)句，只有查詢(xún)條件的值不同，MySQL判定一個(gè)走了全表掃描一個(gè)走了索引
# 這是和數(shù)據(jù)表中的數(shù)據(jù)有關(guān)，因?yàn)榇藭r(shí)數(shù)據(jù)表中一共有12條數(shù)據(jù)數(shù)據(jù)
# 而name='蘋(píng)果'的數(shù)據(jù)占了11條，這種情況下MySQL判定走全表掃描比走索引更快