MySQL優化篇（一），我可以和面試官多聊幾句嗎？——SQL優化流程與優化資料庫對象

2022 年 3 月 31 日
筆記
MySQL

我可以和面試官多聊幾句嗎？只是想偷點技能過來。MySQL優化篇（基於MySQL8.0測試驗證），上部分：優化SQL語句、資料庫對象，MyISAM表鎖和InnoDB鎖問題。

MyISAM表鎖和InnoDB鎖問題會在第二篇發布：MySQL優化篇，我可以和面試官多聊幾句嗎？——MyISAM表鎖和InnoDB鎖問題（二）

你可以將這片博文，當成過度到MySQL8.0的參考資料。注意，經驗是用來參考，不是拿來即用。如果你能看到並分享這篇文章，我很榮幸。如果有誤導你的地方，我表示抱歉。

接著上一篇MySQL開發篇存儲引擎的選擇，上一篇用我現在眼光去看是稀爛的，每隔一段時間回顧自己的文章都感覺稀爛。此次帶來的是MySQL優化篇，部分內容針對多版本進行說明。在對MySQL進行舉例並使用到資料庫表，大多數情況使用MySQL官方提供的sakila（模擬電影出租資訊管理系統）和world資料庫，類似於Oracle的scott用戶。

如果沒有進行特別說明，一般是基於MySQL8.0.28進行測試驗證。官方文檔非常具有參考意義。目前市面上針對MySQL8.0書籍還比較少，部分停留在5.6.x和5.7.x版本，但仍然具有借鑒意義。

文中會給出官方文檔可以找到的參考內容，基本上在小標題末尾有提及並說明。輔助你快速定位出處，心裡更有底氣。如果想應對MySQL面試，我想這篇總結還是有一定的參考意義。需要有耐心看完，個人總結時參考書籍和MySQL8.0官方文檔也很乏味，純英文文檔更令人頭大。不懂的地方可以使用有道，結合實際測試進行理解。英語差，不應該是借口。

個人理解有限，難免出現錯誤偏差。所有測試，僅供參考。

如果感覺對你起到作用，有參考意義，想獲取原markdown文件。

可以訪問我的個人github倉庫，定期上傳md文件，空餘時間會製作目錄鏈接：

//github.com/cnwangk/SQL-study/tree/master/md/SQL/MySQL

MySQL優化篇（一）
正文
- 一、SQL優化
- 二、優化資料庫對象
莫問收穫，但問耕耘

MySQL優化篇（一）

MyISAM表鎖和InnoDB鎖問題會在第二篇：MySQL優化篇（二）進行發布，篇幅太長，不便一次性全部發完。

給出sakila-db資料庫包含三個文件，便於大家獲取與使用：

sakila-schema.sql：資料庫表結構；
sakila-data.sql：資料庫示例模擬數據；
sakila.mwb：資料庫物理模型，在MySQL workbench中可以打開查看。

//downloads.mysql.com/docs/sakila-db.zip

world-db資料庫，包含三張表：city、country、countrylanguage。

只是用於用於簡單測試學習，建議使用world-db：

//downloads.mysql.com/docs/world-db.zip

生產前：

應用開發初期數據量比較小，開發人員在編寫SQL語句時更加註重功能的實現（優先讓程式跑起來，有money賺）。

生產後：

業務體系逐漸擴張，隨著生產數據量的急劇增長，部分SQL語句開始漏出疲態，暴露出性能問題（開始優化，賺更多的money）。

引發的思考：

部分有問題的SQL語句成了系統性能的瓶頸，此時需要對SQL語句進行優化。

演示環境：

作業系統：Windows10 and Linux for Centos7.5
使用工具：MySQL8.0自帶字元命令行工具
資料庫：MySQL8.0.28 and MariaDB10.5.6

正文

注意：在某些情況，你自己測試的結果可能與我演示有所不同，我省略了查詢結果的部分參數。

本文側重點在SQL優化流程以及MySQL鎖問題（MyISAM和InnoDB存儲引擎）。圖片可能會掛，演示時盡量使用SQL查詢語句返回結果進行示例。篇幅很長，因此使用markdown語法加了目錄。

起初，也只是想看MySQL8.0.28有哪些變化，後面索性結合書籍和官方文檔總結了一篇。花了將近兩周，基本是每天完善一點，因為個人只有晚上和周末有時間總結並測試驗證。如果有錯別字，也請多多擔待。如果你能看到並分享這篇文章，我很榮幸。如果有誤導你的地方，我表示抱歉。

如果你是從MySQL5.6或者5.7版本過渡到MySQL8.0。學習之前，建議線看官方文檔這一章節：1.3 What Is New MySQL8.0 。在做對比的時候，文檔中帶有Note標識是你應該注意的地方。比如下面這張截圖：

與我之前一篇《MySQL8.0.28安裝教程全程參考官方文檔》是一樣的想法，希望大家能認識到自學的重要性，以及閱讀官方文檔自我成長。結合有道和Google翻譯以及自己的翻譯進行理解，感覺翻譯很彆扭，可以對單個單詞進行分析，結合自己的經驗調整並符合閱讀習慣。

參考文檔：refman-8.0-en.pdf

參考書籍：

《深入淺出MySQL 第2版資料庫開發、優化與管理維護》，個人參考優化篇部分。
《MySQL技術內幕InnoDB存儲引擎第2版》，個人參考索引與鎖章節描述。

一、SQL優化

01 優化SQL語句流程

登錄到mysql字元命令介面：

mysql -uroot -p

登錄時指定埠和主機地址方式：

mysql -h 192.168.245.147 -uroot -p -P 3307

使用？ show幫助命令查詢show status用法，截取部分語法如下：

? show
SHOW [GLOBAL | SESSION] STATUS [like_or_where]

1 通過show status查詢SQL執行頻率

如果不加參數，默認採用session級別，也可以加上global參數進行測試一下。

使用session與global參數區別：

session：當前連接統計的結果，默認為session級別；
global：上次資料庫啟動至今統計結果，需要手動那個指定global參數。

下面就列舉示例進行說明，分別使用like去查詢所有以及匹配CURD操作（select、insert、update、delete）：

查詢當前session所有統計記錄，如果直接在字元命令介面去查詢，共有175條記錄，大多數情況會採用工具去執行：

show status LIKE 'com_%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Com_admin_commands                  | 0     |
| Com_assign_to_keycache              | 0     |
| Com_alter_db                        | 0     |
| Com_commit    					  | 0     |
| Com_rollback              		  | 0     |
+-------------------------------------+-------+
...
175 rows in set (0.00 sec)

Com_xx部分參數作用說明：

Com_xx：代表某某語句執行次數，一般我們關心的是CURD操作（select、insert、update、delete）。
Com_select：執行select操作次數，每次累加1次。
Com_insert：執行insert操作次數，對於批量執行插入的insert操作只累加1次。
Com_update：執行update操作次數。
Com_delete：執行delete操作次數。

以上這些參數對所有存儲引擎表操作均會進行累計。但也有一些參數只針對InnoDB存儲引擎，累加演算法有些許不同。

查詢innodb參數如下，列舉部分：

show status LIKE 'innodb_rows%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_rows_deleted                   | 0                                                |
| Innodb_rows_inserted                  | 0                                                |
| Innodb_rows_read                      | 0                                                |
| Innodb_rows_updated                   | 0                                                |
+---------------------------------------+--------------------------------------------------+
...
61 rows in set (0.00 sec)

InnoDB_rows_read：執行select查詢返回行數。
InnoDB_rows_inserted：執行insert插入操作返回行數。
InnoDB_rows_updated：執行update更新操作返回行數。
InnoDB_rows_deleted：執行delete刪除操作返回行數。

通過上面幾個參數，可以輕鬆了解當前資料庫應用是以插入更新為主還是查詢操作為主，以及各種SQL大概執行比例是多少。

對於更新操作執行次數計數，無論是提交還是回滾都會進行累加。

對於事務型應用，可以通過Com_commit和Com_rollback了解事務提交與回滾情況。對回滾操作非常頻繁的資料庫，可能存在應用編寫問題。

有幾個參數便於用戶了解資料庫情況：

show status LIKE 'conn%';
show status LIKE 'upti%';
show status LIKE 'slow_q%';

Connections：試圖連接MySQL伺服器次數。
Uptime：伺服器工作時間。
Slow_queries：慢查詢次數。

對優化SQL語句流程就介紹這麼多，主要對關心的（CURD以及事務）各個參數熟練操作運用。

2 定位執行效率較低的SQL語句

可以通過兩種方式定位執行效率較低SQL語句：

使用參數：–log-slow-queries [=file_name]，MySQL會將long_query_time的SQL語句日誌寫入文件；
使用參數show processlist：查詢MySQL執行緒狀態、是否鎖表。

慢查詢日誌在查詢結束以後才記錄，在應用反映執行效率問題時查詢慢查詢慢查詢日誌並不能定位問題。可以使用show processlist，查看當前MySQL在進行的執行緒：執行緒狀態、是否鎖表，實時查看SQL執行狀態。

3 使用explain分析執行效率低的SQL語句

參考mysql8.0官方文檔explain：

//dev.mysql.com/doc/refman/8.0/en/explain-output.html

(//dev.mysql.com/doc/refman/8.0/en/explain.html)

通過上述步驟查詢到低效率SQL語句，然後使用explain或者desc命令獲取MySQL如何執行查詢select語句。

語法：explain [SQL語句]

explain [SQL語句]
-- 例如
mysql> explain select * from sakila.city\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 600
     filtered: 100.00
        Extra: NULL

desc語法：desc [SQL語句 & 表名]

world資料庫是官方提供，文初有給鏈接。

-- 示例查詢world資料庫city表結構
desc world.city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

-- 分析查詢語句資訊
mysql> desc select * from world.city\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4046
     filtered: 100.00
        Extra: NULL

以上是對explain與desc語法的介紹，以及簡單使用。側重點不在desc，主要以explain進行說明。

接下來對各個參數進行演示說明：

序號	explain & desc參數	作用
1	id	查詢標識符。
2	select_type	select類型，一般有simple、primary、union、subquery。
3	table	輸出結果集表。查詢的表名，如果使用了別名，則顯示別名。
4	partitions	對分區的支援。
5	type	執行計劃分析使用訪問類型，ALL代表全表掃描。
6	possible_keys	查詢時可使用的索引。
7	key	實際使用到的索引。
8	key_len	使用到索引欄位長度。
9	ref	與索引比較的列。在type中類型的一種，使用到索引。
10	rows	掃描行數，並不代表實際使用count(*)檢索的所有行數，是一個估值。
11	filtered	過濾恆定成立條件。
12	Extra	執行情況說明和描述，包含不適合在其它列中顯示，但對執行計劃有幫助的額外資訊。

常見訪問類型（type）：

+------+--------+--------+------+---------+---------------+----------+
| ALL  | index  | range  | ref  | eq_ref  | const,system  |   NULL   | 
+------+--------+--------+------+---------+---------------+----------+

性能天梯排行榜：由左至右，依次遞增。

3.1、type=ALL：代表全表掃描，MySQL遍歷全表匹配行。

示例：演示type為ALL執行計劃

explain select * from world.city;

3.2、type=index：索引全掃描，MySQL遍歷整個索引匹配行。

如果不清除哪一個是主鍵或者是index，使用desc命令查看，desc world.city

示例：演示type為index執行計劃

explain select id from world.city;

3.3、type=range：索引範圍掃描，常見於<、<=、>、>=、between等操作符。

=, <>, >, >=, <,<=, IS NULL, <=>, BETWEEN, LIKE, or IN()

— 摘自MySQL8.0官方文檔：8.8.2 explain output format range介紹

示例：演示type為range執行計劃

explain select * from world.city c where c.id<6;

3.4、type=ref：使用非唯一索引掃描或唯一索引的前綴掃描，返回某個單獨值匹配記錄行。

示例：演示type為ref執行計劃

explain select * from world.city where countrycode='AFG';

ref往往還經常出現在join操作中。

示例：演示type為ref執行計劃，使用inner join內連接

 explain select * from world.city t1 inner join world.countrylanguage t2 on t1.countrycode=t2.countrycode;

3.5、type=eq_ref：與ref類似，區別eq_ref使用唯一索引。每個索引鍵值，表中只有一條匹配記錄行。簡單來說，在多表連接查詢中使用primary key或者unique index作為關聯條件。

示例：演示type為eq_ref執行計劃

explain select * from sakila.film t1,sakila.film_text t2 where t1.film_id=t2.film_id;

3.6、type=const&system：單表中最多有一條匹配行，查詢速度很快。這條匹配行中其它列值可以被優化器在當前查詢中當做常量來處理。例如，根據主鍵primary key或者唯一索引unique key進行查詢。

示例：演示type為const執行計劃

explain select * from world.city t where t.id=7;

3.7、type=NULL：MySQL不用訪問表或索引，直接得到結果。

示例：演示type為NULL執行計劃

explain select 1;

以前，只知道統計查詢表使用MyISAM存儲引擎非常快，但不知其原理。使用explain分析了下，看到訪問類型（type）是NULL，瞬間有點明白了。下圖是使用InnoDB與MyISAM存儲引擎表的對比：

個人只演示常見的幾種。官方示例比較多，比如：ref_or_null、index_merge以及index_subquery等等。

你可以找到參考文檔：

8.8.3 Extended EXPLAIN Output Format

— 摘自官方文檔：refman-8.0-en-a4

tips：在MySQL8.0中移除了explain extended，使用這條命令分析SQL語句會報（1064（42000））。

某種場景下，使用explain並不能滿足我們需求，需要更高效定位問題，此時可以配合show profile命令聯合分析。

4 show profile分析SQL

查看當前MySQL版本對profile是否支援：如果是YES，代表是支援的。

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

默認show profiling是關閉的，可以通過set命令設置session級別開啟profiling：

select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

開啟profiling：設置profiling參數值為1，默認是0。

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

示例：

統計查詢world資料庫city錶行記錄數；
執行show profiles命令分析SQL。

統計city表記錄：

mysql> select count(*) from world.city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.01 sec)

使用show profiles命令分析：

示例：

mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00017800 | select @@profiling              |
|        2 | 0.00115675 | select count(*) from world.city |
+----------+------------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

使用show profile for query語句可以查詢到執行過程中執行緒更多資訊：狀態、消耗時間

示例：截取部分參數作為演示。

mysql> show profile for query 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000059 |
| Executing hook on transaction  | 0.000003 |
...
+--------------------------------+----------+
17 rows in set, 1 warning (0.01 sec)

更具上面查到的參數值，可以進一步分析是哪些影響到查詢效率。

更多用法請參考官方文檔：

13.7.7.30 SHOW PROFILE Statement

13.7.7.31 SHOW PROFILES Statement

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type: {
ALL
| BLOCK IO | CONTEXT SWITCHES | CPU 	| IPC
| MEMORY   | PAGE FAULTS      | SOURCE 	| SWAPS
}

比如從BLOCK IO（鎖輸入和輸出操作）、CPU（用戶系統CPU消耗時間）、記憶體等等著手分析。

判斷用戶CPU消耗時間，可以統計數據量大一點的表：我統計這張表模擬數據為1kw條。

show profile CPU for query 1;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| executing                      | 1.685893 | 5.593750 |   0.375000 |
+--------------------------------+----------+----------+------------+

5 使用trace分析優化器如何選擇執行計劃

查看trace是否開啟：OPTIMIZER_TRACE

enabled：默認為off。on代表開啟，off代表關閉。
one_line：json格式顯示，是否以一行顯示。on代表一行顯示，off代表多行顯示（格式化）。

select @@OPTIMIZER_TRACE;
+-------------------------+
| @@OPTIMIZER_TRACE       |
+-------------------------+
| enabled=on,one_line=on  |
+-------------------------+

示例：臨時開啟trace，在字元命令行中使用，測試建議還是使用一行顯示比較好。

set OPTIMIZER_TRACE="enabled=on,one_line=on";

示例：

查詢world資料庫city（城市）表前兩行記錄。
然後使用trace（optimizer_trace分析）追蹤。

-- 1. 查詢world資料庫city（城市）表前兩行記錄。
select * from world.city limit 0,2;
-- 2. 然後使用trace追蹤。
select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: select * from world.city limit 0,2
                            TRACE: {"steps": [{"join_preparation": {"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `world`.`city`.`ID` AS `ID`,`world`.`city`.`Name` AS `Name`,`world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`District` AS `District`,`world`.`city`.`Population` AS `Population` from `world`.`city` limit 0,2"}]}},{"join_optimization": {"select#": 1,"steps": [{"table_dependencies": [{"table": "`world`.`city`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": []}]},{"rows_estimation": [{"table": "`world`.`city`","table_scan": {"rows": 4046,"cost": 9.375}}]},{"considered_execution_plans": [{"plan_prefix": [],"table": "`world`.`city`","best_access_path": {"considered_access_paths": [{"rows_to_scan": 4046,"access_type": "scan","resulting_rows": 4046,"cost": 413.975,"chosen": true}]},"condition_filtering_pct": 100,"rows_for_plan": 4046,"cost_for_plan": 413.975,"chosen": true}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions_computation": [],"attached_conditions_summary": [{"table": "`world`.`city`","attached": null}]}},{"finalizing_table_conditions": []},{"refine_plan": [{"table": "`world`.`city`"}]}]}},{"join_execution": {"select#": 1,"steps": []}}]}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

6 定位問題後採取相應優化方法

建立索引：在常用欄位上建立，不常用欄位（應該考慮是否建立）。

經過上述步驟第3步explain分析SQL查詢語句，使用explain執行計劃發現使用全表掃描（大量數據）非常耗時間。

在相應欄位建立索引，然後進行分析，掃描行數明細減少，大大提高資料庫訪問速度。

02 索引問題

索引問題，是一個老生常談的問題。如果是資料庫優化場景，職場面試中經常被提到。

索引是在MySQL存儲引擎中實現，而不是在伺服器層實現。

每種存儲引擎索引不一定完全相同，並不是所有存儲引擎支援索引類型都一致。

以下列舉幾種常見索引介紹（索引存儲分類）：

B-Tree索引：最常見的使索引類型，大部分存儲引擎都支援B樹索引。
HASH索引：MEMORY、HEAP、NDB支援，使用場景較為簡單。
R-Tree索引（空間索引）：空間索引是MyISAM存儲引擎一個特殊索引類型，主要用於地理空間數據類型，一般使用較少。
Full-text（全文索引）：全文索引是MyISAM存儲引擎一個特殊索引類型，主要用於全文索引。在MySQL5.6版本開始對InnoDB提供全文索引支援。

注意：索引類型子句不能用於FULLTEXT（全文索引）或(在MySQL 8.0.12之前)空間索引規範。全文索引的實現依賴於存儲引擎。空間索引實現為R-tree索引。

1 索引分類

幾種常見的MySQL存儲引擎支援索引類型：

序號	存儲引擎	支援索引
1	InnoDB	BTREE
2	MyISAM	BTREE
3	MEMORY/HEAP	HASH, BTREE
4	NDB	HASH, BTREE

以上四種存儲引擎支援索引特點對比：Primary key（主鍵索引），Unique（唯一索引），key（普通索引），FULLTEXT（全文索引），SPATIAL（空間索引）。

InnoDB存儲引擎：

索引類別	索引類型	Stores NULL 值	Permits Multiple NULL 值	IS NULL 掃描類型	IS NOT NULL 掃描類型
Primary key	BTREE	No	No	N/A	N/A
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
FULLTEXT	N/A	Yes	Yes	Table	Table
SPATIAL	N/A	No	No	N/A	N/A

MyISAM存儲引擎：

索引類別	索引類型	Stores NULL 值	Permits Multiple NULL 值	IS NULL 掃描類型	IS NOT NULL 掃描類型
Primary key	BTREE	No	No	N/A	N/A
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
FULLTEXT	N/A	Yes	Yes	Table	Table
SPATIAL	N/A	No	No	N/A	N/A

MEMORY存儲引擎：

索引類別	索引類型	Stores NULL 值	Permits Multiple NULL 值	IS NULL 掃描類型	IS NOT NULL 掃描類型
Primary key	BTREE	No	No	N/A	N/A
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
Primary key	HASH	No	No	N/A	N/A
Unique	HASH	Yes	Yes	Index	Index
Key	HASH	Yes	Yes	Index	Index

NDB存儲引擎：

索引類別	索引類型	Stores NULL 值	Permits Multiple NULL 值	IS NULL 掃描類型	IS NOT NULL 掃描類型
Primary key	BTREE	No	No	Index	Index
Unique	BTREE	Yes	Yes	Index	Index
Key	BTREE	Yes	Yes	Index	Index
Primary key	HASH	No	No	Table	Table
Unique	HASH	Yes	Yes	Table	Table
Key	HASH	Yes	Yes	Table	Table

關於更多用法介紹，你可以找到參考內容：

8.3.9 Comparison of B-Tree and Hash Indexes

12.10 Full-Text Search Functions

13.1 Index Types Per Storage Engine

13.1.15 CREATE INDEX Statement

2 MySQL如何使用索引

InnoDB存儲引擎Information Schema一些視圖腳本名稱更新（MySQL8.0.3或者更高版本）：

舊名稱	新名稱
INNODB_SYS_COLUMNS	INNODB_COLUMNS
INNODB_SYS_DATAFILES	INNODB_DATAFILES
INNODB_SYS_FIELDS	INNODB_FIELDS
INNODB_SYS_FOREIGN	INNODB_FOREIGN
INNODB_SYS_FOREIGN_COLS	INNODB_FOREIGN_COLS
INNODB_SYS_INDEXES	INNODB_INDEXES
INNODB_SYS_TABLES	INNODB_TABLES
INNODB_SYS_TABLESPACES	INNODB_TABLESPACES
INNODB_SYS_TABLESTATS	INNODB_TABLESTATS
INNODB_SYS_VIRTUAL	INNODB_VIRTUAL

如果你升級到MySQL8.0.3或者更高版本：會發現與MySQL綁定的zlib庫版本從版本1.2.3提升到版本1.2.11

2.1 使用索引

一般情況，針對InnoDB存儲引擎進行描述索引使用，因為MySQL5.5.5開始默認存儲引擎是InnoDB。

InnoDB存儲引擎支援索引：

B-tree indexs（B+樹索引）；
Full-text search indexes（全文索引）：需要在MySQL5.6或者更高的版本中使用。

本不支援HASH indexs（NO Support），但InnoDB內部利用哈希索引來實現自適應哈希索引特性。

B+樹索引是傳統意義上的索引，目前關係型資料庫系統中查找最為常用和最為有效地的引。B+樹索引構造類似於二叉樹，根據鍵值（Key Value）快速查找數據。

注意：B+樹中的B不是代表二叉樹（binary），而是平衡樹（balance），因為B+樹是從平衡二叉樹演化而來，但B+樹也不是一個二叉樹。B+樹索引並不能找到一個給定鍵值的具體行，能找到的是被查找數據行所在頁。然後資料庫通過將頁讀到記憶體，再從記憶體中進行查找，最後得到要查找的數據。

上面簡單介紹了下InnoDB存儲引擎支援的索引，以及部分新特性，以及B+樹索引。如果想深入理解B+樹索引，可以從演算法角度去分析，但不是此次內容的重點，可以私下查找文檔去了解。接著討論如何使用索引。

2.2 MySQL中使用索引典型場景

匹配全值（Match the whole value）。對索引中所有列都指定具體指，即索引所有列都有等值匹配條件。

mysql> explain select * from sakila.rental where rental_date='2005-05-27 07:33:54' and customer_id=134 and inventory_id=360\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: const
possible_keys: uk_rental_date,idx_fk_inventory_id,idx_fk_customer_id
          key: uk_rental_date
      key_len: 10
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL

通過觀察explain輸出結果，發現type=const。表示常量；欄位key值為uk_rental_date，表示優化器使用索引uk_rental_date進行掃描。

匹配範圍查詢（March range）。對索引值能夠進行範圍查找。例如，查找租賃表rental中客戶編號customer_id在指定範圍記錄：

mysql> explain select * from sakila.rental where customer_id>=366 and customer_id<=399\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 925
     filtered: 100.00
        Extra: Using index condition

通過explain分析，發現type=range以及Extra: Using index condition。使用到範圍性查找，以及索引下放操作。

匹配最左前綴（Matches the leftmost prefix）。僅僅使用到索引中的最左邊列進行查找，比如在多個欄位（col1、col2、col3）欄位上的聯合索引能夠被包含col1、（col1、col2）、（col1、col2、col3）的等值查詢利用到，但是不能被（col2、col3）、col2的等值查詢利用到。以sakila資料庫中支付（payment）表進行示例。

下面創建組合索引 idx_payment_date便於測示：

mysql> ALTER TABLE sakila.payment add index idx_payment_date(payment_date,amount,last_update);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用explain執行分析：

mysql> explain select * from sakila.payment where payment_date='2005-06-15 21:08:46' and last_update='2005-06-15 21:08:46'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_payment_date
          key: idx_payment_date
      key_len: 5
          ref: const
         rows: 1
     filtered: 10.00
        Extra: Using index condition

通過觀察執行結果，發現 type=ref 以及Extra: Using index condition，根據最左匹配原則，你會發現payment_date處於索引1號位，此時掃描利用到組合索引idx_payment_date。

如果使用last_update和amount進行測試分析：

mysql> explain select * from sakila.payment where last_update='2005-06-15 21:08:46' and amount=9.99\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 1.00
        Extra: Using where

通過觀察查詢結果，發現type=ALL走全表掃描，索引沒有使用到。

僅僅對索引查詢（Only for index queries）。當查詢列都在索引欄位中，查詢效率更高。

mysql> explain select last_update from sakila.payment where payment_date='2005-06-15 21:08:46' and amount=9.99\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_payment_date
          key: idx_payment_date
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Extra: Using index，意味著現在直接訪問索引就足夠獲取到所有需要的數據，無需索引回表，Using index也是通常所說的覆蓋索引掃描。只訪問必須訪問的數據，一般而言，減少不必要數據訪問可以提高效率。

匹配列前綴（Match a column prefix ），僅僅使用索引中的第一列，並且只包含索引第一列開頭一部分。例如，查詢出標題是AGENT開頭的電影資訊。

創建列前綴索引：

mysql> create index idx_title_desc_part on sakila.film_text(title(10),description(20));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

執行explain進行分析，注意：在B-tree索引中使用時，不要以通配符（%），不然索引會失效。

mysql> explain select title from sakila.`film_text` where title like 'AGENT%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_text
   partitions: NULL
         type: range
possible_keys: idx_title_desc_part,idx_title_description
          key: idx_title_desc_part
      key_len: 42
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

分析執行計劃，看到idx_title_desc_part被利用到，type=range，使用範圍性查詢。Extra: Using where表示優化器需要通過索引回表查詢數據。

索引匹配部分精確，其它部分範圍匹配（Match a part）。

mysql> explain select inventory_id from sakila.rental where rental_date='2006-02-14 15:16:03' and customer_id>=300 and customer_id<=400\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: uk_rental_date,idx_fk_customer_id
          key: uk_rental_date
      key_len: 5
          ref: const
         rows: 182
     filtered: 16.86
        Extra: Using where; Using index

上面通過explain分析，查詢出出租日期（rental_date）、指定日期的客戶編號（customer_id）指定範圍的庫存。根據type=ref，以及key=uk_rental_date，優化器建議走唯一索引。

如果列名是索引，使用column_name is null就會使用索引。

mysql> explain select * from sakila.payment where rental_id is null\G
         type: ref
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
      	Extra: Using index condition

通過explain執行分析，查詢支付表（payment）租賃編號（rental_id）欄位為空的記錄使用到了索引。

MySQL5.6以及更高版本支援Index Condition Pushdown (ICP)特性，索引條件下放操作，進一步優化了查詢。某些情況操作下放到存儲引擎。

ICP可以用於InnoDB和MyISAM表，包括分區的InnoDB和MyISAM表。
當需要訪問全表時，ICP用於range、ref、eq_ref和ref或null訪問方法。
對於InnoDB表，ICP僅用於二級索引（次索引、輔助索引）。ICP的目標是減少全行讀取的數量，從而減少I/O操作。對於InnoDB聚集索引，完整的記錄已經被讀取到InnoDB緩衝區。在這種情況下使用ICP不會減少I/O。
如果在虛擬列上創建二級索引，則不支援ICP。InnoDB支援在虛擬列上建立二級索引。
引用到子查詢條件不能使用操作下放。
引用存儲函數的條件不支援操作下放，存儲引擎無法調用存儲函數。
使用觸發器（觸發的條件），不能使用操作下放。

如下示例，查詢支付表，強制使用索引查詢內容。

mysql> explain select * from sakila.payment force index(fk_payment_rental) where rental_id > 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: range
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
          ref: NULL
         rows: 8043
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

經過explain分析，看到Extra值為Using index condition，表示MySQL使用了ICP進一步優化查詢，在檢索時，將條件rental_id過濾操作下推到到存儲引擎層來完成，可以降低不必要的IO訪問。

注意：前綴限制以位元組為單位，而CREATE TABLE、ALTER TABLE 和 CREATE INDEX語句中的前綴長度，被解析為非二進位字元串類型(CHAR、VARCHAR、TEXT)的字元數，和二進位字元串類型(binary、VARBINARY、BLOB)的位元組數。使用多位元組字符集的非二進位字元串列指定前綴長度時，請考慮這一點。

2.3 存在但不能使用索引的場景

B-Tree索引可用於使用=、>、>=、<、<=或BETWEEN操作符表達式中的列做比較。如果LIKE的參數是一個不以通配符開頭的常量字元串，則索引也可以用於LIKE比較。例如，下面的SELECT語句使用索引場景：

以 % 開頭 LIKE 查詢不能夠利用B-Tree索引，執行計劃中Key值為NULL表示沒有使用索引。如下示例：

-- 沒有利用到索引場景
mysql> explain select * from world.city where countrycode like '%A%'\G
		 type: ALL	
possible_keys: NULL
          key: NULL

-- 索引生效場景
mysql> explain select * from world.city where countrycode like 'A%'\G 
		 type: range
possible_keys: CountryCode
          key: CountryCode

第一種場景，使用explain執行優化分析後：key=NULL，沒有利用到索引。第二種場景，以 % 結束，執行explain優化分析，明顯索引起作用了，type=range，屬於範圍性掃描。

因為B-Tree索引結構特性，以通配符（%）開頭的查詢自然無法利用到索引，一般建議使用全文索引（fulltext）來解決類似問題。或者考慮利用InnoDB聚簇表特點，採用一種輕量級別解決方式：一般情況，索引比表小，掃描索引比掃描表更快。

數據類型出現隱式轉換時不會使用索引，如果列類型是字元串，使用where條件記得將字元常量用引號引起來。MySQL默認將輸入的常量值進行轉換以後才進行檢索。

如下示例：

-- 場景一
mysql> explain select * from world.city where countrycode=1\G
         type: ALL
possible_keys: CountryCode
          key: NULL

-- 場景二
mysql> explain select * from world.city where countrycode='1'\G
         type: ref
possible_keys: CountryCode
          key: CountryCode
      key_len: 12

在場景二中，字元串（char）類型將1引起來，通過explain分析使用到索引。場景一中沒有加引號，索引沒有利用，從而走全表掃描。

複合索引場景下，如果查詢條件不包含索引列最左部分，即不滿足最左原則（LeftMost），不會利用到符合索引：

mysql> explain select * from sakila.payment where amount=9.99 and last_update='2006-02-15 22:12:30'\G
         type: ALL
possible_keys: NULL
          key: NULL

如果 MySQL 判斷使用索引比掃描全錶慢，則不會使用索引。比如，返回記錄很大，但使用索引掃描更費時間，優化器更傾向於使用全表掃描，這樣代價更低，效率更高。（使用Trace可以追蹤更多資訊，前面也提到過）

使用 OR 分割開的條件，如果OR條件前列有索引，OR後列沒有索引，那麼涉及到的索引都不會被利用。

mysql> explain select * from sakila.payment where customer_id=9 or amount=9.99\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: ALL
possible_keys: idx_fk_customer_id
          key: NULL
         rows: 16086
     filtered: 10.15
        Extra: Using where

因為 OR 後列沒有索引，那麼後繼查詢需要走全表掃描。存在全表掃描情況下，也沒必要多走一次索引掃描增加磁碟I/O訪問。如果前面列無索引，後面列有索引，執行結果一樣走全表掃描。（在接下來的優化OR查詢部分，進行了對比）

3 查看索引使用情況

查看 Handler_read_key 值判斷索引工作頻率，基於鍵值讀取一行的請求數。如果這個值（Handler_read_key）很高，說明您的表在查詢時已經建立了適當的索引。讀取一行請求數值很低，則表明增加索引改善並不明顯，索引沒有經常使用。

可以通過show status like ‘Handler_read%’查詢參數值，分析索引使用狀況。

mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 2     |
| Handler_read_key      | 74    |
| Handler_read_last     | 0     |
| Handler_read_next     | 147   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 30    |
| Handler_read_rnd_next | 32    |
+-----------------------+-------+

初始時（索引還未工作），上述查詢出默認值為零，當你使用索引後，這些參數會有變化。

Handler_read_rnd：基於固定位置讀取一行的請求數。如果執行大量需要對結果進行排序的查詢，則該值會很高。你可能有大量需要MySQL掃描全表的查詢，或者你沒有合理地使用鍵連接。

Handler_read_rnd_next：讀取數據文件中下一行的請求數。如果要進行大量的表掃描，這個值就會很高。一般來說，這意味著您的表沒有正確索引，或者說是寫入查詢沒有利用到索引。

03 簡單優化方法

對於開發人員來說，可能只需掌握簡單實用的優化方法。比較複雜的優化，一般交給DBA來管理。

analyze：分析表，analyze table table_name;
check：檢查表，check table table_name;
checksum table：檢查表；
optimize table：優化表，同時支援MyISAM和InnoDB表。回收刪除操作造成的空洞，比如回收索引。
repair table：修復表，支援 MyISAM，ARCHIVE以及CSV 表。

3.1 定期分析表和檢查表

定期分析與檢查主要有兩個關鍵命令：

analyze：分析表，analyze table table_name;
check：檢查表，check table table_name;

分析（analyze）表語法：

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name

UPDATE HISTOGRAM ON col_name [, col_name] ...
[WITH N BUCKETS]

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name
DROP HISTOGRAM ON col_name [, col_name] ...

示例分析表：可以使用官方示例庫進行分析，個人使用自己創建test資料庫進行測試tolove表

analyze table test.tolove;
+-------------+---------+----------+-----------------------------+
| Table       | Op      | Msg_type | Msg_text                    |
+-------------+---------+----------+-----------------------------+
| test.tolove | analyze | status   | Table is already up to date |
+-------------+---------+----------+-----------------------------+
1 row in set (0.01 sec)

總結：analyze語句用於分析存儲表關鍵字分布，分析結果使系統得到更準確的資訊，SQL生成預期執行計劃。如果你感覺實際執行計劃沒有達到預期結果，不妨嘗試執行一次分析表計劃。

檢查（check）表語法：

CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option: {
FOR UPGRADE
| QUICK	| FAST	| MEDIUM
| EXTENDED	| CHANGED
}

示例檢查表：這張tolove表創建後修改為MyISAM存儲引擎進行測試，數據量1kw，所以分析起來有點耗時。

tips：同時測試使用InnoDB表，數據量1kw，花了5.21sec，這裡就不貼出來了。

mysql> check table test.tolove;
+-------------+-------+----------+----------+
| Table       | Op    | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test.tolove | check | status   | OK       |
+-------------+-------+----------+----------+
1 row in set (1.63 sec)

check table作用：用於檢查一張或多張表是否有錯誤，前面提到過，同時支援MyISAM和InnoDB表。同樣支援檢查視圖，這裡不做示範，可以自行參考文檔進行測試驗證。

3.2 定期優化表

優化（optimize ）表語法：

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...

如果已經刪除了表中一大部分數據，或已經對含有可變長度行的表（例如含有：varchar、blob或者txt列的表）進行很多更改，則可以使用optimize table命令進行優化表。

作用：optimize命令可以將表中空間碎片進行合併，消除由於刪除或者更新造成的空間浪費。同樣支援MyISAM和InnoDB表。

示例（optimize）優化表：演示的tolove表前面說過指定MyISAM存儲引擎

mysql> optimize table test.tolove\G
*************************** 1. row ***************************
   Table: test.tolove
      Op: optimize
Msg_type: status
Msg_text: Table is already up to date
1 row in set (0.01 sec)

測試test表使用InnoDB存儲引擎。對於InnoDB存儲引擎，通過設置innodb_file_per_table參數（默認值為1），改為獨立表空間模式，每個資料庫每張表會生成獨立ibd文件，用於存儲表和索引，可以在一定程度上減輕 InnoDB表回收空間問題。此外，在刪除大量數據後，可以通過alter table命令不修改表引擎方式回收不用的空間：

mysql> optimize table test.test\G
*************************** 1. row ***************************
   Table: test.test
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: test.test
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (17.85 sec)

mysql> alter table test.test engine=innodb;
Query OK, 0 rows affected (20.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意：analyze、check、optimize以及alter table執行期間將對錶進行鎖定，一定要注意在資料庫不頻繁使用期間，再進行相關操作。

提到優化方法，在MySQL8.0文檔中你可以找到參考內容：

1.7 MySQL Standards Compliance

13.7.3 Table Maintenance Statements

摘自：refman-8.0-en.pdf

04 常用SQL優化

在某種場景下，查詢使用很頻繁，針對查詢優化確實很有必要。

但實際開發中，還會面臨使用其它常用SQL，比如insert、group by、order by等等。

4.1 批量（大量）插入數據

在使用load命令導入數據時，適當進行設置可以提高導入效率。

對於MyISAM表可以通過以下方式快速導入大量數據。

操作命令：

ALTER TABLE tbl_name DISABLE KEYS;	-- 禁用MyISAM表非唯一索引更新
ALTER TABLE tbl_name ENABLE KEYS;	-- 開啟MyISAM表非唯一索引更新

disable keys和enable keys用於開啟和關閉MyISAM表非唯一索引更新。

MyISAM存儲引擎默認，導入大量數據至一張空MyISAM表，默認先導入數據，然後創建索引，不用進行設置。

示例導入數據語句：

load data infile 'file_name' into table tbl_name;

自行測試時，可以先手動開啟非唯一索引，然後關閉非唯一索引進行對比導入時間。

通過測試關閉唯一索引，導入數據效率確實要高很多。這是對MyISAM表進行測試優化，對InnoDB類型表上述方式作用不是很大。

InnoDB表導入表同樣也有相應優化措施：

導入數據按主鍵順序排列，可以提高效率。（InnoDB表是按主鍵順序排列）
導入數據前執行set unique_checks=0，關閉唯一性校驗；導入完成，再設置set unique_checks=1，恢復唯一性校驗。從而提高導入效率。
如果應用使用自動提交（autocommit），建議導入前執行set autocommit=0，關閉自動提交。導入數據後，再設置set autocommit=1，開啟自動提交，同樣可以提高導入效率。

MyISAM表和InnoDB表導入數據語句是一樣的。以上介紹MyISAM表和InnoDB表導入數據優化方式，可進行參考測試驗證。

更多關於MyISAM表插入數據優化方法可以參考如下引用說明： 對於文檔理應善於使用搜索Ctrl + f

優化InnoDB相關章節：8.5 Optimizing for InnoDB Tables

優化MyISAM相關章節：8.6 Optimizing for MyISAM Tables

導入數據：13.2.7 LOAD DATA Statement

摘自：refman-8.0-en.pdf

4.2 優化 INSERT、ORDER BY、GROUP BY 語句

你可以找到參考內容：

13.2.6 INSERT Statement
8.2.1.16 ORDER BY Optimization
8.2.1.17 GROUP BY Optimization

4.2.1 INSERT語句

當進行資料庫INSERT操作時，可以考慮以下幾種優化方式。

如果同時從同一用戶表插入多行，應盡量使用多個值表的INSERT語句，這種方式大大縮減客戶端與資料庫之間的連接、關閉等消耗。一般情況下，比單個執行INSERT語句效率要高得多，但也分場景。下面給出一次插入多值示例：

INSERT INTO tbl_name(a,b,c) VALUES(1,2,3), (4,5,6), (7,8,9);

上述演示，指定欄位。從安全形度考慮，實際開發過程中也是推薦指定欄位，因為這種方式更加安全。多年前，我還是一位菜雞開發人員，雖然現在也是一名菜鳥。當時不是很理解，為何在DAO中非要在前面指明欄位。直到某天翻閱實體書籍時，才意識到。

如果從不同用戶插入多行。使用到DELAYED語句，需要注意了，在MySQL5.6之前版本還沒被移除，從MySQL5.6開始已經棄用。使用DELAYED之所以快，其實數據被存放在記憶體隊列中，並沒有真正寫入從磁碟。

注意事項：DELAYED關鍵字計劃在未來的版本中刪除。延遲插入（ DELAYED INSERT ）和替換在MySQL 5.6中已棄用。在MySQL 8.0中，不支援DELAYED。伺服器可以識別，但會忽略DELAYED關鍵字，將插入處理視為非延遲插入，並生成ER_WARN_LEGACY_SYNTAX_CONVERTED 警告：INSERT DELAYED is no longer supported. The statement was converted to INSERT。

可以將索引文件與數據文件在不同的磁碟上存放，建表時可以選擇。

如果進行批量插入，可以通過增減bulk_insert_buffer_size變數值的方法來提高速度。對MyISAM表有效，MyISAM使用一種特殊的樹狀快取，使批量插入更快。 INSERT … SELECT，INSERT … VALUES (…)，(…)，…，和LOAD DATA在添加數據到非空表時。這個變數以每個執行緒的位元組為單位限制快取樹的大小。將其設置為0將禁用此優化。默認值為8MB。

注意事項：從MySQL 8.0.14開始，設置bulk_insert_buffer_size這個系統變數的會話值是一個受限制的操作。會話用戶必須具有設置受限制會話變數的許可權。

當從一個文本裝載一張表時，使用LOAD DATA INFILE，一般比使用INSERT語句快得多。

從MySQL 8.0.19版本開始，你也可以使用INSERT…TABLE在MySQL 8.0.19及以後版本中插入一行，使用TABLE替換SELECT。

mysql> CREATE TABLE tb (i INT);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tb TABLE t;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

以上演示，是將表 t 中所有記錄插入到 tb 表中，與之前insert into tb select * from t用法是一樣的執行效果。

4.2.2 ORDER BY語句

看到ORDER BY語句，可以聯想到排序方式。那麼，了解一下MySQL中的排序方式。

查看world資料庫中city表索引情況：此處省略掉了一些參數值，全部展示篇幅太長。

mysql> show index from city\G
*************************** 1. row ***************************
        Table: city
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID
    Collation: A
  Cardinality: 4046
   Index_type: BTREE
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: city
   Non_unique: 1
     Key_name: CountryCode
 Seq_in_index: 1
  Column_name: CountryCode
    Collation: A
  Cardinality: 232
   Index_type: BTREE
      Visible: YES
   Expression: NULL
2 rows in set (0.01 sec)

MySQL中有兩種排序方式：

Use of Indexes to Satisfy ORDER BY，使用using index。
Use of filesort to Satisfy ORDER BY，使用filesort。

在某些情況下，MySQL可能會使用索引來滿足ORDER BY子句，從而避免執行filesort操作時涉及的額外排序。第一種通過有序使用順序掃描直接返回有序數據，這種方式在使用explain分析查詢時顯示Using index，無需額外排序，操作效率較高，示例如下：

mysql> explain select id from city order by id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4046
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

如果索引不能滿足ORDER BY子句，MySQL執行一個filesort操作，讀取錶行並對它們排序。filesort在查詢執行中構成一個額外的排序階段。第二種是通過對返回數據進行排序，也是通常所說的filesort排序，所有不是通過索引直接返回結果的排序都稱為filesort排序。

filesort並不代表通過磁碟文件進行排序，只是說明進行了一個排序操作，至於操作是否使用了磁碟文件或者臨時表等，則取決於MySQL伺服器對排序參數的設置和需要排序數據的大小。

如果結果集太大，無法裝入記憶體，則 filesort 操作將根據需要使用臨時磁碟文件。有些類型的查詢特別適合於完全在記憶體中的filesort操作。例如，優化器可以使用filesort在記憶體中有效地處理，而不需要臨時文件。示例：

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

以下給出使用 Using filesort 情況示例：

mysql> explain select store_id,email,customer_id from sakila.customer order by email\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_storeid_email
      key_len: 204
          ref: NULL
         rows: 599
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

注意：為了獲得 filesort 操作的記憶體，從MySQL 8.0.12開始，優化器根據需要增量分配記憶體緩衝區(sort_buffer_size )，直到由排序緩衝區大小系統變數指示的大小，而不是像在MySQL 8.0.12之前那樣，預先分配固定數量的排序緩衝區(sort_buffer_size )大小位元組。這使用戶可以將排序緩衝區大小設置為更大的值，以加快更大的排序，而不用擔心小排序會佔用過多的記憶體。(這種好處可能不會出現在Windows上的多個並發排序，因為Windows有一個弱多執行緒malloc。）

了解MySQL排序方式後，優化目的清晰了：盡量減少額外排序，通過索引直接返回數據。

添加組合索引，然後使用explain執行測試：

mysql> ALTER TABLE sakila.customer ADD INDEX idx_storeid_email(store_id,email);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select store_id,email,customer_id from sakila.customer where store_id=1 order by email desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx_fk_store_id,idx_storeid_email
          key: idx_storeid_email
      key_len: 1
          ref: const
         rows: 326
     filtered: 100.00
        Extra: Backward index scan; Using index
1 row in set, 1 warning (0.00 sec)

依據上面測試演示結果，可以分析出返回索引掃描。如果是在8.0之前顯示有所區別，比如在MySQL5.7出現的是Extra: Using where; Using index。

查詢商店編號store_id大於等於1小於等於3，按照email進行排序記錄主鍵customer_id時，由於優化器評估使用索引idx_storeid_email進行範圍掃描const最低，所以最終對索引進行掃描的結果，進行額外email逆序操作：

mysql> explain select store_id,email,customer_id from sakila.customer where store_id>=1 and store_id<=3 order by email desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: index
possible_keys: idx_fk_store_id,idx_storeid_email
          key: idx_storeid_email
      key_len: 204
          ref: NULL
         rows: 599
     filtered: 100.00
        Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

優化filesort：通過建立合適的索引減少 filesort 出現，但在某種情況下，條件限制無法讓 filesort 消失，可以想辦法加快 filesort 操作。如何加快，可以通過控制sort_buffer_size 和 max_length_for_sort_data（max_sort_length ）大小進行優化。

注意：對於沒有使用filesort的慢ORDER BY查詢，嘗試將排序數據系統變數（max_length_for_sort_data）的最大長度降低到適合觸發filesort的值。(將此變數值設置過高的一個癥狀是高磁碟活動和低CPU活動的結合。)這種技術只適用於MySQL 8.0.20之前。從8.0.20開始，排序數據的最大長度已棄用，因為優化器的更改使其過時且無效。

4.2.3 GROUP BY語句

滿足GROUP BY子句的最常用方法是掃描全表，並創建一個新的臨時表，其中每個組中所有行都是連續的，然後使用這個臨時表來發現組並應用聚合函數(如果存在的話)。在某些情況下，MySQL能夠做得更好，並通過使用索引訪問避免創建臨時表。

GROUP BY使用索引最重要的前提條件：所有GROUP BY列引用來自同一索引的屬性，並且該索引按順序存儲其鍵(例如，對於BTREE索引是這樣，但對於HASH索引則不同)。臨時表的使用是否可以被索引訪問替代，還取決於查詢中使用索引的哪些部分、為這些部分指定的條件以及所選的聚合函數。

訪問索引執行 GROUP BY 兩種掃描方式：

鬆散索引掃描（Loose Index Scan）
密集索引掃描（Tight Index Scan）

默認情況下，MySQL對所有GROUP BY c1，c2，…欄位進行排序，與查詢中指定ORDER BY c1，c2，…類似。因此，如果顯示包括一個相同列的ORDER BY子句，對MySQL實際執行性能沒有什麼影響。

如果查詢包括GROUP BY，但用戶想避免排序結果的消耗，則可以指定ORDER BY NULL禁止排序。如下示例：

mysql> explain select payment_date,sum(amount) from sakila.payment group by payment_date\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 100.00
        Extra: Using temporary
1 row in set, 1 warning (0.00 sec)

分析查詢出來的結果，發現Extra: Using temporary，使用一個臨時表。type=ALL，執行全表掃描。

注意：在MySQL5.7或者更低的版本中使用 ORDER BY NULL有顯示優化作用，GROUP BY在特定條件下隱式排序。在MySQL8.0中不再出現這種情況，所以在最後指定 ORDER BY NULL 來抑制隱式排序，就沒有必要了。但是，查詢結果可能與之前的MySQL版本有所不同。要生成給定的排序順序，請使用 ORDER BY子句。

即使在MySQL8.0中顯示使用ORDER BY NULL 來抑制隱式排序，結果並沒變化。但在MySQL5.7或者MariaDB10.5.6中使用時有變化，而且你會發現執行結果出現：Extra: Using temporary; Using filesort。對於filesort嗎，上面也給出了簡單處理方法。

mysql> explain select payment_date,sum(amount) from sakila.payment group by payment_date order by null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 100.00
        Extra: Using temporary
1 row in set, 1 warning (0.00 sec)

4.3 優化嵌套查詢、分頁查詢

4.3.1 嵌套查詢

你可以找到參考內容：8.2.1 Optimizing SELECT Statements

MySQL4.1中開始支援SQL子查詢。這個技術可以使用SELECT語句來創建一個單列的查詢結果，然後將查詢的結果作為過濾條件作用在另一個查詢中。使子查詢可以一次性完成更多邏輯上需要多個步驟才能完成的SQL操作，同時可以表面事務或者表鎖死，編寫相對容易。但在某些情況下，使用連接（join）效率更高，可以被替代。

示例：在顧客表查詢排除支付表中的所有顧客資訊，使用子查詢實現。

mysql> EXPLAIN SELECT * FROM sakila.`customer` WHERE customer_id
  NOT IN(SELECT customer_id FROM sakila.`payment`)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.customer.customer_id
         rows: 26
     filtered: 100.00
        Extra: Using where; Not exists; Using index
2 rows in set, 1 warning (0.00 sec)

可使用join進行改進，我提供思路，用left join進行連接查詢，主要以customer表為主，也是以左表為主。

EXPLAIN SELECT * FROM sakila.`customer` a LEFT JOIN sakila.`payment` b ON
a.`customer_id`=b.`customer_id` WHERE b.`customer_id` IS NULL\G

注意：當時還納悶測試看不出index_subquery。查詢後，發現在MySQL8.0.16之前可以看到type由index_subquery變為ref，而在MySQL8.0.16開始優化器調整並做優化（in和exists），與上面子查詢得到結果並無區別。

連接（join）之所以效率更高，因為MySQL不需要在記憶體中創建臨時表來完成這個邏輯上需要兩個步驟完成的工作。

4.3.2 分頁查詢

你可以找到參考內容：8.2.1.19 LIMIT Query Optimization

一般分頁查詢時，通過創建覆蓋索引能夠比較好地提高性能。一個很頭痛的分頁場景：limit 996,20，此時MySQL排序出前996記錄後僅僅只需要返回第996到1016條記錄，前996條記錄會被拋棄，查詢和排序代價非常高。

通過分析上述描述場景，使用explain進行分析：

mysql> explain select * from world.city limit 996,20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4046
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

可以看出，type=ALL，優化分析器走了全表掃描。

第一種優化思路：在索引上完成排序分頁操作，最後根據關聯原表查詢所需要的其它列內容。

通過思考，對上面SQL語句進行調整優化：

mysql> explain select * from world.city c inner join(select id from world.city order by countrycode limit 996,20)a on c.id=a.id\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
  ...
         type: ALL
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
  ...
         type: eq_ref
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: city
   partitions: NULL
         type: index
possible_keys: NULL
          key: CountryCode
      key_len: 12
          ref: NULL
         rows: 1016
     filtered: 100.00
        Extra: Using index
3 rows in set, 1 warning (0.00 sec)

上述結果，前兩頁省略掉了一些內容。這種方式使MySQL掃描儘可能少的頁面來提高分頁效率，缺點是SQL語句變長了。

第二種優化思路：將limit查詢轉換成某個位置的查詢，實際上是將limit m,n轉換為limit n查詢，只適合排序欄位不會出現重複值的特定環境，能減輕分頁翻頁壓力。如果排序欄位現大量重複值，則不適合進行這種優化，因為會丟失部分記錄。

注意：對於帶有ORDER BY或GROUP BY和LIMIT子句的查詢，優化器在默認情況下嘗試選擇一個有序索引，這樣做會加快查詢的執行速度。在MySQL 8.0.21之前，即使在使用一些其它優化，可能更快的情況下，沒有辦法覆蓋這種行為。從MySQL 8.0.21開始，可以通過設置優化器開關（optimizer_switch）系統變數的優先排序索引（prefer_ordering_index）標誌來關閉這種優化。

默認情況optimizer_switch 和 prefer_ordering_index是開啟的：

mysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%'\G
*************************** 1. row ***************************
@@optimizer_switch LIKE '%prefer_ordering_index=on%': 1
1 row in set (0.00 sec)

4.4 優化 OR 條件

你可以查找到參考內容：12.4.3 Logical Operators

在介紹OR條件時，可以先了解MySQL中的邏輯操作符（Logical Operators）。有如下幾種：

AND, &&：邏輯與、並且，在兩個條件中必須都滿足。
NOT, !：否定、取反。
OR, ||：邏輯或、在兩個條件中滿足一個條件即可。
XOR：邏輯XOR。如果是NULL，返回NULL；如果是non-NULL，返回1；如果奇數個非零操作數，則計算結果為1，否則返回0。

示例XOR：

SELECT 1 XOR 1\G		-- return:0
SELECT 1 XOR 0\G		-- return:1
SELECT 1 XOR NULL\G		-- return:NULL
SELECT 1 XOR 1 XOR 1\G	-- return:1

對於含有OR查詢的子句，如果要利用索引、則OR之間的每個條件列都必須用到索引，如果沒有索引，應該考慮增加索引。

可以使用show index from tal_name語句查看錶索引情況：

mysql> show index from city\G
...
Column_name: city_id
Column_name: country_id
...

然後查詢存在索引的兩列，並使用OR條件聯合查詢：

mysql> explain select * from city where city_id=6 or country_id=101\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: index_merge
possible_keys: PRIMARY,idx_fk_country_id
          key: PRIMARY,idx_fk_country_id
      key_len: 2,2
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using union(PRIMARY,idx_fk_country_id); Using where
1 row in set, 1 warning (0.01 sec)

可以發現查詢正確地使用到索引，並且從執行計劃描述中，發現MySQL在處理含有OR子句查詢時，實際對OR各個欄位分別查詢後的結果進行了union操作。

在有複合索引的列上做OR操作，卻無法使用到索引，查詢結果如下：

mysql> explain select * from inventory where inventory_id=6 or store_id=2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory
   partitions: NULL
         type: ALL
possible_keys: PRIMARY,idx_store_id_film_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4581
     filtered: 50.01
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

4.5 使用 SQL 提示

可以找到參考的內容：8.9.4 Index Hints

SQL提示（SQL Hints）是優化資料庫的一項重要手段，簡單說是在SQL語句中加入一些人為的提示達到優化目的。下面將給出一個使用SQL提示的示例：

SELECT SQL_BUFFER_RESULT FROM t1...

其默認值為0，即是關閉狀態，設置為1則啟用。如果啟用，SQL_BUFFER_RESULT將強制SELECT語句的結果放入臨時表中。在需要很長時間向客戶端發送結果的情況下，幫助比較大，因為這有助於MySQL儘早釋放表鎖。

以下介紹一些在MySQL中常用的SQL提示：索引提示（Index Hints）

索引提示語法：

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
		index_hint [index_hint] ...
		
index_hint:
	USE {INDEX|KEY}
		[FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
		| {IGNORE|FORCE} {INDEX|KEY}
		[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
		index_name [, index_name] ...

看完提示語法，可以了解到索引提示三種技巧USE INDEX、IGNORE INDEX以及FORCE INDEX。

4.5.1 USE INDEX

在查詢語句中表名的背後，使用USE INDEX希望MySQL去參考索引列表，此時達到不讓MySQL去參考其它可用索引的目的。

示例：使用explain進行分析

mysql> explain select count(*) from countrylanguage use index(CountryCode)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: countrylanguage
   partitions: NULL
         type: index
possible_keys: NULL
          key: CountryCode
      key_len: 12
          ref: NULL
         rows: 984
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

根據上面分析結果，可以看出type=index，走索引掃描；Extra內容是Using index，達到我們預期要求。

4.5.1 IGNORE INDEX

如果用戶只是單純地想讓MySQL忽略某一個或多個索引，則可以使用IGNORE INDEX作為索引提示（HINTS）。

下面使用IGNORE INDEX進行演示：

mysql> explain select count(*) from countrylanguage ignore index(CountryCode)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: countrylanguage
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 132
          ref: NULL
         rows: 984
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

通過上述執行分析，放棄了默認索引，此時走的索引是PRIMARY。

4.5.1 FORCE INDEX

強制MySQL使用一個特定索引，可以在查詢中使用FORCE INDEX作為HINTS。

例如，不強制使用索引時，此時支付表中大部分rental_id都是大於1的，因此MySQL默認會全表掃描，而不使用索引。如下所示：

mysql> explain select * from sakila.payment where rental_id > 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: fk_payment_rental
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

此時，嘗試指定使用索引fk_payment_rental，發現MySQL依舊走全表掃描。

mysql> explain select * from sakila.payment use index(fk_payment_rental) where rental_id > 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: fk_payment_rental
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

當使用FORCE INDEX進行提示時，即使使用索引效率不是最高，MySQL還是選擇使用索引，這是MySQL將選擇執行計劃的權利交給了用戶。加入FORCE INDEX進行測試索引提示：

mysql> explain select * from sakila.payment force index(fk_payment_rental) where rental_id > 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: range
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
          ref: NULL
         rows: 8043
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

通過測試驗證，發現MySQL確實強制走了索引，印證了MySQL將選擇計劃使用索引提示權利交給了用戶。

注意：在MySQL8.0.20版本，此時服務支援index-level分析優化提示這些索引：JOIN_INDEX，GROUP_INDEX，ORDER_INDEX以及 INDEX。它們相當於取代了FORCE INDEX提示，同樣地NO JOIN INDEX、NO GROUP INDEX、NO ORDER INDEX和NO INDEX優化器提示，它們相當於並打算取代IGNORE INDEX索引提示。因此，你應該預料到使用USE INDEX、FORCE INDEX和IGNORE INDEX會在未來的MySQL版本中被棄用，並且在以後的某個時候會被完全刪除。

05 常用 SQL 技巧

常用SQL技巧主要介紹有：正則表達式。正則表達式泛用性比較廣，無論在資料庫SQL中還是Java語言以及Linux作業系統grep搜索匹配都用得上，甚至網頁爬蟲也很實用。提取隨機行函數RAND()。WITH ROLLUP子句。Bit GROUP Functions 做統計。資料庫庫名、表名大小寫注意事項。使用外鍵注意事項。

5.1 使用正則表達式

在MySQL8.0文檔中，你可以找到參考使用方法：12.8.2 Regular Expression Function and Operator Descriptions

正則表達式（Regular Expression）是指用來描述或匹配一系列符合某個句法規則的字元串的單個字元串。在多數文本編輯器或其它工具里，正則表達式通常被用來檢索或替換哪些符合某個模式的文本內容。許多程式語言都支援利用正則表達式進行字元串操作。例如，在Perl中就內建了一個功能強大的正則表達式引擎。正則表達式最初是由UNIX中的工具軟體（例如SED和GREP）普及開來，通常寫成REGEX 或者 REGEXP。

在linux作業系統中輸入pcretest即可進入練習使用正則表達式（新版本pcre2test）：

$ wget //download.fastgit.org/PhilipHazel/pcre2/releases/download/pcre2-10.39/pcre2-10.39.tar.gz
$ tar -zxvf pcre2-10.39.tar.gz
$ cd pcre2-10.39
$ ./configure
$ make && make install
$ pcre2test

MySQL利用REGEXP命令提供給用戶擴展正則表達式功能，REGEXP實現的功能類似於UNIX上GREP和SED功能，並且REGEXP在進行模式匹配是是區分大小寫的。熟悉掌握REGEXP用法，可以使模式匹配事半功倍。接下來將介紹一些在MySQL中的用法。

正則表達式函數和操作符如下表格所示：

名稱	描述
NOT REGEXP	否定的REGEXP
REGEXP	正則表達式是否匹配字元串
REGEXP_INSTR()	匹配正則表達式子字元串的起始索引
REGEXP_LIKE()	正則表達式是否匹配字元串
REGEXP_REPLACE()	替換正則表達式匹配的子字元串
REGEXP_SUBSTR()	返回正則表達式匹配的子字元串
RLIKE	正則表達式是否匹配字元串

在MySQL中一些正則表達式匹配符號含義：

符號	含義
^	在字元開始處進行匹配（行首）
$	字元串末尾進行匹配（行尾）
.	匹配任意單個字元，包括換行符
[……]	匹配括弧中任意字元
[^……]	匹配不包含括弧中任意字元
a*	匹配0個或多個a（包含空串）
a+	匹配1個或多個a（不包含空串）
a?	匹配1個或0個a
a1 \| a2	匹配a1或a2
a(m)	匹配m個a
(……)	將模式元素組成單一元素

下面將帶來實際示例REGEXP用法：

SELECT 'Michael!' REGEXP '.*';
+------------------------+
| 'Michael!' REGEXP '.*' |
+------------------------+
|                      1 |
+------------------------+

SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
+---------------------------------------+
| 'new*\n*line' REGEXP 'new\\*.\\*line' |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

SELECT 'a' REGEXP '^[a-d]';
+---------------------+
| 'a' REGEXP '^[a-d]' |
+---------------------+
|                   1 |
+---------------------+

REGEXP_INSTR用法：

SELECT REGEXP_INSTR('dog cat dog', 'dog');		-- 返回結果: 1
SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);	-- 返回結果: 9
SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}');		-- 返回結果: 1
SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');		-- 返回結果: 8

REGEXP_LIKE用法：

SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');	-- 返回結果:1

不做過多演示，使用比較容易上手，可以參考官方文檔。

5.2 RAND() 提取隨機行

大多數資料庫都會提供產生隨機數的包或者函數，通過這些包或者函數可以產生用戶需要的隨機數。也可以從數據表中抽取隨機產生的記錄，這對抽樣分析有一定的幫助。個人在MySQL開發篇進行測試生成1kw條數據，就用到了隨機數RAND()函數。在Oracle資料庫中可以使用DBMS_RANDOM包產生隨機數。例如在Oracle中學表隨機生成1kw條數據：

-- 創建表
CREATE TABLE test.student
(
    ID NUMBER not null primary key,
    STU_NAME VARCHAR2(60) not null,
    STU_AGE NUMBER(4,0) NOT NULL,
    STU_SEX VARCHAR2(2) not null
)

-- 學生表隨機生成1kw數據
insert into test.student
select rownum,dbms_random.string('*',dbms_random.value(6,10)),dbms_random.value(14,16),
'女' from dual
connect by level<=10000000

上面只是提一下穿插一點Oracle中的用法，主要介紹重點是MySQL。在MySQL中，產生隨機數是RAND() 函數。

創建表 t 以及插入測試數據。提示：不用創建表，你也可以直接在RAND後面圓括弧中加入數字進行測試。

mysql> CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

演示查詢RAND()：最普通的用法

mysql> SELECT i, RAND() FROM t;
+------+---------------------+
| i    | RAND()              |
+------+---------------------+
|    1 |  0.9726958740248306 |
|    2 |  0.2550815932965666 |
|    3 | 0.35732037514198606 |
+------+---------------------+
3 rows in set (0.00 sec)

演示查詢 RAND(X) 加入參數：X=3

mysql> SELECT i, RAND(3) FROM t;
+------+---------------------+
| i    | RAND(3)             |
+------+---------------------+
|    1 |  0.9057697559760601 |
|    2 | 0.37307905813034536 |
|    3 | 0.14808605345719125 |
+------+---------------------+
3 rows in set (0.00 sec)

RAND()函數用法有好幾種，如下：

RAND()：最原始用法，不加參數；
RAND(X)：加入一個X參數，比如RAND(3)；
RAND(X,D)：加入兩個參數表示範圍，比如RAND(1，2)；

示例：RAND(X,D)用法

SELECT ROUND(1.298, 1);
*************************** 1. row ***************************
ROUND(1.298, 1): 1.3
1 row in set (0.00 sec)

補充一點：RAND() 可以配合 ORDER BY 和 GROUP BY 以及 LIMIT 進行使用。

SELECT * FROM tbl_name ORDER BY RAND();
SELECT * FROM table1, table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000;

5.3 GROUP BY 與 WITH ROLLUP 子句

在SQL語句中，使用GROUP BY配合WITH ROLLUP 子句可以檢索出更多分組聚合資訊，不僅僅局限於GROUP BY檢索出各組聚合資訊，而且還能檢索出本組類的整體聚合資訊，創建實例如下所示。

創建一張某產品銷售利潤統計表進行演示：

CREATE TABLE sales
(
year INT,
country VARCHAR(20),
product VARCHAR(32),
profit INT
);

根據年度進行分組，然後查詢統計年度某產品利潤：

mysql> SELECT year, SUM(profit) AS profit FROM sales GROUP BY year;
+------+--------+
| year | profit |
+------+--------+
| 2022 |    666 |
| 2021 |    555 |
| 2020 |    455 |
+------+--------+

使用ROLLUP檢索出更多資訊。顯示每年的總利潤，要確定所有年份的總利潤，必須自己加起來，或者運行一個額外的查詢。或者您可以使用ROLLUP，它提供兩種級別的分析。

mysql> SELECT year, SUM(profit) AS profit FROM sales GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2020 |    455 |
| 2021 |    555 |
| 2022 |    666 |
| NULL |   1676 |
+------+--------+

配合WITH ROLLUP使用GROUP BY分組後面可以接多個欄位使用，以及使用IF條件加入GROUPING進行統計，這裡不做演示。

注意事項：以前，MySQL不允許在帶有WITH ROLLUP選項的查詢中使用DISTINCT或ORDER BY。這個限制在MySQL 8.0.12及更高版本中被取消（Bug #87450，Bug #86311，Bug #26640100，Bug #26073513）。此外，LIMIT在ROLLUP後面。

我所展示版本是MySQL8.0.28，支援WITH ROLLUP選項的查詢中使用DISTINCT或ORDER BY。

mysql> SELECT * FROM(SELECT year, SUM(profit) AS profit 
       FROM sales GROUP BY year WITH ROLLUP) AS dt ORDER BY year ASC;
+------+--------+
| year | profit |
+------+--------+
| NULL |   1676 |
| 2020 |    455 |
| 2021 |    555 |
| 2022 |    666 |
+------+--------+

5.4 Bit GROUP Functions 做統計

你可以找到參考文檔：12.13 Bit Functions and Operators

此處，不做詳細解釋，只展示具體使用。

以下演示GROUP BY語句和BIT_OR、BIT_AND函數完成統計工作。這兩個函數一般用於做數值間的邏輯運算，當將它們與GROUP BY子句聯合使用時可以做一些其它的任務。

以下是創建一張示例表t2並插入6條測試數據：

CREATE TABLE t2 (
year YEAR, 
month INT UNSIGNED,
day INT UNSIGNED
);

INSERT INTO t2 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),(2000,2,23),(2000,2,23);

使用BIT_COUNT以及BIT_OR、BIT_AND進行查詢：

mysql> SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t2 GROUP BY year,month;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |     1 |    3 |
| 2000 |     2 |    2 |
+------+-------+------+

mysql> SELECT year,month,BIT_AND(day) AS days FROM t2 GROUP BY year,month;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |     1 |    0 |
| 2000 |     2 |    2 |
+------+-------+------+

5.5 資料庫庫名、表名大小寫問題

MySQL資料庫對應作業系統下的數據目錄。資料庫中每張表至少對應資料庫目錄中一個文件（也可能是多個，存儲引擎類型不同，有所差異）。因此，使用的作業系統大小寫敏感性決定了資料庫名、表名大小寫的敏感性。在Unix作業系統中，作業系統對大小敏感，導致資料庫名、表名對大小寫敏感。而Windows平台MySQL資料庫對大小寫不敏感，因為作業系統本身對大小寫不敏感。

列、索引、存儲子程式和觸發器名在任何平台上對大小寫不敏感。默認情況，表別名在Unix中對對大小敏感，但在Windows平台對大小寫並不敏感。如下在Linux平台進行演示，由於區分大小寫，所以拋出錯誤提示：

mysql> select * from girl;
ERROR 1146 (42S02): Table 'test.girl' doesn't exist

正常情況，使用大寫表名進行查找：

mysql> select * from GIRL;
+------+-----------+----------+----------+
| ID   | GIRE_NAME | GIRL_AGE | CUP_SIZE |
+------+-----------+----------+----------+
| 1001 | 夢夢       | 14       | C        |
+------+-----------+----------+----------+
1 row in set (0.02 sec)

如上報錯以及正常返結果查詢操作在Windows平台都可以正常執行。如果想儘可能避免出現差錯，統一規範，例如創建時統一使用小寫創建庫名、表名。

MySQL資料庫中，如何在硬碟中保存使用表名、資料庫名是由lower_case_table_names系統變數決定的，用戶可以在啟動MySQL服務之前設置系統變數值（由於Dynamic=no，非動態）。具體設置對應作業系統、以及含義如下表格：

參數值	作業系統	含義
0	Unix默認值	如果設置為0，表名將按指定的方式存儲，並且比較是區分大小寫的。對大小寫敏感。如果在不區分大小寫的文件系統上強制使用0，並使用不同字母大小寫訪問MyISAM表，可能會導致索引損壞。
1	Windows默認值	如果設置為1，表名在磁碟上以小寫存儲，MySQL在存儲和查找時將所有表名轉換為小寫。同樣使用資料庫名和別名。
2	macOS默認值	如果設置為2，表名將以給定的形式存儲，比較時使用小寫。MySQL將其轉換為小寫以便查找，適用於不區分大小寫的文件系統。

例如：Windows平台使用如下SQL語句進行查詢系統默認設置lower_case_table_names值，返回結果是1

mysql> select @@lower_case_table_names;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

設置--lower-case-table-names[=#]參數值，在Windows平台直接編輯my.ini文件，在Linux作業系統可以使用vim編輯/etc/my.cnf中新增如下設置：

# my.cnf or my.ini
[mysqld]
## --lower-case-table-names[=#]	#命令行格式：參數值可以為0 1 2，根據系統平台設定
#example
lower-case-table-names=1 	# Windows平台默認值
lower-case-table-names=0	# Linux默認值為0，設置0和1都可以成功啟動

tips：如果在單個平台使用，影響不是很大。使用時儘可能在同一查詢中使用相同大小寫來引用資料庫名或表名，養成一個良好習慣。

5.6 使用外鍵注意事項

在MySQL中，InnoDB存儲引擎支援對外部關鍵字約束條件檢查。對於其它類型存儲引擎的表，當使用REFERENCES tbl_name(col_name,…)子句定義列時可以使用外部關鍵字，但該子句沒有實際效果，可以作為注釋提醒用戶目前定義的列指向另一表中的一個列。具體語法在此處，不做演示，在第三章節鎖問題（InnoDB鎖問題：外鍵與鎖有說明）。

接下來，演示不同類型存儲引擎使用外鍵效果，具體也只演示MyISAM和InnoDB存儲引擎使用外鍵創建父（parent）表和子（child）表。示例如下：

5.6.1 MyISAM存儲引擎建立有外鍵父表與子表

CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;

-- 創建子表child，並加入給update與delete條件加入CASCADE
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=MyISAM;

測試插入數據：父表（parent）插入一條演示數據，子表（child）插入3條演示數據，有級聯關係。如果父表內容被修改，子表三條關聯外鍵內容也應該修改過來。實際上MyISAM存儲引擎並不支援外鍵，所以不生效。

INSERT INTO parent (id) VALUES (1);
INSERT INTO child (id,parent_id) VALUES(1,1),(2,1),(3,1);
UPDATE parent SET id = 2 WHERE id = 1;

最後驗證查詢有關聯的子表，數據並沒有變化：

mysql> select * from child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         1 |
|    2 |         1 |
|    3 |         1 |
+------+-----------+
3 rows in set (0.00 sec)

你還可以使用語句show create table tbl_name命令查看創建的表child並沒有顯示外鍵資訊，而InnoDB存儲引擎會顯示外鍵資訊。

mysql> show create table child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  KEY `par_ind` (`parent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> show create table test.child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

5.6.2 InnoDB存儲引擎建立有外鍵父表與子表

DROP TABLE parent;-- 刪除原有創建子表parent
CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;

DROP TABLE child;-- 刪除原有創建子表child
-- 重新創建子表child，並加入給update與delete條件加入CASCADE
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=INNODB;

測試插入數據：父表（parent）插入一條演示數據，子表（child）插入3條演示數據，有級聯關係。如果父表內容被修改，子表三條關聯外鍵內容也應該修改過來。InnoDB存儲引擎支援外鍵，所以級聯修改起作用，parent_id值被集體修改為2。

INSERT INTO parent (id) VALUES (1);
INSERT INTO child (id,parent_id) VALUES(1,1),(2,1),(3,1);
UPDATE parent SET id = 2 WHERE id = 1;

最後驗證查詢有關聯的子表，查詢演示數據：

mysql> SELECT * FROM child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         2 |
|    2 |         2 |
|    3 |         2 |
+------+-----------+
3 rows in set (0.00 sec)

更加詳細演示，在下面外鍵與鎖章節描述比較詳細。也可以參考官方文檔示例：product_order、product、customer這三張表之間使用外鍵進行操作。模擬產品（product）、顧客（customer）、訂單（product_order）三張表關聯關係，訂單表設置級聯（CASCADE）關係，並且同時引用產品與顧客相應欄位作為外鍵引用。

CREATE TABLE product (
category INT NOT NULL, 
id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)
) ENGINE=INNODB;

CREATE TABLE customer (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE product_order (
no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
INDEX (customer_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (customer_id)
REFERENCES customer(id)
) ENGINE=INNODB;

二、優化資料庫對象

第二部分，優化資料庫對象。看看就行，因為沒做過多示例介紹，以理論知識居多。

面對資料庫設計過程，用戶可能會遇到這類問題。是否完全遵循資料庫設計三範式設計表結構？表的欄位值大小到底設置為多長合適？這些問題看似很小，但設計不當則可能會給將來的應用帶來很多性能問題。

01 優化表數據類型

設計表的時候，需要給定欄位類型。

表需要使用何種數據類型應該依據實際應用來判斷。當然，考慮到應用欄位留有冗餘是一個不錯的選擇。但並不推薦所有欄位留有大量的冗餘，因為浪費磁碟存儲空間，同時在操作應用時也浪費物理記憶體。

在MySQL中，可以使用函數procedure analyse()對當前應用的表進行分析。該函數可以對數據表中列的數據類型提出優化建議，可以根據實際情況進行優化。

示例：MariaDB 10.5.6中使用procedure analyse()

MariaDB [test]> select * from student procedure analyse()\G
*************************** 1. row ***************************
             Field_name: test.student.ID
              Min_value: 1
              Max_value: 1000000
             Min_length: 1
             Max_length: 7
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 500000.5000
                    Std: 577357.8230
      Optimal_fieldtype: MEDIUMINT(7) UNSIGNED NOT NULL

最終給出的優化建議Optimal_fieldtype：MEDIUMINT(7) UNSIGNED NOT NULL，欄位類型MEDIUMINT(7) 。

注意：在MySQL 5.x版本和MariaDB 10.5.6還可以使用。但在MySQL8.0.x版本已經被移除了，暫時沒看到替代的方式。

PROCEDURE ANALYSE() syntax is removed.

摘自第1.3章節：Features Removed in MySQL 8.0

02 拆分表提高訪問效率

看小標題已經描述很清晰，通過對數據表進行拆分。

假如針對MyISAM類型表進行，有如下兩種方式：

垂直拆分：將主列和一些列存放至一張表中，然後將主列和另外的列存放到另一張表中。如果不好理解，可以想像一下垂直平分線的方式。如果一張表某些列常用，而另一些列不常用，則可以採取垂直拆分。

垂直拆分可以使數據行變小，一個數據頁可以存放更多數據，查詢時會減少I/O次數。缺點在於需要管理冗餘列，查詢所有數據需要聯合（union）操作。
水平拆分：根據一列或多列數據的值將數據行放入兩張獨立的表中。
水平拆分通常在以下幾種場景下使用：

表很大，分割後可以降低在查詢時需要讀取的數據和索引頁數。同時降低索引層數，提高查詢速度。

表中數據本就有獨立性。比如，表中數據記錄著不同地區的數據或者不同時間段的數據。區分常用數據和不常用數據，需要將數據存放在多個介質上。

水平拆分會給應用增加複雜度，查詢時通常需要聯結多個表，查詢所有數據需要使用UNION操作。考慮是否進行水平拆分，可以依據應用實際數據增長速率進行酌情處理。

03 逆規範

談到逆規範，第一時間會想到規範，其次想到表中加入冗餘欄位便於操作。

從我們學習資料庫知識起，已經深入到腦海里並理解滿足規範設計的重要性。

是不是滿足數據設計規範越高越好呢？以前資料庫沒那麼多範式，最多滿足3範式，現在到了N範式。個人理解，應該根據實際需求定，不應一概而論。規範越高，關係相對越複雜，表之間聯結操作越頻繁。如果是查詢統計較多的應用，則大大影響查詢性能。

設計逆規範時，我們想達到的目的是啥？降低聯結操作需求、減少索引數目，也許還會減少表數目。如果帶來數據完整性問題，如何處理。做逆規範，理應權衡利弊；弊大於利，則適得其反。如果優質索引可以解決，則不必增加逆規範。

使用逆規範前的思考：

數據存儲需求；
常用表大小；
特殊計算（比如合計）；
物理存儲位置。

常用逆規範技術手段：增加冗餘列、派生列、重新組表和分割表。

使用逆規範操作，往往有一種比較友好的方式來應對處理，那就是觸發器。對數據任何修改立即出發對複製列或派生列的相應修改。觸發器是實時的，相應處理邏輯只在一個地方出現，易於維護。

04 中間表提高統計查詢效率

曾幾何時，你在面試時遇到是否有海量數據處理經驗。如果是你來應對，如何處理，思考過如何回答么？

仔細想想，其實可以從單表存儲數據過多，會帶來哪些缺點進行思考。

對於數據量較大的表，進行統計查詢通常效率會降低，並且還要考慮統計查詢是否影響在線應用（負面影響）。通常在這種情況下，使用中間表可以提高查詢效率。考慮前提，對轉移（複製）當前表時間進行忽略。

使用方法進行示例：只需兩步完成操作

1、創建新表使用源表數據結構（你也可以適當優化，比如常用欄位加單獨索引）。當時考慮Oracle中分批次生成1kw數據想到這種方法。

create table test.student01 as select * from test.student;

2、然後插入源表數據，這樣做確實很方便。

insert into test.student01 select * from test.student;

做完之後，數據轉移到中間表上進行統計，得到結果。既不影響在線應用，也可以快速查詢統計。

中間表做統計查詢優點：

複製源表部分數據，與源表隔離，中間表做統計查詢不影響在線應用使用。
靈活添加索引，增加臨時欄位，最終達到提高統計查詢效率。

參考資料&鳴謝：

《深入淺出MySQL 第2版資料庫開發、優化與管理維護》，個人參考優化篇部分。
《MySQL技術內幕InnoDB存儲引擎第2版》，個人參考索引與鎖章節描述。
MySQL8.0官網文檔：refman-8.0-en.pdf，要學習新版本，官方文檔是非常不錯的選擇。

雖然書籍年份比較久遠（停留在MySQL5.6.x版本），但仍然具有借鑒意義。

最後，對以上書籍和官方文檔所有作者表示衷心感謝。讓我充分體會到：前人栽樹，後人乘涼。

莫問收穫，但問耕耘

只停留在看上面，提升效果甚微。應該帶著思考去測試佐證，或者使用（同類書籍）新版本進行對比，這樣帶來的效果更好。最重要的一環，養成閱讀官方文檔，是一個良好的習慣。能編寫官方文檔，至少證明他們在這個領域是有很高的造詣，對用法足夠熟練。

能看到這裡的，都是帥哥靚妹。以上是本次MySQL優化篇（上部分）全部內容，希望能對你的工作與學習有所幫助。感覺寫的好，就拿出你的一鍵三連。如果感覺總結的不到位，也希望能留下您寶貴的意見，我會在文章中定期進行調整優化。好記性不如爛筆頭，多實踐多積累。你會發現，自己的知識寶庫越來越豐富。原創不易，轉載也請標明出處和作者，尊重原創。

一般情況下，會優先在公眾號發布：龍騰萬里sky。

不定期上傳到github倉庫：

//github.com/cnwangk/SQL-study

Tags: MySQL