MySQL 联合索引

引言

用户表软删除,要保证手机号唯一且与已删除的用户不冲突,在数据库层面设置了UNIQUE联合索引。

image.png

在维基百科没找到联合索引比较官方的定义。

通俗的解释:在某一列上加索引以提升相关语句查询效率,联合索引就是在多个列上加索引。

联合索引

唯一性问题

执行以下语句新建test表用于演示:

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 NOT NULL,
  `delete_at` bigint,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK2lcfdkjf5bcn2bs7kfwyjhr91` (`username`,`delete_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

执行语句,索引创建成功:

image.png

去表中新建两条数据为1-admin-102-admin-10,点击保存,会提示重复的admin-10违反了UK2lcfdkjf5bcn2bs7kfwyjhr91这个索引的UNIQUE约束,唯一索引生效。

image.png

NULL 问题

当把delete_at一列删除为NULL时,两条数据却可以保存成功。

image.png

联合索引会在有NULL值的情况下失效,所以应该避免联合索引中的字段值为NULL

完善的建表语句如下:

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 NOT NULL,
  `delete_at` bigint DEFAULT 0 NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK2lcfdkjf5bcn2bs7kfwyjhr91` (`username`,`delete_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

最左匹配原则

为了提高查询效率,过去一直在username列上建立索引;那现在在usernamedelete_at两个字段上建立了联合索引,在执行以下语句的时候是否会有性能问题呢?

SELECT * FROM test WHERE username = 'admin';

EXPLAIN分析以下这个语句,发现该语句的ExtraUsing index,说明本次查询是走索引的,并且就是联合索引UK2lcfdkjf5bcn2bs7kfwyjhr91

image.png

索引其实就是使用B+树进行排序,联合索引就是根据多个字段进行排序。

比如这里的usernamedelete_at的联合索引,先对username进行排序,username相同时,对delete_at进行排序,所以username绝对有序,delete_at相对有序。

所以根据username查询,完全可以使用已经绝对有序的联合索引,而delete_at理论上就不能使用该索引。

总结

EXPLAIN工具在数据库学习与优化过程中很重要,推荐学习:MySQL 性能优化神器 Explain 使用分析 - segmentfault

阅读 363

推荐阅读
Tomorrow
用户专栏

明天,你好

317 人关注
137 篇文章
专栏主页