MySQL索引分类,90%的开发都不知道

时间: 2020-01-24阅读: 737标签: mysql

MySQL的索引分类问题一直让人头疼,几乎所有的资料都会给你列一个长长的清单,给你介绍什么主键索引、单值索引,覆盖索引,自适应哈希索引,全文索引,聚簇索引,非聚簇索引等……给人的感觉就是云里雾里,好像MySQL索引的实现方式有很多种,但是都没有一个清晰的分类。所以本人尝试总结了一下如何给MySQL的索引类型分类,便于大家记忆,由于MySQL中支持多种存储引擎,在不同的存储引擎中实现略微有所差距,下文中如果没有特殊声明,默认指的都是InnoDB存储引擎。

索引从不同维度划分可以有很多种名称,但是需要明确一个问题—— 索引的本质是一种数据结构,其他索引的划分则是针对实际应用而言。


一、根据底层数据结构划分

索引是提高查询效率的数据结构,而能够提高查询效率的数据结构有很多,如二叉搜索树,红黑树,跳表,哈希表(散列表)等,而MySQL中用到了B+Tree和散列表(Hash表)作为索引的底层数据结构(其实也用到了跳表实现全文索引,但这不是重要考点,所以可以忽略)。

1. hash索引

MySQL并没有显式支持Hash索引,而是作为内部的一种优化。具体在Innodb存储引擎里,会监控对表上二级索引的查找,如果发现某二级索引被频繁访问,二级索引成为热数据,就为之建立hash索引。因此,在MySQL的Innodb里,对于热点的数据会自动生成Hash索引。这种hash索引,根据其使用的场景特点,也叫自适应Hash索引。

2. B+树索引

这个是MySQL索引的基本实现方式。除了全文索引、hash索引,Innodb、MyISAM的索引都是通过B+树实现的。


二、根据索引字段个数划分

为了能应对不同的数据检索需求,索引既可以仅包含一个字段,也可以同时包含多个字段。单个字段组成的索引可以称为单值索引,否则称之为复合索引,也称为组合索引或多值索引。

这个很好理解,假如我们有一张表,有三个属性,分别是 id,age 和 name 。假如在id上建立索引,那这就是单值索引;如果在 name 和 age 上建立索引,那这就是复合索引。

复合索引的索引的数据顺序跟字段的顺序相关,包含多个值的索引中,如果当前面字段的值重复时,将会按照其后面的值进行排序。


使用覆盖索引的前提是字段长度比较短,对于值长度较长的字段则不适合使用覆盖索引,原因有很多,比如索引一般存储在内存中,如果占用空间较大,则可能会从磁盘中加载,影响性能。


三、根据是否是在主键上建立的索引进行划分

1. 主键索引

MySQL中是根据主键来组织数据的,所以每张表都必须有主键索引,主键索引只能有一个,不能为null同时必须保证唯一性。建表时如果没有指定主键索引,则会自动生成一个隐藏的字段作为主键索引。

2. 辅助索引

如果不是主键索引,则就可以称之为非主键索引,又可以称之为辅助索引或者二级索引。主键索引的叶子节点存储了完整的数据行,而非主键索引的叶子节点存储的则是主键索引值,通过非主键索引查询数据时,会先查找到主键索引,然后再到主键索引上去查找对应的数据。

在这里假设我们有张表user,具有三列:ID,age,name,create_time,id是主键,(age,create_time,,name)建立辅助索引。执行如下sql语句:

select name from user where age>2 order by create_time desc。

正常的话,查询分两步:

1.按照辅助索引,查找到记录的主键,

2.按照主键主键索引里查找记录,返回name。

但实际上,我们可以看到,辅助索引节点是按照age,create_time,name建立的,索引信息里完全包含我们所要的信息,如果能从辅助索引里返回name信息,则第二步是完全没有必要的,可以极大提升查询速度。

按照这种思想Innodb里针对使用辅助索引的查询场景做了优化,叫覆盖索引(在这里小声吐槽一下,不知道业界起这种名词干嘛,太容易引起歧义了,叫个索引覆盖查询不是更好吗)。


四、根据数据与索引的存储关联性划分

根据数据与索引的存储关联性,可以分为聚簇索引和非聚簇索引(也叫聚集索引和非聚集索引)。聚簇索引也叫簇类索引,是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。 整个简洁的说法,这俩 的 区别就是索引的存储顺序和数据的存储顺序是否是关系 的,有关就是聚簇索引,无 关就是非聚簇索引 。 具体实现方式根据索引的数据结构不同会有所不同。 下面以B+树实现的索引为例,举例来说明聚簇索引和非聚簇索引。

1. 聚簇索引

Innodb的主键索引,非叶子节点存储的是索引指针,叶子节点存储的是既有索引也有数据,是典型的聚簇索引(这里可以发现,索引和数据的存储顺序是强相关的。因此是典型的聚簇索引),如图:


2. 非聚簇索引

MyISAM中索引和数据文件分开存储,B+Tree的叶子节点存储的是数据存放的地址,而不是具体的数据,是典型的非聚簇索引;换言之, 数据可以在磁盘上随便找地方存,索引也可以在磁盘上随便找地方存,只要叶子节点记录对了数据存放地址就行。因此,索引存储顺序和数据存储关系毫无关联,是典型的非聚簇索引,另外Inndob里的辅助索引也是非聚簇索引。



五、其他分类

1. 唯一索引

顾名思义,不允许具有索引值相同的行,从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值,并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查, 如果有重复的值,则会操作失败,抛出异常。

需要注意的是,主键索引一定是唯一索引,而唯一索引不一定是主键索引。唯一索引可以理解为仅仅是将索引设置一个唯一性的属性。

2. 全文索引

在MySQL 5.6版本以前,只有MyISAM存储引擎支持全文引擎。在5.6版本中,InnoDB加入了对全文索引的支持,但是不支持中文全文索引.在5.7.6版本,MySQL内置了ngram全文解析器,用来支持亚洲语种的分词。主要用来利用关键词查询文本,不是MySQL的主要面向场景,使用较少,这里就不展开讨论了。


六、总结

最后总结一张脑图方便记忆:


作者:马志超,腾讯云数据库研发工程师
站长推荐

1.云服务推荐: 国内主流云服务商,各类云产品的最新活动,优惠券领取。地址:阿里云腾讯云华为云

链接: http://www.fly63.com/article/detial/8352

mysql innodb索引原理

innodb存储引擎表是索引组织表,表中数据按照主键顺序存放。其聚集索引就是按照每张表的主键顺序构造一颗B+树,其叶子结点中存放的就是整张表的行记录数据,这些叶子节点成为数据页。

mysql数据库使用insert语句插入中文数据报错

在mysql的命令行模式中,通过insert语句插入中文数据的时候报错,类似于下面这样,造成这个错误通常是由于创建数据表的时候使用了不正确的编码格式,可以使用如下命令查看操作的目标数据表的编码格式。

MySQL有哪些索引类型?

从数据结构角度B+树索引(O(log(n)));hash索引;FULLTEXT索引(现在MyISAM和InnoDB引擎都支持了);R-Tree索引(用于对GIS数据类型创建SPATIAL索引)

MySQL技术内幕:InnoDB存储引擎

本文绝大部分内容来源《MySQL技术内幕:InnoDB存储引擎》一书。InnoDB存储引擎是多线程模型,其后台有多个不同的后台线程,负责处理不同的任务。

Mysql使用索引可能失效的场景

WHERE字句的查询条件里有不等于号(WHERE column!=…),MYSQL将无法使用索引;类似地,如果WHERE字句的查询条件里使用了函数(如:WHERE DAY(column)=…),MYSQL将无法使用索引

MySQL设置时区和默认编码

学习spring boot时要在Windows本地安装MySQL5.7,配置好之后项目,启动之后提示需要设置时区,并且在使用过程中发现出现乱码,中文无法显示,出现上述问题的主要是MySQL5.7中没有设置时区和编码。

mysql图形化管理工_ mysql可视化工具哪个好?

MySQL是一个非常流行的小型关系型数据库管理系统。目前MySQL被广泛地应用在Internet上的 中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据 库

node如何和MySQL进行通信

Node.js与MySQL交互操作有很多库,常用最多的是mysql模块,mysql数注意:安装前先把目录cd到node.exe所在目录下,这样执行安装命令时,这篇文章主要介绍:链接mysql的流程、数据库连接参数说明、MYSQL CURD操作、连接池Pooling connections、断线重连、防止SQL注入

前端开发对MySql使用总结

数据库引擎使用,这里仅仅只介绍常用的两种引擎,而InnoDB是从MySQL 5.6.版本以后InnoDB就是作为默认启动使用的存储引擎。那么这里下面既是对上面几个概念进行基本的日常操作。

MySQL中Explain初识

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化。EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 Explain 就可以了。

点击更多...

内容以共享、参考、研究为目的,不存在任何商业目的。其版权属原作者所有,如有侵权或违规,请与小编联系!情况属实本人将予以删除!