mysql 组合索引 排序_mysql一般组合索引分析及验证最左原则和索引重排序

1、建表语句

CREATE TABLE `temp` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`aaa` varchar(255) DEFAULT NULL,

`bbb` varchar(20) DEFAULT NULL,

`ccc` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `INDX_ABC` (`aaa`,`bbb`,`ccc`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4;

2、组合索引 INDX_ABC` (`aaa`,`bbb`,`ccc`)

//a

EXPLAIN SELECT * FROM temp WHERE aaa='a';

//ab

EXPLAIN SELECT * FROM temp WHERE aaa='a' AND bbb='b';

//ac

EXPLAIN SELECT * FROM temp WHERE aaa='a' AND ccc='c';

//abc

EXPLAIN SELECT * FROM temp WHERE aaa='a' AND bbb='b' AND ccc='c';

//acb

EXPLAIN SELECT * FROM temp WHERE aaa='a' AND ccc='c' AND bbb='b' ;

//b

EXPLAIN SELECT * FROM temp WHERE bbb='b';

//bc

EXPLAIN SELECT * FROM temp WHERE bbb='b' AND ccc='c';

//bca

EXPLAIN SELECT * FROM temp WHERE bbb='b' AND ccc='c'AND aaa='a';

//c

EXPLAIN SELECT * FROM temp WHERE ccc='c';

//cb

EXPLAIN SELECT * FROM temp WHERE ccc='c' AND bbb='b';

//ca

EXPLAIN SELECT * FROM temp WHERE ccc='c' AND aaa='a';

//cba

EXPLAIN SELECT * FROM temp WHERE ccc='c' AND bbb='b' AND aaa='a';

//cab

EXPLAIN SELECT * FROM temp WHERE ccc='c' AND aaa='a' AND bbb='b' ;

通过分析上面13种组合发现   a/ab/abc/bac/bca/cab/cba 都会走索引。如果只按照组合索引的最左原则，则只会有a/ab/abc 这三种情况走索引，但是bac/bca/cab/cba这些情况下也走了索引，不但说明组合索引可以满足最左索引的规则，也说明组合索引重排序的存在。