MySQL优化--索引创建原则，索引什么时候会失效

目录

索引创建原则

面试回答

索引什么时候会失效

面试回答

---

索引创建原则

1). 针对于数据量较大，且查询比较频繁的表建立索引。

2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

面试回答

面试官：索引创建原则有哪些？

候选人：嗯，这个情况有很多，不过都有一个大前提，就是表中的数据要超 过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字 段，一般也是像作为查询条件，排序字段或分组的字段这些。

还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sql的返 回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在 组合索引后面的字段。

如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是 所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导 致新增改的速度变慢。

索引什么时候会失效

为了便于演示 我们提前给tb_seller创建联合索引，字段顺序：name，status，address

1). 违反最左前缀法则

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

匹配最左前缀法则，走索引：

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

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效（name命中，address没有命中）：

 2). 范围查询右边的列，不能使用索引 。

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

 3). 不要在索引列上进行运算操作， 索引将失效。

 4). 字符串不加单引号，造成索引失效。

由于，在查询是，没有对字符串加单引号， MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

 5).以%开头的Like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

面试回答

面试官：什么情况下索引会失效 ?

候选人：嗯，这个情况比较多，我说一些自己的经验，以前遇到过的 比如，索引在使用的时候没有遵循最左匹配法则，第二个是，模糊查询，如 果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作 或者类型转换也都会导致索引失效。

我们之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询， 右边的条件索引也会失效 所以，通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用 explain执行计划来分析