mysql复合索引(联合索引)用法以及最左原则

1.准备表(含23w数据)

SELECT COUNT(*) FROM index_test;

2.查看索引，只有一个主键索引

3.创建联合索引(abc)

ALTER TABLE index_test ADD INDEX index_a_b_c(a, b, c);

4.能够触发索引的几种情况
1.单独用a做条件查询

EXPLAIN SELECT * FROM index_test WHERE a = 'H5158000';

2.用a和b做条件联合查询

EXPLAIN SELECT * FROM index_test WHERE a = 'H5158000' AND b = '432192';

3.用abc联合查询

EXPLAIN SELECT * FROM index_test WHERE a = 'H5158000' AND b = '432192' AND c = '生理盐水清洁棉';

5.联合索引不触发索引的几种情况
1.or两边放联合索引，不触发索引(如果两边是单列索引另算)

EXPLAIN SELECT * FROM index_test WHERE a = 'H5158000' OR b = '432192';

2.单独使用b不触发索引

EXPLAIN SELECT * FROM index_test WHERE b = '432192';

3.不使用a，使用b，c也不触发索引

EXPLAIN SELECT * FROM index_test WHERE b = '432192' AND c = '生理盐水清洁棉';

5.or的用法
1.添加两个单列普通索引

ALTER TABLE index_test ADD INDEX index_d(d);
ALTER TABLE index_test ADD INDEX index_e(e);

1.or两边只要有一个不是索引就不启用索引查询

//e为单列索引，f为非索引--失效
1.EXPLAIN SELECT * FROM index_test WHERE e = 'SLYSQJM' OR f = '1008858000463';
//e为单列索引，b为联合索引(非最左索引)--失效
2.EXPLAIN SELECT * FROM index_test WHERE e = 'SLYSQJM' OR b = '1008858000463';

2.or两边一个是联合索引的最左索引一个是单例索引才生效，否则失效

EXPLAIN SELECT * FROM index_test WHERE e = 'SLYSQJM' OR a = '1008858000463';

3.数据量过小，or也会使本该有效的索引失效
6.大于号小于号的用法
不固定
7.总结