本篇文章給大家帶來了關于mysql高級技巧中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 | 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列的表示用第一個和第四個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進行排序，再從磁盤取其他字段。

  取一批數據，要對磁盤進行了兩次掃描，眾所周知，IO是很耗時的，所以在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)

8 MySQL鎖機制

8.1 概述

定義：

鎖是計算機協調多個進程或線程并發訪問某一資源的機制。

在數據庫中，除傳統的計算資源（如CPU、RAM、I/O等）的爭用以外，數據也是一種供許多用戶共享的資源。如何保證數據并發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖沖突也是影響數據庫并發訪問性能的一個重要因素。從這個角度來說，鎖對數據庫而言顯得尤其重要，也更加復雜

例子：京東購物

打個比方，我們到京東上買一件商品，商品只有一件庫存，這個時候如果還有另一個人買，那么如何解決是你買到還是另一個人買到的問題？

這里肯定要用到事務，我們先從庫存表中取出物品數量，然后插入訂單，付款后插入付款表信息，然后更新商品數量。在這個過程中，使用鎖可以對有限的資源進行保護，解決隔離和并發的矛盾

鎖的分類：

• 從對數據操作的類型（讀寫）分

  • 讀鎖（共享鎖）：針對同一份數據，多個讀操作可以同時進行而不會互相影響。
  • 寫鎖（排它鎖）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。

• 從對數據操作的粒度分

  • 表鎖
  • 行鎖

8.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不適合做寫為主表的引擎。因為寫鎖后，其他線程不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成永遠阻塞

8.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）系統中為什么會有如此多的等待，然后根據分析結果著手指定優化計劃。

優化建議

• 盡可能讓所有數據檢索都通過索引來完成，避免無索引行鎖升級為表鎖。
• 合理設計索引，盡量縮小鎖的范圍
• 盡可能較少檢索條件，避免間隙鎖
• 盡量控制事務大小，減少鎖定資源量和時間長度
• 盡可能低級別事務隔離

頁鎖

開銷和加鎖時間界于表鎖和行鎖之間;會出現死鎖;鎖定粒度界于表鎖和行鎖之間，并發度一般。（了解一下即可）

9 主從復制

9.1 復制的基本原理

slave會從master讀取binlog來進行數據同步

原理圖：

MySQL復制過程分成三步：

• 1、master將改變記錄到二進制日志(binary log)。這些記錄過程叫做二進制日志事件，binary log events;
• 2、slave將master的binary log events拷貝到它的中繼日志(relay log) ;
• 3、slave重做中繼日志中的事件，將改變應用到自己的數據庫中。MySQL復制是異步的且串行化的

9.2 復制的基本原則

• 每個slave只有一個master
• 每個slave只能有一個唯一的服務器ID
• 每個master可以有多個salve

復制的最大問題是延遲。

9.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 statusG（下面兩個參數都是Yes，則說明主從配置成功!）

  • Slave_IO_Running：Yes
  • Slave_SQL_Running：Yes

七、主機新建庫、新建表、insert記錄，從機復制

• 主機操作
  
• 從機（自動同步）
  

八、如何停止從服務復制功能：stop slave;

如果有一段數據暫時不要？

從機：

主機（需要重新查刻度）：

推薦學習：mysql視頻教程