揭秘MySQL索引世界：概念、分类、应用场景一网打尽

一、索引概念

MySQL索引是一种用于提高数据库查询性能的数据结构。它允许数据库系统更有效地检索数据行，减少了在大型数据集中搜索特定数据的时间。索引的作用类似于书籍的目录，通过提供关键字与实际数据位置之间的映射，加速对数据库表中数据的访问。

二、索引类型

MySQL支持多种类型的索引，它们可以根据不同的分类标准进行划分。以下是MySQL中常见的索引类型，按照它们的分类方式进行介绍：

1. 根据数据结构分类：

  • B+Tree 索引： B+Tree（B树的一种变种）索引是一种常见的索引结构，被广泛用于数据库管理系统中。B+Tree索引在实际应用中被MySQL等数据库系统采用，主要用于提高对数据库表中数据的检索效率。
  • Hash索引： 使用哈希算法构建的索引，适用于等值查询，但不适用于范围查询和排序。
  • Full-text索引： 用于全文搜索的索引，支持对文本数据进行关键字搜索。

2. 根据性质&用途分类：

主键索引、唯一索引和普通索引是数据库中常见的索引类型，它们有不同的特点和用途：

  1. 主键索引（Primary Key Index）:
    • 性质： 主键索引是一种特殊的唯一索引，用于唯一标识表中的每一行。
    • 唯一性： 主键索引列的值必须是唯一的，不允许有重复的主键值。
    • 主要用途： 主键索引通常用于作为表的主键，用于唯一标识表中的每一行记录。
CREATE TABLE example (
   id INT PRIMARY KEY,
   name VARCHAR(50)
);
  1. 唯一索引（Unique Index）:
    • 性质： 唯一索引要求索引列中的所有值都是唯一的，但允许有一个 NULL 值。
    • 唯一性： 不同行的索引值必须唯一，允许一个 NULL 值。
    • 主要用途： 唯一索引用于确保表中的某列或列组中的数据不包含重复项。
CREATE TABLE example (
   id INT,
   email VARCHAR(50) UNIQUE,
   PRIMARY KEY (id)
);
  1. 普通索引（Non-Clustered Index）:
    • 性质： 普通索引是最基本的索引类型，没有唯一性约束。
    • 唯一性： 允许有重复的索引值。
    • 主要用途： 普通索引用于加速对表中数据的检索，可以用于等值查询、范围查询等操作。
CREATE TABLE example (
   id INT,
   name VARCHAR(50),
   INDEX name_index (name)
);

3. 根据范围分类：

单列索引和联合索引是两种常见的索引类型，它们在数据库中的使用方式和性能影响有所不同。

1. 单列索引（Single-Column Index）:

  • 性质： 单列索引是针对表中的单个列创建的索引。
  • 唯一性： 单列索引可以是唯一索引，也可以是普通索引。唯一索引要求索引列中的值是唯一的，而普通索引允许重复的索引值。
  • 用途： 单列索引通常用于加速对单个列的等值查询、范围查询等操作。
CREATE TABLE example (
   id INT PRIMARY KEY,
   name VARCHAR(50),
   INDEX name_index (name)
);

2. 联合索引（Composite Index）:

  • 性质： 联合索引是针对表中的多个列创建的索引，这些列按照一定的顺序组合成一个索引。
  • 唯一性： 联合索引可以是唯一索引，要求组合索引列中的所有值是唯一的，也可以是普通索引。

  • 用途： 联合索引通常用于加速对多个列组合进行的查询，尤其是在涉及到多列的等值查询或范围查询时。
CREATE TABLE example (
   id INT,
   category VARCHAR(50),
   price DECIMAL(10,2),
   INDEX category_price_index (category, price)
);

4. 根据存储位置分类：

聚集索引和非聚集索引是 MySQL 中两种常用的索引类型。

1.聚集索引(聚簇索引)

聚集索引是表中唯一的索引，它确定了表中数据的物理顺序。聚集索引的叶子节点存储的是表中的数据行，因此，根据聚集索引进行查询时，可以直接定位到符合查询条件的数据行。

聚集索引主要用于以下场景：

  • 主键：主键索引是聚集索引的一种特殊形式。主键索引的叶子节点存储的是表中主键列的值，因此，根据主键索引进行查询时，可以直接定位到符合查询条件的数据行。
  • 范围查询：范围查询是指查询满足某个条件范围的数据。对于范围查询，聚集索引可以有效提高查询效率。

2.非聚集索引(非聚簇索引)

非聚集索引的叶子节点存储的是指向数据行的指针，因此，根据非聚集索引进行查询时，需要先定位到叶子节点，然后再根据叶子节点中的指针定位到数据行。

非聚集索引主要用于以下场景：

  • 非主键：除了主键列之外的其他列都可以创建非聚集索引。
  • 等值查询：等值查询是指查询满足某个条件的数据。对于等值查询，非聚集索引可以提高查询效率。
  • 连接操作：连接操作是指在两个或多个表中查询数据。对于连接操作，连接条件列上创建非聚集索引可以提高连接操作的效率。

InnoDB 和 MyISAM 是 MySQL 中两种常用的存储引擎。InnoDB 和 MyISAM 对聚集索引和非聚集索引的支持存在以下差异：

InnoDB

  • InnoDB 支持聚集索引，并且每个表只能有一个聚集索引。聚集索引的叶子节点存储的是表中的数据行，因此，根据聚集索引进行查询时，可以直接定位到符合查询条件的数据行。
  • InnoDB 也支持非聚集索引，非聚集索引的叶子节点存储的是指向数据行的指针。

MyISAM

  • MyISAM 不支持聚集索引，因此，表中的数据是随机存储的。
  • MyISAM 支持非聚集索引，非聚集索引的叶子节点存储的是指向数据行的指针。

因此，聚集索引和非聚集索引与 InnoDB 和 MyISAM 的对应关系如下表所示：

索引类型

InnoDB

MyISAM

聚集索引

支持

不支持

非聚集索引

支持

支持

三、使用场景

MySQL 索引是数据库中提高查询速度的一种常用方法。索引是数据库中的一个特殊数据结构，它将数据库表中的一列或多列的值进行排序，并将排序后的结果存储在一个索引文件中。当用户执行查询时，MySQL 会根据查询条件查找索引文件，并根据索引文件中的结果快速定位到相应的记录。

MySQL 索引的使用场景主要包括以下几个方面：

  • 大型表查询：对于大型表，如果不使用索引，MySQL 需要从表头开始逐行扫描，直到找到符合查询条件的记录。这对于大型表来说会非常耗时。使用索引后，MySQL 可以直接根据索引文件定位到符合查询条件的记录，从而提高查询速度。
  • 连接操作：连接操作是指在两个或多个表中查询数据。连接操作的效率很大程度上取决于连接条件。如果连接条件的列上有索引，MySQL 可以直接根据索引文件定位到两个表中符合连接条件的记录，从而提高连接操作的效率。
  • 数据排序：如果需要对数据进行排序，MySQL 需要对整个表的数据进行排序。这对于大型表来说会非常耗时。使用索引后，MySQL 可以直接根据索引文件对数据进行排序，从而提高排序效率。
  • 多列查询：如果查询条件涉及多个列，MySQL 需要逐个检查每个列的值是否符合查询条件。这对于多列查询来说会非常耗时。使用索引后，MySQL 可以根据索引文件快速定位到符合查询条件的记录，从而提高查询效率。

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

  • 索引会占用一定的存储空间。因此，在创建索引时，需要考虑索引对存储空间的影响。
  • 索引会影响数据的更新速度。因为索引文件需要定期更新，因此在更新数据时，会影响索引的更新。
  • 索引的使用需要根据实际的查询需求来进行。如果查询条件很少使用，那么创建索引可能会降低查询效率。

四、相关题目

  • 1、什么是索引？索引有什么作用？
    • 索引是一种数据结构，它可以帮助数据库快速地查询、排序、分组、联合等操作。索引的作用是提高数据库的性能，减少磁盘的I/O操作，节省查询时间。
  • 2、索引有哪些类型？
    • 索引的类型有很多，常见的有以下几种：
      • 主键索引：主键索引是一种唯一的索引，它保证了表中每一行数据的唯一性。主键索引是表的标识，一张表只能有一个主键索引。
      • 唯一索引：唯一索引是一种不允许重复值的索引，它保证了索引列的唯一性。唯一索引可以有多个，但是每个唯一索引只能有一个列或者多个列的组合。
      • 普通索引：普通索引是一种最基本的索引，它没有任何限制，可以在任意列上创建。普通索引可以提高查询速度，但是会降低插入、更新、删除的速度。
      • 复合索引(联合)：复合索引是一种由多个列组成的索引，它可以根据多个条件进行查询。复合索引的顺序很重要，它会影响索引的效率。
      • 全文索引：全文索引是一种针对文本类型的列的索引，它可以对文本内容进行分词、匹配、排序等操作。全文索引适用于搜索引擎等场景，但是它的维护成本较高。
  • 3、索引有哪些数据结构？
    • 索引的数据结构有很多，常见的有以下几种：
      • B+树索引：B+树索引是一种多路平衡搜索树，它的每个节点可以有多个子节点，它的所有叶子节点都在同一层，并且相互连接。B+树索引的优点是查询速度快，范围查询方便，空间利用率高。B+树索引的缺点是插入、删除操作会引起树的调整，维护成本较高。B+树索引是MySQL的默认索引类型，适用于大部分场景。
      • 哈希索引：哈希索引是一种基于哈希表的索引，它的每个节点都有一个哈希值，它的查询速度只与哈希值的计算有关。哈希索引的优点是查询速度极快，适合等值查询。哈希索引的缺点是不支持范围查询，排序，分组等操作，容易发生哈希冲突，空间利用率低。哈希索引适用于内存数据库，如MySQL的Memory引擎。
      • Full-text 索引：也称为全文索引，是 MySQL 中一种特殊的索引，用于对文本数据进行检索。Full-text 索引可以根据文本数据的相似度来进行查询，而不是根据精确的匹配。
  • 4、什么是聚簇索引和非聚簇索引？有什么区别？

聚簇索引和非聚簇索引是 MySQL 中两种常用的索引类型。聚集索引和非聚簇索引的区别主要体现在以下几个方面：

  • 索引的结构：聚集索引的叶子节点存储的是表中的数据行，而非聚集索引的叶子节点存储的是指向数据行的指针。
  • 索引的功能：聚集索引确定了表中数据的物理顺序，而非聚集索引可以用于提高查询效率。

索引的数量：一个表只能有一个聚集索引，而可以有多个非聚集索引。

  • 5、什么是回表查询？

当一个查询使用了索引，并且在查询结果中需要获取表中其他列的值时，就需要进行回表查询。回表查询是指数据库根据索引中的指针找到实际的数据行，并从表中检索其他列的值。

在 MyISAM 中，由于索引和数据是分开存储的，如果查询需要获取的列不在索引中，就需要额外的步骤去表中检索这些列的值。这可能导致性能损失，特别是当查询涉及大量的回表操作时。

  • 6、MyISAM为什么不支持聚集索引

MyISAM 不支持聚集索引的设计主要是基于其性能优化和设计原则。以下是一些解释为什么 MyISAM 不支持聚集索引的主要原因：

    • 表锁定（Table-level Locking）： MyISAM 使用表级锁定，而不是行级锁定。表级锁定对于读密集型操作来说更加简单和高效。在表级锁定的情况下，聚集索引可能会导致更加复杂的锁定策略，而非聚集索引更直接，使得表级锁定更容易实现。
    • 简单的存储结构： MyISAM 存储引擎的数据和索引是分开存储的，这样可以简化存储结构，减少存储和维护的开销。非聚集索引的设计更符合 MyISAM 的设计理念。
    • 不支持事务： MyISAM 不支持事务处理，因此不需要像 InnoDB 那样保证事务特性（比如原子性、一致性、隔离性、持久性）。在不考虑事务特性的情况下，非聚集索引更容易实现并能提供更好的性能。
    • 全文搜索： MyISAM 在非聚集索引上对全文搜索提供了良好的支持。全文搜索通常需要使用非聚集索引。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/216098.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

idea利用SpringMVC框架整合ThymeLeaf

idea利用SpringMVC框架整合ThymeLeaf

简洁一些：两个重要文件 1.controller指定html文件:我们访问http://localhost:8080/test package com.example.appledemo.controller;import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import o…
阅读更多...
ubuntu20.04使用LIO-SAM对热室空间进行重建

ubuntu20.04使用LIO-SAM对热室空间进行重建

一、安装LIO-SAM 1.环境配置 默认已经安装过ros sudo apt-get install -y ros-Noetic-navigation sudo apt-get install -y ros-Noetic-robot-localization sudo apt-get install -y ros-Noetic-robot-state-publisher 安装 gtsam(如果是18.04的ubuntu直接按照官网配置&…
阅读更多...
RabbitMQ 的七种消息传递形式

RabbitMQ 的七种消息传递形式

文章目录 一、RabbitMQ 架构简介二、准备工作 三、消息收发1. Hello World2. Work queues3. Publish/Subscrite3.1. Direct3.2. Fanout3.3. Topic3.4. Header 4. Routing5. Topics 大部分情况下，我们可能都是在 Spring Boot 或者 Spring Cloud 环境下使用 RabbitMQ&…
阅读更多...
HTTPS 之fiddler抓包--jmeter请求

HTTPS 之fiddler抓包--jmeter请求

一、浅谈HTTPS 我们都知道HTTP并非是安全传输，在HTTPS基础上使用SSL协议进行加密构成的HTTPS协议是相对安全的。目前越来越多的企业选择使用HTTPS协议与用户进行通信，如百度、谷歌等。HTTPS在传输数据之前需要客户端（浏览器）与服务…
阅读更多...
2023年，社媒营销的「心智王者」到底是谁？

2023年，社媒营销的「心智王者」到底是谁？

“在未来社会，每个人都可能在15分钟内出名，并有机会出名15分钟。” ——安迪沃霍尔 2023年品牌营销，社交媒体是绝对主战场： 明星加持，玩转粉丝经济； “满天星”式种草，打造爆品； …
阅读更多...
Burp suite抓虚拟机的包

Burp suite抓虚拟机的包

参考：物理机burp抓虚拟机包) 打开物理机的Burp，Proxy->Proxy settings->Add->Specific address，挑个自己喜欢的（除了 127.0.0.1 和 IPV6 地址）。 端口号自己填一个。 打开虚拟机浏览器，Internet选…
阅读更多...
代码随想录算法训练营第五十五天【动态规划part15】 | 392.判断子序列、115.不同的子序列

代码随想录算法训练营第五十五天【动态规划part15】 | 392.判断子序列、115.不同的子序列

392.判断子序列 题目链接 力扣（LeetCode）官网 - 全球极客挚爱的技术成长平台 求解思路 也可以用双指针来做。 动规五部曲 1.确定dp数组及其下标含义 以下标i-1为结尾的字符串s，和以下标j-1为结尾的字符串t，相同子序列的长度…
阅读更多...
通信原理学习笔记--2.信号基础知识

通信原理学习笔记--2.信号基础知识

阅读更多...
EtherCAT超高速实时运动控制卡XPCIE1032H上位机C#开发（四）：板载IO与总线扩展IO的编码器与脉冲配置的应用

EtherCAT超高速实时运动控制卡XPCIE1032H上位机C#开发（四）：板载IO与总线扩展IO的编码器与脉冲配置的应用

XPCIE1032H功能简介 XPCIE1032H是一款基于PCI Express的EtherCAT总线运动控制卡，可选6-64轴运动控制，支持多路高速数字输入输出，可轻松实现多轴同步控制和高速数据传输。 XPCIE1032H集成了强大的运动控制功能，结合MotionRT7运动…
阅读更多...
自动化框架错误排查：本地全通过，pipline上大部分报错

自动化框架错误排查：本地全通过，pipline上大部分报错

现象： 最近经过一次切环境和验证码部分的代码重构，果不其然，我们的自动化框架就出错了 我在本地修改调试，并在堡垒机上全部跑过 但在pipline上则大部分报错 进一步排查 这么多case报错，而且报错log都一模一样，推断是底层出错 我在堡垒机上使用命令行来跑case，发现与…
阅读更多...
vue 修改 this.$confirm 的文字样式、自定义样式

vue 修改 this.$confirm 的文字样式、自定义样式

通常使用 confirm 确认框时&#xff0c;一般这样写&#xff1a; <template><el-button type"text" click"open">点击打开 Message Box</el-button> </template><script>export default {methods: {open() {this.$confirm(此…
阅读更多...
14、pytest像用参数一样使用fixture

14、pytest像用参数一样使用fixture

官方实例 # content of test_fruit.py import pytestclass Fruit:def __init__(self, name):self.name nameself.cubed Falsedef cube(self):self.cubed Trueclass FruitSalad:def __init__(self, *fruit_bowl):self.fruit fruit_bowlself._cube_fruit()def _cube_fruit(s…
阅读更多...
揭秘预付费电表怎么无线收费——方便快捷收费

揭秘预付费电表怎么无线收费——方便快捷收费

【摘要】针对目前市场上普遍以Ic卡作为售电介质的预付费售电系统存在的问题&#xff0c;介绍了一种新型的无线预付费售电系统及其构成&#xff0c;并给出了整个系统设计的完整方案。整个系统包括用户终端和电力管理系统端&#xff0c;它们之间通过双工通信可以将用户用电信息和…
阅读更多...
科普类软文怎么写才能提高用户接受度？媒介盒子分享

科普类软文怎么写才能提高用户接受度？媒介盒子分享

科普类软文以干货为主&#xff0c;可以给用户带来实用价值&#xff0c;但是相应会比较枯燥。如何才能把科普内容讲得专业又有趣&#xff0c;从而提高用户接受度呢&#xff1f;媒介盒子接下来就分享三大技巧&#xff1a; 一、 联系产品选题 科普类软文想要写好就需要做好选题&…
阅读更多...
如何使用Node.js快速创建本地HTTP服务器并实现异地远程访问

如何使用Node.js快速创建本地HTTP服务器并实现异地远程访问

文章目录 前言1.安装Node.js环境2.创建node.js服务3. 访问node.js 服务4.内网穿透4.1 安装配置cpolar内网穿透4.2 创建隧道映射本地端口 5.固定公网地址 前言 Node.js 是能够在服务器端运行 JavaScript 的开放源代码、跨平台运行环境。Node.js 由 OpenJS Foundation&#xff0…
阅读更多...
吉祥物虚拟人IP：如何持续为品牌年轻化营销赋能

吉祥物虚拟人IP：如何持续为品牌年轻化营销赋能

如今&#xff0c;「得年轻人者&#xff0c;得天下」逐渐成为各品牌营销的一个共识&#xff0c;年轻化营销则成为品牌吸引和留存用户的手段。在数字化时代&#xff0c;品牌也不再满足于通过产品带动用户共情&#xff0c;而是突破传统的、单向的吉祥物打造模式&#xff0c;推陈出…
阅读更多...
微信小程序交互（弹框）

微信小程序交互（弹框）

1.wx.showToast(Object object) 显示消息提示框 属性类型默认值必填说明最低版本titlestring是提示的内容iconstringsuccess否图标 合法值说明最低版本success显示成功图标&#xff0c;此时 title 文本最多显示 7 个汉字长度error显示失败图标&#xff0c;此时 title 文本最多…
阅读更多...
开关电源调试时，常见的10个问题：

开关电源调试时，常见的10个问题：

1、变压器饱和 变压器饱和现象 在高压或低压输入下开机(包含轻载&#xff0c;重载&#xff0c;容性负载)&#xff0c;输出短路&#xff0c;动态负载&#xff0c;高温等情况下&#xff0c;通过变压器(和开关管)的电流呈非线性增长&#xff0c;当出现此现象时&#xff0c;电流的…
阅读更多...
关于工业级交换机的分类，你知道多少？

关于工业级交换机的分类，你知道多少？

工业级交换机是指专为工业控制领域设计的以太网交换设备。工业级交换机具备电信级的性能特征&#xff0c;能够在恶劣的工作环境下持久耐用。我们的产品系列非常广泛&#xff0c;可以灵活配置各种不同类型的端口&#xff0c;以满足工业领域的多样化使用需求。该产品具有宽温设计…
阅读更多...
mysql数据导入时数据太大（2006 - MySQL server has gone away）——笔记

mysql数据导入时数据太大（2006 - MySQL server has gone away）——笔记

打开mysql的安装路径&#xff0c;找到my.ini文件 my.ini打开后修改max_allowed_packet的值变成32或者更大 max_allowed_packet32M修改之后重启mysql
阅读更多...
最新文章

Copyright @ 2022~2023