优点

  1. 大大减少了服务器需要扫描的数据量，大大加快数据的检索速度，这也是创建索引的最主要的原因
  2. 帮助服务器避免排序和临时表
  3. 将随机io变成顺序io

  4. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

    缺点

  5. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

  6. 索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值
  7. 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能

由此可见，索引并不是越多越好。

  • 更新十分频繁，数据区分度不高的字段上不宜建立索引
  • 数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果
  • 在一同值少的列上(字段上)不要建立索引，比如在学生表的”性别”字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引
  • 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算

    索引的用处

  1. 快速查找匹配WHERE子句的行
  2. 从consideration中消除行,如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引
  3. 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
  4. 当有表连接的时候，从其他表检索行数据
  5. 查找特定索引列的min或max值
  6. 如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组

  7. 在某些情况下，可以优化查询以检索值而无需查询数据行

    索引失效的场景

  8. like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。（最左原则）

  9. or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。
  10. 组合索引，不是使用第一列索引，索引失效。（最左原则）
  11. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
  12. 在索引列上使用 IS NULL 或 IS NOT NULL操作，索引可能会失效但不一定失效。
  13. 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
  14. 对索引字段进行计算操作、字段上使用函数。
  15. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
  16. 索引列结果集大于总数据的2/3时，索引失效。原因即上面的第8点。比如性别sex=0|1，共30条数据，=0的有21条，此时你查询** where sex=0 ** 则不走索引。

经过朕的亲测 is null 和 is not null 都是走索引的，mysql数据库版本为5.7