数据库 mysql报表sql联合索引以及索引顺序优化

Dear 丶 2022-12-27 04:54 126阅读 0赞

## 数据库 mysql报表sql联合索引以及索引顺序优化 ##

1.  表结构

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;

1.  版本

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

1.  验证sql及总结

-- 添加联合索引
    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
    
    总结：走单列索引