MySQL-10.索引優化與查詢優化_ZenDei技術網路在線

C-10.索引優化與查詢優化都有那些維度可以進行資料庫調優？簡言之：索引失效，沒有充分利用到索引 -- 索引建立關聯查詢太多JOIN(設計缺陷或不得已的需求) -- SQL優化伺服器調優及各個參數設置(緩衝，線程數等) -- 調整my.cnf 數據過多 -- 分庫分表關於資料庫調優的知識點 ...

C-10.索引優化與查詢優化

都有那些維度可以進行資料庫調優？簡言之：

索引失效，沒有充分利用到索引 -- 索引建立
關聯查詢太多JOIN(設計缺陷或不得已的需求) -- SQL優化
伺服器調優及各個參數設置(緩衝，線程數等) -- 調整my.cnf
數據過多 -- 分庫分表

關於資料庫調優的知識點非常分散。不同的DBMS，不同的公司，不同的職位，不同的項目遇到的問題都不盡相同。

雖然SQL查詢優化的技術有很多，但是大方向上完全可以分成物理查詢優化和邏輯查詢優化兩大塊。

物理查詢優化是通過索引和表連接方式等技術來進行優化，這裡重點需要掌握索引的使用。
邏輯查詢優化就是通過SQL等價變換提示查詢效率，直白一點就是說，換一種查詢寫法執行效率可能更高。

1.數據準備

學院表50萬條，班級表1萬條。

步驟1，建表。

CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步驟2，設置參數。

set global log_bin_trust_function_creators=1;#命令開啟：允許創建函數設置

步驟3，創建函數。

#隨機產生字元串
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 //
    
DELIMITER ;

#用於隨機產生多少到多少的編號 隨機產生班級編號
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 //
DELIMITER ;

步驟4：創建存儲過程

#創建往stu表中插入數據的存儲過程
DELIMITER //
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0; #設置手動提交事務
    REPEAT #迴圈
    SET i = i + 1; #賦值
    INSERT INTO student (stuno, name ,age ,classId ) VALUES
    ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
    UNTIL i = max_num
    END REPEAT;
    COMMIT; #提交事務
END //
DELIMITER ;

#假如要刪除
#drop PROCEDURE insert_stu;

#執行存儲過程，往class表添加隨機數據
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
    BEGIN
        DECLARE i INT DEFAULT 0;
        SET autocommit = 0;
        REPEAT
        SET i = i + 1;
        INSERT INTO class ( classname,address,monitor ) VALUES
        (rand_string(8),rand_string(10),rand_num(1,100000));
        UNTIL i = max_num
        END REPEAT;
        COMMIT;
    END //
DELIMITER ;
#假如要刪除
#drop PROCEDURE insert_class;

步驟5：調用存儲過程

#執行存儲過程，往class表添加1萬條數據
CALL insert_class(10000);

#執行存儲過程，往stu表添加50萬條數據
CALL insert_stu(100000,500000);

步驟6：創建刪除某表上索引存儲過程

#創建存儲過程
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=1來控制游標的結束
    DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
    #若沒有數據返回,程式繼續,並將變數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 //
DELIMITER ;

#執行存儲過程
CALL proc_drop_index("dbname","tablename");

2.索引失效案例

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

使用索引可以快速定位表中的某條數據，從而提高資料庫查詢的數據，提高資料庫的性能。
如果查詢沒有使用索引，查詢語句就會掃描表中的所有記錄。在數據量大的情況下，這樣查詢的速度會很慢。

大多數情況下（預設）採用B+樹來構建索引。只是空間列類型的索引使用R-樹，並且MEMORY表還支持hash索引。

其實，用不用索引，最終都是優化器說了算。優化器是基於什麼的優化器？基於cost開銷(CostBaseOptimizer)，它不是基於規則(Rule-BaseOptimizer)，也不是基於語義。怎樣開銷小，就怎麼來。另外，SQL語句是否使用索引，跟資料庫版本、數據量、數據選擇度都有關係。

2.1 全值匹配

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

建立索引前執行

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
Empty set, 1 warning (0.09 sec)

建立索引

#創建索引
CREATE INDEX idx_age ON student(age);

CREATE INDEX idx_age_classid ON student(age,classId);

CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

建立索引後執行

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
Empty set, 1 warning (0.00 sec)

2.2 最佳左首碼規則

在MySQL建立聯合索引時，會遵守最佳左首碼匹配原則，即最左優先，在檢索數據時從聯合索引的最左邊開始匹配。

#1.只能使用上 idx_age索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd' ;

#2.不能使用上索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = 'abcd';

#3.使用idx_age_classid_name索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE classid=4 AND student.age=30 AND student.name = 'abcd';

原因分析：

因為在構建聯合索引對應的B+樹時，每個頁(包括葉子和非葉子)中，的一條數據存儲的列的值的順序，是定義索引的順序，所以只有where子句中，存在聯合索引的最左側的列，才可能使用的上該聯合索引。

對於上述第一條SQL的分析，為什麼只能用idx_age索引，也是同理，對於idx_age_classid_name索引，age列和name列中有個classid列，如果只出現了age列，和name列，就會導致中間斷了。

也就是說，聯合索引(a,b,c)，最佳順序就是 a = ? and b = ? and c = ?。這樣是最完美的使用聯合索引的方式，但是只有a，c的話，就會導致無法使用該索引。因為使用a = ? 確定幾條數據後，必須使用b列去過濾數據了，但是此時where子句中沒有b的條件，所以就無法使用此索引樹，去確定滿足後麵條件的數據了，非要使用此索引的話，可能會導致回表次數過多，執行效率低。

對於第三條SQL，能使用上idx_age_classid_name索引的分析，因為有查詢優化器的存在，雖然在where子句中寫的條件的順序是，先classid 後age後name，但是查詢優化器，會做一個操作，嘗試將條件子句的順序，轉換成聯合索引定義的順序，從而完美的使用的聯合索引。

#刪除這兩個索引
DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid ON student;

#再次執行該語句
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd' ;
#這裡不展示具體執行結果，直接說結論
#使用上了idx_age_classid_name索引，但是key_len的長度是5
#而age列是int類型，且可以為null 4 + 1剛好是5B
#也就是說，該條語句只使用了聯合索引的age列進行過濾，然後根據對應的id值，進行回表，在use where進行過濾。符合上面的原因分析

結論：MySQL可以為一張表的多個欄位創建索引，一個索引可以包括16個欄位。對於多列索引，過濾條件要使用索引必須按照索引的建立順序，依次滿足，一但跳過某個欄位，索引後面的欄位都無法使用。如果查詢條件中沒有使用這些欄位中第1欄位時，聯合索引將失效。也即不會被使用。

擴展Alibaba《Java開發手冊》

索引文件具有B-Tree的最左匹配特性，如果左邊的之值未確定，那麼無法使用該索引。

2.3 主鍵插入順序

對於一個使用InnoDB存儲引擎的表來說，在我們沒有顯式的創建索引時，表中的數據實際上都是存儲在聚簇索引的葉子節點的。而記錄又是存儲在數據頁中的，數據頁和記錄又是按照記錄主鍵值從小到大的順序進行排序，所以如果我們插入的記錄的主鍵值是依次增大的話，那我們每插滿一個數據頁就換到下一個數據頁繼續插，而如果我們插入的主鍵值忽大忽小的話，就比較麻煩了，假設某個數據頁存儲的記錄已經滿了，它存儲的主鍵值在1~100之間:

如果此時再插入一條主鍵為9的記錄，那它插入的位置就如下圖：

可這個數據頁已經滿了，再插進來咋辦呢？我們需要把當前頁面分裂成兩個頁面，把本頁中的一些記錄移動到新創建的這個頁中。頁面分裂和記錄移位意味著什麼？意味著：性能損耗！所以如果我們想儘量避免這樣無謂的性能損耗，最好讓插入的記錄的主鍵值依次遞增，這樣就不會發生這樣的性能損耗了。所以我們建議：讓主鍵具有AUTO_INCREMENT，讓存儲引擎自己為表生成主鍵，而不是我們手動插入，

我們自定義的主鍵列 id 擁有AUTO_INCREMENT屬性，在插入記錄時存儲引擎會自動為我們填入自增的主鍵值。這樣的主鍵占用空間小，順序寫入，減少頁分裂。

2.4 計算、函數導致索引失效

CREATE INDEX idx_name ON student(`name`);

#此語句比下一條要好！（能夠使用上索引）
#執行結果使用上索引 idx_name
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

#未使用索引 type列的值是ALL 全表掃描
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
#原因，在使用完函數後，MySQL只能根據函數的結果，去和給定的值，對比。所以無法使用上索引

CREATE INDEX idx_sno ON student(stuno);

#未使用索引 type列的值是ALL 全表掃描
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
#原因，在進行計算後，MySQL只能根據計算的結果，去和給定的值，對比。所以無法使用上索引

#執行結果使用上索引 idx_sno
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

2.5 類型轉換導致索引失效

#無法使用idx_name索引 type列是ALL 全表掃描
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123; 
#相當於，MySQL，對於類型不匹配的，會嘗試使用隱式的函數轉換成目標類型，這樣就會導致無法使用索引。

#使用idx_name索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123';

2.6 範圍條件右邊的列索引失效

右邊，是指在聯合索引的列的右邊的列，而不是where子句中的右邊的列無法被使用。

SHOW INDEX FROM student;

#根據資料庫和表名刪除除主鍵外的索引
CALL proc_drop_index('atguigudb3','student');
#創建聯合索引 
CREATE INDEX idx_age_classId_name ON student(age,classId,`name`);

#會使用上idx_age_classId_name 但是key_len的長度是10
#age和classId都是int類型，都可以為null 所以是 4 + 1 + 4 + 1=10B 也就是在聯合索引中，未使用上name列

#1.
EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.classId > 20 AND student.name = 'abc' ; 

#創建一個age,name,classId的索引
CREATE INDEX idx_age_name_cid ON student(age,`name`,classId);

#可以使用idx_age_name_cid索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abc' AND student.classId>20; 
#此時執行第一條語句，也會使用idx_age_name_cid，因為mysql會自動改變條件條件子句的順序

應用開發中範圍查詢，例如：金額查詢，日期查詢往往都是範圍查詢。應該把查詢條件放在where語句最後。(創建的聯合索引中，務必把範圍涉及到的欄位寫在最後)

2.7 不等於(!= 或 <>)索引失效

CREATE INDEX idx_name ON student(NAME);

#不能使用idx_name索引 type列的值是ALL
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;

#不能使用idx_name索引 type列的值是ALL
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;

2.8 is null可以使用索引，is not null無法使用索引

#可以使用idx_age_classId_name索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL; 

#無法使用索引 type列的值是ALL
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

結論:最好在設計數據表的時候就將欄位設置為 NOT NULL約束，比如你可以將INT類型的欄位預設值設置為0。將字元類型的預設值設置為空字元串('‘)。
拓展:同理，在查詢中使用not like 也無法使用索引，導致全表掃描。

2.9 like以通配符%開頭索引失效

在使用LIKE關鍵字進行查詢的sql中，如果匹配字元串"%"開頭的，索引就會失效。只有"%"不在第一個位置，索引才會起作用。

#使用idx_name索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%'; 

#無法使用idx_name索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';

拓展：Alibaba《Java開發手冊》

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

2.10 OR前後存在非索引的列，索引失效

在WHERE子句中，如果在OR前的條件列進行了索引，而在OR後的條件列沒有進行索引，那麼索引會失效。也就是說，OR前後的兩個條件中的列都是索引時，查詢中才使用索引。

因為OR的含義就是兩個只要滿足一個即可，因此只有一個條件列進行了索引是沒有意義的，只要有條件列沒有進行索引，就會進行全表掃描，因此索引的條件列也會失效。

查詢語句使用OR關鍵字的情況:

CALL proc_drop_index('atguigudb3','student');

CREATE INDEX idx_age ON student(age);

#type列的值是ALL
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

CREATE INDEX idx_cid ON student(classid);
#建立該索引後，執行，type 值是index_merge索引合併

2.11 資料庫和表的字元集統一使用utf8mb4

統一使用utf8mb4( 5.5.3版本以上支持)相容性更好，統一字元集可以避免由於字元集轉換產生的亂碼。不同的字元集進行比較前需要進行轉換會造成索引失效。

2.12 建議

一般性建議:

對子單列索引，儘量選擇針對當前query過濾性更好的索引。
在選擇組合索引的時候，當前query中過濾性最好的欄位在索引欄位順序中，位置越靠前越好。
在選擇組合索引的時候，儘量選擇能夠包含當前query中的where子句中更多欄位的索引。
在選擇組合索引的時候，如果某個欄位可能出現範圍查詢時，儘量把這個欄位放在索引次序的最後面。

總之，書寫SQL語句時，儘量避免造成索引失效的情況。

3.關聯查詢優化

3.1 數據準備

#分類
CREATE TABLE IF NOT EXISTS `type` (
`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`)
);

#向分類表中添加20條記錄
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(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)));

3.2 左外連接

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card

指向結果看，type都是ALL

添加索引優化

CREATE INDEX idx_book_card ON book(card); #被驅動表建立索引，避免全表掃描

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

可以看到第二行的 type 變為了 ref，rows 也變成了1優化比較明顯。這是由左連接特性決定的。LEFT JOIN條件用於確定如何從右表搜索行，左邊一定都有，所以右邊是我們的關鍵點,一定需要建立索引。這是因為，在外連接中的特性是，左外連接中，左表是主表，左表中的數據是一定要保存的，所以，就必須對左表進行全表掃描。而從表的連接欄位建立索引的話，就可以使用索引，去優化使用主表的數據，在從表中查詢的這一步驟。

CREATE INDEX idx_type_card ON `type`(card); #驅動表的連接列，建立索引

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

從結果看，type表雖然也使用了，索引，但是rows的行數是20，也就是說，還是相當於掃描了全表，不過使用索引優化了這一步。

DROP INDEX idx_book_card ON book;#移除被驅動表card列索引

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

被驅動表book變回了ALL全表掃描。

3.3 採用內連接

DROP INDEX idx_type_card ON type;#移除type表的card列索引

使用inner join，內連接，沒有主從表之分。由select查詢優化器自己根據查詢成本，選擇驅動表和被驅動表。

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

CREATE INDEX idx_book_card ON book(card);#book表添加card列索引，優化

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;

CREATE INDEX idx_type_card ON type(card);#type表添加card列索引，優化

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;

DROP INDEX idx_type_card ON book;

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;

結論：對於內連接來講，如果表的連接條件中只能有一個欄位有索引，則有索引的欄位所在的表會被作為被驅動表出現。

#再次添加book表card列的索引
CREATE INDEX idx_book_card ON book(card);

#向type表中添加數據（20條數據）
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

結論：對於內連接來說，在兩個表的連接條件都存在索引的情況下，會選擇小表作為驅動表。“小表驅動大表”。

3.4 JOIN語句原理

join方式連接多個表，本質就是各個表之間數據的迴圈匹配。MySQL5.5版本之前，MySQL只支持一種表間關聯方式，就是嵌套迴圈(Nested Loop Join)。如果關聯表的數據量很大，則join關聯的執行時間會非常長。在MySQL5.5以後的版本中，MySQL通過引入BNLJ演算法來優化嵌套執行。

1.驅動表和被驅動表

內連接

SELECT * FROM A JOIN B ON ...

A一定是驅動表嗎？不一定，優化器會根據你查詢語句做優化，決定先查哪張表。先查詢的表就是驅動表，反之就是被驅動表。使用explain關鍵字查看。

外連接

SELECT * FROM A LEFT JOIN B ON ...
#或
SELECT * FROM A RIGHT JOIN B ON ...

#4)JOIN的底層原理

CREATE TABLE a(f1 INT, f2 INT, INDEX(f1))ENGINE=INNODB;

CREATE TABLE b(f1 INT, f2 INT)ENGINE=INNODB;


INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);

INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);


#測試1
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);

結論，從結果看，b是驅動表，a是被驅動表。這是底層將sql語句改寫成內連接，這是因為WHERE (a.f2=b.f2) a,b都是只有兩個欄位，都相等就是內連接。所以，外連接也不一定主表就是驅動表。當然很少上述這種情況。

2.Simple Nested-Loop Join(簡單嵌套迴圈連接)

在連接條件上都無索引的情況下，演算法相當簡單，從表A中取一條數據，遍歷B表，將匹配成功的記錄，當到臨時表，以此類推，驅動表A的每一張表與被驅動表進行判斷。

這種方式效率很低，上述表A數據100條，B數據1000條計算，A*B=10萬次。

A代表A表的條數，B代表B表的條數(條數是指，滿足ON條件且滿足WHERE條件的行數)

開銷統計	SNLJ
外表掃描次數	1
內表掃描次數	A
讀取記錄數	A + B*A
JOIN比較次數	B * A
回表讀取記錄次數	0

當然mysql不會使用這中方式進行表的連接，後面出現了Nested-Loop Join優化演算法。

3.Index Nested-Loop Join(索引嵌套迴圈連接)

Index Nested-Loop Join其優化的思路主要是為了減少內層表數據的匹配次數，所以要求被驅動表上的連接條件列上必須有索引才行。通過外層表匹配條件直接與內層表索引進行匹配，避免和內層表的每條記錄去進行比較，這樣極大的減少了對內層表的匹配次數。

驅動表中的每條記錄通過被驅動表的索引進行訪問，因為索引查詢的成本是比較固定的，故mysql優化器都傾向於使用記錄數少的表作為驅動表（外表)。

A代表A表的條數，B代表B表的條數(條數是指，滿足ON條件且滿足WHERE條件的行數)

開銷統計	SNLJ	INLJ
外表掃描次數	1	1
內表掃描次數	A	0
讀取記錄數	A + B*A	A + B(匹配)
JOIN比較次數	B * A	A*Index(索引數的層數)
回表讀取記錄次數	0	B(匹配的記錄條數)

4.Block Nested-Loop Join(塊嵌套迴圈連接)

如果存在索引，那麼會使用index的方式進行join，如果join的列沒有索引，被驅動表要掃描的次數太多了。每次訪問被驅動表，其表中的記錄都會被載入到記憶體中，然後再從驅動表中取一條與其匹配，匹配結束後清除記憶體，然後再從驅動表中載入一條記錄，然後把被驅動表的記錄在載入到記憶體匹配，這樣周而複始，大大增加了IO的次數。為了減少被驅動表的IO次數，就出現了Block Nested-Loop Join的方式。
不再是逐條獲取驅動表的數據，而是一塊一塊的獲取，引入了join buffer緩衝區，將驅動表join相關的部分數據列(大小受join buffer的限制)緩存到join buffer中，然後全表掃描被驅動表，被驅動表的每一條記錄一次性和join buffer中的所有驅動表記錄進行匹配(記憶體中操作)，將簡單嵌套迴圈中的多次比較合併成一次，降低了被驅動表的訪問頻率。

註意:

這裡緩存的不只是關聯表的列, select後面的列也會緩存起來。

在一個有N個join關聯的sql中會分配N-1個join buffer。所以查詢的時候儘量減少不必要的欄位，可以讓join buffer中可以存放更多的列。

驅動表中的每條記錄通過被驅動表的索引進行訪問，因為索引查詢的成本是比較固定的，故mysql優化器都傾向於使用記錄數少的表作為驅動表（外表)。

A代表A表的條數，B代表B表的條數(條數是指，滿足ON條件且滿足WHERE條件的行數)

開銷統計	SNLJ	INLJ	BNLJ
外表掃描次數	1	1	1
內表掃描次數	A	0	(A * used_column_size) / join_buffer_size + 1(如果能整除不加1)
讀取記錄數	A + B*A	A + B(匹配)	A + B * (A * used_column_size / join_buffer_size)
JOIN比較次數	B * A	A*Index(索引數的層數)	B * A
回表讀取記錄次數	0	B(匹配的記錄條數)	0

參數設置

block_nested_loop

SHOW VARIABLES LIKE '%optimizer_switch%';#指令查看預設是開啟的

join_buffer_size

驅動表能不能一次載入完，要看join buffer能不能存儲所有的數據，預設情況下join_buffer_size=256k。

mysql> SHOW VARIABLES LIKE 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.01 sec)

join_buffer_size的最大值在32位系統可以申請4G，而在64位操做系統下可以申請大於4G的Join Buffer空間(64位Windows除外，其大值會被截斷為4GB併發出警告)。

5.Join小結

1、整體效率比較:INLJ > BNLJ > SNLJ

2、永遠用小結果集驅動大結果集(其本質就是減少外層迴圈的數據數量) (小的度量單位指的是表行數*每行大小)

select t1.b, t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100;#推薦

#straight_join查詢優化器不對主表和從表做修改，左邊的一定是驅動表

select t1.b, t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;#不推薦

#上述原因，是因為選擇了查詢的列中，t1表只用了b列，而t2表用來所有的列，
#選擇t1做驅動表的話，通用的join_buffer_size大小下，存儲的t1表的條數就多，記憶體迴圈，也即是查詢被驅動表的次數就少

3、為被驅動表匹配的條件增加索引(減少內層表的迴圈匹配次數)

4、增大join buffer size的大小(一次緩存的數據越多，那麼內層包的掃表次數就越少)

5、減少驅動表不必要的欄位查詢(欄位越少，join buffer所緩存的數據就越多)

6.Hash Join

從MySQL的8.0.20版本開始將廢棄BNLJ，因為從MySQL8.0.18版本開始就加入了hash join預設都會使用hash join

Nested Loop:
對於被連接的數據子集較小的情況，Nested Loop是個較好的選擇。
Hash Join是做大數據集連接時的常用方式，優化器使用兩個表中較小(相對較小）的表利用Join Key在記憶體中建立散列表，然後掃描較大的表並探測散列表，找出與Hash表匹配的行。
- 這種方式適用於較小的表完全可以放於記憶體中的情況，這樣總成本就是訪問兩個表的成本之和。
- 在表很大的情況下並不能完全放入記憶體，這時優化器會將它分割成若幹不同的分區，不能放入記憶體的部分就把該分區寫入磁碟的臨時段，此時要求有較大的臨時段從而儘量提高I/O的性能。
- 它能夠很好的工作於沒有索引的大表和並行查詢的環境中，並提供最好的性能。大多數人都說它是Join的重型升降機。Hash Join只能應用於等值連接(如WHERE A.COL1=B.COL2)，這是由Hash的特點決定的。

3.5 小結

保證被驅動表的JOIN欄位已經創建了索引。
需要JOIN的欄位，數據類型保持絕對一致。
LEFT JOIN時，選擇將小表作為驅動表，大表作為被驅動表。減少外層迴圈的次數。
INNER JOIN時，MySQL會自動將小結果集的表選為驅動表。選擇相信MySQL的優化策略。
能夠直接多表關聯的儘量直接關聯，不用子查詢。(減少查詢的次數)
不建議使用子查詢，建議將子查詢SQL拆開結合程式多次查詢，或使用JOIN來替代子查詢。
衍生表建不了索引。

4.子查詢優化

MySQL從4.1版本開始支持子查詢，使用子查詢可以進行SELECT語句的嵌套查詢，即一個SELECT查詢的結果作為另一個SELECT語句的條件。子查詢可以一次性完成很多邏輯上需要多個步驟才能完成的SQL操作。

子查詢是 MySQL 的一項重要的功能，可以幫助我們通過一個 SQL 語句實現比較複雜的查詢。但是，子查詢的執行效率不高。

原因：

① 執行子查詢時，MySQL需要為內層查詢語句的查詢結果建立一個臨時表，然後外層查詢語句從臨時表中查詢記錄。查詢完畢後，再撤銷這些臨時表。這樣會消耗過多的CPU和IO資源，產生大量的慢查詢。

② 子查詢的結果集存儲的臨時表，不論是記憶體臨時表還是磁碟臨時表都不會存在索引，所以查詢性能會受到一定的影響。

③ 對於返回結果集比較大的子查詢，其對查詢性能的影響也就越大。

在MySQ中，可以使用連接（JOIN）查詢來替代子查詢。連接查詢不需要建立臨時表，其速度比子查詢要快，如果查詢中使用索引的話，性能就會更好。

#創建班級表中班長的索引
CREATE INDEX idx_monitor ON class(monitor);

#查詢班長的信息
EXPLAIN SELECT * FROM student a
WHERE a.`stuno` IN (
SELECT monitor
FROM class c
WHERE monitor IS NOT NULL
);

#子查詢轉成多表聯查
EXPLAIN SELECT a.* FROM student a JOIN class c 
ON a.`stuno` = c.`monitor`
WHERE c.`monitor` IS NOT NULL;

#查詢不為班長的信息
EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` NOT IN (
SELECT monitor
FROM class c
WHERE monitor IS NOT NULL
);

EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a LEFT OUTER JOIN class b 
ON a.`stuno` = b.`monitor`
WHERE b.`monitor` IS NULL;

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

5.排序優化

5.1 排序優化

問題：在WHERE條件欄位上加索引但是為什麼在ORDER BY欄位上還要加索引呢？

回答：

在MySQL中，支持兩種排序方式，分別是FileSort和Index排序。

lndex排序中，索引可以保證數據的有序性，不需要再進行排序，效率更高。
FileSort 排序則一般在記憶體中進行排序，占用CPU較多。如果待排結果較大，會產生臨時文件I/O到磁碟進行排序的情況，效率較低。

優化建議：

1.SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表掃描，在 ORDER BY 子句避免使用 FileSort 排序。當然，某些情況下全表掃描，或者 FileSort 排序不一定比索引慢。但總的來說，我們還是要避免，以提高查詢效率。
2.儘量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 後面是相同的列就使用單索引列；如果不同就使用聯合索引。
3.無法使用 Index 時，需要對FileSort方式進行調優。

5.2 測試

#刪除class表和student表的非主鍵索引
CALL proc_drop_index('atguigudb3','class');

CALL proc_drop_index('atguigudb3','student');


SHOW INDEX FROM class;

SHOW INDEX FROM student;

#過程一：
#無索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 
#無索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10; 

#過程二：order by時不limit，索引失效
#創建索引  
CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);


#不限制,索引失效 是因為，沒有limit限制，對錶的每一條數據都需要回表 
#MySQL優化器覺得使用上索引 成本太高，不如filesort
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 

#覆蓋索引 不用回表 MySQL優化器覺得使用上索引 成本低
#EXPLAIN  SELECT SQL_NO_CACHE age,classid FROM student ORDER BY age,classid;

#增加limit過濾條件，使用上索引了。只用對10條，回表，優化器覺得成本低，可以使用索引
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

#過程三：order by時順序錯誤，索引失效

#創建索引age,classid,stuno
CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno); 

#以下哪些索引失效?
EXPLAIN  SELECT * FROM student ORDER BY classid LIMIT 10;#×

EXPLAIN  SELECT * FROM student ORDER BY classid,NAME LIMIT 10; #× 

EXPLAIN  SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10;#√ 

EXPLAIN  SELECT * FROM student ORDER BY age,classid LIMIT 10;#√ 

EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10;#√ 

#過程四：order by時規則不一致, 索引失效 （順序錯，不索引；方向反，不索引）
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;#× 

EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;#× 

EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10;#× 

#這個可以使用上索引，因為在索引樹中都是asc，而order by中都是降序的，反而可以使用上，倒著查詢索引即可了
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

#過程五：無過濾，不索引

#先過濾，在排序，即便組合索引中的列，在order by中，但是可能也是用不上
#這是因為優化器覺得，age=const(常量)過濾後，就只有很少的行數據，再進行索引的查詢和回表，還不如
#直接filesort
#這裡，本來應該使用上索引idx_age_classid_name 但是再explain的結果中key_len列是5
#即是，只是用到了age列的索引，因為正常age 是可以為null索引長度4 + 1 + classid可以為null + 4 + 1
# 加上name列可以為null 20 * 3 + 1 + 2(變長欄位，描述真實的長度信息的位元組空間) = 4 +1 + 4 +1 + 63 = 73
EXPLAIN  SELECT * FROM student WHERE age = 45 ORDER BY classid;

EXPLAIN  SELECT * FROM student WHERE  age = 45 ORDER BY classid,NAME; 

#使用不上索引
EXPLAIN  SELECT * FROM student WHERE  classid = 45 ORDER BY age;
#使用上了索引idx_age_classid_name 原因同上，不加limit需要回表太多，不如全表掃描
EXPLAIN  SELECT * FROM student WHERE  classid = 45 ORDER BY age LIMIT 10;

CREATE INDEX idx_cid ON student(classid);

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;

#實戰：測試filesort和index排序
CALL proc_drop_index('atguigudb3','student');

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

#方案一: 為了去掉filesort我們可以把索引建成

CREATE INDEX idx_age_name ON student(age,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

#方案二：

CREATE INDEX idx_age_stuno_name ON student(age,stuno,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

DROP INDEX idx_age_stuno_name ON student;

CREATE INDEX idx_age_stuno ON student(age,stuno);

#以上只是sql語句和對於執行計劃的解釋，並未展示結果，

5.3 案例實戰

#實戰：測試filesort和index排序
CALL proc_drop_index('atguigudb3','student');

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

#方案一: 為了去掉filesort我們可以把索引建成

CREATE INDEX idx_age_name ON student(age,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

#方案二：

CREATE INDEX idx_age_stuno_name ON student(age,stuno,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

DROP INDEX idx_age_stuno_name ON student;

CREATE INDEX idx_age_stuno ON student(age,stuno);

結論：

兩個索引同時存在，mysql自動選擇最優的方案。（對於這個例子，mysql選擇idx_age_stuno_name）。但是，隨著數據量的變化，選擇的索引也會隨之變化的。

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

5.4 filesort演算法：雙路排序和單路排序

filesort有兩種演算法

雙路排序（慢）

MySQL 4.1之前是使用雙路排序，字面意思就是兩次掃描磁碟，最終得到數據，讀取行指針和order by列，對他們進行排序，然後掃描已經排序好的列表，按照列表中的值重新從列表中讀取對應的數據輸出。
從磁碟取排序欄位，在buffer進行排序，再從磁碟取其他欄位。

單路排序（快）

從磁碟讀取查詢需要的所有列，按照order by列在buffer對它們進行排序，然後掃描排序後的列表進行輸出，它的效率更快一些，避免了第二次讀取數據。並且把隨機IO變成了順序IO，但是它會使用更多的空間，因為它把每一行都保存在記憶體中了。

結論及引申出的問題

由於單路是後出的，總體而言好過雙路
但是用單路有問題
- 在sort_buffer中，單路比多路要多占用很多空間，因為單路是把所有欄位都取出,所以有可能取出的數據的總大小超出了sort_buffer的容量，導致每次只能取sort_buffer容量大小的數據，進行排序(創建tmp文件，多路合併)，排完再取sort_buffer容量大小，再排......從而多次l/O。
- 單路本來想省一次l/o操作，反而導致了大量的I/0操作，反而得不償失。

優化策略

1.嘗試提高sort_buffer_size

不管用哪種演算法，提高這個參數都會提高效率，要根據系統的能力去提高，因為這個參數是針對每個進程(connection)的1M-8M之間調整。MySQL5.7，InnoDB存儲引擎預設值是1048576位元組，1MB

mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.01 sec)

2.嘗試提高max_length_for_sort_data

提高這個參數，會增加用單路排序的概率。

mysql> show variables like 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 4096  |
+--------------------------+-------+
1 row in set (0.00 sec)

但是如果設的太高，數據總容量超出sort_buffer_size的概率就增大，明顯癥狀是高的磁碟Io活動和低的處理器使用率。如果需要返回的列的總長度大於max_length_for_sort_data，使用雙路演算法，否則使用單路演算法。1024-8192位元組之間調整

3. Order by 時select * 是一個大忌。最好只Query需要的欄位。

當Query的欄位大小總和小於max_length_for_sort_data，而且排序欄位不是TEXTIBLOB類型時，會用改進後的演算法――單路排序，否則用老演算法――多路排序。
兩種演算法的數據都有可能超出sort_buffer_size的容量，超出之後，會創建tmp文件進行合併排序導致多次I/O，但是用單路排序演算法的風險會更大一些，所以要提高sort_buffer_size。

6.GROUP BY優化

group by 使用索引的原則幾乎跟order by一致，group by 即使沒有過濾條件用到索引，也可以直接使用索引。
group by 先排序再分組，遵照索引建的最佳左首碼法則。
當無法使用索引列，增大max_length_for_sort_data和sort_buffer_size參數的設置。
where效率高於having，能寫在where限定的條件就不要寫在having中了。
減少使用order by，和業務溝通能不排序就不排序，或將排序放到程式端去做。Order by、groupby、distinct這些語句較為耗費CPU，資料庫的CPU資源是極其寶貴的。
包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結果集請保持在1000行以內，否則SQL會很慢。

7.優化分頁查詢

一般分頁查詢時，通過創建覆蓋索引能夠比較好地提高性能。一個常見又非常頭疼的問題就是limit 2000000,10，此時需要MySQL排序前2000000-2000010的記錄，其他記錄丟失，查詢排序的代價非常大。

mysql> explain select * from student limit 2000000,10;

優化思路一

在索引上完成排序分頁操作，最後根據主鍵關聯回原表查詢所需要的其他列內容

explain select * from student t,(select id from student order by id limit 2000000,10) a
where t.id = a.id;

優化思路二

該方案適用於主鍵自增的表，可以把Limit查詢轉換成某個位置的查詢。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

8.優先使用覆蓋索引

8.1 什麼是覆蓋索引？

理解方式一：索引是高效找到行的一種方法，但是一般資料庫也能使用索引找到一個列的數據，因此它不必讀取整個行。畢竟索引葉子節點存儲了它們索引的數據；當能通過讀取索引可以得到想要的數據，就不需要讀取行了。一個索引包含了滿足查詢結果的數據就叫做覆蓋索引。

索引方式二：非聚簇索引的一種形式，它包括在查詢里的SELECT，JOIN和WHERE子句用到的所有列(建索引的欄位正好是覆蓋查詢條件中所設計的欄位)。

簡單說，就是索引列+主鍵包含SELECT到FROM之間查詢的列。

舉例一

#刪除之間的索引
CALL proc_drop_index('atguigudb3','student');

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

EXPLAIN SELECT * FROM student WHERE age != 20;

#上面查詢失效案例，使用!=會導致索引失效，但從結果看，也使用了索引。這是因為覆蓋索引現象
#這樣也可以看出，上面講的只是一般情況下的基本規則，方便理解查詢優化器，但是也有特殊情況，
#因為是否使用索引，是由查詢優化器基於成本的計算來選擇的，所以要具體情況，具體分析。當然最好是，在業務代碼中使用
#sql語句時，先explain查看一下。
EXPLAIN SELECT age,name,id FROM student WHERE age != 20;

舉例二

#舉例2
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

#覆蓋索引
EXPLAIN SELECT id,age,NAME FROM student WHERE NAME LIKE '%abc';

8.2 覆蓋索引的利弊

好處：

1. 避免Innodb表進行索引的二次查詢（回表）

Innodb是以聚集索引的順序來存儲的，對於Innodb來說，二級索引在葉子節點中所保存的是行的主鍵信息，如果是用二級索引查詢數據，在查找到相應的鍵值後，還需通過主鍵進行二次查詢才能獲取我們真實所需要的數據。

在覆蓋索引中，二級索引的鍵值中可以獲取所要的數據，避免了對主鍵的二次查詢，減少了IO操作，提升了查詢效率。

2. 可以把隨機IO變成順序IO加快查詢效率

由於覆蓋索引是按鍵值的順序存儲的，對於IO密集型的範圍查找來說，對比隨機從磁碟讀取每一行的數據IO要少的多，因此利用覆蓋索引在訪問時也可以把磁碟的隨機讀取的IO轉變成索引查找的順序IO。

由於覆蓋索引可以減少樹的搜索次數，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優化手段。

弊端：

索引欄位的維護總是有代價的。因此，在建立冗餘索引來支持覆蓋索引時就需要權衡考慮了。這是業務DBA，或者稱為業務數據架構師的工作。

9.如何給字元串添加索引

有一張教師表，表定義如下：

create table teacher(
ID bigint unsigned primary key,
email varchar(64),
...
)engine=innodb;

教師要使用郵箱登錄，所以業務代碼中一定會出現類似於這樣的語句：

mysql> select col1, col2 from teacher where email='xxx';

如果email這個欄位上沒有索引，那麼這個語句就只能做全表掃描。

9.1 首碼索引

MySQL是支持首碼索引的。預設地，如果你創建索引的語句不指定首碼長度，那麼索引就會包含整個字元串。

mysql> alter table teacher add index index1(email);
#或
mysql> alter table teacher add index index2(email(6));

這兩種不同的定義在數據結構和存儲上有什麼區別呢？下圖就是這兩個索引的示意圖。

如果使用的是index1（即email整個字元串的索引結構），執行順序是這樣的：

1.從index1索引樹找到滿足索引值是’ [email protected] ’的這條記錄，取得ID2的值；

2.到主鍵上查到主鍵值是ID2的行，判斷email的值是正確的，將這行記錄加入結果集；

3.取index1索引樹上剛剛查到的位置的下一條記錄，發現已經不滿足email=' [email protected] ’的條件了，迴圈結束。

這個過程中，只需要回主鍵索引取一次數據，所以系統認為只掃描了一行。

如果使用的是index2（即email(6)索引結構），執行順序是這樣的：

1.從index2索引樹找到滿足索引值是’zhangs’的記錄，找到的第一個是ID1；

2.到主鍵上查到主鍵值是ID1的行，判斷出email的值不是’ [email protected] ’，這行記錄丟棄；

3.取index2上剛剛查到的位置的下一條記錄，發現仍然是’zhangs’，取出ID2，再到ID索引上取整行然後判斷，這次值對了，將這行記錄加入結果集；

4.重覆上一步，直到在idxe2上取到的值不是’zhangs’時，迴圈結束。

也就是說使用首碼索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查詢成本。前面已經講過區分度，區分度越高越好。因為區分度越高，意味著重覆的鍵值越少。見第八章，索引的創建和設計原則中的3.2小結中的9和10

9.2 首碼索引對覆蓋索引的影響

結論：

使用首碼索引就用不上覆蓋索引對查詢性能的優化了，這也是你在選擇是否使用首碼索引時需要考慮的一個因素。

10.索引條件下推(索引下推)

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一種在存儲引擎層使用索引過濾數據的一種優化方式。ICP可以減少存儲引擎訪問基表的次數以及MySQL伺服器訪問存儲引擎的次數。

10.1 使用前後的掃描過程

在不使用ICP索引掃描的過程：

storage層：只將滿足index key條件的索引記錄對應的整行記錄取出，返回給server層

server 層：對返回的數據，使用後面的where條件過濾，直至返回最後一行。

使用ICP掃描的過程：

storage層：

首先將index key條件滿足的索引記錄區間確定，然後在索引上使用index filter進行過濾。將滿足的index filter條件的索引記錄才去回表取出整行記錄返回server層。不滿足index filter條件的索引記錄丟棄，不回表、也不會返回server層。

server 層：

對返回的數據，使用table filter條件做最後的過濾。

使用前後的成本差別

使用前，存儲層多返回了需要被index filter過濾掉的整行記錄

使用ICP後，直接就去掉了不滿足index filter條件的記錄，省去了他們回表和傳遞到server層的成本。

ICP的加速效果取決於在存儲引擎內通過 ICP篩選掉的數據的比例。

註意，索引條件下推，一般是用於組合索引中，就是在同一個索引樹上，索引查詢時，只是使用了部分索引，但是where條件中，還使用了索引樹中的其他列，進行條件過濾，這時就先過濾條件，不直接根據部分索引的查詢結果，進行回表操作。過濾條件執行後，滿足的再進行回表操作。

10.2 ICP的使用條件

① 只能用於二級索引(secondary index)

②explain顯示的執行計劃中type值（join 類型）為range、ref、eq_ref或者ref_or_null。

③ 並非全部where條件都可以用ICP篩選，如果where條件的欄位不在索引列中，還是要讀取整表的記錄到server端做where過濾。

④ ICP可以用於MyISAM和InnnoDB存儲引擎

⑤ MySQL 5.6版本的不支持分區表的ICP功能，5.7版本的開始支持。

⑥ 當SQL使用覆蓋索引時，不支持ICP優化方法。因為這種情況下使用ICP不能較少IO。

⑦相關子查詢的條件不能使用ICP

10.3 開啟和關閉索引下推

set optimizer_switch = 'index_condition_pushdown=on'#開啟，關閉是off 預設是開啟狀態 不要關閉，因為確實會優化查詢效率

不在舉例，宋紅康老師，課程中有舉例，但我覺得，理解這個優化的設計思想就可以了。

11.普通索引 VS 唯一索引

從性能的角度考慮，你選擇唯一索引還是普通索引呢？選擇的依據是什麼呢？

假設，我們有一個主鍵列為ID的表，表中有欄位k，並且在k上有索引，假設欄位 k 上的值都不重覆。這個表的建表語句是：

mysql> create table test(
    id int primary key,
    k int not null,
    name varchar(16),
    index (k)
)engine=InnoDB;

表中R1~R5的(ID,k)值分別為(100,1)、(200,2)、(300,3)、(500,5)和(600,6)。

11.1 查詢過程

假設，執行查詢的語句是 select id from test where k=5。

對於普通索引來說，查找到滿足條件的第一個記錄(5,500)後，需要查找下一個記錄，直到碰到第一個不滿足k=5條件的記錄。
對於唯一索引來說，由於索引定義了唯一性，查找到第一個滿足條件的記錄後，就會停止

MySQL-10.索引優化與查詢優化

C-10.索引優化與查詢優化

1.數據準備

2.索引失效案例

2.1 全值匹配

2.2 最佳左首碼規則

2.3 主鍵插入順序

2.4 計算、函數導致索引失效

2.5 類型轉換導致索引失效

2.6 範圍條件右邊的列索引失效

2.7 不等於(!= 或 <>)索引失效

2.8 is null可以使用索引，is not null無法使用索引

2.9 like以通配符%開頭索引失效

2.10 OR前後存在非索引的列，索引失效

2.11 資料庫和表的字元集統一使用utf8mb4

2.12 建議

3.關聯查詢優化

3.1 數據準備

3.2 左外連接

3.3 採用內連接

3.4 JOIN語句原理

1.驅動表和被驅動表

2.Simple Nested-Loop Join(簡單嵌套迴圈連接)

3.Index Nested-Loop Join(索引嵌套迴圈連接)

4.Block Nested-Loop Join(塊嵌套迴圈連接)

5.Join小結

6.Hash Join

3.5 小結

4.子查詢優化

5.排序優化

5.1 排序優化

5.2 測試

5.3 案例實戰

5.4 filesort演算法：雙路排序和單路排序

雙路排序 （慢）

單路排序 （快）

結論及引申出的問題

優化策略

6.GROUP BY優化

7.優化分頁查詢

優化思路一

優化思路二

8.優先使用覆蓋索引

8.1 什麼是覆蓋索引？

舉例一

舉例二

8.2 覆蓋索引的利弊

9.如何給字元串添加索引

9.1 首碼索引

9.2 首碼索引對覆蓋索引的影響

10.索引條件下推(索引下推)

10.1 使用前後的掃描過程

10.2 ICP的使用條件

10.3 開啟和關閉索引下推

11.普通索引 VS 唯一索引

11.1 查詢過程

雙路排序（慢）

單路排序（快）