一文读懂 MySQL 中的索引

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



一文读懂 MySQL 中的索引,MySQL,mysql,android,数据库

一文读懂 MySQL 中的索引,MySQL,mysql,android,数据库

1. 索引概述

1.1 索引概述

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。

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

1.2 优点

(1)类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本 ,这也是创建索引最主 要的原因。

(2)通过创建唯一索引,可以保证数据库表中每一行 数据的唯一性

(3)在实现数据的 参考完整性方面,可以 加速表和表之间的连接 。换句话说,对于有依赖关系的子表和父表联合查询时, 可以提高查询速度。

(4)在使用分组和排序子句进行数据查询时,可以显著 减少查询中分组和排序的时间 ,降低了CPU的消耗。

1.3 缺点

增加索引也有许多不利的方面,主要表现在如下几个方面:

(1)创建索引和维护索引要 耗费时间 ,并且随着数据量的增加,所耗费的时间也会增加。

(2)索引需要占 磁盘空间 ,除了数据表占数据空间之外,每一个索引还要占一定的物理空间, 存储在磁盘上 ,如果有大量的索引,索引文件就可能比数据文 件更快达到最大文件尺寸。

(3)虽然索引大大提高了查询速度,同时却会 降低更新表的速度 。当对表 中的数据进行增加、删除和修改的时候,索引也要动态地维护,这样就降低了数据的维护速度。

因此,选择索引时,需要综合考虑索引的优点和缺点。

提示:

索引可以提高查询速度,但是会影响插入记录的速度。这种情况下,最好的办法是先删除表中的索引,然后插入数据,插入完成后再创建索引。

1.6 常见索引概念

索引按照物理实现方式,索引可以分为 2 种:聚簇(聚集)和 非聚簇(非聚集)索引。

我们也把 非聚集索引 称为 二级索引 或者 辅助索引。

1.6.1 聚簇索引

特点

  1. 使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义:
    1. 页内的记录是按照主键的大小顺序排成一个单向链表 。
    2. 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表 。
    3. 存放目录项记录的页分为不同的层次,在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表 。
    1. B+树的 叶子节点 存储的是完整的用户记录。 所谓完整的用户记录,就是指这个记录中存储了所有列的值(包括隐藏列)。

优点

  • 数据访问更快 ,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快(非聚簇索引要回表)
  • 聚簇索引对于主键的 排序查找范围查找 速度非常快
  • 按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以 **节省了大量的io **操作 。

缺点

  • 插入速度严重依赖于插入顺序 ,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
  • 更新主键的代价很高 ,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新

限制
image.png

1.6.2 二级索引(辅助索引、非聚簇索引)

  • 二级索引访问需要两次索引查找(回表) ,第一次找到主键值,第二次根据主键值找到行数据

image-20220308220446548.png

image-20220307220411811.png

概念回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值,所以如果我们想根据c2列的值查找到完整的用户记录的话,仍然需要到 聚簇索引 中再查一遍,这个过程称为 回表 。也就是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树!

问题:为什么我们还需要一次 回表 操作呢?直接把完整的用户记录放到叶子节点不OK吗?

回答
image-20220308222225155.png

image-20220307220509373.png

小结:聚簇索引和非聚簇索引的区别:

  1. 聚簇索引叶子节点 存储的就是我们的 数据记录非聚簇索引叶子节点 存储的是 数据位置(主键)。 非聚簇索引不会影响数据表的物理存储顺序。
  2. 一个表只能一个聚簇索引, 因为只能有一种排序存储的方式, 但可以有 多个非聚簇索引, 也就是多个索引目录提供数据检索。
  3. 使用聚簇索引的时候,数据的 查询效率高, 但如果对数据进行插入,删除,更新等操作,效率会比非聚簇索引低。

1.6.3 联合索引

我们也可以同时以多个列的大小作为排序规则,也就是同时为多个列建立索引,比方说我们想让B+树按 照 c2c3列 的大小进行排序,这个包含两层含义:

  • 先把各个记录和页按照c2列进行排序。
  • 在记录的c2列相同的情况下,采用c3列进行排序 。

为c2和c3列建立的索引的示意图如下:
image-20220308223435416.png

注意一点,以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ,本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的,不同点如下:

  • 建立 联合索引 只会建立如上图一样的1棵B+树。
  • 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

1.8 MyISAM索引的原理

下图是MyISAM索引的原理图

image-20220307220840790.png

如果我们在Col2上建立一个二级索引,则此索引的结构如下图所示:

image-20220307220855008.png

1.9 MyISAM 与 InnoDB对比

MyISAM的索引方式都是“非聚簇”的,与InnoDB包含1个聚簇索引是不同的。

小结两种引擎中索引的区别

① 在InnoDB存储引擎中,我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录,而在 MyISAM 中却需要进行一次 回表 操作,意味着MyISAM中建立的索引相当于全部都是 二级索引 。

② InnoDB的数据文件本身就是索引文件,而MyISAM索引文件和数据文件是 分离的 ,索引文件仅保存数 据记录的地址。

③ InnoDB的非聚簇索引data域存储相应记录 主键的值 ,而MyISAM索引记录的是 地址 。换句话说, InnoDB的所有非聚簇索引都引用主键作为data域。

④ MyISAM的回表操作是十分 快速 的,因为是拿着地址偏移量直接到文件中取数据的,反观InnoDB是通过获取主键之后再去聚簇索引里找记录,虽然说也不慢,但还是比不上直接用地址去访问。

⑤ InnoDB要求表必须有主键 ( MyISAM可以没有 )。如果没有显式指定,则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整型。

image-20220307220938163.png

1.10 索引的代价

索引是个好东西,可不能乱建,它在空间和时间上都会有消耗:

  • 空间上的代价

每建立一个索引都要为它建立一棵B+树,每一棵B+树的每一个节点都是一个数据页,一个 页 默认会占用 16KB 的存储空间,一棵很大的B+树由许多数据页组成,那就是很大的一片存储空间。

  • 时间上的代价

每次对表中的数据进行 增、删、改 操作时,都需要去修改各个B+树索引。而且我们讲过,B+树每层节点都是按照索引列的值 从小到大的顺序排序 而组成了 双向链表 。不论是叶子节点中的记录,还是内节点中的记录(也就是不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏,所以存储引擎需要额外的时间进行一些 记录移位 , 页面分裂 、 页面回收 等操作来维护好节点和记录的排序。如果我们建了许多索引,每个索引对应的B+树都要进行相关的维护操作,会给性能拖后腿。

一个表上索引建的越多,就会占用越多的存储空间,在增删改的时候,性能就会越差。为了能建立又好又少的索引,我们得学学这些索引在哪些条件下起作用的。


2. 索引的创建与设计原则

2.1 索引的声明与使用

2.1.1 索引的分类


MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引 和 空间索引等。

  • 功能逻辑 上说,索引主要有 4 种,分别是普通索引、唯一索引、主键索引、全文索引。
  • 按照 物理实现方式 ,索引可以分为 2 种:聚簇索引 和 非聚簇索引。
  • 按照 作用字段个数 进行划分,分成 单列索引 和 联合索引。
  1. 普通索引

image.png

  1. 唯一性索引

image.png

  1. 主键索引

image.png

  1. 单列索引

image.png

  1. 多列(组合、联合)索引

image.png

  1. 全文索引

image.png
image.png

  1. 补充:空间索引

image.png

小结:不同的存储引擎支持的索引类型也不一样
InnoDB :支持 B-tree、Full-text 等索引,不支持 Hash索引;
MyISAM : 支持 B-tree、Full-text 等索引,不支持 Hash 索引;
Memory :支持 B-tree、Hash 等索引,不支持 Full-text 索引;
NDB :支持 Hash 索引,不支持 B-tree、Full-text 等索引;
Archive :不支持 B-tree、Hash、Full-text 等索引;

2.1.2 创建索引

image.png

2.1.2.1 创建表的时候创建索引

image.png

CREATE TABLE dept(
  -- 主键自动创建索引
  dept_id INT PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR(20)
);
CREATE TABLE emp(
  emp_id INT PRIMARY KEY AUTO_INCREMENT,
  --	自动创建唯一性索引
  emp_name VARCHAR(20) UNIQUE,
  dept_id INT,
  --	自动创建外键索引
  CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);

但是,如果显式创建表时创建索引的话,基本语法格式如下:

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |
DESC]

  • UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数,分别表示唯一索引、全文索引和空间索引;
  • INDEX 与 KEY 为同义词,两者的作用相同,用来指定创建索引;
  • index_name 指定索引的名称,为可选参数,如果不指定,那么MySQL默认col_name为索引名;
  • col_name 为需要创建索引的字段列,该列必须从数据表中定义的多个列中选择;
  • length 为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度;
  • ASC 或 DESC 指定升序或者降序的索引值存储。
  1. 创建普通索引
    在book表中的year_publication字段上建立普通索引,SQL语句如下:
CREATE TABLE book(
  book_id INT ,
  book_name VARCHAR(100),
  authors VARCHAR(100),
  info VARCHAR(100) ,
  comment VARCHAR(100),
  year_publication YEAR,
  -- 声明索引
  INDEX index_bname(book_name)
);
  1. 创建唯一索引
CREATE TABLE test1(
  id INT NOT NULL,
  name varchar(30) NOT NULL,
  -- 声明唯一索引
  UNIQUE INDEX uk_index_name(name)
);

该语句执行完毕之后,使用SHOW CREATE TABLE查看表结构:

SHOW INDEX FROM test1 \G
  1. 主键索引
    设定为主键后数据库会自动建立索引,innodb为聚簇索引,语法:
  • 随表一起建索引:
CREATE TABLE student (
  id INT(10) UNSIGNED AUTO_INCREMENT ,
  student_no VARCHAR(200),
  student_name VARCHAR(200),
  PRIMARY KEY(id)
);
  • 删除主键索引:
ALTER TABLE student drop PRIMARY KEY ;
  • 修改主键索引:必须先删除掉(drop)原索引,再新建(add)索引
  1. 创建单列索引
CREATE TABLE test2(
  id INT NOT NULL,
  name CHAR(50) NULL,
  INDEX single_idx_name(name(20))
);

该语句执行完毕之后,使用SHOW CREATE TABLE查看表结构:

SHOW INDEX FROM test2 \G  
  1. 创建联合索引
    举例:创建表test3,在表中的id、name和age字段上建立组合索引,SQL语句如下:
CREATE TABLE test3(
  id INT(11) NOT NULL,
  name CHAR(30) NOT NULL,
  age INT(11) NOT NULL,
  info VARCHAR(255),
  INDEX multi_idx(id,name,age)
);

注意:查询时要遵循最左前缀原则,否则索引不能名中。

  1. 创建全文索引
    举例1:创建表test4,在表中的info字段上建立全文索引,SQL语句如下:
CREATE TABLE test4(
  id INT NOT NULL,
  name CHAR(30) NOT NULL,
  age INT NOT NULL,
  info VARCHAR(255),
  FULLTEXT INDEX futxt_idx_info(info)
) ENGINE=MyISAM;


在MySQL5.7及之后版本中可以不指定最后的ENGINE了,因为在此版本中InnoDB支持全文索引。

举例2:

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR (200),
body TEXT,
FULLTEXT index (title, body)
) ENGINE = INNODB ;


举例3:

CREATE TABLE `papers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


不同于like方式的的查询:

SELECT * FROM papers WHERE content LIKE%查询字符串%;


全文索引用match+against方式查询:

SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);


注意点:

  1. 使用全文索引前,搞清楚版本支持情况;
  2. 全文索引比 like + % 快 N 倍,但是可能存在精度问题;
  3. 如果需要全文索引的是大量数据,建议先添加数据,再创建索引。
  1. 创建空间索引
    空间索引创建中,要求空间类型的字段必须为 非空
    举例:创建表test5,在空间类型为GEOMETRY的字段上创建空间索引,SQL语句如下:
CREATE TABLE test5(
  geo GEOMETRY NOT NULL,
  SPATIAL INDEX spa_idx_geo(geo)
) ENGINE=MyISAM;
2.1.2.2 在已经存在的表上创建索引


在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。

  1. 使用ALTER TABLE语句创建索引 ALTER TABLE语句创建索引的基本语法如下:
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...  [ASC | DESC])
  1. 使用CREATE INDEX创建索引CREATE INDEX语句可以在已经存在的表上添加索引,在MySQL中,
    CREATE INDEX被映射到一个ALTER TABLE语句上,基本语法结构为:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

2.1.3 删除索引

  1. 使用ALTER TABLE删除索引 ALTER TABLE删除索引的基本语法格式如下:
 ALTER TABLE table_name DROP INDEX index_name;  
  1. 使用DROP INDEX语句删除索引 DROP INDEX删除索引的基本语法格式如下:
DROP INDEX index_name ON table_name;

提示: 删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除。如果组成
索引的所有列都被删除,则整个索引将被删除。



一文读懂 MySQL 中的索引,MySQL,mysql,android,数据库文章来源地址https://www.toymoban.com/news/detail-577215.html

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

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

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

相关文章

  • MySQL数据库:索引

            索引是一种特殊的文件,包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引,并指定索引的类型,各类索引有各自的数据结构实现。         相当于是给数据库中的数据建立了一个目录,通过目录可以知道数据所在位置,然后到指定位置

    2023年04月17日
    浏览(74)
  • 一文读懂 MySQL 锁

    1.1 什么是锁 锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。MySQL中为了保证数据访问的一致性与有效性等功能,实现了锁机制,MySQL中的锁是在服务器层或者存储引擎层实现的。 1.2 锁用来解决什么问题 锁是用来解决并发事务的访问问题,我们已经知道事

    2024年02月08日
    浏览(68)
  • MySQL数据库唯一索引

    创建索引是指在某个表的一列或多列上建立一个索引,以便提高对表的访问速度。创建索引有3种方式,分别是1.创建表的时候创建索引、2.在已经存在的表上创建索引和使用3.ALTER TABLE语句来创建索引。 本文福利, 莬 费领取Qt开发学习资料包、技术视频,内容包括(C++语言基

    2024年02月08日
    浏览(61)
  • MySQL数据库索引机制

    MySQL是一款有客户端和服务端的网络应用,mysql是它的客户端,mysqld是它的服务端。服务端本质就是一个进程,它存在于内存当中。而我们存储在MySQL中的数据是保存在磁盘上的,当我们对MySQL中数据进行增删查改操作时,不可能是直接在磁盘上进行操作,而是将对应的数据加

    2024年02月12日
    浏览(72)
  • MySQL数据库索引的数据结构

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

    2024年02月10日
    浏览(53)
  • 简单认识MySQL数据库索引

    提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 ●索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址(类似于C语言的链表通过指针指向数据记录的内存地址)。 ●使用索引后可以不用扫描全表来定位某行的

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

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

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

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

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

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

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

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

    2024年02月09日
    浏览(185)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包