mysql普通索引回表_mysql系列:全网最全索引类型汇总

1、mysql中有哪些索引类型?

聚簇索引 (Clustered Index)web

非聚簇索引sql

主键索引(PRIMARY KEY)数据库

辅助索引(Secondary Indexes)缓存

HASH索引微信

BTREE索引数据结构

T-TREE索引工具

R-Tree索引性能

惟一索引 (UNIQUE Indexs)

普通索引 (Normal index)

全文索引 (FULLTEXT Indexes)

空间索引 (Spatial indexes)

组合索引 (Multiple-Column Indexes)

覆盖索引

自适应hash索引(Adaptive Hash Index)

倒序索引 (Descending Indexes)

不可见索引(Invisible Indexes)

吓了一跳吧,你说出了几个索引类型。

2、索引类型说明

一、按索引是否包含数据来分类

聚簇索引:

将数据存储与索引放到了一块,找到索引也就找到了数据,不须要根据主键或行号去进行回表查询。

非聚簇索引:

非聚簇索引就是指B+Tree的叶子节点上的data,并非数据自己,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key必定得是惟一的。主要用在MyISAM存储引擎中.

MyISAM引擎使用B+Tree做为索引结构,叶节点的data域存放的是数据记录的地址。

二、按索引是否创建在主键上分类

主键索引:

在MySQL的主键上建立的索引就是主键索引,主键索引会自动建立,一个表只能有一个主键索引,同时主键索引也是惟一索引。

辅助索引:

在聚簇索引之上建立的索引称之为辅助索引,辅助索引访问数据老是须要二次查找,非聚簇索引都是辅助索引,像复合索引、前缀索引、惟一索引,innodb中辅助索引叶子节点存储的再也不是行的物理位置,而是键值和主键 ID。

7420b07fa3ad27563bc8fcd662498acc.png

注意:

InnoDB引擎支持聚簇索引,MyISAM引擎不支持聚簇索引。

因此,主键索引不必定是聚簇索引。

三、按索引的底层数据结构分类

HASH索引

6809e52f04c9d5dd296cf7d09e685005.png

Hash 索引的特性:

一、等值查询较快,可是不稳定

二、不能使用范围查询

三、不能避免数据排序

四、不能利用组合索引的部分字段进行查询

五、不支持模糊查询

六、避免不了回表查询

B+Tree索引

1b9d15ce373fbbba840505ceb36e05f6.png

B+Tree索引的特性:

1.全部关键字都出如今叶子结点的链表中(稠密索引),且链表中的关键字刚好是有序的;

2.内节点不存储data,只存储key;叶子节点不存储指针。

3.不可能在非叶子结点命中;

4.非叶子结点至关因而叶子结点的索引(稀疏索引),叶子结点至关因而存储(关键字)数据的数据层;

五、B+Tree的每一个叶子节点增长一个指向相邻叶子节点的指针,就造成了带有顺序访问指针的B+Tree。作这个优化的目的是为了提升区间访问的性能

6.更适合文件索引系统;

mysql各个存储引擎支持的索引类型:

1ff874664cbce5f0ca7f82d2d6f8c2e2.png

四、按索引的常规功能分类

惟一索引 (UNIQUE Indexs)

要求索引列的全部值都只能出现一次,即必须惟一。

普通索引 (Normal index)

仅用来提升查询速度,没有其余特性。

全文索引 (FULLTEXT Indexes)

MySQL能够经过创建全文索引,利用查询关键字和查询列内容之间的相关度进行检索,能够利用全文索引来提升匹配的速度。好比实现全匹配模糊查询。

可是实际场景测试mysql的全文索引性能很是不稳定,不建议生产环境使用。须要使用全文检索的地方,仍是推荐使用Elasticsearch

空间索引 (Spatial indexes)

空间索引使用R树,R树是用于索引多维数据的专用数据结构。

这4类索引也是咱们可使用Navicat等客户端工具,可以主动建立的4类索引。

1a9c386b89fe3578a78c121092b0f769.png

五、按索引是否包含多个字段分类

多字段索引 (Multiple-Column Indexes)

也叫组合索引(composite indexes),即索引中包含多个表字段。

样例语句:CREATE TABLE test (

id INT NOT NULL,

last_name CHAR(30) NOT NULL,

1581Verifying Index Usage

first_name CHAR(30) NOT NULL,

PRIMARY KEY (id),

INDEX name (last_name,first_name)

);

六、其余特性索引

T-TREE索引

BTREE索引由NDB存储引擎实现为T树索引,算是BTREE索引在NDB存储引擎中的升级实现。

R-Tree索引

从MySQL 8.0.12开始,R-Tree索引开始在SPATIAL索引中使用。

MySQL对空间列上的SPATIAL索引使用R-Trees进行二次分割。

自适应hash索引(Adaptive Hash Index)

是InnoDB存储引擎中的内存结构的组成部分。

InnoDB存储引擎会监控对表上各索引页的查询,若是观察到创建hash索引能够提升查询速度,则自动创建hash索引。这就是自适应哈希索引(Adaptive Hash Index,AHI)

AHI是经过缓存池的B+树页构造而来,所以创建的速度很快,并且不须要对整张表构建hash索引。

InnoDB存储引擎会自动根据访问的频率和模式来自动的为某些热点也创建hash索引。

覆盖索引

若是一个索引包含(或覆盖)全部须要查询的字段的值,称为‘覆盖索引’。即只需扫描索引而无须回表。

这个概念很是重要,灵活运用对SQL优化很是有帮助。

1403303f5607785abac1024c88e67665.png

c657cdc48f5fc45d39ce9d54da6b7afe.png

好比这2个查询语句,一样是全匹配模糊查询,第二个sql却能使用索引。缘由是就是利用了覆盖索引的概念,减小了回表查询。

降序索引 (Descending Indexes)

从MySQL 8.0开始支持降序索引了。其实,从语法上,MySQL 4就支持了,但正如官方文档所言,"they are parsed but ignored",实际建立的仍是升序索引。

MySQL支持降序索引:再也不忽略索引定义中的DESC,而是致使键值的降序存储。

降序索引的意义:

若是一个查询,须要对多个列进行排序,且顺序要求不一致。在这种场景下,要想避免数据库额外的排序-“filesort”,只能使用降序索引。CREATE TABLE  t (

c1 INT, c2 INT,

INDEX idx1 (c1 ASC, c2 ASC),

INDEX idx2 (c1 ASC, c2 DESC),

INDEX idx3 (c1 DESC, c2 ASC),

INDEX idx4 (c1 DESC, c2 DESC)

);ORDER BY c1 ASC, c2 ASC -- optimizer can use idx1

ORDER BY c1 DESC, c2 DESC -- optimizer can use idx4

ORDER BY c1 ASC, c2 DESC -- optimizer can use idx2

ORDER BY c1 DESC, c2 ASC -- optimizer can use idx3

不可见索引

MySQL支持不可见索引;也就是说,优化器未使用的索引。该功能适用于除主键(显式或隐式)之外的索引。

不可见的索引能够测试删除索引对查询性能的影响,而无需

进行破坏性的更改,若是最终须要索引,则必须撤消该更改。降低和

对于大型表,从新添加索引可能会很是昂贵,而使其不可见和可见则是快速的就地操做。

简单来讲,就是可使索引不起做用,对查询优化器不可见。通常在调试索引对查询性能影响的时候使用。至关于索引的一个开关。

建立不可见索引的三种方式:CREATE TABLE t1 (

i INT,

j INT,

k INT,

INDEX i_idx (i) INVISIBLE

) ENGINE= InnoDB;

CREATE INDEX j_idx ON t1 (j) INVISIBLE;

ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

修改一个索引的可见性:ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;

ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

总结

本文比较全面的介绍了mysql中索引类型。

你还知道Mysql中哪些其余的索引类型,欢迎留言交流。

更多精彩,关注我吧。

a58c619c05424001bf2a5884.html

图注:跟着老万学java

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值