OddFar's Notes
  • 数据库
  • MySQL
zhiyuan
2021-05-20
目录

MySQL - 索引介绍

# 索引的概念

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

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

1555902055367

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

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

索引优缺点

优势：

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

劣势：

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

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

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

# 推荐文章

# 索引结构

# Btree 索引结构

黑马教程：https://www.bilibili.com/video/BV1UQ4y1P7Xr?p=6

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

1555944126588

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

1555944549825

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

1555944596893

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

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

1555944652560

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

1555944686928

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

（插入Z后是，NQTWZ，把中间T提出来，方便更快的查询，所以不提出Z）

1555944713486

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

1555944749984

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

1555944848294

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

# 其他

image-20210520190717898

初始化介绍

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

如磁盘块 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 层的 b+树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的， 如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高。

# B+tree 结构

1555906287178

B+Tree为BTree的变种，B+Tree与BTree的区别为：

1). n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。

2). B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。

3). 所有的非叶子节点都可以看作是key的索引部分。

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

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

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

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

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

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

# MySQL中的B+Tree

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

MySQL中的 B+Tree 索引结构示意图:

1555906287178

# 聚簇索引和非聚簇索引

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

image-20210520211641159

聚簇索引的好处

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

聚簇索引的限制

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

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

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

# 时间复杂度

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

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

image-20210520211835491

image-20210520212305695

# 索引分类

# 单值索引

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

  • 所表一起创建

    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字段创建索引 ；

    1551438009843

  • 删除

    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 条件里用不到的字段不创建索引
  • 过滤性不好的不适合建索引
在 GitHub 上编辑此页 (opens new window)
最后更新: 2023/03/26, 13:03:00
MySQL - 逻辑架构简介
MySQL - 优化SQL检测步骤

← MySQL - 优化SQL检测步骤→

Theme by Vdoing | Copyright © 2021-2023 oddfar | 冀ICP备20001094号