索引创建原则和索引失效情况——Java全栈知识（30）

1、索引创建的原则

1). 针对于数据量较大，且查询比较频繁的表建立索引。
2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7). 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询。

2、索引失效的情况

1、最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引：

例如我们创建以上索引
然后我们可以在 key_len 栏看到索引的使用情况

违反最左前缀法则，索引失效：

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

也就是说我们的联合索引（索引多列）必须要符合最左前缀法则，在查询的时候必须按照索引创建的顺序，不能跳跃前缀的列，否则索引将失效。

2、范围查询的右边的列不能使用索引

前面的两个字段 name ， status 查询是走索引的，但是最后一个条件 address 没有用到索引。

3、索引运算

索引列不能参与运算，如果作为条件的索引列参与运算的话，索引也会失效，如下。

4、字符串不加单引号

因为字符串不加引号，Mysql 会对其进行类型转换，类型转换的列不能使用索引。

5、模糊查询

模糊查询可能导致索引失效，%开头的模糊查询可能导致失效，但是%的后缀不会失效。