目錄

索引使用原則

列的離散度

聯(lián)合索引最左匹配

覆蓋索引

索引條件下推（ICP）

索引的創(chuàng)建與使用

索引創(chuàng)建基本原則

什么時(shí)候用不到索引

?

---

我們創(chuàng)建索引主要是為了提高查詢數(shù)據(jù)的效率，但是索引并不是越多越好，也不是所有的列都可以創(chuàng)建索引

索引使用原則

列的離散度

列的離散度的公式：count(distinct(column_name)) : count(*)?列的全部不同值和所有數(shù)據(jù)行的比例。
數(shù)據(jù)行數(shù)相同的情況下，分子越大，列的離散度就越高。簡單來說，如果列的重復(fù)值越多，離散度就越低，重復(fù)值越少，離散度就越高。
當(dāng)我們建立的索引后去檢索數(shù)據(jù)，如果重復(fù)值太多，那么就需要掃描更多的行數(shù)
原則一:建立索引，要使用離散度更高的字段。

聯(lián)合索引最左匹配

在實(shí)際使用時(shí)，我們往往需要進(jìn)行多條件查詢，會(huì)建立聯(lián)合索引。單列索引可以看成是特殊的聯(lián)合索引

#建表語句 CREATE TABLE `user_innodb` (`id` int(11) PRIMARY KEY AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,`gender` tinyint(1) DEFAULT NULL,`phone` varchar(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;#創(chuàng)建聯(lián)合索引 ALTER TABLE user_innodb add INDEX comidx_name_phone(name,phone);

聯(lián)合索引在B+樹中是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，將多個(gè)字段當(dāng)作一個(gè)整體來組合，它是按照從左到右的順序來建立搜索樹的(name在左邊，phone在右邊)。從上圖不難發(fā)現(xiàn)，name是有序的，phone是無序的(因?yàn)樗饕腂+樹要求有序)。當(dāng)name相等的時(shí)候, phone才是有序的，也就是當(dāng)name相等的時(shí)候才有可以通過phone的"索引"來進(jìn)行檢索

比方說使用這樣的條件查詢where name='Tom' and phone = '137XXX'去查詢數(shù)據(jù)的時(shí)候，B+樹會(huì)優(yōu)先比較 name 來確定之后是向右子樹還是左子樹檢索。當(dāng)找到name相同的節(jié)點(diǎn)的時(shí)候才會(huì)去比較phone。但是如果查詢條件只有phone，那么MySQL就不知道如何去比較節(jié)點(diǎn)，因?yàn)榻+樹的時(shí)候name是第一個(gè)比較元素，只用phone是無序的，無法比較。

原則二:最左匹配原則，建立聯(lián)合索引的時(shí)候，一定要把最常用的列放在最左邊。

舉例:
對(duì)于上述的聯(lián)合索引
SELECT * FROM user_innodb WHERE name= 'chenpp' AND phone='150992121'; 可以使用到索引

SELECT * FROM user_innodb WHERE name= 'chenpp' 也可以使用到索引

SELECT * FROM user_innodb WHERE phone='150992121';不可以使用到索引

也就是說我們創(chuàng)建聯(lián)合索引(name,phone)的時(shí)候就相當(dāng)于建立了兩個(gè)索引(name)和(name,phone)。

如果我們創(chuàng)建三個(gè)字段的聯(lián)合索引index(a,b,c)，相當(dāng)于創(chuàng)建三個(gè)索引：index(a),index(a,b),index(a,b,c)
用 where b=? 和 where b=? and c=? 是不能使用到索引的。但是where a=? and c=?是可以使用到索引的
不能不用第一個(gè)字段

可以驗(yàn)證下:

CREATE TABLE `user_innodb2` (`id` int(11) PRIMARY KEY AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,`alias` varchar(11) DEFAULT NULL,`gender` tinyint(1) DEFAULT NULL,`phone` varchar(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE user_innodb2 add INDEX comidx_name_alias_phone(name,alias,phone);

覆蓋索引

回表：對(duì)于非主鍵索引，我們先通過索引找到主鍵索引的鍵值，再通過主鍵值查出主鍵索引里面的數(shù)據(jù)值，它比基于主鍵索引的查詢多掃描了一棵索引樹，這個(gè)過程就叫回表

對(duì)于innoDB里通過非主鍵索引進(jìn)行查詢，就會(huì)發(fā)生回表

在輔助索引里面，不管是單列索引還是聯(lián)合索引，如果select的數(shù)據(jù)列只用從索引中就能夠取得，不必從數(shù)據(jù)區(qū)中讀取，這時(shí)候使用的索引就叫做覆蓋索引，這樣就避免了回表。--- 這是為什么不建議開發(fā)使用select * from XXX

Extra里面值為“Using index”代表使用了覆蓋索引。

索引條件下推（ICP）

默認(rèn)索引條件下推是開啟的

CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` enum('M','F') NOT NULL,`hire_date` date NULL,PRIMARY KEY (`emp_no`) ) ENGINE=InnoDB DEFAULT;alter table employees add index idx_lastname_firstname(last_name,first_name);#關(guān)閉ICP set optimizer_switch='index_condition_pushdown=off'; #開啟ICP set optimizer_switch='index_condition_pushdown=on'; #查看ICP參數(shù) show variables like 'optimizer_switch';INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (1, NULL, '698', 'liu', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (2, NULL, 'd99', 'zheng', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (3, NULL, 'e08', 'huang', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (4, NULL, '59d', 'lu', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (5, NULL, '0dc', 'yu', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (6, NULL, '989', 'wang', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (7, NULL, 'e38', 'wang', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (8, NULL, '0zi', 'wang', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (9, NULL, 'dc9', 'xie', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10, NULL, '5ba', 'zhou', 'F', NULL);

如果我們要查詢所有姓wang，并且名字最后一個(gè)字是zi的員工，比如王麻子，王瘦子
SQL: select * from employees where last_name='wang' and first_name LIKE '%zi' ;

這條SQL有兩種執(zhí)行方式：
1.根據(jù)聯(lián)合索引查出所有姓wang的二級(jí)索引數(shù)據(jù)，然后回表，到主鍵索引上查詢?nèi)糠蠗l件的數(shù)據(jù)（3 條數(shù)據(jù)）。然后返回給 Server 層，在Server 層過濾出名字以zi結(jié)尾的員工。
2.根據(jù)聯(lián)合索引查出所有姓wang的二級(jí)索引數(shù)據(jù)（3個(gè)索引），然后從二級(jí)索引中篩選出first_name以zi結(jié)尾的索引（1個(gè)索引），然后再回表，到主鍵索引上查詢?nèi)糠蠗l件的數(shù)據(jù)（1條數(shù)據(jù)），返回給Server 層。

很明顯第二種辦法查詢索引樹的次數(shù)更少
注意，索引的比較是在存儲(chǔ)引擎進(jìn)行的，而數(shù)據(jù)記錄的比較在Server層進(jìn)行的。而當(dāng)first_name的條件不能用于索引過濾時(shí)，Server 層不會(huì)把first_name的條件傳遞給存儲(chǔ)引擎，所以會(huì)多讀取兩條無用的記錄。
這時(shí)候，如果滿足last_name='wang'的記錄有100000條，就會(huì)有99999條無用的記錄需要讀取，這是對(duì)資源的浪費(fèi)。

實(shí)際執(zhí)行一下
ICP開啟時(shí):

此時(shí)會(huì)把first_name LIKE '%zi'下推給存儲(chǔ)引擎，只會(huì)從數(shù)據(jù)表讀取所需的1條記錄。
索引條件下推（Index Condition Pushdown），是MySQL5.6以后完善的功能。只適用于二級(jí)索引。

ICP關(guān)閉后:

Using Where代表從存儲(chǔ)引擎取回的數(shù)據(jù)不全部滿足條件，需要在Server 層過濾。
先用last_name 條件進(jìn)行索引檢索，讀取數(shù)據(jù)表記錄后，再進(jìn)行比較，檢查是否符合first_name LIKE '%zi' 的條件。此時(shí)3條中只有1條符合條件。

總的來說，索引條件下推的目標(biāo)就是為了減少訪問表的完整行數(shù)從而減少 I/O 操作。

索引的創(chuàng)建與使用

Mysql在查詢的時(shí)候只會(huì)使用一個(gè)索引，但不是一個(gè)字段

索引創(chuàng)建基本原則

1.在用于where判斷,order排序(B+樹便于進(jìn)行排序和范圍查詢),join的（on）字段上創(chuàng)建索引
2.索引的個(gè)數(shù)不要過多。 —— 因?yàn)楦聰?shù)據(jù)的時(shí)候，還需要對(duì)索引也進(jìn)行更新，過多的索引會(huì)造成空間浪費(fèi)，導(dǎo)致更新變慢。
3.區(qū)分度低的字段，例如性別，不要建索引。—— ?離散度太低，導(dǎo)致掃描行數(shù)過多。
4.頻繁更新的值，不要作為主鍵或者索引。—— ?因?yàn)闀?huì)發(fā)生頁分裂和頁合并，造成額外的開銷
5.組合索引把散列性高（區(qū)分度高）或者常用的值放在前面。
6.盡量創(chuàng)建復(fù)合索引，而不是修改單列索引。
7.?索引會(huì)忽略null值，所以在設(shè)計(jì)數(shù)據(jù)庫的時(shí)候需要設(shè)置為NOT NULL。
8.不適用于負(fù)向查詢操作（not in , <>, !=，or） //用到or地方，盡量用union，或者程序兩次查找
9.過長的字段，怎么建立索引？ ?
可以使用前綴索引或者對(duì)這個(gè)較長字段進(jìn)行hash運(yùn)算，使用其作為索引(如果發(fā)生hash沖突就再過濾就好了)
10.不建議用無序的值（例如身份證、UUID ）作為索引,主要是主鍵索引
使用遞增的id作為索引的時(shí)候，在新增數(shù)據(jù)的時(shí)候，由于是遞增的，如果原來的葉已滿，就會(huì)創(chuàng)建新的葉，而如果索引是無序的，那么在新增數(shù)據(jù)的時(shí)候，會(huì)強(qiáng)行插入到已滿的葉中，導(dǎo)致葉分裂，從而有額外的開銷;所以主鍵一般不建議使用uuid,其他索引不能保證有序

什么時(shí)候用不到索引

1.在索引列上使用函數(shù)（replace\SUBSTR\CONCAT\sum count avg）、表達(dá)式、計(jì)算（+ - * /）：
對(duì)索引字段做函數(shù)操作，可能會(huì)破壞索引值的有序性，因此優(yōu)化器就決定放棄使用索引樹查詢。
MySQL 無法再使用索引快速定位功能，而只能使用全索引掃描或者全表掃描。

2.字符串條件查詢不加引號(hào)，出現(xiàn)隱式轉(zhuǎn)換(如果是id = '1'是可以走索引的)
ALTER TABLE user_innodb add INDEX comidx_name_phone(name,phone);
為什么varchar字段的查詢條件不加引號(hào)會(huì)導(dǎo)致無法使用索引呢?
因?yàn)樵贛ySQL 中，字符串和數(shù)字做比較的話，是將字符串轉(zhuǎn)換成數(shù)字再比較的。
對(duì)于非數(shù)字的字符串，MySQL里統(tǒng)一都會(huì)轉(zhuǎn)成數(shù)字0

這樣一來，判斷name = 136的時(shí)候，如果想走索引，就需要把對(duì)應(yīng)索引樹的name的值全部轉(zhuǎn)化成數(shù)字，一個(gè)是轉(zhuǎn)化時(shí)候費(fèi)時(shí)，而轉(zhuǎn)化后之后原來name的大小關(guān)系也被破壞了(都變成了0) 開銷成本太大，還不如全表掃描(不過當(dāng)使用覆蓋索引的時(shí)候可以使用到索引，index連接類型，掃描全部的二級(jí)索引樹)

其實(shí)這樣的SQL在寫的時(shí)候也是不推薦的，一個(gè)是性能問題，一個(gè)是查詢出來的數(shù)據(jù)也不準(zhǔn)確

3.like條件中前面帶%

對(duì)于%模糊匹配，%chenpp無法使用到索引，但是chenpp%可以使用到。如果實(shí)在需要進(jìn)行模糊匹配，可以使用全文索引。

4. 負(fù)向查詢 不是一定不能使用到索引

NOT LIKE 不能使用索引：
explain select * from employees where last_name not like'wang'

!= （<>）和NOT IN 在某些情況下可以：
explain select * from employees where emp_no not in (1)

explain select * from employees where emp_no <> 1

?

注意一個(gè)SQL語句是否使用索引，跟數(shù)據(jù)庫版本、數(shù)據(jù)量、數(shù)據(jù)選擇度都有關(guān)系。
?

實(shí)際執(zhí)行一個(gè)SQL的時(shí)候，用不用索引，使用什么索引，最終都是由優(yōu)化器來決定的。
MySQL的優(yōu)化器是基于cost開銷的優(yōu)化器(Cost Base Optimizer),而不是基于規(guī)則（Rule-Based Optimizer)。基于開銷的優(yōu)化器不像基于規(guī)則的優(yōu)化器，有具體的執(zhí)行標(biāo)準(zhǔn)，它在執(zhí)行的時(shí)候會(huì)受多種因素影響(比方說數(shù)據(jù)庫版本、數(shù)據(jù)量、數(shù)據(jù)選擇度)，會(huì)實(shí)時(shí)計(jì)算不同的執(zhí)行路徑的開銷，去選擇開銷最小的方案。

最后再總結(jié)下索引的幾種概念 :?

聚集索引:
聚集索引是指索引鍵值的邏輯順序和數(shù)據(jù)庫表行數(shù)據(jù)的物理順序相同。一個(gè)表只能有一個(gè)聚集索引，因?yàn)橐粋€(gè)表的物理順序只有一種情況。 在innodb里，其主鍵索引就是聚集索引

主鍵索引 :?是一種特殊的唯一索引，一張表中只能定義一個(gè)主鍵索引，通常有一列或列組合，用于唯一標(biāo)識(shí)一條記錄，在InnoDB里就是依賴主鍵索引來組織數(shù)據(jù)的

聯(lián)合索引 :?與之相對(duì)的是單列索引，就是索引是由不少于2個(gè)的數(shù)據(jù)列組織的，像INDEX(columnA, columnB)，這就是聯(lián)合索引。

覆蓋索引 :?InnoDB里輔助索引的葉子節(jié)點(diǎn)保存的是主鍵索引的值，所以使用輔助索引(非主鍵索引)查詢，會(huì)先在二級(jí)索引樹找到對(duì)應(yīng)的主鍵索引值，再根據(jù)主鍵索引去查詢數(shù)據(jù)，這個(gè)過程叫做回表， 如果一個(gè)SQL只需要查詢一次索引樹不需要回表就可以找到需要返回的數(shù)據(jù)(也就是說查詢的字段在輔助索引里就可以完全找到),那么就是覆蓋索引

索引條件下推 :?ICP就是為了減少完整記錄讀取的條數(shù)，它對(duì)InnoDB聚集索引無效，只能對(duì)輔助索引有效 。
一個(gè)SQL并不是所有查詢條件都可以使用到索引，如果使用了索引條件下推，那么在通過二級(jí)索引樹檢索到主鍵索引值后，會(huì)在存儲(chǔ)引擎端根據(jù)其他的條件先篩選滿足的主鍵值,然后根據(jù)篩選后的主鍵集合去回表獲取最終的表數(shù)據(jù)。 所以使用了索引條件下推可以減少需要回表的次數(shù)，就不需要全部查詢出來后在server端過濾?
?? ??? ??? ?? ?

補(bǔ)充2020-03-01:

即使查詢條件不滿足最左匹配原則，如果使用到了覆蓋索引，那么最終查詢的時(shí)候也是可以走索引的
比如:這里雖然只以phone為條件不滿足最左匹配原則,但是因?yàn)椴樵兞兄挥衝ame,phone 那么只要掃描整個(gè)輔助索引樹就可以查詢到結(jié)果了，不需要進(jìn)行全表掃描(但是如果在查詢列里加上gender字段，就會(huì)全表掃描) ?不過這里使用的是index連接類型(?Full Index Scan),會(huì)查詢?nèi)克饕械臄?shù)據(jù)，雖然比全表掃描快但也是需要優(yōu)化的

反過來，即使查詢條件滿足最左匹配原則，根據(jù)實(shí)際執(zhí)行的情況也可能不走索引(因?yàn)镸ySQL的優(yōu)化器是基于cost開銷的優(yōu)化器)

#創(chuàng)建這樣的一張表，隨意添加10條數(shù)據(jù)(age 都大于3) CREATE TABLE `user_innodb` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,`gender` tinyint(1) DEFAULT NULL,`phone` varchar(11) DEFAULT NULL,`age` int(4) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_age_phone` (`age`,`phone`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=100002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

從執(zhí)行計(jì)劃可以看出最終走了全表掃描，這是因?yàn)槭褂胊ge>1查詢輔助索引樹(二級(jí)索引樹),查詢出來所有的主鍵都滿足，那么就需要對(duì)每個(gè)主鍵進(jìn)行回表，開銷太大了，直接全表掃描更快

如果使用age>10來查詢就不會(huì)出現(xiàn)全表掃描了

或者刪除數(shù)據(jù)只剩余3條，就可以看到會(huì)使用到索引

再次強(qiáng)調(diào):一個(gè)SQL語句是否使用索引，跟數(shù)據(jù)庫版本、數(shù)據(jù)量、數(shù)據(jù)選擇度都有關(guān)系。
?

?

?

?

?

?

總結(jié)

以上是生活随笔為你收集整理的MySQL(四)索引的使用的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。