MySQL

Basic

什么是MySQL存储过程

我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。优点：

(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。 (2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。 (3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。 (4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。 (5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户

事务

什么是事务？

是一组SQL语句的集合，这些语句要么全部成功执行，要么全部不执行。事务的目的是确保数据库操作的完整性和一致性，即使在并发操作或系统故障的情况下也能保证数据的正确性。

Alice想给Bob转账100元，这个操作需要以下步骤：

从Alice的账户中扣除100元。
向Bob的账户中增加100元。

这两个步骤必须全部成功，否则会导致数据不一致。因此，我们需要将这两个步骤放在一个事务中执行。

-- 开始事务
START TRANSACTION;
 
-- 从Alice的账户中扣除100元
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
 
-- 向Bob的账户中增加100元
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
 
-- 提交事务
COMMIT;

事务的四大特性

事务4大特性

原子性、一致性、隔离性、持久性

原⼦性

事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么全不执行

说明：

如果在执行UPDATE accounts SET balance = balance - 100 WHERE id = 1;时成功，但在执行UPDATE accounts SET balance = balance + 100 WHERE id = 2;时失败（例如，Bob的账户不存在），那么整个事务会回滚。
回滚操作会撤销第一步的操作，恢复Alice的账户余额为1000元。
这样，要么Alice和Bob的账户都成功更新，要么都没有更新，确保了操作的原子性。

一致性

执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的；

在转账操作之前，Alice的余额是1000元，Bob的余额是500元。
转账操作完成后，Alice的余额应该是900元，Bob的余额应该是600元。
如果事务失败并回滚，Alice的余额仍然是1000元，Bob的余额仍然是500元。
无论事务成功还是失败，数据库的状态都保持一致。

隔离性

并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；

假设在Alice给Bob转账的同时，Bob也在给Charlie转账100元。
这两个事务是并发执行的，但它们之间不会相互干扰。
Alice给Bob转账的事务只会影响Alice和Bob的账户，不会影响Bob给Charlie转账的事务。
数据库通过锁机制（如行锁）来确保每个事务在操作时不会被其他事务干扰。

持久性

⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响。

假设在Alice给Bob转账的事务提交后，数据库突然发生故障（如服务器断电）。
当数据库恢复后，Alice的余额仍然是900元，Bob的余额仍然是600元。
事务提交后，数据库通过日志（如重做日志）来确保数据的改变是永久的，即使发生故障也能恢复。

实现保证 MySQL的存储引擎InnoDB使用重做日志保证一致性与持久性，回滚日志保证原子性，使用各种锁来保证隔离性。

索引

日志

Binlog有哪些格式

Statement模式

记录每一条会修改数据的SQL语句

优点：

日志记录量小，节省磁盘空间和网络IO。
性能较高

缺点：

可能导致主从数据不一致，尤其是涉及非确定性函数（如UUID()、NOW()）或存储过程。
需要记录上下文信息以确保SQL语句在从服务器上执行结果一致

结果：

Binlog记录了实际执行的SQL语句（INSERT、UPDATE、DELETE）。
每条记录包含时间戳、线程ID、执行时间等元数据。

# at 4
#250708 10:00:00 server id 1  end_log_pos 123 CRC32 0x12345678  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1688790000/*!*/;
INSERT INTO test VALUES (1, 'Kimi')
/*!*/;
# at 123
#250708 10:00:01 server id 1  end_log_pos 234 CRC32 0x87654321  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1688790001/*!*/;
UPDATE test SET name = 'Moonshot' WHERE id = 1
/*!*/;
# at 234
#250708 10:00:02 server id 1  end_log_pos 345 CRC32 0x11223344  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1688790002/*!*/;
DELETE FROM test WHERE id = 1
/*!*/;

ROW

记录每一行数据的变化，而不是SQL语句

优点：

记录详细，可以精确反映数据的变化。
避免了STATEMENT格式中可能出现的数据一致性问题

缺点：

日志量大，尤其是批量操作时。
性能可能受影响

结果： 在ROW格式下，Binlog会记录每一行数据的变化，而不是SQL语句。记录的内容大致如下：

# at 4
#250708 10:00:00 server id 1  end_log_pos 123 CRC32 0x12345678  Table_map: `test`.`test` mapped to number 1
#250708 10:00:00 server id 1  end_log_pos 234 CRC32 0x87654321  Write_rows: table id 1 flags: STMT_END_F
### INSERT INTO `test`.`test`
### SET
###   @1=1
###   @2='Kimi'
# at 234
#250708 10:00:01 server id 1  end_log_pos 345 CRC32 0x11223344  Update_rows: table id 1 flags: STMT_END_F
### UPDATE `test`.`test`
### WHERE
###   @1=1
###   @2='Kimi'
### SET
###   @1=1
###   @2='Moonshot'
# at 345
#250708 10:00:02 server id 1  end_log_pos 456 CRC32 0x44556677  Delete_rows: table id 1 flags: STMT_END_F
### DELETE FROM `test`.`test`
### WHERE
###   @1=1
###   @2='Moonshot'

INSERT操作记录了插入的行数据（id=1, name='Kimi'）。
UPDATE操作记录了更新前后的行数据（id=1, name='Kimi'变为id=1, name='Moonshot'）。
DELETE操作记录了被删除的行数据（id=1, name='Moonshot'）。
Binlog中没有记录实际的SQL语句，而是记录了数据行的变化。

MIXED

结合了STATEMENT和ROW两种模式

默认使用STATEMENT格式。
在某些情况下（如涉及非确定性函数、存储过程等）自动切换到ROW格式

优点：

兼顾了STATEMENT的性能和ROW的数据一致性

缺点： 配置和管理相对复杂

结果： 在MIXED格式下，MySQL会根据SQL语句的性质自动选择STATEMENT或ROW格式。假设这些SQL语句都是确定性的，记录的内容可能如下：

# at 4
#250708 10:00:00 server id 1  end_log_pos 123 CRC32 0x12345678  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1688790000/*!*/;
INSERT INTO test VALUES (1, 'Kimi')
/*!*/;
# at 123
#250708 10:00:01 server id 1  end_log_pos 234 CRC32 0x87654321  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1688790001/*!*/;
UPDATE test SET name = 'Moonshot' WHERE id = 1
/*!*/;
# at 234
#250708 10:00:02 server id 1  end_log_pos 345 CRC32 0x11223344  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1688790002/*!*/;
DELETE FROM test WHERE id = 1
/*!*/;

https://mp.weixin.qq.com/s/lnzd890lyi6HzXg679nC-Q

https://mp.weixin.qq.com/s/L26rI11OV8hrfJVy5Yu78g

https://mp.weixin.qq.com/s/NbzzHNQdR3jIrEHz9ewTgA

https://mp.weixin.qq.com/s/HNBRqrrrjUV-7V37xYqSqg

https://mp.weixin.qq.com/s/aOOa6noApIvBMCRzqUPmvQ

https://mp.weixin.qq.com/s/UA0IoZpbHISaAWLfI6MSJw

https://mp.weixin.qq.com/s/X2qj_CzA4jWqHUHlYYAseA

https://mp.weixin.qq.com/s/PKYjPJo5HlZO9JupiGVL1g

https://mp.weixin.qq.com/s/oSJ2Q6_y8rr8X57nOPYm5A

https://mp.weixin.qq.com/s/4WP1ciXMXBvskBx8f70yig

https://mp.weixin.qq.com/s/F1D3Eoi_EfpCI3FEZ6m7nQ

https://mp.weixin.qq.com/s/FLD5jWVIu_7-zoUndcNroQ

https://mp.weixin.qq.com/s/7gVLDuQtV51YeZduLsmFRQ

https://mp.weixin.qq.com/s/mLLsOLzq1FIZWWhEgOhh8w

https://mp.weixin.qq.com/s/Zz7mYBGexas_ahr5Pn6FkQ

https://mp.weixin.qq.com/s/90_MfmQCjYnL8VfJpDQ6uA

https://mp.weixin.qq.com/s/YPj5zBew1rRBG4J_oY0lMQ

https://mp.weixin.qq.com/s/od5k5Weqnglo9kue4nUBJQ

Product

12.两台 mysql 服务器，其中一台挂了，怎么让业务端无感切换，并保证正常情况下讲台服务器的数据是一致的？不是核心业务的话，先停写，把备机拉起来，查看两台机器的日志，进行数据补偿，开写。如果是核心业务的话，现在所有的写操作都在正常的状态机器上。把好的这台机器的备机拉起来，当主机。

以上全是应急操作。实际上数据库的容灾设计要复杂的多。面试官要是问你，备机的数据不一致怎么办，你要勇敢怼回去，你们每秒多少写入操作。

按照百万级表，每秒 1000 的写入效率，正常的设计是，分布在 2 台机器上每台 500。这个级别的数据同步，出现差异的概率可以忽略不计的。有一台出现问题，另一台也可以抗住。正常的操作，还是先停写，等数据一致，切换，开写。搞这些切换都是在凌晨 4.00 左右，核心业务的每秒写操作，只有十几个。前后耽搁不到 20 秒。

https://mp.weixin.qq.com/s/HGfjitDLSl3JZCocIXeMvA

https://mp.weixin.qq.com/s/P6Wc1dZqfLCBPf3w0PqlSQ

https://mp.weixin.qq.com/s/uqOjDhbkh9SFDJg_W9e-dQ

https://mp.weixin.qq.com/s/pioYGEnzGcHHsltS2uK33Q

https://mp.weixin.qq.com/s/1sAzLXFaqFpgfr5fYKcrpw