Author: ACatSmiling
Since: 2024-09-21
資料庫調優的維度：
- 索引失效、沒有充分利用所以 ——
索引建立。 - 關聯查詢太多 JOIN（設計缺陷或不得已的需求）——
SQL 最佳化。 - 伺服器調優及各個引數設定（緩衝、 執行緒數）——
調整 my.cnf。 - 資料過多 ——
分庫分表。
關於資料庫調優的知識點非常分散，不同 DBMS，不同的公司，不同的職位，不同的專案遇到的問題都不盡相同。
雖然 SQL 查詢最佳化的技術很多，但是大體方向上完全可以分為物理查詢最佳化和邏輯查詢最佳化兩大塊。
- 物理查詢最佳化：透過
索引和表連線方式等技術來進行最佳化，這裡重點需要掌握索引的使用。 - 邏輯查詢最佳化：透過
SQL 等價變換提升查詢效率，直白一點來講就是，換一種執行效率更高的查詢寫法。
資料準備
建立儲存過程：
# 建立往 stu 表中插入資料的儲存過程
mysql> DELIMITER //
mysql> 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 //
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;
# 建立往 class 表中插入資料的儲存過程
mysql> DELIMITER //
mysql> 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 //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
呼叫儲存過程：
# 往 class 表新增 1 萬條資料
mysql> CALL insert_class(10000);
Query OK, 0 rows affected (6.75 sec)
# 往 stu 表新增 80 萬條資料
mysql> CALL insert_stu(100000, 800000);
Query OK, 0 rows affected (4 min 30.96 sec)
檢視資料是否新增成功：
mysql> SELECT COUNT(*) FROM class;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
| 800000 |
+----------+
1 row in set (0.02 sec)
建立刪除某個表的索引的儲存過程，避免手動刪除索引：
mysql> DELIMITER //
mysql> 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 //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
呼叫方法：
CALL proc_drop_index("dbname", "tablename");。
索引失效案例
MySQL 中提高效能的一個最有效的方式是對資料表設計合理的索引。索引提供了高校訪問資料的方法，並且加快了查詢的速度，因此索引對查詢的速度有著至關重要的影響。
- 使用索引可以快速的定位表中的某條記錄，從而提高資料庫查詢的速度，提高資料庫的效能。
- 如果查詢時沒有使用索引，查詢語句就會掃描表中的所有記錄。在資料量大的情況下，這樣查詢的速度會很慢。
大多數情況下都（預設）採用 B+Tree 來構建索引。只是空間列型別的索引使用 R-Tree，並且 Memory 表還支援 Hash 索引。
其實，用不用索引，最終都是最佳化器說了算。最佳化器時基於cost 開銷 (CostBaseOptimizer)，它不是基於規則（Rule-BasedOptimizer），也不是基於語義。因此，最佳化器總是選擇開銷最小的。另外，SQL 語句是否使用索引，跟資料庫版本、資料量、資料選擇度都有關係。
cost 開銷的單位不是時間。
全值匹配
全值匹配時，可以充分的利用組合索引，會匹配符合欄位最多的組合索引。
如下所示，系統中經常出現的 SQL 語句，當沒有建立索引時，possible_keys和key都為 NULL：
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 1.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND NAME = 'abcd';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 0.10 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
SQL_NO_CACHE表示不使用查詢快取。
此時執行 SQL，資料查詢速度會比較慢：
mysql> SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND NAME = 'abcd';
Empty set, 1 warning (0.22 sec)
接下來建立索引：
mysql> CREATE INDEX idx_age ON student(age);
Query OK, 0 rows affected (3.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> CREATE INDEX idx_age_classid ON student(age, classId);
Query OK, 0 rows affected (3.60 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> CREATE INDEX idx_age_classid_name ON student(age, classId, name);
Query OK, 0 rows affected (4.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
上面建立索引是與三條 SQL 的使用場景對應的，遵守了
全值匹配的規則，就是說建立幾個複合索引欄位，最好的就是用上這幾個欄位，且按照順序來用。
建立索引後執行，發現使用到了聯合索引（組合索引欄位最多的），且耗時明顯減少：
# 全值匹配，使用的是組合欄位最多的索引 idx_age_classid_name
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND NAME = 'abcd';
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_age,idx_age_classid,idx_age_classid_name | idx_age_classid_name | 73 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
1 row in set, 2 warnings (0.01 sec)
mysql> SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND NAME = 'abcd';
Empty set, 1 warning (0.00 sec)
注意：上面的索引可能不生效，在資料量較大的情況下，進行全值匹配
SELECT *，最佳化器可能經過計算發現，我們使用索引查詢所有的資料後，還需要對查詢到的資料進行回表操作，效能還不如全表掃描。此處因為沒有造這麼多資料，所以不演示效果。
最左匹配原則
在 MySQL 建立聯合索引時會遵守最佳左字首匹配原則，即最左優先，在檢索資料時從組合索引的最左邊開始匹配，如果第一個欄位匹配不上，則整個組合索引都不會生效。
-
下面的 SQL 將使用索引 idx_age：
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.name = 'abcd'; +----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------------+ | 1 | SIMPLE | student | NULL | ref | idx_age,idx_age_classid,idx_age_classid_name | idx_age | 5 | const | 30518 | 10.00 | Using where | +----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) -
下面的 SQL 不會使用索引，因為沒有建立 classId 或者 name 的索引：
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId = 4 AND student.name = 'abcd'; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 1.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) -
下面的 SQL 查詢就是遵守這一原則的正確開啟方式：
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.classId = 4 AND student.name = 'abcd'; +----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | student | NULL | ref | idx_age,idx_age_classid,idx_age_classid_name | idx_age_classid_name | 73 | const,const,const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 2 warnings (0.01 sec) -
下面的 SQL 也會使用索引，因為最佳化器會執行最佳化，調整查詢條件的順序，但開發中應保持良好的開發習慣，保持條件中欄位的順序匹配索引的組合順序：
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId = 4 AND student.age = 30 AND student.name = 'abcd'; +----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | student | NULL | ref | idx_age,idx_age_classid,idx_age_classid_name | idx_age_classid_name | 73 | const,const,const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) -
如果刪去索引 idx_age 和 idx_age_classid，只保留 idx_age_classid_name，執行如下 SQL，也會使用索引。
mysql> DROP INDEX idx_age ON student; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DROP INDEX idx_age_classid ON student; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 # 使用了 idx_age_classid_name索引，但是 key_len 是 5，也就是說只使用了 age 部分的排序，因為 age 是 int 型別，4 個位元組加上 null 值列表一共 5 個位元組。仔細想想，B+Tree 是先按照 age 排序，再按照 classid 排序，最後按照 name 排序，因此不能跳過 classId 的排序直接就使用 name 的排序，所以只使用了 age 的索引 mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.name = 'abcd'; +----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+-------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+-------+----------+-----------------------+ | 1 | SIMPLE | student | NULL | ref | idx_age_classid_name | idx_age_classid_name | 5 | const | 31982 | 10.00 | Using index condition | +----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+-------+----------+-----------------------+ 1 row in set, 2 warnings (0.00 sec)
綜上，MySQL 可以為多個欄位建立索引，一個索引可以包括 16 個欄位，對於多列欄位，過濾條件要使用索引必須按照索引建立時的順序，依次滿足，一旦跳過某個欄位，索引後面的欄位都無法使用，如果查詢條件中沒有使用這些欄位中的第一個欄位時，多列索引不會被使用。
主鍵插入順序
對於一個使用 InnoDB 儲存引擎的表來說，在沒有顯式的建立索引時，表中的資料實際上都是儲存在聚簇索引的葉子節點上的。而記錄又是儲存在資料頁中，資料頁和記錄又是按照記錄主鍵值從小到大的順序進行排序，所以如果我們插入的記錄的主鍵是依次增大的話，那我們每插滿一個資料頁就換到下一個資料頁繼續插入，而如果我們插入的主鍵值忽大忽小的話，就比較麻煩。假設某個資料頁儲存的記錄已經滿了，它儲存的主鍵值在 1 ~ 100 之間：
如果此時再插入一條主鍵值為 9 的記錄，那它插入的位置就如下圖：
可這個資料頁已經滿了，再插進來咋辦呢？我們需要把當前頁面分裂成兩個頁面，把本頁中的一些記錄移動到新建立的這個頁中。頁面分裂和記錄移位意味著什麼？意味著：效能損耗！所以如果想盡量避免這樣無謂的效能損耗，最好讓插入的記錄的主鍵值依次遞增，這樣就不會發生這樣的效能損耗了。 所以我們建議：讓主鍵具有 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 屬性，在插入記錄時儲存引擎會自動填入自增的主鍵值。這樣的主鍵佔用空間小，順序寫入，可以減少頁分裂。
計算導致索引失效
在 student 表的欄位 stuno 上建立索引：
mysql> CREATE INDEX idx_sno ON student(stuno);
Query OK, 0 rows affected (2.73 sec)
Records: 0 Duplicates: 0 Warnings: 0
當有計算條件時，索引失效。示例如下：
mysql> EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900001;
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_sno | idx_sno | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
# 有計算條件，索引失效
mysql> EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno + 1 = 900001;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
可以看到如果對索引進行了表示式計算，索引就失效了。這是因為我們需要把索引欄位的值都取出來，然後一次進行表示式的計算來進行條件判斷，因此採用的就是全表掃描的方式，執行時間也會慢很多：
mysql> SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900001;
Empty set, 1 warning (0.00 sec)
mysql> SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno + 1 = 900001;
+--------+--------+--------+
| id | stuno | NAME |
+--------+--------+--------+
| 800000 | 900000 | skWeFP |
+--------+--------+--------+
1 row in set, 1 warning (0.18 sec)
函式導致索引失效
在 student 表的欄位 name 上建立索引：
mysql> CREATE INDEX idx_name ON student(NAME);
Query OK, 0 rows affected (3.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
當有函式條件時，索引失效。示例如下
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | idx_name | idx_name | 63 | NULL | 40 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
# 有函式，索引失效
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name, 3) = 'abc';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
查詢效率也慢很多：
mysql> SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
+--------+--------+--------+------+---------+
| id | stuno | name | age | classId |
+--------+--------+--------+------+---------+
| 580020 | 680020 | ABcHeA | 14 | 850 |
| 519840 | 619840 | ABcHgL | 9 | 671 |
| 626755 | 726755 | ABcHhL | 11 | 915 |
| 311719 | 411719 | AbCHJl | 9 | 177 |
| 97823 | 197823 | aBCihn | 44 | 540 |
| 385679 | 485679 | AbCIJw | 7 | 364 |
| 160879 | 260879 | ABcIjX | 12 | 256 |
| 371703 | 471703 | aBCijy | 38 | 355 |
| 201505 | 301505 | ABcIkb | 25 | 386 |
| 371290 | 471290 | aBCiky | 39 | 446 |
| 463195 | 563195 | AbCILD | 33 | 608 |
| 472221 | 572221 | AbCJMI | 6 | 586 |
| 605967 | 705967 | AbCJNL | 16 | 430 |
| 289583 | 389583 | abcjNn | 50 | 184 |
| 621037 | 721037 | ABcJor | 42 | 166 |
| 164750 | 264750 | ABcJpw | 13 | 993 |
| 763434 | 863434 | ABcKqB | 33 | 683 |
| 477662 | 577662 | aBCkqY | 48 | 821 |
| 351310 | 451310 | abckQz | 3 | 740 |
| 469852 | 569852 | aBCksi | 37 | 219 |
| 300036 | 400036 | abckSJ | 41 | 64 |
| 277597 | 377597 | aBCktm | 6 | 833 |
| 120380 | 220380 | abckTN | 9 | 565 |
| 336922 | 436922 | AbCKTn | 35 | 649 |
| 625396 | 725396 | AbCKUq | 46 | 578 |
| 10062 | 110062 | ABcLuR | 50 | 431 |
| 340986 | 440986 | aBClus | 28 | 698 |
| 600606 | 700606 | ABcLuT | 9 | 208 |
| 275797 | 375797 | ABcLvT | 10 | 283 |
| 377409 | 477409 | AbCLVu | 12 | 19 |
| 440071 | 540071 | abclVU | 36 | 882 |
| 87638 | 187638 | ABcLvV | 18 | 969 |
| 175531 | 275531 | abclVV | 42 | 372 |
| 321836 | 421836 | ABcLvV | 19 | 90 |
| 2012 | 102012 | AbCLWB | 43 | 618 |
| 400060 | 500060 | abclWY | 4 | 431 |
| 528467 | 628467 | aBClxC | 22 | 528 |
| 622236 | 722236 | abclXc | 22 | 21 |
| 37117 | 137117 | AbCLXF | 9 | 28 |
| 89616 | 189616 | abclXf | 35 | 118 |
+--------+--------+--------+------+---------+
40 rows in set, 1 warning (0.00 sec)
mysql> SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name, 3) = 'abc';
+--------+--------+--------+------+---------+
| id | stuno | name | age | classId |
+--------+--------+--------+------+---------+
| 2012 | 102012 | AbCLWB | 43 | 618 |
| 10062 | 110062 | ABcLuR | 50 | 431 |
| 37117 | 137117 | AbCLXF | 9 | 28 |
| 87638 | 187638 | ABcLvV | 18 | 969 |
| 89616 | 189616 | abclXf | 35 | 118 |
| 97823 | 197823 | aBCihn | 44 | 540 |
| 120380 | 220380 | abckTN | 9 | 565 |
| 160879 | 260879 | ABcIjX | 12 | 256 |
| 164750 | 264750 | ABcJpw | 13 | 993 |
| 175531 | 275531 | abclVV | 42 | 372 |
| 201505 | 301505 | ABcIkb | 25 | 386 |
| 275797 | 375797 | ABcLvT | 10 | 283 |
| 277597 | 377597 | aBCktm | 6 | 833 |
| 289583 | 389583 | abcjNn | 50 | 184 |
| 300036 | 400036 | abckSJ | 41 | 64 |
| 311719 | 411719 | AbCHJl | 9 | 177 |
| 321836 | 421836 | ABcLvV | 19 | 90 |
| 336922 | 436922 | AbCKTn | 35 | 649 |
| 340986 | 440986 | aBClus | 28 | 698 |
| 351310 | 451310 | abckQz | 3 | 740 |
| 371290 | 471290 | aBCiky | 39 | 446 |
| 371703 | 471703 | aBCijy | 38 | 355 |
| 377409 | 477409 | AbCLVu | 12 | 19 |
| 385679 | 485679 | AbCIJw | 7 | 364 |
| 400060 | 500060 | abclWY | 4 | 431 |
| 440071 | 540071 | abclVU | 36 | 882 |
| 463195 | 563195 | AbCILD | 33 | 608 |
| 469852 | 569852 | aBCksi | 37 | 219 |
| 472221 | 572221 | AbCJMI | 6 | 586 |
| 477662 | 577662 | aBCkqY | 48 | 821 |
| 519840 | 619840 | ABcHgL | 9 | 671 |
| 528467 | 628467 | aBClxC | 22 | 528 |
| 580020 | 680020 | ABcHeA | 14 | 850 |
| 600606 | 700606 | ABcLuT | 9 | 208 |
| 605967 | 705967 | AbCJNL | 16 | 430 |
| 621037 | 721037 | ABcJor | 42 | 166 |
| 622236 | 722236 | abclXc | 22 | 21 |
| 625396 | 725396 | AbCKUq | 46 | 578 |
| 626755 | 726755 | ABcHhL | 11 | 915 |
| 763434 | 863434 | ABcKqB | 33 | 683 |
+--------+--------+--------+------+---------+
40 rows in set, 1 warning (0.23 sec)
型別轉換（自動或手動）導致索引失效
當有型別轉換 (自動或手動) 條件時，索引失效。示例如下：
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = '123';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_name | idx_name | 63 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
# 有型別轉換，索引失效
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 123;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_name | NULL | NULL | NULL | 742203 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 5 warnings (0.00 sec)
name = 123發生了型別轉換，相當於使用了隱形函式，索引失效。設計實體類屬性時，一定要與資料庫欄位型別相對應。否則，就會出現型別轉換的情況，進而導致索引失效。
範圍條件右邊的列索引失效
先刪除 student 表上的所有索引，再重新建立索引：
# 呼叫儲存過程刪除所有索引
mysql> CALL proc_drop_index('atguigudb1','student');
Query OK, 0 rows affected (0.06 sec)
# 驗證是否刪除成功
mysql> SELECT index_name FROM information_schema.STATISTICS WHERE table_schema='atguigudb1' AND table_name='student' AND seq_in_index=1 AND index_name <>'PRIMARY';
Empty set (0.00 sec)
# 重新建立組合索引
mysql> CREATE INDEX idx_age_classId_name ON student(age, classId, name);
Query OK, 0 rows affected (4.67 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 驗證是否建立成功
mysql> SELECT index_name FROM information_schema.STATISTICS WHERE table_schema='atguigudb1' AND table_name='student' AND seq_in_index=1 AND index_name <>'PRIMARY';
+----------------------+
| INDEX_NAME |
+----------------------+
| idx_age_classId_name |
+----------------------+
1 row in set (0.01 sec)
對於有範圍條件的，如 <、<=、>、>= 和 between 等，範圍右邊的列不能使用索引。示例如下：
# key_len 為 10，只用到了組合索引的前兩個欄位
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc';
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | idx_age_classId_name | idx_age_classId_name | 10 | NULL | 32002 | 10.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
即使是改變查詢條件的順序，依然不能使用全部索引。因為最佳化器會自動滿足最左字首原則 ，即最佳化器會先根據組合索引進行排序，組合索引的順序決定了哪些列不能正常使用索引。
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.name ='abc' AND student.classId > 20;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | idx_age_classId_name | idx_age_classId_name | 10 | NULL | 32002 | 10.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
擴充套件：為什麼範圍查詢會導致索引失效？
因為根據範圍查詢篩選後的資料，無法保證範圍查詢後面的欄位是有序的。例如：a_b_c 這個索引，根據 b 範圍查詢 > 2 的，在滿足 b > 2 的情況下，如 b 為 "3, 4"，c 可能是 "5, 3"，因為 c 是無序的，那麼 c 的索引也便失效了。
針對上述情況，可以建立如下索引（範圍欄位放在最後）進行改進：
# 將 classId 放到組合索引最後
mysql> CREATE INDEX idx_age_name_classId ON student(age, name, classId);
Query OK, 0 rows affected (4.84 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 同一個查詢，使用到了全部的組合索引欄位
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.name ='abc' AND student.classId > 20;
+----+-------------+---------+------------+-------+-------------------------------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-------------------------------------------+----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | idx_age_classId_name,idx_age_name_classId | idx_age_name_classId | 73 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+-------------------------------------------+----------------------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
在應用開發中範圍查詢，例如：金額查詢，日期查詢往往都是範圍查詢。此時，應將查詢條件放置 WHERE 語句最後，在建立的組合索引中，也需要把範圍涉及到的欄位寫在最後。
不等於（!= 或者 <>）索引失效
對於有不等於 (!= 或者 <>) 判斷的，索引失效。示例如下：
mysql> CREATE INDEX idx_name ON student(NAME);
Query OK, 0 rows affected (3.80 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_name | NULL | NULL | NULL | 742203 | 50.16 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_name | NULL | NULL | NULL | 742203 | 50.16 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
IS NULL 可以使用索引，IS NOT NULL 無法使用索引
示例如下：
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name IS NULL;
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | ref | idx_name | idx_name | 63 | const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
# IS NOT NULL，索引失效
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name IS NOT NULL;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_name | NULL | NULL | NULL | 742203 | 50.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
因此，最好在設計資料庫的時候就將 欄位設定 NOT NULL 約束。比如可以將 INT 型別的欄位，預設設定為 0，將字串型別的欄位，預設設定為空字串（""）。
擴充套件：同理，在查詢中使用
NOT LIKE也無法使用索引，會導致全表掃描。
LIKE 以萬用字元 % 開頭索引失效
在使用 LIKE 關鍵字時，如果匹配字串的第一個字元為 "%"，索引失效。只有 "%" 不在第一個位置，索引才會起作用。
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | idx_name | idx_name | 63 | NULL | 1201 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
# % 放在首位，索引失效
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
擴充：Alibaba Java 開發手冊。
【強制】頁面搜尋嚴禁左模糊或者全模糊，如果需要請走搜尋引擎來解決。
OR 前後存在非索引的列，索引失效
在 WHERE 子句中，如果在 OR 前的條件列進行了索引，而在 OR 後的條件列沒有進行索引，那麼索引會失效。也就是說，OR 前後的兩個條件中的列都是索引時，查詢中才使用索引。因為 OR 的含義就是兩個只要滿足一個即可，因此只有一個條件列進行了索引是沒有意義的，只要有條件列沒有進行索引，就會進行全表掃描，因此索引的條件列也會失效。（OR 前後一個使用索引，一個進行全表掃描，合起來還沒有直接進行全表掃描更快。）
示例如下：
# 建立索引，此時只有 OR 前面的欄位有索引
mysql> CREATE INDEX idx_age ON student(age);
Query OK, 0 rows affected (3.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 未使用索引
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
+----+-------------+---------+------------+------+---------------------------------------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------------------------------------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_age_classId_name,idx_age_name_classId,idx_age | NULL | NULL | NULL | 742203 | 11.88 | Using where |
+----+-------------+---------+------------+------+---------------------------------------------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
# 再為 OR 後面的欄位建立一個索引
mysql> CREATE INDEX idx_cid ON student(classid);
Query OK, 0 rows affected (3.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 使用了索引
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
+----+-------------+---------+------------+-------------+-----------------------------------------------------------+-----------------+---------+------+-------+----------+-------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+-----------------------------------------------------------+-----------------+---------+------+-------+----------+-------------------------------------------+
| 1 | SIMPLE | student | NULL | index_merge | idx_age_classId_name,idx_age_name_classId,idx_age,idx_cid | idx_age,idx_cid | 5,5 | NULL | 30807 | 100.00 | Using union(idx_age,idx_cid); Using where |
+----+-------------+---------+------------+-------------+-----------------------------------------------------------+-----------------+---------+------+-------+----------+-------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
資料庫和表的字符集統一使用 utf8mb4/utf8mb3
統一使用 utf8mb4（5.5.3 版本以上支援）相容性更好，統一字符集可以避免由於字符集轉換產生的亂碼。不同的字符集進行比較前，需要進行轉換，會造成索引失效。
小結
一般性建議：
- 對於單列索引，儘量選擇針對當前 QUERY 過濾性更好的索引。
- 在選擇組合索引的時候，當前 QUERY 中過濾性最好的欄位在索引欄位順序中，位置越靠前越好。
- 在選擇組合索引的時候，儘量選擇能夠包含當前 QUERY 中的 WHERE 子句中更多欄位的索引。
- 在選擇組合索引的時候，如果某個欄位可能出現範圍查詢時，儘量把這個欄位放在索引次序的最後面。
總之，書寫 SQL 語句時，儘量避免造成索引失效的情況。
關聯查詢最佳化
資料準備
建立 Type 表：
mysql> CREATE TABLE IF NOT EXISTS `type` (
-> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `card` INT(10) UNSIGNED NOT NULL,
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected, 2 warnings (0.02 sec)
建立 Book 表：
mysql> CREATE TABLE IF NOT EXISTS `book` (
-> `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `card` INT(10) UNSIGNED NOT NULL,
-> PRIMARY KEY (`bookid`)
-> );
Query OK, 0 rows affected, 2 warnings (0.05 sec)
插入資料：
# type 表插入 20 條資料，以下 SQL 執行 20 次
mysql> INSERT INTO type(card) VALUES(FLOOR(1 + RAND() * 20));
Query OK, 1 row affected (0.01 sec)
# book 表插入 20 條資料，以下 SQL 執行 20 次
mysql> INSERT INTO book(card) VALUES(FLOOR(1 + RAND() * 20));
Query OK, 1 row affected (0.00 sec)
mysql> SELECT COUNT(*) FROM type;
+----------+
| COUNT(*) |
+----------+
| 20 |
+----------+
1 row in set (0.01 sec)
mysql> SELECT COUNT(*) FROM book;
+----------+
| COUNT(*) |
+----------+
| 20 |
+----------+
1 row in set (0.01 sec)
採用左外連線
多表查詢分為外連線和內連線，而外連線又分為左外連線，右外連線和滿外連線。其中外連線中，左外連線與右外連線可以透過交換表來相互改造，其原理也是類似的，而滿外連線無非是二者的一個綜合，因此外連線只介紹左外連線的最佳化即可。
首先，當沒有使用索引時，進行 EXPLAIN 分析，可以看到是全表掃描：
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | type | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)
- 在上面的查詢 SQL 中，type 表是驅動表，book 表是被驅動表。在執行查詢時，會先查詢驅動表中符合條件的資料，再根據驅動表查詢到的資料，在被驅動表中根據匹配條件查詢對應的資料。因此被驅動表巢狀查詢的次數是 20 * 20 = 400 次。實際上，由於總是需要在被驅動表中進行查詢，最佳化器幫我們已經做了最佳化，上面的查詢結果中可以看到，使用了
join buffer，將資料快取起來，提高檢索的速度。
然後，為了提高外連線的效能，新增以下索引：
# book 表 card 欄位新增索引
mysql> CREATE INDEX Y ON book(card);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | type | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | Y | Y | 4 | atguigudb1.type.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
- 對於外層表來說，雖然其查詢仍然是全表掃描，但是因為是左外連線，LEFT JOIN 左邊的表的資料無論是否滿足條件都會保留，因此全表掃描也是可以的。另外可以看到第二行的 type 變為了 ref，rows 也變成了 1，最佳化比較明顯。這是由左連線特性決定的。
LEFT JOIN 條件用於確定如何從右表搜尋行，左邊一定都有，所以右邊是關鍵點，一定需要建立索引。
當然也可以給 type 表建立索引：
# type 表 card 欄位新增索引
mysql> CREATE INDEX X ON type(card);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 雖然 type 表 card 欄位建立了索引，但是無法避免全表掃描
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card = book.card;
+----+-------------+-------+------------+-------+---------------+------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | type | NULL | index | NULL | X | 4 | NULL | 20 | 100.00 | Using index |
| 1 | SIMPLE | book | NULL | ref | Y | Y | 4 | atguigudb1.type.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+----------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
注意：
外連線的關聯條件中，兩個關聯欄位的型別、字符集一定要保持一致，否則索引會失效。
刪除索引 Y，繼續查詢：
# 刪除索引
mysql> DROP INDEX Y ON book;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card = book.card;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | type | NULL | index | NULL | X | 4 | NULL | 20 | 100.00 | Using index |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)
- book 表使用了 join buffer，再次驗證了左外連線左邊的表是驅動表，右邊的表是被驅動表，後面將與內連線在這一點進行對比。
左外連結左表是驅動表右表是被驅動表，右外連結和此相反，內連結則是按照資料量的大小，資料量少的是驅動表，多的是被驅動表。
採用內連線
刪除現有的索引，換成 INNER JOIN（MySQL 會自動選擇驅動表）：
mysql> DROP INDEX X ON type;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP INDEX Y ON book;
ERROR 1091 (42000): Can't DROP 'Y'; check that column/key exists
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | type | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)
為 book 表新增索引：
mysql> ALTER TABLE book ADD INDEX Y (card);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 此時，book 表使用了索引，type 表沒有索引，type 表為驅動表，book 表為被驅動表
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | type | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | Y | Y | 4 | atguigudb1.type.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
向 type 表中再增加 20 條資料，觀察情況：
mysql> INSERT INTO type(card) VALUES(FLOOR(1 + RAND() * 20));
Query OK, 1 row affected (0.02 sec)
mysql> SELECT COUNT(*) FROM type;
+----------+
| COUNT(*) |
+----------+
| 40 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM book;
+----------+
| COUNT(*) |
+----------+
| 20 |
+----------+
1 row in set (0.00 sec)
# 此時，雖然 book 表資料少於 type 表，但是因為 type 表有索引，type 表為驅動表，book 表仍然為被驅動表
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | type | NULL | ALL | NULL | NULL | NULL | NULL | 40 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | Y | Y | 4 | atguigudb1.type.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
為 type 表增加索引：
mysql> ALTER TABLE type ADD INDEX X (card);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 此時，type 表和 book 表都有索引，因為 book 表的資料少於 type 表，所以 book 表為驅動表，type 表為被驅動表
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;
+----+-------------+-------+------------+-------+---------------+------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | book | NULL | index | Y | Y | 4 | NULL | 20 | 100.00 | Using index |
| 1 | SIMPLE | type | NULL | ref | X | X | 4 | atguigudb1.book.card | 2 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+----------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
對於內連線，查詢最佳化器可以決定誰作為驅動表，誰作為被驅動表。當都沒有索引時，表資料量少的是驅動表，多的是被驅動表；當只有一方有索引時，沒有索引的表是驅動表，有索引的表是被驅動表（因為被驅動表查詢次數更多）；當都有索引時，則是表資料量少的是驅動表，多的是被驅動表（小表驅動大表）。
上面的規律不是一成不變的，如果一個表有索引，但是資料量很小，一個表沒有索引，但是資料量很大，情況會是怎樣的呢？我們要明白
最佳化器的最佳化原理：對於內連線，MySQL 會選擇掃描次數比較少的作為驅動表，因此實際生產中最好使用 EXPLAIN 測試驗證。
JOIN 語句原理
JOIN 方式連線多表，本質就是各個表之間資料的迴圈匹配。MySQL 5.5 版本之前，MySQL 只支援一種表間關聯方式，就是巢狀迴圈（Nested Loop）。如果關聯表的資料量很大，則 JOIN 關聯的執行時間會非常漫長。在 MySQL 5.5 以後的版本中，MySQL 透過引入 BNLJ 演算法來最佳化巢狀執行。
驅動表和被驅動表
驅動表就是主表，被驅動表就是從表、非驅動表。
對於內連線：
SELECT * FROM A JOIN B ON ...
- A 不一定是驅動表，最佳化器會根據查詢語句做最佳化，決定先查哪張表。先查詢的哪張表就是驅動表，反之就是被驅動表。透過 EXPLAIN 關鍵字可以檢視。
對於外連線：
SELECT * FROM A LEFT JOIN B ON ...
# 或
SELECT * FROM B RIGHT JOIN A ON ...
-
通常，大家會認為 A 就是驅動表，B 就是被驅動表，但也未必。測試如下：
mysql> CREATE TABLE a(f1 INT,f2 INT,INDEX(f1)) ENGINE=INNODB; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE b(f1 INT,f2 INT) ENGINE=INNODB; Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> INSERT INTO b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 # 測試 mysql> EXPLAIN SELECT * FROM a LEFT JOIN b ON a.f1 = b.f1 WHERE a.f2 = b.f2; +----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+ | 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where | | 1 | SIMPLE | a | NULL | ref | f1 | f1 | 5 | atguigudb1.b.f1 | 1 | 16.67 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) -
雖然 SQL 語句是 a LEFT JOIN b，但實際執行時，b 是驅動表，a 是被驅動表。這是因為
查詢最佳化器會把外連線改造為內連線，然後根據其最佳化策略選擇驅動表與被驅動表。
Simple Nested-Loop Join（簡單巢狀迴圈連線）
演算法相當簡單，從表 A 取出一條資料 1，然後遍歷表 B，將匹配到的資料放到 result。以此類推，驅動表 A 中的每一條記錄，都會與被動驅動表 B 的全部記錄進行判斷：
可以看到這種方式效率是非常低的，假設以上述表 A 資料 100 條，表 B 資料 1000 條，則 A * B = 10 萬次。開銷統計如下：
| 開銷統計 | SNLJ |
|---|---|
| 外表掃描次數 | 1 |
| 內表掃描次數 | A |
| 讀取記錄數 | A + B * A |
| JOIN 比較次數 | B * A |
| 回表讀取記錄次數 | 0（沒有索引，不涉及回表） |
當然，MySQL 肯定不會這麼粗暴的進行表的連線，所以就出現了後面的兩種最佳化演算法。另外，從讀取記錄數來看：A + B * A中，驅動表 A 對效能的影響權重更大，因此，最佳化器會選擇小表驅動大表。
Index Nested-Loop Join（索引巢狀迴圈連線）
Index Nested-Loop Join，其最佳化的思路主要是為了減少內層表資料的匹配次數，所以要求被驅動表上必須有索引才行。透過外層表匹配條件直接與內層索引進行匹配，避免和內層表的每條記錄進行比較，這樣極大地減少了對內層表的匹配次數。
驅動表中的每條記錄透過被驅動表的索引進行訪問，因為索引查詢的成本是比較固定的，故 MySQL 最佳化器都傾向於使用記錄數少的表作為驅動表（外表）。
| 開銷統計 | SNLJ | INLJ |
|---|---|---|
| 外表掃描次數 | 1 | 1 |
| 內表掃描次數 | A | 0 |
| 讀取記錄數 | A + B * A | A + B(match) |
| JOIN 比較次數 | B * A | A * Index(Height) |
| 回表讀取記錄次數 | 0 | B(match)（if possible） |
如果被驅動表加索引，效率是非常高的，如果索引不是主鍵索引，還需要進行一次回表查詢。相比之下，如果被驅動表的索引是主鍵索引，效率會更高。
Block Nested-Loop Join（快巢狀迴圈連線）
如果存在索引，那麼會使用 INDEX 的方式進行 JOIN，如果 JOIN 的列沒有索引，則被驅動表要掃描的次數太多了。因為每次訪問被驅動表，其表中的記錄都會被載入到記憶體中，然後再從驅動表中取一條與其匹配，匹配結束後清除記憶體，然後再從驅動表中載入一條記錄，然後把驅動表的記錄再載入到記憶體匹配，這樣週而復始，大大增加了 I/O 次數。為了減少被驅動表的 I/O 次數，就出現了Block Nested-Loop Join的方式。
BNLJ 不再是逐條獲取驅動表的資料，而是一塊一塊的獲取，並引入了 join buffer 緩衝區，將驅動表 JOIN 相關的部分資料列（每一批次的大小受 join buffer 的限制）快取到 join buffer 中，然後全表掃描被驅動表，被驅動表的每一條記錄一次性和 join buffer 中的所有驅動表記錄進行匹配（記憶體中操作），將簡單巢狀迴圈中的多次比較合併成一次，降低了被驅動表的訪問頻率。
注意：這裡快取的不只是關聯表的列，SELECT 後面的列也會快取起來，在一個有 N 個JOIN 關聯的 SQL 中會分配 N - 1 個 join buffer。所以查詢的時候儘量減少不必要的欄位，這樣可以讓 join buffer 中存放更多的列。
| 開銷統計 | SNLJ | INLJ | BNLJ |
|---|---|---|---|
| 外表掃描次數 | 1 | 1 | 1 |
| 內表掃描次數 | A | 0 | A * used_column_size / join_buffer_size + 1 |
| 讀取記錄數 | A + B * A | A + B(match) | A + B * (A * used_column_size / join_buffer_size) |
| JOIN 比較次數 | B * A | A * Index(Height) | B * A |
| 回表讀取記錄次數 | 0 | B(match)（if possible） | 0 |
引數設定：
-
block_nested_loop：透過SHOW VARIABLES LIKE '%optimizer_switch%'檢視 block_nested_loop 狀態，預設是開啟的。mysql> SHOW VARIABLES LIKE '%optimizer_switch%'; +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on | +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) -
join_buffer_size：驅動表能不能一次載入完，要看 join buffer 能不能儲存所有的資料，預設情況下 join_buffer_size = 256 KB。join buffer size 的最大值在 32 位系統可以申請 4 GB，而在 64 位操做系統下可以申請大於 4 GB 的 join_buffer空間（64 位 Windows 除外，其大值會被截斷為 4 GB，併發出警告）。
小結
- 保證被驅動表的 JOIN 欄位已經建立了索引（減少內層表的迴圈匹配次數）。
- 需要 JOIN 的欄位，資料型別保持絕對一致。
- LEFT JOIN 時，選擇小表作為驅動表， 大表作為被驅動表，減少外層迴圈的次數。
- INNER JOIN 時，MySQL 會自動將小結果集的表選為驅動表，選擇相信 MySQL 最佳化策略。
- 能夠直接多表關聯的儘量直接關聯，不用子查詢。（減少查詢的趟數）
- 不建議使用子查詢，建議將子查詢 SQL 拆開，並結合程式多次查詢，或使用 JOIN 來代替子查詢。
- 衍生表建不了索引。
- 預設效率比較：INLJ > BNLJ > SNLJ。
- 正確理解小表驅動大表：大小不是指表中的記錄數，而是永遠用小結果集驅動大結果集（其本質就是減少外層迴圈的資料數量）。 比如 A 表有 100 條記錄，B 表有 1000 條記錄，但是 WHERE 條件過濾後，B 表結果集只留下 50 個記錄，A 表結果集有 80 條記錄，此時就可能是 B 表驅動 A 表。其實上面的例子還是不夠準確，因為結果集的大小也不能粗略的用結果集的行數表示，而是錶行數 * 每行大小。其實要理解這一點，只需要結合 join buffer 就好了，因為錶行數 * 每行大小越小，其佔用記憶體越小，就可以在 join buffer 中儘量少的次數載入完了。
Hash Join
從 MySQL 8.0.20 版本開始，將廢棄 BNLJ，因為加入了 Hash Join，預設都會使用 Hash Join。
Nested Loop 與 Hash Join 對比如下：
| 型別 | Nested Loop | Hash Join |
|---|---|---|
| 使用條件 | 任何條件 | 等值連線（=） |
| 相關資源 | CPU、磁碟 I/O | 記憶體、臨時空間 |
| 特點 | 當有高選擇性索引或進行限制性搜尋時效率比較高，能夠快速返回第一次的搜尋結果 | 當缺乏索引或者索引條件模糊時，Hash Join 比 Nested Loop 有效。在資料倉儲環境下，如果表的記錄數多，效率高 |
| 缺點 | 當索引丟失或者查詢條件限制不夠時，效率很低；當表的記錄數較多，效率低 | 為建立雜湊表，需要大量記憶體。第一次的結果返回較慢 |
- Nested Loop：對於被連線的資料子集較小的情況，Nested Loop 是個較好的選擇。
- Hash Join 是做
大資料集連線時的常用方法，最佳化器使用兩個表中較小（相對較小）的表利用 join key 在記憶體中建立雜湊表，然後掃描較大的表並探測雜湊表，找出與 Hash 表匹配的行。- 這種方式適用於較小的表完全可以放於記憶體中的情況，這樣總成本就是訪問兩個表的成本之和。
- 在表很大的情況下並不能完全放入記憶體，這時最佳化器會將它分割成若干不同的分割槽，不能放入記憶體的部分就把該分割槽寫入磁碟的臨時段，此時要求有較大的臨時段，從而儘量提高 I/O 的效能。
- 它能夠很好的工作於沒有索引的大表和並行查詢的環境中，並提供最好的效能。大多數人都說它是 Join 的重型升降機。Hash Join 只能應用於等值連線（如 WHERE A.COL1 = B.COL2），這是由 Hash 的特點決定的。
子查詢最佳化
MySQL 從 4.1 版本開始支援子查詢，使用子查詢可以進行 SELECT 語句的巢狀查詢，即一個 SELECT 查詢的結果作為另一個 SELECT 語句的條件，子查詢可以一次性完成很多邏輯上需要多個步驟才能完成的操作。
子查詢是 MySQL 的一項重要的功能，可以幫助我們透過一個 SQL 語句實現比較複雜的查詢。但是，子查詢的執行效率不高，通常可以將其最佳化成一個連線查詢。
原因：
- 執行子查詢時，MySQL 需要為內層查詢語句的查詢結果建立一個
臨時表，然後外層查詢語句從臨時表中查詢記錄。查詢完畢後，再撤銷這些臨時表。這樣會消耗過多的 CPU 和 IO 資源，產生大量的慢查詢。 - 子查詢的結果集儲存的臨時表，不論是記憶體臨時表還是磁碟臨時表都
不會存在索引，所以查詢效能會受到一定的影響。 - 對於返回結果集越大的子查詢，其對查詢效能的影響也就越大。
在 MySQL 中，可以使用連線（JOIN）查詢來替代子查詢。 連線查詢不需要建立臨時表，其速度比子查詢要快，如果查詢中使用索引的話，效能就會更好。
示例 1：查詢學生表中是班長的學生資訊。
-
使用子查詢：
# 建立班級表中班長的索引 mysql> CREATE INDEX idx_monitor ON class(monitor); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 # 詢班長的資訊 mysql> EXPLAIN SELECT * FROM student stu1 WHERE stu1.stuno IN (SELECT monitor FROM class c WHERE monitor IS NOT NULL); +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-----------------------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-----------------------+--------+----------+--------------------------+ | 1 | SIMPLE | stu1 | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | NULL | | 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | atguigudb1.stu1.stuno | 1 | 100.00 | NULL | | 2 | MATERIALIZED | c | NULL | index | idx_monitor | idx_monitor | 5 | NULL | 9952 | 100.00 | Using where; Using index | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-----------------------+--------+----------+--------------------------+ 3 rows in set, 1 warning (0.00 sec) -
推薦，使用連線查詢：
mysql> EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c ON stu1.stuno = c.monitor WHERE c.monitor IS NOT NULL; +----+-------------+-------+------------+------+---------------+-------------+---------+-----------------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------------+---------+-----------------------+--------+----------+-------------+ | 1 | SIMPLE | stu1 | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | Using where | | 1 | SIMPLE | c | NULL | ref | idx_monitor | idx_monitor | 5 | atguigudb1.stu1.stuno | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+-------------+---------+-----------------------+--------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
示例 2：所有不為班長的同學。
-
使用子查詢：
# 查詢不為班長的學生資訊 mysql> EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a WHERE a.stuno NOT IN (SELECT monitor FROM class b WHERE monitor IS NOT NULL); +----+-------------+-------+------------+-------+---------------+-------------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+--------+----------+--------------------------+ | 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | Using where | | 2 | SUBQUERY | b | NULL | index | idx_monitor | idx_monitor | 5 | NULL | 9952 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+--------+----------+--------------------------+ 2 rows in set, 2 warnings (0.00 sec) -
推薦，使用連線查詢：
mysql> EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a LEFT OUTER JOIN class b ON a.stuno =b.monitor WHERE b.monitor IS NULL; +----+-------------+-------+------------+------+---------------+-------------+---------+--------------------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------------+---------+--------------------+--------+----------+--------------------------+ | 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | NULL | | 1 | SIMPLE | b | NULL | ref | idx_monitor | idx_monitor | 5 | atguigudb1.a.stuno | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+------+---------------+-------------+---------+--------------------+--------+----------+--------------------------+ 2 rows in set, 2 warnings (0.00 sec)
儘量不要使用 NOT IN 或者 NOT EXISTS，用
LEFT JOIN xxx ON xx WHERE xx IS NULL替代。
ORDER BY 排序最佳化
排序方式
問題：在 WHERE 條件欄位上加索引，但是為什麼在 ORDER BY 欄位上還要加索引呢？
在 MySQL 中，支援兩種排序方式，分別是FileSort和Index 排序。
-
Index 排序中，索引可以保證資料的有序性，就不需要再進行排序，效率更更高。
-
FileSort 排序則一般在
記憶體中進行排序，佔用 CPU 較多。如果待排序的結果較大，會產生臨時檔案 I/O 到磁碟進行排序的情況，效率低。
最佳化建議：
- SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是
在 WHERE 子句中避免全表掃描，在 ORDER BY 子句中避免使用 FileSort 排序。當然，某些情況下全表掃描，或者 FileSort 排序不一定比索引慢。但總的來說，我們還是要避免，以提高查詢效率。 - 儘量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 後面是相同的列就使用單索引列；如果不同就使用聯合索引。
- 無法使用 Index 時，需要對 FileSort 方式進行調優。
最佳化例項
執先案例前，呼叫儲存過程刪除 student 和 class 表上的索引，只留主鍵：
mysql> CALL proc_drop_index('atguigudb1','student');
Query OK, 0 rows affected (0.10 sec)
mysql> CALL proc_drop_index('atguigudb1','class');
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT index_name FROM information_schema.STATISTICS WHERE table_schema='atguigudb1' AND table_name='student' AND seq_in_index=1 AND index_name <>'PRIMARY';
Empty set (0.00 sec)
mysql> SELECT index_name FROM information_schema.STATISTICS WHERE table_schema='atguigudb1' AND table_name='class' AND seq_in_index=1 AND index_name <>'
PRIMARY';
Empty set (0.00 sec)
使用 LIMIT 引數
不加 LIMIT 引數：
# 全表掃描，使用 filesort 排序
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 2 warnings (0.00 sec)
加上 LIMIT 引數：
# 全表掃描，使用 filesort 排序
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid LIMIT 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 2 warnings (0.00 sec)
使用索引
建立索引，但是不加 LIMIT 限制，索引失效：
mysql> CREATE INDEX idx_age_classid_name ON student (age, classid, name);
Query OK, 0 rows affected (4.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 未加 LIMIT 引數，全表掃描，使用 filesort 排序
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 2 warnings (0.00 sec)
- 雖然建立了索引，但是未加 LIMIT 引數得時候，最佳化器透過計算發現，需要回表的資料量特別大，使用索引的效能代價反而比不上不用索引的，因此並未使用索引。
現在，新增 LIMIT 引數：
# 可以看到，使用了索引
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid LIMIT 10;
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | index | NULL | idx_age_classid_name | 73 | NULL | 10 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
假如只查詢組合索引中有的欄位，觀察結果：
# 可以看到，此時也使用了索引，因為不涉及回表
mysql> EXPLAIN SELECT SQL_NO_CACHE age, classid, name, id FROM student ORDER BY age, classid;
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | index | NULL | idx_age_classid_name | 73 | NULL | 742203 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
ODRER BY 時順序錯誤，索引失效
# 建立索引 age，classid，stuno
mysql> CREATE INDEX idx_age_classid_stuno ON student (age, classid, stuno);
Query OK, 0 rows affected (4.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 索引失效
mysql> EXPLAIN SELECT * FROM student ORDER BY classid LIMIT 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
# 索引失效
mysql> EXPLAIN SELECT * FROM student ORDER BY classid, name LIMIT 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
# 索引有效
mysql> EXPLAIN SELECT * FROM student ORDER BY age, classid, stuno LIMIT 10;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | index | NULL | idx_age_classid_stuno | 14 | NULL | 10 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# 索引有效
mysql> EXPLAIN SELECT * FROM student ORDER BY age, classid LIMIT 10;
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | index | NULL | idx_age_classid_name | 73 | NULL | 10 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# 索引有效
mysql> EXPLAIN SELECT * FROM student ORDER BY age LIMIT 10;
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | index | NULL | idx_age_classid_name | 73 | NULL | 10 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
ODRER BY 時規則不一致，索引失效
順序錯，不索引；方向反，不索引。
# 索引失效
mysql> EXPLAIN SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
# 索引失效
mysql> EXPLAIN SELECT * FROM student ORDER BY classid DESC, name DESC LIMIT 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
# 索引失效
mysql> EXPLAIN SELECT * FROM student ORDER BY age ASC, classid DESC LIMIT 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
# 索引有效（方向保持一致，要正都正，要反都反）
mysql> EXPLAIN SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+---------------------+
| 1 | SIMPLE | student | NULL | index | NULL | idx_age_classid_name | 73 | NULL | 10 | 100.00 | Backward index scan |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)
無過濾，不索引
# 索引有效
mysql> EXPLAIN SELECT * FROM student WHERE age = 45 ORDER BY classid;
+----+-------------+---------+------------+------+--------------------------------------------+----------------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------------------------------+----------------------+---------+-------+-------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_age_classid_name,idx_age_classid_stuno | idx_age_classid_name | 5 | const | 29880 | 100.00 | NULL |
+----+-------------+---------+------------+------+--------------------------------------------+----------------------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# 索引有效
mysql> EXPLAIN SELECT * FROM student WHERE age = 45 ORDER BY classid, name;
+----+-------------+---------+------------+------+--------------------------------------------+----------------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------------------------------+----------------------+---------+-------+-------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_age_classid_name,idx_age_classid_stuno | idx_age_classid_name | 5 | const | 29880 | 100.00 | NULL |
+----+-------------+---------+------------+------+--------------------------------------------+----------------------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# 索引失效
mysql> EXPLAIN SELECT * FROM student WHERE classid = 45 ORDER BY age;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 10.00 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
# 索引有效
mysql> EXPLAIN SELECT * FROM student WHERE classid = 45 ORDER BY age LIMIT 10;
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | index | NULL | idx_age_classid_name | 73 | NULL | 10 | 10.00 | Using where |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
# student 表建立 classid 索引
mysql> CREATE INDEX idx_cid ON student(classid);
Query OK, 0 rows affected (3.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 索引有效
mysql> EXPLAIN SELECT * FROM student WHERE classid = 45 ORDER BY age;
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+----------------+
| 1 | SIMPLE | student | NULL | ref | idx_cid | idx_cid | 5 | const | 804 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
小結
對於索引：
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 /* 對於排序來說，多個相等條件也是範圍查詢 */
可以從 SQL 執行順序方面來思考，WHERE 的執行順序，排在 ORDER BY 之前。
避免 FileSort 排序例項
下面，透過一個案例來實戰 FileSort 和 Index 兩種排序。對 ORDER BY 子句，儘量使用 Index 方式排序，避免使用 FileSort 方式排序。
場景：查詢年齡為 30 歲的，且學生編號小於 101000 的學生，按使用者名稱稱排序。
執行前先清除 student 上的索引，只留主鍵：
mysql> CALL proc_drop_index('atguigudb1','student');
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT index_name FROM information_schema.STATISTICS WHERE table_schema='atguigudb1' AND table_name='student' AND seq_in_index=1 AND index_name <>'PRIMARY';
Empty set (0.01 sec)
測試以下的查詢，此時顯然使用的是 FileSort 進行排序：
# type 是 ALL，即最壞的情況，Extra 裡還出現了 Using filesort，也是最壞的情況，最佳化是必須的
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY name;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 3.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 2 warnings (0.00 sec)
方案一：為了去掉 FileSort 我們可以建立特定索引。
# 建立索引
mysql> CREATE INDEX idx_age_name ON student(age, name);
Query OK, 0 rows affected (4.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 可以看到已經使用了索引，雖然僅僅使用到了 age 這個欄位
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno < 101000 ORDER BY name;
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+-------+----------+-------------+
| 1 | SIMPLE | student | NULL | ref | idx_age_name | idx_age_name | 5 | const | 30062 | 33.33 | Using where |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+-------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
方案二：儘量讓 WHERE 的過濾條件和排序使用上索引。
# 刪除舊索引
mysql> DROP INDEX idx_age_name ON student;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 新建索引
mysql> CREATE INDEX idx_age_stuno_name ON student (age, stuno, name);
Query OK, 0 rows affected (4.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 測試結果，雖然使用了索引，但是用的 FileSort 方式排序
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY name;
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | student | NULL | range | idx_age_stuno_name | idx_age_stuno_name | 9 | NULL | 17 | 100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+------+----------+---------------------------------------+
1 row in set, 2 warnings (0.00 sec)
原因：因為所有的排序都是在條件過濾之後才執行的，所以，如果條件過濾大部分資料的話，剩下幾百幾千條資料進行排序其實並不是很消耗效能，即使索引最佳化了排序，但實際提升效能很有限。相對的 stuno < 10100 這個條件，如果沒有用到索引的話，要對幾萬條資料進行掃描，這是非常消耗效能的，所以索引放在這個欄位上價效比最高，是最優選擇。
結論：
- 兩個索引同時存在，MySQL 自動選擇最優的方案。（對於這個例子，MySQL 選擇 idx_age_stuno_name）。但是，隨著資料量的變化，選擇的索引也會隨之變化的 。
- 當 "範圍條件" 和 "GROUP BY 或者 ORDER BY" 的欄位出現二選一時，優先觀察條件欄位的過濾數量，如果過濾的資料足夠多，而需要排序的資料並不多時，優先把索引放在範圍欄位上。反之，亦然。
FileSort 演算法
排序的欄位若不在索引列上，則 FileSort 會有兩種演算法：雙路排序和單路排序。
雙路排序（慢）
MySQL 4.1 之前是使用雙路排序，字面意思就是兩次掃描磁碟，最終得到資料， 讀取行指標和 ORDER BY 列，對它們進行排序，然後掃描已經排序好的列表，按照列表中的值重新從列表中讀取對應的資料輸出。
- 從磁碟取排序欄位，在 buffer 進行排序，再從 磁碟取其他欄位。
因為取一批資料，要對磁碟進行兩次掃描，眾所周知，I/O 是很耗時的，所以在 MySQL 4.1 之後，出現了第二種改進的演算法，就是單路排序。
單路排序（快）
從磁碟讀取查詢需要的所有列，按照 ORDER BY 列在 buffer 對它們進行排序，然後掃描排序後的列表進行輸出，它的效率更快一些，避免了第二次讀取資料。並且把隨機 I/O 變成了順序 I/O，但是它會使用更多的空間， 因為它把每一行都儲存在記憶體中了。
單路排序總體是好過雙路排序的，但是單路排序也存在問題：在 sort_buffer 中，單路排序比多路排序要佔用更多空間。因為單路排序是把所有欄位都取出，所以可能取出的資料的總大小超出了 sort_buffer 的容量，導致每次只能取 sort_buffer 容量大小的資料，再進行排序（建立 temp 檔案，多路合併），排完再取 sort_buffer 容量大小，依次進行，從而導致多次 I/O。單路排序本來想省一次 I/O 操作，反而導致了大量的 I/O 操作，得不償失。
最佳化策略：
-
提高 sort_buffer_size。-
不管用哪種演算法，提高這個引數都會提高效率，但是要根據系統的能力去提高，因為這個引數是針對每個程序（connection）的 1MB ~ 8MB 之間調整。MySQL 5.7，InnoDB 儲存引擎預設值都是 1048576 位元組，即 1MB。
mysql> SHOW VARIABLES LIKE '%sort_buffer_size%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | innodb_sort_buffer_size | 1048576 | | myisam_sort_buffer_size | 8388608 | | sort_buffer_size | 262144 | +-------------------------+---------+ 3 rows in set (0.00 sec)
-
-
提高 max_length_for_sort_data。-
提高這個引數，會增加改進演算法的機率。但是如果設的太高，資料總容量超出 sort_buffer_size 的機率就增大，明顯症狀是高的磁碟 I/O 活動和低的處理器使用率。如果需要返回的列的總長度大於 max_length_for_sort_data，使用雙路演算法，否則使用單路演算法，可以在 1024 ~ 8192 位元組之間調整。
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)
-
-
ORDER BY 時避免使用 SELECT *，儘量只 Query 所需要的欄位。- 當 Query 的欄位大小綜合小於 max_length_for_sort_data，而且排序欄位不是 TEXT|BLOG 型別時，會使用改進後的演算法——單路排序，否則用老演算法——多路排序。
- 兩種演算法的資料都有可能超出 sort_buffer_size 的容量，超出之後，會建立 tmp 檔案進行合併排序，導致多次 I/O，但是用單路排序演算法的風險會更大一些，所以要提高 sort_buffer_size。
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、GROUP BY、DISTINCT 這些語句較為耗費 CPU，資料庫的 CPU 資源是極其寶貴的。
- 包含了 ORDER BY、GROUP BY、DISTINCT 這些查詢的語句，WHERE 條件過濾出來的結果集請保持在 1000 行以內，否則 SQL 會很慢。
LIMIT 分頁最佳化
一般分頁查詢時，透過建立覆蓋索引能夠比較好地提高效能。一個常見又非常頭疼的問題就是LIMIT 2000000, 10，此時需要 MySQL 排序前 2000010 記錄，僅僅返回 2000000-2000010 的記錄，其他記錄丟棄，查詢排序的代價非常大。
mysql> EXPLAIN SELECT * FROM student LIMIT 2000000, 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)
最佳化思路一
-
在索引上完成排序分頁操作，最後根據主鍵關聯回原表查詢所需要的其他列內容。
mysql> EXPLAIN SELECT * FROM student t, (SELECT id FROM student ORDER BY id LIMIT 2000000, 10) a WHERE t.id = a.id; +----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 100.00 | NULL | | 1 | PRIMARY | t | NULL | eq_ref | PRIMARY | PRIMARY | 4 | a.id | 1 | 100.00 | NULL | | 2 | DERIVED | student | NULL | index | NULL | PRIMARY | 4 | NULL | 742203 | 100.00 | Using index | +----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec)
最佳化思路二
-
該方案適用於主鍵自增的表，可以把 LIMIT 查詢轉換成某個位置的查詢。
mysql> EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10; +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | student | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where | +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT * FROM student LIMIT 2000000, 10; Empty set (0.20 sec) mysql> SELECT * FROM student WHERE id > 2000000 LIMIT 10; Empty set (0.00 sec)
優先考慮覆蓋索引
什麼是覆蓋索引
理解方式一：索引是高效找到行的一個方法，但是一般資料庫也能使用索引找到一個列的資料，因此它不必讀取整個行。畢竟索引葉子節點儲存了它們索引的資料；當能透過讀取索引就可以得到想要的資料，那就不需要讀取行了。一個索引包含了滿足查詢結果的資料就叫做覆蓋索引。
理解方式二：非聚簇複合索引的一種形式，它包括在查詢裡的 SELECT、JOIN 和 WHERE 子句用到的所有列（即建索引的欄位正好是覆蓋查詢條件中所涉及的欄位）。
簡單說就是， 覆蓋索引的 "索引列 + 主鍵" 包含了 SELECT 到 FROM 之間查詢的列。
示例一：
# 先刪除索引
mysql> CALL proc_drop_index('atguigudb1','student');
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT index_name FROM information_schema.STATISTICS WHERE table_schema='atguigudb1' AND table_name='student' AND seq_in_index=1 AND index_name <>'PRIMARY';
Empty set (0.00 sec)
# 新建索引，student 表的 age 和 name 欄位
mysql> CREATE INDEX idx_age_name ON student (age, name);
Query OK, 0 rows affected (4.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 使用了不等於條件，索引失效
mysql> EXPLAIN SELECT * FROM student WHERE age <> 20;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_age_name | NULL | NULL | NULL | 742203 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 也使用了不等於條件，但使用了索引 idx_age_name，因為當前查詢的欄位只有 age 和 name，idx_age_name 在此處是覆蓋索引
mysql> EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+
| 1 | SIMPLE | student | NULL | index | idx_age_name | idx_age_name | 68 | NULL | 742203 | 100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
注意：前面提到如果使用上 <> 就不會使用上索引，這並不是絕對的，比如上面這條 SQL。需要明確的一點是，關於索引失效以及索引最佳化，都是根據
效率來決定的。對於二級索引來說：查詢時間 = 二級索引計算時間 + 回表查詢時間，由於此處使用的是覆蓋索引，回表查詢時間 = 0，索引最佳化器考慮到這一點，就使用上二級索引了。
示例二：
mysql> EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 742203 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 與示例一的 <> 同理，此處，LIKE 的 % 在開頭，但仍然使用了索引 idx_age_name，idx_age_name 在此處也是覆蓋索引
mysql> EXPLAIN SELECT id, age FROM student WHERE NAME LIKE '%abc';
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+
| 1 | SIMPLE | student | NULL | index | NULL | idx_age_name | 68 | NULL | 742203 | 11.11 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
覆蓋索引的利弊
好處：
-
可以避免 Innodb 表進行索引的二次查詢（回表）。
- Innodb 是以聚集索引的順序來儲存的，對於 Innodb 來說，二級索引在葉子節點中所儲存的是行的主鍵資訊，如果是用二級索引查詢資料，在查詢到相應的鍵值後，還需透過主鍵進行二次查詢才能獲取真實所需要的其他欄位的資料。
- 在覆蓋索引中，二級索引的鍵值中可以獲取所要的資料，避免了對主鍵的二次查詢，減少了 I/O 操作，提升了查詢效率。
-
可以把隨機 I/O 變成順序 I/O 加快查詢效率。
- 由於覆蓋索引是按鍵值的順序儲存的，對於 I/O 密集型的範圍查詢來說，對比隨機從磁碟讀取每一行的資料 I/O 要少的多，因此利用覆蓋索引在訪問時也可以把磁碟的隨機讀取的 I/O 轉變成索引查詢的順序 I/O。
- 由於覆蓋索引可以減少樹的搜尋次數，顯著提升查詢效能，所以使用覆蓋索引是一個常用的效能最佳化手段。
弊端：
- 索引欄位的維護總是有代價的。因此，在建立冗餘索引來支援覆蓋索引時就需要權衡考慮了。這是業務 DBA，或者稱為業務資料架構師的工作。
如何給字串新增索引
假設有一張教師表，表定義如下：
CREATE TABLE teacher(
ID bigint unsigned PRIMARY KEY,
email varchar(64),
...
)ENGINE=innodb;
講師要使用郵箱登入，所以業務程式碼中一定會出現類似於這樣的語句：
SELECT col1, col2 FROM teacher WHERE email = 'xxx';
如果 email 這個欄位上沒有索引，那麼這個語句就只能做全表掃描 。
字首索引
MySQL是支援字首索引的。預設地，如果你建立索引的語句不指定字首長度，那麼索引就會包含整個字串。
ALTER TABLE teacher ADD INDEX index1(email);
# 或
ALTER TABLE teacher ADD INDEX index2(email(6));
這兩種不同的定義在資料結構和儲存上有什麼區別呢？下圖就是這兩個索引的示意圖：
如果使用的是 index1 （即 email 整個字串的索引結構），執行順序是這樣的：
-
從 index1 索引樹找到滿足索引值是 "zhangssxyz@xxx.com" 的這條記錄，取得 ID2 的值；
-
到主鍵上查到主鍵值是 ID2 的行，判斷 email 的值是正確的，將這行記錄加入結果集；
-
取 index1 索引樹上剛剛查到的位置的下一條記錄，發現已經不滿足email = "zhangssxyz@xxx.com" 的條件了，迴圈結束。
-
這個過程中，只需要回主鍵索引取一次資料，所以系統認為只掃描了一行。
如果使用的是 index2（即 email(6) 索引結構），執行順序是這樣的：
- 從 index2 索引樹找到滿足索引值是 "zhangs " 的記錄，找到的第一個是 ID1；
- 到主鍵上查到主鍵值是 ID1 的行，判斷出 email 的值不是 "zhangssxyz@xxx.com"，這行記錄丟棄；
- 取 index2 上剛剛查到的位置的下一條記錄，發現仍然是 "zhangs"，取出 ID2，再到 ID 索引上取整行然後判斷，這次值對了，將這行記錄加入結果集；
- 重複上一步，直到在 idxe2 上取到的值不是 "zhangs" 時，迴圈結束。
- 也就是說
使用字首索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查詢成本。前面已經講過區分度，區分度越高越好。因為區分度越高，意味著重複的鍵值越少。
字首索引對覆蓋索引的影響
前面我們說了使用字首索引可能會增加掃描行數，這會影響到效能。其實，字首索引的影響不止如此，我們再看一下另外一個場景：
如果使用 index1（即 email 整個字串的索引結構）的話，可以利用覆蓋索引，從 index1 查到結果後直接就返回了，不需要回到 ID 索引再去查一次。而如果使用 index2（即 email(6) 索引結構）的話，就不得不回到 ID 索引再去判斷 email 欄位的值。
即使你將 index2 的定義修改為 email(18) 的字首索引，這時候雖然 index2 已經包含了所有的資訊，但 InnoDB 還是要回到 id 索引再查一下，因為系統並不確定字首索引的定義是否截斷了完整資訊。
結論：使用字首索引就用不上覆蓋索引對查詢效能的最佳化了，這也是你在選擇是否使用字首索引時需要考慮的一個因素。
擴充內容
對於類似於郵箱這樣的欄位來說，使用字首索引的效果可能還不錯。但是，遇到字首的區分度不夠好的情況時，我們要怎麼辦呢?
比如，我們國家的身份證號，一共 18 位，其中前 6 位是地址碼，所以同一個縣的人的身份證號前 6 位一般會是相同的。
假設你維護的資料庫是一個市的公民資訊系統，這時候如果對身份證號做長度為 6 的字首索引的話，這個索引的區分度就非常低了。按照我們前面說的方法，可能你需要建立長度為 12 以上的字首索引，才能夠滿足區分度要求。
但是，索引選取的越長，佔用的磁碟空間就越大，相同的資料頁能放下的索引值就越少，搜尋的效率也就會越低。
那麼，如果我們能夠確定業務需求裡面只有按照身份證進行等值查詢的需求，還有沒有別的處理方法呢？要求這種方法，既可以佔用更小的空間，也能達到相同的查詢效率。有!
第一種方式是使用倒序儲存。如果你儲存身份證號的時候把它倒過來存，每次查詢的時候：
mysql> SELECT field list FROM teacher WHERE id_card = reverse(input_id_card_string);
由於身份證號的最後 6 位沒有地址碼這樣的重複邏輯，所以最後這 6 位很可能就提供了足夠的區分度。當然，實踐中你還要使用 COUNT(DISTINCT) 方法去做驗證區分度。
第二種方式是使用 hash 欄位。你可以在表上再建立一個整數字段，來儲存身份證的校驗碼，同時在這個欄位上建立索引。
mysql> ALTER TABLE teacher ADD id_card_crc int unsignedadd INDEX(id_card_crc);
然後每次插入新記錄的時候，都同時用 crc32() 這個函式得到校驗碼填到這個新欄位，由於校驗碼可能存在衝突，也就是說兩個不同的身份證號透過 crc32() 函式得到的結果可能是相同的，所以你的查詢語句 WHERE 部分要判斷 id_card 的值是否精確相同。
mysql> SELECT field list FROM teacher WHERE id_card_rc = crc32(input_id_card_string) AND id_card = input id_card_string;
這樣，索引的長度變成了 4 個位元組，比原來小了很多。
從查詢效率上看，使用 hash 欄位方式的查詢效能相對更穩定一些，因為 crc32 算出來的值雖然有衝突的機率，但是機率非常小，可以認為每次查詢的平均掃描行數接近 1。而倒序儲存方式畢竟還是用的字首索引的方式，也就是說還是會增加掃描行數。
索引條件下推（索引下推）
Index Condition Pushdown (ICP) 是 MySQL 5.6 中新特性，是一種在儲存引擎層使用索引過濾資料的一種最佳化方式。ICP 可以減少儲存引擎訪問基表的次數，以及 MySQL 伺服器訪問儲存引擎的次數。
索引中包含某個欄位，但是實際查詢沒有使用到這個欄位的索引（失效了，比如該欄位的條件為 "%a%"），此時可以使用這個欄位在索引中進行條件過濾，從而可以減少回表的記錄條數，這種情況就叫做索引條件下推/索引下推。
使用前後對比
在不使用 ICP 索引掃描的過程：
- Storage 層：只將滿足 index key 條件的索引記錄對應的整行記錄取出，返回給 Server 層。
- Server 層：對返回的資料，使用後面的 WHERE 條件過濾，直至返回最後一行。
使用 ICP 掃描的過程：
- Storage層：首先將 index key 條件滿足的索引記錄區間確定，然後在索引上使用 index filter 進行過濾。將滿足的 index filter 條件的索引記錄才去回表取出整行記錄返回 Server 層。不滿足 index filter 條件的索引記錄丟棄，不回表、也不會返回 Server 層。
- Server 層：對返回的資料，使用 table filter 條件做最後的過濾。
使用前後的成本差別：
- 使用 ICP 前，儲存層多返回了需要被 index filter 過濾掉的整行記錄。
- 使用 ICP 後，直接就去掉了不滿足 index filter 條件的記錄，省去了這些記錄回表和傳遞到 Server 層的成本。
- ICP 的加速效果取決於在儲存引擎內透過 ICP 篩選掉的資料的比例。
ICP 的開啟、關閉
預設情況下啟用索引條件下推。可以透過設定系統變數optimizer_switch 控制index_condition_pushdown：
mysql> SHOW VARIABLES LIKE '%optimizer_switch%';
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 關閉索引下推
mysql> SET optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
# 開啟索引下推
mysql> SET optimizer_switch='index_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)
當使用索引條件下推時，
EXPLAIN語句輸出的結果中Extra列內容如果顯示為Using index condition，即為索引條件下推。
ICP 使用案例
建表：
mysql> CREATE TABLE people (
-> `id` int NOT NULL AUTO_INCREMENT,
-> `zipcode` varchar(20) COLLATE utf8_bin DEFAULT NULL,
-> `firstname` varchar(20) COLLATE utf8_bin DEFAULT NULL,
-> `lastname` varchar(20) COLLATE utf8_bin DEFAULT NULL,
-> `address` varchar(50) COLLATE utf8_bin DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> KEY `zip_last_first` (`zipcode`, `lastname`, `firstname`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;
Query OK, 0 rows affected, 6 warnings (0.04 sec)
插入資料：
INSERT INTO people VALUES
(1, '000001', 'san', 'zhang', 'beijing'),
(2, '000002', 'si', 'li', 'nanjing'),
(3, '000003', 'wu', 'wang', 'shanghai'),
(4, '000001', 'liu', 'zhao', 'tianjin');
為該表定義聯合索引 zip_last_first(zipcode, lastname, firstname) 。如果我們知道了一個人的郵編，但是不確定這個人的姓氏，可以進行如下檢索：
mysql> SELECT * FROM people WHERE zipcode= '000001' AND lastname LIKE '%zhang%' AND address LIKE '%beijing%';
+----+---------+-----------+----------+---------+
| id | zipcode | firstname | lastname | address |
+----+---------+-----------+----------+---------+
| 1 | 000001 | san | zhang | beijing |
+----+---------+-----------+----------+---------+
1 row in set (0.00 sec)
# 查詢計劃
mysql> EXPLAIN SELECT * FROM people WHERE zipcode= '000001' AND lastname LIKE '%zhang%' AND address LIKE '%beijing%';
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+------------------------------------+
| 1 | SIMPLE | people | NULL | ref | zip_last_first | zip_last_first | 63 | const | 2 | 25.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
- 從查詢計劃可以看出，Extra 中顯示了 Using index condition，這表示使用了索引下推。即：先使用索引的 zipcode 欄位進行匹配，然後索引下推使用 lastname 欄位進行過濾，最後再進行回表。
- 另外，Using where 表示條件中包含需要過濾的非索引列的資料，即 "address LIKE '%beijing%'" 這個條件並不是索引列，需要在服務端過濾掉。
為了便於 MySQL 客戶端查詢，將相關漢字替換為了拼音。
這個 PEOPLE 表中存在兩個索引，分別是：
-
主鍵索引（簡圖）：
-
二級索引 zip_last_first：
下面關閉 IPC，再次檢視執行計劃：
mysql> SET optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
# 關閉 IPC 後，不再使用索引下推
mysql> EXPLAIN SELECT * FROM people WHERE zipcode= '000001' AND lastname LIKE '%zhang%' AND address LIKE '%beijing%';
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | people | NULL | ref | zip_last_first | zip_last_first | 63 | const | 2 | 25.00 | Using where |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
開啟和關閉 ICP 的效能對比
建立儲存過程，主要目的是插入很多 000001 的資料，這樣查詢的時候，為了在儲存引擎層坐果率，減少 I/O，也為了減少緩衝池（快取資料頁，沒有 I/O）的作用。
mysql> DELIMITER //
mysql> CREATE PROCEDURE inser_people(max_num INT)
-> BEGIN
-> DECLARE i INT DEFAULT 0;
-> SET autocommit = 0;
-> REPEAT
-> SET i = i + 1;
-> INSERT INTO people(zipcode, firstname, lastname, address) VALUES('000001', 'liu', 'zhao', 'tianjin');
-> UNTIL i = max_num
-> END REPEAT;
-> COMMIT;
-> END //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
呼叫儲存過程：
mysql> CALL inser_people(1000000);
Query OK, 0 rows affected (27.68 sec)
首先，開啟 profiling：
mysql> SET profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
執行 SQL 語句，此時索引下推開啟：
mysql> SET optimizer_switch='index_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM people WHERE zipcode = '000001' AND lastname LIKE '%zhang%';
+----+---------+-----------+----------+---------+
| id | zipcode | firstname | lastname | address |
+----+---------+-----------+----------+---------+
| 1 | 000001 | san | zhang | beijing |
+----+---------+-----------+----------+---------+
1 row in set (0.25 sec)
再次執行 SQL 語句，此時索引下推關閉：
mysql> SET optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM people WHERE zipcode = '000001' AND lastname LIKE '%zhang%';
+----+---------+-----------+----------+---------+
| id | zipcode | firstname | lastname | address |
+----+---------+-----------+----------+---------+
| 1 | 000001 | san | zhang | beijing |
+----+---------+-----------+----------+---------+
1 row in set (2.52 sec)
檢視當前會話所產生的所有 profiles：
mysql> SHOW profiles\G;
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.24520475
Query: SELECT * FROM people WHERE zipcode = '000001' AND lastname LIKE '%zhang%'
*************************** 2. row ***************************
Query_ID: 2
Duration: 2.52617775
Query: SELECT * FROM people WHERE zipcode = '000001' AND lastname LIKE '%zhang%'
3 rows in set, 1 warning (0.00 sec)
ERROR:
No query specified
- 多次測試效率對比來看，使用 ICP 最佳化的查詢效率會好一些。這裡建議多儲存一些資料，效果更明顯。
ICP 的使用條件
- 只能用於二級索引（Secondary Index）。
- EXPLAIN 顯示的執行計劃中 type 值（join 型別）為 range 、ref、eq_ref 或者 ref_or_null 。
- 並非全部 WHERE 條件都可以用 ICP 篩選，如果 WHERE 條件的欄位不在索引列中，還是要讀取整表的記錄到 Server 端做 WHERE 過濾。(IPC 是 Storage 層行為)
- ICP 可以用於 MyISAM 和 InnnoDB 儲存引擎。
- MySQL 5.6 版本的不支援分割槽表的 ICP 功能，5.7 版本的開始支援。
- 當 SQL 使用覆蓋索引時，不支援 ICP 最佳化方法。
普通索引 vs 唯一索引
在不同的業務場景下，應該選擇普通索引，還是唯一索引？
假設你在維護一個居民系統，每個人都有一個唯一的身份證號，而且業務程式碼已經保證了不會寫入兩個重複的身份證號。如果居民系統需要按照身份證號查姓名：
SELECT name FROM CUser WHERE id_card = 'xxxxxxxyyyyyyzzzzz';
所以，你一定會考慮在 id_card 欄位上建索引。
由於身份證號欄位比較大，不建議把身份證號當做主鍵。現在有兩個選擇，要麼給 id_card 欄位建立唯一素引，要麼建立一個普通索引。如果業務程式碼已經保證了不會寫入重複的身份證號，那麼這兩個選擇邏輯上都是正確的。
你知道的，InnoDB 的資料是按資料頁為單位來讀寫的。也就是說，當需要讀一條記錄的時候，並不是將這個記錄本身從磁碟讀出來，而是以頁為單位，將其整體讀入記憶體。在 InnoDB中，每個資料頁的大小預設是 16 KB。
從效能的角度考慮，你選擇唯一索引還是普通索引呢？選擇的依據是什麼呢？
假設，我們有一個主鍵列為 id 的表，表中有欄位 k，並且在 k 上有索引，假設欄位 k 上的值都不重複。 這個表的建表語句是：
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)。
查詢過程
假設，執行查詢的語句是 "SELECT id FROM test WHERE k = 5"。
- 對於普通索引來說，查詢到滿足條件的第一個記錄 (5, 500) 後，需要查詢下一個記錄，直到碰到第一個不滿足 k = 5 條件的記錄。
- 對於唯一索引來說，由於索引定義了唯一性，查詢到第一個滿足條件的記錄後，就會停止繼續檢索。
那麼，這個不同帶來的效能差距會有多少呢？答案是， 微乎其微。
你知道的，InnoDB 的資料是按資料頁為單位來讀寫的。也就是說，當需要讀一條記錄的時候，並不是將這個記錄本身從磁碟讀出來，而是以頁為單位，將其整體讀入記憶體。在 InnoDB 中，每個資料頁的大小預設是 16KB。
因為引擎是按頁讀寫的，所以說，當找到 k = 5 的記錄的時候，它所在的資料頁就都在記憶體裡了。那麼，對於普通索引來說，要多做的那一次 "查詢和判斷下一條記錄" 的操作，就只需要一次指標尋找和一次計算。
當然，如果 k = 5 這個記錄剛好是這個資料頁的最後一個記錄，那麼要取下一個記錄，必須讀取下一個資料頁，這個操作會稍微複雜一些。
但是，我們之前計算過，對於整型欄位，一個資料頁可以放近千個 key，因此出現這種情況的機率會很低。所以我們計算平均效能差異時，仍可以認為這個操作成本對於現在的 CPU 來說可以忽略不計。
更新過程
為了說明普通索引和唯一索引對更新語句效能的影響這個問題，介紹一下change buffer。
當需要更新一個資料頁時，如果資料頁在記憶體中就直接更新，而如果這個資料頁還沒有在記憶體中的話， 在不影響資料一致性的前提下，InooDB 會將這些更新操作快取在 change buffer 中，這樣就不需要從磁碟中讀入這個資料頁了。在下次查詢需要訪問這個資料頁的時候，將資料頁讀入記憶體，然後執行 change buffer 中與這個頁有關的操作。透過這種方式就能保證這個資料邏輯的正確性。
將 change buffer 中的操作應用到原資料頁，得到最新結果的過程稱為merge。除了訪問這個資料頁會觸發 merge 外，系統有後臺執行緒會定期 merge。在資料庫正常關閉 (shutdown)的過程中，也會執行 merge 操作。
如果能夠將更新操作先記錄在 change buffer，減少讀磁碟，語句的執行速度會得到明顯的提升。而且， 資料讀入記憶體是需要佔用 buffer pool 的，所以這種方式還能夠避免佔用記憶體，提高記憶體利用率。
那麼，什麼條件下可以使用 change buffer 呢？
對幹唯一索引來說，所有的更新操作都要先判斷這個操作是否違反唯一性約束。比如，要插入 (4, 400) 這個記錄，就要先判斷現在表中是否已經存在 k = 4 的記錄，而這必須要將資料頁讀入記憶體才能判斷。如果都已經讀入到記憶體了，那直接更新記憶體會更快，就沒必要使用 change buffer 了。
因此，唯一索引的更新就不能使用 change buffer，實際上也只有普通索引可以使用。
change buffer 用的是 buffer pool 裡的記憶體，因此不能無限增大。change buffer 的大小，可以透過引數innodb_change_buffer_maxsize來動態設定。這個引數設定為 50 的時候，表示 changebuffer 的大小最多隻能佔用 buffer pool 的 50%。
mysql> SHOW VARIABLES LIKE '%change_buffer%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
+-------------------------------+-------+
2 rows in set (0.01 sec)
那麼，如果要在這張表中插入一個新記錄 (4, 400) 的話，InnoDB 的處理流程是怎樣的？
第一種情況是，這個記錄要更新的目標頁在記憶體中。這時：
- 對幹唯一索引來說，找到 3 和 5 之間的位置，判斷為沒有衝突，插入這個值，語句執行結束。
- 對於普通索引來說，找到 3 和 5 之間的位置，插入這個值，語句執行結束。
- 這樣看來，普通索引和唯一索引對更新語句效能影響的差別，只是一個判斷，只會耗費微小的 CPU 時間。
第二種情況是，這個記錄要更新的目標頁不在記憶體中。這時：
- 對於唯一索引來說，需要將資料頁讀入記憶體，判斷有沒有衝突，插入這個值，語句執行結束。
- 對於普通索引來說，則是將更新記錄在 change buffer，語句執行就結束了。
將資料從磁碟讀入記憶體涉及隨機 I/O 的訪問，是資料庫裡面成本最高的操作之一。change buffer 因為減少了隨機磁碟訪問，所以對更新效能的提升是會很明顯的。
案例：
某個業務的庫記憶體命中率突然從 99% 降低到了 75%，整個系統處於阻塞狀態，更新語句全部堵住。而探究其原因後，發現這個業務有大量插入資料的操作，而他在前一天把其中的某個普通索引改成了唯一索引。
change buffer 的使用場景
change buffer 只限於用在普通索引的場景下，而不適用於唯一索引。那麼，現在有一個問題就是：普通索引的所有場景，使用 change buffer 都可以起到加速作用嗎？
因為 merge 的時候是真正進行資料更新的時刻，而 change buffer 的主要目的就是將記錄的變更動作快取下來，所以在一個資料頁做 merge 之前，change buffer 記錄的變更越多（也就是這個頁面上要更新的次數越多），收益就越大。
因此，對於寫多讀少的業務來說，頁面在寫完以後馬上被訪問到的機率比較小，此時 change buffer 的使用效果最好。這種業務模型常見的就是賬單類、日誌類的系統。
反過來，假設一個業務的更新模式是寫入之後馬上會做查詢，那麼即使滿足了條件，將更新先記錄在 change buffer，之後由於馬上要訪問這個資料頁，會立即觸發 merge 過程，這樣隨機訪問 I/O 的次數不會減少，反而增加了 change buffer 的維護代價。所以，對於這種業務模式來說，changebuffer 反而起到了副作用。
- 普通索引和唯一索引應該怎麼選擇？其實，這兩類索引在查詢能力上是沒差別的，主要考慮的是對
更新效能的影響。所以，建議你儘量選擇普通索引。 - 在實際使用中會發現，普通索引和 change buffer 的配合使用，對於
資料量大的表的更新最佳化還是很明顯的。 - 如果所有的更新後面，都馬上
伴隨著對這個記錄的查詢，那麼你應該關閉change buffer。而在其他情況下，change buffer 都能提升更新效能。 - 由於唯一索引用不上 change buffer 的最佳化機制，因此如果
業務可以接受，從效能角度出發建議優先考慮非唯一索引。但是如果 "業務可能無法確保" 的情況下，怎麼處理呢？- 首先，
業務正確性優先。我們的前提是 "業務程式碼已經保證不會寫入重複資料" 的情況下，討論效能問題。如果業務不能保證，或者業務就是要求資料庫來做約束，那麼沒得選，必須建立唯一索引。 這種情況下，本節的意義在於，如果碰上了大量插入資料慢、記憶體命中率低的時候，給你多提供一個排查思路。 - 然後，在一些 "
歸檔庫" 的場景，你是可以考慮使用唯一索引的。比如，線上資料只需要保留半年， 然後歷史資料儲存在歸檔庫。這時候，歸檔資料已經是確保沒有唯一鍵衝突了。要提高歸檔效率， 可以考慮把表裡面的唯一索引改成普通索引。
- 首先，
其它查詢最佳化策略
EXISTS 和 IN 的區分
問題：
不太理解哪種情況下應該使用 EXISTS，哪種情況應該用 IN。選擇的標準是看能否使用表的索引嗎？
回答:
索引是個前提，其實選擇與否還是要看錶的大小。你可以將選擇的標準理解為小表驅動大表，在這種方式下效率是最高的。
比如下面這樣：
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
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。
COUNT(*) 與 COUNT(具體欄位) 效率對比
問：在 MySQL 中統計資料表的行數，可以使用三種方式SELECT COUNT(*)、SELECT COUNT(1)和SELECT COUNT(具體欄位)，使用這三者之間的查詢效率是怎樣的？
答：
前提：如果你要統計的是某個欄位的非空資料行數，則另當別論，畢竟比較執行效率的前提是結果一樣才可以。
環節 1：COUNT(*) 和 COUNT(1) 都是對所有結果進行 COUNT，COUNT(*) 和 COUNT(1) 本質上並沒有區別（二者執行時間可能略有差別，不過你還是可以把它倆的執行效率看成是相等的）。如果有 WHERE 子句，則是對所有符合篩選條件的資料行進行統計；如果沒有 WHERE 子句，則是對資料表的資料行數進行統計。
環節 2：COUNT(*) 和 COUNT(1) 的使用，如果是 MyISAM 儲存引擎，統計資料表的行數只需要O(1)的複雜度，這是因為每張 MyISAM 的資料表都有一個 meta 資訊儲存了 row_count 值，而一致性則由表級鎖來保證。如果是 InnoDB 儲存引擎，因為 InnoDB 支援事務，採用行級鎖和 MVCC 機制，所以無法像 MyISAM 一樣，維護一個 row_count 變數，因此需要採用掃描全表，是O(n)的複雜度，進行迴圈 + 計數的方式來完成統計。
環節 3：在 InnoDB 引擎中，如果採用 COUNT(具體欄位) 來統計資料行數，要儘量採用二級索引。因為主鍵採用的索引是聚簇索引，聚簇索引包含的資訊多，資源消耗明顯會大於二級索引。對於 COUNT(*) 和COUNT(1) 來說，它們不需要查詢具體的行，只是統計行數，系統會自動採用佔用空間更小的二級索引來進行統計，如果有多個二級索引，會使用 keylen 小的二級索引進行掃描，當沒有二級索引的時候，才會採用主鍵索引來進行統計。
關於 SELECT(*)
在表查詢中，建議明確欄位，不要使用 * 作為查詢的欄位列表，推薦使用 SELECT <欄位列表> 查詢。原因：
- MySQL 在解析的過程中，會透過
查詢資料字典將 "*" 按序轉換成所有列名，這會大大的耗費資源和時間。 - 無法使用
覆蓋索引。
LIMIT 1 對最佳化的影響
針對的是會掃描全表的 SQL 語句，如果你可以確定結果集只有一條，那麼加上LIMIT 1的時候，當找到一條結果的時候就不會繼續掃描了，這樣會加快查詢速度。
如果資料表已經對欄位建立了唯一索引，那麼可以透過索引進行查詢，不會全表掃描的話，就不需要加上LIMIT 1了。
多使用 COMMIT
只要有可能，在程式中儘量多使用 COMMIT，這樣程式的效能得到提高，需求也會因為 COMMIT 所釋放的資源而減少。
COMMIT 所釋放的資源:
- 回滾段上用於恢復資料的資訊。
- 被程式語句獲得的鎖。
- redo/undo log buffer 中的空間。
- 管理上述 3 種資源中的內部花費。
淘寶資料庫，主鍵如何設計的
聊一個實際問題：淘寶的資料庫，主鍵是如何設計的？
某些錯的離譜的答案還在網上年復一年的流傳著，甚至還成為了所謂的 MySQL 軍規。其中，一個最明顯的錯誤就是關於 MySQL 的主鍵設計。
大部分人的回答如此自信：用 8 位元組的 BIGINT 做主鍵，而不要用 INT。錯!
這樣的回答，只站在了資料庫這一層，而沒有從業務的角度思考主鍵。主鍵就是一個自增 ID 嗎？站在 2022 年的新年檔口，用自增做主鍵，架構設計上可能連及格都拿不到。
自增 ID 的問題
自增 ID 做主鍵，簡單易懂，幾乎所有資料庫都支援自增型別，只是實現上各自有所不同而已。自增 ID 除了簡單，其他都是缺點，總體來看存在以下幾方面的問題：
- 可靠性不高
存在自增 ID 回溯的問題，這個問題直到最新版本的 MySQL 8.0 才修復。
- 安全性不高
對外暴露的介面可以非常容易猜測對應的資訊。比如：/user/1/ 這樣的介面，可以非常容易猜測使用者 ID 的值為多少，總使用者數量有多少，也可以非常容易地透過介面進行資料的爬取。
- 效能差
自增 ID 的效能較差，需要在資料庫伺服器端生成。
- 互動多
業務還需要額外執行一次類似 last_insert_id() 的函式才能知道剛才插入的自增值，這需要多一次的網路互動。在海量併發的系統中，多 1 條 SQL，就多一次效能上的開銷。
- 區域性唯一性
最重要的一點，自增 ID 是區域性唯一，只在當前資料庫例項中唯一，而不是全域性唯一，無法保證在任意伺服器間都是唯一的。對於目前分散式系統來說，這簡直就是噩夢。
業務欄位做主鍵
為了能夠唯一地標識一個會員的資訊，需要為會員資訊表設定一個主鍵。那麼，怎麼為這個表設定主鍵，才能達到我們理想的目標呢？這裡我們考慮業務欄位做主鍵。 表資料如下：
在這表裡，哪個欄位比較合適呢？
選擇卡號（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 時，很多人都很容易犯的錯誤是喜歡用業務欄位做主鍵，想當然地認為了解業務需求，但實際情況往往出乎意料，而更改主鍵設定的成本非常高。
淘寶的主鍵設計
在淘寶的電商業務中，訂單服務是一個核心業務。請問， 訂單表的主鍵淘寶是如何設計的呢？是自增ID嗎？
開啟淘寶，看一下訂單資訊：
從上圖可以發現，訂單號不是自增 ID！我們詳細看下上述 4 個訂單號：
1550672064762308113
1481195847180308113
1431156171142308113
1431146631521308113
訂單號是 19 位的長度，且訂單的最後 5 位都是一樣的，都是 08113。且訂單號的前面 14 位部分是單調遞增的。
大膽猜測，淘寶的訂單 ID 設計應該是：訂單ID = 時間 + 去重欄位 + 使用者 ID 後 6 位尾號。
這樣的設計能做到全域性唯一，且對分散式系統查詢及其友好。
推薦的主鍵設計
非核心業務：對應表的主鍵自增 ID，如告警、日誌、監控等資訊。
核心業務：主鍵設計至少應該是全域性唯一且是單調遞增。全域性唯一保證在各系統之間都是唯一的，單調遞增是希望插入時不影響資料庫效能。
這裡推薦最簡單的一種主鍵設計：UUID。
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 到現在的 100 ns 的計數。可以看到 UUID 儲存的時間精度比 TIMESTAMPE 更高，時間維度發生重複的機率降低到 1/100 ns。
- 時鐘序列是為了避免時鐘被回撥導致產生時間重複的可能性。MAC 地址用於全域性唯一。
-
為什麼 UUID 佔用 36 個位元組？
- UUID 根據字串進行儲存，設計時還帶有無用 "-" 字串，因此總共需要 36 個位元組。
-
為什麼 UUID 是隨機無序的呢？
- 因為 UUID 的設計中，將時間低位放在最前面，而這部分的資料是一直在變化的，並且是無序。
改造UUID
若將時間高低位互換，則時間就是單調遞增的了，也就變得單調遞增了。MySQL 8.0 可以更換時間低位和時間高位的儲存方式，這樣無序的 UUID 就是有序的 UUID 了。
MySQL 8.0 還解決了 UUID 存在的空間佔用的問題，除去了 UUID 字串中無意義的 "-" 字串，並且將字串用二進位制型別儲存，這樣儲存空間降低為了 16 位元組。
可以透過 MySQL 8.0 提供的uuid_to_bin函式實現上述功能，同樣的，MySQL 也提供了bin_to_uuid函式進行轉化：
mysql> SET @uuid = UUID();
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @uuid, uuid_to_bin(@uuid), uuid_to_bin(@uuid, TRUE);
+--------------------------------------+----------------------------------------+----------------------------------------------------+
| @uuid | uuid_to_bin(@uuid) | uuid_to_bin(@uuid, TRUE) |
+--------------------------------------+----------------------------------------+----------------------------------------------------+
| 394569c7-7b7d-11ee-bd87-0242ac160002 | 0x394569C77B7D11EEBD870242AC160002 | 0x11EE7B7D394569C7BD870242AC160002 |
+--------------------------------------+----------------------------------------+----------------------------------------------------+
1 row in set (0.00 sec)
透過函式uuid_to_bin(@uuid, TRUE)，可以將無序的 UUID 轉化為有序的 UUID，具有全域性唯一 + 單調遞增的特點，這不就是我們想要的主鍵！
有序 UUID 效能測試
16 位元組的有序 UUID，相比之前 8 位元組的自增ID，效能和儲存空間對比究竟如何呢？
我們來做一個測試，插入 1 億條資料，每條資料佔用 500 位元組，含有 3 個二級索引，最終的結果如下所示：
從上圖可以看到插入 1 億條資料有序 UUID 是最快的，而且在實際業務使用中有序 UUID 在業務端就可以生成，還可以進一步減少 SQL 的互動次數。
另外，雖然有序 UUID 相比自增 ID 多了 8 個位元組，但實際只增大了 3G 的儲存空間，還可以接受。
在當今的網際網路環境中，非常不推薦自增 ID 作為主鍵的資料庫設計，更推薦類似有序 UUID 的全域性唯一的實現。
另外在真實的業務系統中，主鍵還可以加入業務和系統屬性，如使用者的尾號，機房的資訊等。這樣的主鍵設計就更為考驗架構師的水平了。
非 MySQL 8.0 版本
手動賦值欄位做主鍵！
比如，設計各個分店的會員表的主鍵，因為如果每臺機器各自產生的資料需要合併，就可能會出現主鍵重複的問題。
可以在總部 MySQL 資料庫中，有一個管理資訊表，在這個表中新增一個欄位，專門用來記錄當前會員編號的最大值。
門店在新增會員的時候，先到總部 MySQL 資料庫中獲取這個最大值，在這個基礎上加 1，然後用這個值作為新會員的 id 的同時，更新總部 MySQL 資料庫管理資訊表中的當前會員編號的最大值。
這樣一來，各個門店新增會員的時候，都對同一個總部 MySQL 資料庫中的資料表欄位進行操作，就解決了各門店新增會員時會員編號衝突的問題。
原文連結
https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql-advanced.md