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和<>操作。
posted @   李若盛开  阅读(562)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」
点击右上角即可分享
微信分享提示