MySQL 数据库涵盖基础安装、SQL 语言（DDL/DML/DQL）、架构原理（InnoDB/MyISAM/B+Tree 索引）、事务隔离级别与锁机制、性能优化策略（慢查询分析、索引失效场景）、高可用架构（主从复制、分库分表）及实战案例。内容提供具体命令示例与配置指导，适用于从入门到进阶的学习路径。
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。MySQL 是一种，它将数据存储在不同的表中，表之间通过主键和外键建立关联。
Linux (CentOS/Ubuntu) 示例：
# Ubuntu
sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo mysql_secure_installation # 安全配置脚本
# CentOS
wget -i -c http://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum install mysql80-community-release-el7-3.noarch.rpm
yum install mysql-community-server
systemctl start mysqld
mysqladmin --version 或 mysql -u root -p。/etc/my.cnf (Linux) 或 my.ini (Windows)。/var/lib/mysql。主要包含：
.ibd (InnoDB 表数据和索引)。binlog (二进制日志)、error.log (错误日志)。.frm (MySQL 8.0 之前) 或数据字典合并到系统表空间。SQL（结构化查询语言）是操作数据库的标准语言，主要分为以下几类：
| 分类 | 全称 | 描述 | 常见命令 |
|---|---|---|---|
| DDL | Data Definition Language | 定义数据库结构 | CREATE, ALTER, DROP |
| DML | Data Manipulation Language | 操作数据 | INSERT, UPDATE, DELETE |
| DQL | Data Query Language | 查询数据 | SELECT |
| DCL | Data Control Language | 权限控制 | GRANT, REVOKE |
| TCL | Transaction Control Language | 事务控制 | COMMIT, ROLLBACK, SAVEPOINT |
-- 创建数据库
CREATE DATABASE IF NOT EXISTS `shop` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 使用数据库
USE shop;
-- 创建表
CREATE TABLE `user` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户 ID',
`username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
`password` CHAR(32) NOT NULL COMMENT '密码 (MD5)',
`email` VARCHAR(100) COMMENT '邮箱',
`status` TINYINT DEFAULT 1 COMMENT '状态：0 禁用，1 启用',
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
INDEX idx_username (`username`) -- 普通索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
-- 修改表结构
ALTER TABLE `user` ADD COLUMN `phone` VARCHAR(20) AFTER `email`;
ALTER TABLE `user` MODIFY `status` TINYINT DEFAULT 0;
ALTER TABLE `user` DROP COLUMN `phone`;
-- 插入
INSERT INTO `user` (`username`, `password`, `email`) VALUES ('zhangsan', MD5('123456'), '[email protected]');
-- 批量插入
INSERT INTO `user` (`username`, `password`) VALUES ('lisi', MD5('123')), ('wangwu', MD5('456'));
-- 更新 (务必带 WHERE 条件，否则全表更新)
UPDATE `user` SET `status` = 0 WHERE `username` = 'zhangsan';
-- 删除 (逻辑删除推荐使用状态字段，物理删除慎用)
DELETE FROM `user` WHERE `id` = 10;
-- 基础查询
SELECT id, username, email FROM user WHERE status = 1;
-- 条件查询与排序
SELECT * FROM user WHERE created_at >= '2023-01-01' AND status IN (1,2) ORDER BY created_at DESC, id ASC LIMIT 10 OFFSET 0;
-- 分页查询
-- 聚合查询
SELECT COUNT(*) AS total_count, MAX(id) AS max_id, AVG(id) AS avg_id FROM user;
索引是帮助 MySQL 高效获取数据的排好序的数据结构。InnoDB 默认使用 B+Tree。
PRIMARY KEY。(a, b, c) 遵循最左前缀原则。FULLTEXT，用于文本搜索（MATCH AGAINST）。最左前缀法则：
-- 假设创建了联合索引 (name, age, city)
-- ✅ 有效：命中 name 和 age
SELECT * FROM user WHERE name = 'A' AND age = 20;
-- ✅ 有效：命中 name
SELECT * FROM user WHERE name = 'A';
-- ❌ 失效：未使用最左列
SELECT * FROM user WHERE age = 20;
-- ❌ 失效：跳过中间列，只能用到 name
SELECT * FROM user WHERE name = 'A' AND city = 'BJ';
常见索引失效情况：
WHERE phone = 13800000000 (phone 是 varchar，缺少引号)。WHERE LEFT(name,3) = 'abc' 或 WHERE age + 1 = 20。!= 或 <>：通常导致全表扫描（除非选择性极低）。LIKE 以 % 开头：LIKE '%abc' 失效，LIKE 'abc%' 有效。开启慢查询日志：
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 记录超过 1 秒的 SQL
Explain 分析：
EXPLAIN SELECT * FROM user WHERE name = 'zhangsan';
关键字段解读：
system > const > eq_ref > ref > range > index > ALL（全表扫描，需优化）。Using index：覆盖索引，无需回表，好！Using where：使用了 where 过滤。Using filesort：外部排序，需优化。Using temporary：使用了临时表，常见于 group by 无索引，需优化。undo log 实现）。redo log 实现）。| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 |
| READ COMMITTED (RC) | 不会 | 可能 | 可能 |
| REPEATABLE READ (RR) | 不会 | 不会 | 可能 |
| SERIALIZABLE | 不会 | 不会 | 不会 |
REPEATABLE READ (RR)。Next-Key Lock（间隙锁 + 行锁）解决了幻读问题。MVCC 通过在每行记录后维护两个隐藏列：DB_TRX_ID（事务 ID）和 DB_ROLL_PTR（回滚指针），利用 Read View 实现非阻塞读。
Read View，导致不可重复读。Read View，持续到事务结束，保证可重复读。-- INNER JOIN: 返回匹配的行
SELECT u.name, o.order_no FROM user u INNER JOIN orders o ON u.id = o.user_id;
-- LEFT JOIN: 返回左表所有行，右表无匹配则为 NULL
SELECT u.name, o.order_no FROM user u LEFT JOIN orders o ON u.id = o.user_id;
-- 自连接：查询员工及其上级
SELECT e.name, m.name AS manager FROM employee e LEFT JOIN employee m ON e.manager_id = m.id;
-- 标量子查询
SELECT name, (SELECT MAX(salary) FROM employee) FROM employee;
-- IN 子查询 (注意优化，可能转换为 EXISTS)
SELECT * FROM user WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
-- EXISTS 子查询 (通常用于判断存在性，性能通常优于 IN 当子查询结果集大时)
SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
窗口函数在不改变行数的情况下进行聚合运算，非常适合排名、累积计算。
-- 排名：ROW_NUMBER, RANK, DENSE_RANK
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, RANK() OVER (ORDER BY salary DESC) AS rank_num, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num FROM employee;
-- 分组排名：按部门内排名
SELECT name, dept_id, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank FROM employee;
-- 聚合窗口函数：计算累计和
SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS cumulative_amount FROM sales;
传统 LIMIT 1000000, 10 会扫描前 100 万条数据，性能极差。
优化方案 1：延迟关联
-- 先利用覆盖索引快速定位主键，再回表取数据
SELECT * FROM user INNER JOIN ( SELECT id FROM user ORDER BY id LIMIT 1000000, 10 ) AS tmp ON user.id = tmp.id;
优化方案 2：书签记录法
-- 记住上一页的最大 ID
SELECT * FROM user WHERE id > 1000000 ORDER BY id LIMIT 10;
COUNT(*) 优化COUNT(*) ≈ COUNT(1) > COUNT(主键) > COUNT(非主键列)。COUNT(*) 需要实时扫描，建议使用额外汇总表或缓存（Redis）。SELECT *Using index）的命中，避免回表。-- 创建用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPwd123!';
-- 授权
GRANT SELECT, INSERT, UPDATE ON shop.* TO 'app_user'@'192.168.1.%';
-- 刷新权限
FLUSH PRIVILEGES;
-- 查看权限
SHOW GRANTS FOR 'app_user'@'192.168.1.%';
root 远程登录。# 备份所有库
mysqldump -u root -p --all-databases > all_backup.sql
# 备份指定库，并开启事务保证一致性
mysqldump -u root -p --single-transaction --quick shop > shop_backup.sql
# 恢复
mysql -u root -p shop < shop_backup.sql
Percona XtraBackup 支持在线热备份，不锁表，适合大数据库。
Binlog 记录了所有修改数据的操作，主要用于主从复制和基于时间点的恢复。
-- 查看日志状态
SHOW MASTER STATUS;
-- 查看 Binlog 内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';
SHOW STATUS LIKE 'Threads_connected'; -- 当前连接数
SHOW STATUS LIKE 'Innodb_rows_read'; -- InnoDB 读取行数
SHOW PROCESSLIST; -- 查看当前执行中的 SQL
通常配置在 my.cnf 中：
[mysqld]
log-error=/var/log/mysqld.log
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow.log
long_query_time=2
原理：基于 Binlog 的异步复制。
搭建要点：
server-id 唯一。relay-log。CHANGE MASTER TO 指定 Master 信息。常见拓扑：一主一从、一主多从、级联复制、双主（互为主从）。
通过中间件（如 MyCat、ShardingSphere-JDBC）或应用层路由，将写操作发往 Master，读操作发往 Slave，分担读压力。
当单表数据量过大（如超过千万级，或单表空间超过 200G）时，需考虑分库分表。
hash）分片。分片键选择：
场景：高并发下扣减库存，需保证不超卖且性能高。
方案 1：乐观锁（CAS）
UPDATE product SET stock = stock - 1 WHERE id = 1 AND stock > 0;
方案 2：悲观锁（SELECT FOR UPDATE）
BEGIN;
SELECT stock FROM product WHERE id = 1 FOR UPDATE;
-- 业务判断逻辑，如果库存足够则更新
UPDATE product SET stock = stock - 1 WHERE id = 1;
COMMIT;
场景：积分排行榜，要求实时性高且数据持久化。
parent_id）。查询子树需递归 CTE（MySQL 8.0+）。1/2/3/ 的路径字符串。undo log 记录回滚信息。redo log (Write-Ahead Logging) 保证崩溃恢复。binlog 串行同步。varchar(50) 和 varchar(200) 在存储上有什么区别？
hello 时，磁盘占用相同（实际长度 + 长度前缀）。varchar(200) 会消耗更多内存，影响性能。附：学习资源推荐
微信公众号「极客日志」，在微信中扫描左侧二维码关注。展示文案：极客日志 zeeklog
使用加密算法（如AES、TripleDES、Rabbit或RC4）加密和解密文本明文。 在线工具，加密/解密文本在线工具，online
在线格式化和美化您的 SQL 查询（它支持各种 SQL 方言）。 在线工具，SQL 美化和格式化在线工具，online
解析 INSERT 等受限 SQL，导出为 CSV、JSON、XML、YAML、HTML 表格（见页内语法说明）。 在线工具，SQL转CSV/JSON/XML在线工具，online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转，单页多 Tab。 在线工具，CSV 工具包在线工具，online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具，Base64 字符串编码/解码在线工具，online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具，Base64 文件转换器在线工具，online