索引

# 索引

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。

一张表的每个字段都可以添加索引，一个字段可以添加一个索引。当然，多个字段联合起来也可以添加索引。

索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

提示

在任何数据库中，主键和 unique 约束的字段都会自动添加索引。
在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
在 mysql 中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在myISAM存储引擎中，索引存储在一个.MYI 文件中。在 InnoDB 存储引擎中，索引存储在一个逻辑名称为 tablespace 的文件当中。在 MEMEORY 存储引擎当中索引被存储在内存当中。不管存储在哪里，索引在 mysql 中都是一个树的形式存在。(自平衡二叉树)

查字典案例：

方式一：一页一页挨着找，效率极低，这也叫做全扫描方式。

方式二：先通过目录去定位一个大概位置，然后直接定位到这个位置，做全局性扫描，缩小扫描范围，快速的查找，提高效率。

select * from student where name = '王维';
上面的查询如果 name 字段上没添加索引，那么就是全扫描。会挨着查找 name 字段上所有数据。效率极低。

1
2

# 索引的实现原理

索引实现原理：缩小范围避免全表扫描

mysql 查询数据就两种方式: 一种是全表扫描，一种是根据索引检索。

索引或者说目录一般都需要排序。这样查找时就可以查找某个区间，缩小扫描范围。

这个排序和 TreeSet 数据结构相同， TreeSet (TreeMap) 底层是一个自平衡的二叉树。 mysql 中的索引是一个 B-Tree 数据结构。

# 什么时候加索引

数据量庞大(按照硬件配置来动态确定)
添加索引的字段经常出现在 where 后面，以条件的形式存在，也就是说这个字段总被扫描。
该字段很少的 DML 操作，因为 DML 之后索引需要重新排序。

注意

不要随意添加索引，较多的索引会降低性能。

# 创建索引

mysql> create index emp_name_index on employ(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

1
2
3

# 删除索引

mysql> drop index emp_name_index on employ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

1
2
3

# 如何查看 SQL 语句是否使用索引进行检索

mysql> explain select * from employ where name =  '王维';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employ | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

rows = 7  type = all 说明未使用索引

1
2
3
4
5
6
7
8
9

mysql> create index emp_name_index on employ(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from employ where name =  '王维';
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys  | key            | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | employ | ref  | emp_name_index | emp_name_index | 183     | const |    2 | Using where |
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

rows = 2 type = ref 说明使用了索引

1
2
3
4
5
6
7
8
9
10
11
12
13

# 索引失效

模糊查询时以 % 开头会导致索引失效。

mysql> create index emp_name_index on employ(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from employ where name like  '%维';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employ | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
索引失效

1
2
3
4
5
6
7
8
9
10
11
12

使用 or 的时候会失效，如果使用 or 那么要求 or 两边的条件字段都要有索引，才会走索引，如果其中以便有一个字段没有索引，那么另一个字段上的索引也会失效。所以就建议少用 or。

mysql> explain select * from employ where name =  '王维' or name = '李白';
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys  | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employ | ALL  | emp_name_index | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
索引失效

mysql> explain select * from employ where name =  '王维' or age = 23;
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys  | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employ | ALL  | emp_name_index | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
索引失效

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

使用复合索引的时候，没有使用左侧的列查找，索引失效。

两个或更多个字段联合起来添加一个索引，叫做复合索引。

mysql> create index emp_age_salary_index on employ(age,salary);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from employ where age =20;
+----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys        | key                  | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | employ | ref  | emp_age_salary_index | emp_age_salary_index | 5       | const |    1 | Using where |
+----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
使用了左侧的列 age ，所以索引有效。

mysql> explain select * from employ where salary =90;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employ | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
未使用左侧列 age ，所以索引无效。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

在 where 中索引列参加了运算，索引失效。

mysql> create index emp_salary_index on employ(salary);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from employ where salary =90;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | employ | ref  | emp_salary_index | emp_salary_index | 9       | const |    1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
索引有效

mysql> explain select * from employ where salary+1 =90;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employ | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
列参加加法运算，索引无效

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

在 where 中索引列使用了函数

mysql> explain select * from employ where lower(name) = '王维';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employ | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

# 索引的分类

索引是各种数据库优化的重要手段。优化的时候优先考虑的因素就是索引。

索引分类如下：

单一索引：一个字段添加索引
复合索引：两个或多个字段添加索引
主键索引：主键上添加索引
唯一性索引：具有 unqiue约束的字段上添加的索引

编辑

#MYSQL

上次更新: 2025/02/15, 13:42:25

← 事务视图→