sql索引分析
1、如果创建了一个索引
一
ALTER TABLE `table_A` ADD KEY `nid_sn_key`(`a`,`b`,`c`,`d`) USING BTREE;
第一种情况:
explain SELECT * FROM `table_A` WHERE `a` = "xxx";
explain SELECT * FROM `table_A` WHERE `a` != "xxx";
会走索引
第二种情况:
explain SELECT * FROM `table_A` WHERE `b` = "xxx";
不走索引
等等,如果单独查询c, 或者d,都不会走索引;
第三种情况:
explain SELECT * FROM `table_A` WHERE `a` = "xxx", and `b` = "xxx";
explain SELECT * FROM `table_A` WHERE `a` != "xxx", and `b` = "xxx";
explain SELECT * FROM `table_A` WHERE `a` = "xxx", and `b` != "xxx";
explain SELECT * FROM `table_A` WHERE `a` != "xxx", and `b` != "xxx";
explain SELECT * FROM `table_A` WHERE `b` = "xxx" and `a` = "xxx",
会走索引
第四种情况:
explain SELECT * FROM `table_A` WHERE `a` = "xxx" and `b` = "xxx" and `c` ="xxx";
explain SELECT * FROM `table_A` WHERE `b` = "xxx" and `a` = "xxx" and `c` ="xxx";
explain SELECT * FROM `table_A` WHERE `b` = "xxx" and `c` ="xxx" and `a` = "xxx" ;
会走索引
总结 插入了 a、b、c、d 四个字段作为索引,只要带上了a,那么任何排列的组合,都可以走索引。