基礎篇

通用語法及分類

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 條件列表;

條件：

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

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

例子：

-- 年齡等于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';

聚合查詢（聚合函數）

常見聚合函數：

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

語法：
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

管理用戶

查詢用戶：

USER 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';

注意事項

主機名可以使用 % 通配

權限控制

常用權限：

權限	說明
ALL, ALL PRIVILEGES	所有權限
SELECT	查詢數據
INSERT	插入數據
UPDATE	修改數據
DELETE	刪除數據
ALTER	修改表
DROP	刪除數據庫/表/視圖
CREATE	創建數據庫/表

更多權限請看權限一覽表

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

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

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

注意事項

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

函數

函數是指一段可以直接被另外一段程序調用的程序或代碼。

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

字符串函數

常用函數：

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

使用示例：

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

數值函數

常見函數：

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

日期函數

常用函數：

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

例子：

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

流程函數

常用函數：

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

例子：

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

約束

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

分類：

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

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

常用約束

約束條件	關鍵字
主鍵	PRIMARY KEY
自動增長	AUTO_INCREMENT
不為空	NOT NULL
唯一	UNIQUE
邏輯條件	CHECK
默認值	DEFAULT

例子：

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 外鍵名;

刪除/更新行為

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

更改刪除/更新行為：
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');

列子查詢

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

常用操作符：

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

例子：

-- 查詢銷售部和市場部的所有員工信息  
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

并發事務隔離級別：

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

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

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

進階篇

存儲引擎

MySQL體系結構：

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-auIab3zX-1692120469795)(https://dhc.pythonanywhere.com/media/editor/MySQL體系結構_20220315034329549927.png “結構圖”)]
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-lMxFlbk7-1692120469796)(https://dhc.pythonanywhere.com/media/editor/MySQL體系結構層級含義_20220315034359342837.png “層級描述”)]

存儲引擎就是存儲數據、建立索引、更新/查詢數據等技術的實現方式。存儲引擎是基于表而不是基于庫的，所以存儲引擎也可以被稱為表引擎。
默認存儲引擎是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 邏輯存儲結構：
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-Qd5rvjJ0-1692120469796)(https://dhc.pythonanywhere.com/media/editor/邏輯存儲結構_20220316030616590001.png “InnoDB邏輯存儲結構”)]

MyISAM

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

特點：

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

文件：

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

Memory

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

特點：

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

文件：

xxx.sdi: 存儲表結構信息

存儲引擎特點

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

存儲引擎的選擇

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

InnoDB: 如果應用對事物的完整性有比較高的要求，在并發條件下要求數據的一致性，數據操作除了插入和查詢之外，還包含很多的更新、刪除操作，則 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，當不查詢任何表的時候type為NULL一般不可能，當訪問主鍵或者唯一索引時會出現const，非唯一索引會出現ref。all表示全表掃描，index表示用了索引，但是會對全部索引進行掃描，性能也不高。
possible_key：可能應用在這張表上的索引，一個或多個
Key：實際使用的索引，如果為 NULL，則沒有使用索引
Key_len：表示索引中使用的字節數，該值為索引字段最大可能長度，并非實際使用長度，在不損失精確性的前提下，長度越短越好
rows：MySQL認為必須要執行的行數，在InnoDB引擎的表中，是一個估計值，可能并不總是準確的
filtered：表示返回結果的行數占需讀取行數的百分比，filtered的值越大越好
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-104rXATR-1692120469797)(C:\Users\14361\AppData\Roaming\Typora\typora-user-images\image-20230811022103408.png)]

索引

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

優缺點：

優點：

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

缺點：

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

索引結構

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

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

B-Tree

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-J7G4Bmh5-1692120469797)(https://dhc.pythonanywhere.com/media/editor/二叉樹_20220316153214227108.png “二叉樹”)]

二叉樹形成鏈表的缺點可以用紅黑樹來解決：
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-61XPaR9i-1692120469797)(https://dhc.pythonanywhere.com/media/editor/紅黑樹_20220316163142686602.png “紅黑樹”)]
紅黑樹也存在大數據量情況下，層級較深，檢索速度慢的問題。

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

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-dSk0JoAy-1692120469798)(https://dhc.pythonanywhere.com/media/editor/B-Tree結構_20220316163813441163.png “B-Tree結構”)]

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

B+Tree

結構圖：

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-FTv5Px9T-1692120469798)(https://dhc.pythonanywhere.com/media/editor/B+Tree結構圖_20220316170700591277.png “B+Tree結構圖”)]

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

與 B-Tree 的區別：

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

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

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-hM2vmCqe-1692120469799)(https://dhc.pythonanywhere.com/media/editor/結構圖_20220316171730865611.png “MySQL B+Tree 結構圖”)]

Hash

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

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-ccRHGqSc-1692120469799)(https://dhc.pythonanywhere.com/media/editor/Hash索引原理圖_20220317143226150679.png “Hash索引原理圖”)]

特點：

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

存儲引擎支持：

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

面試題

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

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

索引分類

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

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

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

演示圖：

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-Fe9d4nKQ-1692120469800)(https://dhc.pythonanywhere.com/media/editor/原理圖_20220318194454880073.png “大致原理”)]
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-MfApI9TH-1692120469801)(https://dhc.pythonanywhere.com/media/editor/演示圖_20220319215403721066.png “演示圖”)]

聚集索引選取規則：

如果存在主鍵，主鍵索引就是聚集索引
如果不存在主鍵，將使用第一個唯一(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;

使用規則

最左前綴法則

如果索引關聯了多列（聯合索引），要遵守最左前綴法則，最左前綴法則指的是查詢從索引的最左列開始，并且不跳過索引中的列。
如果跳躍某一列，索引將部分失效（后面的字段索引失效）。跳過的話，后面的排序就無從說起了。最左前綴法則在用select的時候，和放的位置是沒有關系的，只要存在就行。

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

索引失效情況

在索引列上進行運算操作，索引將失效。如：explain select * from tb_user where substring(phone, 10, 2) = '15'; 換成 explain select * from tb_user where phone = '17799990015';這是可以的。
字符串類型字段使用時，不加引號，索引將失效。如：explain select * from tb_user where phone = 17799990015;，此處phone的值沒有加引號
模糊查詢中，如果僅僅是尾部模糊匹配，索引不會是失效；如果是頭部模糊匹配，索引失效。如：explain select * from tb_user where profession like '%工程';，前后都有 % 也會失效。explain select * from tb_user where profession like '軟件%'; 這個是不會失效的，只有前面加了%才會失效。
用 or 分割開的條件，如果 or 其中一個條件的列沒有索引，那么涉及的索引都不會被用到。
如果 MySQL 評估使用索引比全表更慢，則不使用索引，比如根據查詢條件查詢的是大部分數據，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 *；
如果在輔助索引（二級索引）中找聚集索引，如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字段建立聯合索引，則不需要回表查詢，直接覆蓋索引。
username和password字段建立聯合索引的葉子節點掛的就是 id 所以不需要三者同時建索引。

前綴索引

當字段類型為字符串（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;

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

![images]

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

單列索引&聯合索引

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

單列索引情況：

explain select id, phone, name from tb_user where phone = '17799990010' and name = '韓信';
phone 和 name 都建立了索引情況下，這句只會用到phone索引字段。

聯合索引的數據組織圖：

注意事項

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

設計原則

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

SQL 優化

插入數據

普通插入：

采用批量插入（一次插入的數據不建議超過1000條，500 - 1000 為宜）
手動提交事務
主鍵順序插入（主鍵順序插入的效率大于亂序插入）

大批量插入：
如果一次性需要插入大批量數據，使用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指令將準備好的數據，加載到表結構中，先要把表建立起來。下述代碼的意思是把本地文件sql1.log插入到表tb_user中，字段以“，”分隔，每一行以\n分隔。
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

主鍵優化

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

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

![images]

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

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

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

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

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

主鍵設計原則：

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

order by優化

Using filesort：通過表的索引或全表掃描，讀取滿足條件的數據行，然后在排序緩沖區 sort buffer 中完成排序操作，所有不是通過索引直接返回排序結果的排序都叫 FileSort 排序
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 ]

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

查詢視圖

查看創建視圖語句： SHOW CREATE VIEW 視圖名稱；

查看視圖數據：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]

刪除視圖

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

視圖檢查選項

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

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

CASCADED

級聯，一旦選擇了這個選項，除了會檢查創建視圖時候的條件，還會檢查所依賴視圖的條件。

比如下面的例子：創建stu_V_l 視圖，id是小于等于 20的。
create or replace view stu_V_l as select id,name from student where id <=20;
再創建 stu_v_2 視圖，20 >= id >=10。
create or replace view stu_v_2 as select id,name from stu_v_1 where id >=10 with cascaded check option;
再創建 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。
insert into stu_v_3 values(17,'Tom');

LOCAL

本地的條件也會檢查，還會向上檢查。在向上找的時候，就要看是否上面開了檢查選項，如果沒開就不檢查。和 CASCADED 的區別就是 CASCADED 不管上面開沒開檢查選項都會進行檢查。

更新及作用

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

聚合函數或窗口函數 ( SUM()、MIN()、MAX()、COUNT() 等 )
DISTINCT
GROUP BY
HAVING
UNION 或者UNION ALL

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

作用

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

安全
數據庫可以授權，但不能授權到數據庫特定行和特定的列上。通過視圖用戶只能查詢和修改他們所能見到的數據
數據獨立
視圖可幫助用戶屏蔽真實表結構變化帶來的影響。

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

存儲過程

存儲過程是事先經過編譯并存儲在數據庫中的一段SQL 語句的集合，調用存儲過程可以簡化應用開發人員的很多工作，減少數據在數據庫和應用服務器之間的傳輸，對于提高數據處理的效率是有好處的。
存儲過程思想上很簡單，就是數據庫SQL 語言層面的代碼封裝與重用。

特點

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

創建

CREATE PROCEDURE 存儲過程名稱( [參數列表] ) BEGINSQL 語句 END;

NOTE: 在命令行中，執行創建存儲過程的SQL時，需要通過關鍵字delimiter 指定SQL語句的結束符。默認是分號作為結束符。

delimiter $ ，則 $ 符作為結束符。

調用

CALL 名稱 ( [參數])

查看

查詢指定數據庫的存儲過程及狀態信息

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

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

SHOW CREATE PROCEDURE

刪除

DROP PROCEDURE [ IFEXISTS ] 存儲過程名稱

游標

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

聲明游標：
DECLARE 游標名稱 CURSOR FOR 查詢語句

打開游標：
OPEN 游標名稱

獲取游標記錄：
FETCH 游標名稱INTO變量[變量]

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

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

handler_action CONTINUE：繼續執行當前程序

EXIT：終止執行當前程序

condition_value :

SQLSTATE sqlstate_value：狀態碼，如02000

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

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

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

例子：

NOTE：要先聲明普通變量，再申請游標。

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

create procedure p1l(in uage int)begindeclare uname varchar(100); decLare upro varchar(100);declare u_cursor cursor for select name,profession from tb_user where age <= uage; 當 條件處理程序的處理的狀態碼為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;

觸發器

介紹
觸發器是與表有關的數據庫對象，指在insert/update/delete之前或之后，觸發并執行觸發器中定義的SQL語句集合。觸發器的這種特性可以協助應用在數據庫端確保數據的完整性，日志記錄，數據校驗等操作。
使用別名OLD和NEW來引用觸發器中發生變化的記錄內容，這與其他的數據庫是相似的。現在觸發器還只支持行級觸發（比如說一條語句影響了 5 行則會被觸發 5 次），不支持語句級觸發（比如說一條語句影響了 5 行則會被觸發 1 次）。

觸發器類型	NEW 和 OLD
INSERT	NEW 表示將要或者已經新增的數據
UPDATE	OLD表示修改之前的數據，NEW表示將要或已經修改后的數據
DELETE	OLD表示將要或者已經刪除的數據

鎖

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

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

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

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

全局鎖：

全局鎖就是對整個數據庫實例加鎖，加鎖后整個實例就處于只讀狀態，后續的DML的寫語句，DDL語句，已經更新操作的事務提交語句都將被阻塞。
其典型的使用場景是做全庫的邏輯備份，對所有的表進行鎖定，從而獲取一致性視圖，保證數據的完整性.

表鎖：

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

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

表鎖：對于表鎖，分為兩類：1.表共享讀鎖（read lock）所有的事物都只能讀（當前加鎖的客戶端也只能讀，不能寫），不能寫 2.表獨占寫鎖（write lock），對當前加鎖的客戶端，可讀可寫，對于其他的客戶端，不可讀也不可寫。
讀鎖不會阻塞其他客戶端的讀，但是會阻塞寫。寫鎖既會阻塞其他客戶端的讀，又會阻塞其他客戶端的寫。
元數據鎖（meta data lock，MDL），MDL加鎖過程是系統自動控制，無需顯式使用，在訪問一張表的時候會自動加上。MDL鎖主要作用是維護表元數據的數據一致性，在表上有活動事務的時候，不可以對元數據進行寫入操作。在MySQL5.5中引入了MDL，當對一張表進行增刪改查的時候，加MDL讀鎖（共享）;當對表結構進行變更操作的時候，加MDL寫鎖（排他）。
意向鎖: 為了避免DML在執行時，加的行鎖與表鎖的沖突，在InnoDB中引入了意向鎖，使得表鎖不用檢查每行數據是否加鎖，使用意向鎖來減少表鎖的檢查。
一個客戶端對某一行加上了行鎖，那么系統也會對其加上一個意向鎖，當別的客戶端來想要對其加上表鎖時，便會檢查意向鎖是否兼容，若是不兼容，便會阻塞直到意向鎖釋放。

意向鎖兼容性：

意向共享鎖（IS）：與表鎖共享鎖（read）兼容，與表鎖排它鎖（write）互斥。
意向排他鎖（lX）：與表鎖共享鎖（read）及排它鎖（write）都互斥。意向鎖之間不會互斥。

行鎖：

行級鎖，每次操作鎖住對應的行數據。鎖定粒度最小，發生鎖沖突的概率最低，并發度最高。應用在InnoDB存儲引擎中。
InnoDB的數據是基于索引組織的，行鎖是通過對索引上的索引項加鎖來實現的，而不是對記錄加的鎖。對于行級鎖，主要分為以下三類：

行鎖（Record Lock）：鎖定單個行記錄的鎖，防止其他事務對此行進行update和delete。在RC（read commit ）、RR（repeat read）隔離級別下都支持。
間隙鎖（GapLock）：鎖定索引記錄間隙（不含該記錄），確保索引記錄間隙不變，防止其他事務在這個間隙進行insert，產生幻讀。在RR隔離級別下都支持。比如說兩個臨近葉子節點為 15 23，那么間隙就是指 [15 , 23],鎖的是這個間隙。
臨鍵鎖（Next-Key Lock）：行鎖和間隙鎖組合，同時鎖住數據，并鎖住數據前面的間隙Gap。在RR隔離級別下支持。

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

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

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

行鎖 - 演示

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

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

間隙鎖/臨鍵鎖-演示

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

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

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

InnoDB 引擎

邏輯存儲結構

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

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

區，表空間的單元結構，每個區的大小為1M。默認情況下，InnoDB存儲引擎頁大小為16K，即一個區中一共有64個連續的頁。

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

行，InnoDB存儲引擎數據是按進行存放的。

架構

![images]

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

![images]
![images]
![images]
![images]

磁盤架構：
![images]
![images]
![images]

InnoDB的整個體系結構為：

當業務操作的時候直接操作的是內存緩沖區，如果緩沖區當中沒有數據，則會從磁盤中加載到緩沖區，增刪改查都是在緩沖區的，后臺線程以一定的速率刷新到磁盤。

事務原理

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

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

redo log:

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

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

undo log:
回滾日志，用于記錄數據被修改前的信息，作用包含兩個：提供回滾和MVCC（多版本并發控制）。
undo log和redo log記錄物理日志不一樣，它是邏輯日志。可以認為當delete一條記錄，undo log中會記錄一條對應的insert記錄，反之亦然，當update一條記錄時，它記錄一條對應相反的update記錄。當執行rollback時，就可以從undo log中的邏輯記錄讀取到相應的內容并進行回滾。

Undo log銷毀：undo log在事務執行時產生，事務提交時，并不會立即刪除undo log，因為這些日志可能還用于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，多版本并發控制。指維護一個數據的多個版本，使得讀寫操作沒有沖突，快照讀為MySQL實現MVCC提供了一個非阻塞讀功能。MVCC的具體實現，還需要依賴于數據庫記錄中的三個隱式字段、undo log日志、readView。

MVCC 實現原理:

有三個隱藏的字段:

![images]

undo log回滾日志，在insert、update、delete的時候產生的便于數據回滾的日志。
當insert的時候，產生的undo log日志只在回滾時需要，在事務提交后，可被立即刪除。
而update、delete的時候，產生的undo log日志不僅在回滾時需要，在快照讀時也需要，不會立即被刪除。

undo log 版本鏈：

undo log日志會記錄原來的版本的數據，因為是通過undo log 日志進行回滾的。

![images]

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

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-d48Fi13f-1692120469805)(]

RC隔離級別下，在事務中每一次執行快照讀時生成ReadView。
RR隔離級別下，在事務中第一次執行快照讀時生成ReadView，后續會復用。

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

MVCC 靠隱藏字段 , undo log 版本鏈 , read view 實現的。

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

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-ptiHcKQ4-1692120469805)(]

數據類型

整型

類型名稱	取值范圍	大小
TINYINT	-128?127	1個字節
SMALLINT	-32768?32767	2個宇節
MEDIUMINT	-8388608?8388607	3個字節
INT (INTEGHR)	-2147483648?2147483647	4個字節
BIGINT	-9223372036854775808?9223372036854775807	8個字節

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

浮點型

類型名稱	說明	存儲需求
FLOAT	單精度浮點數	4 個字節
DOUBLE	雙精度浮點數	8 個字節
DECIMAL (M, D)，DEC	壓縮的“嚴格”定點數	M+2 個字節

日期和時間

類型名稱	日期格式	日期范圍	存儲需求
YEAR	YYYY	1901 ~ 2155	1 個字節
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3 個字節
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3 個字節
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8 個字節
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 個字節

字符串

類型名稱	說明	存儲需求
CHAR(M)	固定長度非二進制字符串	M 字節，1<=M<=255
VARCHAR(M)	變長非二進制字符串	L+1字節，在此，L< = M和 1<=M<=255
TINYTEXT	非常小的非二進制字符串	L+1字節，在此，L<2^8
TEXT	小的非二進制字符串	L+2字節，在此，L<2^16
MEDIUMTEXT	中等大小的非二進制字符串	L+3字節，在此，L<2^24
LONGTEXT	大的非二進制字符串	L+4字節，在此，L<2^32
ENUM	枚舉類型，只能有一個枚舉字符串值	1或2個字節，取決于枚舉值的數目 (最大值為65535)
SET	一個設置，字符串對象可以有零個或多個SET成員	1、2、3、4或8個字節，取決于集合成員的數量（最多64個成員）

二進制類型

類型名稱	說明	存儲需求
BIT(M)	位字段類型	大約 (M+7)/8 字節
BINARY(M)	固定長度二進制字符串	M 字節
VARBINARY (M)	可變長度二進制字符串	M+1 字節
TINYBLOB (M)	非常小的BLOB	L+1 字節，在此，L<2^8
BLOB (M)	小 BLOB	L+2 字節，在此，L<2^16
MEDIUMBLOB (M)	中等大小的BLOB	L+3 字節，在此，L<2^24
LONGBLOB (M)	非常大的BLOB	L+4 字節，在此，L<2^32

權限一覽表

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

GRANT 和 REVOKE 允許的靜態權限

Privilege	Grant Table Column	Context
`ALL [PRIVILEGES]`	Synonym for “all privileges”	Server administration
`ALTER`	`Alter_priv`	Tables
`ALTER ROUTINE`	`Alter_routine_priv`	Stored routines
`CREATE`	`Create_priv`	Databases, tables, or indexes
`CREATE ROLE`	`Create_role_priv`	Server administration
`CREATE ROUTINE`	`Create_routine_priv`	Stored routines
`CREATE TABLESPACE`	`Create_tablespace_priv`	Server administration
`CREATE TEMPORARY TABLES`	`Create_tmp_table_priv`	Tables
`CREATE USER`	`Create_user_priv`	Server administration
`CREATE VIEW`	`Create_view_priv`	Views
`DELETE`	`Delete_priv`	Tables
`DROP`	`Drop_priv`	Databases, tables, or views
`DROP ROLE`	`Drop_role_priv`	Server administration
`EVENT`	`Event_priv`	Databases
`EXECUTE`	`Execute_priv`	Stored routines
`FILE`	`File_priv`	File access on server host
`GRANT OPTION`	`Grant_priv`	Databases, tables, or stored routines
`INDEX`	`Index_priv`	Tables
`INSERT`	`Insert_priv`	Tables or columns
`LOCK TABLES`	`Lock_tables_priv`	Databases
`PROCESS`	`Process_priv`	Server administration
`PROXY`	See `proxies_priv` table	Server administration
`REFERENCES`	`References_priv`	Databases or tables
`RELOAD`	`Reload_priv`	Server administration
`REPLICATION CLIENT`	`Repl_client_priv`	Server administration
`REPLICATION SLAVE`	`Repl_slave_priv`	Server administration
`SELECT`	`Select_priv`	Tables or columns
`SHOW DATABASES`	`Show_db_priv`	Server administration
`SHOW VIEW`	`Show_view_priv`	Views
`SHUTDOWN`	`Shutdown_priv`	Server administration
`SUPER`	`Super_priv`	Server administration
`TRIGGER`	`Trigger_priv`	Tables
`UPDATE`	`Update_priv`	Tables or columns
`USAGE`	Synonym for “no privileges”	Server administration

GRANT 和 REVOKE 允許的動態權限

Privilege	Context
`APPLICATION_PASSWORD_ADMIN`	Dual password administration
`AUDIT_ABORT_EXEMPT`	Allow queries blocked by audit log filter
`AUDIT_ADMIN`	Audit log administration
`AUTHENTICATION_POLICY_ADMIN`	Authentication administration
`BACKUP_ADMIN`	Backup administration
`BINLOG_ADMIN`	Backup and Replication administration
`BINLOG_ENCRYPTION_ADMIN`	Backup and Replication administration
`CLONE_ADMIN`	Clone administration
`CONNECTION_ADMIN`	Server administration
`ENCRYPTION_KEY_ADMIN`	Server administration
`FIREWALL_ADMIN`	Firewall administration
`FIREWALL_EXEMPT`	Firewall administration
`FIREWALL_USER`	Firewall administration
`FLUSH_OPTIMIZER_COSTS`	Server administration
`FLUSH_STATUS`	Server administration
`FLUSH_TABLES`	Server administration
`FLUSH_USER_RESOURCES`	Server administration
`GROUP_REPLICATION_ADMIN`	Replication administration
`GROUP_REPLICATION_STREAM`	Replication administration
`INNODB_REDO_LOG_ARCHIVE`	Redo log archiving administration
`NDB_STORED_USER`	NDB Cluster
`PASSWORDLESS_USER_ADMIN`	Authentication administration
`PERSIST_RO_VARIABLES_ADMIN`	Server administration
`REPLICATION_APPLIER`	`PRIVILEGE_CHECKS_USER` for a replication channel
`REPLICATION_SLAVE_ADMIN`	Replication administration
`RESOURCE_GROUP_ADMIN`	Resource group administration
`RESOURCE_GROUP_USER`	Resource group administration
`ROLE_ADMIN`	Server administration
`SESSION_VARIABLES_ADMIN`	Server administration
`SET_USER_ID`	Server administration
`SHOW_ROUTINE`	Server administration
`SYSTEM_USER`	Server administration
`SYSTEM_VARIABLES_ADMIN`	Server administration
`TABLE_ENCRYPTION_ADMIN`	Server administration
`VERSION_TOKEN_ADMIN`	Server administration
`XA_RECOVER_ADMIN`	Server administration

圖形化界面工具

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/

安裝

小技巧

在SQL語句之后加上\G會將結果的表格形式轉換成行文本形式
查看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;

參考文獻

https://dhc.pythonanywhere.com/entry/share/?key=3ad29aad765a2b98b2b2a745d71bef715507ee9db8adbec98257bac0ad84cbe4#h1-u6743u9650u4E00u89C8u8868
這篇筆記是在別人的基礎上完善而來，感謝B站的黑馬程序員up主，也感謝路途博客。