MySQL索引结构与优化

InnoDB存储引擎支持以下几种常见的索引：B+树索引、全文索引、哈希索引，其中比较关键的是B+树索引

B+树索引

InnoDB中的索引自然也是按照B+树来组织的，B+Tree的特点有单一节点存储更多元素，树层高低，每次查找都在需找到叶子节点，叶子节点有双向链表。

聚集/聚簇索引（速度快）

InnoDB中使用了聚集索引，就是将表的主键用来构造一棵B+树，聚集索引的叶子节点就是数据页。若没定义主键，MySQL可以用隐藏列RowID来构建B+Tree，一张表有且只有一个聚簇索引。

 二级、辅助索引（可能需要回表）

将索引列用B+树构建的，叶子节点存放主键ID。如果需要索引列与ID以外的数据，就需要拿着主键ID去聚集索引中去找到该条完整数据，这个操作就叫回表。

如何在Explain执行计划中看有没有回表

在附加信息Extra字段中，有using index；的就证明没有回表操作。

联合/复合索引（若覆盖索引则不用回表）

 也是使用B+Tree，他的组合方式是先用第一个字段排序，再在第一个的基础上对后面的索引排序，下图是索引排序规则图。从原理可知，为什么有最左前缀法则。如果不从左边第一个开始查索引，后面的索引列看起来都是乱序的。

覆盖索引就是说此索引中包含了select后所有需要查询的列，这样就不用回表，相比于聚集索引，这里面不包含全部数据，因此可以减少大量的IO操作。

索引下推可以减少回表次数，简单来说就是多个字段查询 先都在索引中过滤数据，再回表。

哈希索引

InnoDB存储引擎除了我们前面所说的各种索引，还有一种自适应哈希索引，我们知道B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度一般为3、4层,故需要3、4次的IO查询。

在InnoDB存储引擎内部自动去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，Innodb三大特性之一，创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查询三四次节点的效率高了不少。

InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。注意，对于自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干预。

全文索引（FULLTEXT 基本不用）

MySQL从设计之初就是关系型数据库，存储引擎虽然支持全文检索，整体架构上对全文检索支持并不好而且限制很多，比如每张表只能有一个全文检索的索引，不支持没有单词界定符( delimiter）的语言，如中文、日语、韩语等。

其原理就是用倒排索引，记录每个单词在文档中出现的位置。真的有这方面需求，还是推荐使用ElasticSearch.

建立索引的原则

1、索引列的类型尽量小，能用Int就别用Bigint；

2、索引列数据离散度要高，也就是相同的数据不要占全部数据比值太高。比如sex列就不适合；

3、对大字段（blob、各种text、大varchar）要限制索引长度；

4、只为用于select、order by或group by的列创建索引；

5、使用联合索引并将离散性最高的列放到索引最前列，范围查询的放后面；

6、尽量使用覆盖索引，比索引合并与回表效率高；

7、不要给每个列都建立索引。

Intersection索引合并

MySQL在一般情况下只会走一条索引，只有在特殊条件下，会进行Intersection索引合并，也就是多个二级索引中查询到的结果（主键值）取交集，为了减少回表产生的大量随机IO，根据成本考虑会走二级索引，大部分情况下二级索引中进行查找是顺序IO。

那么何时走索引合并？

1、可以用到的所有索引列等值匹配的时候。

2、主键ID列范围查找的时候

3、Union合并，使用OR时的等值匹配

4、计算合并成本小于回表成本时

三星索引

是索引的评级规则

第一星：where后面的等值谓词，可以匹配索引列的顺序：意义在于谓词匹配的越多，索引片越窄，最终扫描的数据行也是越小。

第二星：order by的排序是否和索引的顺序一致：意义在于避免进行额外的排序，增加消耗。

第三星：select的字段是否都为索引列：意义在于避免每一个索引行查询，都需要去聚簇索引进行一次随机IO查询。

索引失效的场景

1、使用不等于(!= 或者<>)的时候；

2、使用is null、is not null查询时，对于列的声明尽可能的不要允许为null。

innodb_stats_method默认nulls_equal：认为所有NULL值都是相等的。

如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。

3、违反最左前缀法则，

        例如 like %周% ，

        联合索引没从最左侧元素开始查询，或中间缺少字段。

        例如建立索引为（name,age,contact）

        失效场景1：where age=67 and contact = 1331;

        失效场景2：where name like '%树%';

        未完全匹配场景3：where name='周树' and contact = 1331;

4、隐式转换，例如 字段类型是字符串的，没加引号；

5、使用or 不同列时，有一列没有加索引。可加索引或者用Union连接；

6、使用函数、表达式等； 

7、联合索引中，范围查询在其他列前的，

例如： where age>10 and name='周树';会部分失效。看索引长度可以发现。