MySQL的索引类型介绍以及在不同情况下的应用

索引

索引（键 key）是存储引擎用于快速找到记录的一种数据结构

索引的优点：

1.减少了服务器需要扫描的数据量
2.避免排序和临时表
3.随机IO变顺序IO

索引的类型

B-Tree索引

是大多数存储引擎默认的索引

B-Tree叶子节点是有序的，所以适合范围查找和排序

B-Tree索引适合全键查找，范围查找和前缀查找

前缀查找必须是最左前缀，不能跳过前面的列而使用后面列的索引，比如索引列为(A, B, C)，如果想单独查找B，则无法使用索引

InnoDB的B-Tree索引是聚簇索引，分为主索引和辅助索引，只有主索引的叶子节点存储数据，而辅助索引的叶子节点记录的是主键的值，所以要进行辅助索引查找时需要先查主键值，再到主索引中查找，这就意味着所有的索引查找都要用到主索引，因此主索引不能太大

哈希索引

为每一行数据针对索引列计算一个哈希码，将哈希码保存在索引中，并保存指向每行数据的指针

优点：

哈希索引只存储哈希码，所以结构紧凑，查找也非常快

缺点：

哈希索引不适用于排序
哈希索引每次查找必须用到全部的索引列，比如索引列是(A, B)，只查找A是无法使用索引的
哈希索引只适用于等值比较查询，不适用于范围查询

InnoDB有一个自适应哈希索引的功能，当某一个索引值被频繁使用时，会在内存中B-Tree索引之上再建立一个哈希索引，实现快速查找

全文索引

用于查找文本中的关键词

可以在同一个列上创建全文索引和B-Tree索引

空间数据索引

MyISAM存储引擎所支持，在多列索引时不必左前缀，可以用任意维度来查找

索引优化

独立的列

在使用索引时，索引列必须是独立的，而不能在表达式中

比如

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

就无法使用actor_id列的索引

多列索引

要为多个列共同创建索引，而不是为每个列创建单独的索引

在使用多列索引时，按照最左匹配原则，应该将选择性（不重复的索引值和记录总数的比值）最高的列放在最左边

前缀索引

对于BLOB，TEXT和VARCHAR类型的列，由于长度比较大，使用全部值作为索引会占用很大空间

此时应该只对每一行的前几个字符进行索引，字符数应该尽量小且具有足够的选择性

聚簇索引

InnoDB的聚簇索引将索引和数据存放在B-Tree的叶子节点中

优点：

数据访问快
数据的保存位置相邻，少量的数据页就能得到全部的某项数据

缺点：

插入速度依赖于插入顺序，如果不是按照主键顺序插入就很慢
更新的代价很高，会导致行移动到新的位置
可能导致页分裂，占用更多空间，令全表扫描变慢
二级索引包含主键列，且需要查找两次

覆盖索引

当索引包含所要查找的字段，称其为覆盖索引

在索引中直接找到内容，无需回表，访问数据量更少，速度更快

实际应用中的索引

参考

单列索引

首先创建一个表：

create table idx_study 
(a varchar(20), 
b varchar(20), 
c varchar(20), 
value varchar(20), 
index idx_a(a(20)), 
index idx_b(b(20)), 
index idx_c(c(20))
);

表结构如下所示：

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | varchar(20) | YES  | MUL | NULL    |       |
| b     | varchar(20) | YES  | MUL | NULL    |       |
| c     | varchar(20) | YES  | MUL | NULL    |       |
| value | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

单独使用单列索引

单独使用a, b, c作为条件查询都可以使用索引

explain select * from idx_study where a='1';

+------+-------------+-----------+------+---------------+-------+---------+-------+------+-----------------------+
| id   | select_type | table     | type | possible_keys | key   | key_len | ref   | rows | Extra                 |
+------+-------------+-----------+------+---------------+-------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | idx_study | ref  | idx_a         | idx_a | 63      | const | 1    | Using index condition |
+------+-------------+-----------+------+---------------+-------+---------+-------+------+-----------------------+

组合使用单列索引

如果用and组合，则MySQL根据优化策略选择其中某一个单列索引

explain select * from idx_study where a='1' and b='2' and c='3';

+------+-------------+-----------+------+-------------------+-------+---------+-------+------+------------------------------------+
| id   | select_type | table     | type | possible_keys     | key   | key_len | ref   | rows | Extra                              |
+------+-------------+-----------+------+-------------------+-------+---------+-------+------+------------------------------------+
|    1 | SIMPLE      | idx_study | ref  | idx_a,idx_b,idx_c | idx_a | 63      | const | 1    | Using index condition; Using where |
+------+-------------+-----------+------+-------------------+-------+---------+-------+------+------------------------------------+

如果使用or组合，则不能使用索引

explain select * from idx_study where a='1' or b='2';

+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | idx_study | ALL  | idx_a,idx_b   | NULL | NULL    | NULL | 1    | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+

联合索引

创建一个表：

create table union_study 
(`a` varchar(20), 
`b` varchar(20), 
`c` varchar(20), 
`value` varchar(20), 
KEY `union` (`a`, `b`, `c`)
);

由于最左前缀原则，只在联合索引的第一列标识出了索引：

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | varchar(20) | YES  | MUL | NULL    |       |
| b     | varchar(20) | YES  |     | NULL    |       |
| c     | varchar(20) | YES  |     | NULL    |       |
| value | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

使用联合索引

要想使用联合索引，必须带上第一列（最左前缀原则）

单独使用a（可以使用索引）

explain select * from union_study where a='1';

+------+-------------+-------------+------+---------------+-------+---------+-------+------+-----------------------+
| id   | select_type | table       | type | possible_keys | key   | key_len | ref   | rows | Extra                 |
+------+-------------+-------------+------+---------------+-------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | union_study | ref  | union         | union | 63      | const | 1    | Using index condition |
+------+-------------+-------------+------+---------------+-------+---------+-------+------+-----------------------+

单独使用b（不能使用索引）

explain select * from union_study where b='1';

+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | union_study | ALL  | NULL          | NULL | NULL    | NULL | 1    | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+

单独使用c（不能使用索引）

explain select * from union_study where c='1';

+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | union_study | ALL  | NULL          | NULL | NULL    | NULL | 1    | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+

同时使用a, b, c（可以使用索引）

explain select * from union_study where b='1' and a='1' and c='1';

注意and连接中三个索引需要安照最左前缀匹配

对于varchar类型，key_len计算方法是len*3+3，可以通过key_len的大小来判断使用了几列索引，也可以查看ref来判断

+------+-------------+-------------+------+---------------+-------+---------+-------------------+------+-----------------------+
| id   | select_type | table       | type | possible_keys | key   | key_len | ref               | rows | Extra                 |
+------+-------------+-------------+------+---------------+-------+---------+-------------------+------+-----------------------+
|    1 | SIMPLE      | union_study | ref  | union         | union | 189     | const,const,const | 1    | Using index condition |
+------+-------------+-------------+------+---------------+-------+---------+-------------------+------+-----------------------+

跳过b，使用a和c

explain select * from union_study where a='1' and c='1';

key_len为63，说明只有a列用到了索引，c没有用到

+------+-------------+-------------+------+---------------+-------+---------+-------+------+-----------------------+
| id   | select_type | table       | type | possible_keys | key   | key_len | ref   | rows | Extra                 |
+------+-------------+-------------+------+---------------+-------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | union_study | ref  | union         | union | 63      | const | 1    | Using index condition |
+------+-------------+-------------+------+---------------+-------+---------+-------+------+-----------------------+

使用or连接（不能使用索引）

explain select * from union_study where a='1' or b='1' or c='1';

+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | union_study | ALL  | union         | NULL | NULL    | NULL | 1    | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+

联合索引中的范围查询

在联合索引中使用范围查询也要按照最左匹配原则：一旦遇到范围查询，右边的所有列都不能使用索引，所以要想后面的列使用到索引，左边的列必须是等于

例1：

explain select * from union_study where a > '0' and b = '1';

由于a列是范围查询，b就不能用到索引，key_len为63，只用到a列的索引

+------+-------------+-------------+-------+---------------+-------+---------+------+------+-----------------------+
| id   | select_type | table       | type  | possible_keys | key   | key_len | ref  | rows | Extra                 |
+------+-------------+-------------+-------+---------------+-------+---------+------+------+-----------------------+
|    1 | SIMPLE      | union_study | range | union         | union | 63      | NULL | 1    | Using index condition |
+------+-------------+-------------+-------+---------------+-------+---------+------+------+-----------------------+

例2：

explain select * from union_study where a = '0' and b > '1';

a列是等于匹配，所以后面的列可以使用索引

+------+-------------+-------------+-------+---------------+-------+---------+------+------+-----------------------+
| id   | select_type | table       | type  | possible_keys | key   | key_len | ref  | rows | Extra                 |
+------+-------------+-------------+-------+---------------+-------+---------+------+------+-----------------------+
|    1 | SIMPLE      | union_study | range | union         | union | 126     | NULL | 1    | Using index condition |
+------+-------------+-------------+-------+---------------+-------+---------+------+------+-----------------------+

例3：

explain select * from union_study where a = '0' and b > '1' and c = '2';

a列是等于匹配，所以后面的列可以使用索引，但b列是范围查询，所以后面用不到索引，该语句只用到了a列和b列，key_len为126

+------+-------------+-------------+-------+---------------+-------+---------+------+------+-----------------------+
| id   | select_type | table       | type  | possible_keys | key   | key_len | ref  | rows | Extra                 |
+------+-------------+-------------+-------+---------------+-------+---------+------+------+-----------------------+
|    1 | SIMPLE      | union_study | range | union         | union | 126     | NULL | 1    | Using index condition |
+------+-------------+-------------+-------+---------------+-------+---------+------+------+-----------------------+

总结

要想使用联合索引，必须带上第一列
使用or连接时不能使用联合索引和单列索引
联合索引中一旦遇到范围查询，后面的列就不能使用索引

PREVIOUSDocker应用中的一些问题记录

NEXT浮点数的奥秘