本篇摘錄自黑馬程序員的B站教學視頻，由本人學習視頻內容後總結並提取摘要製作而成的簡要筆記。
本筆記只記錄到進階篇（大部分），剩下的進階篇以及運維篇由於本人職業生涯沒有用武之地，所以沒有進行學習。

黑馬程序員黑馬程序員 MySQL數據庫入門到精通，從mysql安裝到mysql高級、mysql優化全囊括

在此非常感謝兩位熱愛學習的小夥伴的鼎力相助，幫助我們完成了所有的章節內容，使得本篇超長筆記得以完結。至此，整個MySQL筆記從入門到高級的所有內容都已編寫完畢。

特別感謝：

• wlh (wen-lh) - Gitee.com
• B站同學：@守心-人

基礎篇

通用語法及分類

• DDL: 數據定義語言，用來定義數據庫對象（數據庫、表、字段）
• DML: 數據操作語言，用來對數據庫表中的數據進行增刪改
• DQL: 數據查詢語言，用來查詢數據庫中表的記錄
• DCL: 數據控制語言，用來創建數據庫用户、控制數據庫的控制權限

DDL（數據定義語言）

數據定義語言

數據庫操作

查詢所有數據庫：
SHOW DATABASES;
查詢當前數據庫：
SELECT DATABASE();
創建數據庫：
CREATE DATABASE [ IF NOT EXISTS ] 數據庫名 [ DEFAULT CHARSET 字符集] [COLLATE 排序規則 ];
刪除數據庫：
DROP DATABASE [ IF EXISTS ] 數據庫名;
使用數據庫：
USE 數據庫名;

注意事項

• UTF8字符集長度為3字節，有些符號佔4字節，所以推薦用utf8mb4字符集

表操作

查詢當前數據庫所有表：
SHOW TABLES;
查詢表結構：
DESC 表名;
查詢指定表的建表語句：
SHOW CREATE TABLE 表名;

創建表：

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 '暱稱';

修改數據類型：
ALTER TABLE 表名 MODIFY 字段名 新數據類型(長度);
修改字段名和字段類型：
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] 表名;
刪除表，並重新創建該表：
TRUNCATE TABLE 表名;

DML（數據操作語言）

添加數據

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

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

注意事項

• 字符串和日期類型數據應該包含在引號中
• 插入的數據大小應該在字段的規定範圍內

更新和刪除數據

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

刪除數據：
DELETE FROM 表名 [ WHERE 條件 ];

DQL（數據查詢語言）

語法：

SELECT
	字段列表
FROM
	表名字段
WHERE
	條件列表
GROUP BY
	分組字段列表
HAVING
	分組後的條件列表
ORDER BY
	排序字段列表
LIMIT
	分頁參數

基礎查詢

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

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

去除重複記錄：
SELECT DISTINCT 字段列表 FROM 表名;

轉義：
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';

聚合查詢（聚合函數）

常見聚合函數：

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

分組查詢

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

where 和 having 的區別：

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

例子：

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

注意事項

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

排序查詢

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

排序方式：

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

例子：

-- 根據年齡升序排序
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age;
-- 兩字段排序，根據年齡升序排序，入職時間降序排序
SELECT * FROM employee ORDER BY age ASC, entrydate DESC;

注意事項

如果是多字段排序，當第一個字段值相同時，才會根據第二個字段進行排序

分頁查詢

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

例子：

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

注意事項

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

DQL執行順序

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

DCL

管理用户

查詢用户：

USE mysql;
SELECT * FROM user;

創建用户:
CREATE USER '用户名'@'主機名' IDENTIFIED BY '密碼';

修改用户密碼：
ALTER USER '用户名'@'主機名' IDENTIFIED WITH mysql_native_password BY '新密碼';

刪除用户：
DROP USER '用户名'@'主機名';

例子：

-- 創建用户test，只能在當前主機localhost訪問
create user 'test'@'localhost' identified by '123456';
-- 創建用户test，能在任意主機訪問
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';

注意事項

• 主機名可以使用 % 通配

權限控制

常用權限：

更多權限請看權限一覽表

查詢權限：
SHOW GRANTS FOR '用户名'@'主機名';

授予權限：
GRANT 權限列表 ON 數據庫名.表名 TO '用户名'@'主機名';

撤銷權限：
REVOKE 權限列表 ON 數據庫名.表名 FROM '用户名'@'主機名';

注意事項

• 多個權限用逗號分隔
• 授權時，數據庫名和表名可以用 * 進行通配，代表所有

函數

• 字符串函數
• 數值函數
• 日期函數
• 流程函數

字符串函數

常用函數：

使用示例：

-- 拼接
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);

數值函數

常見函數：

日期函數

常用函數：

例子：

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

流程函數

常用函數：

例子：

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

約束

分類：

約束是作用於表中字段上的，可以再創建表/修改表的時候添加約束。

常用約束

例子：

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)
);

外鍵約束

添加外鍵：

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 行為;

多表查詢

多表關係

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

一對多

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

多對多

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

一對一

案例：用户與用户詳情
關係：一對一關係，多用於單表拆分，將一張表的基礎字段放在一張表中，其他詳情字段放在另一張表中，以提升操作效率
實現：在任意一方加入外鍵，關聯另外一方的主鍵，並且設置外鍵為唯一的（UNIQUE）

查詢

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

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

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

內連接查詢

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

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

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

顯式性能比隱式高

例子：

-- 查詢員工姓名，及關聯的部門的名稱
-- 隱式
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;

外連接查詢

左外連接：
查詢左表所有數據，以及兩張表交集部分數據
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件 ...;
相當於查詢表1的所有數據，包含表1和表2交集部分數據

右外連接：
查詢右表所有數據，以及兩張表交集部分數據
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;

左連接可以查詢到沒有dept的employee，右連接可以查詢到沒有employee的dept

自連接查詢

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

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

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

例子：

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

聯合查詢 union, union all

把多次查詢的結果合併，形成一個新的查詢集

語法：

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

注意事項

• UNION ALL 會有重複結果，UNION 不會
• 聯合查詢比使用or效率高，不會使索引失效

子查詢

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

根據子查詢結果可以分為：

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

根據子查詢位置可分為：

• WHERE 之後
• FROM 之後
• SELECT 之後

標量子查詢

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

例子：

-- 查詢銷售部所有員工
select id from dept where name = '銷售部';
-- 根據銷售部部門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');

列子查詢

返回的結果是一列（可以是多行）。

常用操作符：

例子：

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

行子查詢

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

例子：

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

表子查詢

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

例子：

-- 查詢與xxx1，xxx2的職位和薪資相同的員工
select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2');
-- 查詢入職日期是2006-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;

事務

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

基本操作：

-- 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 = '李四';

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

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

操作方式二：

開啓事務：
START TRANSACTION 或 BEGIN TRANSACTION;
提交事務：
COMMIT;
回滾事務：
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;

四大特性ACID

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

併發事務

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

併發事務隔離級別：

• √表示在當前隔離級別下該問題會出現
• Serializable 性能最低；Read uncommitted 性能最高，數據安全性最差

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

進階篇

存儲引擎

MySQL體系結構：

存儲引擎就是存儲數據、建立索引、更新/查詢數據等技術的實現方式。存儲引擎是基於表而不是基於庫的，所以存儲引擎也可以被稱為表引擎。
默認存儲引擎是InnoDB。

相關操作：

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

InnoDB

InnoDB 是一種兼顧高可靠性和高性能的通用存儲引擎，在 MySQL 5.5 之後，InnoDB 是默認的 MySQL 引擎

特點：

• DML 操作遵循 ACID 模型，支持事務
• 行級鎖，提高併發訪問性能
• 支持外鍵約束，保證數據的完整性和正確性

文件：

• xxx.ibd: xxx代表表名，InnoDB 引擎的每張表都會對應這樣一個表空間文件，存儲該表的表結構（frm、sdi）、數據和索引。

參數：innodb_file_per_table，決定多張表共享一個表空間還是每張表對應一個表空間

知識點：

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

從idb文件提取表結構數據：
（在cmd運行）
ibd2sdi xxx.ibd

InnoDB 邏輯存儲結構：

MyISAM

MyISAM 是 MySQL 早期的默認存儲引擎。

特點：

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

文件：

• xxx.sdi: 存儲表結構信息
• xxx.MYD: 存儲數據
• xxx.MYI: 存儲索引

Memory

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

特點：

• 存放在內存中，速度快
• hash索引（默認）

文件：

• xxx.sdi: 存儲表結構信息

存儲引擎特點

存儲引擎的選擇

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

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

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

性能分析

查看執行頻次

查看當前數據庫的 INSERT, UPDATE, DELETE, SELECT 訪問頻次：
SHOW GLOBAL STATUS LIKE 'Com_______'; 或者 SHOW SESSION STATUS LIKE 'Com_______';
例：show global status like 'Com_______'

慢查詢日誌

慢查詢日誌記錄了所有執行時間超過指定參數（long_query_time，單位：秒，默認10秒）的所有SQL語句的日誌。
MySQL的慢查詢日誌默認沒有開啓，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
# 開啓慢查詢日誌開關
slow_query_log=1
# 設置慢查詢日誌的時間為2秒，SQL語句執行時間超過2秒，就會視為慢查詢，記錄慢查詢日誌
long_query_time=2
更改後記得重啓MySQL服務，日誌文件位置：/var/lib/mysql/localhost-slow.log

查看慢查詢日誌開關狀態：
show variables like 'slow_query_log';

profile

show profile 能在做SQL優化時幫我們瞭解時間都耗費在哪裏。通過 have_profiling 參數，能看到當前 MySQL 是否支持 profile 操作：
SELECT @@have_profiling;
profiling 默認關閉，可以通過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 如何執行 SELECT 語句的信息，包括在 SELECT 語句執行過程中表如何連接和連接的順序。
語法：
# 直接在select語句之前加上關鍵字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 HWERE 條件;

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的值越大越好

索引

索引是幫助 MySQL 高效獲取數據的數據結構（有序）。在數據之外，數據庫系統還維護着滿足特定查找算法的數據結構，這些數據結構以某種方式引用（指向）數據，這樣就可以在這些數據結構上實現高級查詢算法，這種數據結構就是索引。

優缺點：

優點：

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

缺點：

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

索引結構

B-Tree

二叉樹的缺點可以用紅黑樹來解決：

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

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

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

B+Tree

結構圖：

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

與 B-Tree 的區別：

• 所有的數據都會出現在葉子節點
• 葉子節點形成一個單向鏈表

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

Hash

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

特點：

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

存儲引擎支持：

• Memory
• InnoDB: 具有自適應hash功能，hash索引是存儲引擎根據 B+Tree 索引在指定條件下自動構建的

面試題

1. 為什麼 InnoDB 存儲引擎選擇使用 B+Tree 索引結構？

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

索引分類

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

演示圖：

聚集索引選取規則：

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

思考題

1. 以下 SQL 語句，哪個執行效率高？為什麼？

select * from user where id = 10;
select * from user where name = 'Arm';
-- 備註：id為主鍵，name字段創建的有索引

答：第一條語句，因為第二條需要回表查詢，相當於兩個步驟。

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

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

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

另外，如果有成千上萬的數據，那麼就要考慮分表，涉及運維篇知識。

語法

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

查看索引：
SHOW INDEX FROM table_name;

刪除索引：
DROP INDEX index_name ON table_name;

案例：

-- 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_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;

使用規則

最左前綴法則

如果索引關聯了多列（聯合索引），要遵守最左前綴法則，最左前綴法則指的是查詢從索引的最左列開始，並且不跳過索引中的列。
如果跳躍某一列，索引將部分失效（後面的字段索引失效）。

聯合索引中，出現範圍查詢（<, >），範圍查詢右側的列索引失效。可以用>=或者<=來規避索引失效問題。

索引失效情況

1. 在索引列上進行運算操作，索引將失效。如：explain select * from tb_user where substring(phone, 10, 2) = '15';
2. 字符串類型字段使用時，不加引號，索引將失效。如：explain select * from tb_user where phone = 17799990015;，此處phone的值沒有加引號
3. 模糊查詢中，如果僅僅是尾部模糊匹配，索引不會是失效；如果是頭部模糊匹配，索引失效。如：explain select * from tb_user where profession like '%工程';，前後都有 % 也會失效。
4. 用 or 分割開的條件，如果 or 其中一個條件的列沒有索引，那麼涉及的索引都不會被用到。
5. 如果 MySQL 評估使用索引比全表更慢，則不使用索引。

SQL 提示

是優化數據庫的一個重要手段，簡單來説，就是在SQL語句中加入一些人為的提示來達到優化操作的目的。

例如，使用索引：
explain select * from tb_user use index(idx_user_pro) where profession="軟件工程";
不使用哪個索引：
explain select * from tb_user ignore index(idx_user_pro) where profession="軟件工程";
必須使用哪個索引：
explain select * from tb_user force index(idx_user_pro) where profession="軟件工程";

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

覆蓋索引&回表查詢

儘量使用覆蓋索引（查詢使用了索引，並且需要返回的列，在該索引中已經全部能找到），減少 select *。

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

如果在聚集索引中直接能找到對應的行，則直接返回行數據，只需要一次查詢，哪怕是select *；如果在輔助索引中找聚集索引，如select id, name from xxx where name='xxx';，也只需要通過輔助索引(name)查找到對應的id，返回name和name索引對應的id即可，只需要一次查詢；如果是通過輔助索引查找其他字段，則需要回表查詢，如select id, name, gender from xxx where name='xxx';

所以儘量不要用select *，容易出現回表查詢，降低效率，除非有聯合索引包含了所有字段

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

解：給username和password字段建立聯合索引，則不需要回表查詢，直接覆蓋索引

前綴索引

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

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

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

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

單列索引&聯合索引

單列索引：即一個索引只包含單個列
聯合索引：即一個索引包含了多個列
在業務場景中，如果存在多個查詢條件，考慮針對於查詢字段建立索引時，建議建立聯合索引，而非單列索引。

單列索引情況：
explain select id, phone, name from tb_user where phone = '17799990010' and name = '韓信';
這句只會用到phone索引字段

注意事項

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

設計原則

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

SQL 優化

插入數據

普通插入：

1. 採用批量插入（一次插入的數據不建議超過1000條）
2. 手動提交事務
3. 主鍵順序插入

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

# 客户端連接服務端時，加上參數 --local-infile（這一行在bash/cmd界面輸入）
mysql --local-infile -u root -p
# 設置全局參數local_infile為1，開啓從本地加載文件導入數據的開關
set global local_infile = 1;
select @@local_infile;
# 執行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：合併頁的閾值，可以自己設置，在創建表或創建索引時指定

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

主鍵設計原則：

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

order by優化

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

如果order by字段全部使用升序排序或者降序排序，則都會走索引，但是如果一個字段升序排序，另一個字段降序排序，則不會走索引，explain的extra信息顯示的是Using index, Using filesort，如果要優化掉Using filesort，則需要另外再創建一個索引，如：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;會全部走索引

總結：

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

group by優化

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

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

limit優化

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

例如：

-- 此語句耗時很長
select * from tb_sku limit 9000000, 10;
-- 通過覆蓋索引加快速度，直接通過主鍵索引進行排序及查詢
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);
-- 通過連表查詢即可實現第一句的效果，並且能達到第二句的速度
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優化

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

count的幾種用法：

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

各種用法的性能：

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

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

update優化（避免行鎖升級為表鎖）

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

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

視圖/存儲過程/觸發器

視圖

視圖(View)是一種虛擬存在的表。視圖中的數據並不在數據庫中實際存在，行和列數據來自定義視圖的查詢中使用的表，並且是在使用視圖時動態生成的。

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

語法

創建視圖：
CREATE [OR REPLACE] VIEW 視圖名稱(列名列表)】AS SELECT語句[WITH[CASCADED|LOCAL] CHECK OPTION]

查詢視圖：
查看創建視圖語句：SHOW CRETE VIEW 視圖名稱;
查看視圖數據：查看視圖數據:SELECT*FROM 視圖名稱…;

修改視圖：
方式一：
CREATE [OR REPLACE]VIEW 視圖名稱(列名列表)AS SELECT語句[WITH[CASCADEDLLOCAL] CHECK OPTION
方式二：
ALTER VEW 視圖名稱(列名列表)AS SELECT語句[WITH[CASCADED|LOCAL]CHECK OPTION]

刪除視圖：
DROP VIEW [IF EXISTS]視圖名稱[,視圖名稱]

-- 創建視圖
create or replace view stu_v_1 as select id, name from student where id <= 10;

-- 查詢視圖
show create view stu_v_1;
select * from stu_v_1;

-- 修改視圖
create or replace view stu_v_1 as select id, name, no from student where id <= 10;
alter view stu_v_1 as select id, name from student where id <= 10;

-- 刪除視圖
drop view if exists stu_v_1;/*  */

檢查選項

視圖的檢查選項：

當使用WITH CHECK OPTION子句創建視圖時，MySOL會通過視圖檢查正在更改的每個行，例如 插入，更新，刪除，以使其符合視圖的定義。MVSOL允許基於另一個視圖創建視圖，它還會檢查依賴視圖中的規則以保持一致性。

為了確定檢查的範圍，mysql 提供了兩個選項:CASCADED 和 LOCAL，默認值為CASCADED。

cascaded：在對創建時含有該字段的視圖，插入數據時，該視圖依賴的視圖都會加上檢查，需要所有條件都滿足才能夠插入成功。

local：在對創建時含有該字段的視圖，插入數據時，對於該視圖依賴的視圖中含有檢查語句的條件進行檢查判斷。

更新及作用

視圖的更新：

要使視圖可更新，視圖中的行與基礎表中的行之間必須存在一對一的關係。

如果視圖包含以下任何一項，則該視圖不可更新：

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

作用：

• 簡單
  視圖不僅可以簡化用户對數據的理解，也可以簡化他們的操作。那些被經常使用的查詢可以被定義為視圖，從而使得用户不必為以後的操作每次指定全部的條件。
• 安全
  數據庫可以授權，但不能授權到數據庫特定行和特定的列上。通過視圖用户只能查詢和修改他們所能見到的數據。
• 數據獨立
  視圖可幫助用户屏蔽真實表結構變化帶來的影響。

案例

-- 1.為了保證數據庫表的安全性，開發人員在操作tb_user表時，只能看到的用户的基本字段，屏蔽手機號和郵箱兩個字段。
create view tb user view as select id,name,profession, age,gender,status,createtime from tb_user;
select *from tb user view;

-- 2.查詢每個學生所選修的課程（三張表聯查），這個功能在很多的業務中都有使用到，為了簡化操作，定義一個視圖。
create view tb_stu_course_view 
select s.name student_name, s.no student_no, c.name course_name 
from student s, stuent_course sc, course c 
where s.id = sc.studentid and sc.courseid = c.id;

-- 以後每次只需要進行查詢視圖即可
select * from tb_stu_course_view;

存儲過程

存儲過程其實就類似 java，c 這種語言，這一部分可以通過文檔快速學習，不懂的再回過頭看視頻。

存儲過程是事先經過編譯並存儲在數據庫中的一段 SQL語句的集合，調用存儲過程可以簡化應用開發人員的很多工作，減少數據在數據庫和應用服務器之間的傳輸，對於提高數據處理的效率是有好處的。

存儲過程思想上很簡單，就是數據庫 SOL語言層面的代碼封裝與重用。

特點：

• 封裝，複用
• 可以接收參數，也可以返回數據
• 減少網絡交互，效率提升

基本語法

查看視圖數據:SELECT*FROM 視圖名稱…;

查看：

SELECT* FROM INFORMATION SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='xx';--查詢數據庫的存儲過程及狀態信息
SHOW CREATE PROCEDURE 存儲過程名稱;--查詢某個存儲過程的定義

刪除：

DROP PROCEDURE [IF EXISTS]存儲過程名稱;

案例：

-- 存儲過程基本語法
-- 創建
create procedure p1()
begin
  select count(*)from student;
end;

-- 調用
call p1();

-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
show create procedure p1;

-- 刪除
drop procedure if exists p1;

變量

系統變量

系統變量 是MySQL服務器提供，不是用户定義的，屬於服務器層面。分為全局變量(GLOBAL)、會話變量(SESSION)。

查看系統變量

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

-- 變量：系統變量
-- 查看系統變量
show session variables;
show session variables like 'auto%';
show glabal variables like 'auto%';
select @@global.autocommit;

-- 設置系統變量
set session autocommit = 1;
insert intto course(id, name) values (6, 'ES');
set global auto commit = 0;

注意：

• 如果沒有指定 session / global，默認 session，會話變量
• myesql 服務器重啓之後，所設置的全局參數會失效，要想不失效，需要更改/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;

案例：

-- 變量：用户變量
-- 賦值
set @myname = 'itcast';
set @myage := 10;

select @mycolor := 'red';
select count(*) into @mycount from tb_user;

-- 使用
select @myname, @myage, @mycolor, @mycount;

select @abc; -- 輸出為NULL

注意：

用户定義的變量無需對其進行聲明或者初始化，只不過獲取到的值為 NULL。

局部變量

局部變量 是根據需要定義的在局部生效的變量，訪問之前，需要DECLARE聲明。可用作存儲過程內的局部變量和輸入參數，局部變量的範圍是在其內聲明的BEGIN .. END塊。

聲明：

DECLARE 變量名 變量類型 [DEFAULT..];

變量類型就是數據庫字段類型:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

賦值：

SET 變量名=值;

SET 變量名:=值;

SELECT 字段名 INTO 變量名 FROM 表名 ...;

案例：

-- 變量：局部變量
-- 聲明 - declare
-- 賦值 -
create procedure p2()
begin
  declare stu_count int default 0;
  select count(*) into stu_count from student;
  select stu_count;
end;

call p2();

if 判斷

語法：

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

案例：

create procedure p3()
begin
  declare score int default 58;
  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 :

案例：

-- 根據傳入(in)參數score，判定當前分數對應的分數等級，並返回(out)
-- score >= 85分，等級為優秀。
-- score >= 60分 且 score < 85分，等級為及格
-- score < 60分，等級為不及格。
create procedure p3(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;
  select result;
end;

-- 將傳入的200分制的分數，進行換算，換算成百分制，然後返回分數 --> inout
create procedure p5(inout score double)
begin
  set score := score * 0.5;
end;

set @score = 198;
call p5(score);
select @score;

case

語法一：

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

語法二：

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

案例：

-- case
-- 根據傳入的月份，判定月份所屬的季節(要求採用case結構)
-- 1-3月份，為第一季度
-- 4-6月份，為第二季度
-- 7-9月份，為第三季度
-- 10-12月份，為第四季度

create procedure p6(in month int)
begin 
  declare result varchar(10);
  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 := '第四季度';
    else
      set result := '非法參數';
  end case;

  select concat('你輸入的月份為：', month, '，所屬季度為：', result);
end;

循環

while

while 循環是有條件的循環控制語句。滿足條件後，再執行循環體中的SQL語句。

語法：

#先判定條件，如果條件為true，則執行邏輯，否則，不執行邏輯
WHILE 條件 DO
  SOL邏輯...
END WHILE;

案例：

-- while計算從1累加到 n 的值，n 為傳入的參數值。
-- A.定義局部變量，記錄累加之後的值;
-- B.每循環一次，就會對 n 進行減1，如果 n 減到0，則退出循環

create procedure p7(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;
call p7( n: 100);

repeat

repeat是有條件的循環控制語句,當滿足條件的時候退出循環。

與 while 區別：

1. 先進行循環一次再判斷。相當於 c 語言中的 do while();
2. 滿足條件則退出

語法：

#先執行一次邏輯，然後判定邏輯是否滿足，如果滿足，則退出。如果不滿足，則繼續下一次循環
REPEAT
  SOL邏輯.
  UNTIL 條件
END REPEAT;

案例：

-- while計算從1累加到 n 的值，n 為傳入的參數值。
-- A.定義局部變量，記錄累加之後的值;
-- B.每循環一次，就會對 n 進行減1，如果 n 減到0，則退出循環

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

call p8( n: 10);
call p8( n: 100);

loop

LOOP 實現簡單的循環，如果不在SQL邏輯中增加退出循環的條件，可以用其來實現簡單的死循環。LOOP可以配合一下兩個語句使用。

1. LEAVE:配合循環使用，退出循環。
2. ITERATE:必須用在循環中，作用是跳過當前循環剩下的語句，直接進入下一次循環。

[begin label:] LOOP
  SQL邏輯..
END LOOP [end label];

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

案例：

-- loop 計算從1到n之間的偶數累加的值，n為傳入的參數值。
-- A.定義局部變量，記錄累加之後的值;
-- B.每循環一次，就會勸進行-1，如果n減到0，則退出循環。------> leave xx
-- C.如果當次累加的數據是奇數，則直接進入下一次循壞。-------> iterate xx

create procedure p10(in n int)
begin 
  declare total int defatult 0;

  sum: loop
    if n <= 10 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

遊標(CURSOR)是用來存儲查詢結果集的數據類型,在存儲過程和函數中可以使用遊標對結果集進行循環的處理。遊標的使用包括遊標的聲明、OPEN、FETCH和 CLOSE，其語法分別如下。

通俗點講：類似於 c 語言中的結構體，java 中的實體類。

聲明遊標

DECLARE 遊標名稱 CURSOR FOR 查詢語句;

打開遊標：

OPEN 遊標名稱;

獲取遊標記錄：

FETCH 遊標名稱 INTO 變量[,變量];

關閉遊標：

CLOSE 遊標名稱;

案例：

-- 遊標
-- 根據傳入的參數uage，來查詢用户表tb_user 中， 所有的用户年齡小於uage的用户姓名（name)和專業（profession），
-- 並將用户的姓名和專業插入到所創建的一張新表(id,name,profession)中。
-- 邏輯:
-- A.聲明遊標，存儲查詢結果集-
-- B.準備:創建表結構
-- C.開啓遊標-
-- D.獲取遊標中的記錄
-- E.插入數據到新表中-
-- F.關閉遊標

create procedure p11(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;

  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;

條件處理程序-handler

條件處理程序(Handler)可以用來定義在流程控制結構執行過程中遇到問題時相應的處理步驟。

語法：

DECLARE handler action HANDLERFOR condition value l, condition value.... statement;
handler action
  CONTINUE: 繼續執行當前程序
  EXIT: 終止執行當前程序
condition value
  SOLSTATE sqlstate_value:狀態碼，如 02000
  SQLWARNING:所有以01開頭的SQLSTATE代碼的簡寫
  NOT FOUND:所有以02開頭的SOLSTATE代碼的簡寫
  SOLEXCEPTION:所有沒有被SOLWARNING 或 NOT FOUND捕獲的SOLSTATE代碼的簡寫

案例：

create procedure p11(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;
  
  -- 監控到02000的狀態碼後，關閉遊標後執行exit退出操作。
  declare exit handler for not found 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;

存儲函數：

存儲函數是有返回值的存儲過程，存儲函數的參數只能是IN類型的。

存儲函數用的較少，能夠使用存儲函數的地方都可以用存儲過程替換。

語法：

CREATE FUNCTION 存儲函數名稱([ 參數列表 ])
RETURNS type [characteristic ...]
BEGIN
  -- SQL語句
  RETURN ...;
END ;
characteristic説明:
· DETERMINISTIC:相同的輸入參數總是產生相同的結果
· NO SQL:不包含 SQL語句。
· READS SOL DATA:包含讀取數據的語句，但不包含寫入數據的語句,

案例：

create function fun1(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 來引用觸發器中發生變化的記錄內容，這與其他的數據庫是相似的。現在觸發器還只支持行級觸發，不支持語句級觸發。

語法：

創建：

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，默認為當前數據庫

案例：

-- 插入數據觸發器
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;

-- 查看
show triggers;

-- 刪除
drop trigger tb_user_insert_trigger;

-- 插入數據tb_user
insert into tb_user(id, name, phtone, email, profession, age, gender, status, createtime) values(25, '二皇子', '1880901212', 'erhuangzi@163.com', '軟件工程', 23, '1', '1'1, now());

-- 修改數據觸發器
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;

update tb_user set age = 32 where id = 23;
update tb_user set age = 32 where id <= 5; -- 觸發器為行級觸發器，所以更改幾行數據則出發幾次，該語句出發5次

-- 刪除數據觸發器
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, 'insert', now(), old.id, 
   concat('刪除之前的數據：id=', new.id, ',name=', old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));
end;

delete from tb_user where id = 26;

鎖

介紹：

鎖是計算機協調多個進程或線程併發訪問某一資源的機制。在數據庫中，除傳統的計算資源(CPU、RAM、I/0)的爭用以外，數據也是一種供許多用户共享的資源。如何保證數據併發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖衝突也是影響數據庫併發訪問性能的一個重要因素。從這個角度來説，鎖對數據庫而言顯得尤其重要，也更加複雜。

分類：

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

1. 全局鎖：鎖定數據庫中的所有表。
2. 表級鎖：每次操作鎖住整張表。
3. 行級鎖：每次操作鎖住對應的行數據。

全局鎖

介紹：

全局鎖就是對整個數據庫實例加鎖，加鎖後整個實例就處於只讀狀態，後續的DML的寫語句，DDL語句，已經更新操作的事務提交語句都將被阻塞。

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

基本操作：

使用全局鎖：flush tables with read lock
釋放全局鎖：unlock tables

演示圖：

特點：

數據庫中加全局鎖，是一個比較重的操作，存在以下問題:

1. 如果在主庫上備份，那麼在備份期間都不能執行更新，業務基本上就得停擺。
2. 如果在從庫上備份，那麼在備份期間從庫不能執行主庫同步過來的二進制日誌(binlog)，會導致主從延遲。（該結構會在後續主從複製講解）

解決方法：

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

mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql（只適用於支持「可重複讀隔離級別的事務」的存儲引擎）

原理補充：通過加上這個參數，確保了在備份開始時創建一個一致性的快照，通過啓動一個新的事務來實現這一點。（該事務的隔離級別是Repeatable Read級別），從而實現在該事務讀取下一直讀取的是創建時的數據，而不影響其他事務的讀寫操作。

表級鎖

每次操作鎖住整張表。鎖定粒度大，發生鎖的衝突的概率最高，併發度最低。應用在MyISAM、InnoDB、BDB等存儲引擎中。

對於表級鎖，主要分為一下三類：

1. 表鎖
2. 元數據鎖（meta data lock，MDL）
3. 意向鎖

表鎖

對於表鎖，分為兩類：

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

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

語法：

//表級別的共享鎖，也就是讀鎖；
//允許當前會話讀取被鎖定的表，但阻止其他會話對這些表進行寫操作。
lock tebles t_student read;

//表級鎖的獨佔鎖，也是寫鎖；
//允許當前會話對錶進行讀寫操作，但阻止其他會話對這些表進行任何操作（讀或寫）。
lock tables t_stuent write;

釋放所有鎖：

unlock tables （會話退出，也會釋放所有鎖）

元數據鎖

MDL加鎖過程是系統自動控制，無需顯式使用，在訪問一張表的時候會自動加上。MDL鎖主要作用是維護表元數據的數據一致性，在表
上有活動事務的時候，不可以對元數據進行寫入操作。為了避免DML與DDL衝突，保證讀寫的正確性。

• 對一張表進行 CRUD 操作時，加的是 MDL 讀鎖；
• 對一張表做結構變更操作的時候，加的是 MDL 寫鎖；

查看元數據鎖：

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

意向鎖

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

意向共享鎖和意向獨佔鎖是表級鎖，不會和行級的共享鎖和獨佔鎖發生衝突，而且意向鎖之間也不會發生衝突，只會和共享表鎖（lock tables … read)和獨佔表鎖（lock tables … write）發生衝突

如果沒有「意向鎖」，那麼加「獨佔表鎖」時，就需要遍歷表裏所有記錄，查看是否有記錄存在獨佔鎖，這樣效率會很慢。

那麼有了「意向鎖」，由於在對記錄加獨佔鎖前，先會加上表級別的意向獨佔鎖，那麼在加「獨佔表鎖」時，直接查該表是否有意向獨佔鎖，如果有就意味着表裏已經有記錄被加了獨佔鎖，這樣就不用去遍歷表裏的記錄。

意向鎖的目的是為了快速判斷表裏是否有記錄被加鎖。

加鎖方式：

意向共享鎖：（先在表上加上意向共享鎖，然後對讀取的記錄加共享鎖）
由 select ... lock in share mode 添加

意向獨佔鎖：（先表上加上意向獨佔鎖，然後對讀取的記錄加獨佔鎖）
由 insert、update、delete、select ... for update 添加

AUTO-INC鎖（補充）

行級鎖

行級鎖，每次操作鎖住對應的行數據。鎖定粒度最小，發生鎖衝突的概率最低，併發度最高。應用在InnoDB存儲引擎中。

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

Record Lock（行鎖）

Record Lock 稱為記錄鎖，鎖住的是一條記錄。而且記錄鎖是有 S 鎖和 X 鎖之分。

InnoDB實現了以下兩種類型的行鎖：

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

行鎖類型：

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

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

查看意向鎖及行鎖的加鎖情況：

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

Gap Lock（間隙鎖）

Next-Key Lock（臨鍵鎖）

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

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

InnoDB引擎

邏輯存儲結構

架構

內存架構

磁盤結構

後台線程

事務原理

事務：

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

特徵：

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

特性原理分類圖：

redo log

重做日誌，記錄的是事務提交時數據頁的物理修改，是用來實現事務的持久性。

該日誌文件由兩部分組成:重做日誌緩衝(redo log buffer)以及重做日誌文件(redo log file),前者是在內存中，後者在磁盤中。當事務提交之後會把所有修改信息都存到該日誌文件中,用於在刷新髒頁到磁盤,發生錯誤時,進行數據恢復使用。

Buffer Pool在產生髒頁數據的時候，會先將數據存儲到 redo log buffer 再存儲到 redo log 中進行磁盤持久化存儲，在內存出現異常（比如突然斷電）時，通過redo log中持久化的數據進行回滾。過程如下圖：

redo log 要寫到磁盤，數據也要寫磁盤，為什麼要多此一舉?

寫入 redo log 的方式使用了追加操作，所以磁盤操作是順序寫，而寫入數據需要先找到寫入位置，然後才寫到磁盤，所以磁盤操作是隨機寫。

undo log

回滾日誌，用於記錄數據被修改前的信息，作用包含兩個:提供回滾 和 MVCC(多版本併發控制)。

undo log 和 redo log 記錄物理日誌不一樣，它是邏輯日誌。可以認為當 delete 一條記錄時，undo log中會記錄一條對應的insert記錄，反之亦然，當 update 一條記錄時，它記錄一條對應相反的 update 記錄。當執行 rollback 時，就可以從 undo log 中的邏輯記錄讀取到相應的內容並進行回滾。

Undo log 銷燬：undo log 在事務執行時產生，事務提交時，並不會立即刪除undol0g，因為這些日誌可能還用於 MVCC。

Undo log 存儲：undo log 採用段的方式進行管理和記錄，存放在前面介紹的 rollback segment 回滾段中，內部包含1024個 undo log segment.

MVCC

當前讀：

讀取的是記錄的最新版本，讀取時還要保證其他併發事務不能修改當前記錄，會對讀取的記錄進行加鎖。對於我們日常的操作，如:select…lock in share mode(共享鎖)，select… for update、update、insert、delete(排他鎖)都是一種當前讀。

快照讀：

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

• Read committed:每次select，都生成一個快照讀。
• Repeatable Read:開啓事務後第一個select語句才是快照讀的地方。
• Serializable:快照讀會退化為當前讀。

MVCC：

全稱 Multi-Version Concurrency Control，多版本併發控制。指維護一個數據的多個版本，使得讀寫操作沒有衝突，快照讀為MVSOL實現MVCC提供了一個非阻塞讀功能。MVCC的具體實現，還需要依賴於數據庫記錄中的三個隱式字段、undo log日誌、read View。

三個隱藏字段

undo log

回滾日誌，在insert、update、delete的時候產生的便於數據回滾的日誌。

當insert的時候，產生的undoloq日誌只在回滾時需要，在事務提交後，可被立即刪除。

而update、delete的時候，產生的undo log日誌不僅在回滾時需要，在快照讀時也需要，不會立即被刪除。

那麼何時刪除？

• 事務提交後：
  • 對於INSERT操作，事務提交後，undo log可以被立即刪除，因為不再需要用於回滾。
  • 對於UPDATE和DELETE操作，undo log不會立即被刪除，因為它們可能在後續的快照讀取中被使用。
• 快照讀取結束：
  • 當所有依賴於該undo log的快照讀取操作結束後，undo log才會被刪除。這意味着如果有一個事務正在進行快照讀取，並且依賴於某個undo log，那麼這個undo log會一直保留直到該事務結束。

readview

ReadView(讀視圖)是 快照讀 SOL執行時MVCC提取數據的依據，記錄並維護系統當前活躍的事務(未提交的)id。

ReadView中包含了四個核心字段:

依次比較 undo log 日誌中版本數據鏈，找到可以進行訪問的版本數據。

MySQL管理

系統數據庫介紹

Mysql數據庫安裝完成後，自帶了一下四個數據庫，具體作用如下：

常用工具

mysql

mysqladmin

mysqlbinlog

mysqlshow

mysqldump

mysqlimport/source

運維篇

日誌

錯誤日誌

錯誤日誌是 MySQL 中最重要的日誌之一，它記錄了當 mysqld 啓動和停止時，以及服務器在運行過程中發生任何嚴重錯誤時的相關信息當數據庫出現任何故障導致無法正常使用時，建議首先查看此日誌。

該日誌是默認開啓的，默認存放目錄 /var/log/，默認的日誌文件名為 mysqld.log 。查看日誌位置：

show variables like '%log_error%'

二進制日誌

介紹

二進制日誌(BINLOG)記錄了所有的 DDL(數據定義語言)語句和 DML(數據操縱語言)語句，但不包括數據查詢(SELECT、SHOW)語句。

作用：

1. 災難時的數據恢復；
2. MySQL的主從複製。

在MVSOL8版本中，默認二進制日誌是開啓着的，涉及到的參數如下：

show variables like '%log_bin%'

日誌格式

MySQL服務器中提供了多種格式來記錄二進制記錄，具體格式及特點如下：

查看參數方式：show variables like '%binlog_format%';

日誌查看

由於日誌是以二進制方式存儲的，不能直接讀取，需要通過二進制日誌查詢工具 mysqlbinlog 來查看，具體語法：

mysqlbinlog[參數選項]logfilename

參數選項：
    -d    指定數據庫名稱，只列出指定的數據庫相關的操作。
    -o    忽略掉日誌中的前n行命令。
    -v    將行事件(數據變更)重構為SOL語句。
    -w    將行事件(數據變更)重構為SQL語句，並輸出註釋信息

日誌刪除

對於比較繁忙的業務系統，每天生成的binlog數據巨大，如果長時間不清除，將會佔用大量磁盤空間。可以通過以下幾種方式清理日誌：

也可以在mysql的配置文件中配置二進制日誌的過期時間，設置了之後，二進制日誌過期會自動刪除.

show variables like '%binlog_expire_logs_seconds%'

查詢日誌

查詢日誌中記錄了客户端的所有操作語句，而二進制日誌不包含查詢數據的SQL語句。默認情況下，查詢日誌是未開啓的。如果需要開啓查詢日誌，可以設置一下配置：

修改MySQL的配置文件 /etc/my.cnf 文件，添加如下內容：

#該選項用來開啓查詢日誌，可選值：0或者1；0代表關閉，1代表開啓
general_log=1
#設置日誌的文件名 ， 如果沒有指定，默認的文件名為 host_name.log
general_log_file=mysql_query.log

慢查詢日誌

慢查詢日誌記錄了所有執行時間超過參數 long_query_time 設置值並且掃描記錄數不小於 min_examined_row_limit的所有的SQL語句的日誌，默認未開啓。long_query_time 默認為 10 秒，最小為0，精度可以到微秒。

#慢查詢日誌
slow_query_log=1
#執行時間參數
long_query_time=2

默認情況下，不會記錄管理語句，也不會記錄不使用索引進行查找的查詢。可以使用log_slow_admin_statements和更改此行為log_queries_not_using_indexes，如下所述。

#記錄執行較慢的管理語句
log_slow_admin_statements = 1
#記錄執行較慢的未使用索引的語句
log_queries_not_using_indexes = 1

主從複製

原理

搭建實現

主庫配置

從庫配置

測試

1、在主庫上創建數據庫、表，並插入數據

create database db01;
use db01;
create table tb_use(
	id int(11) primary key not null auto_increment,
	name varchar(50) not null,
	sex varchar(1)
)engine=innodb default charset=utf8mb4;
insert into tb_user(id, name, sex) valurs (null, 'Tom', '1'), (null, 'Trigger', '0'), (null, 'Dawn', '1');

2、在從庫中查詢數據，驗證主從是否同步。

分庫分表

介紹

Mycat概述

Mycat入門

Mycat配置

schema.xml

schema.xml作為MyCat中最重要的配置文件之一,涵蓋了MyCat的邏輯庫 、邏輯表 、分片規則、分片節點及數據源的配置.

主要包含以下三組標籤:

• schema標籤
• datanode標籤
• datahost標籤

rule.xml

Mycat分片

垂直分庫

水平分表

分片規則

範圍分片

取模分片

一致性hash算法

枚舉分片

應用指定算法

固定hash算法

字符串hash解析

按天分片

按自然月分片

Mycat管理及監控

讀寫分離

介紹

一主一從讀寫分離

雙主雙從

測試：

分別在兩台主庫Master1、Master2上執行DDL、DML語句，查看涉及到的數據庫服務器的數據同步情況。

create database db01;
use db01;
create table tb_user(
	id in(11)not null primary key,
	name varchar(50) not null,
	sex varcahr(1)
)engine=innodb default charset=utf8mb4

insert into tb user(id,name,sex) values(l,'Tom','1');
insert into tb user(id,name,sex) values(2,'Trigger','0');
insert into tb user(id,name,sex) values(3,'Dawn','1');
insert into tb user(id,name,sex) values(4,"ack Ma','1');
insertinto tb user(id,name,sex) values(5,'Coco','0');
insert into tb user(id,name,sex) values(6,'erry','1');

雙主雙從讀寫分離

測試：

登錄MyCat，測試查詢及更新操作，判定是否能夠進行讀寫分離，以及讀寫分離的策略是否正確。

當主庫掛掉一個之後，是否能夠自動切換。

數據類型

整型

無符號在數據類型後加 unsigned 關鍵字。

浮點型

日期和時間

字符串

二進制類型

權限一覽表

具體權限的作用詳見官方文檔

GRANT 和 REVOKE 允許的靜態權限

GRANT 和 REVOKE 允許的動態權限

圖形化界面工具

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

安裝

小技巧

1. 在SQL語句之後加上\G會將結果的表格形式轉換成行文本形式
2. 查看Mysql數據庫佔用空間：

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