索引優(yōu)化與查詢優(yōu)化

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

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

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

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

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

1. 索引失效案例

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

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

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

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

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

1.0 數(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 = 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;

SET GLOBAL log_bin_trust_function_creators = 1;

#隨機產(chǎn)生字符串
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 ;

# 用于隨機產(chǎn)生多少到多少的編號
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 ;

# 創(chuàng)建往stu表中插入數(shù)據(jù)的存儲過程
DELIMITER //
CREATE PROCEDURE insert_stu(START INT, max_num INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0; #設(shè)置手動提交事務(wù)REPEAT
    REPEAT
        #循環(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í)行存儲過程，往class表添加隨機數(shù)據(jù)
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 ;

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

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

SELECT COUNT(*)
FROM class;

SELECT COUNT(*)
FROM student;

# 刪除某表上的索引 存儲過程
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';
    #每個游標(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í)行存儲過程
CALL proc_drop_index("atguigu2", "student");

2.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 = 30
  AND classId = 4 
  AND name = 'abcd';

CREATE INDEX idx_age ON student (age);

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

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

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

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

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

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

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

刪掉前兩個索引，保留student(age, classId, name);

發(fā)現(xiàn)雖然用上了索引，但是key_len=5，說明只用上了聯(lián)合索引的一部分age這個字段(int4字節(jié)+1null)

先取查詢條件的classid到索引第一個列匹配無結(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到索引第一個列匹配無結(jié)果，再取name匹配仍然無結(jié)果，因為索引只存在age開頭的這個。

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

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

2.3 主鍵插入順序

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

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

可這個數(shù)據(jù)頁已經(jīng)滿了，再插進來咋辦呢？我們需要把當(dāng)前頁面分裂成兩個頁面，把本頁中的一些記錄移動到新創(chuàng)建的這個頁中。頁面分裂和記錄移位意味著什么？意味著：性能損耗！所以如果我們想盡量避免這樣無謂的性能損耗，最好讓插入的記錄的主鍵值依次遞增，這樣就不會發(fā)生這樣的性能損耗了。所以我們建議：讓主鍵具有AUTO_INCREMENT，讓存儲引擎自己為表生成主鍵，而不是我們手動插入 ，比如： 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屬性，在插入記錄時存儲引擎會自動為我們填入自增的主鍵值。這樣的主鍵占用空間小，順序?qū)懭?，減少頁分裂。

2.4 計算、函數(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ōu)化失效，因為使用到了函數(shù)，但是對于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;

2.5 類型轉(zhuǎn)換(自動或手動)導(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è)計實體類屬性時，一定要與數(shù)據(jù)庫字段類型相對應(yīng)。否則，就會出現(xiàn)類型轉(zhuǎn)換的情況

1.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語句最后。

1.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';

1.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è)計數(shù)據(jù)表的時候就將字段設(shè)置為NOT NULL約束，比如你可以將INT類型的字段，默認(rèn)值為0，將字符類型的默認(rèn)值設(shè)置為空字符串(‘’)

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

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

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

# 使用索引
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ā)手冊》：【強制】頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請走搜索引擎來解決

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

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

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

CREATE INDEX idx_age ON student(age);
# 因為classid字段沒有索引，所以沒有使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=10 OR classid=100;

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

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

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

1.12 一般性建議

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

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

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

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

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

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

2.0 數(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)));

2.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)鍵點,一定需要建立索引;

2.2 內(nèi)連接

對于內(nèi)連接來講，如果表的連接條件中只能有一個字段有索引，則有索引的字段會被作為被驅(qū)動表

對于內(nèi)連接來說，在兩個表的連接條件都存在索引的情況下，會選擇小表作為驅(qū)動表——小表驅(qū)動大表

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;

2.3 JOIN語句原理

JOIN方式連接多個表，本質(zhì)就是各個表之間數(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í)行時間會非常長。MySQL5.5以后的版本中，MySQL通過引入BNLJ算法來優(yōu)化嵌套執(zhí)行

(1) 驅(qū)動表和被驅(qū)動表

• 驅(qū)動表就是主表，被驅(qū)動表就是從表、非驅(qū)動表
• 對于內(nèi)連接來說
• SELECT * FROM A JOIN B ON …
• A一定是驅(qū)動表嗎？不一定，優(yōu)化器會根據(jù)你查詢語句做優(yōu)化，決定先查哪張表。先查詢的那張表就是驅(qū)動表，反之就是被驅(qū)動表。通過EXPLAIN關(guān)鍵字可以查看
• 對于外連接來說
• SELECT FROM A LEFT JOIN B ON …
  #或
  SELECT FROM B RIGHT JOIN A ON …
• 通常認(rèn)為A就是驅(qū)動表，B就是被驅(qū)動表。但也未必。測試如下：

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

(2) Simple Nested-Loop Join（簡單嵌套循環(huán)連接）

算法相當(dāng)簡單，從表A中取出一條數(shù)據(jù)1，遍歷表B，將匹配到的數(shù)據(jù)放到result…以此類推，驅(qū)動表A中的每一條記錄與被驅(qū)動表B的記錄進行判斷

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

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

(3) Index Nested-Loop Join（索引嵌套循環(huán)連接）

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

驅(qū)動表中的每條記錄通過被驅(qū)動表的索引進行訪問，因為索引查詢的成本是比較固定的，故mysql優(yōu)化器都傾向于使用記錄數(shù)少的表作為驅(qū)動表（外表）

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

(4) Block Nested-Loop Join（塊嵌套循環(huán)連接）

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

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

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

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

參數(shù)設(shè)置：

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

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

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

(5) Join小結(jié)

整體效率比較：INLJ>BNLJ>SNLJ

永遠(yuǎn)用小結(jié)果集驅(qū)動大結(jié)果集(其本質(zhì)就是減少外層循環(huán)的數(shù)據(jù)數(shù)量)（小的度量單位指的是 表行數(shù)*每行大?。?/p>

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ū)動表匹配的條件增加索引(減少內(nèi)存表的循環(huán)匹配次數(shù))

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

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

(6) Hash Join

從MySQL的8.0.20版本開始將廢棄BNLJ，因為從MySQL8.0.18版本開始就加入了hash join默認(rèn)都會使用hash join

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

Hash Join是做大數(shù)據(jù)集連接時的常用方式，優(yōu)化器使用兩個表中較?。ㄏ鄬^?。┑谋砝肑oin Key在內(nèi)存中建立散列表，然后掃描較大的表并探測散列表，找出與Hash表匹配的行

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

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

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

3. 子查詢優(yōu)化

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

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

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

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

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

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

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

使用子查詢創(chuà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;

舉例：取所有不為班長的同學(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 a
         LEFT 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替代

4. 排序優(yōu)化

4.1 排序優(yōu)化

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

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

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

優(yōu)化建議

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

4.2 測試

刪除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)建索引，但order by時不limit，索引失效

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

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

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

創(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時規(guī)則不一致，索引失效

順序錯，不索引；方向反，不索引

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;
# 失效 方向反
# 沒有使用索引是因為，最后還要按照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方式排序

過程五：無過濾，不索引

# 雖然使用了索引，但是key_len都是5，并沒有使用到ORDER BY后面的，是因為經(jīng)過WHERE的篩選剩下的數(shù)據(jù)不是太多，所以就沒有使用
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;
EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age limit 10;

4.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

不能使用索引進行排序
- 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 /對于排序來說，多個相等條件也是范圍查詢/

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

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

雙路排序（Two-Phase Sort）：

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

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

單路排序（One-Phase Sort）：

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

當(dāng)查詢需要排序的數(shù)據(jù)量較小時，MySQL會優(yōu)先選擇單路排序，因為單路排序避免了磁盤I/O，相對較快。但是，如果排序數(shù)據(jù)量較大，超出了sort_buffer的設(shè)置，MySQL會回退到雙路排序。

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

5. 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ū)⑴判蚍诺匠绦蚨巳プ?。Order by、groupby、distinct這些語句較為耗費CPU，數(shù)據(jù)庫的CPU資源是極其寶貴的。
• 包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結(jié)果集請保持在1000行以內(nèi)，否則SQL會很慢

6. 優(yōu)化分頁查詢

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

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 s
         INNER JOIN order_name o ON s.id = o.id;

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

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

7. 優(yōu)先考慮覆蓋索引

7.1 什么時覆蓋索引

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

理解方式二：非聚簇復(fù)合索引的一種形式，它包括在查詢里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆蓋查詢條件中所涉及的字段）。

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

7.2 覆蓋索引的利弊

好處：

避免Innodb表進行索引的二次查詢(回表)：

Innodb是以聚集索引的順序來存儲的，對于innodb來說，二級索引在葉子節(jié)點中所保存的是行的主鍵信息，如果是用二級索引查詢數(shù)據(jù)，在查找到相應(yīng)的鍵值后，還需要通過主鍵進行二次查詢才能獲取我們真實所需要的數(shù)據(jù)

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

可以把隨機IO變成順序IO加快查詢效率（實際就是砍掉了回表時的隨機IO，只留下了二級索引查詢的順序IO）

由于覆蓋索引是按鍵值的順序存儲的，對于IO密集型的范圍查找來說，對比隨機從磁盤讀取每一行的數(shù)據(jù)IO要少的多，因此利用覆蓋索引在訪問時也可以把磁盤的隨機讀取的IO轉(zhuǎn)變成索引查找的順序IO

由于覆蓋索引可以減少樹的搜索次數(shù)，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優(yōu)化手段

弊端：索引字段的維護總是有代價的。因此，在建立冗余索引來支持覆蓋索引時就需要權(quán)衡考慮了。（DBA或數(shù)據(jù)架構(gòu)師考慮的）

8. 給字符串添加索引

# 教師表
create table teacher
(
    ID    bigint unsigned primary key,
    email varchar(64),
    …
) engine = innodb;

講師要使用郵箱登錄，所以業(yè)務(wù)代碼中一定會出現(xiàn)類似于這樣的語句：
select col1, col2 from teacher where email=’xxx’;

如果email這個字段上沒有索引，那么這個語句就只能做全表掃描

可以使用字符串前綴創(chuàng)建索引，詳見3.2.8:

https://blog.csdn.net/a2272062968/article/details/131917628

9. 索引下推ICP

索引下推（Index Condition Pushdown，ICP）是MySQL 5.6版本中引入的一項優(yōu)化技術(shù)，它在某些情況下可以提高查詢性能。ICP的主要目標(biāo)是減少MySQL在執(zhí)行查詢時需要訪問表的行數(shù)，從而減少IO操作和提高查詢效率。

在傳統(tǒng)的查詢執(zhí)行中，MySQL首先使用索引進行條件過濾，然后再到表中檢索相應(yīng)的行數(shù)據(jù)。ICP通過在索引上應(yīng)用查詢的其他條件，來減少對表的實際訪問。當(dāng)MySQL發(fā)現(xiàn)可以通過索引直接滿足查詢的所有條件時，ICP將會停止對表的行數(shù)據(jù)訪問，從而避免了額外的IO操作。

ICP主要適用于復(fù)合索引，即包含多個列的索引。當(dāng)查詢中涉及到索引的所有列，并且查詢的條件都可以在索引上進行計算時，ICP就會發(fā)揮作用。

以下是ICP的一些優(yōu)點和適用條件：

優(yōu)點：

• 減少了對表的實際訪問，從而減少了IO操作，提高了查詢性能。
• 在某些情況下，可以避免對表的臨時文件和臨時表的創(chuàng)建和使用。

適用條件：

• 查詢涉及的索引是復(fù)合索引，包含多個列。
• 查詢涉及的索引的所有列都要在查詢中使用，并且查詢條件可以在索引上進行計算。
• 表的存儲引擎支持ICP，目前InnoDB和MyISAM存儲引擎支持ICP。

ICP是MySQL中的一個自動優(yōu)化特性，不需要顯式地啟用。在執(zhí)行查詢時，MySQL的優(yōu)化器會自動判斷是否可以使用ICP來優(yōu)化查詢計劃。對于適合使用復(fù)合索引和滿足ICP條件的查詢，ICP會幫助提高查詢性能，減少不必要的IO操作，從而加快查詢的執(zhí)行速度。

10. 普通索引vs唯一索引

普通索引和唯一索引應(yīng)該怎么選擇？其實，這兩類索引在查詢能力上是沒差別的，主要考慮的是對更新性能的影響。所以，建議你盡量選擇普通索引

在實際使用中會發(fā)現(xiàn)，普通索引和change buffer的配合使用，對于 數(shù)據(jù)量大 的表的更新優(yōu)化還是很明顯的

如果所有的更新后面，都馬上伴隨著對這個記錄的查詢，那么你應(yīng)該關(guān)閉change buffer。而在其他情況下，change buffer都能提升更新性能

由于唯一索引用不上change buffer的優(yōu)化機制，因此如果業(yè)務(wù)可以接受，從性能角度出發(fā)建議優(yōu)先考慮非唯一索引。但是如果”業(yè)務(wù)可能無法確?！钡那闆r下，怎么處理呢

• 首先，業(yè)務(wù)正確性優(yōu)先。我們的前提是“業(yè)務(wù)代碼已經(jīng)保證不會寫入重復(fù)數(shù)據(jù)”的情況下，討論性能問題。如果業(yè)務(wù)不能保證，或者業(yè)務(wù)就是要求數(shù)據(jù)庫來做約束，那么沒得選，必須創(chuàng)建唯一索引。這種情況下，本節(jié)的意義在于，如果碰上了大量插入數(shù)據(jù)慢、內(nèi)存命中率低的時候，給你多提供一個排查思路。
• 然后，在一些“歸檔庫”的場景，你是可以考慮使用唯一索引的。比如，線上數(shù)據(jù)只需要保留半年，然后歷史數(shù)據(jù)保存在歸檔庫。這時候，歸檔數(shù)據(jù)已經(jīng)是確保沒有唯一鍵沖突了。要提高歸檔效率，可以考慮把表里面的唯一索引改成普通索引

11. 其他查詢優(yōu)化策略

11.1 EXISTS和IN的區(qū)分

問題：不太理解那種情況下使用EXISTS，那種情況用IN。選擇的標(biāo)準(zhǔn)是能否使用表的索引嗎？

回答：索引是個前提，其實選擇與否還是要看表的大小?？梢詫⑦x擇的標(biāo)準(zhǔn)理解為小表驅(qū)動大表。在這種方式下效率是最高的

例如：

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)

當(dāng)A小于B時，用EXISTS。因為EXISTS的實現(xiàn)，相當(dāng)于外表循環(huán)，實現(xiàn)的邏輯類似于：

for i in A
for j in B
if j.cc == i.cc then …

當(dāng)B小于A時用IN，因為實現(xiàn)的邏輯類似于：

for i in B
for j in A
if j.cc == i.cc then …

哪個表小就用哪個表來驅(qū)動，A表小就用EXISTS，B表小就用IN

11.2 COUNT(*)與COUNT(具體字段)效率

問：在MYSQL中統(tǒng)計數(shù)據(jù)表的行數(shù)，可以使用三種方式：SELECT COUNT(*)、SELECT COUNT(1)、SELECT COUNT(具體字段)，使用這三者之間的查詢效率是怎樣的？

答：

前提：如果要統(tǒng)計的是某個字段的非空數(shù)據(jù)行數(shù)，則另當(dāng)別論，畢竟比較執(zhí)行效率的前提是結(jié)果一樣才可以

環(huán)節(jié)1：COUNT(*)和COUNT(1)都是對所有結(jié)果進行COUNT，COUNT(*)和COUNT(1)本質(zhì)上并沒有區(qū)別（二者執(zhí)行時間可能略有差別，不過還是可以把它倆的執(zhí)行效率看成是相等的）。如果有WHERE子句，則是對所有符合篩選條件的數(shù)據(jù)進行統(tǒng)計；如果沒有WHERE子句，則是對數(shù)據(jù)表的數(shù)據(jù)行數(shù)進行統(tǒng)計

環(huán)節(jié)2：如果是MyISAM存儲引擎，統(tǒng)計數(shù)據(jù)表的行數(shù)只需要O(1)的復(fù)雜度，這是因為每張MyISAM的數(shù)據(jù)表都有一個meta信息存儲了row_count值，而一致性則由表級鎖來保證。如果是InnoDB存儲引擎，因為InnoDB支持事務(wù)，采用行級鎖和MVCC機制，所以無法像MyISAM一樣，維護一個row_count變量，因此需要采用掃描全表，進行循環(huán)+計數(shù)的方式來完成統(tǒng)計

環(huán)節(jié)3：在InnoDB引擎中，如果采用COUNT(具體字段)來統(tǒng)計數(shù)據(jù)行數(shù)，要盡量采用二級索引。因為主鍵采用的索引是聚簇索引，聚簇索引包含的信息多，明顯會大于二級索引（非聚簇索引）。對于COUNT(*)和COUNT(1)來說，它們不需要查找具體的行，只是統(tǒng)計行數(shù)，系統(tǒng)會自動采用占用空間更小的二級索引來進行統(tǒng)計。如果有多個二級索引，會使用key_len小的二級索引進行掃描。當(dāng)沒有二級索引的時候，才會采用主鍵索引進行統(tǒng)計

11.3 關(guān)于SELECT(*)

在表查詢中，建議明確字段，不要使用*作為查詢的字段列表，推薦使用SELECT<字段列表>查詢。

• MySQL在解析的過程中，會通過查詢數(shù)據(jù)字典將*按序轉(zhuǎn)換成所有列名，這會大大的消耗資源和時間
• 無法使用覆蓋索引

11.4 LIMIT 1對優(yōu)化的影響

針對的是會掃描全表的SQL語句，如果你可以確定結(jié)果集只有一條，那么加上LIMIT 1的時候，當(dāng)找到一條結(jié)果的時候就不會繼續(xù)掃描了，這樣會加快查詢速度

如果數(shù)據(jù)表已經(jīng)對字段建立了唯一索引，那么可以通過索引進行查詢，不會全表掃描的話，就不需要加上LIMIT 1了

11.5 多使用COMMIT

只要有可能，在程序中盡量多使用 COMMIT，這樣程序的性能得到提高，需求也會因為 COMMIT 所釋放的資源而減少。

COMMIT 所釋放的資源：文章來源地址http://www.zghlxwxcb.cn/news/detail-611324.html

• 回滾段上用于恢復(fù)數(shù)據(jù)的信息
• 被程序語句獲得的鎖
• redo / undo log buffer 中的空間
• 管理上述 3 種資源中的內(nèi)部花費

到了這里，關(guān)于數(shù)據(jù)庫索引優(yōu)化與查詢優(yōu)化——醍醐灌頂?shù)奈恼戮徒榻B完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！