MySQL索引

# 索引概述

索引是帮助MySQL高效获取数据的数据结构。这些数据结构以某种方式指向数据，可以在这些数据结构上实现高级查找算法。
在无索引情况下，查询数据需要从第一行开始扫描，直到最后一行，称之为全表扫描，数据量大时性能很低。
虽然索引可以提高查询性能，但过多或不正确使用索引可能会导致性能下降。

# 索引分类

按功能划分：

名称	含义	关键字
主键索引	唯一、不为NULL，只能有一个主键索引	PRIMARY
唯一索引	唯一、可以为NULL，可以有多个唯一索引	UNIQUE
普通索引	可以重复、可以为NULL，可以有多个普通索引
全文索引	用于全文检索	FULLTEXT
空间索引	不为NULL	SPATIAL

按存储形式划分：
聚簇索引，将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，有且只有一个。
非聚簇索引，将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以有多个。

# 索引管理

# 创建索引
CREATE INDEX 索引名称 on 表名(列名);
CREATE UNIQUE | FULLTEXT INDEX 索引名称 ON 表名(列名)  // 添加唯一/全文索引
create index index_birthday on tb_student(birthday);

# 查看索引
SHOW INDEX FROM 表名;
show index from tb_student;

# 删除索引
DROP INDEX 索引名 on 表名;
drop index index_birthday on tb_student;

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

# 索引建立原则

针对于数据量较大，且查询比较频繁的表建立索引，数据量小的表最好不要建立索引。
避免对经常更新的表进行过多的索引，不仅占用磁盘空间，而且会影响新增、删除、修改的性能。
选择适当的索引列，针对于常作为查询条件（where）、连接(join)、排序（order by）、分组（group by）操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高(区分度是不同值的数量与总行数的比率)。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，确保将最常用的列放在最左侧，范围查询索引字段放在最后，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

# 索引使用

# 最左前缀原则

多列索引(联合索引)遵循最左前缀原则，从索引最左列开始，且不能跳过索引中的列，如果跳过某列将会部分索引失效(后面索引失效)。

# 联合索引: a, b, c
# 生效情况
ORDER BY a,
ORDER BY a, b
ORDER BY a, b, c 
ORDER BY a DESC, b DESC, c DESC 

WHERE a = ''
WHERE a = '' AND b = ''
WHERE a = '' AND b = '' AND c = ''

WHERE a = const ORDER BY b, c
WHERE a = const and b = const ORDER BY c
WHERE a = const and b > const ORDER BY b, c

# 失效情况
ORDER BY a, b desc, c desc # 排序不一致
WHERE b = const ORDER BY b, c # a丢失
WHERE a = const ORDER b, d # 部分失效,d不是索引的一部分
WHERE a in (...) ORDER BY b, c # a范围查询

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

注意事项

最左前缀原则，是指最左边的列必须存在，与编写先后顺序无关。
WHERE b = '' AND a = '' AND c = ''

# 覆盖索引

覆盖索引是指查询使用了索引，并且要返回的列，在该索引中能全部找到。

# sno,name为索引列,查询数据不需要回表查询
SELECT id, sno, name FROM WHERE sno = '' AND name = '';
  
# card_no不是索引,需要回表查询
SELECT id, sno, name, card_no FROM WHERE sno = '' AND name = '';

1
2
3
4
5

# 范围查询

# 范围查询 >, < ,范围查询右侧的列索引失效
WHERE a = '' AND b > const AND c = '';  # c列不走索引

# 范围查询>=, <=, 范围查询右侧的列索引有效
WHERE a = '' AND b >= const AND c = ''; # c列索引有效

1
2
3
4
5

# 索引失效情况

# 索引列进行运算操作
WHERE SUBSTRING(sno,1,4) = '2021';  # sno学号列有索引,截取字符串操作后索引失效

# 字符串不加引号
WHERE sno = 2021005; # sno学号为字符串, 不加引号索引失效

# 左模糊查询
WHERE sno LIKE '%2021'; # sno学号左模糊查询,索引失效
WHERE sno LIKE '%2021%'; # sno学号全模糊查询,索引失效

# OR连接条件
WHERE sno = '2021001' OR card_no = '370207201504162912'; # sno学号为索引列,card_no证件号码不是索引列,索引失效

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

# 前缀索引

当需要索引很长的字符串时，会让索引变得很大，查询时，浪费磁盘IO，影响查询效率。可以只将字符串的一部分前缀建立索引，可以大大节约索引空间，提高索引效率。

# 语法
CREATE INDEX 索引名 ON 表名(列名(长度));

# 长度计算,不重复索引值/记录总数, 值越高查询效率越高,唯一索引的值是1.
SELECT COUNT(DISTINCT SUBSTRING(列名,1,长度)) / COUNT(*) FROM 表名;

1
2
3
4
5

#SQL教程

上次更新: 2023/11/22, 14:29:31

← MySQL数据类型 MySQL视图→