第1章 存儲過程

1.1 什么是存儲過程？

存儲過程可稱為過程化SQL語言，是在普通SQL語句的基礎上增加了編程語言的特點，把數據操作語句(DML)和查詢語句(DQL)組織在過程化代碼中，通過邏輯判斷、循環等操作實現復雜計算的程序語言。
換句話說，存儲過程其實就是數據庫內置的一種編程語言，這種編程語言也有自己的變量、if語句、循環語句等。在一個存儲過程中可以將多條SQL語句以邏輯代碼的方式將其串聯起來，執行這個存儲過程就是將這些SQL語句按照一定的邏輯去執行，所以一個存儲過程也可以看做是一組為了完成特定功能的SQL 語句集。
每一個存儲過程都是一個數據庫對象，就像table和view一樣，存儲在數據庫當中，一次編譯永久有效。并且每一個存儲過程都有自己的名字。客戶端程序通過存儲過程的名字來調用存儲過程。
在數據量特別龐大的情況下利用存儲過程能達到倍速的效率提升。

1.2 存儲過程的優點和缺點？

優點：速度快。

  - 降低了**應用服務器**和**數據庫服務器**之間網絡通訊的開銷。尤其在數據量龐大的情況下效果顯著。

缺點：移植性差。編寫難度大。維護性差。

  - 每一個數據庫都有自己的存儲過程的語法規則，這種語法規則不是通用的。一旦使用了存儲過程，則數據庫產品很難更換，例如：編寫了mysql的存儲過程，這段代碼只能在mysql中運行，無法在oracle數據庫中運行。- 對于數據庫存儲過程這種語法來說，沒有專業的IDE工具（集成開發環境），所以編碼速度較低。自然維護的成本也會較高。

在實際開發中，存儲過程還是很少使用的。只有在系統遇到了性能瓶頸，在進行優化的時候，對于大數量的應用來說，可以考慮使用一些。

1.3 第一個存儲過程

存儲過程的創建

create procedure p1()
beginselect empno,ename from emp;
end;

存儲過程的調用

call p1();

存儲過程的查看

查看創建存儲過程的語句：

show create procedure p1;

通過系統表information_schema.ROUTINES查看存儲過程的詳細信息：
information_schema.ROUTINES 是 MySQL 數據庫中一個系統表，存儲了所有存儲過程、函數、觸發器的詳細信息，包括名稱、返回值類型、參數、創建時間、修改時間等。

select * from information_schema.routines where routine_name = 'p1';

information_schema.ROUTINES 表中的一些重要的列包括：

• SPECIFIC_NAME：存儲過程的具體名稱，包括該存儲過程的名字，參數列表。
• ROUTINE_SCHEMA：存儲過程所在的數據庫名稱。
• ROUTINE_NAME：存儲過程的名稱。
• ROUTINE_TYPE：PROCEDURE表示是一個存儲過程，FUNCTION表示是一個函數。
• ROUTINE_DEFINITION：存儲過程的定義語句。
• CREATED：存儲過程的創建時間。
• LAST_ALTERED：存儲過程的最后修改時間。
• DATA_TYPE：存儲過程的返回值類型、參數類型等。

存儲過程的刪除

drop procedure if exists p1;

delimiter命令

在 MySQL 中，delimiter 命令用于改變 MySQL 解釋語句的定界符。MySQL 默認使用分號 ; 作為語句的定界符。而使用 delimiter 命令可以將分號 ; 更改為其他字符，從而可以在 SQL 語句中使用分號 ;。

例如，假設需要創建一個存儲過程。在存儲過程中通常會包括多條 SQL 語句，而這些語句都需要以分號 ; 結尾。但默認情況下，執行到第一條語句的分號 ; 后，MySQL 就會停止解釋，導致后續的語句無法執行。解決方式就是使用 delimiter 命令將分號改為其他字符，使分號 ; 不再是語句定界符。例如：

delimiter //CREATE PROCEDURE my_proc ()
BEGIN
SELECT * FROM my_table;
INSERT INTO my_table (col1, col2) VALUES ('value1', 'value2');
END //delimiter ;

在這個例子中，我們使用 delimiter // 命令將定界符改為兩個斜線 //。在存儲過程中，以分號 ; 結尾的語句不再被解釋為語句的結束。而使用 delimiter ; 可以將分號恢復為語句定界符。

總之，delimiter 命令可以改變 MySQL 數據庫系統中 SQL 查詢語句的分隔符，從而可使一條 SQL 查詢語句包含多個 SQL 語句。這樣的話，就方便了我們在一個語句里面加入多個語句，而且不會被錯

1.4 MySQL的變量

mysql中的變量包括：系統變量、用戶變量、局部變量。

系統變量

MySQL 系統變量是指在 MySQL 服務器運行時控制其行為的參數。這些變量可以被設置為特定的值來改變服務器的默認設置，以滿足不同的需求。
MySQL 系統變量可以具有全局（global）或會話（session）作用域。

• 全局作用域是指對所有連接和所有數據庫都適用；
• 會話作用域是指只對當前連接和當前數據庫適用。

查看系統變量

show [global|session] variables;show [global|session] variables like '';select @@[global|session.]系統變量名;

注意：沒有指定session或global時，默認是session。

設置系統變量

set [global | session] 系統變量名 = 值;set @@[global | session.]系統變量名 = 值;

注意：無論是全局設置還是會話設置，當mysql服務重啟之后，之前配置都會失效。可以通過修改MySQL根目錄下的my.ini配置文件達到永久修改的效果。（my.ini是MySQL數據庫默認的系統級配置文件，默認是不存在的，需要新建，并參考一些資料進行配置。）
windows系統是my.ini
linux系統是my.cnf
my.ini文件通常放在mysql安裝的根目錄下，如下圖：

這個文件通常是不存在的，可以新建，新建后例如提供以下配置：

[mysqld]
autocommit=0

這種配置就表示永久性關閉自動提交機制。（不建議這樣做。）

用戶變量

用戶自定義的變量。只在當前會話有效。所有的用戶變量’@'開始。

給用戶變量賦值

set @name = 'jackson';
set @age := 30;
set @gender := '男', @addr := '北京大興區';
select @email := 'jackson@123.com';
select sal into @sal from emp where ename ='SMITH';

讀取用戶變量的值

select @name, @age, @gender, @addr, @email, @sal;

注意：mysql中變量不需要聲明。直接賦值就行。如果沒有聲明變量，直接讀取該變量，返回null

局部變量

在存儲過程中可以使用局部變量。使用declare聲明。在begin和end之間有效。

變量的聲明

declare 變量名 數據類型 [default ...];

變量的數據類型就是表字段的數據類型，例如：int、bigint、char、varchar、date、time、datetime等。
注意：declare通常出現在begin end之間的開始部分。

變量的賦值

set 變量名 = 值;
set 變量名 := 值;
select 字段名 into 變量名 from 表名 ...;

例如：以下程序演示局部變量的聲明、賦值、讀取：

create PROCEDURE p2()
begin /*聲明變量*/declare emp_count int default 0;/*聲明變量*/declare sal double(10,2) default 0.0;/*給變量賦值*/select count(*) into emp_count from emp;/*給變量賦值*/set sal := 5000.0;/*讀取變量的值*/select emp_count;/*讀取變量的值*/select sal;
end;

call p2();

1.5 if語句

語法格式：

if 條件 then
......
elseif 條件 then
......
elseif 條件 then
......
else
......
end if;

案例：員工月薪sal，超過10000的屬于“高收入”，6000到10000的屬于“中收入”，少于6000的屬于“低收入”。

create procedure p3(               )
begindeclare sal int default 5000;declare grade varchar(20);if sal > 10000 thenset grade := '高收入';elseif sal >= 6000 thenset grade := '中收入';elseset grade := '低收入';end if;select grade;
end;

call p3();

1.6 參數

存儲過程的參數包括三種形式：

• in：入參（未指定時，默認是in）
• out：出參
• inout：既是入參，又是出參

不管入參還是出參，都是變量

案例：員工月薪sal，超過10000的屬于“高收入”，6000到10000的屬于“中收入”，少于6000的屬于“低收入”。

create procedure p4(in sal int, out grade varchar(20))
beginif sal > 10000 thenset grade := '高收入';elseif sal >= 6000 thenset grade := '中收入';elseset grade := '低收入';end if;
end;

call p4(5000, @grade);
select @grade;

案例：將傳入的工資sal上調10%

create procedure p5(inout sal int)
beginset sal := sal * 1.1;
end;

set @sal := 10000;
call p5(@sal);
select @sal;

1.7 case語句

語法格式：

case 值when 值1 then......when 值2 then......when 值3 then......else......
end case;

casewhen 條件1 then......when 條件2 then......when 條件3 then......else......
end case;

案例：根據不同月份，輸出不同的季節。3 4 5月份春季。6 7 8月份夏季。9 10 11月份秋季。12 1 2 冬季。其他非法。

create procedure mypro(in month int, out result varchar(100))
begin case monthwhen 3 then set result := '春季';when 4 then set result := '春季';when 5 then set result := '春季';when 6 then set result := '夏季';when 7 then set result := '夏季';when 8 then set result := '夏季';when 9 then set result := '秋季';when 10 then set result := '秋季';when 11 then set result := '秋季';when 12 then set result := '冬季';when 1 then set result := '冬季';when 2 then set result := '冬季';else set result := '非法月份';end case;
end;

create procedure mypro(in month int, out result varchar(100))
begin case when month = 3 or month = 4 or month = 5 then set result := '春季';when  month = 6 or month = 7 or month = 8  then set result := '夏季';when  month = 9 or month = 10 or month = 11  then set result := '秋季';when  month = 12 or month = 1 or month = 2  then set result := '冬季';else set result := '非法月份';end case;
end;

call mypro(9, @season);
select @season;

1.8 while循環

語法格式：

while 條件 do循環體;
end while;

案例：傳入一個數字n，計算1~n中所有偶數的和。

create procedure mypro(in n int)
begindeclare sum int default 0;while n > 0 doif n % 2 = 0 thenset sum := sum + n;end if;set n := n - 1;end while;select sum;
end;

call mypro(10);

1.9 repeat循環

語法格式：

repeat循環體;until 條件
end repeat;

注意：條件成立時結束循環。

案例：傳入一個數字n，計算1~n中所有偶數的和。

create procedure mypro(in n int, out sum int)
begin set sum := 0;repeat if n % 2 = 0 then set sum := sum + n;end if;set n := n - 1;until n <= 0end repeat;
end;

call mypro(10, @sum);
select @sum;

1.10 loop循環

語法格式：

create procedure mypro()
begin declare i int default 0;mylp:loop set i := i + 1;if i = 5 then leave mylp;end if;select i;end loop;
end;

create procedure mypro()
begin declare i int default 0;mylp:loop set i := i + 1;if i = 5 then iterate mylp;end if;if i = 10 then leave mylp;end if;select i;end loop;
end;

1.11 游標cursor

游標（cursor）可以理解為一個指向結果集中某條記錄的指針，允許程序逐一訪問結果集中的每條記錄，并對其進行逐行操作和處理。

使用游標時，需要在存儲過程或函數中定義一個游標變量，并通過 DECLARE 語句進行聲明和初始化。然后，使用 OPEN 語句打開游標，使用 FETCH 語句逐行獲取游標指向的記錄，并進行處理。最后，使用 CLOSE 語句關閉游標，釋放相關資源。游標可以大大地提高數據庫查詢的靈活性和效率。

聲明游標的語法：

declare 游標名稱 cursor for 查詢語句;

打開游標的語法：

open 游標名稱;

通過游標取數據的語法：

fetch 游標名稱 into 變量[,變量,變量......]

關閉游標的語法：

close 游標名稱;

案例：從dept表查詢部門編號和部門名，創建一張新表dept2，將查詢結果插入到新表中。

drop procedure if exists mypro;create procedure mypro()
begin declare no int;declare name varchar(100);declare dept_cursor cursor for select deptno,dname from dept;drop table if exists dept2;create table dept2(no int primary key,name varchar(100));open dept_cursor;while true dofetch dept_cursor into no, name;insert into dept2(no,name) values(no,name);end while;close dept_cursor;
end;call mypro();

執行結果：

出現了異常：異常信息中顯示沒有數據了。這是因為while true循環導致的。

不過雖然出現了異常，但是表創建成功了，數據也插入成功了：

注意：聲明局部變量和聲明游標有順序要求，局部變量的聲明需要在游標聲明之前完成。

1.12 捕捉異常并處理

語法格式：

DECLARE handler_name HANDLER FOR condition_value action_statement

1. handler_name 表示異常處理程序的名稱，重要取值包括：
   1. CONTINUE：發生異常后，程序不會終止，會正常執行后續的過程。(捕捉)
   2. EXIT：發生異常后，終止存儲過程的執行。（上拋）
2. condition_value 是指捕獲的異常，重要取值包括：
   1. SQLSTATE sqlstate_value，例如：SQLSTATE ‘02000’
   2. SQLWARNING，代表所有01開頭的SQLSTATE
   3. NOT FOUND，代表所有02開頭的SQLSTATE
   4. SQLEXCEPTION，代表除了01和02開頭的所有SQLSTATE
3. action_statement 是指異常發生時執行的語句，例如：CLOSE cursor_name

給之前的游標添加異常處理機制：

drop procedure if exists mypro;create procedure mypro()
begin declare no int;declare name varchar(100);declare dept_cursor cursor for select deptno,dname from dept;declare exit handler for not found close dept_cursor;drop table if exists dept2;create table dept2(no int primary key,name varchar(100));open dept_cursor;while true dofetch dept_cursor into no, name;insert into dept2(no,name) values(no,name);end while;close dept_cursor;
end;call mypro();

1.13 存儲函數

存儲函數：帶返回值的存儲過程。參數只允許是in（但不能寫顯示的寫in）。沒有out，也沒有inout。
語法格式：

CREATE FUNCTION 存儲函數名稱(參數列表) RETURNS 數據類型 [特征]
BEGIN--函數體RETURN ...;
END;

“特征”的可取重要值如下：

• deterministic：用該特征標記該函數為確定性函數（什么是確定性函數？每次調用函數時傳同一個參數的時候，返回值都是固定的）。這是一種優化策略，這種情況下整個函數體的執行就會省略了，直接返回之前緩存的結果，來提高函數的執行效率。
• no sql：用該特征標記該函數執行過程中不會查詢數據庫，如果確實沒有查詢語句建議使用。告訴 MySQL 優化器不需要考慮使用查詢緩存和優化器緩存來優化這個函數，這樣就可以避免不必要的查詢消耗產生，從而提高性能。
• reads sql data：用該特征標記該函數會進行查詢操作，告訴 MySQL 優化器這個函數需要查詢數據庫的數據，可以使用查詢緩存來緩存結果，從而提高查詢性能；同時 MySQL 還會針對該函數的查詢進行優化器緩存處理。

案例：計算1~n的所有偶數之和

-- 刪除函數
drop function if exists sum_fun;-- 創建函數
create function sum_fun(n int)
returns int deterministic 
begin declare result int default 0;while n > 0 do if n % 2 = 0 then set result := result + n;end if;set n := n - 1;end while;return result;
end;-- 調用函數
set @result = sum_fun(100);
select @result;

1.14 觸發器

MySQL 觸發器是一種數據庫對象，它是與表相關聯的特殊程序。它可以在特定的數據操作（例如插入（INSERT）、更新（UPDATE）或刪除（DELETE））觸發時自動執行。MySQL 觸發器使數據庫開發人員能夠在數據的不同狀態之間維護一致性和完整性，并且可以為特定的數據庫表自動執行操作。

觸發器的作用主要有以下幾個方面：

1. 強制實施業務規則：觸發器可以幫助確保數據表中的業務規則得到強制執行，例如檢查插入或更新的數據是否符合某些規則。
2. 數據審計：觸發器可以聲明在執行數據修改時自動記日志或審計數據變化的操作，使數據對數據庫管理員和 SQL 審計人員更易于追蹤和審計。
3. 執行特定業務操作：觸發器可以自動執行特定的業務操作，例如計算數據行的總數、計算平均值或總和等。

MySQL 觸發器分為兩種類型: BEFORE 和 AFTER。BEFORE 觸發器在執行 INSERT、UPDATE、DELETE 語句之前執行，而 AFTER 觸發器在執行 INSERT、UPDATE、DELETE 語句之后執行。

創建觸發器的語法如下：

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW
BEGIN
-- 觸發器執行的 SQL 語句
END;

其中：

• trigger_name：觸發器的名稱
• BEFORE/AFTER：觸發器的類型，可以是 BEFORE 或者 AFTER
• INSERT/UPDATE/DELETE：觸發器所監控的 DML 調用類型
• table_name：觸發器所綁定的表名
• FOR EACH ROW：表示觸發器在每行受到 DML 的影響之后都會執行
• 觸發器執行的 SQL 語句：該語句會在觸發器被觸發時執行

需要注意的是，觸發器是一種高級的數據庫功能，只有在必要的情況下才應該使用，例如在需要實施強制性業務規則時。過多的觸發器和復雜的觸發器邏輯可能會影響查詢性能和擴展性。

關于觸發器的NEW和OLD關鍵字：
在 MySQL 觸發器中，NEW 和 OLD 是兩個特殊的關鍵字，用于引用在觸發器中受到修改的行的新值和舊值。具體而言：

• NEW：在觸發 INSERT 或 UPDATE 操作期間，NEW 用于引用將要插入或更新到表中的新行的值。
• OLD：在觸發 UPDATE 或 DELETE 操作期間，OLD 用于引用更新或刪除之前在表中的舊行的值。

通俗的講，NEW 是指觸發器執行的操作所要插入或更新到當前行中的新數據；而 OLD 則是指當前行在觸發器執行前原本的數據。

在MySQL 觸發器中，NEW 和 OLD 使用方法是相似的。在觸發器中，可以像引用表的其他列一樣引用 NEW 和 OLD。例如，可以使用 OLD.column_name 從舊行中引用列值，也可以使用 NEW.column_name 從新行中引用列值。

示例：

假設有一個名為 my_table 的表，其中包含一個名為 quantity 的列。當在該表上執行 UPDATE 操作時，以下觸發器會將舊值 OLD.quantity 累加到新值 NEW.quantity 中：

CREATE TRIGGER my_trigger
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
SET NEW.quantity = NEW.quantity + OLD.quantity;
END;

在此觸發器中，OLD.quantity 引用原始行的 quantity 值（舊值），而 NEW.quantity 引用更新行的 quantity 值（新值）。在觸發器執行期間，數據行的 quantity 值將設置為舊值加上新值。

需要注意的是，在使用 NEW 和 OLD 時，需要根據 DML 操作的類型進行判斷，以確定哪個關鍵字表示新值，哪個關鍵字則表示舊值。

案例：當我們對dept表中的數據進行insert delete update的時候，請將這些操作記錄到日志表當中，日志表如下：

drop table if exists oper_log;create table oper_log(id bigint primary key auto_increment,table_name varchar(100) not null comment '操作的哪張表',oper_type varchar(100) not null comment '操作類型包括insert delete update',oper_time datetime not null comment '操作時間',oper_id bigint not null comment '操作的那行記錄的id',oper_desc text comment '操作描述'
);

觸發器1：向dept表中插入數據時，記錄日志

create trigger dept_trigger_insert 
after insert on dept
for each row
begininsert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values
(null,'dept','insert',now(),new.deptno,concat('插入數據：deptno=', new.deptno, ',dname=', new.dname,',loc=', new.loc));
end;

查看觸發器：

show triggers;

刪除觸發器：

drop trigger if exists dept_trigger_insert;

向dept表中插入一條記錄：

日志表中多了一條記錄：

觸發器2：修改dept表中數據時，記錄日志

create trigger dept_trigger_update
after update on dept
for each row
begininsert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values
(null,'dept','update',now(),new.deptno,concat('更新前：deptno=', old.deptno, ',dname=', old.dname,',loc=', old.loc, ',更新后：deptno=', new.deptno, ',dname=', new.dname,',loc=', new.loc));
end;

更新一條記錄：

update dept set loc = '北京' where deptno = 60;

日志表中多了一條記錄：

注意：更新一條記錄則對應一條日志。如果一次更新3條記錄，那么日志表中插入3條記錄。

觸發器3：刪除dept表中數據時，記錄日志

create trigger dept_trigger_delete
after delete on dept
for each row
begininsert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values
(null,'dept','delete',now(),old.deptno,concat('刪除了數據：deptno=', old.deptno, ',dname=', old.dname,',loc=', old.loc));
end;

刪除一條記錄：

delete from dept where deptno = 60;

日志表中多了一條記錄：

第2章 存儲引擎

2.1 存儲引擎概述

MySQL存儲引擎決定了數據在磁盤上的存儲方式和訪問方式。不同的存儲引擎實現了不同的存儲和檢索算法，因此它們在處理和管理數據的方式上存在差異。

MySQL常見的存儲引擎包括InnoDB、MyISAM、Memory、Archive等。每個存儲引擎都有自己的特點和適用場景。

例如，

• InnoDB引擎支持事務和行級鎖定，適用于需要高并發讀寫的應用；
• MyISAM引擎不支持事務，但適用于讀操作較多的應用；
• Memory引擎數據全部存儲在內存中，適用于對讀寫速度要求很高的應用等等。（基于Hash算法）

選擇適合的存儲引擎可以提高MySQL的性能和效率，并且根據應用需求來合理選擇存儲引擎可以提供更好的數據管理和查詢功能。

2.2 MySQL支持哪些存儲引擎

使用show engines \G;命令可以查看所有的存儲引擎：

*************************** 1. row ***************************Engine: MEMORYSupport: YESComment: Hash based, stored in memory, useful for temporary tables
Transactions: NOXA: NOSavepoints: NO
*************************** 2. row ***************************Engine: MRG_MYISAMSupport: YESComment: Collection of identical MyISAM tables
Transactions: NOXA: NOSavepoints: NO
*************************** 3. row ***************************Engine: CSVSupport: YESComment: CSV storage engine
Transactions: NOXA: NOSavepoints: NO
*************************** 4. row ***************************Engine: FEDERATEDSupport: NOComment: Federated MySQL storage engine
Transactions: NULLXA: NULLSavepoints: NULL
*************************** 5. row ***************************Engine: PERFORMANCE_SCHEMASupport: YESComment: Performance Schema
Transactions: NOXA: NOSavepoints: NO
*************************** 6. row ***************************Engine: MyISAMSupport: YESComment: MyISAM storage engine
Transactions: NOXA: NOSavepoints: NO
*************************** 7. row ***************************Engine: InnoDBSupport: DEFAULTComment: Supports transactions, row-level locking, and foreign keys
Transactions: YESXA: YESSavepoints: YES
*************************** 8. row ***************************Engine: ndbinfoSupport: NOComment: MySQL Cluster system information storage engine
Transactions: NULLXA: NULLSavepoints: NULL
*************************** 9. row ***************************Engine: BLACKHOLESupport: YESComment: /dev/null storage engine (anything you write to it disappears)
Transactions: NOXA: NOSavepoints: NO
*************************** 10. row ***************************Engine: ARCHIVESupport: YESComment: Archive storage engine
Transactions: NOXA: NOSavepoints: NO
*************************** 11. row ***************************Engine: ndbclusterSupport: NOComment: Clustered, fault-tolerant tables
Transactions: NULLXA: NULLSavepoints: NULL

Support是Yes的表示支持該存儲引擎。當前MySQL的版本是8.0.33
MySQL默認的存儲引擎是：InnoDB

2.3 指定和修改存儲引擎

2.3.1 指定存儲引擎

在MySQL中，你可以在創建表時指定使用的存儲引擎。通過在CREATE TABLE語句中使用ENGINE關鍵字，你可以指定要使用的存儲引擎。

以下是指定存儲引擎的示例：

CREATE TABLE my_table (column1 INT, column2 VARCHAR(50)) ENGINE = InnoDB;

在這個例子中，我們創建了一個名為my_table的表，并指定了使用InnoDB存儲引擎。

如果你不顯式指定存儲引擎，MySQL將使用默認的存儲引擎。默認情況下，MySQL 8的默認存儲引擎是InnoDB。

2.3.2 修改存儲引擎

在MySQL中，你可以通過ALTER TABLE語句修改表的存儲引擎。下面是修改存儲引擎的示例：

ALTER TABLE my_table ENGINE = MyISAM;

在這個例子中，我們使用ALTER TABLE語句將my_table表的存儲引擎修改為MyISAM。

請注意，在修改存儲引擎之前，你需要考慮以下幾點：

1. 修改存儲引擎可能需要執行復制表的操作，因此可能會造成數據的丟失或不可用。確保在執行修改之前備份你的數據。
2. 不是所有的存儲引擎都支持相同的功能。要確保你選擇的新存儲引擎支持你應用程序所需的功能。
3. 修改表的存儲引擎可能會影響到現有的應用程序和查詢。確保在修改之前評估和測試所有的影響。
4. ALTER TABLE語句可能需要適當的權限才能執行。確保你擁有足夠的權限來執行修改存儲引擎的操作。

總而言之，修改存儲引擎需要謹慎進行，且需要考慮到可能的影響和風險。建議在進行修改之前進行適當的測試和備份。

2.4 常用的存儲引擎及適用場景

在實際開發中，以下存儲引擎是比較常用的：

1. InnoDB：
   1. MySQL默認的事務型存儲引擎
   2. 支持ACID事務
   3. 具有較好的并發性能和數據完整性
   4. 支持行級鎖定。（還支持外鍵）
   5. 適用于大多數應用場景，尤其是需要事務支持的應用。
2. MyISAM：
   1. 是MySQL早期版本中常用的存儲引擎
   2. 支持全文索引和表級鎖定
   3. 不支持事務
   4. 由于其簡單性和高性能，在某些特定的應用場景中會得到廣泛應用，如讀密集的應用。
3. MEMORY：（基于Hash算法）
   1. 稱為HEAP，是將表存儲在內存中的存儲引擎
   2. 具有非常高的讀寫性能，但數據會在服務器重啟時丟失。
   3. 適用于需要快速讀寫的臨時數據集、緩存和臨時表等場景。
4. CSV：
   1. 將數據以純文本格式存儲的存儲引擎
   2. 適用于需要處理和導入/導出CSV格式數據的場景。
5. ARCHIVE：
   1. 將數據高效地進行壓縮和存儲的存儲引擎
   2. 適用于需要長期存儲大量歷史數據且不經常查詢的場景。

第3章 索引

3.1 什么是索引

索引是一種能夠提高檢索（查詢）效率的提前排好序的數據結構。例如：書的目錄就是一種索引機制。索引是解決SQL慢查詢的一種方式。

3.2 索引的創建和刪除

主鍵會自動添加索引

主鍵字段會自動添加索引，不需要程序員干涉，主鍵字段上的索引被稱為主鍵索引

unique約束的字段自動添加索引

unique約束的字段也會自動添加索引，不需要程序員干涉，這種字段上添加的索引稱為唯一索引

給指定的字段添加索引

建表時添加索引：

CREATE TABLE emp (...name varchar(255),...INDEX idx_name (name)
);

如果表已經創建好了，后期給字段添加索引

ALTER TABLE emp ADD INDEX idx_name (name);

也可以這樣添加索引：

create index idx_name on emp(name);

刪除指定字段上的索引

ALTER TABLE emp DROP INDEX idx_name;

查看某張表上添加了哪些索引

show index from 表名;

3.3 索引的分類

不同的存儲引擎有不同的索引類型和實現：

• 按照數據結構分類：
  • B+樹 索引（mysql的InnoDB存儲引擎采用的就是這種索引）采用 B+樹 的數據結構
  • Hash 索引（僅 memory 存儲引擎支持）：采用 哈希表 的數據結構
• 按照物理存儲分類：
  • 聚集索引：索引和表中數據在一起，數據存儲的時候就是按照索引順序存儲的。一張表只能有一個聚集索引。
  • 非聚集索引：索引和表中數據是分開的，索引是獨立于表空間的，一張表可以有多個非聚集索引。
• 按照字段特性分類：
  • 主鍵索引（primary key）
  • 唯一索引（unique）
  • 普通索引（index）
  • 全文索引（fulltext：僅 InnoDB和MyISAM 存儲引擎支持）
• 按照字段個數分類：
  • 單列索引、聯合索引（也叫復合索引、組合索引）

3.4 MySQL索引采用了B+樹數據結構

常見的樹相關的數據結構包括：

• 二叉樹
• 紅黑樹
• B樹
• B+樹

區別：樹的高度不同。樹的高度越低，性能越高。這是因為每一個節點都是一次I/O

3.4.1 二叉樹

有這樣一張表

如果不給id字段添加索引，默認進行全表掃描，假設查詢id=10的數據，那至少要進行10次磁盤IO。效率低。可以給id字段添加索引，假設該索引使用了二叉樹這種數據結構，這個二叉樹是這樣的（推薦一個數據結構可視化網站Data Structure Visualizations，是舊金山大學（USFCA）的一個網站）：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

如果這個時候要找id=10的數據，需要的IO次數是？4次。效率顯著提升了。
但是MySQL并沒有直接使用這種普通的二叉樹，這種普通二叉樹在數據極端的情況下，效率較低。比如下面的數據：

如果給id字段添加索引，并且該索引底層使用了普通二叉樹，這棵樹會是這樣的：

你雖然使用了二叉樹，但這更像一個鏈表。查找效率等同于鏈表查詢O(n)【查找算法的時間復雜度是線性的】。查找效率極低。
因此對于MySQL來說，它并沒有選擇這種數據結構作為索引。

3.4.2 紅黑樹（自平衡二叉樹）

通過自旋平衡規則進行旋轉，子節點會自動分叉為2個分支，從而減少樹的高度，當數據有序插入時比二叉樹數據檢索性能更好。
例如有以下數據

給id字段添加索引，并且該索引使用了紅黑樹數據結構，那么會是這樣：

如果查找id=10的數據，磁盤IO次數為：5次。效率比普通二叉樹要高一些。

但是如果數據量龐大，例如500萬條數據，也會導致樹的高度很高，磁盤IO次數仍然很多，查詢效率也會比較低。

因此MySQL并沒有使用紅黑樹這種數據結構作為索引。

3.4.3 B Trees（B樹）

B Trees首先是一個自平衡的。
B Trees每個節點下的子節點數量 > 2。
B Trees每個節點中也不是存儲單個數據，可以存儲多個數據。
B Trees又稱為平衡多路查找樹。

B Trees分支的數量不是2，是大于2，具體是多少個分支，由階決定。例如：

• 3階的B Trees，一個節點下最多有3個子節點，每個節點中最多有2個數據。
• 4階的B Trees，一個節點下最多有4個子節點，每個節點中最多有3個數據。
• 5階（5, 4）
• 6階（6, 5）
• …
• 16階（16, 15）【MySQL采用了16階】

采用B Trees，你會發現相同的數據量，B Tree 樹的高度更低。磁盤IO次數更少。
3階的B Trees：

假設id字段添加了索引，并且采用了B Trees數據結構，查找id=10的數據，只需要3次磁盤IO。
4階的B Trees：

更加詳細的存儲是這樣的，請看下圖：


在B Trees中，每個節點不僅存儲了索引值，還存儲該索引值對應的數據行。
并且每個節點中的p1 p2 p3是指向下一個節點的指針。

B Trees數據結構存在的缺點是：不適合做區間查找，對于區間查找效率較低。假設要查id在[3~7]之間的，需要查找的是3,4,5,6,7。那么查這每個索引值都需要從頭節點開始。
因此MySQL使用了B+ Trees解決了這個問題。

3.4.4 B+ Trees（B+ 樹）★

B+ Trees 相較于 B Trees改進了哪些？

• B+樹將數據都存儲在葉子節點中。并且葉子節點之間使用鏈表連接，這樣很適合范圍查詢。
• B+樹的非葉子節點上只有索引值，沒有數據，所以非葉子節點可以存儲更多的索引值，這樣讓B+樹更矮更胖，提高檢索效率。

假設有這樣一張表：

B+ Trees方式存儲的話如下圖所示：

經典面試題： mysql為什么選擇B+樹作為索引的數據結構，而不是B樹？

1. 非葉子節點上可以存儲更多的鍵值，階數可以更大，更矮更胖，磁盤IO次數少，數據查詢效率高。
2. 所有數據都是有序存儲在葉子節點上，讓范圍查找，分組查找效率更高。
3. 數據頁之間、數據記錄之間采用鏈表鏈接，讓升序降序更加方便操作。

經典面試題： 如果一張表沒有主鍵索引，那還會創建B+樹嗎？
當一張表沒有主鍵索引時，默認會使用一個隱藏的內置的聚集索引（clustered index）。這個聚集索引是基于表的物理存儲順序構建的，通常是使用B+樹來實現的。

3.5 其他索引及相關調優

3.5.1 Hash索引

支持Hash索引的存儲引擎有：

• InnoDB（不支持手動創建Hash索引，系統會自動維護一個自適應的Hash索引）
  • 對于InnoDB來說，即使手動指定了某字段采用Hash索引，最終show index from 表名的時候，還是BTREE。
• Memory（支持Hash索引）

Hash索引底層的數據結構就是哈希表。一個數組，數組中每個元素是鏈表。和java中HashMap一樣。哈希表中每個元素都是key value結構。key存儲索引值，value存儲行指針。

原理如下：

如果name字段上添加了Hash索引idx_name

Hash索引長這個樣子：

檢索原理：假設 name=‘孫行者’。通過哈希算法將’孫行者’轉換為數組下標，通過下標找鏈表，在鏈表上遍歷找到孫行者的行指針。

注意：不同的字符串，經過哈希算法得到的數組下標可能相同，這叫做哈希碰撞/哈希沖突。【不過，好的哈希算法應該具有很低的碰撞概率。常用的哈希算法如MD5、SHA-1、SHA-256等都被設計為盡可能減少碰撞的發生。】

Hash索引優缺點：

• 優點：只能用在等值比較中，效率很高。例如：name=‘孫悟空’
• 缺點：不支持排序，不支持范圍查找。

3.5.2 聚集索引和非聚集索引

按照數據的物理存儲方式不同，可以將索引分為聚集索引（聚簇索引）和非聚集索引（非聚簇索引）。

存儲引擎是InnoDB的，主鍵上的索引屬于聚集索引。
存儲引擎是MyISAM的，任意字段上的索引都是非聚集索引。

InnoDB的物理存儲方式：當創建一張表t_user，并使用InnoDB存儲引擎時，會在硬盤上生成這樣一個文件：

• t_user.ibd （InnoDB data表索引 + 數據）
• t_user.frm （存儲表結構信息）

MyISAM的物理存儲方式：當創建一張表t_user，并使用MyISAM存儲引擎時，會在硬盤上生成這樣一個文件：

• t_user.MYD （表數據）
• t_user.MYI （表索引）
• t_user.frm （表結構）

注意：從MySQL8.0開始，不再生成frm文件了，引入了數據字典，用數據字典來統一存儲表結構信息，例如：

• information_schema.TABLES （表包含了數據庫中所有表的信息，例如表名、數據庫名、引擎類型等）
• information_schema.COLUMNS（表包含了數據庫中所有表的列信息，例如列名、數據類型、默認值等）

聚集索引的原理圖：（B+樹，葉子節點上存儲了索引值 + 數據）

非聚集索引的原理圖：（B+樹，葉子節點上存儲了索引值 + 行指針）

聚集索引的優點和缺點：

1. 優點：聚集索引將數據存儲在索引樹的葉子節點上。可以減少一次查詢，因為查詢索引樹的同時可以獲取數據。
2. 缺點：對數據進行修改或刪除時需要更新索引樹，會增加系統的開銷。

3.5.3 二級索引

二級索引也屬于非聚集索引。也有人把二級索引稱為輔助索引。
有表t_user，id是主鍵。age是非主鍵。在age字段上添加的索引稱為二級索引。（所有非主鍵索引都是二級索引）

二級索引的數據結構：

二級索引的查詢原理：
假設查詢語句為：

select * from t_user where age = 30;

為什么會“回表”？因為使用了select *
避免“回表【回到原數據表】”是提高SQL執行效率的手段。例如：select id from t_user where age = 30; 這樣的SQL語句是不需要回表的。

3.5.4 覆蓋索引

覆蓋索引（Covering Index），顧名思義，是指某個查詢語句可以通過索引的覆蓋來完成，而不需要回表查詢真實數據。其中的覆蓋指的是在執行查詢語句時，查詢需要的所有列都可以從索引中提取到，而不需要再去查詢實際數據行獲取查詢所需數據。
當使用覆蓋索引時，MySQL可以直接通過索引，也就是索引上的數據來獲取所需的結果，而不必再去查找表中的數據。這樣可以顯著提高查詢性能。

假設有一個用戶表（user）包含以下列：id, username, email, age。

常見的查詢是根據用戶名查詢用戶的郵箱。如果為了提高這個查詢的性能，可以創建一個覆蓋索引，包含（username, email）這兩列。

創建覆蓋索引的SQL語句可以如下：

CREATE INDEX idx_user_username_email ON user (username, email);

當執行以下查詢時：

SELECT email FROM user WHERE username = 'lucy';

MySQL可以直接使用覆蓋索引（idx_user_username_email）來獲取查詢結果，而不必再去查找用戶表中的數據。這樣可以減少磁盤I/O并提高查詢效率。而如果沒有覆蓋索引，MySQL會先使用索引（username）來找到匹配的行，然后再回表查詢獲取郵箱，這個過程會增加更多的磁盤I/O和查詢時間。

值得注意的是，覆蓋索引的創建需要考慮查詢的字段選擇。如果查詢需要的字段較多，可能需要創建包含更多列的覆蓋索引，以滿足完全覆蓋查詢的需要。

覆蓋索引具有以下優點：

1. 提高查詢性能：覆蓋索引能夠滿足查詢的所有需求，同時不需要訪問表中的實際數據行，從而可以提高查詢性能。這是因為DBMS可以直接使用索引來執行查詢，而不需要從磁盤讀取實際的數據行。
2. 減少磁盤和內存訪問次數：當使用覆蓋索引時，DBMS不需要訪問實際的數據行。這樣可以減少磁盤和內存訪問次數，從而提高查詢性能。
3. 減少網絡傳輸：由于在覆蓋索引中可以存儲所有查詢所需的列，因此可以減少數據的網絡傳輸次數，從而提高查詢的性能。
4. 可以降低系統開銷：在高壓力的數據庫系統中，使用覆蓋索引可以減少系統開銷，從而提高系統的可靠性和可維護性。

覆蓋索引的缺點包括：

1. 需要更多的內存：覆蓋索引需要存儲查詢所需的所有列，因此需要更多的內存來存儲索引。在大型數據庫系統中，這可能會成為一項挑戰。
2. 會使索引變得龐大：當索引中包含了許多列時，它們可能會使索引變得非常龐大，從而影響查詢性能，并且可能會占用大量的磁盤空間。
3. 只有在查詢中包含了索引列時才能使用：只有當查詢中包含了所有的索引列時才能使用覆蓋索引。如果查詢中包含了其他列，DBMS仍然需要訪問實際的數據行，并且無法使用覆蓋索引提高查詢性能。

3.5.5 索引下推

索引下推（Index Condition Pushdown）是一種 MySQL 中的優化方法，它可以將查詢中的過濾條件下推到索引層級中處理，從而減少回表次數，優化查詢性能。

具體來說，在使用索引下推時，MySQL 會在索引的葉節點層級執行查詢的過濾條件，過濾掉無用的索引記錄，僅返回符合條件的記錄的主鍵，這樣就可以避免查詢時回表讀取表格的數據行，從而縮短了整個查詢過程的時間。

假設有以下表結構：

表名：users

id	name	age	city
1	John	25	New York
2	Alice	30	London
3	Bob	40	Paris
4	Olivia	35	Berlin
5	Michael	28	Sydney

現在我們創建了一個多列索引：（索引下推通常是基于多列索引的。）

ALTER TABLE users ADD INDEX idx_name_city_age (name, city, age);

假設我們要查詢年齡大于30歲，并且所在城市是"London"的用戶，假設只給age字段添加了索引，它就不會使用索引下推。傳統的查詢優化器會將所有滿足年齡大于30歲的記錄讀入內存，然后再根據城市進行篩選。

使用索引下推優化后，在索引范圍掃描的過程中，優化器會判斷只有在城市列為"London"的情況下，才會將滿足年齡大于30歲的記錄加載到內存中。這樣就可以避免不必要的IO和數據傳輸，提高查詢性能。

具體的查詢語句可以是：

SELECT * FROM users WHERE age > 30 AND city = 'London';

在執行這個查詢時，優化器會使用索引下推技術，先根據索引范圍掃描找到所有滿足條件的記錄，然后再回到原數據表中獲取完整的行數據，最終返回結果。

3.5.6 單列索引（單一索引）

單列索引是指對數據庫表中的某一列或屬性進行索引創建，對該列進行快速查找和排序操作。單列索引可以加快查詢速度，提高數據庫的性能。

舉個例子，假設我們有一個學生表（student），其中有以下幾個列：學生編號（student_id）、姓名（name）、年齡（age）和性別（gender）。

如果我們針對學生表的學生編號（student_id）列創建了單列索引，那么可以快速地根據學生編號進行查詢或排序操作。例如，我們可以使用以下SQL語句查詢學生編號為123456的學生信息：

SELECT * FROM student WHERE student_id = 123456;

由于我們對學生編號列建立了單列索引，所以數據庫可以直接通過索引快速定位到具有學生編號123456的那一行記錄，從而加快查詢速度。

3.5.7 復合索引（組合索引）

復合索引（Compound Index）也稱為多列索引（Multi-Column Index），是指對數據庫表中多個列進行索引創建。

與單列索引不同，復合索引可以包含多個列。這樣可以將多個列的值組合起來作為索引的鍵，以提高多列條件查詢的效率。

舉個例子，假設我們有一個訂單表（Order），其中包含以下幾個列：訂單編號（OrderID）、客戶編號（CustomerID）、訂單日期（OrderDate）和訂單金額（OrderAmount）。

如果我們為訂單表的客戶編號和訂單日期這兩列創建復合索引（CustomerID, OrderDate），那么可以在查詢時同時根據客戶編號和訂單日期來快速定位到匹配的記錄。

例如，我們可以使用以下SQL語句查詢客戶編號為123456且訂單日期為2021-01-01的訂單信息：

SELECT * FROM Order WHERE CustomerID = 123456 AND OrderDate = '2021-01-01';

由于我們為客戶編號和訂單日期創建了復合索引，數據庫可以使用這個索引來快速定位到符合條件的記錄，從而加快查詢速度。復合索引的使用能夠提高多列條件查詢的效率，但需要注意的是，復合索引的創建和維護可能會增加索引的存儲空間和對于寫操作的影響。

相對于單列索引，復合索引有以下幾個優勢：

1. 減少索引的數量：復合索引可以包含多個列，因此可以減少索引的數量，減少索引的存儲空間和維護成本。
2. 提高查詢性能：當查詢條件中涉及到復合索引的多個列時，數據庫可以使用復合索引進行快速定位和過濾，從而提高查詢性能。
3. 覆蓋查詢：如果復合索引包含了所有查詢需要的列，那么數據庫可以直接使用索引中的數據，而不需要再進行表的讀取，從而提高查詢性能。
4. 排序和分組：由于復合索引包含多個列，因此可以用于排序和分組操作，從而提高排序和分組的性能。

3.6 索引的優缺點

索引是數據庫中一種重要的數據結構，用于加速數據的檢索和查詢操作。它的優點和缺點如下：

優點：

1. 提高查詢性能：通過創建索引，可以大大減少數據庫查詢的數據量，從而提升查詢的速度。
2. 加速排序：當查詢需要按照某個字段進行排序時，索引可以加速排序的過程，提高排序的效率。
3. 減少磁盤IO：索引可以減少磁盤IO的次數，這對于磁盤讀寫速度較低的場景，尤其重要。

缺點：

1. 占據額外的存儲空間：索引需要占據額外的存儲空間，特別是在大型數據庫系統中，索引可能占據較大的空間。
2. 增刪改操作的性能損耗：每次對數據表進行插入、更新、刪除等操作時，需要更新索引，會導致操作的性能降低。
3. 資源消耗較大：索引需要占用內存和CPU資源，特別是在大規模并發訪問的情況下，可能對系統的性能產生影響。

3.7 何時用索引

在以下情況下建議使用索引：

1. 頻繁執行查詢操作的字段：如果這些字段經常被查詢，使用索引可以提高查詢的性能，減少查詢的時間。
2. 大表：當表的數據量較大時，使用索引可以快速定位到所需的數據，提高查詢效率。
3. 需要排序或者分組的字段：在對字段進行排序或者分組操作時，索引可以減少排序或者分組的時間。
4. 外鍵關聯的字段：在進行表之間的關聯查詢時，使用索引可以加快關聯查詢的速度。

在以下情況下不建議使用索引：

1. 頻繁執行更新操作的表：如果表經常被更新數據，使用索引可能會降低更新操作的性能，因為每次更新都需要維護索引。
2. 小表：對于數據量較小的表，使用索引可能并不會帶來明顯的性能提升，反而會占用額外的存儲空間。
3. 對于唯一性很差的字段，一般不建議添加索引。當一個字段的唯一性很差時，查詢操作基本上需要掃描整個表的大部分數據。如果為這樣的字段創建索引，索引的大小可能會比數據本身還大，導致索引的存儲空間占用過高，同時也會導致查詢操作的性能下降。

總之，索引需要根據具體情況進行使用和權衡，需要考慮到表的大小、查詢頻率、更新頻率以及業務需求等因素。

第4章 MySQL優化

4.1 MySQL優化手段

MySQL數據庫的優化手段通常包括但不限于：

• SQL查詢優化：這是最低成本的優化手段，通過優化查詢語句、適當添加索引等方式進行。并且效果顯著。
• 庫表結構優化：通過規范化設計、優化索引和數據類型等方式進行庫表結構優化，需要對數據庫結構進行調整和改進
• 系統配置優化：根據硬件和操作系統的特點，調整最大連接數、內存管理、IO調度等參數
• 硬件優化：升級硬盤、增加內存容量、升級處理器等硬件方面的投入，需要購買和替換硬件設備，成本較高

我們主要掌握：SQL查詢優化

4.2 SQL性能分析工具

4.2.1 查看數據庫整體情況

通過以下命令可以查看當前數據庫在SQL語句執行方面的整體情況：

show global status like 'Com_select';
show global status like 'Com_insert';
show global status like 'Com_delete';
show global status like 'Com_update';show global status like 'Com_______';

這些結果反映了從 MySQL 服務器啟動到當前時刻，所有的 SELECT 查詢總數。對于 MySQL 性能優化來說，通過查看 Com_select 的值可以了解 SELECT 查詢在整個 MySQL 服務期間所占比例的情況：

• 如果 Com_select 次數過高，可能說明查詢表中的每條記錄都會返回過多的字段。（表示該數據庫是屬于讀密集型的數據庫）
• 如果 Com_select 次數很少，同時insert或delete或update的次數很高，可能說明服務器運行的應用程序過于依賴寫入操作和少量讀取操作。（表示該數據庫是屬于寫密集型的數據庫）

總之，通過查看 Com_select 的值，可以了解 MySQL 服務器的長期執行情況，并在優化查詢性能時，幫助我們了解 MySQL 的性能瓶頸。

4.2.2 慢查詢日志

慢查詢日志文件可以將查詢較慢的DQL語句記錄下來，便于我們定位需要調優的select語句。
通過以下命令查看慢查詢日志功能是否開啟：

show variables like 'slow_query_log';

慢查詢日志功能默認是關閉的。請修改my.ini文件來開啟慢查詢日志功能，在my.ini的[mysqld]后面添加如下配置：


注意：slow_query_log=1表示開啟慢查詢日志功能，long_query_time=3表示：只要SELECT語句的執行耗時超過3秒則將其記錄到慢查詢日志中。
重啟mysql服務。再次查看是否開啟慢查詢日志功能：

嘗試執行一條時長超過3秒的select語句：

select empno,ename,sleep(4) from emp where ename='smith';

慢查詢日志文件默認存儲在：C:\dev\mysql-8.0.36-winx64\data 目錄下，默認的名字是：計算機名-slow.log
通過該文件可以清晰的看到哪些DQL語句屬于慢查詢：

4.2.3 show profiles

通過show profiles可以查看一個SQL語句在執行過程中具體的耗時情況。幫助我們更好的定位問題所在。

查看當前數據庫是否支持 profile操作：

select @@have_profiling;

查看 profiling 開關是否打開：

select @@profiling;

將 profiling 開關打開：

set profiling = 1;

可以執行多條DQL語句，然后使用 show profiles; 來查看當前數據庫中執行過的每個SELECT語句的耗時情況。

select empno,ename from emp;
select empno,ename from emp where empno=7369;
select count(*) from emp;
show profiles;

查看某個SQL語句語句在執行過程中，每個階段的耗時情況：

show profile for query 4;

想查看執行過程中cpu的情況，可以執行以下命令：

show profile cpu for query 4;

4.2.4 explain

explain命令可以查看一個DQL語句的執行計劃，根據執行計劃可以做出相應的優化措施。提高執行效率。

explain select * from emp where empno=7369;

id

id反映出一條select語句執行順序，id越大優先級越高。id相同則按照自上而下的順序執行。

explain select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal;

由于id相同，反映出三張表在執行順序上屬于平等關系，執行時采用，先d，再e，最后s。

explain select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where e.sal=(select sal from emp where ename='ford');

反映出，先執行子查詢，然后讓e和d做表連接。

select_type

反映了mysql查詢語句的類型。常用值包括：

• SIMPLE：表示查詢中不包含子查詢或UNION操作。這種查詢通常包括一個表或是最多一個聯接（JOIN）
• PRIMARY：表示當前查詢是一個主查詢。（主要的查詢）
• UNION：表示查詢中包含UNION操作
• SUBQUERY：子查詢
• DERIVED：派生表（表示查詢語句出現在from后面）

table

反映了這個查詢操作的是哪個表。

type

反映了查詢表中數據時的訪問類型，常見的值：

1. NULL：效率最高，一般不可能優化到這個級別，只有查詢時沒有查詢表的時候，訪問類型是NULL。例如：select 1;
2. system：通常訪問系統表的時候，訪問類型是system。一般也很難優化到這個程度。
3. const：根據主鍵或者唯一性索引查詢，索引值是常量值時。explain select * from emp where empno=7369;
4. eq_ref：根據主鍵或者唯一性索引查詢。索引值不是常量值。
5. ref：使用了非唯一的索引進行查詢。
6. range：使用了索引，掃描了索引樹的一部分。
7. index：表示用了索引，但是也需要遍歷整個索引樹。
8. all：全表掃描

效率最高的是NULL，效率最低的是all，從上到下，從高到低。

possible_keys

這個查詢可能會用到的索引

key

實際用到的索引

key_len

反映索引在查詢中使用的列所占的總字節數。

rows

查詢掃描的預估計行數。

Extra

給出了與查詢相關的額外信息和說明。這些額外信息可以幫助我們更好地理解查詢執行的過程。

4.3 索引優化

加索引 vs 不加索引

將這個sql腳本初始化到數據庫中（初始化100W條記錄）：t_vip.sql
根據id查詢（id是主鍵，有索引）：

select * from t_vip where id = 900000;

根據name查詢（name上沒有索引）：

select * from t_vip where name='4c6494cb';

給name字段添加索引：

create index idx_t_user_name on t_vip(name);

再次根據name查詢（此時name上已經有索引了） ：

select * from t_vip where name='4c6494cb';

最左前綴原則★

假設有這樣一張表：

create table t_customer(id int primary key auto_increment,name varchar(255),age int,gender char(1),email varchar(255)
);

添加了這些數據：

insert into t_customer values(null, 'zhangsan', 20, 'M', 'zhangsan@123.com');
insert into t_customer values(null, 'lisi', 22, 'M', 'lisi@123.com');
insert into t_customer values(null, 'wangwu', 18, 'F', 'wangwu@123.com');
insert into t_customer values(null, 'zhaoliu', 22, 'F', 'zhaoliu@123.com');
insert into t_customer values(null, 'jack', 30, 'M', 'jack@123.com');

添加了這樣的復合索引：

create index idx_name_age_gender on t_customer(name,age,gender);

最左前綴原則：當查詢語句條件中包含了這個復合索引最左邊的列 name 時，此時索引才會起作用。

驗證1：

explain select * from t_customer where name='zhangsan' and age=20 and gender='M';

驗證結果：完全使用了索引

驗證2：

explain select * from t_customer where name='zhangsan' and age=20;

驗證結果：使用了部分索引

驗證3：

explain select * from t_customer where name='zhangsan';

驗證結果：使用了部分索引

驗證4：

explain select * from t_customer where age=20 and gender='M' and name='zhangsan';

驗證結果：完全使用了索引

驗證5：

explain select * from t_customer where gender='M' and age=20;

驗證結果：沒有使用任何索引

驗證6：

explain select * from t_customer where name='zhangsan' and gender='M';

驗證結果：使用了部分索引

驗證7：

explain select * from t_customer where name='zhangsan' and gender='M' and age=20;

驗證結果：完全使用了索引

范圍查詢時，在“范圍條件”右側的列索引會失效：
驗證：

explain select * from t_customer where name='zhangsan' and age>20 and gender='M';

驗證結果：name和age列索引生效。gender列索引無效。

怎么解決？建議范圍查找時帶上“=”

explain select * from t_customer where name='zhangsan' and age>=20 and gender='M';

索引失效情況★

有這樣一張表：

create table t_emp(id int primary key auto_increment,name varchar(255),sal int,age char(2)
);

有這樣一些數據：

insert into t_emp values(null, '張三', 5000,'20');
insert into t_emp values(null, '張飛', 4000,'30');
insert into t_emp values(null, '李飛', 6000,'40');

有這樣一些索引：

create index idx_t_emp_name on t_emp(name);
create index idx_t_emp_sal on t_emp(sal);
create index idx_t_emp_age on t_emp(age);

索引列參加了運算，索引失效

explain select * from t_emp where sal > 5000;

驗證結果：使用了索引

explain select * from t_emp where sal*10 > 50000;

驗證結果：索引失效

索引列進行模糊查詢時以 % 開始的，索引失效

explain select * from t_emp where name like '張%';

驗證結果：索引有效

explain select * from t_emp where name like '%飛';

驗證結果：索引失效

索引列是字符串類型，但查詢時省略了單引號，索引失效

explain select * from t_emp where age='20';

驗證結果：索引有效

explain select * from t_emp where age=20;

驗證結果：索引失效

查詢條件中有or，只要有未添加索引的字段，索引失效

explain select * from t_emp where name='張三' or sal=5000;

驗證結果：使用了索引

將t_emp表sal字段上的索引刪除：

alter table t_emp drop index idx_t_emp_sal;

再次驗證：

explain select * from t_emp where name='張三' or sal=5000;

驗證結果：索引失效

當查詢的符合條件的記錄在表中占比較大，索引失效

復制一張新表：emp2

create table emp2 as select * from emp;

給sal添加索引：

alter table emp2 add index idx_emp2_sal(sal);

驗證1：

explain select * from emp2 where sal > 800;

不走索引：

驗證2：

explain select * from emp2 where sal > 1000;

不走索引：

驗證3：

explain select * from emp2 where sal > 2000;

走索引：

關于is null和is not null的索引失效問題

給emp2的comm字段添加一個索引：

create index idx_emp2_comm on emp2(comm);

將emp2表的comm字段值全部更新為NULL：

update emp2 set comm=null;

驗證此時條件使用is null是否走索引：

explain select * from emp2 where comm is null;

驗證結果：不走索引。

驗證此時條件使用is not null是否走索引：

將emp2表的comm字段全部更新為非NULL：

update emp2 set comm=100;

驗證此時條件使用is null是否走索引：

explain select * from emp2 where comm is null;

驗證結果：走索引

驗證此時條件使用is not null是否走索引：

explain select * from emp2 where comm is not null;

驗證結果：不走索引

結論：走索引還是不走索引，根數據分布有很大關系，如果符合條件的記錄占比較大，會考慮使用全表掃描，而放棄走索引。

指定索引

當一個字段上既有單列索引，又有復合索引時，我們可以通過以下的SQL提示來要求該SQL語句執行時采用哪個索引：

• use index(索引名稱)：建議使用該索引，只是建議，底層mysql會根據實際效率來考慮是否使用你推薦的索引。
• ignore index(索引名稱)：忽略該索引
• force index(索引名稱)：強行使用該索引

查看 t_customer 表上的索引：

show index from t_customer;

可以看到name age gender三列添加了一個復合索引。
現在給name字段添加一個單列索引：

create index idx_name on t_customer(name);

看看以下的語句默認使用了哪個索引：

explain select * from t_customer where name='zhangsan';

通過測試得知，默認使用了聯合索引。

如何建議使用單列索引idx_name：

explain select * from t_customer use index(idx_name) where name='zhangsan';

如何忽略使用符合索引 idx_name_age_gender：

explain select * from t_customer ignore index(idx_name_age_gender) where name='zhangsan';

如何強行使用單列索引idx_name：

explain select * from t_customer force index(idx_name) where name='zhangsan';

覆蓋索引★

覆蓋索引我們在講解索引的時候已經提到過了，覆蓋索引強調的是：在select后面寫字段的時候，這些字段盡可能是索引所覆蓋的字段，這樣可以避免回表查詢。盡可能避免使用 select *，因為select * 很容易導致回表查詢。（本質就是：能在索引上檢索的，就不要再次回表查詢了。）

例如：有一張表 emp3，其中 ename,job添加了聯合索引：idx_emp3_ename_job，以下這個select語句就不會回表：

drop table if exists emp3;
create table emp3 as select * from emp;
alter table emp3 add constraint emp3_pk primary key(empno);
create index idx_emp3_ename_job on emp3(ename,job);
explain select empno,ename,job from emp3 where ename='KING';

如果查詢語句要查找的列沒有在索引中，則會回表查詢，例如：

explain select empno,ename,job,sal from emp3 where ename='KING';

面試題：t_user表字段如下：id,name,password,realname,birth,email。表中數據量500萬條，請針對以下SQL語句給出優化方案：

select id,name,realname from t_user where name='魯智深';

如果只給name添加索引，底層會進行大量的回表查詢，效率較低，建議給name和realname兩個字段添加聯合索引，這樣大大減少回表操作，提高查詢效率。

前綴索引★

如果一個字段類型是varchar或text字段，字段中存儲的是文本或者大文本，直接對這種長文本創建索引，會讓索引體積很大，怎么優化呢？可以將字符串的前幾個字符截取下來當做索引來創建。這種索引被稱為前綴索引，例如：

drop table if exists emp4;
create table emp4 as select * from emp;
create index idx_emp4_ename_2 on emp4(ename(2));

以上SQL表示將emp4表中ename字段的前2個字符創建到索引當中。

使用前綴索引時，需要通過以下公式來確定使用前幾個字符作為索引：

select count(distinct substring(ename,1,前幾個字符)) / count(*) from emp4;

以上查詢結果越接近1，表示索引的效果越好。（原理：做索引值的話，索引值越具有唯一性效率越高）

假設我們使用前1個字符作為索引值：

select count(distinct substring(ename,1,1)) / count(*) from emp4;

假設我們使用前2個字符作為索引值：

select count(distinct substring(ename,1,2)) / count(*) from emp4;

可見使用前2個字符作為索引值，能夠讓索引值更具有唯一性，效率越好，因此我們選擇前2個字符作為前綴索引。

create index idx_emp4_ename_2 on emp4(ename(2));

執行以下的查詢語句則會走這個前綴索引：

explain select * from emp4 where ename='KING';

單列索引和復合索引怎么選擇

當查詢語句的條件中有多個條件，建議將這幾個列創建為復合索引，因為創建單列索引很容易回表查詢。
例如分別給emp5表ename，job添加兩個單列索引：

create table emp5 as select * from emp;
alter table emp5 add constraint emp5_pk primary key(empno);create index idx_emp5_ename on emp5(ename);
create index idx_emp5_job on emp5(job);

執行以下查詢語句：

explain select empno,ename,job from emp5 where ename='SMITH' and job='CLERK';

ename和job都出現在查詢條件中，可以給emp6表的ename和job創建一個復合索引：

create table emp6 as select * from emp;
alter table emp6 add constraint emp6_pk primary key(empno);create index idx_emp6_ename_job on emp6(ename,job);
explain select empno,ename,job from emp6 where ename='SMITH' and job='CLERK';

對于以上查詢語句，使用復合索引避免了回表，因此這種情況下還是建議使用復合索引。

注意：創建索引時應考慮最左前綴原則，主字段并且具有很強唯一性的字段建議排在第一位，例如：

create index idx_emp_ename_job on emp(ename,job);

和以下方式對比：

create index idx_emp_job_ename on emp(job,ename);

由于ename是主字段，并且ename具有很好的唯一性，建議將ename列放在最左邊。因此這兩種創建復合索引的方式，建議采用第一種。

復合索引底層原理：

索引創建原則

1. 表數據量龐大，通常超過百萬條數據。
2. 經常出現在where，order by，group by后面的字段建議添加索引。
3. 創建索引的字段盡量具有很強的唯一性。
4. 如果字段存儲文本，內容較大，一定要創建前綴索引。
5. 盡量使用復合索引，使用單列索引容易回表查詢。
6. 如果一個字段中的數據不會為NULL，建議建表時添加not null約束，這樣優化器就知道使用哪個索引列更加有效。
7. 不要創建太多索引，當對數據進行增刪改的時候，索引需要重新重新排序。
8. 如果很少的查詢，經常的增刪改不建議加索引。

4.4 SQL優化

order by的優化

準備數據：

drop table if exists workers;create table workers(id int primary key auto_increment,name varchar(255),age int,sal int
);insert into workers values(null, '孫悟空', 500, 50000);
insert into workers values(null, '豬八戒', 300, 40000);
insert into workers values(null, '沙和尚', 600, 40000);
insert into workers values(null, '白骨精', 600, 10000);

explain查看一個帶有order by的語句時，Extra列會顯示：using index 或者 using filesort，區別是什么？

• using index: 表示使用索引，因為索引是提前排好序的。效率很高。
• using filesort：表示使用文件排序，這就表示沒有走索引，對表中數據進行排序，排序時將硬盤的數據讀取到內存當中，在內存當中排好序。這個效率是低的，應避免。

此時name沒有添加索引，如果根據name進行排序的話：

explain select id,name from workers order by name;

顯然這種方式效率較低。
給name添加索引：

create index idx_workers_name on workers(name);

再根據name排序：

explain select id,name from workers order by name;

這樣效率則提升了。

如果要通過age和sal兩個字段進行排序，最好給age和sal兩個字段添加復合索引，不添加復合索引時：
按照age升序排，如果age相同則按照sal升序

explain select id,age,sal from workers order by age,sal;

這樣效率是低的。
給age和sal添加復合索引：

create index idx_workers_age_sal on workers(age, sal);

先按照age升序排，如果age相同則按照sal升序：

explain select id,age,sal from workers order by age,sal;

這樣效率提升了。

在B+樹上葉子結點上的所有數據默認是按照升序排列的，如果按照age降序，如果age相同則按照sal降序，會走索引嗎？

explain select id,age,sal from workers order by age desc,sal desc;

可以看到備注信息是：反向索引掃描，使用了索引。
這樣效率也是很高的，因為B+樹葉子結點之間采用的是雙向指針。可以從左向右（升序），也可以從右向左（降序）。

如果一個升序，一個降序會怎樣呢？

explain select id,age,sal from workers order by age asc, sal desc;

可見age使用了索引，但是sal沒有使用索引。怎么辦呢？可以針對這種排序情況創建對應的索引來解決：

create index idx_workers_ageasc_saldesc on workers(age asc, sal desc);

創建的索引如下：A表示升序，D表示降序。

再次執行：

explain select id,age,sal from workers order by age asc, sal desc;

我們再來看看，對于排序來說是否支持最左前綴法則：

explain select id,age,sal from workers order by sal;

通過測試得知，order by也遵循最左前綴法則。

我們再來看一下未使用覆蓋索引會怎樣？

explain select * from workers order by age,sal;

通過測試得知，排序也要盡量使用覆蓋索引。

order by 優化原則總結：

1. 排序也要遵循最左前綴法則。
2. 使用覆蓋索引。
3. 針對不同的排序規則，創建不同索引。（如果所有字段都是升序，或者所有字段都是降序，則不需要創建新的索引）
4. 如果無法避免filesort，要注意排序緩存的大小，默認緩存大小256KB，可以修改系統變量 sort_buffer_size ：

show variables like 'sort_buffer_size';

group by優化

創建empx表：

create table empx as select * from emp;

job字段上沒有索引，根據job進行分組，查看每個工作崗位有多少人：

select job,count(*) from empx group by job;

看看是否走索引了：

explain select job,count(*) from empx group by job;

使用了臨時表，效率較低。

給job添加索引：

create index idx_empx_job on empx(job);

再次執行：

explain select job,count(*) from empx group by job;

效率提升了。

我們再來看看group by是否需要遵守最左前綴法則：給deptno和sal添加復合索引

create index idx_empx_deptno_sal on empx(deptno, sal);

根據部門編號分組，查看每個部門人數：

explain select deptno,count(*) from empx group by deptno;

效率很高，因為deptno是復合索引中最左邊的字段。
根據sal分組，查看每個工資有多少人：

explain select sal, count(*) from empx group by sal;

使用了臨時表，效率較低。
通過測試得知，group by也同樣遵循最左前綴法則。

我們再來測試一下，如果將部門編號deptno（復合索引的最左列）添加到where條件中，效率會不會提升：

explain select sal, count(*) from empx where deptno=10 group by sal;

效率有提升的，這說明了，group by確實也遵循最左前綴法則。（where中使用了最左列）

limit優化

數據量特別龐大時，取數據時，越往后效率越低，怎么提升？mysql官方給出的解決方案是：使用覆蓋索引+子查詢的形式來提升效率。

怎么解決？使用覆蓋索引，加子查詢
使用覆蓋索引：速度有所提升

使用子查詢形式取其他列的數據：

通過測試，這種方式整體效率有所提升。

主鍵優化

主鍵設計原則：

1. 主鍵值不要太長，二級索引葉子結點上存儲的是主鍵值，主鍵值太長，容易導致索引占用空間較大。
2. 盡量使用auto_increment生成主鍵。盡量不要使用uuid做主鍵，因為uuid不是順序插入。
3. 最好不要使用業務主鍵，因為業務的變化會導致主鍵值的頻繁修改，主鍵值不建議修改，因為主鍵值修改，聚集索引一定會重新排序。
4. 在插入數據時，主鍵值最好是順序插入，不要亂序插入，因為亂序插入可能會導致B+樹葉子結點頻繁的進行頁分裂與頁合并操作，效率較低。
   1. 主鍵值對應聚集索引，插入主鍵值如果是亂序的，B+樹葉子結點需要不斷的重新排序，重排過程中還會頻繁涉及到頁分裂和頁合并的操作，效率較低。
   2. B+樹上的每個節點都存儲在頁（page）中。一個頁面中存儲一個節點。
   3. MySQL的InnoDB存儲引擎一個頁可以存儲16KB的數據。
   4. 如果主鍵值不是順序插入的話，會導致頻繁的頁分裂和頁合并。在一個B+樹中，頁分裂和頁合并是樹的自動調整機制的一部分。當一個頁已經滿了，再插入一個新的關鍵字時就會觸發頁分裂操作，將頁中的關鍵字分配到兩個新的頁中，同時調整樹的結構。相反，當一個頁中的關鍵字數量下降到一個閾值以下時，就會觸發頁合并操作，將兩個相鄰的頁合并成一個新的頁。如果主鍵值是隨機的、不是順序插入的，那么頁的利用率會降低，頁分裂和頁合并的次數就會增加。由于頁的分裂和合并是比較耗時的操作，頻繁的分裂和合并會降低數據庫系統的性能。因此，為了優化B+樹的性能，可以將主鍵值設計成順序插入的，這樣可以減少頁的分裂和合并的次數，提高B+樹的性能。在實際應用中，如果對主鍵值的順序性能要求不是特別高，也可以采用一些技術手段來減少頁分裂和合并，例如B+樹分裂時采用“延遲分裂”技術，或者通過調整頁的大小和節點的大小等方式來優化B+樹的性能。

insert優化

insert優化原則：

• 批量插入：數據量較大時，不要一條一條插入，可以批量插入，當然，建議一次插入數據不超過1000條

insert into t_user(id,name,age) values (1,'jack',20),(2,'lucy',30),(3,'timi',22);

• mysql默認是自動提交事務，只要執行一條DML語句就自動提交一次，因此，當插入大量數據時，建議手動開啟事務和手動提交事務。不建議使用數據庫事務自動提交機制。
• 主鍵值建議采用順序插入，順序插入比亂序插入效率高。
• 超大數據量插入可以考慮使用mysql提供的load指令，load指令可以將csv文件中的數據批量導入到數據庫表當中，并且效率很高，過程如下：
  • 第一步：登錄mysql時指定參數

mysql --local-infile -uroot -p1234

• 第二步：開啟local_infile功能

set global local_infile = 1;

• 第三步：執行load指令

use powernode;create table t_temp(id int primary key,name varchar(255),password varchar(255),birth char(10),email varchar(255)
);load data local infile 'E:\\powernode\\05-MySQL高級\\resources\\t_temp-100W.csv' into table t_temp fields terminated by ',' lines terminated by '\n';

文件中的數據如下：

導入表中之后，數據如下：

count(*)優化

分組函數count的使用方式：

• count(主鍵)
  • 原理：將每個主鍵值取出，累加
• count(常量值)
  • 原理：獲取到每個常量值，累加
• count(字段)
  • 原理：取出字段的每個值，判斷是否為NULL，不為NULL則累加。
• count(*)
  • 原理：不用取值，底層mysql做了優化，直接統計總行數，效率最高。

結論：如果你要統計一張表中數據的總行數，建議使用 count(*)

注意：

• 對于InnoDB存儲引擎來說，count計數的實現原理就是將表中每一條記錄取出，然后累加。如果你想真正提高效率，可以自己使用額外的其他程序來實現，例如每向表中插入一條記錄時，在redis數據庫中維護一個總行數，這樣獲取總行數的時候，直接從redis中獲取即可，這樣效率是最高的。
• 對于MyISAM存儲引擎來說，當一個select語句沒有where條件時，獲取總行數效率是極高的，不需要統計，因為MyISAM存儲引擎維護了一個單獨的總行數。

update優化

當存儲引擎是InnoDB時，表的行級鎖是針對索引列添加的鎖，如果索引失效了，或者不是索引列時，會提升為表級鎖。
什么是行級鎖？A事務和B事務，開啟A事務后，通過A事務修改表中某條記錄，修改后，在A事務未提交的前提下，B事務去修改同一條記錄時，無法繼續，直到A事務提交，B事務才可以繼續。

有一張表：t_fruit

create table t_fruit(id int primary key auto_increment,name varchar(255)
);insert into t_fruit values(null, '蘋果');
insert into t_fruit values(null, '香蕉');
insert into t_fruit values(null, '橘子');

開啟A事務和B事務，演示行級鎖：
事務A沒有結束之前，事務B卡住：

事務A結束之后，事務B繼續執行：

當然，如果更新的不是同一行數據，事務A和事務B可以并發：

行級鎖是對索引列加鎖，以上更新語句的where條件是id，id是主鍵，當然有索引，所以使用了行級鎖，如果索引失效，或者字段上沒有索引，則會升級為表級鎖：

因此，為了更新的效率，建議update語句中where條件中的字段是添加索引的。