哪些情况适合创建索引,哪些情况不适合创建索引

哪些情况适合创建索引

• 字段的数值有唯一性的限制

  业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
  说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的

• 频繁作为 WHERE 查询条件的字段

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

• 经常 GROUP BY 和 ORDER BY 的列

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

• UPDATE、DELETE 的 WHERE 条件列

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

• DISTINCT 字段需要创建索引

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

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

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

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

  最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。

  连接字段上创建索引是指在被驱动表上创建索引，因为是先从驱动表查出数据后，拿着数据根据ON的条件去被驱动表中查数据（这里面你可以理解为驱动表查出一条数据就去被驱动表中检索，但是实际上为了减少随机IO的发生，可能会触发MRR优化，就是先从驱动表查出多条数据，存在缓存中，这多条数据先按主键进行排序，再一下子到被驱动表中检索）

  连接分为内连接和外连接，外连接的话，比较好判断被驱动表，左连接的左边就是驱动表，右边就是被驱动表，右连接与之相反，但是对于内连接我们是没发判断，驱动表和被驱动表的，因为在SQL执行前会mysql的查询优化器会分析两个表分别作驱动表和被驱动表时，谁的执行最好，最终是由查询优化器决定的，但是通常遵循”小表驱动大表“原则

• 使用列的类型小的创建索引

  这个还是比较好理解的，因为索引即数据，数据即索引，一个页的大小是16kb，使用的索引列类型越小，一个页中能够存储的数据就越多，而页是内存和磁盘交互的基本单位，那么IO从磁盘读到内存的数据就越多

• 使用字符串前缀创建索引

拓展：Alibaba《Java开发手册》
【 强制 】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本
区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会 高达 90% 以上 ，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

引申另一个问题：索引列前缀对排序的影响
如果对前缀索引的索引列进行排序的话，会导致索引失效，这个也可以理解，前缀索引只是对截取的字段排序。

• 区分度高(散列性高)的列适合作为索引

• 使用最频繁的列放到联合索引的左侧

  这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

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

  通常是联合索比单列索引强的，但是这个不能说的太绝对。比如对下面这个sql
  select c from table where a=’1‘ and b =‘2’
  如果我们对a和b分别建立了索引index_a ,索引 index_b,和联合索引index_ab
  那么查询优化器在生成SQL执行计划之前，会分别分析三种索引＋全表扫描，这四种情况，谁的执行成本更低，最终选择最好的一个，但是这个是由查询优化器决定的
  另外，如果你没有对a、b建立联合索引，那么mysql也会尝试给你建立联合索引，并分析它的执行成本，这是mysql自身对索引的一种优化

• 限制索引的数目

哪些情况不适合创建索引

• 在where中使用不到的字段，不要设置索引
• 数据量小的表最好不要使用索引
• 有大量重复数据的列上不要建立索引
• 避免对经常更新的表创建过多的索引
• 不建议用无序的值作为索引

  例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

• 删除不再使用或者很少使用的索引
• 不要定义冗余或重复的索引