本篇文章給大家帶來了關於的相關知識，主要介紹了關於SQL優化、索引優化、鎖機制和主從複製的相關問題，希望對大家有幫助。

推薦學習：

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>
FROM
    <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_number>

MySQL機讀順序：

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、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,'[email protected]');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'[email protected]');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'[email protected]');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'[email protected]');

//查詢
mysql> select * from tbl_user;
+----+------+------+-----------+
| id | name | age  | email     |
+----+------+------+-----------+
|  1 | 1aa1 |   21 | [email protected] |
|  2 | 2bb2 |   23 | [email protected] |
|  3 | 3cc3 |   24 | [email protected] |
|  4 | 4dd4 |   26 | [email protected] |
+----+------+------+-----------+
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;

如果有一段資料暫時不要？

從機：
在這裡插入圖片描述

主機（需要重新查刻度）：
在這裡插入圖片描述

9 MySQL從入門到精通PPT大全下載

在這裡插入圖片描述

下載地址：MySQL從入門到精通PPT大全（13份）.rar

推薦學習：

以上就是MySQL知識總結之SQL優化、索引優化、鎖機制、主從複製的詳細內容，更多請關注TW511.COM其它相關文章！

MySQL知識總結之SQL優化、索引優化、鎖機制、主從複製

0 儲存引擎介紹

1 SQL效能分析

2 常見通用的JOIN查詢

SQL執行載入順序

七種JOIN寫法

3 索引介紹

3.1 索引是什麼

3.2 索引優劣勢

3.3 索引分類和建索引命令語句

3.4 索引結構與檢索原理

3.5 哪些情況適合建索引

3.6 哪些情況不適合建索引

4 效能分析

4.1 效能分析前提知識

4.2 Explain使用簡介

4.3 執行計劃包含的資訊欄位解釋（重中之重）

id（表的讀取順序）

select_type（ 資料讀取操作的操作型別）

table（顯示執行的表名）

type（存取型別排列）

possible_keys（哪些索引可以使用）

key（哪些索引被實際使用）

key_len（消耗的位元組數）

ref（表之間的參照）

rows（每張表有多少行被優化器查詢）

Extra [ˈekstrə]

練習

5 索引優化

5.1 索引單表優化案例

5.2 索引兩表優化案例

5.3 索引三表優化案例

5.4 索引失效

5.5 索引面試題分析

5.6 總結

6 查詢擷取分析

6.1 小表驅動大表

6.2 Order by 關鍵字排序優化

6.3 Group by 優化

6.4 慢查詢紀錄檔（重點）

6.5 批次插入資料指令碼

6.6 Show Profile進行sql分析（重中之重）

6.7 全域性查詢紀錄檔

7 MySQL鎖機制

7.1 概述

7.2 表鎖（偏讀）

讀鎖案例講解1

讀鎖案例講解2

表鎖總結

7.3 行鎖（偏寫）

行鎖案例講解

索引失效行鎖變表鎖

間隙鎖

面試題：如何鎖定一行

行鎖總結

8 主從複製

8.1 複製的基本原理

8.2 複製的基本原則

8.3 一主一從常見設定

9 MySQL從入門到精通PPT大全下載

select_type（資料讀取操作的操作型別）