mysql索引的应用_MySQL索引应用总结

目录：

对于几种类型SQL索引应用的理解和分析 ※

|---- 单个字段的筛选和排序

|---- 多个字段等值筛选

|---- 多个字段范围筛选

|---- 多个字段等值筛选和排序

|---- 多个字段范围筛选和排序

执行计划的 Explain 字段说明

|---- Using index

|---- Using where; Using index

|---- Using index condition

一、对于几种类型SQL索引应用的理解和分析

1. 单个字段的筛选和排序

(1) SQL：

SELECT * FROM `table` WHERE `a` > 12 ORDER BY `a`;

索引建立方式：(`a`)

解释：为 a 建立索引后，该索引上的数据即按照 a 顺序排列，只需做一次查找找到 a=12 的数据，再顺序继续遍历即可找到所有满足 where 条件的数据，这样避免了全表扫描。同时可以发现，这些数据刚好也是按照 a 列排序好的，也避免了再次排序。

主键ID 索引列A

1 10

5 11

3 12

2 13

结论：若筛选和排序都是同一个字段，不管等值和非等值都可以用到索引

2. 多个字段等值筛选

(1) SQL：

SELECT * FROM `table` WHERE `a` = 10 and `b` = 20 and `c` = 30;

索引建立方式：abc 建立联合索引，且区分度高的字段放在前面

(区分度：不重复的值数量/总行数，unique字段的区分度最高为1，性别、状态等字段通常区分度较低)

3. 多个字段范围筛选

(1) SQL：

SELECT * FROM `table` WHERE `a` = 10 and `b` > 10;

索引建立方式：(`a`, `b`)

(2) SQL：

SELECT * FROM `table` WHERE `a` IN (10,11,12) and `b` > 10;

索引建立方式：(`a`, `b`)

(3) SQL：

SELECT * FROM `table` WHERE `a` > '10' and `b` = '10';

索引建立方式：(`b`, `a`)

(4) SQL：

SELECT * FROM `table` WHERE `a` > '10' and `b` = '10' and `c` > '10';

索引建立方式：(`b`, `a`) 或 (`b`, `c`)

解释：

(1) (2) 范围条件(> < between)及之前的等值条件可使用索引，但是范围之后的条件无法使用索引

(另外 in 属于特殊情况，in 在此处可被优化为等值条件，不会中止剩余索引的使用)

(3) and 条件的前后顺序可被自动优化，从而尽可能多的使用索引

(4) 根据上面两条，b 为等值条件，顺序会被调整到首位，a 和 c 属于范围条件，之后的条件无法使用索引，只能使用一个作为索引

4. 多个字段等值筛选和排序

(1) SQL：

SELECT * FROM `table` WHERE `a` = 10 ORDER BY `b`;

索引建立方式：(`a`, `b`)

解释：(`a`, `b`) 索引建立后，数据排列如图所示，即数据先按 A 列排序，A 列相同时再按 B 列排序。此时若筛选 A 列等于某个值，只需将数据依次取出，B 列默认有序，这样可以避免再次排序。

主键ID 索引列A 索引列B

1 10 100

5 10 101

3 10 103

2 13 50

7 13 51

(2) SQL：

SELECT * FROM `table` WHERE `a` = 10 AND `b` = 20 ORDER BY `c`;

索引建立方式：abc、ab、ac、bc 都可以使用到索引！一般选 (A, B, C) 建索引比较稳妥，且需要把区分度高的放前面。

解释：建立 abc 和 ab 索引时，筛选流程同 (1)，需要说明一下bc建立索引情况：将 B=20 数据顺序取出，C 列默认有序，但是本次筛选数据不一定满足 A=10，二次筛选一遍仅保留满足 A 条件的数据即可，这样仅需对较小的结果集进行顺序扫描一次，避免了性能消耗较高的排序。

5. 多个字段范围筛选和排序

(1) SQL：

SELECT * FROM `table` WHERE `a` > 10 ORDER BY `b`;

索引建立方式：(`a`)

解释：见 3(1) 的图，即使建立 ab 索引，筛选出 A > 10 的数据后，C 也是无序的，必须再次对 C 排序

结论：筛选字段和排序字段不同、且筛选条件为非等值条件(包括 in)，order by 的列一定无法应用索引，无法避免排序(Using filesort)

(2) SQL：

SELECT * FROM `table` WHERE `a` = 10 AND `b` = 20 AND `c` > 30 ORDER BY `c`;

索引建立方式：(`a`, `b`, `c`) 联合索引，c 排序可以使用索引

解释：同 1(1)，筛选和排序都是同一个字段，按 ab 筛选后，c 都是有序的，只需过滤 >30 的值，不需要再次排序

(3) SQL：

SELECT * FROM `table` WHERE `a`=10 AND `b`=10 AND `c` > 20 ORDER BY `d`;

索引建立方式：为 (A, B, C) 建立联合索引，d 无法使用索引

解释：同 4(1)，abc 筛选后是按 c 排序，c 排序≠ d 排序，即使建立 abcd 索引，筛选出 c > 20 的数据后，d 也是无序的，必须再次排序

(4) SQL：

SELECT * FROM `table` WHERE `a`=10 AND `b` IN (10,20,30) AND `c` > 40 ORDER BY `c`;

索引建立方式：为 (A, B, C) 建立联合索引，筛选可以使用索引，但排序用不到索引！

解释：如 3(2)，IN 虽然属于范围查询，但是不会终止索引使用，ABC的筛选都可以使用索引。但是B是非等值查询，会阻止C使用索引排序。

据其他资料，MySQL会将 IN 拆成多个语句，再进行聚合得到结果。如 b=10 时得到的子结果中 c 确实为有序，但和 b=20、b=30 的子结果放在一起，c 不能保证有序，需要再次排序。

二、执行计划的 Explain 字段说明

Using index：仅依赖索引即可，不需要回表查询数据(当 select A where A=xx，且 A 为索引/联合索引最左时出现)

Using where; Using index：如果同时出现 Using where; Using index 也是依赖索引、不需要回表查询，但由于是范围查询，需要进行过滤。

(当 SQL 如 SELECT `a` FROM `table` WHERE `a` > 10;，且 A 为索引/联合索引最左时出现。即使用索引找到数据再使用大于条件过滤，所以有 Using where 和 Using index)

Using index condition：使用索引，但仅靠索引无法得出全部数据，需要进行回表查询(当 select * where A=xx，且 A 为非主索引的索引/联合索引最左时出现)