目标
分析联合唯一索引问题查询sql的性能问题,执行计划结果含义学习可参考博客https://blog.csdn.net/da_guo_li/article/details/79008016
对于建立索引意见,参考博客
https://blog.csdn.net/wulex/article/details/69540136
背景
tbl_trx_order中有merchant_no、request_no两个字段作为联合索引,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;
重点关注 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相似,主要是联合唯一索引使用的时候有没有顺序问题,看计划
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';
充分说明,联合索引的执行是和其两个参数的摆放先后顺序没有关系的.
- 问题3和4相似,问的主要是联合索引的多个参数单独使用时生不生效
explain SELECT * FROM tbl_trx_order where merchant_no='1318070417698';
explain SELECT * FROM tbl_trx_order where request_no='lcPay1530699111557';
可以看出,单独使用merchant_no时,索引并没有生效,且rows为575,相当于遍历了整表,type为ALL;而使用request_no 时,现在索引是生效的.
所以,联合索引中字段单独使用时,从联合索引前向后方向使用部分字段索引是生效的,这也叫做数据库的最佳左前缀特性.
下面举个多子端联合索引的例子测试一下:
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 ;
此时显然索引是生效的
例二: explain SELECT * FROM tbl_check_batch where CHECK_CONTROLER_ID=1 and BATCH_TYPE='TRADE';
显然此时索引是不生效的,索引证明我们上面的结论同样适用于2个以上联合索引的情况.
附:例子中涉及了BATCH_NUM是一个Varchar类型字段,如果此时sql如下
SELECT * FROM tbl_check_batch where BATCH_NUM=000000 and CHECK_CONTROLER_ID=1 and BATCH_TYPE='TRADE';我们是一样能查出结果的,但是切记mysql字段自动类型转换时索引时不生效的,我们生产使用时千万注意
