一.索引
什么是索引
什么是索引？
提到索引， 我們想到的是查詢慢了 設置索引唄！但是索引為什么起作用？設置了索引為什么還是慢我們其實不是很清楚。
在關系數據庫中，索引是一種數據結構，他將數據提前按照一定的規則進行排序和組織， 能夠幫助快速定位到數據記錄的數據，加快數據庫表中數據的查找和訪問速度。
像書籍的目錄、文件夾、標簽 、房號.... 都可以幫助我們快速定位，都可以視為索引。
能實現快速定位數據的一種存儲結構，其設計思想是以空間換時間。
索引的種類
在MySQL中索引是在存儲引擎層實現的，而不是在服務器層實現的，所以不同存儲引擎具有不同的索引類型和實現。常見的索引分類如下：
- 按數據結構分類：B+tree索引、Hash索引、Full-text索引。
- 按物理存儲分類：聚集索引、非聚集索引。
- 按字段特性分類：主鍵索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)。
- 按字段個數分類：單列索引、聯合索引（也叫復合索引、組合索引）。
常見索引數據結構和區別
-
- 二叉樹、紅黑樹、B樹 、B+樹
- 區別：樹的高度影響獲取數據的性能（每一個樹節點都是一次磁盤I/O)
1. 二叉樹：
特點：每個節點最多有兩個子節,大在左，小在右 ，數據隨機性情況下樹杈越明顯。
如果數據是按順序依次進入：
樹的高度則會很高（就是一個鏈表結構）， 此時元素的查找效率就等于鏈表查詢O(n)，數據檢索效率將極為低下。
極端的情況下 就是一個鏈表結構（如下圖），此時元素的查找效率就等于鏈表查詢O(n)。
2. 紅黑樹（平衡二叉樹）
雖通過自旋平衡，子節點會自動分叉為2個分支，從而減少樹的高度，當數據有序插入時比二叉樹數據檢索性能更佳.
但是如果 數據量過大，節點個數就越多，樹高度也會增高（也就是樹的深度越深），增加磁盤I/O次數，影響查詢效率。
3. B-樹
B樹的出現可以解決樹高度的問題。之所以是B樹，而并不是名稱中"xxx二叉樹"，就是它不再限制一個父節點中只能有兩個子節點，而是允許 M 個子節點（M > 2）。不僅如此，B樹的一個節點可以存儲多個元素，相比較于前面的那些二叉樹數據結構又將整體的樹高度降低了。
B 樹的節點可以包含有多個字節點，所以 B樹是一棵多叉樹，它的每一個節點包含的最多子節點數量的稱為B樹的階。如下圖是一顆3階的B樹。
當一顆3階的B樹查找 7這個的元素時的流程是怎么樣的？
先從根節點出發 ，判斷 7在4和8之間，根據P2存儲指針6的節點，判斷7大于6 最后指針找到葉子節點。也就找到有匹配7的鍵值。
可以發現一顆3階的B樹在查找葉子節點時，由于樹高度只有 3，所以查找過程最多只需要3次的磁盤I/O操作。
數據量不大時可能不太真切。但當數據量大時，節點也會隨著增多；此時如果還是前面的自平衡二叉樹的場景下，由于二叉樹只能最多2個葉子節點的約束，也只能縱向去的去擴展子節點，樹的高度會很高，意味著需要更多的操作磁盤I/O次數。而B樹則可以通過橫向擴展節點從而降低樹的高度，所以效率自然要比二叉樹效率更高。（直白說就是變矮胖了）
看到這，相信你也知道如果B樹這么適合，也就沒有接下來B+樹的什么事了。
接著，那為什么不用B樹，而用了B+樹呢？
你看啊，B樹其實已經滿足了我們最前面所要滿足的條件，減少磁盤I/O操作，同時支持按區間查找。但注意，雖然B樹支持按區間查找，但并不高效。例如上面的例子中，B樹能高效的通過等值查詢 15 這個值，但不方便查詢出一個區間內3 ~ 10區間內所有數的結果。因為當B樹做范圍查詢時需要使用中序遍歷，那么父節點和子節點也就需要不斷的來回切換涉及了多個節點會給磁盤I/O帶來很多負擔。
4. B+tree索引
在MySQL中為什么會選用B+tree做索引結構呢？
B+tree 是在B樹基礎上的一種優化，其更適合做存儲索引結構。在 B+tree 中，非葉子節點上僅存儲鍵值，不存儲數據；而所有數據記錄均存儲在葉子節點上，并且數據是按照順序排列的。此外在 B+tree 中各個數據頁之間是通過雙向鏈表連接的。B+tree 的結構圖如下：
B樹和B+樹的區別，Mysql為什么要選擇B+樹作為默認索引的數據結構
B+tree 結構實現數據索引具有如下優點：
a. 非葉子節點上可以存儲更多的鍵值，相應的樹的階數（節點的子節點樹）就會更大，樹也就會變得更矮更胖。這樣一來我們查找數據進行磁盤I/O的次數就會大大減少，數據查詢的效率也會更快。
b. 所有數據記錄都有序存儲在葉子節點上，就會使得范圍查找，排序查找，分組查找以及去重查找變得異常簡單。
c. 數據頁之間、數據記錄之間都是通過鏈表鏈接的，有了這個結構的支持就可以方便的在數據查詢后進行升序或者降序操作。
Hash索引
Hash索引其實用的不多，最主要是因為最常見的存儲引擎InnoDB不支持顯示地創建Hash索引，只支持自適應Hash索引。
雖然可以使用sql語句在InnoDB顯示聲明Hash索引，但是其實是不生效的
對name字段建立Hash索引，但是通過show index from 表名就會發現實際還是B+樹
在存儲引擎中，Memory引擎支持Hash索引
Hash索引其實有點像JAVA中的HashMap底層的數據結構，他也有很多的槽，存的也是鍵值對，鍵值為索引列，值為數據的這條數據的行指針，通過行指針就可以找到數據
假設現在user表用Memory存儲引擎，對name字段建立Hash索引，表中插入三條數據
Hash索引會對索引列name的值進行Hash計算，然后找到對應的槽下面，如下圖所示
當遇到name字段的Hash值相同時，也就是Hash沖突，就會形成一個鏈表，比如有name=張三有兩條數據，就會形成一個鏈表。
之后如果要查name=李四的數據，只需要對李四進行Hash計算，找到對應的槽，遍歷鏈表，取出name=李四對應的行指針，然后根據行指針去查找對應的數據。
Hash索引優缺點
- hash索引只能用于等值比較，所以查詢效率非常高
- 不支持范圍查詢，也不支持排序，因為索引列的分布是無序的
什么是聚簇索引與非聚集索引和區別？
按物理存儲分類：InnoDB的存儲方式是聚集索引，MyISAM的存儲方式是非聚集索引。
聚簇索引
- 聚簇索引將數據存儲在索引樹的葉子節點上。
- 聚簇索引可以減少一次查詢，因為查詢索引樹的同時就能獲取到數據。
- 聚簇索引的缺點是，對數據進行修改或刪除操作時需要更新索引樹，會增加系統的開銷。
- 聚簇索引通常用于數據庫系統中，主要用于提高查詢效率。
非聚簇索引（又稱二級索引 / 輔助索引）
- 非聚簇索引不將數據存儲在索引樹的葉子節點上，而是存儲在數據頁中。
- 非聚簇索引在查詢數據時需要兩次查詢，一次查詢索引樹，獲取數據頁的地址，再通過數據頁的地址查詢數據（通常情況下來說是的，但如果索引覆蓋的話實際上是不用回表的）。
- 非聚簇索引的優點是，對數據進行修改或刪除操作時不需要更新索引樹，減少了系統的開銷。
- 非聚簇索引通常用于數據庫系統中，主要用于提高數據更新和刪除操作的效率。
二級索引
在MySQL中，創建一張表時會默認為主鍵創建聚簇索引，B+樹將表中所有的數據組織起來，即數據就是索引主鍵所以在InnoDB里，主鍵索引也被稱為聚簇索引，索引的葉子節點存的是整行數據。而除了聚簇索引以外的所有索引都稱為二級索引，二級索引的葉子節點內容是主鍵的值。
例如創建如下一張表:
CREATE TABLE users(
id INT NOT NULL,
name VARCHAR(20) NOT NULL,
age INT NOT NULL,
PRIMARY KEY(id)
);
新建一個以age字段的二級索引:
ALTER TABLE users ADD INDEX index_age(age);
MySQL會分別創建主鍵id的聚簇索引和age的二級索引:
在MySQL中主鍵索引的葉子節點存的是整行數據，而二級索引葉子節點內容是主鍵的值.
回表
講完二級索引，接下來講一講如何使用二級索引查找數據。
這里假設對name字段創建了一個索引，并且表里就存了上面示例中的幾條數據，這里我再把圖拿過來
例如執行下面這條sql 則需要進行回表:
SELECT * FROM users WHERE age=35;
由于查詢條件是name = '趙六'，所以會走name索引
整個過程大致分為以下幾個步驟：
- 從根節點開始，21<35 定位右邊存儲指針，
- 在索葉子節點找到35的第一條記錄，也就是id=9的那條
- 由于是select *，還要查其它字段，此時就會根據id=9到聚簇索引（主鍵索引）中查找其它字段數據，這個查找過程前面說了很多次了，這個根據id=4到聚簇索引中查找數據的過程就被稱為回表
覆蓋索引
上一節說當執行select * from `user` where age = 35;這條sql的時候，會先從索引頁中查出來age = 35;對應的主鍵id，之后再回表，到聚簇索引中查詢其它字段的值。
那么當執行下面這條sql，又會怎樣呢？
select id from `user` where age = 35;
這次查詢字段從select *變成select id，查詢條件不變，所以也會走age索引
所以還是跟前面一樣了，先從索引頁中查出來age = 35;對應的主鍵id之后，驚訝的發現，sql中需要查詢字段的id值已經查到了，那次此時壓根就不需要回表了，已經查到id了，還回什么表。
而這種需要查詢的字段都在索引列中的情況就被稱為覆蓋索引，索引列覆蓋了查詢字段的意思。
當使用覆蓋索引時會減少回表的次數，這樣查詢速度更快，性能更高。
所以，在日常開發中，盡量不要select * ，需要什么查什么，如果出現覆蓋索引的情況，查詢會快很多。
單列索引
ALTER TABLE `test`.`user` ADD INDEX(`name`);
假設，我們現在對name字段加了一個普通非唯一索引，那么name就是索引列，同時name這個索引也就是單列索引
此時如果往表中插入三條數據，那么name索引的葉子節點存的數據就如下圖所示
mysql會根據name字段的值進行排序，這里我假設張三排在李四前面，當索引列的值相同時，就會根據id排序，所以索引實際上已經根據索引列的值排好序了。
這里肯定有小伙伴疑問，name字段存儲的中文也可以排序么？
答案是可以的，并且mysql支持很多種排序規則，我們在建數據庫或者是建表的時候等都可以指定排序規則，并且后面文章涉及到的字符串排序都是我隨便排的，實際情況可能不一樣。
對于單個索引列數據查找也是跟前面說的聚簇索引一樣，也會對數據分組，之后可以根據二分查找在單個索引列來查找數據。
當數據不斷增多，一個索引頁存儲不下數據的時候，也會用多個索引頁來存儲，并且索引頁直接也會形成雙向鏈表
當索引頁不斷增多是，為了方便在不同索引頁中查找數據，也就會抽取一個索引頁，除了存頁中id，同時也會存儲這個id對應的索引列的值
當數據越來越多越來越多，還會抽取，也會形成三層的一個B+樹，這里我就不畫了。
聯合索引
ALTER TABLE `test`.`user` ADD INDEX(`name`, `age`, `id`);
除了單列索引，聯合索引其實也是一樣的，只不過索引頁存的數據就多了一些索引列
比如，在name和age上建立一個聯合索引，此時單個索引頁就如圖所示
先以name排序，name相同時再以age排序，如果再有其它列，依次類推，最后再以id排序。
相比于只有name一個字段的索引來說，索引頁就多存了一個索引列。
最后形成的B+樹簡化為如下圖
最左前綴原則
顧名思義是最左優先，以最左邊的為起點任何連續的索引都能匹配上。
如果沒有第一列的話，直接訪問第二列，那第二列肯定是無序的，直接訪問后面的列就用不到索引了
當創建(a,b,c)復合索引時，想要索引生效的話，只能使用 a和ab、ac和abc三種組合！
單列索引聯合索引分別什么場景創建，優勢是什么
聯合索引的優勢
- 減少開銷
建一個聯合索引(a,b,c),實際相當于建了(a),(a,b),(a,b,c)三個索引.每多一個索引,都會增加寫操作的開銷和磁盤空間的開銷.對于大量數據的表,使用聯合索引會大大的減少開銷!
- 覆蓋索引
對聯合索引(a,b,c),如果有如下sql的
select a,b,c from table where a='xxx' and b='xx';
那么mysql可以直接通過遍歷索引取得數據,而無需回表,這減少了很多的隨機io操作.減少io操作,特別是隨機io其實DBA主要的優化策略.所以,在真正的實際應用中,覆蓋索引是主要的提升性能的優化手段之一.
- 效率高
索引列多,通過聯合索引篩選出的數據越少.比如有1000w條數據的表,有如下sql:
select col1,col2,col3 from table where col1=1 and col2=2 and col3=3;
假設:假設每個條件可以篩選出10%的數據
A:如果只有單列索引,那么通過該索引能篩選出1000w*10%=100w條數據,然后再回表從100w調數據中找到符合col2=2 and col3=3的數據,然后再排序,再分頁,以此類推(遞歸);
B:如果是(col1,col2,col3)聯合索引,通過三列索引篩選出1000w*10%*10%*10%=1w,效率提升可想
索引下推
索引下推（INDEX CONDITION PUSHDOWN，簡稱 ICP）是在 MySQL 5.6 針對掃描二級索引的一項優化改進。 用來在范圍查詢時減少回表的次數 。ICP 適用于 MYISAM 和 INNODB。
ALTER TABLE `test`.`user` ADD INDEX (`name`,`age`)
不使用索引下推實現
ExplAIn SELECT * FROM user1 WHERE name LIKE 'A%' and age = 40;
使用索引下推實現
Explain SELECT * FROM user1 WHERE name LIKE 'A%' and age = 40;
接下來要執行如下的sql
select * from `user` where name > '王五' and age > 22;
在MySQL5.6(不包括5.6)之前，整個sql大致執行步驟如下：
- 先根據二分查找，定位到name > '王五'的第一條數據，也就是id=4的那個趙六
- 之后就會根據id=4進行回表操作，到聚簇索引中查找id=4其它字段的數據，然后判斷數據中的age是否大于22，是的話就說明是我們需要查找的數據，否則就不是
- 之后順著鏈表，繼續遍歷，然后找到一條記錄就回一次表，然后判斷age，如此反復下去，直至結束
所以對于圖上所示，整個搜索過程會經歷5次回表操作，兩個趙六，兩個劉七，一個王九，最后符合條件的也就是id=6的趙六那條數據，其余age不符和。
雖然這么執行沒什么問題，但是不知有沒有發現其實沒必要進行那么多次回表，因為光從上面的索引圖示就可以看出，符合name > '王五' and age > 22的數據就id=6的趙六那條數據
所以在MySQL5.6之后，對上面的age > 22判斷邏輯進行了優化
前面還是一樣，定位查找到id=4的那個趙六，之后就不回表來判斷age了，因為索引列有age的值了，那么直接根據索引中age判斷是否大于22，如果大于的話，再回表查詢剩余的字段數據（因為是select *），然后再順序鏈表遍歷，直至結束
所以這樣優化之后，回表次數就成1了，相比于前面的5次，大大減少了回表的次數。
而這個優化，就被稱為索引下推，就是為了減少回表的次數。
之所以這個優化叫索引下推，其實是跟判斷age > 22邏輯執行的地方有關，這里就不過多贅述了。
索引合并
索引合并（index merge）是從MySQL5.1開始引入的索引優化機制，在之前的MySQL版本中，一條sql多個查詢條件只能使用一個索引，但是引入了索引合并機制之后，MySQL在某些特殊的情況下會掃描多個索引，然后將掃描結果進行合并
結果合并會為下面三種情況：
- 取交集（intersect）
- 取并集（union）
- 排序后取并集（sort-union）
為了不耽誤演示，刪除之前所有的索引，然后為name和age各自分別創建一個二級索引idx_name和idx_age
取交集（intersect）
當執行下面這條sql就會出現取交集的情況
select * from `user` where name = '趙六' and age= 22;
查看執行計劃
type是index_merge，并且possible_key和key都是idx_name和idx_age，說明使用了索引合并，并且Extra有Using intersect(idx_age,idx_name)，intersect就是交集的意思。
整個過程大致是這樣的，分別根據idx_name和idx_age取出對應的主鍵id，之后將主鍵id取交集，那么這部分交集的id一定同時滿足查詢name = '趙六' and age= 22的查詢條件（仔細想想），之后再根據交集的id回表
不過要想使用取交集的聯合索引，需要滿足各自索引查出來的主鍵id是排好序的，這是為了方便可以快速的取交集
比如下面這條sql就無法使用聯合索引
select * from `user` where name = '趙六' and age > 22;
只能用name這個索引，因為age > 22查出來的id是無序的，前面在講索引的時候有說過索引列的排序規則
由此可以看出，使用聯合索引條件還是比較苛刻的。
取并集（union）
取并集就是將前面例子中的and換成or
select * from `user` where name = '趙六' or age = 22;
前面執行的情況都一樣，根據條件到各自的索引上去查，之后對查詢的id取并集去重，之后再回表
同樣地，取并集也要求各自索引查出來的主鍵id是排好序的，如果查詢條件換成age > 22時就無法使用取并集的索引合并
select * from `user` where name = '趙六' or age > 22;
排序后取并集（sort-union）
雖然取并集要求各自索引查出來的主鍵id是排好序的，但是如果遇到沒排好序的情況，mysql會自動對這種情況進行優化，會先對主鍵id排序，然后再取并集，這種情況就叫 排序后取并集（sort-union）。
比如上面提到的無法直接取并集的sql就符合排序后取并集（sort-union）這種情況
select * from `user` where name = '趙六' or age > 22;
為什么Mysql默認 InnoDB, 而不是 MyISAM ？
MyISAM 與 InnoDB
眾所周知，MySQL 有兩種常見的存儲引擎。一種是 MyISAM，一種是 InnoDB。
一、它們是什么？
先來看看官網對 MyISAM 的描述，只有一句話，看來官方也不想多加解釋。
MyISAM is based on the older (and no longer available) ISAM storage engine but has many useful extensions.
大意：MyISAM 是一款青出于藍而勝于藍的存儲引擎，它在 ISAM 基礎上作了一些擴展和加工。關于 ISAM ，我只告訴你它是 Indexed Sequential Access Method 的縮寫，翻譯為“有索引的順序訪問方法”。
而對 InnoDB 的描述，就更 professional 一些了。
InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 8.0, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.
大意：InnoDB 是一種通用的存儲引擎，在高可靠和高性能上作了均衡。MySQL 8.0 中，它是默認的存儲引擎（其實在5.5之后的版本就是了），當你執行 CREATE TABLE 建表語句并且不帶 “ENGINE = ”子句時，默認幫你創建的就是 InnoDB 表了。
二、兩者有什么區別？
拿官網兩者的 Features 來作一個分析對比吧：
1、InnoDB 是聚集索引，數據文件是和索引綁在一起的，必須要有主鍵，通過主鍵索引效率很高，但是輔助索引需要兩次查詢，先查詢到主鍵，然后再通過主鍵查詢到數據。因此，主鍵不應該過大，否則其他索引也會很大。而 MyISAM 是非聚集索引，數據文件是分離的，索引保存的是數據文件的指針，主鍵索引和輔助索引是獨立的。
2、InnoDB 支持外鍵，而 MyISAM 不支持。對一個包含外鍵的 InnoDB 表轉為 MYISAM 會失敗。
3、InnoDB 在 MySQL 5.6 之前不支持全文索引，而 MyISAM 一直都支持，如果你用的是老版本，查詢效率上 MyISAM 要高。
4、InnoDB 鎖粒度是行鎖，而 MyISAM 是表鎖。
5、InnoDB 支持事務，MyISAM 不支持，對于 InnoDB 每一條 SQL 語言都默認封裝成事務，自動提交，這樣會影響速度，所以最好把多條 SQL 語言放在 begin 和 commit 之間，組成一個事務。
6、InnoDB 不保存表的具體行數，執行 select count(*) from table 時需要全表掃描。而 MyISAM 用一個變量保存了整個表的行數，執行上述語句時只需要讀出該變量即可，速度很快，但如果上述語句還包含了 where 子句，那么兩者執行效率是一樣的。
https://blogs.oracle.com/mysql/post/still-using-myisam-it-is-time-to-switch-to-innodb
https://tecadmin.NET/choosing-between-innodb-myisam-and-memory-storage-engines/
http://dimitrik.free.fr/blog/archives/2015/12/mysql-performance-revisiting-innodb-vs-myisam-with-mysql-57.html
如果一個表沒有主鍵索引那還會創建B+樹嗎？
答案是會的?。?！
InnoDB是MySQL中的一種存儲引擎，它會為每個表創建一個主鍵索引。如果表沒有明確的主鍵索引，InnoDB會使用一個隱藏的、自動生成的主鍵來創建索引。這個隱藏的主鍵索引使用的就是B+樹結構。因此，在InnoDB中，即使表沒有明確的主鍵索引，也會創建一個B+樹索引。
索引的優缺點，什么時候該用和不該用
優點：
1. 提高檢索效率
2. 降低排序成本，索引對應的字段是會有一個自動排序功能的，默認是升序asc。
它缺點是
- 創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加。
- 索引需要占用物理空間，數據量越大，占用空間越大
- 會降低表的增刪改的效率，因為每次增刪改索引，都需要進行動態維護
竟然索引有壞有好，什么時候需要索引，什么時候不需要？
適合：
1. 較頻繁的作為查詢條件的字段應該創建索引
不適合：
1. 字段值的唯一性太差不適合單獨做索引
2. 更新非常頻繁的字段不適合
3. 不會出現在where句中的字段不適合。
二.優化
1、優化方法
關于SQL優化方法，包括5點
1）創建索引減少掃描量；
2）調整索引減少計算量；
3）索引覆蓋（減少不必訪問的列，避免回表查詢）；
4）干預執行計劃；
5）SQL改寫；
2、通過Explain干預執行計劃
1.Explain含義
Explain是 SQL 分析工具中非常重要的一個功能，它可以模擬優化器執行查詢語句，幫助我們理解查詢是如何執行的；分析查詢執行計劃可以幫助我們發現查詢瓶頸，優化查詢性能。
2.Explain作用
- 表的讀取順序
- SQL執行時查詢操作類型
- 可以使用哪些索引
- 實際使用哪些索引
- 每張表有多少行記錄被掃描
- SQL語句性能分析
3.Explain用法
drop table orders;
drop table products;
drop table users;
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
password VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
price FLOAT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
order_date DATETIME NOT NULL,
total_price FLOAT NOT NULL,
product_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
alter table users add index index_name_email (name,email);
INSERT INTO users (name, email, password)
VALUES ('張三', 'zhangsan@example.com', 'password123'),
('李四', 'lisi@example.com', 'password123'),
('王五', 'wangwu@example.com', 'password123'),
('趙六', 'zhaoli@example.com', 'password123'),
('錢七', 'qianqi@example.com', 'password123');
INSERT INTO products (name, price)
VALUES ('產品 1', 10.00),
('產品 2', 15.00),
('產品 3', 20.00),
('產品 4', 12.00),
('產品 5', 18.00);
INSERT INTO orders (user_id, order_date, total_price, product_id)
VALUES (1, '2023-02-18 10:00:00', 100.00, 1),
(2, '2023-02-18 11:00:00', 50.00, 2),
(3, '2023-02-18 12:00:00', 20.00, 3),
(4, '2023-02-18 13:00:00', 15.00, 4),
(5, '2023-02-18 14:00:00', 25.00, 5);
MySQL5.7版本之前，使用Explain Extended在Explain的基礎上額外多返回filtered列與extra列；
Explain Extended select * from users;
MySQL5.7版本之前，使用Explain Partitions在Explain的基礎上額外多返回partitions列；
Explain Partitions select * from users;
MySQL5.7版本引入了這兩個特性，直接使用Explain關鍵字可以將partitions列、filtered列、extra列直接查詢出來。
Explain select * from users;
Explain語句返回列的各列含義：
|
列名 |
含義 |
|
id |
每個select都有一個對應的id號，并且是從1開始自增的 |
|
select_type |
查詢語句執行的查詢操作類型 |
|
table |
表名 |
|
partitions |
表分區情況 |
|
type |
查詢所用的訪問類型 |
|
possible_keys |
可能用到的索引 |
|
key |
實際查詢用到的索引 |
|
key_len |
所使用到的索引長度 |
|
ref |
使用到索引時，與索引進行等值匹配的列或者常量 |
|
rows |
預計掃描的行數（索引行數或者表記錄行數） |
|
filtered |
表示符合查詢條件的數據百分比 |
|
Extra |
SQL執行的額外信息 |
這些查詢列大家先留一個印象，后續會詳細講解。
4.Explain返回列詳解
接下來我們將展示Explain中每個列的信息
1. id列：每個select都有一個對應的id號，并且是從1開始自增的。
- 如果id序號相同，從上往下執行。
- 如果id序號不同，序號大先執行。
- 如果兩種都存在，先執行序號大，在同級從上往下執行。
- 如果顯示NULL，最后執行。表示結果集，并且不需要使用它來進行查詢。
explain
SELECT users.name, orders.total_price, products.price
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN products ON orders.product_id = products.id;
explain
select * from orders where product_id = (select id from products where products.price = 10);
set session optimizer_switch='derived_merge=off'; #關閉MySQL5.7對衍生表合并優化
explain
select orders.*
from (select id from products) as temp inner join orders on temp.id = orders.product_id;
set session optimizer_switch='derived_merge=on'; #還原配置
explain
select id from users
union
select id from products;
優化器會針對子查詢進行一定的優化重寫SQL：
EXPLAIN select * from users WHERE id in (select user_id from orders where id = 1);
show WARNINGS;
2.select_type列：表示查詢語句執行的查詢操作類型
2.1.simple：簡單select，不包括union與子查詢
Explain select * from users;
Explain select * from users inner join orders on users.id = orders.user_id;
2.2.primary：復雜查詢中最外層查詢，比如使用union或union all時，id為1的記錄select_type通常是primary
explain
select id from users
union
select id from products;
2.3.subquery：指在 select 語句中出現的子查詢語句,結果不依賴于外部查詢（不在from語句中）
explain
select orders.*,(select name from products where id = 1) from orders;
2.4.dependent subquery：指在 select 語句中出現的查詢語句，結果依賴于外部查詢
explain
select orders.*,(select name from products where products.id = orders.user_id) from orders;
2.5.derived：派生表，在FROM子句的查詢語句，表示從外部數據源中推導出來的，而不是從 SELECT 語句中的其他列中選擇出來的。
set session optimizer_switch='derived_merge=off'; #關閉MySQL5.7對衍生表合并優化
explain
select * from (select user_id from orders where id = 1) as temp;
set session optimizer_switch='derived_merge=on'; #還原配置
2.6.union：分union與union all兩種，若第二個select出現在union之后，則被標記為union；如果union被from子句的子查詢包含，那么第一個select會被標記為derived；union會針對相同的結果集進行去重，union all不會進行去重處理。
explain
select * from (
select id from products where price = 10
union
select id from orders where user_id in (1,2)
union
select id from users where name = '張三' ) as temp;
explain
select * from (
select id from products where price = 10
union all
select id from orders where user_id in (1,2)
union all
select id from users where name = '張三' ) as temp;
2.7.dependent union：當union作為子查詢時，其中第一個union為dependent subquery，第二個union為dependent union。
explain
select * from orders where id in (
select id from products where price = 10
union
select id from orders where user_id = 2
union
select id from users where name = '張三' );
2.8.union result：如果兩個查詢中有相同的列，則會對這些列進行重復刪除，只保留一個表中的列。
explain
select id from users
union
select id from products;
3.table列：查詢所涉及的表名。如果有多個表，將顯示多行記錄
4.partitions列：表分區情況
查詢語句所涉及的表的分區情況。具體來說，它會顯示出查詢語句在哪些分區上執行，以及是否使用了分區裁剪等信息。如果沒有分區，該項為NULL。
5.type列：查詢所使用的訪問類型
效率從高到低分別為：system > const > eq_ref > ref > fulltext > ref_or_null > range > index > ALL，一般來說保證range級別，最好能達到ref級別。
5.1.system：const類型的一種特殊場景，查詢的表只有一行記錄的情況，并且該表使用的存儲引擎的統計數據是精確的
InnoDb存儲引擎的統計數據不是精確的，雖然只有一條數據但是type類型為ALL；
DROP TABLE t;
CREATE TABLE t(i INT) ENGINE=InnoDb;
INSERT INTO t VALUES(1);
explain select * from t;
Memory存儲引擎的統計數據是精確的，所以當只有一條記錄的時候type類型為system。
DROP TABLE tt;
CREATE TABLE tt(i INT) ENGINE=memory;
INSERT INTO tt VALUES(1);
explain select * from tt;
5.2.const：基于主鍵或唯一索引查看一行，當MySQL對查詢某部分進行優化，并轉換為一個常量時，使用這些類型訪問轉換成常量查詢，效率高
explain
select * from orders where id = 1;
5.3.eq_ref：基于主鍵或唯一索引連接兩個表，對于每個索引鍵值，只有一條匹配記錄，被驅動表的類型為'eq_ref'
explain
select users.* from users inner join orders on users.id = orders.id;
5.4.ref：基于非唯一索引連接兩個表或通過二級索引列與常量進行等值匹配，可能會存在多條匹配記錄
1.關聯查詢，使用非唯一索引進行匹配。
explain
select users.* from users inner join orders on users.id = orders.user_id;
2.簡單查詢，使用二級索引列匹配。
explain
select * from orders where user_id = 1;
5.5.range：使用非唯一索引掃描部分索引，比如使用索引獲取某些范圍區間的記錄
explain
select * from orders where user_id > 3;
5.6.index：掃描整個索引就能拿到結果，一般是二級索引，這種查詢一般為使用覆蓋索引（需優化，縮小數據范圍）
explain
select user_id from orders;
5.7.all：掃描整個表進行匹配，即掃描聚簇索引樹（需優化，添加索引優化）
explain
select * from users;
5.8.NULL：MySQL在優化過程中分解語句就已經可以獲取到結果，執行時甚至不用訪問表或索引。
explain
select min(id) from users;
6.possible_keys列：表示在查詢中可能使用到某個索引或多個索引；如果沒有選擇索引，顯示NULL
7.key列：表示在查詢中實際使用的索引，如果沒有使用索引，顯示NULL。
8.key_len列：表示當優化器決定使用某個索引執行查詢時，該索引記錄的最大長度（主要使用在聯合索引）
聯合索引可以通過這個值算出具體使用了索引中的哪些列。
使用單例索引：
explain
select * from users where id = 1;
使用聯合索引：
explain
select * from users where name = '張三' and email = 'zhangsan@example.com';
計算規則：
- 字符串：
char(n)：n個字節
varchar(n)：如果是uft-8：3n+2字節，加的2個字節存儲字符串長度。如果是utf8mb4：4n+2字節。
- 數值類型：
tinyint：1字節
smaillint：2字節
int：4字節
bigint：8字節
- 時間類型：
date：3字節
timestamp：4字節
datetime：8字節
字段如果為NULL，需要1個字節記錄是否為NULL
9.ref列：表示將哪個字段或常量和key列所使用的字段進行比較。
當使用索引列等值查詢時，與索引列進行等值匹配的對象信息。
1.常量：
explain
select * from users where name = '張三' and email = 'zhangsan@example.com';
2.字段：
explain
select users.* from users inner join orders on users.id = orders.id;
3.函數
explain
select users.* from users inner join orders on users.id = trim(orders.id);
10.rows列：全表掃描時表示需要掃描表的行數估計值；索引掃描時表示掃描索引的行數估計值；值越小越好（不是結果集中的行數）
1.全表掃描
explain
select * from orders where user_id >= 3 and total_price = 25;
2.索引掃描
explain
select * from orders where user_id > 3;
11.filtered列：表示符合查詢條件的數據百分比?？梢允褂胷ows * filtered/100計算出與explain前一個表進行連接的行數。
前一個表指 explain 中的id值比當前表id值小的表，id相同的時候指后執行的表。
explain
select users.* from users inner join orders on users.id = orders.id;
12.Extra列：SQL執行查詢的一些額外信息
12.1.Using Index：使用非主鍵索引樹就可以查詢所需要的數據。一般是覆蓋索引，即查詢列都包含在輔助索引樹葉子節點中，不需要回表查詢。
explain
select user_id,id from orders where user_id = 1;
12.2.Using where：不通過索引查詢所需要的數據
explain
select * from orders where total_price = 100;
explain
select * from orders where user_id = 1 and total_price = 100;
12.3.Using index condition：表示查詢列不被索引覆蓋，where 條件中是一個索引范圍查找，過濾完索引后回表找到所有符合條件的數據行。
explain
select * from orders where user_id > 3;
12.4.Using temporary：表示需要使用臨時表來處理查詢；
1.total_price列無索引，需要創建一張臨時表進行去重
explain
select distinct total_price from orders;
2.name列有聯合索引
explain
select distinct name from users;
12.5.Using filesort：當查詢中包含 order by 操作而且無法利用索引完成的排序操作，數據較少時從內存排序，如果數據較多需要在磁盤中排序。 需優化成索引排序。
1.total_price列無索引，無法通過索引進行排序。需要先保存total_price與對應的主鍵id，然后在排序total_price查找數據。
explain
select total_price from orders order by total_price;
2.name列有索引，因索引已經是排好序的所以直接讀取就可以了。
explain
select name from users order by name;
12.6.Select tables optimized away：使用某些聚合函數（min,max）來訪問某個索引值。
explain
select min(id) from users;
explain
select min(password) from users;
5.索引優化最佳實踐
示例表：
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',
`remark` varchar(255) DEFAULT NULL COMMENT '備注',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (2, '張三', 18, 'beijing', '2023-06-11 20:51:35', '測試');
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (3, '張三', 23, 'shanghai', '2023-06-11 20:51:35', '測試2');
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (1, '李四', 20, 'shanghai', '2023-06-11 20:51:35', '測試3');
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (5, '王五', 19, 'beijing', '2023-06-12 14:32:15', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (4, '趙六', 22, 'shenzheng', '2023-06-12 14:33:00', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (6, '趙六', 24, 'beijing', '2023-06-12 14:37:50', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (7, '劉七', 20, 'shanghai', '2023-06-12 14:38:27', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (8, '劉七', 22, 'beijing', '2023-06-12 14:38:41', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (9, '王九', 9, 'shanghai ', '2023-06-12 14:40:17', NULL);
1.全值匹配
MySQL全值匹配是指在使用復合索引時，查詢條件要包含索引的所有列，才能最大程度地利用索引。
EXPLAIN SELECT * FROM employees WHERE name= '張三';
查看索引長度是74=(3*24+2),可以算出聯合索引中只使用了name前綴索引.
EXPLAIN SELECT * FROM employees WHERE name= '張三' AND age = 18;
查看索引長度是78=(3*24+2)+4,可以算出聯合索引中只使用了name和age前綴索引.
EXPLAIN SELECT * FROM employees WHERE name= '張三' AND age = 18 AND position ='beijing';
查看索引長度是140=(3*24+2)+4+(3*20+2),可以算出聯合索引中只使用了完整的聯合索引
2.最左前綴法則
如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。
——帶頭大哥不能死，中間兄弟不能斷；
-- name 74 +age 78 + position 140
EXPLAIN SELECT * FROM employees WHERE name= '張三' and age = 18 AND position = 'beijing' ;
-- 帶頭大哥不能死
EXPLAIN SELECT * FROM employees WHERE age = 18 AND position = 'beijing';
-- 中間兄弟不能斷
EXPLAIN SELECT * FROM employees WHERE name= '張三' AND position = 'beijing';
3.不在索引列上做任何操作（計算、函數、（自動or手動）類型轉換），會導致索引失效而轉向全表掃描
EXPLAIN SELECT * FROM employees WHERE name = '張三';
EXPLAIN SELECT * FROM employees WHERE left(name,3) = '張三';
給hire_time增加一個普通索引：
ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
EXPLAIN select * from employees where date(hire_time) ='2018-09-30';
轉化為日期范圍查詢，有可能會走索引：
EXPLAIN select * from employees where hire_time >='2018-09-30 00:00:00' and hire_time <='2018-09-30 23:59:59';
還原最初索引狀態
ALTER TABLE `employees` DROP INDEX `idx_hire_time`;
4.存儲引擎不能使用索引中范圍條件右邊的列
范圍查詢會使后面字段無序，造成部分索引失效。
——范圍之后全失效；
EXPLAIN SELECT * FROM employees WHERE name= '張三' AND age = 18 AND position ='beijing';
EXPLAIN SELECT * FROM employees WHERE name= '張三' AND age > 18 AND position ='beijing';
5.盡量使用覆蓋索引（只訪問索引的查詢（索引列包含查詢列）），減少 select * 語句
——覆蓋索引不寫星；
EXPLAIN SELECT name,age FROM employees WHERE name= '張三' AND age = 18 AND position ='beijing';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
6.不等空值還有or，索引失效要少用；
mysql在使用不等于（！=或者<>），not in ，not exists 的時候無法使用索引會導致全表掃描
< 小于、 > 大于、 <=、>= 這些，mysql內部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引
EXPLAIN SELECT * FROM employees WHERE name != '張三';
is null,is not null 一般情況下也無法使用索引
EXPLAIN SELECT * FROM employees WHERE name is null
少用or或in，用它查詢時，mysql不一定使用索引，mysql內部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引，詳見范圍查詢優化
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';
7.Like百分寫最右
——Like百分寫最右
EXPLAIN SELECT * FROM employees WHERE name like '%三'
EXPLAIN SELECT * FROM employees WHERE name like '張%'
問題：解決like'%字符串%'索引不被使用的方法？
a）使用覆蓋索引，查詢字段必須是建立覆蓋索引字段
EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';
b）如果不能使用覆蓋索引則可能需要借助搜索引擎
8.字符串不加單引號索引失效
——VAR引號不可丟
EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;
9.范圍查詢優化
給年齡添加單值索引
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
explain select * from employees where age >=1 and age <=2000;
沒走索引原因：mysql內部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引。比如這個例子，可能是由于單次數據量查詢過大導致優化器最終選擇不走索引
優化方法：可以將大的范圍拆分成多個小范圍
explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;
還原最初索引狀態
ALTER TABLE `employees` DROP INDEX `idx_age`;
索引使用總結：
全值匹配我最愛，最左前綴要遵守；
帶頭大哥不能死，中間兄弟不能斷；
索引列上少計算，范圍之后全失效；
Like百分寫最右，覆蓋索引不寫星；
不等空值還有or，索引失效要少用；
VAR引號不可丟，SQL高級也不難！
6. trace工具用法：
trace工具用法：
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; --開啟trace
mysql> select * from employees where name > 'a' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看trace字段：
{
"steps": [
{
"join_preparation": { --第一階段：SQL準備階段，格式化sql
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { --第二階段：SQL優化階段
"select#": 1,
"steps": [
{
"condition_processing": { --條件處理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ --表依賴詳情
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ --預估表的訪問成本
{
"table": "`employees`",
"range_analysis": {
"table_scan": { --全表掃描情況
"rows": 10123, --掃描行數
"cost": 2054.7 --查詢成本
} /* table_scan */,
"potential_range_indexes": [ --查詢可能使用的索引
{
"index": "PRIMARY", --主鍵索引
"usable": false,
"cause": "not_Applicable"
},
{
"index": "idx_name_age_position", --輔助索引
"usable": true,
"key_parts": [
"name",
"age",
"position",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": { --分析各個索引使用成本
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"a < name" --索引使用范圍
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, --使用該索引獲取的記錄是否按照主鍵排序
"using_mrr": false,
"index_only": false, --是否使用覆蓋索引
"rows": 5061, --索引掃描行數
"cost": 6074.2, --索引使用成本
"chosen": false, --是否選擇該索引
"cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`employees`",
"best_access_path": { --最優訪問路徑
"considered_access_paths": [ --最終選擇的訪問路徑
{
"rows_to_scan": 10123,
"access_type": "scan", --訪問類型：為scan，全表掃描
"resulting_rows": 10123,
"cost": 2052.6,
"chosen": true, --確定選擇
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 10123,
"cost_for_plan": 2052.6,
"sort_cost": 10123,
"new_cost_for_plan": 12176,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`employees`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { --第三階段：SQL執行階段
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
結論：全表掃描的成本低于索引掃描，所以mysql最終選擇全表掃描
mysql> select * from employees where name > 'zzz' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看trace字段可知索引掃描的成本低于全表掃描，所以mysql最終選擇索引掃描
mysql> set session optimizer_trace="enabled=off"; --關閉trace
3、SQL改寫
1.1. 準備工作：
1.1.1. 創建student表：
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id int(10) NOT NULL AUTO_INCREMENT COMMENT '序號',
student_id INT NOT NULL COMMENT '學號',
name varchar(20) COMMENT '姓名',
department varchar(20) COMMENT '院系',
remarks varchar(400) COMMENT '備注',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
1.1.2. 創建scores表：
DROP TABLE IF EXISTS scores;
CREATE TABLE scores (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '序號',
student_id INT NOT NULL COMMENT '學號',
course_name VARCHAR(50) NOT NULL COMMENT '課程名稱',
score INT NOT NULL COMMENT '分數',
remarks varchar(400) COMMENT '備注'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
1.1.3. 添加索引：
ALTER TABLE student ADD index idx_name_department (name, department);
1.1.4. 插入數據：
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('劉零',1,'美術','備注0');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('鄭一',2,'土木','備注1');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('吳二',3,'數學','備注2');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('張三',4,'中文','備注3');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('李四',5,'英語','備注4');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('王五',6,'美術','備注5');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('錢六',7,'土木','備注6');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('孫七',8,'數學','備注7');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('趙八',9,'英語','備注8');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('周九',10,'數學','備注9');
BEGIN
DECLARE v_name VARCHAR(20);
DECLARE v_department VARCHAR(200);
DECLARE i INT DEFAULT 0;
DECLARE n INT DEFAULT 100000;
DECLARE v_max_id INT DEFAULT 1;
set autocommit = 0;
select max(id) into v_max_id from student;
REPEAT
set i = i + 1;
set v_max_id = v_max_id + 1;
set v_name = CONCAT('mock_name',i);
set v_department = CONCAT('mock_department',i);
INSERT INTO `student` (`student_id`,`name`,`department`,`remarks`) values (v_max_id,v_name,v_department,'mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks');
INSERT INTO `scores` (`student_id`,`course_name`,`score`,`remarks`) values (v_max_id,CONCAT('mock_Chinese',i),RAND()*(100-50)+50,'mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks');
INSERT INTO `scores` (`student_id`,`course_name`,`score`,`remarks`) values (v_max_id,CONCAT('mock_Math',i),RAND()*(100-50)+50,'mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks');
INSERT INTO `scores` (`student_id`,`course_name`,`score`,`remarks`) values (v_max_id,CONCAT('mock_English',i),RAND()*(100-50)+50,'mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks');
UNTIL i = n
END REPEAT;
COMMIT;
set autocommit = 1;
END
1.2. SQL優化最佳實踐：
1.2.1. 避免使用select *
阿里規范：
分析：
查看執行計劃，select * 走全表掃描，沒有用到任何索引，查詢效率非常低；查詢列都是索引列那么這些列被稱為覆蓋索引。這種情況下查詢的相關字段都能走索引，索引查詢的效率相對較高。
EXPLAIN select * from student where name like 'mock_name%';--不走索引
EXPLAIN select name, department from student where name like 'mock_name%';--走索引
通過show warnings語句查看查詢列*號替換成表所有字段。
EXPLAIN select * from student where name like 'mock_name%';--替換成表對應的字段
SHOW WARNINGS;
總結：
- 查詢時需要先將星號解析成表的所有字段然后在查詢，增加查詢解析器的成本；
- select * 查詢一般不走覆蓋索引會產生大量的回表查詢；
- 在實際應用中我們通常只需要使用某幾個字段，其他不需要使用的字段也查出來浪費CPU、內存資源；
- 文本數據、大字段數據數據傳輸增加網絡消耗。
1.2.2. 小表驅動大表
小表驅動大表就是指用數據量較小、索引比較完備的表，然后使用其索引和條件對大表進行數據篩選，從而減少數據計算量，提高查詢效率。比如說student表有30條數據，scores表有80w條數據。
EXPLAIN
select * from student left join scores on student.id = scores.student_id;
EXPLAIN
select * from scores left join student on student.id = scores.student_id;
Join Buffer（連接緩沖區）是優化器用于處理連接查詢操作時的臨時緩沖區。簡單來說當我們需要比較兩個或多個表的數據進行Join操作時，Join Buffer可以幫助MySQL臨時存儲結果，以減少磁盤讀取和CPU負擔，提高查詢效率。需要注意的是每個join都有一個單獨的緩沖區。
Block nested-loop join（BNL算法）會將驅動表數據加載到join buffer里面，然后再批量與非驅動表進行匹配；如果驅動表數據量較大，join buffer無法一次性裝載驅動表的結果集，將會分階段與被驅動表進行批量數據匹配，會增加被驅動表的掃描次數，從而降低查詢效率。所以開發中要遵守小表驅動大表的原則。
分階段匹配過程如下：
1、先把student表前15條數據讀到join buffer中。
2、然后用scores表去匹配join buffer中的前15條。
3、記錄下匹配結果。
4、清空join buffer。
5、再把student表后15條讀取join buffer中。
6、然后用scores表去匹配join buffer中的后15條。
7、記錄下匹配結果。
1.2.3. 用連接查詢代替子查詢
mysql需要在兩張表以上獲取數據的方式有兩種：第一種通過連表查詢獲取，第二種通過子查詢獲取。
模擬一個真實場景，同樣student表有30條數據，scores表有80w條數據，我們想查看學號小于15的學員各科分數信息：
ALTER TABLE scores ADD index idx_student_id (student_id);
EXPLAIN
SELECT
(SELECT student.NAME FROM student WHERE student.id = scores.student_id),
scores.course_name,
scores.score
FROM
scores;
因為子查詢需要執行兩次數據庫查詢，一次是外部查詢，一次是嵌套子查詢。因此，使用連接查詢可以減少數據庫查詢的次數，提高查詢的效率。
連接查詢可以更好地利用數據庫索引，提高查詢的性能。子查詢通常會使用臨時表或內存表，而連接查詢可以直接利用表上的索引。這意味著連接查詢可以更快地訪問表中的數據，減少查詢的資源消耗。
對于大型數據集，使用連接查詢通常比使用子查詢更高效。子查詢通常需要掃描整個表，而連接查詢可以利用索引加速讀取操作。
EXPLAIN
SELECT
student.NAME,
scores.course_name,
scores.score
FROM
student inner JOIN scores ON student.id = scores.student_id;
使用連接查詢可以更快地執行查詢操作，減少數據庫的負載，提高查詢的性能和效率。
1.2.4. 提升group by的效率
- 創建索引：如果你使用group by的列沒有索引，那么查詢可能會變得很慢。因此，可以創建一個或多個適當的索引來加速查詢。
select remarks from scores group by remarks;
添加索引前：
添加索引后：
- 調整查詢：查詢的寫法也會影響group by的效率?？梢試L試不使用子查詢或臨時表，或者可以使用JOIN或EXISTS來代替IN子查詢。
- 限制結果集的數量：如果你只需要查看一小部分結果，可以在查詢中添加LIMIT子句，以便只返回一定數量的結果。
1.2.5. 批量操作
批量插入或批量刪除數據，比如說現在需要將1w+數據插入到數據庫，大家是一條一條處理還是批量操作呢？建議是批量操作，逐個處理會頻繁的與數據庫交互，損耗性能。
反例：
for(Order order: list){
orderMapper.insert(order):
}
在循環中逐條插入數據。
insert into order(id,code,user_id) values(123,'001',100)
該操作需要多次請求數據庫，才能完成這批數據的插入。
但眾所周知，我們在代碼中，每次遠程請求數據庫，是會消耗一定性能的。而如果我們的代碼需要請求多次數據庫，才能完成本次業務功能，勢必會消耗更多的性能。
那么如何優化呢？
正例：
orderMapper.insertBatch(List<User> users):
提供一個批量插入數據的方法。
insert into order(id,code,user_id) values(123,'001',100),(124,'002',100),(125,'003',101);
這樣只需要遠程請求一次數據庫，sql性能會得到提升，數據量越多，提升越大。
但需要注意的是，不建議一次批量操作太多的數據，如果數據太多數據庫響應也會很慢。批量操作需要把握一個度，建議每批數據盡量控制在500以內。如果數據多于500，則分多批次處理。
1.2.6. 使用limit
- 提高查詢效率：一個查詢返回成千上萬的數據行，不僅占用了大量的系統資源，也會占用更多的網絡帶寬，影響查詢效率。使用LIMIT可以限制返回的數據行數，減輕了系統負擔，提高了查詢效率。
- 避免過度提取數據：對于大型數據庫系統，從數據庫中提取大量的數據可能會導致系統崩潰。使用LIMIT可以限制提取的數據量，避免過度提取數據，保護系統不受影響。
- 優化分頁查詢：分頁查詢需要查詢所有的數據才能進行分頁處理，這會浪費大量的系統資源和時間。使用LIMIT優化分頁查詢可以只查詢需要的數據行，縮短查詢時間，減少資源的浪費。
- 簡化查詢結果：有時我們只需要一小部分數據來得出決策，而不是整個數據集。使用LIMIT可以使結果集更加精簡和易于閱讀和理解。
限制行數非常有用，因為它可以提高查詢性能、減少處理需要的時間，并且只返回我們關心的列。
百萬級表Limit翻頁越往后越慢咋辦
為什么 offset 偏大之后 limit 查找會變慢？這需要了解 limit 操作是如何運作的，以下面這句查詢為例：
select * from table_name limit 10000,10
這句 SQL 的執行邏輯是
- 1.從數據表中讀取第N條數據添加到數據集中
- 2.重復第一步直到 N = 10000 + 10
- 3.根據 offset 拋棄前面 10000 條數
- 4.返回剩余的 10 條數據
第一次優化
根據數據庫這種查找的特性，就有了一種想當然的方法，利用自增索引（假設為id）：
select * from table_name where (id >= 10000) limit 10
第二次優化
說起數據庫查詢優化，第一時間想到的就是索引，所以便有了第二次優化：先查找出需要數據的索引列（假設為 id），再通過索引列查找出需要的數據。
Select * From table_name Where id in (Select id From table_name where ( user = xxx )) limit 10000, 10;
select * from table_name where( user = xxx ) limit 10000,10
相比較結果是（500w條數據）：第一條花費平均耗時約為第二條的 1/3 左右。
同樣是較大的 offset，第一條的查詢更為復雜，為什么性能反而得到了提升？
這涉及到 mysql 主索引的數據結構 b+Tree ，這里不展開，基本原理就是：
- 子查詢只用到了索引列，沒有取實際的數據，所以不涉及到磁盤IO，所以即使是比較大的 offset 查詢速度也不會太差。
- 利用子查詢的方式，把原來的基于 user 的搜索轉化為基于主鍵（id）的搜索，主查詢因為已經獲得了準確的索引值，所以查詢過程也相對較快。
第三次優化
在數據量大的時候 in 操作的效率就不怎么樣了，我們需要把 in 操作替換掉，使用 join 就是一個不錯的選擇。
select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)
1.2.7. 用union all代替union
- union all：獲取所有數據但是數據不去重，包含重復數據；
- union：獲取所有數據且數據去重，不包含重復數據；
select id,name,department from student
union all
select id,student_id,score from scores
select id,name,department from student
union
select id,student_id,score from scores
那么union all與union如果當然它業務數據容許出現重復的記錄，我們更推薦使用union all，因為union去重數據需要遍歷、排序和比較，它更耗時，更消耗cpu資源，但是數據結果最完整。
1.2.8. join的表不宜過多
- 查詢效率下降：多表JOIN查詢數據對比時間邊長
- 系統負載增加：JOIN操作需要進行大量的計算，因此會導致系統負載增加。
- 維護難度加大：在一個連接了多個表的查詢中，如果需要修改其中一個表的結構或內容，就可能會需要同時修改其他表的結構或內容。
因此，在數據庫設計時，應該盡量減少JOIN操作的使用頻率，并且簡化表之間的關系，以提高查詢效率和系統的性能。
除上述優化之外，通常在建表還需要注意以下內容：
- 控制索引數量
- 選擇合理的字段類型
2. 總結
SQL優化是提高數據庫性能的重要方法，在實際開發中我們的SQL要盡量遵守以下幾點原則，避免留下技術債：
- 減少數據掃描
- 返回更少數據
- 減少交互次數
- 減少服務器CPU及內存開
