Quartz 5

数据库导学（Day48）

Properties1
tags来源, 数据库, mysql, 关系型, nosql, sql, 存储引擎, 事务, 索引, data

14分钟阅读

数据库导学（Day48）

主题：数据库本质（socket + 文件管理）、库/表/记录与文件系统对应、关系型 vs 非关系型数据库、MySQL 部署方式、基本管理、SQL 分类、权限管理、存储引擎、索引与慢查询优化、事务 ACID、并发读现象与隔离级别、锁与 MVCC、日志管理、备份恢复、主从复制、高可用与读写分离、数据库优化全景

结构总览

数据库导学（MySQL 学习周期路线图）
  ├─ 数据库介绍
  │    ├─ MySQL = socket 服务端程序 + 本地文件管理
  │    ├─ 库=文件夹 / 表=文件 / 记录=行 / 字段=列
  │    └─ RDBMS（二维表+外键+ACID）vs NoSQL（键值/文档/列族/图）
  ├─ MySQL 部署
  │    ├─ MariaDB（CentOS 7 默认源）/ 官方源（生产推荐）/ Windows（开发）
  │    └─ 多实例：同一 mysqld 多进程、不同端口、独立数据目录
  ├─ 基本管理：systemctl 启停 / 密码设置 / mysql 客户端连接 / utf8mb4
  ├─ SQL 语句：DDL / DML / DQL / DCL + 库表记录 CRUD + 视图/触发器/存储过程/函数
  ├─ 权限管理：mysql.user / db / tables_priv / columns_priv + GRANT / REVOKE
  ├─ 存储引擎：InnoDB（默认）/ MyISAM / Memory + Buffer Pool + Redo/Undo/Binlog
  ├─ 索引与慢查询：B+树、聚集 vs 辅助索引、覆盖索引/回表、最左前缀、EXPLAIN、慢查询日志
  ├─ 事务：ACID 四特性
  ├─ 并发读现象：脏读 / 不可重复读 / 幻读 + 四种隔离级别 + 间隙锁/Next-Key Lock
  ├─ 锁机制与 MVCC：行锁/表锁、共享/排他锁、死锁、快照读/当前读
  ├─ 日志管理：错误日志 / 慢查询日志 / Binlog / Redo Log
  ├─ 备份恢复：mysqldump（逻辑） vs xtrabackup（物理）
  ├─ 主从复制：Binlog → IO线程 → Relay Log → SQL线程；异步/半同步；一主一从/一主多从/双主
  ├─ 高可用与读写分离：MHA 故障切换 / Atlas 读写分离 / MyCat 分库分表
  └─ 数据库优化：硬件 / 系统 / 配置 / SQL 四层面

关键要点

数据库本质： MySQL 数据库管理软件本质上是一个 socket 套接字程序，核心作用是管理本地的文件数据。用户通过客户端连接 MySQL 服务端，发送 SQL 指令，MySQL 将这些指令转化为对本地文件的读写操作。核心管理单位与文件系统对应：库（Database）= 文件夹、表（Table）= 文件（一张表对应一个或多个文件，取决于存储引擎）、记录（Record）= 文件内一行、字段（Field）= 文件内一列。

RDBMS vs NoSQL： 关系型数据库以二维表组织数据，表间通过外键关联，有固定 Schema，支持 ACID 事务，通过 SQL 操作，数据存硬盘（MySQL、PostgreSQL、Oracle、SQL Server、MariaDB）。非关系型数据库不采用二维表，以键值对、文档、列族、图等形式存储，结构灵活无需预定义 Schema，事务支持较弱或没有，读写性能高适合高并发，部分存内存（Redis 键值对、MongoDB 文档、Cassandra 列族、Neo4j 图）。

部署方式： MariaDB（CentOS 7 默认源，兼容性好）、MySQL 官方源（生产环境推荐）、Windows 部署（开发环境学 SQL 用）、多实例部署（单机多服务：同一台机器用同一个 mysqld 启动多个进程，各监听不同端口如 3306/3307/3308，各自独立数据目录与配置文件，适合资源有限但需隔离的场景）。

基本管理： systemctl start/stop mysqld 或 mysqld_safe；首次安装设置 root 密码；客户端连接 mysql -uroot -p -h 127.0.0.1 -P 3306；字符编码统一 utf8mb4（支持 emoji 与完整 Unicode，生产标准）。常用快捷命令：SHOW DATABASES / USE / SHOW TABLES / DESC / SHOW CREATE TABLE / STATUS / \q。

SQL 四大分类： DDL 数据定义语言（CREATE/ALTER/DROP）、DML 数据操作语言（INSERT/UPDATE/DELETE）、DQL 数据查询语言（SELECT）、DCL 数据控制语言（GRANT/REVOKE）。库/表/记录均有增删改查（CRUD）。高级机制：视图（虚拟表简化查询）、触发器（表操作前后自动执行，审计/同步）、存储过程（预编译 SQL 块封装业务）、函数（返回值 SQL 块）、流程控制（IF/CASE/LOOP/WHILE）。

权限管理： 权限信息存于系统表：mysql.user（用户账号+全局权限）、mysql.db（库级）、mysql.tables_priv（表级）、mysql.columns_priv（字段级）。核心命令：CREATE USER / GRANT（如 GRANT SELECT, INSERT ON db1.t1 TO 'user'@'host'）/ SHOW GRANTS / REVOKE / DROP USER / FLUSH PRIVILEGES。权限粒度可细化到数据库、表、字段级别。

存储引擎： 存储引擎是 MySQL 与本地文件之间的”翻译层”，决定数据如何存储、索引如何建立、是否支持事务（类比 Word 软件选择不同字体/排版引擎）。InnoDB（默认）支持事务/外键/行级锁/崩溃恢复；MyISAM 不支持事务，表锁，适合读多写少；Memory 存内存，用于临时表/缓存。InnoDB 核心组件： Buffer Pool（缓存池，存储索引与数据页，性能优化核心，生产可分配 60%~70% 物理内存）、Redo Log（重做日志，崩溃恢复，持久性）、Undo Log（回滚日志，事务回滚，MVCC）、Binlog（二进制日志，主从复制与数据恢复）。存储单位层级：Row（行，最小单位）→ Page（页，默认 16KB，读写以页为单位）→ Extent（区，64 连续页约 1MB）→ Segment（段，每索引/表一个）→ Tablespace（表空间，逻辑最高层）。

索引与慢查询优化： 索引是加速查询的数据结构（类比书的目录）。InnoDB 用 B+树（所有数据存叶子节点，叶子间链表连接，范围查询高效；对比二叉树/AVL/B树）。聚集索引叶子存完整行数据（InnoDB 主键即聚集索引）；辅助索引叶子存主键值，查询需回表。覆盖索引 = 查询所需数据已在辅助索引中，无需回表。联合索引 (a,b,c) 遵循最左前缀匹配：查询须从最左列开始（WHERE b=2、WHERE a=1 AND c=3 不能用索引）。EXPLAIN 分析执行计划：type 访问类型（ALL > index > range > ref > eq_ref > const 性能递增）、possible_keys/key/rows/Extra（Using index = 覆盖索引，Using where = 需回表）。慢查询日志：SET GLOBAL slow_query_log=ON; SET GLOBAL long_query_time=2; 定位超时 SQL。索引使用原则：WHERE 频繁列、JOIN 关联列、区分度高列、避免索引列函数运算、最左前缀。

事务 ACID： 事务是一组 SQL 的逻辑单元，要么全部成功要么全部失败。原子性（Atomicity，全部成功或全部回滚）、一致性（Consistency，从一个一致状态到另一一致状态）、隔离性（Isolation，并发事务互不干扰）、持久性（Durability，提交后修改永久，崩溃不丢失）。示例：START TRANSACTION → UPDATE 转账 → COMMIT / ROLLBACK。

并发读现象与隔离级别： 三种现象：脏读（读到另一事务未提交数据，对方回滚则读到无效值）、不可重复读（同一事务内多次读同一行结果不一致，因期间其他事务修改提交）、幻读（同一事务内多次查询结果集行数不一致，因期间其他事务插入/删除满足条件的行）。四种隔离级别（从上到下越来越严、并发性能越来越低）：READ UNCOMMITTED（读未提交，三现象都可能）→ READ COMMITTED（读已提交，避免脏读）→ REPEATABLE READ（可重复读，避免脏读+不可重复读）→ SERIALIZABLE（串行化，全部避免）。InnoDB 默认 RR，通过**间隙锁（Gap Lock）**和 **Next-Key Lock（间隙锁+行锁）**解决幻读。

锁机制与 MVCC： 锁粒度：行锁（InnoDB）/表锁（MyISAM）/页锁。锁模式：共享锁 S（可读不可写）、排他锁 X（不可读不可写）。锁算法：记录锁、间隙锁、Next-Key Lock（InnoDB 特有）。死锁 = 两个事务互相等待对方释放锁（如 A 锁记录1请求记录2，B 锁记录2请求记录1）。解决：统一访问顺序、优化索引减少锁范围、拆分事务减少持锁时间。MVCC（多版本并发控制）：快照读（读历史版本，不加锁，一致性读）vs 当前读（读最新版本，需加锁）；通过 Undo Log 保存历史版本，使读不等待写释放锁，大幅提高并发性能。

四种核心日志： 错误日志（启动/运行/停止错误，故障排查首要依据）、慢查询日志（超阈值 SQL，性能优化数据来源）、Binlog（记录数据变更 INSERT/UPDATE/DELETE，主从复制与数据恢复）、Redo Log（数据页物理修改，崩溃恢复保证持久性）。面试高频：Binlog 与 Redo Log 区别——Binlog 记录逻辑变更、用于主从/恢复；Redo Log 记录物理修改、用于崩溃恢复，两者职责不同。

备份与恢复： 逻辑备份 mysqldump（SQL 语句，恢复慢，适合小数据量/迁移：mysqldump -uroot -p --all-databases > all.sql，恢复 mysql -uroot -p < all.sql）；物理备份 xtrabackup（数据文件，恢复快，适合大数据量/生产：xtrabackup --backup --target-dir=/backup/full，--copy-back 恢复）。备份是数据库运维的最后一道防线。

主从复制： 原理流程：主库写 Binlog → 从库 IO 线程连主库请求 → 主库 dump 线程发送 Binlog → 从库 IO 线程写入中继日志 Relay Log → 从库 SQL 线程执行同步。Binlog 三种格式：STATEMENT（记录 SQL 语句，日志量小但非确定性函数可能主从不一致）/ ROW（行级变更，日志量大但最安全）/ MIXED（自动选择）。两种方式：异步复制（主库不等从库确认，性能高可能丢数据）vs 半同步复制（主库等至少一个从库确认，一致性高性能略低）。架构：一主一从（读写分离入门）、一主多从（读多写少）、双主/主主（高可用，配 Keepalived）、双主+多从+Keepalived（生产高可用方案）。

高可用与读写分离： MHA（Master High Availability）：故障检测、自动选举新 Master、数据补偿（从其他从库获取最新数据）、VIP 漂移（新 Master 接管 VIP 应用无感知）。Atlas 读写分离中间件：对外统一地址，写请求分发主库、读请求分发从库。MyCat 分布式数据库中间件：读写分离 + 分库分表（数据水平拆分突破单库瓶颈）+ 多租户 + 全局序列（分布式唯一 ID）。

数据库优化四层面： 硬件（CPU/内存/SSD 选型）、系统（内核参数/文件系统）、配置（innodb_buffer_pool_size 建议 60%~70% 物理内存、innodb_log_file_size 影响恢复速度、innodb_flush_log_at_trx_commit Redo 刷盘策略 0/1/2、sync_binlog Binlog 刷盘策略 0/1/N）、SQL（索引/查询语句/执行计划分析）。

相关页面

最近更新

关系图谱

反向链接