什么是索引？

索引是一種能幫助 MySQL 提高查詢效率的數(shù)據(jù)結構。

索引分別有哪些優(yōu)點和缺點？

索引的優(yōu)點如下：

• 快速訪問數(shù)據(jù)表中的特定信息，提高檢索速度。
• 創(chuàng)建唯一性索引，保證數(shù)據(jù)表中每一行數(shù)據(jù)的唯一性。
• 加速表與表之間的連接。
• 使用分組和排序進行數(shù)據(jù)檢索時，可以顯著減少查詢中分組和排序的時間。

索引的缺點：

• 雖然提高了的查詢速度，但卻降低了更新表的速度，比如 update、insert，因為更新數(shù)據(jù)時，MySQL 不僅要更新數(shù)據(jù)，還要更新索引文件；
• 建立索引會占用磁盤文件的索引文件。

使用索引注意事項：

• 使用短索引，短索引不僅可以提高查詢速度，更能節(jié)省磁盤空間和 I/O 操作；
• 索引列排序，MySQL 查詢只使用一個索引，因此如果 where 子句中已經使用了索引的話，那么 order by 中的列是不會使用索引的，因此數(shù)據(jù)庫默認排序可以符合要求的情況下，不要進行排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創(chuàng)建復合索引；
• like 語句操作，一般情況下不鼓勵使用 like 操作，如果非使用不可， 注意 like "%aaa%" 不會使用索引，而like "aaa%"可以使用索引；
• 不要在列上進行運算；
• 不適用 NOT IN 和 <> 操作。

以下 SQL 有什么問題？該如何優(yōu)化？

select * from t where f/2=100;

該 SQL 會導致引擎放棄索引而全表掃描，盡量避免在索引列上計算。可改為：

select * from t where f=100*2;

為什么 MySQL 官方建議使用自增主鍵作為表的主鍵？

因為自增主鍵是連續(xù)的，在插入過程中盡量減少頁分裂，即使要進行頁分裂，也只會分裂很少一部分；并且自增主鍵也能減少數(shù)據(jù)的移動，每次插入都是插入到最后，所以自增主鍵作為表的主鍵，對于表的操作來說性能是最高的。

自增主鍵有哪些優(yōu)缺點?

優(yōu)點：

• 數(shù)據(jù)存儲空間很??；
• 性能最好；
• 減少頁分裂。

缺點：

• 數(shù)據(jù)量過大，可能會超出自增長取值范圍；
• 無法滿足分布式存儲，分庫分表的情況下無法合并表；
• 主鍵有自增規(guī)律，容易被破解；

綜上所述：是否需要使用自增主鍵，需要根據(jù)自己的業(yè)務場景來設計。如果是單表單庫，則優(yōu)先考慮自增主鍵，如果是分布式存儲，分庫分表，則需要考慮數(shù)據(jù)合并的業(yè)務場景來做數(shù)據(jù)庫設計方案。

索引有幾種類型？分別如何創(chuàng)建？

MySQL 的索引有兩種分類方式：邏輯分類和物理分類。 按照邏輯分類，索引可分為：

• 主鍵索引：一張表只能有一個主鍵索引，不允許重復、不允許為 NULL；
• 唯一索引：數(shù)據(jù)列不允許重復，允許為 NULL 值，一張表可有多個唯一索引，但是一個唯一索引只能包含一列，比如身份證號碼、卡號等都可以作為唯一索引；
• 普通索引：一張表可以創(chuàng)建多個普通索引，一個普通索引可以包含多個字段，允許數(shù)據(jù)重復，允許 NULL 值插入；
• 全文索引：讓搜索關鍵詞更高效的一種索引。

按照物理分類，索引可分為：

• 聚集索引：一般是表中的主鍵索引，如果表中沒有顯示指定主鍵，則會選擇表中的第一個不允許為 NULL 的唯一索引，如果還是沒有的話，就采用 Innodb 存儲引擎為每行數(shù)據(jù)內置的 6 字節(jié) ROWID 作為聚集索引。每張表只有一個聚集索引，因為聚集索引的鍵值的邏輯順序決定了表中相應行的物理順序。聚集索引在精確查找和范圍查找方面有良好的性能表現(xiàn)（相比于普通索引和全表掃描），聚集索引就顯得彌足珍貴，聚集索引選擇還是要慎重的（一般不會讓沒有語義的自增 id 充當聚集索引）；
• 非聚集索引：該索引中索引的邏輯順序與磁盤上行的物理存儲順序不同（非主鍵的那一列），一個表中可以擁有多個非聚集索引。

各種索引的創(chuàng)建腳本如下：

-- 創(chuàng)建主鍵索引
alter table t add primary key add (`id`);
-- 創(chuàng)建唯一索引
alter table t add unique (`username`);
-- 創(chuàng)建普通索引
alter table t add index index_name (`username`);
-- 創(chuàng)建全文索引
alter table t add fulltext (`username`);

主索引和唯一索引有什么區(qū)別？

• 主索引不能重復且不能為空，唯一索引不能重復，但可以為空；
• 一張表只能有一個主索引，但可以有多個唯一索引；
• 主索引的查詢性能要高于唯一索引。

在 InnDB 中主鍵索引為什么比普通索引的查詢性能高？

因為普通索引的查詢會多執(zhí)行一次檢索操作。比如主鍵查詢 select * from t where id=10 只需要搜索 id 的這棵 B+
樹，而普通索引查詢 select * from t where f=3 會先查詢 f 索引樹，得到 id 的值之后再去搜索 id 的 B+
樹，因為多執(zhí)行了一次檢索，所以執(zhí)行效率就比主鍵索引要低。

什么叫回表查詢？

普通索引查詢到主鍵索引后，回到主鍵索引樹搜索的過程，我們稱為回表查詢。

參考SQL：

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

如果語句是 select * from T where ID=500，即主鍵查詢方式，則只需要檢索主鍵 ID 字段。

mysql>  select * from T where ID=500;
+-----+---+-------+
| id  | k | name  |
+-----+---+-------+
| 500 | 5 | name5 |
+-----+---+-------+

如果語句是 select * from T where k=5，即普通索引查詢方式，則需要先搜索 k 索引樹，得到 ID 的值為 500，再到 ID
索引樹搜索一次，這個過程稱為回表查詢。

mysql> select * from T where k=5;
+-----+---+-------+
| id  | k | name  |
+-----+---+-------+
| 500 | 5 | name5 |
+-----+---+-------+

也就是說，基于非主鍵索引的查詢需要多掃描一棵索引樹。因此，我們在應用中應該盡量使用主鍵查詢。

如何查詢一張表的所有索引？

SHOW INDEX FROM T 查詢表 T 所有索引。

MySQL 最多可以創(chuàng)建多少個索引列？

MySQL 中最多可以創(chuàng)建 16 個索引列。

以下 like 查詢會使用索引的是哪一個選項？為什么？

A.like '%A%' B.like '%A' C.like 'A%' D.以上都不是 答：C 題目解析：like
查詢要走索引，查詢字符不能以通配符（%）開始。

如何讓 like %abc 走索引查詢？

我們知道如果要讓 like 查詢要走索引，查詢字符不能以通配符（%）開始，如果要讓 like %abc 也走索引，可以使用 REVERSE()
函數(shù)來創(chuàng)建一個函數(shù)索引，查詢腳本如下：

select * from t where reverse(f) like reverse('%abc');

MySQL 聯(lián)合索引應該注意什么？

聯(lián)合索引又叫復合索引，MySQL 中的聯(lián)合索引，遵循最左匹配原則，比如，聯(lián)合索引為 key(a,b,c)，則能觸發(fā)索引的搜索組合是 a|ab|abc
這三種查詢。

聯(lián)合索引的作用是什么？

聯(lián)合索引的作用如下：

• 用于多字段查詢，比如，建了一個 key(a,b,c) 的聯(lián)合索引，那么實際等于建了 key(a)、key(a,b)、key(a,b,c) 等三個索引，我們知道，每多一個索引，就會多一些寫操作和占用磁盤空間的開銷，尤其是對大數(shù)據(jù)量的表來說，這可以減少一部分不必要的開銷；
• 覆蓋索引，比如，對于聯(lián)合索引 key(a,b,c) 來說，如果使用 SQL：select a,b,c from table where a=1 and b = 1 ，就可以直接通過遍歷索引取得數(shù)據(jù)，而無需回表查詢，這就減少了隨機的 IO 操作，減少隨機的 IO 操作，可以有效的提升數(shù)據(jù)庫查詢的性能，是非常重要的數(shù)據(jù)庫優(yōu)化手段之一；
• 索引列越多，通過索引篩選出的數(shù)據(jù)越少。

什么是最左匹配原則？它的生效原則有哪些？

最左匹配原則也叫最左前綴原則，是 MySQL
中的一個重要原則，說的是索引以最左邊的為起點任何連續(xù)的索引都能匹配上，當遇到范圍查詢（>、<、between、like）就會停止匹配。
生效原則來看以下示例，比如表中有一個聯(lián)合索引字段 index(a,b,c)：

• where a=1 只使用了索引 a；
• where a=1 and b=2 只使用了索引 a,b；
• where a=1 and b=2 and c=3 使用a,b,c；
• where b=1 or where c=1 不使用索引；
• where a=1 and c=3 只使用了索引 a；
• where a=3 and b like 'xx%' and c=3 只使用了索引 a,b。

列值為 NULL 時，查詢會使用到索引嗎？

在 MySQL 5.6 以上的 InnoDB 存儲引擎會正常觸發(fā)索引。但為了兼容低版本的 MySQL 和兼容其他數(shù)據(jù)庫存儲引擎，不建議使用 NULL
值來存儲和查詢數(shù)據(jù)，建議設置列為 NOT NULL，并設置一個默認值，比如 0 和空字符串等，如果是 datetime 類型，可以設置成
1970-01-01 00:00:00 這樣的特殊值。

以下語句會走索引么？

select * from t where year(date)>2018;

不會，因為在索引列上涉及到了運算。

能否給手機號的前 6 位創(chuàng)建索引？如何創(chuàng)建？

可以，創(chuàng)建方式有兩種：

• alter table t add index index_phone(phone(6));
• create index index_phone on t(phone(6));

什么是前綴索引？

前綴索引也叫局部索引，比如給身份證的前 10 位添加索引，類似這種給某列部分信息添加索引的方式叫做前綴索引。

為什么要用前綴索引？

前綴索引能有效減小索引文件的大小，讓每個索引頁可以保存更多的索引值，從而提高了索引查詢的速度。但前綴索引也有它的缺點，不能在 order by 或者
group by 中觸發(fā)前綴索引，也不能把它們用于覆蓋索引。

什么情況下適合使用前綴索引？

當字符串本身可能比較長，而且前幾個字符就開始不相同，適合使用前綴索引；相反情況下不適合使用前綴索引，比如，整個字段的長度為 20，索引選擇性為
0.9，而我們對前 10 個字符建立前綴索引其選擇性也只有
0.5，那么我們需要繼續(xù)加大前綴字符的長度，但是這個時候前綴索引的優(yōu)勢已經不明顯，就沒有創(chuàng)建前綴索引的必要了。

什么是頁？

頁是計算機管理存儲器的邏輯塊，硬件及操作系統(tǒng)往往將主存和磁盤存儲區(qū)分割為連續(xù)的大小相等的塊，每個存儲塊稱為一頁。主存和磁盤以頁為單位交換數(shù)據(jù)。數(shù)據(jù)庫系統(tǒng)的設計者巧妙利用了磁盤預讀原理，將一個節(jié)點的大小設為等于一個頁，這樣每個節(jié)點只需要一次磁盤
IO 就可以完全載入。

索引的常見存儲算法有哪些？

• 哈希存儲法：以 key、value 方式存儲，把值存入數(shù)組中使用哈希值確認數(shù)據(jù)的位置，如果發(fā)生哈希沖突，使用鏈表存儲數(shù)據(jù)；
• 有序數(shù)組存儲法：按順序存儲，優(yōu)點是可以使用二分法快速找到數(shù)據(jù)，缺點是更新效率，適合靜態(tài)數(shù)據(jù)存儲；
• 搜索樹：以樹的方式進行存儲，查詢性能好，更新速度快。

InnoDB 為什么要使用 B+ 樹，而不是 B 樹、Hash、紅黑樹或二叉樹？

因為 B 樹、Hash、紅黑樹或二叉樹存在以下問題：

• B 樹：不管葉子節(jié)點還是非葉子節(jié)點，都會保存數(shù)據(jù)，這樣導致在非葉子節(jié)點中能保存的指針數(shù)量變少（有些資料也稱為扇出），指針少的情況下要保存大量數(shù)據(jù)，只能增加樹的高度，導致IO操作變多，查詢性能變低；
• Hash：雖然可以快速定位，但是沒有順序，IO 復雜度高；
• 二叉樹：樹的高度不均勻，不能自平衡，查找效率跟數(shù)據(jù)有關（樹的高度），并且 IO 代價高；
• 紅黑樹：樹的高度隨著數(shù)據(jù)量增加而增加，IO 代價高。

為什么 InnoDB 要使用 B+ 樹來存儲索引？

B+Tree 中的 B 是 Balance，是平衡的意思，它在經典 B Tree 的基礎上進行了優(yōu)化，增加了順序訪問指針，在B+Tree
的每個葉子節(jié)點增加一個指向相鄰葉子節(jié)點的指針，就形成了帶有順序訪問指針的 B+Tree，這樣就提高了區(qū)間訪問性能：如果要查詢 key 為從 18 到 49
的所有數(shù)據(jù)記錄，當找到 18 后，只需順著節(jié)點和指針順序遍歷就可以一次性訪問到所有數(shù)據(jù)節(jié)點，極大提到了區(qū)間查詢效率（無需返回上層父節(jié)點重復遍歷查找減少 IO
操作）。

索引本身也很大，不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲的磁盤上，這樣的話，索引查找過程中就要產生磁盤 IO 消耗，相對于內存存取，IO
存取的消耗要高幾個數(shù)量級，所以索引的結構組織要盡量減少查找過程中磁盤 IO 的存取次數(shù)，從而提升索引效率。 綜合所述，InnDB 只有采取 B+
樹的數(shù)據(jù)結構存儲索引，才能提供數(shù)據(jù)庫整體的操作性能。

唯一索引和普通索引哪個性能更好？

• 對于查詢操作來說：普通索引和唯一索引的性能相近，都是從索引樹中進行查詢；
• 對于更新操作來說：唯一索引要比普通索引執(zhí)行的慢，因為唯一索引需要先將數(shù)據(jù)讀取到內存中，再在內存中進行數(shù)據(jù)的唯一效驗，所以執(zhí)行起來要比普通索引更慢。

優(yōu)化器選擇查詢索引的影響因素有哪些？

優(yōu)化器的目的是使用最小的代價選擇最優(yōu)的執(zhí)行方案，影響優(yōu)化器選擇索引的因素如下：

• 掃描行數(shù)，掃描的行數(shù)越少，執(zhí)行代價就越少，執(zhí)行效率就會越高；
• 是否使用了臨時表；
• 是否排序。

MySQL 是如何判斷索引掃描行數(shù)的多少？

MySQL 的掃描行數(shù)是通過索引統(tǒng)計列（cardinality）大致得到并且判斷的，而索引統(tǒng)計列（cardinality）可以通過查詢命令 show
index 得到，索引掃描行數(shù)的多少就是通過這個值進行判斷的。

MySQL 是如何得到索引基數(shù)的？它準確嗎？

MySQL 的索引基數(shù)并不準確，因為 MySQL 的索引基數(shù)是通過采樣統(tǒng)計得到的，比如 InnoDb 默認會有 N
個數(shù)據(jù)頁，采樣統(tǒng)計會統(tǒng)計這些頁面上的不同值得到一個平均值，然后除以這個索引的頁面數(shù)就得到了這個索引基數(shù)。

MySQL 如何指定查詢的索引？

在 MySQL 中可以使用 force index 強行選擇一個索引，具體查詢語句如下：

select * from t force index(index_t)

在 MySQL 中指定了查詢索引，為什么沒有生效？

我們知道在 MySQL 中使用 force index 可以指定查詢的索引，但并不是一定會生效，原因是 MySQL 會根據(jù)優(yōu)化器自己選擇索引，如果
force index 指定的索引出現(xiàn)在候選索引上，這個時候 MySQL 不會在判斷掃描的行數(shù)的多少直接使用指定的索引，如果沒在候選索引中，即使 force
index 指定了索引也是不會生效的。

以下 or 查詢有什么問題嗎？該如何優(yōu)化？

select * from t where num=10 or num=20;

答：如果使用 or 查詢會使 MySQL 放棄索引而全表掃描，可以改為：

select * from t where num=10 union select * from t where num=20;

以下查詢要如何優(yōu)化？

表中包含索引：

• KEY mid (mid)
• KEY begintime (begintime)
• KEY dg (day,group)

使用以下 SQL 進行查詢：

select f from t where day='2010-12-31' and group=18 and
begintime<'2019-12-31 12:14:28' order by begintime limit 1;

答：此查詢理論上是使用 dg 索引效率更高，通過 explain 可以對比查詢掃描次數(shù)。由于使用了 order by begintime 則使查詢放棄了
dg 索引，而使用 begintime 索引，從側面印證 order by 關鍵字會影響查詢使用索引，這時可以使查詢強制使用索引，改為以下SQL：

select f from t use index(dg) where day='2010-12-31' and group=18 and
begintime< '2019-12-31 12:14:28' order by begintime limit 1;

MySQL 會錯選索引嗎？

MySQL 會錯選索引，比如 k 索引的速度更快，但是 MySQL 并沒有使用而是采用了 v 索引，這種就叫錯選索引，因為索引選擇是 MySQL
的服務層的優(yōu)化器來自動選擇的，但它在復雜情況下也和人寫程序一樣出現(xiàn)缺陷。

如何解決 MySQL 錯選索引的問題？

• 刪除錯選的索引，只留下對的索引；
• 使用 force index 指定索引；
• 修改 SQL 查詢語句引導 MySQL 使用我們期望的索引，比如把 order by b limit 1 改為 order by b,a limit 1 語義是相同的，但 MySQL 查詢的時候會考慮使用 a 鍵上的索引。

如何優(yōu)化身份證的索引？

在中國因為前 6 位代表的是地區(qū)，所以很多人的前六位都是相同的，如果我們使用前綴索引為 6
位的話，性能提升也并不是很明顯，但如果設置的位數(shù)過長，那么占用的磁盤空間也越大，數(shù)據(jù)頁能放下的索引值就越少，搜索效率也越低。針對這種情況優(yōu)化方案有以下兩種：

• 使用身份證倒序存儲，這樣設置前六位的意義就很大了；
• 使用 hash 值，新創(chuàng)建一個字段用于存儲身份證的 hash 值。