1. 什麼是索引
一種能幫助mysql提高查詢效率的資料結構：索引資料結構
索引優點：
大大提高資料查詢速度
索引缺點：
維護索引需要耗費資料庫資源
索引要占用磁盤空間
當對表的資料進行增删改的時候，因為要維護索引，是以速度收到影響
結合索引的優缺點，得出結論：資料庫表并不是索引加的越多越好，而是僅為那些常用的搜尋字段建立索引效果才是最佳的！
2. 索引的分類
主鍵索引：PRIMARY KEY
設定為逐漸後，資料庫自動建立索引，innodb為聚簇索引，主鍵索引列值不能有空(Null)
單值索引：又叫單列索引、普通索引
即，一個索引隻包含單個列，一個表可以有多個單列索引
唯一索引：
索引列的值必須唯一，但允許有空值(Null)，但隻允許有一個空值(Null)
複合索引：
即，一個索引可以包含多個列，多個列共同構成一個複合索引！
eg: SELECT id (name age) INDEX WHERE name AND age;
全文索引：Full Text （MySQL5.7之前，隻有MYISAM存儲引擎支援全文索引）
全文索引類型為FULLTEXT，在定義索引的列上支援值的全文查找，允許在這些索引列中插入重複值和空值。全文索引可以在Char 、Varchar 上建立。
3. 索引的基本操作
3.1 主鍵索引建立
-- 建表語句：建表時，設定主鍵，自動建立主鍵索引
CREATE TABLE t_user (
id VARCHAR(20) PRIMARY KEY,
name VARCHAR(20)
);
-- 檢視索引
SHOW INDEX FROM t_user;
3.2 單列索引建立(普通索引/單值索引)
-- 建表時建立單列索引：
-- 這種方式建立單列索引，其名稱預設為字段名稱：name
CREATE TABLE t_user (
id VARCHAR(20) PRIMARY KEY,
name VARCHAR(20)，
KEY(name)
);
-- 建表後建立單列索引：
-- 索引名稱為：name_index 格式---> 字段名稱_index
CREATE INDEX name_index ON t_user(name)
-- 删除單列索引
DROPINDEX 索引名稱 ON 表名
3.3 唯一索引建立
-- 建表時建立唯一索引：
CREATE TABLE t_user2 (
id VARCHAR(20) PRIMARY KEY,
name VARCHAR(20),
UNIQUE(name)
);
-- 建表後建立唯一索引：
CREATE UNIQUE INDEX name_index ON t_user2(name);
3.4 複合索引建立
-- 建表時建立複合索引：
CREATE TABLE t_user3 (
id VARCHAR(20) PRIMARY KEY,
name VARCHAR(20),
age INT,
KEY(name,age)
);
-- 建表後建立複合索引：
CREATE INDEX name_age_index ON t_user3(name,age);
-- 複合索引查詢的2個原則
-- 1.最左字首原則
-- eg: 建立複合索引時，字段的順序為 name,age,birthday
-- 在查詢時能利用上索引的查詢條件為：
SELECT * FROM t_user3 WHERE name = ?
SELECT * FROM t_user3 WHERE name = ? AND age = ?
SELECT * FROM t_user3 WHERE name = ? AND birthday = ?
SELECT * FROM t_user3 WHERE name = ? AND age = ? AND birthday = ?
-- 而其他順序則不滿足最左字首原則：
... WHERE name = ? AND birthday = ? AND age = ? -- 不滿足最左字首原則
... WHERE name = ? AND birthday = ? -- 不滿足最左字首原則
... WHERE birthday = ? AND age = ? AND name = ? -- 不滿足最左字首原則
... WHERE age = ? AND birthday = ? -- 不滿足最左字首原則
-- 2.MySQL 引擎在執行查詢時，為了更好地利用索引，在查詢過程中會動态調整查詢字段的順序！
-- 這時候再來看上面不滿足最左字首原則的四種情況：
-- 不滿足最左字首原則，但經過動态調整順序後，變為：name age birthday 可以利用複合索引！
... WHERE name = ? AND birthday = ? AND age = ?
-- 不滿足最左字首原則，也不能動态調整（因為缺少age字段），不可以利用複合索引！
... WHERE name = ? AND birthday = ?
-- 不滿足最左字首原則，但經過動态調整順序後，變為：name age birthday 可以利用複合索引！
... WHERE birthday = ? AND age = ? AND name = ?
-- 不滿足最左字首原則，也不能動态調整（因為缺少name字段），不可以利用複合索引！
... WHERE age = ? AND birthday = ?
4. MySQL索引的資料結構(B+Tree)
-- 建表：
CREATE TABLE t_emp(
id INT PRIMARY KEY,
name VARCHAR(20),
age INT
);
-- 插入資料：插入時，主鍵無序
INSERT INTO t_emp VALUES(5,'d',22);
INSERT INTO t_emp VALUES(6,'d',22);
INSERT INTO t_emp VALUES(7,'3',21);
INSERT INTO t_emp VALUES(1,'a',23);
INSERT INTO t_emp VALUES(2,'b',26);
INSERT INTO t_emp VALUES(3,'c',27);
INSERT INTO t_emp VALUES(4,'a',32);
INSERT INTO t_emp VALUES(8,'f',53);
INSERT INTO t_emp VALUES(9,'b',13);
-- 查詢：自動排序，有序展示（因為主鍵是有主鍵索引的，是以會自動排序）
問題：為什麼資料插入時，未按照主鍵順序，而查詢時卻是有序的呢
？
- 原因：MySQL底層為主鍵自動建立索引，一旦建立了索引，就會進行排序！
- 實際上這些資料在MySQL底層的真正存儲結構變成了下面這種方式：
問題：為什麼要排序呢？
因為排序之後查詢效率就快了，比如查詢 id = 3 的資料，隻需要按照順序去找即可，而如果不排序，就如同大海撈針，假如100W條資料，可能有時候需要随機查詢100W次才找到這個資料，也可能運氣好上來第1次就查詢到了該資料，不确定性太高！
4.1 原理分析圖
上圖這種分層樹結構查詢效率較高，因為如果我需要查詢 id=4的資料，隻需要在頁目錄中比對，大于3且小于5，則去3對應的page=2中查找資料，這樣就不需要從第1頁開始檢索資料了，大大提高了效率！
從上圖可得出，在隻有2層的結構下，1page 可以存儲記錄總數為 1365 * 455 ≈ 62萬條，而如果再加1層結構，來存儲page層分頁目錄資料的分頁層PAGE的話，那麼1PAGE可以存儲總page數為：1365 * 1365 ≈ 186萬條page，而1PAGE存儲的總記錄數為 1365 * 1365 * 455 ≈ 8.5 億條。是以，我們平時使用的話，2層結構就已經足夠了！實際上1個頁存儲的總資料樹可能大于理論估計的，因為我們配置設定name字段的VARCHAR(20)占20個位元組，而實際上可能存儲的name資料并沒有20個位元組，可能更小！
三層結構執行個體如圖：
4.2 B+樹結構分析
上圖4.1 原理分析圖中這種索引結構稱之為B+樹資料結構，那麼什麼是B+樹呢？B樹和B+樹差別是什麼呢?
詳情參考文章：
https://www.cnblogs.com/lianzhilei/p/11250589.html問題4.2.1 為什麼InnoDB底層使用B+樹做索引而不用B樹？
B樹結構圖：
從上面的B樹結構圖中分析得出，B樹每個節點中不僅包含資料的key，還有data資料。而每個頁的存儲空間是有限的，如果data資料較大時，講會導緻每個節點(即一個頁16KB)能存儲的key的數量較少，當存儲資料量很大時，會造成B樹的深度較大，增大查詢時的磁盤讀取I/O次數，進而影響查詢效率。(樹的深度影響I/O讀取次數)
在上一小節的B+樹結構圖分析中，所有資料記錄都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上隻能存儲key值資訊，這樣可以大大增加每個節點(即一個頁16KB)能存儲的key的數量，進而可以降低樹的高度，進而減少磁盤讀取I/O次數，提高查詢效率
是以B樹和B+樹的差別就在于：
B+樹隻有葉子節點存儲資料記錄
B+樹非葉子節點隻存儲鍵值資訊（B樹的非葉子也存資料記錄）
所有節點直接都有一個鍊指針
InnoDB存儲引擎中，頁的大小為16KB，一般表的主鍵類型為INT(占用4個位元組) 或 BIGINT(占用8個位元組)，指針類型也一般占4或8個位元組，也就是說，一個頁(B+樹中的一個節點)中大概可以存儲16KB/(8B+8B)=1000個鍵值(隻是估計值，友善計算而已)。也就是說，一個深度為3的B+樹索引可以維護10^3 * 10^3 * 10^3 = 10億條記錄。
實際情況中每個節點可能不能填充滿，是以在資料庫中，B+樹的高度一般是24層**。**MySQL的InnoDB存儲引擎在設計時是将根節點常駐在記憶體中（不需要動磁盤I/O）**的，也就是說**查找某個鍵值的行記錄最多隻需要13次I/O操作！（每查詢一層都需要動用一次磁盤I/O）
5. 聚簇索引和非聚簇索引
5.1 聚簇索引和非聚簇索引分析
在表中，聚簇索引實際上就是指的是主鍵索引！如果表中沒有主鍵的話，則MySQL會根據該表生成一個RoleID，拿這個RoleId當做聚簇索引！
聚簇索引：将資料存儲與索引放到一起，索引結構的葉子節點儲存了每行的資料。例如：4.1小結分析圖中的data層一個機關就是聚簇索引存儲資料的例子，主鍵id 字段就是聚簇索引，4.1小結分析圖就是基于主鍵索引(聚簇索引)構成的B+樹結構！聚簇索引不一定是主鍵索引，但是主鍵索引肯定是聚簇索引！
非聚簇索引：
将資料與索引分開存儲
，索引結構的葉子節點指向了資料對應的位置(聚簇索引的值)！非聚簇索引檢索資料是在自己的 “樹” 上進行查找，例如我們根據表中的非聚簇索引name字段去查找資料時，流程如下圖：
再看一張比較正規的分析圖：
注意：在InnoDB中，在聚簇索引之上建立的索引稱之為輔助索引，例如：複合索引、單列索引、唯一索引。一個表中隻能有1個聚簇索引，而其他索引都是輔助索引！輔助索引的葉子節點存儲的不再是行的實體位置，而是主鍵的值，輔助索引通路資料總是需要二次查找的！
**問題5.1.1 **：為什麼非聚簇索引(name字段的單列索引)構成的樹，其葉子節點存儲聚簇索引(主鍵id)，而不直接存儲行資料的實體位址呢？
換個方式問：非聚簇索引檢索資料時，檢索一次本樹再去聚簇索引樹中檢索一次，這樣二次檢索樹結構，那麼為什麼不直接在非聚簇索引樹葉子節點中存放行資料實體位址，這樣隻需要檢索一次樹結構就拿到行資料呢？
這裡畫個圖友善了解一些：
從上圖得出，在做新增資料時，因為底層是需要基于主鍵索引進行排序的，那麼就可能導緻原來某些資料對應的實體位址發生了變化，而這時候由于我們的非聚簇索引樹的葉子節點直接存儲了資料的實體位址，是以為了保證能擷取到資料，還需要同時對非聚簇索引樹葉子節點的位址進行一遍更新修改！
同理，如果我們不做插入主鍵id為4這行記錄的操作，而是将其删除的話，這個流程可以自己思考一下！
也就是說：之是以不在非聚簇索引樹的葉子節點直接存放行資料的實體位址，是因為，存儲資料的實體位址會随着資料庫表的CRUD操作而不斷變更，為了保證能擷取到資料，這時必須要對非聚簇索引樹相關葉子節點的位址進行一遍修改！而存主鍵，主鍵不會随着CRUD操作發生變化，甯願多查一次樹，也不要再修改一次樹的結構！
5.2 MySQL兩種引擎中的(非)聚簇索引
InnoDB中：
InnoDB中使用的是聚簇索引，将主鍵組織到一顆B+樹中，而行資料就存儲在該B+樹的葉子節點上，若使用WHERE id = 4 這樣的條件查找主鍵，則按照B+樹的檢索算法即可查找對應的葉子節點，之後獲得對應的行資料！
若對使用單列索引(非聚簇索引)的name字段進行搜尋，則需要執行2個步驟：
第一步：在輔助索引B+樹中檢索name，到達其對應的葉子節點後獲得該字段對應行記錄的主鍵id！
第二步：使用主鍵id在主索引B+樹中再次執行一次樹的檢索，最終到達對應的葉子節點并擷取到行記錄資料！
聚簇索引預設是主鍵，如果表中沒有定義主鍵，InnoDB會選擇一個唯一且非空的索引代替主鍵作為聚簇索引。而如果也沒有這樣的唯一非空索引，那麼InnoDB就會隐式定義一個主鍵（類似于Oracle中的RowId）來做為聚簇索引。
如果已經設定了聚簇索引又希望再單獨設定聚簇索引，則必須先删除主鍵，然後添加我們想要的聚簇索引，最後再恢複主鍵即可！
MYISAM中：
MYISAM使用的是非聚簇索引，非聚簇索引的兩顆B+樹看上去沒有什麼不同，節點的結構完全一緻，隻是存儲的内容不同，主鍵索引B+樹的節點存儲了主鍵，輔助索引B+樹存儲量輔助鍵。
表資料存儲在獨立的地方，這兩顆B+樹的葉子節點都使用一個位址指針指向真正的表資料，對于表資料來說，這兩個鍵沒有任何差别。
由于索引樹是獨立的，通過輔助鍵檢索無需再次檢索主鍵索引樹！
5.3 聚簇索引和非聚簇索引的優/劣勢
問題：5.3.1 使用聚簇索引的優勢
問題：每次使用輔助索引檢索都需要經過2次B+樹查找，看上去聚簇索引的效率明顯要低于非聚簇索引，那麼聚簇索引的優勢何在呢？
-- 1.由于行資料和聚簇索引樹的葉子節點存儲在一起，同一頁中會有多條行資料，首次通路資料頁中某條行記錄時，會把該資料頁資料加載到Buffer(緩存器)中，當再次通路該資料頁中其他記錄時，不必通路磁盤而直接在記憶體中完成通路。
-- 注：主鍵id和行資料一起被載入記憶體，找到對應的葉子節點就可以将行資料傳回了，如果按照主鍵id來組織資料，擷取資料效率更快！
-- 2.輔助索引的葉子節點，存儲主鍵的值，而不是行資料的存放位址。這樣做的好處是，因為葉子節點存放的是主鍵值，其占據的存儲空間小于存放行資料實體位址的儲存空間
問題：5.3.2 使用聚簇索引需要注意什麼？
-- 當使用主鍵為聚簇索引時，而不要使用UUID方式，因為UUID的值太過離散，不适合排序，導緻索引樹調整複雜度增加，消耗更多時間和資源。
-- 建議主鍵最好使用INT/BIGINT類型，且為自增，這樣便于排序且預設會在索引樹的末尾增加主鍵值，對索引樹的結構影響最小(下面主鍵自增的問題會解釋原因)。而且主鍵占用的存儲空間越大，輔助索引中儲存的主鍵值也會跟着增大，占用空間且影響IO操作讀取資料！
問題：5.3.3 為什麼主鍵通常建議使用自增id？
-- 聚簇索引樹存放資料的實體位址(xx1,xx2,xx3,xxx5)與索引順序(1,2,3,5)是一緻的，即：
-- 1.隻要索引是相鄰的，那麼在磁盤上索引對應的行資料存放位址也是相鄰的。
-- 2.如果主鍵是自增，那麼當插入新資料時，隻需要按照順序在磁盤上開辟新實體位址存儲新增行資料即可。
-- 3.而如果不是主鍵自增，那麼當新插入資料後，會對索引進行重新排序(重新調整B+樹結構)，磁盤上的實體存儲位址也需要重新配置設定要存儲的行資料！
問題：5.3.4 什麼情況下無法利用索引呢？
-- 1. 查詢語句中使用LIKE關鍵字：（這種情況主要是針對于單列索引）
-- 在使用LIKE關鍵字查詢時，如果比對字元串的第一個字元為'%'，則索引不會被使用，而'%'不在最左邊，而是在右邊，則索引會被使用到！
-- eg:
SELECT * FROM t_user WHERE name LIKE 'xx%' -- 可以利用上索引,這種情況下可以拿xx到索引樹上去比對
SELECT * FROM t_user WHERE name LIKE '%xx%' -- 不可以利用上索引
SELECT * FROM t_user WHERE name LIKE '%xx' -- 不可以利用上索引
-- 2. 查詢語句中使用多列索引：（這種情況主要是針對于聚合索引）
-- 多索引是在表的多個字段建立索引，隻有查詢條件中使用了這些字段中的第一個字段，索引才會被使用。即：最左字首原則，詳情檢視3.4小結聚合索引中的介紹！
-- 3. 查詢語句中使用OR關鍵字：
-- 查詢條件中有OR關鍵字時，如果OR前後的兩個條件列都具有索引，則查詢中索引将被使用，而如果OR前後有一個或2個列不具有索引，那麼查詢中索引将不被使用到！
6. 什麼是限制以及分類
限制:
作用：是為了保證資料的完整性而實作的摘自一套機制，即(限制是針對表中資料記錄的)
MySQL中的限制：
非空限制：NOT NULL 保證某列資料不能存儲NULL 值;
唯一限制：UNIQUE(字段名) 保證所限制的字段，資料必須是唯一的，允許資料是空值(Null)，但隻允許有一個空值(Null)；
主鍵限制：PRIMARY KEY(字段名) 主鍵限制= 非空限制 + 唯一限制 保證某列資料不能為空且唯一；
外鍵限制：FOREIGN KEY(字段名) 保證一個表中某個字段的資料比對另一個表中的某個字段，可以建立表與表直接的聯系；
自增限制：AUTO_INCREMENT 保證表中新插入資料時，某個字段資料可以依次遞增；
預設限制：DEFALUT 保證表中新插入資料時，如果某個字段未被指派，則會有預設初始化值；
檢查性限制：CHECK 保證列中的資料必須符合指定的條件；
示例：
create table member(
id int(10),
phone int(15) unsigned zerofill,
name varchar(30) not null,
constraint uk_name unique(name),
constraint pk_id primary key (id),
constraint fk_dept_id foreign key (dept_id，字段2)
references dept(主表1)(dept_id)
);
7. MySQL索引和限制的差別
索引的作用：索引用于快速定位特定資料，提高查詢效率的。
限制的作用：限制是為了保證資料的完整性，即限制是針對表中資料記錄的。
總結：限制是為了保證表資料的完整性，索引是為了提高查詢效率，兩者作用不一樣！種類也不太一樣！
MySQL索引相關面試題
1.MySQL索引分類，并對比差別？
答案參考：第2小結
2.複合索引查詢時，字段排列的先後順序與建立索引時不同，能否成功利用索引查詢?
考察點：複合索引的最左字首原則
-- 假設構成複合索引的字段為 name,age,birthday
-- 則下面那種情況可以使用成功利用複合索引查詢？
... WHERE name = ? -- 可以利用
... WHERE name = ? AND age = ? -- 可以利用
... WHERE name = ? AND birthday = ? -- 可以利用
... WHERE name = ? AND age = ? AND birthday = ? -- 可以利用
... WHERE name = ? AND birthday = ? AND age = ? -- 不滿足最左字首原則，但經過動态調整後可以利用
... WHERE birthday = ? AND age = ? AND name = ? -- 不滿足最左字首原則，但經過動态調整後可以利用
... WHERE age = ? AND birthday = ? -- 不滿足最左字首原則，不能動态調整，不能利用複合索引
3.MySQL索引的資料結構是什麼？
MySQL索引資料結構: B + Tree
B+樹，聚簇索引~
4.MySQL中索引和限制的差別以及各自種類？
MySQL索引與限制
5.為什麼InnoDB底層使用B+樹做索引而不用B樹？
答案參考: 4.2小結B+樹結構分析中的問題4.2.1
6.什麼是聚簇索引和非聚簇索引
答案參考：5.1小結聚簇索引和非聚簇索引分析
7.為什麼非聚簇索引構成的樹的葉子節點存儲聚簇索引(或主鍵)，而不直接存儲資料的實體位址呢？
答案參考：5.1小結的問題5.1.1
8.使用聚簇索引需要注意什麼？
答案參考：5.3小結的問題5.3.2
9.為什麼主鍵通常建議使用自增id？
答案參考：5.3小結的問題5.3.3
10.什麼情況下無法利用索引呢？
答案參考：5.3小結的問題5.3.4
11.聚簇索引相對于非聚簇索引的優勢是什麼？
答案參考：5.3小結的問題5.3.1