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

在维基百科没找到联合索引比较官方的定义。
通俗的解释:在某一列上加索引以提升相关语句查询效率,联合索引就是在多个列上加索引。
联合索引
唯一性问题
执行以下语句新建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;
执行语句,索引创建成功:

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

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

联合索引会在有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列上建立索引;那现在在username和delete_at两个字段上建立了联合索引,在执行以下语句的时候是否会有性能问题呢?
SELECT * FROM test WHERE username = 'admin';
用EXPLAIN分析以下这个语句,发现该语句的Extra为Using index,说明本次查询是走索引的,并且就是联合索引UK2lcfdkjf5bcn2bs7kfwyjhr91。

索引其实就是使用B+树进行排序,联合索引就是根据多个字段进行排序。
比如这里的username与delete_at的联合索引,先对username进行排序,username相同时,对delete_at进行排序,所以username绝对有序,delete_at相对有序。
所以根据username查询,完全可以使用已经绝对有序的联合索引,而delete_at理论上就不能使用该索引。
总结
EXPLAIN工具在数据库学习与优化过程中很重要,推荐学习:MySQL 性能优化神器 Explain 使用分析 - segmentfault

评论
登录后参与技术讨论,分享你的经验与见解。
登录 / 注册暂无评论