MySQL索引创建原则

适合创建索引的场景

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

2. 频繁作为WHERE查询条件的字段
   某个字段在SELECT语句的WHERE条件中经常被使用到，那么就需要给这个字段创建索引。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询效率。

3. 经常GROUP BY和ORDER BY的列
   索引就是让数据按照某种顺序进行存储或检索，因此当我们使用GROUP BY对数据进行分组查询，或者使用ORDER BY对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。

4. UPDATE、DELETE的WHERE条件列
   当我们对某条数据进行UPDATE或者DELETE操作的时候，针对操作的条件列也需要创建索引，如果提件列存在多个可以创建组合索引。

5. DISTINCT字段需要创建索引
   有时候我们需要对某个字段进行去重，使用DISTINCT，那么对这个字段创建索引，也会提升查询效率。

6. 多表JOIN连接操作时，创建索引注意事项
   首先，连接表的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长非常快，严重影响查询的效率。
   其次，对WHERE条件创建索引，因为WHERE才是对数据条件的过滤。如果在数据量非常大的情况下，没有WHERE条件过滤是非常可怕的。
   最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

7. 使用列的类型小的创建索引
   这里所说的类型大小指的是该类型表示的数据范围的大小。
   我们在定义表结构的时候需要显式指定列的类型，以整型为例，有TINYINT、MEDIUMINT、INT、BIGINT等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT，因为：
       数据类型越小，在查询时进行的比较操作越快。
       数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以存储更多的记录，从而减少磁盘IO带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
   这个建议对于表的主键来说更加适用，因为不仅是主键索引中会存储主键值，其他所有的二级索引的节点都会储存主键值，如果主键值使用更小的数据类型，也就意味着节省更多的存储空间和高效的IO。

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

9. 区分度高(散列性高)的列适合作为索引
   列的基数指的是某一列不重复数据的个数，比如某个列包含值2，5，8，2，5，8，2，5，8，虽然这里有9条记录，但该列的基数却是3，也就是说 在记录行数一定情况下，列的基数越大，该列中的值越分散；列的基数越小，该列中的值越集中。 这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小的列建立索引效果可能不好。
   在联合索引中，把区分度高(散列性高)的列放在前面。

10. 使用最频繁的列放到联合索引的左侧
    联合索引是由多个列组合成的，如ABC列组成索引ABC，如果想使得这个索引有效，必须先匹配到A列才可以，即最左侧列先被匹配，这样可以增加这个联合索引命中几率。

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

12. 限制索引的数量
    在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过16个，原因：
        每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
        索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
        优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划的时间，降低查询性能。

---

不适合创建索引的场景

1. 在WHERE中使用不到的字段，不要设置索引
   WHERE条件(包括GROUP BY、ORDER BY)里面用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。

2. 数据量小的表最好不要使用索引
   表记录太少，索引建了弊大于利。

3. 有大量重复数据的列上不要建立索引
   在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在表中 “性别” 列字段上建立索引，最多只有三个值男、女、未知，如果建立索引，这个索引一共三个值，可以想象B+树索引上就三个节点，查询完了回表，可以说这个索引几乎无效，影响了查询的性能和更新数据的性能。
   当数据重复度较大，比如高于10%的时候，不建议对这个字段建立索引。

4. 避免对经常更新的表创建过多的索引
   在更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

5. 不建议用无序的值作为索引
   例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能导致页分裂)、MD5、HSAH、无序长字符串等。

6. 删除不再使用或者很少使用的索引
   表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

7. 不要定义冗余或重复的索引
   冗余索引：如组合索引index(a,b,c)相当于index(a)，index(a,b)，index(a,b,c)，这时如果同时建立了index(a,b,c)又建立了index(a)或者index(a,b)就是索引冗余了。
   重复索引：如主键，主键本身存在主键索引，如果还建立普通索引，就属于重复索引。