--- title: MySQL description: 复习用 categories: [bagu] --- ## select流程 ### 连接 获取TCP连接，查询用户的权限，该权限保存在连接中，就算管理员改了用户权限，该连接的权限不会变。 空闲连接的最大空闲时长由`wait_time`控制，超过最大时长就自动断开。 最大连接数由`max_connections`控制，超过最大连接数就拒绝新的连接。 MySQL连接也分长连接和短连接： ```text 连接 mysql 服务（TCP 三次握手） 执行sql 断开 mysql 服务（TCP 四次挥手） // 长连接 连接 mysql 服务（TCP 三次握手） 执行sql 执行sql 执行sql .... 断开 mysql 服务（TCP 四次挥手） ``` 由于很多资源保存在内存中，并且在连接断开后才释放，长连接很多很可能MySQL程序占用内存过大导致被系统杀死并重启，对于长连接的解决方式是客户端主动调用`mysql_reset_connection`重置连接释放内存，这个过程不需要重连和重新做权限验证。 在实际开发中（以Java为例），会使用DBCP、C3P0或者Druid的连接池来管理连接，由连接池处理这种问题。 ### 解析SQL 词法分析+语法分析。 **这一步不会检查表/字段是否存在，只是单纯检查SQL语法** ### 执行SQL - 预处理：检查表/字段是否存在、将`select *`展开为所有字段 - 优化：制定执行计划，选择索引（使用`explain SQL语句`可以查看执行计划） - 执行：执行器根据执行计划，将索引传给引擎去查询一条符合的记录，得到一条记录后检查剩余的非索引条件是否满足，满足的话发送这条记录给客户端。不断重复上面这个过程。 ## redo log（引擎层） ![image-20241119151214046](https://cdn.jsdelivr.net/gh/NOS-AE/assets@main/img/image-20241119151214046.png) redo log具有crash safe的能力 redo log是物理日志，记录的是“在某个数据页上做了什么修改” redo log是循环写的，空间固定会用完。 redo log buffer是全局共用的，参数`innodb_flush_log_at_trx_commit=N`控制write和fsync，N=0的时候不write也不fsync，N=1的时候每个事务提交都write+fsync，N=2的时候只write ## bin log（服务层） binlog是归档日志，没有crash safe的能力 binlog是逻辑日志，比如“给 ID=2 这一行的 c 字段加 1" binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志 binlog+某个时间点的数据库全量备份，可以将数据库恢复到之前某个时间点，比如误删了数据库想要恢复： - 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库； - 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。 每个线程维护自己的binlog cache，参数`sync_binlog=N`控制fsync，0为不fsync，N为每N个事务fsync。为N的话，binlog最多会丢失N个binlog日志。 binlog的格式： - statement：记录sql语句，可能会发生主从不一致的情况，比如用到了time()、uuid动态生成等。 - row：记录每行的数据，能确保一致性 - mixed：自动判别sql语句在主从分别执行是否会出现不一致的情况，不会出现不一致则statement格式，否则用row格式 ## update流程（两阶段提交） 1. 执行器调用引擎**读取数据**并返回给执行器 2. 执行器**更新数据**并调用引擎写回数据页 3. 引擎将操作记录到 redo log ，此时 redo log 处于 **prepare 状态**，告知执行器可以提交事务 4. 执行器将操作记录到 binlog，并调用引擎**提交事务** 5. 引擎把刚刚写入的 redo log 改成**提交（commit）状态**，更新完成。 当数据库崩溃恢复的时候，一个完整的事务至少需要redo log的prepare状态+完整的binlog。 ## 两阶段提交为什么不能先写binlog 原因是可能会造成主从不一致：先写binlog的话，可能会在写redo log之前就同步到了从库并且宕机，主库恢复过来后删除了binlog，但此时从库已经执行了binlog，造成主从不一致。 ## 为什么binlog cache是线程私有的，redo log buffer是公共的 由于一个完整的事务至少需要redo log的prepare状态+完整的binlog，必须保证只有事务提交的时候才写磁盘。binlog cache设计成私有的，可以避免把其他未完成事务的binlog也写进磁盘。 ## 一张表是如何存储的 - 段：包含索引段（B+树非叶子节点）、数据段（B+树叶子节点）、回滚段（用于实现MVCC） - 区：存储多个页，为了让逻辑相邻的页在物理上也临近。**因此分配空间的时候，按区为单位分配，每个区的大小为 1MB** - 页：Innodb的IO基本单位是页 - 行 下面主要介绍「一行」除了我们用到的列数据，还包含什么数据，主要介绍compact结构，如下： img - 变长字段长度列表（当存在变长字段）：存储变长字段的实际长度，方便读取相应字段。 - NULL值列表（当存在值可以为NULL的字段）：设列数为n，NULL值列表大小为n bits（但需要8bits对齐），字段为NULL则对应位为1。 - 记录头信息：包含很多元数据，比如delete_mask标识数据是否被删除、next_record下一行的位置（记录之间是用链表组织的）、record_type - rowid：没有主键的时候用rowid作为主键，用于索引查询时回表 - trxid：属于哪个事务 - roll_ptr：记录上一个版本的指针 ## varcher(n)的n含义 首先MySQL规定大对象比如text、blob外，**其他所有列加起来大小不能超过65535字节**。varchar(n)中的n表示最大能存多少 **字符**。 而字符的大小与使用的字符集有关，即n最大值约等于65535除以单个字符大小（NULL值列表、变长字段长度列表也占空间） ## 行溢出 由于单行65535的限制，如果有不受限制的大对象text、blob，那么就会发生行溢出，溢出部分的数据放到「溢出页中」，并在行中增加一个指针指向溢出页中这部分数据的地址。 ## 为什么用B+树 **B+ vs B**： - B+树非叶子节点存储索引，索引占空间很小。而B树所有节点都存储数据，读底层的数据的时候还得读上层不需要的数据。因此一个节点能存更多索引，**一次IO相当于能查询更多的节点**。换种说法就是，B+树高更矮。（对于N个叶子节点的B+树，高度为`O(logdN)`，其中 d 为树的度） - B+树所有数据用双链表连接，**适合范围查询**，而B树只能中序遍历，需要读取更多的页而且更加复杂 - B+树插入删除的时候，**由于上层存储了冗余节点，节点发生的变化更少**，效率更高 - 注意对于大量的单个索引查询的场景，可以考虑 B 树，比如 nosql 的MongoDB **B+ vs 平衡树**： - 也是因为B+树更矮（`O(logdN)`），平衡树是二叉树，为`O(logN)` **B+ vs Hash**： - Hash适合等值查询，不适合范围查询 ## 索引的类型 - 主键索引：`PRIMARY KEY (index_column_1) USING BTREE` - 普通索引：`INDEX(index_column_1,index_column_2,...) ` - 唯一索引：`UNIQUE KEY(index_column_1,index_column_2,...) `，与普通索引的区别就是增加了唯一约束，**但是在更新数据的时候，由于不能直接写到change buffer，需要读页检查是否唯一，性能没有普通索引好** - 前缀索引：`INDEX(column_name(length))`，对字符串前几个字符建立索引，减少索引占用的存储空间，提升查询效率 ## 联合索引 多个列组合成一个索引。 使用联合索引时，存在**最左匹配原则**，如果不遵循「最左匹配原则」，联合索引会失效。 - `a > 1 and b = 2`，只有a用联合索引，第一条为a>1的记录 - `a >= 1 and b = 2`，a和b都用，第一条为a=1,b=2的记录 - `name like 'j%' and age = 22`，并且如果存在name='j'的记录，那么name和age都用，第一条为name=j,age=22的记录 综上，即使是范围查询，只要存在范围边界的记录，就不会停止匹配。 ## 索引下推 在联合索引中，查询条件`a > 1 and b = 2`的时候，由于只能用索引a，如果没有索引下推的话，只能一个个回表，然后检查b是否等于2。如果有索引下推的话，就直接在联合索引（二级索引）这里检查b是否等于2，不用回表。 Extra 为 `Using index condition`，那么说明使用了索引下推 因此索引下推是为了减少回表次数。 ## 联合索引避免排序 ```sql status = 1 order by create_time asc ``` 建立status和create_time的联合索引，status=1中的create_time已经有序 ## 索引优化的手段 - **前缀索引优化（降低树高减少IO）**：减小索引大小，增加一个索引页存储的索引值，降低树高提高查询效率。局限性：order by无法使用前缀索引、前缀索引无法覆盖索引 - **覆盖索引优化（避免回表）**：建立要查询字段的联合索引，这样直接在二级索引就能查到需要的字段，避免回表 - **主键索引递增（避免页分裂造成的内存碎片）**：新数据只需要存到最后一页的最后一条记录，存满了就新开一页，原来的记录都不用动。如果是键是非递增的，那么可能会存到某个页的中间位置，存满了就会造成页分裂。因此 - **索引not null**：null值索引会使得优化器在索引选择更加复杂，因为null会使得索引统计、值比较等更复杂，另外可以减少null值列表大小 - **防止索引失效**：常见索引失效的情况： - 左模糊匹配比如：'%xx', '%xx%' - where做计算、函数、类型转换等 - 不满足最左匹配 - where 索引 or 非索引 ## 执行计划类型（explain的type） - all：全表扫描 - index：全索引扫描/索引覆盖，即不用再去回表 - range：范围索引扫描：范围查找 - ref：非唯一索引等值查询 - eq_ref：唯一索引扫描，在join的时候用到，比如`t1.user_id=t2.user_id` - const：唯一索引等值查询常量 ## 执行计划额外信息（explain的extra） - using filesort：出现在orderby或group by，无法用索引排序进行操作的时候。 - using temporary：出现在order by或group by，排序结果要使用临时表存储（） - using index：覆盖索引 ## InnoDB的B+树 图片 ## InnoDB数据页（叶子节点） 首先每个页之间通过双链表连接（就是B+树的叶子节点用双链表连接）。用户数据存储在user records中，下面看一下user records里面的数据如何组织。 图片 下图是一页中用户数据的组织，每行之间用按主键顺序组成单链表。为了避免页内顺序查找的效率低，增加了一层「页目录」。**当查找页内某行数据的时候，先在页目录用二分找到对应的分组，然后在分组内顺序查找** 图片 ## 聚簇索引和非聚簇索引的区别 从包含的列来说，聚簇索引包含了所有的列数据，非聚簇索引只有索引列和主键列。 另外，聚簇索引还包含了事务 id、用于事务和 MVCC 的回滚指针等。 **综上，聚簇索引包含了所有列以及额外信息，占据空间更大。当要查询的数据在非聚簇索引都能查到的时候，就优先选择在非聚簇索引上查询**。（比如mysql优化count(*)/count(1)为在keylen最小的二级索引上查询） ## 如何优化count(*) - 使用explain获取rows列得到近似值 - 额外维护一个表来存储其他表的行数 innodb不为表维护一个count字段存储行数的原因是：由于支持事务，因此同一个时刻的多个count查询不一定相同。（个人认为更多是因为维护一个count只能实现全表count(*)效率提高，适用范围太小） ## 事务特性（ACID） - 原子性atomicity：通过undo log保证 - 一致性consistency（最终目的）：通过保证其他三个特性实现 - 隔离性isolation：通过mvcc或锁保证 - 持久性durability：通过redo log保证（crash safe） ## 事务隔离级别 - 读未提交：不需要额外手段实现，直接往数据库读写就行了 - 读提交：执行语句时生成read view - 可重复读（默认）：事务开启时生成read view - 串行化：加锁 图片 ## MVCC **read view中的四个字段：** ![img](https://cdn.jsdelivr.net/gh/NOS-AE/assets@main/img/readview%25E7%25BB%2593%25E6%259E%2584.drawio.png) - m_ids：启动还未提交的事务id列表 - min_trx_id：m_ids中的最小值 - max_trx_id：m_ids中的最大值+1 - creator_trx_id：当前事务id **聚簇索引记录中的两个隐藏列：** 图片 - trx_id：改动该行的事务id - roll_pointer：指向undo log中最新的版本（各版本之间组成单链表） **持有read view访问一条记录时：** - 如果trx_id < min_trx_id，版本可见 - 如果trx_id >= max_trx_id，版本不可见 - 如果m_ids包含trx_id，版本不可见，m_ids不包含trx_id则版本可见 其中「m_ids不包含trx_id」的情况出现于： 1. 事务A创建 2. 事务B创建，更新记录并提交事务 3. 事务C（当前事务）创建 undo log其实就是一系列回滚操作，当版本不可见时，就通过记录的roll_pointer指针往回一路执行回滚操作，找到第一个可见的版本。 img 为什么不使用长事务？由于这些事务随时可能访问数据库里面的任何数据，回滚日志无法删除，占据大量空间。（或者因为事务长期持有锁资源，也可能拖垮整个库） ## 幻读 一个事务中，前后两次相同的select得到的行数不一样。解决幻读的办法只有加锁，防止其他事务插入/修改记录 在可重复读的级别下，一般不会出现幻读： - 针对快照读（普通select），事务开启时生成read view，因此不会发生幻读 - 针对当前读（select..for update），直接读取的是最新的数据，因此此时会加锁（记录锁+间隙锁），避免后续产生幻读 **但是有特殊的情况会使得幻读发生，那就是快照读之后，本事务对“别的事务插入的行“发生了更新：** - 本事务select * from table - 其他事务插入一条id为3的新记录 - **本事务update了这条id为3的记录，该记录的trx_id变成了本事务的id，导致该记录此时对本事务可见** - 本事务再次select * from table，由于id=3的记录此时可见，因此结果多了这行数据，即发生了幻读 另一种类似的情况是，将该update语句变成select * from table for update，效果也是一样的，也会出现幻读。 综上，**可重复读要完全解决幻读的最好办法就是开启事务之后马上select ..for update加锁**，并且要注意加锁的范围，如果后续的查询不在加锁范围内，依然可能发生幻读。 ## 全局锁 ```mysql flush tables with read lock ``` **整个数据库处于只读状态**。 应用场景：全库逻辑备份。 避免全局锁影响正常业务：使用可重复读的隔离级别，在备份数据时开启事务 ## 表级锁 - 表锁：分为读锁和写锁 - 元数据锁：mysql自动加的锁，当curd时加读锁，变更表结构时加写锁，并且写锁优先级高于读锁（当写锁阻塞时，后续读锁也阻塞） - 意向锁：加读/写行锁前要加读/写意向锁，意向锁是为了在加表锁时快速检查有没有加行锁 - AUTO-INC锁：主键自增的锁 ## 行级锁 - 记录锁：单个索引 - 间隙锁：(上一个索引值，下一个索引值)。只存在于可重复读级别，目的是解决幻读。两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的 - next-key lock：（上一个索引值，当前记录索引值]。记录锁+间隙锁（前开后闭区间），即既能保护记录，又能防止新记录插入到间隙 ## mysql如何加行锁 ```mysql select * from performance_schema.data_locks\G; ``` **加锁的对象是索引**，当索引为主键则加在主键索引上。当索引为二级索引则同时加在二级索引和主键索引上，但此时主键索引只加记录锁 **加锁的基本单位是next-ley lock**。 **对扫描到的记录都会加上next-key lock**，但可能会退化成记录锁或间隙锁 其实不用管退化不退化，最重要只需要记住next-key lock对某个索引的范围是(上一个索引值，当前记录索引值]即可，要判断加了什么锁，只需要看查询语句的查询范围会涉及到哪些索引值，就能推出来会加什么锁了。 还有一些很细节的情况，假设当前数据为： img 查询age=25，那么加锁(22, 39)，这样看来如果要插入age=22或者age=39都是成功的，然而由于二级索引包含主键，因此这个锁的范围还要考虑主键，即实际范围为(「22,10」,「39,20」)，即插入的记录是「22, id>10」，「39, id<20」的话也会阻塞。 ## 查询没有走索引如何加锁 相当于全表扫描，会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。 ## 死锁处理 在数据库层面有两种方法打破「循环等待」： - **设置等待锁超时时间** - **开启主动死锁检测** ## 主从复制 **原理**：将主库的binlog（记录所有DDL和DML）同步到从库 **步骤**： 1. 从库连接主库 2. 从库创建IO线程向主库请求binlog 3. 主库创建一个binlog dump线程发送binlog 4. 从库IO线程将binlog写入relay log中 5. 从库的SQL执行relay log的内容 **分类**： - **异步复制**：主库生成binlog后直接提交事务，然后返回结果给客户端（dump线程异步发送binlog） 存在问题：主库生成binlog后宕机，导致dump线程发送的任务失败，主从不同步 - **半同步复制 after_commit**：主库生成binlog后直接提交事务，然后至少等待N个从库将binlog写入relay log后返回结果给客户端 存在问题：主库生成binlog后宕机，导致dump线程发送的任务失败，主从不同步（基本与异步复制是一样的问题） - **半同步复制 after_sync（无损复制）**：主库生成binlog后，至少等待N个从库将binlog写入relay log，然后再提交事务，最后返回结果给客户端。这个策略基本能解决由于主库宕机的主从一致性问题 ## 读写分离 **读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。一般情况下，我们都会选择一主多从，主库和从库之间会进行数据同步，这样的架构实现起来比较简单，并且也符合系统的写少读多的特点** 实现方式： - 代理：在应用和数据库之间增加一个代理层，将读写请求路由到对应的数据库 - 组件：推荐使用`sharding-jdbc`，直接引入 jar 包即可使用，非常方便。同时，也节省了很多运维的成本 ## 分库分表 为什么要分库分表： - 数据量庞大，单机存储不下 - 查询性能变得缓慢 - 单个数据库支持的连接数不够（也能用读写分离解决，不过有主从同步问题） - 备份时间长 分库 - 垂直分库：不同的表使用不同数据库 - 水平分库：将一张表分割到不同数据库中存储 分表 - 垂直分表：将一张列比较多的表，不常用的一些列抽出来作为扩展表 - 水平分表：将一张行比较多的表拆分为多张表 一般水平分表通常和水平分库同时出现。 ## 分片算法 - 哈希分片：不太适合经常需要范围查询的场景 - 范围分片：比如 将 `id` 为 `1~299999` 的记录分到第一个库， `300000~599999` 的分到第二个库。适合范围查找，不太适合随机读写的场景（数据未被分散，容易出现热点数据的问题） - mixed：比如哈希+范围融合 ## 分库分表的问题 - **不同数据库的数据无法join**。解决办法：在业务层多次查询组装数据。 - **分布式事务**：单个事务涉及多个数据库 - **分布式ID**：数据库的自增ID只能应用于单个数据库 - **聚合操作**：多个数据库数据group by、order by等，需要编写复杂业务代码，或者使用中间件 推荐方案： - `Apache ShardingSphere`是一款分布式的数据库生态系统， 可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强 - 类似 TiDB 这种分布式关系型数据库，不需要我们手动进行分库分表（数据库层面已经帮我们做了），也不需要解决手动分库分表引入的各种问题，直接一步到位 ## BufferPool缓存方案 BufferPool使用LRU作为缓存淘汰算法，并对其作一定改造，以解决： - 预读失效：加载一页时会将相邻的一页提前加载进来，如果后续不访问预读的页的话，那就白做了。因此**LRU分为old和young区域，预读页进入old区域，当真正访问的时候才进入young区域** - 缓存污染：比如进行了全表扫描但是最终只需要少量数据，很多数据只访问一次并进入了yound区域，但后续不在访问。因此需要**提高进入到 young 区域的门槛，即数据加载到old后，如果第一次访问和后续访问的时间大于某个间隔，才能进入young**，这样可以避免「假热点」数据污染缓存。 ## BufferPool写回时机 - redo log日志满了 - BufferPool 空间不足 - mysql空闲时 - mysql正常关闭 ## BufferPool双写 正在写回某个脏页到磁盘的时候，主机断电，那么该页就是不完整的。 **不完整的页无法用redo log恢复**：redo重做日志中记录的是对页的物理操作，而不是页面的全量记录。如果page本身就已经损坏了，则无法恢复。 **因此需要备份以恢复不完整的数据页**：回写数据页前，先写到双写缓存（磁盘文件，顺序写），然后再将脏页写回到对应的文件中。当出现损坏的页，就用双写缓存来恢复。 ## 数据库范式 - 1NF：单个列不能再拆分 - 2NF：所有列都完全依赖于主键，比如(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)不满足2NF，可以拆分为(课程名称) → (学分) 和 (学号) → (姓名, 年龄)。不满足2NF会出现 - 数据冗余：一个人选了多少门课，这个人的姓名年龄学号就重复多少次 - 更新异常：更新学分、姓名等要更新多行 - 插入异常：如果存在没人选的课，那么姓名和年龄都是null，很明显是不对的 - 3NF：所有列都直接依赖于主键，比如(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)，因为(学号) → (所在学院) → (学院地点, 学院电话)，其中(学院地点, 学院电话)不应该出现在这里，也是会造成数据冗余 ## SQL优化 - 覆盖索引 - 最左匹配 - select具体字段，不要select*：因为无用字段增加网络带宽资源消耗、无法使用覆盖索引、减少表结构变更带来的影响 - 避免在查询条件字段上作运算（包括数学计算、函数转换、类型转换） - 同一列进行 - 避免使用子查询而是join - join使用小表驱动大表 ## 避免使用子查询而是join的原因 子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。 ## join使用小表驱动大表 小驱动大：耗时=小表行数 * 查询大表的耗时（大表查询使用索引） 大驱动小：耗时=大表行数 * 查询小表的耗时（小表查询使用索引） ## innodb和myisam区别 1. **事务支持**：innodb支持，myisam不支持 2. **外键支持**：innodb支持外键，myisam不支持，只能通过业务代码来约束 3. **锁粒度**：innodb支持行级锁，myisam使用表级锁 4. **崩溃恢复**：innodb的redo log支持崩溃恢复，myisam不支持 5. 全文索引：innodb在mysql5.6后支持，myisam默认支持 6. 存储空间：innodb需要更多存储空间，因为需要支持事务、行级锁、外键等特性 7. 读取速度：innodb在行级锁和事务的加持下，在大并发更新频繁的场景下表现更好。单纯读操作使用myisam更快 8. 使用场景：innodb用于事务型业务、外键约束、高并发读写的场景。myisam适用于多读少写、数据一致性要求不高，比如数据分析、日志系统等 ## mysql线程模型 - 主线程：协调各种后台任务（比如刷脏页、合并插入缓冲、回滚、压缩表） - IO线程：写脏页线程、读页线程、管理插入缓冲区线程、redo log刷盘线程 - purge线程：清理undo log - page清洁线程：定义刷buffer pool的脏页