首页 > 数据库 >2. MySQL 索引分类

2. MySQL 索引分类

时间:2024-11-08 23:15:52浏览次数:7  
标签:Index 存储 聚集 分类 索引 MySQL 数据 主键

MySQL中的索引是提高数据查询速度的重要工具,就像一本书的目录,可以帮助我们快速定位到所需的内容。选择适合的索引类型对数据库设计和性能优化至关重要。本文将详细介绍MySQL中常见的索引类型,并重点讲解聚集索引和二级索引的概念及应用。


1. 主键索引(Primary Key Index)
  • 概念:主键索引是唯一索引的一种,确保表中每条记录的唯一性。每张表只能有一个主键,通常在创建表时指定。

  • 特点:自动唯一,不允许空值(NULL),且主键列会自动添加一个聚集索引(在InnoDB存储引擎中)。

  • 使用场景:用作表的唯一标识,如用户表的用户ID字段,能够快速定位数据。

    示例

    CREATE TABLE users (
        user_id INT PRIMARY KEY,
        name VARCHAR(100),
        email VARCHAR(100)
    );
    

2. 唯一索引(Unique Index)
  • 概念:唯一索引确保索引列的所有值唯一,但允许空值(NULL)。唯一索引主要用于保证数据完整性。

  • 特点:允许列值唯一,适合有唯一性要求的数据。

  • 使用场景:如邮箱、身份证号等信息,适合使用唯一索引来防止重复。

    示例

    sql复制代码CREATE TABLE employees (
        emp_id INT PRIMARY KEY,
        emp_email VARCHAR(100) UNIQUE
    );
    

3. 普通索引(Index)
  • 概念:普通索引没有唯一性限制,可以在表的多个字段上创建,主要作用是加快查询。

  • 特点:允许重复和空值,适合搜索频繁的数据列。

  • 使用场景:比如文章的标题、内容摘要等字段的搜索。

    示例

    CREATE INDEX idx_title ON articles(title);
    

4. 全文索引(Full-Text Index)
  • 概念:用于较长文本字段的全文搜索。MySQL会通过分词来快速匹配关键词。

  • 特点:适合模糊查询。

  • 使用场景:博客文章、商品描述等长文本的关键词匹配。

    示例

    CREATE TABLE posts (
        id INT PRIMARY KEY,
        content TEXT,
        FULLTEXT(content)
    );
    

5. 组合索引(Composite Index)
  • 概念:包含多个列的索引,用于多列查询。MySQL会将这些列组合形成一个单一索引。

  • 特点:需遵循“最左前缀”原则。

  • 使用场景:适合多列组合查询。

    示例

    CREATE INDEX idx_country_city ON users(country, city);
    

6. 聚集索引(Clustered Index)

InnoDB 存储引擎的特有

  • 概念:聚集索引是将数据存储与索引结构结合的特殊索引类型。在InnoDB中,每张表都必须有一个聚集索引,它会将数据行存储在B+树结构中,树的叶节点包含了实际的数据行。

  • 特点:数据按照主键顺序存储在磁盘上,因此主键列就是聚集索引。每个表只能有一个聚集索引,因为数据的物理存储方式只能按一种顺序排列。

  • 使用场景:聚集索引适合需要按顺序读取的数据,比如按日期排序的日志记录。它提高了范围查询的效率,但在需要频繁更新主键列的表中应谨慎使用,因为频繁重排会影响性能。

    示例

    CREATE TABLE orders (
        order_id INT PRIMARY KEY,
        order_date DATE,
        customer_id INT
    );
    

    在这里,order_id作为主键将创建一个聚集索引,数据按order_id的顺序存储。


7. 二级索引(Secondary Index)

InnoDB 存储引擎的特有

  • 概念:二级索引(也称非聚集索引)是除了聚集索引之外的其他索引。它只存储索引列和指向数据行的引用(如主键值),而不直接存储数据本身。

  • 特点:二级索引的叶节点存储了键值和指向聚集索引的指针,因此当通过二级索引查找数据时,需要通过该指针找到数据行。这种方式称为“回表”操作,会稍微影响查询速度。

  • 使用场景:适合不需要顺序访问的数据列,如姓名、邮箱等字段,通过二级索引可以有效地加快查询速度。

    示例

    CREATE INDEX idx_name ON users(name);
    

    在这里,idx_name是一个二级索引,通过用户的姓名来加速查询,但它需要回表找到实际数据行。


8. 索引总结图:

索引的具体类型主要分为以下几类:主键索引、唯一索引、常规索引、全文索引。

分类含义特点关键字
主键索引针对于表中主键创建的索引默认自动创建,只能有一个PRIMARY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键词,而不是比较索引中的值可以有多个FULLTEXT

InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:

分类含义特点
聚集索引 (Clustered Index)将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据必须有,而且只有一个
二级索引 (Secondary Index)将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键可以存在多个

对应B+Tree图:
在这里插入图片描述

9. 总结

在MySQL中,聚集索引与二级索引的搭配使用能更好地平衡数据访问性能。聚集索引适合数据量大且有顺序需求的场景,而二级索引则适合独立的数据列搜索。根据实际应用场景选择合适的索引,不仅能提升查询效率,也能确保数据库的高效运行。希望这篇文章帮助您更好地理解MySQL中的索引类型及其使用场景!

标签:Index,存储,聚集,分类,索引,MySQL,数据,主键
From: https://blog.csdn.net/m0_72560900/article/details/143636710

相关文章

  • 关于MySQL表设计,测试人员可以关注哪些点
    测试人员关注数据库表设计是“测试左移”的一种手段,可以把有关数据库的潜在bug消灭在系统测试之前,从而提高交付效率。以MySQL为例,QA可从以下方面对数据库表设计做测试的左移:1、数据表功能表结构确保每张表都有主键,且主键值唯一且非空,以保证表中每行数据的唯一性和可识别性检......
  • mysql ubuntu 卸载
    mysql卸载:引用https://developer.aliyun.com/article/1306777在Ubuntu系统中,MySQL是一种常用的关系型数据库服务器。有时,我们可能需要完全卸载MySQL服务器,包括所有配置文件和数据,以便重新安装或切换到其他数据库服务器。本文将详细介绍在Ubuntu中如何完全卸载MySQL服......
  • MySQLMonitor: 黑盒测试Mysql实时监控辅助工具
    MySQLMonitorMySQL实时监控工具(代码审计、黑盒测试辅助工具)使用1.自行打包使用gitclonehttps://github.com/fupinglee/MySQLMonitorcdMySQLMonitormvncleanpackage-DskipTests=true打开target下的jar文件即可执行2.直接下载使用https://github.com/fupinglee/......
  • 初始mysql以及创建
    mysql是一个客户端服务器结构的程序mysql的服务器是真正的本体,负责保存和管理数据,数据都是保存在硬盘上数据库服务器上可以把很多有业务上联系的表放在一起,构成一个逻辑上的数据集合 登陆mysql打开终端输入mysql-uroot-p,然后显示这个就说明mysql客户端连接到了服务器 ......
  • dbeaver连接MySQL出现了 Public Key Retrieval is not allowed 错误
    问题现象出现这个错误的原因错误提示“PublicKeyRetrievalisnotallowed”通常出现在尝试使用SSL(SecureSocketsLayer)连接到MySQL数据库时。这可能是由于以下原因之一导致的:MySQL服务器配置:MySQL服务器可能没有正确配置以允许公钥检索。检查MySQL服务器的my.cnf或my.......
  • 智谱BigModel研习社|搭建 AI 搜索引擎 - 使用免费的Web-Search-Pro+脑图Agent智能体
    作者:Cartman文章:多智能体AI搜索引擎点击链接,更多实践案例等你探索~ #智谱BigModel研习社是专业的大模型开发者交流平台,欢迎在评论区与我们互动! 传统搜索引擎如今的问题在于输出很多不相关结果(大量垃圾信息+SEO操纵的标题党内容),大模型也面临着幻觉问题。在网......
  • ubuntu 安装mysql+redis+nginx
    1.mysql安装vim/etc/apt/sources.list 追加一下内容debhttps://mirrors.aliyun.com/ubuntu/focalmainrestricteduniversemultiversedeb-srchttps://mirrors.aliyun.com/ubuntu/focalmainrestricteduniversemultiversedebhttps://mirrors.aliyun.com/ubuntu/......
  • 【QT】【MYSQL】【WINDOWS】qmysql驱动编译
    文章目录前言一、安装QT,MYSQL二、生成qsqlmysql.dll1.配置mysql.pro2.修改qsqldriverbase.pri3.管理员身份运行x64NativeToolsCommandPromptforVS2022重点!!!!成功执行代码前言提示:参考网上各种安装教程均失败,下面来一个完整qmysql驱动编译,并qt连接数据库操作......
  • 【MySQL】数据库备份详解
    一、引言1.1数据库备份的重要性二、MySQL数据库备份的基础知识2.1备份类型2.2备份工具与方法三、MySQL数据库备份的实施步骤3.1环境准备3.2选择合适的备份工具与方法3.3执行备份3.4验证备份3.5存储与管理备份四、MySQL数据库恢复流......
  • MySQL RC隔离级别下罕见的gap lock
    复现方法:CREATETABLE`test1`(`id`int(11)NOTNULL,`name`varchar(10)DEFAULTNULL,`id1`int(11)DEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`id1`(`id1`);insertintotest.test1values(1,'a',1),(3,'a',3),(5,'a'......