數(shù)據(jù)庫索引優(yōu)化與查詢優(yōu)化

哪些維度可以進(jìn)行數(shù)據(jù)庫調(diào)優(yōu)：

• 索引失效、沒有充分利用到索引-一索引建立
• 關(guān)聯(lián)查詢太多JOIN (設(shè)計(jì)缺陷或不得已的需求) --SQL優(yōu)化
• 服務(wù)器調(diào)優(yōu)及各個(gè)參數(shù)設(shè)置 (緩沖、線程數(shù)等)–調(diào)整my.cnf
• 數(shù)據(jù)過多–分庫分表

關(guān)于數(shù)據(jù)庫調(diào)優(yōu)的知識(shí)點(diǎn)非常分散。不同的 DBMS，不同的公司，不同的職位，不同的項(xiàng)目遇到的問題都不盡相
同。這里我們分為三個(gè)章節(jié)進(jìn)行細(xì)致講解。

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

• 物理查詢優(yōu)化是通過 索引和 表連接方式 等技術(shù)來進(jìn)行優(yōu)化，這里重點(diǎn)需要掌握索引的使用。
• 邏輯查詢優(yōu)化就是通過 SOL 等價(jià)變換 提升查詢效率，直白一點(diǎn)就是說，換一種查詢寫法執(zhí)行效率可能更高

索引失效案例

MySQL中提高性能的一個(gè)最有效的方式是對(duì)數(shù)據(jù)表設(shè)計(jì)合理的索引。索引提供了高效訪問數(shù)據(jù)的方法，并且加快查詢的速度，因此索引對(duì)查詢的速度有著至關(guān)重要的影響。

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

大多數(shù)情況下都（默認(rèn)）采用B+樹來構(gòu)建索引。只是空間列類型的索引使用R-樹，并且MEMORY表還支持hash索引。

其實(shí)，用不用索引，最終都是優(yōu)化器說了算。優(yōu)化器是基于什么的優(yōu)化器？基于cost開銷(CostBaseOptimizer)，它不是基于規(guī)則(Rule-BasedOptimizer)，也不是基于語義。怎么樣開銷小就怎么來。另外，SQL語句是否使用索引，跟數(shù)據(jù)庫版本、數(shù)據(jù)量、數(shù)據(jù)選擇度都有關(guān)系。

說明：SQL_NO_CACHE 是MySQL中的一個(gè)查詢提示（Query Hint），用于在查詢時(shí)告訴MySQL不要緩存該查詢的結(jié)果。通常，MySQL會(huì)在查詢結(jié)果中使用查詢緩存來提高性能，如果查詢緩存中已經(jīng)存在相同的查詢結(jié)果，MySQL將直接返回緩存中的結(jié)果，而不需要再執(zhí)行實(shí)際的查詢操作。

數(shù)據(jù)準(zhǔn)備

#1. 數(shù)據(jù)準(zhǔn)備CREATE DATABASE atguigudb2;USE atguigudb2;#建表
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 = INNODBAUTO_INCREMENT = 1DEFAULT 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 = INNODBAUTO_INCREMENT = 1DEFAULT CHARSET = utf8;SET GLOBAL log_bin_trust_function_creators = 1;#隨機(jī)產(chǎn)生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGINDECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i < nDOSET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND() * 52), 1));SET i = i + 1;END WHILE;RETURN return_str;
END //
DELIMITER ;# 用于隨機(jī)產(chǎn)生多少到多少的編號(hào)
DELIMITER //
CREATE FUNCTION rand_num(from_num INT, to_num INT) RETURNS INT(11)
BEGINDECLARE i INT DEFAULT 0;SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1));RETURN i;
END //
DELIMITER ;# 創(chuàng)建往stu表中插入數(shù)據(jù)的存儲(chǔ)過程
DELIMITER //
CREATE PROCEDURE insert_stu(START INT, max_num INT)
BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0; #設(shè)置手動(dòng)提交事務(wù)REPEATREPEAT#循環(huán)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; #提交事務(wù)
END //
DELIMITER ;# 執(zhí)行存儲(chǔ)過程，往class表添加隨機(jī)數(shù)據(jù)
DELIMITER //
CREATE PROCEDURE `insert_class`(max_num INT)
BEGINDECLARE i INT DEFAULT 0; SET autocommit = 0;REPEATSET 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 ;# 執(zhí)行存儲(chǔ)過程，往class表添加1萬條數(shù)據(jù)
CALL insert_class(10000);# 執(zhí)行存儲(chǔ)過程，往stu表添加50萬條數(shù)據(jù)
CALL insert_stu(100000, 500000);SELECT COUNT(*)
FROM class;SELECT COUNT(*)
FROM student;# 刪除某表上的索引 存儲(chǔ)過程
DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200), tablename VARCHAR(200))
BEGINDECLARE done INT DEFAULT 0;DECLARE ct INT DEFAULT 0;DECLARE _index VARCHAR(200) DEFAULT '';DECLARE _cur CURSOR FOR SELECT index_nameFROM information_schema.STATISTICSWHERE table_schema = dbname AND table_name = tablename AND seq_in_index = 1 AND index_name <> 'PRIMARY';#每個(gè)游標(biāo)必須使用不同的declare continue handler for not found set done=1來控制游標(biāo)的結(jié)束DECLARE  CONTINUE HANDLER FOR NOT FOUND SET done=2 ;
#若沒有數(shù)據(jù)返回,程序繼續(xù),并將變量done設(shè)為2OPEN _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 ;# 執(zhí)行存儲(chǔ)過程
CALL proc_drop_index("atguigu2", "student");

1. 全值匹配

# 創(chuàng)建索引前后
# 145 ms (execution: 126 ms, fetching: 19 ms)
# 76 ms (execution: 34 ms, fetching: 42 ms)
SELECT SQL_NO_CACHE *
FROM student
WHERE age = 30AND classId = 4 AND name = 'abcd';CREATE INDEX idx_age ON student (age);

再創(chuàng)建一個(gè)索引，發(fā)現(xiàn)使用的聯(lián)合索引:

CREATE INDEX idx_age_classId ON student(age, classId);
EXPLAIN SELECT SQL_NO_CACHE *
FROM student
WHERE age = 30AND classId = 4AND name = 'abcd';

繼續(xù)創(chuàng)建這三個(gè)字段的聯(lián)合索引，發(fā)現(xiàn)使用的3個(gè)的聯(lián)合索引

CREATE INDEX idx_age_classId_name ON student(age, classId, name);

2 最佳左前綴法則(聯(lián)合索引)

MySQL建立聯(lián)合索引時(shí)會(huì)遵守最佳左前綴匹配原則，即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開始匹配；

# 使用idx_age_classId索引 順序一致
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name='abcd';
# 沒用上索引 因?yàn)闆]有classid開頭順序的索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name='abcd';

刪掉前兩個(gè)索引，保留student(age, classId, name);
發(fā)現(xiàn)雖然用上了索引，但是key_len=5，說明只用上了聯(lián)合索引的一部分age這個(gè)字段(int4字節(jié)+1null)。

先取查詢條件的classid到索引第一個(gè)列匹配無結(jié)果，再取age匹配找到可用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND age=30 AND student.name='abcd';

這樣則完全沒有使用上索引：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name='abcd';

先取查詢條件的classid到索引第一個(gè)列匹配無結(jié)果，再取name匹配仍然無結(jié)果，因?yàn)樗饕淮嬖赼ge開頭的這個(gè)。

結(jié)論：MySQL可以為多個(gè)字段創(chuàng)建索引，一個(gè)索引可以包括16個(gè)字段。對(duì)于多列索引，過濾條件要使用索引必須按照索引建立時(shí)的順序，依次滿足，一旦跳過某個(gè)字段，索引后面的字段都無法被使用。如果查詢條件中沒有使用這些字段中第1個(gè)字段時(shí)，多列（或聯(lián)合）索引不會(huì)被使用

Alibaba《Java開發(fā)手冊(cè)》：索引文件具有B-Tree的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引

主鍵插入順序

對(duì)于一個(gè)使用InnoDB存儲(chǔ)引擎的表來說，在我們沒有顯式的創(chuàng)建索引時(shí)，表中的數(shù)據(jù)實(shí)際上都是存儲(chǔ)在聚簇索引的葉子節(jié)點(diǎn)的。而記錄又是存儲(chǔ)在數(shù)據(jù)頁中的，數(shù)據(jù)頁和記錄又是按照記錄主鍵值從小到大的順序進(jìn)行排序，所以如果我們插入的記錄的主鍵值是依次增大的話，那我們每插滿一個(gè)數(shù)據(jù)頁就換到下一個(gè)數(shù)據(jù)頁繼續(xù)插，而如果我們插入的主鍵信息忽大忽小的話，就比較麻煩，假設(shè)某個(gè)數(shù)據(jù)頁存儲(chǔ)的記錄已經(jīng)滿了，它存儲(chǔ)的主鍵值在1~100之間

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

可這個(gè)數(shù)據(jù)頁已經(jīng)滿了，再插進(jìn)來咋辦呢？我們需要把當(dāng)前頁面分裂成兩個(gè)頁面，把本頁中的一些記錄移動(dòng)到新創(chuàng)建的這個(gè)頁中。頁面分裂和記錄移位意味著什么？意味著：性能損耗！所以如果我們想盡量避免這樣無謂的性能損耗，最好讓插入的記錄的主鍵值依次遞增，這樣就不會(huì)發(fā)生這樣的性能損耗了。所以我們建議：讓主鍵具有AUTO_INCREMENT，讓存儲(chǔ)引擎自己為表生成主鍵，而不是我們手動(dòng)插入 ，比如： person_info 表：

CREATE TABLE person_info
(id           INT UNSIGNED NOT NULL AUTO_INCREMENT,name         VARCHAR(100) NOT NULL,birthday     DATE         NOT NULL,phone_number CHAR(11)     NOT NULL,country      varchar(100) NOT NULL,PRIMARY KEY (id),KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

自定義的主鍵列id擁有AUTO_INCREMENT屬性，在插入記錄時(shí)存儲(chǔ)引擎會(huì)自動(dòng)為我們填入自增的主鍵值。這樣的主鍵占用空間小，順序?qū)懭?#xff0c;減少頁分裂。

4 計(jì)算、函數(shù)導(dǎo)致索引失效

CREATE INDEX idx_name ON student(NAME);
# 可以使用上索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
# 無法使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

第一種：索引優(yōu)化生效，因?yàn)榭梢允褂枚?jí)索引進(jìn)行匹配，第二種：索引優(yōu)化失效，因?yàn)槭褂玫搅撕瘮?shù)，但是對(duì)于mysql來說，函數(shù)作用的是什么并不知道，所以不能使用索引。

CREATE INDEX idx_sno ON student(stuno);
# 無法使用索引
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
# 可以使用索引
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

5 類型轉(zhuǎn)換(自動(dòng)或手動(dòng))導(dǎo)致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

name=123發(fā)生類型轉(zhuǎn)化，索引失效

結(jié)論：設(shè)計(jì)實(shí)體類屬性時(shí)，一定要與數(shù)據(jù)庫字段類型相對(duì)應(yīng)。否則，就會(huì)出現(xiàn)類型轉(zhuǎn)換的情況

6 范圍條件右邊的列索引失效

CREATE INDEX idx_age_cid_name ON student(age, classId, name);EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = 'abc';

key_len=10,age和classid加起來，沒有name

范圍右邊的列不能使用。比如：(<) (<=) (>) (>=) 和 between等

如果這種sql出現(xiàn)較多，應(yīng)該注意聯(lián)合索引建立順序，將范圍查詢條件放置語句最后：

CREATE INDEX idx_age_name_classid ON student(age, name, classid);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;

應(yīng)用開發(fā)中范圍查詢，例如：金額查詢，日期查詢往往都是范圍查詢。應(yīng)將查詢條件放置where語句最后。

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

CREATE INDEX idx_name ON student(NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc';

8 is null可以使用索引, is not null無法使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

最好在設(shè)計(jì)數(shù)據(jù)表的時(shí)候就將字段設(shè)置為NOT NULL約束，比如你可以將INT類型的字段，默認(rèn)值為0，將字符類型的默認(rèn)值設(shè)置為空字符串(‘’)

同理，在查詢中使用NOT LIKE也無法使用索引，導(dǎo)致全表掃描

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

在使用LIKE關(guān)鍵字進(jìn)行查詢的查詢語句中，如果匹配字符串的第一個(gè)字符為”%“，索引就不會(huì)起作用。只有”%“不在第一個(gè)位置，索引才會(huì)起作用

# 使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'ab%';
# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab%';

Alibaba《Java開發(fā)手冊(cè)》：【強(qiáng)制】頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請(qǐng)走搜索引擎來解決

10 OR前后存在非索引的列，索引失效

在WHERE子句中，如果在OR前的條件列進(jìn)行了索引，而OR后的條件列沒有進(jìn)行索引，那么索引會(huì)失效。也就是說，OR前后的兩個(gè)條件中的列都是索引時(shí)，查詢中才使用索引

因?yàn)镺R的含義就是兩個(gè)只要滿足一個(gè)即可，因此只有一個(gè)條件列進(jìn)行了索引是沒有意義的，只要有條件列沒有進(jìn)行索引，就會(huì)進(jìn)行全表掃描，因此索引的條件列也會(huì)失效

CREATE INDEX idx_age ON student(age);
# 因?yàn)閏lassid字段沒有索引，所以沒有使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=10 OR classid=100;# 因?yàn)閍ge字段和name字段上都有索引，所以查詢中使用了索引。
# 這里使用到了index_merge，簡(jiǎn)單說index_merge就是對(duì)age和name分別進(jìn)行了掃描，然后將這兩個(gè)結(jié)果集進(jìn)行了合并。這樣的好處就是避免了全表掃描
CREATE INDEX idx_name ON student(name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=10 OR name= 'able';

11 數(shù)據(jù)庫和表的字符集統(tǒng)一使用utf8mb4

統(tǒng)一使用utf8mb4(5.5.3版本以上支持)兼容性更好，統(tǒng)一字符集可以避免由于字符集轉(zhuǎn)換產(chǎn)生的亂碼。不同的字符集進(jìn)行比較前需要進(jìn)行轉(zhuǎn)換會(huì)造成索引失效.

12 一般性建議

對(duì)于單列索引，盡量選擇針對(duì)當(dāng)前query過濾性更好的索引

在選擇組合索引的時(shí)候，當(dāng)前query中過濾性最好的字段在索引字段順序中，位置越靠前越好

在選擇組合索引的時(shí)候，盡量選擇能夠包含當(dāng)前query中的where子句中更多字段的索引

在選擇組合索引的時(shí)候，如果某個(gè)字段可能出現(xiàn)范圍查詢時(shí)，盡量把這個(gè)字段放在索引次序的最后面

總之，書寫SQL語句時(shí)，盡量避免造成索引失效的情況

關(guān)聯(lián)查詢優(yōu)化

數(shù)據(jù)準(zhǔn)備

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)));
# 20條
INSERT INTO book(card)
VALUES (FLOOR(1 + (RAND() * 20)));

左外連接，右外連接，和內(nèi)連接的區(qū)別

首先這三種連接都是join連接，只要是join連接，假如我們兩張表都沒有索引，假如A表有100條數(shù)據(jù)，B表有1000條數(shù)據(jù)，假如A表是驅(qū)動(dòng)表，B表是被驅(qū)動(dòng)表，那么我們連接的時(shí)候其實(shí)先從A表取出一條數(shù)據(jù)，遍歷全部的B表，將匹配到的數(shù)據(jù)放到result里面，依次類推，最后會(huì)執(zhí)行A*B=10萬次。所以如果沒有索引效率是非常低的。所以一般情況下都會(huì)給被驅(qū)動(dòng)表B建立索引，這樣從A取出一條數(shù)據(jù)，然后B表直接根據(jù)索引匹配，就不會(huì)再是全表掃描B表了；

那么左外連接和內(nèi)連接有什么區(qū)別呢？
左外連接是左表為主表，左表的每一行數(shù)據(jù)都會(huì)展示，在此約束下存在兩種情況，第一種是右表沒有關(guān)聯(lián)到相關(guān)數(shù)據(jù)，那么這個(gè)時(shí)候左表的對(duì)應(yīng)行右邊的關(guān)聯(lián)部分全部寫NULL；第二種情況是右表成功關(guān)聯(lián)到相關(guān)數(shù)據(jù)，那么這個(gè)時(shí)候左表的對(duì)應(yīng)行右邊會(huì)展示出右表的關(guān)聯(lián)數(shù)據(jù)；
而內(nèi)連接是左表不會(huì)展示出它的全部行數(shù)據(jù)，如果左表成功關(guān)聯(lián)到了右表，那么會(huì)有一行對(duì)應(yīng)的結(jié)果，存放左表和右表關(guān)聯(lián)之后的數(shù)據(jù)；而如果左表沒有關(guān)聯(lián)到右表，那么結(jié)果中就不會(huì)出現(xiàn)此行數(shù)據(jù)了，不會(huì)像左外連接那樣保存左表的行數(shù)據(jù)而沒有關(guān)聯(lián)到的右表數(shù)據(jù)填寫NULL。

1 左外連接

沒有索引type全是all：

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

CREATE INDEX Y ON book (card);

可以看到第二行的 type 變?yōu)榱?ref，rows 也變成了優(yōu)化比較明顯。這是由左連接特性決定的。LEFT JOIN條件用于確定如何從右表搜索行，左邊一定都有，所以右邊是我們的關(guān)鍵點(diǎn),一定需要建立索引;

2 內(nèi)連接

對(duì)于內(nèi)連接來講，如果表的連接條件中只能有一個(gè)字段有索引，則有索引的字段對(duì)應(yīng)的表會(huì)被作為被驅(qū)動(dòng)表。

對(duì)于內(nèi)連接來說，在兩個(gè)表的連接條件都存在索引的情況下，會(huì)選擇小表（可以簡(jiǎn)單理解成數(shù)據(jù)行少的表）作為驅(qū)動(dòng)表——小表驅(qū)動(dòng)大表。

這樣先從驅(qū)動(dòng)表拿一條數(shù)據(jù)再去被驅(qū)動(dòng)表匹配的時(shí)候，因?yàn)轵?qū)動(dòng)表數(shù)據(jù)少，因此我們執(zhí)行的次數(shù)就比較少，而被驅(qū)動(dòng)表即便數(shù)據(jù)多也沒事，因?yàn)楸或?qū)動(dòng)表里面有索引，查詢會(huì)很快。

CREATE INDEX Y ON book (card);
DROP INDEX Y ON book;EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;CREATE INDEX X ON type (card);
DROP INDEX X ON type;

3 JOIN語句原理

JOIN方式連接多個(gè)表，本質(zhì)就是各個(gè)表之間數(shù)據(jù)的循環(huán)匹配。MySQL5.5版本之前，MySQL只支持一種表間關(guān)聯(lián)方式，就是嵌套循環(huán)(Nested Loop Join)。如果關(guān)聯(lián)表的數(shù)據(jù)量很大，則join關(guān)聯(lián)的執(zhí)行時(shí)間會(huì)非常長(zhǎng)。MySQL5.5以后的版本中，MySQL通過引入BNLJ算法來優(yōu)化嵌套執(zhí)行。

(1) 驅(qū)動(dòng)表和被驅(qū)動(dòng)表

• 驅(qū)動(dòng)表就是主表，被驅(qū)動(dòng)表就是從表、非驅(qū)動(dòng)表

• 對(duì)于內(nèi)連接來說

• SELECT * FROM A JOIN B ON …

• A一定是驅(qū)動(dòng)表嗎？不一定，優(yōu)化器會(huì)根據(jù)你查詢語句做優(yōu)化，決定先查哪張表。先查詢的那張表就是驅(qū)動(dòng)表，反之就是被驅(qū)動(dòng)表。通過EXPLAIN關(guān)鍵字可以查看

• 對(duì)于外連接來說

• SELECT FROM A LEFT JOIN B ON …
  #或
  SELECT FROM B RIGHT JOIN A ON …

• 通常認(rèn)為A就是驅(qū)動(dòng)表，B就是被驅(qū)動(dòng)表。但也未必。測(cè)試如下：

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);# 測(cè)試1 使用了索引f1
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);
# 測(cè)試2 沒有使用索引
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) AND (a.f2=b.f2);

(2) Simple Nested-Loop Join（簡(jiǎn)單嵌套循環(huán)連接）
算法相當(dāng)簡(jiǎn)單，從表A中取出一條數(shù)據(jù)1，遍歷表B，將匹配到的數(shù)據(jù)放到result…以此類推，驅(qū)動(dòng)表A中的每一條記錄與被驅(qū)動(dòng)表B的記錄進(jìn)行判斷

可以看到這種方式效率是非常低的，以上述表A數(shù)據(jù)100條，表B數(shù)據(jù)1000條計(jì)算，則A*B=10萬次。開銷統(tǒng)計(jì)如下：

當(dāng)然mysql肯定不會(huì)這么粗暴的去進(jìn)行表的連接，所以就出現(xiàn)了后面的兩種對(duì)Nested-Look Join優(yōu)化算法

(3) Index Nested-Loop Join（索引嵌套循環(huán)連接）
Index Nested-Loop Join其優(yōu)化的思路主要是為了減少內(nèi)層表數(shù)據(jù)的匹配次數(shù)，所以要求被驅(qū)動(dòng)表上必須有索引才行。通過外層表匹配條件直接與內(nèi)層表索引進(jìn)行匹配，避免和內(nèi)層表的每條記錄去進(jìn)行比較，這樣極大的減少了對(duì)內(nèi)層表的匹配次數(shù)

驅(qū)動(dòng)表中的每條記錄通過被驅(qū)動(dòng)表的索引進(jìn)行訪問，因?yàn)樗饕樵兊某杀臼潜容^固定的，故mysql優(yōu)化器都傾向于使用記錄數(shù)少的表作為驅(qū)動(dòng)表（外表）

如果被驅(qū)動(dòng)表加索引，效率是非常高的，但如果索引不是主鍵索引，所以還得進(jìn)行一次回表查詢。相比，被驅(qū)動(dòng)表的索引是主鍵索引，效率會(huì)更高

(4) Block Nested-Loop Join（塊嵌套循環(huán)連接）
如果存在索引，那么會(huì)使用index的方式進(jìn)行join，如果join的列沒有索引，被驅(qū)動(dòng)表要掃描的次數(shù)太多了。每次訪問被驅(qū)動(dòng)表，其表中的記錄都會(huì)被加載到內(nèi)存中，然后再?gòu)尿?qū)動(dòng)表中取一條與其匹配，匹配結(jié)束后清除內(nèi)存，然后再?gòu)尿?qū)動(dòng)表中加載一條記錄，然后把被驅(qū)動(dòng)表的記錄再加載到內(nèi)存匹配，這樣周而復(fù)始，大大增加了IO的次數(shù)。為了減少被驅(qū)動(dòng)表的IO次數(shù)，就出現(xiàn)了Block Nested-Loop Join的方式

不再是逐條獲取驅(qū)動(dòng)表的數(shù)據(jù)，而是一塊一塊的獲取，引入了join buffer緩沖區(qū)，將驅(qū)動(dòng)表join相關(guān)的部分?jǐn)?shù)據(jù)列（大小受join buffer的限制）緩存到j(luò)oin buffer中，然后全表掃描被驅(qū)動(dòng)表，被驅(qū)動(dòng)表的每一條記錄一次性和join buffer中所有驅(qū)動(dòng)表記錄進(jìn)行匹配（內(nèi)存中操作），將簡(jiǎn)單嵌套循環(huán)中的多次比較合并成一次，降低了被驅(qū)動(dòng)表的訪問頻率

注意：這里緩存的不只是關(guān)聯(lián)表的列，SELECT后面的列也會(huì)緩存起來

在一個(gè)有N個(gè)join關(guān)聯(lián)的sql中會(huì)分配N-1個(gè)join buffer。所以查詢的時(shí)候盡量減少不必要的字段，可以讓join buffer中可以存放更多的列


參數(shù)設(shè)置：
block_nested_loop：通過show variables like ‘%optimizer_switch%’查看block_nested_loop狀態(tài)。默認(rèn)是開啟的

join_buffer_size：驅(qū)動(dòng)表能不能一次加載完，要看join buffer能不能存儲(chǔ)所有數(shù)據(jù)，默認(rèn)情況下join_buffer_size=256k

join_buffer_size的最大值在32位系統(tǒng)可以申請(qǐng)4G，而在64位操作系統(tǒng)下可以申請(qǐng)大于4G的Join Buffer空間（64位Windows除外，其大值會(huì)被截?cái)酁?G并發(fā)出警告）

(5) Join小結(jié)
整體效率比較：INLJ>BNLJ>SNLJ

永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大結(jié)果集(其本質(zhì)就是減少外層循環(huán)的數(shù)據(jù)數(shù)量)（小的度量單位指的是 表行數(shù)*每行大小，可以粗略的理解成是表行數(shù)），這樣先從驅(qū)動(dòng)表拿一條數(shù)據(jù)再去被驅(qū)動(dòng)表匹配的時(shí)候，因?yàn)轵?qū)動(dòng)表數(shù)據(jù)少，因此我們執(zhí)行的次數(shù)就比較少，而被驅(qū)動(dòng)表即便數(shù)據(jù)多也沒事，因?yàn)楸或?qū)動(dòng)表里面有索引，查詢會(huì)很快。

SELECT t1.b, t2.* FROM t1 straight_join t2 ON (t1.b=t2.b) WHERE t2.id<=100; #推薦SELECT t1.b, t2.* FROM t2 straight_join t1 ON (t1.b=t2.b) WHERE t2.id<=100; #不推薦

為被驅(qū)動(dòng)表匹配的條件增加索引(減少內(nèi)存表的循環(huán)匹配次數(shù))

增大join buffer size的大小（一次緩存的數(shù)據(jù)越多，那么內(nèi)層包的掃表次數(shù)就越少）

減少驅(qū)動(dòng)表不必要的字段查詢（字段越少，join buffer所緩存的數(shù)據(jù)就越多）

(6) Hash Join
從MySQL的8.0.20版本開始將廢棄BNLJ，因?yàn)閺腗ySQL8.0.18版本開始就加入了hash join默認(rèn)都會(huì)使用hash join

Nested Loop：對(duì)于被連接的數(shù)據(jù)子集較小的情況，Nested Loop是個(gè)較好的選擇

Hash Join是做大數(shù)據(jù)集連接時(shí)的常用方式，優(yōu)化器使用兩個(gè)表中較小（相對(duì)較小）的表利用Join Key在內(nèi)存中建立散列表，然后掃描較大的表并探測(cè)散列表，找出與Hash表匹配的行

這種方式適用于較小的表完全可以放于內(nèi)存中的情況，這樣總成本就是訪問兩個(gè)表的成本之和

在表很大的情況下并不能完全放入內(nèi)存，這時(shí)優(yōu)化器會(huì)將它分割成若干不同的分區(qū)，不能放入內(nèi)存的部分就把該分區(qū)寫入磁盤的臨時(shí)段，此時(shí)要求有較大的臨時(shí)段從而盡量提高I/O的性能

它能夠很好的工作于沒有索引的大表和并行查詢的環(huán)境中，并提供最好的性能。大多數(shù)人都說它是Join的重型升降機(jī)。Hash Join只能應(yīng)用于等值連接(如WHERE A.COL1=B.COL2)，這是由Hash的特點(diǎn)決定的

子查詢優(yōu)化

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

子查詢是 MySQL 的一項(xiàng)重要的功能，可以幫助我們通過一個(gè) SQL 語句實(shí)現(xiàn)比較復(fù)雜的查詢。但是，子查詢的執(zhí)行效率不高。原因：

執(zhí)行子查詢時(shí)，MySQL需要為內(nèi)層查詢語句的查詢結(jié)果建立一個(gè)臨時(shí)表，然后外層查詢語句從臨時(shí)表中查詢記錄。查詢完畢后，再撤銷這些臨時(shí)表。這樣會(huì)消耗過多的CPU和IO資源，產(chǎn)生大量的慢查詢。

子查詢的結(jié)果集存儲(chǔ)的臨時(shí)表，不論是內(nèi)存臨時(shí)表還是磁盤臨時(shí)表都不會(huì)存在索引，所以查詢性能會(huì)受到一定的影響。

對(duì)于返回結(jié)果集比較大的子查詢，其對(duì)查詢性能的影響也就越大。在MySQL中，可以使用連接（JOIN）查詢來替代子查詢。連接查詢 不需要建立臨時(shí)表 ，其速度比子查詢要快 ，如果查詢中使用索引的話，性能就會(huì)更好。

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

---

舉例：查詢學(xué)生表中是班長(zhǎng)的學(xué)生信息

使用子查詢創(chuàng)建班級(jí)表中班長(zhǎng)的索引

CREATE INDEX idx_moniitor ON class (monitor);EXPLAIN
SELECT *
FROM student stu1
WHERE stu1.`stuno` IN (SELECT monitor FROM class c WHERE monitor IS NOT NULL);

推薦：使用多表查詢

EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c ON stu1.stuno = c.monitor WHERE c.monitor IS NOT NULL;

舉例：取所有不為班長(zhǎng)的同學(xué)

# 不推薦
EXPLAIN
SELECT SQL_NO_CACHE a.*
FROM student a
WHERE a.stuno NOT IN (SELECT monitor FROM class b WHERE monitor IS NOT NULL);
# 推薦
EXPLAIN
SELECT SQL_NO_CACHE a.*
FROM student aLEFT OUTER JOIN class b ON a.stuno = b.monitor
WHERE b.monitor IS NULL;

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

排序優(yōu)化

1 排序優(yōu)化

問題：在 WHERE 條件字段上加索引，但是為什么在 ORDER BY 字段上還要加索引呢？

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

• Index排序中，索引可以保證數(shù)據(jù)的有序性，不需要再進(jìn)行排序，效率更高
• FileSort排序則一般再內(nèi)存中進(jìn)行排序，占用CPU較多。如果待排結(jié)果較大，會(huì)產(chǎn)生臨時(shí)文件I/O到磁盤進(jìn)行排序的情況，效率較低

優(yōu)化建議

• SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表掃描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。當(dāng)然，某些情況下全表掃描，或者 FileSort 排序不一定比索引慢。但總的來說，我們還是要避免，以提高查詢效率
• 盡量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用單索引列；如果不同就使用聯(lián)合索引。
• 無法使用 Index 時(shí)，需要對(duì) FileSort 方式進(jìn)行調(diào)優(yōu)

2.測(cè)試

刪除student、class索引

以下是否能使用到索引，能否去掉using filesort

過程一：沒有使用索引

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

過程二：創(chuàng)建索引

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

SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid;為何沒有使用索引？實(shí)際上SQL在執(zhí)行時(shí)，優(yōu)化器會(huì)考慮成本問題，雖然有索引，但是此索引是一個(gè)二級(jí)索引，那么如果通過索引排完序后需要回表查詢其他的所有列信息。干脆直接在內(nèi)存中做排序發(fā)現(xiàn)花費(fèi)的時(shí)間還要少，所以就沒有使用索引。（注意：并不是任何情況下有索引就一定會(huì)使用，優(yōu)化器是考慮時(shí)間成本進(jìn)行選擇最優(yōu)的執(zhí)行計(jì)劃）。如果sql換成SELECT SQL_NO_CACHE age, classid FROM student ORDER BY age, classid;就會(huì)使用上索引。這里不需要回表（覆蓋索引）

過程三：order by時(shí)順序錯(cuò)誤，索引失效
創(chuàng)建索引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時(shí)規(guī)則不一致，索引失效
順序錯(cuò)，不索引；方向反，不索引

CREATE INDEX idx_age_classid_stuno ON student(age,classid,stuno);
# 失效 方向反
EXPLAIN SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;
# 失效 最左前綴法則
EXPLAIN SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;
# 失效 方向反
# 沒有使用索引是因?yàn)?#xff0c;最后還要按照classid逆序，所以不如直接文件排序。
EXPLAIN SELECT * FROM student ORDER BY age ASC, classid DESC LIMIT 10;
# 有效
EXPLAIN SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

結(jié)論：ORDER BY子句，盡量使用index方式排序，避免使用FileSort方式排序。orderby是規(guī)則不一致是指有的升序有的是降序，必須要是升序都是升序要是降序都是降序，要不然會(huì)索引失效。

3 小結(jié)

INDEX a_b_c(a,b,c)

order by 能使用索引最左前綴

• ORDER BY a
• ORDER BY a,b
• ORDER BY a,b,c
• ORDER BY a DESC, b DESC, c DESC

如果WHERE使用索引的最左前綴定義為常量，則order by能使用索引

• WHERE a=const ORDER BY b,c
• WHERE a=const AND b=const ORDER BY c
• WHERE a=const ORDER BY b,c
• WHERE a=const AND b>const ORDER BY b,c

不能使用索引進(jìn)行排序

• ORDER BY a ASC, b DESC, c DESC /排序不一致/
• WHERE g=const ORDER BY b,c /丟失a索引/
• WHERE a=const ORDER BY c /丟失b索引/
• WHERE a=const ORDER BY a,d /d不是索引的一部分/
• WHERE a in (…) ORDER BY b,c /對(duì)于排序來說，多個(gè)相等條件也是范圍查詢/

4 filesort算法：雙路排序和單路排序

在MySQL中，當(dāng)需要進(jìn)行ORDER BY或GROUP BY操作時(shí)，可能會(huì)使用到filesort算法。filesort算法用于對(duì)查詢結(jié)果集進(jìn)行排序，以滿足ORDER BY或GROUP BY子句的要求。根據(jù)不同的場(chǎng)景和配置，MySQL中的filesort算法分為雙路排序（Two-Phase Sort）和單路排序（One-Phase Sort）兩種。

雙路排序（Two-Phase Sort）：
雙路排序是默認(rèn)情況下的排序算法，它執(zhí)行兩個(gè)排序階段。首先，MySQL會(huì)嘗試使用索引完成排序，如果存在適合的索引來滿足ORDER BY或GROUP BY條件，那么排序就是在索引的幫助下完成的。如果索引無法滿足排序需求，MySQL會(huì)使用雙路排序。

在雙路排序中，MySQL首先嘗試使用內(nèi)存（sort_buffer）進(jìn)行排序。如果排序所需的內(nèi)存超出了sort_buffer的設(shè)置，MySQL將使用磁盤臨時(shí)文件進(jìn)行排序。這樣，雙路排序使用了兩種資源：內(nèi)存和磁盤。通常情況下，雙路排序是比較高效的排序算法，因?yàn)樗浞掷昧藘?nèi)存和磁盤的優(yōu)勢(shì)。

單路排序（One-Phase Sort）：

單路排序是一種特殊的排序算法，它只使用內(nèi)存來完成排序，而不涉及磁盤臨時(shí)文件。單路排序通常在查詢需要排序的數(shù)據(jù)較小時(shí)，MySQL可以保證所有排序數(shù)據(jù)都在sort_buffer內(nèi)存中進(jìn)行排序，從而避免了使用磁盤臨時(shí)文件。

當(dāng)查詢需要排序的數(shù)據(jù)量較小時(shí)，MySQL會(huì)優(yōu)先選擇單路排序，因?yàn)閱温放判虮苊饬舜疟PI/O，相對(duì)較快。但是，如果排序數(shù)據(jù)量較大，超出了sort_buffer的設(shè)置，MySQL會(huì)回退到雙路排序。

在實(shí)際使用中，可以通過調(diào)整sort_buffer的大小來影響MySQL在排序時(shí)選擇單路排序還是雙路排序。如果想強(qiáng)制使用單路排序，可以將sort_buffer設(shè)置為一個(gè)較大的值，但這也會(huì)增加內(nèi)存的消耗。綜合考慮查詢的性能需求和系統(tǒng)的資源情況，選擇合適的排序算法和合理設(shè)置sort_buffer是優(yōu)化查詢性能的重要一環(huán)。

GROUP BY優(yōu)化

• group by 使用索引的原則幾乎跟order by一致 ，group by 即使沒有過濾條件用到索引，也可以直接使用索引。
• group by 先排序再分組，遵照索引建的最佳左前綴法則
• 當(dāng)無法使用索引列，增大max_length_for_sort_data和sort_buffer_size參數(shù)的設(shè)置
• where效率高于having，能寫在where限定的條件就不要寫在having中了
• 減少使用order by，和業(yè)務(wù)溝通能不排序就不排序，或?qū)⑴判蚍诺匠绦蚨巳プ觥rder by、groupby、distinct這些語句較為耗費(fèi)CPU，數(shù)據(jù)庫的CPU資源是極其寶貴的。
• 包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結(jié)果集請(qǐng)保持在1000行以內(nèi)，否則SQL會(huì)很慢

優(yōu)化分頁查詢

一般分頁查詢時(shí)，通過創(chuàng)建覆蓋索引能夠比較好地提高性能。一個(gè)常見由非常頭疼地問題就是limit 2000000,10，此時(shí)需要MySQL排序前2000010記錄，僅僅返回2000000-2000010的記錄，其他記錄丟棄，查詢排序的代價(jià)非常大

EXPLAIN SELECT * FROM student LIMIT 2000000,10

優(yōu)化思路一：在索引上完成排序分頁操作，最后根據(jù)主鍵關(guān)聯(lián)回原表查詢所需要的其他列內(nèi)容

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id=a.id;

# 查根據(jù)name排序的分頁，在name上創(chuàng)建索引查找id，再根據(jù)id找具體數(shù)據(jù)，避免了使用聚簇索引數(shù)據(jù)量大的問題# 優(yōu)化前 498 ms (execution: 465 ms, fetching: 33 ms)
SELECT *
FROM student s
ORDER BY name
limit 490000,10;# 優(yōu)化后95 ms (execution: 72 ms, fetching: 23 ms) 
CREATE INDEX stu_name ON student(name);WITH order_name (id) AS (select id from student ORDER BY name limit 490000,10)
SELECT *
FROM student sINNER JOIN order_name o ON s.id = o.id;

優(yōu)化思路二：該方案適用于主鍵自增的表，可以把limit查詢轉(zhuǎn)換成某個(gè)位置的查詢

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