Mysql5.6以后的版本开始默认支持索引下推
查看索引下推配置
select @@optimizer_switch;
//设置索引下推关闭
SET optimizer_switch = 'index_condition_pushdown=off';//设置索引下推开启
SET optimizer_switch = 'index_condition_pushdown=on';
-- 表结构
CREATE TABLE `chargebjitem` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`sheetid` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`venderid` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`contracttype` int(11) NULL DEFAULT NULL,
`contractid` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`venderpayflag` int(11) NULL DEFAULT 0,
`shopid` varchar(6) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`shopname` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`chargecode` int(11) NULL DEFAULT NULL,
`chargename` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`startdate` date NOT NULL,
`enddate` date NOT NULL,
`chargeamt` decimal(12, 2) NOT NULL,
`paymode` int(11) NOT NULL DEFAULT 0,
`categoryid` int(11) NOT NULL DEFAULT 0,
`categoryname` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '未指定',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_sheetid`(`sheetid`) USING BTREE,
INDEX `idx_startdate`(`startdate`) USING BTREE,
INDEX `idx_shopid_venderid_sheetid`(`shopid`, `venderid`, `sheetid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- 创建索引
create index idx_shopid_venderid_sheetid on chargebjitem(shopid,venderid,sheetid);
执行计划差异
关闭索引下推时
开启索引下推时
查看存储引擎相关指标监控结果
flush status; //清除status
select * from chargebjitem where shopid ='9253' and venderid like '%1208%';
show status like '%handler%'; //查看上面sql的存储引擎的相关指标监控结果
指标 Handler_read_next表示 MySQL 存储引擎按照索引键顺序读取下一行记录的请求数
关闭索引下推和开启索引下推该数值分别是3921和16
通过profiles查看
关闭索引下推时
开启索引下推时