核心基础与操作
SQL语言
- DDL (数据定义语言)：用于定义和修改数据库对象结构，如 CREATE, ALTER, DROP。
- DML (数据操作语言)：用于操作数据本身，如 INSERT, UPDATE, DELETE。
- DQL (数据查询语言)：用于查询数据，主要是 SELECT 及其复杂的子句（WHERE, JOIN, GROUP BY, HAVING, ORDER BY, LIMIT）。
- DCL (数据控制语言)：用于管理权限和安全，如 GRANT, REVOKE。
数据库对象
包括数据库、表、索引、视图等。
- 表设计：涉及数据类型（数值、字符串、时间）、列属性（NOT NULL, DEFAULT, AUTO_INCREMENT）和约束（主键、外键、唯一键）。
- 索引：是提高查询效率的关键数据结构，默认使用B+Tree算法。理解聚簇索引与非聚簇索引的区别、最左前缀原则以及如何避免索引失效是优化查询的基石。
核心机制与原理
SQL执行过程
- 连接器：客户端通过连接器连接MySQL服务器，验证账号密码权限是否正确
- 缓存：查询缓存有则直接返回，无则进入分析器（MySQL8+废弃）
- 分析器：检测SQL语法是否正确，验证库表是否存在
- 优化器：优化SQL语句，判断要使用哪个索引、如何排序，生成最优的执行计划
- 执行器：执行语句，生成具体的计划树
- 返回结果：格式化结果返回给客户端
存储引擎
- InnoDB：是默认且绝对主流的引擎。支持事务、行级锁、外键，使用MVCC支持高并发，具备崩溃恢复能力，适合绝大多数OLTP场景。
- MyISAM：不支持事务和行级锁，但查询速度快，适合读多写少的静态数据场景。
索引
按存储架构
- 聚簇索引：InnoDB特有，一个表只能有一个，主键自动成为聚簇索引
- 非聚簇索引：包涵普通索引和唯一非主键索引
按数据结构
- B-Tree索引：最常见的索引类型，使用全键值、键值范围和键值前缀检索
- Hash索引：按照Hash值划分
- 全文索引：全文搜索
- 空间索引：地理数据
按功能划分
- 主键索引：主键唯一且只能有一个、不能为空
- 唯一索引：唯一可以有多个，可以为空
- 复合索引：多个列组合的索引
- 前缀索引：只索引字段的前面部分字符
- 普通索引：最基本的索引，无特殊限制
事务
原子性 (Atomicity)： 事务是不可分割的工作单位，事务中的操作要么全部成功，要么全部失败回滚
一致性 (Consistency)： 事务执行前后，数据库从一个一致状态变到另一个一致状态
隔离性 (Isolation)： 多个事务并发执行时，一个事务的执行不应影响其他事务
- 读未提交
- 读已提交
- 可重复读
- 串行化
持久性 (Durability)： 事务一旦提交，其结果就是永久性的
锁机制
保证并发操作的数据一致性。
按锁粒度分类
- 全局锁：锁住整个MySQL实例
- 表锁：锁住单张表
- 行锁：锁住具体的数据行
按锁模式分类
- 共享锁(S) - 读锁，多个事务可同时持有
- 排他锁(X) - 写锁，只能一个事务持有
按实现算法分类
- 记录锁 - 锁定索引记录
- 间隙锁 - 锁定索引记录间的空隙
- Next-Key锁 - 记录锁+间隙锁
死锁
死锁形成
- 事务A持有id=1锁，等待id=2锁
- 事务B持有id=2锁，等待id=1锁
- MySQL检测到死锁，自动回滚其中一个事务
日志系统
错误日志(Error Log)
记录服务器启动、运行和关闭过程中的错误、警告和关键事件
查询日志(General Query Log)
记录所有客户端发送到服务器的查询语句,无论这些查询是否成功执行,生产环境建议关闭
慢查询日志(Slow Query Log)
记录执行时间超过阈值的查询语句,慢查询日志默认关闭，需要手动配置开启，不建议长期开启
二进制日志(Binary Log)
记录所有更改数据库数据的DDL和DML语句(不包括SELECT、SHOW等查询语句)，用于数据恢复和复制
事务日志(Transaction Log)
重做日志(Redo Log)
重做日志(redo log)是InnoDB存储引擎特有的物理日志，记录事务提交时数据页的物理修改，主要功能是保证事务的持久性和数据库的崩溃恢复能力。
回滚日志(Undo Log)
回滚日志(undo log)用于记录数据被修改前的信息
- 当delete一条数据时，会插入一条对应的insert记录到undo log
- 当update一条记录时，会插入一条相反的update记录到undo log
中继日志(Relay Log)
中继日志(relay log)是主从复制过程中从服务器(I/O线程)使用的日志，记录了从主服务器接收的二进制日志事件
- 由从服务器的I/O线程从主服务器读取并写入
- 从服务器的SQL线程读取中继日志并执行其中的事件
运维管理
备份与恢复
按备份内容
- 逻辑备份：使用 mysqldump 或 mydumper 导出SQL语句，灵活但恢复慢。
- 物理备份：使用 XtraBackup 拷贝物理文件，速度快，适合大规模数据。
按备份方法
- 热备：服务不停进行备份，对进行数据库操作无影响，但是对性能有影响
- 冷备：停止服务进行备份，数据一致性和完整性高，但是服务不可用
- 温备：数据库运行备份，但是只支持读操作，会阻塞写操作
按备份策略
- 全备：对整个数据完全备份，操作简单，恢复完整，占用空间大
- 增量备份：仅备份自上次备份(完全或增量)以来变化的数据，备份数据量小，占用空间小，备份速度快
- 差异备份：备份自上次完全备份以来所有变化的数据，恢复只需最近一次完全备份和最后一次差异备份
监控与优化
- 性能分析：使用 EXPLAIN 命令分析SQL语句的执行计划，是查询优化的首要步骤。
- 压力测试：使用 mysqlslap 等工具模拟业务压力，评估数据库性能。
高可用与扩展架构
主从复制
复制原理
主库将数据变更写入Binlog，从库通过I/O线程获取并写入Relay Log，再由SQL线程重放，实现数据同步。
主从复制涉及两日志和三线程机制：
- 二进制日志(Binlog)：主库记录所有数据变更的日志文件
- 中继日志(Relay Log)：从库暂存从主库获取的二进制日志
- 主库Dump 线程监听 Binlog 变化并发送给从库的 I/O 线程
- 从库I/O 线程：请求主库的 Binlog 并写入 Relay Log
- 从库SQL 线程：读取 Relay Log 并执行其中的 SQL 语句
复制模式
- 异步复制(默认)：主库执行完事务后立即返回，不等待从库确认
- 半同步复制：主库至少等待一个从库接收并写入 Relay Log 后才返回
- 组复制(Group Replication)：基于 Paxos 协议的多主同步复制
高可用方案
MHA
经典的MySQL主从自动故障切换方案。 MHA是一套开源的 MySQL 高可用性解决方案，能够在主库发生故障时自动进行故障转移，将从库提升为新主库，从而保证 MySQL 服务的高可用性和可靠性 MHA Manager定期检查主节点的心跳信号，根据从库的复制位置(position/GTID)选择数据最接近原主库的从库晋升为主库。这种方案只适用于只支持一主多从，不支持多主写入
主主复制
主主复制架构原理就是两台MySQL互为主从A主B，B主A实现主主复制，多节点也是同样的原理。
在MySQl之上使用Haproxy做MySQL的主备切换或读写分离，当MySQL主故障会自动转发流量到备库
在Haproxy之上利用Keepalived漂移VIP方案做Haproxy的高可用