前言

实践是检验知识的唯一标准!

联合索引

准备环境

CREATE TABLE `abc_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aid` varchar(20) NOT NULL DEFAULT '' COMMENT 'aid',
  `bid` varchar(20) NOT NULL DEFAULT '' COMMENT 'bid',
  `cid` varchar(20) NOT NULL DEFAULT '' COMMENT 'cid',
  PRIMARY KEY (`id`),
  KEY `abc` (`aid`,`bid`,`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- truncate table abc_table
INSERT INTO abc_table (aid,bid,cid) VALUES('a01','b01','c01');
INSERT INTO abc_table (aid,bid,cid) VALUES('a02','b02','c02');
INSERT INTO abc_table (aid,bid,cid) VALUES('a03','b03','c03');

开始测试

--组合结果为 abc ab ac bc 四种组合结果

--1.abc 能用到索引 ,bca  cab 都可以索引,因为mysql有查询优化器
explain select * from abc_table where aid='a01' and bid='b01' and cid='c01';
--bca能用到索引
explain select * from abc_table where bid='b01' and cid='c01'and aid='a01';
--cab能用到索引
explain select * from abc_table where  cid='c01'and aid='a01' and bid='b01';

-- 部分值匹配时,只要条件中有最左索引项就会用到索引
-- ab 能用到索引,两个
explain select * from abc_table where aid='a01' and bid='b01';
-- ac 能用到索引, 一个, 只能依靠a来索引
explain select * from abc_table where aid='a01'  and cid='c01';

--ba 能用到索引,两个
explain select * from abc_table where bid='b01'  and aid='a01';
--ca 能用到索引 一个, 只能依靠a来索引
explain select * from abc_table where cid='c01' and aid='a01';

-- 条件中没有最左索引,不会用到索引,全表扫描
--bc 不能用到索引
explain select * from abc_table where bid='b01' and cid='c01';

--b 用不到索引
explain select * from abc_table where bid='b01';

--c 用不到索引
explain select * from abc_table where cid='c01';

--cb 用不到索引
explain select * from abc_table where cid='c01' and bid='b01';

结论

  1. 联合索引abc,b+树会按照先a再b再c的优先级进行排序
  2. 遵循最左前缀原则,所以a必须要参与where条件,任意组合都可以。因为mysql有优化器可以优化顺序
  3. 如果是ac则只会走a的索引,不会走c的索引。但是还是走索引的!!!!
  4. 当只有bc或者只有b、c的时候,不会触发索引。不满足最左前缀原则

索引失效情况总结

  • !=、<> 会索引导致失效,走全表扫描
  • or连接条件,当or左右查询字段只有一个是索引,该索引失效,只有当or左右查询字段均为索引时,才会生效
  • like 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效。
  • 组合索引,不是使用第一列索引,索引失效。
  • 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描。
  • 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的,所以这样的操作不能使用索引
  • 对索引字段进行计算操作、字段上使用函数