超详细 mysql 主键索引、唯一索引、普通索引、联合索引的区别与实例说明

文章目录

前言

我们很多项目都有可能会使用到mysql，当遇到表格数据量很庞大时，导致慢查询，查询效率就会特别低。
这时往往会采用索引来提高查询效率，但是盲目使用索引则极有可能达不到期望的效果。
因此，我们需要先理解，索引的功能区别，与使用索引的条件，以及索引的失效场景。

（1）功能区别

1 主键索引

主键也是一种索引，且是我们最常用的索引，使用方法为`primary key(ColumnList)`。
一张表只允许一个主键索引，且主键列的值不允许重复，不允许为空`NULL`。

2 唯一索引

使用方法是`unique index IndexName(ColumnList)`。
一张表格可以有多个唯一索引，唯一索引列的值不允许重复，但允许为空`NULL`。

3 普通索引

使用方式为`index IndexName(ColumnList)`
一张表格可以有多个普通索引，普通索引列的值允许重复，允许为空`NULL`。

4 联合索引

实际上，所有的索引都可以由多个字段列创建，这就叫联合索引。
但联合索引的功能区别也跟单列索引的功能区别一致。

（2）使用条件

1 索引列的数值

索引列、或者组成索引列的数值尽可能唯一。
唯一的数值索引，在查询的时候可以更快的定位数据记录。

2 数据类型选择

整型（int）的索引列查询效率要比字符串（char、varchar、text）高。

3 数据表复杂且量大

索引的创建会对应维护一张索引表，因此在采用索引时候应该是因为数据表复杂且量大。

（3）索引实例

1 创建索引（alter | create）

2 删除索引

3 查询效果

（a） explain 字段说明

标识符

explain

，常被我们用于测试操作语句效率情况。其中需要额外注意的是这些字段：

• type：指查询语句的查询范围，ALL是全表查找； - 其中查询效率，由快到慢依次是：const > eq_ref > ref > range > index > all。
• possible_keys：指查询语句可能会用到的索引列表，NULL表示没有使用索引；
• ref：指查询语句的关联的列，NULL是没有关联的列；
• rows：指有多少条记录被查询了；
• filtered：指查询的记录过滤的比例；
• Extra：指优化器查询记录的时候，采用的优化方式。 - 其中查询效率，由快到慢依次是：Using index > Using where > Using index condition > Using filesort > Using temporary，其他的额外信息并没有太大的差距，主要看查询语句的整体。

（b） 索引实例

• 不使用索引
• 使用单列索引
• 使用联合索引

（4）失效场景

1 创建索引失败

• 创建主键索引失败

主键索引，一张表只允许存在一个，且列的值不允许重复，不允许为空。
如果创建失败，多半是这三个条件有一个不满足。

• 创建唯一索引失败

唯一索引，一张表中允许存在多个，且列的值不允许重复，但允许为空。
如果创建失败，多半是列的值存在重复。

2 索引不生效

• 联合索引没有生效

联合索引，会采用左匹配的方式，activeUserID进行匹配时，会采用索引查找，效率依然很快；
但如果使用 tieToUserID进行匹配，则不会触发索引，而依然使用全表遍历，导致索引失效。