谈谈索引

前言

    索引是数据库设计和开发的一个重要方面，在程序开发过程中，经常有些技术人员会有两个极端的思想。比如：在设计数据库表时不设计索引，而是在数据库上线之后再创建索引或者干脆交给DBA去创建索引。数据库上线后，创建索引的很有可能会影响到业务，而专职的DBA不会特别关注业务逻辑，因此仅仅从数据库知识出发而不结合实际业务，对数据库索引的创建可能不会达到很好的效果；另外一些开发人员 恰恰相反，他们在创建数据库表时，对表中的大量数据列建立索引，认为这样就可以达到很好的查询效率。以MySQL的InnoDB存储引擎为例，索引是以B+树的数据结构存储的，建立一个索引实际上就是在对这个B+树进行维护，而维护是需要额外的资源的。因此索引越多，需要的资源也越多，索引泛滥不但达不到我们想要的效果，反而会对数据库的性能产生影响。下面将几个建立索引的常用技巧。

1.使用数据区分度高的列建索引

   什么是数据区分度，有个计算公式：count(distinct(列名))/count(*)，这个公式的意思是该列去重后的数据量与总数据量的比值。例如：性别列只有男女两个值（第三类人群不算），一共有10条记录，那么则个公式计算出的结果就是2/10=0.2。从这个例子可以看出，这个值越接近0，区分度越低，越接近1，区分度越高。我们要尽量选择区分度高的数据列建立索引，前面说过MySQL数据库InnoDB存储引擎的索引是一个B+树，区分度越高这个树的查询效率就越高，在性别类上建立索引是没有什么实际意义的。

    判断区分度实际还有一个值，在MySQL中，通过命令show index from 表名\G;命令可以查看表上的索引的情况，每个索引都有一个Cardinality属性，这个是表示该索引所在数据列唯一值得预估值。这个值越大越接近总行数说明区分度越高，反之越低。

    同样，在使用联合索引时，将区分度高的列放到左边，这样会提高联合索引的效率。

2.在Where后的条件中不要对索引进行函数运算

    对索引进行函数运算会使索引失效，并不是这个索引没用了，而是在B+树的检索时，会对元素进行运算后才比较，这会造成很大的开销，使索引得不偿失。

3.索引列中尽量不要出现空值

    这个就不解释了，空值越多，索引效果越差。

4.左前缀匹配

     MySQL在解析SQL语句时，从左向右匹配，遇到分段查询时，比如：>,<,between或like时，就停止匹配，如果把想使用的索引放到了分段查询后面，就会失去效果，比如：

    select * from user where login_date<'2017-01-01' and id=1;

    这时id索引并不生效，应该为id=1 and login_date<'2017-01-01'。

5.尽量修改已有索引而不是新建索引

    有时候，在数据库上线之后，我们需要对索引进行调整。这时候，如果能通过修改现有索引满足需求，就不要新建索引，总之，我们追求满足需求索引最小原则。