草庐IT

MySQL 10几种索引类型，你都清楚吗？

迷路的架构师 2023-03-28 原文
今天整理了一下 MySQL 的索引，一般人只知道3~4个，但我经常听到有10几个之多，如下图：

索引

是不是很多都听过，但是只是想不起来。我大概归了一下类：

索引分类

密度

按照密度分，可分为稠密索引和稀疏索引，我们常见的MySQL Innodb 引擎使用的是稠密索引。一般列式数据库采用的是稀疏索引，如 Clickhouse。

稠密索引

稠密索引（Dense Index）也称为完全索引，是一种将每个记录（行）都映射到一个索引项（Index Entry）的索引方式。这种索引方式对于每一条记录都会创建一个索引项，因此它可以支持高效的查询和排序操作。但是，由于每个记录都需要一个索引项，因此它会占用更多的存储空间，特别是在大型表中使用时，可能会导致索引文件的过大，从而降低查询性能。

稀疏索引

稀疏索引（Sparse Index）也称为部分索引，是一种仅为部分记录创建索引项的索引方式。它仅为某些值创建索引项，而其他值则没有索引项。这种索引方式可以减少索引文件的大小，并且可以提高插入和更新的性能。但是，由于它需要进行更多的磁盘访问来执行查询操作，因此查询性能可能会受到影响。

稠密索引和稀疏索引

存储

按照存储分类，可以分为聚簇索引和非聚簇索引。

聚簇索引

聚簇索引（Clustered Index）是一种索引方式，它将数据存储在磁盘上，并且按照索引的顺序进行排序。它可以将相邻的行存储在相邻的磁盘页上，从而提高查询的性能。聚簇索引只能为表创建一个索引，因为每个表只能以一种方式进行排序。聚簇索引对于经常需要根据特定列进行查询的表非常有用，因为它们可以快速定位数据。

非聚簇索引

非聚簇索引（Non-Clustered Index）是一种索引方式，它将索引数据存储在单独的数据结构中，而不是存储在表的磁盘上。它包含了指向表中每行的指针，并按照索引列的顺序进行排序。这种索引方式可以为表创建多个索引，并且可以根据多个列进行排序。非聚簇索引对于经常需要根据不同的列进行查询的表非常有用，因为它们可以快速定位数据。

在使用聚簇索引时，需要注意以下几点：

  • 每个表只能有一个聚簇索引。
  • 聚簇索引的排序方式对查询性能有很大的影响，因此需要仔细选择聚簇索引的列。
  • 聚簇索引的创建和维护可能会占用较多的磁盘空间和时间。
在使用非聚簇索引时，需要注意以下几点：

  • 非聚簇索引可以为表创建多个索引，因此可以根据多个列进行排序。
  • 非聚簇索引可以减少查询时间，但是需要额外的空间存储索引数据结构。
  • 对于经常进行插入、更新和删除操作的表，非聚簇索引需要频繁更新，因此可能会影响性能。

算法

按照算法分类，可以分为B+树索引和哈希索引。

B+树索引

B+树索引是一种常用的数据库索引结构，它是基于B树的一种变体。B+树索引与B树的区别在于，它的非叶子节点不存储数据，只存储索引，而所有数据都存储在叶子节点中。这种结构使得B+树索引的查询速度更快，因为在进行范围查询时，只需要遍历叶子节点即可。

B+树索引的特点如下：

  1. 所有数据都存储在叶子节点中，非叶子节点只存储索引，因此可以存储更多的索引数据。
  2. 叶子节点之间形成一个有序的链表，方便进行范围查询。
  3. B+树索引的深度较浅，因此查询速度快，同时也减少了磁盘I/O操作的次数，提高了性能。

B+树

Hash索引

Hash索引是一种基于哈希表实现的数据库索引结构。在Hash索引中，每个索引项包含两部分，一个是关键字的哈希值，另一个是指向存储该关键字的数据块的指针。

Hash索引的查询速度非常快，因为它通过哈希函数将关键字转换为固定长度的哈希值，然后根据哈希值直接访问索引项。由于哈希值是唯一的，因此可以直接找到存储数据的位置，不需要进行比较操作。

Hash索引的优点包括：

  1. 查询速度快，查询性能稳定。
  2. 索引构建速度快，适合处理大量数据。
  3. 支持等值查询，适用于一些需要高速查询的场景。
Hash索引的缺点包括：

  1. 不支持范围查询、模糊查询和排序操作。
  2. 哈希函数的选择和设计比较关键，不合适的哈希函数可能导致哈希冲突，进而影响查询效率。
  3. Hash索引在内存中存储，如果数据过大，可能会导致内存不足的问题。
Hash索引的使用场景包括：

  1. 需要快速查找具有唯一性的数据的表，例如用户表、商品表等。
  2. 数据量较大、查询操作较多、更新操作较少的表，例如日志表、统计表等。

Hash索引

优化

从优化的角度分类，可分为前缀索引、复合索引、覆盖索引。

前缀索引

前缀索引是一种基于字符串前缀的数据库索引结构。在前缀索引中，对于字符串类型的列，可以只对其前几个字符建立索引，而不是对整个字符串进行索引。这样可以大大减小索引的存储空间，同时也可以提高查询效率。

例如，对于一个名字列，如果需要进行模糊查询，可以只对名字的前几个字符建立索引，这样可以有效减小索引的存储空间，同时也可以提高查询效率。一般来说，前缀索引的长度越短，索引的存储空间就越小，但是查询效率也会受到影响。

前缀索引的优点包括：

  1. 可以减小索引的存储空间，适用于字符串类型的列。
  2. 可以提高查询效率，特别是在模糊查询等操作中。
前缀索引的缺点包括：

  1. 可能会出现重复的索引项，进而影响查询效率。
  2. 前缀长度的选择比较关键，如果长度太短，可能会出现大量的哈希冲突，如果长度太长，可能会导致索引的存储空间变大。
前缀索引的使用场景包括：

  1. 需要对字符串类型的列进行模糊查询、前缀匹配等操作的表。
  2. 数据量较大、查询操作较多、更新操作较少的表，例如日志表、统计表等。
前缀索引是一种非常实用的数据库索引结构，可以大大提高查询效率，减小索引的存储空间。但是需要注意的是，在选择前缀长度时需要根据具体的业务需求进行选择，不合适的前缀长度可能会影响查询效率。

复合索引

复合索引是一种将多个列组合在一起来创建的索引，用于优化查询中涉及到多个列的查询效率。相对于单列索引，复合索引可以更有效地支持多列的查询，提高查询效率。

在创建复合索引时，可以选择多个列，并按照顺序依次排列。这样可以使得查询更加高效，因为复合索引的叶子节点存储的是多列的值，可以直接满足多列查询的需求，避免了查询时多次进行索引扫描的开销。

例如，对于一个包含姓名和年龄的表，可以创建一个复合索引，将这三列按照顺序依次排列。这样，当需要查询姓名、性别和年龄都满足一定条件的记录时，可以直接使用复合索引来完成查询，避免了多次索引扫描的开销，从而提高了查询效率。

复合索引的优点包括：

  1. 可以提高多列查询的效率，避免多次索引扫描的开销。
  2. 可以减小索引的存储空间，适用于需要对多个列进行查询的表。
复合索引的缺点包括：

  1. 复合索引的维护成本比单列索引高，因为需要对多个列进行维护。
  2. 对于一些单列查询，复合索引可能并不适用，因为需要扫描复合索引的叶子节点，而单列索引可以直接定位到需要的记录。
复合索引的使用场景包括：

  1. 需要对多列进行查询的表，例如包含姓名和年龄等多个列的表。
  2. 查询操作较多，更新操作较少的表。

复合索引

复合索引是一种非常实用的数据库索引结构，可以提高多列查询的效率，减小索引的存储空间。但是需要注意的是，在创建复合索引时需要根据具体的业务需求进行选择，不合适的复合索引可能会影响查询效率。

覆盖索引

覆盖索引是一种特殊的索引，它包含了所有需要查询的列的数据，而不需要进一步的查找操作就可以直接返回查询结果。这种索引也被称为索引覆盖或索引包含查询。

覆盖索引的原理是在索引结构中包含了查询所需要的列，因此数据库不需要再到数据表中查找所需的列。这样可以减少磁盘I/O操作，从而提高查询效率，特别是对于大型的数据表和频繁的查询操作。

例如，如果有一个包含姓名和年龄的表，并且需要查询所有男性的姓名和年龄。可以创建一个复合索引，按照性别、姓名和年龄的顺序依次排列。这样，查询时可以直接使用这个复合索引，并且由于该索引包含了查询所需的姓名和年龄信息，不需要进一步的查找操作就可以直接返回查询结果，从而提高了查询效率。

覆盖索引的优点包括：

  1. 可以减少磁盘I/O操作，从而提高查询效率。
  2. 可以减少CPU和内存的开销，特别是对于大型数据表和频繁的查询操作。
覆盖索引的缺点包括：

  1. 只有当需要查询的列都在索引中时，才能使用覆盖索引。否则，仍然需要到数据表中查找所需的列。
  2. 创建覆盖索引需要占用更多的磁盘空间。
覆盖索引的使用场景包括：

  1. 需要频繁进行查询操作的表。
  2. 查询操作需要使用多个列的信息。

覆盖索引

覆盖索引是一种特殊的复合索引，可以减少磁盘I/O操作，提高查询效率。但是需要注意的是，只有当需要查询的列都在索引中时才能使用覆盖索引，否则仍然需要到数据表中查找所需的列。因此，在创建覆盖索引时需要根据具体的业务需求进行选择。

功能

按照功能的角度划分，可以分为普通索引、唯一索引、全文索引。

普通索引

普通索引是数据库中最基本的索引结构，也被称为单列索引或简单索引。它只包含一个列的值和指向该行的指针，用于加速对该列的单列查询。可以对表的任意列创建普通索引，但通常建议对经常进行查询和排序的列创建索引，例如主键列和外键列等。

普通索引的原理是将所需要查询的列作为索引列，按照索引列的值建立索引。当查询该列时，数据库系统会先在索引结构中进行查找，然后根据索引中的指针到数据表中找到对应的行。由于普通索引只包含一个列的值和指向该行的指针，因此查询时需要在数据表中找到其他所需的列的值。

普通索引的优点包括：

  1. 可以加速单列查询的速度，特别是对于大型数据表和频繁的查询操作。
  2. 可以提高数据的访问效率，从而加快数据的处理速度。
普通索引的缺点包括：

  1. 当需要查询的列不在索引列中时，需要进行额外的查找操作，从而降低查询效率。
  2. 创建普通索引需要占用额外的磁盘空间和内存空间，可能会对写入操作的性能产生一定的影响。
普通索引的使用场景包括：

  1. 经常进行查询和排序操作的列。
  2. 需要经常进行连接操作的表的外键列。
  3. 数据表中需要保证唯一性的列，如主键列等。
普通索引是数据库中最基本的索引结构，用于加速对单列查询的速度，提高数据的访问效率。在创建普通索引时需要根据具体的业务需求进行选择，避免对写入操作的性能产生过大的影响。

唯一索引

唯一索引是一种限制数据库表中列值唯一性的索引，用于保证在指定列上没有重复的数据。与普通索引不同，唯一索引在索引列中的每个值都是唯一的，且不允许插入重复值，包括 NULL 值。

唯一索引的原理与普通索引类似，只是对于唯一索引而言，如果插入或更新操作的列值已经存在，数据库系统会抛出一个唯一性冲突的错误。唯一索引可以用于加速唯一性约束条件的验证，从而提高数据的访问效率。

唯一索引的优点包括：

  1. 可以保证数据表中的列值唯一性，避免重复数据的插入。
  2. 可以提高数据的访问效率，加速唯一性约束条件的验证。
唯一索引的缺点包括：

  1. 创建唯一索引需要占用额外的磁盘空间和内存空间，可能会对写入操作的性能产生一定的影响。
  2. 如果需要对多个列进行唯一性约束，需要创建多个唯一索引，会占用更多的磁盘空间和内存空间。
唯一索引的使用场景包括：

  1. 需要对数据表中的某一列或多个列进行唯一性约束的情况。
  2. 经常进行查询和排序操作的列需要保证唯一性。
唯一索引是一种用于限制数据库表中列值唯一性的索引，可以保证数据表中的列值唯一性，提高数据的访问效率。在创建唯一索引时需要根据具体的业务需求进行选择，避免对写入操作的性能产生过大的影响。

全文索引

全文索引是一种基于文本内容的索引技术，可以快速地检索出包含指定关键词或短语的文档或记录。相比于传统的索引技术，全文索引更加适用于文本数据的搜索和查询。

全文索引通常使用倒排索引（Inverted Index）的数据结构，将每个单词或短语出现的位置作为索引项，以便进行快速的查找和匹配。倒排索引可以理解为是一张单词词表，每个单词都指向包含该单词的文档或记录的位置。通过倒排索引，可以快速地找到包含指定单词的文档或记录，以及它们出现的位置。

全文索引的优点包括：

  1. 可以对文本数据进行高效的搜索和查询，提高数据的访问效率。
  2. 可以支持模糊搜索和短语搜索等功能，增强搜索的灵活性和准确性。
全文索引的缺点包括：

  1. 创建全文索引需要占用大量的磁盘空间和内存空间，可能会对系统的性能产生影响。
  2. 全文索引的更新和维护成本较高，需要花费较多的时间和资源。
全文索引的使用场景包括：

  1. 需要对大量文本数据进行搜索和查询的情况，如新闻、博客、社交媒体等应用。
  2. 需要支持模糊搜索和短语搜索等高级搜索功能的情况。
全文索引是一种基于文本内容的索引技术，可以高效地对文本数据进行搜索和查询，支持模糊搜索和短语搜索等高级搜索功能。在使用全文索引时需要考虑到其占用的磁盘空间和内存空间，以及更新和维护的成本。

关系

按照关系分类，可分为主键索引和辅助索引。

主键索引

主键索引是一种基于数据库表中主键的索引技术，主要用于加速对数据库表中某个记录的查找和访问。主键是一种唯一标识数据库表中每个记录的字段或一组字段，每个记录都应该具有不同的主键值。

主键索引通常使用B+树等数据结构进行实现，以便能够快速地查找和定位某个记录。主键索引的特点包括：

  1. 主键索引是一种唯一索引，要求每个记录的主键值都是唯一的。
  2. 主键索引通常是表中的主键字段上创建的索引，可以加快对该字段的查找和访问速度。
  3. 主键索引可以用于加速数据库表的连接操作和数据的排序操作。
主键索引的优点包括：

  1. 提高了对数据库表中某个记录的查找和访问速度，降低了访问数据库表的成本。
  2. 由于主键值是唯一的，可以保证每个记录都能被唯一地标识和访问。
  3. 可以用于加速数据库表的连接操作和数据的排序操作，提高了查询和分析数据的效率。
主键索引的缺点包括：

  1. 主键索引只适用于基于主键的查询，对于其他类型的查询可能会不够高效。
  2. 在插入新记录或更新主键值时，需要重新维护主键索引，可能会对数据库性能产生影响。
  3. 如果主键值不够唯一或者数据量较大，主键索引可能会占用较多的磁盘空间和内存空间。
主键索引是一种基于数据库表中主键的索引技术，可以提高对数据库表中某个记录的查找和访问速度，用于加速数据库表的连接操作和数据的排序操作。在使用主键索引时需要考虑到其对于其他类型的查询可能不够高效，以及在插入新记录或更新主键值时需要重新维护主键索引的成本。MySQL 主键索引还是聚簇索引。

辅助索引

辅助索引，也称为非聚簇索引，是一种基于数据库表中某个字段或多个字段的索引技术，用于加速对数据库表的查询操作。辅助索引与聚簇索引不同，辅助索引不会改变表中数据的物理存储方式。因此除主键索引外，其余索引均为辅助索引。

MySQL几种spanstyledata-id数据库$MySQL索引存储

有关MySQL 10几种索引类型，你都清楚吗？的更多相关文章

  1. ruby - Infinity 和 NaN 的类型是什么？ - 2

    我可以得到Infinity和NaNn=9.0/0#=>Infinityn.class#=>Floatm=0/0.0#=>NaNm.class#=>Float但是当我想直接访问Infinity或NaN时:Infinity#=>uninitializedconstantInfinity(NameError)NaN#=>uninitializedconstantNaN(NameError)什么是Infinity和NaN？它们是对象、关键字还是其他东西？ 最佳答案 您看到打印为Infinity和NaN的只是Float类的两个特殊实例的字符串

  2. ruby - 检查方法参数的类型 - 2

    我不确定传递给方法的对象的类型是否正确。我可能会将一个字符串传递给一个只能处理整数的函数。某种运行时保证怎么样？我看不到比以下更好的选择:defsomeFixNumMangler(input)raise"wrongtype:integerrequired"unlessinput.class==FixNumother_stuffend有更好的选择吗？ 最佳答案 使用Kernel#Integer在使用之前转换输入的方法。当无法以任何合理的方式将输入转换为整数时，它将引发ArgumentError。defmy_method(number)

  3. ruby - Ruby 有 `Pair` 数据类型吗？ - 2

    有时我需要处理键/值数据。我不喜欢使用数组，因为它们在大小上没有限制(很容易不小心添加超过2个项目，而且您最终需要稍后验证大小)。此外，0和1的索引变成了魔数(MagicNumber)，并且在传达含义方面做得很差(“当我说0时，我的意思是head...”)。散列也不合适，因为可能会不小心添加额外的条目。我写了下面的类来解决这个问题:classPairattr_accessor:head,:taildefinitialize(h,t)@head,@tail=h,tendend它工作得很好并且解决了问题，但我很想知道:Ruby标准库是否已经带有这样一个类？ 最佳

  4. ruby - 查找字符串中的内容类型(数字、日期、时间、字符串等) - 2

    我正在尝试解析一个CSV文件并使用SQL命令自动为其创建一个表。CSV中的第一行给出了列标题。但我需要推断每个列的类型。Ruby中是否有任何函数可以找到每个字段中内容的类型。例如，CSV行:"12012","Test","1233.22","12:21:22","10/10/2009"应该产生像这样的类型['integer','string','float','time','date']谢谢! 最佳答案 require'time'defto_something(str)if(num=Integer(str)rescueFloat(s

  5. ruby-on-rails - 在 Rails 开发环境中为 .ogv 文件设置 Mime 类型 - 2

    我正在玩HTML5视频并且在ERB中有以下片段:mp4视频从在我的开发环境中运行的服务器很好地流式传输到chrome。然而firefox显示带有海报图像的视频播放器，但带有一个大X。问题似乎是mongrel不确定ogv扩展的mime类型，并且只返回text/plain，如curl所示:$curl-Ihttp://0.0.0.0:3000/pr6.ogvHTTP/1.1200OKConnection:closeDate:Mon,19Apr201012:33:50GMTLast-Modified:Sun,18Apr201012:46:07GMTContent-Type:text/plain

  6. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  7. ruby-on-rails - 协会的 Rails 索引 - 2

    我发现自己需要这个。假设cart是一个包含用户列表的模型。defindex_of_itemcart.users.each_with_indexdo|u,i|ifu==current_userreturniendend获取此类关联索引的更简单方法是什么？ 最佳答案 indexArray上的方法与您的index_of_item方法相同，例如cart.users.index(current_user)返回数组中第一个对象的索引==给obj。如果未找到匹配项，则返回nil。 关于ruby-on-

  8. ruby - Rails -- :id attribute? 所需的数据库索引 - 2

    因此，当我遵循MichaelHartl的RubyonRails教程时，我注意到在用户表中，我们为:email属性添加了一个唯一索引，以提高find的效率方法，因此它不会逐行搜索。到目前为止，我们一直在根据情况使用find_by_email和find_by_id进行搜索。然而，我们从未为:id属性设置索引。:id是否自动索引，因为它在默认情况下是唯一的并且本质上是顺序的？或者情况并非如此，我应该为:id搜索添加索引吗？ 最佳答案 大多数数据库(包括sqlite，这是RoR中的默认数据库)会自动索引主键，对于RailsMigration

  9. ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2

    我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby​​目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin

  10. 由于 libgmp.10.dylib 的问题，Ruby 2.2.0 无法运行 - 2

    我刚刚安装了带有RVM的Ruby2.2.0，并尝试使用它得到了这个:$rvmuse2.2.0--defaultUsing/Users/brandon/.rvm/gems/ruby-2.2.0dyld:Librarynotloaded:/usr/local/lib/libgmp.10.dylibReferencedfrom:/Users/brandon/.rvm/rubies/ruby-2.2.0/bin/rubyReason:Incompatiblelibraryversion:rubyrequiresversion13.0.0orlater,butlibgmp.10.dylibpro

随机推荐