一文读懂 MySQL 中的索引

发布于：2024-04-16 ⋅ 阅读:(18) ⋅ 点赞:(0)

文章目录



在这里插入图片描述

在这里插入图片描述

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+树按 照 c2和c3列 的大小进行排序，这个包含两层含义：

  • 先把各个记录和页按照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;

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



在这里插入图片描述

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布