[数据库优化-查询优化] 创建索引以及单一/复合索引区别与使用场景分析

Song3061 次浏览0个评论2018年06月02日

我们在大数据库查询优化过程中,常常接触到创建索引,也会接触到加速查询过程,比如where a="1"where a='1' and b='2'这样的查询优化,索引我们结合总结什么情况下索引单一索引,什么情况下使用复合索引以及优缺点总结。

复合/联合索引

复合索引指将多个字段联合创建一个索引,复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引;如下表中idcard_name为复合索引/联合索引,如果我们在查询idcardcreated_at时候,会查询idcard_name索引和对查询出来的结果对created_at全表扫描,但是使用namecreated_at查询不会使用索引,因为mysql的索引是最左前缀原则,最左边的索引先匹配,后面的可以不用匹配,索引index(key1,key2)相当于包括key1单独索引。

CREATE TABLE `orders` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(35) DEFAULT '1' COMMENT '姓名',
  `idcard` varchar(20) DEFAULT '1' COMMENT '身份证',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态',
  `created_at` int(11) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idcard_name` (`idcard`,`name`),
  KEY `status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

注意
对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高;select * from table1 where col1=A AND col2=B AND col3=D,如果使用where col2=B AND col1=A或者where col2=B将不会使用索引。

单一索引

将表中一个字段单独存储,如上表中status为单一索引,单一索引使用比较灵活,同时对查询优化比较大。但是在多条件查询下性能略逊色于复合索引。

单一索引和复合索引的优缺点

  • 索引所占用空间:单一列索引相对要小。
  • 索引创建时间:单一列索引相对短。
  • 索引对insert,update,delete的影响程序:单一列索引要相对低。
  • 在多条件查询时,联合索引效率要高。
  • 索引的使用范围:单一列索引可以出现在where条件中的任何位置,而联合索引需要按一定的顺序来写。

单一索引和复合索引的选择场景

如果常常需要查询指定字段的信息,且该字段数据重复不严重(如DISTINCT后数据占比小)可以创建单一索引,如:身份证,电话,订单号等字段;

  • 由于单一索引比较灵活,性能提升比较明显。建议一般情况下使用单一索引
  • 如果两个/多个字段关联度较低,复合索引优势不明显,可以使用单列索引
  • 如果两个/多个字段关联度较高,复合索引优势明显,可以使用复合索引,比如:身份证和姓名(因为身份证只能对应一个姓名)
  • 如果SQL查询中使用OR语法,使用单一索引,因为复合索引会进行全表扫描
  • 复合索引中只要有一列含有NULL值,那这一列对于此复合索引就是无效的

常见问题解析

  • 单一索引和复合索引同时存在:有复合索引on(col1,col2),就没有必要同时建立一个单索引on col1;如果索引满足窄索引的情况下可以建立复合索引,这样可以节约空间和时间
  • 复合索引命名:复合索引可以使用_来联合命名,如:name_phone_idcard
  • 索引字段不是越多越好,窄索引性能远远高于宽索引(同时操作表的性能也是影响较大)
  • 索引大大提高了查询速度,却会降低INSERT/UPDATE/DELETE的速度。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
  • MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
  • 不推荐使用like操作。like '%xxx'或'%xxx%'不会使用索引而like 'aaa%'会使用索引。

参考:

提交评论

请登录后评论

用户评论

    当前暂无评价,快来发表您的观点吧...

更多相关好文

    当前暂无更多相关好文推荐...