mysql 知识总结

MySQL
125
0
0
2024-05-14

数据类型

整型

  • 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。