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='周树';会部分失效。看索引长度可以发现。