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

更新日期: 2020-01-24阅读: 1.7k标签: 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的主要面向场景,使用较少,这里就不展开讨论了。


六、总结

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


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

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

node如何和MySQL进行通信

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

MySQL 常用30种SQL查询语句优化方法

MySQL查询语句优化方法:应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

一个案例彻底弄懂如何正确使用 mysql inndb 联合索引

有一个业务是查询最新审核的5条数据,查看当时的监控情况 cpu 使用率是超过了100%,show processlist看到很多类似的查询都是处于create sort index的状态。索引有一个audit_time在左边的联合索引,没有关于status的索引。

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

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

MySQL技术内幕:InnoDB存储引擎

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

MYSQL中视图的使用

什么是视图?执行一条SQL,将结果集保存在一张虚拟表中。在视图创建后,可以用与表基本相同的方式使用(查询、过滤、排序数据、与其他视图或连结、(添加、更新))视图只是用来查看存储在别处的数据的设施,本身不包含数据,返回的数据也是从其他表检索出来的。

Mysql常用函数

MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。

mysql innodb索引原理

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

一条sql语句在mysql中是如何执行的

最近开始在学习mysql相关知识,自己根据学到的知识点,根据自己的理解整理分享出来,本篇文章会分析下一个sql语句在mysql中的执行流程,包括sql的查询在mysql内部会怎么流转,sql语句的更新是怎么完成的。

mysql统计行数时到底应该怎么count

每个人在写代码时都有遇到过要获取MYSQL表里数据行数的情况,多数人获取数据表行数时都用COUNT(*),但同时也流传了不少其他方式,比如说COUNT(1)、COUNT(主键)、COUNT(字段)。

点击更多...

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