MySQL 索引分类

这篇具有很好参考价值的文章主要介绍了MySQL 索引分类。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

MySQL 索引分类

索引的类型和存储引擎有关,每种存储引擎所支持的索引类型不一定完全相同。MySQL 中的索引,可以从存储方式使用逻辑实际使用等不同角度来进行分类

1、按存储方式区分

  • 索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。
  • 这样的话,索引是使用过程中,就要产生磁盘对索引文件的 I/O 消耗,相对于内存存取的消耗,I/O 存取的消耗要更高。所以评价索引的优劣最重要的指标,就是在查找过程中磁盘 I/O 操作次数的复杂度,而索引的本质都是基于某种数据结构来设计的,所以,索引的数据结构要尽量减少查找过程中磁盘 I/O 的存取次数
  • 根据数据结构存储方式的不同,MySQL 中常用的索引,在物理上分为 B+Tree 索引HASH 索引两类,两种不同类型的索引各有其不同的适用范围
(1)B+Tree 索引
1、BTree

B+Tree 是BTree 的一种特殊变种

  • BTree 是一个 多路平衡查找树(Balance Tree),多路也就是多叉的意思
  • 所有叶子节点在同一高度,保证数据有序

MySQL 索引分类,mysql,分类,数据库

假设要从图中查找id = X的数据,BTREE 搜索过程如下:

  1. 取出根磁盘块,加载4060两个关键字。
  2. 如果X = 40,则命中;如果X < 40P1;如果40 < X < 60P2;如果X = 60,则命中;如果X > 60P3
  3. 根据以上规则命中后,接下来加载对应的数据, 数据区中存储的是具体的数据或者是指向数据的指针。
2、B+Tree

B+Tree 在原有 BTree 的基础上补充了如下特性:

  • B+Tree 根节点和支节点没有数据区,数据data全部存储在叶子节点中;
  • 每一个父节点的值,都包含在叶子节点中,是叶子节点中==最大(或最小)==的元素;
  • 每一个叶子节点,都持有一个指向下一个叶子节点的指针,形成了有序链表

MySQL 索引分类,mysql,分类,数据库

假设为字段ID添加索引,搜索X = 1的数据,**B+TREE** 搜索过程如下:

  1. 取出根磁盘块,加载12866三个关键字。
  2. X <= 1P1,取出磁盘块,加载11020三个关键字。
  3. X <= 1P1,取出磁盘块,加载1 8 9三个关键字。
  4. 已经到达叶子节点,命中1,加载对应数据节点
3、BTree 个 B+Tree 的主要区别
  1. BTree的数据(或指向数据的指针)存在每个节点里,而 B+Tree的数据(或指向数据的指针)仅存在叶子节点里,非叶子节点只有索引。
  2. BTree 的查找,可能会在任意一个节点停止,而 B+Tree的查找相对稳定。
  3. B+Tree 的非叶子节点可以存储更多的索引值,阶数更高
  4. B+Tree 的叶子节点使用双向链表链接,提高顺序查询效率
  5. 相比于 BTreeB+Tree 在区间查找方面更胜一筹
4、MySQL为什么选择B+Tree
  1. B+Tree**全表扫描能力更强。**如果我们要根据索引去进行数据表的扫描,如果基于BTREE进行扫描,需要把整棵树遍历一遍,而B+TREE只需要遍历所有叶子节点即可(叶子节点之间形成有序列表)。
  2. B+Tree排序能力更强
  3. B+TREE**磁盘读写能力更强。**他的根节点和枝节点不保存数据区,所以根节点和枝节点同样大小的情况下,保存的关键字要比BTREE要多。所以,B+TREE读写一次磁盘加载的关键字比BTREE更多。
  4. B+Tree查询性能稳定。B+Tree数据只保存在叶子节点,每次查询数据,查询IO次数一定是稳定的
(2)哈希索引
  • 哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY存储引擎和 HEAP存储引擎支持这类索引。
  • 哈希索引,是基于哈希表实现的一种索引结构。将字段的内容(key)通过哈希算法,计算该字段的哈希值,用于访问哈希表结构中的对应索引,该索引指向数据行

MySQL 索引分类,mysql,分类,数据库

1、哈希索引的特点
  • 无法用于排序与分组;
  • 只支持精确查找,无法用于部分查找和范围查找。
  • InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”,当某个索引值被使用的非常频繁时,会在 B+Tree 索引之上再创建一个哈希索引,这样就让 B+Tree 索引具有哈希索引的优点,比如:快速的精准查找

2、使用逻辑区分

根据索引的具体用途,MySQL 中的索引在使用逻辑上分为以下 4 类:

(1)普通索引
  • 普通索引也被称为辅助索引。是 MySQL 中最基本的索引类型,它没有任何限制,唯一任务就是加快系统对数据的访问速度。
  • 普通索引允许在定义索引的列中,插入重复值和空值
  • 创建普通索引时,通常使用的关键字是 Index

示例:

tb_student 表中的 id 字段上建立名为 index_id 的索引

CREATE INDEX index_id ON tb_student(id);
(2)唯一索引
  • 唯一索引与普通索引类似,不同的是,创建唯一性索引的目的:除了提高访问速度以外,同时还能避免数据出现重复。
  • 唯一索引列的值必须唯一,允许有空值。如果是组合索引,则列值的组合必须唯一
  • 创建唯一索引通常使用 UNIQUE关键字

示例:

tb_student 表中的 id 字段上建立名为 index_id 的索引

CREATE UNIQUE INDEX index_id ON tb_student(id);
(3)主键索引
  • 主键索引就是专门为主键字段创建的索引,也属于索引的一种。主键索引是一种特殊的唯一索引,不允许值重复或者值为空。
  • 创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE ``INDEX 语句创建主键索引,需要以创建或修改表结构的方式进行添加

示例:

tb_student 表中的 id 字段上添加主键索引

ALTER TABLE tb_student ADD PRIMARY KEY (id)
(4)全文索引
  • 全文索引主要用来查找文本中的关键字,只能在 CHARVARCHARTEXT 类型的列上创建。全文索引允许在索引列中插入重复值和空值。
  • 不过对于大容量的数据表,生成全文索引非常消耗时间和硬盘空间。
  • 创建全文索引使用 FULLTEXT关键字

示例:

tb_student 表中的 info 字段上建立名为 index_info 的全文索引

CREATE FULLTEXT INDEX index_info ON tb_student(info);

3、按实际的使用情况区分

(1)单列索引
  • 单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引,单列索引只根据该字段进行索引。
  • 单列索引可以是普通索引,也可以是唯一性索引,还可以是全文索引。只要保证该索引只对应一个字段即可。
(2)组合索引
  • 组合索引也称为复合索引或多列索引。相对于单列索引来说,组合索引是将原表的多个列共同组成一个索引。
  • 多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段,可以通过这几个字段进行查询。但是,只有查询条件中使用了这些字段中第一个字段时,索引才会被使用

示例:

tb_student 表中的 nameaddress 字段上建立名为 index_na 的索引。该索引创建好了以后,查询条件中必须有 name 字段才能使用索引文章来源地址https://www.toymoban.com/news/detail-731222.html

CREATE INDEX index_na ON tb_student(name,address);
  • 一个表可以有多个单列索引,但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引,以此来加快查询速度。
  • 比如:在一个表中创建了一个组合索引(c1,c2,c3),在实际查询中,系统用来实际加速的索引有三个:单个索引(c1)、双列索引(c1,c2)和多列索引(c1,c2,c3)

到了这里,关于MySQL 索引分类的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL数据库索引的数据结构

    数据库索引的功能就是让查找更加的高效,所以索引的数据结构应该是能够加速查找的数据结构。 MySQL的innoDB存储引擎的索引的数据结构就是多叉搜索树中的b+树,这可以说是为索引量身定做的一个数据结构。 首先,索引可以通过主键,unique修饰创建,也可以直接使用sql语句

    2024年02月10日
    浏览(52)
  • 【MySql系列】深入解析数据库索引

    MySQL索引是数据库中一个关键的概念,它可以极大地提高查询性能,加快数据检索速度。但是,要充分发挥索引的作用,需要深入理解它们的工作原理和使用方式。 在本文中,我们将深入解析MySQL索引,探讨它们的重要性、类型、创建、维护以及最佳实践。 在数据库中,索引

    2024年02月08日
    浏览(71)
  • 【MySQL数据库 | 第十七篇】索引以及索引结构介绍

    目录 前言: 索引简介:  索引结构:           二叉树索引结构         Tree(普通二叉树)         B-Tree(多路平衡查找树)         B+Tree          哈希索引数据结构 总结: 在实际生活中,我们对SQL语句进行优化实际上有很大一部分都是对索引进行优化,因此对索引

    2024年02月09日
    浏览(69)
  • MySQL数据库索引的种类、创建、删除

    目录 一:MySQL 索引 1、MySQL 索引介绍 2、 索引的作用  3、索引的副作用 4、 创建索引的原则依据  二、索引的分类和创建 1、 普通索引 (1) 直接创建索引 (2) 修改表方式创建 (3) 创建表的时候指定索引 2、 唯一索引 (1) 直接创建唯一索引 (2) 修改表方式创建

    2024年02月09日
    浏览(175)
  • 【Mysql系列】——详细剖析数据库“索引”【上篇】

        😎博客昵称:博客小梦 😊最喜欢的座右铭:全神贯注的上吧!!! 😊作者简介:一名热爱C/C++,算法,数据库等技术、喜爱运动、热爱K歌、敢于追梦的小博主! 😘博主小留言:哈喽! 😄各位CSDN的uu们,我是你的博客好友小梦,希望我的文章可以给您带来一定的帮

    2024年02月02日
    浏览(54)
  • B+树:MySQL数据库索引的实现

    作为一个软件开发工程师,你对数据库肯定再熟悉不过了。作为主流的数据存储系统,它在我们的业务开发中,有着举足轻重的地位。在工作中,为了加速数据库中数据的查找速度,我们常用的处理思路是,对表中数据创建索引。那你是否思考过,数据库索引是如何实现的呢

    2024年02月09日
    浏览(58)
  • MySQL数据库的ID列添加索引

    要为MySQL数据库的ID列添加索引,可以使用以下语法: 其中, table_name 是要添加索引的表名, index_name 是索引的名称, id 是要添加索引的列名。 例如,如果要为名为 users 的表的 id 列添加索引,可以执行以下语句: 这将在 users 表的 id 列上创建名为 idx_id 的索引。 需要注意的

    2024年02月07日
    浏览(50)
  • 【MySQL索引与优化篇】数据库的设计规范

    在关系型数据库中,关于数据表设计的基本原则、规则就称为范式 。范式的英文名称是 Normal Form ,简称 NF 。它是英国人 E.F.Codd 在上个世纪70年代提出关系数据库模型后总结出来的。范式是关系数据库理论的基础,也是我们在设计数据库结构过程中所要遵循的 规则 和 指导方

    2024年02月05日
    浏览(71)
  • MySQL数据库中的索引(含SQL语句)

    假设有一张表,表中有100万条数据,这100万条数据在硬盘上是存储在数据页上的,一页数据大小为16k。存储100万条数据那么就需要数据页,假设其中有一条数据是“id为7900”的,那么如果要查询这条数据,其中SQL是SELECT * FROM 表名 WHERE id = 7900。在执行这条SQL语句的时候,MyS

    2024年02月02日
    浏览(74)
  • 初识mysql数据库之索引概念与磁盘效率问题

    目录 一、索引的概念及作用 二、实际看看索引的效率提升 三、认识磁盘 1. 简单了解磁盘 2. 数据库文件存储位置 3. 定位扇区 4. 数据读取效率问题 5. 磁盘随机访问与磁盘连续访问 5.1 随机访问 5.2 连续访问 四、mysql与磁盘的交互 五、建立共识  索引,其实就是用于 提高数据

    2024年02月16日
    浏览(53)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包