一、索引使用
1.驗證索引效率
tb_sku?這張表中準備了?1000w?的記錄。
我用夸克網盤分享了「1000w的模擬數據」鏈接：https://pan.quark.cn/s/15cf665202b2
?這張表中id為主鍵，有主鍵索引，而其他字段是沒有建立索引的。 我們先來查詢其中的一條記錄，看看里面的字段情況，執行如下SQL：
select * from tb_sku where id = 1\G;
可以看到即使有1000w的數據,根據id進行數據查詢,性能依然很快，因為主鍵id是有索引的。 那么接下來，我們再來根據 sn 字段進行查詢，執行如下SQL：
SELECT * FROM tb_sku WHERE sn = '100000003145001';
創建索引：
create index idx_sku_sn on tb_sku(sn) ;
?然后再次執行相同的SQL語句，再次查看SQL的耗時。
SELECT * FROM tb_sku WHERE sn = '100000003145001';
2.最左前綴法則
在 tb_user 表中，有一個聯合索引，這個聯合索引涉及到三個字段，順序分別為：profession， age，status。
對于最左前綴法則指的是，查詢時，最左變的列，也就是profession必須存在，否則索引全部失效。 而且中間不能跳過某一列，否則該列后面的字段索引將失效。 接下來，我們來演示幾組案例，看一下具體的執行計劃：
explain select * from tb_user where profession = '軟件工程' and age = 31 and status= '0';
explain select * from tb_user where profession = '軟件工程' and age = 31;
explain select * from tb_user where profession = '軟件工程';
?以上的這三組測試中，我們發現只要聯合索引最左邊的字段 profession存在，索引就會生效，只不過索引的長度不同。 而且由以上三組測試，我們也可以推測出profession字段索引長度為47、age字段索引長度為2、status字段索引長度為5。
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';
?而通過上面的這兩組測試，我們也可以看到索引并未生效，原因是因為不滿足最左前綴法則，聯合索引最左邊的列profession不存在。
explain select * from tb_user where profession = '軟件工程' and status = '0';
上述的SQL查詢時，存在profession字段，最左邊的列是存在的，索引滿足最左前綴法則的基本條 件。但是查詢時，跳過了age這個列，所以后面的列索引是不會使用的，也就是索引部分生效，所以索引的長度就是47。
思考題：
當執行 SQL 語句 : explain select * from tb_user where age = 31 and status = '0' and profession = '軟件工程 ' ； 時，是否滿足最左前綴法則，走不走上述的聯合索引，索引長度？
可以看到，是完全滿足最左前綴法則的，索引長度 54 ，聯合索引是生效的。注意 ： 最左前綴法則中指的最左邊的列，是指在查詢時，聯合索引的最左邊的字段 ( 即是第一個字段 ) 必須存在，與我們編寫 SQL 時，條件編寫的先后順序無關。
3.范圍查詢
聯合索引中，出現范圍查詢(>,<)，范圍查詢右側的列索引失效。
explain select * from tb_user where profession = '軟件工程' and age > 30 and status
= '0';
explain select * from tb_user where profession = '軟件工程' and age >= 30 and status = '0';
4.索引失效情況
?1.索引列運算
不要在索引列上進行運算操作， 索引將失效。
A. 當根據phone字段進行等值匹配查詢時, 索引生效。?
explain select * from tb_user where phone = '17799990015';
?B. 當根據phone字段進行函數運算操作之后，索引失效。
explain select * from tb_user where substring(phone,10,2) = '15';
2.字符串不加引號 ?
explain select * from tb_user where profession = '軟件工程' and age = 31 and status
= '0';
explain select * from tb_user where profession = '軟件工程' and age = 31 and status
= 0;
explain select * from tb_user where phone = '17799990015';
explain select * from tb_user where phone = 17799990015;
?
3.模糊查詢
explain select * from tb_user where profession like '軟件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';
?
?經過上述的測試，我們發現，在like模糊查詢中，在關鍵字后面加%，索引可以生效。而如果在關鍵字前面加了%，索引將會失效。
4.or連接條件
explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;
由于age沒有索引，所以即使id、phone有索引，索引也會失效。所以需要針對于age也要建立索引。
create index idx_user_age on tb_user(age);
最終，我們發現，當or連接的條件，左右兩側字段都有索引時，索引才會生效。
5.數據分布影響
如果MySQL評估使用索引比全表更慢，則不使用索引。
explain select * from tb_user where phone >= '17799990005';
explain select * from tb_user where phone >= '17799990015';
經過測試我們發現，相同的SQL語句，只是傳入的字段值不同，最終的執行計劃也完全不一樣，這是為什么呢？
5.SQL提示
目前tb_user表的數據情況如下:
索引情況如下:?
把上述的 idx_user_age, idx_email 這兩個之前測試使用過的索引直接刪除。 ?
explain select * from tb_user where profession = '軟件工程';
查詢走了聯合索引。 ?
create index idx_user_pro on tb_user(profession);
測試結果，我們可以看到，possible_keys中 idx_user_pro_age_sta,idx_user_pro 這兩個索引都可能用到，最終MySQL選擇了idx_user_pro_age_sta索引。這是MySQL自動選擇的結果。
那么，我們能不能在查詢的時候，自己來指定使用哪個索引呢？
答案是肯定的，此時就可以借助于 MySQL的SQL提示來完成。 接下來，介紹一下SQL提示。
explain select * from tb_user use index(idx_user_pro) where profession = '軟件工
程';
explain select * from tb_user ignore index(idx_user_pro) where profession = '軟件工
程';
3). force index ： 強制使用索引。 ?
explain select * from tb_user force index(idx_user_pro) where profession = '軟件工
程';
6.覆蓋索引
盡量使用覆蓋索引，減少select *。 那么什么是覆蓋索引呢？ 覆蓋索引是指 查詢使用了索引，并 且需要返回的列，在該索引中已經全部能夠找到 。
接下來，我們來看一組SQL的執行計劃，看看執行計劃的差別，然后再來具體做一個解析。
explain select id, profession from tb_user where profession = '軟件工程' and age =
31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '軟件工程'
and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '軟
件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '軟件工程' and age = 31 and status
= '0';?
?id是主鍵，是一個聚集索引。 name字段建立了普通索引，是一個二級索引（輔助索引）。
?B. 執行SQL :
select * from tb_user where id = 2;
根據id查詢，直接走聚集索引查詢，一次索引掃描，直接返回數據，性能高。 ?
C. 執行SQL：
selet id,name from tb_user where name = 'Arm';
?雖然是根據name字段查詢，查詢二級索引，但是由于查詢返回在字段為 id，name，在name的二級索引中，這兩個值都是可以直接獲取到的，因為覆蓋索引，所以不需要回表查詢，性能高。
selet id,name,gender from tb_user where name = 'Arm';
思考題：一張表 , 有四個字段 (id, username, password, status), 由于數據量大 , 需要對以下SQL 語句進行優化 , 該如何進行才是最優方案 :select id,username,password from tb_user where username = 'itcast';答案 : 針對于 username, password 建立聯合索引 , sql為:create index idx_user_name_pass on tb_user(username,password);這樣可以避免上述的 SQL 語句，在查詢的過程中，出現回表查詢。
7.前綴索引
當字段類型為字符串（varchar，text，longtext等）時，有時候需要索引很長的字符串，這會讓索引變得很大，查詢時，浪費大量的磁盤IO， 影響查詢效率。此時可以只將字符串的一部分前綴，建立索引，這樣可以大大節約索引空間，從而提高索引效率。
1). 語法
create index idx_xxxx on table_name(column(n)) ;create index idx_email_5 on tb_user(email(5));
select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;
8.單列索引與聯合索引
在查詢出來的索引中，既有單列索引，又有聯合索引。 ?
explain select id,phone,name from tb_user where phone='17799990010' AND name='韓信';
create unique index idx_user_phone_name on tb_user(phone,name);
在業務場景中，如果存在多個查詢條件，考慮針對于查詢字段建立索引時，建議建立聯合索引，而非單列索引。
?
)
