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，如果覺得還不錯希望能繼續關注~
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/501129.html
標籤：MySQL
下一篇：史上最全Mysql規范