回到顶部 暗色模式

MySQL笔记

1. 架构

        大体来说，MySQL可以分为Server层和存储引擎层两部分。Server层包括连接器、查询缓存、分析器、优化器、执行器等，以及所有的内置函数，所有跨存储引擎功能都在这一层实现。而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个引擎。不同的存储引擎共用一个Server层，也就是从连接器到执行器的部分。

        一个InnoDB表包含两个部分，即表结构定义和数据。表结构定义的占用空间很小，在MySQL 8.0版本以前，表结构是存在以 $.frm$ 为后缀的文件里，而MySQL 8.0版本已经允许把表结构定义放在系统数据表中了。表数据既可放在共享表空间里，也可以是单独的文件，从MySQL 5.6.6开始，默认是存储在一个以 $.ibd$ 为后缀的文件中的。对于放在共享表空间中的表，即使通过 $DROP$ 命令删除后，空间也不会回收。在删除的过程中，InnoDB会查找聚簇索引，将对应的记录标记为删除，而不是真正删除，目的是为以后插入新数据时的复用。记录的复用与数据页的复用不同，记录的复用只允许对应范围的新记录复用，而如果删除整个数据页后，数据页的复用可以允许复用到任何位置。如果相邻的两个数据页的利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另一个数据页就被标记为可复用。
        重建表即将原来表的记录读取出来，一行一行地插入到新表中。原来的表在删除和插入时会产生很多空洞，数据利用率不高，而重建表就可以解决这个问题。MySQL 5.6引入了 $Online\ \ DDL$ ，会先建立一个临时文件，保存原来表中的聚簇索引。在建立临时文件的过程中，在之后对原表的操作会被记录到日志文件中，并在临时文件建立完成后重新应用到临时文件中，从而允许重建表过程中的读写。
        在不同的引擎中，$COUNT(*)$ 有不同的实现方式，MyISAM把表的行数存在了磁盘上，可以直接返回；InnoDB会把数据一行一行地读出来然后计数。由于MVCC的存在，在某个时间段表有多少行是不确定的，因此InnoDB并不能简单地将行数存起来。MySQL对此的优化策略是，如果表存在多个索引，那么会选择较小的一颗索引树进行扫描。
        MySQL会给每个线程分配一块内存用于排序，称为 $sort_-buffer$。在 $sort_-buffer$ 里面的字段，会每次从数据库中取出数据并存到里面。当取出所有数据后再进行快速排序，如果内存空间不足，会使用外部排序。如果单行长度超过排序的最大长度，那么会将要排序的字段与ID关联，在排序完成后再通过ID回查。如果现有索引覆盖了需要排序的字段，那么会直接使用索引。对于使用聚集函数的排序，MySQL可能会使用临时表。临时表默认是在内存中的，如果超过了内存临时表的大小，就会转成磁盘临时表。
        内存表指的是使用Memory引擎的表，这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。临时表可以使用各种引擎类型，如果是使用InnoDB或者MyISAM引擎的临时表，写数据的时候是写到磁盘上的。一个临时表只能被创建它的 $session$ 访问，可以与普通表同名。

2. 日志

        更新语句的执行过程其实与查询语句一样，但是会涉及日志模块。
        redo log记录将要修改的记录，使用了WAL ( $Write-Ahead\ \ Logging$ ) 技术，即先写日志，再写磁盘。当有一条记录需要更新时，InnoDB会先把记录写到redo log中，并更新内存，之后在适当的时候，将这个操作记录更新到磁盘中，往往是在磁盘比较空闲的时候。
        InnoDB的redo log是固定大小的，写到末尾时就循环回到开头重新写。$write\ \ pos$ 是当前记录的位置，随着数据写入后移。$check\ \ point$ 是当前要擦除的位置，在擦除之前要把记录更新到数据文件。$write\ \ pos$ 和 $check\ \ point$ 之间的部分可以记录新的操作。如果之间没有空白部分，需要等待执行记录。
        InnoDB通过redo log，可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，称为 $crash-safe$ 。
        binlog与redo log的区别有：

  1. redo log是InnoDB特有的，binlog是MySQL的Server层实现的；
  2. redo log是物理日志，记录对某个数据页的修改；binlog是逻辑日志，记录语句的原始逻辑；
  3. redo log是循环写入的，binlog是可以追加写入的，即当文件达到一定空间后切换到下一个文件；
  4. binlog的应用场景是主从复制和数据恢复；redo log记录对数据页的修改，InnoDB使用LSN ( $Log\ \ Sequence\ \ Number$ ) 进行标记，如果数据页中的LSN落后于redo log的LSN，就通过redo log恢复数据。

        每当引擎将新数据更新到内存中时，就会把更新操作记录到redo log中，此时redo log处于 $prepare$ 状态。当执行器执行完成后，会生成操作的binlog。当事务提交之后，引擎会把刚写入的redo log改成 $commit$ 状态，完成更新。
        写redo log的时候，可以通过 $innodb_-flush_-log_-at_-trx_-commit$ 参数设置：

        InnoDB后台有一个线程，每隔 $1$ 秒，就会把 $redo\ \ log\ \ buffer$ 中的日志调用 $write$ 写到 $page\ \ cache$ 中，然后调用 $fsync$ 持久化到磁盘。事务执行中间过程的redo log也是直接写在 $redo\ \ log\ \ buffer$ 中的，也就是说，一个没有提交的事务也是可能已经持久化到磁盘的。
        binlog会记录所有的逻辑操作，如果要恢复到以前的某个状态，可以通过取出binlog并重放操作。binlog的写入逻辑是先写到 $binlog\ \ cache$ ，在事务提交的时候清空 $binlog\ \ cache$ 并写到binlog文件中。事务的binlog不能拆开，要保证一次性写入。每个线程都有自己的 $binlog\ \ cache$ ，但是共用同一份binlog。通过参数 $sync_-binlog$ 控制写磁盘方式：

        redo log和binlog是两个独立的逻辑，如果不使用两阶段提交，要么就是写完redo log再写binlog，或者反过来，但这会带来问题：

        binlog存在三种格式：$statement$ 、$row$ 和 $mixed$ 。$statement$ 格式会记录操作的语句，$row$ 格式会记录操作的行，$mixed$ 格式则由MySQL自己判断是使用 $statement$ 还是 $row$ 。
        在更新方面，MySQL引入 $change\ \ buffer$ 的概念。当需要更新一个数据页时，如果数据页在内存中就会直接更新，如果不在内存中，在不影响一致性的前提下，InnoDB会将更新操作缓存在 $change\ \ buffer$ 中，在下次查询访问改数据页时进行更新。$change\ \ buffer$ 是可持久化的数据，既在内存中有拷贝，也会被写到磁盘上。将 $change\ \ buffer$ 中的操作应用到原数据页的操作称为 $merge$ ，除了访问数据页外，系统也会有后台线程定期进行 $merge$ ，在数据库正常关闭的过程中，也会执行 $merge$ 。
        $change\ \ buffer$ 和redo log的关系在于，$change\ \ buffer$ 中所做的更改会被写入到redo log当中，也就是redo log的记录先于 $change\ \ buffer$ 写入。redo log的目的是减少随机写磁盘的I/O消耗，即转换成顺序写；$change\ \ buffer$ 的目的是节省随机读磁盘的I/O消耗，如果数据在内存中，那么被更改后的数据可以直接从内存中获得。
        当内存数据页与磁盘数据页内容不一致时，内存页就被称为脏页。在内存页被写入磁盘后，数据就一致了，这时内存页就是干净页。在MySQL查询的过程中，有时候会抖动一下，就是在刷脏页 ( $flush$ ) 的过程。引发 $flush$ 的情况有：redo log写满、系统内存不足需要淘汰脏页、系统空闲自动更新、MySQL正常关闭。一般情况下，$flush$ 都是由于系统内存不足导致的。InnoDB使用缓冲池 ( $buffer\ \ pool$ ) 管理内存，使用策略是尽量使用内存，因此对于一个长时间执行的数据库来说，未被使用的页面很少。当要读入的数据页不在内存时，就必须到缓冲池中申请数据页，即淘汰页面。InnoDB会控制一个脏页比例，以避免一次要淘汰过多的脏页。在默认情况下，如果MySQL在刷一个脏页的时候发现其相邻的下一个页面也是脏页，那么就会顺带刷掉，这种连坐机制可以很好的利用顺序I/O，减少随机I/O ( 在MySQL 8.0中默认不开启 ) 。
        InnoDB的内存管理使用的是LRU算法，用链表实现。因为数据库经常会有全表读或者大范围扫描的需求，如果直接采用LRU，当我们对一些使用不太频繁的大表进行全表扫描，一段时间之后，$buffer\ \ pool$ 的命中率会明显下降。实际上，InnoDB对LRU算法做了改进，按照 $5:3$ 的比例把整个LRU链表分成了 $young$ 区域和 $old$ 区域，即靠近链表头部的 $\large\frac{5}{8}$ 是 $young$ 区域，靠近链表尾部的 $\large\frac{3}{8}$ 是 $old$ 区域。每次访问一个不存在于当前链表的数据页，依然淘汰链表尾部的数据页，新插入的数据页会放在 $old$ 区域的头部，并且在 $1$ 秒内对这个数据页的访问不会移动该数据页 ( 可以通过 $innodb_-old_-blocks_-time$ 控制 )。如果超过了 $1$ 秒，就会把它移到整个链表的头部。

3. 事务

        当数据库上有多个事务同时执行的时候，就可能出现脏读 ( $dirty\ \ read$ )、不可重复读 ( $non-repeatable\ \ read$ )、幻读 ( $phantom\ \ read$ ) 的问题。

        在实现上，数据库会创建一个视图，访问的时候以视图的逻辑结果为准。在可重复读的隔离级别下，这个视图在事务启动时创建，并且在整个事务的过程中一直使用。在提交读的隔离级别下，视图会在每个SQL语句开始执行时创建。
        在MySQL中，每条记录在更新时都会记录一条回滚操作，即undo log，记录上的最新值通过回滚操作都可以得到前一个状态的值。在回滚段中的日志分为 $insert\ \ undo\ \ log$ 和 $update\ \ undo\ \ log$ ，前者是 $INSERT$ 时产生的，在事务提交后就可以丢弃；后者是 $DELETE$ 和 $UPDATE$ 时产生的，不仅在事务回滚时需要，一致性读也需要，只有当数据库所使用的视图中不存在比回滚日志更早的读视图时才会被删除，因此长事务的存在会导致日志长时间存在，占用大量存储空间。
        InnoDB里面每个事务都有一个唯一的事务ID ( $trasaction\ \ id$ )，在事务开始时向InnoDB事务系统申请，按照申请顺序严格递增。每行数据都会对应一个事务版本，每次事务更新数据时都会把事务ID赋给这个数据作为版本，记为 $row\ \ trx_-id$ 。同时，旧的数据版本会被保留。在undo log中记录的每条数据都会有一个指向上一个版本数据的指针，每当需要获取之前某个版本的数据时，就会通过最新版本进行访问。
        InnoDB存储引擎在数据库每行数据的后面添加了三个字段：

        在视图的实现上，InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在活跃的所有事务ID。数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加 $1$ 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图 ( $read-view$ )。对于当前事务的启动瞬间来说，一个数据版本 $row\ \ trx_-id$ ，有以下几种可能：

        此外，对于更新语句会有特殊情况。更新数据都是先读后写的，读只能读到当前值，称为当前读 ( $current\ \ read$ )。在更新的过程中会对要更新的数据进行加锁，因此如果存在多个事务对同一个数据进行更新，那么需要先获取锁。而如果对 $select$ 语句进行加锁的话，也是当前读。
        幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的，因此幻读在提交读下才会出现。为了解决幻读问题，InnoDB引入了间隙锁 ( $Gap\ \ Lock$ )。间隙锁和行锁合称 $next-key\ \ lock$，每个 $next-key\ \ lock$ 是前开后闭区间。对同个区间的多个间隙锁不会冲突，这会导致一个区间可能会被多个session加锁，从而造成死锁。

4. 索引

        三种常见的索引：哈希表、有序数组和搜索树。
        哈希表是一种以键值对方式存储数据的结构，只适用于等值查询，当出现哈希碰撞时采用拉链法解决。有序数组采用递增顺序存储索引数据，但是只适用于静态存储引擎，因为在数组中插入和删除数据的代价很大。
        InnoDB采用B+树作为索引。在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式称为索引组织表。主键索引的叶子节点存储整行数据，也被称为聚簇索引 ( $clustered\ \ index$ )。非主键索引的叶子结点存储主键值，也被称为二级索引 ( $secondary\ \ index$ )。也就是说，基于非主键索引的查询需要多扫描一次索引树。
        为了维护索引的有序性，在插入和删除的时候索引可能需要分裂或者合并。对于自增主键，由于可以保证上一条数据的主键值小于下一条数据，因此不会挪动其他记录。而有业务逻辑的字段做主键，往往不容易保证有序插入，成本相对较高。
        最左前缀原则可以用于在索引中定位记录。但是对于不满足最左前缀原则的条件，MySQL 5.6引入了索引下推优化 ( $index\ \ condition\ \ pushdown$ )，可以在索引遍历的过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
        唯一索引保证了每条数据在表中只会出现一次，主键默认是唯一的。与普通索引相比，唯一索引在查询方面的性能相差几乎没有，因为InnoDB以页为单位读取数据，而大部分情况下普通索引相比于唯一索引仅仅多做了一次查询而已。对于唯一索引来说，所有的更新操作都要先判断是否违反唯一性约束，如果数据页不在内存中，会要求将数据页读入内存，而这时无法使用 $change\ \ buffer$ ；相比之下，普通索引可以用到 $change\ \ buffer$ 。如果要更新的页面存在于内存中，那么唯一索引和普通索引的更新时间几乎没有区别；但是如果不在内存中，唯一索引会要求将数据页读入内存中，这时唯一索引比起普通索引来讲性能就会差很多。
        MySQL在执行语句之前无法精确的知道满足这个条件的记录有多少条，只能根据统计信息来估算记录数，这个统计信息就是区分度。一个索引上不同的值越多，这个索引的区分度就越好。一个索引上不同值的个数称为基数 ( $cardinality$ )。MySQL通过采样统计获取索引的基数，默认选择 $N$ 个数据页，统计页面上不同值得到平均值，然后乘以这个索引的页面值，从而得到基数。当变更的数据行数超过 $1/M$ 的时候，就会自动触发一次索引统计。对于持久化的索引统计，$N$ 默认是 $20$ ，$M$ 是 $10$ ；对于非持久化的统计，$N$ 默认是 $8$ ，$M$ 是 $16$ 。由于 $N$ 比较小，因此很容易不准确。由于不确定性的存在，所以MySQL优化器有时候会选择更差的索引进行搜索。
        对于字符串数据，可以使用前缀索引减少查询次数，但是会损失一些区分度，而且无法使用覆盖索引。对于一些长字符串，前缀索引的效率较差，可以考虑使用倒序索引，或者建立一个哈希字段，再对哈希字段进行索引。

5. 锁

        根据加锁范围，MySQL的锁大致可以分为全局锁、表级锁和行锁。
        全局锁就是对整个数据库进行加锁，在加锁后整个数据库就处于只读状态。全局锁的典型使用场景是做全库逻辑备份，也就是把整库每个表都 $select$ 出来存成文本。对于支持可重复读的引擎，通过事务可以完成备份。而对于不支持可重复读的引擎比如MyISAM，就需要通过全局锁来进行备份。全局锁会在客户端断开时自动释放。
        MySQL里面表级锁有两种：一种是表锁，一种是元数据锁 ( $meta\ \ data\ \ lock$, $MDL$ )。与全局锁一样，表锁会在客户端连接断开时自动释放。对于不支持更细粒度的引擎，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，则一般不实用。与表锁相比，MDL不需要显式使用，在访问一个表的时候会被自动加上，用于保证读写的正确性。当要对一个表做增删改查操作时，会加MDL读锁；当要对一个表做结构变更操作时，会加MDL写锁。读锁和写锁、写锁和写锁之间是互斥的，因此不能同时对一个表进行结构变更。
        在InnoDB事务中，行锁是在需要的时候才加上，在事务结束的时候释放。如果事务需要多个锁，要把最可能造成锁冲突、最可能影响并发度的锁放在后面。出现死锁的时候，InnoDB提供了两种策略，第一种是等待超时，默认超时时间是 $50s$ ，第二种是进行死锁检测。每一个新来的进入阻塞状态的线程在发现行上有锁后都要进行死锁检测，如果不会产生死锁才会执行。

6. 分布式

6.1 CAP理论

        CAP理论是指在分布式系统中，$C$ ( $Consistency$ )、$A$ ( $Availability$ )、$P$ ( $Partition\ \ Tolerance$ ) 这三个特征不能同时满足。在分布式系统中，现在的网络基础设施无法做到始终保持稳定，网络分区难以避免，牺牲 $P$ 相当于放弃使用分布式系统，因此在分布式系统中不能牺牲 $P$ 。对于涉及钱的交易，数据的一致性非常重要，因此保 $CP$ 弃 $A$ 是最佳选择。对于其他场景，大多数情况下的做法是选择 $AP$ 牺牲 $C$ ，因为很多情况下不需要太强的一致性，只要满足最终一致性即可。

6.2 分布式数据存储

        哈希是一种非常常用的数据分布式方法，其核心思想是确定一个哈希函数，通过计算得到对应的存储节点。哈希算法的一个优点是，只要哈希函数设置得当，可以很好地保证数据均匀性，缺点是稳定性较差。如果需要新增节点，那么原有节点中的数据就需要重新计算，即大规模数据迁移，降低稳定性。所以，哈希适用于同类型节点且节点数量比较固定的场景。
        一致性哈希是指将存储节点和数据都映射到一个首尾相连的哈希环上，数据先通过哈希函数计算其在哈希环中的位置，存储节点可以根据IP地址再进行哈希，然后数据通过顺时针方向寻找的方式，来确定自己所属的存储节点。一致性哈希是对哈希的改进，在数据存储时采用哈希方式确定存储位置的基础上，又增加了一层哈希，也就是在数据存储前，对存储节点预先进行了哈希。这种改进可以很好地解决哈希方法存在的稳定性问题，当节点加入或退出时，仅影响该节点在哈希环上顺时针相邻的后继节点。比如前一个节点发生故障需要移除时，需要把该节点数据移到后一个节点，避免了大规模数据迁移。所以，一致性哈希方法比较适合同类型节点、节点规模会发生变化的场景。
        带有限负载的一致性哈希方法的核心原理是给每个存储节点设置了一个存储上限值来控制存储节点添加或移除造成的数据不均匀。当数据按照一致性哈希算法找到相应的存储节点时，要先判断该存储节点是否达到了存储上限，如果达到上限，则继续寻找存储节点。
        其实，哈希、一致性哈希、带有限负载的一致性哈希，都没有考虑节点异构性的问题。如果存储节点的性能好坏不一，数据分布方案还按照这些方法的话，还是没做到数据的均匀分布。带虚拟节点的一致性哈希方法，核心思想是根据每个节点的性能，为每个节点划分不同数量的虚拟节点，并将这些虚拟节点映射到哈希环中，然后再按照一致性哈希算法进行数据映射。带虚拟节点的一致性哈希方法比较适合异构节点，节点规模会发生变化的场景。

6.3 分布式数据复制

        从库与主库之间维持一个长连接，主库内部有一个线程，专门用于服务从库的长连接。完整的同步过程为：

  1. 在从库上通过 $change\ \ master$ 命令，设置主库的IP、端口、用户名、密码以及binlog的起始位置 ( 文件名和偏移量 )；
  2. 在从库上执行 $start\ \ slave$ 命令，从库会启动一个 $io_-thread$ 和多个 $sql_-thread$ ，前者负责与主库建立连接；
  3. 主库校验完用户名和密码后，根据位置读取binlog并发送给从库；
  4. 从库将binlog写到relay log中；
  5. $sql_-thread$ 读取relay log，解析命令并执行。

        对于分布式存储系统中的数据复制技术来讲，也需要在一致性和可用性之间作出一些权衡。
        同步复制技术是指，当用户请求更新数据时，主数据库必须要同步到备数据库之后才可以给用户返回，即如果主数据库没有同步到备数据库，用户的更新操作会一直阻塞。这种方式保证了 $CP$ ，牺牲了 $A$ 。由于性能不佳，影响用户体验，同步复制技术经常用于分布式数据库主备场景或对数据一致性有严格要求的场景，比如金融、交易之类的场景。
        异步复制技术是指当用户请求更新数据时，主数据库处理完请求后可以直接给用户响应，而不必等待备数据库完成同步，即备数据库会异步进行数据的同步，用户的更新操作不会因为备数据库未完成数据同步而导致阻塞。显然，这种方式保证了AP ，牺牲了 $C$ 。异步复制技术大多应用在对用户请求响应时延要求很高的场景。
        同步复制技术会满足数据的强一致性，但会牺牲可用性；异步复制技术会满足高可用，但会在一定程度上牺牲数据的一致性。半同步复制技术则介于两者之间。半同步复制技术是，用户发出写请求后，主数据库会执行写操作，并给备数据库发送同步请求，但主数据库不需要等待所有备数据库回复数据同步成功便可以响应用户，也就是说主数据库可以等待一部分备数据库同步完成后响应用户写操作执行成功。半同步复制技术通常有两种方式：

  1. 当主数据库收到多个备数据库中的某一个回复数据同步成功后，便可以给用户响应写操作完成；
  2. 另一种是，主数据库等待超过一半节点 ( 包括主数据库 ) 回复数据更新后，再给用户响应写操作成功。

        MySQL集群在一主多备的场景下，也支持半同步复制模式，一般采用的是第一种半同步复制模式。普通的半同步是在主库事务提交后再等待从库同步，而增强型半同步会在主库事务提交前等待从库同步，避免了主从数据不一致的问题。

MySQL笔记

回到顶部