MySQL

从基础到高级到分布式，涵盖安装、管理、优化、高可用、分布式等核心内容。以 MySQL 8.0 为基准。

版本基线说明：本文所有 SQL 与特性描述默认基于 MySQL 8（8.0.x）。涉及历史版本差异时会明确标注。

目录

  1. 基础篇

    • 安装与初始化

    • 用户与权限管理

    • 数据库与表操作

    • 数据类型

    • 约束

    • 基本事务

    • 存储引擎（InnoDB/MyISAM）

  2. 进阶篇

    • 索引

    • 视图

    • 存储过程

    • 函数专题（内置函数）

    • 触发器

    • 事务隔离级别

    • 锁机制

    • JSON 操作

    • 全文搜索

  3. 性能优化篇

    • EXPLAIN 分析

    • 索引优化

    • 查询优化

    • 连接池

    • 配置调优

    • 表分区

    • 慢查询日志

  4. 高可用篇

    • 主从复制

    • 主从架构

    • 故障切换

    • 备份与恢复

  5. 分布式篇

    • InnoDB Cluster

    • 分库分表（ShardingSphere）

    • 读写分离（ProxySQL）

    • 常见分布式方案对比

  6. 运维与监控

  7. 面试高频补充（来自 2mysql.txt 梳理）

一、基础篇

1.1 安装与初始化

# Ubuntu/Debian
sudo apt install mysql-server

# 启动 & 开机自启
sudo systemctl start mysql
sudo systemctl enable mysql

# 安全初始化（设置 root 密码、删除匿名用户等）
sudo mysql_secure_installation

# 登录
mysql -u root -p

# 指定主机和端口
mysql -h 127.0.0.1 -P 3306 -u root -p

常用命令

命令

说明

SHOW DATABASES;

列出所有数据库

USE dbname;

切换数据库

SHOW TABLES;

列出所有表

DESC tablename;

查看表结构

SHOW INDEX FROM tablename;

查看索引

SHOW PROCESSLIST;

查看当前连接

STATUS;

查看服务器状态

\q / exit

退出

1.2 用户与权限管理

-- 创建用户
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword'; -- 只允许本地连接

-- 授权
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%';
GRANT SELECT, INSERT, UPDATE ON mydb.mytable TO 'myuser'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

-- 撤销权限
REVOKE INSERT ON mydb.mytable FROM 'myuser'@'%';

-- 查看权限
SHOW GRANTS FOR 'myuser'@'%';

-- 修改密码
ALTER USER 'myuser'@'%' IDENTIFIED BY 'newpassword';

-- 删除用户
DROP USER 'myuser'@'%';

1.3 数据库与表操作

-- 数据库
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
DROP DATABASE mydb;

-- 建表
CREATE TABLE users (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username    VARCHAR(50) NOT NULL,
    email       TEXT NOT NULL,
    age         INT CHECK (age >= 0),          -- MySQL 8.0.16+ 才真正生效
    balance     DECIMAL(12, 2) DEFAULT 0.00,
    is_active   TINYINT(1) DEFAULT 1,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    profile     JSON
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 修改表结构
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users MODIFY COLUMN age SMALLINT;
ALTER TABLE users CHANGE COLUMN email user_email TEXT;

-- 删除表
DROP TABLE IF EXISTS users;

-- 查看建表语句
SHOW CREATE TABLE users;

1.4 数据类型

分类

类型

说明

应用场景

整数

TINYINT / SMALLINT / INT / BIGINT

1/2/4/8 字节

状态位、计数器、主键 ID

浮点

FLOAT / DOUBLE

浮点数（有精度损失）

传感器数据、近似计算

精确小数

DECIMAL(p, s)

推荐用于金额

金额、税率、结算值

字符串

VARCHAR(n) / TEXT / LONGTEXT

变长字符串

昵称、描述、文章内容

布尔

TINYINT(1)

MySQL 无原生 BOOLEAN，用 0/1

开关位、启用状态

时间

DATE / TIME / DATETIME / TIMESTAMP

时间类型

下单时间、审计时间、任务调度

二进制

BLOB / LONGBLOB

二进制数据

文件片段、图片二进制、密文字节流

JSON

JSON

MySQL 8 原生支持，支持函数与路径查询

半结构化扩展字段、动态属性

枚举

ENUM('a','b','c')

枚举类型

状态机固定值（如订单状态）

集合

SET('a','b','c')

多值集合

小规模标签集合（可选项）

注意：

  • DATETIME 不受时区影响，TIMESTAMP 受时区影响，范围到 2038 年

  • 金额字段务必用 DECIMAL，不要用 FLOAT/DOUBLE

  • 字符集推荐 utf8mb4（支持 emoji），不要用 utf8（MySQL 的 utf8 是残缺的）

1.5 约束

-- 示例建表：主键、唯一、检查、外键
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT uq_users_email UNIQUE (email),
    CONSTRAINT chk_users_age CHECK (age BETWEEN 0 AND 150)
) ENGINE=InnoDB;

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    CONSTRAINT fk_orders_user
      FOREIGN KEY (user_id) REFERENCES users(id)
      ON DELETE RESTRICT ON UPDATE CASCADE
);

-- 添加约束
ALTER TABLE users
  ADD CONSTRAINT chk_users_created_at CHECK (created_at >= '2000-01-01');
ALTER TABLE users
  ADD CONSTRAINT uq_users_email2 UNIQUE (email);

-- 删除约束
ALTER TABLE users DROP CHECK chk_users_created_at;   -- MySQL 8.0.16+
ALTER TABLE users DROP INDEX uq_users_email2;        -- UNIQUE 通过索引删除
ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;

-- 查询当前库有哪些约束（推荐）
SHOW CREATE TABLE 表名;
-- 或
DESC 表名;
-- 或
SELECT
  tc.constraint_schema,
  tc.table_name,
  tc.constraint_name,
  tc.constraint_type
FROM information_schema.table_constraints AS tc
WHERE tc.constraint_schema = DATABASE()
ORDER BY tc.table_name, tc.constraint_type, tc.constraint_name;


-- 查询某张表的约束
SELECT
  tc.constraint_name,
  tc.constraint_type
FROM information_schema.table_constraints AS tc
WHERE tc.constraint_schema = DATABASE()
  AND tc.table_name = 'users'
ORDER BY tc.constraint_type, tc.constraint_name;


-- 查询外键明细（当前库）
SELECT
  kcu.table_name,
  kcu.column_name,
  kcu.constraint_name,
  kcu.referenced_table_name,
  kcu.referenced_column_name
FROM information_schema.key_column_usage AS kcu
WHERE kcu.table_schema = DATABASE()
  AND kcu.referenced_table_name IS NOT NULL
ORDER BY kcu.table_name, kcu.constraint_name, kcu.ordinal_position;


-- 查询 CHECK 约束表达式（MySQL 8）
SELECT
  cc.constraint_schema,
  cc.table_name,
  cc.constraint_name,
  cc.check_clause
FROM information_schema.check_constraints AS cc
WHERE cc.constraint_schema = DATABASE()
ORDER BY cc.table_name, cc.constraint_name;

约束设计建议（MySQL 8）

  • 主键优先使用短且稳定的整型（如 BIGINT UNSIGNED）。

  • UNIQUE + 业务字段能有效实现幂等（如订单号、业务流水号）。

  • 外键适合核心主数据一致性场景；高吞吐场景可在应用层做一致性并谨慎评估。

  • CHECK 在 MySQL 8.0.16+ 才真正执行校验，生产需确认版本。

1.6 基本事务

START TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 回滚
START TRANSACTION;
    DELETE FROM orders WHERE id = 999;
ROLLBACK;

-- 保存点
START TRANSACTION;
    INSERT INTO orders VALUES (...);
    SAVEPOINT sp1;
    INSERT INTO order_items VALUES (...);
    ROLLBACK TO sp1;
COMMIT;

-- 关闭自动提交
SET autocommit = 0;

1.7 存储引擎（InnoDB / MyISAM）

MySQL 8 默认引擎是 InnoDB，生产环境几乎总是优先选择 InnoDB。

-- 查看默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';

-- 查看数据库支持的引擎
SHOW ENGINES;

-- 查看某张表使用的引擎
SHOW TABLE STATUS LIKE 'users';

-- 建表时指定引擎
CREATE TABLE t_innodb (
    id BIGINT UNSIGNED PRIMARY KEY
) ENGINE=InnoDB;

对比项

InnoDB（MySQL 8 默认）

MyISAM

事务

支持 ACID、回滚、崩溃恢复

不支持

锁粒度

行级锁（并发友好）

表级锁

外键

支持

不支持

崩溃恢复

支持（redo/undo）

弱

适用场景

OLTP、绝大多数业务库

只读或历史兼容场景

二、进阶篇

2.1 索引

-- 普通索引
CREATE INDEX idx_users_email ON users(email);
ALTER TABLE users ADD INDEX idx_users_email2 (email);  -- MySQL 8 正确语法，ADD KEY 也可

-- 唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);
ALTER TABLE users ADD UNIQUE INDEX idx_username2 (username);

-- 组合索引（遵循最左前缀原则）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
ALTER TABLE orders ADD INDEX idx_orders_user_status2 (user_id, status);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);
ALTER TABLE articles ADD FULLTEXT INDEX idx_content2 (content);

-- 前缀索引（TEXT/BLOB 必须指定长度）
CREATE INDEX idx_email_prefix ON users(email(20));

-- 隐式索引（MySQL 8.0，不被优化器使用，用于测试删除索引的影响）
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;
ALTER TABLE users ALTER INDEX idx_email VISIBLE;

-- 删除索引
DROP INDEX idx_users_email ON users;
ALTER TABLE users DROP INDEX idx_users_email;

最左前缀原则

-- 组合索引 (a, b, c)
WHERE a = 1              -- ✅ 走索引
WHERE a = 1 AND b = 2   -- ✅ 走索引
WHERE a = 1 AND b = 2 AND c = 3  -- ✅ 走索引
WHERE b = 2              -- ❌ 不走索引
WHERE a = 1 AND c = 3   -- ✅ 只用到 a 部分

索引类型

类型

说明

适用场景

B-Tree

默认，InnoDB 标准

等值、范围查询

FULLTEXT

全文索引

文章内容搜索

HASH

Memory 引擎

仅等值查询

空间索引

SPATIAL

地理空间数据

2.2 视图

MySQL 不支持物化视图，只有普通视图（每次查询实时计算）。

-- 创建视图
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email FROM users WHERE is_active = 1;

-- 查询视图
SELECT * FROM active_users;

-- 可更新视图（满足条件时可对视图做 INSERT/UPDATE/DELETE）
-- 条件：单表、无聚合、无 DISTINCT、无子查询等

-- 删除视图
DROP VIEW IF EXISTS active_users;

-- 查看视图定义
SHOW CREATE VIEW active_users;

2.3 存储过程

-- 修改分隔符（避免与语句内 ; 冲突）
DELIMITER $$

-- 存储过程
CREATE PROCEDURE get_user_orders(IN uid BIGINT)
BEGIN
    SELECT * FROM orders WHERE user_id = uid;
END$$

-- 带输出参数
CREATE PROCEDURE get_user_total(IN uid BIGINT, OUT total DECIMAL(12,2))
BEGIN
    SELECT SUM(amount) INTO total FROM orders WHERE user_id = uid;
END$$

DELIMITER ;

-- 调用
CALL get_user_orders(1);
CALL get_user_total(1, @total); SELECT @total;

-- 删除
DROP PROCEDURE IF EXISTS get_user_orders;

-- 查看存储过程定义
SHOW CREATE PROCEDURE get_user_orders;

-- 查询当前库存储过程
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
  AND ROUTINE_TYPE = 'PROCEDURE';

2.4 函数专题（内置函数）

函数是 SQL 提效关键。MySQL 8 常用函数可按以下类别掌握：

-- 1) 字符串函数
SELECT CONCAT('user-', 1001) AS v1;
SELECT LENGTH('hello') AS bytes_len, CHAR_LENGTH('你好') AS char_len;
SELECT LOWER('AbC') AS lower_v, UPPER('AbC') AS upper_v;
SELECT SUBSTRING('mysql8', 1, 5) AS sub_v;
SELECT REPLACE('2026-04-06', '-', '/') AS rep_v;

-- 2) 数值函数
SELECT ROUND(3.14159, 2) AS r1, CEIL(3.1) AS c1, FLOOR(3.9) AS f1;
SELECT ABS(-10) AS abs_v, MOD(10, 3) AS mod_v;

-- 3) 日期时间函数
SELECT NOW() AS now_v, CURDATE() AS d, CURTIME() AS t;
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS plus_7d;
SELECT TIMESTAMPDIFF(DAY, '2026-01-01', '2026-04-06') AS diff_days;
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS fmt_v;

-- 4) 条件与空值函数
SELECT IF(1 < 2, 'yes', 'no') AS if_v;
SELECT IFNULL(NULL, 'fallback') AS ifnull_v;
SELECT COALESCE(NULL, NULL, 'x') AS coalesce_v;

-- 5) 聚合函数（分组统计）
SELECT COUNT(*) AS cnt, SUM(amount) AS total_amt, AVG(amount) AS avg_amt
FROM orders;

-- 6) 窗口函数（MySQL 8 核心特性）
SELECT
  user_id,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
  SUM(amount) OVER (PARTITION BY user_id) AS user_total
FROM orders;

-- 7) JSON 函数（MySQL 8 常用）
SELECT JSON_EXTRACT(profile, '$.age') AS age_json FROM users;
SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) AS name_txt FROM users;
SELECT JSON_SET(profile, '$.vip', true) AS profile_new FROM users;

函数使用建议

  • 尽量避免在索引列上直接套函数（如 WHERE DATE(created_at)=...），否则容易索引失效。

  • 可以使用“函数结果持久化（生成列）+ 索引”的方式兼顾可读性与性能。

  • 窗口函数优先用于“组内排名、累计值、TopN”场景，通常比自连接更直观。

2.5 触发器

DELIMITER $$

CREATE TRIGGER trg_update_time
    BEFORE UPDATE ON users
    FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END$$

-- 审计日志触发器
CREATE TRIGGER trg_order_audit
    AFTER INSERT ON orders
    FOR EACH ROW
BEGIN
    INSERT INTO order_audit(order_id, action, created_at)
    VALUES (NEW.id, 'INSERT', NOW());
END$$

DELIMITER ;

-- 查看触发器
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER IF EXISTS trg_update_time;

2.6 事务隔离级别

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

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL  TRANSACTION ISOLATION LEVEL REPEATABLE READ;

级别

脏读

不可重复读

幻读

说明

READ UNCOMMITTED

可能

可能

可能

几乎不用

READ COMMITTED

❌

可能

可能

Oracle 默认

REPEATABLE READ

❌

❌

❌*

MySQL 默认，MVCC+间隙锁解决幻读

SERIALIZABLE

❌

❌

❌

最严格，性能差

MySQL InnoDB 在 REPEATABLE READ 下通过间隙锁（Gap Lock）解决了大部分幻读问题。

2.7 锁机制

-- 行级锁（排他锁）
SELECT * FROM orders WHERE id = 1 FOR UPDATE;

-- 共享锁
SELECT * FROM orders WHERE id = 1 FOR SHARE;  -- MySQL 8.0

-- 跳过锁定行（MySQL 8.0）
SELECT * FROM orders WHERE status = 'pending'
LIMIT 1 FOR UPDATE SKIP LOCKED;

-- 表级锁
LOCK TABLES orders WRITE;
LOCK TABLES orders READ;
UNLOCK TABLES;

-- 元数据锁（MDL）：DDL 操作会自动加，注意长事务阻塞 DDL
-- 查看锁情况
SELECT * FROM performance_schema.data_locks;
SELECT * FROM information_schema.INNODB_TRX;

-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;

-- 杀掉阻塞进程
KILL 进程id;

InnoDB 锁类型

锁类型

说明

记录锁（Record Lock）

锁定单行

间隙锁（Gap Lock）

锁定索引间隙，防幻读

临键锁（Next-Key Lock）

记录锁 + 间隙锁，默认

意向锁（Intention Lock）

表级，辅助行锁

2.8 JSON 操作

-- 插入
INSERT INTO users(profile) VALUES ('{"age": 25, "tags": ["vip", "new"]}');

-- 查询
SELECT profile->'$.age'       FROM users;   -- 返回 JSON
SELECT profile->>'$.age'      FROM users;   -- 返回文本（去引号）
SELECT JSON_EXTRACT(profile, '$.address.city') FROM users;

-- 条件查询
SELECT * FROM users WHERE profile->>'$.age' > '20';
SELECT * FROM users WHERE JSON_CONTAINS(profile->'$.tags', '"vip"');

-- 更新
UPDATE users SET profile = JSON_SET(profile, '$.level', 5);
UPDATE users SET profile = JSON_REMOVE(profile, '$.age');

-- 函数
JSON_OBJECT('key', 'value')        -- 构造 JSON 对象
JSON_ARRAY(1, 2, 3)                -- 构造 JSON 数组
JSON_KEYS(profile)                  -- 获取所有 key
JSON_LENGTH(profile->'$.tags')     -- 数组长度

-- JSON 索引（需用虚拟列）
ALTER TABLE users ADD COLUMN age_virtual INT GENERATED ALWAYS AS (profile->>'$.age') VIRTUAL;
CREATE INDEX idx_age ON users(age_virtual);

2.9 全文搜索

-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(title, content);

-- 自然语言模式（默认）
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL tutorial');

-- 布尔模式
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);
-- + 必须包含  - 必须排除  * 通配符  "" 短语

-- 查询相关性分数
SELECT *, MATCH(title, content) AGAINST('MySQL') AS score
FROM articles ORDER BY score DESC;

全文搜索对中文支持有限，中文场景推荐使用 Elasticsearch。

三、性能优化篇

3.1 EXPLAIN 分析

-- 基本执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- 详细格式
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 1;

-- 实际执行（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

关键字段说明

字段

说明

type

连接类型，性能从好到差：const > eq_ref > ref > range > index > ALL

key

实际使用的索引，NULL 表示未用索引

rows

预估扫描行数，越小越好

Extra

附加信息，Using filesort / Using temporary 是性能警告

possible_keys

可能使用的索引

filtered

按条件过滤后剩余行的百分比

type 类型说明

type

说明

const

主键/唯一索引等值查询，最快

eq_ref

关联查询，关联字段为主键/唯一索引

ref

普通索引等值查询

range

索引范围查询

index

全索引扫描

ALL

全表扫描，需优化

3.2 索引优化

  • 遵循最左前缀原则：组合索引按查询条件顺序建立

  • 避免索引失效：

    • 不对索引列做函数操作：WHERE YEAR(created_at) = 2024 ❌

    • 不做隐式类型转换：字符串字段用数字查询 ❌

    • 不用 != / NOT IN（通常走不了索引）

    • LIKE '%xxx' 不走索引，LIKE 'xxx%' 可以

    • OR 连接的字段都需要有索引，否则全表扫描

  • 覆盖索引：查询字段全在索引中，避免回表

-- 覆盖索引示例（Extra: Using index，不需要回表）
CREATE INDEX idx_cover ON orders(user_id, status, amount);
SELECT user_id, status, amount FROM orders WHERE user_id = 1;

  • 定期检查无用索引：

-- 查找从未使用的索引
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0
AND object_schema NOT IN ('mysql','performance_schema','information_schema');

3.3 查询优化

  • 避免 SELECT *：只查需要的列

  • 小表驱动大表：IN 子查询数据量小时用 IN，大时用 EXISTS

  • 避免深分页：

-- 慢（深分页）
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;

-- 快（延迟关联）
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 10 OFFSET 100000) tmp
ON o.id = tmp.id;

-- 最快（keyset 分页）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

  • 大批量操作分批执行：

-- 避免一次性删除大量数据
DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 1000;
-- 循环执行直到影响行数为 0

3.4 连接池

应用层 → 连接池 → MySQL

常用连接池

工具

语言/场景

说明

ProxySQL

通用代理

支持读写分离、连接池

HikariCP

Java

性能最好的 Java 连接池

SQLAlchemy Pool

Python

Python ORM 内置

Druid

Java

阿里开源，监控强

ProxySQL 读写分离配置

-- 设置后端服务器
INSERT INTO mysql_servers(hostgroup_id, hostname, port)
VALUES (1, 'master_ip', 3306),   -- 写组
       (2, 'slave_ip',  3306);   -- 读组

-- 路由规则
INSERT INTO mysql_query_rules(rule_id, match_pattern, destination_hostgroup)
VALUES (1, '^SELECT', 2),        -- SELECT 走从库
       (2, '.*',      1);        -- 其他走主库

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

3.5 配置调优

编辑 /etc/mysql/mysql.conf.d/mysqld.cnf，以 16GB 内存为例：

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

# InnoDB 缓冲池（最重要，建议物理内存的 70-80%）
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8      # 建议每 1G 一个实例

# InnoDB 日志
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1    # 1=最安全，2=性能好但崩溃可能丢1秒数据

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

# 查询缓存（MySQL 8.0 已移除）
# query_cache_type = 0

# 排序/临时表
sort_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 64M
max_heap_table_size = 64M

# 慢查询
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

# 二进制日志（主从复制必须开启）
log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800   # 7天（MySQL 8 推荐）

3.6 表分区

-- 范围分区（按时间）
CREATE TABLE orders (
    id         BIGINT NOT NULL AUTO_INCREMENT,
    created_at DATETIME NOT NULL,
    amount     DECIMAL(12,2),
    PRIMARY KEY (id, created_at)   -- 分区键必须包含在主键中
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

-- 列表分区
PARTITION BY LIST (status) (
    PARTITION p_pending VALUES IN (0, 1),
    PARTITION p_done    VALUES IN (2, 3)
);

-- 哈希分区
PARTITION BY HASH (user_id) PARTITIONS 8;

-- 管理分区
ALTER TABLE orders ADD PARTITION (PARTITION p2026 VALUES LESS THAN (2027));
ALTER TABLE orders DROP PARTITION p2023;
ALTER TABLE orders TRUNCATE PARTITION p2023;

-- 查看分区信息
SELECT * FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'orders';

3.7 慢查询日志

-- 开启慢查询（运行时）
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;        -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = 1;  -- 未用索引也记录

-- 查看配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

# 用 mysqldumpslow 分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log   # 按时间排序，取 Top10
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log   # 按次数排序

# 用 pt-query-digest（更强大）
pt-query-digest /var/log/mysql/slow.log

四、高可用篇

4.1 主从复制

主节点配置（my.cnf）

[mysqld]
server-id = 1
log_bin   = mysql-bin
binlog_format = ROW
gtid_mode = ON                     # 开启 GTID（推荐）
enforce_gtid_consistency = ON

从节点配置

[mysqld]
server-id = 2
relay_log = relay-bin
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON                     # 从库只读

创建复制账户（主节点）

CREATE USER 'replicator'@'%' IDENTIFIED BY 'reppass';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

配置从节点

-- 基于 GTID
CHANGE MASTER TO
    MASTER_HOST = 'master_ip',
    MASTER_USER = 'replicator',
    MASTER_PASSWORD = 'reppass',
    MASTER_AUTO_POSITION = 1;     -- GTID 自动定位

START SLAVE;

-- 查看复制状态
SHOW SLAVE STATUS\G
-- 关键字段：Slave_IO_Running: Yes，Slave_SQL_Running: Yes

4.2 主从架构

写请求 → 主节点 (Master)
              ↓ binlog 复制
读请求 → 从节点1 (Slave)
         从节点2 (Slave)

查看复制延迟

SHOW SLAVE STATUS\G
-- 关注：Seconds_Behind_Master（从库延迟秒数）

-- MySQL 8.0 更精确的延迟监控
SELECT * FROM performance_schema.replication_applier_status_by_worker;

4.3 故障切换

手动切换

-- 从库提升为主库
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = OFF;

自动切换工具

工具

说明

MHA

最成熟，自动主从切换

Orchestrator

拓扑可视化 + 自动切换

MySQL Router

官方，配合 InnoDB Cluster

Keepalived + VIP

简单 VIP 漂移方案

4.4 备份与恢复

# 逻辑备份（mysqldump）
mysqldump -u root -p mydb > mydb.sql
mysqldump -u root -p --all-databases > all.sql
mysqldump -u root -p --single-transaction mydb > mydb.sql  # 不锁表（InnoDB）

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

# 物理备份（Percona XtraBackup，不锁表）
xtrabackup --backup --target-dir=/backup/base -u root -p
xtrabackup --prepare --target-dir=/backup/base
xtrabackup --copy-back --target-dir=/backup/base

# mysqlbinlog（基于时间点恢复）
mysqlbinlog --start-datetime="2025-01-01 00:00:00" \
            --stop-datetime="2025-01-01 12:00:00" \
            mysql-bin.000001 | mysql -u root -p

五、分布式篇

5.1 InnoDB Cluster（官方方案）

MySQL 官方的高可用分布式方案，由三个组件组成：

MySQL Shell      ← 管理工具
MySQL Router     ← 连接路由（读写分离）
Group Replication ← 多主同步复制

# 用 MySQL Shell 部署
mysqlsh

# 配置集群
dba.configureInstance('root@node1:3306')
dba.configureInstance('root@node2:3306')
dba.configureInstance('root@node3:3306')

# 创建集群
var cluster = dba.createCluster('MyCluster')
cluster.addInstance('root@node2:3306')
cluster.addInstance('root@node3:3306')
cluster.status()

5.2 分库分表（ShardingSphere）

分片策略

# ShardingSphere-JDBC 配置示例
rules:
  - !SHARDING
    tables:
      orders:
        actualDataNodes: ds_${0..1}.orders_${0..3}
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: db_mod   # 按 user_id 取模分库
        tableStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: tbl_mod  # 按 order_id 取模分表

分片注意事项

  • 分片键选择：区分度高、查询频繁、不会修改的字段（如 user_id、order_id）

  • 避免跨分片查询（JOIN、聚合）

  • 全局唯一 ID：使用雪花算法（Snowflake）代替 AUTO_INCREMENT

  • 分布式事务：使用 XA 事务或 Seata 框架

5.3 读写分离（ProxySQL）

应用
 ↓
ProxySQL（代理层）
 ├─→ Master（写）
 ├─→ Slave1（读）
 └─→ Slave2（读）

ProxySQL 配置见 3.4 连接池 章节。

5.4 常见分布式方案对比

方案

类型

适用场景

优点

缺点

主从复制

主从

读写分离、高可用

原生支持、成熟

单主写，延迟

InnoDB Cluster

多主

强一致高可用

官方、自动切换

性能有损耗

ShardingSphere

分片

海量数据、高并发写

水平扩展

跨分片复杂

TiDB

NewSQL

海量数据、强一致

兼容 MySQL 协议

运维复杂

Vitess

分片代理

超大规模（YouTube 使用）

极强扩展性

学习成本高

六、运维与监控

6.1 常用系统表

-- 查看当前连接
SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST WHERE TIME > 30;

-- 查看表状态
SHOW TABLE STATUS FROM mydb LIKE 'orders'\G

-- 查看 InnoDB 状态（锁、事务等）
SHOW ENGINE INNODB STATUS\G

-- 查看当前事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看锁等待
SELECT r.trx_id waiting_trx,
       r.trx_mysql_thread_id waiting_thread,
       b.trx_id blocking_trx,
       b.trx_mysql_thread_id blocking_thread
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id;

-- 数据库大小
SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
GROUP BY table_schema ORDER BY size_mb DESC;

-- 表大小
SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
       table_rows
FROM information_schema.TABLES
WHERE table_schema = 'mydb'
ORDER BY size_mb DESC;

6.2 Performance Schema

-- 开启（默认已开启）
UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES';

-- Top 慢 SQL
SELECT digest_text, count_star, avg_timer_wait/1000000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC LIMIT 10;

-- 哪些表 IO 最多
SELECT object_name, count_read, count_write
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY count_read + count_write DESC LIMIT 10;

-- 等待事件分析
SELECT event_name, count_star, avg_timer_wait/1000000000 AS avg_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0
ORDER BY avg_timer_wait DESC LIMIT 10;

6.3 监控工具

工具

说明

MySQL Workbench

官方 GUI 管理工具

Percona Monitoring and Management (PMM)

最专业的 MySQL 监控，含慢查询分析

Prometheus + mysqld_exporter

指标采集，配合 Grafana 展示

Zabbix

企业级监控

pt-toolkit

Percona 工具集，包含 pt-query-digest、pt-online-schema-change 等

6.4 在线 DDL

大表 ALTER TABLE 会锁表，生产环境需谨慎：

-- MySQL 5.6+ 原生 Online DDL（部分操作支持）
ALTER TABLE orders ADD COLUMN remark VARCHAR(200), ALGORITHM=INPLACE, LOCK=NONE;

-- 查看支持的算法
-- ALGORITHM=INPLACE：不重建表，速度快
-- ALGORITHM=COPY：重建表，锁表
-- LOCK=NONE：不加锁
-- LOCK=SHARED：共享锁，允许读

# pt-online-schema-change（大表首选）
pt-online-schema-change \
  --alter "ADD COLUMN remark VARCHAR(200)" \
  --execute \
  D=mydb,t=orders,u=root,p=password

# gh-ost（GitHub 开源，无触发器）
gh-ost \
  --user="root" --password="password" \
  --host=127.0.0.1 --database="mydb" --table="orders" \
  --alter="ADD COLUMN remark VARCHAR(200)" \
  --execute

6.5 日常维护清单

任务

频率

方式

慢查询分析

每天

pt-query-digest / slow_query_log

检查主从延迟

实时

Seconds_Behind_Master

备份验证

每周

恢复到测试环境验证

清理 binlog

自动

binlog_expire_logs_seconds 配置

检查表空间碎片

每月

information_schema.TABLES

重建碎片化表

按需

OPTIMIZE TABLE tablename（锁表，慎用）

更新统计信息

按需

ANALYZE TABLE tablename

检查无用索引

每月

performance_schema

七、面试高频补充（来自 2mysql.txt 梳理）

以下内容按 MySQL 8 语义去重、纠错和重排，作为面试复习补充。

7.1 索引与 B+ 树

  • InnoDB 使用 B+ 树作为主流索引结构：树高低、磁盘 IO 少、范围查询友好。

  • 聚簇索引（主键）叶子节点存整行；二级索引叶子节点存“索引列 + 主键值”，可能回表。

  • 联合索引遵循最左前缀：(a,b,c) 可命中 a/a,b/a,b,c，不能直接命中 b。

  • 面试常问：索引不是越多越好，索引会增加写入成本与存储开销。

7.2 事务、隔离级别与 MVCC

隔离级别

脏读

不可重复读

幻读

READ UNCOMMITTED

可能

可能

可能

READ COMMITTED

否

可能

可能

REPEATABLE READ（MySQL 默认）

否

否

InnoDB 通过 MVCC + Next-Key Lock 大幅抑制

SERIALIZABLE

否

否

否

  • ACID：

    • 原子性（A）：undo log 保证可回滚。

    • 一致性（C）：数据库约束 + 应用规则共同保证。

    • 隔离性（I）：锁 + MVCC 保证并发正确性。

    • 持久性（D）：redo log 与刷盘策略保证提交不丢。

  • MVCC 核心：多版本 + Read View，提升读写并发能力。

7.3 锁与并发控制（高频问法）

  • 悲观锁：SELECT ... FOR UPDATE / FOR SHARE，先加锁再更新。

  • 乐观锁：version 版本号或时间戳，更新时比较版本（WHERE id=? AND version=?）。

  • InnoDB 常见锁：记录锁、间隙锁、临键锁、意向锁、元数据锁（MDL）。

  • 并发异常：脏读、不可重复读、幻读（结合隔离级别回答）。

7.4 存储引擎对比（MySQL 8 口径）

维度

InnoDB（默认）

MyISAM

事务

支持

不支持

锁粒度

行锁

表锁

外键

支持

不支持

崩溃恢复

强

弱

场景

OLTP 主流业务

历史兼容/只读场景

7.5 SQL 优化面试速记

  • 避免 SELECT *，只查必要列。

  • 在 WHERE、ORDER BY 高频列建合适索引。

  • 避免在索引列上做函数/表达式（如 DATE(created_at)）。

  • 避免隐式类型转换（字符串列拿数字比）。

  • 警惕 !=、NOT IN、前导 %LIKE、OR 导致索引利用差（需看执行计划）。

  • 大结果集分页/分批，避免一次返回超大数据。

验证方式：

EXPLAIN SELECT ...;
EXPLAIN FORMAT=JSON SELECT ...;
EXPLAIN ANALYZE SELECT ...;  -- MySQL 8.0.18+

7.6 视图、触发器、存储过程区别（简答版）

  • 视图：保存查询定义，不存储数据，用于简化查询与控制权限暴露。

  • 触发器：在表的 INSERT/UPDATE/DELETE 事件自动执行，适合审计与轻量规则。

  • 存储过程：可显式 CALL，适合封装批处理逻辑、减少网络往返。

7.7 备份恢复命令（常考）

# 备份
mysqldump -h127.0.0.1 -P3306 -uroot -p mydb > mydb.sql

# 恢复
mysql -h127.0.0.1 -P3306 -uroot -p mydb < mydb.sql

或客户端内：

SOURCE /path/to/mydb.sql;

7.8 快问快答

  • TINYINT 能存 200 吗？

    • SIGNED 不能（-128~127），UNSIGNED 可以（0~255）。

  • 一张表能存多少数据？

    • 没有固定值，取决于页大小、行长度、索引、行格式、磁盘与引擎限制。

  • 为什么联合索引常优于多个单列索引？

    • 多条件过滤/排序时可一次命中，减少 index merge 与回表成本。

参考资源