MySQL联合索引以及索引顺序优化

1. 环境准备

-- 查看版本 ,5.7.19-17-log
select VERSION();

-- 创建表结构
CREATE TABLE `order_demo` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `B2C_ORD_ID` varchar(50) DEFAULT NULL COMMENT '销售订单号',
  `ORDER_NO` varchar(50) NOT NULL COMMENT '订单号',
  `SKU_ID` varchar(50) DEFAULT NULL COMMENT '商品ID',
  `SKU_AMOUNT` decimal(14,2) DEFAULT NULL COMMENT '商品金额',
  `SKU_NUM` decimal(14,0) DEFAULT NULL COMMENT '商品数量',
  `COUNT_DATE` datetime DEFAULT NULL COMMENT '点击时间',
  `COMMIT_DATE` datetime DEFAULT NULL COMMENT '下单时间',
  `THROW_SOURCE` varchar(10) DEFAULT NULL COMMENT '投放终端/推广设备 P.PC端，W.无线',
  `PROMOTION_ID` varchar(100) DEFAULT NULL COMMENT '推广计划ID',
  `PROMOTION_NAME` varchar(255) DEFAULT NULL COMMENT '推广计划名称',
  `PROMOTION_UNIT_ID` varchar(100) DEFAULT NULL COMMENT '推广计划ID',
  `PROMOTION_UNIT_NAME` varchar(255) DEFAULT NULL COMMENT '推广单元名称',
  `PROMOTION_CHANNEL` int(2) DEFAULT NULL COMMENT '推广渠道	1.搜索广告位	2.推荐广告位',
  `PROVINCE` varchar(50) DEFAULT NULL COMMENT '省份',
  `CITY` varchar(50) DEFAULT NULL COMMENT '城市',
  `ORDER_TYPE` int(2) DEFAULT NULL COMMENT '订单类型:1.直接订单 2.间接订单',
  `ORDER_STATUS` int(4) DEFAULT NULL COMMENT '订单状态	1.提交 2.成交',
  `USER_ID` bigint(20) DEFAULT NULL COMMENT '查询用户ID',
  `ISACTIVE` int(4) DEFAULT NULL COMMENT '是否有效	0.失效 1.有效',
  `IS_GIFT` int(2) DEFAULT NULL COMMENT '是否赠品 (0 非赠品 1 赠品)',
  `IS_USE_COUPON` int(2) DEFAULT NULL COMMENT '是否用券 (0 未用券 1 用券)',
  `COUPON_AMOUNT` decimal(14,2) DEFAULT NULL COMMENT '用券金额',
  PRIMARY KEY (`ID`),
  KEY `index_unit_id` (`PROMOTION_UNIT_ID`),
  KEY `index_count_date` (`COUNT_DATE`),
  KEY `index_promotion_id` (`PROMOTION_ID`),
  KEY `index_user_id` (`USER_ID`),
  KEY `idx_USER_ID_COUNT_DATE` (`USER_ID`,`COUNT_DATE`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;

2. 测试验证

-- 添加联合索引
ALTER TABLE `order_demo` add index `idx_USER_ID_COUNT_DATE` (`USER_ID`,`COUNT_DATE`); 
-- 删除索引
ALTER TABLE order_demo DROP INDEX `idx_USER_ID_COUNT_DATE`;

-- SQL_ID:4972193622039785254
SELECT COUNT(1)
FROM order_demo T
WHERE T.USER_ID =428964304
  AND T.COUNT_DATE >='2020-11-30'
  AND T.COUNT_DATE < DATE_ADD('2020-12-06',INTERVAL 1 DAY);

-- 添加联合索引前，表原来没有联合索引，只有单列索引COUNT_DATE，USER_ID
SELECT COUNT(1) FROM order_demo T WHERE T.USER_ID =428964304 AND T.COUNT_DATE >='2020-11-30' AND T.COUNT_DATE < DATE_ADD('2020-12-06',INTERVAL 1 DAY);

id   select_type table 	partitions 	type 		possible_keys 							 key key_len ref 	rows 	filtered 	Extra 
1			SIMPLE  		T  		null     		ALL		index_count_date,index_user_id 	         				5     100			Using where

总结：没走索引全表扫描

-- 添加联合索引后，添加联合索引(`USER_ID`,`COUNT_DATE`)
SELECT COUNT(1) FROM order_demo T WHERE T.USER_ID =428964304 AND T.COUNT_DATE >='2020-11-30' AND T.COUNT_DATE < DATE_ADD('2020-12-06',INTERVAL 1 DAY);

id   select_type table 	partitions 	type 		possible_keys 																				key 										key_len ref 	rows 	filtered 	Extra 
1			SIMPLE  		T  		null     		rang		index_count_date,index_user_id,idx_USER_ID_COUNT_DATE idx_USER_ID_COUNT_DATE	15       NULL  5     100			Using where; Using index

总结：使用到了联合索引idx_USER_ID_COUNT_DATE

-- 验证联合索引顺序问题，`idx_USER_ID_COUNT_DATE` (`USER_ID`,`COUNT_DATE`)，查询条件是`USER_ID`,`COUNT_DATE`
SELECT COUNT(1) FROM order_demo T WHERE T.USER_ID =428964304 AND T.COUNT_DATE >='2020-11-30' AND T.COUNT_DATE < DATE_ADD('2020-12-06',INTERVAL 1 DAY);

id   select_type table 	partitions 	type 		possible_keys 																				key 										key_len ref 	rows 	filtered 	Extra 
1			SIMPLE  		T  		null     		rang		index_count_date,index_user_id,idx_USER_ID_COUNT_DATE idx_USER_ID_COUNT_DATE	15       NULL  5     100			Using where; Using index

总结：条件语句与联合索引顺序一致，rang，走了该联合索引。

-- 验证联合索引顺序问题，`idx_USER_ID_COUNT_DATE` (`USER_ID`,`COUNT_DATE`)，查询条件是`COUNT_DATE`,`USER_ID`
SELECT COUNT(1) FROM order_demo T WHERE T.COUNT_DATE >='2020-11-30' AND T.COUNT_DATE < DATE_ADD('2020-12-06',INTERVAL 1 DAY) AND T.USER_ID =428964304;

id   select_type table 	partitions 	type 		possible_keys 																				key 										key_len ref 	rows 	filtered 	Extra 
1			SIMPLE  		T  		null     		rang		index_count_date,index_user_id,idx_USER_ID_COUNT_DATE idx_USER_ID_COUNT_DATE	15       NULL  5     100			Using where; Using index

总结：条件语句与联合索引顺序颠倒，rang，走了该联合索引。与条件顺序一致的执行计划完全一样，说明mysql拿到sql语句并不是立即执行，而是优化后执行！！！
			仅限于5.7.19版本测试，其他版本未测试不做评论。

-- 验证联合索引顺序问题，`idx_USER_ID_COUNT_DATE` (`USER_ID`,`COUNT_DATE`)，查询条件是`COUNT_DATE`
SELECT COUNT(1) FROM order_demo T WHERE T.COUNT_DATE >='2020-11-30' AND T.COUNT_DATE < DATE_ADD('2020-12-06',INTERVAL 1 DAY);

id   select_type table 	partitions 	type 		possible_keys 	 key 						key_len  ref 	rows 	filtered 	Extra 
1			SIMPLE  		T  		null     		rang		index_count_date idx_COUNT_DATE		6      NULL  5     100			Using where; Using index

总结：走单列索引

-- 验证联合索引顺序问题，`idx_USER_ID_COUNT_DATE` (`USER_ID`,`COUNT_DATE`)，查询条件是`USER_ID`
SELECT COUNT(1) FROM order_demo T WHERE T.USER_ID =428964304;

id   select_type table 	partitions 	type 		possible_keys 	 												key 				key_len  ref 	rows 	filtered 	Extra 
1			SIMPLE  		T  		null     		rang		index_user_id,idx_USER_ID_COUNT_DATE index_user_id		9      NULL  14     100			Using index

总结：走单列索引