Mysql中索引优化和失效

什么是索引

要了解索引优化和索引失效的场景就要先了解什么是索引

索引是一种有序的存储结构，按照单个或者多个列的值进行排序，以提升搜索效率。

索引的类型

UNIQUE唯一索引

不可以出现相同的值，可以有NULL值。

INDEX普通索引

允许出现相同的索引内容。

PRIMARY KEY主键索引

不允许出现相同的值，且不能为NULL值，一个表只能有一个primary_key索引。

fulltext index 全文索引

上述三种索引都是针对列的值发挥作用，但全文索引，可以针对值中的某个单词，比如一篇文章中的某个词，然而并没有什么卵用，因为只有myisam以及英文支持，并且效率让人不敢恭维，但是可以用coreseek和xunsearch等第三方应用来完成这个需求。

Sql索引优化共有以下几种方法

1. 通过explain 语句帮助选择更好的索引和写出更优化的查询语句。
2. SQL语句中的IN包含的值不应该过多。
3. 当只需要一条数据的时候，使用limit 1。
4. 如果限制条件中其他字段没有索引，尽量少用or。
5. 尽量用union all代替union。
6. 不使用ORDER BY RAND()。
7. 区分in和exists、not in和not exists。
8. 使用合理的分页方式以提高分页的效率。
9. 查询的数据过大，可以考虑使用分段来进行查询。
10. 避免在where子句中对字段进行null值判断。
11. 避免在where子句中对字段进行表达式操作。
12. 必要时可以使用force index来强制查询走某个索引。
13. 注意查询范围，between、>、<等条件会造成后面的索引字段失效。
14. 关于JOIN优化

 sql索引失效的场景

非最左匹配

最左匹配原则指的是，以最左边的为起点字段查询可以使用联合索引，否则将不能使用联合索引

错误模糊查询

模糊查询 like 的常见用法有 3 种：

1. 模糊匹配后面任意字符：like '张%'
2. 模糊匹配前面任意字符：like '%张'
3. 模糊匹配前后任意字符：like '%张%'

而这 3 种模糊查询中只有第 1 种查询方式可以使用到索引

列运算

如果索引列使用了运算，那么索引也会失效

使用函数

查询列如果使用任意 MySQL 提供的函数就会导致索引失效

类型转换

如果索引列存在类型转换，那么也不会走索引，比如 address 为字符串类型，而查询的时候设置了 int 类型的值就会导致索引失效

使用 is not null

当在查询中使用了 is not null 也会导致索引失效，而 is null 则会正常触发索引的