MySql数据存储原理及索引介绍

1. Mysql的简单架构

基本架构图，分为Server层和引擎层

1.1 Server层

Server层主要是负责提供对客户端的接口连接, 解析SQL语句，搭载缓存

1.1.1 连接器

执行mysql -h $ip -P $port -u $user -p命令就是通过连接器建立客户端与服务器的连接

• show processlist命令可以查看数据库的连接状态

一般连接数据库是通过长连接进行控制，但是如果长期没有断开连接，则会导致一直不断的积累内存，最后内存越占越大，所以在使用长连接时需要注意下面两点

• 定义断开或者程序执行一个占用内存过大的查询时，最好查询结束断开连接重新连
• 如果是使用MYSQL5.7以上的版本可以执行mysql_reset_connection命令初始化连接资源

为了避免频繁创建和销毁线程，当一个客户端断开链接后并不会直接的将线程销毁，而是缓存起来，当有新的客户端连接进来就会分配在缓存中的线程

1.1.2 查询缓存

MySql在每次执行查询语句时会将这条语句以及查询结果以key-value的形式保存在内存中。所以在每次执行查询语句时都会先在内存中查询是否曾经查询过，如果有则直接返回数据结果，以提高查询效率。

但是查询缓存在表数据进行更新后会将查询缓存都清空，所以在更新操作较为频繁的数据库不建议使用

需要注意的是，MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。

1.1.3 分析器

如果没有命中缓存，那么便会开始执行语句，执行语句会经过词法分析，语法分析进行检测你的语句是否输入正确，并将语句转化为类似表的结果。比如:select被识别为1代表查询。仅仅只是举例，内部并不是这样。

1.1.4 优化器

经过了分析器，MySQL就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的join：

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

• 既可以先从表t1里面取出c=10的记录的ID值，再根据ID值关联到表t2，再判断t2里面d的值是否等于20。
• 也可以先从表t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

1.1.5 执行器

MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示。

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

1. 调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

你会在数据库的慢查询日志中看到一个rows_examined的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

1.2 引擎层

主要是提供对外的读写接口，不同的引擎有不同的效果，但是都共用同一个Server层，默认的引擎为InnerDB

2. 数据存储

2.1 数据存储方式

Mysql中存储数据中以页的形式进行存储，页是MySQL中磁盘和内存交互的基本单位，也是MySQL是管理存储空间的基本单位。

一个页一般是16KB，当记录中的数据太多，当前页放不下的时候，会把多余的数据存储到其他页中，这种现象称为行溢出

页中存储的是一行一行的数据，mysql定义了四种行格式:

• COMPACT
• Redundant
• Dynamic
• Compressed

2.1.1 数据页中存储的信息

• File Header，表示页的一些通用信息，占固定的38字节。
• Page Header，表示数据页专有的一些信息，占固定的56个字节。
• Infimum + Supremum，两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的26个字节。
• User Records：真实存储我们插入的记录的部分，大小不固定。
• Free Space：页中尚未使用的部分，大小不确定。
• Page Directory：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插入的记录越多，这个部分占用的空间越多。
• File Trailer：用于检验页是否完整的部分，占用固定的8个字节。

2.1.2 页管理方式

• 页中的数据管理

  每个记录的头信息中都有一个next_record属性，从而使页中的所有记录串联成一个单链表。

• 页与页之间的管理

  每个数据页的File Header部分都有上一个和下一个页的编号，所以所有的数据页会组成一个双链表。

2.1.3 页中数据的查找方式

InnoDB会把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个槽，存放在Page Directory中，所以在一个页中根据主键查找记录是非常快的，分为两步：

• 通过二分法确定该记录所在的槽。
• 通过记录的next_record属性遍历该槽所在的组中的各个记录.(一个组内数据数为1~8条)

2.1.4 数据完整性保证

为保证从内存中同步到磁盘的页的完整性，在页的首部和尾部都会存储页中数据的校验和和页面最后修改时对应的LSN值，如果首部和尾部的校验和和LSN值校验不成功的话，就说明同步过程出现了问题。

2.2 索引

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们刚刚唠叨过的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的，如果一个表有一亿条记录，使用这种方式去查找记录那要等到猴年马月才能等到查找结果。所以需要索引来提高查询效率

2.2.1 InnoDB索引方案

因为数据是通过页的形式存储，并通过链表的形式关联起来，但是这样查询效率低下，所以引入了目录页的概念, 通过目录页的方式对数据页进行管理。

• 目录页和数据页的区别
  1. 目录页中存储的只有每页数据中最小的主键与页面编号,
  2. 目录页的File Header中的record_type标记的类型不一样
  3. 在目录页中记录最小的主键页的File Header中的min_rec_mask记录为1，代表当前非叶子结点层中包含最小主键的目录页
• 有了目录页后如何查询数据？
  1. 通过二分快速查找对应的目录页
  2. 通过目录页存储的数据继续查找到数据所在真实的页，再通过主键在页中查找数据
• 目录页太多如何管理？
  1. 给目录页继续添加目录页，形成树的结构，最终存储的格式为B+树
  2. 一般树的层级不会超过4层，因为4层能够存储数据就已经非常庞大了;

2.2.2 聚簇索引

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

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建，InnoDB存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是，在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

2.2.3 二级索引

二级索引即为用户根据业务创建的索引列，如果包含多个索引列则被称为联合索引

• 特点
  • 叶子结点的数据只包含了用户自定义的索引列和主键
  • 页内的记录是按照索引列的大小顺序排成一个单向链表。
  • 各个存放用户记录的页也是根据页中记录的索引列大小顺序排成一个双向链表。
  • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的索引列大小顺序排成一个双向链表
  • 目录项记录中不再是主键+页号的搭配，而变成了索引列+主键+页号的搭配。
• 注意事项
  • 联合索引中，排序顺序按照创建索引时设置的顺序，如: c1、c2。那就是先根据c1列进行排序，如果相同再根据c2进行排序
  • 如果利用索引列查询数据列中时包含了非索引列, 此时需要进行一次回表操作，通过主键到聚簇索引中查询非索引列，所以在使用索引时需要尽可能的减少回表操作，降低查询时间。

2.2.4 MyISAM索引

​ 与InnoDB不同的地方在于树中的叶子结点并不是存储了所有的数据，而是存储的数据文件中的地址，所以即使通过主键查询，依然需要通过数据地址去获取真实数据

MyISAM的行格式有定长记录格式（Static）、变长记录格式（Dynamic）、压缩记录格式（Compressed）。上边用到的index_demo表采用定长记录格式，也就是一条记录占用存储空间的大小是固定的，这样就可以轻松算出某条记录在数据文件中的地址偏移量。但是变长记录格式就不行了，MyISAM会直接在索引叶子节点处存储该条记录在数据文件中的地址偏移量。通过这个可以看出，MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里边儿找记录，虽然说也不慢，但还是比不上直接用地址去访问。这里只是希望大家理解InnoDB中的索引即数据，数据即索引，而MyISAM中却是索引是索引、数据是数据。

2.3 索引的使用

2.3.1 索引的代价

• 空间上的代价

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

• 时间上的代价

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

2.3.2 索引生效的条件

• 全值匹配

  如果搜索条件中的列和索引列一致的话，或者说时用=去做搜索，这两种情况就称为全值匹配。

  -- 其中name为索引
  ELECT * FROM user WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';
  

• 最左匹配

  在联合索引的情况下，搜索无需包含联合索引中的所有列，只包含最左边的列即可

  -- name 、birthday、phone_number 为联合索引 
  SELECT * FROM user WHERE name = 'Ashburn';
  

  或者包含多个左边的列

  -- name 、birthday、phone_number 为联合索引 
  SELECT * FROM user WHERE name = 'Ashburn' AND birthday = '1990-09-27';
  

• 左前缀匹配

  在模糊匹配的时候同样适用最左匹配原则，但是不能是前缀模糊搜索

  -- 模糊搜索后缀是可以使用到索引的
  SELECT * FROM user WHERE name LIKE 'As%';
  -- 包含了前缀的模糊搜索是无法使用索引，必须全表扫描
  SELECT * FROM user WHERE name LIKE '%As%';
  

  原因：建立索引的时候是根据字符串的前缀进行排序，而不是根据中间的部分进行排序，所以当使用前缀模糊搜索时无法根据索引进行搜索

• 范围匹配

  范围匹配依然使用最左匹配原则，但只有最左边的索引列会生效，其余索引列并不会生效

  SELECT * FROM user WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';
  
  -- 以下就birthday可以使用联合索引，但是phone_number列依然无法使用，原因参考下面
  SELECT * FROM user WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';
  
  -- 以下可以完全的使用联合索引
  SELECT * FROM user WHERE name = 'Ashburn' AND birthday = '1980-01-01' AND phone_number > '15100000000';
  

  • 原因

    在联合索引中只有当name也就是最左边的索引列相同时才能够用到下一个索引列的值进行排序，当name值都不相等时，无法根据birthday进行排序

2.4 InnoDB表空间

2.4.1 区

为了更好的管理页，所以Mysql提出了区的概念，即64页为一个区，又因为一页的大小的16k所以一个区的大小是1M

• 区的分类
  • 空闲的区：现在还没有用到这个区中的任何页面。
  • 有剩余空间的碎片区：表示碎片区中还有可用的页面。
  • 没有剩余空间的碎片区：表示碎片区中的所有页面都被使用，没有空闲页面。
  • 附属于某个段的区。每一个索引都可以分为叶子节点段和非叶子节点段，除此之外InnoDB还会另外定义一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位。