我可以和麵試官多聊幾句嗎？只是想偷點技能過來。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檔案，空餘時間會製作目錄連結：
https://github.com/cnwangk/SQL-study/tree/master/md/SQL/MySQL
MySQL優化篇（一）
MyISAM表鎖和InnoDB鎖問題會在第二篇：MySQL優化篇（二）進行釋出，篇幅太長，不便一次性全部發完。
給出sakila-db資料庫包含三個檔案，便於大家獲取與使用：
- sakila-schema.sql：資料庫表結構；
- sakila-data.sql：資料庫示例模擬資料；
- sakila.mwb：資料庫物理模型，在MySQL workbench中可以開啟檢視。
world-db資料庫，包含三張表：city、country、countrylanguage。
只是用於用於簡單測試學習，建議使用world-db：
生產前：
應用開發初期資料量比較小，開發人員在編寫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安裝教程全程參考官方文件》是一樣的想法，希望大家能認識到自學的重要性，以及閱讀官方文件自我成長。結合有道和谷歌翻譯以及自己的翻譯進行理解，感覺翻譯很彆扭，可以對單個單詞進行分析，結合自己的經驗調整並符合閱讀習慣。
參考文件：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：
通過上述步驟查詢到低效率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 https://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倉庫：