共計 47302 個字符，預計需要花費 119 分鐘才能閱讀完成。
本文丸趣 TV 小編為大家詳細介紹“MySQL 的 SQL 優化、索引優化、鎖機制、主從復制知識有哪些”，內容詳細，步驟清晰，細節處理妥當，希望這篇“MySQL 的 SQL 優化、索引優化、鎖機制、主從復制知識有哪些”文章能幫助大家解決疑惑，下面跟著丸趣 TV 小編的思路慢慢深入，一起來學習新知識吧。
0 存儲引擎介紹
myisam 存儲：如果表對事務要求不高，同時是以查詢和添加為主的，我們考慮使用 myisam 存儲引擎，比如 bbs 中的發帖表，回復表
需要定時進行碎片整理（因為刪除的數據還是存在）：optimize table table_name;
InnoDB 存儲：對事務要求高，保存的數據都是重要數據，我們建議使用 INN0DB, 比如訂單表，賬號表.
面試問 MyISAM 和 INNODB 的區別：
1. 事務安全
2. 查詢和添加速度
3. 支持全文索引
4. 鎖機制
5. 外鍵 MyISAM 不支持外鍵，INNODB 支持外鍵.
Mermory 存儲：比如我們數據變化頻繁，不需要入庫，同時又頻繁的查詢和修改，我們考慮使用 memory
查看 mysql 以提供什么存儲引擎：show engines;
查看 mysql 當前默認的存儲引擎：show variables like %storage_engine%
1 SQL 性能分析
SQL 性能下降原因：
1、查詢語句寫的爛
2、索引失效（數據變更）
3、關聯查詢太多 join（設計缺陷或不得已的需求）
4、服務器調優及各個參數設置（緩沖、線程數等）
通常 SQL 調優過程：
觀察，至少跑 1 天，看看生產的慢 SQL 情況。
開啟慢查詢日志，設置闕值，比如超過 5 秒鐘的就是慢 SQL，并將它抓取出來。
explain + 慢 SQL 分析。
show profile。
運維經理 or DBA，進行 SQL 數據庫服務器的參數調優。
總結：
1、慢查詢的開啟并捕獲
2、explain + 慢 SQL 分析
3、show profile 查詢 SQL 在 Mysql 服務器里面的執行細節和生命周期情況
4、SQL 數據庫服務器的參數調優
2 常見通用的 JOIN 查詢 SQL 執行加載順序
手寫順序：
SELECT DISTINCT
select_list
left_table join_type
JOIN right_table on join_codition //join_codition：比如員工的部門 ID 和部門表的主鍵 id 相同
WHERE
where_condition
GROUP BY
group_by_list
HAVING
having_condition
ORDER BY
order_by_condition
LIMIT
limit_numberMySQL 機讀順序：
1 FROM left_table
2 ON join_condition
3 join_type JOIN right_table
4 WHERE where_condition
5 GROUP BY group_by_list
6 HAVING having_condition
7 SELECT
8 DISTINCT select_list
9 ORDER BY order_by_condition
10 LIMIT limit_number總結：
運行順序一上一下
七種 JOIN 寫法
創建表插入數據（左右主外鍵相連）：
CREATE TABLE tbl_dept(id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
locAdd VARCHAR(40) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
// 設置存儲引擎，主鍵自動增長和默認文本字符集
CREATE TABLE tbl_emp (id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY fk_dept_Id (deptId)
#CONSTRAINT fk_dept_Id foreign key (deptId) references tbl_dept (Id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,locAdd) VALUES(RD ,11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES(HR ,12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES(MK ,13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES(MIS ,14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES(FD ,15);
INSERT INTO tbl_emp(NAME,deptId) VALUES(z3 ,1);
INSERT INTO tbl_emp(NAME,deptId) VALUES(z4 ,1);
INSERT INTO tbl_emp(NAME,deptId) VALUES(z5 ,1);
INSERT INTO tbl_emp(NAME,deptId) VALUES(w5 ,2);
INSERT INTO tbl_emp(NAME,deptId) VALUES(w6 ,2);
INSERT INTO tbl_emp(NAME,deptId) VALUES(s7 ,3);
INSERT INTO tbl_emp(NAME,deptId) VALUES(s8 ,4);
INSERT INTO tbl_emp(NAME,deptId) VALUES(s9 ,51);
#查詢執行后結果
mysql select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
+----+----------+--------+
5 rows in set (0.00 sec)
mysql select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
8 rows in set (0.00 sec)1、inner join：只有 deptId 和 id 的共有部分
2、left join（全 A）：前七條共有數據；第八條 a 表獨有數據，b 表補 null
3、right join（全 B）：前七條共有數據；第八條 b 表獨有數據，a 表補 null
4、左 join 獨 A：表 A 獨有部分
5、右 join 獨 B：表 B 獨有部分
6、full join：MySQL 不支持 full join，用全 a + 全 b，union 去重中間部分
union 關鍵字可以合并去重
7、A、B 各自獨有集合
3 索引介紹 3.1 索引是什么
MySQL 官方對索引的定義為：索引（Index）是幫助 MySQL 高效獲取數據的數據結構（索引的本質是數據結構，排序 + 查詢兩種功能）。
索引的目的在于提高查詢效率，可以類比字典。
如果要查“mysql”這個單詞，我們肯定需要定位到 m 字母，然后從下往下找到 y 字母，再找到剩下的 sql。
如果沒有索引，那么你可能需要逐個逐個尋找，如果我想找到 Java 開頭的單詞呢？或者 Oracle 開頭的單詞呢？
是不是覺得如果沒有索引，這個事情根本無法完成？
索引可以理解為：排好序的快速查找數據結構
下圖就是一種可能的索引方式示例：
假如：找 4 號這本書，掃碼得到對應的編號為 91，91 比 34 大往右邊找，91 比 89 大往右邊找，然后找到（比較三次后就可以找到，然后檢索出對應的物理地址）
為了加快 Col2 的查找，可以維護一個右邊所示的二叉查找樹，每個節點分別包含索引鍵值和一個指向對應數據記錄物理地址的指針，這樣就可以運用二叉查找在一定的復雜度內獲取到相應數據，從而快速的檢索出符合條件的記錄
結論：在數據之外，數據庫系統還維護著滿足特定查找算法的數據結構，這些數據結構以某種方式引用(指向）數據，這樣就可以在這些數據結構上實現高級查找算法。這種數據結構，就是索引
一般來說索引本身也很大，不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲的磁盤上。
我們平常所說的索引，如果沒有特別指明，都是指 B 樹（多路搜索樹，并不一定是二叉的）結構組織的索引。其中聚集索引，次要索引，覆蓋索引，復合索引，前綴索引，唯一索引默認都是使用 B + 樹索引，統稱索引。當然，除了 B + 樹這種類型的索引之外，還有哈稀索引 (hash index) 等
3.2 索引優劣勢
優勢：
類似大學圖書館建書目索引，提高數據檢索的效率，降低數據庫的 IO 成本。
通過索引列對數據進行排序，降低數據排序的成本，降低了 CPU 的消耗。
劣勢：
實際上索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄，所以索引列也是要占用空間的（占空間）
雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行 INSERT、UPDATE 和 DELETE。因為更新表時，MySQL 不僅要保存數據，還要保存一下索引文件每次更新添加了索引列的字段，都會調整因為更新所帶來的鍵值變化后的索引信息。
索引只是提高效率的一個因素，如果你的 MysQL 有大數據量的表，就需要花時間研究建立最優秀的索引，或優化查詢
3.3 索引分類和建索引命令語句
主鍵索引：索引值必須是唯一的，且不能為 NULL
第一種：CREATE TABLE table_name(id int PRIMARY KEY aoto_increment,name varchar(10));
第二種：ALTER TABLE table_name ADD PRIMARY KEY (columnName);
普通索引：索引值可出現多次
第一種：CREATE INDEX index_name on table_name(columnName);
第二種：ALTER TABLE table_name ADD INDEX index_name (columnName);
全文索引：主要是針對文本的檢索，如：文章，全文索引只針對 MyISAM 引擎有效，并且只針對英文內容生效
建表時創建
# 建表
CREATE TABLE articles(
id INT UNSIGNED ATUO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT(title,body)
)engine=myisam charset utf8; #指定引擎
select * from articles where match(title,body) against( 英文內容 # 只針對英語內容生效
#1、在 mysql 中 fultext 索引只針對 myisam 生效
#2、mysq1 自己提供的 flltext 只針對英文生效 - sphinx (coreseek)技術處理中文工
#3、使用方法是 match(字段名...) against(‘關鍵字)
#4、全文索引一個叫停止詞，因為在一個文本中創建索引是一個無窮大的數，因此對一些常用詞和字符就不會創建，這些詞稱為停止詞
ALTER TABLE table_name ADD FULLTEXT index_name (columnName);
唯一索引：索引列的值必須唯一，但允許有空值 NULL，并可以有多個。
第一種：CREATE UNIQUE INDEX index_name ON table_name(columnName);
第二種：ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (columnName);
單值索引：即一個索引只包含單個列，一個表可以有多個單列索引。
第一種：CREATE INDEX index_name ON table_name(columnName);
第二種：ALTER TABLE table_name ADD INDEX index_name ON (columnName);
select * from user where name=
// 經常查 name 字段，為其建索引
create index idx_user_name on user(name);復合索引：即一個索引包含多個列
第一種：CREATE INDEX index_name ON table_name(columnName1，columnName2…);
第二種：ALTER TABLE table_name ADD INDEX index_name ON (columnName1，columnName2…);
select * from user where name= and email=
// 經常查 name 和 email 字段，為其建索引
create index idx_user_name on user(name, email);查詢索引
第一種：SHOW INDEX FROM table_name;
第二種：SHOW KEYS FROM table_name;
刪除索引
第一種：DROP INDEX index_name ON table_name;
第二種：ALTER TABLE table_name DROP INDEX index_name;
刪除主鍵索引：ALTER TBALE table_name DROP PRIMARY KEY;
3.4 索引結構與檢索原理
MySQL 索引結構：
BTree 索引
Hash 索引
full-text 全文索引
R-Tree 索引
初始化介紹
一顆 b + 樹，淺藍色的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個數據項（深藍色所示）和指針（黃色所示), 如磁盤塊 1 包含數據項 17 和 35，包含指針 P1、P2、P3,
P1 表示小于 17 的磁盤塊，P2 表示在 17 和 35 之間的磁盤塊，P3 表示大于 35 的磁盤塊。
真實的數據存在于葉子節點：3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非葉子節點只不存儲真實的數據，只存儲指引搜索方向的數據項，如 17、35 并不真實存在于數據表中。
查找過程
如果要查找數據項 29，那么首先會把磁盤塊 1 由磁盤加載到內存，此時發生一次 IO。在內存中用二分查找確定 29 在 17 和 35 之間，鎖定磁盤塊 1 的 P2 指針，內存時間因為非常短（相比磁盤的 IO）可以忽略不計，通過磁盤塊 1 的 P2 指針的磁盤地址把磁盤塊 3 由磁盤加載到內存，發生第二次 IO，29 在 26 和 30 之間，鎖定磁盤塊 3 的 P2 指針，通過指針加載磁盤塊 8 到內存，發生第三次 IO，同時內存中做二分查找找到 29，結束查詢，總計三次 IO
真實的情況是，3 層的 b + 樹可以表示上百萬的數據，如果上百萬的數據查找只需要三次 IO，性能提高將是巨大的，如果沒有索引，每個數據項都要發生一次 IO，那么總共需要百萬次的 IO，顯然成本非常非常高
3.5 哪些情況適合建索引
主鍵自動建立唯一索引
頻繁作為查詢條件的字段應該創建索引
查詢中與其它表關聯的字段，外鍵關系建立索引
單鍵 / 組合索引的選擇問題，who?(在高并發下傾向創建組合索引)
查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度
查詢中統計或者分組字段
3.6 哪些情況不適合建索引
Where 條件里用不到的字段不創建索引
表記錄太少（300w 以上建）
經常增刪改的表（提高了查詢速度，同時卻會降低更新表的速度，如對表進行 INSERT、UPDATE 和 DELETE。因為更新表時，MySQL 不僅要保存數據，還要保存一下索引文件）
數據重復且分布平均的表字段，因此應該只為最經常查詢和最經常排序的數據列建立索引。注意，如果某個數據列包含許多重復的內容，為它建立索引就沒有太大的實際效果。（比如：國籍、性別）
假如一個表有 10 萬行記錄，有一個字段 A 只有 T 和 F 兩種值，且每個值的分布概率天約為 50%，那么對這種表 A 字段建索引一般不會提高數據庫的查詢速度。
索引的選擇性是指索引列中不同值的數目與表中記錄數的比。如果一個表中有 2000 條記錄，表索引列有 1980 個不同的值，那么這個索引的選擇性就是 1980/2000=0.99。一個索引的選擇性越接近于 1，這個索引的效率就越高
4 性能分析 4.1 性能分析前提知識
MySQL Query Optimizer（查詢優化器）[?kw??ri] [??pt?ma?z?]
Mysql 中專門負責優化 SELECT 語句的優化器模塊，主要功能：通過計算分析系統中收集到的統計信息，為客戶端請求的 Query 提供他認為最優的執行計劃（他認為最優的數據檢索方式，但不見得是 DBA 認為是最優的, 這部分最耗費時間）
當客戶端向 MySQL 請求一條 Query，命令解析器模塊完成請求分類，區別出是 SELECT 并轉發給 MySQL Query Optimizer 時，MySQL Query Optimizer 首先會對整條 Query 進行優化，處理掉一些常量表達式的預算直接換算成常量值。并對 Query 中的查詢條件進行簡化和轉換，如去掉一些無用或顯而易見的條件、結構調整等。然后分析 Query 中的 Hint 信息(如果有），看顯示 Hint 信息是否可以完全確定該 Query 的執行計劃。如果沒有 Hint 或 Hint 信息還不足以完全確定執行計劃，則會讀取所涉及對象的統計信息，根據 Query 進行寫相應的計算分析，然后再得出最后的執行計劃
MySQL 常見瓶頸：
CPU：CPU 在飽和的時候一般發生在數據裝入內存或從磁盤上讀取數據時候
IO：磁盤 I / O 瓶頸發生在裝入數據遠大于內存容量的時候
服務器硬件的性能瓶頸：top，free，iostat 和 vmstat 來查看系統的性能狀態
4.2 Explain 使用簡介
使用 EXPLAIN 關鍵字可以模擬優化器執行 SQL 查詢語句，從而知道 MySQL 是如何處理你的 SQL 語句的。分析你的查詢語句或是表結構的性能瓶頸
官網地址
Explain 的作用：
表的讀取順序
數據讀取操作的操作類型
哪些索引可以使用
哪些索引被實際使用
表之間的引用
每張表有多少行被優化器查詢
使用 Explain：
explain + sql 語句
執行計劃包含的信息（重點）：| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
mysql select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
8 rows in set (0.00 sec)
mysql explain select * from tbl_emp;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tbl_emp | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
4.3 執行計劃包含的信息字段解釋（重中之重）
執行計劃包含的信息（重點）：| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
面試重點：id、type、key、rows、Extra
id（表的讀取順序）
select 查詢的序列號，包含一組數字，表示查詢中執行 select 子句或操作表的順序
三種情況：
1、id 相同，執行順序由上至下（t1、t3、t2）
2、id 不同，如果是子查詢，id 的序號會遞增，id 值越大優先級越高，越先被執行（t3、t1、t2）
3、id 相同不同，同時存在。先走數字大的，數字相同的由上至下（t3、s1、t2）
select_type（數據讀取操作的操作類型）
查詢的類型，主要是用于區別普通查詢、聯合查詢、子查詢等的復雜查詢。
SIMPLE [?s?npl]：簡單的 select 查詢, 查詢中不包含子查詢或者 UNION
PRIMARY：查詢中若包含任何復雜的子部分，最外層查詢則被標記為（最后加載的那個）
SUBQUERY [?kw??ri]：在 SELECT 或 WHERE 列表中包含了子查詢
DERIVED [d??ra?vd]：在 FROM 列表中包含的子查詢被標記為 DERIVED（衍生）MySQL 會遞歸執行這些子查詢，把結果放在臨時表里
UNION [?ju?ni?n]：若第二個 SELECT 出現在 UNION 之后，則被標記為 UNION；若 UNION 包含在 FROM 子句的子查詢中外層 SELECT 將被標記為：DERIVED
UNION RESULT [r??z?lt]：從 UNION 表獲取結果的 SELECT（兩個 select 語句用 UNION 合并）
table（顯示執行的表名）
顯示這一行的數據是關于哪張表的
type（訪問類型排列）
顯示查詢使用了何種類型
訪問類型排列：system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL
type 常用八種類型：
結果值從最好到最壞依次是（重點）：：system const eq_ref ref range index ALL
一般來說，得保證查詢至少達到 range 級別，最好能達到 ref
詳細說明
system：表只有一行記錄（等于系統表），這是 const 類型的特列，平時不會出現，這個也可以忽略不計。
const：表示通過索引一次就找到了，const 用于比較 primary key 或者 unique 索引。因為只匹配一行數據，所以很快如將主鍵置于 where 列表中，MySQL 就能將該查詢轉換為一個常量。
eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描。
ref：非唯一性索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而，它可能會找到多個符合條件的行，所以他應該屬于查找和掃描的混合體
range：只檢索給定范圍的行, 使用一個索引來選擇行。key 列顯示使用了哪個索引一般就是在你的 where 語句中出現了 between、、、in 等的查詢。這種范圍掃描索引掃描比全表掃描要好，因為它只需要開始于索引的某一點，而結束語另一點，不用掃描全部索引
index：Full Index Scan，index 與 ALL 區別為 index 類型只遍歷索引列。這通常比 ALL 快，因為索引文件通常比數據文件小（也就是說雖然 all 和 Index 都是讀全表，但 index 是從索引中讀取的，而 all 是從硬盤中讀的）
all：Full Table Scan，將遍歷全表以找到匹配的行
工作案例：經理這條 SQL 我跑了一下 Explain 分析，在系統上可能會有 ALL 全表掃描的情況，建議嘗試一下優化。我把這條 SQL 改了改，我優化后是這么寫，這個效果已經從 ALL 變成了…
possible_keys（哪些索引可以使用）
顯示可能應用在這張表中的索引，一個或多個。查詢涉及到的字段火若存在索引，則該索引將被列出，但不一定被查詢實際使用（系統認為理論上會使用某些索引）
key（哪些索引被實際使用）
實際使用的索引。如果為 NULL，則沒有使用索引（要么沒建，要么建了失效）
查詢中若使用了覆蓋索引，則該索引僅出現在 key 列表中
覆蓋索引：建的索引字段和查詢的字段一致，如下圖
key_len（消耗的字節數）
表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度。在不損失精確性的情況下，長度越短越好
key_len 顯示的值為索引字段的最大可能長度，并非實際使用長度，即 key_len 是根據表定義計算而得，不是通過表內檢索出的
ref（表之間的引用）
顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或常量被用于查找索引列上的值。
rows（每張表有多少行被優化器查詢）
根據表統計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數（越小越好）
未建索引時：
建索引后：掃描行數減少
Extra [?ekstr?]
包含不適合在其他列中顯示但十分重要的額外信息
信息種類：Using filesort、Using temporary、Using index、Using where、Using join buffer、impossible where、select tables optimized away、distinct
Using filesort（需要優化）
說明 mysql 會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。MySQL 中無法利用索引完成的排序操作稱為 文件排序 
Using temporary（需要優化）
使了用臨時表保存中間結果，MysQL 在對查詢結果排序時使用臨時表。常見于排序 order by 和分組查詢 group by
Using index（good）
表示相應的 select 操作中使用了覆蓋索引（Covering Index），避免訪問了表的數據行，效率不錯！
情況一：
情況二：
覆蓋索引 / 索引覆蓋（Covering Index）。
理解方式一：就是 select 的數據列只用從索引中就能夠取得，不必讀取數據行，MySQL 可以利用索引返回 select 列表中的字段，而不必根據索引再次讀取數據文件, 換句話說查詢列要被所建的索引覆蓋。
理解方式二：索引是高效找到行的一個方法，但是一般數據庫也能使用索引找到一個列的數據，因此它不必讀取整個行。畢竟索引葉子節點存儲了它們索引的數據；當能通過讀取索引就可以得到想要的數據，那就不需要讀取行了。一個索引包含了（或覆蓋了）滿足查詢結果的數據就叫做覆蓋索引。
注意：
如果要使用覆蓋索引，一定要注意 select 列表中只取出需要的列，不可 select*
因為如果將所有字段一起做索引會導致索引文件過大，查詢性能下降
Using where：表明使用了 where 過濾。
Using join buffer：使用了連接緩存
impossible where：where 子句的值總是 false，不能用來獲取任何元組
select tables optimized away
在沒有 GROUPBY 子句的情況下，基于索引優化 MIN/MAX 操作，或者對于 MyISAM 存儲引擎優化 COUNT(*)操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化。
distinct
優化 distinct 操作，在找到第一匹配的元組后即停止找同樣值的動作。
練習
寫出下圖的表的執行順序
第一行（執行順序 4）：id 列為 1，表示是 union 里的第一個 select，select_type 列的 primary 表示該查詢為外層查詢，table 列被標記為，表示查詢結果來自一個衍生表，其中 derived3 中 3 代表該查詢衍生自第三個 select 查詢，即 id 為 3 的 select。【select d1.name…】
第二行（執行順序 2）：id 為 3，是整個查詢中第三個 select 的一部分。因查詢包含在 from 中，所以為 derived。【select id,namefrom t1 where other_column=’’】
第三行（執行順序 3）：select 列表中的子查詢 select_type 為 subquery，為整個查詢中的第二個 select。【select id from t3】
第四行（執行順序 1）：select_type 為 union，說明第四個 select 是 union 里的第二個 select，最先執行【select name,id from t2】
第五行（執行順序 5）：代表從 union 的臨時表中讀取行的階段，table 列的 union1,4 表示用第一個和第四個 select 的結果進行 union 操作。【兩個結果 union 操作】
5 索引優化 5.1 索引單表優化案例
建表：
CREATE TABLE IF NOT EXISTS article(id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) UNSIGNED NOT NULL,
category_id INT(10) UNSIGNED NOT NULL,
views INT(10) UNSIGNED NOT NULL,
comments INT(10) UNSIGNED NOT NULL,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL
INSERT INTO article(author_id,category_id,views,comments,title,content)
VALUES
(1,1,1,1, 1 , 1),
(2,2,2,2, 2 , 2),
(1,1,3,3, 3 , 3
mysql select * from article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 1 | 1 | 3 | 3 | 3 | 3 |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)
案例
要求：查詢 category_id 為 1 且 comments 大于 1 的情況下，views 最多的 article_id
// 功能實現
mysql SELECT id, author_id FROM article WHERE category_id = 1 AND comments 1 ORDER BY views DESC LIMIT 1;
+----+-----------+
| id | author_id |
+----+-----------+
| 3 | 1 |
+----+-----------+
1 row in set (0.00 sec)
//explain 分析
mysql explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)結論：很顯然，type 是 ALL，即最壞的情況。Extra 里還出現了 Using filesort，也是最壞的情況。優化是必須的
開始優化
新建索引（給 WHERE 語句后使用的字段添加索引）
創建方式：
create index idx_article_ccv on article(category_id,comments,views);
ALTER TABLE article ADD INDEX idx_article_ccv (category_id , comments , views 
索引用處不大，刪除：DROP INDEX idx_article_ccv ON article;
結論：
type 變成了 range，這是可以忍受的。但是 extra 里使用 Using filesort 仍是無法接受的。
但是我們已經建立了索引，為啥沒用呢？
這是因為按照 BTree 索引的工作原理，先排序 category_id，如果遇到相同的 category_id 則再排序 comments, 如果遇到相同的 comments 則再排序 views。
當 comments 字段在聯合索引里處于中間位置時，因 comments 1 條件是一個范圍值(所謂 range)，MySQL 無法利用索引再對后面的 views 部分進行檢索，即 range 類型查詢字段后面的索引無效。
改進
上次創建索引相比，這次不為 comments 字段創建索引
結論：type 變為了 ref，ref 中是 const，Extra 中的 Using filesort 也消失了，結果非常理想
5.2 索引兩表優化案例
建表：
CREATE TABLE IF NOT EXISTS class(id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(id)
CREATE TABLE IF NOT EXISTS book(bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(bookid)
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
mysql select * from class;
+----+------+
| id | card |
+----+------+
| 1 | 17 |
| 2 | 2 |
| 3 | 18 |
| 4 | 4 |
| 5 | 4 |
| 6 | 8 |
| 7 | 9 |
| 8 | 1 |
| 9 | 18 |
| 10 | 6 |
| 11 | 15 |
| 12 | 15 |
| 13 | 12 |
| 14 | 15 |
| 15 | 18 |
| 16 | 2 |
| 17 | 18 |
| 18 | 5 |
| 19 | 7 |
| 20 | 1 |
| 21 | 2 |
+----+------+
21 rows in set (0.00 sec)
mysql select * from book;
+--------+------+
| bookid | card |
+--------+------+
| 1 | 8 |
| 2 | 14 |
| 3 | 3 |
| 4 | 16 |
| 5 | 8 |
| 6 | 12 |
| 7 | 17 |
| 8 | 8 |
| 9 | 10 |
| 10 | 3 |
| 11 | 4 |
| 12 | 12 |
| 13 | 9 |
| 14 | 7 |
| 15 | 6 |
| 16 | 8 |
| 17 | 3 |
| 18 | 11 |
| 19 | 5 |
| 20 | 11 |
+--------+------+
20 rows in set (0.00 sec)
開始 Explain 分析：type 都是 all，需要優化（總有一個表來添加索引驅動）
左連接為左表加索引
刪除索引：drop index y on class;
左連接為右表添加索引
刪除索引：drop index Y on book;
案例：如果別人建的索引位置不對，只需要自己查詢時調整左右表的順序即可
結論：
第二行的 type 變為了 ref，rows 也變少了，優化比較明顯。這是由左連接特性決定的。LEFT JOIN 條件用于確定如何從右表搜索行，左邊一定都有，所以右邊是我們的關鍵點，一定需要在右表建立索引（小表驅動大表）。
左連接，右表加索引
同理：右連接，左表加索引
5.3 索引三表優化案例
建表：
CREATE TABLE IF NOT EXISTS phone(phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(phoneid)
)ENGINE=INNODB;
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
mysql select * from phone;
+---------+------+
| phoneid | card |
+---------+------+
| 1 | 10 |
| 2 | 13 |
| 3 | 17 |
| 4 | 5 |
| 5 | 12 |
| 6 | 7 |
| 7 | 15 |
| 8 | 17 |
| 9 | 17 |
| 10 | 14 |
| 11 | 19 |
| 12 | 13 |
| 13 | 5 |
| 14 | 8 |
| 15 | 2 |
| 16 | 8 |
| 17 | 11 |
| 18 | 14 |
| 19 | 13 |
| 20 | 5 |
+---------+------+
20 rows in set (0.00 sec)
用上一節兩個表，刪除他們的索引：
三表查詢語句應為：SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
創建索引：
應該為第一個 LFET JOIN 的右表 book 建索引
alter table `book` add index Y(`card`);應該為第二個 LFET JOIN 的右表 phone 建索引
alter table `phone` add index z(`card`);Explain 分析：
后 2 行的 type 都是 ref 且總 rows 優化很好，效果不錯。因此索引最好設置在需要經常查詢的字段中
結論：
Join 語句的優化
盡可能減少 Join 語句中的 NestedLoop 的循環總次數：“永遠用小結果集驅動大的結果集（比如：書的類型表驅動書的名稱表）”。
優先優化 NestedLoop 的內層循環，保證 Join 語句中被驅動表上 Join 條件字段已經被索引。
當無法保證被驅動表的 Join 條件字段被索引且內存資源充足的前提下，不要太吝惜 JoinBuffer 的設置
5.4 索引失效
建表：
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT COMMENT 姓名 ,
`age` INT NOT NULL DEFAULT 0 COMMENT 年齡 ,
`pos` VARCHAR(20) NOT NULL DEFAULT COMMENT 職位 ,
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入職時間
)CHARSET utf8 COMMENT 員工記錄表
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES(z3 ,22, manager ,NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES(July ,23, dev ,NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES(2000 ,23, dev ,NOW());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
索引失效案例：
1、全值匹配我最愛
2、最佳左前綴法則（重要！）：如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過復合索引中間列。
中間列不能斷：
3、不在索引列上做任何操作（計算、函數、（自動 or 手動）類型轉換），會導致索引失效而轉向全表掃描。
4、存儲引擎不能使用索引中范圍條件右邊的列（范圍之后全失效，范圍列并不是做的查詢而是排序）。
5、盡量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致）），減少 select *。
6、mysql 在使用不等于（!= 或者）的時候無法使用索引會導致全表掃描。
7、is null, is not null 也無法使用索引。
8、like 以通配符開頭（’%abc…’），mysql 索引失效會變成全表掃描的操作（% 寫在最右邊索引不會失效，或覆蓋索引）。
問題：解決 like % 字符串 % 時索引不被使用的方法？采用覆蓋索引的方法！
建表：
CREATE TABLE `tbl_user`(`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES( 1aa1 ,21, a@163.com
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES( 2bb2 ,23, b@163.com
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES( 3cc3 ,24, c@163.com
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES( 4dd4 ,26, d@163.com
mysql select * from tbl_user;
+----+------+------+-----------+
| id | name | age | email |
+----+------+------+-----------+
| 1 | 1aa1 | 21 | a@163.com |
| 2 | 2bb2 | 23 | b@163.com |
| 3 | 3cc3 | 24 | c@163.com |
| 4 | 4dd4 | 26 | d@163.com |
+----+------+------+-----------+
4 rows in set (0.00 sec)
創建索引：
CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);索引成功使用：
索引失效：
總結：% 寫在最右邊，如果非要寫在最左邊，就使用覆蓋索引
9、字符串不加單引號索引失效。
Explain 分析：
10、少用 or，用它來連接時會索引失效
5.5 索引面試題分析
建表：
create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
insert into test03(c1,c2,c3,c4,c5) values ( a1 , a2 , a3 , a4 , a5
insert into test03(c1,c2,c3,c4,c5) values ( b1 , b2 , b3 , b4 , b5
insert into test03(c1,c2,c3,c4,c5) values ( c1 , c2 , c3 , c4 , c5
insert into test03(c1,c2,c3,c4,c5) values ( d1 , d2 , d3 , d4 , d5
insert into test03(c1,c2,c3,c4,c5) values ( e1 , e2 , e3 , e4 , e5
// 查看表結構
mysql select * from test03;
+----+------+------+------+------+------+
| id | c1 | c2 | c3 | c4 | c5 |
+----+------+------+------+------+------+
| 1 | a1 | a2 | a3 | a4 | a5 |
| 2 | b1 | b2 | b3 | b4 | b5 |
| 3 | c1 | c2 | c3 | c4 | c5 |
| 4 | d1 | d2 | d3 | d4 | d5 |
| 5 | e1 | e2 | e3 | e4 | e5 |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)
建索引：
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
// 查看索引
mysql show index from test03;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test03 | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 3 | c3 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | | |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)1）逐一增加列
2）交換條件順序不影響索引，但最好按照建索引順序來寫 SQL
3) 限定范圍
4）order by
5）group by
定值、范圍還是排序，一般 order by 是給個范圍
group by 基本上都需要進行排序，會有臨時表產生
建議：
對于單值索引，盡量選擇針對當前 query 過濾性更好的索引。
在選擇組合索引的時候，當前 Query 中過濾性最好的字段在索引字段順序中，位置越靠左越好。
在選擇組合索引的時候，盡量選擇可以能夠包含當前 query 中的 where 字句中更多字段的索引。
盡可能通過分析統計信息和調整 query 的寫法來達到選擇合適索引的目的。
5.6 總結
優化總結口訣
全值匹配我最愛，最左前綴要遵守；
帶頭大哥不能死，中間兄弟不能斷；
索引列上少計算，范圍之后全失效；
LIKE 百分寫最右，覆蓋索引不寫 *；
不等空值還有 OR，索引影響要注意；
VAR 引號不可丟，SQL 優化有訣竅。
6 查詢截取分析 6.1 小表驅動大表
EXISTS [?ɡ?z?sts]語法：SELECT …FROM table WHERE EXISTS (subquery)
該語法可以理解為：將主查詢的數據，放到子查詢中做條件驗證，根據驗證結果（TRUE 或 FALSE）來決定主查詢的數據結果是否得以保留
提示：
EXSTS(subquey) 只返回 TRUE 或 FALSE，因此子查詢中的 SELECT * 也可以是 SELECT 1 或 select‘X’，官方說法是實際執行時會忽略 SELECT 清單，因此沒有區別。
EXISTS 子查詢的實際執行過程可能經過了優化而不是我們理解上的逐條對比，如果擔憂效率問題，可進行實際檢驗以確定是否有效率問題。
EXISTS 子查詢往往也可以用條件表達式，其他子查詢或者 JOIN 來替代，何種最優需要具體問題具體分析
in 和 exists 用法：
6.2 Order by 關鍵字排序優化
1、ORDER BY 之后子句，盡量使用 Index 方式排序，避免使用 FileSort 方式排序
建表：
create table tblA(
#id int primary key not null auto_increment,
age int,
birth timestamp not null
insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());
create index idx_A_ageBirth on tblA(age, birth);
mysql select * from tblA;
+------+---------------------+
| age | birth |
+------+---------------------+
| 22 | 2021-04-04 19:31:45 |
| 23 | 2021-04-04 19:31:45 |
| 24 | 2021-04-04 19:31:45 |
+------+---------------------+
3 rows in set (0.00 sec)
mysql show index from tblA;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbla | 1 | idx_A_ageBirth | 1 | age | A | 3 | NULL | NULL | YES | BTREE | | |
| tbla | 1 | idx_A_ageBirth | 2 | birth | A | 3 | NULL | NULL | | BTREE | | |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
關注點：是 order by 之后會不會產生 Using filesort
MySQL 支持二種方式的排序，FileSort 和 lIndex，Index 效率高，它指 MySQL 掃描索引本身完成排序。FileSort 方式效率較低。
ORDER BY 滿足兩情況，會使用 Index 方式排序：
ORDER BY 語句使用索引最左前列。
使用 where 子句與 Order BY 子句條件列組合滿足索引最左前列。
2、盡可能在索引上完成排序操作，遵照建索引的最佳左前綴
3、如果不在索引列上，mysql 的 filesort 有兩種算法（自動啟動）
雙路排序
MySQL4.1 之前是使用雙路排序，字面意思就是兩次掃描磁盤，最終得到數據，讀取行指針和 OrderBy 列，對他們進行排序，然后掃描已經排序好的列表，按照列表中的值重新從列表中讀對應的數據輸出。
從磁盤取排序字段，在 buffer 進行排序，再從磁盤取其他字段。
取一批數據，要對磁盤進行了兩次掃描，眾所周知，I\O 是很耗時的，所以在 mysql4.1 之后，出現了第二種改進的算法，就是單路排序
單路排序
從磁盤讀取查詢需要的所有列，按照 order by 列在 buffer 對它們進行排序，然后掃描排序壓的列表進行輸出，它的效率更快一些，避免了第二次讀取數據。并且把隨機 IO 變成了順序 IO, 但是它會使用更多的空間，因為它把每一行都保存在內存中了
結論及引申出的問題
由于單路是后出的，總體而言好過雙路
但是用單路有問題，在 sort_buffer 中，方法 B 比方法 A 要多占用很多空間，因為方法 B 是把所有字段都取出, 所以有可能取出的數據的總大小超出了 sort_buffer 的容量，導致每次只能取 sort_buffer 容量大小的數據，進行排序（創建 tmp 文件，多路合并)，排完再取取
sort_buffer 容量大小，再排……從而多次 I /O。
本來想省一次 I / O 操作，反而導致了大量的 I / O 操作，反而得不償失
4、優化策略
增大 sort_buffer_size 參數的設置
增大 max_length_for_sort_data 參數的設置
Why？
5、小總結：
6.3 Group by 優化
group by 實質是先排序后進行分組，遵照索引建的最佳左前綴。
當無法使用索引列，增大 max_length_for_sort_data 參數的設置 + 增大 sort_buffer_size 參數的設置。
where 高于 having，能寫在 where 限定的條件就不要去 having 限定了
6.4 慢查詢日志（重點）
介紹：
MySQL 的慢查詢日志是 MySQL 提供的一種日志記錄，它用來記錄在 MySQL 中響應時間超過閥值的語句，具體指運行時間超過 long_query_time 值的 SQL，則會被記錄到慢查詢日志中。
具體指運行時間超過 long_query_time 值的 SQL，則會被記錄到慢查詢日志中。long_query_time 的默認值為 10，意思是運行 10 秒以上的語句。
由他來查看哪些 SQL 超出了我們的最大忍耐時間值，比如一條 sql 執行超過 5 秒鐘，我們就算慢 SQL，希望能收集超過 5 秒的 sql，結合之前 explain 進行全面分析
操作說明：
默認情況下，MySQL 數據庫沒有開啟慢查詢日速，需要我們手動來設置這個參數。
當然，如果不是調優需要的話，一般不建議啟動該參數，因為開啟慢查詢日志會或多或少帶來一定的性能影響。慢查詢日志支持將日志記錄寫入文件。
查看是否開啟及如何開啟：
默認：SHOW VARIABLES LIKE %slow_query_log% [?ve?ri?bls]
開啟：set global slow_query_log=1;，只對當前數據庫生效，如果 MySQL 重啟后則會失效
如果要永久生效，就必須修改配置文件 my.cnf(其它系統變量也是如此)
修改 my.cnf 文件，[mysqld] 下增加或修改參數 slow_query_log 和 slow_query_log_file 后，然后重啟 MySQL 服務器。也即將如下兩行配置進 my.cnf 文件
slow_query_log =1slow_query_log_file=/var/lib/mysqatguigu-slow.log關于慢查詢的參數 slow_query_log_file，它指定慢查詢日志文件的存放路徑，系統默認會給一個缺省的文件 host_name-slow.log（如果沒有指定參數 slow_query_log_file 的話）
開啟了慢查詢日志后，什么樣的 SQL 才會記錄到慢查詢日志里面呢？
這個是由參數 long_query_time 控制，默認情況下 long_query_time 的值為 10 秒，命令：SHOW VARIABLES LIKE long_query_time% 
可以使用命令修改，也可以在 my.cnf 參數里面修改。
假如運行時間正好等于 long_query_time 的情況，并不會被記錄下來。也就是說，在 mysql 源碼里是判斷大于 long_query_time，而非大于等于。
命名修改慢 SQL 閾值時間：set global long_query_time=3; [?ɡl??bl]
看不到修改情況的話，重開連接，或者換一個語句：show global variables like long_query_time 
記錄慢 SQL 并后續分析:
假設我們成功設置慢 SQL 閾值時間為 3 秒（set global long_query_time=3;）。
模擬超時 SQL：select sleep(4);
查詢當前系統中有多少條慢查詢記錄：show global status like %Slow_queries% [?ste?t?s]
在配置文件中設置慢 SQL 閾值時間（永久生效）：
#[mysqld]下配置:slow_query_log=1;slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3;log_output=FILE;
日志分析工具 mysqldumpslow
在生產環境中，如果要手工分析日志，查找、分析 SQL，顯然是個體力活，MySQL 提供了日志分析工具 mysqldumpslow。
查看 mysqldumpslow 的幫助信息，mysqldumpslow –help。
常用 mysqldumpslow 幫助信息：
s：是表示按照何種方式排序
c：訪問次數
l：鎖定時間
r：返回記錄
t：查詢時間
al：平均鎖定時間
ar：平均返回記錄數
at：平均查詢時間
t：即為返回前面多少條的數據
g：后邊搭配一個正則匹配模式，大小寫不敏感的
工作常用參考：
得到返回記錄集最多的 10 個 SQL：mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
得到訪問次數最多的 10 個 SQL：mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
得到按照時間排序的前 10 條里面含有左連接的查詢語句：mysqldumpslow -s t -t 10 -g left join /var/lib/mysql/atguigu-slow.log
另外建議在使用這些命令時結合│和 more 使用，否則有可能出現爆屏情況：`mysqldumpslow -s r-t 10 /ar/lib/mysql/atguigu-slow.log | more
6.5 批量插入數據腳本
1、建表：
create database bigData;use bigData;// 部門表 CREATE TABLE dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20)NOT NULL DEFAULT ,
loc VARCHAR(13) NOT NULL DEFAULT )ENGINE=INNODB DEFAULT CHARSET=utf8;// 員工表 CREATE TABLE emp(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0, // 編號
ename varchar(20) not null default , // 名字
job varchar(9) not null default , // 工作
mgr mediumint unsigned not null default 0, // 上級編號
hiredate date not null, // 入職時間
sal decimal(7,2) not null, // 薪水
comm decimal(7,2) not null, // 紅利
deptno mediumint unsigned not null default 0 // 部門編號)ENGINE=INNODB DEFAULT CHARSET=utf8;
2、設置參數 log_bin_trust_function_creators
創建函數，假如報錯：This function has none of DETERMINISTIC…
由于開啟過慢查詢日志，因為我們開啟了 bin-log，我們就必須為我們的 function 指定一個參數
show variables like log_bin_trust_function_creators set global log_bin_trust_function_creators=1;這樣添加了參數以后，如果 mysqld 重啟，上述參數又會消失，永久方法：
windows 下：my.ini[mysqld] 加上 log_bin_trust_function_creators=1
linux 下：/etc/my.cnf 下 my.cnf[mysqld] 加上 log_bin_trust_function_creators=1
3、創建函數，保證每條數據都不同
隨機產生字符串
delimiter $$ # 為了存儲過程能正常運行，修改命令結束符，兩個 $$ 表示結束 create function rand_string(n int) returns varchar(255)begin
declare chars_str varchar(100) default abcdefghijklmnopqrstuvwxyz
declare return_str varchar(255) default
declare i int default 0;
while i n do
set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i=i+1;
end while;
return return_str;end $$隨機產生部門編號
delimiter $$create function rand_num() returns int(5)begin
declare i int default 0;
set i=floor(100+rand()*10);
return i;end $$4、創建存儲過程
創建往 emp 表中插入數據的存儲過程
delimiter $$create procedure insert_emp(in start int(10),in max_num int(10)) #max_num：表示插入多少條數據 begin
declare i int default 0;
set autocommit = 0; # 關閉自動提交，避免寫一個 insert 提交一次，50w 條一次性提交
repeat
set i = i+1;
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6), salesman ,0001,curdate(),2000,400,rand_num());
until i=max_num end repeat;
commit;end $$創建往 dept 表中插入數據的存儲過程
delimiter $$create procedure insert_dept(in start int(10),in max_num int(10))begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i+1;
insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
until i=max_num end repeat;
commit;end $$5、調用存儲過程
往 dept 表中插入數據
mysql DELIMITER ; # 修改默認結束符號為（;），之前改成了 ##
mysql CALL insert_dept(100, 10);
Query OK, 0 rows affected (0.01 sec)往 emp 表中插入 50 萬數據
mysql DELIMITER ;
mysql CALL insert_emp(100001, 500000);
Query OK, 0 rows affected (27.00 sec)查看運行結果
mysql select * from dept;
+----+--------+---------+--------+
| id | deptno | dname | loc |
+----+--------+---------+--------+
| 1 | 101 | mqgfy | ck |
| 2 | 102 | wgighsr | kbq |
| 3 | 103 | gjgdyj | brb |
| 4 | 104 | gzfug | p |
| 5 | 105 | keitu | cib |
| 6 | 106 | nndvuv | csue |
| 7 | 107 | cdudl | tw |
| 8 | 108 | aafyea | aqq |
| 9 | 109 | zuqezjx | dpqoyo |
| 10 | 110 | pam | cses |
+----+--------+---------+--------+
10 rows in set (0.00 sec)
mysql select * from emp limit 10; # 查看前 10 條數據（50W 太多了）+----+--------+-------+----------+-----+------------+---------+--------+--------+
| id | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| 1 | 100002 | xmbva | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 2 | 100003 | aeq | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 109 |
| 3 | 100004 | cnjfz | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 105 |
| 4 | 100005 | wwhd | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 100 |
| 5 | 100006 | e | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 |
| 6 | 100007 | yjfr | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 |
| 7 | 100008 | xlp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 |
| 8 | 100009 | mp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 |
| 9 | 100010 | tcdl | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 |
| 10 | 100011 | akw | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 106 |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
10 rows in set (0.00 sec)6.6 Show Profile 進行 sql 分析（重中之重）
Show Profile 是 mysql 提供可以用來分析當前會話中語句執行的資源消耗情況。可以用于 SQL 的調優的測量
官網文檔
默認情況下，參數處于關閉狀態，并保存最近 15 次的運行結果
分析步驟：
1、是否支持，看看當前的 mysql 版本是否支持：show variables like profiling
默認是關閉，使用前需要開啟
2、開啟功能，默認是關閉，使用前需要開啟：set profiling=on;
3、運行 SQL（隨便運行用來測試）
mysql select * from emp group by id%10 limit 150000;
mysql select * from emp group by id%20 order by 5;
4、查看結果：show profiles;
mysql show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------+
| 1 | 0.00204000 | show variables like profiling |
| 2 | 0.55134250 | select * from emp group by id%10 limit 150000 |
| 3 | 0.56902000 | select * from emp group by id%20 order by 5 |
+----------+------------+-----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)5、診斷 SQL，show profile cpu,block io for query ID 號;（ID 號為第 4 步 Query_ID 列中數字）
mysql show profile cpu,block io for query 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000049 | 0.000000 | 0.000000 | NULL | NULL |
| checking permissions | 0.000005 | 0.000000 | 0.000000 | NULL | NULL |
| Opening tables | 0.000012 | 0.000000 | 0.000000 | NULL | NULL |
| init | 0.000021 | 0.000000 | 0.000000 | NULL | NULL |
| System lock | 0.000009 | 0.000000 | 0.000000 | NULL | NULL |
| optimizing | 0.000003 | 0.000000 | 0.000000 | NULL | NULL |
| statistics | 0.000017 | 0.000000 | 0.000000 | NULL | NULL |
| preparing | 0.000008 | 0.000000 | 0.000000 | NULL | NULL |
| Creating tmp table | 0.000045 | 0.000000 | 0.000000 | NULL | NULL |
| Sorting result | 0.000004 | 0.000000 | 0.000000 | NULL | NULL |
| executing | 0.000002 | 0.000000 | 0.000000 | NULL | NULL |
| Sending data | 0.568704 | 0.546875 | 0.046875 | NULL | NULL |
| Creating sort index | 0.000048 | 0.000000 | 0.000000 | NULL | NULL |
| end | 0.000003 | 0.000000 | 0.000000 | NULL | NULL |
| query end | 0.000005 | 0.000000 | 0.000000 | NULL | NULL |
| removing tmp table | 0.000006 | 0.000000 | 0.000000 | NULL | NULL |
| query end | 0.000003 | 0.000000 | 0.000000 | NULL | NULL |
| closing tables | 0.000004 | 0.000000 | 0.000000 | NULL | NULL |
| freeing items | 0.000061 | 0.000000 | 0.000000 | NULL | NULL |
| cleaning up | 0.000015 | 0.000000 | 0.000000 | NULL | NULL |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)參數備注（寫在代碼中）：show profile cpu,block io for query 3;（如此代碼中的 cpu,block）
ALL：顯示所有的開銷信息。
BLOCK IO：顯示塊 lO 相關開銷。
CONTEXT SWITCHES：上下文切換相關開銷。
CPU：顯示 CPU 相關開銷信息。
IPC：顯示發送和接收相關開銷信息。
MEMORY：顯示內存相關開銷信息。
PAGE FAULTS：顯示頁面錯誤相關開銷信息。
SOURCE：顯示和 Source_function，Source_file，Source_line 相關的開銷信息。
SWAPS：顯示交換次數相關開銷的信息。
6、日常開發需要注意的結論（Status 列中的出現此四個問題嚴重）
converting HEAP to MyISAM：查詢結果太大，內存都不夠用了往磁盤上搬了。
Creating tmp table：創建臨時表，拷貝數據到臨時表，用完再刪除
Copying to tmp table on disk：把內存中臨時表復制到磁盤，危險!
locked：鎖了
6.7 全局查詢日志
永遠不要在生產環境開啟這個功能，只能在測試環境使用！
第一種：配置文件啟用。在 mysq l 的 my.cnf 中，設置如下：
#開啟 general_log=1#記錄日志文件的路徑 general_log_file=/path/logfile# 輸出格式 log_output=FILE第二種：編碼啟用。命令如下：
set global general_log=1;
set global log_output= TABLE 
此后，你所編寫的 sql 語句，將會記錄到 mysql 庫里的 geneial_log 表，可以用下面的命令查看：
mysql select * from mysql.general_log;
+----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
| 2021-04-05 19:57:28.182473 | root[root] @ localhost [::1] | 5 | 1 | Query | select * from mysql.general_log |
+----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+
1 row in set (0.00 sec)
7 MySQL 鎖機制 7.1 概述
定義：
鎖是計算機協調多個進程或線程并發訪問某一資源的機制。
在數據庫中，除傳統的計算資源（如 CPU、RAM、I/ O 等）的爭用以外，數據也是一種供許多用戶共享的資源。如何保證數據并發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖沖突也是影響數據庫并發訪問性能的一個重要因素。從這個角度來說，鎖對數據庫而言顯得尤其重要，也更加復雜
例子：京東購物
打個比方，我們到京東上買一件商品，商品只有一件庫存，這個時候如果還有另一個人買，那么如何解決是你買到還是另一個人買到的問題？
這里肯定要用到事務，我們先從庫存表中取出物品數量，然后插入訂單，付款后插入付款表信息，然后更新商品數量。在這個過程中，使用鎖可以對有限的資源進行保護，解決隔離和并發的矛盾
鎖的分類：
從對數據操作的類型（讀 \ 寫）分
讀鎖（共享鎖）：針對同一份數據，多個讀操作可以同時進行而不會互相影響。
寫鎖（排它鎖）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。
從對數據操作的粒度分
表鎖
行鎖
7.2 表鎖（偏讀）
特點：偏向 MyISAM 存儲引擎，開銷小，加鎖快；無死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低。
讀鎖案例講解 1
案例分析
建表表
create table mylock (
id int not null primary key auto_increment,
name varchar(20) default
) engine myisam;
insert into mylock(name) values( a
insert into mylock(name) values( b
insert into mylock(name) values( c
insert into mylock(name) values( d
insert into mylock(name) values( e
mysql select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)手動增加表鎖：lock table 表名字 read(write), 表名字 2 read(write), 其他;
mysql lock table mylock read;Query OK, 0 rows affected (0.00 sec)查看表上加過的鎖：show open tables;
mysql show open tables;
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
| performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 |
| performance_schema | replication_connection_status | 0 | 0 |
| mysql | time_zone_leap_second | 0 | 0 |
| mysql | columns_priv | 0 | 0 |
| my | test03 | 0 | 0 |
| bigdata | mylock | 1 | 0 |
# In_use 為 1 時表示已上鎖釋放鎖：unlock tables;
mysql unlock tables;
Query OK, 0 rows affected (0.00 sec)
# 再次查看
mysql show open tables;
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
| performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 |
| performance_schema | replication_connection_status | 0 | 0 |
| mysql | time_zone_leap_second | 0 | 0 |
| mysql | columns_priv | 0 | 0 |
| my | test03 | 0 | 0 |
| bigdata | mylock | 0 | 0 |加讀鎖——為 mylock 表加 read 鎖（讀阻塞寫例子）
讀鎖案例講解 2
為 mylock 表加 write 鎖（MylSAM 存儲引擎的寫阻塞讀例子）
MyISAM 在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行增刪改操作前，會自動給涉及的表加寫鎖。
MySQL 的表級鎖有兩種模式：
表共享讀鎖(Table Read Lock)
表獨占寫鎖(Table Write Lock)
結合上表，所以對 MyISAM 表進行操作，會有以下情況：
對 MyISAM 表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放后，才會執行其它進程的寫操作。
對 MyISAM 表的寫操作〈加寫鎖），會阻塞其他進程對同一表的讀和寫操作，只有當寫鎖釋放后，才會執行其它進程的讀寫操作。
重點！：簡而言之，就是讀鎖會阻塞寫，但是不會堵塞讀。而寫鎖則會把讀和寫都堵塞
表鎖總結
看看哪些表被加鎖了：show open tables;
如何分析表鎖定
可以通過檢查 table_locks_waited 和 table_locks_immediate 狀態變量來分析系統上的表鎖定
mysql show status like table_locks%
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 170 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)
這里有兩個狀態變量記錄 MySQL 內部表級鎖定的情況，兩個變量說明如下：
Table_locks_immediate：產生表級鎖定的次數，表示可以立即獲取鎖的查詢次數，每立即獲取鎖值加 1 ;
Table_locks_waited（重點）：出現表級鎖定爭用而發生等待的次數(不能立即獲取鎖的次數，每等待一次鎖值加 1)，此值高則說明存在著較嚴重的表級鎖爭用情況；
此外，MyISAM 的讀寫鎖調度是寫優先，這也是 MyISAM 不適合做寫為主表的引擎。因為寫鎖后，其他線程不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成永遠阻塞
7.3 行鎖（偏寫）
偏向 InnoDB 存儲引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。
InnoDB 與 MyISAM 的最大不同有兩點：一是支持事務(TRANSACTION)；二是采用了行級鎖
由于行鎖支持事務，復習老知識：
事務(Transaction）及其 ACID 屬性
并發事務處理帶來的問題
事務隔離級別
1）事務是由一組 SQL 語句組成的邏輯處理單元，事務具有以下 4 個屬性，通常簡稱為事務的 ACID 屬性：
原子性(Atomicity）：事務是一個原子操作單元，其對數據的修改，要么全都執行，要么全都不執行。
一致性（Consistent）：在事務開始和完成時，數據都必須保持一致狀態。這意味著所有相關的數據規則都必須應用于事務的修改，以保持數據的完整性; 事務結束時，所有的內部數據結構〈如 B 樹索引或雙向鏈表）也都必須是正確的。
隔離性（lsolation）：數據庫系統提供一定的隔離機制，保證事務在不受外部并發操作影響的“獨立”環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。
持久性（Durable）：事務完成之后，它對于數據的修改是永久性的，即使出現系統故障也能夠保持。
2）并發事務處理帶來的問題
更新丟失(Lost Update)
當兩個或多個事務選擇同一行，然后基于最初選定的值更新該行時，由于每個事務都不知道其他事務的存在，就會發生丟失更新問題――最后的更新覆蓋了由其他事務所做的更新。
例如，兩個程序員修改同一 java 文件。每程序員獨立地更改其副本，然后保存更改后的副本，這樣就覆蓋了原始文檔。最后保存其更改副本的編輯人員覆蓋前一個程序員所做的更改。
如果在一個程序員完成并提交事務之前，另一個程序員不能訪問同一文件，則可避免此問題。
臟讀(Dirty Reads)
一個事務正在對一條記錄做修改，在這個事務完成并提交前，這條記錄的數據就處于不一致狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些“臟”數據，并據此做進一步的處理，就會產生未提交的數據依賴關系。這種現象被形象地叫做”臟讀”。
一句話：事務 A 讀取到了事務 B 已修改但尚未提交的的數據，還在這個數據基礎上做了操作。此時，如果 B 事務回滾，A 讀取的數據無效，不符合一致性要求
不可重復讀(Non-Repeatable Reads)
一個事務在讀取某些數據后的某個時間，再次讀取以前讀過的數據，卻發現其讀出的數據已經發生了改變、或某些記錄已經被刪除了，這種現象就叫做“不可重復讀”。
一句話：事務 A 讀取到了事務 B 已經提交的修改數據，不符合隔離性。
幻讀(Phantom Reads)
一個事務接相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為“幻讀“。
一句話：事務 A 讀取到了事務 B 體提交的新增數據，不符合隔離性
多說一句：幻讀和臟讀有點類似。臟讀是事務 B 里面修改了數據；幻讀是事務 B 里面新增了數據。
3）事務隔離級別
”臟讀”、“不可重復讀”和“幻讀”，其實都是數據庫讀一致性問題，必須由數據庫提供一定的事務隔離機制來解決
數據庫的事務隔離越嚴格，并發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上“串行化”進行，這顯然與“并發”是矛盾的。同時，不同的應用對讀一致性和事務隔離程度的要求也是不同的，比如許多應用對“不可重復讀”和“幻讀”并不敏感，可能更關心數據并發訪問的能力。
常看當前數據庫的事務隔離級別：show variables like tx_isolation
mysql show variables like tx_isolation
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set, 1 warning (0.00 sec)
# 默認情況下：MySQL 避免了臟讀和不可重復讀
行鎖案例講解
建表：
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;
INSERT INTO test_innodb_lock VALUES(1, b2
INSERT INTO test_innodb_lock VALUES(3, 3
INSERT INTO test_innodb_lock VALUES(4, 4000
INSERT INTO test_innodb_lock VALUES(5, 5000
INSERT INTO test_innodb_lock VALUES(6, 6000
INSERT INTO test_innodb_lock VALUES(7, 7000
INSERT INTO test_innodb_lock VALUES(8, 8000
INSERT INTO test_innodb_lock VALUES(9, 9000
INSERT INTO test_innodb_lock VALUES(1, b1
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
mysql select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
mysql show index from test_innodb_lock;
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_innodb_lock | 1 | test_innodb_a_ind | 1 | a | A | 8 | NULL | NULL | YES | BTREE | | |
| test_innodb_lock | 1 | test_innodb_lock_b_ind | 1 | b | A | 9 | NULL | NULL | YES | BTREE | | |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+行鎖定基本演示（兩個客戶端更新同一行記錄）
疑惑解答為什么兩個都要 commint
索引失效行鎖變表鎖
無索引行鎖升級為表鎖
間隙鎖
什么是間隙鎖
當我們用范圍條件而不是相等條件檢索數據，并請求共享或排他鎖時，InnoDB 會給符合條件的已有數據記錄的索引項加鎖，對于鍵值在條件范圍內但并不存在的記錄，叫做“間隙（GAP）”。
InnoDB 也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key 鎖）。
危害
因為 Query 執行過程中通過過范圍查找的話，他會鎖定整個范圍內所有的索引鍵值，即使這個鍵值并不存在。
間隙鎖有一個比較致命的弱點，就是當鎖定一個范圍鍵值之后，即使某些不存在的鍵值也會被無辜的鎖定，而造成在鎖定的時候無法插入鎖定鍵值范圍內的任何數據。在某些場景下這可能會對性能造成很大的危害
面試題：如何鎖定一行
begin（中間寫自己的操作）commit
行鎖總結
總結：
Innodb 存儲引擎由于實現了行級鎖定，雖然在鎖定機制的實現方面所帶來的性能損耗可能比表級鎖定會要更高一些，但是在整體并發處理能力方面要遠遠優于 MyISAM 的表級鎖定的。當系統并發量較高的時候，Innodb 的整體性能和 MylISAM 相比就會有比較明顯的優勢了。
但是，Innodb 的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓 Innodb 的整體性能表現不僅不能比 MyISAM 高，甚至可能會更差
如何分析行鎖定?
通過檢查 lnnoDB_row_lock 狀態變量來分析系統上的行鎖的爭奪情況：show status like innodb_row_lock%
mysql show status like innodb_row_lock%
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
對各個狀態量的說明如下:
Innodb_row_lock_current_waits：當前正在等待鎖定的數量；
Innodb_row_lock_time：從系統啟動到現在鎖定總時間長度；
Innodb_row_lock_time_avg：每次等待所花平均時間；
Innodb_row_lock_time_max：從系統啟動到現在等待最常的一次所花的時間；
Innodb_row_lock_waits：系統啟動后到現在總共等待的次數；
對于這 5 個狀態變量，比較重要的主要是：
lnnodb_row_lock_time（等待總時長）
Innodb_row_lock_time_avg（等待平均時長）
lnnodb_row_lock_waits（等待總次數）
尤其是當等待次數很高，而且每次等待時長也不小的時候，我們就需要分析（Show Profile）系統中為什么會有如此多的等待，然后根據分析結果著手指定優化計劃。
優化建議
盡可能讓所有數據檢索都通過索引來完成，避免無索引行鎖升級為表鎖。
合理設計索引，盡量縮小鎖的范圍
盡可能較少檢索條件，避免間隙鎖
盡量控制事務大小，減少鎖定資源量和時間長度
盡可能低級別事務隔離
頁鎖
開銷和加鎖時間界于表鎖和行鎖之間; 會出現死鎖; 鎖定粒度界于表鎖和行鎖之間，并發度一般。（了解一下即可）
8 主從復制 8.1 復制的基本原理
slave 會從 master 讀取 binlog 來進行數據同步
原理圖：
MySQL 復制過程分成三步：
1、master 將改變記錄到二進制日志(binary log)。這些記錄過程叫做二進制日志事件，binary log events;
2、slave 將 master 的 binary log events 拷貝到它的中繼日志(relay log) ;
3、slave 重做中繼日志中的事件，將改變應用到自己的數據庫中。MySQL 復制是異步的且串行化的
8.2 復制的基本原則
每個 slave 只有一個 master
每個 slave 只能有一個唯一的服務器 ID
每個 master 可以有多個 salve
復制的最大問題是延遲。
8.3 一主一從常見配置
一、mysql 版本一致且后臺以服務運行
二、主從都配置在 [mysqld] 結點下，都是小寫
主機修改 my.ini 配置文件：
1、[必須]主服務器唯一 ID：server-id=1
2、[必須]啟用二進制日志
log-bin= 自己本地的路徑 /mysqlbin
log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
3、[可選]啟用錯誤日志
log-err= 自己本地的路徑 /mysqlerr
log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr
4、[可選]根目錄
basedir=“自己本地路徑”
basedir=“D:/devSoft/MySQLServer5.5/”
5、[可選]臨時目錄
tmpdir=“自己本地路徑”
tmpdir=“D:/devSoft/MySQLServer5.5/”
6、[可選]數據目錄
datadir=“自己本地路徑 /Data/”
datadir=“D:/devSoft/MySQLServer5.5/Data/”
7、主機，讀寫都可以
read-only=O
8、[可選]設置不要復制的數據庫
binlog-ignore-db=mysql
9、[可選]設置需要復制的數據庫
binlog-do-db= 需要復制的主數據庫名字
從機修改 my.cnf 配置文件：
1、[必須]從服務器唯一 ID：vim etc/my.cnf（進入修改配置文件）
...#server-id=1 // 注釋吊...server-id=1 // 開啟...2、[可選]啟用二進制日志
三、配置文件，請主機 + 從機都重啟后臺 mysql 服務
主機：手動重啟
Linux 從機命名：
service mysql stop
service mysql start
四、主機從機都關閉防火墻
windows 手動關閉
關閉虛擬機 linux 防火墻：service iptables stop
五、在 Windows 主機上建立帳戶并授權 slave
GRANT REPLICATION SLAVE ON . TO‘zhangsan’@‘從機器數據庫 IP’IDENTIFIED BY‘123456’;
刷新：flush privileges;
查詢 master 的狀態
show master status;
記錄下 File 和 Position 的值
執行完此步驟后不要再操作主服務器 MYSQL，防止主服務器狀態值變化
六、在 Linux 從機上配置需要復制的主機
CHANGE MASTER TO MASTER_HOST=’主機 IP’,
MASTER_USER=‘zhangsan’,
MASTER_PASSWORD=’123456’,
MASTER_LOG_FILE= File 名字’,
MASTER_LOG_POS=Position 數字;
啟動從服務器復制功能：start slave;
show slave status\G（下面兩個參數都是 Yes，則說明主從配置成功!）
Slave_IO_Running：Yes
Slave_SQL_Running：Yes
七、主機新建庫、新建表、insert 記錄，從機復制
主機操作
從機（自動同步）
八、如何停止從服務復制功能：stop slave;
如果有一段數據暫時不要？
從機：
主機（需要重新查刻度）：
讀到這里，這篇“MySQL 的 SQL 優化、索引優化、鎖機制、主從復制知識有哪些”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注丸趣 TV 行業資訊頻道。
