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

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

复合/联合索引

复合索引指将多个字段联合创建一个索引，复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引；如下表中idcard_name为复合索引/联合索引，如果我们在查询idcard和created_at时候，会查询idcard_name索引和对查询出来的结果对created_at全表扫描，但是使用name和created_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%'会使用索引。

参考：

• MySQL单列索引和组合索引的区别介绍
• SQL查询优化联合索引 与 单一列的索引