MySQL 数据库从入门到精通：核心知识点全覆盖 🗄️

MySQL 是世界上最受欢迎的开源关系型数据库之一，凭借其高性能、高可靠性和易用性，广泛应用于各类 Web 应用和企业级系统。本文将带你系统掌握 MySQL 的核心知识点，从基础操作到性能优化，图文并茂，干货满满！💪

---

📚 目录导航

• 一、MySQL 概述与架构
• 二、数据类型详解
• 三、数据库与表的基本操作
• 四、CRUD 增删改查
• 五、索引：数据库的性能之钥
• 六、事务：数据一致性的守护者
• 七、查询优化与执行计划
• 八、备份与恢复
• 九、总结与知识框架

---

一、MySQL 概述与架构

1.1 什么是 MySQL？

MySQL 是一款开源的关系型数据库管理系统（RDBMS），由瑞典 MySQL AB 公司开发，现属于 Oracle 旗下。它使用 SQL（Structured Query Language） 作为操作语言，支持多线程、多用户，能够处理大量数据。

1.2 MySQL 的整体架构

MySQL 采用插件式存储引擎架构，这种设计让它在灵活性上表现出色。我们可以通过一张架构图来理解 MySQL 的工作层次：

1.3 核心存储引擎对比

MySQL 支持多种存储引擎，不同引擎在功能特性上差异明显：

特性	InnoDB	MyISAM	Memory
事务支持	✅ 支持	❌ 不支持	❌ 不支持
行级锁	✅ 支持	❌ 表级锁	❌ 表级锁
外键约束	✅ 支持	❌ 不支持	❌ 不支持
崩溃恢复	✅ 支持	❌ 不支持	❌ 不支持
全文索引	✅ 支持（5.6+）	✅ 支持	❌ 不支持
存储限制	64TB	256TB	受 RAM 限制
适用场景	事务业务、高并发	只读/静态表	临时表、缓存

💡 推荐：日常业务开发首选 InnoDB，它是 MySQL 5.5 之后的默认引擎，支持事务和行锁，功能全面。

1.4 连接 MySQL 的几种方式

# 方式一：命令行客户端连接
mysql -u root -p

# 方式二：指定主机和端口
mysql -h 192.168.1.100 -P 3306 -u root -p

# 方式三：连接后选择数据库
mysql -u root -p
USE my_database;
SHOW TABLES;

-- 查看 MySQL 版本和状态
SELECT VERSION();
SHOW STATUS;
SHOW VARIABLES LIKE 'version%';

---

二、数据类型详解

MySQL 支持多种数据类型，合理选择数据类型不仅能节省存储空间，还能提升查询性能。

2.1 数值类型

类型	大小	范围	用途
TINYINT	1 字节	-128 ~ 127 或 0 ~ 255	小整数，如年龄、状态码
SMALLINT	2 字节	-32768 ~ 32767	中等整数
INT / INTEGER	4 字节	-21亿 ~ 21亿	常规整数，最常用
BIGINT	8 字节	极大范围	大整数，如 ID
FLOAT	4 字节	浮点数	单精度
DOUBLE	8 字节	浮点数	双精度，常用
DECIMAL(M,D)	变长	精确小数	金额、精度要求高的数据

-- 示例：创建用户表，展示数值类型的使用
CREATE TABLE user (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    age TINYINT UNSIGNED COMMENT '年龄（0-255）',
    balance DECIMAL(10, 2) COMMENT '账户余额（精确到分）',
    score DOUBLE COMMENT '评分（可有小数）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.2 字符串类型

类型	最大长度	特点
CHAR(N)	255 字符	固定长度，不足补空格；适合定长内容如手机号
VARCHAR(N)	16383 字符	变长，最常用；节省空间
TEXT	65535 字节	长文本，如文章内容
MEDIUMTEXT	16MB	中等长文本
LONGTEXT	4GB	超长文本

-- 示例：创建文章表，展示字符串类型
CREATE TABLE article (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL COMMENT '文章标题',
    content TEXT COMMENT '文章内容',
    author CHAR(18) COMMENT '作者身份证号（定长）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- VARCHAR vs CHAR 性能对比
-- VARCHAR：存储变长，节省空间，但写入时可能需要额外操作
-- CHAR：固定长度，查询快，但浪费空间；适合长度固定的场景

💡 建议：对于中文内容，一定要使用 utf8mb4 字符集，一个中文占 4 字节，utf8 只支持 3 字节可能导致表情符号存储失败。

2.3 日期时间类型

类型	格式	范围
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-31
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 ~ 9999-12-31 23:59:59
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 ~ 2038-01-19
YEAR	YYYY	1901 ~ 2155

-- 示例：订单表，展示日期时间类型
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL COMMENT '订单号',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    pay_time DATE COMMENT '支付日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- TIMESTAMP vs DATETIME 的关键区别：
-- 1. TIMESTAMP 占用 4 字节，DATETIME 占用 8 字节
-- 2. TIMESTAMP 会自动转换时区，DATETIME 不会
-- 3. TIMESTAMP 范围有限（到 2038 年），DATETIME 范围更大

2.4 枚举与集合类型

-- ENUM：单选，从预定义列表中选择一个值
CREATE TABLE product (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    size ENUM('S', 'M', 'L', 'XL') COMMENT '尺码',
    color ENUM('red', 'green', 'blue', 'black', 'white') COMMENT '颜色',
    status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending'
) ENGINE=InnoDB;

-- SET：多选，从预定义列表中选择多个值
CREATE TABLE user_hobby (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(50),
    hobbies SET('reading', 'sports', 'music', 'travel', 'coding') COMMENT '爱好（可多选）'
) ENGINE=InnoDB;

---

三、数据库与表的基本操作

3.1 数据库操作

-- 创建数据库
CREATE DATABASE IF NOT EXISTS my_blog DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 查看所有数据库
SHOW DATABASES;

-- 选择数据库
USE my_blog;

-- 查看当前数据库
SELECT DATABASE();

-- 删除数据库（谨慎操作！）
DROP DATABASE IF EXISTS my_blog;

3.2 表的创建与修改

-- 创建一个完整的用户表
CREATE TABLE IF NOT EXISTS users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    password VARCHAR(255) NOT NULL COMMENT '密码（加密存储）',
    phone CHAR(11) COMMENT '手机号',
    avatar VARCHAR(500) DEFAULT '/static/img/default.png' COMMENT '头像URL',
    status TINYINT DEFAULT 1 COMMENT '状态：1-正常，0-禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    -- 添加索引
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 查看表结构
DESC users;
DESCRIBE users;

-- 查看建表语句（完整信息）
SHOW CREATE TABLE users;

-- 修改表：添加新字段
ALTER TABLE users ADD COLUMN last_login_time DATETIME COMMENT '最后登录时间' AFTER created_at;

-- 修改表：修改字段
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) COMMENT '手机号（可变长）';

-- 修改表：删除字段
ALTER TABLE users DROP COLUMN avatar;

-- 修改表：添加索引
ALTER TABLE users ADD INDEX idx_phone (phone);

-- 修改表：重命名表
ALTER TABLE users RENAME TO user_info;

-- 删除表（谨慎操作！）
DROP TABLE IF EXISTS users;

3.3 约束详解

约束是用来限制字段取值合法性的规则，是保证数据完整性的重要手段：

-- 创建带完整约束的订单表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(64) NOT NULL UNIQUE COMMENT '订单号',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    -- 外键约束：关联用户表
    FOREIGN KEY (user_id) REFERENCES users(id) 
        ON DELETE RESTRICT 
        ON UPDATE CASCADE,
    
    -- 检查约束：金额必须大于等于 0（MySQL 8.0.16+）
    CHECK (total_amount >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 约束相关的查看与删除
SHOW CREATE TABLE orders;
ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;
ALTER TABLE orders DROP CHECK orders_chk_1;

---

四、CRUD 增删改查

4.1 插入数据（INSERT）

-- 方式一：插入单条数据
INSERT INTO users (username, email, password, phone) 
VALUES ('zhangsan', 'zhangsan@example.com', 'hashed_password', '13800138000');

-- 方式二：插入多条数据（效率更高）
INSERT INTO users (username, email, password, phone) VALUES
('lisi', 'lisi@example.com', 'pass123', '13800138001'),
('wangwu', 'wangwu@example.com', 'pass456', '13800138002'),
('zhaoliu', 'zhaoliu@example.com', 'pass789', '13800138003');

-- 方式三：直接插入完整数据（省略字段名）
INSERT INTO users VALUES (NULL, 'tianqi', 'tianqi@example.com', 'pass000', '13800138004', '/avatar.png', 1, NOW(), NOW());

-- 方式四：插入或更新（主键/唯一冲突时更新）
INSERT INTO users (id, username, email, password) 
VALUES (1, 'updated_user', 'new@example.com', 'new_pass')
ON DUPLICATE KEY UPDATE 
    username = VALUES(username),
    email = VALUES(email),
    password = VALUES(password);

-- 方式五：蠕虫复制（从已有表复制数据）
INSERT INTO users_backup SELECT * FROM users WHERE created_at > '2026-01-01';

4.2 查询数据（SELECT）

-- 基本查询
SELECT * FROM users;

-- 条件查询
SELECT username, email FROM users WHERE status = 1 AND id > 10;

-- 排序查询
SELECT * FROM users ORDER BY created_at DESC, id ASC;

-- 分页查询（重要！）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;  -- 第一页，每页10条
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10; -- 第二页

-- 简化写法：LIMIT start, count
SELECT * FROM users ORDER BY id LIMIT 0, 10;
SELECT * FROM users ORDER BY id LIMIT 10, 10;

-- 聚合查询
SELECT 
    COUNT(*) AS total_users,
    COUNT(DISTINCT status) AS status_types,
    MAX(created_at) AS latest_registration,
    MIN(created_at) AS earliest_registration,
    AVG(id) AS avg_id
FROM users;

-- 分组查询 + 聚合函数
SELECT 
    status,
    COUNT(*) AS user_count,
    MAX(created_at) AS last_time
FROM users 
GROUP BY status 
HAVING user_count > 5;  -- HAVING 对分组后的结果进行过滤

-- 子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 1000);

-- 关联查询：多表联合
SELECT 
    u.username,
    o.order_no,
    o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC;

4.3 更新数据（UPDATE）

-- 更新单个字段
UPDATE users SET phone = '13900139000' WHERE id = 1;

-- 更新多个字段
UPDATE users 
SET 
    email = 'new_email@example.com',
    status = 0,
    updated_at = NOW()
WHERE id = 1;

-- 批量更新（谨慎使用，最好加条件）
UPDATE users SET status = 1 WHERE created_at > '2026-01-01';

-- 使用 CASE 进行条件更新
UPDATE orders 
SET 
    status = CASE 
        WHEN status = 'pending' AND pay_time < DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN 'cancelled'
        WHEN status = 'shipped' AND delivery_time < DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 'completed'
        ELSE status
    END
WHERE status IN ('pending', 'shipped');

4.4 删除数据（DELETE）

-- 删除指定数据
DELETE FROM users WHERE id = 10;

-- 删除前N条（配合排序使用）
DELETE FROM users ORDER BY created_at ASC LIMIT 5;

-- 截断表（删除全部数据，速度极快，但无法恢复）
TRUNCATE TABLE users;

-- 清空用户表并重置自增ID
TRUNCATE TABLE users;

-- DELETE vs TRUNCATE 区别
-- 1. DELETE 可加 WHERE 条件，TRUNCATE 不行
-- 2. DELETE 记录删除日志（一行一行删除），TRUNCATE 不记录
-- 3. DELETE 不会重置自增ID，TRUNCATE 会
-- 4. DELETE 可触发触发器，TRUNCATE 不会
-- 5. TRUNCATE 更快，DELETE 更安全

4.5 关联查询详解

关联查询是 MySQL 中最核心也最复杂的查询方式：

-- 示例表结构
-- users: id, username, email
-- orders: id, order_no, user_id, total_amount, status
-- order_items: id, order_id, product_name, quantity, price

-- INNER JOIN：只返回两边匹配到的记录
SELECT 
    u.username,
    o.order_no,
    o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid';

-- LEFT JOIN：返回左表所有记录，右表没有匹配的显示 NULL
SELECT 
    u.username,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY total_spent DESC;

-- 多表关联查询
SELECT 
    u.username,
    o.order_no,
    oi.product_name,
    oi.quantity,
    oi.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'paid' AND oi.quantity > 2
ORDER BY o.created_at DESC;

---

五、索引：数据库的性能之钥

5.1 索引的概念与原理

索引是 MySQL 中用于加速数据检索的数据结构，可以类比为书籍的目录。创建合适的索引能让查询从”遍历全表”变成”直接定位”。

5.2 MySQL 索引类型

索引类型	关键字	说明	示例
主键索引	PRIMARY KEY	每表只有一个，不允许 NULL，自动唯一	PRIMARY KEY(id)
唯一索引	UNIQUE	字段值唯一，允许 NULL	UNIQUE idx_email(email)
普通索引	INDEX / KEY	普通索引，无唯一性约束	INDEX idx_name(name)
全文索引	FULLTEXT	用于全文搜索，只支持 CHAR、VARCHAR、TEXT	FULLTEXT idx_content(content)
组合索引	INDEX	多字段组合，需要遵循最左前缀原则	INDEX idx_a_b_c(a, b, c)

5.3 创建索引的示例

-- 创建表时直接添加索引
CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    category_id BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    description TEXT,
    created_at DATETIME,
    
    -- 普通索引
    INDEX idx_category (category_id),
    INDEX idx_created (created_at),
    
    -- 组合索引（遵循最左前缀原则）
    INDEX idx_cat_price (category_id, price),
    
    -- 全文索引
    FULLTEXT INDEX ft_name_desc (name, description)
) ENGINE=InnoDB;

-- 已存在表添加索引
CREATE INDEX idx_name ON products(name);
CREATE INDEX idx_cat_price ON products(category_id, price);
CREATE FULLTEXT INDEX ft_desc ON products(description);

-- 查看表的索引
SHOW INDEX FROM products;

-- 删除索引
DROP INDEX idx_name ON products;
DROP INDEX idx_created ON products;

5.4 最左前缀原则（组合索引核心）

组合索引 (a, b, c) 的查询效率如下：

-- 创建组合索引
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 这些查询能用到索引（最左前缀原则）
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01';
EXPLAIN SELECT * FROM orders WHERE status = 'paid' AND created_at BETWEEN '2026-01-01' AND '2026-06-01';

-- 这些查询无法用到索引
EXPLAIN SELECT * FROM orders WHERE created_at > '2026-01-01';  -- 跳过最左列
EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01' AND status = 'paid';  -- 顺序颠倒

5.5 索引使用建议

场景	建议
WHERE 条件字段	频繁出现在 WHERE 子句中的字段建索引
ORDER BY 字段	排序字段建索引可避免filesort
JOIN 连接字段	外键字段建索引，加快连接速度
高基数列	选择性高的列（值分布广）更适合建索引
低基数列	如性别、状态，不适合单独建索引
组合索引顺序	将选择性高的列放前面

⚠️ 注意：索引不是越多越好！每个索引都会占用磁盘空间，且增删改时需要维护索引，降低写操作性能。

---

六、事务：数据一致性的守护者

6.1 事务的概念

事务（Transaction）是一组原子性的 SQL 操作，要么全部成功，要么全部失败。事务是数据库区别于文件系统的关键特性之一。

6.2 事务的四大特性（ACID）

6.3 事务的隔离级别

MySQL 支持四种事务隔离级别，越高的级别数据越安全，但性能越差：

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	✅ 可能	✅ 可能	✅ 可能	最快
READ COMMITTED	❌ 不可能	✅ 可能	✅ 可能	较快
REPEATABLE READ（默认）	❌ 不可能	❌ 不可能	✅ 可能	一般
SERIALIZABLE	❌ 不可能	❌ 不可能	❌ 不可能	最慢

-- 查看当前会话隔离级别
SELECT @@tx_isolation;
SELECT @@transaction_isolation;

-- 设置隔离级别（会话级别）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置隔离级别（全局级别）
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 开启事务
START TRANSACTION;  -- 或 BEGIN;

-- 转账示例：原子性操作的典型场景
START TRANSACTION;

-- 从 A 账户扣款
UPDATE accounts SET balance = balance - 1000 WHERE user_id = 1 AND balance >= 1000;

-- 给 B 账户充值
UPDATE accounts SET balance = balance + 1000 WHERE user_id = 2;

-- 判断操作是否成功，决定提交或回滚
-- 如果 A 余额不足，第一条 UPDATE 影响行数为 0，此时应该回滚
IF (@@row_count = 0) THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

6.4 并发事务带来的问题

-- 脏读示例（READ UNCOMMITTED 级别）
-- Session 1:
BEGIN;
UPDATE users SET status = 0 WHERE id = 1;  -- 未提交
-- Session 2:
SELECT status FROM users WHERE id = 1;  -- 读到脏数据：status = 0
-- Session 1:
ROLLBACK;  -- 数据回滚，但 Session 2 已经基于错误数据做了判断

-- 不可重复读示例（READ COMMITTED 级别）
-- Session 1:
BEGIN;
SELECT balance FROM accounts WHERE user_id = 1;  -- 第一次读取：1000
-- Session 2（在另一个连接中）:
UPDATE accounts SET balance = 2000 WHERE user_id = 1;
COMMIT;
-- Session 1:
SELECT balance FROM accounts WHERE user_id = 1;  -- 第二次读取：2000，数据变了！

-- 幻读示例（REPEATABLE READ 级别）
-- Session 1:
BEGIN;
SELECT * FROM orders WHERE status = 'pending';  -- 第一次查询：3 条
-- Session 2:
INSERT INTO orders VALUES (4, 'pending');  -- 插入新订单
COMMIT;
-- Session 1:
UPDATE orders SET status = 'cancelled' WHERE status = 'pending';  -- 更新所有待处理订单
-- 此时可能：原 3 条被更新 + 新插入的 1 条也被更新 = 4 条更新，好像"幻影"被读取了

6.5 事务控制语句

-- 开启事务
BEGIN;  -- 或 START TRANSACTION;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点（可部分回滚）
BEGIN;
INSERT INTO orders (user_id, total_amount) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO orders (user_id, total_amount) VALUES (1, 200);
SAVEPOINT sp2;
INSERT INTO orders (user_id, total_amount) VALUES (1, 300);
ROLLBACK TO sp2;  -- 回滚到 sp2，保留前两条插入
-- 此时表中有 2 条订单记录

-- 释放保存点
RELEASE SAVEPOINT sp1;

-- 自动提交关闭（手动控制事务）
SET autocommit = 0;  -- 关闭自动提交，每个 SQL 都是独立事务
SET autocommit = 1;  -- 开启自动提交

---

七、查询优化与执行计划

7.1 使用 EXPLAIN 分析查询

EXPLAIN 是 MySQL 提供的查询分析工具，可以让你看到 MySQL 如何执行 SQL 语句，是优化查询的第一步。

-- 基本语法
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 扩展 EXPLAIN（MySQL 5.6+）
EXPLAIN ANALYZE SELECT ...  -- 会显示实际执行时间和成本

-- 查看更详细的信息
EXPLAIN EXTENDED SELECT ...;
SHOW WARNINGS;  -- 查看优化后的查询语句

7.2 EXPLAIN 输出字段解读

EXPLAIN SELECT u.username, o.order_no 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id 
WHERE u.status = 1 
ORDER BY o.created_at DESC 
LIMIT 10;

字段	含义	优化目标值
id	查询执行的顺序，id 越大越先执行	-
select_type	查询类型（SIMPLE/PRIMARY/SUBQUERY/UNION 等）	越简单越好
table	涉及的表	-
type	访问类型，关键指标	system > const > eq_ref > ref > range > index > ALL
possible_keys	可能用到的索引	-
key	实际使用的索引	不为 NULL 表示用到索引
key_len	索引长度	越短越好
rows	预计扫描的行数	越少越好
Extra	附加信息（Using filesort/Using index 等）	避免 Using filesort

7.3 访问类型等级

7.4 常见优化技巧

-- 1. 避免 SELECT *，只查询需要的字段
-- ❌ 低效
SELECT * FROM orders WHERE id = 1;
-- ✅ 高效
SELECT id, order_no, total_amount FROM orders WHERE id = 1;

-- 2. 使用 LIMIT 限制返回行数
-- ❌ 低效
SELECT * FROM orders WHERE status = 'paid';
-- ✅ 高效
SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 100;

-- 3. 使用覆盖索引（Extra: Using index）
-- ❌ 低效（回表查询）
SELECT email, phone FROM users WHERE id = 1;
-- ✅ 高效（索引包含所有字段，无需回表）
CREATE INDEX idx_id_phone_email ON users(id, phone, email);
SELECT phone, email FROM users WHERE id = 1;

-- 4. 避免前置通配符（导致索引失效）
-- ❌ 低效（无法使用索引）
SELECT * FROM users WHERE phone LIKE '%138%';
-- ✅ 高效（可以使用索引）
SELECT * FROM users WHERE phone LIKE '138%';

-- 5. 使用延迟关联优化大表分页
-- ❌ 低效（深度分页，OFFSET 大时性能差）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- ✅ 高效（延迟关联，先定位 ID 再关联）
SELECT o.* FROM orders o 
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) AS t ON o.id = t.id;

-- 6. 批量插入替代循环单条插入
-- ❌ 低效
INSERT INTO orders (user_id, total_amount) VALUES (1, 100);
INSERT INTO orders (user_id, total_amount) VALUES (2, 200);
-- ✅ 高效
INSERT INTO orders (user_id, total_amount) VALUES 
(1, 100), (2, 200), (3, 300), (4, 400);

-- 7. 使用 EXPLAIN 检查慢查询
SHOW VARIABLES LIKE 'slow_query_log%';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询记录到慢查询日志

---

八、备份与恢复

8.1 常用备份方式

8.2 mysqldump 逻辑备份

# 备份单个数据库
mysqldump -u root -p my_blog > /backup/my_blog_$(date +%Y%m%d).sql

# 备份所有数据库
mysqldump -u root -p --all-databases > /backup/all_db_$(date +%Y%m%d).sql

# 备份指定表
mysqldump -u root -p my_blog users orders > /backup/my_blog_tables.sql

# 只备份结构（不含数据）
mysqldump -u root -p my_blog --no-data > /backup/my_blog_schema.sql

# 只备份数据（不含结构）
mysqldump -u root -p my_blog --no-create-info > /backup/my_blog_data.sql

# 远程备份
mysqldump -h 192.168.1.100 -P 3306 -u root -p my_blog > /backup/remote_db.sql

# 压缩备份（节省空间）
mysqldump -u root -p my_blog | gzip > /backup/my_blog_$(date +%Y%m%d).sql.gz

8.3 数据恢复

# 恢复数据库
mysql -u root -p my_blog < /backup/my_blog_20260524.sql

# 恢复压缩的备份
gunzip < /backup/my_blog_20260524.sql.gz | mysql -u root -p

# 从全量备份恢复指定数据库
mysql -u root -p --one-database my_blog < /backup/all_db_20260524.sql

# 恢复指定表
mysql -u root -p my_blog < /backup/my_blog_tables.sql

8.4 增量备份与 binlog

# 查看 binlog 文件列表
SHOW MASTER STATUS;
SHOW BINARY LOGS;

# 查看 binlog 内容（用于恢复）
mysqlbinlog /var/log/mysql/mysql-bin.000001

# 基于时间点恢复
mysqlbinlog --stop-datetime="2026-05-24 10:00:00" /var/log/mysql/mysql-bin.000001 | mysql -u root -p

# 基于位置点恢复
mysqlbinlog --start-position=100 --stop-position=500 /var/log/mysql/mysql-bin.000001 | mysql -u root -p

# 定期备份策略建议
# - 每天全量备份 + 实时增量备份（binlog）
# - 备份文件保留 7 天以上
# - 定期在测试环境验证备份可用性

8.5 使用工具进行备份

-- 使用 MySQL Enterprise Backup（企业版）
mysqlbackup --user=root --password=xxx --backup-dir=/backup/ backup

-- 使用 Percona XtraBackup（开源免费，功能强大）
xtrabackup --backup --target-dir=/backup/full_backup/
xtrabackup --prepare --target-dir=/backup/full_backup/
xtrabackup --copy-back --target-dir=/backup/full_backup/

-- 使用 Liquibase（适合版本化管理数据库变更）
liquibase --changeLogFile=changelog.xml update

---

九、总结与知识框架

9.1 核心知识点回顾

9.2 学习路线建议

9.3 推荐学习资源

• 📖 官方文档：MySQL 8.0 Reference Manual
• 📖 《高性能 MySQL》：MySQL 领域的经典著作，必读！
• 💻 在线练习：SQL Fiddle、LeetCode SQL

---

💡 写给读者的话：MySQL 学习没有捷径，多敲 SQL、多踩坑、多总结。内功深厚的程序员，一定是那些对数据库原理有深刻理解的人。纸上得来终觉浅，绝知此事要躬行，去真实环境中练习吧！

---

📅 本文首次发布于 2026 年 5 月 24 日