什么是索引几蜻？

索引是一種能幫助 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 子句中已經(jīng)使用了索引的話用踩，那么 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ù)內(nèi)置的 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)勢已經(jīng)不明顯，就沒有創(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处窥，是平衡的意思嘱吗，它在經(jīng)典 B Tree 的基礎上進行了優(yōu)化，增加了順序訪問指針滔驾，在B+Tree
的每個葉子節(jié)點增加一個指向相鄰葉子節(jié)點的指針谒麦，就形成了帶有順序訪問指針的 B+Tree，這樣就提高了區(qū)間訪問性能：如果要查詢 key 為從 18 到 49
的所有數(shù)據(jù)記錄哆致，當找到 18 后绕德，只需順著節(jié)點和指針順序遍歷就可以一次性訪問到所有數(shù)據(jù)節(jié)點，極大提到了區(qū)間查詢效率（無需返回上層父節(jié)點重復遍歷查找減少 IO
操作）摊阀。

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

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

• 對于查詢操作來說：普通索引和唯一索引的性能相近趴荸，都是從索引樹中進行查詢；
• 對于更新操作來說：唯一索引要比普通索引執(zhí)行的慢宦焦，因為唯一索引需要先將數(shù)據(jù)讀取到內(nèi)存中发钝，再在內(nèi)存中進行數(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 索引吨枉，從側(cè)面印證 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 值丈屹。