## 1、索引類型
**UNIQUE唯一索引**
不可以出現相同的值,可以有NULL值
**INDEX普通索引**
允許出現相同的索引內容
**PRIMARY KEY主鍵索引**
不允許出現相同的值,且不能為NULL值,一個表只能有一個primary_key索引
**fulltext index 全文索引**
上述三種索引都是針對列的值發揮作用,但全文索引,可以針對值中的某個單詞,比如一篇文章中的某個詞,**然而并沒有什么卵用,因為只有myisam以及英文支持,并且效率讓人不敢恭維,但是可以用coreseek和xunsearch等第三方應用來完成這個需求**
## 2、如何選擇適合的列建立索引
* 維度高的列創建索引
* 數據列中**不重復值**出現的個數,這個數值越高,維度就越高,可選擇性越多。
* 要為維度高的列創建索引,如性別和年齡,那年齡的維度高于性別
* 性別這樣的列不適合創建索引,因為維度過低。
* 維度高的列放在聯合索引的前面
* 在 **where** ,**group by** ,**order by** ,**on** 中出現的列使用索引;
* 當一個索引包含了查詢中的所有列,稱該索引為`覆蓋索引`
* 對**較小的數據列**使用索引,這樣會使索引文件更小,同時內存中也可以裝載更多的索引鍵
* 為較長的字符串使用**前綴索引**;
* 不要過多創建索引,除了增加額外的磁盤空間外,對于DML操作的速度影響很大,因為其每增刪改一次就得從新建立索引
* 使用**組合索引**,可以減少文件索引大小,在使用時速度要優于多個`單列索引`
`注意:組合索引與前綴索引,這兩種稱呼是對建立索引技巧的一種稱呼,并非索引的類型;`
## 3、組合索引
MySQL單列索引和組合索引究竟有何區別呢?
為了形象地對比兩者,先建一個表:
```
CREATE TABLE `myIndex` (
`i_testID` INT NOT NULL AUTO_INCREMENT,
`vc_Name` VARCHAR(50) NOT NULL,
`vc_City` VARCHAR(50) NOT NULL,
`i_Age` INT NOT NULL,
`i_SchoolID` INT NOT NULL,
PRIMARY KEY (`i_testID`)
);
```
假設表內已有1000條數據,在這 10000 條記錄里面 7 上 8 下地分布了 5 條 vc_Name="erquan" 的記錄,只不過 city,age,school 的組合各不相同。
來看這條 T-SQL:
```
SELECT `i_testID` FROM `myIndex` WHERE `vc_Name`='erquan' AND `vc_City`='鄭州' AND `i_Age`=25; -- 關聯搜索;
```
首先考慮建MySQL單列索引:
>在 vc_Name 列上建立了索引。執行 T-SQL 時,MYSQL 很快將目標鎖定在了 vc_Name=erquan 的 5 條記錄上,取出來放到一中間結果集。在這個結果集里,先排除掉 vc_City 不等于"鄭州"的記錄,再排除 i_Age 不等于 25 的記錄,最后篩選出唯一的符合條件的記錄。
>
>雖然在 vc_Name 上建立了索引,查詢時MYSQL不用掃描整張表,效率有所提高,但離我們的要求還有一定的距離。同樣的,在 vc_City 和 i_Age 分別建立的MySQL單列索引的效率相似。
為了進一步榨取 MySQL 的效率,就要考慮建立組合索引。就是將 vc_Name,vc_City,i_Age 建到一個索引里:
```
ALTER TABLE `myIndex` ADD INDEX `name_city_age` (vc_Name(10),vc_City,i_Age);
```
>建表時,vc_Name 長度為 50,這里為什么用 10 呢?這就是下文要說到的**前綴索引**,因為一般情況下名字的長度不會超過 10,這樣會加速索引查詢速度,還會減少索引文件的大小,提高 INSERT 的更新速度。
執行 T-SQL 時,MySQL 無須掃描任何記錄就到找到唯一的記錄!!
如果分別在 vc_Name,vc_City,i_Age 上建立單列索引,讓該表有 3 個單列索引,查詢時和上述的組合索引效率一樣嗎?答案是大不一樣,遠遠低于我們的組合索引。雖然此時有了三個索引,**但 MySQL 只能用到其中的那個它認為似乎是最有效率的單列索引,另外兩個是用不到的,也就是說還是一個全表掃描的過程。**
建立這樣的組合索引,其實是相當于分別建立了
```
vc_Name,vc_City,i_Age
vc_Name,vc_City
vc_Name
```
這樣的三個組合索引!為什么沒有 vc_City,i_Age 等這樣的組合索引呢?這是因為 mysql 組合索引"**最左前綴**"的結果。簡單的理解就是只從最左面的開始組合。并不是只要包含這三列的查詢都會用到該組合索引,下面的幾個 T-SQL 會用到:
```
SELECT * FROM myIndex WHREE vc_Name="erquan"
SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="鄭州"
SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="鄭州" AND i_Age = 20
SELECT * FROM myIndex WHREE vc_City="鄭州" AND vc_Name="erquan" AND i_Age = 20 //mysql會自動優化成上面那條語句的樣子
SELECT * FROM myIndex WHREE vc_Name="erquan" OR vc_City="鄭州" //只有vc_Name用到索引
```
而下面幾個則不會用到:
```
SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="鄭州"
SELECT * FROM myIndex WHREE vc_City="鄭州"
```
**也就是,name_city_age (vc_Name(10),vc_City,i_Age) 從左到右進行索引,如果沒有左前索引Mysql不執行索引查詢**
### 3.1、單列索引的一些案例:
```
SELECT * FROM app WHERE id > 10 and id < 20 ; //使用到了范圍索引
//如果有id和name兩個單列索引,則此查詢會用到兩個索引
explain SELECT * FROM rcloud_app WHERE id = 10 or name = '20';
//如果有id和name兩個單列索引;1、有匹配結果,會用到id索引;2、無匹配結果不會用到索引。
explain SELECT * FROM rcloud_app WHERE id = 10 AND name = 'aa';
//只有id一個索引,不會用到索引,會全表掃描
explain SELECT * FROM rcloud_app WHERE id = 10 or name = '44';
//只有id一個索引,會用到id索引
explain SELECT * FROM rcloud_app WHERE id = 10 and name = '44';
```
## 4、前綴索引
如果索引列長度過長,這種列索引時將會產生很大的索引文件,不便于操作,可以使用前綴索引方式進行索引
前綴索引應該控制在一個合適的點,控制在0.31黃金值即可(大于這個值就可以創建)
```
SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; -- 這個值大于0.31就可以創建前綴索引,Distinct去重復
ALTER TABLE `user` ADD INDEX `uname`(title(10)); -- 增加前綴索引SQL,將人名的索引建立在10,這樣可以減少索引文件大小,加快索引查詢速度
```
## 5、什么樣的sql不走索引
要盡量避免這些不走索引的sql
```
SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不會使用索引,因為所有索引列參與了計算
SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不會使用索引,因為使用了函數運算,原理與上面相同
SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引
-- 正則表達式不使用索引,這應該很好理解,所以為什么在SQL中很難看到regexp關鍵字的原因
-- 字符串與數字比較不使用索引;
CREATE TABLE `a` (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引
select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果條件中有or,即使其中有條件帶索引也不會使用。換言之,就是要求使用的所有字段,都必須建立索引, 我們建議大家盡量避免使用or 關鍵字
-- 如果mysql估計使用全表掃描要比使用索引快,則不使用索引
```
## 6、多表關聯時的索引效率

從上圖可以看出,所有表的type為all,表示全表索引;也就是6_6_6,共遍歷查詢了216次;
除第一張表示全表索引(必須的,要以此關聯其他表),其余的為range(索引區間獲得),也就是6+1+1+1,共遍歷查詢9次即可;
所以我們建議在多表join的時候盡量少join幾張表,因為一不小心就是一個笛卡爾乘積的恐怖掃描,另外,我們還建議盡量使用left join,以少關聯多.因為使用join 的話,第一張表是必須的全掃描的,以少關聯多就可以減少這個掃描次數.
## 7、索引的弊端
不要盲目的創建索引,只為查詢操作頻繁的列創建索引,創建索引會使查詢操作變得更加快速,但是會降低增加、刪除、更新操作的速度,因為執行這些操作的同時會對索引文件進行重新排序或更新;
但是,在互聯網應用中,查詢的語句遠遠大于DML的語句,甚至可以占到80%~90%,所以也不要太在意,只是在大數據導入時,可以先刪除索引,再批量插入數據,最后再添加索引;
## 8、索引優化SQL的方法
* `重復索引`是指相同的列以相同的順序建立的同類型的索引,如下表中 primary key 和 ID 列上的索引就是重復索引。

* `冗余索引`是指多個索引的前綴列相同,或是在聯合索引中包含了主鍵的索引,下面這個列子中 key\(name,id\) 就是一個`冗余索引`。

* 使用pt-duplicate-key-checker工具檢查重復及冗余索引
```sql
pt-duplicate-key-checker -uroot -p'' -h 127.0.0.1
```
## 9、索引維護的方法
* 刪除不用的索引
目前 MySQL 中還沒有記錄索引的使用情況,但是在 PerconMySQL 和 MariaDB 中可以通過 INDEX_STATISTICS 表來查看那些索引未使用,但在 MySQL 中目前只能通過慢查詢日志配合 pt-index-usage 工具來進行索引使用情況的分析.
```sql
pt-index-usage -uroot -p'' mysql-slow.log
```