面试题集锦-Mysql-索引下推ICP

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查看

关闭索引下推时

 开启索引下推时