mysql基础(10) MYSQL 组合索引使用(一)

MYSQL版本:

一、建表SQL 

DROP TABLE IF EXISTS `index_test`;
CREATE TABLE `index_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(32) DEFAULT '' COMMENT '姓名',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `sex` int(3) NOT NULL DEFAULT '0' COMMENT '性别(0-男;1-女,)',
  PRIMARY KEY (`id`),
  KEY `index_union_key` (`name`,`age`,`sex`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='组合索引测试表';

-- ----------------------------
-- Records of index_test
-- ----------------------------
INSERT INTO `index_test` VALUES ('1', '张三', '25', '0');
INSERT INTO `index_test` VALUES ('2', '李四', '21', '1');
INSERT INTO `index_test` VALUES ('3', '王五', '18', '1');

二、测试SQL

-- 1.abc顺序(abc三个索引都在where条件里面用到了，而且都发挥了作用)
EXPLAIN select * from index_test where name='张三' AND age=5 AND sex=1;

 

-- 注意:name是字符串类型 
EXPLAIN select * from index_test where name=32 AND age=5 AND sex=1; 

 

-- 2.cba顺序(where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样)
EXPLAIN select * from index_test where sex=1 AND age=5 AND name='张三';  

 

-- 3.ac顺序(a用到索引，b没有用，所以c是没有用到索引效果的)
EXPLAIN select * from index_test where name='张三' AND sex=1;

 

-- 4.abc顺序+范围(b范围值，断点，阻塞了c的索引) a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
EXPLAIN select * from index_test where name='张三' AND age > 7 AND sex=1;    

 

-- 5.bc顺序(因为a索引没有使用，所以这里 bc都没有用上索引效果) 联合索引必须按照顺序使用，并且需要全部使用
EXPLAIN select * from index_test where age = 7 AND sex=1;

 

-- 6.abc顺序+范围(a用到了  b没有使用，c没有使用)
EXPLAIN select * from index_test where name >'张三' AND age = 7 and sex=1; 

-- 7.b排序(a用到了索引，b在结果排序中也用到了索引的效果，a下面任意一段的b是排好序的)
EXPLAIN select * from index_test where name ='张三' ORDER BY age;

-- 8.c排序(a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了;使用了filesort)
EXPLAIN select * from index_test where name ='张三' ORDER BY sex; 

 

-- 9.a排序(b没有用到索引，排序中a也没有发挥索引效果)
EXPLAIN select * from index_test where age =25 ORDER BY name;