一文搞懂 MySQL 索引

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

一文搞懂 MySQL 索引



1、MySQL 索引 简介

1.1、MySQL 索引 是什么?

 索引是一个单独的、存储在 磁盘 上的 数据库结构 ,包含着对数据表里 所有记录的 引用指针。


1.2、 MySQL 索引 的存储类型有哪些?

 MySQL中索引的存储类型有两种,即 BTree 和 Hash。


1.3、MySQL 索引 在哪里实现的?

 索引是在存储引擎中实现的。(MySQL 的存储引擎有:InnoDB、MyISAM、Memory、Heap)

  • InnoDB / MyISAM 只支持 BTree 索引
  • Memory / Heap 都支持 BTree 和 Hash 索引

1.4、存储引擎 是什么?

 存储引擎就是指 表的类型 以及 表在计算机上的存储方式。


1.5、索引 的优缺点有哪些?

优点:

  • 提高数据的查询的效率(类似于书的目录)
  • 可以保证数据库表中每一行数据的唯一性(唯一索引)
  • 减少分组和排序的时间(使用分组和排序子句进行数据查询)
    • 被索引的列会自动进行分组和排序

缺点:

  • 占用磁盘空间
  • 降低更新表的效率(不仅要更新表中的数据,还要更新相对应的索引文件)


2、MYSQL 索引 的分类

1、普通索引 和 唯一索引

  • 普通索引:MySQL 中的基本索引类型,允许在定义索引的列中插入 重复值 和 空值

  • 唯一索引:要求索引列的值必须 唯一,但允许 有空值

    • 如果是组合索引,则列值的组合必须 唯一
    • 主键索引是一种特殊的唯一索引,不允许 有空值

2、单列索引 和 组合索引

  • 单列索引:一个索引只包含单个列,一个表可以有多个单列索引
  • 组合索引:在表的 多个字段 组合上 创建的 索引
    • 只有在查询条件中使用了这些字段的 左边字段 时,索引才会被使用(最左前缀原则)

3、全文索引

  • 全文索引 的类型为 fulltext
  • 在定义索引的 列上 支持值的全文查找,允许在这些索引列中插入 重复值 和 空值
  • 全文索引 可以在 char、varchar 和 text 类型的 列 上创建

4、空间索引

  • 空间索引 是对 空间数据类型 的字段 建立的索引

  • MySQL中的空间数据类型有4种,分别是 Geometry、Point、Linestring 和 Polygon

  • MySQL 使用 Spatial 关键字进行扩展,使得能够用创建正规索引类似的语法创建空间索引

  • 创建空间索引的列,不允许为空值,且只能在 MyISAM 的表中创建。

5、前缀索引

  • 在 char、varchar 和 text 类型的 列 上创建索引时,可以指定索引 列的长度


3、MySQL 索引 的数据结构

 MySQL 索引 的数据结构可以分为 BTree 和 Hash 两种,BTree 又可分为 BTree 和 B+Tree。


Hash:使用 Hash 表存储数据,Key 存储索引列,Value 存储行记录或行磁盘地址。

 Hash 只支持等值查询(“=”,“IN”,“<=>”),不支持任何范围查询(原因在于 Hash 的每个键之间没有任何的联系),Hash 的查询效率很高,时间复杂度为 O(1)。


BTree:属于多叉树,又名多路平衡查找树。

性质:

  • BTree 的节点存储多个元素( 键值 - 数据 / 子节点 的地址)
  • BTree 节点的键值按 非降序 排列
  • BTree 所有叶子节点都位于同一层(具有相同的深度)

一文搞懂 MySQL 索引

查询过程,例如:Select * from table where id = 6;

一文搞懂 MySQL 索引

BTree 的不足:

  • 不支持范围查询的快速查找(每次查询都得从根节点重新进行遍历)
  • 节点都存储数据会导致磁盘数据存储比较分散,查询效率有所降低

B+Tree:在 BTree 的基本上,对 BTree 进行了优化:只有叶子节点才会存储 键值 - 数据,非叶子节点只存储 键值 和 子节点 的地址;叶子节点之间使用双向指针进行连接,形成一个双向有序链表。

一文搞懂 MySQL 索引

等值查询,例如:Select * from table where id = 8;

一文搞懂 MySQL 索引

范围查询,例如:Select * from table where id between 8 and 22;

一文搞懂 MySQL 索引

B+Tree 的优点:

  • 保证了等值查询和范围查询的快速查找
  • 单一节点存储更多的元素,减少了查询的 IO 次数


4、MySQL 索引 的实现

4.1、MyISAM 索引
  • MyISAM 的 数据文件(.myd) 和 索引文件(.myi) 是分开存储的
  • MyISAM(B+Tree)叶子节点中存储的键值为索引列的值,数据为索引所在行的磁盘地址
  • MyISAM 的 主键索引(Primary key)和 辅助索引(Secondary key)在结构上没有任何区别,只是 主键索引 要求 键值唯一,而 辅助索引 键值 可以重复

一文搞懂 MySQL 索引


4.2、InnoDB 索引
  • 数据和索引都存储在一个文件中(.ibd)

  • 一般情况下,聚簇索引等同于主键索引;除 聚簇索引 外的所有索引 均称为 辅助索引

  • InnoDB(B+Tree)叶子节点中存储的键值为索引列的值

    • 如果是聚簇索引,数据为整行记录(除了主键值)
    • 如果是辅助索引,数据为该行的主键值
  • 每一张表都有一个聚簇索引

    • 如果表中有定义主键,主键索引用作聚簇索引
    • 如果表中没有定义主键,选择第一个不为 NULL 的唯一索引列用作聚簇索引
    • 如果以上都没有,使用一个 6 字节长整形的隐式字段 ROWID (自增)用作聚簇索引
  • 根据在 辅助索引树 中获取的 主键id,再到 主键索引树 查询数据的过程 称为 回表 查询

  • 组合索引

    • 遵循 最左匹配(最左前缀)原则:
      • 使用 组合索引 查询时,MySQL 会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。
    • 只有第一列是有序的,其它列都是无序的(最左匹配原则的原因)

主键索引(聚簇索引):
一文搞懂 MySQL 索引

辅助索引:

一文搞懂 MySQL 索引

组合索引:

一文搞懂 MySQL 索引

一文搞懂 MySQL 索引

覆盖索引:

  • 覆盖索引不是一种索引结构,而是一种优化手段
  • 我们只需要查询 组合索引 中的字段,而不需要表中的其它字段,在这过程中不会产生回表现象,这种情况称为 覆盖索引
create index idx on user(name, age, gender);
-- 使用覆盖索引
explain select name, age, gender from user where name ='万叶' and age = 18 and gender = '0'; 

一文搞懂 MySQL 索引

-- 未使用覆盖索引
explain select * from user where name ='万叶' and age = 18 and gender = '0';

一文搞懂 MySQL 索引



5、MySQL 索引 的使用

5.1、MySQL 索引 的基本语法

  • 定义 主键约束、外键约束、唯一约束 等约束时 相当于同时在指定列上创建了一个索引

创建表时:

create table table_name(
	[col_name data_type] [unique | fulltext | spatial...],
    [unique...] [index | key] [index_name] (col_name [length], ...)
);

create table user (
    id INT NOT NULL, 
    name CHAR(30) NOT NULL, 
    unique index uniqueIdx(id) 
);

表已存在时:

-- 第一种 
alter table table_name 
	add [unique...] [index | key] [index_name] (col_name [length], ...);
	
alter table user add unique index uniqueIdx(id);

-- 第二种 
create [unique...] index index_name 
on table_name (col_name [length], ...);

create unique index uniqueIdx on user(id);


-- 删除索引
 drop index index_name on table_name;

5.2、怎么判断要不要加索引?

加索引:

  • 数据本身具有某种的性质,如:唯一性、非空性…
  • 频繁进行 分组或排序 的列;如果待排序的列有多个,可以建立 组合索引

不加索引:

  • 经常更新的列
  • 列 的值类型 很少,如 性别
  • where 条件中用不到的列
  • 参与计算的列
  • 数据量小的表

5.3、只要创建了索引,就一定会生效吗?

 不一定。当使用 组合索引 时,如果没有遵循 最左匹配 原则,索引不生效。

例如,创建 id、name、age 组合索引

  • id、(id、name)、(id、name、age)查询,索引生效
  • age、(age、name)查询,索引不生效

5.4、怎样判断索引是否生效?

 使用 explain 关键字。

  • possible_keys:MySQL 在搜索数据记录时可选用的各个索引
  • key:MySQL 实际选用的索引

例如:

explain select * from user where id = 1;

一文搞懂 MySQL 索引


5.5、怎么避免索引失效?文章来源地址https://www.toymoban.com/news/detail-440557.html

  • 使用组合索引时,遵循 最左匹配 原则
  • 不在索引列上进行任何操作,如:计算、函数、类型转换
  • 尽量使用覆盖索引
  • 索引列 尽量不使用 不等于(!= / <>)条件、通配符开头的模糊查询(like %abc)、or 作为连接条件
  • 字符串加单引号(不加可能会发生索引列的隐式转换,导致索引失效)

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

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

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

相关文章

  • mysql处理json格式的字段,一文搞懂mysql解析json数据

    略。自行百度。 JSON 数据类型是 MySQL 5.7.8 开始支持的。在此之前,只能通过字符类型(CHAR,VARCHAR 或 TEXT )来保存 JSON 文档。 MySQL 8.0版本中增加了对JSON类型的索引支持。可以使用CREATE INDEX语句创建JSON类型的索引,提高JSON类型数据的查询效率。 存储JSON文档所需的空间与存储

    2024年02月07日
    浏览(54)
  • MySQL:一文掌握MySQL索引

    官方定义:索引(Index)是帮助MySQL高效获取数据的数据结构。 在数据库中,索引被定义为一种特殊的数据结构,由数据库中的一列或多列组合而成,可以用来快速查询数据表中某一特定值的记录,就像一本书的目录一样。索引是在表的字段的基础上建立的一种数据库对象。

    2024年02月05日
    浏览(41)
  • 【MYSQL篇】一文弄懂mysql索引原理

    MySQL 数据库应该是最常用的数据库之一,在各种大大小小的公司都可以看到它的身影,你对 MySQL 数据库掌握的如何呢?想要更好的使用它,那么我们就必须先了解它,正所谓的 工欲善其事,必先利其器 。 本篇文章就带领大家一起来深入剖析MySQL索引的一些知识,先来了解什

    2024年02月09日
    浏览(69)
  • 一文读懂 MySQL 中的索引

    MySQL官方对索引的 定义 为:索引(Index)是帮助MySQL高效获取数据的数据结构。 索引的本质 :索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”,满足特定查找算法。 这些数据结构以某种方式指向数据, 这样就可以在这些数据结构的基础上实现 高级查找算

    2024年02月16日
    浏览(36)
  • 一文让你对mysql索引底层实现明明白白

    图片是本人随笔画的,有点粗糙,望大家谅解,如有不妥之处,请联系我们,感谢 .索引是帮助mysql高效获取数据的排好序的数据结构 .索引是存储在文件里的 .数据结构: 二叉树 HASH BTREE       如果没有索引的话,循环一条一条的找,找一次就是一次IO,这样速度就会很慢 我

    2024年01月16日
    浏览(41)
  • 【MySQL】一文带你了解数据库索引与事务

    数据库索引是一种提高数据库查询效率的数据结构。它可以快速地定位和访问数据库中的数据,从而大大提高数据库查询的速度和效率。数据库索引可以根据不同的查询需求构造多个索引,以最大化提高查询效率。 数据库索引基于各种字段来创建,在查询时可以通过索引直接

    2024年02月09日
    浏览(49)
  • 一文彻底搞清楚MySQL的主键、外键、约束和各种索引

    主键用于唯一标识表中每一行数据,外键用于建立表与表之间关联关系,约束用于限制表中数据的规则,索引用于加速查询。 主键是一种用于唯一标识表中每一行数据的标识符。在Mysql中,主键可以是一个或多个列的组合,但是必须满足以下条件: 主键列的值必须唯一,不能

    2024年02月08日
    浏览(51)
  • 一文带你了解MySQL之B+树索引的使用

    前言 我们上一篇文章详细的了InnoDB存储引擎的B+树索引,我们必须知道下边这些结论: 每个索引都对应1棵B+树,B+树分为好多层,最下边一层是叶字节点,其余的是内节点(非叶子节点)。所有用户户记录都存储在B+树的叶子节点,所有目录项记录都存储在内节点。 InnoDB存储

    2024年02月06日
    浏览(44)
  • 一文搞懂KMP算法!!!

    KMP算法是一种改进的 字符串匹配算法 ,由 D.E. K nuth , J.H. M orris 和 V.R. P ratt 提出的,因此人们称它为 克努特—莫里斯—普拉特 操作(简称 KMP 算法)。 KMP 算法的核心是利用匹配失败后的信息,尽量减少模式串与主串的匹配次数以达到快速匹配的目的。 具体实现就是通过一

    2024年02月07日
    浏览(45)
  • 一文搞懂隐私计算

    隐私计算(Privacy computing)是指在保证数据不对外泄露的前提下,由两个或多个参与方联合完成数据分析计算相关技术的统称。 隐私计算作为跨学科技术,以密码学为核心理论, 结合了大数据、人工智能、区块链等多领域知识。其这些技术路线中,以安全多方计算为代表的基

    2024年02月07日
    浏览(48)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包