MySQL8高級之架構和優化 讓

第01章 Linux下MySQL的安裝與使用

1、安裝

1.1、docker安裝

docker run -d \
-p 3309:3306 \
-v /atguigu/mysql/mysql8/conf:/etc/mysql/conf.d \
-v /atguigu/mysql/mysql8/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name atguigu-mysql8 \
--restart=always \
mysql:8.0.29

1.2、查看是否啟動

docker ps

1.3、測驗遠程鏈接

測驗連接：MySQL 8 版本，圖形連接時還會出現如下問題

配置新連接報錯：錯誤號碼 2058，出現這個原因是MySQL 8 之前的版本中加密規則是mysql_native_password，而在MySQL 8之后，加密規則是caching_sha2_password，

解決方案有兩種，一種是升級SQLyog和Navicat（因此，新版SQLyog和Navicat不會出現此問題），另一種是把MySQL用戶登錄密碼加密規則還原成mysql_native_password，

解決方法：登錄你的 MySQL 資料庫

#進入容器：env LANG=C.UTF-8 避免容器中顯示中文亂碼
docker exec -it atguigu-mysql8 env LANG=C.UTF-8 /bin/bash
#進入容器內的mysql命令列
mysql -uroot -p
#修改默認密碼校驗方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

然后再重新配置SQLyog的連接，重新填寫密碼，則可連接成功了，

2、字符集

2.1、默認字符集

MySQL 8版本之前，默認字符集為 latin1（ISO-8859-1），不支持中文，使用前必須設定字符集為utf8（utf8mb3）或utf8mb4，從MySQL 8開始，資料庫的默認字符集為 utf8mb4 ，從而避免中文亂碼的問題，

SHOW VARIABLES LIKE '%char%';

2.2、utf8與utf8mb4

utf8 字符集表示一個字符需要使用1～4個位元組，但是我們常用的一些字符使用1～3個位元組就可以表示了，而字符集表示一個字符所用的最大位元組長度，在某些方面會影響系統的存盤和性能，所以設計MySQL的設計者偷偷的定義了兩個概念：

utf8mb3 ：閹割過的 utf8 字符集，只使用1～3個位元組表示字符，（無法存盤emoji表情）

MySQL5.7中的utf8是utf8mb3字符集

utf8mb4 ：正宗的 utf8 字符集，使用1～4個位元組表示字符，

MySQL8.0中的utf8是utf8mb4字符集

3、 SQL大小寫規范

3.1、Windows和Linux的區別

Windows環境：

全部不區分大小寫

Linux環境：

1、資料庫名、表名、表的別名、變數名嚴格區分大小寫；

2、列名與列的別名不區分大小寫，

3、關鍵字、函式名稱不區分大小寫；

3.2、Linux下大小寫規則設定（了解）

在MySQL 8中設定的具體步驟為：

1、停止MySQL服務 
2、洗掉資料目錄，即洗掉 /var/lib/mysql 目錄 
3、在MySQL組態檔（/etc/my.cnf ）的 [mysqld] 中添加 lower_case_table_names=1 
4、初始化資料目錄 mysqld --initialize --user=mysql
5、啟動MySQL服務 systemctl start mysqld

注意：不建議在開發程序中修改此引數，將會丟失所有資料

4、sql_mode

4.1、寬松模式 vs 嚴格模式

寬松模式：

執行錯誤的SQL或插入不規范的資料，也會被接受，并且不報錯，

嚴格模式：

執行錯誤的SQL或插入不規范的資料，會報錯，MySQL5.7版本開始就將sql_mode默認值設定為了嚴格模式，

4.2、查看和設定sql_mode

查詢sql_mode的值：

SELECT @@session.sql_mode; 
SELECT @@global.sql_mode; 
-- 或者 
SHOW VARIABLES LIKE 'sql_mode'; --session級別

臨時設定sql_mode的值：

SET GLOBAL sql_mode = 'mode1,model2,...'; --全域，要重新啟動客戶端生效，重啟MySQL服務后失效
SET SESSION sql_mode = 'mode1,model2,...'; --當前會話生效效，關閉當前會話就不生效了，可以省略SESSION關鍵字

在mysql組態檔中配置，永久生效：在宿主機上執行以下命令，創建組態檔：

vim /atguigu/mysql/mysql8/conf/my.cnf

編輯組態檔

[mysqld]
sql-mode = "mode1,model2,..."

重啟mysql容器

docker restart atguigu-mysql8

4.3、錯誤開發演示

建表并插入資料：

CREATE DATABASE atguigudb;
USE atguigudb;
CREATE TABLE employee(id INT, `name` VARCHAR(16),age INT,dept INT);
INSERT INTO employee VALUES(1,'zhang3',33,101);
INSERT INTO employee VALUES(2,'li4',34,101);
INSERT INTO employee VALUES(3,'wang5',34,102);
INSERT INTO employee VALUES(4,'zhao6',34,102);
INSERT INTO employee VALUES(5,'tian7',36,102);

需求：查詢每個部門年齡最大的人

-- 錯誤演示
SELECT `name`, dept, MAX(age) FROM employee GROUP BY dept;

以上查詢陳述句在 “ONLY_FULL_GROUP_BY” 模式下查詢出錯，因為select子句中的name列并沒有出現在group by子句中，也沒有出現在函式中：

在非 “ONLY_FULL_GROUP_BY” 模式下可以正常執行，但是得到的是錯誤的結果：

SET SESSION sql_mode = '';

正確的查詢方式：查詢應該分兩個步驟

1、查詢每個部門最大的年齡

2、查詢人

正確的陳述句：

SELECT e.* 
FROM employee e
INNER JOIN (SELECT dept, MAX(age) age FROM employee GROUP BY dept) AS maxage 
ON e.dept = maxage.dept AND e.age = maxage.age;

測驗完成后再將sql_mode設定回來：

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

4.4、sql_mode常用值（了解）

ONLY_FULL_GROUP_BY：對于GROUP BY聚合操作，SELECT子句中只能包含函式和 GROUP BY 中出現的欄位，
STRICT_TRANS_TABLES：
- 對于支持事務的表，如果發現某個值缺失或非法，MySQL將拋出錯誤，陳述句會停止運行并回滾，
- 對于不支持事務的表，不做限制，提高性能，
NO_ZERO_IN_DATE：不允許日期和月份為零，
NO_ZERO_DATE：MySQL資料庫不允許插入零日期，插入零日期會拋出錯誤而不是警告，
ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE程序中，如果資料被零除，則產生錯誤而非警告，如果未給出該模式，那么資料被零除時MySQL回傳NULL，
NO_ENGINE_SUBSTITUTION：如果需要的存盤引擎被禁用或不存在，那么拋出錯誤，不設定此值時，用默認的存盤引擎替代，

第02章邏輯架構

1、邏輯架構剖析

1.1、服務器處理客戶端請求

下面是MySQL5.7使用的經典架構圖，MySQL 8中去掉了Caches&Buffers部分：

b7a8eb46-1168-434c-b63b-ec775affab7b

1.2、Connectors（客戶端）

MySQL服務器之外的客戶端程式，與具體的語言相關，例如Java中的JDBC，圖形用戶界面SQLyog等，本質上都是在TCP連接上通過MySQL協議和MySQL服務器進行通信，

1.3、MySQL Server（服務器）

第1層：連接層

客戶端訪問 MySQL 服務器前，做的第一件事就是建立 TCP 連接，
經過三次握手建立連接成功后， MySQL 服務器對 TCP 傳輸過來的賬號密碼做身份認證、權限獲取，
- 用戶名或密碼不對，會收到一個Access denied for user錯誤，客戶端程式結束執行
- 用戶名密碼認證通過，會從權限表查出賬號擁有的權限與連接關聯，之后的權限判斷邏輯，都將依賴于此時讀到的權限
TCP 連接收到請求后，必須要分配給一個執行緒專門與這個客戶端的互動，所以還會有個執行緒池，去走后面的流程，每一個連接從執行緒池中獲取執行緒，省去了創建和銷毀執行緒的開銷，

第2層：服務層

Management Serveices & Utilities：系統管理和控制工具

SQL Interface：SQL介面：

接收用戶的SQL命令，并且回傳用戶需要查詢的結果，比如SELECT ... FROM就是呼叫SQL Interface
MySQL支持DML（資料操作語言）、DDL（資料定義語言）、存盤程序、視圖、觸發器、自定義函式等多種SQL語言介面

Parser：決議器：

在SQL命令傳遞到決議器的時候會被決議器驗證和決議，決議器中SQL 陳述句進行詞法分析、語法分析、語意分析，并為其創建語法樹，

詞法分析：將整個陳述句拆分成一個個欄位
語法分析：將詞法分析拆分出的欄位，按照MySQl語法規則，生成決議樹
語意分析：檢查決議樹是否合法，比如查看表是否存在，列是否存在

典型的決議樹如下：

Optimizer：查詢優化器：

SQL陳述句在語法決議后、查詢前會使用查詢優化器對查詢進行優化，確定SQL陳述句的執行路徑，生成一個執行計劃，

Caches & Buffers：查詢快取組件：

MySQL內部維持著一些Cache和Buffer，比如Query Cache用來快取一條SELECT陳述句的執行結果，如果能夠在其中找到對應的查詢結果，那么就不必再進行查詢決議、查詢優化和執行的整個程序了，直接將結果反饋給客戶端，
這個快取機制是由一系列小快取組成的，比如表快取，記錄快取，key快取，權限快取等，
這個查詢快取可以在不同客戶端之間共享，
問：大多數情況查詢快取就是個雞肋，為什么呢？
- 只有相同的SQL陳述句才會命中查詢快取，兩個查詢請求在任何字符上的不同（例如：空格、注釋、大小寫），都會導致快取不會命中，
- 在兩條查詢之間有 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTER TABLE 、 DROP TABLE 或 DROP DATABASE 陳述句也會導致快取失效
- 因此 MySQL的查詢快取命中率不高，所以在MySQL 8之后就拋棄了這個功能，

第3層：引擎層

存盤引擎層（ Storage Engines），負責MySQL中資料的存盤和提取，對物理服務器級別維護的底層資料執行操作，服務器通過API與存盤引擎進行通信，不同的存盤引擎具有的功能不同，管理的表有不同的存盤結構，采用的存取演算法也不同，這樣我們可以根據自己的實際需要進行選取，例如MyISAM引擎和InnoDB引擎，

1.4、存盤層

所有的資料、資料庫、表的定義、表的每一行的內容、索引，都是存在檔案系統 上，以檔案的方式存在，并完成與存盤引擎的互動，

1.5、查詢流程說明

首先，MySQL客戶端通過協議與MySQL服務器建連接，通過SQL介面發送SQL陳述句，先檢查查詢快取，如果命中，直接回傳結果，否則進行陳述句決議，也就是說，在決議查詢之前，服務器會先訪問查詢快取，如果某個查詢結果已經位于快取中，服務器就不會再對查詢進行決議、優化、以及執行，它僅僅將快取中的結果回傳給用戶即可，這將大大提高系統的性能，

接下來是決議程序，MySQL決議器通過關鍵字將SQL陳述句進行決議，并生成一棵對應的決議樹，決議器使用MySQL語法規則驗證和決議SQL陳述句，例如，它將驗證是否使用了錯誤的關鍵字，或者使用關鍵字的順序是否正確，引號能否前后匹配等；前處理器則根據MySQL規則進一步檢查決議樹是否合法，例如，這里將檢查資料表和資料列是否存在，還會決議名字和別名，看是否有歧義等，并生成一棵新決議樹，新決議樹可能和舊決議樹結構一致，

然后是優化程序，MySQL優化程式會對我們的陳述句做一些優化，將查詢的IO成本和CPU成本降到最低，優化的結果就是生成一個執行計劃，這個執行計劃表明了應該使用哪些索引執行查詢，以及表之間的連接順序是啥樣，必要時將子查詢轉換為連接、運算式簡化等等，我們可以使用EXPLAIN陳述句來查看某個陳述句的執行計劃，

最后，進入執行階段，完成查詢優化后，查詢執行引擎會按照生成的執行計劃呼叫存盤引擎提供的介面執行SQL查詢并將結果回傳給客戶端，在MySQL8以下的版本，如果設定了查詢快取，這時會將查詢結果進行快取，再回傳給客戶端，

2、SQL執行流程

利用SHOW VARIABLES 可以查看SQL的執行流程，使用前需要先開啟該功能：

2.1、MySQL8

1.開啟profiling

確認profiling是否開啟

SHOW VARIABLES LIKE '%profiling%';

profiling=0 代表關閉，我們需要把 profiling 打開，即設定為 1：

SET profiling = 1;  -- profiling = ON

2.顯示查詢

執行任意SQL陳述句：

SELECT * FROM atguigudb.employee; 
SELECT * FROM atguigudb.employee WHERE id = 5;

顯示最近的幾次查詢：

SHOW PROFILES;

3.查看執行流程

查看最后一個SQL的執行流程：

SHOW PROFILE;

查看指定SQL的執行流程：查詢指定的 Query ID

SHOW PROFILE FOR QUERY 3;

查詢更豐富的內容：

SHOW PROFILE cpu,block io FOR QUERY 3;

SHOW PROFILE 的常用查詢引數:
- ? ①ALL：顯示所有的開銷資訊，
- ? ②BLOCK IO：顯示塊IO開銷，
- ? ③CONTEXT SWITCHES：背景關系切換開銷，
- ? ④CPU：顯示CPU開銷資訊，
- ? ⑤IPC：顯示發送和接收開銷資訊，
- ? ⑥MEMORY：顯示記憶體開銷資訊，
- ? ⑦PAGE FAULTS：顯示頁面錯誤開銷資訊，
- ? ⑧SOURCE：顯示和Source_function，Source_file，Source_line相關的開銷資訊，
- ? ⑨SWAPS：顯示交換次數開銷資訊，

2.2、MySQL5.7（了解）

1. 查看查詢快取是否啟用

SHOW VARIABLES LIKE '%query_cache_type%';

2. 開啟查詢快取

修改組態檔：vim /etc/my.cnf
新增一行：query_cache_type=1
重啟MySQL：systemctl restart mysqld

3. 執行SQL并查看執行流程

參考MySQl8中執行流程的啟用和查看方式，在MySQL5.7中查看執行流程

第一次執行查詢SQL：

第二次執行相同的SQL：

3、存盤引擎

3.1、查看存盤引擎

查看MySQL提供什么存盤引擎

SHOW ENGINES;

下面的結果表示MySQL中默認使用的存盤引擎是InnoDB，支持事務，行鎖，外鍵，支持分布式事務(XA)，支持保存點(回滾)

也可以通過以下陳述句查看默認的存盤引擎：

SHOW VARIABLES LIKE '%default_storage_engine%';

3.2、設定存盤引擎（了解）

方法1：

設定默認存盤引擎：

SET DEFAULT_STORAGE_ENGINE=MyISAM;

方法2：

或者修改 my.cnf 檔案：vim /etc/my.cnf
在[mysqld]節點下新增一行：default-storage-engine=MyISAM
重啟MySQL：systemctl restart mysqld

方法3：

我們可以為不同的表設定不同的存盤引擎

CREATE TABLE 表名( 建表陳述句 ) ENGINE = 存盤引擎名稱;
ALTER TABLE 表名 ENGINE = 存盤引擎名稱;

-- 例如：
CREATE TABLE student(id INT, `name` VARCHAR(16),age INT,dept INT) ENGINE = MyISAM;

3.3、各種引擎介紹

1. InnoDB存盤引擎

InnoDB是MySQL的默認事務型引擎，它被設計用來處理大量的短期(short-lived)事務，可以確保事務的完整提交(Commit)和回滾(Rollback)，
除非有非常特別的原因需要使用其他的存盤引擎，否則應該優先考慮InnoDB引擎，
InnoDB不僅快取索引還要快取真實資料，對記憶體要求較高，而且記憶體大小對性能有決定性的影響，

2. MyISAM存盤引擎

MyISAM提供了大量的特性，包括全文索引、壓縮、空間函式(GIS)等，但MyISAM不支持事務和行級鎖，有一個毫無疑問的缺陷就是崩潰后無法安全恢復，
優勢是訪問的速度快，對事務完整性沒有要求或者以SELECT、INSERT為主的應用，
MyISAM只快取索引，不快取真實資料，

3. Archive引擎

Archive檔案存盤引擎只支持INSERT和SELECT操作，
Archive表適合日志和資料采集（檔案）類應用，
根據英文的測驗結論來看，Archive表比MyISAM表要小大約75%，比支持事務處理的InnoDB表小大約83%，

4. Blackhole引擎（黑洞）

Blackhole引擎沒有實作任何存盤機制，它會丟棄所有插入的資料，不做任何保存，
但服務器會記錄Blackhole表的日志，所以可以用于復制資料到備庫，或者簡單地記錄到日志，但這種應用方式會碰到很多問題，因此并不推薦，

5. CSV引擎

CSV引擎可以將普通的CSV檔案作為MySQL的表來處理，但不支持索引，
CSV引擎可以作為一種資料交換的機制，非常有用，
CSV存盤的資料直接可以在作業系統里，用文本編輯器，或者excel讀取，

6. Memory引擎

如果需要快速地訪問資料，并且這些資料不會被修改，重啟以后丟失也沒有關系，那么使用Memory表是非常有用，
Memory表至少比MyISAM表要快一個數量級，

7. Federated引擎

Federated引擎是訪問其他MySQL服務器的一個代理（跨庫關聯查詢），盡管該引擎看起來提供了一種很好的跨服務器的靈活性，但也經常帶來問題，因此默認是禁用的，

3.4、MyISAM和InnoDB的區別

對比項	MyISAM	InnoDB
外鍵	不支持	支持
事務	不支持	支持
行表鎖	表鎖，即使操作一條記錄也會鎖住整個表，不適合高并發的操作	行鎖，操作時只鎖某一行，不對其它行有影響，適合高并發的操作
快取	只快取索引，不快取真實資料	不僅快取索引還要快取真實資料，對記憶體要求較高，而且記憶體大小對性能有決定性的影響，支持聚簇索引
關注點	并發查詢，節省資源、消耗少、簡單業務	并發寫、事務、更大更復雜的資源操作
默認使用	N	Y
自帶系統表使用	Y	N

第03章 SQL預熱

1、創建測驗資料

CREATE TABLE `t_dept` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
);
 
CREATE TABLE `t_emp` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT DEFAULT NULL,
 `deptId` INT DEFAULT NULL,
`empno` INT NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
);

INSERT INTO t_dept(id,deptName,address) VALUES(1,'華山','華山');
INSERT INTO t_dept(id,deptName,address) VALUES(2,'丐幫','洛陽');
INSERT INTO t_dept(id,deptName,address) VALUES(3,'峨眉','峨眉山');
INSERT INTO t_dept(id,deptName,address) VALUES(4,'武當','武當山');
INSERT INTO t_dept(id,deptName,address) VALUES(5,'明教','光明頂');
INSERT INTO t_dept(id,deptName,address) VALUES(6,'少林','少林寺');

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(1,'風清揚',90,1,100001);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐沖',24,1,100003);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(5,'喬峰',35,2,100005);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(6,'滅絕師太',70,3,100006);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(7,'周芷若',20,3,100007);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(8,'張三豐',100,4,100008);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(9,'張無忌',25,5,100009);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(10,'韋小寶',18,NULL,100010);

2、常見七種JOIN查詢

需求1：查詢所有有部門的員工資訊以及他所在的部門資訊

在A、和B中都存在的資料 => 查詢A、B兩表交集

SELECT * FROM t_emp a INNER JOIN t_dept b ON a.deptid = b.id;

需求2：查詢所有用戶，并顯示其部門資訊（如果員工沒有所在部門，也會被列出） => 查詢A的全集

SELECT * 
FROM t_emp a 
LEFT JOIN t_dept b ON a.deptid = b.id;

需求3：列出所有部門，并顯示其部門的員工資訊（如果部門沒有員工，也會被列出）=> 查詢B的全集

SELECT * 
FROM t_emp a 
RIGHT JOIN t_dept b ON a.deptid = b.id;

需求4：查詢沒有加入任何部門的員工（先查詢所有員工，再過濾掉包含部門的資料） => 查詢A且不包含B

SELECT * FROM t_emp a 
LEFT JOIN t_dept b ON a.deptid = b.id 
WHERE b.id IS NULL;

需求5：查詢沒有任何員工的部門 => 查詢B且不包含A

SELECT * FROM t_emp a 
RIGHT JOIN t_dept b ON a.deptid = b.id 
WHERE a.id IS NULL;

需求6：查詢所有員工和所有部門 => AB全有

MySQL FULL JOIN 的實作：因為MySQL不支持FULL JOIN，下面是替代方法

LEFT JOIN + UNION(合并并去重) + RIGHT JOIN

注意：

UNION和UNION ALL要求欄位數量和順序都一致

如果確定兩表結果不會重復，則使用UNION ALL提升效率

SELECT * 
FROM t_emp a 
LEFT JOIN t_dept b ON a.deptid = b.id 
UNION 
SELECT * 
FROM t_emp a 
RIGHT JOIN t_dept b ON a.deptid = b.id;

需求7：查詢沒有加入任何部門的員工，以及查詢出部門下沒有任何員工的部門 => A的獨有+B的獨有

SELECT * 
FROM t_emp a 
LEFT JOIN t_dept b ON a.deptid = b.id 
WHERE b.id IS NULL 

UNION ALL

SELECT * 
FROM t_emp a 
RIGHT JOIN t_dept b ON a.deptid = b.id 
WHERE a.id IS NULL;

3、擴展掌門人

3.1、增加掌門人欄位

ALTER TABLE t_dept ADD CEO INT(11);
UPDATE t_dept SET CEO=2 WHERE id=1;
UPDATE t_dept SET CEO=4 WHERE id=2;
UPDATE t_dept SET CEO=6 WHERE id=3;
UPDATE t_dept SET CEO=8 WHERE id=4;
UPDATE t_dept SET CEO=9 WHERE id=5;

3.2、練習

需求1：求各個門派對應的掌門人

SELECT b.deptname, a.name FROM t_dept b LEFT JOIN t_emp a ON b.ceo = a.id;

需求2：求所有掌門人的平均年齡

SELECT AVG(a.age) FROM t_emp a INNER JOIN t_dept b ON a.id = b.ceo;

需求3：求所有人物對應的掌門名稱（4種寫法分析）

三表左連接方式

-- 員工表(t_emp)、部門表(t_dept)、ceo(t_emp)表 關聯查詢
SELECT emp.name, ceo.name AS ceoname 
FROM t_emp emp
LEFT JOIN t_dept dept ON emp.deptid = dept.id 
LEFT JOIN t_emp ceo ON dept.ceo = ceo.id;

子查詢方式

SELECT 
emp.name, 
(SELECT ceo.name FROM t_emp ceo WHERE ceo.id = dept.ceo) AS ceoname
FROM t_emp emp
LEFT JOIN t_dept dept ON emp.deptid = dept.id

臨時表連接方式1

SELECT emp_with_ceo_id.name, emp.name AS ceoname FROM 
-- 查詢所有員工及對應的ceo的id
( 
SELECT emp.name, dept.ceo 
FROM t_emp emp 
LEFT JOIN t_dept dept ON emp.deptid = dept.id 
) emp_with_ceo_id
LEFT JOIN t_emp emp ON emp_with_ceo_id.ceo = emp.id;

臨時表連接方式2

SELECT emp.name, ceo.ceoname FROM t_emp emp LEFT JOIN
-- 查詢并創建臨時表ceo：包含ceo的部門id和ceo的name
( 
SELECT emp.deptId AS deptId, emp.name AS ceoname 
FROM t_emp emp 
INNER JOIN t_dept dept ON emp.id = dept.ceo 
) ceo
ON emp.deptId = ceo.deptId;

第04章 MySQL索引

1、索引簡介

1.1、什么是索引☆

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取資料的資料結構，
索引的本質：索引是資料結構，你可以簡單理解為“排好序的快速查找資料結構”，這些資料結構以某種方式指向資料，可以在這些資料結構的基礎上實作高級查找演算法，

1.2、索引的優缺點☆

優點：

（1）提高資料檢索的效率，降低資料庫的IO成本
（2）保證表中每條記錄的唯一性 ，

缺點：
（1）創建索引和維護索引要耗費時間 ，
（2）索引是存盤在磁盤上的，因此需要占用磁盤空間 ，

1.3、索引分類☆

從功能邏輯上劃分，索引主要有 4 種，分別是普通索引、唯一索引、主鍵索引、全文索引，
按照作用欄位個數劃分，索引可以分為單列索引和聯合索引，
按照物理實作方式劃分，索引可以分為 2 種，分別是聚簇索引和非聚簇索引，

2、樹

2.1、二叉樹

二叉樹

樹有很多種，每個節點最多只能有兩個子節點的一種形式稱為二叉樹，二叉樹的子節點分為左節點和右節點，

二叉搜索樹BST

BST(Binary Sort(Search) Tree)：對于二叉排序樹的任何一個非葉子節點，要求左子節點的值比當前節點的值小，右子節點的值比當前節點的值大，

特別說明：如果有相同的值，可以將該節點放在左子節點或右子節點，

BST的生成演示：https://www.cs.usfca.edu/~galles/visualization/BST.html

下圖就是一種可能的索引方式示例：

左邊是資料表，一共有兩列七條記錄，最左邊的是資料記錄的物理地址：

為了加快Col2的查找，可以維護一個右邊所示的二叉查找樹，每個節點分別包含索引鍵值和一個指向對應資料記錄物理地址的指標，這樣就可以運用二叉查找獲取到相應資料，從而快速的檢索出符合條件的記錄：

BST的問題

上面演示的實際上是一個特殊的二叉搜索樹，叫做平衡二叉搜索樹，如果二叉樹不平衡，例如左子樹全部為空，從形式上看，更像一個單鏈表，不能發揮BST的優勢，
解決方案：平衡二叉樹(AVL)

平衡二叉樹（AVL）

AVL樹全稱G.M. Adelson-Velsky和E.M. Landis，這是兩個人的人名，

平衡二叉樹也叫平衡二叉搜索樹（Self-balancing binary search tree）又被稱為AVL樹，可以保證查詢效率較高，

具有以下特點：

它是一棵空樹或它的左右兩個子樹的高度差的絕對值不超過1
并且左右兩個子樹都是一棵平衡二叉樹，

AVL的生成演示：https://www.cs.usfca.edu/~galles/visualization/AVLtree.html

AVL的問題

眾所周知，IO操作的效率很低，在大量資料存盤中，查詢時我們不能一下子將所有資料加載到記憶體中，只能逐節點加載（一個節點一次IO），如果我們利用二叉樹作為索引結構，那么磁盤的IO次數和索引樹的高度是相關的，平衡二叉樹由于樹深度過大而造成磁盤IO讀寫過于頻繁，進而導致效率低下，

為了提高查詢效率，就需要減少磁盤IO數，為了減少磁盤IO的次數，就需要盡量降低樹的高度 ，需要把原來“瘦高”的樹結構變的“矮胖”，樹的每層的分叉越多越好，針對同樣的資料，如果我們把二叉樹改成三叉樹：

上面的例子中，我們將二叉樹變成了三叉樹，降低了樹的高度，如果能夠在一個節點中存放更多的資料，我們還可以進一步減少節點的數量，從而進一步降低樹的高度，這就是多叉樹，

2.2、B樹

B-Tree即B樹，Balance Tree，平衡樹,B樹就是典型的多叉樹，它的高度遠小于平衡二叉樹的高度，
B樹的階：節點的最多子節點個數，

上圖所表示的 B 樹就是一棵 3 階的 B 樹，假設一個磁盤塊可以存盤一個節點的資料，我們可以看下磁盤塊 2，里面的關鍵字為（8，12），它有 3 個孩子 (3，5)，(9，10) 和 (13，15)，你能看到 (3，5) 小于 8，(9，10) 在 8 和 12 之間，而 (13，15)大于 12，三節點的子樹的值大小仍然遵守 BST 二叉排序樹的規則，

假設我們想要查找的資料項是 9 ，那么步驟可以分為以下幾步：

第一次磁盤IO：找到根節點磁盤塊1，讀入記憶體，執行二分查找，9 小于 17 ，得到指標 P1；
第二次磁盤IO：按照指標P1找到磁盤塊 2，讀入記憶體，執行二分查找， 9 在 8 和 12 之間，得到指標 P2；
第三次磁盤IO：按照指標P2找到磁盤塊 6，讀入記憶體，執行二分查找，找到了資料項 9，

你能看出來在 B 樹的搜索程序中，我們比較的次數并不少，但如果把資料讀取出來然后在記憶體中進行比較，這個時間就是可以忽略不計的，而讀取磁盤塊本身需要進行 I/O 操作，消耗的時間比在記憶體中進行比較所需要的時間要多，是資料查找用時的重要因素， B 樹相比于平衡二叉樹來說磁盤 I/O 操作要少，在資料查詢中比平衡二叉樹效率要高，所以只要樹的高度足夠低，IO次數足夠少，就可以提高查詢性能 ，

再舉例：

假設使用B樹作為索引結構，存放MySQL中的資料：

藍色部分表示資料的主鍵，黃色部分表示除主鍵外的其他資料，紫色部分表示指向子節點的指標

3、MySQL的索引結構：B+tree

3.1、InnoDB中的索引

3.1.1、設計索引

假設有一個表index_demo，表中有2個INT型別的列，1個CHAR(1)型別的列，c1列為主鍵：

CREATE TABLE index_demo(c1 INT,c2 INT,c3 CHAR(1),PRIMARY KEY(c1)) ;

index_demo表的簡化的行格式示意圖如下：

record_type：表示記錄的型別， 0是普通記錄、 2是最小記錄、 3 是最大記錄、1是B+樹非葉子節點記錄，
next_record：表示下一條記錄的相對位置，我們用箭頭來表明下一條記錄，
各個列的值：這里只記錄在 index_demo 表中的三個列，分別是 c1 、 c2 和 c3 ，

把一些記錄放到頁里的示意圖就是（這里一頁就是一個磁盤塊，代表一次IO）：

MySQL InnoDB的默認的頁大小是16KB：

-- 查看默認頁的大小
SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

因此資料存盤在磁盤中，可能會占用多個資料頁，如果各個頁中的記錄沒有規律，我們就不得不依次遍歷所有的資料頁，如果我們想快速的定位到需要查找的記錄在哪些資料頁中，我們可以這樣做：

下一個資料頁中用戶記錄的主鍵值必須大于上一個頁中用戶記錄的主鍵值
給所有的頁建立目錄項

以頁28為例，它對應目錄項2 ，這個目錄項中包含著該頁的頁號28以及該頁中用戶記錄的最小主鍵值 5，我們只需要把幾個目錄項在物理存盤器上連續存盤（比如：陣列），就可以實作根據主鍵值快速查找某條記錄的功能了，比如：查找主鍵值為 20 的記錄，具體查找程序分兩步：

先從目錄項中根據二分法快速確定出主鍵值為20的記錄在目錄項3中（因為 12 ≤ 20 < 209 ），對應頁9，
再到頁9中根據二分法快速定位到主鍵值為 20 的用戶記錄，

至此，針對資料頁做的簡易目錄就搞定了，這個目錄有一個別名，稱為索引 ，

3.1.2、InnoDB中的索引方案

我們新分配一個編號為30的頁來專門存盤目錄項記錄，頁10、28、9、20專門存盤用戶記錄：

目錄項記錄和普通的用戶記錄的不同點：

目錄項記錄的 record_type 值是1，而普通用戶記錄的 record_type 值是0，
目錄項記錄只有主鍵值和頁的編號兩個列，而普通的用戶記錄的列是用戶自己定義的，包含很多列，另外還有InnoDB自己添加的隱藏列，

現在查找主鍵值為 20 的記錄，具體查找程序分兩步：

先到頁30中通過二分法快速定位到對應目錄項，因為 12 ≤ 20 < 209 ，就是頁9，
再到頁9中根據二分法快速定位到主鍵值為 20 的用戶記錄，

更復雜的情況如下：

我們生成了一個存盤更高級目錄項的頁33 ，這個頁中的兩條記錄分別代表頁30和頁32，如果用戶記錄的主鍵值在 [1, 320) 之間，則到頁30中查找更詳細的目錄項記錄，如果主鍵值不小于320 的話，就到頁32中查找更詳細的目錄項記錄，這個資料結構，它的名稱是 B+樹，

3.2、B樹和B+樹對比☆

B+ 樹和 B 樹的差異：

B+樹中非葉子節點僅用于索引，不保存資料記錄，跟記錄有關的資訊都放在葉子節點中，而B樹中，非葉子節點既保存索引，也保存資料記錄，
B+樹中所有關鍵字都在葉子節點出現，葉子節點構成一個有序鏈表，而且葉子節點本身按照關鍵字的大小從小到大順序鏈接，
B+樹中非葉子節點的關鍵字也會同時存在于子節點中，并且是在子節點中所有關鍵字的最小值，

B+樹為什么IO的次數會更少：

真實環境中一個頁存放的記錄數量是非常大的（默認16KB），假設指標與鍵值占大約10位元組，資料占 1 kb 的空間：
如果B+樹只有1層，也就是只有1個用于存放用戶記錄的節點，則最多能存放 16 條記錄，
如果B+樹有2層，最多能存放約 1600×16=25600 條記錄，
如果B+樹有3層，最多能存放約 1600×1600×16=40960000 條記錄，
如果存盤千萬級別的資料，只需要三層就夠了

B+樹的非葉子節點不存盤用戶記錄，只存盤目錄記錄，相對B樹每個節點可以存盤更多的記錄，樹的高度會更矮胖，IO次數也會更少，

3.3、聚簇索引☆

特點：

索引和資料保存在同一個B+樹中
頁內的記錄是按照主鍵的大小順序排成一個單向鏈表 ，
頁和頁之間也是根據頁中記錄的主鍵的大小順序排成一個雙向鏈表 ，
非葉子節點存盤的是記錄的主鍵+頁號，
葉子節點存盤的是完整的用戶記錄，

優點：

資料訪問更快，因為索引和資料保存在同一個B+樹中，因此從聚簇索引中獲取資料比非聚簇索引更快，
聚簇索引對于主鍵的排序查找和范圍查找速度非常快，
按照聚簇索引排列順序，查詢顯示一定范圍資料的時候，由于資料都是緊密相連，資料庫可以從更少的資料塊中提取資料，節省了大量的IO操作 ，

缺點：

插入速度嚴重依賴于插入順序，按照主鍵的順序插入是最快的方式，否則將會出現頁分裂，嚴重影響性能，因此，對于InnoDB表，我們一般都會定義一個自增的ID列為主鍵，
更新主鍵的代價很高，因為將會導致被更新的行移動，因此，對于InnoDB表，我們一般定義主鍵為不可更新，

限制：

只有InnoDB引擎支持聚簇索引，MyISAM不支持聚簇索引，
由于資料的物理存盤排序方式只能有一種，所以每個MySQL的表只能有一個聚簇索引，
如果沒有為表定義主鍵，InnoDB會選擇非空的唯一索引列代替，如果沒有這樣的列，InnoDB會隱式的定義一個主鍵作為聚簇索引，
為了充分利用聚簇索引的聚簇特性，InnoDB中表的主鍵應選擇有序的id，不建議使用無序的id，比如UUID、MD5、HASH、字串作為主鍵，無法保證資料的順序增長，

3.4、非聚簇索引☆

（二級索引、輔助索引）

聚簇索引，只能在搜索條件是主鍵值時才發揮作用，因為B+樹中的資料都是按照主鍵進行排序的，如果我們想以別的列作為搜索條件，那么需要創建非聚簇索引，

例如，以c2列作為搜索條件，那么需要使用c2列創建一棵B+樹，如下所示：

這個B+樹與聚簇索引有幾處不同：

頁內的記錄是按照從c2列的大小順序排成一個單向鏈表 ，
頁和頁之間也是根據頁中記錄的c2列的大小順序排成一個雙向鏈表 ，
非葉子節點存盤的是記錄的c2列+頁號，
葉子節點存盤的并不是完整的用戶記錄，而只是c2列+主鍵這兩個列的值，

例如：根據c2列的值查找c2=4的記錄，查找程序如下：

根據根頁面44定位到頁42（因為2 ≤ 4 < 9）
由于c2列沒有唯一性約束，所以c2=4的記錄可能分布在多個資料頁中，又因為 2 ≤ 4 ≤ 4，所以確定實際存盤用戶記錄的頁在頁34和頁35中，
在頁34和35中定位到具體的記錄，
但是這個B+樹的葉子節點只存盤了c2和c1（主鍵）兩個列，所以我們必須再根據主鍵值去聚簇索引中再查找一遍完整的用戶記錄，

概念：回表

我們根據這個以c2列大小排序的B+樹只能確定我們要查找記錄的主鍵值，所以如果我們想根據c2列的值查找到完整的用戶記錄的話，仍然需要到聚簇索引中再查一遍，這個程序稱為回表，也就是根據c2列的值查詢一條完整的用戶記錄需要使用到 2棵B+樹！

問題：

為什么我們還需要一次回表操作呢？直接把完整的用戶記錄放到葉子節點不OK嗎？

回答：
如果把完整的用戶記錄放到葉子節點是可以不用回表，但是太占地方了，相當于每建立一棵B+樹都需要把所有的用戶記錄再都拷貝一遍，這就有點太浪費存盤空間了，

一張表可以有多個非聚簇索引：

3.5、聯合索引

為c2和c3列建立聯合索引：

各個頁中的記錄按照c2列進行排序，
在記錄的c2列相同的情況下，采用c3列進行排序
B+樹葉子節點處的記錄由c2列、c3列和主鍵c1列組成
本質上也是二級索引

3.6、覆寫索引☆

如果能通過讀取索引就可以得到想要的資料，那就不需要讀取用戶記錄，或者不用再做回表操作了，一個索引包含了滿足查詢結果的資料就叫做覆寫索引，

例如，如果為前面例子中的資料庫表創建c2列索引，則如下SQL會使用覆寫索引

SELECT c1, c2 from index_demo where c2 = 5;

3.7、MyISAM中的索引☆

MyISAM引擎使用 B+Tree 作為索引結構，葉子節點的data域存放的是資料記錄的地址 ，

下圖是MyISAM索引的原理圖（索引和資料分開存盤，是非聚簇索引）：

如果我們在Col2上建立一個二級索引，則此索引的結構如下圖所示（是非聚簇索引）：

3.8、MyISAM與InnoDB對比☆

InnoDB的資料檔案本身就是索引檔案，而MyISAM索引檔案和資料檔案是分離的：
- InnoDB的表在磁盤上存盤在以下檔案中： .ibd（表結構、索引和資料都存在一起,MySQL5.7表結構放在.frm中）
- MyISAM的表在磁盤上存盤在以下檔案中： *.sdi（描述表結構，MySQL5.7是.frm）、*.MYD（資料），*.MYI（索引）
InnoDB中主鍵索引是聚簇索引，葉子節點中存盤完整的資料記錄；其他索引是非聚簇索引，存盤相應記錄主鍵的值，
InnoDB要求表必須有主鍵（ MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以非空且唯一標識資料記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含欄位作為主鍵，
MyISAM中無論是主鍵索引還是非主鍵索引都是非聚簇的，葉子節點記錄的是資料的地址，
MyISAM的回表操作是十分快速的，因為是拿著地址偏移量直接到檔案中取資料的，反觀InnoDB是通過獲取主鍵之后再去聚簇索引里找記錄，雖然說也不慢，但還是比不上直接用地址去訪問，

4、索引操作

4.1、創建索引

隨表一起創建索引：

CREATE TABLE customer (
    
  id INT UNSIGNED AUTO_INCREMENT,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
    
  PRIMARY KEY(id), -- 主鍵索引：列設定為主鍵后會自動建立索引，唯一且不能為空，
  UNIQUE INDEX uk_no (customer_no), -- 唯一索引：索引列值必須唯一，允許有NULL值，且NULL可能會出現多次，
  KEY idx_name (customer_name), -- 普通索引：既不是主鍵，列值也不需要唯一，單純的為了提高查詢速度而創建，
  KEY idx_no_name (customer_no,customer_name) -- 復合索引：即一個索引包含多個列，
);

單獨建創索引：

CREATE TABLE customer1 (
  id INT UNSIGNED,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200)
);

-- 建表后創建索引
ALTER TABLE customer1 ADD PRIMARY KEY customer1(id); -- 主鍵索引
CREATE UNIQUE INDEX uk_no ON customer1(customer_no); -- 唯一索引
CREATE INDEX idx_name ON customer1(customer_name);  -- 普通索引
CREATE INDEX idx_no_name ON customer1(customer_no,customer_name); -- 復合索引

ALTER TABLE customer1 MODIFY id INT UNSIGNED AUTO_INCREMENT, ADD PRIMARY KEY customer1(id); --創建自增的主鍵索引

使用ALTER命令：

ALTER TABLE customer1 ADD PRIMARY KEY (id); -- 主鍵索引
ALTER TABLE customer1 ADD UNIQUE INDEX uk_no (customer_no); -- 唯一索引
ALTER TABLE customer1 ADD INDEX idx_name (customer_name);   -- 普通索引
ALTER TABLE customer1 ADD INDEX idx_no_name (customer_no,customer_name);  -- 復合索引

4.2、查看索引

SHOW INDEX FROM customer;

4.3、洗掉索引

DROP INDEX idx_name ON customer; -- 洗掉單值、唯一、復合索引

ALTER TABLE customer MODIFY id INT UNSIGNED, DROP PRIMARY KEY; -- 洗掉主鍵索引(有主鍵自增)
ALTER TABLE customer1 DROP PRIMARY KEY;  -- 洗掉主鍵索引(沒有主鍵自增)

5、索引的使用場景☆

哪些情況適合創建索引：

頻繁作為WHERE查詢條件的欄位
經常GROUP BY 和 ORDER BY的列
欄位的值有唯一性的限制
DISTINCT欄位需要創建索引
多表JOIN時，對連接欄位創建索引
使用字串前綴創建索引
- 例如一個欄位 address varchar（120），我們可以創建索引的長度為（12）個字符，節省索引空間
區分度高的列（重復的資料少）適合作為索引
使用頻繁的列，放到聯合索引的左側

哪些情況不要創建索引：

WHERE、GROUP BY 、ORDER BY里用不到的欄位不創建索引
表的資料記錄太少
有大量重復資料的列上
避免對經常增刪改的表創建索引
不要定義冗余或重復的索引

第05章索引優化

1、資料庫優化方案

問題：

哪些方法可以進行資料庫調優？

解決方案：

索引失效，沒有充分利用到索引：索引建立
關聯查詢太多JOIN（設計缺陷或不得已的需求）：SQL優化
資料過多：分庫分表
服務器調優及各個引數設定（緩沖、執行緒數等）：調整my.cnf

2、性能分析（EXPLAIN）

2.1、EXPLAIN是什么

查看SQL執行計劃：使用EXPLAIN關鍵字可以模擬優化器執行SQL查詢陳述句，從而知道MySQL是如何處理你的SQL陳述句的，分析你的查詢陳述句或是表結構的性能瓶頸，

用法：

EXPLAIN + SQL陳述句

2.2、資料準備

USE atguigudb;
 
CREATE TABLE t1(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id));
CREATE TABLE t2(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT, content1 VARCHAR(100) NULL, content2 VARCHAR(100) NULL, PRIMARY KEY (id));

CREATE INDEX idx_content1 ON t4(content1);  -- 創建普通索引

# 以下新增sql多執行幾次，以便演示
INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
INSERT INTO t4(content1, content2) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)), CONCAT('t4_',FLOOR(1+RAND()*1000)));

2.3、各欄位解釋

2.3.1、table

單表：顯示這一行的資料是關于哪張表的

EXPLAIN SELECT * FROM t1;

多表：關聯查詢中，顯示在執行計劃第一行的是驅動表，第二行是被驅動表

EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;

2.3.2、id

在一個完整的查詢陳述句中，每個SELECT關鍵字，都對應一個唯一的id，同時通過id也可以知道操作表的順序，

id相同：一個SELECT，id都是1

EXPLAIN SELECT * FROM t1, t2, t3;

id不同：三個SELECT，id是1、2、3

EXPLAIN SELECT t1.id FROM t1 WHERE t1.id =(
  SELECT t2.id FROM t2 WHERE t2.id =(
    SELECT t3.id FROM t3 WHERE t3.content = '000'
  )
);

注意：如果t3表查詢無結果，則table列t1和t2處為NULL

注意：查詢優化器可能對涉及子查詢的陳述句進行優化，轉為連接查詢

EXPLAIN SELECT * FROM t1 WHERE content IN (SELECT content FROM t2 WHERE content = 'a');

id為NULL：把t1和t2的查詢結果合并，并創建名為<union1,2>的臨時表，然后對結果去重

EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;

因此包含UNION ALL的執行計劃中就沒有這條記錄

EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2;

小結：

id如果相同，可以認為是一組，從上往下順序執行
在所有組中，id值越大，越先執行
關注點：每個id號碼，表示一趟獨立的查詢, 一個sql的查詢趟數越少越好

2.3.3、select_type

查詢的型別，主要是用于區別普通查詢、聯合查詢、子查詢等的復雜查詢，

SIMPLE：簡單查詢，查詢中不包含子查詢或者UNION，

EXPLAIN SELECT * FROM t1;

PRIMARY：主查詢，查詢中若包含子查詢，則最外層查詢被標記為PRIMARY，
SUBQUERY：子查詢，在SELECT或WHERE串列中包含了子查詢，

EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content= 'a');

DEPENDENT SUBQUREY：如果包含了子查詢，并且查詢陳述句不能被優化器轉換為連接查詢，并且子查詢是相關子查詢（子查詢基于外部資料列），則子查詢就是DEPENDENT SUBQUREY，

EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = t3.content);

UNCACHEABLE SUBQUREY：表示這個subquery的查詢要受到外部系統變數的影響

EXPLAIN SELECT * FROM t3 
WHERE id = ( SELECT id FROM t2 WHERE content = @@character_set_server);

UNION：對于包含UNION或者UNION ALL的查詢陳述句，除了最左邊的查詢是PRIMARY，其余的查詢都是UNION，
UNION RESULT：UNION會對查詢結果進行查詢去重，MYSQL會使用臨時表來完成UNION查詢的去重作業，針對這個臨時表的查詢就是"UNION RESULT"，

EXPLAIN 
SELECT * FROM t3 WHERE id = 1 
UNION  
SELECT * FROM t2 WHERE id = 1;

DEPENDENT UNION：子查詢中的UNION或者UNION ALL，除了最左邊的查詢是DEPENDENT SUBQUREY，其余的查詢都是DEPENDENT UNION，

 EXPLAIN SELECT * FROM t1 WHERE content IN
 (
 SELECT content FROM t2 
 UNION 
 SELECT content FROM t3
 );

DERIVED：在包含派生表（子查詢在from子句中）的查詢中，MySQL會遞回執行這些子查詢，把結果放在臨時表里，

EXPLAIN SELECT * FROM (
   SELECT content, COUNT(*) AS c FROM t1 GROUP BY content
) AS derived_t1 WHERE c > 1;

這里的<derived2>就是在id為2的查詢中產生的派生表，

補充：MySQL在處理帶有派生表的陳述句時，優先嘗試把派生表和外層查詢進行合并，如果不行，再把派生表物化掉，然后執行查詢，下面的例子就是就是將派生表和外層查詢進行合并的例子：

EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE content = 't1_832') AS derived_t1;

物化：執行子查詢，將結果放入臨時表的程序稱為物化，默認情況下會建立基于記憶體的物化表，并建立哈希索引，如果子查詢的結果非常大，超過了系統變數tmp_table_size的設定，會建立基于磁盤的物化表，并建立B+樹索引，（MySQl5.7及之后）

MATERIALIZED：優化器對于包含子查詢的陳述句，如果選擇將子查詢物化后再與外層查詢連接查詢，該子查詢的型別就是MATERIALIZED，如下的例子中，查詢優化器先將子查詢轉換成物化表，然后將t1和物化表進行連接查詢，

 EXPLAIN SELECT * FROM t1 WHERE content IN (SELECT content FROM t2);

2.3.4、partitions

代表磁區表中的命中情況，非磁區表，該項為NULL

2.3.5、type ☆

說明：

結果值從最好到最壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

比較重要的包含：system > const > eq_ref > ref > range > index > ALL

SQL 性能優化的目標：至少要達到 range 級別，要求是 ref 級別，最好是 const級別，（阿里巴巴
開發手冊要求）

ALL：全表掃描，Full Table Scan，將遍歷全表以找到匹配的行

EXPLAIN SELECT * FROM t1;

index：全索引掃描，當使用覆寫索引，但需要掃描全部的索引記錄時

EXPLAIN SELECT content1 FROM t4;

EXPLAIN SELECT id FROM t1;

range：只檢索給定范圍的行，使用一個索引來選擇行，key 列顯示使用了哪個索引，一般就是在你的where陳述句中出現了between、<、>、in等的查詢，這種范圍掃描索引掃描比全表掃描要好，因為它只需要開始于索引的某一點，而結束于另一點，不用掃描全部索引，

EXPLAIN SELECT * FROM t1 WHERE id > 2;

ref：通過普通二級索引列與常量進行等值匹配時

EXPLAIN SELECT * FROM t4 WHERE content1 = 'a';

eq_ref：連接查詢時通過主鍵或不允許NULL值的唯一二級索引列進行等值匹配時

EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;

const：根據主鍵或者唯一二級索引列與常數進行匹配時

EXPLAIN SELECT * FROM t1 WHERE id = 1;

system：MyISAM引擎中，當表中只有一條記錄時，（這是所有type的值中性能最高的場景）

CREATE TABLE t(i int) Engine=MyISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t;

2.3.6、possible_keys 和 key ☆

possible_keys表示執行查詢時可能用到的索引，一個或多個，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢實際使用，
keys表示實際使用的索引，如果為NULL，則沒有使用索引，

EXPLAIN SELECT id FROM t1 WHERE id = 1;

2.3.7、key_len ☆

表示索引使用的位元組數，根據這個值可以判斷索引的使用情況，檢查是否充分利用了索引，針對聯合索引值越大越好，

如何計算：

先看索引上欄位的型別+長度，比如：int=4 ; varchar(20) =20 ; char(20) =20
如果是varchar或者char這種字串欄位，視字符集要乘不同的值，比如utf8要乘 3(MySQL5.7)，如果是utf8mb4要乘4，GBK要乘2
varchar這種動態字串要加2個位元組
允許為空的欄位要加1個位元組

-- 創建索引
CREATE INDEX idx_age_name ON t_emp(age, `name`);
-- 測驗1
EXPLAIN SELECT * FROM t_emp WHERE age = 30 AND `name` = 'ab%';
-- 測驗2
EXPLAIN SELECT * FROM t_emp WHERE age = 30;

2.3.8、ref

顯示與key中的索引進行比較的列或常量，

const： 與索引列進行等值比較的東西是啥，const表示一個常數

EXPLAIN SELECT * FROM t4 WHERE content1 = 'a';

ref=atguigudb.t1.id 關聯查詢時出現，t2表和t1表的哪一列進行關聯

EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;

2.3.9、rows ☆

MySQL認為它執行查詢時實際從索引樹中查找到的行數，值越小越好，

-- 如果是全表掃描，rows的值就是表中資料的估計行數
EXPLAIN SELECT * FROM t_emp WHERE empno = '100001';

-- 如果是使用索引查詢，rows的值就是預計掃描索引記錄行數
EXPLAIN SELECT * FROM t_emp WHERE deptId = 1;

2.3.10、filtered

最后查詢出來的資料占所有服務器端（server）檢查行數（rows）的百分比，值越大越好，

例如上一個例子，

2.3.11、Extra ☆

包含不適合在其他列中顯示但十分重要的額外資訊，通過這些額外資訊來理解MySQL到底將如何執行當前的查詢陳述句，MySQL提供的額外資訊有好幾十個，這里只挑比較重要的介紹，

Impossible WHERE：where子句的值總是false

EXPLAIN SELECT * FROM t_emp WHERE 1 != 1;

Using where：使用了where，但在where上有欄位沒有創建索引，也可以理解為如果資料從引擎層被回傳到server層進行過濾，那么就是Using where，

EXPLAIN SELECT * FROM t_emp WHERE `name` = '風清揚';

Using filesort：

在對查詢結果中的記錄進行排序時，是可以使用索引的，如下所示：

EXPLAIN SELECT * FROM t1 ORDER BY id;

如果排序操作無法使用到索引，只能在記憶體中（記錄較少時）或者磁盤中（記錄較多時）進行排序（filesort），如下所示：

EXPLAIN SELECT * FROM t1 ORDER BY content;

Using index：使用了覆寫索引，表示直接訪問索引就足夠獲取到所需要的資料，不需要通過索引回表

 EXPLAIN SELECT id, content1 FROM t4;

Using index condition：叫作 Index Condition Pushdown Optimization （索引下推優化）
- 如果沒有索引下推（ICP），那么MySQL在存盤引擎層找到滿足content1 > 'z'條件的第一條二級索引記錄，主鍵值進行回表，回傳完整的記錄給server層，server層再判斷其他的搜索條件是否成立，如果成立則保留該記錄，否則跳過該記錄，然后向存盤引擎層要下一條記錄，
- 如果使用了索引下推（ICP），那么MySQL在存盤引擎層找到滿足content1 > 'z'條件的第一條二級索引記錄，不著急執行回表，而是在這條記錄上先判斷一下所有關于idx_content1索引中包含的條件是否成立，也就是content1 > 'z' AND content1 LIKE '%a'是否成立，如果這些條件不成立，則直接跳過該二級索引記錄，去找下一條二級索引記錄；如果這些條件成立，則執行回表操作，回傳完整的記錄給server層，

-- content1列上有索引idx_content1
EXPLAIN SELECT * FROM t4 WHERE content1 > 'z' AND content1 LIKE '%a';

注意：如果這里的查詢條件只有content1 > 'z'，那么找到滿足條件的索引后也會進行一次索引下推的操作，判斷content1 > 'z'是否成立（這是原始碼中為了編程方便做的冗余判斷）

Using join buffer：在連接查詢時，當被驅動表（t2）不能有效的利用索引時，MySQL會提前申請一塊記憶體空間（join buffer）存盤驅動表的資料，來加快查詢速度

EXPLAIN  SELECT * FROM t1, t2 WHERE t1.content = t2.content;

下面這個例子就是被驅動表使用了索引，此時Extra中就沒有Using join buffer了：

EXPLAIN SELECT * FROM t_emp, t_dept WHERE t_dept.id = t_emp.deptId;

課外閱讀：在沒有索引的情況下，為了優化多表連接，減少磁盤IO讀取次數和資料遍歷次數，MySQL為我們提供了很多不同的連接快取的優化演算法，可參考https://blog.csdn.net/qq_35423190/article/details/120504960

Using join buffer (hash join)8.0新增：連接快取（hash連接） 速度更快

Using join buffer (Block Nested Loop)從5.7開始：連接快取（塊嵌套回圈）

3、準備資料

在做優化之前，要準備大量資料，接下來創建兩張表，并往員工表里插入50W資料，部門表中插入1W條資料，

怎么快速插入50w條資料呢？ 存盤程序

怎么保證插入的資料不重復？函式

部門表：

id：自增長
deptName：隨機字串，允許重復
address：隨機字串，允許重復
CEO：1-50w之間的任意數字

員工表：

id：自增長
empno：可以使用亂數字，或者從1開始的自增數字，不允許重復
name：隨機生成，允許姓名重復
age：區間亂數
deptId：1-1w之間亂數

總結：需要產生隨機字串和區間亂數的函式，

3.1、創建表

CREATE TABLE `dept` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`deptName` VARCHAR(30) DEFAULT NULL,
	`address` VARCHAR(40) DEFAULT NULL,
	ceo INT NULL ,
	PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

CREATE TABLE `emp` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`empno` INT NOT NULL ,
	`name` VARCHAR(20) DEFAULT NULL,
	`age` INT(3) DEFAULT NULL,
	`deptId` INT(11) DEFAULT NULL,
	PRIMARY KEY (`id`)
	#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

3.2、創建函式

-- 查看mysql是否允許創建函式：
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
-- 命令開啟：允許創建函式設定：（global-所有session都生效）
SET GLOBAL log_bin_trust_function_creators=1;

-- 隨機產生字串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	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 $$

-- 假如要洗掉
-- drop function rand_string;

-- 用于隨機產生區間數字
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(from_num +RAND()*(to_num -from_num+1));
RETURN i;  
END$$

-- 假如要洗掉
-- drop function rand_num;

3.3、創建存盤程序

-- 插入員工資料
DELIMITER $$
CREATE PROCEDURE  insert_emp(START INT, max_num INT)
BEGIN  
	DECLARE i INT DEFAULT 0;   
	#set autocommit =0 把autocommit設定成0  
	SET autocommit = 0;    
	REPEAT  
		SET i = i + 1;  
		INSERT INTO emp (empno, NAME, age, deptid ) VALUES ((START+i) ,rand_string(6), rand_num(30,50), rand_num(1,10000));  
		UNTIL i = max_num  
	END REPEAT;  
	COMMIT;  
END$$
 
-- 洗掉
-- DELIMITER ;
-- drop PROCEDURE insert_emp;

-- 插入部門資料
DELIMITER $$
CREATE PROCEDURE insert_dept(max_num INT)
BEGIN  
	DECLARE i INT DEFAULT 0;   
	SET autocommit = 0;    
	REPEAT  
		SET i = i + 1;  
		INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
		UNTIL i = max_num  
	END REPEAT;  
	COMMIT;  
END$$
 
-- 洗掉
-- DELIMITER ;
-- drop PROCEDURE insert_dept;

3.4、呼叫存盤程序

-- 執行存盤程序，往dept表添加1萬條資料
CALL insert_dept(10000); 

-- 執行存盤程序，往emp表添加50萬條資料，編號從100000開始
CALL insert_emp(100000,500000);

3.5、批量洗掉表索引

-- 批量洗掉某個表上的所有索引
DELIMITER $$
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE ct INT DEFAULT 0;
	DECLARE _index VARCHAR(200) DEFAULT '';
	DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY'  ;
	DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;      
	OPEN _cur;
		FETCH _cur INTO _index;
		WHILE  _index<>'' DO 
			SET @str = CONCAT("drop index ",_index," on ",tablename ); 
			PREPARE sql_str FROM @str ;
			EXECUTE sql_str;
			DEALLOCATE PREPARE sql_str;
			SET _index=''; 
			FETCH _cur INTO _index; 
		END WHILE;
	CLOSE _cur;
END$$

-- 執行批量洗掉：dbname 庫名稱, tablename 表名稱
CALL proc_drop_index("dbname","tablename");

3.6、開啟SQL執行時間的顯示

為了方便后面的測驗中隨時查看SQL運行的時間，測驗索引優化后的效果，我們開啟profiling

-- 顯示sql陳述句執行時間
SET profiling = 1;
SHOW VARIABLES  LIKE '%profiling%';
SHOW PROFILES;

4、單表索引失效案例

MySQL中提高性能的一個最有效的方式是對資料表設計合理的索引，索引提供了高效訪問資料的方法，并且加快查詢的速度，因此索引對查詢的速度有著至關重要的影響，

我們創建索引后，用不用索引，最終是優化器說了算，優化器會基于開銷選擇索引，怎么開銷小就怎么來，不是基于規則，也不是基于語意，

另外SQL陳述句是否使用索引，和資料庫的版本、資料量、資料選擇度（查詢中選擇的列數）運行環境都有關系，

-- 創建索引
CREATE INDEX idx_name ON emp(`name`);

4.1、計算、函式導致索引失效

-- 顯示查詢分析
EXPLAIN SELECT * FROM emp WHERE emp.name  LIKE 'abc%';
EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; --索引失效

4.2、LIKE以%開頭索引失效

EXPLAIN SELECT * FROM emp WHERE name LIKE '%ab%'; --索引失效

拓展：Alibaba《Java開發手冊》

【強制】頁面搜索嚴禁左模糊或者全模糊，如果需要請走搜索引擎來解決，

4.3、不等于(!= 或者<>)索引失效

EXPLAIN SELECT * FROM emp WHERE emp.name = 'abc' ;
EXPLAIN SELECT * FROM emp WHERE emp.name <> 'abc' ; --索引失效

4.4、IS NOT NULL 和 IS NULL

EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;
EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效

注意：當資料庫中的資料的索引列的NULL值達到比較高的比例的時候，即使在IS NOT NULL 的情況下 MySQL的查詢優化器會選擇使用索引，此時type的值是range（范圍查詢）

-- 將 id>20000 的資料的 name 值改為 NULL
UPDATE emp SET `name` = NULL WHERE `id` > 20000;

-- 執行查詢分析，可以發現 IS NOT NULL 使用了索引
-- 具體多少條記錄的值為NULL可以使索引在IS NOT NULL的情況下生效，由查詢優化器的演算法決定
EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL;

測驗完將name的值改回來

UPDATE emp SET `name` = rand_string(6) WHERE `id` > 20000;

4.5、型別轉換導致索引失效

EXPLAIN SELECT * FROM emp WHERE name='123'; 
EXPLAIN SELECT * FROM emp WHERE name= 123; --索引失效

4.6、全值匹配我最愛

準備：

-- 首先洗掉之前創建的索引
CALL proc_drop_index("atguigudb","emp");

問題：為以下查詢陳述句創建哪種索引效率最高

-- 查詢分析
EXPLAIN SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
-- 執行SQL
SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
-- 查看執行時間
SHOW PROFILES;

創建索引并重新執行以上測驗：

-- 創建索引：分別創建以下三種索引的一種，并分別進行以上查詢分析
CREATE INDEX idx_age ON emp(age);
CREATE INDEX idx_age_deptid ON emp(age,deptid);
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

結論：可以發現最高效的查詢應用了聯合索引 idx_age_deptid_name

4.7、最佳左前綴法則

準備：

-- 首先洗掉之前創建的索引
CALL proc_drop_index("atguigudb","emp");
-- 創建索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

問題：以下這些SQL陳述句能否命中 idx_age_deptid_name 索引，可以匹配多少個索引欄位

測驗：

如果索引了多列，要遵守最左前綴法則，即查詢從索引的最左前列開始并且不跳過索引中的列，
過濾條件要使用索引，必須按照索引建立時的順序，依次滿足，一旦跳過某個欄位，索引后面的欄位都無法被使用，

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' ;
-- EXPLAIN結果：
-- key_len：5 只使用了age索引
-- 索引查找的順序為 age、deptid、name，查詢條件中不包含deptid，無法使用deptid和name索引

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd';
-- EXPLAIN結果：
-- type： ALL， 執行了全表掃描
-- key_len： NULL， 索引失效
-- 索引查找的順序為 age、deptid、name，查詢條件中不包含age，無法使用整個索引

EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND emp.deptid=1 AND emp.name = 'abcd';
-- EXPLAIN結果：
-- 索引查找的順序為 age、deptid、name，匹配所有索引欄位

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd' AND emp.age = 30;
-- EXPLAIN結果：
-- 索引查找的順序為 age、deptid、name，匹配所有索引欄位

4.8、索引中范圍條件右邊的列失效

準備：

-- 首先洗掉之前創建的索引
CALL proc_drop_index("atguigudb","emp");

問題：為以下查詢陳述句創建哪種索引效率最高

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.deptId>1000 AND emp.name = 'abc';

測驗1：

-- 創建索引并執行以上SQL陳述句的EXPLAIN
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);
-- key_len：10， 只是用了 age 和 deptid索引，name失效

注意：當我們修改deptId的范圍條件的時候，例如deptId>100，那么整個索引失效，MySQL的優化器基于成本計算后認為沒必要使用索引了，所以就進行了全表掃描，（注意：因為表中的資料是隨機生成的，因此實際測驗中根據具體資料的不同測驗的結果也會不一樣，最終是否使用索引由優化器決定）

測驗2：

-- 創建索引并執行以上SQL陳述句的EXPLAIN（將deptid索引的放在最后）
CREATE INDEX idx_age_name_deptid ON emp(age,`name`,deptid);
-- 使用了完整的索引

補充：以上兩個索引都存在的時候，MySQL優化器會自動選擇最好的方案

5、關聯查詢優化

5.1、資料準備

創建兩張表，并分插入16條和20條資料：

-- 分類
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`)
);
 
-- 插入16條記錄
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)));
 
-- 插入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)));

5.2、左外連接

沒有創建索引前的測驗：進行了全表掃描，查詢次數為16*20

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
-- 左表class：驅動表、右表book：被驅動表

測驗1：在驅動表上創建索引：進行了全索引掃描，查詢次數是16*20

-- 創建索引
CREATE INDEX idx_class_card ON class(card);

測驗2：在被驅動表上創建索引：可以避免全表掃描，查詢次數是16*1

-- 首先洗掉之前創建的索引
CALL proc_drop_index("atguigudb","class");
-- 創建索引
CREATE INDEX idx_book_card ON book(card);

測驗3：同時給兩張表添加索引：充分利用了索引，查詢次數是16*1

-- 已經有了book索引
CREATE INDEX idx_class_card ON class(card);

結論：

針對兩張表的連接條件涉及的列，索引要創建在被驅動表上，驅動表盡量是小表

如果驅動表上沒有where過濾條件

當驅動表的連接條件沒有索引時，驅動表是全表掃描

當針對驅動表的連接條件建立索引時，驅動表依然要進行全索引掃描

因此，此時建立在驅動表上的連接條件上的索引是沒有太大意義的

如果驅動表上有where過濾條件，那么針對過濾條件創建的索引是有必要的

5.3、內連接

測驗：將前面外連接中的LEFT JOIN 變成 INNER JOIN

-- 換成inner join
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card=book.card;
-- 交換class和book的位置
EXPLAIN SELECT * FROM book INNER JOIN class ON class.card=book.card;

都有索引的情況下：查詢優化器自動選擇資料量小的表做為驅動表

class表有索引的情況下：book表是驅動表

book表有索引的情況下：class表是驅動表

都沒有索引的情況下：選擇資料量小的表做為驅動表

結論：發現即使交換表的位置，MySQL優化器也會自動選擇驅動表，自動選擇驅動表的原則是：索引創建在被驅動表上，驅動表是小表，

5.4、擴展掌門人的練習

-- 首先洗掉之前創建的索引
CALL proc_drop_index("atguigudb","emp");
CALL proc_drop_index("atguigudb","dept");

1.三表左連接方式:

-- 員工表(t_emp)、部門表(t_dept)、ceo(t_emp)表 關聯查詢
EXPLAIN SELECT emp.name, ceo.name AS ceoname 
FROM emp
LEFT JOIN dept ON emp.deptid = dept.id 
LEFT JOIN emp ceo ON dept.ceo = ceo.id;

一趟查詢，用到了主鍵索引，效果最佳

2.子查詢方式：

EXPLAIN SELECT emp.name,
(SELECT emp.name FROM emp WHERE emp.id = dept.ceo) AS ceoname
FROM emp 
LEFT JOIN dept ON emp.deptid = dept.id;

兩趟查詢，用到了主鍵索引，跟第一種比，效果稍微差點，

3.臨時表連接方式

EXPLAIN SELECT emp_with_ceo_id.name, emp.name AS ceoname 
FROM 
( 
SELECT emp.name, dept.ceo 
FROM emp 
LEFT JOIN dept ON emp.deptid = dept.id 
) emp_with_ceo_id
LEFT JOIN emp ON emp_with_ceo_id.ceo = emp.id;

查詢一趟，MySQL查詢優化器將衍生表查詢轉換成了連接表查詢，速度堪比第一種方式

MySQL5.5查詢結果：兩趟查詢，先查詢a,b產生衍生表ab,衍生表作為驅動表，c作為被驅動表，使用到c表主鍵，效果比后面一種要好一點，

4、臨時表連接方式2

EXPLAIN SELECT emp.name, ceo.ceoname FROM emp LEFT JOIN
( 
SELECT emp.deptId AS deptId, emp.name AS ceoname 
FROM emp 
INNER JOIN dept ON emp.id = dept.ceo 
) ceo
ON emp.deptId = ceo.deptId;

查詢一趟，MySQL查詢優化器將衍生表查詢轉換成了連接表查詢，但是只有一個表使用了索引，資料檢索的次數稍多，性能最差，

MySQL5.5查詢結果：兩趟查詢，先查詢b, a產生衍生表ab,衍生表作為被驅動表，衍生表無法建立索引，也就無法優化; 所以，這種陳述句是性能最差的，

5.5、總結

保證被驅動表的JOIN欄位已經創建了索引
需要JOIN 的欄位，資料型別保持絕對一致，
LEFT JOIN 時，選擇小表作為驅動表，大表作為被驅動表，減少外層回圈的次數，
INNER JOIN 時，MySQL會自動將小結果集的表選為驅動表，選擇相信MySQL優化策略，
能夠直接多表關聯的盡量直接關聯，不用子查詢，(減少查詢的趟數)
衍生表建不了索引（MySQL5.5）

6、子查詢優化

查詢非掌門人

-- 不推薦
-- 查詢員工，這些員工的id沒在（掌門人id串列中）
-- 【查詢不是CEO的員工】
SELECT * FROM t_emp emp WHERE emp.id NOT IN 
(SELECT dept.ceo FROM t_dept dept WHERE dept.ceo IS NOT NULL);

注意：使用大表（emp、dept表）測驗更加直觀

-- 推薦
-- 按照集合查詢
SELECT emp.* FROM t_emp emp 
LEFT JOIN t_dept dept ON emp.id = dept.ceo WHERE dept.id IS NULL;

也可以為ceo添加一個索引欄位

結論：盡量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx = xx WHERE xx IS NULL替代

7、排序優化

7.1、索引失效的情況

以下三種情況不走索引：

無過濾，不索引

順序錯，不索引

方向反，不索引

準備：

-- 洗掉現有索引
CALL proc_drop_index("atguigudb","emp");
-- 創建索引
CREATE INDEX idx_age_deptid_name ON emp (age,deptid,`name`);

無過濾，不索引：

-- 沒有使用索引：
EXPLAIN SELECT * FROM emp ORDER BY age,deptid;

-- 使用了索引：order by想使用索引，必須有過濾條件，索引才能生效，limit也可以看作是過濾條件
EXPLAIN SELECT * FROM emp ORDER BY age,deptid LIMIT 10;

順序錯，不索引：

-- 排序使用了索引：
-- 注意：key_len = 5是where陳述句使用age索引的標記，order by陳述句使用索引不在key_len中體現，
--      order by陳述句如果沒有使用索引，在extra中會出現using filesort，                  
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid;

-- 排序使用了索引：
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, `name`; 

-- 排序沒有使用索引：因為索引列中不存在empno
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, empno;

-- 排序沒有使用索引：order by 后的排序條件的順序，與索引順序不一致
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY `name`, deptid;

-- 排序沒有使用索引：出現的順序要和復合索引中的列的順序一致！
EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;

方向反，不索引：

-- 排序使用了索引：排序條件和索引一致，并方向相同，可以使用索引
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid DESC, `name` DESC;

-- 沒有使用索引：兩個排序條件方向相反
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid ASC, `name` DESC;

7.2、索引優化案例

排序優化的目的是，去掉 Extra 中的 using filesort（手工排序）

準備：

-- 洗掉現有索引
CALL proc_drop_index("atguigudb","emp");
-- 這個例子結合 show profiles; 查看運行時間
SET profiling = 1;

需求：查詢年齡為30歲的，且員工編號小于101000的用戶，按用戶名稱排序

測驗1：很顯然，type 是 ALL，即最壞的情況，Extra 里還出現了 Using filesort,也是最壞的情況，優化是必須的，

EXPLAIN SELECT * FROM emp WHERE age =30 AND empno <101000 ORDER BY `name`;
-- 然后查看一下SQL的執行時間

性能測驗：

優化思路： 盡量讓where的過濾條件和排序使用上索引

step1： 我們建一個三個欄位的組合索引可否？

CREATE INDEX idx_age_empno_name ON emp (age,empno,`name`);

最后的name索引沒有用到，出現了Using filesort，原因是，因為empno是一個范圍過濾，所以索引后面的欄位不會再使用索引了，所以我們建一個3值索引是沒有意義的

性能測驗：

step2：那么我們先刪掉這個索引：

DROP INDEX idx_age_empno_name ON emp;

為了去掉filesort我們可以把索引建成，也就是說empno 和name這個兩個欄位我只能二選其一，

CREATE INDEX idx_age_name ON emp(age,`name`);

這樣我們優化掉了 using filesort，但是經過測驗，性能反而下降

性能測驗：

step3：如果我們選擇那個范圍過濾，而放棄排序上的索引呢?

DROP INDEX idx_age_name ON emp;
CREATE INDEX idx_age_empno ON emp(age,empno);

執行原始的sql陳述句，查看性能，結果竟然有 filesort的 sql 運行速度，超過了已經優化掉 filesort的 sql，而且快了好多倍，何故？

性能測驗：

原因：所有的排序都是在條件過濾之后才執行的，所以，如果條件過濾掉大部分資料的話，剩下幾百幾千條資料進行排序其實并不是很消耗性能，即使索引優化了排序，但實際提升性能很有限，相對的 empno<101000 這個條件，如果沒有用到索引的話，要對幾萬條的資料進行掃描，這是非常消耗性能的，所以索引放在這個欄位上性價比最高，是最優選擇，

結論：當【范圍條件】和【group by 或者 order by】的欄位出現二選一時，優先觀察條件欄位的過濾數量，如果過濾的資料足夠多，而需要排序的資料并不多時，優先把索引放在范圍欄位上，反之，亦然，

也可以將選擇權交給MySQL：索引同時存在，mysql自動選擇最優的方案：（對于這個例子，mysql選擇idx_age_empno），但是，隨著資料量的變化，選擇的索引也會隨之變化的，

7.3、雙路排序和單路排序

如果排序沒有使用索引，引起了filesort（手工排序），那么filesort有兩種演算法

雙路排序
單路排序

雙路排序（慢）

MySQL 4.1之前是使用雙路排序，字面意思就是兩次掃描磁盤，最終得到資料，

首先，根據行指標從磁盤取排序欄位，在buffer進行排序，
再按照排序欄位的順序從磁盤取其他欄位，

取一批資料，要對磁盤進行兩次掃描，眾所周知，IO是很耗時的，所以在mysql4.1之后，出現了第二種改進的演算法，就是單路排序，

單路排序（快）

從磁盤讀取查詢需要的所有欄位，按照order by列在buffer對它們進行排序，
然后掃描排序后的串列進行輸出，

它的效率更快一些，因為只讀取一次磁盤，避免了第二次讀取資料，并且把隨機IO變成了順序IO，但是它會使用更多的空間，因為它把每一行都保存在記憶體中了，

結論及引申出的問題

單路比多路要多占用更多記憶體空間
因為單路是把所有欄位都取出，所以有可能取出的資料的總大小超出了sort_buffer_size的容量，導致每次只能取sort_buffer_size容量大小的資料，進行排序（創建tmp檔案，多路合并），排完再取sort_buffer容量大小，再排……從而多次I/O，
單路本來想省一次I/O操作，反而導致了大量的I/O操作，反而得不償失，

優化策略

減少select 后面的查詢的欄位：Order by時select * 是一個大忌，查詢欄位過多會占用sort_buffer_size的容量，
增大sort_buffer_size引數的設定：當然，要根據系統的能力去提高，因為這個引數是針對每個行程（connection）的 1M-8M之間調整， MySQL8.0，InnoDB存盤引擎默認值是1048576位元組，1MB，

SHOW VARIABLES LIKE '%sort_buffer_size%'; --默認1MB

增大max_length_for_sort_data引數的設定：MySQL根據max_length_for_sort_data變數來確定使用哪種演算法，默認值是4096位元組，如果需要回傳的列的總長度大于max_length_for_sort_data，使用雙路排序演算法，否則使用單路排序演算法，但是如果設的太高，資料總容量超出sort_buffer_size的概率就增大，明顯癥狀是高的磁盤I/O活動和低的處理器使用率，1024-8192之間調整，

SHOW VARIABLES LIKE '%max_length_for_sort_data%'; --默認4K

舉例：

1、如果資料總量很小（單路一次就可以讀取所有資料），單條記錄大小很大（大于4K，默認會使用雙路排序），此時，可以增加max_length_for_sort_data的值，增加sort_buffer_size的值，讓服務器默認使用單路排序，

2、如果資料總量很大（單路很多次IO才可以），單條記錄大小很小（小于4K，默認會使用單路排序），此時，可以減小max_length_for_sort_data的值，讓服務器默認使用雙路排序，

8、分組優化

group by 使用索引的原則幾乎跟order by一致，但是group by 即使沒有過濾條件用到索引，也可以直接使用索引（Order By 必須有過濾條件才能使用上索引）
包含了order by、group by、distinct這些查詢的陳述句，where條件過濾出來的結果集請保持在1000行以內，否則SQL會很慢，

9、覆寫索引優化

總結

禁止使用select *，禁止查詢與業務無關欄位

盡量利用覆寫索引

第06章慢查詢日志

1、是什么

一種日志記錄，查看哪些SQL超出了我們的最大忍耐時間值，

2、開啟慢查詢日志引數

2.1、開啟slow_query_log

默認情況下，MySQL資料庫沒有開啟慢查詢日志，需要我們手動來設定這個引數，當然，如果不是調優需要的話，一般不建議啟動該引數，因為開啟慢查詢日志會或多或少帶來一定的性能影響，

SET GLOBAL slow_query_log=1;

然后我們再來查看下慢查詢日志是否開啟，以及慢查詢日志檔案的位置：

SHOW VARIABLES LIKE '%slow_query_log%';

2.2、修改long_query_time閾值

SHOW VARIABLES LIKE '%long_query_time%'; -- 查看值：默認10秒
SET GLOBAL long_query_time=0.1; -- 設定一個比較短的時間，便于測驗

注意：

需要重新登錄客戶端使上面的設定生效
假如運行時間正好等于long_query_time的情況，并不會被記錄下來，
也就是說，在mysql原始碼里是判斷大于long_query_time，而非大于等于，

2.3、日志分析工具

執行耗時sql：

SELECT * from emp;
SELECT * FROM emp;
SELECT * FROM emp WHERE deptid > 1;

查詢慢查詢記錄數：

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

查詢日志：

#退出mysql容器
quit
#查看慢查詢日志
cat -n /var/lib/mysql/d7b5e8f7503e-slow.log

mysqldumpslow：

在生產環境中，如果要手工分析日志，查找、分析SQL，顯然是個體力活，MySQL提供了日志分析工具mysqldumpslow，

注意：默認情況下，傳統rpm方式安裝的MySQL環境自帶mysqldumpslow工具，直接使用即可，docker下安裝的MySQL環境沒有mysqldumpslow工具，

退出mysql命令列，執行以下命令：

-- 查看mysqldumpslow的幫助資訊
mysqldumpslow --help

-- 作業常用參考
-- 1.得到回傳記錄集最多的10個SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
-- 2.得到訪問次數最多的10個SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
-- 3.得到按照時間排序的前10條里面含有左連接的查詢陳述句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
-- 4.另外建議在使用這些命令時結合 | 和more 使用 ，否則陳述句過多有可能出現爆屏情況
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

-a: 不將數字抽象成N，字串抽象成S
-s: 是表示按照何種方式排序；
- c: sql陳述句的訪問次數
- l: 鎖定時間
- r: 回傳資料記錄集的總數量
- t: 查詢時間
- al:平均鎖定時間
- ar:平均回傳記錄數
- at:平均查詢時間
-t: 即為回傳前面多少條的資料；
-g: 后邊搭配一個正則匹配模式，大小寫不敏感的；

第07章 View視圖

1、是什么

將一段查詢sql封裝為一個虛擬的表，
這個虛擬表只保存了sql邏輯，不會保存任何查詢結果，

2、作用

封裝復雜sql陳述句，提高復用性
邏輯放在資料庫上面，更新不需要發布程式，面對頻繁的需求變更更靈活

3、適用場景

共用查詢結果
報表

4、語法

4.1、創建

-- 語法
CREATE VIEW view_name 
AS SELECT column_name(s) FROM table_name WHERE condition;

-- 例如：求所有人物對應的掌門名稱
CREATE VIEW v_ceo AS
SELECT emp.name, ceo.name AS ceoname 
FROM t_emp emp
LEFT JOIN t_dept dept ON emp.deptid = dept.id 
LEFT JOIN t_emp ceo ON dept.ceo = ceo.id;

4.2、使用

查詢

-- 語法
SELECT * FROM view_name; 

-- 例如：
SELECT * FROM v_ceo;

更新

-- 語法
CREATE OR REPLACE VIEW view_name 
AS SELECT column_name(s) FROM table_name WHERE condition

-- 建議直接洗掉重新創建

洗掉

DROP VIEW view_name;

-- 例如：
DROP VIEW v_ceo;

本文來自博客園，作者：自律即自由-，轉載請注明原文鏈接：https://www.cnblogs.com/deyo/p/17504678.html

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/556072.html

標籤：MySQL

上一篇：教程 | Datavines 自定義資料質量檢查規則（Metric）

下一篇：返回列表

一招帶你吃透MySQL高級

第01章 Linux下MySQL的安裝與使用

1、安裝

1.1、docker安裝

1.2、查看是否啟動

1.3、測驗遠程鏈接

2、字符集

2.1、默認字符集

2.2、utf8與utf8mb4

3、 SQL大小寫規范

3.1、Windows和Linux的區別

3.2、Linux下大小寫規則設定（了解）

4、sql_mode

4.1、寬松模式 vs 嚴格模式

4.2、查看和設定sql_mode

4.3、錯誤開發演示

4.4、sql_mode常用值（了解）

第02章 邏輯架構

1、邏輯架構剖析

1.1、服務器處理客戶端請求

1.2、Connectors（客戶端）

1.3、MySQL Server（服務器）

第1層：連接層

第2層：服務層

第3層：引擎層

1.4、存盤層

1.5、查詢流程說明

2、SQL執行流程

2.1、MySQL8

2.2、MySQL5.7（了解）

3、存盤引擎

3.1、查看存盤引擎

3.2、設定存盤引擎（了解）

3.3、各種引擎介紹

3.4、MyISAM和InnoDB的區別

第03章 SQL預熱

1、創建測驗資料

2、常見七種JOIN查詢

3、擴展掌門人

3.1、增加掌門人欄位

3.2、練習

第04章 MySQL索引

1、索引簡介

1.1、什么是索引☆

1.2、索引的優缺點☆

1.3、索引分類☆

2、樹

2.1、二叉樹

2.2、B樹

3、MySQL的索引結構：B+tree

3.1、InnoDB中的索引

3.1.1、設計索引

3.1.2、InnoDB中的索引方案

3.2、B樹和B+樹對比☆

3.3、聚簇索引☆

3.4、非聚簇索引☆

3.5、聯合索引

3.6、覆寫索引☆

3.7、MyISAM中的索引☆

3.8、MyISAM與InnoDB對比☆

4、索引操作

4.1、創建索引

4.2、查看索引

4.3、洗掉索引

5、索引的使用場景☆

第05章 索引優化

1、資料庫優化方案

2、性能分析（EXPLAIN）

2.1、EXPLAIN是什么

2.2、資料準備

2.3、各欄位解釋

2.3.1、table

2.3.2、id

2.3.3、select_type

2.3.4、partitions

2.3.5、type ☆

2.3.6、possible_keys 和 key ☆

2.3.7、key_len ☆

2.3.8、ref

2.3.9、rows ☆

第02章邏輯架構

第05章索引優化

第06章慢查詢日志