MySQL 优化 (四) --------- 索引设计与使用规则

---

一、索引设计

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

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

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

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

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

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

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

二、索引失效

1、索引列运算

如果索引字段上使用运算，导致索引失效。

2、索引列函数

如果索引字段使用了函数，导致索引失效。

3、隐式转换

如果索引字段产生了隐式类型转换，导致索引失效。

4、模糊查询

在模糊查询时，如果是以 ‘%’ 为开始导致索引失效。
在模糊查询时，如果是以 ‘%’ 为结束时不会导致索引失效。

5、最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左侧开始，并且不能跳过索引中的列。如果跳过某一列，索引将部分失效 (后面的字段索引失效)。最左列的索引存在即可，与所放的位置无关。

6、全列匹配原则

在使用多列索引时，由于 MySQL 对于多列索引中字段顺序是敏感的。所以一般要求多列索引字段出现顺序必须与多列索引声明时的字段声明顺序一致。但是，如果这些查询条件都是使用 “=” 或则 “in”，则 MySQL 依然在本次查询使用多列索引.

7、范围查询

联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效。

8、字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

9、or连接的条件

用 or 分隔开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

10、数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

三、索引使用

1. SQL 提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

use index
ignore index
force index

explain select * from student where gender='male' use index(stuId);

2. 覆盖索引

尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select * 。

3. 前缀索引

当字段类型为字符串（varchar，text等）时，有时候需要索引保存很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

4. 单列索引与联合索引

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。