如何创建高效的索引

新手一定要注意索引不是万能的，不要瞎创建索引，以前的文章也说过，在mysql的InnoDB中索引就是数据，数据就是索引，索引建多了占存储空间（在任何数据库中索引都是实际的数据，它会占空间）。

在空间上的浪费说完了，再说说时间上，每次对表中的数据进行增删改操作时，都需要去修改各个B+树索引。

总的来说一个表上索引建的越多，就会占用更多的存储空间。在增删改记录的时候性能就越差。

具体索引的结构可以看：《MySQL是怎样运行的：从根儿上理解MySQL》阅读笔记————5.InnoDB数据页结构-CSDN博客

索引在查询中的作用

一个索引就是一个B+Tree，索引可以让我们快速定位记录，加快查询速度。

索引列的类型尽量小

1）整数类型：tinyint，smallint，int，bigint。占用的存储空间是主键增加的。

2）选择较小数据类型创建列的好处

        查询带来性能上的一些提升

        数据类型小一个数据页可以容纳更多的记录，从而较少io小号。

3）主键类型选择小类型非常重要

        主键值，不仅是在聚簇索引中存储，其他的二级索引上的节点上会存储主键值，选择较小的主键数据类型，可以节省存储空间。

索引列的选择性尽量高

索引列的选择性是指在一个数据库表中，某个特定列的值的唯一性和多样性程度。

索引列的选择性：选择性=不同值的数据量 / 总行数，选择性介于0-1之间的值。

        索引选择性计算方式：

                select count(distinct  name) / count(*) from emp;

                注意：distinct  name

select distinct age from nul_test;

看着是null值也查询出来了，但是count（distinct age）的时候，不会把null算进去（也就是说在算选择性的时候不会把null算进去）——在mysql中null有特殊的概念，下一文章会讲。

select count(distinct age) from nul_test;

选择性高低的区别：

        选择性越高，表示该列中的值多样化，就是唯一值特别多’

        选择性越低，表示重复值较多

选择性高的索引，可以让mysql在查询时过滤掉更多的行，唯一性索引的选择性1。

前缀索引的使用：

针对text，很长的varchar类型，mysql不支持索引它们全部的长度，需要建立前缀索引。

语法：

alter table tablename add key indexname(字段(长度));

  使用上述公式计算不通前缀长度，的选择性，选择一个选择性适合的（高，且短）

        select count(distinct left(order_note,3)) / count(*) from orders；

        选择到合适的长度，创建前缀索引

        alter table orders add key inx_not(order_not(14));

前缀索引的缺点给：mysql中无法使用前缀索引做order by、group by，也无法使用前缀索引进行索引覆盖。

联合索引（多列索引）

联合索引的创建原则：

1、选择性高的列放在最前面，因为可以更好的筛选数据，减少检索的数据量

2、根据运行频率最高的查询（sql），来调整索引列的顺序

3、覆盖查询需要的列，创建联合索引的时候，要注意这个联合索引是否是一个覆盖索引，可以避免回表。

4、避免冗余索引：如果已经有了一个联合索引，在创建一个包含该索引一部分的索引是没必要的，在创建增加维护成本。

5、避免使用太多列

6、只对在查询条件中被经常使用，或者排序分组中经常使用的字段，去创建索引

 三星索引：

        三星索引并不像上面的索引，三星索引就像是个概念，就像数据库开发的三范式。

针对查询而言，一个三星索引，可能是其最好的索引。

三星索引需要满足的条件：

        一星：让索引片尽量变窄，扫描的范围越小越好。通俗的意思就是创建普通索引选择性一定要高，创建联合索引第一列的选择性一定要高，扫描更少的数据

        二星：当查询有order by，使用索引，因为索引就是有序的，就不用另外排序

        三星：尽量使用覆盖索引，查询不需要回表，减少io次数

遇到查询语句可以先评估这个查询使用到的索引是几颗星的（最少也要满足两颗星）。

B+树索引适用于下边这些情况：

        全值匹配

        匹配左边的列

        匹配范围值

        精确匹配某一列并范围匹配另外一列

        用于排序

        用于分组

使用索引需要注意：

        只为用于搜索、排序或者分组的列创建索引

        在选择性好的列上建索引

        索引列的类型尽量小

        可以支队字符串值的前缀建立索引

        只有索引列在比较表达式中单独出现才使用索引（避免运算和函数）

        主键自增

        定位并删除表中的重复和冗余索引（别建已经存在的索引，例如联合索引最左面的列，不要再单独建索引）

        尽量使用覆盖索引进行查询，避免回表