MYSQL（進階篇）——一篇文章帶你深入掌握MYSQL

我們在上篇文章中已經學習了MYSQL的基本語法和概念

在這篇文章中我們將講解底層結構和一些新的語法幫助你更好的運用MYSQL

溫馨提醒：該文章大約20000字，建議關註收藏慢慢觀看，希望能給你帶來幫助~

進階篇內容目錄

這篇文章我們主要分為七個部分：

• 存儲引擎
• 索引
• SQL優化
• 視圖/存儲過程/觸發器
• 鎖
• InnoDB引擎
• MYSQL管理

存儲引擎

在講解存儲引擎前我們先來瞭解一下MYSQL的整體體繫結構

MYSQL整體分為四個部分：

• 連接層
• 服務層
• 引擎層
• 存儲層

存儲引擎簡介

存儲引擎概念：

• 存儲數據，建立索引，更新/查詢數據等技術的實現方式

存儲引擎註意點：

• 存儲引擎是基於表的，而不是基於庫的，所以不同表可以有不同的存儲引擎，同時存儲引擎也被稱為表類型

首先我們要先直到存儲引擎是在哪裡定義的：

# 下麵是一個表的創建語句
create table Name (
    ~~~~~~~~
)engine = InnoDB;
# 在上面的engine = 存儲引擎類型 就是存儲引擎的設計語句
# 我們預設情況下是InoDB存儲引擎

同樣我們可以通過查看創建方法來得到表的存儲引擎類型：

show create table 表名;

存儲引擎類型以及特點

我們可以在DG或其他資料庫軟體中直接查看存儲引擎的類型：

# 下述代碼會給出該資料庫中支持的存儲引擎類型
show engines;

在這裡我們僅詳細介紹三種存儲引擎：

1. InnoDB

介紹：

• InnoDB是一種兼顧可靠性和高性能的通用存儲引擎，在MYSQL5.5之後，稱為MYSQL預設存儲引擎

特點：

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

文件：

• xxx.ibd:xxx表示表名，innoDB引擎的每張表對應一個表空間文件，存儲該表的表結構，數據和索引
• 我們可以通過show variables like ‘innodb_file_per_table‘來查看查看存儲引擎分類情況（是否共用一個存儲引擎）
• 如果為ON表示一個表分配一個ibd文件

2. MyISAM

介紹：

• MyISAM是MYSQL早期的預設存儲引擎

特點：

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

文件：

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

3. Memory

介紹：

• Memory引擎的表數據是存儲在記憶體中的，由於受到硬體問題或斷電問題，只能作為臨時表或緩存使用

特點：

• 記憶體存放
• hash索引（預設）

文件：

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

三者區別：

存儲引擎選擇

我們在各個表都可以選擇不同的存儲引擎，而存儲引擎的選擇大多遵循以下特征：

優選InnoDB：

• MYSQL的預設存儲引擎，支持事務，外鍵
• 如果應用對事務的完整性有較高要求，併在併發條件下要求數據的一致性，數據操作除了插入和查詢外，還包括很多的更新和刪除操作，那麼InnoDB存儲引擎是比較合理的選擇

優選MyISAM：

• 如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，並對事務的完整性，併發性要求不高，那麼MyISAM存儲引擎是比較合理的選擇

優選Memory：

• 將所有數據保存到記憶體中，訪問速度快，常用於臨時表和緩存
• Memory的缺陷是對錶的大小有限制，太大的表無法存儲在記憶體中，且無法保證安全性

索引

首先我們來簡略的介紹一下索引：

• 索引是幫助MYSQL高效獲得數據的數據結構（有序）
• 在數據之外，資料庫系統還維護著滿足特定查找演算法的數據結構，這些數據結構以某種方法引用數據，實現查找

索引的優點：

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

索引的缺點：

• 索引列也是要占用空間的。
• 索引大大提高了查詢效率，同時卻也降低更新表的速度， 如對錶進行INSERT、UPDATE、DELETE時，效率降低。

索引結構

MySQL的索引是在存儲引擎層實現的，因而不同的存儲引擎有不同的索引結構 ：

不同的索引結構也有不同的適配情況：

在這裡我們僅對B+Tree索引和Hash索引做出詳細解釋：

1. B+Tree索引

以一顆最大度數為4的B+Tree結構為例：

圖片內容解釋：

• 綠色框框起來的部分，是索引部分，僅僅起到索引數據的作用，不存儲數據。
• 紅色框框起來的部分，是數據存儲部分，在其葉子節點中要存儲具體的數據。

註意：

• 上面節點僅用於快速定位數據位置，且所有的數據元素均會出現在葉節點
• 葉節點之間用單項鏈接相連，形成鏈表類型

MYSQL中的B+Tree索引在B+Tree結構上對葉節點進行了一點改造：

• 在B+Tree結構上增加了一個指向相鄰葉子節點的鏈表指針，形成了帶有順序指針的B+Tree，提高區間訪問性能

2. Hash索引

我們先來講解一下hash表：

• 哈希索引採用hash演算法，把鍵值換算成新的hash值，映射到對應的槽位上，然後存儲在hash表中
• 如果兩個或多個鍵值對映射到同一個相同的槽位上，我們採用鏈表的方法來解決

Hash索引特點：

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

Hash索引的存儲引擎支持：

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

接下來我們分析一下InnoDB存儲引擎為什麼選擇B+tree索引結構：

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

索引分類

我們根據索引類型常常把索引分為四種：

在InoDB存儲引擎中，根據索引的存儲形式，我們又可以把他們分為以下兩種：

聚集索引選擇特點：

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

聚集索引：

• 在葉節點下掛上整行的信息

二級索引：

• 在葉節點下掛上主鍵的信息

我們在查詢時常常採用回表查詢：

• 先利用已知條件採用二級索引得到主鍵，再利用主鍵用聚集索引獲得所有信息

索引語法

索引的語法只有三條：

# 創建索引
-- UNIQUE表示唯一索引 FULLTEXT表示全文索引
-- (index_col_name,... ) 表示可以形成聯合索引，一個索引包括多個表內列
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;

# 查看索引
SHOW INDEX FROM table_namne;

# 刪除索引
DROP INDEX index_name ON table_name;

我們下麵通過一個實例來演示索引的使用方法：

# 以下為構造表

-- 創建表
create table tb_user(
	id int primary key auto_increment comment '主鍵',
	name varchar(50) not null comment '用戶名',
	phone varchar(11) not null comment '手機號',
	email varchar(100) comment '郵箱',
	profession varchar(11) comment '專業',
	age tinyint unsigned comment '年齡',
	gender char(1) comment '性別 , 1: 男, 2: 女',
	status char(1) comment '狀態',
	createtime datetime comment '創建時間'
) comment '系統用戶表';

-- 添加數據
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('呂布', '17799990000', '[email protected]', '軟體工程', 23, '1', '6', '2001-02-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', '[email protected]', '通訊工程', 33, '1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('趙雲', '17799990002', '[email protected]', '英語', 34, '1', '2', '2002-03-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孫悟空', '17799990003', '[email protected]', '工程造價', 54, '1', '0', '2001-07-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木蘭', '17799990004', '[email protected]', '軟體工程', 23, '2', '1', '2001-04-22 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大喬', '17799990005', '[email protected]', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', '[email protected]', '應用數學', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', '[email protected]', '化工', 38, '1', '5', '2001-05-23 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('項羽', '17799990008', '[email protected]', '金屬材料', 43, '1', '0', '2001-09-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', '[email protected]', '機械工程及其自動化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韓信', '17799990010', '[email protected]', '無機非金屬材料工程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荊軻', '17799990011', '[email protected]', '會計', 29, '1', '0', '2001-05-11 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('蘭陵王', '17799990012', '[email protected]', '工程造價', 44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂鐵', '17799990013', '[email protected]', '應用數學', 43, '1', '2', '2001-04-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蟬', '17799990014', '[email protected]', '軟體工程', 40, '2', '3', '2001-02-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '[email protected]', '軟體工程', 31, '2', '0', '2001-01-30 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('羋月', '17799990016', '[email protected]', '工業經濟', 35, '2', '0', '2000-05-03 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '[email protected]', '化工', 38, '1', '1', '2001-08-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁傑', '17799990018', '[email protected]', '國際貿易', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', '[email protected]', '城市規劃', 51, '2', '0', '2001-08-15 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韋', '17799990020', '[email protected]', '城市規劃', 52, '1', '2', '2000-04-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉頗', '17799990021', '[email protected]', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('後羿', '17799990022', '[email protected]', '城市園林', 20, '1', '0', '2002-03-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '[email protected]', '工程造價', 29, '1', '4', '2003-05-26 00:00:00');

# 創建索引

-- 為name創造索引，因為name可能重覆，這裡創造簡單索引
create index idx_user_name on tb_user(name);

-- 為phone創造索引，因為phone只有一個，我們創造唯一索引
create unique index idx_user_phone on tb_user(phone);

-- 創造聯合索引，為professin，age，status創造聯合索引
create index idx_user_pro_age_status on tb_user(profession,age,status);

-- 為email創造索引
create index idx_user_email on tb_user(email);

# 刪除索引

-- 我們刪除email的索引
drop index idx_user_email on tb_user;

# 查看索引
show index from tb_user;

SQL性能分析

我們在後面的章節中將會講到SQL語句的優化

那麼優化自然是要針對SQL中性能較差的部分進行優化，因而這部分我們先講解如何分析其性能差異

語句執行頻率

在SQL中為我們提供了SHOW語句來查看當前資料庫的INSERT，DELETE，UPDATE，SELECT的訪問頻率：

# 顯示SQL中各種語句的訪問頻率
# 註意_______是七個_
SHOW GLOBAL STATUS LIKE 'Com_______';

慢查詢日誌

慢查詢日誌記錄了所有執行時間超過指定參數（long_query_time，單位：秒，預設10秒）的所有SQL語句的日誌。

MySQL的慢查詢日誌預設沒有開啟，我們可以查看一下系統變數 slow_query_log

# 開啟MYSQL慢日誌查詢開關
slow_query_log = 1;
# 設置慢查詢日誌的時間為2s，即SQL的語句執行時間超過2s就被記錄到慢查詢日誌中
long_query_time = 2;

這部分暫時瞭解即可，我們在運維篇會重點介紹日誌這一章節

profile詳情

首先我們需要查看當前MySQL是否支持profile操作：

# 查看是否支持profile操作
SELECT @@have_profiling;

在預設情況下profile操作時關閉的，我們需要通過set語句開啟profile：

# 開啟profile操作
SET profiling = 1;

profile可以存儲我們之前的操作時長，幫助我們在SQL優化中瞭解時間損耗的具體項目並加以改善：

# 查看每條SQL語句的耗時情況
SHOW profile;

# 查看指定query_id的SQL語句各個階段的耗時情況（這裡的id是SHOW profile的標號id）
SHOW profile for query query_id;

# 查看指定query_id的SQL語句CPU的使用情況（這裡的id是SHOW profile的標號id）
show profile cpu for query query_id;

explain執行計劃

Explain是很重要的一部分，我們在下麵的調試中經常使用並查看

EXPLAIN 或者 DESC命令獲取 MySQL 如何執行 SELECT 語句的信息，包括在 SELECT 語句執行過程中表如何連接和連接的順序 。

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

我們可以看到一些關於欄位的詳細內容（針對其內部分析）

接下來我來一一解釋EXPLAIN所給出的信息含義：

索引使用

我們在前面已經學了索引的創建，索引實際上大部分是由系統決定使用類型，但我們也有需要註意的地方

時間差異

我們創建索引時需要遍歷所有數據，因而創建索引時的時間相當於我們不創建索引而查詢數據的時間

但當我們創建索引後去查詢數據，就會發現時間大大減少

# 假設我們的table中有1w條數據，當我們直接查詢時可能需要10s
SELECT * FROM table WHERE name = 02932131;

# 但當我們創建name索引，這時可能耗時15s
CREATE INDEX table_name_index ON table;

# 然後我們再憑藉name而進行數據查詢時，耗時將會接近0s
SELECT * FROM table WHERE name = 02932131;

聯合索引註意點

我們在使用聯合索引時需要註意以下兩點：

1. 最左首碼法則

如果索引了多列（聯合索引），要遵守最左首碼法則。

最左首碼法則指的是查詢從索引的最左列開始，並且不跳過索引中的列。如果跳躍某一列，索引將會部分失效(後面的欄位索引失效)。

# 例如我們在前面建立了一個idx_user_pro_age_status索引包含了pro，age，status三個列

# 當我們從左邊往右邊逐漸使用時不會產生錯誤：
explain select * from tb_user where profession = '軟體工程' and age = 31 and status= '0';
explain select * from tb_user where profession = '軟體工程' and age = 31;
explain select * from tb_user where profession = '軟體工程';

# 但當我們中間省略一列，或者缺少最前面的列，後面的索引列將不再被使用
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';

註意：

• where後的索引順序不受約束，最前面的索引可以放在where的任意位置不受影響

2. 範圍查詢

在聯合索引中，不允許出現（>,<），範圍查詢的右側列索引失效

# 我們如果使用 <,> 後面的索引將不再生效
explain select * from tb_user where profession = '軟體工程' and age > 30 and status = '0';

# 但我們使用 <=,>= 則不受影響
explain select * from tb_user where profession = '軟體工程' and age >= 30 and status = '0';

索引失效註意點

針對於索引失效的常見情況共有五種：

1. 索引列運算

我們不能在索引列上繼續運算，否則索引失效

# 如果我們採用substring等操作，索引列將失效
explain select * from tb_user where substring(phone,10,2) = '15';

2. 字元串未加引號

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

# 這裡最後一部分status的0未加引號，數據仍舊可以輸出，但不再通過索引查詢
explain select * from tb_user where profession = '軟體工程' and age = 31 and status= 0;

3. 模糊查詢

索引中禁止頭部出現模糊查詢

# 如果頭部出現%，索引失效

-- 索引有效
explain select * from tb_user where profession like '軟體%';

-- 索引失效
explain select * from tb_user where profession like '%工程';

-- 索引失效
explain select * from tb_user where profession like '%工%';

4. or連接條件

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

# 如果phone被設置有索引 但age未設置索引 ，則採用普通查詢方法不採用索引
explain select * from tb_user where phone = '17799990017' or age = 23;

5. 數據分佈影響

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

# 假設我們所查詢的數據占用該表的大多數數據，可能不採用索引而直接採用全表查詢

-- 假設我們希望查詢 phone 不為 NULL的行，但全表大部分都不為NULL，則會直接採用全表查詢
SELECT * FROM table WHERE phone is not NULL;

SQL提示

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

# 因為我們的SQL系統自動判定時可能不會採用最佳的運行方法

-- 比如 我們有 profession索引 和 profession，age，status聯合索引
-- 當我們希望查詢含有profession，age，status的數據時，系統卻自動選擇profession索引導致速度降低
-- 因而我們需要手動設置SQL提示來提高整體運行速度

# 推薦使用索引 use index
explain select * from tb_user use index(idx_user_pro) where profession = '軟體工程';

# 拒絕使用索引 ignore index
explain select * from tb_user ignore index(idx_user_pro) where profession = '軟體工程';

# 強迫使用索引 force index
explain select * from tb_user force index(idx_user_pro) where profession = '軟體工程';

覆蓋索引

我們希望儘量使用覆蓋索引，減少select * 操作。

• 覆蓋索引是指查詢使用了索引，並且需要返回的列，在該索引中已經全部能夠找到 。

# 當我們可以一次性獲得所有數據時就不再需要回表查詢操作，可以大大提高查詢速度

-- 例如：我們的主鍵為id，索引有name

-- 則下述我們在查詢name時，可以獲得id，這樣就獲得了所有數據，就不再進行回表查詢
SELECT * FROM table WHERE name = 'xxx';

-- 但如果包含其他元素，我們就會進行回表查詢，導致速度降低
SELECT * FROM table WHERE name = 'xxx' and status = '0';

首碼查詢

當欄位類型為字元串（varchar，text，longtext等）時，有時候需要索引很長的字元串，這會讓索引變得很大，查詢時，浪費大量的磁碟IO， 影響查詢效率。

此時可以只將字元串的一部分首碼，建立索引，這樣可以大大節約索引空間，從而提高索引效率。

# 選擇首碼長度來創建首碼索引
create index idx_xxxx on table_name(column(n)) ;

當然我們也需要得知如何取得最合適的首碼長度：

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

# 我們可以通過下述代碼找到 不同元素 和 全部元素 的比例從而獲得最佳首碼長度
select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

單列/聯合索引選擇

我們先來回顧一下單列索引和聯合索引：

• 單列索引：即一個索引只包含單個列。
• 聯合索引：即一個索引包含了多個列。

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

索引設計原則

我們的索引並非說是無條件的任意設計，我們針對索引的設計上有以下七條規則：

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

SQL優化

這部分我們來介紹SQL的優化

SQL的大部分優化主要是屬於我們手動的優化以及配合索引的優化

插入數據優化

插入數據優化有四個方面：

• 批量插入數據

# 我們在插入數據時，可以一次插入多條數據以進行SQL優化（可以一次插入500~1000條數據）
insert into tb_test values (1,'TOM'),(2,'JERRY')...;

• 手動提交事務

# SQL在每條語句後都進行提交會影響整體性能，我們可以手動提交以減輕電腦負擔

start transaction;

insert into tb_test values (1,'TOM'),(2,'JERRY')...;
insert into tb_test values (3,'TaM'),(4,'JyRRY')...;
insert into tb_test values (5,'TeM'),(6,'JiRRY')...;

commit;

• 主鍵順序插入

# 主鍵的順序插入會減輕SQL排序操作直接插入加快速度
主鍵插入：1，2，3，6，9，12，40，60...

• 大批量插入數據（簡單介紹）

如果一次性插入超大量數據，insert語句的插入性能就太低了，因而我們採用load方法插入：

# 如果想要更詳細瞭解，可以移步其他大佬的文章介紹~

-- 客戶端連接服務端時，加上參數 -–local-infile
mysql –-local-infile -u root -p

-- 設置全局參數local_infile為1，開啟從本地載入文件導入數據的開關
set global local_infile = 1;

-- 執行load指令將準備好的數據，載入到表結構中
load data local infile '/root/sql1.log' into table tb_user fieldsterminated by ',' lines terminated by '\n' ;

主鍵優化

首先我們要先瞭解InnoDB存儲引擎的數據排序：

• 在InnoDB存儲引擎中，表數據都是根據主鍵順序組織存放的，這種存儲方式的表稱為索引組織表

主鍵設計原則：

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

這裡原本應該具有拓展知識：頁合併和頁分裂

但因不好講解所以我把黑馬鄧老師的鏈接放在這裡，有興趣的小伙伴可以去查看：33. 進階-SQL優化-主鍵優化_嗶哩嗶哩_bilibili

Order by優化

order by排序具有兩種排序方式：

• Using filesort：

  • 通過表的索引或全表掃描，讀取滿足條件的數據行，然後在排序緩衝區sortbuffer中完成排序操作
  • 所有不是通過索引直接返回排序結果的排序都叫 FileSort 排序。

• Using index：

  • 通過有序索引順序掃描直接返回有序數據，這種情況即為 using index
  • 不需要額外排序，操作效率高。

# 我們通常直接排序（在不使用主鍵或者索引時）使用的是Using filesort
explain select id，age，phone from tb_user order by name;

# 但當我們通過主鍵排序或者使用索引後，採用Using index，速度提高
create index idx_user_age_phone_aa on tb_user(age,phone);

explain select id,age,phone from tb_user order by age，phone;

explain select id,age,phone from tb_user order by age desc，phone desc;

# 但是請註意：我們的索引排序也有具有ASC和DESC排序，當我們預設時均為ASC
# 當我們採用ASC，ASC或DESC，DESC時可以採用Using index，但若以ASC，DESC或DESC，ASC的形式時使用Using filesort
# 因而我們如果需要ASC，DESC或DESC，ASC的形式時需要再次創建index：create index idx_user_age_phone_aa on tb_user(age ASC,phone DESC);

create index idx_user_age_phone_aa on tb_user(age ASC,phone DESC);

explain select id,age,phone from tb_user order by age ASC，phone DESC;

Order by優化原則：

• 根據排序欄位建立合適的索引，多欄位排序時，也遵循最左首碼法則。
• 儘量使用覆蓋索引。
• 多欄位排序, 一個升序一個降序，此時需要註意聯合索引在創建時的規則（ASC/DESC）。
• 如果不可避免的出現filesort，大數據量排序時，可以適當增大排序緩衝區大小sort_buffer_size(預設256k)。

Group by優化

Group by優化同樣藉助索引進行優化：

# 當我們正常使用時，效率較低
explain select profession , count(*) from tb_user group by profession ;

# 但當我們建立索引後，效率會有明顯提升（註意同樣滿足索引的使用規範）
create index idx_user_pro_age_sta on tb_user(profession , age , status);

explain select profession , count(*) from tb_user group by profession ;

Group by優化原則：

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

Limit優化

limit用來作為分頁操作，我們常常在數據過多時對limit進行優化：

# 當我們希望獲得第900000個數據後的十個數據，就需要完全獲得前9000000個數據才可以，這會損耗許多時間

# 優化思路：
# 我們通過select只獲得第9000000個後的十個數據的id
# 然後通過id對比來獲得整行數據：

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

Count優化

針對count操作，不同存儲引擎有不同的處理方式：

• MyISAM：直接把表的總行數存儲在磁碟中，當運行count（*）時直接輸出
• InnoDB：需要一行一行讀取數據，進行累加

優化思路：

• 在添加數據和刪除數據時，同時存儲其數據數量

在這裡我們順便講解一下count的四種常見情況：

註意：

• count(1)和count(*)速度基本相近，均為最快速度
• 按照效率排序的話，count(欄位) < count(主鍵 id) < count(1) ≈ count（*)，所以儘量使用 count( *)。

Update優化

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

# 我們所需要註意的就是Update的操作儘量採用索引來進行改變，這樣鎖就會變成行鎖，只控制這一行數據
# 如果我們採用的Update的操作沒有使用索引，那麼就會採用表鎖，導致整個表的數據都無法改變，影響其他人同步修改該表

-- 這個就是採用行鎖，你可以在另一個伺服器同步修改該表中其他行
update course set name = 'javaEE' where id = 1 ;

-- 這個採用表鎖，你無法在另一個伺服器同步修改該表
update course set name = 'SpringBoot' where name = 'PHP' ;

視圖

視圖（View）是一種虛擬存在的表。

視圖中的數據並不在資料庫中實際存在，行和列數據來自定義視圖的查詢中使用的表，並且是在使用視圖時動態生成的。

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

視圖操作

視圖的操作主要分為四部分：

• 創建

# 創建視圖
# [WITH [CASCADED | LOCAL] CHECK OPTION] 表示限制條件，我們在後續會講到
CREATE [OR REPLACE] VIEW 視圖名稱[(列名列表)] AS SELECT語句 [WITH [CASCADED | LOCAL] CHECK OPTION]

• 查詢

# 查詢

-- 查看創建視圖語句
SHOW CREATE VIEW 視圖名稱;

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

• 修改

# 修改

-- 方法1：
CREATE [OR REPLACE] VIEW 視圖名稱[(列名列表)] AS SELECT語句 [WITH [CASCADED | LOCAL] CHECK OPTION]

-- 方法2：
ALTER VIEW 視圖名稱[(列名列表)] AS SELECT語句 [WITH [CASCADED | LOCAL] CHECK OPTION]

• 刪除

DROP VIEW [IF EXISTS] 視圖名稱；

註意：

• 在視圖中我們的INSERT等操作都是直接針對創建視圖的原表進行操作

CHECK檢查操作

視圖的CHECK檢查操作就是指[WITH [CASCADED | LOCAL] CHECK OPTION]這部分

首先我們要明白為什麼需要檢查操作：

# 我們在對視圖進行INSERT操作時，會直接對原表進行操作
# 但倘若我們對原表操作成功，但是對該視圖要求不符合，該操作結構是不會產生在視圖中的，導致原表改變但並未達到我們希望的效果

# 另一方面，我們對視圖的創建常常建立於另一視圖的操作
# 倘若我們不對此設置檢查，可能導致視圖創建失敗或對之前視圖操作失敗

當使用WITH CHECK OPTION子句創建視圖時，MySQL會通過視圖檢查正在更改的每個行，例如 插入，更新，刪除，以使其符合視圖的定義。

MySQL允許基於另一個視圖創建視圖，它還會檢查依賴視圖中的規則以保持一致性。

為了確定檢查的範圍，mysql提供了兩個選項： CASCADED 和 LOCAL，預設值為 CASCADED 。

1. CHECK操作

# 首先我們來介紹CHECK操作

-- CHECK操作會對視圖要求進行檢測並加以約束

-- 假設我們有一個 原表table含有age屬性

-- 下述視圖沒有設置CHECK
CREATE VIEW table_view1 AS SELECT id,age FROM table WHERE age > 20;
-- 當我們對view1操作時,如果添加的數據沒有大於20，仍舊會執行成功
INSERT INTO table_view1 values (1,18)；

-- 下述視圖設置CHECK
CREATE VIEW table_view2 AS SELECT id,age FROM table WHERE age > 20 WITH CASCADED CHECK OPTION;
-- 當我們對view1操作時,如果添加的數據沒有大於20，不會執行成功
INSERT INTO table_view2 values (1,18)；

2. CASCADED操作

# 下麵我們介紹CASCADED操作

-- CASCADED：不僅為當前視圖檢查條件，而且為當前視圖的之前視圖檢查條件

-- 假設我們有一個 原表table含有age屬性

-- 下述視圖沒有設置CHECK
CREATE VIEW table_view1 AS SELECT id,age FROM table WHERE age > 20;

-- 下述視圖view2以view1為模板設置檢查條件
CREATE VIEW table_view2 AS SELECT id,age FROM table WHERE age < 25 WITH CASCADED CHECK OPTION;
-- 這時，我們所添加的數據不僅需要滿足當前條件age<25,並且需要滿足上一視圖條件age>20
INSERT INTO table_view2 values (1,23)；

3. LOCAL操作

# 下麵我們介紹LOCAL操作

-- LOCAL：只為當前視圖檢查條件，不為之前視圖設置條件

-- 假設我們有一個 原表table含有age屬性

-- 下述視圖沒有設置CHECK
CREATE VIEW table_view1 AS SELECT id,age FROM table WHERE age > 20;

-- 下述視圖view2以view1為模板設置檢查條件
CREATE VIEW table_view2 AS SELECT id,age FROM table WHERE age < 25 WITH LOCAL CHECK OPTION;
-- 這時，我們所添加的數據只需要滿足當前條件age<25即可
INSERT INTO table_view2 values (1,10)；

更新

視圖的更新具有一定的嚴格性

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

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

• 聚合函數或視窗函數（SUM()、 MIN()、 MAX()、 COUNT()等）
• DISTINCT
• GROUP BY
• HAVING
• UNION 或 UNION ALL

作用

視圖一般具有四大作用：

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

存儲過程和存儲函數

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

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

存儲過程具有以下三大特點：

• 封裝，復用
  • 可以把某一業務SQL封裝在存儲過程中，需要用到的時候直接調用即可。
• 可以接收參數，也可以返回參數
  • 再存儲過程中，可以傳遞參數，也可以接收返回值。
• 減少網路交互，效率提高
  • 如果涉及到多條SQL，每執行一次都是一次網路傳輸。 而如果封裝在存儲過程中，我們只需要網路交互一次可能就可以了。

基本語法

關於存儲函數的基本語法我們大致分為四類：

• 創建

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

• 調用

# 調用存儲過程
CALL 名稱（[參數]）

• 查看

# 查看存儲過程

-- 查詢指定資料庫的存儲過程及狀態信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'XXX';

-- 查詢某個存儲過程的定義
HOW CREATE PROCEDURE 存儲過程名稱 ;

• 刪除

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

三種變數

存儲過程中存在三種變數：

• 系統變數
• 用戶自定義變數
• 局部變數

讓我們一一介紹：

1. 系統變數

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

我們直接給出相關變數操作代碼：

# 查看系統變數

-- 查看所有系統變數
SHOW [SESSION | GLOBAL] VARIABLES;

-- 可以通過LIKE模糊匹配查找變數
SHOW [SESSION | GLOBAL] VARIABLES LIKE '...';

-- 直接查看指定變數
SELECT @@[SESSION | GLOBAL].系統變數名;

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

註意：

• 如果沒有指定SESSION/GLOBAL，預設是SESSION，會話變數
• mysql服務重新啟動之後，所設置的全局參數會失效，要想不失效，可以在 /etc/my.cnf 中配置。

兩者區別：

• 全局變數(GLOBAL): 全局變數針對於所有的會話。
• 會話變數(SESSION): 會話變數針對於單個會話，在另外一個會話視窗就不生效了。

2. 用戶自定義變數

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

我們直接給出相關變數操作代碼：

# 賦值

-- 直接賦值
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;
SELECT @var_name := expr [, @var_name := expr] ... ;

-- 從表中抽取數據進行賦值
SELECT 欄位名 INTO @var_name FROM 表名;

# 使用
SELECT @var_name ;

註意：

• 註意: 用戶定義的變數無需對其進行聲明或初始化，只不過獲取到的值為NULL。

3. 局部變數

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

我們直接給出相關變數操作代碼：

# 聲明

-- [DEFAULT ...]表示設置初始化值
DECLARE 變數名 變數類型[DEFAULT ...]

# 賦值

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

七種結構

在MYSQL中同樣設置了相關判斷和迴圈結構：

• if
• case
• while
• repeat
• loop
• cursor
• handler

在介紹迴圈結構之前，我們先來介紹一下參數設置：

用法如下：

CREATE PROCEDURE 存儲過程名稱 ([ IN/OUT/INOUT 參數名 參數類型 ])
BEGIN
-- SQL語句
END ;

接下來讓我們一一介紹結構：

1. IF結構

if 用於做條件判斷，具體的語法結構為：

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

2. CASE結構

case結構及作用，和我們在基礎篇中所講解的流程式控制制函數很類似。

# case結構1：

-- 當case_value的值為 when_value1時，執行statement_list1，
-- 當值為 when_value2時，執行statement_list2，
-- 否則就執行 statement_list

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

# case結構2：

-- 當條件search_condition1成立時，執行statement_list1，
-- 當條件search_condition2成立時，執行statement_list2， 
-- 否則就執行 statement_list

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

3. WHILE結構

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

# while迴圈結構

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

4. REPEAT結構

repeat是有條件的迴圈控制語句, 當滿足until聲明的條件的時候，則退出迴圈 。

# repeat迴圈結構

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

5. LOOP結構

LOOP 實現簡單的迴圈，如果不在SQL邏輯中增加退出迴圈的條件，可以用其來實現簡單的死迴圈。

loop結構需要配合兩個語句使用：

• LEAVE ：配合迴圈使用，退出迴圈。
• ITERATE：必須用在迴圈中，作用是跳過當前迴圈剩下的語句，直接進入下一次迴圈。

# loop迴圈結構

-- begin_label可以自己設置
-- 退出指定標記的迴圈體：LEAVE label;
-- 直接進入下一次迴圈： ITERATE label;

[begin_label:] LOOP
	SQL邏輯...
END LOOP [end_label];

我們給出相關案例進行解釋：

# 要求：計算從1到n之間的偶數累加的值，n為傳入的參數值。

-- A. 定義局部變數, 記錄累加之後的值;
-- B. 每迴圈一次, 就會對n進行-1 , 如果n減到0, 則退出迴圈 ----> leave xx
-- C. 如果當次累加的數據是奇數, 則直接進入下一次迴圈. --------> iterate xx

-- 創建存儲過程
create procedure p10(in n int)
begin
	-- 設置返回值
    declare total int default 0;
    
    -- 進入loop迴圈，sum是label標記
    sum:loop
    
    -- 整體結束判斷：如果n減到0, 則退出迴圈
    if n<=0 then
    leave sum;
    end if;
    
    -- 單個判斷：如果當次累加的數據是奇數, 則直接進入下一次迴圈.
    if n%2 = 1 then
    set n := n - 1;
    iterate sum;
    end if;
    
    -- 如果沒有問題，最後執行語句
    set total := total + n;
    set n := n - 1;
    
    -- 結束loop迴圈
    end loop sum;
    
    -- 輸出結果
    select total;
end;

-- 執行存儲過程
call p10(100);

6. CURSOR結構

游標（CURSOR）是用來存儲查詢結果集的數據類型 , 在存儲過程和函數中可以使用游標對結果集進行迴圈的處理。

游標的使用包括游標的聲明、OPEN、FETCH 和 CLOSE，其語法分別如下：

# 聲明游標
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;

-- 調用存儲過程
call p11(30);

7. HANDLER結構

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

# handler結構

DECLARE handler_action HANDLER FOR condition_value [, condition_value]... statement ;

handler_action 的取值：
CONTINUE: 繼續執行當前程式
EXIT: 終止執行當前程式

condition_value 的取值：
SQLSTATE sqlstate_value: 狀態碼，如 02000
SQLWARNING: 所有以01開頭的SQLSTATE代碼的簡寫
NOT FOUND: 所有以02開頭的SQLSTATE代碼的簡寫
SQLEXCEPTION: 所有沒有被SQLWARNING 或 NOT FOUND捕獲的SQLSTATE代碼的簡寫

我們給出相關案例進行解釋：

# 在上述的游標案例中，我們的while迴圈無法關閉，因為無法判斷游標中的值何時結束
# 所以我們需要採用HANDLER結構來關閉WHILE迴圈

-- 創建存儲過程
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;
    
    -- 聲明條件處理程式 ： 當SQL語句執行拋出的狀態碼為02000時，將關閉游標u_cursor，並退出
    -- 這裡也可以採用NOT FOUND 狀況碼進行判斷並退出
	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;

-- 調用存儲過程
call p11(30);

存儲函數

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

# 存儲函數創建結構

CREATE FUNCTION 存儲函數名稱 ([ 參數列表 ])
RETURNS type [characteristic ...]
BEGIN
    -- SQL語句
    RETURN ...;
END ;

# characteristic 可以有以下三種情況：

-- DETERMINISTIC：相同的輸入參數總是產生相同的結果
-- NO SQL ：不包含 SQL 語句。
-- READS SQL DATA：包含讀取數據的語句，但不包含寫入數據的語句。

我們給出相關案例進行解釋：

# 要求：計算從1累加到n的值，n為傳入的參數值。

-- 創建存儲函數
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;

-- 執行存儲函數
select fun1(50);

觸發器

觸發器是與表有關的資料庫對象，指在insert/update/delete之前(BEFORE)或之後(AFTER)，觸發並執行觸發器中定義的SQL語句集合。

觸發器的這種特性可以協助應用在資料庫端確保數據的完整性, 日誌記錄 , 數據校驗等操作 。

使用別名OLD和NEW來引用觸發器中發生變化的記錄內容，這與其他的資料庫是相似的。

現在觸發器還只支持行級觸發，不支持語句級觸發。

基本語法

觸發器的基本語法主要分為三種：

• 創建

# 創建觸發器

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW 
BEGIN
    trigger_stmt ;
END;

# 解釋

-- BEFORE/AFTER 表示是在操作進行前/操作進行後觸發
-- INSERT/UPDATE/DELETE 表示操作類型
-- FOR EACH ROW 表示行級觸發器

• 查看

# 查看觸發器

SHOW TRIGGERS ;

• 刪除

# 刪除觸發器

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

案例解釋

我們直接給出一個大案例來對觸發器進行講解

要求：

• 通過觸發器記錄 tb_user 表的數據變更日誌，將變更日誌插入到日誌表user_logs中, 包含增加,修改 , 刪除 ;

1. 日誌表user_logs創建：

create table user_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作類型, insert/update/delete',
    operate_time datetime not null comment '操作時間',
    operate_id int(11) not null comment '操作的ID',
    operate_params varchar(500) comment '操作參數',
    primary key(`id`)
)engine=innodb default charset=utf8;

2. 插入數據觸發器：

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;

3. 修改數據觸發器 :

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;

4. 刪除數據觸發器:

create trigger tb_user_delete_trigger
after delete on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES
    (null, 'delete', now(), old.id,concat('刪除之前的數據: id=',old.id,',name=',old.name, ', 					phone=',old.phone, ', email=', old.email, ', profession=', old.profession));
end;

鎖

鎖是電腦協調多個進程或線程併發訪問某一資源的機制。（類似java的LOCK）

在資料庫中，除傳統的計算資源（CPU、RAM、I/O）的爭用以外，數據也是一種供許多用戶共用的資源。

如何保證數據併發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫併發訪問性能的一個