Mysql 索引

一、组合索引(MySQL最左匹配原则)

示例表结构

CREATE TABLE `vote_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(20) NOT NULL,
  `string_a` varchar(255) NOT NULL,
  `string_b` varchar(255) NOT NULL,
  `string_c` varchar(255) NOT NULL,
  `string_d` varchar(255) NOT NULL,
  `string_e` varchar(255) NOT NULL,
  `string_f` varchar(255) NOT NULL,
  `vote_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `vote_group_id` (`vote_id`,`group_id`) USING BTREE,
  KEY `A_B_C` (`string_a`,`string_b`,`string_c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2122299 DEFAULT CHARSET=utf8;

• 建立索引
  
  在建立联合索引的时候，可以选择优先级，(a|b|c)，或 (b|a|c) 或 (c|ab) 顺序,这里示例选用 abc

• 最左匹配原则
  MySQL 建立多列索引（联合索引）有最左匹配的原则，即最左优先：

具体测试如下:

# const,const,const  A_B_C
EXPLAIN SELECT * from vote_record where string_a = 'k' AND string_b = 'gd' AND string_c = '8QE' 

# const,const  A_B_C
EXPLAIN SELECT * from vote_record where string_a = 'k' AND string_b = 'gd'

# const A_B_C
EXPLAIN SELECT * from vote_record where string_a = 'k' AND string_c = '8QE'

# all
EXPLAIN SELECT * from vote_record where string_b = 'gd' AND string_c = '8QE'

# const A_B_C
EXPLAIN SELECT * from vote_record where string_a = '1'

# all
EXPLAIN SELECT * from vote_record where string_a = 1  # 字符串类型 如果查询int类型 则不走索引，反之可以

# all
EXPLAIN SELECT * from vote_record where string_b = 'gd'

# all
EXPLAIN SELECT * from vote_record where string_c = '8QE'

1.结论

查询走索引: abc ,ab,ac,a

查询不走索引: bc,b,c

其中 当 where 条件只有 (a,c) 时比较特殊 虽然走索引 但是只走a字段的索引，不会走 c 字段。

2.违背最左原则导致索引失效的情况

（参考链接 https://blog.csdn.net/weiguang102/article/details/127895627）

1、查询条件中，缺失优先级最高的索引 “a”

当 bc 这种没有以 a 为条件来检索时；B+树就不知道第一步该查哪个节点，从而需要去全表扫描了（即不走索引）。

因为建立搜索树的时候 a 就是第一个比较因子，必须要先根据 a 来搜索，进而才能往后继续查询b 和 c，这点我们通过上面的存储结构图可以看明白。

2、查询条件中，缺失优先级居中的索引 “b”

当 ac 这样的数据来检索时；B+ 树可以用 a 来指定第一步搜索方向，但由于下一个字段 b 的缺失，所以只能把 a = ? 的数据主键ID都找到，通过查到的主键ID回表查询相关行，再去匹配 c = ? 的数据了，当然，这至少把 a = 1 的数据筛选出来了，总比直接全表扫描好多了。

这就是MySQL非常重要的原则，即索引的最左匹配原则。

3.查询优化器偷偷干了哪些事儿

当对索引中所有列通过 “=” 或 “IN” 进行精确匹配时，索引都可以被用到。

1、查询的语句是 where b = ? AND a = ? AND c=?也可以走索引

理论上索引对顺序是敏感的，但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以 MySQL 不存在 where 子句的顺序问题而造成索引失效

2、还有一个特殊情况说明下，where a = ? and b > ? and c=? ， a 与 b 会走索引，c不会走。

对于上面这种类型的sql语句；
mysql会一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配（包括like '值%'这种）。

在a、b走完索引后，c已经是无序了，所以c就没法走索引，优化器会认为还不如全表扫描c字段来的快。所以只使用了(a,b)两个索引，影响了执行效率。

其实，这种场景可以通过修改索引顺序为 (a,c,b)，就可以使三个索引字段都用到索引，建议小伙伴们不要有问题就想着新增索引哦，浪费资源还增加服务器压力。

综上，如果通过调整顺序，就可以解决问题或少维护一个索引，那么这个顺序往往就是我们DBA人员需要优先考虑采用的。

二、索引失效

• like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
• 当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
• 组合索引，不是使用第一列索引，索引失效
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引
• 在索引列上使用 IS NULL 或 IS NOT NULL操作，索引不一定失效！！！，这个是要考虑执行的成本问题
• 在索引字段上使用not，<>，!=。 优化方法： key<>0 改为 key>0 or key<0。
• 对索引字段进行计算操作、字段上使用函数
• 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。