01、字段的数值有唯一性的限制

索引本身可以起到约束的作用。比如唯一索引、主键索引都是可以起到唯一性约束的，所以在数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。

例如：学生表中学号字段是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息。但如果使用姓名的话，可能会存在同名的现象，从而降低查询速度。

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

说明：不是创建了唯一索引就一定影响了 insert 的速度，这个速度损耗是可以忽略的，但是很明显是可以提高查询速度的。

02、频繁作为 where 查询条件的字段

某个字段在 select 语句的 where 条件中经常被使用到时，就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅度提升数据查询的效率。

例如：student_info 表里有 10w 条数据，如果想要查询 student_id = 173368 的用户信息，添加索引和不添加索引的区别还是很大的。查询效果如下图所示：

由此可见，效率的提升还是很明显的。

03、经常 group by 和 order by 的列

索引就是让数据按照某种顺序进行存储或检索，因此当使用 group by 对数据进行分组查询，或者使用 order by 对数据进行排序的时候，就需要对分组或者排序的字段进行索引了。如果待排序的列有多个，就可以在这些列上建立组合索引。

比如：按照 student_id 对学生选修的课程进行分组，显示不同的 student_id 和 课程数量。

SELECT student_id, COUNT(student_id) AS num FROM student_info GROUP BY student_id;

由此可见，效率提升明显，并且得到的结果中 student_id 字段的数值也是按照顺序展示的。

order by 语句的效果与 group by 是一致的。因为添加索引时 B+Tree 本身就排完序了，使用 group by 或 order by 时可以直接取数据了（索引相当于提前把数据查出来放入缓存里，去查询时直接从缓存中取数据）。

04、update、delete 语句的 where 条件列

当我们对某条数据进行 update 或 delete 操作的时候，是否也需要对 where 的条件列创建索引呢？

例如：现在想把 name 为 NKmdJX 对应的 student_id 修改为 10002，加了索引和不加索引的执行效果：

由此可以看出，添加索引后执行效率有很大地提升。

下面再来试一下 delete 语句中，添加了索引和不添加索引的区别：

由此可见，添加索引后，delete 语句的执行效率也有很大地提升。

总结：对数据按照某个条件进行查询后再进行 update 或 delete 操作时，如果对 where 字段创建了索引，就能大幅度提升效率。原理是因为我们需要先根据 where 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

05、distinct 字段需要创建索引

如果需要对某个字段进行去重，使用 distinct 时，如果对该字段创建索引，也会提升查询效率。

例如：现在想要查询课程表中不同的 student_id 都有哪些，对 student_id 创建索引和不创建索引的结果：

可以看到添加索引的效率比不加索引的效率明显是提升的，并且查出的字段还是按照递增的顺序进行展示的，这是因为索引会对数据按照某种顺序进行排序，所以在去重时也会快很多。

06、多表 join 连接操作时，创建索引注意事项

多表 join 连接操作时，创建索引注意事项：

首先，连接表的数量尽量不要超过 3 条，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长也会非常快，严重影响查询的效率。

其次，对 where 条件创建索引，因为 where 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 where 条件过滤还是非常可怕的。

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。也就是说假如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

数据类型不一致时，会进行隐式转换，从而导致索引失效。

例如：只对 student_id 创建索引和对 student_id、name 都创建索引，执行的效率：

07、使用列的类型小的创建索引

这里所说的类型大小指的就是该类型表示的数据范围的大小。

在定义表结构的时候要显式的指定列的类型，以整数类型为例，有 TININT、MEDIUMINT、INT、BIGINT 等等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型（MEDIUMINT -> INT -> BIGINT），有两个原因：

数据类型越小，查询时的效率越高；
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放得下更多的记录，从而减少磁盘 I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键值使用更小的数据类型，也就意味着节省更多的存储空间和更高效的 I/O。

08、使用字符串前缀创建索引

假如字符串很长，存储一个字符串就需要占用很大的存储空间。当需要为这个字符串建立索引时，那就意味着在对应的 B+Tree 中有两个问题：

B+Tree 索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间就越大。
如果 B+Tree 索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。
所以，可以通过截取字段的前面一部分内容建立索引，就叫做前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，再根据前缀相同的记录的主键值回表查询完整的字符串值。既节约了空间，又减少了字符串的比较时间，还大体能解决排序的问题。

09、区分度高（散列性高）的列适合作为索引

列的基数指的是某一列中不重复的个数，比如说某个列包含值 2，5，8，2，5，8，2，5，8，虽然有 9 条记录，但是该列的基数是 3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散；列的基数越小，该列中的值越集中。列的基数直接影响到我们能否有效的利用索引，最好为列的基数大的列创建索引，为基数太小的列建立索引效果不太好。

注意：联合索引把区分度高的列（散列性高）放在前面。

10、使用最频繁的列放到联合索引的左侧（最左前缀原则）

根据最左前缀原则，可以增加联合索引的使用率。

11、 在多个字段都要创建索引的情况下，联合索引优于单值索引

当有多个单值索引，使用时会优先执行一个，所以在多个值都需要索引的时候，建议创建联合索引。

创建联合索引时，使用最频繁的字段必须放在最左边，如果不频繁，建议把区分度高的字段放在最前面。

explain 计划中有两个字段：possible_key、key。其中，possible_key 代表可能使用到的索引，经过优化器判断后会选择一个最优索引执行。如果可能用到的索引过多，优化器在判别最优索引时耗时较大，影响性能。

12、限制索引的数量

在实际工作中，索引的数量不是越多越好，需要限制每张表中的索引数量，建议单张表的索引数量不操作6个。
①每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大；
②索引会影响INSERT DELETE UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担；
③优化器在选择优化查询时，会根据统一信息，对每一个可以用到的索引进行评估，已生成出一个最好的执行计划，如果同时有多个索引都可以用于查询，会增加mysql优化器生成执行时间，降低查询性能。