MySQL

MySQL基礎篇

MySQL概述

資料庫相關概念

名稱	概念	簡稱
資料庫	存盤資料的倉庫，資料是有組織的進行存盤	DataBase(DB)
資料庫管理系統	操縱和管理資料庫的大型軟體	DataBase Management System(DBMS)
SQL	操作關系型資料庫的編程語言，定義了一套操作關系型資料庫統一標準	Structured Query Language(SQL)

• 主流的關系型資料庫管理系統

MySQL資料庫

• 版本

MySQL官方提供了兩種不同的版本：

• 社區版（MySQL Community Server）

? 免費，MySQL不提供任何技術支持

• 商業版（MySQL Enterprise Edition）

? 收費，可以試用30天，官方提供技術支持

• 下載

? 下載地址：https://dev.mysql.com/downloads/installer

• 啟動與停止

  • 啟動

  net start mysql
  

  • 停止

  net stop mysql
  

• 客戶端連接

方式一：MySQL提供的客戶端命令列工具

方式二：系統自帶的命令列工具執行指令

mysql [-h 127.0.0.1] [-P 3306] -u root -p

• 關系型資料庫

概念：建立在關系模型基礎上，由多張相互連接的二維表組成的資料庫，

特點：

1. 使用表存盤資料，格式統一，便于維護
2. 使用SQL語言操作，標準統一，使用方便

• 資料模型

SQL

SQL通用語法

1. SQL陳述句可以單行或多行書寫，以分號結尾，
2. SQL陳述句可以使用空格/縮進來增強陳述句的可讀性，
3. MySQL資料庫的SQL陳述句不區分大小寫，關鍵字建議使用大寫，
4. 注釋：

• 單行注釋：-- 注釋內容 或 # 注釋內容（MySQL特有）
• 多行注釋：/* 注釋內容 */

SQL分類

分類	全稱	說明
DDL	Data Definition Language	資料定義語言，用來定義資料庫物件（資料庫，表，欄位）
DML	Data Manipulation Language	資料操作語言，用來對資料庫表中的資料進行增刪改
DQL	Data Query Language	資料查詢語言，用來查詢資料庫中表的記錄
DCL	Data Control Language	資料控制語言，用來創建資料庫用戶、控制資料庫的訪問權限

DDL

• DDL-資料庫操作

  • 查詢

  查詢所有資料庫

  SHOW DATABASES;
  

  查詢當前資料庫

  SELECT DATABASE();
  

  • 創建

  CREATE DATABASE [IF NOT EXISTS] 資料庫名 [DEFAULT CHARSET 字符集] [COLLATE 排序規則];
  

  • 洗掉

  DROP DATABASE [IF EXISTS] 資料庫名;
  

  • 使用

  USE 資料庫名;
  

• DDL-表操作-查詢

  • 查詢當前資料庫所有表

  SHOW TABLES;
  

  • 查詢表結構

  DESC 表名;
  

  • 查詢指定表的建表陳述句

  SHOW CREATE TABLE 表名;
  

• DDL-表操作-創建

CREATE TABLE 表名(
	欄位1 欄位1型別 [COMMENT 欄位1注釋],
    欄位2 欄位2型別 [COMMENT 欄位2注釋],
    欄位3 欄位3型別 [COMMENT 欄位3注釋],
    ...
    欄位n 欄位n型別 [COMMENT 欄位n注釋]
) [COMMENT 表注釋];

• DDL-表操作-資料型別

MySQL中的資料型別有很多，主要分為三類：數值型別、字串型別、日期時間型別，

數值型別：

分類	型別	大小	有符號(SIGNED)范圍	無符號(UNSIGNED)范圍	描述
數值型別	TINYINT	1 byte	(-128, 127)	(0, 255)	小整數值
	SMALLINT	2 bytes	(-32768, 32767)	(0, 65535)	大整數值
	MEDIUMINT	3 bytes	(-8388608, 8388607)	(0, 16777215)	大整數值
	INT或INTEGER	4 bytes	(-2147483648, 2147483647)	(0, 4294967295)	大整數值
	BIGINT	8 bytes	(-2^63, 2^63-1)	(0, 2^64-1)	極大整數值
	FLOAT	4 bytes	(-3.402823466 E+38, 3.402823466351 E+38)	0 和 (1.175494351 E-38, 3.402823466 E+38)	單精度浮點數值
	DOUBLE	8 bytes	(-1.7976931348623157 E+308, 1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308)	雙精度浮點數值
	DECIMAL		依賴于M(精度)和D(標度)的值	依賴于M(精度)和D(標度)的值	小數值(精確定點數)

字串型別：

分類	型別	大小	描述
字串型別	CHAR	0-255 bytes	定長字串
	VARCHAR	0-65535 bytes	變長字串
	TINYBLOB	0-255 bytes	不超過255個字符的二進制資料
	TINYTEXT	0-255 bytes	短文本字串
	BLOB	0-66 535 bytes	二進制形式的長文本資料
	TEXT	0-66 535 bytes	長文本資料
	MEDIUMBLOB	0-16 777 215 bytes	二進制形式的中等長度文本資料
	MEDIUMTEXT	0-16 777 215 bytes	中等長度文本資料
	LONGBLOB	0-4 294 967 295 bytes	二進制形式的極大文本資料
	LONGTEXT	0-4 294 967 295 bytes	極大文本資料

日期時間型別：

分類	型別	大小	范圍	格式	描述
日期時間型別	DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
	TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	時間值或持續時間
	YEAR	1	1901 至 2155	YYYY	年份值
	DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和時間值
	TIMESTAMP	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和時間值，時間戳

案例：

根據需求創建表（設計合理的資料型別、長度）

設計一張員工資訊表，要求如下：

1. 編號（純數字）
2. 員工工號（字串型別，長度不超過10位）
3. 員工姓名（字串型別，長度不超過10位）
4. 性別（男/女，存盤一個漢字）
5. 年齡（正常人年齡，不可能存盤負數）
6. 身份證號（二代身份證號均為18位，身份證中有X這樣的字符）
7. 入職時間（取值年月日即可）

create table emp(
	id int comment '編號',
    workno varchar(10) comment '工號',
    name varchar(10) comment '姓名',
    gender char(1) comment '性別',
    age tinyint unsigned comment '年齡',
    idcard char(18) comment '身份證號',
    entrydate date comment '入職時間'
) comment '員工表';

• DDL-表操作-修改

  • 添加欄位

  ALTER TABLE 表名 ADD 欄位名 型別(長度) [COMMENT 注釋] [約束];
  

  案例：

  為emp表增加一個新的欄位"昵稱"為nickname，型別為varchar(20)

  alter table emp add nickname varchar(20) comment '昵稱';
  

  • 修改資料型別

  ALTER TABLE 表名 MODIFY 欄位名 新資料型別(長度);
  

  • 修改欄位名和欄位型別

  ALTER TABLE 表名 CHANGE 舊欄位名 新欄位名 型別(長度) [COMMENT 注釋] [約束];
  

  案例：

  將emp表的nickname欄位修改為username，型別為varchar(30)

  alter table emp change nickname username varchar(30) comment '用戶名'; 
  

  • 洗掉欄位

  ALTER TABLE 表名 DROP 欄位名;
  

  案例：

  將emp表的欄位username洗掉

  alter table emp drop username;
  

  • 修改表名

  ALTER TABLE 表名 RENAME TO 新表名;
  

  案例：

  將emp表的表名修改為employee

  alter table emp rename to employee;
  

• DDL-表操作-洗掉

  • 洗掉表

  DROP TABLE [IF EXISTS] 表名;
  

  • 洗掉指定表，并重新創建該表

  TRUNCATE TABLE 表名;
  

  注意：在洗掉表時，表中的全部資料也會被洗掉

DML

• DML-介紹

DML英文全稱是Data Manipulation Language（資料操作語言），用來對資料庫中表的資料記錄進行增刪改操作，

添加資料（INSERT）

修改資料（UPDATE）

洗掉資料（DELETE）

• DML-添加資料

1. 給指定欄位添加資料

INSERT INTO 表名(欄位名1,欄位名2,...) VALUES(值1,值2,...);

2. 給全部欄位添加資料

INSERT INTO 表名 VALUES(值1,值2,...);

3. 批量添加資料

INSERT INTO 表名(欄位名1,欄位名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);

INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);

注意：

• 插入資料時，指定的欄位順序需要與值的順序是一一對應的，

• 字串和日期型資料應該包含在引號中，

• 插入的資料大小，應該在欄位的規定范圍內，

• DML-修改資料

UPDATE 表名 SET 欄位名1=值1,欄位名2=值2,... [WHERE 條件];

注意：修改陳述句的條件可以有，也可以沒有，如果沒有條件，則會修改整張表的所有資料，

• DML-洗掉資料

DELETE FROM 表名 [WHERE 條件];

注意：

• DELETE陳述句的條件可以有，也可以沒有，如果沒有條件，則會洗掉整張表的所有資料，
• DELETE陳述句不能洗掉某一個欄位的值（可以使用UPDATE），

DQL

• DQL-介紹

DQL英文全稱是Data Query Language（資料查詢語言），用來查詢資料庫中表的記錄，

查詢關鍵字：SELECT

• DQL-語法

SELECT
	欄位串列
FROM
	表名串列
WHERE
	條件串列
GROUP BY
	分組欄位串列
HAVING
	分組后條件串列
ORDER BY
	排序欄位串列
LIMIT
	分頁引數

• DQL-基本查詢

1. 查詢多個欄位

SELECT 欄位1,欄位2,欄位3,... FROM 表名;

SELECT * FROM 表名;

2. 設定別名

SELECT 欄位1 [AS 別名1],欄位2 [AS 別名2],... FROM 表名;

3. 去除重復記錄

SELECT DISTINCT 欄位串列 FROM 表名;

• DQL-條件查詢

1. 語法

SELECT 欄位串列 FROM 表名 WHERE 條件串列;

2. 條件

比較運算子	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN ... AND ...	在某個范圍之內（含最小、最大值）
IN(...)	在in之后的串列中的值，多選一
LIKE 占位符	模糊匹配（_匹配單個字符，%匹配任意個字符）
IS NULL	是NULL

邏輯運算子	功能
AND 或 &&	并且（多個條件同時成立）
OR 或 ||	或者（多個條件任意一個成立）
NOT 或 !	非，不是

• DQL-聚合函式

1. 介紹

將一列資料作為一個整體，進行縱向計算，

2. 常見聚合函式

函式	功能
count	統計數量
max	最大值
min	最小值
avg	平均值
sum	求和

3. 語法

SELECT 聚合函式(欄位串列) FROM 表名;

注意：null值不參與所有聚合函式運算，

• DQL-分組查詢

1. 語法

SELECT 欄位串列 FROM 表名 [WHERE 條件] GROUP BY 分段欄位名 [HAVING 分組后過濾條件];

2. where和having區別

• 執行時機不同：where是分組之前進行過濾，不滿足where條件，不參與分組；而having是分組之后對結果進行過濾，
• 判斷條件不同：where不能對聚合函式進行判斷，而having可以，

注意：

• 執行順序：where > 聚合函式 > having

• 分組之后，查詢的欄位一般為聚合函式和分組欄位，查詢其他欄位無任何意義

• DQL-排序查詢

1. 語法

SELECT 欄位串列 FROM 表名 ORDER BY 欄位1 排序方式1,欄位2 排序方式2,...;

2. 排序方式

• ASC: 升序（默認值）
• DESC: 降序

注意：如果是多欄位排序，當第一個欄位值相同時，才會根據第二個欄位進行排序，

• DQL-分頁查詢

1. 語法

SELECT 欄位串列 FROM 表名 LIMIT 起始索引,查詢記錄數;

注意：

• 起始索引從0開始，起始索引 = (查詢頁碼 - 1) * 每頁顯示記錄數，

• 分頁查詢是資料庫的方言，不同的資料庫有不同的實作，MySQL中是LIMIT，

• 如果查詢的是第一頁資料，起始索引可以省略，直接簡寫為limit 查詢記錄數，

• DQL-執行順序

FROM
	表名串列
WHERE
	條件串列
GROUP BY
	分組欄位串列
HAVING
	分組后條件串列
SELECT
	欄位串列
ORDER BY
	排序欄位串列
LIMIT
	分頁引數

DCL

• DCL-介紹

DCL英文全稱是Data Control Language（資料控制語言），用來管理資料庫用戶、控制資料庫的訪問權限，

• DCL-管理用戶

1. 查詢用戶

USE mysql;
SELECT * FROM user;

2. 創建用戶

CREATE USER '用戶名'@'主機名' IDENTIFIED BY '密碼';

3. 修改用戶密碼

ALTER USER '用戶名'@'主機名' IDENTIFIED WITH mysql_native_password BY '新密碼';

4. 洗掉用戶

DROP USER '用戶名'@'主機名';

注意：

• 主機名可以使用%通配，

• 這類SQL開發人員操作的比較少，主要是DBA（Database Administrator 資料庫管理員）使用，

• DCL-權限控制

MySQL中定義了很多種權限，都是常用的就以下幾種：

權限	說明
ALL, ALL PRIVILEGES	所有權限
SELECT	查詢資料
INSERT	插入資料
UPDATE	修改資料
DELETE	洗掉資料
ALTER	修改表
DROP	洗掉資料庫/表/視圖
CREATE	創建資料庫/表

1. 查詢權限

SHOW GRANTS FOR '用戶名'@'主機名';

2. 授予權限

GRANT 權限串列 ON 資料庫名.表名 TO '用戶名'@'主機名';

3. 撤銷權限

REVOKE 權限串列 ON 資料庫名.表名 FROM '用戶名'@'主機名';

注意：

• 多個權限之間，使用逗號分隔，
• 授權時，資料庫名和表名可以使用 * 進行通配，代表所有，

函式

函式是指一段可以直接被另一段程式呼叫的程式或代碼，

字串函式

MySQL中內置了很多字串函式，常用的幾個如下：

函式	功能
CONCAT(S1,S2,...,Sn)	字串拼接，將S1, S2, ... Sn拼接成一個字串
LOWER(str)	將字串str全部轉為小寫
UPPER(str)	將字串str全部轉為大寫
LPAD(str,n,pad)	左填充，用字串pad對str的左邊進行填充，達到n個字串長度
RPAD(str,n,pad)	右填充，用字串pad對str的右邊進行填充，達到n個字串長度
TRIM(str)	去掉字串頭部和尾部的空格
SUBSTRING(str,start,len)	回傳字串str從start位置起的len個長度的字串

• 根據需求完成以下SQL撰寫

由于業務需求變更，企業員工的工號，統一為5位數，目前不足5位數的全部在前面補0，比如：1號員工的工號應該為00001，

update emp set workno = lpad(workno, 5, '0');

數值函式

常見的數值函式如下：

函式	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x)	回傳x/y的模
RAND()	回傳0~1內的亂數
ROUND(x,y)	求引數x的四舍五入的值，保留y位小數

• 根據需求完成以下SQL撰寫

通過資料庫的函式，生成一個六位數的隨機驗證碼

select lpad(round(rand()*1000000, 0), 6, '0');

日期函式

常見的日期函式如下：

函式	功能
CURDATE()	回傳當前日期
CURTIME()	回傳當前時間
NOW()	回傳當前日期和時間
YEAR(date)	獲取指定date的年份
MONTH(date)	獲取指定date的月份
DAY(date)	獲取指定date的日期
DATE_ADD(date, INTERVAL expr type)	回傳一個日期/時間值加上一個時間間隔expr后的時間值
DATEDIFF(date1, date2)	回傳起始時間date1和結束時間date2之間的天數

• 根據需求完成以下SQL撰寫

查詢所有員工的入職天數，并根據入職天數倒序排序

select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;

流程函式

流程函式也是很常用的一類函式，可以在SQL陳述句中實作條件篩選，從而提高陳述句的效率，

函式	功能
IF(value, t, f)	如果value為true，則回傳t，否則回傳f
IFNULL(value1, value2)	如果value1不為空，回傳value1，否則回傳value2
CASE WHEN [val1] THEN [res1] ... ELSE [default] END	如果val1為true，回傳res1，...，否則回傳default默認值
CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END	如果expr的值等于val1，回傳res1，...，否則回傳default默認值

• 根據需求完成以下SQL撰寫

統計班級各個學員的成績，展示的規則如下：

• 大于等于85，展示優秀
• 大于等于60，展示及格
• 否則，展示不及格

select
	id,
	name,
	(case when math >= 85 then '優秀' when math >= 60 then '及格' else '不及格' end) '數學',
	(case when english >= 85 then '優秀' when english >= 60 then '及格' else '不及格' end) '英語',
	(case when chinese >= 85 then '優秀' when chinese >= 60 then '及格' else '不及格' end) '語文'
from score;

約束

概述

1. 概念：約束是作用于表中欄位上的規則，用于限制存盤在表中的資料，
2. 目的：保證資料庫中資料的正確性、有效性和完整性，
3. 分類：

約束	描述	關鍵字
非空約束	限制該欄位的資料不能為null	NOT NULL
唯一約束	保證該欄位的所有資料都是唯一、不重復的	UNIQUE
主鍵約束	主鍵是一行資料的唯一標識，要求非空且唯一	PRIMARY KEY
默認約束	保存資料時，如果未指定該欄位的值，則采用默認值	DEFAULT
檢查約束(8.0.16版本之后)	保證欄位值滿足某一個條件	CHECK
外鍵約束	用來讓兩張表的資料之間建立連接，保證資料的一致性和完整性	FOREIGN KEY

注意：約束是作用于表中欄位上的，可以在創建表/修改表的時候添加約束，

約束演示

根據需求，完成表結構的創建

欄位名	欄位含義	欄位型別	約束條件	約束關鍵字
id	ID唯一標識	int	主鍵，并且自動增長	PRIMARY KEY, AUTO_INCREMENT
name	姓名	varchar(10)	不為空，并且唯一	NOT NULL, UNIQUE
age	年齡	int	大于0，并且小于等于120	CHECK
status	狀態	char(1)	如果沒有指定該值，默認為1	DEFAULT
gender	性別	char(1)	無

create table user(
	id int primary key auto_increment comment '主鍵',
    name varchar(10) not null unique comment '姓名',
    age int check(age > 0 && age <= 120) comment '年齡',
    status char(1) default '1' comment '狀態',
    gender char(1) comment '性別'
) comment '用戶表';

外鍵約束

• 概念

外鍵用來讓兩張表的資料之間建立連接，從而保證資料的一致性和完整性，

• 語法

  • 添加外鍵

  CREATE TABLE 表名(
  	欄位名 資料型別,
      ...
      [CONSTRAINT] [外鍵名稱] FOREIGN KEY (外鍵欄位名) REFERENCES 主表(主表列名)
  );
  

  ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵欄位名) REFERENCES 主表(主表列名);
  

  • 洗掉外鍵

  ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;
  

• 洗掉/更新行為

行為	說明
NO ACTION	當從父表中洗掉/更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則不允許洗掉/更新，（與RESTRICT一致）
RESTRICT	當從父表中洗掉/更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則不允許洗掉/更新，（與NO ACTION一致）
CASCADE	當在父表中洗掉/更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有，則也洗掉/更新外鍵在子表中的記錄
SET NULL	當在父表中洗掉對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則設定子表中該外鍵值為null（這就要求該外鍵允許取null）
SET DEFAULT	父表有變更時，子表將外鍵列設定成一個默認的值（Innodb不支持）

ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵欄位) REFERENCES 主表名(主表欄位名) ON UPDATE CASCADE ON DELETE CASCADE;

多表查詢

多表關系

• 概述

專案開發中，在進行資料庫表結構設計時，會根據業務需求及業務模塊之間的關系，分析并設計表結構，由于業務之間相互關聯，所以各個表結構之間也存在著各種聯系，基本上分為三種：

• 一對多（多對一）

• 多對多

• 一對一

• 一對多（多對一）

  • 案例：部門與員工的關系
  • 關系：一個部門對應多個員工，一個員工對應一個部門
  • 實作：在多的一方建立外鍵，指向一的一方的主鍵

• 多對多

  • 案例：學生與課程的關系
  • 關系：一個學生可以選修多門課程，一門課程也可以供多個學生選擇
  • 實作：建立第三張中間表，中間表至少包含兩個外鍵，分別關聯兩方主鍵

• 一對一

  • 案例：用戶與用戶詳情的關系
  • 關系：一對一關系，多用于單表拆分，將一張表的基礎欄位放在一張表中，其他詳情欄位放在另一張表中，以提升操作效率
  • 實作：在任意一方加入外鍵，關聯另外一方的主鍵，并且設定外鍵為唯一的（UNIQUE）

多表查詢概述

• 概述：指從多張表中查詢資料

• 笛卡爾積：笛卡爾乘積是指在數學中，兩個集合A集合和B集合的所有組合情況，（在多表查詢時，需要消除無效的笛卡爾積）

• 多表查詢分類：

  • 連接查詢

    • 內連接：相當于查詢A、B交集部分的資料

    • 外連接：

      ? 左外連接：查詢左表所有資料，以及兩張表交集部分資料

      ? 右外連接：查詢右表所有資料，以及兩張表交集部分資料

    • 自連接：當前表與自身的連接查詢，自連接必須使用表別名

  • 子查詢

內連接

內連接查詢語法：

• 隱式內連接：

SELECT 欄位串列 FROM 表1, 表2 WHERE 條件...;

• 顯式內連接：

SELECT 欄位串列 FROM 表1 [INNER] JOIN 表2 ON 連接條件...;

內連接查詢的是兩張表交集的部分

-- 內連接演示
-- 1.查詢每一個員工的姓名，及關聯的部門的名稱（隱式內連接實作）
select e.name, d.name from emp e, dept d where e.dept_id = d.id;
-- 2.查詢每一個員工的姓名，及關聯的部門的名稱（顯式內連接實作）
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

外連接

外連接查詢語法：

• 左外連接：

SELECT 欄位串列 FROM 表1 LEFT [OUTER] JOIN 表2 ON 條件...;

相當于查詢表1（左表）的所有資料包含表1和表2交集部分的資料

• 右外連接：

SELECT 欄位串列 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 條件...;

相當于查詢表2（右表）的所有資料包含表1和表2交集部分的資料

-- 外連接演示
-- 1.查詢emp表的所有資料，和對應的部門資訊（左外連接）
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
-- 2.查詢dept表的所有資料，和對應的員工資訊（右外連接）
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

自連接

自連接查詢語法：

SELECT 欄位串列 FROM 表A 別名A JOIN 表A 別名B ON 條件...;

自連接查詢，可以是內連接查詢，也可以是外連接查詢，

-- 自連接
-- 1.查詢員工及其所屬領導的名字
select a.name '員工', b.name '領導' from emp a join emp b on a.manager_id = b.id;
-- 2.查詢所有員工及其領導的名字，如果員工沒有領導，也需要查詢出來
select a.name '員工', b.name '領導' from emp a left outer join emp b on a.manager_id = b.id;

聯合查詢

對于union查詢，就是把多次查詢的結果合并起來，形成一個新的查詢結果集，

SELECT 欄位串列 FROM 表A ...
UNION [ALL]
SELECT 欄位串列 FROM 表B ...;

-- 聯合查詢
-- 1.將薪資低于5000的員工和年齡大于50歲的員工全部查詢出來
select * from emp where salary < 5000
union
select * from emp where age > 50;

注意：

• 對于聯合查詢的多張表的列數必須保持一致，欄位型別也需要保持一致，
• union all會將全部的資料直接合并在一起，union會對合并之后的資料去重，

子查詢

• 概念：SQL陳述句中嵌套SELECT陳述句，稱為嵌套陳述句，又稱子查詢，

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查詢外部的陳述句可以是INSERT/UPDATE/DELETE/SELECT的任何一個，

• 根據子查詢結果的不同，分為：

  • 標量子查詢（子查詢結果為單個值）
  • 列子查詢（子查詢結果為一列）
  • 行子查詢（子查詢結果為一行）
  • 表子查詢（子查詢結果為多行多列）

• 根據子查詢的位置，分為：WHERE之后、FROM之后、SELECT之后，

• 標量子查詢

子查詢回傳的結果是單個值（數字、字串、日期等），最簡單的形式，這種子查詢稱為標量子查詢，

常用的運算子：= <> > >= < <=

-- 標量子查詢
-- 1.查詢"銷售部"的所有員工資訊
select * from emp where dept_id = (select id from dept where name = '銷售部');
-- 2.查詢在"方東白"入職之后的員工資訊
select * from emp where entrydate > (select entrydate from emp where name = '方東白');

• 列子查詢

子查詢回傳的結果是一列（可以是多行），這種子查詢稱為列子查詢，

常見的運算子：IN、NOT IN、ANY、SOME、ALL

運算子	描述
IN	在指定的集合范圍之內，多選一
NOT IN	不在指定的集合范圍之內
ANY	子查詢回傳串列中，有任意一個滿足即可
SOME	與ANY等同，使用SOME的地方都可以使用ANY
ALL	子查詢回傳串列的所有值都必須滿足

-- 列子查詢
-- 1.查詢"銷售部"和"市場部"的所有員工資訊
select * from emp where dept_id in (select id from dept where name = '銷售部' or name = '市場部');
-- 2.查詢比財務部所有人工資都高的員工資訊
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '財務部'));
-- 3.查詢比研發部其中任意一人工資高的員工資訊
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研發部'));

• 行子查詢

子查詢回傳的結果是一行（可以是多列），這種子查詢稱為行子查詢，

常用的運算子：= <> IN NOT IN

-- 行子查詢
-- 1.查詢與"張三"的薪資及直屬領導相同的員工資訊
select * from emp where (salary, manager_id) = (select salary, manager_id from emp where name = '張三');

• 表子查詢

子查詢回傳的結果是多行多列，這種子查詢稱為表子查詢，

常用的運算子：IN

-- 表子查詢
-- 1.查詢與"張三"，"李四"的職位和薪資相同的員工資訊
select * from emp where (job, salary) in (select job, salary from emp where name = '張三' or name = '李四');
-- 2.查詢入職日期是"2006-01-01"之后的員工資訊，及其部門資訊
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left outer join dept d on e.dept_id = d.id;

多表查詢案例

根據需求，完成SQL陳述句的撰寫

1. 查詢員工的姓名、年齡、職位、部門資訊，
2. 查詢年齡小于30歲的員工姓名、年齡、職位、部門資訊，
3. 查詢擁有員工的部門ID、部門名稱，
4. 查詢所有年齡大于40歲的員工，及其歸屬的部門名稱；如果員工沒有分配部門，也需要展示出來，
5. 查詢所有員工的工資等級，
6. 查詢"研發部"所有員工的資訊及工資等級，
7. 查詢"研發部"員工的平均工資，
8. 查詢工資比"張三"高的員工資訊，
9. 查詢比平均薪資高的員工資訊，
10. 查詢低于本部門平均工資的員工資訊，
11. 查詢所有的部門資訊，并統計部門的員工人數，
12. 查詢所有學生的選課情況，展示出學生名稱、學號、課程名稱，

-- 1. 查詢員工的姓名、年齡、職位、部門資訊，
select e.name, e.age, e.job, d.* from emp e, dept d where e.dept_id = d.id;
-- 2. 查詢年齡小于30歲的員工姓名、年齡、職位、部門資訊，
select e.name, e.age, e.job, d.* from emp e inner join dept d on e.dept_id = d.id where e.age < 30;
select e.name, e.age, e.job, d.* from (select * from emp where age < 30) e, dept d where e.dept_id = d.id;
-- 3. 查詢擁有員工的部門ID、部門名稱，
select distinct d.id, d.name from emp e, dept d where e.dept_id = d.id;
-- 4. 查詢所有年齡大于40歲的員工，及其歸屬的部門名稱；如果員工沒有分配部門，也需要展示出來，
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;
-- 5. 查詢所有員工的工資等級，
select e.*, s.grade from emp e, salgrade s where e.salary between s.losal and s.hisal;
-- 6. 查詢"研發部"所有員工的資訊及工資等級，
select e.*, s.grade from emp e, dept d, salgrade s where e.dept_id = d.id and (e.salary between s.losal and s.hisal) and d.name = '研發部';
-- 7. 查詢"研發部"員工的平均工資，
select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研發部';
-- 8. 查詢工資比"張三"高的員工資訊，
select * from emp where salary > (select salary from emp where name = '張三');
-- 9. 查詢比平均薪資高的員工資訊，
select * from emp where salary > (select avg(salary) from emp);
-- 10. 查詢低于本部門平均工資的員工資訊，
select * from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);
-- 11. 查詢所有的部門資訊，并統計部門的員工人數，
select d.*, (select count(*) from emp e where e.dept_id = d.id) from dept d;
-- 12. 查詢所有學生的選課情況，展示出學生名稱、學號、課程名稱，
select s.name, s.no, c.name from student s, student_course sc, course c where s.id = sc.student_id and sc.course_id = c.id;

事務

事務簡介

事務是一組操作的集合，它是一個不可分割的作業單位，事務會把所有的操作作為一個整體一起向系統提交或撤銷操作請求，即這些操作要么同時成功，要么同時失敗，

默認MySQL的事務是自動提交的，也就是說，當執行一條DML陳述句，MySQL會立即隱式的提交事務，

事務操作

• 查看/設定事務提交方式

SELECT @@autocommit;
SET @@autocommit = 0;-- 設定為手動提交事務

• 開啟事務

START TRANSACTION 或 BEGIN;

• 提交事務

COMMIT;

• 回滾事務

ROLLBACK;

事務四大特性

• 原子性（Atomicity）：事務是不可分割的最小操作單元，要么全部成功，要么全部失敗，
• 一致性（Consistency）：事務完成時，必須使所有的資料都保持一致狀態，
• 隔離性（Isolation）：資料庫系統提供的隔離機制，保證事務在不受外部并發操作影響的獨立環境下運行，
• 持久性（Durability）：事務一旦提交或回滾，它對資料庫中的資料的改變就是永久的，

并發事務問題

問題	描述
臟讀	一個事務讀到另外一個事務還沒有提交的資料，
不可重復讀	一個事務先后讀取同一條記錄，但兩次讀取的資料不同，稱之為不可重復讀，
幻讀	一個事務按照條件查詢資料時，沒有對應的資料行，但是在插入資料時，又發現這行資料已經存在，好像出現了”幻影“，

事務隔離級別

隔離級別	臟讀	不可重復讀	幻讀
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read(默認)	×	×	√
Serializable	×	×	×

-- 查看事務隔離級別
SELECT @@TRANSACTION_ISOLATION;
-- 設定事務隔離級別
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE};

注意：事務隔離級別越高，資料越安全，但是性能越低，

MySQL進階篇

存盤引擎

MySQL體系結構

• 連接層

最上層是一些客戶端和連接服務，主要完成一些類似于連接處理、授權認證，及相關的安全方案，服務器也會為安全接入的每個用戶端驗證它所具有的操作權限，

• 服務層

第二層架構主要完成大多數的核心服務功能，如SQL介面，并完成快取的查詢，SQL的分析和優化，部分內置函式的執行，所有跨存盤引擎的功能也在這一層實作，如程序、函式等，

• 引擎層

存盤引擎真正的負責了MySQL中資料的存盤和提取，服務器通過API和存盤引擎進行通信，不同的存盤引擎具有不同的功能，這樣我們可以根據自己的需要，來選取合適的存盤引擎，

• 存盤層

主要是將資料存盤在檔案系統之上，并完成與存盤引擎的互動，

存盤引擎簡介

存盤引擎就是存盤資料、建立索引、更新/查詢資料等技術的實作方式，存盤引擎是基于表的，而不是基于庫的，所以存盤引擎也可以被稱為表型別，

1. 在創建表時，指定存盤引擎

CREATE TABLE 表名(
	欄位1 欄位1型別 [COMMENT 欄位1注釋],
    ...
    欄位n 欄位n型別 [COMMENT 欄位n注釋]
) ENGINE = INNODB [COMMENT 表注釋];

2. 查看當前資料庫支持的存盤引擎

SHOW ENGINES;

存盤引擎特點

• InnoDB

  • 介紹

  InnoDB是一種兼顧高可靠性和高性能的通用存盤引擎，在MySQL5.5之后，InnoDB是默認的MySQL存盤引擎，

  • 特點

  DML操作遵循ACID模型，支持事務；

  行級鎖，提高并發訪問性能；

  支持外鍵FOREIGN KEY約束，保證資料的完整性和正確性，

  • 檔案

  xxx.ibd：xxx代表的是表名，innoDB引擎的每張表都會對應這樣一個表空間檔案，存盤該表的表結構（frm、sdi）、資料和索引，

  引數：innodb_file_per_table

  

• MyISAM

  • 介紹

  MyISAM是MySQL早期的默認存盤引擎，

  • 特點

  不支持事務，不支持外鍵；

  支持表鎖，不支持行鎖；

  訪問速度快，

  • 檔案

  xxx.sdi：存盤表結構資訊

  xxx.MYD：存盤資料

  xxx.MYI：存盤索引

• Memory

  • 介紹

  Memory引擎的表資料是存盤在記憶體中的，由于受到硬體問題或斷電問題的影響，只能將這些表作為臨時表或快取使用，

  • 特點

  記憶體存放；

  hash索引（默認），

  • 檔案

  xxx.sdi：存盤表結構資訊

特點	InnoDB	MyISAM	Memory
存盤限制	64TB	有	有
事務安全	支持	-	-
鎖機制	行鎖	表鎖	表鎖
B+tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持（5.6版本之后）	支持	-
空間使用	高	低	N/A
記憶體使用	高	低	中等
批量插入速度	低	高	高
支持外鍵	支持	-	-

存盤引擎選擇

在選擇存盤引擎時，應該根據應用系統的特點選擇合適的存盤引擎，對于復雜的應用系統，還可以根據實際情況選擇多種存盤引擎進行組合，

• InnoDB：是MySQL的默認存盤引擎，支持事務、外鍵，如果應用對事務的完整性有比較高的要求，在并發條件下要求資料的一致性，資料操作除了插入和查詢之外，還包含很多的更新、洗掉操作，那么InnoDB存盤引擎是比較合適的選擇，
• MyISAM：如果應用是以讀操作和插入操作為主，只有很少的更新和洗掉操作，并且對事務的完整性、并發性要求不是很高，那么選擇這個存盤引擎是非常合適的，
• MEMORY：將所有資料保存在記憶體中，訪問速度快，通常用于臨時表及快取，MEMORY的缺陷就是對表的大小有限制，太大的表無法快取在記憶體中，而且無法保障資料的安全性，

索引

索引概述

• 介紹

索引（index）是幫助MySQL高效獲取資料的資料結構（有序），在資料之外，資料庫系統還維護著滿足特定查找演算法的資料結構，這些資料結構以某種方式參考（指向）資料，這樣就可以在這些資料結構上實作高級查找演算法，這種資料結構就是索引，

• 優缺點

優勢	劣勢
提高資料檢索的效率，降低資料庫的IO成本	索引列也是要占用空間的
通過索引列對資料進行排序，降低資料排序的成本，降低CPU的消耗	索引大大提高了查詢效率，同時卻也降低了更新表的速度，如對表進行INSERT、UPDATE、DELETE時，效率降低

索引結構

MySQL的索引是在存盤引擎層實作的，不同的存盤引擎有不同的結構，主要包含以下幾種：

索引結構	描述
B+tree索引	最常見的索引型別，大部分引擎都支持B+樹索引
Hash索引	底層資料結構是用哈希表實作的，只有精確匹配索引列的查詢才有效，不支持范圍查詢
R-tree（空間索引）	空間索引是MyISAM引擎的一個特殊索引型別，主要用于地理空間資料型別，通常使用較少
Full-text（全文索引）	是一種通過建立倒排索引，快速匹配檔案的方式，類似于Lucene,Solr,ES

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我們平常所說的索引，如果沒有特別指明，都是指B+樹結構組織的索引，

• B+Tree

MySQL索引資料結構對經典的B+Tree進行了優化，在原B+Tree的基礎上，增加一個指向相鄰葉子節點的鏈表指標，就形成了帶有順序指標的B+Tree，提高區間訪問的性能，

• Hash

哈希索引就是采用一定的hash演算法，將鍵值換算成新的hash值，映射到對應的槽位上，任何存盤在hash表中，

如果兩個（或多個）鍵值，映射到一個相同的槽位上，它們就產生了hash沖突（也稱為hash碰撞），可以通過鏈表來解決，

Hash索引特點：

1. Hash索引只能用于對等比較(=, in)，不支持范圍查詢(between, >, <, ...)
2. 無法利用索引完成排序操作
3. 查詢效率高，通常只需要一次檢索就可以了，效率通常要高于B+tree索引

存盤引擎支持：

在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自適應hash功能，hash索引是存盤引擎根據B+Tree索引在指定條件下自動構建的，

為什么InnoDB存盤引擎選擇使用B+Tree索引結構？

• 相對于二叉樹，層級更少，搜索效率高；
• 對于B-tree，無論是葉子節點還是非葉子節點，都會保存資料，這樣導致一頁中存盤的鍵值減少，指標跟著減少，要同樣保存大量資料，只能增加樹的高度，導致性能降低；
• 相對Hash索引，B+tree支持范圍匹配及排序操作，

索引分類

分類	含義	特點	關鍵字
主鍵索引	針對于表中主鍵創建的索引	默認自動創建，只能有一個	PRIMARY
唯一索引	避免同一個表中某資料列中的值重復	可以有多個	UNIQUE
常規索引	快速定位特定資料	可以有多個
全文索引	全文索引查找的是文本中的關鍵詞，而不是比較索引中的值	可以有多個	FULLTEXT

在InnoDB存盤引擎中，根據索引的存盤形式，又可以分為以下兩種：

分類	含義	特點
聚集索引(Clustered Index)	將資料存盤與索引放到了一塊，索引結構的葉子節點保存了行資料	必須有，而且只有一個
二級索引(Secondary Index)	將資料與索引分開存盤，索引結構的葉子節點關聯的是對應的主鍵	可以存在多個

聚集索引選取規則：

• 如果存在主鍵，主鍵索引就是聚集索引，
• 如果不存在主鍵，將使用第一個唯一（UNIQUE）索引作為聚集索引，
• 如果表沒有主鍵，或沒有合適的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚集索引，

回表查詢：先走二級索引找到對應的主鍵值，再到聚集索引中拿到主鍵值所對應的行資料

索引語法

• 創建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(index_col_name,...);

• 查看索引

SHOW INDEX FROM table_name;

• 洗掉索引

DROP INDEX index_name ON table_name;

按照下列的需求，完成索引的創建

1. name欄位為姓名欄位，該欄位的值可能會重復，為該欄位創建索引，
2. phone手機號欄位的值，是非空，且唯一的，為該欄位創建唯一索引，
3. 為profession、age、status創建聯合索引，
4. 為email建立合適的索引來提升查詢效率，

-- name欄位為姓名欄位，該欄位的值可能會重復，為該欄位創建索引，
create index idx_user_name on tb_user(name);
-- phone手機號欄位的值，是非空，且唯一的，為該欄位創建唯一索引，
create unique index idx_user_phone on tb_user(phone);
-- 為profession、age、status創建聯合索引，
create index idx_user_pro_age_sta on tb_user(profession,age,status);
-- 為email建立合適的索引來提升查詢效率，
create index idx_email on tb_user(email);

SQL性能分析

• SQL執行頻率

MySQL客戶端連接成功后，通過show [session|global] status命令可以提供服務器狀態資訊，通過如下指令，可以查看當前資料庫的INSERT、UPDATE、DELETE、SELECT的訪問頻次：

SHOW GLOBAL STATUS LIKE 'Com_______';

• 慢查詢日志

慢查詢日志記錄了所有執行時間超過指定引數（long_query_time，單位：秒，默認10秒）的所有SQL陳述句的日志，

-- 查詢慢查詢日志開關是否開啟
show variables like 'slow_query_log';

MySQL的慢查詢日志默認沒有開啟，需要在MySQL的組態檔（/etc/my.cnf）中配置如下資訊：

#開啟MySQL慢查詢日志的開關
slow_query_log=1
#設定慢日志的時間為2秒，SQL陳述句執行時間超過2秒，就會視為慢查詢，記錄慢查詢日志
long_query_time=2

配置完畢之后，通過以下指令重新啟動MySQL服務器進行測驗，查看慢日志檔案中記錄的資訊/var/lib/mysql/localhost-slow.log，

systemctl restart mysqld

• profile詳情

show profiles能夠在做SQL優化時幫助我們了解時間都耗費到哪里去了，通過have_profiling引數，能夠看到當前MySQL是否支持profile操作：

SELECT @@have_profiling;

默認profiling是關閉的，可以通過set陳述句在session/global級別開啟profiling：

-- 查看profiling開關是否打開
SELECT @@profiling;
-- 打開profiling開關
SET profiling=1;

執行一系列的業務SQL的操作，然后通過如下指令查看指令的執行耗時：

#查看每一條SQL的耗時基本情況
show profiles;
#查看指定query_id的SQL陳述句各個階段的耗時情況
show profile for query query_id;
#查看指定query_id的SQL陳述句CPU的使用情況
show profile cpu for query query_id;

• explain執行計劃

EXPLAIN或者DESC命令獲取MySQL如何執行SELECT陳述句的資訊，包括在SELECT陳述句執行程序中表如何連接和連接的順序，

語法：

#直接在select陳述句之前加上關鍵字explain/desc
EXPLAIN SELECT 欄位串列 FROM 表名 WHERE 條件;

EXPLAIN執行計劃各欄位含義：

• id

select查詢的序列號，表示查詢中執行select子句或者是操作表的順序（id相同，執行順序從上到下；id不同，值越大，越先執行），

• select_type

表示SELECT的型別，常見的取值有SIMPLE（簡單表，即不使用表連接或者子查詢）、PRIMARY（主查詢，即外層的查詢）、UNION（UNION中的第二個或者后面的查詢陳述句）、SUBQUERY（SELECT/WHERE之后包含了子查詢）等，

• type

表示連接型別，性能由好到差的連接型別為NULL、system、const、eq_ref、ref、range、index、all，

• possible_key

顯示可能應用在這張表上的索引，一個或多個，

• key

實際使用的索引，如果為NULL，則沒有使用索引，

• Key_len

表示索引中使用的位元組數，該值為索引欄位最大可能長度，并非實際使用長度，在不損失精確性的前提下，長度越短越好，

• rows

MySQL認為必須要執行查詢的行數，在InnoDB引擎的表中，是一個估計直接，可能并不總是準確的，

• filtered

表示回傳結果的行數占需讀取行數的百分比，filtered的值越大越好，

索引使用

• 驗證索引效率

在未建立索引之前，執行如下SQL陳述句，查看SQL的耗時：

SELECT * FROM tb_sku WHERE sn = '100000003145001';

針對欄位創建索引：

create index idx_sku_sn on tb_sku(sn);

然后再次執行相同的SQL陳述句，再次查看SQL的耗時：

SELECT * FROM tb_sku WHERE sn = '100000003145001';

• 最左前綴法則

如果索引了多列（聯合索引），要遵守最左前綴法則，最左前綴法則指的是查詢從索引的最左列開始，并且不跳過索引中的列，

如果跳躍某一列，索引將部分失效（后面的欄位索引失效），

• 范圍查詢

聯合索引中，出現范圍查詢（>，<），范圍查詢右側的列索引失效，

• 索引列運算

不要在索引列上進行運算操作，索引將失效，

• 字串不加引號

字串型別欄位使用時，不加引號，索引將失效，

• 模糊查詢

如果僅僅是尾部模糊匹配，索引不會失效；如果是頭部模糊匹配，索引失效，

• or連接的條件

用or分割開的條件，如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會被用到，

• 資料分布影響

如果MySQL評估使用索引比全表更慢，則不使用索引，

• SQL提示

SQL提示，是優化資料庫的一個重要手段，簡單來說，就是在SQL陳述句中加入一些人為的提示來達到優化操作的目的，

use index:

explain select * from tb_user use index(idx_user_pro) where profession = '軟體工程';

ignore index:

explain select * from tb_user ignore index(idx_user_pro) where profession = '軟體工程';

force index:

explain select * from tb_user force index(idx_user_pro) where profession = '軟體工程';

• 覆寫索引

盡量使用覆寫索引（查詢使用了索引，并且需要回傳的列，在該索引中已經全部能夠找到），減少select *，

using index condition: 查找使用了索引，但是需要回表查詢資料，

using where; using index: 查找使用了索引，但是需要的資料都在索引列中能找到，所以不需要回表查詢資料，

• 前綴索引

當欄位型別為字串（varchar, text等）時，有時候需要索引很長的字串，這會讓索引變得很大，查詢時，浪費大量的磁盤IO，影響查詢效率，此時可以只將字串的一部分前綴建立索引，這樣可以大大節約索引空間，從而提高索引效率，

• 語法

create index idx_xxxx on table_name(column(n));

• 前綴長度

可以根據索引的選擇性來決定，而選擇性是指不重復的索引值（基數）和資料表的記錄總數的比值，索引選擇性越高則查詢效率越高，唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的，

select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email,1,5)) / count(*) from tb_user;

• 單列索引與聯合索引

單列索引：即一個索引只包含單個列，

聯合索引：即一個索引包含了多個列，

在業務場景中，如果存在多個查詢條件，考慮針對于查詢欄位建立索引時，建議建立聯合索引，而非單列索引，

多條件聯合查詢時，MySQL優化器會評估哪個欄位的索引效率更高，會選擇該索引完成本次查詢，

索引設計原則

1. 針對于資料量較大，且查詢比較頻繁的表建立索引，
2. 針對于常作為查詢條件（where）、排序（order by）、分組（group by）操作的欄位建立索引，
3. 盡量選擇區分度高的列作為索引，盡量建立唯一索引，區分度越高，使用索引的效率越高，
4. 如果是字串型別的欄位，欄位的長度較長，可以針對于欄位的特點，建立前綴索引，
5. 盡量使用聯合索引，減少單列索引，查詢時，聯合索引很多時候可以覆寫索引，節省存盤空間，避免回表，提高查詢效率，
6. 要控制索引的數量，索引并不是多多益善，索引越多，維護索引結構的代價也就越大，會影響增刪改的效率，
7. 如果索引列不能存盤NULL值，請在創建表時用NOT NULL約束它，當優化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用于查詢，

SQL優化

插入資料

• insert優化

  • 批量插入

  insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
  

  • 手動提交事務

  start transaction;
  insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
  insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
  insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
  commit;
  

  • 主鍵順序插入

  -- 主鍵亂序插入：8  1  9  21  88  2  4  15  89  5  7  3
  -- 主鍵順序插入：1  2  3  4  5  7  8  9  15  21  88  89
  

• 大批量插入資料

如果一次性需要插入大批量資料，使用insert陳述句插入性能較低，此時可以使用MySQL資料庫提供的load指令進行插入，操作如下：

#客戶端連接服務端時，加上引數--local-infile
mysql --local-infile -u root -p
#設定全域引數local_infile為1，開啟從本地加載檔案匯入資料的開關
set global local_infile = 1;
#執行load指令將準備好的資料，加載到表結構中
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by'\n';

主鍵優化

• 資料組織方式

在InnoDB存盤引擎中，表資料都是根據主鍵順序組織存放的，這種存盤方式的表稱為索引組織表（index organized table IOT），

• 頁分裂

頁可以為空，也可以填充到一半，也可以填充100%，每個頁包含了2-N行資料（如果一行資料過大，會行溢位），根據主鍵排列，

• 頁合并

當洗掉一行記錄時，實際上記錄并沒有被物理洗掉，只是記錄被標記（flaged）為洗掉并且它的空間變得允許被其他記錄宣告使用，

當頁中洗掉的記錄達到MERGE_THRESHOLD（默認為頁的50%），InnoDB會開始尋找最靠近的頁（前或后）看看是否可以將兩個頁合并以優化空間使用，

MERGE_THRESHOLD：合并頁的閾值，可以自己設定，在創建表或者創建索引時指定，

• 主鍵設計原則
  • 滿足業務需求的情況下，盡量降低主鍵的長度，
  • 插入資料時，盡量選擇順序插入，選擇使用AUTO_INCREMENT自增主鍵，
  • 盡量不要使用UUID做主鍵或者是其他自然主鍵，如身份證號，
  • 業務操作時，避免對主鍵的修改，

order by優化

① Using filesort：通過表的索引或全表掃描，讀取滿足條件的資料行，然后在排序緩沖區sort buffer中完成排序操作，所有不是通過索引直接回傳排序結果的排序都叫FileSort排序，

② Using index：通過有序索引順序掃描直接回傳有序資料，這種情況即為using index，不需要額外排序，操作效率高，

• 根據排序欄位建立合適的索引，多欄位排序時，也遵循最左前綴法則，
• 盡量使用覆寫索引，
• 多欄位排序，一個升序一個降序，此時需要注意聯合索引在創建時的規則（ASC/DESC），
• 如果不可避免的出現filesort，大資料量排序時，可以適當增大排序緩沖區大小sort_buffer_size（默認256k），

group by優化

• 在分組操作時，可以通過索引來提高效率，
• 分組操作時，索引的使用也是滿足最左前綴法則的，

limit優化

一個常見又頭疼的問題就是limit 2000000,10，此時需要MySQL排序前2000010記錄，僅僅回傳2000000-2000010的記錄，其他記錄丟棄，查詢排序的代價非常大，

優化思路：一般分頁查詢時，通過創建覆寫索引能夠比較好地提高性能，可以通過覆寫索引加子查詢形式進行優化，

explain select s.* from tb_sku s,(select id from tb_sku order by id limit 2000000,10) a where s.id = a.id;

count優化

• MyISAM引擎把一個表的總行數存在了磁盤上，因此執行count(*)的時候會直接回傳這個數，效率很高；
• InnoDB引擎執行count(*)的時候，需要把資料一行一行地從引擎里面讀出來，然后累積計數，

優化思路：自己計數，

• count的幾種用法

  • count()是一個聚合函式，對于回傳的結果集，一行行地判斷，如果count函式的引數不是NULL，累計值就加1，否則不加，最后回傳累計值，

  • 用法：count(*)、count(主鍵)、count(欄位)、count(1)

  • count(主鍵)

  InnoDB引擎會遍歷整張表，把每一行的主鍵id值都取出來，回傳給服務層，服務層拿到主鍵后，直接按行進行累加（主鍵不可能為null），

  • count(欄位)

  沒有not null約束：InnoDB引擎會遍歷整張表把每一行的欄位值都取出來，回傳給服務層，服務層判斷是否為null，不為null，計數累加，

  有not null約束：InnoDB引擎會遍歷整張表把每一行的欄位值都取出來，回傳給服務層，直接按行進行累加，

  • count(1)

  InnoDB引擎遍歷整張表，但不取值，服務層對于回傳的每一行，放一個數字“1”進去，直接按行進行累加，

  • count(*)

  InnoDB引擎并不會把全部欄位取出來，而是專門做了優化，不取值，服務層直接按行進行累加，

按照效率排序的話，count(欄位) < count(主鍵id) < count(1) ≈ count(*)，所以盡量使用count( *)，

update優化

InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖，并且該索引不能失效，否則會從行鎖升級為表鎖，

視圖/存盤程序/觸發器

視圖

• 介紹

視圖（View）是一種虛擬存在的表，視圖中的資料并不在資料庫中實際存在，行和列資料來自定義視圖的查詢中使用的表，并且是在使用視圖時動態生成的，

通俗的講，視圖只保存了查詢的SQL邏輯，不保存查詢結果，所以我們在創建視圖的時候，主要作業就落在創建這條SQL查詢陳述句上，

• 創建

CREATE [OR REPLACE] VIEW 視圖名稱[(列名串列)] AS SELECT陳述句 [WITH [CASCADED|LOCAL] CHECK OPTION];

• 查詢

#查看創建視圖陳述句
SHOW CREATE VIEW 視圖名稱;
#查看視圖資料
SELECT * FROM 視圖名稱...;

• 修改

#方式一
CREATE [OR REPLACE] VIEW 視圖名稱([列名串列]) AS SELECT陳述句 [WITH [CASCADED|LOCAL] CHECK OPTION];
#方式二
ALTER VIEW 視圖名稱[(列名串列)] AS SELECT陳述句 [WITH [CASCADED|LOCAL] CHECK OPTION];

• 洗掉

DROP VIEW [IF EXISTS] 視圖名稱 [,視圖名稱]...;

• 視圖的檢查選項

當使用WITH CHECK OPTION子句創建視圖時，MySQL會通過視圖檢查正在更改的每個行，例如插入，更新，洗掉，以使其符合視圖的定義，MySQL允許基于另一個視圖創建視圖，它還會檢查依賴視圖中的規則以保持一致性，為了確定檢查的范圍，MySQL提供了兩個選項：CASCADED和LOCAL，默認值為CASCADED，

• 視圖的更新

要使視圖可更新，視圖中的行與基礎表中的行之間必須存在一對一的關系，如果視圖包含以下任何一項，則該視圖不可更新：

1. 聚合函式或視窗函式（SUM()、MIN()、MAX()、COUNT()等）
2. DISTINCT
3. GROUP BY
4. HAVING
5. UNION或者UNION ALL

• 作用

  • 簡單

  視圖不僅可以簡化用戶對資料的理解，也可以簡化他們的操作，那些被經常使用的查詢可以被定義為視圖，從而使得用戶不必為以后的操作每次指定全部的條件，

  • 安全

  資料庫可以授權，但不能授權到資料庫特定行和特定的列上，通過視圖用戶只能查詢和修改他們所能見到的資料，

  • 資料獨立

  視圖可幫助用戶屏蔽真實表結構變化帶來的影響，

根據如下需求，定義視圖

1. 為了保證資料庫表的安全性，開發人員在操作tb_user表時，只能看到用戶的基本欄位，屏蔽手機號和郵箱兩個欄位，
2. 查詢每個學生所選修的課程（三張表聯查），這個功能在很多的業務中都有使用到，為了簡化操作，定義一個視圖，

-- 為了保證資料庫表的安全性，開發人員在操作tb_user表時，只能看到用戶的基本欄位，屏蔽手機號和郵箱兩個欄位，
create view tb_user_view as select id,name,profession,age,gender,status,createtime from tb_user;
-- 查詢每個學生所選修的課程（三張表聯查），這個功能在很多的業務中都有使用到，為了簡化操作，定義一個視圖，
create view tb_stu_course_view as select s.name student_name,s.no student_no,c.name course_name from student s,student_course sc,course c where s.id = sc.studentid and sc.courseid = c.id;

存盤程序

• 介紹

存盤程序是事先經過編譯并存盤在資料庫中的一段SQL陳述句的集合，呼叫存盤程序可以簡化應用開發人員的很多作業，減少資料在資料庫和應用服務器之間的傳輸，對于提高資料處理的效率是有好處的，

存盤程序思想上很簡單，就是資料庫SQL語言層面的代碼封裝與重用，

• 特點

封裝，復用

可以接收引數，也可以回傳資料

減少網路互動，效率提升

• 創建

CREATE PROCEDURE 存盤程序名稱([引數串列])
BEGIN
	SQL陳述句
END;

• 呼叫

CALL 名稱([引數]);

• 查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '資料庫名';-- 查詢指定資料庫的存盤程序及狀態資訊
SHOW CREATE PROCEDURE 存盤程序名稱;-- 查詢某個存盤程序的定義

• 洗掉

DROP PROCEDURE [IF EXISTS] 存盤程序名稱;

注意：在命令列中，執行創建存盤程序的SQL時，需要通過關鍵字delimiter指定SQL陳述句的結束符，

delimiter $$
create procedure p1()
begin	
	select count(*) from student;
end$$

• 變數

  • 系統變數

  系統變數是MySQL服務器提供，不是用戶定義的，屬于服務器層面，分為全域變數（GLOBAL）、會話變數（SESSION），

  查看系統變數

  SHOW [SESSION|GLOBAL] VARIABLES;			-- 查看所有系統變數
  SHOW [SESSION|GLOBAL] VARIABLES LIKE '...'; -- 可以通過LIKE模糊匹配方式查找變數
  SELECT @@[SESSION|GLOBAL].系統變數名;		 -- 查看指定變數的值
  

  設定系統變數

  SET [SESSION|GLOBAL] 系統變數名 = 值;
  SET @@[SESSION|GLOBAL].系統變數名 = 值；
  

  注意：

  如果沒有指定SESSION/GLOBAL，默認是SESSION會話變數，

  MySQL服務重新啟動之后，所設定的全域引數會失效，要想不失效，可以在/etc/my.cnf中配置，

  • 用戶定義變數

  用戶定義變數是用戶根據需要自己定義的變數，用戶變數不用提前宣告，在用的時候直接用"@變數名"就可以使用，其作用域為當前連接，

  賦值

  SET @var_name = expr [,@var_name = expr]...;
  SET @var_name := expr [,@var_name := expr]...;
  SELECT @var_name := expr [,@var_name := expr]...;
  SELECT 欄位名 INTO @var_name FROM 表名;
  

  使用

  SELECT @var_name;
  

  注意：用戶定義的變數無需對其進行宣告或初始化，只不過獲取到的值為NULL，

  • 區域變數

  區域變數是根據需要定義的在區域生效的變數，訪問之前，需要DECLARE宣告，可用作存盤程序內的區域變數和輸入引數，區域變數的范圍是在其內宣告的BEGIN...END塊，

  宣告

  DECLARE 變數名 變數型別 [DEFAULT ...];
  

  變數型別就是資料庫欄位型別：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等，

  賦值

  SET 變數名 = 值;
  SET 變數名 := 值;
  SELECT 欄位名 INTO 變數名 FROM 表名 ...;
  

• if

語法：

IF 條件1 THEN
	...
ELSEIF 條件2 THEN	-- 可選
	...
ELSE			 -- 可選
	...
END IF;

定義存盤程序，完成如下需求

根據定義的分數score變數，判定當前分數對應的分數等級，

1. score >= 85分，等級為優秀，
2. score >= 60分且score < 85分，等級為及格，
3. score < 60分，等級為不及格，

create procedure p()
begin
	declare score int default 60;
	declare result varchar(10);
	if score >= 85 then
		set result := '優秀';
	elseif score >= 60 then
		set result := '及格';
	else
		set result := '不及格';
	end if;
	select result;
end;

• 引數

型別	含義	備注
IN	該類引數作為輸入，也就是需要呼叫時傳入值	默認
OUT	該類引數作為輸出，也就是該引數可以作為回傳值
INOUT	既可以作為輸入引數，也可以作為輸出引數

用法：

CREATE PROCEDURE 存盤程序名稱([IN/OUT/INOUT 引數名 引數型別])
BEGIN
	-- SQL陳述句
END;

根據存盤程序，完成如下需求

1. 根據傳入引數score，判定當前分數對應的分數等級，并回傳，

① score >= 85分，等級為優秀，

② score >= 60分且score < 85分，等級為及格，

③ score < 60分，等級為不及格，

create procedure p(in score int, out result varchar(10))
begin
	if score >= 85 then
		set result := '優秀';
	elseif score >= 60 then
		set result := '及格';
	else
		set result := '不及格';
	end if;
end;

2. 將傳入的200分制的分數，進行換算，換算成百分制，然后回傳，

create procedure p(inout score double)
begin
	set score := score * 0.5;
end;

• case

語法一：

CASE case_value
	WHEN when_value1 THEN statement_list1
	[WHEN when_value2 THEN statement_list2]...
	[ELSE statement_list]
END CASE;

語法二：

CASE
	WHEN search_condition1 THEN statement_list1
	[WHEN search_condition2 THEN statement_list2]...
	[ELSE statement_list]
END CASE;

定義存盤程序，完成如下需求

根據傳入的月份，判定月份所屬的季節（要求采用case結構），

1. 1-3月份，為第一季度
2. 4-6月份，為第二季度
3. 7-9月份，為第三季度
4. 10-12月份，為第四季度

create procedure p(in month int)
begin
	declare result varchar(10);
	case
		when month between 1 and 3 then 
			set result := '第一季度';
		when month between 4 and 6 then 
			set result := '第二季度';
		when month between 7 and 9 then 
			set result := '第三季度';
		when month between 10 and 12 then 
			set result := '第四季度';
		else 
			set result := '非法引數';
	end case;
	select concat('您輸入的月份為：',month,'，所屬的季度為：',result);
end;

• while

while回圈是有條件的回圈控制陳述句，滿足條件后，再執行回圈體中的SQL陳述句，具體語法為：

#先判定條件，如果條件為true，則執行邏輯，否則，不執行邏輯
WHILE 條件 DO
	-- SQL陳述句
END WHILE;

定義存盤程序，完成如下需求

計算從1累加到n的值，n為傳入的引數值，

create procedure p(in n int)
begin
	declare total int default 0;
	while n > 0 do
		set total := total + n;
		set n := n - 1;
	end while;
	select total;
end;

• repeat

repeat是有條件的回圈控制陳述句，當滿足條件的時候退出回圈，具體語法為：

#先執行一次邏輯，然后判定條件是否滿足，如果滿足，則退出，否則繼續下一次回圈
REPEAT
	-- SQL陳述句
	UNTIL 條件
END REPEAT;

定義存盤程序，完成如下需求

計算從1累加到n的值，n為傳入的引數值，

create procedure p(in n int)
begin
	declare total int default 0;
	repeat
		set total := total + n;
		set n := n - 1;
	until n <= 0
	end repeat;
	select total;
end;

• loop

LOOP實作簡單的回圈，如果不在SQL陳述句中增加退出回圈的條件，可以用其來實作簡單的死回圈，LOOP可以配合以下兩個陳述句使用：

1. LEAVE：配合回圈使用，退出回圈，
2. ITERATE：必須用在回圈中，作用是跳過當前回圈剩下的陳述句，直接進入下一次回圈，

[begin_label:] LOOP
	-- SQL陳述句
END LOOP [end_label];

LEAVE label;  -- 退出指定標記的回圈體
ITERATE label;-- 直接進入下一次回圈

定義存盤程序，完成如下需求

1. 計算從1累加到n的值，n為傳入的引數值，

create procedure p(in n int)
begin
	declare total int default 0;
	sum:loop
		if n <= 0 then
			leave sum;
		end if;
		set total := total + n;
		set n := n - 1;
	end loop sum;
	select total;
end;

2. 計算從1到n之間的偶數累加的值，n為傳入的引數值，

create procedure p(in n int)
begin
	declare total int default 0;
	sum:loop
		if n <= 0 then
			leave sum;
		end if;
		if n % 2 = 1 then
			set n := n - 1;
			iterate sum;
		end if;
		set total := total + n;
		set n := n - 1;
	end loop sum;
	select total;
end;

• 游標

游標（CURSOR）是用來存盤查詢結果集的資料型別，在存盤程序和函式中可以使用游標對結果集進行回圈的處理，游標的使用包括游標的宣告、OPEN、FETCH和CLOSE，

宣告游標：

DECLARE 游標名稱 CURSOR FOR 查詢陳述句;

打開游標：

OPEN 游標名稱;

獲取游標記錄：

FETCH 游標名稱 INTO 變數[,變數];

關閉游標：

CLOSE 游標名稱;

• 條件處理程式

條件處理程式（Handler）可以用來定義在流程控制結構執行程序中遇到問題時相應的處理步驟，具體語法為：

DECLARE handler_action HANDLER FOR condition_value [,condition_value]... statement;
handler_action
	CONTINUE：繼續執行當前程式
	EXIT：終止執行當前程式
condition_value
	SQLSTATE sqlstate_value：狀態碼，如02000
	SQLWARNING：所有以01開頭的SQLSTATE代碼的簡寫
	NOT FOUND：所有以02開頭的SQLSTATE代碼的簡寫
	SQLEXCEPTION：所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE代碼的簡寫

定義存盤程序，完成如下需求

根據傳入的引數uage，來查詢用戶表tb_user中所有的用戶年齡小于等于uage的用戶姓名（name）和專業（profession），并將用戶的姓名和專業插入到所創建的一張新表（id,name,profession）中，

create procedure p(in uage int)
begin
	declare uname varchar(100);
	declare uprofession varchar(100);
	declare u_cursor cursor for select name,profession from tb_user where age <= uage;
	declare exit handler for SQLSTATE '02000' close u_cursor;
	drop table if exists tb_user_pro;
	create table tb_user_pro(
    	id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );
    open u_cursor;
    while true do
    	fetch u_cursor into uname,uprofession;
    	insert into tb_user_pro values(null,uname,uprofession);
    end while;
    end u_cursor;
end;

存盤函式

存盤函式是有回傳值的存盤程序，存盤函式的引數只能是IN型別的，具體語法如下：

CREATE FUNCTION 存盤函式名稱([引數串列])
RETURNS type [characteristic ...]
BEGIN
	-- SQL陳述句
	RETURN ...;
END;
characteristic說明：
DETERMINISTIC：相同的輸入引數總是產生相同的結果
NO SQL：不包含SQL陳述句
READS SQL DATA：包含讀取資料的陳述句，但不包含寫入資料的陳述句

定義存盤程序，完成如下需求

計算從1累加到n的值，n為傳入的引數值，

create function fun(n int)
returns int deterministic
begin
	declare total int default 0;
	while n > 0 do
		set total := total + n;
		set n := n - 1;
	end while;
	return total;
end;

觸發器

• 介紹

觸發器是與表有關的資料庫物件，指在insert/update/delete之前或之后，觸發并執行觸發器中定義的SQL陳述句集合，觸發器的這種特性可以協助應用在資料庫端確保資料的完整性，日志記錄，資料校驗等操作，

使用別名OLD和NEW來參考觸發器中發生變化的記錄內容，這與其他的資料庫是相似的，現在觸發器還只支持行級觸發，不支持陳述句級觸發，

觸發器型別	NEW和OLD
INSERT型觸發器	NEW表示將要或者已經新增的資料
UPDATE型觸發器	OLD表示修改之前的資料，NEW表示將要或者已經修改后的資料
DELETE型觸發器	OLD表示將要或者已經洗掉的資料

• 語法

創建

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行級觸發器
BEGIN
	trigger_stmt;
END;

查看

SHOW TRIGGERS;

洗掉

DROP TRIGGER [schema_name.]trigger_name;-- 如果沒有指定schema_name，默認為當前資料庫

定義觸發器，完成以下需求

通過觸發器記錄tb_user表的資料變更日志，將變更日志插入到日志表user_logs中，包含增加，修改，洗掉，

create table user_logs(
	id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作型別，insert/update/delete',
    operate_time datetime not null comment '操作時間',
    operate_id int(11) not null comment '操作ID',
    operate_params varchar(500) comment '操作引數',
    primary key('id')
)engine=innodb default charset=utf8;
-- 插入資料觸發器
create trigger tb_user_insert_trigger
	after insert on tb_user for each row
begin
	insert into user_logs(id,operation,operate_time,operate_id,operate_params) values
	(null, 'insert', now(), new.id, concat('插入的資料內容為：id=',new.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',profession=',new.profession));
end;
-- 修改資料觸發器
create trigger tb_user_update_trigger
	after update on tb_user for each row
begin
	insert into user_logs(id,operation,operate_time,operate_id,operate_params) values
	(null, 'update', now(), new.id, concat('更新之前的資料為：id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession,
'|更新之后的資料為：id=',new.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',profession=',new.profession));
end;
-- 洗掉資料觸發器
create trigger tb_user_delete_trigger
	after delete on tb_user for each row
begin
	insert into user_logs(id,operation,operate_time,operate_id,operate_params) values
	(null, 'delete', now(), old.id, concat('洗掉之前的資料為：id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession));
end;

鎖

概述

• 介紹

鎖是計算機協調多個行程或執行緒并發訪問某一資源的機制，在資料庫中，除傳統的計算資源（CPU、RAM、I/O）的爭用以外，資料也是一種供許多用戶共享的資源，如何保證資料并發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖沖突也是影響資料庫并發訪問性能的一個重要因素，從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加復雜，

• 分類

MySQL中的鎖，按照鎖的粒度分，分為以下三類：

1. 全域鎖：鎖定資料庫中的所有表，
2. 表級鎖：每次操作鎖住整張表，
3. 行級鎖：每次操作鎖住對應的行資料，

全域鎖

• 介紹

全域鎖就是對整個資料庫實體加鎖，加鎖后整個實體就處于只讀狀態，后續的DML的寫陳述句，DDL陳述句，已經更新操作的事務提交陳述句都將被阻塞，

其典型的使用場景是做全庫的邏輯備份，對所有的表進行鎖定，從而獲取一致性視圖，保證資料的完整性，

加全域鎖

FLUSH TABLES WITH READ LOCK;

將資料庫做邏輯備份

mysqldump -h 主機地址 -u用戶名 -p密碼 資料庫名 > 備份檔案名.sql

解鎖

UNLOCK TABLES;

• 特點

資料庫這個加全域鎖，是一個比較重的操作，存在以下問題：

1. 如果在主庫上備份，那么在備份期間都不能執行更新，業務基本上就得停擺，
2. 如果在從庫上備份，那么在備份期間從庫不能執行主庫同步過來的二進制日志（binglog），會導致主從延遲，

在InnoDB引擎中，我們可以在備份時加上引數--single-transaction來完成不加鎖的一致性資料備份，

mysqldump --single-transaction -h 主機地址 -u用戶名 -p密碼 資料庫名 > 備份檔案名.sql

表級鎖

• 介紹

表級鎖，每次操作鎖住整張表，鎖定粒度越大，發生鎖沖突的概率越高，并發度越低，應用在MyISAM、InnoDB、BDB等存盤引擎中，

對于表級鎖，主要分為以下三類：

1. 表鎖
2. 元資料鎖（meta data lock, MDL）
3. 意向鎖

• 表鎖

對于表鎖，分為兩類：

1. 表共享讀鎖（read lock）
2. 表獨占寫鎖（write lock）

語法：

加鎖

LOCK TABLES 表名... READ/WRITE;

釋放鎖

UNLOCK TABLES;/客戶端斷開連接

讀鎖不會阻塞其他客戶端的讀，但是會阻塞寫；寫鎖既會阻塞其他客戶端的讀，又會阻塞其他客戶端的寫，

• 元資料鎖（meta data lock, MDL）

MDL加鎖程序是系統自動控制，無需顯式使用，在訪問一張表的時候會自動加上，MDL鎖主要作用是維護表元資料的資料一致性，在表上有活動事務的時候，不可以對元資料進行寫入操作，

在MySQL5.5中引入了MDL，當對一張表進行增刪改查的時候，加MDL讀鎖（共享）；當對表結構進行變更操作的時候，加MDL寫鎖（排他），

對應SQL	鎖型別	說明
lock tables xxx read/write	SHARED_READ_ONLY/SHARED_NO_READ_WRITE
select、select ... lock in share mode	SHARED_READ	與SHARED_READ、SHARED_WRITE兼容，與EXCLUSIVE互斥
insert、update、delete、select ... for update	SHARED_WRITE	與SHARED_READ、SHARED_WRITE兼容，與EXCLUSIVE互斥
alter table ...	EXCLUSIVE	與其他的MDL都互斥

查看元資料鎖：

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

• 意向鎖

為了避免DML在執行時，加的行鎖與表鎖的沖突，在InnoDB中引入了意向鎖，使得表鎖不用檢查每行資料是否加鎖，使用意向鎖來減少表鎖的檢查，

1. 意向共享鎖（IS）：

   由陳述句select ... lock in share mode添加，

   與表鎖共享鎖（read）兼容，與表鎖排他鎖（write）互斥，

2. 意向排他鎖（IX）：

   由insert、update、delete、select ... for update添加，

   與表鎖共享鎖（read）及排他鎖（write）都互斥，意向鎖之間不會互斥，

可以通過以下SQL，查看意向鎖及行鎖的加鎖情況：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行級鎖

• 介紹

行級鎖，每次操作鎖住對應的行資料，鎖定粒度最小，發生鎖沖突的概率最低，并發度最高，應用在InnoDB存盤引擎中，

InnoDB的資料是基于索引組織的，行級鎖是通過對索引上的索引項加鎖來實作的，而不是對記錄加的鎖，

對于行級鎖，主要分為以下三類：

1. 行鎖（Record Lock）：鎖定單個行記錄的鎖，防止其他事務對此行進行update和delete，在RC、RR隔離級別下都支持，
2. 間隙鎖（Gap Lock）：鎖定索引記錄間隙（不含該記錄），確保索引記錄間隙不變，防止其他事務在這個間隙進行insert，產生幻讀，在RR隔離級別下支持，
3. 臨鍵鎖（Next-Key Lock）：行鎖和間隙鎖組合，同時 鎖住資料，并鎖住資料前面的間隙Gap，在RR隔離級別下支持，

• 行鎖

InnoDB實作了以下兩種型別的行鎖：

1. 共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同資料集的排他鎖，
2. 排他鎖（X）：允許獲取排他鎖的事務更新資料，阻止其他事務獲得相同資料集的共享鎖和排他鎖，

SQL	行鎖型別	說明
INSERT ...	排他鎖	自動加鎖
UPDATE ...	排他鎖	自動加鎖
DELETE ...	排他鎖	自動加鎖
SELECT (正常)	不加任何鎖
SELECT ... LOCK IN SHARE MODE	共享鎖	需要手動在SELECT之后加LOCK IN SHARE MODE
SELECT ... FOR UPDATE	排他鎖	需要手動在SELECT之后加FOR UPDATE

默認情況下，InnoDB在REPEATABLE READ事務隔離級別運行，InnoDB使用next-key鎖進行搜索和索引掃描，以防止幻讀，

1. 針對唯一索引進行檢索時，對已存在的記錄進行等值匹配時，將會自動優化為行鎖，
2. InnoDB的行鎖是針對于索引加的鎖，不通過索引條件檢索資料，那么InnoDB將對表中的所有記錄加鎖，此時就會升級為表鎖，

可以通過以下SQL，查看意向鎖及行鎖的加鎖情況：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

• 間隙鎖/臨鍵鎖

默認情況下，InnoDB在REPEATABLE READ事務隔離級別運行，InnoDB使用next-key鎖進行搜索和索引掃描，以防止幻讀，

1. 索引上的等值查詢（唯一索引），給不存在的記錄加鎖時，優化為間隙鎖，
2. 索引上的等值查詢（普通索引），向右遍歷時最后一個值不滿足查詢需求時，next-key lock退化為間隙鎖，
3. 索引上的范圍查詢（唯一索引），會訪問到不滿足條件的第一個值為止，

注意：間隙鎖唯一目的是防止其他事務插入間隙，間隙鎖可以共存，一個事務采用的間隙鎖不會阻止另一個事務在同一間隙上采用間隙鎖，

InnoDB引擎

邏輯存盤結構

表空間（ibd檔案），一個mysql實體可以對應多個表空間，用于存盤記錄、索引等資料，

架構

事務原理

MVCC

MySQL管理

MySQL運維篇

日志

主從復制

分庫分表

讀寫分離

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/527911.html

標籤：其他

上一篇：云資料庫 GaussDB(for Influx) 解密第十一期：讓智能電網中時序資料處理更高效

下一篇：5分鐘搞定 PostgreSQL 到 Doris 資料遷移和同步