mysql

mian

为什么选择mysql

• 业务结构清晰、事务强一致性要求高的业务（金融、电商、ERP）
• 查询频繁、更新较少的系统，读取比写略高
• 使用外键约束的模型设计
• 免费+社区丰富

  和关系型数据库比较

比较项	MySQL 优势	PostgreSQL/Oracle 等其他关系型特点
性能	轻量、读写性能优秀，适合中小数据量和高并发场景	PostgreSQL 功能强但偏重，Oracle 商业数据库稳定性更强
易用性	配置简单、文档多、部署方便	PostgreSQL 配置更复杂、学习曲线陡峭
社区与生态	开源社区大、支持语言广泛、插件丰富	Oracle 生态闭源，PostgreSQL 社区活跃但规模稍小
成本	开源免费（可选商业支持如MySQL Enterprise）	Oracle/SQL Server 商业授权费用高
复制机制	主从复制、半同步复制成熟，适合读多写少场景	PostgreSQL 原生逻辑复制起步较晚，Oracle 有强商业支持但复杂

和非关系型数据库比较

如果你追求的是高扩展性、灵活结构、大数据吞吐、实时响应，则可考虑 NoSQL（如MongoDB、Redis）。

方面	MySQL（关系型）	NoSQL 数据库（如 MongoDB、Redis）
数据结构	表结构，强制 Schema，关系强（外键、约束）	文档型、键值对、列族等结构，Schema 灵活
事务支持	完善的事务机制（ACID），InnoDB引擎支持事务	大多数 NoSQL 不支持复杂事务（MongoDB 有一定事务支持）
查询能力	强大的 SQL 查询语言	一般使用自定义语法，灵活性高但不如 SQL 强大
扩展性	垂直扩展为主，分库分表较复杂	天然支持水平扩展，适合大规模分布式系统
一致性 vs 可用性	偏向一致性，追求数据准确	更偏向高可用、最终一致性（BASE 理论）
典型应用场景	金融、库存、电商、ERP、CMS等需要事务一致性	日志存储、缓存、社交网络、实时分析、IoT 等大数据高吞吐场景

mysql 的 InnoDB引擎写数据过程

总结：InnoDB 在插入数据时，先将数据写入内存（Buffer Pool）并生成 Undo/Redo 日志，事务提交时仅刷 Redo 日志到磁盘，通过 Doublewrite 机制和后台线程保障最终脏页落盘的完整性，从而在性能与事务安全之间取得平衡。

客户端发起 SQL
↓
交由解析器、优化器、执行器等组件处理，最终调用 InnoDB 引擎层 完成实际操作
↓
事务开始
↓
检查 Buffer Pool 中的目标页->若不在更新 Buffer Pool 中数据页
↓
写 Redo Log 记录操作（基于WAL原则（[^WAL原则]））：先写缓冲区->等事务提交写到binlog
↓
修改页内数据,同时写 Undo Log 备份原状态（回滚用）：从缓冲区->硬盘¹
↓
操作记录到事务系统中：支持隔离性检查
↓
锁机制生效（保证隔离性，使用行锁 + Next-Key Lock防止幻读
↓
提交事务 → 刷 Redo Log 到磁盘（保证持久性）
↓
写入 Undo Log 的标志为“不可回滚”
↓
释放行锁、事务ID 回收 → 事务完成
↓
后台慢慢刷脏页到 .ibd 文件（物理数据文件）:双写²

ACID特性	事务支持 实现机制
原子性	Undo Log：可以将插入操作回滚
一致性	通过锁 + Undo 回滚确保中间状态不可见
隔离性	MVCC + 行锁（RC、RR等隔离级别支持）
持久性	Redo Log 提交时 fsync() 确保落盘

InnoDB 的底层组成

• 表空间（Tablespace）
  • 使用表空间来管理存储数据
• 数据页与页结构
  • B+tree
• Buffer Pool（缓冲池）
  • 把磁盘页缓存到内存中（读、写都走缓存）来提高性能
  • 有脏页（dirty page） 刷盘机制³
• Redo Log（重做日志）
  • WAL 策略：先写日志，再写数据。
• Undo Log（回滚日志）
  • 逻辑日志，记录“旧值”，以便回滚。
  • 保存在 Undo 表空间中。
  • 支持 MVCC（快照读的前提）和事务回滚。

    InnoDB 的特性

• 事务系统
  • 分配 Transaction ID
  • 控制事务状态、生命周期
  • 管理 Undo/Redo、隔离级别、锁信息等
• 锁机制
  • 支持 行锁、意向锁、Gap Lock、Next-Key Lock、自增长锁
• 索引系统
  • 聚簇索引
  • 主键索引
  • 辅助索引-仅包含主键值做回表
• MVCC¹（多版本并发控制）
  • 实现高并发下的非阻塞读（快照读）
• 双写机制 ²
  • 写数据页时不会直接写入 .ibd 文件，而是 脏页（内存） → Doublewrite Buffer（共享表空间中间区）→ 最终写入表空间页
• 自适应哈希索引⁴
  • InnoDB 观察访问模式后，如果发现某个页被频繁按相似 key 查询，会自动为其生成哈希索引。

数据库优点	底层逻辑
事务支持	完全支持 ACID，使用 Undo Log 和 Redo Log 实现
行级锁	高并发、减少锁冲突，支持多版本并发控制（MVCC）
外键支持	关系完整性控制
崩溃恢复能力强	依靠 WAL（Write-Ahead Logging）机制
自动灾难恢复	数据崩溃后可自动恢复一致状态
自适应哈希索引	高频访问页转为哈希结构加速访问
Buffer Pool 缓存机制	类似数据库内存中的操作系统页缓存，提高性能
聚簇索引	主键索引和数据存储在一起，提高主键查询性能

优化

sql优化

1. 问题定位：
   1. 使用 EXPLAIN SELECT … 语句查看：使用了哪张表、索引是否命中、回表操作、全表扫描（type=ALL）
      1. 常见关注字段：type、key、rows、Extra
   2. 使用慢 SQL 监控工具
      1. MySQL 慢查询日志 等
2. where后查询条件优化
   1. 合理使用索引
      1. 避免在索引列上使用函数或计算
      2. 避免在索引列上隐式类型转换
   2. 减少 OR，使用 UNION / IN 替代
   3. 使用 EXISTS 替代 IN（子查询优化）：对大表，EXISTS 通常效率更高
   4. LIMIT 分页优化 ：避免大 OFFSET
   5. 使用覆盖索引
3. 避免SELECT *
4. JOIN 限制结果集，避免大表交叉乘积

   数据库字段选择

   text和varchar选择

   上限都是64k

   • varchar是变长字符串，存储更弹性，长度小于255性能更高
   • 尽量 用 VARCHAR 替代 TEXT 类型，除非字段长度极大且不可预测
   • 超过64k使用MEDIUMTEXT(最大16M) 或LONGTEXT（最大4G） - varchar存储长度大于255和text存储机制一样，性能差不多 - varchar（255）存储的实质会占用更多（会有一两个字节的额外开销） - text不能有默认值 - text临时表不能使用内存，需要写磁盘

DATETIME 和 TIMESTAMP 的选择

• 如果极度关注比较性能，或者需要频繁跨系统传递时间数据，并且可以接受可读性的牺牲（或总是在应用层转换），数值时间戳是一个强大的选项

区分	DATETIME	TIMESTAMP
时区信息	存储字面量，本身不包含任何时区信息	有时区处理能力，基于会话时区的自动转换
占用空间	‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’	表示的时间范围更小，只能到 2038 年

NULL 和 ‘‘的存储选择

区分	NULL	’’
含义	代表一个不确定的值，它不等于任何值	一个空字符串
存储空间	占用取决于数据库的实现	只存储一个空字符串的标志
比较运算	任何值与 NULL 进行比较结果都是 NULL	可以像其他字符串一样进行比较运算
聚合函数	大多数聚合函数（例如 SUM, AVG, MIN, MAX,COUNT(列名)）会忽略 NULL 值	会被聚合函数计算在内

架构优化

• 读写分离
• 分库分表
• 缓存机制

---

---

故事未完:161 Thoughts:: justdoit.

1. MVCC每行数据有两个隐藏字段：创建版本号（trx_id）删除版本号（roll_pointer），不同事务根据自己的版本号看到“不同的世界”，从而规避脏读和数据对不同事务的可见性。 ↩︎ ↩︎²

2. 双写机制保证页写入的原子性（页为 16KB，而 OS 的 sector 可能只有 512B）避免写一半导致页结构损坏的问题。 ↩︎ ↩︎²

3. 缓冲池先改内存页（标记为脏页），延后写入磁盘（后台刷盘线程处理）。 ↩︎

4. 自适应哈希索引条件：必须命中同一个页（Page）中的记录,访问模式重复性强（如某个范围经常被查）,AHI 的效果依赖于工作负载和缓冲池大小。如果缓冲池较小或数据频繁从内存中逐出，AHI 的收益可能有限。可以通过配置innodb_adaptive_hash_index禁用。 ↩︎