MySQL進階學習：詳解建立高效率且適當索引的方法

這篇文章是MySQL的進階學習，帶大家詳細了解建立更適合索引的方法，希望對大家有幫助！

不要當庫裡的資料較多的時候才能知道索引的重要性，更不要當庫裡的資料更多的時候才能知道合適的索引重要性。本文介紹下怎麼創建高效且合適的索引。 【相關推薦：mysql影片教學】

1. 使用索引列進行查詢的時候盡量不要使用表達式，把計算放到業務層而不是資料庫層

如下圖兩個sql的結果是一樣的，但是兩個sql的執行計劃是不一樣，在type中index的效率遠不如const where條件中actor_id 4 表達式影響了執行計劃,對於type表示的含義請參考explain詳解一篇

2. 盡量使用主鍵查詢，而不是其他索引，主鍵查詢不會出現回表查詢。

我們所有的表基本上都會有主鍵的，所以平時開發中能用索引就用索引，能用主鍵索引就用主鍵索引。

3. 使用前綴索引

很多時候我們的索引其實都是字串，不可避免會出現長字串，就會導致索引佔用過大，降低其效率。尤其是對於blob,text, varchar這樣的長列。這時候處理方式就是不使用欄位的全值作為索引，而是只取其前半部即可（所選的這部分前綴索引的選擇性接近整個欄位）。這樣可以大幅減少索引空間，進而提高效率，壞處就是降低了索引的選擇性。

索引選擇性：不重複的索引值和資料表記錄總數的比值(#T),範圍從1/#T到1之間。索引的選擇性越高查詢效率也高，因為資料的區分度很高，可以過濾掉更多的行。唯一性索引的選擇性是1，其效能也最好。

例如公司的員工表中郵箱字段，一個公司的郵箱後綴都是一樣的如xxxx@qq.com, 其實郵箱作為索引有效的就xxxx部分，因為@qq.com都是一樣的，對索引是無意義的，明顯只用xxxx作為索引，其選擇性和整個值的是一樣的，但是xxxx作為索引明顯就會減少索引空間。

下面我們已employee表為範例(表格結構與資料看文末)

我們以email欄位建立索引為例：

這個資料的信箱其實是手機號 @qq.com為例的，其實前11位後面都是相同的。我用下面的sql來看看這些資料的選擇性（分別取前10，11，12）位元來計算。

-- 当是11个前缀的时候选择性是1，在增加字段长度，选择性也不会变化
select count(distinct left(email,10))/count(*) as e10, count(distinct left(email,11))/count(*) as e11,      count(distinctleft(email,12))/count(*) as e12 from employee;

從上圖我們可以看出前10，前11，前12的選擇性分別是0.14，1.0，1.0 ,在第11位的時候索引選擇性是最高的1，就沒必要使用全部作為索引，增加了索引的空間。

-- 创建前缀索引
alter table employee add key(email(11));

我們也可以使用count計算頻率來統計（出現的次數越少，表示重複率越低，選擇性越大）

-- 查找前缀出现的频率
select count(*) as cnt,left(email,11) as pref from employee group by pref order by cnt desc limit 10;

4.使用索引掃描來排序

我們常常會有排序的需求，使用order by 但是order by是比較影響效能的，它是透過把資料載入到記憶體去排序的，如果資料量很大內存放不下，只能分多次處理。但是索引本身就是有順序的，直接透過索引完成排序更省事。

掃描索引本身是很快的，因為只需要從一條索引記錄移動到緊接著的下一筆記錄，但如果索引不能覆蓋查詢所需的所有列時，就不得不每掃描一條索引記錄就回表查詢一次對應行，這基本上都是隨機IO。因此按索引順序讀取資料的速度通常比順序的全表掃描慢。

mysql可以使用同一個索引即滿足排序，又用來尋找行。如果可以的話請考慮建立這種索引。

只有當索引列順序和order by子句的順序完全一致，並且所有列的排序方向(倒敘或正序)都是一樣的，mysql才能使用索引對結果做排序。如果查詢需要關聯多張表，只有當order by子句的欄位全是第一張表時才能使用索引排序。 order by 查詢同時也需要滿足組合索引的最左前綴，否​​則也不能使用索引排序。

其實在開發中主要注意兩點：

• where條件中的欄位和order by中的欄位能夠是組合索引而且滿足最左前綴。
• order by中的欄位的順序需要一致，不能存在desc,又存在asc。

5. union all ,in,or都能夠使用索引，但是推薦使用in

如上union all 會有兩次執行，而in 和or只有一次。同時看出or和in的執行計劃是一樣的,\

但是我們在看一下他們的執行時間。如下圖使用set profiling=1可以看到詳細時間，使用show profiles 查看具體時間。下圖看出or的時間0.00612000，in的時間0.00022800，差距還是很大的(測試的表資料只有200行)

union all:查詢分為了兩個階段，其實還有一個union，在平時開發中必須使用到union的時候推薦使用union all，因為union中多出了distinct去重的步驟。所以盡量用union all。

6. 範圍列可以用到索引

範圍的條件：>,>=,

範圍列可以用到索引，但是範圍列後面的列就無法用到索引了(索引最多用於一個範圍列)

#例如一個組合索引age name 如果查詢條件是where age>18 and name="紀"後面的name是用不到的索引的。

曾經面試被問到不等於是否能夠走某個索引，平時沒有註意過也沒有回答成功，這次親自做個實驗，關於結論請看文末。

7.強制類型轉換會全表掃描

我在employee表中定義了mobile欄位是varchar類型且建立索引，我分別用數字和字串查詢.

看看結果: 兩者type是不一樣的，而且只有字串才用到索引。

如果條件的值的類型和表中定義的不一致，那麼mysql會強制進行類型轉換，但是結果是不會走索引，索引在開發中我們需要根據自己定義的類型輸入對應的類型值。

8. 資料區分度不高，更新頻繁的欄位不宜建立索引

• 索引列更新會變更B 樹的，頻繁更新的會大大降低資料庫效能。
• 類似性別這類(只有男女，或未知)，無法有效過濾資料。
• 一般區分度在80%以上就可以建立索引，區分度可以使用count(distinct(列名))/count(*)

9. 建立索引的列不允許為null，可能會得到不符合預期的結果

也就是建立索引的欄位盡量不要為空，可能會有些意想不到的問題，但是實際工作中也不太可能不為空，所以根據實際業務來處理吧，盡量避免這種情況。

10. 當需要進行表連接的時候，最好不要超過三張表

表連接其實就是多張表循環嵌套匹配，是比較影響性能的， 而且需要join的欄位資料類型必須一致，提高查詢效率。關於手錶連接原理後面專門寫一篇吧。

11. 能使用limit的時候盡量使用limit。

limit的作用不是只用了分頁，本質作用是限制輸出。

limit其實是挨個遍歷查詢數據，如果只需要一條數據添加limit 1的限制，那麼索引指針找到符合條件的數據之後就停止了，不會繼續向下判斷了，直接返回。如果沒有limit，就會繼續判斷。

但是如果分頁取1萬條後的5條limit 10000,10005 就需要慎重了，他會遍歷1萬條之後取出5條，效率很低的。小技巧：如果主鍵是順序的，可以直接透過主鍵取得分頁資料。

12. 單一表索引盡量控制在5個內

建立/維護索引也是需要代價的，也需要佔用空間的。索引並不是越多越好，要合理使用索引。

13. 單一組合索引的欄位數不宜超過5個

欄位越多，索引就會越大，佔用的儲存空間就越多。

索引並不是越多越好，而且索引並不需要在開始建表的時候就全部設計出來，過早優化反而不會是高效索引，需要在了解業務，根據相關業務sql做個統計權衡之後再去建立相關索引，這樣考慮的更周全，建立的索引更有效和有效率。

以上就是對應索引最佳化的小細節，希望能幫助你寫出咻咻的sql.

補充

---

關於不等於是否走索引的問題

结论：只有主键会走，唯一键和普通索引都不会走。

我在employee表中建了唯一索引employee_num和联合索引employee_num+name，结果就是下图的执行情况。

---

employee表结构

CREATE TABLE `employee`  (  
`employee_id` bigint(20) NOT NULL AUTO_INCREMENT, 
`employee_num` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工编码',
`name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工姓名',  
`email` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '电子邮件', 
`mobile` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '移动电话', 
`gender` tinyint(1) NOT NULL COMMENT '性别, 0: 男 1: 女',  PRIMARY KEY (`employee_id`) USING BTREE, 
INDEX `email`(`email`(11)) USING BTREE,  INDEX `employee_u1`(`employee_num`, `name`) USING BTREE,
UNIQUE INDEX `employee_u2`(`employee_num`) USING BTREE,  INDEX `employee_u3`(`mobile`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '员工表' ROW_FORMAT = Dynamic;

employee数据如下：

INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (10, '001', '员工A', '15500000001@qq.com', '15500000001', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (11, '002', '员工B', '15500000002@qq.com', '15500000002', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (12, '003', '员工C', '15500000003@qq.com', '15500000003', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (13, '004', '员工D', '15500000004@qq.com', '15500000004', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (14, '005', '员工E', '15500000005@qq.com', '15500000005', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (15, '006', '员工F', '15500000006@qq.com', '15500000006', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (16, '007', '员工G', '15500000007@qq.com', '15500000007', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (17, '008', '员工H', '15500000008@qq.com', '15500000008', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (18, '009', '员工I', '15500000009@qq.com', '15500000009', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (19, '010', '员工J', '15500000010@qq.com', '15500000010', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (20, '011', '员工K', '15500000011@qq.com', '15500000011', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (21, '012', '员工L', '15500000012@qq.com', '15500000012', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (22, '013', '员工M', '15500000013@qq.com', '15500000013', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (23, '014', '员工N', '15500000014@qq.com', '15500000014', 1);

更多编程相关知识，请访问：编程视频！！

以上是MySQL進階學習：詳解建立高效率且適當索引的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！