基本使用MySQL

通用語法及分類

• DDL: 數(shù)據(jù)定義語言，用來定義數(shù)據(jù)庫對象（數(shù)據(jù)庫、表、字段）
• DML: 數(shù)據(jù)操作語言，用來對數(shù)據(jù)庫表中的數(shù)據(jù)進(jìn)行增刪改
• DQL: 數(shù)據(jù)查詢語言，用來查詢數(shù)據(jù)庫中表的記錄
• DCL: 數(shù)據(jù)控制語言，用來創(chuàng)建數(shù)據(jù)庫用戶、控制數(shù)據(jù)庫的控制權(quán)限

DDL（數(shù)據(jù)定義語言）

數(shù)據(jù)定義語言

數(shù)據(jù)庫操作

查詢所有數(shù)據(jù)庫:
SHOW DATABASES;
查詢當(dāng)前數(shù)據(jù)庫：
SELECT DATABASE();
創(chuàng)建數(shù)據(jù)庫：
CREATE DATABASE [ IF NOT EXISTS ] 數(shù)據(jù)庫名 [ DEFAULT CHARSET 字符集] [COLLATE 排序規(guī)則 ];
刪除數(shù)據(jù)庫:
DROP DATABASE [ IF EXISTS ] 數(shù)據(jù)庫名;
使用數(shù)據(jù)庫：
USE 數(shù)據(jù)庫名;

注意事項

• MySQL的UTF8字符集長度為3字節(jié)，有些符號占4字節(jié)（如Emoji表情），所以推薦用utf8mb4、utf8mb4_unicode_ci字符集

表操作

查詢當(dāng)前數(shù)據(jù)庫所有表：
SHOW TABLES;
查詢表結(jié)構(gòu)：
DESC 表名;
查詢指定表的建表語句：
SHOW CREATE TABLE 表名;

創(chuàng)建表：

CREATE TABLE 表名(
	字段1 字段1類型 [COMMENT 字段1注釋],
	字段2 字段2類型 [COMMENT 字段2注釋],
	字段3 字段3類型 [COMMENT 字段3注釋],
	...
	字段n 字段n類型 [COMMENT 字段n注釋]
)[ COMMENT 表注釋 ];

所有的要用英文的格式
最后一個字段后面沒有逗號

添加字段：
ALTER TABLE 表名 ADD 字段名 類型(長度) [COMMENT 注釋] [約束];
例：ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵稱';

修改數(shù)據(jù)類型：
ALTER TABLE 表名 MODIFY 字段名 新數(shù)據(jù)類型(長度);

修改字段名和字段類型：
ALTER TABLE 表名 CHANGE 舊字段名 新字段名 類型(長度) [COMMENT 注釋] [約束];

例：將emp表的nickname字段修改為username，類型為varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵稱';

刪除字段：
ALTER TABLE 表名 DROP 字段名;

修改表名：
ALTER TABLE 表名 RENAME TO 新表名

刪除表：
DROP TABLE [IF EXISTS] 表名;

刪除表，并重新創(chuàng)建該表：
TRUNCATE TABLE 表名;

DML（數(shù)據(jù)操作語言）

添加數(shù)據(jù)

指定字段：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
全部字段：
INSERT INTO 表名 VALUES (值1, 值2, ...);

批量添加數(shù)據(jù)：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

注意事項

• 字符串和日期類型數(shù)據(jù)應(yīng)該包含在引號中
• 插入的數(shù)據(jù)大小應(yīng)該在字段的規(guī)定范圍內(nèi)

更新和刪除數(shù)據(jù)

修改數(shù)據(jù)：
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 條件 ];
例：
UPDATE emp SET name = 'Jack' WHERE id = 1;

刪除數(shù)據(jù)：
DELETE FROM 表名 [ WHERE 條件 ];

DQL（數(shù)據(jù)查詢語言）

語法：

SELECT
	字段列表
FROM
	表名字段
WHERE
	條件列表
GROUP BY
	分組字段列表
HAVING
	分組后的條件列表
ORDER BY
	排序字段列表
LIMIT
	分頁參數(shù)

基礎(chǔ)查詢

查詢多個字段：
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名;

設(shè)置別名：
SELECT 字段1 [ AS 別名1 ], 字段2 [ AS 別名2 ], 字段3 [ AS 別名3 ], ... FROM 表名;
SELECT 字段1 [ 別名1 ], 字段2 [ 別名2 ], 字段3 [ 別名3 ], ... FROM 表名;

去除重復(fù)記錄：
SELECT DISTINCT 字段列表 FROM 表名;

轉(zhuǎn)義：
SELECT * FROM 表名 WHERE name LIKE '/_張三' ESCAPE '/'
/ 之后的_不作為通配符

條件查詢

語法：
SELECT 字段列表 FROM 表名 WHERE 條件列表;

條件：

例子：

-- 年齡等于30
select * from employee where age = 30;
-- 年齡小于30
select * from employee where age < 30;
-- 小于等于
select * from employee where age <= 30;
-- 沒有身份證
select * from employee where idcard is null or idcard = '';
-- 有身份證
select * from employee where idcard;
select * from employee where idcard is not null;
-- 不等于
select * from employee where age != 30;
-- 年齡在20到30之間
select * from employee where age between 20 and 30;
select * from employee where age >= 20 and age <= 30;
-- 下面語句不報錯，但查不到任何信息
select * from employee where age between 30 and 20;
-- 性別為女且年齡小于30
select * from employee where age < 30 and gender = '女';
-- 年齡等于25或30或35
select * from employee where age = 25 or age = 30 or age = 35;
select * from employee where age in (25, 30, 35);
-- 姓名為兩個字
select * from employee where name like '__';
-- 身份證最后為X
select * from employee where idcard like '%X';

聚合查詢（聚合函數(shù)）

常見聚合函數(shù)：

語法：
SELECT 聚合函數(shù)(字段列表) FROM 表名;
例：
SELECT count(id) from employee where workaddress = "廣東省";

分組查詢

語法：
SELECT 字段列表 FROM 表名 [ WHERE 條件 ] GROUP BY 分組字段名 [ HAVING 分組后的過濾條件 ];

where 和 having 的區(qū)別：

• 執(zhí)行時機(jī)不同：where是分組之前進(jìn)行過濾，不滿足where條件不參與分組；having是分組后對結(jié)果進(jìn)行過濾。
• 判斷條件不同：where不能對聚合函數(shù)進(jìn)行判斷，而having可以。

例子：

-- 根據(jù)性別分組，統(tǒng)計男性和女性數(shù)量（只顯示分組數(shù)量，不顯示哪個是男哪個是女）
select count(*) from employee group by gender;
-- 根據(jù)性別分組，統(tǒng)計男性和女性數(shù)量
select gender, count(*) from employee group by gender;
-- 根據(jù)性別分組，統(tǒng)計男性和女性的平均年齡
select gender, avg(age) from employee group by gender;
-- 年齡小于45，并根據(jù)工作地址分組
select workaddress, count(*) from employee where age < 45 group by workaddress;
-- 年齡小于45，并根據(jù)工作地址分組，獲取員工數(shù)量大于等于3的工作地址
select workaddress, count(*) address_count from employee where age < 45 group by workaddress having address_count >= 3;

注意事項

• 執(zhí)行順序：where > 聚合函數(shù) > having
• 分組之后，查詢的字段一般為聚合函數(shù)和分組字段，查詢其他字段無任何意義

排序查詢

語法：
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

排序方式：

• ASC: 升序（默認(rèn)）
• DESC: 降序

例子：

-- 根據(jù)年齡升序排序
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age;
-- 兩字段排序，根據(jù)年齡升序排序，入職時間降序排序(如果年齡相同那么就按這個)
SELECT * FROM employee ORDER BY age ASC, entrydate DESC;

注意事項

如果是多字段排序，當(dāng)?shù)谝粋€字段值相同時，才會根據(jù)第二個字段進(jìn)行排序

分頁查詢

語法：
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查詢記錄數(shù);

例子：

-- 查詢第一頁數(shù)據(jù)，展示10條
SELECT * FROM employee LIMIT 0, 10;
-- 查詢第二頁
SELECT * FROM employee LIMIT 10, 10;

注意事項

• 起始索引從0開始，起始索引 = （查詢頁碼 - 1） * 每頁顯示記錄數(shù)
• 分頁查詢是數(shù)據(jù)庫的方言，不同數(shù)據(jù)庫有不同實現(xiàn)，MySQL是LIMIT
• 如果查詢的是第一頁數(shù)據(jù)，起始索引可以省略，直接簡寫 LIMIT 10

DQL執(zhí)行順序

FROM -> join -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

DCL

管理用戶

查詢用戶：

USER mysql;
SELECT * FROM user;

創(chuàng)建用戶:
CREATE USER '用戶名'@'主機(jī)名' IDENTIFIED BY '密碼';

修改用戶密碼：
ALTER USER '用戶名'@'主機(jī)名' IDENTIFIED WITH mysql_native_password BY '新密碼';

刪除用戶：
DROP USER '用戶名'@'主機(jī)名';

例子：

-- 創(chuàng)建用戶test，只能在當(dāng)前主機(jī)localhost訪問
create user 'test'@'localhost' identified by '123456';
-- 創(chuàng)建用戶test，能在任意主機(jī)訪問
create user 'test'@'%' identified by '123456';
create user 'test' identified by '123456';
-- 修改密碼
alter user 'test'@'localhost' identified with mysql_native_password by '1234';
-- 刪除用戶
drop user 'test'@'localhost';

注意事項

• 主機(jī)名可以使用 % 通配

權(quán)限控制

常用權(quán)限：

更多權(quán)限請看權(quán)限一覽表

查詢權(quán)限：
SHOW GRANTS FOR '用戶名'@'主機(jī)名';

授予權(quán)限：
GRANT 權(quán)限列表 ON 數(shù)據(jù)庫名.表名 TO '用戶名'@'主機(jī)名';

撤銷權(quán)限：
REVOKE 權(quán)限列表 ON 數(shù)據(jù)庫名.表名 FROM '用戶名'@'主機(jī)名';

注意事項

• 多個權(quán)限用逗號分隔
• 授權(quán)時，數(shù)據(jù)庫名和表名可以用 * 進(jìn)行通配，代表所有

函數(shù)

函數(shù) 是指一段可以直接被另外一段程序調(diào)用的程序或代碼。

• 字符串函數(shù)
• 數(shù)值函數(shù)
• 日期函數(shù)
• 流程函數(shù)

字符串函數(shù)

常用函數(shù)：

使用示例：

-- 拼接
SELECT CONCAT('Hello', 'World');
-- 小寫
SELECT LOWER('Hello');
-- 大寫
SELECT UPPER('Hello');
-- 左填充
SELECT LPAD('01', 5, '-');
-- 右填充
SELECT RPAD('01', 5, '-');
-- 去除空格
SELECT TRIM(' Hello World ');
-- 切片（起始索引為1）
SELECT SUBSTRING('Hello World', 1, 5);

數(shù)值函數(shù)

常見函數(shù)：

日期函數(shù)

常用函數(shù)：

例子：

-- DATE_ADD
SELECT DATE_ADD(NOW(), INTERVAL 70 YEAR);

流程函數(shù)

常用函數(shù)：

例子：

select
	name,
	(case when age > 30 then '中年' else '青年' end)
from employee;
select
	name,
	(case workaddress when '北京市' then '一線城市' when '上海市' then '一線城市' else '二線城市' end) as '工作地址'
from employee;

約束

1. 概念：約束是用來作用于表中字段上的規(guī)則，用于限制存儲在表中的數(shù)據(jù)。
2. 目的：保證數(shù)據(jù)庫中的數(shù)據(jù)的正確、有效性和完整性

分類：

約束是作用于表中字段上的，可以再創(chuàng)建表/修改表的時候添加約束。

常用約束

例子：

create table user(
	id int primary key auto_increment,
	name varchar(10) not null unique,
	age int check(age > 0 and age < 120),
	status char(1) default '1',
	gender char(1)
);

外鍵約束

外鍵用來讓兩張表的數(shù)據(jù)之間建立連接，從而保證數(shù)據(jù)的一致性和完整性。

添加外鍵：

CREATE TABLE 表名(
	字段名 字段類型,
	...
	[CONSTRAINT] [外鍵名稱] FOREIGN KEY(外鍵字段名) REFERENCES 主表(主表列名)
);  
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名) REFERENCES 主表(主表列名);

-- 例子  
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);  

刪除外鍵：
ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名;

刪除/更新行為

更改刪除/更新行為：
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段) REFERENCES 主表名(主表字段名) ON UPDATE 行為 ON DELETE 行為;

多表查詢

多表關(guān)系

• 一對多（多對一）
• 多對多
• 一對一

一對多

案例：部門與員工
關(guān)系：一個部門對應(yīng)多個員工，一個員工對應(yīng)一個部門
實現(xiàn)：在多的一方建立外鍵，指向一的一方的主鍵

多對多

案例：學(xué)生與課程
關(guān)系：一個學(xué)生可以選多門課程，一門課程也可以供多個學(xué)生選修
實現(xiàn)：建立第三張中間表，中間表至少包含兩個外鍵，分別關(guān)聯(lián)兩方主鍵

一對一

案例：用戶與用戶詳情
關(guān)系：一對一關(guān)系，多用于單表拆分，將一張表的基礎(chǔ)字段放在一張表中，其他詳情字段放在另一張表中，以提升操作效率
實現(xiàn)：在任意一方加入外鍵，關(guān)聯(lián)另外一方的主鍵，并且設(shè)置外鍵為唯一的（UNIQUE）

連接查詢

合并查詢（笛卡爾積，會展示所有組合結(jié)果）：
select * from employee, dept;

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

消除無效笛卡爾積：
select * from employee, dept where employee.dept = dept.id;

內(nèi)連接查詢

內(nèi)連接查詢的是兩張表交集的部分

隱式內(nèi)連接：
SELECT 字段列表 FROM 表1, 表2 WHERE 條件 ...;

顯式內(nèi)連接：
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 連接條件 ...;

顯式性能比隱式高

inner join顯式內(nèi)連接是hashtable連接比較 ，O(Log N)

where隱式內(nèi)連接是取笛卡爾積過濾，O(N**2)

例子：

-- 查詢員工姓名，及關(guān)聯(lián)的部門的名稱
-- 隱式
select e.name, d.name from employee as e, dept as d where e.dept = d.id;
-- 顯式
select e.name, d.name from employee as e inner join dept as d on e.dept = d.id;

外連接查詢

左外連接：
查詢左表所有數(shù)據(jù)，以及兩張表交集部分?jǐn)?shù)據(jù)
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件 ...;
相當(dāng)于查詢表1的所有數(shù)據(jù)，包含表1和表2交集部分?jǐn)?shù)據(jù)

右外連接：
查詢右表所有數(shù)據(jù)，以及兩張表交集部分?jǐn)?shù)據(jù)
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 條件 ...;

例子：

-- 左
select e.*, d.name from employee as e left outer join dept as d on e.dept = d.id;  
select d.name, e.* from dept d left outer join emp e on e.dept = d.id;  -- 這條語句與下面的語句效果一樣  
-- 右
select d.name, e.* from employee as e right outer join dept as d on e.dept = d.id;  

左連接可以查詢到?jīng)]有dept的employee，右連接可以查詢到?jīng)]有employee的dept

自連接查詢

當(dāng)前表與自身的連接查詢，自連接必須使用表別名

語法：
SELECT 字段列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ...;

自連接查詢，可以是內(nèi)連接查詢，也可以是外連接查詢

例子：

-- 查詢員工及其所屬領(lǐng)導(dǎo)的名字  
select a.name, b.name from employee a, employee b where a.manager = b.id;  
-- 沒有領(lǐng)導(dǎo)的也查詢出來  
select a.name, b.name from employee a left join employee b on a.manager = b.id;  

連接查詢編寫規(guī)范

當(dāng)使用join時，又有需要限制條件如：xxx字段=1。規(guī)則：join連接表，where處理條件

例子：

①表A左連接表B ②限制表A的id為2 ③限制表B的id為3

select A.id,B.id
from A 
left join B on A.id=B.aid and B.id=3
where A.id=2

結(jié)論：左外連接查詢，左表的過濾應(yīng)該使用WHERE子句，右表的過濾應(yīng)該使用ON子句；右外連接查詢正好相反；全外連接的過濾條件使用ON子句

拓展：

若不按照以上規(guī)范編寫可能出現(xiàn)查出數(shù)據(jù)不符合條件，具體看文章：https://zhuanlan.zhihu.com/p/444551101

聯(lián)合查詢 union, union all

把多次查詢的結(jié)果合并，形成一個新的查詢集

語法：

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...

注意事項

• union去重并排序，union all直接返回合并的結(jié)果，不去重也不排序；
• union all比union性能好；
• 聯(lián)合查詢比使用or效率高，不會使索引失效

子查詢

SQL語句中嵌套SELECT語句，稱謂嵌套查詢，又稱子查詢。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);
子查詢外部的語句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一個

根據(jù)子查詢結(jié)果可以分為：

• 標(biāo)量子查詢（子查詢結(jié)果為單個值）
• 列子查詢（子查詢結(jié)果為一列）
• 行子查詢（子查詢結(jié)果為一行）
• 表子查詢（子查詢結(jié)果為多行多列）

根據(jù)子查詢位置可分為：

• WHERE 之后
• FROM 之后
• SELECT 之后

標(biāo)量子查詢

子查詢返回的結(jié)果是單個值（數(shù)字、字符串、日期等）。
常用操作符：- < > > >= < <=

例子：

-- 查詢銷售部所有員工
select id from dept where name = '銷售部';
-- 根據(jù)銷售部部門ID，查詢員工信息
select * from employee where dept = 4;
-- 合并（子查詢）  
select * from employee where dept = (select id from dept where name = '銷售部');  

-- 查詢xxx入職之后的員工信息  
select * from employee where entrydate > (select entrydate from employee where name = 'xxx');  

列子查詢

返回的結(jié)果是一列（可以是多行）。

常用操作符：

例子：

-- 查詢銷售部和市場部的所有員工信息  
select * from employee where dept in (select id from dept where name = '銷售部' or name = '市場部');
-- 查詢比財務(wù)部所有人工資都高的員工信息
select * from employee where salary > all(select salary from employee where dept = (select id from dept where name = '財務(wù)部'));
-- 查詢比研發(fā)部任意一人工資高的員工信息
select * from employee where salary > any (select salary from employee where dept = (select id from dept where name = '研發(fā)部'));

行子查詢

返回的結(jié)果是一行（可以是多列）。
常用操作符：=, <, >, IN, NOT IN

例子：

-- 查詢與xxx的薪資及直屬領(lǐng)導(dǎo)相同的員工信息  
select * from employee where (salary, manager) = (12500, 1);  
select * from employee where (salary, manager) = (select salary, manager from employee where name = 'xxx');  

表子查詢

返回的結(jié)果是多行多列
常用操作符：IN

特別注意點：SQL在使用in()的時候要注意，括號內(nèi)一定要有數(shù)據(jù)，如果沒有數(shù)據(jù)或者null則會報錯SQL語法錯誤

例子：

-- 查詢與xxx1，xxx2的職位和薪資相同的員工
select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2');
-- 查詢?nèi)肼毴掌谑?006-01-01之后的員工，及其部門信息
select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e left join dept as d on e.dept = d.id;

事務(wù)

事務(wù)是一組操作的集合，事務(wù)會把所有操作作為一個整體一起向系統(tǒng)提交或撤銷操作請求，即這些操作要么同時成功，要么同時失敗。

基本操作：

-- 1. 查詢張三賬戶余額
select * from account where name = '張三';
-- 2. 將張三賬戶余額-1000
update account set money = money - 1000 where name = '張三';
-- 此語句出錯后張三錢減少但是李四錢沒有增加
模擬sql語句錯誤
-- 3. 將李四賬戶余額+1000
update account set money = money + 1000 where name = '李四';

-- 查看事務(wù)提交方式
SELECT @@AUTOCOMMIT;
-- 設(shè)置事務(wù)提交方式，1為自動提交，0為手動提交，該設(shè)置只對當(dāng)前會話有效
SET @@AUTOCOMMIT = 0;
-- 提交事務(wù)
COMMIT;
-- 回滾事務(wù)
ROLLBACK;

-- 設(shè)置手動提交后上面代碼改為：
select * from account where name = '張三';  
update account set money = money - 1000 where name = '張三';  
update account set money = money + 1000 where name = '李四';  
commit;  

操作方式二：

開啟事務(wù)：
START TRANSACTION 或 BEGIN TRANSACTION;
提交事務(wù)：
COMMIT;
回滾事務(wù)：
ROLLBACK;

操作實例：

start transaction; 
select * from account where name = '張三';
update account set money = money - 1000 where name = '張三';
update account set money = money + 1000 where name = '李四';
commit;

開啟事務(wù)后，只有手動提交才會改變數(shù)據(jù)庫中的數(shù)據(jù)。

四大特性ACID

• 原子性(Atomicity)：事務(wù)是不可分割的最小操作但愿，要么全部成功，要么全部失敗
• 一致性(Consistency)：事務(wù)完成時，必須使所有數(shù)據(jù)都保持一致狀態(tài)
• 隔離性(Isolation)：數(shù)據(jù)庫系統(tǒng)提供的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的獨立環(huán)境下運(yùn)行
• 持久性(Durability)：事務(wù)一旦提交或回滾，它對數(shù)據(jù)庫中的數(shù)據(jù)的改變就是永久的

并發(fā)事務(wù)

這三個問題的詳細(xì)演示：https://www.bilibili.com/video/BV1Kr4y1i7ru?p=55cd

并發(fā)事務(wù)隔離級別：

• √表示在當(dāng)前隔離級別下該問題會出現(xiàn)
• Serializable 性能最低；Read uncommitted 性能最高，數(shù)據(jù)安全性最差

查看事務(wù)隔離級別：
SELECT @@TRANSACTION_ISOLATION;
設(shè)置事務(wù)隔離級別：
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };
SESSION 是會話級別，表示只針對當(dāng)前會話有效，GLOBAL 表示對所有會話有效

拓展：

1. 在SQL語句之后加上\G會將結(jié)果的表格形式轉(zhuǎn)換成行文本形式
2. 查看Mysql數(shù)據(jù)庫占用空間：

SELECT table_schema "Database Name"
     , SUM(data_length + index_length) / (1024 * 1024) "Database Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema;

深入了解MySQL

存儲引擎

MySQL體系結(jié)構(gòu)：

存儲引擎就是存儲數(shù)據(jù)、建立索引、更新/查詢數(shù)據(jù)等技術(shù)的實現(xiàn)方式。存儲引擎是基于表而不是基于庫的，所以存儲引擎也可以被稱為表引擎。
MySQL5.5+默認(rèn)存儲引擎是InnoDB。

相關(guān)操作：

-- 查詢建表語句
show create table account;  
-- 建表時指定存儲引擎
CREATE TABLE 表名(
	...
) ENGINE=INNODB;
-- 查看當(dāng)前數(shù)據(jù)庫支持的存儲引擎
show engines;

InnoDB

InnoDB 是一種兼顧高可靠性和高性能的通用存儲引擎，在 MySQL 5.5 之后，InnoDB 是默認(rèn)的 MySQL 引擎。

特點：

• DML 操作遵循 ACID 模型，支持事務(wù)
• 行級鎖，提高并發(fā)訪問性能
• 支持外鍵約束，保證數(shù)據(jù)的完整性和正確性

文件：

• xxx.ibd: xxx代表表名，InnoDB 引擎的每張表都會對應(yīng)這樣一個表空間文件，存儲該表的表結(jié)構(gòu)（frm、sdi）、數(shù)據(jù)和索引。

參數(shù)：innodb_file_per_table，決定多張表共享一個表空間還是每張表對應(yīng)一個表空間

知識點：

查看 Mysql 變量：
show variables like 'innodb_file_per_table';

從idb文件提取表結(jié)構(gòu)數(shù)據(jù)：
（在cmd運(yùn)行）
ibd2sdi xxx.ibd

InnoDB 邏輯存儲結(jié)構(gòu)：

MyISAM

MyISAM 是 MySQL 早期的默認(rèn)存儲引擎。

特點：

• 不支持事務(wù)，不支持外鍵
• 支持表鎖，不支持行鎖
• 訪問速度快

文件：

• xxx.sdi: 存儲表結(jié)構(gòu)信息
• xxx.MYD: 存儲數(shù)據(jù)
• xxx.MYI: 存儲索引

Memory

Memory 引擎的表數(shù)據(jù)是存儲在內(nèi)存中的，受硬件問題、斷電問題的影響，只能將這些表作為臨時表或緩存使用。

特點：

• 存放在內(nèi)存中，速度快
• hash索引（默認(rèn)）

文件：

• xxx.sdi: 存儲表結(jié)構(gòu)信息

存儲引擎特點

不同引擎之間主要的區(qū)別為：事務(wù)、行級鎖、外鍵

存儲引擎的選擇

在選擇存儲引擎時，應(yīng)該根據(jù)應(yīng)用系統(tǒng)的特點選擇合適的存儲引擎。對于復(fù)雜的應(yīng)用系統(tǒng)，還可以根據(jù)實際情況選擇多種存儲引擎進(jìn)行組合。

• InnoDB: 如果應(yīng)用對事物的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作除了插入和查詢之外，還包含很多的更新、刪除操作，則 InnoDB 是比較合適的選擇
• MyISAM: 如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務(wù)的完整性、并發(fā)性要求不高，那這個存儲引擎是非常合適的。
• Memory: 將所有數(shù)據(jù)保存在內(nèi)存中，訪問速度快，通常用于臨時表及緩存。Memory 的缺陷是對表的大小有限制，太大的表無法緩存在內(nèi)存中，而且無法保障數(shù)據(jù)的安全性

電商中的足跡和評論適合使用 MyISAM 引擎，緩存適合使用 Memory 引擎。

SQL性能分析

查看執(zhí)行頻次

查看當(dāng)前數(shù)據(jù)庫的 INSERT, UPDATE, DELETE, SELECT 訪問頻次：

-- session 是查看當(dāng)前會話 ;

-- global 是查詢?nèi)謹(jǐn)?shù)據(jù) ;

SHOW GLOBAL STATUS LIKE 'Com_______'; 或者 SHOW SESSION STATUS LIKE 'Com_______';
例：show global status like 'Com_______'

慢查詢?nèi)罩?/h3>

慢查詢?nèi)罩居涗浟怂袌?zhí)行時間超過指定參數(shù)（long_query_time，單位：秒，默認(rèn)10秒）的所有SQL語句的日志。

查看慢查詢?nèi)罩鹃_關(guān)狀態(tài)：（默認(rèn)是關(guān)閉的）
show variables like 'slow_query_log';

MySQL的慢查詢?nèi)罩灸J(rèn)沒有開啟，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

-- 開啟慢查詢?nèi)罩鹃_關(guān)  
slow_query_log=1  
-- 設(shè)置慢查詢?nèi)罩镜臅r間為2秒，SQL語句執(zhí)行時間超過2秒，就會視為慢查詢，記錄慢查詢?nèi)罩? 
long_query_time=2  

更改后記得重啟MySQL服務(wù)，日志文件位置：/var/lib/mysql/localhost-slow.log

profile

show profile 能在做SQL優(yōu)化時幫我們了解時間都耗費(fèi)在哪里。通過 have_profiling 參數(shù)，能看到當(dāng)前 MySQL 是否支持 profile 操作：

SELECT @@have_profiling;
profiling 默認(rèn)關(guān)閉，可以通過set語句在session/global級別開啟 profiling：
SET profiling = 1;
查看所有語句的耗時：
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 如何執(zhí)行 SELECT 語句的信息，包括在 SELECT 語句執(zhí)行過程中表如何連接和連接的順序。
語法：
直接在select語句之前加上關(guān)鍵字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 HWERE 條件;

EXPLAIN 各字段含義：

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

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

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

  聚集索引查詢是const，二級索引查詢是ref，不用索引查詢是all（全表掃描）

• possible_key：可能應(yīng)用在這張表上的索引，一個或多個

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

• Key_len：表示索引中使用的字節(jié)數(shù)，該值為索引字段最大可能長度，并非實際使用長度，在不損失精確性的前提下，長度越短越好

• rows：MySQL認(rèn)為必須要執(zhí)行的行數(shù)，在InnoDB引擎的表中，是一個估計值，可能并不總是準(zhǔn)確的

• filtered：表示返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比，filtered的值越大越好

索引

索引是幫助 MySQL 高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（有序）。在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查詢算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。

優(yōu)點：

• 提高數(shù)據(jù)檢索效率，降低數(shù)據(jù)庫的IO成本
• 通過索引列對數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低CPU的消耗

缺點：

• 索引列也是要占用空間的
• 索引大大提高了查詢效率，但降低了更新的速度，比如 INSERT、UPDATE、DELETE

索引結(jié)構(gòu)

B-Tree

二叉樹形成鏈表的缺點可以用紅黑樹來解決：

紅黑樹也存在大數(shù)據(jù)量情況下，層級較深，檢索速度慢的問題。

為了解決上述問題，可以使用 B-Tree 結(jié)構(gòu)。
B-Tree (多路平衡查找樹) 以一棵最大度數(shù)（max-degree，指一個節(jié)點的子節(jié)點個數(shù)）為5（5階）的 b-tree 為例（每個節(jié)點最多存儲4個key，5個指針）

B-Tree 的數(shù)據(jù)插入過程動畫參照：https://www.bilibili.com/video/BV1Kr4y1i7ru?p=68
演示地址：https://www.cs.usfca.edu/~galles/visualization/BTree.html

B+Tree

結(jié)構(gòu)圖：

注意：頁與頁之間是雙向鏈表，頁內(nèi)是單項鏈表。

以下圖為例：6和12之間應(yīng)是單向鏈表；6 12 和16 18 之間應(yīng)是雙向鏈表

演示地址：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

與 B-Tree 的區(qū)別：

• 所有的數(shù)據(jù)都會出現(xiàn)在葉子節(jié)點
• 葉子節(jié)點形成一個單向鏈表

MySQL 索引數(shù)據(jù)結(jié)構(gòu)對經(jīng)典的 B+Tree 進(jìn)行了優(yōu)化。在原 B+Tree 的基礎(chǔ)上，增加一個指向相鄰葉子節(jié)點的鏈表指針，就形成了帶有順序指針的 B+Tree，提高區(qū)間訪問的性能。

注意：頁與頁之間是雙向鏈表，頁內(nèi)是單項鏈表。

以下圖為例：6和12之間應(yīng)是單向鏈表；6 12 和16 18 之間應(yīng)是雙向鏈表

Hash

哈希索引就是采用一定的hash算法，將鍵值換算成新的hash值，映射到對應(yīng)的槽位上，然后存儲在hash表中。
如果兩個（或多個）鍵值，映射到一個相同的槽位上，他們就產(chǎn)生了hash沖突（也稱為hash碰撞），可以通過鏈表來解決。

特點：

• Hash索引只能用于對等比較（=、in），不支持范圍查詢（betwwn、>、<、...）
• 無法利用索引完成排序操作
• 查詢效率高，通常只需要一次檢索就可以了，效率通常要高于 B+Tree 索引

存儲引擎支持：

• Memory
• InnoDB: 具有自適應(yīng)hash功能，hash索引是存儲引擎根據(jù) B+Tree 索引在指定條件下自動構(gòu)建的

面試題

1. 為什么 InnoDB 存儲引擎選擇使用 B+Tree 索引結(jié)構(gòu)？

• 相對于二叉樹，層級更少，搜索效率高
• 對于 B-Tree，無論是葉子節(jié)點還是非葉子節(jié)點，都會保存數(shù)據(jù)，這樣導(dǎo)致一頁中存儲的鍵值減少，指針也跟著減少，要同樣保存大量數(shù)據(jù)，只能增加樹的高度，導(dǎo)致性能降低
• 相對于 Hash 索引，B+Tree 支持范圍匹配及排序操作

索引分類

在 InnoDB 存儲引擎中，根據(jù)索引的存儲形式，又可以分為以下兩種：（也可以稱為是聚簇索引和非聚簇索引）

聚集索引選取規(guī)則：

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

演示圖：

聚集索引是存放主鍵為key，葉子節(jié)點數(shù)據(jù)為主鍵那行的所有數(shù)據(jù)

二級索引（輔助索引）是存放當(dāng)前索引字段的值，葉子節(jié)點數(shù)據(jù)為索引字段當(dāng)行的id值（使用二級索引【回表查詢】：會根據(jù)字段值找到當(dāng)行id，再使用id去查聚集索引，從而獲取當(dāng)行數(shù)據(jù)）

思考題

1. 以下 SQL 語句，哪個執(zhí)行效率高？為什么？

select * from user where id = 10;
select * from user where name = 'Arm';
-- 備注：id為主鍵，name字段創(chuàng)建的有索引

答：第一條語句，因為第二條需要回表查詢，相當(dāng)于兩個步驟。

2. InnoDB 主鍵索引的 B+Tree 高度為多少？

答：假設(shè)一行數(shù)據(jù)大小為1k，一頁中可以存儲16行這樣的數(shù)據(jù)。InnoDB 的指針占用6個字節(jié)的空間，主鍵假設(shè)為bigint，占用字節(jié)數(shù)為8.
可得公式：n * 8 + (n + 1) * 6 = 16 * 1024，其中 8 表示 bigint 占用的字節(jié)數(shù)，n 表示當(dāng)前節(jié)點存儲的key的數(shù)量，(n + 1) 表示指針數(shù)量（比key多一個）。算出n約為1170。

如果樹的高度為2，那么他能存儲的數(shù)據(jù)量大概為：1171 * 16 = 18736；
如果樹的高度為3，那么他能存儲的數(shù)據(jù)量大概為：1171 * 1171 * 16 = 21939856。

另外，如果有成千上萬的數(shù)據(jù)，那么就要考慮分表，涉及運(yùn)維知識。

語法

創(chuàng)建索引：
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);
如果不加 CREATE 后面不加索引類型參數(shù)，則創(chuàng)建的是常規(guī)索引

查看索引：
SHOW INDEX FROM table_name;

刪除索引：
DROP INDEX index_name ON table_name;

案例：

-- name字段為姓名字段，該字段的值可能會重復(fù)，為該字段創(chuàng)建索引
create index idx_user_name on tb_user(name);
-- phone手機(jī)號字段的值非空，且唯一，為該字段創(chuàng)建唯一索引
create unique index idx_user_phone on tb_user (phone);
-- 為profession, age, status創(chuàng)建聯(lián)合索引
create index idx_user_pro_age_stat on tb_user(profession, age, status);
-- 為email建立合適的索引來提升查詢效率
create index idx_user_email on tb_user(email);

-- 刪除索引  
drop index idx_user_email on tb_user;  

索引使用規(guī)則

最左前綴法則

如果索引關(guān)聯(lián)了多列（聯(lián)合索引），要遵守最左前綴法則，最左前綴法是查詢從索引的最左列開始，并且不跳過索引中的列。

最左前綴法則失效的兩種情況：

1. 查詢的時候如果跳躍某一列，索引將部分失效（后面的字段索引失效）。跳過的話，后面的排序就無從說起了。

   注意：最左前綴法則在用select的時候，和放的位置是沒有關(guān)系的，只要存在就行（這里說的是select后面）

   explain select * from tb user where age = 31 and status = '0' and profession ="計算機(jī)系";
   

   索引是idx_age_sta_pro，where后三者age、status、profession，順序不能反且必須age在最前面，然后status，profession。
   若是age、profession則只有age才走索引。

2. 聯(lián)合索引中，出現(xiàn)范圍查詢（<, >），范圍查詢右側(cè)的列索引失效。可以用>=或者<=來規(guī)避索引失效問題。 因為使用了等于號后右側(cè)的索引列仍然生效。 下面這個的索引是idx_pro_age_sta

   #右側(cè)status不會走索引，profession和age會走索引
   explain select * from tb_user where profession = "軟件工程" and age > 30 and status=0;
   #右側(cè)status會走索引，profession和age會走索引
   explain select * from tb_user where profession = "軟件工程" and age >= 30 and status=0;
   

索引失效情況

1. 在索引列上進(jìn)行運(yùn)算操作，索引將失效。如：explain select * from tb_user where substring(phone, 10, 2) = '15'; 換成 explain select * from tb_user where phone = '17799990015';這是可以的。

   這里的運(yùn)算操作包含：函數(shù)、類型轉(zhuǎn)換等

2. 字符串類型字段使用時，不加引號會導(dǎo)致隱式類型轉(zhuǎn)換，索引將失效。如：explain select * from tb_user where phone = 17799990015;，此處phone的值沒有加引號

3. 模糊查詢中，字符開頭的都會導(dǎo)致索引失效，如：%我、_你
   ① 如果僅僅是尾部模糊匹配，索引不會是失效；explain select * from tb_user where profession like '軟件%';
   ② 如果是頭部模糊匹配，索引失效。如：explain select * from tb_user where profession like '%工程';，
   ③ 前后都有 % 也會失效。

4. 用 or 分割開的條件，如果 or 其中一個條件的列沒有索引，那么涉及的索引都不會被用到。

   # id有聚集索引，age沒有索引。因為用or連接，所以id也不走索引了
   explain select * from tb user where id = 10 or age = 23;
   

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

   # 不走索引，走全表掃描。因為該表里大部分?jǐn)?shù)據(jù)都大于這個值，走全表掃描更快
   explain select * from tb_user where phone >= 17799990000;
   

SQL 提示

SQL提示就是指定索引

是優(yōu)化數(shù)據(jù)庫的一個重要手段，簡單來說，就是在SQL語句中加入一些人為的提示來達(dá)到優(yōu)化操作的目的。

1. 建議使用索引：use index(idx_user_pro)
explain select * from tb_user use index(idx_user_pro) where profession="軟件工程";
2. 不使用哪個索引：ignore index(idx_user_pro)
explain select * from tb_user ignore index(idx_user_pro) where profession="軟件工程";
3. 必須使用哪個索引：force index(idx_user_pro)
explain select * from tb_user force index(idx_user_pro) where profession="軟件工程";

use 是建議，實際使用哪個索引 MySQL 還會自己權(quán)衡運(yùn)行速度去更改，force就是無論如何都強(qiáng)制使用該索引。

覆蓋索引&回表查詢

覆蓋索引：查詢條件使用了索引，并且需要返回的列在該索引中已經(jīng)全部能找到。

explain 語句中 extra 字段含義：
using index condition：查找使用了索引，但是需要回表查詢數(shù)據(jù)
using where; using index;：查找使用了索引，但是需要的數(shù)據(jù)都在索引列中能找到，所以不需要回表查詢

覆蓋索引效率分析：①②③沒懂可以看看前面聚集索引分類的圖

① 如果在聚集索引中直接能找到對應(yīng)的行，則直接返回行數(shù)據(jù)，只需要一次查詢，哪怕是select *；如select * from tab where id=1;

② 如果在生成的二級索引（輔助索引）中可以一次性獲得select所需要的字段，不需要回表查詢。如select id, name from xxx where name='xxx';，也只需要通過輔助索引(name)查找到對應(yīng)的id，返回name和name索引對應(yīng)的id即可，只需要一次查詢；

③ 如果是通過二級索引查找其他字段，則需要回表查詢，如select id, name, gender from xxx where name='xxx';

所以盡量不要用select *，容易出現(xiàn)回表查詢，降低效率，除非有聯(lián)合索引包含了所有字段

例子：

第一條sql是直接聚集索引可以查到數(shù)據(jù)

第二條sql需要先二級索引查到id，然后再用id去聚集索引查到那行數(shù)據(jù)

面試題：一張表，有四個字段（id, username, password, status），由于數(shù)據(jù)量大，需要對以下SQL語句進(jìn)行優(yōu)化，該如何進(jìn)行才是最優(yōu)方案：
select id, username, password from tb_user where username='itcast';

解：給username和password字段建立聯(lián)合索引，則不需要回表查詢，直接覆蓋索引。
username和password字段建立聯(lián)合索引的葉子節(jié)點掛的就是 id 所以不需要三者同時建索引。

前綴索引

當(dāng)字段類型為字符串（varchar, text等）時，有時候需要索引很長的字符串，這會讓索引變得很大，查詢時，浪費(fèi)大量的磁盤IO，影響查詢效率，此時可以只將字符串的一部分前綴建立索引，這樣可以大大節(jié)約索引空間，從而提高索引效率。

語法：create index idx_xxxx on table_name(columnn(n));
例子：create index idx_user_phone_5 on table_name(phone(5));
前綴長度：可以根據(jù)索引的選擇性來決定，而選擇性是指不重復(fù)的索引值（基數(shù)）和數(shù)據(jù)表的記錄總數(shù)的比值，索引選擇性越高則查詢效率越高，唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。

求選擇性公式：

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

前綴索引中是有可能碰到相同的索引的情況的（因為選擇性可能不為1），所以使用前綴索引進(jìn)行查詢的時候，mysql 會有一個回表查詢的過程，確定是否為所需數(shù)據(jù)。如圖中的查詢到lvbu6之后還要進(jìn)行回表，回表完再查xiaoy，看到xiaoy是不需要的數(shù)據(jù)，則停止查下一個。

show index 里面的sub_part可以看到接取的長度

單列索引&聯(lián)合索引

單列索引：即一個索引只包含單個列
聯(lián)合索引：即一個索引包含了多個列
在業(yè)務(wù)場景中，如果存在多個查詢條件，考慮針對于查詢字段建立索引時，建議建立聯(lián)合索引，而非單列索引。

單列索引情況：

explain select id, phone, name from tb_user where phone = '17799990010' and name = '韓信';
phone 和 name 都建立了單列索引情況下，這句只會用到phone索引字段（左邊優(yōu)先）

聯(lián)合索引的數(shù)據(jù)組織圖：先根據(jù)第一個字段排序，若第一個字段相同才根據(jù)第二個字段排序（默認(rèn)順序排序）

注意事項

• 多條件聯(lián)合查詢時，MySQL優(yōu)化器會評估哪個字段的索引效率更高，會選擇該索引完成本次查詢

設(shè)計原則

1. 針對于數(shù)據(jù)量較大，且查詢比較頻繁的表建立索引（達(dá)到百萬條數(shù)據(jù)，才建議建立索引）
2. 針對于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立索引
3. 盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引，區(qū)分度越高，使用索引的效率越高（若聯(lián)合索引中有字段是唯一的，就建唯一索引）
4. 如果是字符串類型的字段，字段長度較長，可以針對于字段的特點，建立前綴索引
5. 盡量使用聯(lián)合索引，減少單列索引，查詢時，聯(lián)合索引很多時候可以覆蓋索引，節(jié)省存儲空間，避免回表，提高查詢效率
6. 要控制索引的數(shù)量，索引并不是多多益善，索引越多，維護(hù)索引結(jié)構(gòu)的代價就越大，會影響增刪改的效率
7. 如果索引列不能存儲NULL值，請在創(chuàng)建表時使用NOT NULL約束它。當(dāng)優(yōu)化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用于查詢

注意事項：

1. 當(dāng)需要查詢一張表的兩個字段信息，且已經(jīng)有字段A索引、字段B索引、字段AB聯(lián)合索引。此時查詢會使用哪個索引？

   答：使用AB字段聯(lián)合索引。原因：MySQL的查詢優(yōu)化器也不能在執(zhí)行前判斷哪個索引才最好，所以它選擇規(guī)則是：索引數(shù)量少、索引覆蓋范圍最廣，最有可能用到的索引。

SQL 優(yōu)化

省流版：

1. 插入數(shù)據(jù)：① insert: 批量插入、手動控制事務(wù)、主鍵順序插入 ②大批量插入: load data local infile
2. 主鍵優(yōu)化：主鍵長度盡量短，如：自增AUTO INCREMENT，少用UUID、雪花算法等
3. order by 優(yōu)化：① using index：直接通過聯(lián)合索引返回數(shù)據(jù)，性能高；② using filesort：需要將返回的結(jié)果在排序緩沖區(qū)排序
4. group by 優(yōu)化：聯(lián)合索引，多字段分組滿足最左前綴法則
5. limit 優(yōu)化：覆蓋索引 + 子查詢
6. count（）優(yōu)化：性能考慮，count(字段)< count(主鍵 id) < count(1)約等于 count(*)
7. update 優(yōu)化：盡量根據(jù)主鍵/索引字段進(jìn)行數(shù)據(jù)更新

插入數(shù)據(jù)

普通插入：

1. 采用批量插入（一次插入的數(shù)據(jù)不建議超過1000條，500 - 1000 為宜）

2. 設(shè)置手動提交事務(wù)

   mysql默認(rèn)是每次操作都會提交一次事務(wù)，這樣很消耗數(shù)據(jù)庫性能，要改成多次操作一次性提交

3. 主鍵順序插入（主鍵順序插入的效率大于亂序插入）

   就是按照id：1，2，3，4，5插入比id：5，0，9，1，36，2插入效率高

   
   

大批量插入：

如果一次性需要插入大批量數(shù)據(jù)，使用insert語句插入性能較低，此時可以使用MySQL數(shù)據(jù)庫提供的load指令插入。

本地文件只需要有一定規(guī)則就可以直接加載進(jìn)數(shù)據(jù)庫表中。

如：1,conan,男,180,120 幾個字段就幾個數(shù)據(jù)對應(yīng)，中間用逗號/分號/句號等統(tǒng)一符號即可

使用load指令的操作：

# 客戶端連接服務(wù)端時，加上參數(shù) --local-infile（這一行在bash/cmd界面輸入）
mysql --local-infile -u root -p
# 設(shè)置全局參數(shù)local_infile為1（默認(rèn)是0未開啟），開啟從本地加載文件導(dǎo)入數(shù)據(jù)的開關(guān)
set global local_infile = 1;
select @@local_infile;  --查看local_infile這個參數(shù)的值是什么
# 建立一張表tb_user（這里省略如何建表）
#將本地文件數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫表。參數(shù)：本地文件地址、表名、數(shù)據(jù)之間用什么符號分割、數(shù)據(jù)每行之間用什么分割
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

主鍵優(yōu)化

主鍵設(shè)計原則：

• 滿足業(yè)務(wù)需求的情況下，盡量降低主鍵的長度（二級索引的葉子節(jié)點保存的就是主鍵，所以主鍵小占用的空間也就會少）
• 插入數(shù)據(jù)時，盡量選擇順序插入，選擇使用 AUTO_INCREMENT 自增主鍵
• 盡量不要使用 UUID、雪花算法 做主鍵或者是其他的自然主鍵，如身份證號，占用的空間大。
• 業(yè)務(wù)操作時，避免對主鍵的修改

數(shù)據(jù)組織方式：在InnoDB存儲引擎中，表數(shù)據(jù)都是根據(jù)主鍵順序組織存放的，這種存儲方式的表稱為索引組織表（Index organized table, IOT）

主鍵的順序插入過程如下：

但是如果主鍵是亂序插入的話，就會導(dǎo)致需要插入的位置為中間的位置，會有頁分裂的過程。

頁分裂：頁可以為空，也可以填充一般，也可以填充100%，每個頁包含了2-N行數(shù)據(jù)（如果一行數(shù)據(jù)過大，會行溢出），根據(jù)主鍵排列。

頁合并：當(dāng)刪除一行記錄時，實際上記錄并沒有被物理刪除，只是記錄被標(biāo)記（flaged）為刪除并且它的空間變得允許被其他記錄聲明使用。當(dāng)頁中刪除的記錄到達(dá) MERGE_THRESHOLD（默認(rèn)為頁的50%），InnoDB會開始尋找最靠近的頁（前后）看看是否可以將這兩個頁合并以優(yōu)化空間使用。

MERGE_THRESHOLD：合并頁的閾值，可以自己設(shè)置，在創(chuàng)建表或創(chuàng)建索引時指定

文字說明不夠清晰明了，具體可以看視頻里的PPT演示過程：https://www.bilibili.com/video/BV1Kr4y1i7ru?p=90

order by優(yōu)化

總結(jié)：

• 根據(jù)排序字段建立合適的索引，多字段排序時，也遵循最左前綴法則
• 盡量使用覆蓋索引（覆蓋索引是指查詢時查字段且字段是索引中的，聯(lián)合索引是指多個字段一起綁定為一個索引）
• 多字段排序，一個升序一個降序，此時需要注意聯(lián)合索引在創(chuàng)建時的規(guī)則（ASC/DESC）
• 如果不可避免出現(xiàn)filesort，大數(shù)據(jù)量排序時，可以適當(dāng)增大排序緩沖區(qū)大小 sort_buffer_size（默認(rèn)256k）

1. Using filesort：通過表的索引或全表掃描，讀取滿足條件的數(shù)據(jù)行，然后在排序緩沖區(qū) sort buffer 中完成排序操作，所有不是通過索引直接返回排序結(jié)果的排序都叫 FileSort 排序
2. Using index：通過有序索引順序掃描直接返回有序數(shù)據(jù)，這種情況即為 using index，不需要額外排序，操作效率高

#沒有創(chuàng)建索引時，根據(jù)age, phone進(jìn)行排序
explain select id,age,phone from tb user order by age , phone;
#創(chuàng)建索引(沒指定asc、desc時，默認(rèn)都是順序asc)
create index idx_user_age_phone_aa on tb_user(age,phone);
#創(chuàng)建索引后，根據(jù)age, phone進(jìn)行升序排序(此時會用索引，用Using index)
explain select id,age,phone from tb user order by age,phone;
#創(chuàng)建索引后，根據(jù)age,phone進(jìn)行降序排序(此時也會用索引，用Using index，因為數(shù)據(jù)庫可以倒序索引)
explain select id,age,phone from tb user order by age desc , phone desc;
#根據(jù)age, phone進(jìn)行降序一個升序，一個降序(此時不會用索引，還是用Using filesort，因為索引是兩者順序，這里是一順一反)
explain select id,agephone from tb user order by age asc , phone desc;
#根據(jù)age, phone進(jìn)行降序一個升序，一個降序(此時不會用索引，還是用Using filesort，因為索引是先age后phone，這里是反了)
explain select id,agephone from tb user order by phone asc , age asc;
#創(chuàng)建索引
create index idx user age phone ad on tb user(age asc ,phone desc);
#根據(jù)age, phone進(jìn)行降序一個升序，一個降序(此時會用索引，用Using index，因為創(chuàng)建索引時是一順一反)
explain select id,agephone from tb user order by age asc , phone desc;

創(chuàng)建索引時都是升序，如果order by字段全部使用升序排序或者降序排序，則都會走索引，但是如果一個字段升序排序，另一個字段降序排序，則不會走索引，explain的extra信息顯示的是Using index, Using filesort，如果要優(yōu)化掉Using filesort，則需要另外再創(chuàng)建一個索引，如：create index idx_user_age_phone_ad on tb_user(age asc, phone desc);，此時使用select id, age, phone from tb_user order by age asc, phone desc;會全部走索引

group by優(yōu)化

總結(jié)：

• 在分組操作時，可以通過索引來提高效率（多用聯(lián)合索引，少用單列索引，聯(lián)合索引也可以使用在查詢單列的時候）
• 分組操作時，索引的使用也是滿足最左前綴法則的

如索引為idx_user_pro_age_stat，則句式可以是select ... where profession order by age，這樣也符合最左前綴法則

limit優(yōu)化

常見的問題如limit 2000000, 10，此時需要 MySQL 排序前2000000條記錄，但僅僅返回2000000 - 2000010的記錄，其他記錄丟棄，查詢排序的代價非常大。
優(yōu)化方案：一般分頁查詢時，通過創(chuàng)建覆蓋索引能夠比較好地提高性能，可以通過覆蓋索引加子查詢形式進(jìn)行優(yōu)化

例如：

-- 此語句耗時很長(19s)
select * from tb_sku limit 9000000, 10;
-- 通過覆蓋索引加快速度，直接通過主鍵索引進(jìn)行排序及查詢
select id from tb_sku order by id limit 9000000, 10;
-- 下面的語句是錯誤的，因為 MySQL 不支持 in 里面使用 limit
-- select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
-- 通過連表查詢即可實現(xiàn)第一句的效果，并且能達(dá)到第二句的速度(11s)
select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;

count優(yōu)化

MyISAM 引擎把一個表的總行數(shù)存在了磁盤上，因此執(zhí)行 count(*) 的時候會直接返回這個數(shù)，效率很高（前提是不適用where）；
InnoDB 在執(zhí)行 count(*) 時，需要把數(shù)據(jù)一行一行地從引擎里面讀出來，然后累計計數(shù)。
優(yōu)化方案：自己計數(shù)，如創(chuàng)建key-value表存儲在內(nèi)存或硬盤，或者是用redis

count的幾種用法：

• 如果count函數(shù)的參數(shù)（count里面寫的那個字段）不是NULL（字段值不為NULL），累計值就加一，最后返回累計值
• 用法：count(*)、count(主鍵)、count(字段)、count(1)
• count(主鍵)跟count(*)一樣，因為主鍵不能為空；count(字段)只計算字段值不為NULL的行；count(1)引擎會為每行添加一個1，然后就count這個1，返回結(jié)果也跟count(*)一樣；count(null)返回0

各種用法的性能：

• count(主鍵)：InnoDB引擎會遍歷整張表，把每行的主鍵id值都取出來，返回給服務(wù)層，服務(wù)層拿到主鍵后，直接按行進(jìn)行累加（主鍵不可能為空）
• count(字段)：沒有not null約束的話，InnoDB引擎會遍歷整張表把每一行的字段值都取出來，返回給服務(wù)層，服務(wù)層判斷是否為null，不為null，計數(shù)累加；有not null約束的話，InnoDB引擎會遍歷整張表把每一行的字段值都取出來，返回給服務(wù)層，直接按行進(jìn)行累加
• count(1)：InnoDB 引擎遍歷整張表，但不取值。服務(wù)層對于返回的每一層，放一個數(shù)字 1 進(jìn)去，直接按行進(jìn)行累加
• count(*)：InnoDB 引擎并不會把全部字段取出來，而是專門做了優(yōu)化，不取值，服務(wù)層直接按行進(jìn)行累加

按效率排序：count(字段) < count(主鍵) < count(1) < count(*)，所以盡量使用 count(*)

update優(yōu)化（避免行鎖升級為表鎖）

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

如以下兩條語句：
update student set no = '123' where id = 1;，這句由于id有主鍵索引，所以只會鎖這一行；
update student set no = '123' where name = 'test';，這句由于name沒有索引，所以會把整張表都鎖住進(jìn)行數(shù)據(jù)更新，解決方法是給name字段添加索引，就可以由表鎖變成行鎖。

視圖

若對視圖進(jìn)行增數(shù)據(jù)操作是可以插入的，且插入到關(guān)聯(lián)的表中

視圖（View）是一種虛擬存在的表。視圖中的數(shù)據(jù)并不在數(shù)據(jù)庫中實際存在，行和列數(shù)據(jù)來自定義視圖的查詢中使用的表，并且是在使用視圖時動態(tài)生成的。
通俗的講，視圖只保存了查詢的SQL邏輯，不保存查詢結(jié)果。所以我們在創(chuàng)建視圖的時候，主要的工作就落在創(chuàng)建這條SQL查詢語句上。

創(chuàng)建視圖

CREATE [ OR REPLACE ] VIEW 視圖名稱[（列名列表）] AS SELECT 語句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

例子： create or replace view stu_wll as select id,name from student where id<=10;

查詢視圖

查看創(chuàng)建視圖語句： SHOW CREATE VIEW 視圖名稱；

查看視圖數(shù)據(jù)：SELECT*FROM 視圖名稱；
show create view stu_v_1;

修改視圖

方式一：CREATE[OR REPLACE] VIEW 視圖名稱[（列名列表)）] AS SELECT 語句[ WITH[ CASCADED | LOCAL ] CHECK OPTION ]

方式二：ALTER VIEW 視圖名稱 [（列名列表)] AS SELECT語句 [WITH [CASCADED | LOCAL] CHECK OPTION]

例子：

# 既可以做創(chuàng)建語句也可以做修改語句，因為已經(jīng)存在該視圖的話會直接覆蓋掉
create or replace view v1 as select id,name from student where id<=20;

# 只能做修改語句
alter view v1 as select id,name from student where id<=20;

刪除視圖

DROP VIEW [IF EXISTS] 視圖名稱 [視圖名稱]

視圖檢查選項

當(dāng)使用WITH CHECK QPTION子句創(chuàng)建/修改視圖時，MySQL會通過視圖檢查正在更改的每個行，例如插入，更新，刪除，以使其符合視圖的定義。MySQL允許基于另一個視圖創(chuàng)建視圖，它還會檢查依賴視圖中的規(guī)則以保持一致性。為了確定檢查的范圍，mysql提供了兩個選項：CASCADED 和 LOCAL ，默認(rèn)值為 CASCADED。

NOTE：如果沒有開檢查選項就不會進(jìn)行檢查。不同版本是不同含義的，要看版本。

CASCADED

級聯(lián)檢查：增刪改的時候會檢查是否符合當(dāng)前視圖的條件，以及與當(dāng)前視圖有關(guān)聯(lián)的視圖（如該視圖是基于另一視圖創(chuàng)建的，也需要符合另一視圖的條件，因為級聯(lián)檢查會相當(dāng)于給當(dāng)前視圖以及與之有關(guān)聯(lián)的視圖都增加CASCADED）

例子：

# 比如下面的例子：創(chuàng)建stu_V_l 視圖，id是小于等于 20的。  
create or replace view stu_V_l as select id,name from student where id <=20;
# 再創(chuàng)建 stu_v_2 視圖(由于加了級聯(lián)檢查，所以對2視圖增刪改的時候需要滿足：id>=10,id<=20. 別忘了級聯(lián)檢查也會檢查關(guān)聯(lián)視圖)
create or replace view stu_v_2 as select id,name from stu_v_1 where id >=10 with cascaded check option;
# 再創(chuàng)建 stu_v_3 視圖。     
create or replace view stu_v_3 as select id,name from stu_v_2 where id<=15;
# 可以插入。stu_v_3 沒有開檢查選項所以不會去判斷 id 是否小于等于15，因為使用到了視圖stu_v_2且其加了級聯(lián)檢查，所以需要判斷stu_v_2與stu_v_1視圖的條件是否滿足，都滿足可以插入。
insert into stu_v_3 values(17,'Tom');
# 可以插入。雖然不符合stu_v_3的條件，但stu_v_3沒有級聯(lián)檢查，關(guān)聯(lián)視圖stu_v_2、stu_v_1才有級聯(lián)檢查，符合stu_v_2、stu_v_1的條件
insert into stu_v_3 values(11,'Tom');
# 不能插入。和上面同理，這里是不滿足stu_v_1的條件
insert into stu_v_3 values(28,'Tom');

LOCAL

本地的條件也會檢查，還會向上檢查。在向上找的時候，就要看是否上面開了檢查選項，如果沒開就不檢查。
和 CASCADED 的區(qū)別就是 CASCADED 不管上面開沒開檢查選項都會進(jìn)行檢查。LOCAL是會遞歸上面有關(guān)視圖，遍歷到的視圖開啟了LOCAL才會檢查條件。

例子：

# 比如下面的例子：創(chuàng)建stu_V_l 視圖，id是小于等于 20的。  
create or replace view stu_V_l as select id,name from student where id <=20;
# 再創(chuàng)建 stu_v_2 視圖(由于加了本地檢查，所以對2視圖增刪改的時候需要滿足：id>=10)
create or replace view stu_v_2 as select id,name from stu_v_1 where id >=10 with local check option;
# 再創(chuàng)建 stu_v_3 視圖。
create or replace view stu_v_3 as select id,name from stu_v_2 where id<=15;
# 可以插入。stu_v_3 沒有開檢查選項所以不會去判斷 id 是否小于等于15，因為使用到了視圖stu_v_2且其加了本地檢查，所以需要判斷stu_v_2的條件是否滿足，再去檢查stu_v_1發(fā)現(xiàn)其未加檢查，所以不判斷stu_v_1的條件。
insert into stu_v_3 values(17,'Tom');
# 可以插入。雖然不符合stu_v_3的條件，但stu_v_3沒有檢查，關(guān)聯(lián)視圖stu_v_2才有級聯(lián)檢查，符合stu_v_2的條件。
insert into stu_v_3 values(11,'Tom');
# 可以插入。和上面同理
insert into stu_v_3 values(28,'Tom');

更新及作用

視圖更新限制條件：

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

1. 聚合函數(shù)或窗口函數(shù) ( SUM()、MIN()、MAX()、COUNT() 等 )
2. DISTINCT
3. GROUP BY
4. HAVING
5. UNION 或者UNION ALL

例子： 使用了聚合函數(shù)，插入會失敗。
create view stu_v_count as select count(*) from student;
insert into stu_v_count values(10);

視圖作用：

• 簡單

? 視圖不僅可以簡化用戶對數(shù)據(jù)的理解，也可以簡化他們的操作。那些被經(jīng)常使用的查詢可以被定義為視圖，從而使得用戶不必為以后 的操作每次指定全部的條件。

• 安全

  數(shù)據(jù)庫可以授權(quán)，但不能授權(quán)到數(shù)據(jù)庫特定行和特定的列上。通過視圖用戶只能查詢和修改他們所能見到的數(shù)據(jù)

• 數(shù)據(jù)獨立

  視圖可幫助用戶屏蔽真實表結(jié)構(gòu)變化帶來的影響。（查表字段的時候給字段取別名，這樣視圖名稱不會被真是表所影響）

總而言之 類似于給表加上了一個外殼，通過這個外殼訪問表的時候，只能按照所設(shè)計的方式進(jìn)行訪問與更新。

存儲過程

存儲過程和函數(shù)是事先經(jīng)過編譯并存儲在數(shù)據(jù)庫中的一段 SQL 語句的集合，調(diào)用存儲過程和函數(shù)可以簡化應(yīng)用開發(fā)人員的很多工作，減少數(shù)據(jù)在數(shù)據(jù)庫和應(yīng)用服務(wù)器之間的傳輸，對于提高數(shù)據(jù)處理的效率是有好處的（存儲過程思想上很簡單，就是數(shù)據(jù)庫SQL 語言層面的代碼封裝與重用）
存儲過程和函數(shù)的區(qū)別在于函數(shù)必須有返回值，而存儲過程沒有。
函數(shù) ： 是一個有返回值的過程 ；
過程 ： 是一個沒有返回值的函數(shù) ；

特點

1. 封裝
2. 復(fù)用
3. 可以接收參數(shù)，也可以返回數(shù)據(jù)減少網(wǎng)絡(luò)交互，效率提升

基本操作

創(chuàng)建

CREATE PROCEDURE 存儲過程名稱( [參數(shù)列表] ) 
BEGIN
	 -- SQL 語句 
END;

# 如：（執(zhí)行以下命令會報錯，看下面注意）
create procedure pro_test1()
begin
    select 'Hello World';
end;

# 修改完結(jié)束符后的語句
create procedure pro_test1()
begin
    select 'Hello World';
end$

注意：在命令行中，執(zhí)行創(chuàng)建存儲過程的SQL時，默認(rèn)是分號作為結(jié)束符，需要在執(zhí)行begin end前通過關(guān)鍵字delimiter 指定SQL語句的結(jié)束符。

delimiter $ ，則 $ 符作為結(jié)束符。

調(diào)用

CALL 名稱 ( [參數(shù)])

查看

查詢指定數(shù)據(jù)庫的存儲過程及狀態(tài)信息

SELECT* FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'

存儲過程名稱；--查詢某個存儲過程的定義

SHOW CREATE PROCEDURE

例子：

--查詢db_name數(shù)據(jù)庫中的所有的存儲過程
select name from mysql.proc where db='db_name';
--例子：
select name from mysql.proc where db='demo_01';
 
--查詢存儲過程的狀態(tài)信息
show procedure status;
 
--查詢某個存儲過程的定義
show create procedure pro_test1 \G;

刪除

DROP PROCEDURE [ IFEXISTS ] 存儲過程名稱

如：drop procedure if exists pro_test1;

語法

存儲過程是可以編程的，意味著可以使用變量，表達(dá)式，控制結(jié)構(gòu) ， 來完成比較復(fù)雜的功能。

變量

在MySQL中變量分為三種類型: 系統(tǒng)變量、用戶定義變量、局部變量

變量類型

系統(tǒng)變量

系統(tǒng)變量 是MySQL服務(wù)器提供，不是用戶定義的，屬于服務(wù)器層面。分為全局變量（GLOBAL）、會話變量（SESSION）。

1. 查看系統(tǒng)變量

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

2. 設(shè)置系統(tǒng)變量

   SET [ SESSION | GLOBAL ] 系統(tǒng)變量名 = 值 ;
   SET @@[SESSION | GLOBAL] 系統(tǒng)變量名 = 值 ;
   

注意:

如果沒有指定SESSION/GLOBAL，默認(rèn)是SESSION會話變量

A. 全局變量(GLOBAL): 全局變量針對于所有的會話。

B. 會話變量(SESSION): 會話變量針對于單個會話，在另外一個會話窗口就不生效了。

用戶定義變量

用戶定義變量 是用戶根據(jù)需要自己定義的變量，用戶變量不用提前聲明，在用的時候直接用 "@變量名" 使用就可以。其作用域為當(dāng)前連接。

1. 賦值（賦值時，可以使用 = ，也可以使用 := ）

   • 方式一：

     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 表名;
     

2. 使用

   SELECT @var_name ;
   

注意：用戶定義的變量無需對其進(jìn)行聲明或初始化，只不過獲取到的值為NULL。

局部變量

局部變量是根據(jù)需要定義的在局部生效的變量，訪問之前，需要DECLARE聲明?？捎米鞔鎯^程內(nèi)的局部變量和輸入?yún)?shù)，局部變量的范圍是在其內(nèi)聲明的BEGIN ... END塊。

1. 聲明：DECLARE 變量名 變量類型 [DEFAULT ... ] ;

   變量類型就是數(shù)據(jù)庫字段類型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等

2. 賦值

   SET 變量名 = 值 ;
   SET 變量名 := 值 ;
   SELECT 字段名 INTO 變量名 FROM 表名 ... ;
   

變量定義語法

DECLARE

通過 DECLARE 可以定義一個局部變量，該變量的作用范圍只能在 BEGIN…END 塊中。

DECLARE var_name[,...] type [DEFAULT value]
示例：

delimiter $

create procedure pro_test2()
begin
    declare num int default 10;
    select num+5;
end$

delimiter ;

SET

直接賦值使用 SET，可以賦常量或者賦表達(dá)式，具體語法如下：

SET var_name = expr [, var_name = expr] ...
示例：

delimiter $

create procedure pro_test3()
begin
    declare num int(11);
    set num = 100;
    select num;
end$

delimiter ;

也可以通過select ... into 方式進(jìn)行賦值操作：

delimiter $

create procedure pro_test4()
begin
    declare countnum int(11);
    select count(*) into countnum from city;
    select countnum;
end$

delimiter ;

if條件判斷

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

if search_condition then statement_list
    [elseif search_condition then statement_list] ...
    [else statement_list]
end if;

例子需求：

根據(jù)定義的身高變量，判定當(dāng)前身高的所屬的身材類型
180 及以上 ----------> 身材高挑
170 - 180 ---------> 標(biāo)準(zhǔn)身材
170 以下 ----------> 一般身材

存儲過程實現(xiàn)如下：

delimiter $

create procedure pro_test5()
begin
    declare height int(11) default 175;
    declare description varchar(50);
    if height > 180 then
        set description='身材高挑';
    elseif height >=170 and height <= 180 then
        set description='標(biāo)準(zhǔn)身材';
    else set description='一般身材';
    end if;
    select description;
end$

delimiter ;

調(diào)用：call pro_test5 ( ) ;

參數(shù)與返回結(jié)果

語法格式 :

create procedure procedure_name([in/out/inout] 參數(shù)名 參數(shù)類型)
...--其他部分和之前的是一樣的
IN （默認(rèn)）: 該參數(shù)可以作為輸入，也就是需要調(diào)用方傳入值 
OUT: 該參數(shù)作為輸出，也就是該參數(shù)可以作為返回值
INOUT: 既可以作為輸入?yún)?shù)，也可以作為輸出參數(shù)

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

需求 ：每次調(diào)用存儲過程的時候動態(tài)的傳入身高，返回體型值

delimiter $

create procedure pro_test6(in height int)
begin
    declare description varchar(50) default '';
    if height >= 180 then
        set description='身材高挑';
      elseif height >=170 and height < 180 then
        set description='標(biāo)準(zhǔn)身材';
      else set description='一般身材';
    end if;
    select concat('身高 ', height , '對應(yīng)的身材類型為:',description);
end$

delimiter ;

OUT-輸出

需求 ：定義兩個參數(shù)，傳入身高和身材描述的值（傳出參數(shù)）

delimiter $

create procedure pro_test7(in height int(11),out description varchar(50))
begin
    if height >= 180 then
        set description='身材高挑';
      elseif height >= 170 and height < 180 then
        set description='標(biāo)準(zhǔn)身材';
      else set description='一般身材';
    end if;
end$

delimiter ;

調(diào)用：

call pro_test7(178, @description);
查詢得到的身材描述的變量值，我們需要單獨去查詢下：

select @description;

小知識
@description : 這種變量要在變量名稱前面加上“@”符號，叫做用戶會話變量，代表整個會話過程他都是有作用的，這個類似于全局變量一樣。
@@global.sort_buffer_size : 這種在變量前加上 "@@" 符號, 叫做 系統(tǒng)變量

INOUT

需求：將傳入的200分制的分?jǐn)?shù)，進(jìn)行換算，換算成百分制，然后返回。

create procedure p5(inout score double)

begin
	set score := score * 0.5;
end;

set @score = 198;

call p5(@score);

select @score;

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

語法結(jié)構(gòu) :

方式一 :
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE;

方式二 :
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE;

需求：給定一個月份, 然后計算出所在的季度

delimiter $

create procedure pro_test8(month int)
begin
    declare result varchar(50) default '';
    case
        when month >=1 and month <=3 then
            set result ='第一季度';
        when month >=4 and month <=6 then
            set result ='第二季度';
        when month >=7 and month <=9 then
            set result ='第三季度';
        when month >=10 and month <=12 then
            set result ='第四季度'; 
    end case;
    select concat('您輸入的月份是：',month,'，對應(yīng)的季節(jié)是：',result) content;
end$

delimiter ;

while循環(huán)

語法結(jié)構(gòu):

while search_condition do
    statement_list
end while;
需求：計算從1加到n的值，傳入?yún)?shù)是n

delimiter $

create procedure pro_test9(in n int)
begin
    declare countvalue int(11) default 0;
    while n > 0 do
        set countvalue=countvalue+n;
        set n=n-1;
    end while;
    select concat('從1加到n的和是：',countvalue);
end$

delimiter ;

調(diào)用效果如下：

mysql> call pro_test9(10)$
+----------------------------------------------+
| concat('從1加到n的和是：',countvalue)        |
+----------------------------------------------+
| 從1加到n的和是：55                           |
+----------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

repeat結(jié)構(gòu)

有條件的循環(huán)控制語句, 當(dāng)滿足條件的時候退出循環(huán) 。while 是滿足條件才執(zhí)行，repeat 是滿足條件就退出循環(huán)。
語法結(jié)構(gòu) :

REPEAT
    statement_list
    UNTIL search_condition
END REPEAT;

需求：計算從1到n的和

delimiter $

create procedure pro_test10(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$

delimiter ;

loop語句

LOOP 實現(xiàn)簡單的循環(huán)，退出循環(huán)的條件需要使用其他的語句定義，通常可以使用 LEAVE 語句實現(xiàn)，具體語法如下：

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

如果不在 statement_list 中增加退出循環(huán)的語句，那么 LOOP 語句可以用來實現(xiàn)簡單的死循環(huán)。

leave語句

? 用來從標(biāo)注的流程構(gòu)造中退出，通常和 BEGIN ... END 或者循環(huán)一起使用。下面是一個使用 LOOP 和 LEAVE 的簡單例子 , 退出循環(huán)：

delimiter $

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

delimiter ;

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

? 游標(biāo)是用來存儲查詢結(jié)果集的數(shù)據(jù)類型 , 在存儲過程和函數(shù)中可以使用光標(biāo)對結(jié)果集進(jìn)行循環(huán)的處理。光標(biāo)的使用包括光標(biāo)的聲明、OPEN、FETCH 和 CLOSE，其語法分別如下。

聲明光標(biāo)：

declare cursor_name cursor for select_statement;

OPEN 光標(biāo)：

open cursor_name;

FETCH 光標(biāo)：

FETCH cursor_name INTO var_name [, var_name] ...;

CLOSE 光標(biāo)：

CLOSE cursor_name ;

條件處理程序：
條件處理程序（Handler）可以用來定義在流程控制結(jié)構(gòu)執(zhí)行過程中遇到問題時相應(yīng)的處理步驟。具體語法為：

DECLARE handler action HANDLER FOR condition value L condition value]..statement

handler_action CONTINUE：繼續(xù)執(zhí)行當(dāng)前程序

EXIT：終止執(zhí)行當(dāng)前程序

condition_value :

SQLSTATE sqlstate_value：狀態(tài)碼，如02000

SQLWARNING：所有以01開頭的SQLSTATE代碼的簡寫

NOT FOUND：所有以02開頭的SQLSTATE代碼的簡寫

SQLEXCEPTION：所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE代碼的簡寫

例子：

NOTE：要先聲明普通變量，再申請游標(biāo)。

要求：
根據(jù)傳入的參數(shù)uage，來查詢用戶表tb_user中，所有的用戶年齡小于等于uage的用戶姓名（name）和專業(yè)（profession），并將用戶的姓名和專業(yè)插入到所創(chuàng)建的一張新表（id，name，profession）中。

create procedure p1l(in uage int)

	begin

		declare uname varchar(100); 

		decLare upro varchar(100);

		declare u_cursor cursor for select name,profession from tb_user where age <= uage; 

		當(dāng) 條件處理程序的處理的狀態(tài)碼為02000的時候，就會退出。
		declare exit handler for SQLSTATE '02000'close u_cursor;

		drop table if exists tb_user_pro; 

		create table if not exists 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,Upro; 

		insert into tb_user_pro values(null,uname,Upro); 

		end while;

		close u_cursor; 

	end;

示例 :

初始化腳本:

create table emp(

id int(11) not null auto_increment ,

name varchar(50) not null comment '姓名',

age int(11) comment '年齡',

salary int(11) comment '薪水',

primary key(`id`)

)engine=innodb default charset=utf8 ;

insert into emp(id,name,age,salary) values(null,'金毛獅王',55,3800),(null,'白眉鷹王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龍王',42,1800);

需求：查詢emp表中數(shù)據(jù), 并逐行獲取進(jìn)行展示

delimiter $

create procedure pro_test12()

begin
    declare e_id int(11);

    declare e_name varchar(50);

    declare e_age int(11);

    declare e_salary int(11);

    declare emp_result cursor for select * from emp;

    
    open emp_result;

    fetch emp_result into e_id,e_name,e_age,e_salary;

    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為:',e_salary);

    

    fetch emp_result into e_id,e_name,e_age,e_salary;

    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為:',e_salary);

    

    fetch emp_result into e_id,e_name,e_age,e_salary;

    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為:',e_salary);

    

    fetch emp_result into e_id,e_name,e_age,e_salary;

    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為:',e_salary);

    

    fetch emp_result into e_id,e_name,e_age,e_salary;

    select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為:',e_salary);


    close emp_result;

end$

delimiter ;

調(diào)用效果如下：

mysql> call pro_test12();

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

| concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為:',e_salary)    |

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

| id=5, name=金毛獅王, age=55, 薪資為:3800                                        |

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

1 row in set (0.00 sec)

 

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

| concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為:',e_salary)    |

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

| id=6, name=白眉鷹王, age=60, 薪資為:4000                                        |

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

1 row in set (0.00 sec)

 

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

| concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為:',e_salary)    |

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

| id=7, name=青翼蝠王, age=38, 薪資為:2800                                        |

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

1 row in set (0.00 sec)

 

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

| concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為:',e_salary)    |

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

| id=8, name=紫衫龍王, age=42, 薪資為:1800                                        |

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

1 row in set (0.00 sec)

 

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

可以看到最后的時候報了一個錯誤：ERROR 1329 (02000): No data - zero rows fetched, selected, or processed，這是因為游標(biāo)中已經(jīng)沒有數(shù)據(jù)了，而我們還在fetch，所以就報錯了。我們要防止這個錯誤，就要使用循環(huán)來從游標(biāo)中獲取數(shù)據(jù)，如下操作：

delimiter $

create procedure pro_test13()

begin

    declare e_id int(11);

    declare e_name varchar(50);

    declare e_age int(11);

    declare e_salary int(11);

    declare has_data int default 1;

 
    declare emp_result cursor for select * from emp;

    declare exit handler for not found set has_data=0;


    open emp_result;    

    repeat

        fetch emp_result into e_id,e_name,e_age,e_salary;

        select concat('id=',e_id,'，name=',e_name,'，age=',e_age,'，薪資:',e_salary);

        until has_data=0

    end repeat; 
    
    close emp_result;

end$
 

delimiter ;

注意：切記一點，until語句后面是沒有分號結(jié)尾的

調(diào)用效果：

mysql> call pro_test13()$

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

| concat('id=',e_id,'，name=',e_name,'，age=',e_age,'，薪資:',e_salary)      |

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

| id=5，name=金毛獅王，age=55，薪資:3800                                     |

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

1 row in set (0.00 sec)

 

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

| concat('id=',e_id,'，name=',e_name,'，age=',e_age,'，薪資:',e_salary)      |

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

| id=6，name=白眉鷹王，age=60，薪資:4000                                     |

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

1 row in set (0.00 sec)

 

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

| concat('id=',e_id,'，name=',e_name,'，age=',e_age,'，薪資:',e_salary)      |

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

| id=7，name=青翼蝠王，age=38，薪資:2800                                     |

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

1 row in set (0.00 sec)

 

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

| concat('id=',e_id,'，name=',e_name,'，age=',e_age,'，薪資:',e_salary)      |

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

| id=8，name=紫衫龍王，age=42，薪資:1800                                     |

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

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

存儲函數(shù)

存儲過程和存儲函數(shù)的區(qū)別：存儲過程更多用來封裝腳本（執(zhí)行一些重復(fù)性操作），存儲函數(shù)更多是封裝一個簡單的方法（查詢操作）不能修改表數(shù)據(jù)。

1. 返回值：存儲過程可以不返回值、返回多個結(jié)果集，而存儲函數(shù)必須返回一個值
2. 調(diào)用語法：調(diào)用存儲過程使用CALL語句，而調(diào)用存儲函數(shù)使用SELECT語句

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

CREATE FUNCTION function_name([param type ... ])

RETURNS type

BEGIN

    ...

END;

案例 ：定義一個存儲函數(shù), 請求滿足條件的總記錄數(shù) ;

delimiter $

 

create function fun_count_city(countryId int)

returns int

begin

    declare cnum int(11);

    select count(1) into cnum from city where country_id=countryId;

    return cnum;

end$

 

delimiter ;

調(diào)用：

mysql> select fun_count_city(1);

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

| fun_count_city(1) |

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

|                 3 |

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

1 row in set (0.00 sec)

觸發(fā)器

介紹

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

使用別名 OLD 和 NEW 來引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容，這與其他的數(shù)據(jù)庫是相似的。現(xiàn)在Mysql觸發(fā)器還只支持行級觸發(fā)，不支持語句級觸發(fā)(Oracle支持語句級觸發(fā))。

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

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

create trigger trigger_name

before/after insert/update/delete

on tbl_name

[ for each row ] -- 行級觸發(fā)器

begin

    trigger_stmt ;

end;

需求：通過觸發(fā)器記錄 emp 表的數(shù)據(jù)變更日志 , 包含增加, 修改 , 刪除 ;

首先創(chuàng)建一張日志表 :

create table emp_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 '操作參數(shù)',

    primary key(`id`)

)engine=innodb default charset=utf8;

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

delimiter $

 

create trigger emp_logs_insert_trigger

after insert

on emp

for each row

begin

    insert into emp_logs values(null,'insert',now(),new.id,concat('插入的數(shù)據(jù)是：id=',new.id,'，name=',new.name,'，age=',new.age,'，薪資是：',new.salary));

end$

 

delimiter ;

創(chuàng)建成功之后，我們就執(zhí)行語句，往emp表中插入一條新的數(shù)據(jù)，然后查詢emp_logs表，看是否有記錄信息：

insert into emp values(null,'光明左使',23,9000);

 

--查詢emp_logs表

select * from emp_logs;

效果如下：

mysql> insert into emp values(null,'光明左使',23,9000);

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from emp_logs;

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

| id | operation | operate_time        | operate_id | operate_params                                                            |

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

|  1 | insert    | 2019-07-06 08:29:20 |          9 | 插入的數(shù)據(jù)是：id=9，name=光明左使，age=23，薪資是：9000                   |

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

1 row in set (0.00 sec)

創(chuàng)建 update 型觸發(fā)器，完成更新數(shù)據(jù)時的日志記錄

delimiter $

 

create trigger emp_logs_update_trigger

after update

on emp

for each row

begin

    insert into emp_logs values(null,'update',now(),new.id,concat('修改之前的數(shù)據(jù)是：id=',old.id,'，name=',old.name,'，age=',old.age,'，薪資是：',old.salary,'修改完成后的數(shù)據(jù)是：id=',new.id,'，name=',new.name,'，age=',new.age,'，薪資是：',new.salary));

end$

 

delimiter ;

創(chuàng)建成功之后，我們修改一條emp表中的數(shù)據(jù)，然后再次查詢emp_logs表：

update emp set name='楊逍',salary=10000 where id=9;

效果如下：

mysql> update emp set name='楊逍',salary=10000 where id=9;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> select * from emp_logs;

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

| id | operation | operate_time        | operate_id | operate_params                                                                                                                                               |

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

|  1 | insert    | 2019-07-06 08:29:20 |          9 | 插入的數(shù)據(jù)是：id=9，name=光明左使，age=23，薪資是：9000                                                                                                      |

|  2 | update    | 2019-07-06 08:38:24 |          9 | 修改之前的數(shù)據(jù)是：id=9，name=光明左使，age=23，薪資是：9000修改完成后的數(shù)據(jù)是：id=9，name=楊逍，age=23，薪資是：10000                                        |

創(chuàng)建delete 行的觸發(fā)器 , 完成刪除數(shù)據(jù)時的日志記錄

delimiter $

 

create trigger emp_logs_delete_trigger

after delete

on emp

for each row

begin

    insert into emp_logs values(null,'delete',now(),old.id,concat('刪除的數(shù)據(jù)是：id=',old.id,'，name是：',old.name,'，年齡是：',old.age,'，薪資是：',old.salary));

end$

 

delimiter ;

操作成功之后，我們刪除emp表中的一條數(shù)據(jù)，然后查看效果：

mysql> delete from emp where id=9;

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from emp_logs;

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

| id | operation | operate_time        | operate_id | operate_params                                                                                                                                               |

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

|  1 | insert    | 2019-07-06 08:29:20 |          9 | 插入的數(shù)據(jù)是：id=9，name=光明左使，age=23，薪資是：9000                                                                                                      |

|  2 | update    | 2019-07-06 08:38:24 |          9 | 修改之前的數(shù)據(jù)是：id=9，name=光明左使，age=23，薪資是：9000修改完成后的數(shù)據(jù)是：id=9，name=楊逍，age=23，薪資是：10000                                        |

|  3 | delete    | 2019-07-06 08:43:42 |          9 | 刪除的數(shù)據(jù)是：id=9，name是：楊逍，年齡是：23，薪資是：10000                                                                                                  |

刪除觸發(fā)器

drop trigger [schema_name.]trigger_name

如果沒有指定 schema_name，默認(rèn)為當(dāng)前數(shù)據(jù)庫 。

查看觸發(fā)器

可以通過執(zhí)行 SHOW TRIGGERS 命令查看觸發(fā)器的狀態(tài)、語法等信息。

mysql> SHOW TRIGGERS\G;

*************************** 1. row ***************************

             Trigger: emp_logs_insert_trigger

               Event: INSERT

               Table: emp

           Statement: begin

    insert into emp_logs values(null,'insert',now(),new.id,concat('插入的數(shù)據(jù)是：id=',new.id,'，name=',new.name,'，age=',new.age,'，薪資是：',new.salary));

 

end

              Timing: AFTER

             Created: NULL

            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

             Definer: root@localhost

character_set_client: utf8

collation_connection: utf8_general_ci

  Database Collation: utf8mb4_general_ci

*************************** 2. row ***************************

             Trigger: emp_logs_update_trigger

               Event: UPDATE

               Table: emp

           Statement: begin

    insert into emp_logs values(null,'update',now(),new.id,concat('修改之前的數(shù)據(jù)是：id=',old.id,'，name=',old.name,'，age=',old.age,'，薪資是：',old.salary,'修改完成后的數(shù)據(jù)是：id=',new.id,'，name=',new.name,'，age=',new.age,'，薪資是：',new.salary));

 

end

              Timing: AFTER

             Created: NULL

            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

             Definer: root@localhost

character_set_client: utf8

collation_connection: utf8_general_ci

  Database Collation: utf8mb4_general_ci

*************************** 3. row ***************************

             Trigger: emp_logs_delete_trigger

               Event: DELETE

               Table: emp

           Statement: begin

    insert into emp_logs values(null,'delete',now(),old.id,concat('刪除的數(shù)據(jù)是：id=',old.id,'，name是：',old.name,'，年齡是：',old.age,'，薪資是：',old.salary));

 

end

              Timing: AFTER

             Created: NULL

            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

             Definer: root@localhost

character_set_client: utf8

collation_connection: utf8_general_ci

  Database Collation: utf8mb4_general_ci

3 rows in set (0.00 sec)

鎖

以下的互斥是說事務(wù)還沒提交之前，其他事務(wù)無法提交

以下語句是查看意向鎖和行鎖的加鎖情況：
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

鎖是計算機(jī)協(xié)調(diào)多個進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制。在數(shù)據(jù)庫中，除傳統(tǒng)的計算資源（CPU、RAM、I/O）的爭用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫必須解決的一個問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素。從這個角度來說，鎖對數(shù)據(jù)庫而言顯得尤其重要，也更加復(fù)雜。

NOTE : 針對事物才有加鎖的意義。

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

1. 全局鎖：鎖定數(shù)據(jù)庫中的所有表。
2. 表級鎖：每次操作鎖住整張表。
3. 行級鎖：每次操作鎖住對應(yīng)的行數(shù)據(jù)。

全局鎖

全局鎖就是對整個數(shù)據(jù)庫實例加鎖，加鎖后整個實例就處于只讀狀態(tài)，后續(xù)的DML的寫語句，DDL語句，已經(jīng)更新操作的事務(wù)提交語句都將被阻塞。
使用場景：全庫的邏輯備份，對所有的表進(jìn)行鎖定，從而獲取一致性視圖，保證數(shù)據(jù)的完整性。

注意：一般不用全局鎖來備份，因為有不加鎖的備份方式。

在InnoDB引擎中，在備份語句中加參數(shù) --single-transaction 來完成不加鎖的一致性數(shù)據(jù)備份

# 以下命令在cmd中執(zhí)行，不需要進(jìn)mysql再執(zhí)行。（itcast是數(shù)據(jù)庫名，itcast.sql是導(dǎo)出的數(shù)據(jù)庫文件路徑）
mysqldump --single-transaction -uroot -p123456 itcast>itcast.sql

紅色箭頭是訪問失敗，綠色是訪問成功

表鎖

表級鎖，每次操作鎖住整張表。鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。應(yīng)用在MyISAM、InnoDB、BDB等存儲引擎中。

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

1. 表鎖：對于表鎖，分為兩類：

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

1. 表共享讀鎖（read lock）所有的事物都只能讀（當(dāng)前加鎖的客戶端也只能讀，不能寫），不能寫 
2. 表獨占寫鎖（write lock），對當(dāng)前加鎖的客戶端，可讀可寫，對于其他的客戶端，不可讀也不可寫。 

紅色箭頭是訪問失敗，綠色是訪問成功

2. 元數(shù)據(jù)鎖（meta data lock，MDL），MDL加鎖過程是系統(tǒng)自動控制，無需顯式使用，在訪問一張表的時候會自動加上。MDL鎖主要作用是維護(hù)表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動事務(wù)的時候，不可以對元數(shù)據(jù)進(jìn)行寫入操作。

   重點：在MySQL5.5中引入了MDL，當(dāng)對一張表進(jìn)行增刪改查的時候，加MDL讀鎖（共享）;當(dāng)對表結(jié)構(gòu)(alter)進(jìn)行變更操作的時候，加MDL寫鎖（排他）。

   • 擁有MDL讀鎖（共享）時，不能對表進(jìn)行結(jié)構(gòu)修改，但自己和其他用戶可以對表進(jìn)行增刪改查。（只與MDL寫鎖互斥）
   • 擁有MDL寫鎖（排他）時，不能對表進(jìn)行增刪改查，且其他用戶也不能對表結(jié)構(gòu)進(jìn)行修改。（與MDL讀/寫鎖都互斥）

3. 意向鎖: 為了避免DML在執(zhí)行時，加的行鎖與表鎖的沖突，在InnoDB中引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來減少表鎖的檢查。

重點：一個客戶端對某一行加上了行鎖，那么系統(tǒng)也會對其表加上一個意向鎖，當(dāng)別的客戶端來想要對其加上表鎖時，便會檢查意向鎖是否兼容，若是不兼容，便會阻塞直到意向鎖釋放。

• 意向共享鎖（IS）：由語句select ... lock in share mode 添加。（與表鎖共享鎖(read)兼容，與表鎖排它鎖(write)互斥）
• 意向排他鎖（IX）：有語句insert、update、delete、select ... for update 添加（與表鎖共享鎖(read)及排它鎖(write)都互斥。意向鎖之間不會互斥）

行鎖

行級鎖，每次操作鎖住對應(yīng)的行數(shù)據(jù)。鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度最高。應(yīng)用在InnoDB存儲引擎中（只有InnoDB才有）
InnoDB的數(shù)據(jù)是基于索引組織的，行鎖是通過對索引上的索引項加鎖來實現(xiàn)的，而不是對記錄加的鎖。對于行級鎖，主要分為以下三類：

1. 行鎖（Record Lock）：鎖定單個行記錄的鎖，防止其他事務(wù)對此行進(jìn)行update和delete。在RC（read commit ）、RR（repeat read）隔離級別下都支持。

   1. 共享鎖（S）：允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排它鎖。
   2. 排他鎖（X）：允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排他鎖。

   SQL	行鎖類型	說明
   insert	排他鎖	自動加鎖
   update	排他鎖	自動加鎖
   delete	排他鎖	自動加鎖
   select	不加任何鎖
   select　lock　in　share mode	排他鎖	需要手動在SELECT之后加LOCK IN SHARE MODE
   select　for　update	排他鎖	需要手動在SELECT之后加FOR UPDATE

2. 間隙鎖（GapLock）：鎖定索引記錄間隙（不含該記錄），確保索引記錄間隙不變，防止其他事務(wù)在這個間隙進(jìn)行insert，產(chǎn)生幻讀。在RR隔離級別下都支持。比如說 兩個臨近葉子節(jié)點為 15 23，那么間隙就是指 [15 , 23],鎖的是這個間隙。

3. 臨鍵鎖（Next-Key Lock）：行鎖和間隙鎖組合，同時鎖住數(shù)據(jù)，并鎖住數(shù)據(jù)前面的間隙Gap。在RR隔離級別下支持。

默認(rèn)情況下，InnoDB在REPEATABLE READ事務(wù)隔離級別運(yùn)行，InnoDB使用next-key 鎖（臨鍵鎖）進(jìn)行搜索和索引掃描，以防止幻讀。

1. 針對唯一索引進(jìn)行檢索時，對已存在的記錄進(jìn)行等值匹配時，將會自動優(yōu)化為行鎖。
2. InnoDB的行鎖是針對于索引加的鎖，不通過索引條件檢索數(shù)據(jù)，那么InnoDB將對表中的所有記錄加鎖，此時就會升級為表鎖。

默認(rèn)情況下，InnoDB在REPEATABLE READ事務(wù)隔離級別運(yùn)行，InnoDB使用next-key 鎖（臨鍵鎖）進(jìn)行搜索和索引掃描，以防止幻讀。

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

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

InnoDB 引擎

邏輯存儲結(jié)構(gòu)

表空間（ibd文件），一個mysql實例可以對應(yīng)多個表空間，用于存儲記錄、索引等數(shù)據(jù)。

段，分為數(shù)據(jù)段（Leaf node segment）、索引段（Non-leaf node segment）、回滾段（Rollback segment），InnoDB是索引組織表，數(shù)據(jù)段就是B+樹的葉子節(jié)點，索引段即為B+樹的非葉子節(jié)點。段用來管理多個Extent（區(qū)）。

區(qū)，表空間的單元結(jié)構(gòu)，每個區(qū)的大小為1M。默認(rèn)情況下，InnoDB存儲引擎頁大小為16K，即一個區(qū)中一共有64個連續(xù)的頁。

頁，是InnoDB存儲引擎磁盤管理的最小單元，每個頁的大小默認(rèn)為16KB。為了保證頁的連續(xù)性，InnoDB存儲引擎每從磁盤申請4-5個區(qū)。一頁包含若干行。

行，InnoDB存儲引擎數(shù)據(jù)是按進(jìn)行存放的。

架構(gòu)

Buffer Pool：緩沖池是主內(nèi)存中的一個區(qū)域，里面可以緩存磁盤上經(jīng)常操作的真實數(shù)據(jù)，在執(zhí)行增刪改查操作時，先操作緩沖池中的數(shù)據(jù)（若緩沖池沒有數(shù)據(jù)，則從磁盤加載并緩存），然后再以一定頻率刷新到磁盤，從而減少磁盤I0，加快處理速度。

磁盤架構(gòu)：

InnoDB的整個體系結(jié)構(gòu)為：

當(dāng)業(yè)務(wù)操作的時候直接操作的是內(nèi)存緩沖區(qū)，如果緩沖區(qū)當(dāng)中沒有數(shù)據(jù)，則會從磁盤中加載到緩沖區(qū)，增刪改查都是在緩沖區(qū)的，后臺線程以一定的速率刷新到磁盤。

事務(wù)原理

事務(wù)是一組操作的集合，它是一個不可分割的工作單位，事務(wù)會把所有的操作作為一個整體一起向系統(tǒng)提交或撤銷操作請求，即這些操作要么同時成功，要么同時敗。具有ACID四大特征。

原子性，一致性，持久性這三大特性由 redo log 和 undo log 日志來保證的。
隔離性 是由鎖機(jī)制和MVCC保證的。

redo log:

重做日志，記錄的是事務(wù)提交時數(shù)據(jù)頁的物理修改，是用來實現(xiàn)事務(wù)的持久性。
該日志文件由兩部分組成：重做日志緩沖（redo log buffer）以及重做日志文件（redo log file），前者是在內(nèi)存中，后者在磁盤中。當(dāng)事務(wù)提交之后會把所有修改信息都存到該日志文件中，用于在刷新臟頁到磁盤，發(fā)生錯誤時，進(jìn)行數(shù)據(jù)恢復(fù)使用。

前置知識：每次增刪改都是將操作先放入Buffer Pool和redolog buffer中，然后再寫入磁盤。

個人理解： 事物每次提交的時候都會將數(shù)據(jù)刷到redo log中而不是直接將buffer pool中的數(shù)據(jù)直接刷到磁盤中（ibd文件中），是因為redo log 是順序?qū)?，性能處理的夠快，而Buffer Pool直接刷到ibd中，是隨機(jī)寫，性能慢。所以臟頁是在下一次讀的時候，或者后臺線程采用一定的機(jī)制進(jìn)行刷盤到ibd中。

最新個人理解：當(dāng)Buffer Pool可以正常同步到磁盤文件時，就不用redolog日志。但假如Buffer Pool寫入磁盤時發(fā)生錯誤（宕機(jī)），就會通過Redolog buffer將未同步的臟數(shù)據(jù)同步到磁盤。

注意：

1. 磁盤中redolog日志ibd會定期刪除掉，因為他們只用保證短期（宕機(jī)期間）的數(shù)據(jù)操作能夠持久化就行。
2. 磁盤中的redolog日志ibd是有兩份的，循環(huán)寫。

undo log:
回滾日志，用于記錄數(shù)據(jù)被修改前的信息，作用包含兩個：提供回滾和MVCC（多版本并發(fā)控制）。
undo log和redo log記錄物理日志不一樣，它是邏輯日志，記錄逆操作?？梢哉J(rèn)為當(dāng)delete一條記錄，undo log中會記錄一條對應(yīng)的insert記錄，反之亦然，當(dāng)update一條記錄時，它記錄一條對應(yīng)相反的update記錄。當(dāng)執(zhí)行rollback時，就可以從undo log中的邏輯記錄讀取到相應(yīng)的內(nèi)容并進(jìn)行回滾。

Undo log銷毀：undo log在事務(wù)執(zhí)行時產(chǎn)生，事務(wù)提交時，并不會立即刪除undo log，因為這些日志可能還用于MVCC。
Undo log存儲：undo log采用段的方式進(jìn)行管理和記錄，存放在前面介紹的rollback segment回滾段中，內(nèi)部包含1024個undo log segment。

自己Process on上的總結(jié)

面試題：

1. redolog和undolog的區(qū)別是什么？

   redo log：記錄的是數(shù)據(jù)頁的物理變化，服務(wù)宕機(jī)可用來同步數(shù)據(jù)。
   undo log：記錄的是邏輯日志，當(dāng)事務(wù)滾時，通過逆操作恢復(fù)原來的數(shù)據(jù)。

   區(qū)別：redo log保證了事務(wù)的持久性，undolog保證了事務(wù)的原子性和一致性

   答：

   1）其中redo log日志記錄的是數(shù)據(jù)頁的物理變化，服務(wù)宕機(jī)可用來同步數(shù)據(jù)。而undo log不同，它主要記錄的是邏輯日志，當(dāng)事務(wù)回滾時，通過逆操作恢復(fù)原來的數(shù)據(jù)，比如我們刪除一條數(shù)據(jù)的時候，就會在undo log日志文件中新增一條delete語句，如果發(fā)生回滾就執(zhí)行逆操作;
   2）redo log保證了事務(wù)的持久性，undo log保證了事務(wù)的原子性和一致性

MVCC

當(dāng)前讀:

讀取的是記錄的最新版本，讀取時還要保證其他并發(fā)事務(wù)不能修改當(dāng)前記錄，會對讀取的記錄進(jìn)行加鎖。對于我們?nèi)粘５牟僮鳎纾?/p>

• select...lock in share mode（共享鎖）。
• select..…for update、update、insert、delete（排他鎖）都是一種當(dāng)前讀。

快照讀:

簡單的select（不加鎖）就是快照讀，快照讀，讀取的是記錄數(shù)據(jù)的可見版本，有可能是歷史數(shù)據(jù)，不加鎖，是非阻塞讀。

• Read Committed：每次select，都生成一個快照讀。
• Repeatable Read：開啟事務(wù)后第一個select語句才是快照讀的地方。
• Serializable：快照讀會退化為當(dāng)前讀。

MVCC:

這個原理去看自己的process on 上的思維導(dǎo)圖：

全稱Multi-Version Concurrency Control，多版本并發(fā)控制。指維護(hù)一個數(shù)據(jù)的多個版本，使得讀寫操作沒有沖突，快照讀為MySQL實現(xiàn)MVCC提供了一個非阻塞讀功能。MVCC的具體實現(xiàn)，還需要依賴于數(shù)據(jù)庫記錄中的三個隱式字段、undo log日志、readView。

MVCC 實現(xiàn)原理:

有三個隱藏的字段:

undo log回滾日志，在insert、update、delete的時候產(chǎn)生的便于數(shù)據(jù)回滾的日志。
當(dāng)insert的時候，產(chǎn)生的undo log日志只在回滾時需要，在事務(wù)提交后，可被立即刪除。
而update、delete的時候，產(chǎn)生的undo log日志不僅在回滾時需要，在快照讀時也需要，不會立即被刪除。

undo log 版本鏈：

undo log日志會記錄原來的版本的數(shù)據(jù)，因為是通過undo log 日志進(jìn)行回滾的。

如何確定返回哪一個版本 這是由read view決定返回 undo log 中的哪一個版本。

RC隔離級別下，在事務(wù)中每一次執(zhí)行快照讀時生成ReadView。
RR隔離級別下，在事務(wù)中第一次執(zhí)行快照讀時生成ReadView，后續(xù)會復(fù)用。

https://www.bilibili.com/video/BV1Kr4y1i7ru?p=145&spm_id_from=pageDriver&vd_source=bbc04b831b54029788a178a7c2e9ae20

MVCC 靠 隱藏字段 , undo log 版本鏈 , read view 實現(xiàn)的。

• 原子性-undo log
• 持久性-redo log
• 一致性-undo log + redo log
• 隔離性-鎖 + MVCC

數(shù)據(jù)類型

整型

無符號在數(shù)據(jù)類型后加 unsigned 關(guān)鍵字。

浮點型

日期和時間

字符串

二進(jìn)制類型

權(quán)限一覽表

具體權(quán)限的作用詳見官方文檔

GRANT 和 REVOKE 允許的靜態(tài)權(quán)限

GRANT 和 REVOKE 允許的動態(tài)權(quán)限文章來源地址http://www.zghlxwxcb.cn/news/detail-825171.html

圖形化界面工具

• Workbench(免費(fèi)): http://dev.mysql.com/downloads/workbench/
• navicat(收費(fèi)，試用版30天): https://www.navicat.com/en/download/navicat-for-mysql
• Sequel Pro(開源免費(fèi)，僅支持Mac OS): http://www.sequelpro.com/
• HeidiSQL(免費(fèi)): http://www.heidisql.com/
• phpMyAdmin(免費(fèi)): https://www.phpmyadmin.net/
• SQLyog: https://sqlyog.en.softonic.com/

到了這里，關(guān)于MySQL數(shù)據(jù)庫精選（從入門使用到底層結(jié)構(gòu)）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！