Young Kbt blog
Young Kbt
2021-10-24
目录

MySQL - 索引介绍

# 推荐文章

# 索引的概念

索引（Index）是帮助 MySQL 高效获取数据的数据结构，可以简单理解为 排好序的快速查找数据结构。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是 索引。

作为一个索引，一般是采用 Key-Value 的方式来存储内容。Key 表示索引的关键字，而 Value 表示索引内容存放的位置（记录），假设是硬盘中的某个位置，或者说是一个数据文件的偏移量。于是这样就可以根据索引的内容来查询文件的位置。

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

索引优缺点

优势：

  • 提高数据检索的效率，降低数据库的 IO 成本
  • 数据排序，降低 CPU 消耗

劣势：

  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度

    如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要在索引文件保存一下每次更新添加了索引列的字段，都会调整因更新所带来的键值变化后的索引信息。

  • 索引本质也是一张表，保存着索引字段和指向实际记录的指针，所以也要占用数据库空间，一般而言，索引表占用的空间是数据表的 1.5 倍

# 索引结构

# Btree 索引结构

Btree 又可以写成 B-tree（B-Tree，并不是 B「减」树，横杠为连接符，容易被误导）

BTree 又叫多路平衡搜索树，一颗 m 叉的 BTree 特性如下：

  • 树中每个节点最多包含 m 个孩子
  • 除根节点与叶子节点外，每个节点至少有 ceil(m / 2) 个孩子
  • 若根节点不是叶子节点，则至少有两个孩子
  • 所有的叶子节点都在同一层
  • 每个非叶子节点由 n 个 key 与 n + 1 个指针组成，其中 ceil(m / 2) - 1 <= n <= m - 1

celi()：向上取整，例如 celi(2.5) = 3。

# 演变过程

以 5 叉 BTree 为例，key 的数量：公式推导 ceil(m / 2) - 1 <= n <= m - 1。所以 2 <= n <= 4 。

当 n > 4 时，中间节点分裂到父节点，两边节点分裂。

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。（插入时，按照 ABCD... 顺序）

1). 插入前 4 个字母 C N G A

2). 插入 H，n > 4，中间元素 G 字母向上分裂到新的节点

3). 插入 E，K，Q 不需要分裂

4). 插入 M，中间元素 M 字母向上分裂到父节点 G

M 在 K、N 中间，BTree 最多含有 n - 1 个 key，这里即 4 个 key，5 个指针

5). 插入 F，W，L，T 不需要分裂

6). 插入 Z，中间元素 T 向上分裂到父节点中

插入 Z 后，把 N Q T W Z 的中间 T 提出来，方便更快的查询，所以不提出 Z

7). 插入 D，中间元素 D 向上分裂到父节点中。然后插入 P，R，X，Y 不需要分裂

8). 最后插入 S（NPQR 后面），此时节点 n > 5，所以中间节点 Q 向上分裂，但分裂后父节点 DGMT 的 n > 5，中间节点 M 向上分裂

到此，该 BTree 树就已经构建完成了，BTree 树和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，BTree 的层级结构比二叉树小，因此搜索速度快。

# 其他

初始化介绍

一颗 BTree 树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）。

如磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3，P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块。真实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如 17、35 并不真实存在于数据表中。

查找过程

如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO，在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘的 IO）可以忽略不计，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO，29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指 针，通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总计三次 IO。

真实的情况是，3 层的 BTree 树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高。

# B+tree 结构

B+Tree 为 BTree（也叫 B-Tree）的变种，B+Tree 与 BTree 的区别为：

  • n 叉 B+Tree 最多含有 n 个 key，而 BTree 最多含有 n-1 个 key
  • B+Tree 的叶子节点保存所有的 key 信息，依 key 大小顺序排列
  • 所有的非叶子节点都可以看作是 key 的索引部分
  • B 树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+ 树的非叶子节点中只有索引关键字和指向下一个节点的索引，记录（字段位置）只放在叶子节点中
  • 在 B 树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B+ 树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看 B 树的性能好像要比 B+ 树好，而在实际应用中却是 B+ 树的性能要好些。因为 B+ 树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B 树多，树高比 B 树小（B- 树存放数据量大，所以数深度较大），这样带来的好处是减少磁盘访问次数。尽管 B+ 树找到一个记录所需的比较次数要比 B 树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中 B+ 树的性能可能还会好些，而且 B+ 树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用 B+ 树的缘故
  • 还有一个原因：B+ 树的叶子节点是排序且通过指针连接的，所以非常适合范围和排序查询，即适合关系型数据库，而 B 树将关键字和记录放在一起，更适合精准查询，适合非关系型数据库

为什么 B+ 树比 B- 树更适合索引？

B+ 树的磁盘读写代价更低

B+ 树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。

B+ 树的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

也就是范围、排序查询等花费的时间基本一样，而 B 树需要的数据可能在不同层的节点，所以需要多次遍历。

# MySQL中的B+Tree

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针（包括首尾叶子节点），就形成了带有顺序指针的 B+Tree，提高区间访问的性能。

并且 MySQL 中的 B+Tree 根节点会存储最小或最大的数据。

MySQL 中的 B+Tree 索引结构示意图（叶子节点的指针是双向的）:

# 聚簇索引和非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语「聚簇」表示数据行和相邻的键值聚簇的存储在一起。如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

聚簇索引的好处

按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多个数据块中提取数据，所以节省了大量的 IO 操作。

聚簇索引的限制

对于 MySQL 数据库目前只有 InnoDB 数据引擎支持聚簇索引，而 Myisam 并不支持聚簇索引。

由于数据物理存储排序方式只能有一种，所以每个 Mysql 的表只能有一个聚簇索引。一般情况下就是该表的主键。

为了充分利用聚簇索引的聚簇的特性，所以 InnoDB 表的主键列尽量选用有序的顺序 ID，而不建议用无序的 ID，比如 UUID 这种。

所以主键一般都和 数据 一起存储在一起，查询就非常快，非聚簇索引是将关键词和 数据的位置 存在一起，所以还得根据数据的位置找到数据，相对慢了些。

# 时间复杂度

同一问题可用不同算法解决，而一个算法的质量优劣将影响到算法乃至程序的效率。算法分析的目的在于选择合适算法和改进算法。

时间复杂度是指执行算法所需要的计算工作量，用大 O 表示记为：O(…)

# 索引分类

# 单列索引

概念：即一个索引只包含单个列，一个表可以有多个单列索引。

  • 索引和表一起创建

    CREATE TABLE customer (
  id INT (10) UNSIGNED AUTO_INCREMENT,
  customer_no VARCHAR (200),
  customer_name VARCHAR (200),
  PRIMARY KEY (id),
  KEY (customer_name)	#this
);
    1
    2
    3
    4
    5
    6
    7

  • 单独建单列索引

    CREATE INDEX idx_customer_name
ON customer (customer_name);
    1
    2

# 唯一索引

概念：索引列的值必须唯一，但允许有空值

  • 随表一起创建

    CREATE TABLE customer (
  id INT (10) UNSIGNED AUTO_INCREMENT,
  customer_no VARCHAR (200),
  customer_name VARCHAR (200),
  PRIMARY KEY (id),
  KEY (customer_name),
  UNIQUE (customer_no)	# this
);
    1
    2
    3
    4
    5
    6
    7
    8

  • 单独建唯一索引：

    CREATE UNIQUE INDEX idx_customer_no
ON customer (customer_no);
    1
    2

# 复合索引

概念：即一个索引包含多个列

  • 随表一起建索引

    CREATE TABLE customer (
  id INT (10) UNSIGNED AUTO_INCREMENT,
  customer_no VARCHAR (200),
  customer_name VARCHAR (200),
  PRIMARY KEY (id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no, customer_name)	#this
);
    1
    2
    3
    4
    5
    6
    7
    8
    9

  • 单独建索引：

    CREATE INDEX idx_no_name
ON customer (customer_no, customer_name);
    1
    2

# 主键索引

概念：设定为主键后数据库会自动建立索引，InnoDB 为聚簇索引

  • 随表一起建索引

    CREATE TABLE customer (
  id INT (10) UNSIGNED AUTO_INCREMENT,
  customer_no VARCHAR (200),
  customer_name VARCHAR (200),
  PRIMARY KEY (id)	#this
);
    1
    2
    3
    4
    5
    6

  • 单独建主键索引：

    ALTER TABLE customer ADD PRIMARY KEY customer(customer_no);
    1

  • 删除主键索引

    ALTER TABLE customer drop PRIMARY KEY;
    1

  • 修改建主键索引

    必须先删除掉（drop）原索引，再新建（add）索引。

# 索引基本语法

  • 创建

    CREATE [UNIQUE] INDEX [indexName]
ON table_name(column))
    1
    2

    示例 ：为 city 表中的 city_name 字段创建索引；

  • 删除

    DROP INDEX index_name ON tbl_name;
    1

  • 查看

    show index from table_name;
    1

  • AlTER

    1). alter table tb_name add primary key(column_list); 

	该语句添加一个主键，这意味着索引值必须是唯一的，且不能为 NULL
	
2). alter table tb_name add unique index_name(column_list);
	
	这条语句创建索引的值必须是唯一的（除了 NULL 外，NULL 可能会出现多次）
	
3). alter table tb_name add index index_name(column_list);

	添加普通索引，索引值可以出现多次。
	
4). alter table tb_name add fulltext index_name(column_list);
	
	该语句指定了索引为 FULLTEXT，用于全文索引
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16

# 索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

  • 对查询频次较高，且数据量比较大的表建立索引

  • 索引字段的选择，最佳候选列应当从 where 子句的条件中提取，如果 where 子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合

  • 使用唯一索引，区分度越高，使用索引的效率越高

  • 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等 DML 操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低 DML 操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL 也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价

  • 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的 I/O 效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升 MySQL 访问索引的 I/O 效率

  • 利用最左前缀，N 个列组合而成的组合索引，那么相当于是创建了 N 个索引，如果询时 where 子句中使用了组成该索引的前几个字段，那么这条查询 SQL 可以利用组合索引来提升查询效率

创建复合索引

CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
1

就相当于

​ 对 name 创建索引; ​ 对 name，email 创建了索引; ​ 对 name，email，status 创建了索引;

不适合创建索引的情况

  • 表记录太少
  • 经常增删改的表或者字段
  • Where 条件里用不到的字段不创建索引
  • 过滤性不好的不适合建索引
编辑此页 (opens new window)
#关系数据库
更新时间: 2023/10/23, 10:58:52
MySQL - 安装
MySQL - 逻辑架构简介

← MySQL - 逻辑架构简介→

最近更新
01
技术随笔 - Element Plus 修改包名 原创
11-02
02
Reactor - 扩展性
11-02
03
Reactor - 最佳实践
11-02
更多文章>
Theme by Vdoing | Copyright © 2021-2024 Young Kbt | blog
桂ICP备2021009994号