目標(biāo)
分析聯(lián)合唯一索引問題查詢sql的性能問題,執(zhí)行計(jì)劃結(jié)果含義學(xué)習(xí)可參考博客https://blog.csdn.net/da_guo_li/article/details/79008016
對(duì)于建立索引意見,參考博客
https://blog.csdn.net/wulex/article/details/69540136
背景
tbl_trx_order中有merchant_no住拭、request_no兩個(gè)字段作為聯(lián)合索引,ddl為
CREATE TABLE
tbl_trx_order
(
ID bigint NOT NULL AUTO_INCREMENT,
CREATE_TIME DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
LAST_UPDATE_TIME DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
REQUEST_NO VARCHAR(128) NOT NULL,
MERCHANT_NO VARCHAR(128) NOT NULL,
ORDER_AMOUNT DECIMAL(10,2) NOT NULL,
PAY_TOOL VARCHAR(32) NOT NULL,
EXPIRE_TIME INT,
ORDER_DATE DATETIME NOT NULL,
TRX_EXTERNAL_NO VARCHAR(128) NOT NULL,
PAY_EXTERNAL_NO VARCHAR(128),
TRX_STATUS VARCHAR(32) NOT NULL,
REFUND_AMOUNT DECIMAL(10,2) NOT NULL,
PRODUCT_NAME VARCHAR(128),
PRODUCT_DESC VARCHAR(128),
ORDER_SOURCE VARCHAR(64),
PAY_TYPE VARCHAR(64),
PAY_SCENE VARCHAR(64),
DISTRICT_INFO VARCHAR(64),
DEVICE_NO VARCHAR(64),
OPERATOR_NO VARCHAR(64),
SERVER_CALLBACK_URL VARCHAR(128),
REMARK VARCHAR(128),
TRX_BATCH_NO VARCHAR(64),
CANCEL_SIGN VARCHAR(32),
SETTLE_SIGN VARCHAR(32),
TERMINAL_NO VARCHAR(64),
COMPLETED_TIME DATETIME,
SUM_STATUS VARCHAR(32),
PRIMARY KEY (ID),
CONSTRAINT trxOrder_unique_index UNIQUE (REQUEST_NO, MERCHANT_NO),
CONSTRAINT trxOrder_external_index UNIQUE (TRX_EXTERNAL_NO),
INDEX TBL_TRX_ORDER_CREATE_TIME_INDEX (CREATE_TIME),
INDEX TBL_TRX_ORDER_LAST_UPDATE_TIME_INDEX (LAST_UPDATE_TIME),
INDEX TBL_TRX_ORDER_PAY_EXTERNAL_NO_INDEX (PAY_EXTERNAL_NO),
INDEX TBL_TRX_ORDER_BATCH_NO_INDEX (TRX_BATCH_NO),
INDEX TBL_TRX_ORDER_COMPLETED_TIME_INDEX (COMPLETED_TIME)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
重點(diǎn)關(guān)注 CONSTRAINT trxOrder_unique_index UNIQUE (REQUEST_NO, MERCHANT_NO)
問題
-
SELECT * FROM tbl_trx_order where request_no='lcPay1530699111557' and merchant_no='1318070417698'有沒有利用索引
-
SELECT * FROM tbl_trx_order where merchant_no='1318070417698' and request_no='lcPay1530699111557'有沒有利用索引
SELECT * FROM tbl_trx_order where merchant_no='1318070417698'有沒有利用索引SELECT * FROM tbl_trx_order where request_no='lcPay1530699111557' 有沒有利用索引
分析
- 問題1和2相似,主要是聯(lián)合唯一索引使用的時(shí)候有沒有順序問題,看計(jì)劃
explain SELECT * FROM tbl_trx_order where merchant_no='1318070417698' and request_no='lcPay1530699111557';
explain SELECT * FROM tbl_trx_order where request_no='lcPay1530699111557' and merchant_no='1318070417698';
充分說明,聯(lián)合索引的執(zhí)行是和其兩個(gè)參數(shù)的擺放先后順序沒有關(guān)系的.
- 問題3和4相似,問的主要是聯(lián)合索引的多個(gè)參數(shù)單獨(dú)使用時(shí)生不生效
explain SELECT * FROM tbl_trx_order where merchant_no='1318070417698';
explain SELECT * FROM tbl_trx_order where request_no='lcPay1530699111557';
可以看出,單獨(dú)使用merchant_no時(shí),索引并沒有生效,且rows為575,相當(dāng)于遍歷了整表,type為ALL;而使用request_no 時(shí),現(xiàn)在索引是生效的.
所以,聯(lián)合索引中字段單獨(dú)使用時(shí),從聯(lián)合索引前向后方向使用部分字段索引是生效的,這也叫做數(shù)據(jù)庫的最佳左前綴特性.
下面舉個(gè)多子端聯(lián)合索引的例子測(cè)試一下:
CREATE TABLE
tbl_check_batch
(
ID bigint NOT NULL AUTO_INCREMENT,
CREATE_TIME DATETIME DEFAULT CURRENT_TIMESTAMP,
LAST_UPDATE_TIME DATETIME DEFAULT CURRENT_TIMESTAMP,
BATCH_NUM VARCHAR(64) NOT NULL,
CHECK_STATUS VARCHAR(32) NOT NULL,
CHECK_CONTROLER_ID bigint NOT NULL,
BATCH_TYPE VARCHAR(32),
PRIMARY KEY (ID),
CONSTRAINT batch_num_record_unique_index UNIQUE (BATCH_NUM, CHECK_CONTROLER_ID, BATCH_TYPE)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
例一: explain SELECT * FROM tbl_check_batch where BATCH_NUM='000000' and CHECK_CONTROLER_ID=1 ;
此時(shí)顯然索引是生效的
例二: explain SELECT * FROM tbl_check_batch where CHECK_CONTROLER_ID=1 and BATCH_TYPE='TRADE';
顯然此時(shí)索引是不生效的,索引證明我們上面的結(jié)論同樣適用于2個(gè)以上聯(lián)合索引的情況.
附:例子中涉及了BATCH_NUM是一個(gè)Varchar類型字段,如果此時(shí)sql如下
SELECT * FROM tbl_check_batch where BATCH_NUM=000000 and CHECK_CONTROLER_ID=1 and BATCH_TYPE='TRADE';我們是一樣能查出結(jié)果的,但是切記mysql字段自動(dòng)類型轉(zhuǎn)換時(shí)索引時(shí)不生效的,我們生產(chǎn)使用時(shí)千萬注意
