MySQL 系列 #01：MySQL 简介

MySQL 是世界上最流行的开源关系型数据库，由 Oracle 维护。以高性能、高可靠、易部署著称，是 Web 应用的首选数据库。 InnoDB 存储引擎（默认）支持事务、外键、行级锁，是生产环境的标准选择。

⚠️ 版本说明：本文以 MySQL 8.0 为基准。8.0 相比 5.7 引入了窗口函数、CTE、降序索引、JSON 增强等重要特性。

📎 关联文档：MySQL数据类型×Java类型映射 — 字段类型选型与 Java 映射详细说明。

目录

核心架构

InnoDB 核心组件

三大日志

安装与配置

安装

# macOS
brew install mysql@8.0
brew services start mysql@8.0

# 安全初始化（设置 root 密码）
mysql_secure_installation

# 验证
mysql -u root -p
mysql> SELECT VERSION();

常用 my.cnf 配置

[mysqld]
# 基础
port            = 3306
character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci

# InnoDB
innodb_buffer_pool_size = 1G          # 建议设为物理内存的 50%~75%
innodb_buffer_pool_instances = 4      # buffer pool 实例数，建议 CPU 核数
innodb_flush_log_at_trx_commit = 1    # 1=最安全(每次提交刷盘), 2=性能好但崩溃可能丢1秒数据
innodb_file_per_table = ON            # 每张表独立 ibd 文件
innodb_log_file_size = 256M           # Redo Log 单文件大小

# 连接
max_connections = 500
wait_timeout    = 600
interactive_timeout = 600

# 慢查询
slow_query_log  = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1                   # 超过 1 秒记录慢查询
log_queries_not_using_indexes = ON    # 未命中索引的查询也记录

# Binlog（主从复制必需）
server-id   = 1
log-bin     = mysql-bin
binlog_format = ROW
expire_logs_days = 7

常用连接与管理命令

# 连接
mysql -h 127.0.0.1 -P 3306 -u root -p
mysql -u root -p mydb < dump.sql     # 导入 SQL 文件

# 查看状态
SHOW STATUS LIKE 'Threads_connected';  -- 当前连接数
SHOW STATUS LIKE 'Innodb_buffer_pool%'; -- Buffer Pool 命中率
SHOW PROCESSLIST;                       -- 查看当前连接和执行的 SQL
SHOW ENGINE INNODB STATUS\G            -- InnoDB 详细状态

# 数据库操作
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
DROP DATABASE mydb;
USE mydb;
SHOW DATABASES;
SHOW TABLES;
DESCRIBE users;          -- 查看表结构
SHOW CREATE TABLE users; -- 查看建表语句

存储引擎

InnoDB vs MyISAM

结论：新建表一律使用 InnoDB，MyISAM 已无使用场景。

索引

B+ 树原理

InnoDB 使用 B+ 树作为索引结构：
- 所有数据存储在叶节点（Leaf Node）
- 叶节点通过双向链表连接，支持范围查询
- 非叶节点只存索引键，不存数据
- 树高通常为 3~4 层，千万级数据 3 次 IO 即可定位

主键索引（聚簇索引）：叶节点存完整行数据
二级索引（非聚簇）：叶节点存主键值，查完整行需"回表"

索引类型

-- 主键索引（自动创建）
PRIMARY KEY (id)

-- 唯一索引
CREATE UNIQUE INDEX uq_email ON users (email);

-- 普通索引
CREATE INDEX idx_status ON users (status);

-- 复合索引（遵循最左前缀原则）
CREATE INDEX idx_user_status_time ON orders (user_id, status, created_at);

-- 前缀索引（长字符串节省空间）
CREATE INDEX idx_email_prefix ON users (email(20));

-- 全文索引（5.6+ InnoDB 支持，中文需配合 ngram 解析器）
CREATE FULLTEXT INDEX ft_content ON articles (title, content) WITH PARSER ngram;

覆盖索引

-- 查询字段全部在索引中，无需回表（EXPLAIN 中 Extra 显示 Using index）
-- 复合索引 (user_id, status, amount)
SELECT user_id, status, amount
FROM orders
WHERE user_id = 1 AND status = 'paid';
-- ↑ 三列均在索引中，不需要回表

索引失效场景

-- 1. 对索引列使用函数或运算
WHERE YEAR(created_at) = 2024          -- ❌ 失效
WHERE created_at >= '2024-01-01'       -- ✅ 有效

-- 2. 隐式类型转换（字符串列传入数字）
WHERE phone = 13800138000              -- ❌ phone 是 VARCHAR，传入数字会失效
WHERE phone = '13800138000'            -- ✅ 有效

-- 3. LIKE 前缀通配符
WHERE name LIKE '%张%'                 -- ❌ 失效
WHERE name LIKE '张%'                  -- ✅ 有效

-- 4. 违反最左前缀原则
-- 复合索引 (a, b, c)
WHERE b = 1 AND c = 2                  -- ❌ 跳过 a，失效
WHERE a = 1 AND c = 2                  -- ⚠️ 只用到 a 列
WHERE a = 1 AND b = 2                  -- ✅ 命中 a, b

-- 5. OR 条件中有非索引列
WHERE age = 25 OR name = '张三'        -- ❌ name 无索引则整体失效

-- 6. NOT IN / NOT EXISTS（通常不走索引）
WHERE status NOT IN (0, 2)             -- ❌ 一般失效

事务与锁

隔离级别

MySQL InnoDB 在 REPEATABLE READ 下通过 Next-Key Lock 解决了大部分幻读问题。

-- 查看和设置隔离级别
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 事务操作
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 或 ROLLBACK;

锁类型

-- 手动加锁
SELECT * FROM orders WHERE id = 1 FOR UPDATE;      -- 排他锁（写锁）
SELECT * FROM orders WHERE id = 1 LOCK IN SHARE MODE; -- 共享锁（读锁）

-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits\G
-- 或
SHOW ENGINE INNODB STATUS\G   -- 查看 TRANSACTIONS 段

MVCC（多版本并发控制）

每行数据有两个隐藏字段：
  DB_TRX_ID：最近修改该行的事务 ID
  DB_ROLL_PTR：指向 Undo Log 的指针

读操作（快照读）：根据事务开始时的 ReadView，
  通过 Undo Log 链找到对应版本，不加锁，高并发

写操作（当前读）：SELECT...FOR UPDATE、UPDATE、DELETE
  读取最新版本，加锁

死锁处理

-- 查看最近一次死锁信息
SHOW ENGINE INNODB STATUS\G   -- 查看 LATEST DETECTED DEADLOCK 段

-- 死锁预防原则：
-- 1. 固定加锁顺序（所有事务按相同顺序访问表/行）
-- 2. 缩短事务，减少持锁时间
-- 3. 大事务拆小事务
-- 4. 避免在事务中与用户交互

SQL 优化

EXPLAIN 解读

EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid' AND u.age > 25;

-- 开启 Optimizer Trace（查看优化器决策过程）
SET optimizer_trace = 'enabled=on';
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace = 'enabled=off';

慢查询分析

# 查看慢查询日志
tail -f /var/log/mysql/slow.log

# 用 mysqldumpslow 汇总分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log  # 按时间排序，取 top 10
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log  # 按次数排序

常见优化手段

-- 1. 分页优化：大偏移量用子查询 + 主键
-- 慢（扫描 10020 行）
SELECT * FROM orders ORDER BY id LIMIT 10000, 20;
-- 快（先定位主键，再取数据）
SELECT * FROM orders WHERE id >= (
    SELECT id FROM orders ORDER BY id LIMIT 10000, 1
) ORDER BY id LIMIT 20;

-- 2. 避免 SELECT *，只查需要的列
SELECT id, status, amount FROM orders WHERE user_id = 1;

-- 3. 用 EXISTS 替代 IN（子查询返回大量数据时）
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 4. 批量写入替代逐条插入
INSERT INTO logs (user_id, action, created_at) VALUES
    (1, 'login', NOW()),
    (2, 'purchase', NOW()),
    (3, 'logout', NOW());

-- 5. 强制使用/忽略索引（调试用）
SELECT * FROM orders USE INDEX (idx_user_id) WHERE user_id = 1;
SELECT * FROM orders IGNORE INDEX (idx_status) WHERE status = 'paid';

主从复制

复制原理

主库（Master）                副库（Slave）
    ↓ 写操作
Binlog（ROW 格式）  →→→→→→  IO Thread 读取 Binlog
                              ↓
                           Relay Log（中继日志）
                              ↓
                           SQL Thread 回放
                              ↓
                           从库数据同步

-- 主库：查看 Binlog 状态
SHOW MASTER STATUS;
SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 10;

-- 从库：查看复制状态
SHOW SLAVE STATUS\G
-- 关注：
--   Slave_IO_Running: Yes
--   Slave_SQL_Running: Yes
--   Seconds_Behind_Master: 0  （复制延迟秒数）

读写分离注意事项

写操作 → 主库
读操作 → 从库（注意主从延迟！）

主从延迟场景下读从库可能读到旧数据，解决方案：
1. 强一致性读（如余额）→ 强制走主库
2. 等待从库追上（Semi-sync 半同步复制）
3. 业务层容忍短暂延迟

备份与恢复

# mysqldump 逻辑备份（适合中小型数据库）
mysqldump -u root -p \
  --single-transaction \     # InnoDB 一致性快照，不锁表
  --master-data=2 \          # 记录 Binlog 位点（用于增量恢复）
  --routines --triggers \    # 包含存储过程和触发器
  mydb > mydb_backup.sql

# 恢复
mysql -u root -p mydb < mydb_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases --single-transaction > all_backup.sql

# 使用 Binlog 做增量恢复（从某个位点开始）
mysqlbinlog --start-position=154 \
  --stop-datetime="2024-01-15 12:00:00" \
  mysql-bin.000001 | mysql -u root -p

# xtrabackup 物理热备（大型数据库推荐，速度快）
xtrabackup --backup --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full

快速参考卡

常用管理命令

Buffer Pool 命中率计算

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_reads：从磁盘读取的次数
-- Innodb_buffer_pool_read_requests：总读取请求次数
-- 命中率 = 1 - reads / read_requests，正常应 > 99%

数据类型速查（简版）

详细类型映射见 MySQL数据类型×Java类型映射

参考资料

• MySQL 8.0 官方文档
• MySQL 是怎样运行的（小孩子4919）
• 高性能 MySQL（第四版）
• Use The Index, Luke