MySQL易忘点

一、基础知识

1.1 小tips

1.1.1 事务开启时机

在 MySQL 有两种开启事务的命令，分别是： 第一种：begin/start transaction 命令；

事务的执行过程如下，以 begin 或者 start transaction 开始，然后执行一系列操作，最后要执行 commit 操作。注意：begin 命令并不代表事务的开始，事务开始于 begin 命令之后的第一条语句执行的时候。例如下面示例中，select * from xxx 才是事务的开始，

begin;

select * from xxx;

commit; -- 或者 rollback;

第二种：start transaction with consistent snapshot 命令，执行后就会马上启动事务。

1.1.2 深分页

offset超过建议值10000, 存在深度分页问题，丢弃数据需要回表，建议用覆盖索引+子查询避免回表代价，提高查询速度;

1.2 锁

间隙锁（Gap Locks按区间）：保证某个间隙内的数据，在锁定期间不会发生任何变化。 开区间，在索引记录之间的间隙加锁（3，4）。

临键锁（Next-key Locks 按区间）：=间隙锁+行锁，即左开右闭，（-∞，1]，(3,4]。InnoDB的默认隔离级别RR下，select for update（走索引的情况下）、select *.. in share mode时加临键锁，可防止幻读。

表级锁（Table-level lock 按粒度）：给整张表加锁。 InnoDB下select for update不走索引的情况时；MyISAM下select前给涉及到的所有表加读锁；执行更新操作前自动加写锁。

开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

页级锁（按粒度）：介于行锁、表锁之间，应用于BDB存储引擎。

行锁（Record Locks 按粒度）：仅锁上符合条件的某些行。InnoDB通过给索引上的索引项加锁来实现；

开销大、加锁慢、会出现死锁；锁的粒度小，发生锁冲突的概率低，处理并发的能力强。

InnoDB实现了两种标准的行级锁：

——共享锁（S Lock）：允许事务读一行数据。即允许多个线程同时获取一个锁。

e.g. 若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，T释放A上的读锁前，其他事务不能加X锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。比如CountDownLatch

——排他锁（X Lock、写锁）：允许事务删除或更新一行数据；即一个锁在某一时刻只能被一个线程占用。

e.g. 事务T对数据加上X锁，可对其进行读、写；其他事务不能加任何锁，直至T释放；这保证了其他事务在T释放A上的锁之前不能再读取和修改A。比如ReentrantLock。

ReentrantReadWriteLock同时包括共享锁 和排他锁

意向锁：

乐观锁：

悲观锁：

对数据被修改时持悲观态度（认为数据在被修改的时候一定会存在并发问题），因此在整个数据处理过程中将数据锁定。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在应用层中实现了加锁机制，也无法保证外部系统不会修改数据）。

悲观锁的应用：select * from tableB for update 加锁机制，在查询数据时对所选的数据进行锁定，避免其他事务对其进行修改，直到当前事务提交或回滚。保证数据的一致性和并发控制。

update tableA set name='test' where age=5 的加锁范围？

1. MySQL锁是加在索引记录上面的。
2. 如果是非唯一性索引，不论表中是否存在该记录，除了会对该记录所在范围加锁，还会向右遍历到不满足条件的范围进行加锁。
3. 如果是唯一索引，如果表中存在该记录，只对该行记录加锁。如果表中不存在该记录，除了会对该记录所在范围加锁，还会向右遍历到不满足条件的范围进行加锁。

参考：chttps://segmentfault.com/a/1190000042027893

1.3 日志文件

在InnoDB存储引擎中，事务日志通过重做日志文件（redo log）和日志缓冲（InnoDB log buffer）实现。当一个事务开始时，会记录事务的一个LSN（Log Sequence Number）；当事务执行时，会往InnoDB的日志缓冲中插入事务日志；当事务提交时，必须先将日志缓冲写入磁盘，换言之，就是先写日志再写数据，这种方式称作预写日志WAL（write ahead logging）。

WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间由后台线程将buffer pool里的数据写到磁盘上。

1.3.1 redo log

     redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。 在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

• 事务提交时，从buffer写入磁盘（可设置）；
• redo log占buffer一半以上容量时，触发写磁盘；
• 后台线程每1s写一次；

1.3.2.undo log

redo记录了事务的行为，可以方便的通过其进行“重做”。但事务有时还需要撤销，这是就需要undo。事务对数据库进行修改时，会产生redo和undo，当有错误或rollback时，可以根据undo log将数据恢复到修改前的样子；undo存放在数据库内部的特殊段中（segment），undo段位于共享表空间内。

undo并非对数据的物理恢复，而是逻辑恢复，例如插入了10万条数据，使表空间增加，undo后表空间并不会收缩；对于每条insert，undo是做相应的delete，对于update则是做了相反的update。

假设有A、B两个数据，值分别为1、2，开启事务分别对其进行修改A → 3，B → 4，在提交，过程如下：

• 事务开始

• 记录A=3到undo log

• 修改A=3

• 记录A=1到redo log

• 记录B=4到undo log

• 修改B=4

• 记录B=2到redo log

• 将redo log写入磁盘

• 事务提交

1.3.3 binlog

mysql的基本架构如下，binlog属于Server层的日志文件，二进制存储，记录数据库所有修改操作。

binlog作用：

• 数据恢复（回放binlog到指定时间点）；
• 主从复制（从库读取主库binlog，做相同的操作，达到数据一致的效果）
• 点对点复制（类似于主从复制，可相互复制）
• 数据备份（可增量，可全量）
• 审计（记录了所有操作）

binlog类型：
在MySQL中，Binlog（二进制日志）有不同的类型，主要包括Statement、Row和Mixed模式。这些模式定义了MySQL在记录二进制日志时采用的策略，以确定如何记录对数据库的更改。

1. Statement模式

在Statement模式下，MySQL将每个SQL语句作为一个事件记录到Binlog中。具体来说，每个更新操作（如INSERT、UPDATE、DELETE等）都以SQL语句的形式被记录。

优势：
• 易读性高： Binlog中记录的是SQL语句，便于人类阅读和理解。
• 节省空间： 因为记录的是SQL语句，所以通常比其他模式占用更少的存储空间。
适用场景：
• 基于SQL语句的复制： 当使用基于SQL语句的复制技术，或者要求Binlog具有高可读性时，可以选择Statement模式。
注意事项：
• 可能引发非确定性问题： 由于某些SQL语句的执行结果可能会受到环境和状态的影响，因此在一些特定场景下可能会引发非确定性问题。

2. Row模式

在Row模式下，MySQL将每个被修改的行的内容作为一个事件记录到Binlog中。不再记录SQL语句，而是记录数据行的变更情况。

优势：
• 更精确： 记录了实际被修改的行，不受SQL语句的语法或环境影响，更为精确。
• 避免非确定性问题： 由于记录了行的具体变更情况，避免了某些非确定性问题。
适用场景：
• 要求更高精度的数据复制： 当要求更高的数据复制精度，或者在一些需要避免非确定性问题的场景中，可以选择Row模式。
注意事项：
• 占用更多存储空间： 由于记录了每个被修改的行，所以通常比Statement模式占用更多的存储空间。

3. Mixed模式
Mixed模式是Statement模式和Row模式的结合，MySQL会根据具体的SQL语句来选择使用Statement模式或Row模式。大多数情况下，MySQL会选择Statement模式，但对于某些特殊的情况，会使用Row模式。

优势：
• 灵活性： 根据具体情况动态选择合适的模式，兼顾了Statement和Row模式的优势。
适用场景：
• 在大多数情况下使用Statement模式，但对于一些特殊情况使用Row模式： Mixed模式在大多数情况下保持了Statement模式的高效性，但在需要更高精度的情况下可以动态切换到Row模式。
注意事项：
• 可能引发非确定性问题： 在Statement模式下可能存在的非确定性问题，在Mixed模式下仍然可能存在。

选择合适的Binlog模式：
     • 考虑复制技术和需求： 如果使用基于SQL语句的复制技术，Statement模式可能更合适。如果要求更高的数据精度，Row模式可能更适用。
     • 考虑存储空间： 如果对存储空间比较敏感，可以选择Statement模式。如果数据精度是更为关键的因素，可以选择Row模式。
     • 使用Mixed模式： 如果希望在大多数情况下保持高效性，但对于某些特殊情况需要更高精度，可以选择Mixed模式。

1.3.4 redo log vs binlog

• redo log 是 InnoDB 引擎特有的日志，
  而 Server 层也有自己的日志，称为 binlog（归档日志），针对所有引擎。
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；
  binlog 是逻辑日志。
• redo log 是循环写的，空间固定会用完；
  binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
• redo log记录变更后的值，无论事务是否提交，事务进行中不断写入
  binlog记录仅在事务提交后记录一次

两阶段提交：

redo log 和binlog都写成功，才算事务成功；任意一个失败，都会导致主从不一致。两阶段提交将redo log写磁盘分为两步，中间穿插binlog写磁盘，两个log记录同一个xId。以binlog是否写成功作为事务提交成功与否的标志。

参考：mysql三大日志文件 

MySQL 日志：undo log、redo log、binlog 有什么用？ | 小林coding

1.4 B+树

B+树优点：

• 非叶子节点不存数据，只是索引，可以一次加载更多索引数据；
• 所有数据都在叶子节点——主索引是聚簇索引，每个叶子节点包含一行所有数据；二级索引/非主索引，叶子节点存放id，
• 叶子节点间通过指针链接，行成有序链表，方便范围查询；
• IO次数稳定

计算树高度：

Innodb引擎下，一个page是16K，假设主键是bigint占8字节，指针占6个字节，那么一个page能容纳的（主键+指针）数=16K/(8+6)≈1170个。注意：不用关心几叉树；

如果每行数据1K，那么一个page能容纳行数=16K/1K=16；

一个指针对应一个树子节点（一个page），如果第二层是数据，数据总数最大值=1170*16=18720行数据；

如果第三层是数据，最大数据量=1170*1170*16=2190,2400，可见三层B+树就能容纳2千多万条数据。

二、实战遇到的问题

2.1 数据空洞

背景：巡检系统每日产生3万+条数据，总量有一千多万，严重影响SQL查找性能。

长期方案：优化代码、业务逻辑；

代码优化完成前的短期方案：对数据定时归档。
每日归档 6个月前/7天前 （按不同类型）的数据。归档完成的数据，从原表里删除。大量的删除操作产生数据空洞。

        使用delete删除数据时，对应空间并没有释放掉，仅把B+树上数据记录擦除，标记为『可复用』，便于插入数据时复用该部分空间；这些空间即是『数据空洞』。

优点：表空间结构复用；

缺点：浪费空间

优化措施：optimize table 表名。底层逻辑：

1、OPTIMIZE TABLE使用online DDL机制，以此来降低并发DML操作场景下的停顿时间。

2、OPTIMIZE TABLE操作会在prepare和commit两个阶段对表加锁。

3、在prepare阶段，会对数据表的元数据进行更新，并创建临时表。

4、在commit阶段，数据表的元数据更新会进行提交。

5、online DDL机制不支持InnoDB引擎的FULLTEXT索引，如果使用FULLTEXT索引，那么将会变为表数据拷贝的方式进行重建。

MySQL :: MySQL 8.0 Reference Manual :: 15.7.3.4 OPTIMIZE TABLE Statement

    

 2.2 慢SQL优化

背景：巡检系统每日产生3万+条数据，总量有一千多万，严重影响SQL查找性能。归档历史数据，减少数据量，将数据量从700万+减少到100+， 但优化效果不明显

调研各种方案后，决定接入ES。

• 创建集群

• 新建索引
  【基本信息】中必须要填写索引名称，索引名称规范：由英文字母和数字以及特殊字符("_","-","."三种符号)组成，且首字符必须由英文字母开头。

• 索引-索引定义-完善Schema
  添加字段

  对于数据是:A,B,C,D 。然后term：A查询的类型，ES上配置为keyword，然后写入的时候，自己split一下，转为数组，用数组写入。

  比如表里字符串模糊查询的字段pis_operator，用逗号分割的mis字符串。

• 申请生产者和消费组
  在接入D_T_S时需要提前申请好用于同步binlog的kafka topic和consumer，其中生产者topic选业务appkey ，此处巡检appkey

• 3.3 D_T_S配置数据订阅
  数据同步VS数据订阅：如果不需要对表字段处理，源字段和目标字段一模一样，就可以用数据同步，无需写代码；如果目标字段需要经过加工，就使用数据订阅，监听binlog，通过代码同步数据。

• 编写代码，读/写ES
  定时任务 -- 存量数据导入ES
  MQ监听 -- 监听巡检数据表变更binlog的MQ消息，处理新增数据；
  增加灰度开关 -- 控制查询逻辑 ，查询ES失败后降级为查数据库
  数据比对工具 -- 比对ES数据和DB数据差异

2.3 数据库迁移

我司目前使用的关系型数据库有三种架构，MySQL单库主从存储、MySQL分库分表存储和Blade存储。因为业务发展，会出现数据在这三种存储之间相互迁移的场景：比如扩容、缩容、从MySQL换到Blade等等。为了实现不停服的平滑迁移，zebra为此实现了统一数据源来支持这些数据迁移场景。除此之外，统一数据源还（即将）支持跨数据源事务、动态数据源等等场景

迁移流程分为以下几步：

a.迁移梳理：业务梳理涉及迁移表的业务服务代码、迁移表的上下游订阅、消费任务。（比如D_T_S订阅任务、B_C_P任务、实时数仓、离线数仓任务等）。主要思路：表都迁走了，这个表关联的相关任务是不是也切到目标？

b.目标创建：业务需要提前在R_D_S平台创建目标集群和库表结构，并与源表保持结构一致。

  代码修改jdbcRef配置，改为迁移库链接， 由数据库组件进行源库、目标库的流量切换

c.任务配置：平台配置迁移任务，并开启。

d.代码改造：业务进行代码改造，接入统一数据源，并上线（切换前上线即可）。

e.增量同步：全量迁移结束后，自动触发D_T_S增量服务，开始增量回溯同步。

f.数据校验：D_T_S同步任务延迟追到10s以内后，平台手动开启校验任务（建议可以校验多次）。

g.访问切换：校验成功且数据一致后，进行访问切换（包括读灰度和写切换）。
  切换前，读写源库；

  切读：调整比例，0% -> 100%；观察无异常，切换D_T_S订阅任务&离线同步任务mysql2hive； 测试-操作城市规划页面，查询/创建/删除规划

  切写：一次性切换，不可灰度；切换时读锁锁表，此时不可写，会报错；24h，

h.业务观察：在切换完成后，业务观察一天左右，业务无异常，则到平台停止迁移任务，回收D_T_S资源。

流量迁移流程

1. 全量数据同步
2. 增量实时数据同步
3. 读流量灰度目标库 0~100%
4. 读流量全量迁移目标库
   
5. 写流量全量迁移目标库 一次性

Tips：

可通过show create table tbName查看表结构

迁移数据库时，表数量很多，一个个复制DDL费时费力，有相关SQL可用：

--设置长度
SET SESSION group_concat_max_len = 1000000;

--查出数据库下全部表名，组合起来放入新表createTableStmt

SELECT GROUP_CONCAT(CONCAT('SHOW CREATE TABLE ', TABLE_NAME, ';') SEPARATOR ' ') into @createTableStmt

FROM information_schema.tables

WHERE table_schema = 'mall_smart';

--预提交

PREPARE stmt FROM _test_createTableStmts;

--执行语句

EXECUTE stmt;

但我司的RDS上不支持1、3、4句，可以通过RDS工具导出指定表结构。

也可通过show create table tbName查看表结构，但表多时不方便：

如何将结果组合到一起？

切写后，会对原数据库锁表24h， 使用以下命令查看锁着的表：
    SHOW OPEN TABLES WHERE In_use > 0;

5. 遇到的问题

5.1 修改float类型字段的值，执行保存，再次查询数据无变化

       该字段定义是 float(10,6)，此类型并不能精准保存数据，数据库会自动进行格式化或四舍五入； 如果仅仅修改了最末尾小数，可能精度上会产生变化。

业务中，经纬度使用float类型，出现过两次线上问题。

①问题：页面修改经度 116.35199改到了116.351994，但DB里保存的是116.351997。更新了第六位小数点导致运距变化，运输会重新向美团地图获取运输距离，所以即使很小的变化，都会影响到后续的计费。
   背景：原DB是保留5位小数；后产品逻辑修改，保留6位小数，页面强校验。页面操作时新增了一位小数。
   原因：float类型不保证精度，执行后的结果就是116.351997。

②问题：下游监听binlog，从消息体里获取的经纬度信息和我们库里的信息不一致
   背景：一个物理门店在DB中对应多条数据，有不同经纬度（即：对DB来说是3个门店），下游按经纬度严格匹配 才导致一点点变化（0.3米）就会计算错门店。
            原保留5位小数，没出现过精度问题；后产品逻辑修改，保留6位小数，出现问题。
   原因：页面修改了经纬度最后两位，保存DB成功。但由于float精度问题，会导致DB数据和产生的binlog不一致。下游通过监听binlog发送的消息将经纬度保存在自己的业务表中，后面再做某些业务逻辑时，发现自己存的经纬度，和调用poi接口获取的经纬度不一致，阻塞业务
   影响：导致下游计算两个门店是不是一个物理位置时 判断错了，会对运输排线业务【通过经纬度计算导航时间和距离】造成影响（计算结果不够准确），计算运费时费用有误差。

`latitude` float(10,6) NOT NULL DEFAULT '0.000000' COMMENT '纬度',
`longitude` float(10,6) NOT NULL DEFAULT '0.000000' COMMENT '经度',

   复盘
        业务方的问题：① 匹配逻辑有问题。假如上游修改数据，从123.12345-> 123.123450 (即增加一位小数)，但binlog认为数据没变，没发送MQ变更消息，下游还是旧数据123.12345，上游是123.123450。此时业务方就认为不是一个位置。
         ②应该以接口调用结果为准，而不是从MQ消息里获取信息。更稳妥点做法是监听MQ消息，从消息体获取ID，根据ID调用接口查数据。

        自身问题：作为基础数据提供方，应保证数据的正确性。设计DB时，经纬度这种影响较大、强要求完全一致的数据，使用decimal(10,6)类型；或使用numeric(10,2) —— 其实是保存成了字符串。

5.2 NULL和’’的区别

空值('')的长度是0，是不占用空间的；

NULL长度是NULL，其实它是占用空间的，NULL列需要行中的额外空间来记录它们的值是否为NULL。

使用 COUNT(字段) 统计会过滤掉 NULL 值，但是不会过滤掉空值。

e.g. 创建表时，如果设置字段为null，平台会提示避免null

面试题：

mysql的NULL值是怎么存储的