一、索引的本質(zhì)

• 索引：在數(shù)據(jù)庫管理系統(tǒng)中，從海量數(shù)據(jù)中高效獲取數(shù)據(jù)的一種數(shù)據(jù)結(jié)構(gòu)，使用索引可以減少數(shù)據(jù)庫掃描的行數(shù)，從而提高數(shù)據(jù)檢索效率。
• 索引的價值是快速定位。
• 在 MySQL 中采用的是 B+ 樹，B+ 樹的非葉子節(jié)點不會存儲數(shù)據(jù)，每次只有訪問到葉子節(jié)點才能找到對應的數(shù)據(jù)，因此B+ 樹在查詢性能上更穩(wěn)定，在磁盤頁大小相同的情況下，樹的構(gòu)造更加矮胖，所需要進行的磁盤 I/O 次數(shù)更少。

二、索引的主要種類

按照功能邏輯劃分

1. 普通索引：基礎的索引，沒有任何約束，主要用于提高查詢效率。
2. 唯一索引：在普通索引的基礎上增加了數(shù)據(jù)唯一性的約束，在一張數(shù)據(jù)表里可以有多個唯一索引。
3. 主鍵索引：唯一索引的基礎上增加了不為空的約束，一張表里最多只有一個主鍵索引。這是由主鍵索引的物理實現(xiàn)方式?jīng)Q定的，因為數(shù)據(jù)存儲在文件中只能按照一種順序進行存儲。
4. 全文索引：用的不多，MySQL 自帶的全文索引只支持英文。我們通常可以采用專門的全文搜索引擎，比如 ES(ElasticSearch) 和 Solr。

• 效率：主鍵索引>唯一索引>普通索引

按照物理實現(xiàn)劃分

1. 聚集索引：即主鍵索引，一張表里只有一個聚集索引。
2. 聚集索引：除聚集索引以外的索引，在數(shù)據(jù)庫系統(tǒng)會有單獨的存儲空間存放，這些索引項是按照順序存儲的，但索引項指向的內(nèi)容是隨機存儲的。也稱為二級索引或者輔助索引。

按照字段個數(shù)劃分

1. 單一索引：索引列為一列時為單一索引。
2. 聯(lián)合索引：多個列組合在一起創(chuàng)建的索引叫做聯(lián)合索引。

聯(lián)合索引的最左原則

• 按照最左優(yōu)先的方式進行索引的匹配。
• 索引是一種順序結(jié)構(gòu)，我們按照什么順序創(chuàng)建索引，就只能按照這個順序使用索引。
• 使用聯(lián)合索引時，若不包含第一個索引字段，則聯(lián)合索引無效，聯(lián)合索引的字段中有使用OR時聯(lián)合索引失效。

三、索引的使用前提

• 除了聚集索引（主鍵索引）外，其他的索引都是有自己的物理存儲空間的，而且索引中只存儲數(shù)據(jù)位置并不存儲數(shù)據(jù)內(nèi)容，數(shù)據(jù)庫在使用索引檢索數(shù)據(jù)時，需要先去加載索引數(shù)據(jù)到內(nèi)存，然后再根據(jù)索引的地址去加載對應的數(shù)據(jù)行。所以當數(shù)據(jù)量較小時，不需要建立索引，一般數(shù)據(jù)量大于1000才需要建立索引。
• 當數(shù)據(jù)重復率較高時，不需要建立索引，一般數(shù)據(jù)重復率小于10%才需要建立索引。

四、數(shù)據(jù)調(diào)優(yōu)的方法

1、優(yōu)化邏輯查詢

• 邏輯查詢優(yōu)化就是通過改變 SQL 語句的內(nèi)容讓 SQL 執(zhí)行效率更高效，采用的方式是對 SQL 語句進行等價變換，對查詢進行重寫。
• SQL 的查詢重寫包括了子查詢優(yōu)化、等價謂詞重寫、視圖重寫、條件簡化、連接消除和嵌套連接消除等。
• 使用EXISTS 子查詢和 IN 子查詢的時候，小表驅(qū)動大表用EXISTS，大表驅(qū)動小表用IN。
• 任何地方都不要使用 select * from t

2、優(yōu)化物理查詢

• 對查詢進行優(yōu)化，應盡量避免全表掃描，首先應考慮在where、group by、order by 涉及的列上建立索引。
• 一般數(shù)據(jù)量少于1000不需要建立索引，但是被其他表鏈接，可以適量進行添加索引。
• 使用聯(lián)合索引時，第一個索引字段必須要包含，否則無效，另外要注意聯(lián)合索引的順序，要按照MySQL的執(zhí)行順序制定對應的順序索引。MySQL的執(zhí)行順序如下：

FROM  > JOIN > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT

• 在SQL語句之前加上EXPLAIN，可以查看SQL語句執(zhí)行過程，通過【key】使用了哪些索引，【rows】掃描的行數(shù)來進行調(diào)試。

• 要注意多個索引對索引使用的影響。索引不是越多越好，因為每個索引都需要存儲空間，索引多也就意味著需要更多的存儲空間。此外，過多的索引也會導致優(yōu)化器在進行評估的時候增加了篩選出索引的計算時間，影響評估的效率。
• 刪除數(shù)據(jù)重復度高的索引，使用SHOW INDEX命令，刪除結(jié)果集中基數(shù)（cardinality）小于1000的索引。

SHOW INDEX FROM 表名

• 索引字段盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。
• 盡可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長字段存儲空間小，可以節(jié)省存儲空間，其次對于查詢來說，在一個相對較小的字段內(nèi)搜索效率顯然要高些。

五、索引失效的幾種情況

• WHERE 子句中對索引字段進行了表達式的計算和操作
• WHERE 子句中對索引字段進行 null 值判斷
• WHERE 子句中對索引字段使用!=或<>操作符
• WHERE 子句中對索引字段使用函數(shù)，例：find_in_set
• WHERE 子句中對索引字段使用like，例：‘%李%’
• WHERE 子句中對索引字段使用參數(shù)，例：num=@num
• 聯(lián)合索引的字段中有使用OR時聯(lián)合索引無效
• 聯(lián)合索引的字段中第一個索引字段沒有使用時聯(lián)合索引無效