MySQL笔记

与MySQL建立连接

命令：

1

mysql -h$ip -P$port -u$user -p

连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。
输完命令之后，你就需要在交互对话里面输入密码。虽然密码也可以直接跟在 -p 后面写在命令行中，但这样可能会导致你的密码泄露。如果你连的是生产服务器，强烈建议你不要这么做。
连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。文本中这个图是 show processlist 的结果，其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。这时候如果你要继续，就需要重连，然后再执行请求了。
数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。
但是全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。
可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

先说结论：查询缓存大部分情况下没什么用。
因为查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。

除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

MySQL 提供“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

1

mysql> select SQL_CACHE * from T where ID=10；

MySQL 8.0 版本直接将查询缓存的整块功能删掉了，说明官方也觉得确实没啥用，笑～

日志

redolog 重放日志（InnoDB）

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做
如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。
其实就是 MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘
InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

binlog 归档日志

binlog日志的几种格式

MySQL的二进制日志（Binlog）有多种录入格式，其中常见的有三种：
**Statement-Based Replication (SBR)：基于语句的复制，**在这个模式下，二进制日志会记录每个执行的SQL语句。当主服务器执行一个修改数据库的SQL语句（例如INSERT、UPDATE、DELETE）时，相关的SQL语句将会被记录到二进制日志中。在从服务器上，这些语句将被重新执行，从而使从服务器的数据保持与主服务器一致

1. 优点：
   • 简单，易于理解。
   • 较小的日志文件。
2. 缺点：
   • 对于一些非确定性的SQL语句，可能会导致主从不一致。
   • 由于记录的是SQL语句，而非数据变更的结果，可能引发一些问题。

**Row-Based Replication (RBR)：基于行的复制，**这个模式下，二进制日志会记录每一行数据的变更。当主服务器执行修改数据的SQL语句时，实际的数据变更会被记录到二进制日志中，而不是SQL语句本身。

1. 优点：
   • 更精确，避免了一些SBR的非确定性问题。
   • 能够处理一些不同步SQL语句的情况。
2. 缺点：
   • 日志文件可能相对较大，因为记录的是数据变更的细节。

**Mixed Format：混合格式，**这个模式是上述两种格式的混合使用。MySQL会根据执行的SQL语句类型来选择使用SBR或RBR。

1. 优点：
   • 兼顾了SBR和RBR的优势，可以灵活适应不同的情况。
2. 缺点：
   • 增加了复杂性，可能需要更多的维护工作。

选择使用哪种二进制日志的格式通常取决于具体的复制需求、性能和容错要求。默认情况下，MySQL通常使用的是Mixed Format。可以通过配置binlog_format参数来指定使用哪种格式。

redolog 与 binlog的区别

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

从日志的角度看 Update 语句的执行过程

你可能注意到了，最后三步看上去有点“绕”，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

两阶段提交是什么？

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。
它能保证无论是写redolog时崩溃还是写binlog时崩溃，数据库在恢复数据后与原数据库一致。

如何利用日志恢复数据？

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

• 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
• 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。

实际情况往往更加复杂0.0后面有机会再补充吧

该几天备份一次数据库？

备份频率与指标 RTO 息息相关（恢复目标时间）
一天一备跟一周一备的对比的话，好处是“最长恢复时间”更短。在一天一备的模式里，最坏情况下需要应用一天的 binlog。比如，你每天 0 点做一次全量备份，而要恢复出一个到昨天晚上 23 点的备份。一周一备最坏情况就要应用一周的 binlog 了。
当然这个是有成本的，因为更频繁全量备份需要消耗更多存储空间，所以这个 RTO 是成本换来的，就需要你根据业务重要性来评估了。

事务

提到事务，你肯定不陌生，和数据库打交道的时候，我们总是会用到事务。最经典的例子就是转账。

你要给朋友小王转 100 块钱，而此时你的银行卡只有 100 块钱。转账过程具体到程序里会有一系列的操作，比如查询余额、做加减法、更新余额等。 这些操作必须保证是一体的，不然等程序查完之后，还没做减法之前，你这 100 块钱，完全可以借着这个时间差再查一次，然后再给另外一个朋友转账，如果银行这么整，不就乱了么？

简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL 中，事务支持是在引擎层实现的。

事务的特点：ACID

1. 原子性： 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
2. 一致性： 指在事务开始之前和事务结束以后，数据不会被破坏，假如 A 账户给 B 账户转 10 块钱，不管成功与否，A 和 B 的总金额是不变的。
3. 隔离性： 多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果。简言之，就是事务之间是进水不犯河水的。
4. 持久性： 表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

原子性：是使用 undo log 来实现的，如果事务执行过程中出错或者用户执行了
rollback，系统通过 undo log 日志返回事务开始的状态。
 持久性：使用 redo log 来实现，只要 redo log 日志持久化了，当系统崩溃，即可
通过 redo log 把数据恢复。
 隔离性：通过锁以及 MVCC,使事务相互隔离开。
 一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。

隔离级别

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。
在谈隔离级别之前，你首先要知道，你隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。下面我逐一为你解释：

• 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
• 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
• 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

我们来看看在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、V2、V3 的返回值分别是什么。

• 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
• 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
• 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

配置隔离级别

配置的方式是，将启动参数 transaction-isolation 的值设置成 READ-COMMITTED。你可以用 show variables 来查看当前的值。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

mysql> show variables like 'transaction_isolation';

+-----------------------+----------------+

| Variable_name | Value |

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED |

+-----------------------+----------------+

如何选择合适的隔离级别

总结来说，存在即合理，每种隔离级别都有自己的使用场景，你要根据自己的业务情况来定。我想你可能会问那什么时候需要“可重复读”的场景呢？我们来看一个数据校对逻辑的案例。
假设你在管理一个个人银行账户表。一个表存了账户余额，一个表存了账单明细。到了月底你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。
这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他事务更新的影响。

事务隔离的实现原理

理解了事务的隔离级别，我们再来看看事务隔离具体是怎么实现的。这里我们展开说明“可重复读”。
在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

A 当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

为什么事务要尽量短小？

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

如何开启一段事务

SQL执行过程

一些最佳实践

数据库常见命名规范

1. 所有数据库对象名称必须使用小写字母并用下划线分割；
2. 临时库表必须以tmp_为前缀并以日期为后缀，备份表必须以bak_为前缀并以日期(时间戳)为后缀；
3. 业务侧，同一种数据在不同表的列名和列类型必须一致；
4. 索引：
   1. 核心业务实例中表的唯一索引使用u_前缀，非唯一索引使用i_前缀。
   2. 辅助性业务（如控制台、数据统计）实例中表的唯一索引使用au_前缀、非唯一索引使用ai_前缀。—— 解读：避免主库创建从库已有的索引。
   3. 测试性唯一索引使用tu_前缀，非唯一索引使用ti_前缀。—— 解读：测试性索引是指在业务实例中新增索引之前进行分析、验证的索引。使用独特的前缀可以与业务索引区分，方便测试完毕后移除。
   4. 组合索引中的列使用大驼峰命名法，用下划线分隔。例如：i_GroupID_UpdateTime。

所有表必须使用Innodb存储引擎

没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎。
Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。

每个Innodb表必须有个主键

Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。
Innodb是按照主键索引的顺序来组织表的

1. 不要使用更新频繁的列作为主键，不适用多列主键；
2. 不要使用UUID、MD5、HASH、字符串列作为主键（无法保证数据的顺序增长）；
3. 主键建议使用自增ID值；

数据库和表的字符集统一使用UTF8

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效，如果数据库中有存储emoji表情的需要，字符集需要采用utf8mb4字符集。

尽量使用数值替代字符串类型

1. 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；
2. 而对于数字型而言只需要比较一次就够了；
3. 字符会降低查询和连接的性能，并会增加存储开销；

使用varchar代替char

1. varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；
2. char按声明大小存储，不足补空格；
3. 其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

财务、银行相关的金额字段必须使用decimal类型

• 非精准浮点：float,double
• 精准浮点：decimal

1. Decimal类型为精准浮点数，在计算时不会丢失精度；
2. 占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节；
3. 可用于存储比bigint更大的整型数据；

避免使用ENUM类型

• 修改ENUM值需要使用ALTER语句；
• ENUM类型的ORDER BY操作效率低，需要额外操作；
• 禁止使用数值作为ENUM的枚举值；

不在数据库做计算。能让业务层干的事不要交给数据库。—— 解读：数据库很难扩展、而业务层容易扩展。

索引不宜太多，一般5个以内

1. 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；
2. 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；
3. 索引表的数据是排序的，排序也是要花时间的；
4. insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；
5. 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；

如何建立更好的索引

建立索引的目的是：希望通过索引进行数据查找，减少随机IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。
区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）。
尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）。
使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）。

尽量把所有列定义为NOT NULL

NOT NULL列更节省空间，NULL列需要一个额外字节作为判断是否为 NULL的标志位。NULL列需要注意空指针问题，NULL列在计算和比较的时候，需要注意空指针问题。

禁止在数据库中存储图片，文件等大的二进制数据

通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时。
通常存储于文件服务器，数据库只存储文件地址信息。

建议把BLOB或是TEXT列分离到单独的扩展表中

Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。而且对于这种数据，Mysql还是要进行二次查询，会使sql性能变得很差，但是不是说一定不能使用这样的数据类型。
如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select * 而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询。

TEXT或BLOB类型只能使用前缀索引

因为MySQL对索引字段长度是有限制的，所以TEXT类型只能使用前缀索引，并且TEXT列上是不能有默认值的。

尽量做到冷热数据分离，减小表的宽度

Mysql限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节。
减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO）；
更有效的利用缓存，避免读入无用的冷数据；
经常一起使用的列放到一个表中（避免更多的关联操作）。

谨慎使用Mysql分区表

• 分区表在物理上表现为多个文件，在逻辑上表现为一个表；
• 谨慎选择分区键，跨分区查询效率可能更低；
• 建议采用物理分表的方式管理大数据。

数据库范式

当谈到数据库范式时，我们可以通过一个简单的例子来说明。考虑一个图书管理系统的数据库，其中有两个表格：一个存储书籍信息的表格和另一个存储作者信息的表格。
未规范化的设计：
Book表格：

1
2
3
4
5

| BookID | Title                | Author              | Genre      | Price |
|--------|----------------------|---------------------|------------|-------|
| 1      | The Catcher in the Rye| J.D. Salinger       | Fiction    | 15.99 |
| 2      | To Kill a Mockingbird | Harper Lee          | Fiction    | 12.50 |
| 3      | 1984                 | George Orwell       | Dystopian  | 20.00 |

规范化的设计：
Author表格：

1
2
3
4
5

| AuthorID | AuthorName      |
|----------|-----------------|
| 1        | J.D. Salinger   |
| 2        | Harper Lee      |
| 3        | George Orwell   |

Book表格：

1
2
3
4
5

| BookID | Title                | AuthorID | Genre      | Price |
|--------|----------------------|----------|------------|-------|
| 1      | The Catcher in the Rye| 1        | Fiction    | 15.99 |
| 2      | To Kill a Mockingbird | 2        | Fiction    | 12.50 |
| 3      | 1984                 | 3        | Dystopian  | 20.00 |

在未规范化的设计中，每本书的作者信息被重复存储，可能导致数据冗余。在规范化的设计中，我们将作者信息单独存储在Author表格中，每个作者都有唯一的AuthorID。然后，Book表格中使用AuthorID作为外键来关联作者信息，从而避免了数据冗余。
这个例子展示了**第一范式（确保每列是原子的）、第二范式（确保非主键列完全依赖于主键）和第三范式（确保所有列直接依赖于主键）**的应用。这样的设计有助于提高数据库的一致性和减少冗余，但在实际应用中，设计时需要根据具体情况进行权衡。

权限

MySQL 有关权限的表都有哪几个？

在MySQL中，权限相关的信息存储在多个系统表中。以下是一些常见的权限相关表：

1. mysql.user： 存储用户账户信息，包括用户名、密码以及与账户相关的全局权限。
2. mysql.db： 记录了数据库级别的权限，指定了用户对特定数据库的访问权限。
3. mysql.tables_priv： 包含有关表级别权限的信息，指定了用户对特定表的操作权限。
4. mysql.columns_priv： 存储列级别的权限信息，指定了用户对表中特定列的操作权限。
5. mysql.procs_priv： 记录了存储过程级别的权限，指定了用户对存储过程的执行权限。
6. mysql.proxies_priv： 存储关于代理用户（proxy users）的信息，包括代理用户的来源和目标用户。

MVVM多版本并发控制

分库分表

目前只做过垂直分库，根据 DDD 的领域概念，将一个库的表拆分到多个数据库中去。
分库分表可能遇到的问题：

1. 事务问题：需要用分布式事务啦。
2. 跨节点 Join 的问题：解决这一问题可以分两次查询实现
3. 跨节点的 count、order by、group by 等问题：分别在各个节点上得到

结果后在应用程序端进行合并。

4. 唯一Id问题：使用 IdGenerator 服务。
5. 分页问题。

索引

主从

主从复制原理

上图主从复制过程分了五个步骤进行：

1. 主库的更新SQL(update、insert、delete)被写到binlog
2. 从库发起连接，连接到主库。
3. 此时主库创建一个binlog dump thread，把bin log的内容发送到从库。
4. 从库启动之后，创建一个I/O线程，读取主库传过来的bin log内容并写入到relay log
5. 从库还会创建一个SQL线程，从relay log里面读取内容，从ExecMasterLog_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

主从复制延迟的原因

1. 机器性能差。
2. 机器负载高。
3. 网络问题。
4. 主库执行大事务。

主从复制延迟的解决方案

1. 主服务器要负责更新操作，对安全性的要求比从服务器要高，所以有些设置参数可以修改，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的 设置等。
2. 检查是否执行了大事务。