Mysql索引实践

Mysql索引实践
建表语句如下：

CREATE TABLE `al_dzfp_status` (
  `invoice_status_id` char(21) NOT NULL COMMENT '主键',
  `fpqqlsh` char(21) DEFAULT NULL COMMENT '请求流水号',
  `content` text COMMENT '请求报文',
  `sys_status` varchar(20) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`invoice_status_id`),
  KEY `fpqqlsh_index` (`fpqqlsh`) USING BTREE,
  KEY `date_index` (`update_time`) USING BTREE,
  KEY `status_index` (`sys_status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='开票状态表';

表中数据量大约有三万条
SQL如下：

-- select count(1) from al_dzfp_status;
-- 不能使用索引
EXPLAIN select * from al_dzfp_status where sys_status != 'PUSH_SUCCESS';

-- 0.031s 可使用索引
explain select * from al_dzfp_status where sys_status = 'ISSUE_FAIL' OR sys_status = 'RECIEVE_SUCCESS';

-- 0.034s 可使用索引
explain select * from al_dzfp_status where sys_status in ('ISSUE_FAIL' ,'RECIEVE_SUCCESS');

-- 0.052s 可使用索引
explain select * from al_dzfp_status where sys_status = 'ISSUE_FAIL' UNION select * from al_dzfp_status where sys_status = 'RECIEVE_SUCCESS';

-- 子查询中能使用到索引（type 为ref），where条件是否做索引，视查询出来的结果集大小而定，如果结果集太大也命中不了索引，结果集数量小的话可以走索引
explain select * from al_dzfp_status where fpqqlsh in (select fpqqlsh from al_dzfp_status where sys_status = 'PUSH_SUCCESS');
explain select * from al_dzfp_status where fpqqlsh in (select fpqqlsh from al_dzfp_status where sys_status = 'RECIEVE_SUCCESS');

-- 走status的索引，不走update_time索引;使用(status,update_time)联合索引即可
explain select * from al_dzfp_status where sys_status in ('ISSUE_FAIL' ,'RECIEVE_SUCCESS') order by update_time DESC;

-- 不走索引
EXPLAIN select * from al_dzfp_status where sys_status = 'ISSUE_FAIL' or update_time < CURDATE();