MYSQL单列索引和组合索引的对比分析
单列索引:即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。
组合索引:即一个索包含多个列。
怎么选择:
- 如果查询where条件只有一个,完全可以用单列索引,这样的查询速度较快,索引也比较瘦身。
- 如果业务场景是需要经常查询多个组合列,不要试图分别基于单个列建立多个单列索引(因为虽然有多个单列索引,但是MySQL只能用到其中的那个它认为似乎最有效率的单列索引)。这是因为当SQL语句所查询的列,全部都出现在复合索引中时,此时由于只需要查询索引块即可获得所有数据,当然比使用多个单列索引要快得多。
- MySQL优化器使得一条查询语句只使用一个限制最严格的索引,只用一个索引的基础上,联合索引要比单例索引快。
week_item是星期,begin_time是开始时间,end_time是结束时间,如果现在表中存在10000条记录,要查询where week_item = 6
week_item上增加单列索引,直接就返回结果集,那么此时的查询效率是非常快
更换搜索条件:where week_item = 6 and begin_time = '09:00' and end_time = '10:00'
由于建立了week_item列的索引,与执行表的完全扫描相比,查询效率提高了很多,但要求MySQL扫描的记录数量仍旧远远超过了实际所需要的。所以对剩下的结果集还是需要进行全部扫描比较,那如果此时在begin_time和 end_time上再增加单列索引,那么这就涉及到上面说的了:虽然有多个单列索引,但MySQL只能用到其中的那个它认为似乎最有效率的单列索引,也就是说只会用到一个索引。
为了提高搜索效率,需要考虑运用多列索引。如果为week_item 、begin_time 和end_time 这三个列创建一个多列索引,MySQL只需一次检索就能够找出正确的结果。
如果在week_item、begin_time、end_time 这三个列上分别创建单列索引,效果是否和创建一个week_item、begin_time、end_time的多列索引一样呢?答案是否定的,两者完全不同。
当执行查询的时候,MySQL只能使用一个索引。如果有三个单列的索引,MySQL会试图选择一个限制最严格的索引。但是,即使是限制最严格的单列索引,它的限制能力也肯定远远低于week_item、begin_time、end_time这三个列上的多列索引。
注意事项:
- 建立联合索引,谨防最左前缀索引失效问题;
- 只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为NULL;
- 对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作;
- MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要,最好给这些列创建复合索引。
- 一般情况下不鼓励使用like操作;
- 不要在列上进行运算,会导致索引失效;
- 不使用NOT IN和<>操作。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」