1、索引概述
索引（Index）是高效獲取資料的資料結構，就像書的目錄，提高檢索資料的效率。
優點：提高資料檢索效率，降低資料庫的 IO 成本；通過索引列對資料進行排序，降低資料排序的成本，降低 CPU 的消耗。
缺點：索引列會佔用空間；索引提高查詢效率的同時也降低了更新表的速度，如對錶進行增刪改時，效率降低。
2、索引結構
MySQL 的索引結構主要包含以下幾種：
| 索引 | 描述 |
|---|---|
| B+Tree | 最常見的索引型別，大部分儲存引擎都支援 B+ 樹索引 |
| Hash | 底層資料結構是用雜湊表實現的，只有精確匹配索引列的查詢才有效，不支援範圍查詢 |
| R-Tree | 空間索引時 MyISAM 引擎的一個特殊索引型別，主要用於地理空間資料型別，較少使用 |
| Full-Text | 是一種通過建立倒排序索引，快速匹配文件的方式，類似於 ES、Solr |
MySQL 的索引是在儲存引擎層實現的，不同的儲存引擎對於索引結構的支援：
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+Tree | 支援 | 支援 | 支援 |
| Hash | × | × | 支援 |
| R-Tree | × | 支援 | × |
| Full-Text | 5.6版本之後支援 | 支援 | × |
-
二叉樹
Binary tree，二叉樹特點是每個節點最多隻能有兩棵子樹，且有左右之分。如果選擇二叉樹作為索引結構，會存在以下缺點：順序插入時，會形成一個連結串列，查詢效能大大降低；大資料量情況下，層級較深，檢索速度慢。 -
B樹
B-Tree，B樹是一種多叉路衡查詢樹，相對於二叉樹，B樹每個節點可以有多個分支，即多叉。以一顆最大度數（max-degree，一個節點的子節點個數）為5階的B樹為例，那這個B樹每個節點最多儲存4個key，5個指標。一旦節點儲存的key數量到達5，就會裂變，中間元素向上分裂。在B樹中，非葉子節點和葉子節點都會存放資料。 -
B+樹
相對於B樹的區別：所有的資料都出現在葉子節點；葉子節點形成一個單向連結串列；非葉子節點僅起到索引資料作用。MySQL 索引資料結構在原B+樹上進行了優化，增加了一個相鄰葉子節點的連結串列指標，形成帶有順序指標的B+樹，提高區間訪問效能，利於排序。 -
Hash
雜湊索引就是採用一定的hash演算法，將鍵值換算成新的 hash 值，對映到對應的槽位上，然後儲存在 hash 表中。如果兩個或多個鍵值對映到一個相同的槽位上，他們就產生了 hash 衝突/碰撞，可以通過連結串列來解決。Hash 索引只能用於對等比較（=，in），不支援範圍查詢（between，>，< ，...），無法利用索引完成排序操作，查詢效率高，通常不存在 hash 衝突的情況下只需要一次檢索就可以了，效率通常要高於B+樹索引。
3、索引分類
MySQL 資料庫中索引的具體型別主要分為以下幾類：
| 分類 | 含義 | 特點 | 關鍵字 |
|---|---|---|---|
| 主鍵索引 | 表中主鍵建立的索引 | 預設自動建立，只有能一個 | primary |
| 唯一索引 | 避免同一個表中某資料列中的值重複 | 可以有多個 | unique |
| 常規索引 | 快速定位特定資料 | 可以有多個 | |
| 全文索引 | 全文索引查詢的是文字中的關鍵詞，而不是比較索引中的值 | 可以有多個 | fulltext |
而在 InnoDB 儲存引擎中，根據索引的儲存形式，又可以分為以下兩種：
| 分類 | 含義 | 特點 |
|---|---|---|
| 聚集索引（ClusteredIndex） | 將資料儲存與索引放到了一塊，索引結構的葉子節點儲存了行資料 | 必須有，且只有一個 |
| 二級索引（SecondaryIndex） | 將資料與索引分開儲存，索引結構的葉子節點關聯的是該欄位值對應的主鍵值 | 可以存在多個 |
聚集索引選取規則：
1、如果存在主鍵，主鍵索引就是聚集索引；
2、如果不存在主鍵，將使用第一個唯一索引作為聚集索引；
3、如果表沒有主鍵，或沒有合適的唯一索引，則 InnoDB 會自動生成一個 rowid 作為隱藏的聚集索引。
4、索引語法
#建立索引
create [ unique | fulltext ] index 索引名 on 表名 (欄位名, ...);
#檢視索引
show index from 表名;
#刪除索引
drop index 索引名 on table;
5、SQL 效能分析
5.1、執行頻次
MySQL 客戶端連線成功後，通過如下指令可以檢視當前資料庫 insert、delete、update、select 的訪問頻次。
#查詢全域性資料：global
#檢視當前會話：session
show global status like 'Com_______'; #七個下劃線
#執行命令
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 0 |
| Com_delete | 2 | #刪除次數
| Com_import | 0 |
| Com_insert | 5 | #插入次數
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 10 | #查詢次數
| Com_signal | 0 |
| Com_update | 2 | #更新次數
| Com_xa_end | 0 |
+---------------+-------+
5.2、慢日誌查詢
慢查詢日誌記錄了所有執行時間超過指定引數（long_query_time，單位：秒，預設10秒）的所有SQL語句的日誌。
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
配置檔案：MySQL Server 8.0\my.ini
#慢日誌查詢開關
slow-query-log=1
#設定慢日誌的時間為2秒，SQL語句執行時間超過2秒，就會視為慢查詢，記錄慢查詢日誌
long_query_time=10
暫略。
5.3、profile
show profiles 能夠在做 SQL 優化時幫助我們瞭解時間都耗費在哪裡。
檢視當前 MySQL 是否支援 profile 操作：
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
profile 是預設關閉的，檢視 profile 是否開啟：
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 | #0表示未開啟
+-------------+
通過 set 語句在 session / global 級別開啟 profiling：
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 | #1表示開啟
+-------------+
此時執行一系列的業務SQL操作：
select count(*) from student;
select * from student;
select * from student where name = '張三';
通過以下命令檢視指令的執行耗時：
#檢視每一條SQL的耗時基本情況
show profiles;
#檢視指定 query_id 的SQL語句各個階段的耗時情況
show profile for query query_id;
#檢視指定 query_id 的SQL語句CPU的使用情況
show profile cpu for query query_id;
#檢視每一條SQL的耗時基本情況
mysql> show profiles;
+----------+------------+-------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------+
| 1 | 0.00012400 | select @@profiling |
| 2 | 0.00010975 | select * from student |
| 3 | 0.00017200 | SELECT DATABASE() |
| 4 | 0.00020550 | select count(*) from student |
| 5 | 0.09767125 | select * from student |
| 6 | 0.00056350 | select * from student where name = '張三' |
+----------+------------+-------------------------------------------+
#檢視指定 query_id 的SQL語句各個階段的耗時情況
mysql> show profile for query 4;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000042 |
| Executing hook on transaction | 0.000005 |
| starting | 0.000006 |
| checking permissions | 0.000004 |
| Opening tables | 0.000234 |
| init | 0.000004 |
| System lock | 0.000006 |
| optimizing | 0.000003 |
| statistics | 0.000007 |
| preparing | 0.000011 |
| executing | 0.097268 |
| end | 0.000010 |
| query end | 0.000003 |
| waiting for handler commit | 0.000009 |
| closing tables | 0.000007 |
| freeing items | 0.000047 |
| cleaning up | 0.000008 |
+--------------------------------+----------+
5.4、explain
explain 或 desc 命令獲取 MySQL 如何執行 select 語句的資訊，包括在 select 語句執行過程中表如何連線和連線的順序。
[explain | desc] select * from 表名 [where 過濾條件];
mysql> desc select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
explain 執行計劃中各個欄位的含義：
| 欄位 | 含義 |
|---|---|
| id | 查詢序列號，表示查詢中執行 select 子句或者是操作表的順序（id相同，執行順序從上到下；id不同，值越大，越先執行） |
| select_type | 查詢型別，常見的取值有：simple（簡單表，即不使用表連線或者子查詢）、primary（主查詢，即外層的查詢）、union（union 中的第二個或者後面的查詢語句）、subquery（select / where 之後包含子查詢）等 |
| table | 表名 |
| partitions | 涉及到的分割槽 |
| type | 連線型別，效能由好到差的順序為：null、system、const、eq_ref、ref、range、 index、all |
| possible_keys | 可能應用在這張表上的索引，一個或多個 |
| key | 實際使用的索引，如果為 null，則沒有使用索引 |
| key_len | 索引中使用的位元組數， 該值為索引欄位最大可能長度，並非實際使用長度，在不損失精確性的前提下， 長度越短越好 |
| ref | 引用到的上一個表的列 |
| rows | MySQL 認為必須要執行查詢的行數，在 InnoDB 引擎的表中，是一個估計值，可能並不總是準確的 |
| filtered | 返回結果的行數佔需讀取行數的百分比， 值越大越好 |
| Extra | 額外的資訊說明 |
6、索引使用規則
6.1、單列&聯合索引
單列索引：一個索引只包含單個列。
聯合索引：一個索引包含了多個列。
在 and 連線的兩個欄位上都有單列索引，但 mysql 最終只會選擇一個索引，只能走一個欄位的索引，此時容易造成回表查詢的。
在業務場景中，如果存在多個查詢條件，考慮針對於查詢欄位建立索引時，建議建立聯合索引。
6.2、最左字首
最左字首法則：
- 索引列全部存在，索引生效；
- 索引列部分存在：
- 不存在最左列索引，索引失效；
- 存在最左列索引：
- 最左列索引在第一個條件，後續條件按索引列順序編寫，該部分索引全部生效；
- 最左列索引不在第一個條件，只有最左列索引生效。
6.3、範圍查詢
聯合索引中，出現範圍查詢大於或小於的，範圍查詢右側的列索引失效。
6.4、索引失效
- 索引列上進行運算操作；
- 字串型別欄位使用時不加引號；
- 頭部模糊匹配；
- or 前的條件有索引，or 後的條件沒有索引；
- MySQL 評估使用索引比全表更慢（資料量大於等於表中一半）。
6.5、SQL 提示
如果列 A 既是單列索引，又是聯合索引的最左列。此時根據 A 欄位條件進行查詢，MySQL 優化器會自動選擇一個索引來使用。
假如在某些場景下，我想告訴資料庫，如果 A 欄位有多個索引，則需要按照我指定的索引來進行查詢，此時需要 SQL 提示。
SQL 提示是優化資料庫的一個重要手段，就是在 SQL 語句中加入一些人為的提示來達到優化操作的目的。
use index：建議 MySQL 使用哪一個索引完成此次查詢，MySQL 內部還會再次進行評估。
ignore index：忽略指定索引。
force index：強制使用索引。
語法：
select * from 表名 [ use | ignore | force index(索引名) where 過濾條件];
6.6、覆蓋索引
儘量使用覆蓋索引（查詢使用了索引，且需要返回的列在該索引中能全部找到），減少 select * 。
| Extra | 含義 |
|---|---|
| Using where; Using Index | 查詢使用了索引，但是需要的資料都在索引列中能找到，所以不需要回表查詢資料 |
| Using index condition | 查詢使用了索引，但是需要回表查詢資料 |
| Null | 需要回表查詢資料 |
6.7、字首索引
當欄位型別為字串（varchar，text，longtext 等）時，有時候需要索引很長的字串，這會讓索引變得很大，查詢時浪費大量的磁碟IO， 影響查詢效率。此時可以只將字串的一部分字首，建立索引，這樣可以大大節約索引空間，從而提高索引效率。
語法：
create index 索引名 on 表名(欄位名(n)); #n：字首長度
字首長度：
可以根據索引的選擇性來決定，而選擇性是指不重複的索引值（基數）和資料表的記錄總數的比值。
索引選擇性越高則查詢效率越高， 唯一索引的選擇性是1，這是最好的索引選擇性，效能也是最好的。
#建立字首索引，降低字首索引的體積，指定前 n 個字元
select count(distinct substring(欄位名, 1, n)) / count(*) from 表名; #值=1最好
7、索引設計原則
- 針對於資料量較大，且查詢比較頻繁的表建立索引；
- 針對於常作為查詢條件（where）、排序（order by）、分組（group by）之後的欄位建立索引；
- 儘量選擇區分度高的列作為索引，儘量建立唯一索引，區分度越高，使用索引的效率越高；
- 如果是字串型別的欄位，欄位的長度較長，可以針對於欄位的特點，建立字首索引；
- 儘量使用聯合索引，減少單列索引，查詢時，聯合索引很多時候可以覆蓋索引，節省儲存空間，避免回表，提高查詢效率；
- 要控制索引的數量，索引並不是多多益善，索引越多，維護索引結構的代價也就越大，會影響增刪改的效率；
- 如果索引列不能儲存 null 值，在建表時使用 not null 約束它。當優化器知道每列是否包含 null 值時，它可以更好的確定哪個索引最有效地用於查詢。