一、基础篇

1. MySQL组成

MySQL基础架构图.webp

大体分为Server层和引擎层

  1. Server层包括连接器，分析器，优化器，执行器等，涵盖MySQL大多数核心服务功能，以及所有内置函数(如日期，时间，数学和加密函数等)，所有跨存储引擎的功能都在这一层实现，比如存储过程，触发器，视图等。

  2. 存储引擎负责数据的存储和提取，其架构模式为插件式，支持InnoDB，MylSAM，Memory等多个存储引擎，最常用的是InnoDB，5.5.5版本为MySQL默认的存储引擎，可通过指定引擎的类型选择别的引擎，比如在create table中使用engine=memory，不同存储引擎共用一个Server层

    Server层各组件功能及步骤

  3. 连接器：与客户端建立连接(身份认证)，获取权限，维持和管理连接。

mysql -h $ip -P $port -u $user -p 密码，密码建议别写一块，易泄露，不建议这样连生产环境

  • 用户名密码不对 -> Access denied for user；
  • 认证通过，连接器会到权限表中查出所拥有的权限，之后，此连接的权限判断逻辑都将依赖此时读到的权限，意味着，一个用户连接成功后，即使管理员账号对此用户权限做了修改，也不会影响已有的连接，只有重新建立新的连接才会使用新的权限设置。

连接完成后，没有后续动作，则此连接会处于空闲状态，show processlist查看连接，Command一列为 sleep，默认空闲8小时连接器会自动将它断开，可由wait_timeout控制，断开连接后，客户端再发请求就会Lost connect to MySQL server…
长连接指连接成功后，如果客户端持续有请求，则一直使用同一个连接；
短连接指每次执行完很少的几次查询就断开连接，下次查询再信创建一个；
注意：连接过程比较复杂，尽量减少连接动作，尽量使用长连接，但全部使用长连接后，由于MySQL在执行过程中临时使用的内存是管理在连接对象中，这些资源在断开时才会释放，如果长连接积累下来，可能会因为内存占用太大，被强行杀掉(OOM)，现象就是MySQL异常重启，
解决办法：

  • 定期断开长连接，使用一段时间，或者程序中判断执行过一个占用内存大的查询后，断开连接，之后要查询再重连；
  • MySQL57及以上，可在每次执行一个比较大的操作后执行mysql_reset_connect重新初始化连接资源，此过程不需要重连和重新做权限验证，但是会将连接恢复到刚建立的状态。
    1. 查询缓存：建立连接->执行select->查询缓存，查看是否之前执行过此语句，如果在缓存里则直接返回结果，不在的话会继续后面的阶段，完成后执行结果会以key-value形式存入缓存(内存中)，其中key为查询语句，value为查询结果。

缺点：查询缓存效率高，但是命中太低，只要对一个表进行更新操作，此表上所有的查询缓存都会被清空，适用于静态表，即很少更新的表，可按需使用，可将query_cache_type设置为DEMAND禁止使用，在select语句后加SQL_CACHE即可使用，8.0版本将查询缓存删除了。

  1. 分析器：未命中缓存，就开始真正执行语句了，分析器进行词法分析(根据select，update等关键字来判断表名列名等)，检查是否符合语法，错误则You have an error in your SQL syntax…需要关注下语句后use near的内容，此内容为错误信息。
  2. 优化器：分析完之后进行优化，选择最优执行方案，比如在表里有多个索引时，决定用哪个索引，或者在一个语句有多表关联时，决定各个表的连接顺序，先查哪张表，选择最优解。
  3. 执行器：分析器知道你要做什么，优化器知道了该怎么做，那么就开始执行了，先判断你有没有操作权限，没有则权限错误，有则打开表继续执行，根据表的引擎定义，表使用这个引擎提供的接口(在工程实现上，如果命中缓存，会在查询缓存返回结果时做权限验证，查询也会在优化器之前调用precheck验证权限)

执行流程：调用InnoDB引擎接口，取表的第一行，进行条件判断，不是则跳过，继续往下扫描，是则将此行数据存在结果集中，直到取到这个表的最后一行，执行器会将所有满足条件的行组成的结果集返回给客户端。数据库的慢查询日志会有一个rows_examined的字段记录语句执行时扫描了多少行，此值是执行器每次调用引擎获取数据行的时候累加的，但有时执行器调用一次，在引擎内部扫描了多行，所以引擎扫描行数与rows_examined并不完全相同。

2. redo log和binlog

redo log(重做日志，InnoDB特有，物理日志)

如果每一次的更新操作都需要写入磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程的成本、查找成本都很高，所以出现redo log提升效率，类似于缓存，但有大小限制，达到限制或空闲的时候会将redo log里的数据更新到磁盘中，将数据在redo log里抹去，然后反复此操作，叫做WAL(Write-Ahead-Log)，先写日志再写磁盘。
流程：
一条记录更新 -> 写redo log并更新内存(InnoDB在空闲时将此操作更新到磁盘里，也可手动修改配置) -> redo log 写满，则先更新磁盘，再重置redo log
redo log图.webp
InnoDB的redo log大小固定，可配置，log file类似于环状(可设置为四个部分，每个部分1G)，分别有write pos和check point两个指针指向，二者之间为剩余可写入部分；write pos为写指针，边写边往后移动，check point检测指针，当前要擦除的部分，往后推移，擦除记录前会把记录更新到数据文件中，当写指针追上检测指针的时候，不再进行更新操作，需要先擦除一部分，check point往后移。
有了redo log，InnoDB就可以保证即使DB异常重启，之前提交的记录也不会丢失，即crash-safe能力

binlog(归档日志，server层日志，逻辑日志)

归档日志，只能用于归档，没有crash-safe能力。
为什么会有两个日志：
因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。
对比：

  1. redo log是InnoDB特有，binlog是MySQL中server层实现的，所有引擎均可用；
  2. redo log是物理日志，记录的是“在某个数据页上做了什么操作”，binlog是逻辑层日志，记录的是语句的原始逻辑，比如给ID=2的这一行的c字段加1；
  3. redo log循环写，空间固定会用完，binlog是追加写，即写到一定大小会切换下一个，不会覆盖。

    update时，执行器和InnoDB流程

  1. 执行器找引擎取ID=2这一行，ID为主键，引擎采用树搜索找到，如果ID=2这一行所在数据页在内存中，则直接返回给执行器，否则，从磁盘读入内存，再返回；
  2. 执行器拿到给的行数据，更新之后得到新的一行数据，再调引擎写入；
  3. 引擎将新的数据更新入内存(更新到内存中是指更新内存中这条数据所在的数据页中的数据)，同时将这个更新操作记录到redo log里，此时redo log处于prepare状态，然后告知执行器，随时可提交事务；
  4. 执行器生成此操作的binlog，并将binlog写入磁盘；
  5. 执行器调用引擎的提交事务接口，引擎把刚写入的redo log状态改为提交状态，更新完成。

流程图：
浅色框表示在InnoDBn内部执行，深色框表示在执行器中执行。
更新语句流程图.webp

两阶段提交

将redo log的写入拆为prepare和commit状态，是为了让两份日志之间的逻辑一致。
反证法：

  1. 先写redo log后写binlog

redo log写完，binlog未写完，MySQL异常重启，仍能把数据恢复过来，但是binlog没写完就crash了，此时binlog无此语句，之后备份日志时，存起来的binlog也就无此语句，如果需要用这个binlog恢复临时库，就会少了一次更新，与原库值不同；

  1. 先写binlog后写redo log

binlog写完之后crash，redo log还未写，crash之后这个事务无效，所以这一行的值不变，但是binlog已经记录此语句，如果用binlog恢复数据，就会多执行一条语句，则值与原库不同；
所以如果不同两阶段提交，那么数据库的状态就有可能和它的日志回复出来的库状态不一致。
扩容时，需要多搭建一些备库，增加系统的读能力，常见做法就是全量备份加上应用binlog实现，如果不一致的话就会导致主从数据不一致。
建议innoDB_flush_log_at_trx_commit设为1，表示每次事务的redo log都直接持久到硬盘里，保证重启不丢失数据；sync_binlog设为1，表示每次事务的binlog都持久化到磁盘里，保证重启不丢失。

3. 事务及隔离级别(InnoDB特有)

事务就是保证一组数据库操作，要么全部成功，要么全部失败，在引擎层实现
MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作，记录上的最新值通过回滚操作，都可以得到前一个状态的值。
假设一条记录的某个值为1，被顺序修改为2，3，4，操作完之后当前值为4，但在查询这条记录时，不同时刻启动的事务会有不同的read-view，对于视图A，要得到1，就必须将当前值依次回滚操作，即使有另一个事务正在将4改为5，这个事务跟ABC也不会冲突。

为什么不推荐长事务

回滚日志会在系统判断没有事务需要用到这些回滚日志的时候被删除，所以尽量不要使用长事务，否则，数据库里会保留它可能用到的回滚记录，导致占用大量内存，除此之外，长事务还会占用锁资源，也可能拖垮整个库。

MVCC多版本并发控制

同一条记录在系统中可能存在多个版本

事务启动方式

  1. 显式启动，begin/start transaction，配套的有commit，rollback，在执行语句时，事务才是真正的启动，同时一致性视图才创建；
  2. set autocommit=1，如果为0，则只执行select也开启了事务且不会自动提交；

  3. start transaction with consistent snapshot，事务立刻启动一致性视图也立刻创建。

    视图

    MySQL中有两个视图概念：

    • 一个是view，它是查询语句定义的虚拟表，在调用时主席那个查询语句并生成结果，语法为create view...，查询方法与表一样；
    • 另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC(Read committed 读提交)和RR(Repeatable Read，可重复读)隔离级别的实现。

没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

“快照”在MVCC中是如何工作的

InnoDB里每个事务有一个唯一的事务ID，transaction id，是在事务开始的时候想InnoDB事务系统申请的，按顺序严格递增，每行数据有多个版本，每次事务更新数据时，都会生成一个新的数据版本，并把transaction id值赋给这个数据版本的事务ID，记为row trx_id，同时，旧的数据版本也要保留，并在新的数据版本中，可以回滚拿到，即数据表中的一行记录，可能有多个版本(row)，每个版本有自己的row trx_id与事务ID一一对应。
InnoDB为每个事务构造了一个数组，用来保存到当前正在“活跃”的所有事务ID，“活跃”指启动但未提交，此组事务ID最小值为低水位，当前系统里已创建过的事务ID最大值加一为高水位，整个视图数组和高水位就组成了事务的一致性视图(read-view)。
一个数据版本，对于一个事务视图来说，除了自己更新总是可见外，有三种情况：

  • 版本未提交，不可见；
  • 版本已提交，但是是在视图创建后提交的，不可见；
  • 版本已提交，而且在视图创建前提交，可见。

    4. 索引

    类似于书的目录，提高查询效率，存在于存储引擎层

    索引常见模型

  1. hash表：把值放入数组里，用一个hash函数把key(待查找的键)换算成一个确定的位置，然后把value放入此位置；
  • hash冲突：多个key哈希之后出现值相同，可以再拉出一个链表，当添加字段hash值相同时，只需往后追加即可，速度很快，但由于是无序的，做区间查询很慢，所以只适用于等值查询。
  1. 有序数组：在等值查询和范围查询中性能可以，二分查找O(logN)，但如果新增数据就很麻烦，需要挪动元素，适用于静态存储引擎
  2. 二叉搜索树：左子树所有节点的值<父节点的值<右子树所有节点的值，查询O(logN)，需要保持这棵树为平衡二叉树
  3. N叉搜索树：保证从左到右连续递增
  • 索引不止存在于内存中，还要写到磁盘上，假如一棵树高20，一次查询可能需要访问20个数据块，为了让查询尽量少读磁盘，就必须查询时访问尽量少的数据块，所以要使用N叉树，N取决于数据块的大小；
  • 以InnoDB一个整数字段索引为例，N差不多为1200，这颗树高4的时候，可存1200的三次方个值，约7亿，树根的数据块总在内存中，一个10亿行的表上一个整数字段的索引，查找一个值最多访问3此磁盘；

  • 读写上性能高，适配磁盘的访问模式。

    InnoDB的索引模型

    索引组织表：在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表成为索引组织表，所以索引都存在于B+树中，每一个索引在InnoDB中都对应一个B+树。
    主键索引：主键索引的叶子节点存的是整行数据，在InnoDB中也称为聚簇索引(clustered index)。
    非主键索引：非主键索引的叶子节点存的是主键的值，在InnoDB中也称为二级索引(secondary index)。
    二者查询区别：主键索引查询方式，只需要搜ID这棵B+树，非主键索引则先搜索k索引树，找到对应的ID值，再到ID索引树搜索一次，也叫回表。所以应尽量使用主键查询，减少扫描索引书的次数。

    索引维护 — 维护索引的有序性

    如果需要插入新的行ID值700，则只需要在R5的记录后面插入一个新纪录即可，如果插入的是400，则需要挪动后面的数据，空出位置，如果R5所在数据页满了，根据B+树算法，此时需要申请一个新的数据页，然后挪动部分数据过去，称为页分裂，会影响性能及操作页的利用率，原本放在一个页的数据分为了两页，整体空间利用率降低约为50%；当相邻两个页由于删除数据，利用率很低之后，会将数据页做合并，即页分裂过程的逆过程。

    为什么使用自增主键做主键索引

    自增主键的插入数据模式，正符合递增插入的场景，每插入一条新纪录，都是追加操作，不涉及数据挪动和叶子节点分裂，而业务逻辑的字段做主键，则往往不容易保证有序插入，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

    适用于业务字段做主键的场景

  • 只有一个索引

  • 该索引必须为唯一索引，即k-v场景

    覆盖索引

    当查询的字段正好为主键索引时，例select ID from T where k between 3 and 5;而ID值已经在k索引树上了，可直接提供查询结果，不需要回表，可以说此查询里，索引k已覆盖了我们的查询需求，称为覆盖索引。
    好处：可减少树的搜素次数，显著提升查询性能，所以为常用的性能优化手段。

    最左前缀原则(联合索引)

    索引项是按照索引定义里面出现的字段顺序排序
    查找张三时，可快速定位到ID4，然后向后遍历得到所有结果，如果查所有名字第一个字是“张”的人，where name like '张%';也可用此索引，查到第一个ID3，然后往后遍历。
    索引的效果不仅为索引的全部定义，只要满足最左前缀，就可利用索引加速，这个最左前缀可以是联合索引的最左N个字段或者字符串索引的最左M个字符。

    如何安排索引顺序(联合索引)

  • 一般(a,b)联合后，无需再a上单独建立索引，所以第一原则是通过调整顺序可少维护一个索引；

  • 如果既有联合索引，又有基于a，b各自的查询，同时维护(a,b)，(b)两个索引即可，此时需要考虑空间，a字段比b字段大，那么创建一个(b,a)，(a)即可。

    索引下推(联合索引)

    可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，无需回表。即当联合索引为查询条件时，会先过滤掉不符合条件的，而条件为联合的字段条件，比如查询“张”开头的，年龄为20的人，会先查“张”开头的所有数据，再过滤掉不为20的人。

    5. 锁

    数据库锁设计的初衷是处理并发问题，作为多用户共享的资源，当出现并发访问时，数据库需合理地控制资源的访问规则，而锁就是用来实现这些访问规则的重要数据结构。

    全局锁

    对整个数据库实例加锁，整个数据库处于只读状态，MySQL提供了加全局锁的方法Flush tables with read lcok(FTWRL)，之后其他线程的数据更新语句(数据的增删改DML)、数据定义语句(建表，修改表结构DDL)和更新类事务的提交语句将被阻塞

    • 使用场景：全库逻辑备份，即把整库每个表都select出来存成文本
    • 缺点：如果在主库上备份，那么备份期间都不能执行更新，业务基本停摆；在从库上备份，那么备份期间不能执行主库同步过来的binlog，会出现主从延迟

如果不加锁，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的，比如买课，一个用户，购买一门课，加入先扣余额再往已购课程里加上一门课，如果先备份余额表，然后用户购买，然后备份课程表，相当于余额没扣，但用户课程里多了一门课。
官方自带的逻辑备份工具是mysqldump，当mysqldump使用参数-single-transaction时，导数据之前就会启动一个事物，来确保拿到一致性视图，由于MVCC支持，这个过程中数据是可正常更新的，但前提引擎要支持一致性读这个隔离级别(支持事务)，比如MyISAM不支持事务，总会取到最新的数据，就破坏了一致性，此时就需要FTWRL了。
set global readonly=true 也可以让全库处于只读状态，但仍建议使用FTWRL，原因如下：

  • 有些系统中，readonly的值会被用来做其他逻辑，比如判断一个库是主库还是备库，修改global变量影响面太大；

  • 异常处理机制上有差别。在客户端异常断开时，FTWRL会自动释放全局锁，整个库可正常更新，而readonly会一直保持readonly状态，会导致整个库长时间处于不可写状态，风险较高。

    表级锁

    表级锁分为表锁和元数据锁

    表锁(meta data lock, MDL)

    语法lock tables 表名 read/write，与FTWRL类似，可用unlock tables主动释放，也可在客户端断开连接时自动释放，lock tables会限定本线程接下来的操作对象，比如在线程A中执行lock tables t1 read, t2 write；则其他线程写t1，读写t2都会被阻塞，同时A线程只能读t1写t2，连写t1都不行，自然也访问不了其他表，如未出现更细粒度的锁时，表锁是最常用的处理并发方式，而InnoDB这类支持行锁的引擎一般不使用lock tables控制并发。

    MDL

    保证读写的正确性，访问表时自动加上
    5.5版本引入MDL，对一个表进行增删改查时，加MDL读锁，对表结构变更操作加MDL写锁，读锁之间不互斥，因此可以有多个线程同时对一张表增删改查，读写锁之间、写锁之间互斥，即用来保证变更表结构操作的安全性，因此读写锁，写锁之间要等另一方执行完才能执行，否则处于阻塞状态。
    写锁时完全不可读写，如果某个表上查询语句频繁，而且客户端有重试机制，也就是超时后会再起一个session再请求，那这个库的线程很快就会爆满。
    事务中的MDL锁，在语句执行开始时申请，在整个事务提交后再释放。
    如何安全地给小表加字段：

  • 解决长事务，事务不提交就会一直占用MDL锁，如果要做DDL变更，刚好有长事务在执行，需要先暂时kill掉；

  • 但如果变更的是一个热点表，请求很频繁又不得不加字段，kill掉后新的请求又来了，可以在alter table语句中设定等待时间，如果在设定的时间内拿到MDL写锁最好，拿不到就先放弃加字段，之后再重试此过程。

    InnoDB的行锁

    MySQL的行锁只存在于引擎层，由各引擎自己实现，MyISAM不支持，只能用表锁。
    行锁就是针对数据表中行记录的锁，比如事务A更新了一行，而这个事务B也要更新同一行，必须等到事务A的操作完成之后再更新

    两阶段锁

    再InnoDB事务中，行锁时再执行操作时加上的，到事务结束时才释放。

如果一个事务中需要锁多个行，要把最可能造成锁冲突，最可能影响并发度的锁尽量往后放，可很大程度上减少事务之间的锁等待，提高并发度，但并不一定解决问题，比如cpu消耗接近100%，但整个数据库每秒就执行不到100个事务，为什么呢？

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时 ，就会陷入无限等待状态，即死锁。
两种策略：

  • 一是直接进入等待，直到超时，超时时间可通过innodb_lock_wait_timeout设置，默认50s，超时退出；
  • 二是发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其它事务继续执行，innodb_deadlock_detect设为on，表示开启，默认on。

第一种要么时间太长，要么会误伤正常的事务锁等待，所以一般采用第二种，但也有额外的负担，每当一个事务被锁时就要看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁，每个信赖的被堵住的线程都要判断，时间复杂度O(n)，此期间是消耗大量cpu资源的，所以会出现cpu利用率很高，但每秒缺执行不了几个事务。
热点行更新性能解决：

  • 如果保证整个业务不死锁，可临时把死锁检测关掉，但可能会出现大量的超时；
  • 控制并发，比如同一行最多是10个线程在更新，那么死锁检测成本就很低，建议数据库服务端做；
  • 将一行数据改成逻辑上的多行，减少锁冲突，但要注意一部分记录为0时，代码要特殊处理。