MySQL中如何創(chuàng )建高效且合適的索引

這篇文章主要介紹了中如何創(chuàng )建高效且合適的索引，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著(zhù)大家一起了解一下。

1. 當使用索引列進(jìn)行查詢(xún)的時(shí)候盡量不要使用表達式，把計算放到業(yè)務(wù)層而不是數據庫層

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

2. 盡量使用主鍵查詢(xún)，而不是其他索引，主鍵查詢(xún)不會(huì )出現回表查詢(xún)。

我們所有的表基本都會(huì )有主鍵的，所以平時(shí)開(kāi)發(fā)中能用索引就用索引，能用主鍵索引就用主鍵索引。

3. 使用前綴索引

很多時(shí)候我們的索引其實(shí)都是字符串，不可避免會(huì )出現長(cháng)字符串，就會(huì )導致索引占用過(guò)大，降低其效率。尤其是對于blob,text, varchar這樣的長(cháng)列。這時(shí)候處理方式就是不使用字段的全值作為索引，而是只取其前半部分即可（選擇的這部分前綴索引的選擇性接近于整個(gè)列）。這樣可以大大減少索引空間，從而提高效率，壞處就是降低了索引的選擇性。

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

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

下面我們已employee表為例子(表結構和數據看文末)

我們以email字段建立索引為例：

這個(gè)數據的郵箱其實(shí)是手機號+@qq.com為例的，其實(shí)前11位后面都是相同的。我用下面的sql來(lái)看看這些數據的選擇性（分別取前10，11，12）位計算。

-- 當是11個(gè)前綴的時(shí)候選擇性是1，在增加字段長(cháng)度，選擇性也不會(huì )變化
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位的時(shí)候索引選擇性是最高的1，就沒(méi)必要使用全部作為索引，增加了索引的空間。

-- 創(chuàng  )建前綴索引
alter table employee add key(email(11));

我們也可以使用count計算頻率來(lái)統計（出現的次數越少，說(shuō)明重復率越低，選擇性越大）

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

4.使用索引掃描來(lái)排序

我們經(jīng)常會(huì )有排序的需求，使用order by 但是order by是比較影響性能的，它是通過(guò)把數據加載到內存去排序的，如果數據量很大內存放不下，只能分多次處理。但是索引本身就是有序的，直接通過(guò)索引完成排序更省事。

掃描索引本身是很快的，因為只需要從一條索引記錄移動(dòng)到緊接著(zhù)的下一條記錄，但如果索引不能覆蓋查詢(xún)所需的所有列時(shí)，就不得不每掃描一條索引記錄就回表查詢(xún)一次對應行，這基本都是隨機IO。因此按索引順序讀取數據的速度通常比順序的全表掃描慢。

mysql可以使用同一個(gè)索引即滿(mǎn)足排序，又用于查找行。如果可以的話(huà)請考慮建立這種索引。

只有當索引列順序和order by子句的順序完全一致，并且所有列的排序方向(倒敘或者正序)都是一樣的，mysql才能使用索引對結果做排序。如果查詢(xún)需要關(guān)聯(lián)多張表，只有當order by子句的字段全是第一張表時(shí)才能使用索引排序。order by 查詢(xún)同時(shí)也需要滿(mǎn)足組合索引的最左前綴，否則也不能使用索引排序。

其實(shí)在開(kāi)發(fā)中主要注意兩點(diǎn)：

• where條件中的字段和order by中的字段能夠是組合索引而且滿(mǎn)足最左前綴。

• order by中的字段的順序需要一致，不能存在desc,又存在asc。

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

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

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

union all: 查詢(xún)分為了兩階段，其實(shí)還有一個(gè)union，在平時(shí)開(kāi)發(fā)中必須使用到union的時(shí)候推薦使用union all，因為union中多出了distinct去重的步驟。所以盡量用union all。

6. 范圍列可以用到索引

范圍的條件：>,>=,<,<=,between

范圍列可以用到索引，但是范圍列后面的列就無(wú)法用到索引了(索引最多用于一個(gè)范圍列)

比如一個(gè)組合索引age+name 如果查詢(xún)條件是where age>18 and name="紀"后面的name是用不到的索引的。

曾經(jīng)面試被問(wèn)到不等于是否能夠走某個(gè)索引，平時(shí)沒(méi)有注意過(guò)也沒(méi)有回答成功，這次親自做個(gè)實(shí)驗，關(guān)于結論請看文末。

7. 強制類(lèi)型轉換會(huì )全表掃描

我在employee表中定義了mobile字段是varchar類(lèi)型且建立索引，我分別用數字和字符串查詢(xún).

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

如果條件的值的類(lèi)型和表中定義的不一致，那么mysql會(huì )強制進(jìn)行類(lèi)型轉換，但是結果是不會(huì )走索引，索引在開(kāi)發(fā)中我們需要根據自己定義的類(lèi)型輸入對應的類(lèi)型值。

8. 數據區分度不高，更新頻繁的字段不宜建立索引

• 索引列更新會(huì )變更B+樹(shù)的，頻繁更新的會(huì )大大降低數據庫性能。

• 類(lèi)似于性別這類(lèi)(只有男女，或者未知)，不能有效過(guò)濾數據。

• 一般區分度在80%以上就可以建立索引，區分度可以使用count(distinct(列名))/count(*)

9. 創(chuàng )建索引的列不允許為null，可能會(huì )得到不符合預期的結果

也就是建立索引的字段盡量不要為空，可能會(huì )有些意想不到的問(wèn)題，但是實(shí)際工作中也不太可能不為空，所以根據實(shí)際業(yè)務(wù)來(lái)處理吧，盡量避免這種情況。

10. 當需要進(jìn)行表連接的時(shí)候，最好不要超過(guò)三張表

表連接其實(shí)就是多張表循環(huán)嵌套匹配，是比較影響性能的， 而且需要join的字段數據類(lèi)型必須一致，提高查詢(xún)效率。關(guān)于表連接原理后面專(zhuān)門(mén)寫(xiě)一篇吧。

11. 能使用limit的時(shí)候盡量使用limit。

limit的作用不是僅僅用了分頁(yè)，本質(zhì)作用是限制輸出。

limit其實(shí)是挨個(gè)遍歷查詢(xún)數據，如果只需要一條數據添加 limit 1的限制，那么索引指針找到符合條件的數據之后就停止了，不會(huì )繼續向下判斷了，直接返回。如果沒(méi)有limit，就會(huì )繼續判斷。

但是如果分頁(yè)取1萬(wàn)條后的5條limit 10000,10005 就需要慎重了，他會(huì )遍歷1萬(wàn)條之后取出5條，效率很低的。小技巧：如果主鍵是順序的，可以直接通過(guò)主鍵獲取分頁(yè)數據。

12. 單表索引盡量控制在5個(gè)內

建立/維護索引也是需要代價(jià)的，也需要占用空間的。索引并不是越多越好，要合理使用索引。

13. 單個(gè)組合索引的字段個(gè)數不宜超過(guò)5個(gè)

字段越多，索引就會(huì )越大，占用的存儲空間就越多。

索引并不是越多越好，而且索引并不需要在開(kāi)始建表的時(shí)候就全部設計出來(lái)，過(guò)早優(yōu)化反而不會(huì )是高效索引，需要在了解業(yè)務(wù)，根據相關(guān)業(yè)務(wù)sql做個(gè)統計權衡之后再去構建相關(guān)索引，這樣考慮的更周全，建立的索引更有效和高效。

以上就是對應索引優(yōu)化的小細節，希望能夠幫助你寫(xiě)出嗖嗖的sql.

補充

關(guān)于不等于是否走索引的問(wèn)題

結論：只有主鍵會(huì )走，唯一鍵和普通索引都不會(huì )走。

我在employee表中建了唯一索引employee_num和聯(lián)合索引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 '移動(dòng)電話(huà)', 
`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);