MySQL-10.索引最佳化與查詢最佳化

长名06發表於2024-05-23
MySql索引

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);

結論：

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

  2. 當【範圍條件】和【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索引樹找到滿足索引值是’ zhangssxyz@xxx.com ’的這條記錄，取得ID2的值；

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

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

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

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

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

2.到主鍵上查到主鍵值是ID1的行，判斷出email的值不是’ zhangssxyz@xxx.com ’，這行記錄丟棄；

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條件的記錄。
  • 對於唯一索引來說，由於索引定義了唯一性，查詢到第一個滿足條件的記錄後，就會停止繼續檢索。

那麼，這個不同帶來的效能差距會有多少呢？答案是，微乎其微。

11.2 更新過程

為了說明普通索引和唯一索引對更新語句效能的影響這個問題，介紹一下change buffer。

當需要更新一個資料頁時，如果資料頁在記憶體中就直接更新，而如果這個資料頁還沒有在記憶體中的話，在不影響資料一致性的前提下， InooDB會將這些更新操作快取在change buffer中，這樣就不需要從磁碟中讀入這個資料頁了。在下次查詢需要訪問這個資料頁的時候，將資料頁讀入記憶體，然後執行changebuffer中與這個頁有關的操作。透過這種方式就能保證這個資料邏輯的正確性。

將change buffer中的操作應用到原資料頁，得到最新結果的過程稱為merge。除了訪問這個資料頁會觸發merge外，系統有後臺執行緒會定期merge。在資料庫正常關閉（shutdown）的過程中，也會執行merge操作。

如果能夠將更新操作先記錄在change buffer，減少讀磁碟，語句的執行速度會得到明顯的提升。而且，資料讀入記憶體是需要佔用buffer pool的，所以這種方式還能夠避免佔用記憶體，提高記憶體利用率。

唯一索引的更新就不能使用change buffer，實際上也只有普通索引可以使用。

11.3 change buffer的使用場景

  • 1.普通索引和唯一索引應該怎麼選擇？其實，這兩類索引在查詢能力上是沒差別的，主要考慮的是對更新效能的影響。所以，建議你儘量選擇普通索引。
  • 2.在實際使用中會發現，普通索引和change buffer的配合使用，對於資料量大的表的更新最佳化還是很明顯的。
  • 3.如果所有的更新後面，都馬上伴隨著對這個記錄的查詢，那麼你應該關閉change buffer。而在其他情況下，change buffer都能提升更新效能。
  • 4.由於唯一索引用不上change buffer的最佳化機制，因此如果業務可以接受，從效能角度出發建議優先考慮非唯一索引。但是如果"業務可能無法確保"的情況下，怎麼處理呢？
  • 首先，業務正確性優先。我們的前提是“業務程式碼已經保證不會寫入重複資料”的情況下，討論效能問題。如果業務不能保證，或者業務就是要求資料庫來做約束，那麼沒得選，必須建立唯一索引。這種情況下，本節的意義在於，如果碰上了大量插入資料慢、記憶體命中率低的時候，給你多提供一個排查思路。
  • 然後，在一些“歸檔庫”的場景，你是可以考慮使用唯一索引的。比如，線上資料只需要保留半年，然後歷史資料儲存在歸檔庫。這時候，歸檔資料已經是確保沒有唯一鍵衝突了。要提高歸檔效率，可以考慮把表裡面的唯一索引改成普通索引。

阿里開發規範

【強制】業務上具有唯一特性的欄位，即使是多個欄位的組合，也必須建成唯一索引。

說明：不要以為唯一索引影響了 insert 速度，這個速度損耗可以忽略，但提高查詢速度是明顯的；另外，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，根據墨菲定律，必然有髒資料產生。

12.其他最佳化策略

12.1 EXISTS 和 IN 的區分

問題

不太理解哪種情況下應該使用EXISTS，哪種情況應該用IN。選擇的標準是看能否使用表的索引嗎？

回答

索引是個前提，其實選擇與否還是要看錶的大小。你可以將選擇的標準理解為小表驅動大表。在這種方式下效率是最高的。

舉例

SELECT * FROM A WHERE cc IN ( SELECT cc FROM 3)

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

當A表小於B時，用EXISTS，因為EXISTS的實現，相當於外表迴圈，實現邏輯類似於

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

當B表小於A時，用IN

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

那個表小就用那個表來驅動，A表小就用EXISTS，B表下用IN

12.2 COUNT(*)與COUNT(具體欄位)效率

問：在 MySQL 中統計資料表的行數，可以使用三種方式：SELECT COUNT(*)、SELECT COUNT(1) 和SELECT COUNT(具體欄位) ，使用這三者之間的查詢效率是怎樣的？

環節1: COUNT(*)和 COUNT(1)都是對所有結果進行COUNT，COUNT(*)和COUNT(1)本質上並沒有區別(二者執行時間可能略有差別，不過你還是可以把它倆的執行效率看成是相等的)。如果有WHERE子句，則是對所有符合篩選條件的資料行進行統計;如果沒有WHERE子句，則是對資料表的資料行數進行統計。

環節2: 如果是MyISAM儲存引擎，統計資料表的行數只需要O(1)的複雜度，這是因為每張MyISAM的資料表都有一個meta資訊儲存了row_count值，而一致性則由表級鎖來保證。

如果是InnoDB儲存引擎，因為InnoDB支援事務，採用行級鎖和MVCC機制，所以無法像MyISAM一樣，維護一個row_count變數，因此需要採用掃描全表，是O(n)的複雜度，進行迴圈＋計數的方式來完成統計。

環節3: 在InnoDB引擎中，如果採用COUNT(具體欄位)來統計資料行數，要儘量採用二級索引。因為主鍵採用的索引是聚簇索引，聚簇索引包含的資訊多，明顯會大於二級索引(非聚簇索引)。對於COUNT(*)和COUNT(1)來說，它們不需要查詢具體的行，只是統計行數，系統會自動採用佔用空間更小的二級索引來進行統計。

如果有多個二級索引，會使用 key_len 小的二級索引進行掃描。當沒有二級索引的時候，才會採用主鍵索引來進行統計。

12.3 關於SELECT(*)

在表查詢中，建議明確欄位，不要使用 * 作為查詢的欄位列表，推薦使用SELECT <欄位列表> 查詢。原因：

① MySQL 在解析的過程中，會透過 查詢資料字典 將"*"按序轉換成所有列名，這會大大的耗費資源和時間。

② 無法使用 覆蓋索引

12.4 LIMIT 1 對最佳化的影響

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

如果資料表已經對欄位建立了唯一索引，那麼可以透過索引進行查詢，不會全表掃描的話，就不需要加上LIMIT 1了。

12.5 多使用COMMIT

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

COMMIT 所釋放的資源：

  • 回滾段上用於恢復資料的資訊
  • 被程式語句獲得的鎖
  • redo / undo log buffer 中的空間
  • 管理上述 3 種資源中的內部花費

13.淘寶訂單，主鍵設計的學習

13.1 自增ID的問題

自增ID做主鍵，簡單易懂，幾乎所有資料庫都支援自增型別，只是實現上各自有所不同而已。自增ID除了簡單，其他都是缺點，總體來看存在以下幾方面的問題：

1. 可靠性不高

存在自增ID回溯的問題，這個問題直到最新版本的MySQL 8.0才修復。自增ID回溯，是指在MySQL8.0之前，自增id的值，在mysql服務端重啟後，會退回1。

2. 安全性不高

對外暴露的介面可以非常容易猜測對應的資訊。比如：/User/1/這樣的介面，可以非常容易猜測使用者ID的值為多少，總使用者數量有多少，也可以非常容易地透過介面進行資料的爬取。

3. 效能差

自增ID的效能較差，需要在資料庫伺服器端生成。

4. 互動多

業務還需要額外執行一次類似 last_insert_id() 的函式才能知道剛才插入的自增值，這需要多一次的網路互動。在海量併發的系統中，多1條SQL，就多一次效能上的開銷。

5. 區域性唯一性

最重要的一點，自增ID是區域性唯一，只在當前資料庫例項中唯一，而不是全域性唯一，在任意伺服器間都是唯一的。對於目前分散式系統來說，這簡直就是噩夢。

13.2 業務欄位做主鍵

為了能夠唯一地標識一個會員的資訊，需要為會員資訊表設定一個主鍵。那麼，怎麼為這個表設定主鍵，才能達到我們理想的目標呢？ 這裡我們考慮業務欄位做主鍵。

表資料如下：

在這個表裡，哪個欄位比較合適呢？

選擇卡號（cardno）

會員卡號（cardno）看起來比較合適，因為會員卡號不能為空，而且有唯一性，可以用來 標識一條會員記錄。

mysql> CREATE TABLE demo.membermaster
-> (
-> cardno CHAR(8) PRIMARY KEY, -- 會員卡號為主鍵
-> membername TEXT,
-> memberphone TEXT,
-> memberpid TEXT,
-> memberaddress TEXT,
-> sex TEXT,
-> birthday DATETIME
-> );

Query OK, 0 rows affected (0.06 sec)

不同的會員卡號對應不同的會員，欄位“cardno”唯一地標識某一個會員。如果都是這樣，會員卡號與會員一一對應，系統是可以正常執行的。

但實際情況是，會員卡號可能存在重複使用的情況。比如，張三因為工作變動搬離了原來的地址，不再到商家的門店消費了 （退還了會員卡），於是張三就不再是這個商家門店的會員了。但是，商家不想讓這個會 員卡空著，就把卡號是“10000001”的會員卡發給了王五。

從系統設計的角度看，這個變化只是修改了會員資訊表中的卡號是“10000001”這個會員 資訊，並不會影響到資料一致性。也就是說，修改會員卡號是“10000001”的會員資訊， 系統的各個模組，都會獲取到修改後的會員資訊，不會出現“有的模組獲取到修改之前的會員資訊，有的模組獲取到修改後的會員資訊，而導致系統內部資料不一致”的情況。因此，從資訊系統層面上看是沒問題的。

但是從使用系統的業務層面來看，就有很大的問題 了，會對商家造成影響

比如，我們有一個銷售流水錶（trans），記錄了所有的銷售流水明細。2020 年 12 月 01 日，張三在門店購買了一本書，消費了 89 元。那麼，系統中就有了張三買書的流水記錄，如下所示：

接著，我們查詢一下 2020 年 12 月 01 日的會員銷售記錄：

mysql> SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate
-> FROM demo.trans AS a
-> JOIN demo.membermaster AS b
-> JOIN demo.goodsmaster AS c
-> ON (a.cardno = b.cardno AND a.itemnumber=c.itemnumber);
+------------+-----------+----------+------------+---------------------+
| membername | goodsname | quantity | salesvalue | transdate |
+------------+-----------+----------+------------+---------------------+
| 張三        | 書        | 1.000    | 89.00      | 2020-12-01 00:00:00 |
+------------+-----------+----------+------------+---------------------+
1 row in set (0.00 sec)

如果會員卡“10000001”又發給了王五，我們會更改會員資訊表。導致查詢時：

mysql> SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate
-> FROM demo.trans AS a
-> JOIN demo.membermaster AS b
-> JOIN demo.goodsmaster AS c
-> ON (a.cardno = b.cardno AND a.itemnumber=c.itemnumber);
+------------+-----------+----------+------------+---------------------+
| membername | goodsname | quantity | salesvalue | transdate |
+------------+-----------+----------+------------+---------------------+
| 王五        | 書        | 1.000    | 89.00      | 2020-12-01 00:00:00 |
+------------+-----------+----------+------------+---------------------+
1 row in set (0.01 sec)

這次得到的結果是：王五在 2020 年 12 月 01 日，買了一本書，消費 89 元。顯然是錯誤的！結論：千萬不能把會員卡號當做主鍵。

  • 選擇會員電話 或 身份證號

會員電話可以做主鍵嗎？不行的。在實際操作中，手機號也存在被運營商收回，重新發給別人用的情況。

那身份證號行不行呢？好像可以。因為身份證決不會重複，身份證號與一個人存在一一對 應的關係。可問題是，身份證號屬於個人隱私，顧客不一定願意給你。要是強制要求會員必須登記身份證號，會把很多客人趕跑的。其實，客戶電話也有這個問題，這也是我們在設計會員資訊表的時候，允許身份證號和電話都為空的原因。

所以，建議儘量不要用跟業務有關的欄位做主鍵。畢竟，作為專案設計的技術人員，我們誰也無法預測在專案的整個生命週期中，哪個業務欄位會因為專案的業務需求而有重複，或者重用之類的情況出現。

經驗：

剛開始使用 MySQL 時，很多人都很容易犯的錯誤是喜歡用業務欄位做主鍵，想當然地認為了解業務需求，但實際情況往往出乎意料，而更改主鍵設定的成本非常高。

13.3 淘寶的主鍵設計

在淘寶的電商業務中，訂單服務是一個核心業務。請問，訂單表的主鍵淘寶是如何設計的呢？是自增ID嗎？

開啟淘寶，看一下訂單資訊：

從上圖可以發現，訂單號不是自增ID！我們詳細看下上述4個訂單號：

1550672064762308113
1481195847180308113
1431156171142308113
1431146631521308113

訂單號是19位的長度，且訂單的最後5位都是一樣的，都是08113。且訂單號的前面14位部分是單調遞增的。

大膽猜測，淘寶的訂單ID設計應該是：

訂單ID = 時間 + 去重欄位 + 使用者ID後6位尾號

這樣的設計能做到全域性唯一，且對分散式系統查詢及其友好。

13.4 推薦的主鍵設計

非核心業務：對應表的主鍵自增ID，如告警、日誌、監控等資訊。

核心業務：主鍵設計至少應該是全域性唯一且是單調遞增。全域性唯一保證在各系統之間都是唯一的，單調遞增是希望插入時不影響資料庫效能。

這裡推薦最簡單的一種主鍵設計：UUID。

UUID的特點：

全域性唯一，佔用36位元組，資料無序，插入效能差。

認識UUID：

  • 為什麼UUID是全域性唯一的？
  • 為什麼UUID佔用36個位元組？
  • 為什麼UUID是無序的？

MySQL資料庫的UUID組成如下所示：

UUID = 時間+UUID版本（16位元組）- 時鐘序列（4位元組） - MAC地址（12位元組）

我們以UUID值e0ea12d4-6473-11eb-943c-00155dbaa39d舉例：

為什麼UUID是全域性唯一的？

在UUID中時間部分佔用60位，儲存的類似TIMESTAMP的時間戳，但表示的是從1582-10-15 00：00：00.00到現在的100ns的計數。可以看到UUID儲存的時間精度比TIMESTAMPE更高，時間維度發生重複的機率降低到1/100ns。

時鐘序列是為了避免時鐘被回撥導致產生時間重複的可能性。MAC地址用於全域性唯一。

為什麼UUID佔用36個位元組？

UUID根據字串進行儲存，設計時還帶有無用"-"字串，因此總共需要36個位元組。

為什麼UUID是隨機無序的呢？

因為UUID的設計中，將時間低位放在最前面，而這部分的資料是一直在變化的，並且是無序。

改造UUID

若將時間高低位互換，則時間就是單調遞增的了，也就變得單調遞增了。MySQL 8.0可以更換時間低位和時間高位的儲存方式，這樣UUID就是有序的UUID了。

MySQL 8.0還解決了UUID存在的空間佔用的問題，除去了UUID字串中無意義的"-"字串，並且將字串用二進位制型別儲存，這樣儲存空間降低為了16位元組。

可以透過MySQL8.0提供的uuid_to_bin函式實現上述功能，同樣的，MySQL也提供了bin_to_uuid函式進行轉化

SET @uuid = UUID();
SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);


透過函式uuid_to_bin(@uuid,true)將UUID轉化為有序UUID了。全域性唯一 + 單調遞增，這不就是我們想要的主鍵！

4、有序UUID效能測試

16位元組的有序UUID，相比之前8位元組的自增ID，效能和儲存空間對比究竟如何呢？

我們來做一個測試，插入1億條資料，每條資料佔用500位元組，含有3個二級索引，最終的結果如下所示：

從上圖可以看到插入1億條資料有序UUID是最快的，而且在實際業務使用中有序UUID在業務端就可以生成。還可以進一步減少SQL的互動次數。

另外，雖然有序UUID相比自增ID多了8個位元組，但實際只增大了3G的儲存空間，還可以接受。

在當今的網際網路環境中，非常不推薦自增ID作為主鍵的資料庫設計。更推薦類似有序UUID的全域性唯一的實現。

另外在真實的業務系統中，主鍵還可以加入業務和系統屬性，如使用者的尾號，機房的資訊等。這樣的主鍵設計就更為考驗架構師的水平了。

如果不是MySQL8.0腫麼辦？

手動賦值欄位做主鍵！

比如，設計各個分店的會員表的主鍵，因為如果每臺機器各自產生的資料需要合併，就可能會出現主鍵重複的問題。

可以在總部 MySQL 資料庫中，有一個管理資訊表，在這個表中新增一個欄位，專門用來記錄當前會員編號的最大值。

門店在新增會員的時候，先到總部 MySQL 資料庫中獲取這個最大值，在這個基礎上加 1，然後用這個值作為新會員的“id”，同時，更新總部 MySQL 資料庫管理資訊表中的當 前會員編號的最大值。

這樣一來，各個門店新增會員的時候，都對同一個總部 MySQL 資料庫中的資料表欄位進 行操作，就解決了各門店新增會員時會員編號衝突的問題。

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。

相關文章