mysql-SQL优化和失效索引

1. 索引失效

可能失效
1. where 语句中包含or时
  1. 使用or并不是一定会使索引失效，你需要看or左右两边的查询列是否命中相同的索引。
2. where语句中索引列中使用了负项查询
  1. 负向查询
    NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。
  2. 注意
    但是负向查询并不绝对会索引失效，这要看MySQL优化器的判断，全表扫描或者走索引哪个成本低了。
3. 索引字段可以为null，使用is null 或者 is not null 时
  1. 不过某些“军规”和规范中都有强调，字段要设为not null并提供默认值
  2. null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化。
  3. null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多。
  4. null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识。
一定失效
1. 在索引列上使用内置函数
  - 优化建议，尽量在应用程序中进行计算和转换。
2. 对索引列进行计算
  - 优化的话，要把运算放在值上，或者在应用程序中直接算。
3. like通配符
  - like查询以 % 开头时，会导致索引失效
4. 联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效
  - 当创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。
5. MYSQL优化器的最终选择，不走索引
6. 注意
  - 即使完全符合索引生效的场景，考虑到实际数据量等原因，最终是否使用索引还要看MySQL优化器的判断。

2. 优化

字段类型优化
- 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
- 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.（备注、描述、评论之类的可以设置为 NULL）
where条件语句优化
- where 条件是自右向左的顺序执行的，索引是自左向右
- 避免模糊查询中使用前置 %
- 过滤大量数据的条件写到where 条件的末尾（右），这样可以过滤大量数据
- 避免在where语句中出现字段的类型转换（字段的类型和传入的参数类型不一致的时候发生的隐式类型转换），当两张表字符集不同的时候需要关联查询
- 不要在where条件语句中的"="左边进行函数、算数运算或其他表达式运算，可以通过使用冗余字段来替代函数运算，否则系统无法正确使用索引
- where 子句中对字段进行 null 值判断、包含not、!=、<>等操作符，或like的关键词前加%(like '%关键词')，都无法使用索引，从而引发全表扫描.
- 使用like进行模糊查询时应注意,除非必要，否则不要在关键词前加%，否则必然导致全表查询