mysql 知识总结

数据类型

整型

• tinyint，1字节
• smallint，2字节
• mediumint，3字节
• int，4字节
• bigint，8字节
• 默认是带符号的，可以加unsigned表示无符号。
• int(n)，n不是表示长度，不会影响存储，仅用于控制显示的宽度。

浮点型

• float，4字节
• double，8字节
• double(m,d)，m表示总长度，d表示小数位，超出四舍五入。
• 存在精度丢失问题，避免等值比较。

实数型

• decimal(m,d)，16字节，m表示总长度，d表示小数位，超出四舍五入。
• 不存在精度丢失问题。
• 适合存储金额等精度敏感数据。

日期类型

• date，3字节，年月日
• time，3字节，时分秒
• year，1字节，年
• datetime，8字节，年月日时分秒，默认为空。
• timestamp，4字节，时间戳
  • 1970-01-01 00:00:00UTC 到当前时间的毫秒数。
  • 最大到 2038 年。
  • 默认当前时间。
  • 创建和修改时间一般用 timestamp。

字符串

• char(n)，定长字符串
  • n表示字符串长度，超出截断。
  • 最大长度255，与字符编码无关。
  • 长度不够补空格。
• varchar(n)，变长字符串
  • n表示字符串最大长度，超出截断。
  • 不同字符编码，一个字符占用字节数不同。
  • 一行数据最大长度64K，减去其他字段占用才能计算n最大值。
  • 一般建议n 不要超过 5K，如果大于可使用 text 并且独立表。
• text，大型文本
  • 不需要指定长度限制。
  • 一行数据最大长度64K和字符编码决定的最大长度限制。
  • 不能建索引。
• blob，二进制大对象
  • 其限制及使用类似 text。

数据类型的属性

• not null，非空
• default，默认值
• unsigned，无符号
• auto_increment，自增，适用于整型。
• primary key，主键，唯一标识，不能重复，一个表只能有一个主键。

sql

DDL

数据库定义语言，如创建数据库表结构等。

DML

数据库操作语言，如数据的增删改查。

查询分页

• 使用limit m,n：m表示起始位置，n表示查询条数。
• 数据量很大时，可以用覆盖索引分页查出 ID，再根据 ID 查数据。

批量插入数据

• insert 的值支持多行，可以同时插入多行数据。
• 在一个事务内批量插入，避免每次插入后自动提交。

清空表数据

• DELETE，常规删除操作，可以回滚。
• TRUNCATE，属于 DDL 操作，不能回滚，速度快，自增标识会重置。

约束

• 主键约束：用来唯一标识一行数据，不能重复，不能为空。
• 唯一约束：用来唯一标识一行数据，不能重复，可以为空。
• 自增长约束：从1开始每次加1，和主键配合使用。
• 外键约束：用来和其他表建立联系的字段，是另一表的主键，可以重复可以为空，可以有多个外键。
• 非空约束：不能为空。
• 默认值约束：不指定值时使用默认值填充。

可空字段可能导致的问题

• count数据丢失，count(*)和count(可空字段)结果不一样，前者是推荐用法。
• select数据丢失，对可空字段进行非等于比较查询时，NULL数据丢失。
• 空指针异常，sum(可空字段)统计不存在的数据时，结果为NULL而非0。
• 增加查询难度，当需要进行条件比较时需考虑NULL值，增加is not null判断条件。
• 索引失效，null值在索引中被视为最小值，某些场景下可能出现索引失效。

数据库设计范式

• 关系型数据库设计范式，是保证数据完整性和减少冗余的原则。
• 第一范式：要求表字段是不可分割的单一属性。
• 第二范式：在第一范式基础上，要求表中每个字段都和主键相关，不能依赖于主键的一部分。
• 第三范式：在第二范式的基础上，要求除主键外的其它字段必须互不依赖。
• 优点：符合三范式可以确保数据是准确、一致和易于维护的。
• 缺点：过度规范化导致设计复杂，降低性能，实际应用中需要权衡规范化和性能。

mysql 架构

Server 层

• 实现跨存储引擎的功能，包括：
  • 连接器：管理客户端连接，实现认证、权限、加密等。
  • 分析器：词法分析、语法分析、语义分析等。
  • 优化器：SQL语句优化，如选择索引、join优化等。
  • 执行器：负责执行具体操作，如 CRUD、函数、存储过程等。

存储引擎层

• 负责数据的存储和提取。
• 插件式，一个数据库的多个表支持不同的存储引擎。
• 常用是 InnoDB 和 MyISAM，默认是 InnoDB。

执行查询语句的过程

• 建立连接，验证身份，给于权限。
• 查询缓存，有则返回，mysql 8.0版本后移除，因为有更新或条件不同则缓存失效作用不大。
• 分析器，进行sql语法分析。
• 优化器，选择最优方案，生成执行计划。
• 执行器，根据执行计划，调用存储引擎接口执行。

mysql 连接

mysql 连接器

• 每一个数据库连接，都会创建一个线程来处理。
• 默认最大连接数是151，超过会等待。
• 默认连接方式是 TCP，默认端口 3306。
• 认证方式：
  • 密码认证模式，通过用户名和密码认证。
  • SSL认证模式，用户名密码基础上增加 SSL 安全连接。

客户端连接池

• 客户端连接数据库时，一般通过连接池方式。
• 连接池维持一定数量的连接，需要时取出，使用完放回。
• 一般会设置最小连接数和最大连接数来控制数量。
• 优点：减少频繁创建和销毁连接带来的开销，限制连接数。

mysql存储引擎

• mysql 采用插件式存储引擎，一个数据库的多个表支持使用不同的存储引擎。
• 常用是 InnoDB 和 MyISAM，默认是 InnoDB。
• InnoDB
  • 默认存储引擎，支持ACID事务、外键和行锁。
  • 并发条件下要求数据一致性，适合更新比较频繁的场景。
• MyISAM
  • 不支持事务，不支持外键，只支持表锁。
  • 适用于读多写少且对事务要求不高的场景。

索引

• 定义：索引是单独的、物理的对数据库表中的一列或多列进行排序的存储结构。
• 作用：相当于图书的目录，用于提高查询效率，降低 IO 成本。

分类

• 主键索引：主键唯一且不为空，是一种特殊的唯一索引。
• 唯一索引：索引列值必须唯一，但允许有空值。
• 普通索引：索引列允许重复。
• 联合索引：对多列进行索引，使用最左匹配原则。
• 全文索引：一般不用，不是 mysql 专长。

数据结构

• B+树，平衡多路查找树，时间复杂度O(logn)。
• 哈希，时间复杂度O(1)，只支持等值查询，不支持排序和范围，innodb 自动创建的内存索引。

物理存储

• 聚集索引：叶子节点包含完整一行数据，类比于字典的按首字母排序组织。
  • 一个表必须有一个聚集索引。
  • 默认使用主键，然后使用非空唯一索引，都没有则生成隐藏自增列作为聚集索引。
• 非聚集索引（辅助索引）：叶子节点仅包含主键，查询非主键字段需要回表二次查询。
  • NULL值作为最小数看待，全部放在树的最左边。

索引分裂

• 插入数据时，如果不是插入到叶子节点的最后，而是插入到中间，则可能导致索引的页的分裂，导致磁盘数据移动。
• 使用自增ID作为主键可以减少索引分裂的发生。

最左匹配原则

• 以下情况会失效：
• （A、B、C）索引，查询（B、C）没有最左列会失效，注意 mysql 会自动优化查询条件的顺序。
• Like VAL%可以使用索引，而 Like %VAL%会失效。
• 索引列运算、函数或隐式转换会失效。

哪些列适合加索引

• 经常作为查询条件的字段。
• 需要 join 连接的字段。
• 需要排序的字段。
• 需要group by 的字段。
• 字段值的离散程度大时才需要加索引，值重复率高的不适合加索引。

覆盖索引

• 索引包含所有需要查询的字段，是常用的优化手段。
• 对于非聚集索引，查询结果只包含索引字段或主键，可以避免回表做二次查询，提高效率。

索引下推

• 将服务层的工作下推到存储引擎层，减少回表次数。
• 在联合索引中，同时使用范围查询和等值查询，索引下推就可以发挥作用。

事务

事务的ACID特性

• A（原子性）：要么都成功，要么都失败，不可能出现部分完成情况。
• C（一致性）：事务执行前后，数据库完整性约束不会被破坏。
• I（隔离性）：并发环境，事务不会相互干扰。
• D（持久性）：事务成功，数据必须完整存盘不会丢失。

事务的隔离级别

• 隔离级别是指多个并发事务中保持数据一致性的隔离程度，决定了一个事务对其他事务产生的影响。
• 并发事务可能出现的不一致情况：
  • 脏读：读到其他事务未提交的数据。
  • 不可重复读：一个事务内，一开始读取的数据和结束前任意时刻读取的同一批数据出现不一致。
    • 其他事务对某些行的修改或删除。
  • 幻读：读到其他事务插入的数据。
• 隔离级别从低到高分别为：
  • 读取未提交（RU，Read Uncommitted）：允许脏读、不可重复读、幻读。
  • 读取已提交（RC，Read Committed）：不允许脏读，允许不可重复读、幻读。
  • 可重复读（RR，Repeateable Read）：不允许脏读和不可重复，允许幻读。
  • 串行化（S，Serializable）：所有事务串行化执行，不允许脏读、不可重复读、幻读。

MVCC

• MVCC 是多版本并发控制。
• 可以解决脏读、不可重复读和部分幻读的事务隔离问题。
• 有了 MVCC，只有写写会相互阻塞，读读、读写、写读都可以并行处理，提高了并发度。
• MVCC 的实现主要依赖：undo 日志和读试图。
  • undo 日志记录事务 ID 和旧版本数据，用于回退。
  • 读视图用于支持事务的快照读。
• 快照读：读取的是历史数据，不加锁。
• 当前读：读取的是最新数据，加锁。

锁

• 锁是协调多线程并发访问某一资源的机制。
• 锁机制可以保证数据并发访问的安全性，但是也会导致数据库的并发性能下降。

锁的分类

• 写锁（排他锁）：一次只能加一个写锁，一旦加了写锁无法再加其他锁。
• 读锁（共享锁）：可以多次加读锁，一旦加了读锁无法再加写锁。
• 乐观锁：
  • 假设出现并发写资源的概率较低。
  • 全程不加锁，只有提交数据时，才会判断是否违反数据完整性。
  • 实现方式：对数据加版本号，写入时把之前读取的版本号作为条件同时对版本号加 1，执行后检查影响行数。
  • 如果读取后版本号发生过变更，那么将会出现写入失败。
• 悲观锁：假设并发读写资源的概率较高，读写锁属于悲观锁。

锁的粒度

• 全局锁：对整个数据库加锁，阻塞所有写操作，用于数据库备份等维护操作。
• 表锁：粒度大，加锁快，不会出现死锁，并发性差。
• 行锁：粒度小，加锁慢，会出现死锁，并发性好，包括：
  • 记录锁，锁一行记录。
  • 间隙锁，锁一段范围，不包括记录本身，用于防止范围内插入新记录。

死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

日志

慢查询日志

• 记录执行时间超过阈值的 SQL 语句。
• 默认关闭，可以通过设置参数 slow_query_log 临时开启。
• 默认阈值是 10s。
• 可以用自带的 mysqldumpslow 命令分析日志。

二进制日志（Binlog）

• 记录对数据进行修改的操作日志，用于数据恢复和主从复制。
• 默认关闭，需要修改配置开启。
• 格式：
  • Statement：记录写数据的原始 sql，当有函数时可能出现不一致。
  • Row：记录修改的数据，日志量较大。
  • Mixed：混合模式，根据执行的 SQL 语句选择日志记录方式。

重放日志（Redolog）

• 记录对数据页物理改动的日志。
• 用于数据库崩溃后的数据恢复，确保事务的持久性。
• 与Binlog差别：
  • Binlog 是逻辑日志，效率低，理论上无限大.
  • Redolog 是物理日志，效率高，循环写.

回滚日志（Undolog）

• 记录用于回滚的日志。
• 对于插入，只记录主键，回滚时删除则可。
• 对于删除和修改，除了原记录外还记录用于 MVCC 的字段。
• 事务在快照读时，会生成一个读视图，基于回滚日志生成。

查询优化

• 减少 select 中的字段数量，避免使用复杂查询。
• 使用索引。
• 优化表结构，避免可空类型，合理设置数据类型和长度。
• 使用分区表。
• 分析执行计划，在 sql 前加 explain，输出信息中：
  • type 列，从快到慢分别为：
    • system：系统表，不需要磁盘 IO
    • const：常量，固定值
    • eq_ref：主键或唯一索引，返回结果最多只有一行
    • ref：非唯一索引，返回结果可能有多一行
    • range：索引范围扫描
    • index：索引全扫描
    • ALL：全表扫描
  • key 列为使用的索引。
  • extra 列，包含以下信息时可能索引失效，需要优化
    • Using filesort：无法利用索引排序，使用文件排序
    • Using temporary：使用了临时表，效率较差。
    • Using index：使用了覆盖索引，效率较高。
  • rows 列表示找到记录需要读取的行数，越少越好。

分区

• 表分区用于将表数据分成多个文件存储。
• 只能水平拆分（按行），不能垂直拆分。

表的文件结构

• InnoDB，一张表存储为2个文件：表结构，表数据和索引。
• MyISAM，一张表存储为3个文件：表结构，表数据，表索引。

分区好处

• 提升查询性能，只扫描特定分区，而不是全表。
• 易于管理和维护，可以只处理特定分区的文件。
• 更好的数据安全性和可用性，不同分区存储在不同的设备上，如将热数据放在高速存储上。

分区缺点

• 增加复杂性，需要设计合理的分区策略。
• 索引效率下降，跨分区查询效率降低。

分区表限制

• 无法使用外键约束。
• 分区数量有限，5.6.7 之后 最多8192 个分区。
• 分区键必须是主键或唯一索引的部分或全部字段。

分区类型

• RANGE：按范围分区。
• LIST：按离散值分区。
• HASH：按哈希值分区，分区键必须是整数。
• KEY：类似按哈希值分区，分区键支持除 BLOB 和 TEXT 外的类型。

常见场景：按日期字段的年份分区：

create table tbl(
  id int not null,
  content varchar(255),
  created_at timestamp not null
) partition by range (year(created_at)) (
partition p0 values less than (2023),
partition p1 values less than (2024),
partition p2 values less than MAXvalue
);

分库分表

垂直分表

• 定义：将一张表按列拆分到多张表中。
• 何时需要垂直分表：表字段过多影响读写效率，将冷热字段拆分到不同表中。
• 带来的问题：
  • 跨表查询复杂，需要表连接。
  • 修改多张表时需要用事务保证原子性。
  • 增加维护成本。

水平分表

• 定义：将一张表的数据按行拆分到多张表中。

何时需要水平分表

• 单表数据量超过 1KW 时，B+树可能超过3层导致查询时IO次数过多性能下降。
• 一般，单表数据量超过500W 需要考虑分表。
• 如果预估数据量会超过500W，可以提前规划分表。

如何选择分表键

• 原则：数据均匀分布，避免触发全表扫描。
• 查询条件尽可能利用分表键过滤。
• 根据业务，如按时间、地区、用户ID等。

非分表键如何查询

• 数据冗余到 ES 查询，推荐做法。
• 遍历所有表。

分表策略（类似分区策略）

• 范围：有利于扩容，可能存在分布不均问题。
• 哈希取模：扩容麻烦，分布较均匀。
• 一致性哈希：用哈希环，避免扩容时大量数据迁移。
• 范围+哈希取模：结合两种策略。

分布式 ID

• 分表后不能依赖表自增ID会重复，需使用分布式ID保证唯一性。
• 雪花算法：
  • 将64位整数分成三部分：时间、机器、序列号。
  • 第 1位符号位，不用。
  • 41 位时间戳，表示毫秒级的时间，最多表示69年，需要约定开始时间。
  • 10 位机器 ID，可以部署 1024 个节点。
  • 12 位序列号，同一毫秒内最多生成 4096 个 ID。

不停服拆表

• 加数据库访问代理层，通过配置开关决定访问新旧 DAO。
• 读旧表，双写，新增和修改在新旧表都执行。
• 通过脚本进行数据迁移。
• 读新表，仍然维持双写
• 稳定运行一段时间后再停写旧表。

分表中间件

• 简化开发。
• 如 Sharding-JDBC、go-orm/sharding。

分库

• 定义：将一个库的数据拆分到多个库中。

何时需要分库

• 单库数据量超过 5KW 时，需要拆分。

跨库表连接问题解决

• 字段冗余，避免连接。
• 全局表，所有库都保存一份。
• 应用层组装。

跨库事务问题解决

• 使用分布式事务。
• 如2PC、3PC、TCC、SAGA 等。

集群架构

集群作用

• 提高可用性，避免单点故障。
• 提高性能，分摊计算压力。

主从复制

• 主从复制是实现集群的基础。
• 同步复制：主库必须等待从库复制完成才能返回写入成功。
• 异步复制：主库不需要等待，只负责写入，从库负责复制。
• 半同步复制：主库等待至少一个从库复制完成，再返回成功。
• 并行复制：从库多线程处理数据同步，降低复制延迟。

集群模式

一主多从

• 读写分离，读负载均衡。
• 使用MHA（Master High Availablity），可以实现主库的故障切换。

级联复制

• 部分从库不连接主节点，而是连接从节点复制。
• 用于避免主从复制增加主节点负载。

双主复制

• 互为主从，相互复制。
• 复杂容易出现不一致，不建议使用。

多主一从

• 用于多源复制，即汇总多个不同库的数据到一个库中。
• 垂直或水平分库之后，可能使用此模式。

主从复制实现原理

• 主库开启 binlog。
• 从库两个线程，一个 IO 线程，一个 SQL 线程。
• IO 线程请求主库 binlog，写入relay log（中继日志）。
• SQL 线程读取 relay log，回放写入操作。
• 主库通过 log dump 线程，给从库传 binlog。