【MySQL进阶之路 | 高级篇】索引失效

1. SQL查询优化

我们可以从下面几个方面进行数据库调优：

• 索引失效，没有充分利用到索引-->索引建立
• 关联查询太多JOIN-->SQL优化
• 服务器调优及各个参数设置-->调整my.cnf
• 数据过多-->分库分表

虽然SQL查询优化的技术很多，但大方向上完全可以分为物理查询优化和逻辑查询优化

• 物理查询优化是通过索引和表连接方式来进行优化，这里重点需要掌握索引的使用。
• 逻辑查询优化是通过SQL等价变换提升查询效率，直白点就是说，换一种查询写法执行效率可能更高。

2. 索引失效案例

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

其实用不用索引，最终都是优化器说了算，优化器是基于cost开销，它不是基于规则，也不是基于语义，怎么样开销小怎么来。另外，SQL语句是否使用索引，根数据库版本，数据量，数据选择度都有关系。

1). 计算，函数，类型转换导致索引失效

前者比后者更好，因为前者可以用到索引，而后者因为使用了函数（因为不确定函数会返回什么，所以需要遍历全表）导致索引失效。

name是字符串类型，等值与整数匹配时，字符串类型会隐式转换为整型，导致索引失效。

2). 范围条件右边的列索引失效

情况二是范围查询，导致第三个索引列失效，即name索引列失效。

因为一旦第二个索引列进行了范围查询，此时记录的第三个索引列的值是无序的，第三个索引列进行等值匹配时需要扫描第二个索引列剩下的记录，导致其后面的索引列失效。

3). 不等于索引失效

4). is null可以使用索引，is not null不可以使用索引

is not null由于查询的记录数很大，可能不使用索引比使用索引的效率还要高。

5). like以通配符%开头的索引失效

通配符开头无法匹配，走不了b+树。

6). OR前后存在非索引的列，索引失效

在WHERE语句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，当OR前后两个条件的列都是索引列的时候，查询中才会使用索引。、

因为OR的含义就是两个只要满足一个就可以，因此只有一个条件列进行了索引是没有意义的，因只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

7). 字符集转换

统一使用utf8mb4兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

3. 建议

1. 对于单列索引，尽量选择针对当前查询过滤性更好的索引
2. 在选择组合索引时，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好。（最左前缀原则）
3. 在选择组合索引时，尽量选择能包含当前查询中的where子句中更多字段的索引。
4. 在选择组合索引时，如果某个字段可能出现范围查询，尽量把这个字段放在索引次序的最后面。