MYSQL（進階篇）——一篇文章帶你深入掌握MYSQL
MYSQL（進階篇）——一篇文章帶你深入掌握MYSQL
我們在上篇文章中已經學習了MYSQL的基本語法和概念
在這篇文章中我們將講解底層結構和一些新的語法幫助你更好的運用MYSQL
溫馨提醒：該文章大約20000字，建議關注收藏慢慢觀看，希望能給你帶來幫助~
進階篇內容目錄
這篇文章我們主要分為七個部分：
- 存儲引擎
- 索引
- SQL優化
- 視圖/存儲過程/觸發器
- 鎖
- InnoDB引擎
- MYSQL管理
存儲引擎
在講解存儲引擎前我們先來了解一下MYSQL的整體體系結構
MYSQL整體分為四個部分：
- 連接層
- 服務層
- 引擎層
- 存儲層
存儲引擎簡介
存儲引擎概念：
- 存儲數據，建立索引，更新/查詢數據等技術的實現方式
存儲引擎注意點：
- 存儲引擎是基於表的，而不是基於庫的，所以不同表可以有不同的存儲引擎，同時存儲引擎也被稱為表類型
首先我們要先直到存儲引擎是在哪裡定義的：
# 下面是一個表的創建語句
create table Name (
~~~~~~~~
)engine = InnoDB;
# 在上面的engine = 存儲引擎類型 就是存儲引擎的設計語句
# 我們默認情況下是InoDB存儲引擎
同樣我們可以通過查看創建方法來得到表的存儲引擎類型：
show create table 表名;
存儲引擎類型以及特點
我們可以在DG或其他資料庫軟體中直接查看存儲引擎的類型：
# 下述程式碼會給出該資料庫中支援的存儲引擎類型
show engines;
在這裡我們僅詳細介紹三種存儲引擎：
- InnoDB
介紹：
- InnoDB是一種兼顧可靠性和高性能的通用存儲引擎，在MYSQL5.5之後，稱為MYSQL默認存儲引擎
特點：
- DML操作遵循ACID模型，支援事務
- 行級鎖，提高並發訪問性能
- 支援 外鍵 FOREIGN KEY約束，保證數據的完整性和正確性
文件：
- xxx.ibd:xxx表示表名，innoDB引擎的每張表對應一個表空間文件，存儲該表的表結構，數據和索引
- 我們可以通過show variables like 『innodb_file_per_table『來查看查看存儲引擎分類情況（是否共用一個存儲引擎）
- 如果為ON表示一個表分配一個ibd文件
- MyISAM
介紹：
- MyISAM是MYSQL早期的默認存儲引擎
特點：
- 不支援事務，不支援外鍵
- 支援表鎖，不支援行鎖
- 訪問速度快
文件：
- xxx.sdi:存儲表結構資訊
- xxx.MYD:存儲數據
- xxx.MYI:存儲索引
- Memory
介紹：
- Memory引擎的表數據是存儲在記憶體中的，由於受到硬體問題或斷電問題，只能作為臨時表或快取使用
特點：
- 記憶體存放
- hash索引（默認）
文件：
- xxx.sdi:存儲表結構資訊
三者區別：
| 特點 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| 存儲限制 | 64TB | 有 | 有 |
| 事務安全 | 支援 | – | – |
| 鎖機制 | 行鎖 | 表鎖 | 表鎖 |
| B+tree索引 | 支援 | 支援 | 支援 |
| Hash索引 | – | – | 支援 |
| 全文索引 | 支援（5.6版本） | 支援 | – |
| 空間使用 | 高 | 低 | N/A |
| 記憶體使用 | 高 | 低 | 中等 |
| 批量插入速度 | 低 | 高 | 高 |
| 支援外鍵 | 支援 | – | – |
存儲引擎選擇
我們在各個表都可以選擇不同的存儲引擎，而存儲引擎的選擇大多遵循以下特徵：
優選InnoDB：
- MYSQL的默認存儲引擎，支援事務，外鍵
- 如果應用對事務的完整性有較高要求，並在並發條件下要求數據的一致性，數據操作除了插入和查詢外，還包括很多的更新和刪除操作，那麼InnoDB存儲引擎是比較合理的選擇
優選MyISAM：
- 如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，並對事務的完整性，並發性要求不高，那麼MyISAM存儲引擎是比較合理的選擇
優選Memory：
- 將所有數據保存到記憶體中，訪問速度快，常用於臨時表和快取
- Memory的缺陷是對錶的大小有限制，太大的表無法存儲在記憶體中，且無法保證安全性
索引
首先我們來簡略的介紹一下索引：
- 索引是幫助MYSQL高效獲得數據的數據結構（有序）
- 在數據之外，資料庫系統還維護著滿足特定查找演算法的數據結構，這些數據結構以某種方法引用數據，實現查找
索引的優點：
- 提高數據檢索的效率，降低資料庫的IO成本
- 通過索引列對數據進行排序，降低數據排序的成本，降低CPU的消耗。
索引的缺點：
- 索引列也是要佔用空間的。
- 索引大大提高了查詢效率，同時卻也降低更新表的速度， 如對錶進行INSERT、UPDATE、DELETE時，效率降低。
索引結構
MySQL的索引是在存儲引擎層實現的，因而不同的存儲引擎有不同的索引結構 ：
| 索引結構 | 描述 |
|---|---|
| B+Tree索引 | 最常見的索引類型，大部分引擎都支援 B+ 樹索引 |
| Hash索引 | 底層數據結構是用哈希表實現的, 只有精確匹配索引列的查詢才有效, 不 支援範圍查詢 |
| R-tree索引 | 空間索引是MyISAM引擎的一個特殊索引類型，主要用於地理空間數據類 型，通常使用較少 |
| Full-text索引 | 是一種通過建立倒排索引,快速匹配文檔的方式。類似於 Lucene,Solr,ES |
不同的索引結構也有不同的適配情況：
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+Tree索引 | √ | √ | √ |
| Hash索引 | × | × | √ |
| R-tree索引 | × | √ | × |
| Full-text索引 | √ | √ | × |
在這裡我們僅對B+Tree索引和Hash索引做出詳細解釋：
- B+Tree索引
以一顆最大度數為4的B+Tree結構為例：
圖片內容解釋：
- 綠色框框起來的部分，是索引部分，僅僅起到索引數據的作用，不存儲數據。
- 紅色框框起來的部分，是數據存儲部分，在其葉子節點中要存儲具體的數據。
注意：
- 上面節點僅用於快速定位數據位置，且所有的數據元素均會出現在葉節點
- 葉節點之間用單項鏈接相連，形成鏈表類型
MYSQL中的B+Tree索引在B+Tree結構上對葉節點進行了一點改造：
- 在B+Tree結構上增加了一個指向相鄰葉子節點的鏈表指針，形成了帶有順序指針的B+Tree，提高區間訪問性能
- 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支援範圍匹配及排序操作；
索引分類
我們根據索引類型常常把索引分為四種：
| 分類 | 含義 | 特點 | 關鍵字 |
|---|---|---|---|
| 主鍵索引 | 針對於表中主鍵創建的索引 | 默認自動創建, 只能 有一個 | PRIMARY |
| 唯一索引 | 避免同一個表中某數據列中的值重複 | 可以有多個 | UNIQUE |
| 常規索引 | 快速定位特定數據 | 可以有多個 | |
| 全文索引 | 全文索引查找的是文本中的關鍵詞，而不是比 較索引中的值 | 可以有多個 | FULLTEXT |
在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所給出的資訊含義：
| 欄位 | 含義 |
|---|---|
| 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 的值越大越好。 |
索引使用
我們在前面已經學了索引的創建，索引實際上大部分是由系統決定使用類型，但我們也有需要注意的地方
時間差異
我們創建索引時需要遍歷所有數據，因而創建索引時的時間相當於我們不創建索引而查詢數據的時間
但當我們創建索引後去查詢數據，就會發現時間大大減少
# 假設我們的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;
聯合索引注意點
我們在使用聯合索引時需要注意以下兩點：
- 最左前綴法則
如果索引了多列（聯合索引），要遵守最左前綴法則。
最左前綴法則指的是查詢從索引的最左列開始，並且不跳過索引中的列。如果跳躍某一列，索引將會部分失效(後面的欄位索引失效)。
# 例如我們在前面建立了一個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的任意位置不受影響
- 範圍查詢
在聯合索引中，不允許出現（>,<），範圍查詢的右側列索引失效
# 我們如果使用 <,> 後面的索引將不再生效
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';
索引失效注意點
針對於索引失效的常見情況共有五種：
- 索引列運算
我們不能在索引列上繼續運算，否則索引失效
# 如果我們採用substring等操作，索引列將失效
explain select * from tb_user where substring(phone,10,2) = '15';
- 字元串未加引號
字元串類型欄位使用時，不加引號，索引將失效
# 這裡最後一部分status的0未加引號，數據仍舊可以輸出，但不再通過索引查詢
explain select * from tb_user where profession = '軟體工程' and age = 31 and status= 0;
- 模糊查詢
索引中禁止頭部出現模糊查詢
# 如果頭部出現%，索引失效
-- 索引有效
explain select * from tb_user where profession like '軟體%';
-- 索引失效
explain select * from tb_user where profession like '%工程';
-- 索引失效
explain select * from tb_user where profession like '%工%';
- or連接條件
用or分割開的條件， 如果or前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。
# 如果phone被設置有索引 但age未設置索引 ，則採用普通查詢方法不採用索引
explain select * from tb_user where phone = '17799990017' or age = 23;
- 數據分布影響
如果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用法 | 含義 |
|---|---|
| count(主鍵) | InnoDB 引擎會遍歷整張表，把每一行的 主鍵id 值都取出來，返回給服務層。 服務層拿到主鍵後，直接按行進行累加(主鍵不可能為null) |
| count(欄位) | 沒有not null 約束 : InnoDB 引擎會遍歷整張表把每一行的欄位值都取出 來，返回給服務層，服務層判斷是否為null，不為null，計數累加。 有not null 約束：InnoDB 引擎會遍歷整張表把每一行的欄位值都取出來，返 回給服務層，直接按行進行累加。 |
| count(1) | InnoDB 引擎遍歷整張表，但不取值。服務層對於返回的每一行，放一個數字「1」 進去，直接按行進行累加。 |
| count(*) | InnoDB引擎並不會把全部欄位取出來，而是專門做了優化，不取值，服務層直接按行進行累加。 |
注意：
- 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 。
- 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)；
- 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)；
- 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] 存儲過程名稱;
三種變數
存儲過程中存在三種變數：
- 系統變數
- 用戶自定義變數
- 局部變數
讓我們一一介紹：
- 系統變數
系統變數 是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): 會話變數針對於單個會話，在另外一個會話窗口就不生效了。
- 用戶自定義變數
用戶定義變數 是用戶根據需要自己定義的變數，用戶變數不用提前聲明，在用的時候直接用 “@變數名” 使用就可以。其作用域為當前連接。
我們直接給出相關變數操作程式碼：
# 賦值
-- 直接賦值
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;
SELECT @var_name := expr [, @var_name := expr] ... ;
-- 從表中抽取數據進行賦值
SELECT 欄位名 INTO @var_name FROM 表名;
# 使用
SELECT @var_name ;
注意：
- 注意: 用戶定義的變數無需對其進行聲明或初始化，只不過獲取到的值為NULL。
- 局部變數
局部變數 是根據需要定義的在局部生效的變數，訪問之前，需要DECLARE聲明。可用作存儲過程內的局部變數和輸入參數，局部變數的範圍是在其內聲明的BEGIN … END塊。
我們直接給出相關變數操作程式碼：
# 聲明
-- [DEFAULT ...]表示設置初始化值
DECLARE 變數名 變數類型[DEFAULT ...]
# 賦值
SET 變數名 = 值 ;
SET 變數名 := 值 ;
SELECT 欄位名 INTO 變數名 FROM 表名 ... ;
七種結構
在MYSQL中同樣設置了相關判斷和循環結構：
- if
- case
- while
- repeat
- loop
- cursor
- handler
在介紹循環結構之前，我們先來介紹一下參數設置：
| 類型 | 含義 | 備註 |
|---|---|---|
| IN | 該類參數作為輸入，也就是需要調用時傳入值 | 默認 |
| OUT | 該類參數作為輸出，也就是該參數可以作為返回值 | |
| INOUT | 既可以作為輸入參數，也可以作為輸出參數 |
用法如下：
CREATE PROCEDURE 存儲過程名稱 ([ IN/OUT/INOUT 參數名 參數類型 ])
BEGIN
-- SQL語句
END ;
接下來讓我們一一介紹結構：
- IF結構
if 用於做條件判斷，具體的語法結構為：
IF 條件1 THEN
.....
ELSEIF 條件2 THEN -- 可選
.....
ELSE -- 可選
.....
END IF;
- 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;
- WHILE結構
while 循環是有條件的循環控制語句。滿足條件後，再執行循環體中的SQL語句。
# while循環結構
-- 先判定條件，如果條件為true，則執行邏輯，否則，不執行邏輯
WHILE 條件 DO
SQL邏輯...
END WHILE;
- REPEAT結構
repeat是有條件的循環控制語句, 當滿足until聲明的條件的時候，則退出循環 。
# repeat循環結構
-- 先執行一次邏輯，然後判定UNTIL條件是否滿足，如果滿足，則退出。如果不滿足，則繼續下一次循環
REPEAT
SQL邏輯...
UNTIL 條件
END REPEAT;
- 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);
- 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);
- 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來引用觸發器中發生變化的記錄內容，這與其他的資料庫是相似的。
現在觸發器還只支援行級觸發，不支援語句級觸發。
| 觸發器類型 | NEW和OLD |
|---|---|
| INSERT觸發器 | NEW 表示將要或者已經新增的數據 |
| UPDATE觸發器 | OLD 表示修改之前的數據 , NEW 表示將要或已經修改後的數據 |
| DELETE觸發器 | OLD 表示將要或者已經刪除的數據 |
基本語法
觸發器的基本語法主要分為三種：
- 創建
# 創建觸發器
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW
BEGIN
trigger_stmt ;
END;
# 解釋
-- BEFORE/AFTER 表示是在操作進行前/操作進行後觸發
-- INSERT/UPDATE/DELETE 表示操作類型
-- FOR EACH ROW 表示行級觸發器
- 查看
# 查看觸發器
SHOW TRIGGERS ;
- 刪除
# 刪除觸發器
DROP TRIGGER [schema_name.]trigger_name ;
-- 如果沒有指定 schema_name，默認為當前資料庫 。
案例解釋
我們直接給出一個大案例來對觸發器進行講解
要求：
- 通過觸發器記錄 tb_user 表的數據變更日誌，將變更日誌插入到日誌表user_logs中, 包含增加,修改 , 刪除 ;
- 日誌表user_logs創建：
create table user_logs(
id int(11) not null auto_increment,
operation varchar(20) not null comment '操作類型, insert/update/delete',
operate_time datetime not null comment '操作時間',
operate_id int(11) not null comment '操作的ID',
operate_params varchar(500) comment '操作參數',
primary key(`id`)
)engine=innodb default charset=utf8;
- 插入數據觸發器：
create trigger tb_user_insert_trigger
after insert on tb_user for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
(null, 'insert', now(), new.id, concat('插入的數據內容為:id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ',profession=', NEW.profession));
end;
- 修改數據觸發器 :
create trigger tb_user_update_trigger
after update on tb_user for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
(null, 'update', now(), new.id,concat('更新之前的數據: id=',old.id,',name=',old.name, ', phone=',old.phone, ', email=', old.email, ', profession=', old.profession,' | 更新之後的數據: id=',new.id,',name=',new.name, ', phone=',NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;
- 刪除數據觸發器:
create trigger tb_user_delete_trigger
after delete on tb_user for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
(null, 'delete', now(), old.id,concat('刪除之前的數據: id=',old.id,',name=',old.name, ', phone=',old.phone, ', email=', old.email, ', profession=', old.profession));
end;
鎖
鎖是電腦協調多個進程或執行緒並發訪問某一資源的機制。（類似java的LOCK）
在資料庫中，除傳統的計算資源（CPU、RAM、I/O）的爭用以外，數據也是一種供許多用戶共享的資源。
如何保證數據並發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫並發訪問性能的一個重要因素。
從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加複雜。
MySQL中的鎖，按照鎖的粒度分，分為以下三類：
- 全局鎖：鎖定資料庫的所有表
- 表級鎖：每次操作鎖住整張表
- 行級鎖：每次操作鎖住對應的行數據
全局鎖
全局鎖就是對整個資料庫實例加鎖，加鎖後整個實例就處於只讀狀態，後續的DML的寫語句，DDL語句，已經更新操作的事務提交語句都將被阻塞。
其典型的使用場景是做全庫的邏輯備份，對所有的表進行鎖定，從而獲取一致性視圖，保證數據的完整性。
# 這裡對 全庫的邏輯備份 做出解釋
-- 當我們進行全庫邏輯備份時，在實際開發不止一台客戶端操作該資料庫
-- 整個資料庫的保存備份需要一定時間，如果我們在保存過程中，有其他客戶端用戶進行操作，就會導致備份與原稿不同
下面對全局鎖的語法進行介紹：
# 設置全局鎖
flush tables with read lock;
# 進行備份
mysqldump -uroot -p123456 itcast > itcaset.sql
-- -u後加帳號 -p後加密碼 itcast為資料庫名稱 itcast.sql為本地地址
# 關閉全局鎖
unlock tables;
全局鎖特點：
- 如果在主庫上備份，那麼在備份期間都不能執行更新，業務基本上就得停擺
- 如果在從庫上備份，那麼在備份期間從庫不能執行主庫同步過來的二進位日誌（binlog），會導致主從延遲。
因而為了防止全局業務停擺，MYSQL給出了一種新的備份方法：
# 在InnoDB引擎中，我們可以在備份時加上參數 --single-transaction 參數來完成不加鎖的一致性數據備份。
mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql
表級鎖
表級鎖，每次操作鎖住整張表。
鎖定粒度大，發生鎖衝突的概率最高，並發度最低。應用在MyISAM、InnoDB、BDB等存儲引擎中。
對於表級鎖，主要分為以下三類：
- 表鎖
- 元數據鎖（MDL）
- 意向鎖
表鎖
表鎖一般分為兩種：
- 表共享讀鎖：對於所有客戶端均可以進行讀操作，但均不可進行寫操作
- 表獨佔寫鎖：僅對當前客戶端可以進行讀寫操作，其他客戶端不可操作
語法：
# 加鎖操作：
lock tables 表名... read/write;
# 釋放鎖：
unlock tables / 斷開客戶端連接
總結：
- 讀鎖不會阻塞其他客戶端的讀，但是會阻塞寫。
- 寫鎖既會阻塞其他客戶端的讀，又會阻塞其他客戶端的寫。
元數據鎖（MDL）
MDL加鎖過程是系統自動控制，無需顯式使用，在訪問一張表的時候會自動加上。
MDL鎖主要作用：
- 維護表元數據的數據一致性，在表上有活動事務的時候，不可以對元數據進行寫入操作
- 為了避免DML與DDL衝突，保證讀寫的正確性。
- 某一張表涉及到未提交的事務時，是不能夠修改這張表的表結構的。
MDL具體操作為：
- 當對一張表進行增刪改查的時候，加MDL讀鎖(共享)
- 當對錶結構進行變更操作的時候，加MDL寫鎖(排他)
| 對應SQL | 鎖類型 | 說明 |
|---|---|---|
| lock tables xxx read / write | SHARED_READ_ONLY / SHARED_NO_READ_WRITE | |
| select 、select … lock in share mode | SHARED_READ | 與SHARED_READ、 SHARED_WRITE兼容，與 EXCLUSIVE互斥 |
| insert 、update、 delete、select … for update | SHARED_WRITE | 與SHARED_READ、 SHARED_WRITE兼容，與 EXCLUSIVE互斥 |
| alter table … | EXCLUSIVE | 與其他的MDL都互斥 |
下面我們給出案例演示：
- 案例1（SHARED_READ、 SHARED_WRITE）
# 注意：下述客戶端1，2號是同時進行，同時開始事務！
# 這裡是 客戶端1號
-- 首先我們需要開啟事務，才能體現鎖的作用
begin;
-- 這裡我們使用select 、select ... lock in share mode 開啟了SHARED_READ鎖
select * from table;
-- 然後我們提交事務
commit;
# 這裡是 客戶端2號
-- 我們同樣採用select 、select ... lock in share mode ，這裡回饋的是SHARED_READ鎖，與前面的SHARED_READ鎖兼容，可以運行
select * from table1;
-- 這裡我們採用insert方法，回饋為SHARED_WRITE鎖，與前面的SHARED_READ鎖兼容，可以運行
update table1 set name = '葉' where id = '1';
-- 然後我們提交事務
commit;
- 案例2（SHARED_READ、 SHARED_WRITE 和 EXCLUSIVE）
# 注意：下述客戶端1，2號是同時進行，同時開始事務！
# 這裡是 客戶端1號
-- 首先我們需要開啟事務，才能體現鎖的作用
begin;
-- 這裡我們使用select 、select ... lock in share mode 開啟了SHARED_READ鎖
select * from table;
-- 然後我們提交事務
commit;
# 這裡是 客戶端2號
-- 這裡我們採用alter方法，這裡觸發的是EXCLUSIVE鎖，與之前的SHARED_READ鎖不兼容
-- 則一直等待，直至客戶端1號提交後，才會進行操作
alter table table1 add java int;
-- 然後我們提交事務
commit;
下面我們提供一條語句進行鎖的查看：
# 查看所有鎖
select object_type,object_schema,object_name,lock_type,lock_duration fromperformance_schema.metadata_locks ;
意見鎖
為了避免DML在執行時，加的行鎖與表鎖的衝突
在InnoDB中引入了意向鎖，使得表鎖不用檢查每行數據是否加鎖，使用意向鎖來減少表鎖的檢查。
首先我們先來模擬一下加鎖環節：
# 這裡是沒有意見鎖的情況下：
-- 我們在客戶端1中使用了行級鎖，這時就不能使用表級鎖
-- 表級鎖在使用前，需要一行一行檢查該行是否具有行級鎖，在確認所有行均沒有行級鎖後才可以進行表級鎖
-- 這種操作效率低下
# 這裡是有意見鎖的情況下：
-- 我們在客戶端1中使用了行級鎖並且設置了意見鎖，這時就不能使用表級鎖
-- 表級鎖在使用前，只需要檢查是否含有意見鎖，若含有則等待至意見鎖消失（客戶端1事務結束）才可以進行表級鎖
-- 這種操作效率較高
意見鎖分為兩種：
- 意向共享鎖(IS):
- 由語句select … lock in share mode添加 。
- 與 表鎖共享鎖(read)兼容，與表鎖排他鎖(write)互斥。
- 意向排他鎖(IX):
- 由insert、update、delete、select…for update添加 。
- 與表鎖共享鎖(read)及排他鎖(write)都互斥，意向鎖之間不會互斥。
我們同樣給出案例解釋：
- 意向共享鎖(IS):
# 這裡是客戶端1
-- 開啟事務
begin；
-- 首先我們設置意見鎖：
select * from table1 lock in share mode;
-- 提交事務
commit;
#這裡是客戶端2
-- 開啟事務
begin;
-- 在IS中，我們只能進行read表鎖操作
lock tables table1 read;-- 運行成功
-- 在IS中，我們無法進行write操作
lock tables table1 write;-- 運行失敗
-- 關閉鎖
unlock tables;
-- 關閉事務
commit;
- 意向排他鎖(IX):
# 這裡是客戶端1
-- 開啟事務
begin；
-- 首先我們設置意見鎖：
select * from table1 lock in share mode;
-- 提交事務
commit;
#這裡是客戶端2
-- 開啟事務
begin;
-- 在IX中，我們無法進行read表鎖操作
lock tables table1 read;-- 運行失敗，進行堵塞，當客戶端提交事務後才可進行
-- 在IX中，我們無法進行write操作
lock tables table1 write;-- 運行失敗，進行堵塞，當客戶端提交事務後才可進行
-- 關閉鎖
unlock tables;
-- 關閉事務
commit;
行級表
行級鎖，每次操作鎖住對應的行數據。鎖定粒度最小，發生鎖衝突的概率最低，並發度最高。應用在InnoDB存儲引擎中。
InnoDB的數據是基於索引組織的，行鎖是通過對索引上的索引項加鎖來實現的，而不是對記錄加的鎖。
對於行級鎖，主要分為以下三類：
-
行鎖：
- 鎖定單個行記錄的鎖，防止其他事務對此行進行update和delete。
- 在RC、RR隔離級別下都支援。
-
間隙鎖（Gap Lock）：
- 鎖定索引記錄間隙（不含該記錄），確保索引記錄間隙不變，防止其他事務在這個間隙進行insert，產生幻讀。
- 在RR隔離級別下都支援。
-
臨鍵鎖（Next-Key Lock）：
- 行鎖和間隙鎖組合，同時鎖住數據，並鎖住數據前面的間隙Gap。
- 在RR隔離級別下支援。
行鎖
InnoDB實現了以下兩種類型的行鎖 ：
- 共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排它鎖。
- 排他鎖（X）：允許獲取排他鎖的事務更新數據，阻止其他事務獲得相同數據集的共享鎖和排他鎖。
注意：
- 僅當共享鎖和共享鎖共存時兼容
- 其他情況兼不兼容
下面我們給出不同SQL語句相對應的行鎖級別：
| SQL | 行鎖類型 | 說明 |
|---|---|---|
| INSERT | 排他鎖 | 自動加鎖 |
| UPDATE | 排他鎖 | 自動加鎖 |
| DELETE | 排他鎖 | 自動加鎖 |
| SELECT | 不加鎖 | |
| SELECT … LOCK IN SHARE MOOE | 共享鎖 | 需要手動在SELECT之後加LOCK IN SHARE MODE |
| SELECT … FOR UPDATE | 排他鎖 | 需要手動在SELECT之後加FOR UPDATE |
行鎖特點：
- 默認情況下，InnoDB在 REPEATABLE READ事務隔離級別運行，InnoDB使用 next-key 鎖進行搜索和索引掃描，以防止幻讀。
- 針對唯一索引進行檢索時，對已存在的記錄進行等值匹配時，將會自動優化為行鎖。
- InnoDB的行鎖是針對於索引加的鎖，不通過索引條件檢索數據，那麼InnoDB將對表中的所有記錄加鎖，此時 就會升級為表鎖。
我們同樣給出案例解釋：
- 正常SELECT語句
# 客戶端1
-- 開啟事務
begin；
-- 正常select語句，不加鎖，對另一個客戶端無影響
select * from table1;
-- 提交事務
commit；
# 客戶端2
- select…lock in share mode，加共享鎖，共享鎖與共享鎖之間兼容。
# 客戶端1
-- 開啟事務
begin；
-- select...lock in share mode，加共享鎖，共享鎖與共享鎖之間兼容。
select * from table1 in share mode;
-- 提交事務
commit；
# 客戶端2
-- 開啟事務
begin；
-- 客戶端1採用共享鎖，這裡同樣採用共享鎖，可以同時觸發
select * from table1 in share mode;
-- 提交事務
commit；
- 共享鎖與排他鎖之間互斥。
# 客戶端1
-- 開啟事務
begin；
-- select...lock in share mode，加共享鎖，
select * from table1 in share mode;
-- 提交事務
commit；
# 客戶端2
-- 開啟事務
begin；
-- 客戶端1採用共享鎖，但這裡使用update為排他鎖，不能同時使用，該事務等待，直至客戶端1提交後才可以運行
update table1 set name = 'xxx' where id = '1';
-- 提交事務
commit；
- 排它鎖與排他鎖之間互斥
# 客戶端1
-- 開啟事務
begin；
-- 這裡設置為排他鎖
update table1 set name = 'xxx' where id = '1';
-- 提交事務
commit；
# 客戶端2
-- 開啟事務
begin；
-- 客戶端1採用排他鎖，這裡也採用排他鎖，兩者互斥，需要等待客戶端1提交事務後才可以運行
update table1 set name = 'xxx' where id = '1';
-- 提交事務
commit；
- 無索引行鎖升級為表鎖
# 當我們依據索引進行的查找 屬於 行鎖
-- 例如下述我們採用id進行查找，屬於正常行鎖
# 客戶端1
-- 開啟事務
begin；
-- 這裡設置為排他鎖
update table1 set name = 'xxx' where id = '1';
-- 提交事務
commit；
# 客戶端2
-- 開啟事務
begin；
-- 因為上述屬於行鎖，只鎖住id為1的行資訊，我們下述修改id為2的內容不受影響
update table1 set name = 'xxx' where id = '2';
-- 提交事務
commit；
# 當我們依據非索引進行的查找 屬於 表鎖
-- 例如下述我們採用name進行查找，這裡就屬於表鎖
# 客戶端1
-- 開啟事務
begin；
-- 因為這裡的查找並未採用索引查找，所以行鎖自動變成表鎖，整個表無法進行修改（假設為id為2的行資訊）
update table1 set age = 18 where name = 'xxx';
-- 提交事務
commit；
# 客戶端2
-- 開啟事務
begin；
-- 因為客戶端1啟動了表鎖，所以我們下述的修改無法成功
update table1 set name = 'xxx' where id = '1'; -- 運行失敗
-- 提交事務
commit；
間隙鎖&臨鍵鎖
默認情況下，InnoDB在 REPEATABLE READ事務隔離級別運行，InnoDB使用 next-key 鎖進行搜索和索引掃描，以防止幻讀。
一般出現上述鎖有以下三種情況：
- 索引上的等值查詢(唯一索引)，給不存在的記錄加鎖時, 優化為間隙鎖 。
- 索引上的等值查詢(非唯一普通索引)，向右遍歷時最後一個值不滿足查詢需求時，next-keylock 退化為間隙鎖。
- 索引上的範圍查詢(唯一索引)–會訪問到不滿足條件的第一個值為止。
注意：
- 間隙鎖唯一目的是防止其他事務插入間隙。間隙鎖可以共存，一個事務採用的間隙鎖不會阻止另一個事務在同一間隙上採用間隙鎖。
接下來我們通過案例進行解釋：
- 索引上的等值查詢(唯一索引)，給不存在的記錄加鎖時, 優化為間隙鎖 。
# 我們給出表結構以及資訊
-- id name age
-- 1 xxx 15
-- 3 xxx 23
-- 7 xxx 32
-- 15 xxx 16
-- 25 xxx 27
# 客戶端1
-- 開啟事務
begin；
-- 當我們進行查詢，並且查詢的資訊是不存在的時
-- 我們會在不存在的資訊的前後之間加上間隙鎖，例如下述程式碼會導致：3~7之間存在間隙鎖，不能對此進行修改
update table set name = 'xxx' where id = 5;
-- 提交事務
commit；
# 客戶端2
-- 開啟事務
begin；
-- 因為上述我們對3~7之間設置了間隙鎖，我們不能在此之間添加資訊
insert into table values (6,'xxx',28) -- 運行錯誤
-- 提交事務
commit；
- 索引上的等值查詢(非唯一普通索引)，向右遍歷時最後一個值不滿足查詢需求時，next-keylock 退化為間隙鎖。
# 介紹分析一下：
-- 我們知道InnoDB的B+樹索引，葉子節點是有序的雙向鏈表。
-- 假如，我們要根據這個二級索引查詢值為x的數據，並加上共享鎖，我們是只鎖定x這一行就可以了嗎？
-- 並不是，因為是非唯一索引，這個結構中可能有多個x的存在，所以，在加鎖時會繼續往後找，找到一個不滿足條件的值。
-- 此時會對x加臨鍵鎖，並對x到一個不為x的數之前的間隙加鎖。
-- 整體目的就是為了防止出現新的 等值資訊 導致結果發生變化
# 我們給出表結構以及資訊
-- id name age
-- 1 xxx 15
-- 3 xxx 23
-- 7 xxx 32
-- 15 xxx 16
-- 25 xxx 27
# 我們只使用一個客戶端
-- 開啟事務
begin；
-- 我們設置普通索引，並通過普通索引進行操作
create index ind_table_age on table(age);
-- 我們通過age查找id為3的行資訊並加鎖
select * from table where age = 23 lock in share mode;
-- 提交事務
commit；
-- 然後我們進行鎖查詢
-- 查看所有鎖
select object_type,object_schema,object_name,lock_type,lock_duration fromperformance_schema.metadata_locks ;
# 接下來我們可以看到的是
-- 在 3~3 之間存在間隙鎖
-- 在 3~7 之間存在間隙鎖
-- 在 3 上存在行鎖
- 索引上的範圍查詢(唯一索引)–會訪問到不滿足條件的第一個值為止。
# 稍微先提示一下：
-- 當我們進行範圍查詢時，我們所需要鎖住的資訊包括這個範圍中的所有資訊
# 我們給出表結構以及資訊
-- id name age
-- 1 xxx 15
-- 3 xxx 23
-- 7 xxx 32
-- 19 xxx 16
-- 25 xxx 27
# 我們只使用一個客戶端
-- 開啟事務
begin；
-- 我們希望查找id>=15的值
select * from table where id >=15 lock in share mode;
-- 提交事務
commit；
-- 然後我們進行鎖查詢
-- 查看所有鎖
select object_type,object_schema,object_name,lock_type,lock_duration fromperformance_schema.metadata_locks ;
# 接下來我們可以看到的是
-- 針對單個 id=19 的行鎖
-- 在 （19，25] 之間的臨鍵鎖
-- 在 （25，正無窮] 之間的臨鍵鎖
InnoDB引擎（拓展內容）
這部分內容大多數屬於底層解釋，稍微查看理解即可。
InnoDB邏輯存儲結構
首先我們來查看一張圖，該圖表示了InnoDB引擎的整體結構
我們依次介紹圖中元素：
- 表空間
表空間是InnoDB存儲引擎邏輯結構的最高層， 如果用戶啟用了參數 innodb_file_per_table(在8.0版本中默認開啟) ，則每張表都會有一個表空間（xxx.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 存儲引擎數據是按行進行存放的
注意行中有兩個隱藏欄位：
- Trx_id：每次對某條記錄進行改動時，都會把對應的事務id賦值給trx_id隱藏列。
- Roll_pointer：每次對某條引記錄進行改動時，都會把舊的版本寫入到undo日誌中，然後這個隱藏列就相當於一個指針，可以通過它來找到該記錄修改前的資訊。
架構
MySQL5.5 版本開始，默認使用InnoDB存儲引擎，它擅長事務處理，具有崩潰恢復特性，在日常開發中使用非常廣泛。
下面是InnoDB架構圖，左側為記憶體結構，右側為磁碟結構。
接下來我們分別從記憶體結構和磁碟結構分開介紹，並在最後介紹一下後台執行緒：
- 記憶體結構：
-
Buffer Pool
- 緩衝池 Buffer Pool，是主記憶體中的一個區域，裡面可以快取磁碟上經常操作的真實數據，在執行增刪改查操作時，先操作緩衝池中的數據（若緩衝池沒有數據，則從磁碟載入並快取），然後再以一定頻率刷新到磁碟，從而減少磁碟IO，加快處理速度。
- 緩衝池以Page頁為單位，底層採用鏈表數據結構管理Page。
- 根據狀態，將Page分為三種類型：
- free page：空閑page，未被使用。
- clean page：被使用page，數據沒有被修改過。
- dirty page：臟頁，被使用page，數據被修改過，也中數據與磁碟的數據產生了不一致。
-
Change Buffer
- Change Buffer，更改緩衝區（針對於非唯一二級索引頁），在執行DML語句時，如果這些數據Page沒有在Buffer Pool中，不會直接操作磁碟，而會將數據變更存在更改緩衝區 Change Buffer中，在未來數據被讀取時，再將數據合併恢復到Buffer Pool中，再將合併後的數據刷新到磁碟中。
- 與聚集索引不同，二級索引通常是非唯一的，並且以相對隨機的順序插入二級索引。同樣，刪除和更新可能會影響索引樹中不相鄰的二級索引頁，如果每一次都操作磁碟，會造成大量的磁碟IO。有了ChangeBuffer之後，我們可以在緩衝池中進行合併處理，減少磁碟IO。
-
Adaptive Hash Index
- 自適應hash索引，用於優化對Buffer Pool數據的查詢。
- InnoDB存儲引擎會監控對錶上各索引頁的查詢，如果觀察到在特定的條件下hash索引可以提升速度，則建立hash索引，稱之為自適應hash索引。
- 自適應哈希索引，無需人工干預，是系統根據情況自動完成。
- 參數： adaptive_hash_index
-
Log Buffer
- 日誌緩衝區，用來保存要寫入到磁碟中的log日誌數據（redo log 、undo log），默認大小為 16MB，日誌緩衝區的日誌會定期刷新到磁碟中。如果需要更新、插入或刪除許多行的事務，增加日誌緩衝區的大小可以節省磁碟 I/O。
- 參數：innodb_log_buffer_size：緩衝區大小
- 參數：innodb_flush_log_at_trx_commit：日誌刷新到磁碟時機，包含以下三種數值
- 1 ： 日誌在每次事務提交時寫入並刷新到磁碟，默認值。
- 0 : 每秒將日誌寫入並刷新到磁碟一次。
- 2 : 日誌在每次事務提交後寫入，並每秒刷新到磁碟一次。
- 磁碟結構：
-
System Tablespace
- 系統表空間是更改緩衝區的存儲區域。如果表是在系統表空間而不是每個表文件或通用表空間中創建的，它也可能包含表和索引數據。(在MySQL5.x版本中還包含InnoDB數據字典、undolog等)
- 參數：innodb_data_file_path
-
File-Per-Table Tablespaces
- 如果開啟了innodb_file_per_table開關 ，則每個表的文件表空間包含單個InnoDB表的數據和索引 ，並存儲在文件系統上的單個數據文件中。
- 開關參數：innodb_file_per_table ，該參數默認開啟。
-
General Tablespaces
- 通用表空間，需要通過 CREATE TABLESPACE 語法創建通用表空間，在創建表時，可以指定該表空間。
-
Undo Tablespaces
- 撤銷表空間，MySQL實例在初始化時會自動創建兩個默認的undo表空間（初始大小16M），用於存儲undo log日誌。
-
Temporary Tablespaces
- InnoDB 使用會話臨時表空間和全局臨時表空間。存儲用戶創建的臨時表等數據。
-
Doublewrite Buffer Files
- 雙寫緩衝區，innoDB引擎將數據頁從Buffer Pool刷新到磁碟前，先將數據頁寫入雙寫緩衝區文件中，便於系統異常時恢複數據。
-
Redo Log
- 重做日誌，是用來實現事務的持久性。該日誌文件由兩部分組成：重做日誌緩衝（redo logbuffer）以及重做日誌文件（redo log）,前者是在記憶體中，後者在磁碟中。
- 當事務提交之後會把所有修改資訊都會存到該日誌中, 用於在刷新臟頁到磁碟時,發生錯誤時, 進行數據恢復使用。
- 後台執行緒：
在InnoDB的後台執行緒中，分為4類，分別是：Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread。
- Master Thread
- 核心後台執行緒，負責調度其他執行緒，還負責將緩衝池中的數據非同步刷新到磁碟中, 保持數據的一致性，還包括臟頁的刷新、合併插入快取、undo頁的回收 。
- IO Thread
- 在InnoDB存儲引擎中大量使用了AIO來處理IO請求, 這樣可以極大地提高資料庫的性能，而IOThread主要負責這些IO請求的回調。
- Purge Thread
- 主要用於回收事務已經提交了的undo log，在事務提交之後，undo log可能不用了，就用它來回收。
- Page Cleaner Thread
- 協助 Master Thread 刷新臟頁到磁碟的執行緒，它可以減輕 Master Thread 的工作壓力，減少阻塞。
事務原理
在介紹事務原理前，我們先回顧一下事務的基本概念：
- 事務 是一組操作的集合，它是一個不可分割的工作單位，事務會把所有的操作作為一個整體一起向系統提交或撤銷操作請求，即這些操作要麼同時成功，要麼同時失敗。
事務的四大特點：
- 原子性（Atomicity）：事務是不可分割的最小操作單元，要麼全部成功，要麼全部失敗。
- 一致性（Consistency）：事務完成時，必須使所有的數據都保持一致狀態。
- 隔離性（Isolation）：資料庫系統提供的隔離機制，保證事務在不受外部並發操作影響的獨立環境下運行。
- 持久性（Durability）：事務一旦提交或回滾，它對資料庫中的數據的改變就是永久的。
而這四大特點均有相關的技術支援，我們在下面一一介紹：
redo log
重做日誌，記錄的是事務提交時數據頁的物理修改，是用來實現事務的持久性。
該日誌文件由兩部分組成：重做日誌緩衝（redo log buffer）以及重做日誌文件（redo logfile）,前者是在記憶體中，後者在磁碟中。
當事務提交之後會把所有修改資訊都存到該日誌文件中, 用於在刷新臟頁到磁碟,發生錯誤時, 進行數據恢復使用。
undo log
回滾日誌，用於記錄數據被修改前的資訊 , 作用包含兩個 :
- 提供回滾(保證事務的原子性)
- MVCC(多版本並發控制) 。
undo log和redo log記錄物理日誌不一樣，它是邏輯日誌：
- 可以認為當delete一條記錄時，undolog中會記錄一條對應的insert記錄，反之亦然，當update一條記錄時，它記錄一條對應相反的update記錄。
- 當執行rollback時，就可以從undo log中的邏輯記錄讀取到相應的內容並進行回滾。
undo log 具有兩種操作：
-
Undo log銷毀：undo log在事務執行時產生，事務提交時，並不會立即刪除undo log，因為這些日誌可能還用於MVCC。
-
Undo log存儲：undo log採用段的方式進行管理和記錄，存放在前面介紹的 rollback segment回滾段中，內部包含1024個undo log segment。
MVCC多版本並發控制
我們先通過對比來認識一下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。
實現原理三部曲
- 隱式欄位
當我們創建一個表之後，表中的欄位不僅僅包括我們創造的欄位，還包括三個自動生成的欄位：
| 隱藏欄位 | 含義 |
|---|---|
| DB_TRX_ID | 最近修改事務ID，記錄插入這條記錄或最後一次修改該記錄的事務ID。 |
| DB_ROLL_PTR | 回滾指針，指向這條記錄的上一個版本，用於配合undo log，指向上一個版本。 |
| DB_ROW_ID | 隱藏主鍵，如果表結構沒有指定主鍵，將會生成該隱藏欄位。 |
- undo log日誌
回滾日誌，在insert、update、delete的時候產生的便於數據回滾的日誌。
undo log日誌刪除條件：
-
當insert的時候，產生的undo log日誌只在回滾時需要，在事務提交後，可被立即刪除。
-
當update、delete的時候，產生的undo log日誌不僅在回滾時需要，在快照讀時也需要，不會立即被刪除。
- readView
ReadView（讀視圖）是 快照讀 SQL執行時MVCC提取數據的依據，記錄並維護系統當前活躍的事務（未提交的）id。
readView包含四個重要欄位：
| 欄位 | 含義 |
|---|---|
| m_ids | 當前活躍的事務ID集合 |
| min_trx_id | 最小活躍事務ID |
| max_trx_id | 預分配事務ID，當前最大事務ID+1（因為事務ID是自增的） |
| creator_trx_id | ReadView創建者的事務ID |
而在readview中就規定了版本鏈數據的訪問規則： （trx_id 代表當前undolog版本鏈對應事務ID。 ）
| 條件 | 是否可以訪問 | 說明 |
|---|---|---|
| trx_id == creator_trx_id | 可以訪問該版本 | 成立，說明數據是當前這個事務更改 |
| trx_id < min_trx_id | 可以訪問該版本 | 成立，說明數據已經提交了。 |
| trx_id > max_trx_id | 不可以訪問該版本 | 成立，說明該事務是在 ReadView生成後才開啟。 |
| min_trx_id <= trx_id <= max_trx_id | 如果trx_id不在m_ids中， 是可以訪問該版本的 | 成立，說明數據已經提交。 |
不同的隔離級別，生成ReadView的時機不同：
- READ COMMITTED ：在事務中每一次執行快照讀時生成ReadView。
- REPEATABLE READ：僅在事務中第一次執行快照讀時生成ReadView，後續復用該ReadView。
MYSQL管理（拓展內容）
MYSQL管理主要分為兩個部分：
- 自帶資料庫
- 常用工具
自帶資料庫
MYSQL系統自下載後自帶四個資料庫，具體內容如下：
| 資料庫 | 含義 |
|---|---|
| mysql | 存儲MySQL伺服器正常運行所需要的各種資訊 （時區、主從、用 戶、許可權等） |
| information_schema | 提供了訪問資料庫元數據的各種表和視圖，包含資料庫、表、欄位類 型及訪問許可權等 |
| performance_schema | 為MySQL伺服器運行時狀態提供了一個底層監控功能，主要用於收集 資料庫伺服器性能參數 |
| sys | 包含了一系列方便 DBA 和開發人員利用 performance_schema 性能資料庫進行性能調優和診斷的視圖 |
常用工具
我們將會介紹到六種常用工具：
- mysql
- mysqladmin
- mysqlbinlog
- mysqlshow
- mysqldump
- mysqlimport
mysql
該mysql不是指mysql服務，而是指mysql的客戶端工具。
語法 ：
mysql [options] [database]
選項 ：
-u, --user=name #指定用戶名
-p, --password[=name] #指定密碼
-h, --host=name #指定伺服器IP或域名
-P, --port=port #指定連接埠
-e, --execute=name #執行SQL語句並退出
mysqladmin
mysqladmin 是一個執行管理操作的客戶端程式。可以用它來檢查伺服器的配置和當前狀態、創建並刪除資料庫等。
語法:
mysqladmin [options] command ...
選項:
-u, --user=name #指定用戶名
-p, --password[=name] #指定密碼
-h, --host=name #指定伺服器IP或域名
-P, --port=port #指定連接埠
通過幫助文檔查看選項：
mysqladmin --help
mysqlbinlog
由於伺服器生成的二進位日誌文件以二進位格式保存，所以如果想要檢查這些文本的文本格式，就會使用到mysqlbinlog 日誌管理工具。
語法 ：
mysqlbinlog [options] log-files1 log-files2 ...
選項 ：
-d, --database=name 指定資料庫名稱，只列出指定的資料庫相關操作。
-o, --offset=# 忽略掉日誌中的前n行命令。
-r,--result-file=name 將輸出的文本格式日誌輸出到指定文件。
-s, --short-form 顯示簡單格式， 省略掉一些資訊。
--start-datatime=date1 --stop-datetime=date2 指定日期間隔內的所有日誌。
--start-position=pos1 --stop-position=pos2 指定位置間隔內的所有日誌。
mysqlshow
mysqlshow 客戶端對象查找工具，用來很快地查找存在哪些資料庫、資料庫中的表、表中的列或者索引。
語法 ：
mysqlshow [options] [db_name [table_name [col_name]]]
選項 ：
--count 顯示資料庫及表的統計資訊（資料庫，表 均可以不指定）
-i 顯示指定資料庫或者指定表的狀態資訊
示例：
#查詢test庫中每個表中的欄位書，及行數
mysqlshow -uroot -p2143 test --count
#查詢test庫中book表的詳細情況
mysqlshow -uroot -p2143 test book --count
mysqldump
mysqldump 客戶端工具用來備份資料庫或在不同資料庫之間進行數據遷移。備份內容包含創建表，及插入表的SQL語句。
語法 ：
mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A
連接選項 ：
-u, --user=name 指定用戶名
-p, --password[=name] 指定密碼
-h, --host=name 指定伺服器ip或域名
-P, --port=# 指定連接埠
輸出選項：
--add-drop-database 在每個資料庫創建語句前加上 drop database 語句
--add-drop-table 在每個表創建語句前加上 drop table 語句 , 默認開啟 ; 不開啟 (--skip-add-drop-table)
-n, --no-create-db 不包含資料庫的創建語句
-t, --no-create-info 不包含數據表的創建語句
-d --no-data 不包含數據
-T, --tab=name 自動生成兩個文件：一個.sql文件，創建表結構的語句；一個.txt文件，數據文件
mysqlimport/source
- mysqlimport
mysqlimport 是客戶端數據導入工具，用來導入mysqldump 加 -T 參數後導出的文本文件。
語法 ：
mysqlimport [options] db_name textfile1 [textfile2...]
示例 ：
mysqlimport -uroot -p2143 test /tmp/city.txt
- source
如果需要導入sql文件,可以使用mysql中的source 指令 :
語法 ：
source /root/xxxxx.sql
結束語
好的，關於MYSQL的進階篇我們就介紹到這裡吧，希望能為各位帶來幫助！
附錄
文章屬於學習筆記，學習於B站黑馬鄧老師的MYSQL資料庫
這裡為大家附上鏈接：01. 進階-課程介紹_嗶哩嗶哩_bilibili
因為進階篇的內容基本已經足夠就業的需求，所以針對MYSQL的運維篇可能會在以後更新
下一篇文章應該是JDBC，如果覺得還不錯希望能繼續關注~
