MySQL的索引类型有哪些

embedded/2024/10/31 3:23:25/

MySQL的索引类型有哪些

MySQL数据库系统中的索引是用来加快搜索和检索数据库记录的数据结构。这些索引类似于书籍的目录,帮助MySQL快速定位数据表中的数据,而无需逐行扫描整个表。

索引解释

主键索引(PRIMARY KEY)

在MySQL中,主键索引是一种特殊的索引,用于保证表中每一行数据的唯一性。主键的设定是数据库设计中的一个重要环节,因为它不仅影响数据的完整性,还直接影响数据库的查询性能。

主键索引确保所在列的每个值都是唯一的,这意味着不能有重复的行。这是通过在底层数据库管理系统中强制实施唯一性约束来实现的。

由于主键索引的唯一性和数据库内部的优化机制(如B+树索引结构),主键查询通常比其他类型的查询更快,尤其是在处理大量数据时。

唯一索引(UNIQUE)

在MySQL中,唯一索引是用来确保一列或列组合中的值不重复,即每个值都必须在该列中是唯一的。这种索引类型是数据完整性策略的重要组成部分,特别是在处理不允许重复的数据字段时。

唯一索引不允许任何重复值存在于索引列。这对于如电子邮件地址、身份证号等需要保证唯一性的数据非常有用。

当尝试插入重复值时,数据库会抛出错误,从而防止数据完整性受到破坏。这是通过在数据库层面自动进行约束检查来实现的。

普通索引(INDEX)

普通索引,也简称为索引,在MySQL中是最基础的索引类型之一,用于提升数据检索的速度,使得数据库能更快地找到指定的数据行。普通索引没有唯一性要求,即允许索引列包含重复值。

不是所有的列都适合建立普通索引。通常,选择那些在WHERE子句、JOIN条件或ORDER BY子句中频繁出现的列来创建索引

虽然索引可以提高查询性能,但它们也会增加写操作(如插入、更新和删除)的成本,因为数据库需要同时维护索引数据结构。因此,应该避免对不经常查询的列创建索引

全文索引(FULLTEXT)

全文索引在MySQL中是专门为文本搜索设计的索引类型,允许对VARCHAR、CHAR或TEXT类型列中的数据进行快速的文本搜索。这种索引适用于包含大量文本的列,如文章内容、评论或任何其他形式的自由文本。

全文索引支持复杂的文本查询,包括词语的匹配和相关性排序。它使用一种称为倒排索引的数据结构,这种结构记录了每个单词出现在哪些文档中,以及它们在文档中的位置。

与传统的LIKE查询相比,全文索引支持更复杂的搜索操作,如自然语言搜索、布尔文本搜索等。这些功能通过特定的查询扩展(如MATCH() ... AGAINST()语法)提供。

全文索引查询不仅可以找出包含特定词汇的文档,还可以根据词汇出现的频率和文档中的位置,对搜索结果进行相关性评分,从而提供更加相关的搜索结果。

全文索引通常比其他类型的索引占用更多的磁盘空间,并且在构建索引时需要更多的处理时间。因此,建立全文索引前需要考虑到性能和存储成本。

当表中涉及全文索引的列更新时,索引也需要相应更新,这可能会影响写操作的性能。因此,对于频繁更新的数据,使用全文索引需要权衡利弊。

在MySQL中,全文索引最初只在MyISAM存储引擎上支持,但从MySQL 5.6版本开始,InnoDB也开始支持全文索引

组合索引(COMPOSITE)又叫联合索引,复合索引

组合索引(也称为联合索引或复合索引)在MySQL中是指在两个或多个列上创建的索引。这种类型的索引可以极大地提高查询性能,特别是当查询涉及多个列的组合时。

索引的最左前缀规则:MySQL在查询时遵循所谓的“最左前缀”规则,这意味着索引中的列在被查询时,必须按照索引创建时列的顺序来使用。例如,如果一个索引是按(列A, 列B, 列C)的顺序创建的,那么查询可以利用包含列A,列A和列B,或者全部三列的条件,但不能只利用列B和/或列C。

空间索引(SPATIAL)

空间索引是专门用于优化和处理空间数据查询的索引类型。在MySQL中,这种索引主要支持地理空间数据类型,如GEOMETRYPOINTLINESTRINGPOLYGON等。空间索引对于GIS(地理信息系统)应用和任何需要进行空间位置分析的应用来说是至关重要的。

空间索引使得查询如地理位置查找、空间距离计算和空间关系判断(例如,一个点是否在一个多边形内)等操作更加高效。这种索引类型使用特殊的数据结构(通常是R树),优化了空间数据的存储和查询。

空间索引支持各种空间查询操作,包括但不限于检测两个空间对象之间的关系(如相交、包含、邻接等)。

外键索引

MySQL的外键索引主要用于两个表之间建立联系,确保数据库的引用完整性。
外键是一个表中的字段,它是另一个表的主键。外键用于将一张表中的数据与另一张表的数据相关联。

  • 数据完整性:外键保证了数据库中的数据正确性和一致性。它确保了在一张表中引用的数据在另一张表中必须存在。
  • 约束和级联操作:外键可以定义不同的行为,例如在删除或更新关联表中的数据时采取的动作(如级联删除、级联更新等)。

前缀索引

前缀索引不会索引整个字段的全部内容,而是只索引字段的前N个字符。

在MySQL中,创建前缀索引的语法如下:

CREATE INDEX index_name ON table_name(column_name(length));

其中,length是你希望索引的字符数。例如,如果你有一个包含电子邮件地址的VARCHAR(100)字段,你可以只对前20个字符建立索引

CREATE INDEX idx_email_prefix ON users(email(20));

聚簇索引(clustered index)

MySQL中的聚簇索引(Clustered Index)是一种特殊类型的索引,它决定了表中行数据的物理存储顺序。与非聚簇索引(非聚簇索引存储数据和索引分开)相比,聚簇索引的关键特点是表数据直接存储在索引的叶子节点上。这意味着每个表只能有一个聚簇索引

二级索引(Secondary Index)

二级索引数据库中一个非常重要的功能,它允许对非主键列创建索引。这种索引有助于加快查询速度,尤其是在查询条件不包含主键的情况下。在一个表中,主键索引通常被称为主索引,而所有其他索引则被称为二级索引。二级索引的每一个条目包含索引的键值和一个指向该行主键的指针,这样就可以通过二级索引快速定位到表中的相应行。

多列索引

多列索引,又称为组合索引,是在数据库表的两个或更多列上建立的索引。这种索引可以极大地提高执行多列查询条件时的性能。在创建多列索引时,列的顺序非常关键,因为数据库引擎会根据索引中列的顺序来优化查询。例如,如果经常根据姓和名进行查询,则应该创建一个以姓和名为顺序的多列索引

覆盖索引

覆盖索引是指一个索引包含了查询中需要的所有数据。换句话说,如果一个查询能够仅通过索引就能获取到所需的全部数据,而无需回表查询实际的数据行,那么这个索引就被称为覆盖索引。使用覆盖索引可以显著提高查询性能,因为它减少了磁盘I/O的需求和数据行的访问。

哈希索引

哈希索引基于哈希表实现,适用于快速查找操作。在哈希索引中,索引项的存储位置是通过对键值进行哈希处理后得到的。这种索引的优势是提供了快速的查找性能,尤其是在等值查询(即精确查找)中。然而,哈希索引不支持范围查询和部分匹配查询,并且对键值的小变动可能导致存储位置的大变动。

B+树索引

B+树索引数据库中使用最广泛的索引类型之一。它是一种自平衡的树数据结构,可以保持数据有序。B+树索引的特点是所有实际的数据指针都存在于叶子节点,而内部节点仅存储键值,这使得B+树在进行范围查询时更加高效。由于其结构的有序性,B+树可以快速进行查找、插入和删除操作。

索引分类

按照功能和特性分类

  1. 主键索引(PRIMARY KEY)
    保证表中每一行数据的唯一性,并提供快速的数据访问能力。每个表只能有一个主键索引

  2. 唯一索引(UNIQUE)
    类似于主键索引,确保列中的所有值都是唯一的。不同之处在于表可以有多个唯一索引,且列可以接受NULL值。

  3. 普通索引(INDEX)
    最基本的索引类型,用于提高数据检索速度,无额外的唯一性或其他约束。

  4. 全文索引(FULLTEXT)
    专为文本数据设计,能够在字符数据中进行快速的搜索,常用于搜索引擎内部实现。

  5. 外键索引
    用于维护数据库的完整性,确保一个表中的字段值必须出现在另一个表的主键或唯一索引中。

  6. 聚簇索引(Clustered Index)
    数据存储与索引的顺序相同,一个表只能有一个聚簇索引

  7. 二级索引(Secondary Index)
    非聚簇索引,可以加速非主键字段的数据访问速度。

按照结构分类

  1. B+树索引
    适用于大部分数据库索引,特别是主键和二级索引,优化了范围查询的性能。

  2. 哈希索引
    基于哈希表实现,适用于等值查询,但不支持范围查询。

  3. 空间索引(SPATIAL)
    专为地理空间数据设计,如地图位置的存储和查询。

  4. 前缀索引
    针对文本字段的一种优化方式,只索引字段值的一部分前缀以减少索引大小。

按照组合方式分类

  1. 组合索引(COMPOSITE)
    又称联合索引或复合索引,是在两个或更多列上建立的索引,用于优化多条件查询。

  2. 覆盖索引
    索引包含所有查询所需的字段,查询操作不需要访问数据表,可以显著提高查询性能。

参考链接

  • 二级索引:https://en.wikipedia.org/wiki/Secondary_index
  • 多列索引:https://www.techopedia.com/definition/24029/composite-index
  • 覆盖索引:https://en.wikipedia.org/wiki/Covering_index
  • 哈希索引:https://en.wikipedia.org/wiki/Hash_table
  • B+树索引:https://en.wikipedia.org/wiki/B%2B_tree
  • 主键索引与唯一索引:https://www.techtarget.com/searchdatamanagement/definition/primary-key
  • 普通索引与全文索引:https://www.elastic.co/guide/en/elasticsearch/reference/current/full-text-indices.html
  • 组合索引与聚簇索引:https://www.sqlshack.com/understanding-clustered-indexes/
  • 二级索引与哈希索引:https://www.geeksforgeeks.org/indexing-in-databases-set-1/
  • B+树索引与空间索引:https://www.postgresql.org/docs/current/indexes-types.html

在这里插入图片描述


http://www.ppmy.cn/embedded/9360.html

相关文章

13-LINUX--消息队列

一.消息队列 1.消息队列:消息队列为一个进程向另一个进程发送一个数据块提供了条件,每个数据块会包含一个类型。 2.相关函数 1>.msgget(key_t key,int msgflg) : 创建消息队列 2>. msgsnd:把消息添加到消息队列 3>.msgrcv &#xf…

如何利用R语言获取worldclim30s的气候数据

要使用R语言获取WorldClim30s的气候数据,你可以使用raster包来处理栅格数据,而且getData()函数可以方便地从WorldClim网站下载数据。以下是一个获取WorldClim30s数据的基本步骤: 安装和加载必要的包: 在R中,首先要确保…

Web前端-Vue

黑马程序员JavaWeb开发教程 文章目录 一、什么是Vue1、Vue概念2、Vue快速入门(1)快速入门(2)插值表达式 三、Vue常用指令1、常用指令2、指令使用详解(1)v-bind(2)v-model&#xff08…

面试十八、容器适配器

容器适配器是一种特殊类型的容器,它们提供了一种不同于常规容器的接口和行为。容器适配器通常是建立在其他容器之上,通过改变接口或添加限制来满足特定的需求或解决特定的问题。 在 C 中,标准库提供了三种常见的容器适配器: 栈&am…

解决宝塔面板无法访问(无法访问或拒绝链接)

🐌博主主页:🐌​倔强的大蜗牛🐌​ 📚专栏分类:Linux ❤️感谢大家点赞👍收藏⭐评论✍️ 问题如下: 本人设置了授权IP,但是有些问题,所以是打算取消授权IP 重…

轮转数组(Leedcode)的题目

题目:给定一个整数数组 nums,将数组中的元素向右轮转 k 个位置,其中 k 是非负数。 示例 1: 输入: nums [1,2,3,4,5,6,7], k 3 输出: [5,6,7,1,2,3,4] 解释: 向右轮转 1 步: [7,1,2,3,4,5,6] 向右轮转 2 步: [6,7,1,2,3,4,5] 向右轮转 3 步…

如何查看Debian Linux的内核版本

2024年4月19日,周五上午 uname -r

HBuilder如何发布前端H5应用

目录 一、点击菜单 “发行” -> “网站-PC Web或手机H5...”二、输入网站标题和网站域名,点击【发行】按钮三、创建应用1.登录 DCLOUD 开发者中心2.点击【创建应用】按钮,进入创建应用页面3.应用类型选uni-app,输入应用名称、应用描述后提…