L o a d i n g . . .

MySQL学习(4)---MySQL索引

ps：没有特殊说明，此随笔中默认采用innoDB存储引擎中的索引，且索引都是指B+树（多路平衡搜索树）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+树，统称为索引。

索引概述

索引是存储引擎用于快速找到记录的一种数据结构。

如下图所示：

使用和未使用索引对比

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

要理解MySQL中索引是如何工作的，最简单的方法就是去看看一本书的“索引”部分：如果想在一本书中找到某个特定主题，一般会先看书的“索引”，找到对应的页码。

索引对于良好的性能非常关键。尤其是当表中的数据越来越大时，索引对性能影响愈发重要。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降。

一般来说索引本身占用也较大，不可能将全部索引存储在内存中，因此索引往往以索引文件（注：存储引擎是MYISAM时，索引单独存储在.myi文件中；存储引擎是InnoDB时，索引和表数据一起存储在.ibd文件中）的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

为什么需要索引

不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

建立索引的优势与劣势

优势

  • 类似于书籍的目录索引，提高数检索的效率，降低数据库的IO成本。
  • 在实现数据的参考完整性方面可以加速表与表之间的连接。
  • 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间.

劣势

  • 实际上索引也是一张表，该表中保存了主键和索引字段，并指向实体类的记录，因此需要占用物理空间。数据量越大，占用空间就越大。
  • 创建和维护索引都需要耗费时间，这种时间随着数据量的增加而增加。
  • 虽然创建索引大大提高了查询的效率，同时却也降低了更新表的速度，例如对表进行insert、update、delete等操作。因为更新表时，MySQL不仅要保存数据，还需要保存索引文件每次更新的索引列的字段，动态调整因为更新所带来的键值变化后的索引信息。

索引的底层数据结构

索引的底层数据结构是B+树。在学习它之前需要了解它的由来。

二叉查找树的引入

首先，我们来看二叉查找树。

二叉查找树有这样的特点：

  • 若它的左子树不空，则左子树上所有结点的值均小于它的根结点的值；
  • 若它的右子树不空，则右子树上所有结点的值均大于它的根结点的值；
  • 它的左、右子树也分别为二叉查找树。

普通二叉查找树如下图所示：

二叉查找树

二叉查找树如果是完全二叉树，查找的效率很高，时间复杂度为O(log2N)。因为最多只需要查找到树的log2N深度层。

但是当二叉查找树为单支树时，其退化为链表，此时它的深度为N，时间复杂度为O(N)。

此时的二叉查找树如下图所示：

单支树

因此，需要平衡二叉树来降低最坏情况下二叉查找树的深度。

平衡二叉树（AVL树）的引入

平衡二叉查找树除了具备二叉查找树的特点，最主要的特征是树的左右两个子树的层级最多相差1。其在插入删除数据时通过左旋或右旋操作来保持树的平衡，不会出现左右子树一边很高、一边很矮的情况。

平衡二叉树如下图所示：

平衡二叉树

平衡二叉树通过对二叉查找树进行平衡化，从而保证了对树进行操作的时间复杂度不会退化。因此，它的平均时间复杂度为O(log2N)。

毫无疑问，平衡二叉树的查找效率是非常高的。但是当数据量非常大，树存储的元素数量是有限的的，这样会导致平衡二叉树的平均深度过大而造成磁盘IO读写过于频繁，进而导致查询效率低下。另外数据量过大会导致内存空间不够容纳平衡二叉树所有节点的情况。

时间复杂度和树高相关。树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操 作。树的高度就等于每次查询数据时磁盘 IO 操作的次数。磁盘每次寻道时间为10ms，在表数据量大时，查询性能就会很差。（1百万的数据量，log2n约等于20次磁盘IO，时间20*10=0.2s）

平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

B树是解决这个问题的很好的数据结构。

前置知识：磁盘IO与预读

上文提到了磁盘访问，这里简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分：

寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常说的磁盘转速磁盘转速，比如一个磁盘7200转/min，表示每分钟能转7200次，也就是说一秒能转120次，旋转延迟就是1/120/2=4.17ms，也就是半圈的时间（这里有两个时间：平均寻道时间，受限于目前的物理水平，大概是5ms的时间，找到磁道了，还需要找到你数据存在的那个点，寻点时间，这寻点时间的一个平均值就是半圈的时间，这个半圈时间叫做平均延迟时间，那么平均延迟时间加上平均寻道时间就是你找到一个数据所消耗的平均时间，大概9ms，其实机械硬盘慢主要是慢在这两个时间上了，当找到数据然后把数据拷贝到内存的时间是非常短暂的，和光速差不多了）；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。

那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17=9ms左右，听起来还挺不错的，但要知道一台500-MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的消耗的时间段下cpu可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难，所以我们要想办法降低IO次数。下图是计算机硬件延迟的对比图，供大家参考：

1614350-20201110080218818-1146633478

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页（page）。具体一页有多大数据跟操作系统有关，一般为4KB或8KB，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

B树（平衡二叉树的改造）的引入

对于平衡二叉树，考虑到磁盘IO对查询数据效率的影响，我们更希望出现“矮胖”而不是“瘦高”树，因为这样可以显著减少查询时的IO次数，增加查询效率。那么我们如何能够降低树的高度呢？

假如key为8字节（bigint类型），每个节点有两个指针，每个指针为4个字节（B），一个节点占用的空间16个字节（8+4*2=16）

因为在MySQL的InnoDB存储引擎一次IO会读取的一页（默认一页16KB）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16kB/16B=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建100万条数据，树的高度只需要2层就可以（1000*1000=106），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。、

B树又叫多路平衡树，通常我们所说的m阶/m叉的B树，它必须满足以下条件：

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

B树的主要特点如下：

  • B树的节点中存储着多个元素，每个内节点有多个分叉。
  • 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，所有的节点中都储存数据。
  • 父节点当中的元素不会出现在子节点中。
  • 所有的叶子结点都位于同一层，叶节点具有相同的深度，且彼此之间没有指针连接。

B树的简图如下图所示：

B树简图

注：这里限于篇幅，不再详解B树的插入和删除操作，感兴趣可到MySQL学习(5)---B树和B+树详解查看。

应该说，B树已经接近数据库对底层数据结构的要求了，但仍有可以优化的地方。如：

B树不支持范围查询的快速查找，因为它采取了中序遍历的方式。以上图为例，我们要查询2~9之间的数据，在查找到2之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。

如果data域中存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

B+树的引入

B+树，作为B树的升级版，在B树基础上，MySQL在B树的基础上继续改造，使用B+树结构构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题。

  • B树：非叶子节点（包括根节点）和叶子节点都会存储数据。

  • B+树：只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

B+树的简图如下所示：

B+树简图

B+树的特征：

  • 有m个子节点的中间节点最多包含m个键（B树中是m-1个键），每个键不保存数据，只用来存储索引。
  • 所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接。 (而B树的叶子节点并没有包括全部需要查找的信息）

B+树的优势：

  • 由于叶子节点上存放了所有的数据，并且有指针相连，每个叶子节点在逻辑上是相连的，所以对于范围查找比较友好。
  • B+树的所有数据都在叶子节点上，所以B+树的查询效率稳定，一般都是查询3次。
  • B+树有利于磁盘的IO，因为他的层高基本不会因为数据扩大而增高。（三层树结构大概可以存放2000万数据量）

为什么说B+树比B树更适合数据库索引？

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

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

（2）B+树查询效率更加稳定

B树搜索在非叶子节点还是叶子节点结束都有可能，越靠近根节点，查找效率越快；而B+树无论查找的是什么数据，最终都需要从根节点一直走向叶节点，所有查找所经过的次数都是一样的，导致每一个数据的查询效率相当。

（3）B+树便于范围查询（最重要的原因，范围查找是数据库的常态）

B树在提高了IO性能的同时并没有解决元素遍历效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

补充：B树的范围查找采用中序遍历，而B+树采用在双向链表上遍历的方式。

索引分类

可以查看官网文档，看看MySQL存储引擎支持的索引类型。官网文档：https://dev.mysql.com/doc/refman/8.0/en/create-index.html

下图是MySQLinnoDB、MYISAM、MEMORY、RDB存储引擎对各种索引类型的支持情况。

1271254-20201013142145162-80980408

由于本文是基于MySQL的innoDB存储引擎，所以重点观察第一个表格。

由于本文是基于MySQL的InnoDB存储引擎，因此重点观察第一个表格，其他的表格可以自行观看。从表格中可以看出，InnoDB存储引擎索引只支持BTREE类型的索引，索引的类别有Primary Key，Unique，Key，FULLTEXT和SPATIAL。

按表列属性分类，有以下几种索引类型：

普通索引（Key）

作为MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。

唯一索引（Unique）

索引列的值必须有值且不能重复，但允许空值。语法如下：

# 随表一起建立唯一索引
# 唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错
create table user (
    uid int(10) unsigned auto_increment,
    name varchar(50) not null,
    gender int(2) not null,
    primary key(uid),
    KEY(name),
    unique(gender)
);

# 单独建立唯一索引
Ccreate unique index idx_gender on user(gender);

# 删除唯一索引
drop index idx_gender on user;

主键索引

主键索引是唯一的，通常以表的ID设置为主键索引，一个表只能有一个主键索引，这是它跟唯一索引的区别。语法如下：

# 随表一起建立主键索引
create table user (
    uid int(10) unsigned auto_increment,
    name varchar(50) not null,
    gender int(2) not null,
    primary key(uid) 
);

create table user2 (
    uid int(10) unsigned,
    name varchar(50) not null,
    gender int(2) not null,
    primary key(uid) 
);

# 单独建立主键索引
alter table user add primary key user(name);  
# 删除主键索引
alter table user drop primary key;  
# 修改主键索引
# 必须先删除掉（drop）原索引，再新建（add）索引

全文索引（FULLTEXT）

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

空间索引（SPATIAL）

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是geometry、point、linestring和polygon。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL。

按索引列的数量分类，有以下几种索引类型：

单列索引

单列索引即一个索引只包含单个列，一个表中可以有多个单列索引。语法如下：

# 随表一起建立单列索引。索引名同列名（name）
create table user (
    uid int(10) unsigned auto_increment,
    name varchar(50) not null,
    gender int(2) not null,
    primary key(uid),
    key(name)  
);

# 单独建立单列索引
create index idx_name on user(name); 
# 删除单列索引
drop index idx_name;

联合索引/复合索引

联合索引即一个索引中包含多个列，在数据库操作期间，联合索引所需要的开销更小（相对于相同的多个列建立单值索引）。语法如下：

# 随表一起建立联合索引
create table user (
    uid int(10) unsigned auto_increment,
    name varchar(50) not null,
    gender int(2) not null,
    primary key(uid),
    key(name),
    unique(gender),
    key(name,gender)
);

# 单独建立联合索引
create index idx_name_gender on user(name,gender); 
# 删除联合索引
drop index idx_name_gender on user;

按存储结构分类，有以下几种索引类型：

聚簇索引/聚集索引（Primary Key）

InnoDB引擎中的聚簇索引通常由主键或非空唯一索引来实现的，实际上是在叶子节点中同时存储了索引和整行数据，通过该索引查询可以直接获取查询数据行。

与MYISAM引擎不同，InnoDB的数据文件本身就是索引文件，表数据文件本身就是按照B+组织的一个索引结构，其叶子节点的键值就是表的主键，这种数据存储方式也被称为聚簇索引。由此可见，聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

聚簇索引的叶子节点都包含主键值、事务ID、用于事务MVCC的回滚指针以及所有的剩余列。

注：InnoDB存储引擎的表会存在主键（唯一且非空），如果建表的时候没有指定主键，则会使用第一个非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。

非聚簇索引/辅助索引/二级索引

非聚簇索引也叫辅助索引、二级索引等，叶子节点存储索引值和主键值。当需要访问数据时，根据主键从聚簇索引中查询。

与MYISAM引擎的辅助索引实现不同，InnoDB的辅助索引，其叶子节点存储的不是行指针而是主键值，得到主键值再要查询具体行数的话，要去聚簇索引中再查找一次，也叫回表。这样的策略优势是减少了当出现行移动或者数据页分裂时二级索引的维护工作。

索引的创建原则

  • 索引并非越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响insert、delete、update等语句的性能，因为在表中的数据更改的同时，索引也会进行调整和更新
  • 避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
  • 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
  • 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。
  • 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
  • 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。
  • 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。
  • 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，有一个CHAR(200)列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前10个或20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘 IO 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
  • 利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
  • 对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，所以InnoDB表尽量自己指定主键，当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键，提高查询的效率。另外，还需要注意，InnoDB 表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果

索引优化

前置知识：联合索引的最左匹配原则

在MySQL中，联合索引的最左匹配原则为：最左优先，以最左边的为起点任何连续的索引都能匹配上，同时遇到范围查询（>、<、between、like）就会停止匹配。

如果在(a,b,c)三个字段上建立联合索引，那么它能够加快a|(a,b)|(a,b,c)三组的查询速度。

注：这里限于篇幅，就对联合索引的最左匹配原则做一个简要的概括。感兴趣可到MySQL学习(7)---最左匹配原则详解查看。

覆盖索引

覆盖索引指select的数据列仅从索引树中就能获得，不必读取数据行，不需要回表进行二次查询，也就是说，查询列要被所使用的索引覆盖。

覆盖索引的优势：

  • 避免了辅助索引对聚簇索引（在InnoDB引擎中对应主键索引）的二次查询。
  • 索引条目通常远小于数据行大小，只需要读取索引，则MySQL会极大地减少数据访问量。
  • 辅助索引不包含整行记录的所有信息，故其大小要远小于聚簇索引，因此可以减少大量的IO操作。

注：覆盖索引技术最早是在InnoDB Plugin完成并实现。这意味着对于InnoDB版本小于1.0的，或者MySQL数据库版本为5.0或以下的，InnoDB存储引擎不支持覆盖索引特性。

Multi-Range Read（MRR）优化

Multi-Range Read优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极大的提升。Multi-Range Read优化可适用于range、ref、eq_ref类型的查询。

Multi-Range Read的好处：

  • MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
  • 减少缓冲池中页被替换的次数。
  • 批量处理对键值的查询操作

MRR的工作方式如下：

  • 将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
  • 将缓存中的键值根据RowID进行排序。
  • 根据RowID的排序顺序来访问实际的数据文件。

Index Condition PushDown（ICP）优化

和Muiti-Range Read一样，Index Condition PushDown同样是MySQL5.6开始支持的一种根据索引进行查询的优化方式。它能减少在使用辅助索引过滤where条件时的回表次数和减少MySQL Server层和引擎层的交互次数。在索引组织表中，使用二级索引进行回表的代价相比堆表中是要高一些的。相关文档地址：http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

之前的MySQL数据库版本不支持Index Condition Pusddown，当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录。在支持index Condition Pushdown后，MySQL数据库会在取出索引的同时，判断是否可以进行where条件的过滤，也就是将where的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库的整体性能，优化支持range、ref、eq_ref、ref_or_null类型的查询，选择Index Condition Pushdown优化时，可在执行计划的列Extra看到Using index condition提示。

注意事项

  • 索引最用于一个范围列，如果查询条件中有两个或以上的范围列则无法全用到索引。范围条件有：>、<、between、like等。
  • 建立索引的列不能包含null值。
  • 查询的时候，不要在索引列上做任何操作（如计算、函数、手动/自动类型转换等），会导致索引失效而转向全表扫描。（MySQL认为这些操作会改变索引顺序）
  • 使用auto_increment关键字的列必须有索引。（只要有索引就行）
  • 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

常见问题

主键索引就是聚簇索引吗？

答案是否定的。

注意：聚簇索引决定了数据库的物理存储结构，而主键只是确定了表格逻辑组织方式。这二者不可混为一谈。

对于InnoDB存储引擎而言，主键毫无疑问是一个聚簇索引。但是当一个表没有主键，或者没有一个索引，Innodb存储引擎会如何处理？有如下规则：

  • 如果一个主键被定义了，那么这个主键就是作为聚簇索引，有且仅有一个。
  • 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚簇索引。
  • 如果没有主键也没有唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。
  • 自增主键会把数据自动向后插入，避免了插入过程中的聚簇索引排序问题。聚簇索引的排序，必然会带来大范围的数据的物理移动，这里面带来的磁盘IO性能损耗是非常大的。 而如果聚集索引上的值可以改动的话，那么也会触发物理磁盘上的移动，于是就可能出现page分裂，表碎片横生。所以不应该修改聚簇索引。

为什么InnoDB只在主键索引树的叶子节点存储了具体数据？

为了节省存储空间，一个表中可能有多个索引，InnoDB都会给每个加了索引的字段生成索引树，如果每个字段的索引树多存储了具体数据，那么这个表的索引数据文件就变得非常巨大。（数据极度冗余）

为什么MySQL不推荐使用uuid作为主键？

  • 使用自增id的内部结构

自增的主键的值是顺序的，索引InnoDB把每一条记录都存储在上一条记录的后面。当达到页面的最大填充因子时候（InnoDB默认的最大填充因子是页的15/16，会留出1/16的空间留作以后的修改），这样做有几个好处：

（1）下一条流就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费。

（2）新插入的行一定会在原有的最大数据行下一行，MySQL定位和寻址很快，不会为计算新行的位置而作出额外的消耗。

（3）减少了页分裂和碎片的产生。

  • 使用uuid的索引内部结构

因为uuid相对顺序的自增id来说时毫无规律可言的，新行的值不一定要比之前的主键的值要大，所以InnoDb无法做到总是把新行插入到索引的最后，而是需要为新寻找新的合适的位置从而来分配新的空间。

这个过程需要做很多额外的操作，数据的毫无顺序会导致数据分布散乱，将会导致以下问题：

（1）写入的目标页很可能已经刷新到磁盘上并且从缓存中移除，或者还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO。

（2）因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，一次插入最少需要修改三个页以上。

（3）由于频繁的页分裂，页会变得稀疏并被不规则地填充，最终会导致数据有碎片。

结论：使用InnoDB引擎应该尽可能地按主键的自增顺序插入，并且尽可能地使用单调的增加的聚簇键的值来插入新行。

posted @ 2022-09-26 10:09  vgshots  阅读(110)  评论(0)  编辑  收藏  举报
Fork me on GitHub