MySQL聯(lián)合索引以及索引順序優(yōu)化

• 1. 環(huán)境準(zhǔn)備
• 2. 測(cè)試驗(yàn)證

1. 環(huán)境準(zhǔn)備

-- 查看版本 ,5.7.19-17-log select VERSION();-- 創(chuàng)建表結(jié)構(gòu) CREATE TABLE `order_demo` (`ID` bigint(20) NOT NULL AUTO_INCREMENT,`B2C_ORD_ID` varchar(50) DEFAULT NULL COMMENT '銷售訂單號(hào)',`ORDER_NO` varchar(50) NOT NULL COMMENT '訂單號(hào)',`SKU_ID` varchar(50) DEFAULT NULL COMMENT '商品ID',`SKU_AMOUNT` decimal(14,2) DEFAULT NULL COMMENT '商品金額',`SKU_NUM` decimal(14,0) DEFAULT NULL COMMENT '商品數(shù)量',`COUNT_DATE` datetime DEFAULT NULL COMMENT '點(diǎn)擊時(shí)間',`COMMIT_DATE` datetime DEFAULT NULL COMMENT '下單時(shí)間',`THROW_SOURCE` varchar(10) DEFAULT NULL COMMENT '投放終端/推廣設(shè)備 P.PC端，W.無(wú)線',`PROMOTION_ID` varchar(100) DEFAULT NULL COMMENT '推廣計(jì)劃ID',`PROMOTION_NAME` varchar(255) DEFAULT NULL COMMENT '推廣計(jì)劃名稱',`PROMOTION_UNIT_ID` varchar(100) DEFAULT NULL COMMENT '推廣計(jì)劃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 '訂單狀態(tài) 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 '是否贈(zèng)品 (0 非贈(zèng)品 1 贈(zèng)品)',`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. 測(cè)試驗(yàn)證

-- 添加聯(lián)合索引 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 =428964304AND T.COUNT_DATE >='2020-11-30'AND T.COUNT_DATE < DATE_ADD('2020-12-06',INTERVAL 1 DAY);-- 添加聯(lián)合索引前，表原來沒有聯(lián)合索引，只有單列索引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總結(jié)：沒走索引全表掃描-- 添加聯(lián)合索引后，添加聯(lián)合索引(`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總結(jié)：使用到了聯(lián)合索引idx_USER_ID_COUNT_DATE-- 驗(yàn)證聯(lián)合索引順序問題，`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總結(jié)：條件語(yǔ)句與聯(lián)合索引順序一致，rang，走了該聯(lián)合索引。-- 驗(yàn)證聯(lián)合索引順序問題，`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總結(jié)：條件語(yǔ)句與聯(lián)合索引順序顛倒，rang，走了該聯(lián)合索引。與條件順序一致的執(zhí)行計(jì)劃完全一樣，說明mysql拿到sql語(yǔ)句并不是立即執(zhí)行，而是優(yōu)化后執(zhí)行！！！僅限于5.7.19版本測(cè)試，其他版本未測(cè)試不做評(píng)論。-- 驗(yàn)證聯(lián)合索引順序問題，`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總結(jié)：走單列索引-- 驗(yàn)證聯(lián)合索引順序問題，`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總結(jié)：走單列索引

總結(jié)

以上是生活随笔為你收集整理的MySQL联合索引以及索引顺序优化的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。