mysql 查詢優(yōu)化中，索引的使用原則
- 前提：sys_user 。數(shù)據(jù)340W
CREATE TABLE `sys_user` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用戶名' ,
`user_password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用戶密碼' ,
`phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '電話號碼' ,
`email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '郵箱' ,
`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地址' ,
PRIMARY KEY (`id`),
INDEX `idx_user_name` (`user_name`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=3409377
ROW_FORMAT=DYNAMIC
;
- 列的離散度
在分析索引的使用之前，先看一個必要的概念：列的離散度。
- 計算公式：user_name 的離散度
select (select count(distinct(user_name)) from sys_user )/(select count(1) from sys_user);
從上面公式可以看出：列重復(fù)的值越少，離散度越高，重復(fù)的值越多離散度越低。這個和索引有啥關(guān)系？結(jié)論是：離散度越高，在查詢時，掃描的行數(shù)越少，效率越高。原理后面分析，先看現(xiàn)象。
- 離散度測試
數(shù)據(jù)量 340W。
數(shù)據(jù).PNG
如上圖所示：email 的數(shù)據(jù)離散度非常低。也就是email 存中很多的重復(fù)的值。
- 在沒有使用索引的前提下：查詢
查詢.png
SELECT user_name,email from sys_user where email = '1234@163.com'
運行結(jié)果是，需要2.9s，我們可以使用 explain 分析一下執(zhí)行情況。
EXPLAIN SELECT user_name,email from sys_user where email = '1234@163.com'
掃描結(jié)果.png
從圖中我們可以看到，在沒有使用索引的情況下，查詢條件是email 時，要掃描 310W 行的數(shù)據(jù)，幾乎是全表掃描了，所以很慢，用了接近3s 的時間。
- 給email 創(chuàng)建索引
ok, 從上面的查詢結(jié)果看，查詢很慢，我們第一反應(yīng)是創(chuàng)建一個索引，ok，現(xiàn)在我們就來創(chuàng)建一個索引。
索引查詢.png
我們看到查詢結(jié)果，結(jié)果明顯只用于0.001s。那么是不是說明離散度的低的列建立索引也能對查詢結(jié)果有明顯的提高呢？先分析explain :
image.png
我們看到，這條語句執(zhí)行只掃描了三行，所以查詢很快。再看一條語句：
image.png
image.png
怎么回事？不是加了索引就會變快嗎？怎么換個值查詢，就又變慢了呢？？原因就是掃描了150萬行的數(shù)據(jù)。
- 多條件查詢：
SELECT user_name,email, user_password from sys_user where email = '123@163.com' and user_password = '1234569'
查詢結(jié)果：
查詢結(jié)果.png
簡直就是災(zāi)難，明明只有兩條數(shù)據(jù)符合結(jié)果，但是卻用了接近10s 。
explain結(jié)果.png
掃描數(shù)據(jù)庫 157W行數(shù)據(jù)。
- 結(jié)論：從上面的運行結(jié)果看，表中的email 列數(shù)據(jù)離散度非常低，也就是說重復(fù)度非常高，那么我們就算為email 創(chuàng)建了索引，效果也沒有多么明顯，也就是說，如果想通過索引來優(yōu)化查詢性能的話，創(chuàng)建索引的列的值的重復(fù)度必須要小。
