本文由读者小平同志投稿,小平是一位非常朴实认真的猿,现于某上市证券公司做微服务开发,对 MySQL 优化有深入研究,小平的博客地址是https://blog.csdn.net/weixin_41193109。
MySQL的索引对查询速度的提高非常明显,但是索引种类很多,如复合索引、单列索引,那它们有什么区别和联系呢?下面我会对两者进行分析。
先来准备一张表和一点测试数据:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `age` int(4) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `sex` int(3) DEFAULT NULL, `nickname` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`), KEY `testKey` (`name`,`age`,`nickname`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('1', '20', 'test1', '1', 'ntest1');INSERT INTO `user` VALUES ('2', '21', 'test2', '0', 'ntest2');INSERT INTO `user` VALUES ('3', '24', 'test1', '1', 'ntest3');INSERT INTO `user` VALUES ('4', '23', 'test4', '0', 'ntest4');INSERT INTO `user` VALUES ('5', '24', 'test5', '1', 'ntest5');INSERT INTO `user` VALUES ('6', '25', 'test6', '0', 'ntest6');
通过上面表格,我们会发现,复合索引( name
, age
, nickname
)和它们三列的单个索引是有区别的(该案例不做复合索引和单列索引的性能分析)主要区别有以下几点:
name
, age
, nickname
)相当于 name
索引、( name
, age
)索引以及( name
, age
, nickname
) 索引(注意,后面两个索引不能再按复合索引算,只是为了解释说明)。age=21andname='test1'
一样会触发复合索引(MySQL会对查询条件顺序进行优化,我们无需担心顺序问题,但是为了更好理解,建议合理安排顺序)。上面表格中,第一行和第二行都走了索引,但是第一行是相连的两列,rows是1,这里我们可以说是使用了( name
, age
)索引(该索引并发真实存在,只是为了区分效果);第二行是不相连的两列rows是2,然后第四行是使用了复合索引的第一列 name 和非复合索引中的列作为查询条件,rows 同样是2,非相连的两列作为查询条件时,复合索引相当于使用了第一列作为查询条件。
为什么会这样呢?MySQL 在进行查询时,会根据索引筛选出复合索引的行,如果存在查询条件不在索引中的列,会进行二次筛选(即根据筛选出来的行进行二次查询),导致遍历的行数增加。
部分查询条件会导致全表扫描
特殊注意:
在我们使用单列索引和复合索引时,需要注意以下几点:
select*
或许性能和指定字段相差不是非常大,但是代码的可读性降低了很多,不推荐使用。