MySQL-从入门到精通学习指南

The_bo

334人浏览 · 2026-04-28 15:05:57

The_bo · 2026-04-28 15:05:57 发布

MySQL 是全球最流行的开源关系型数据库，本文将带你系统掌握 MySQL，从基础 SQL 语法到高级性能优化。

一、MySQL 基础入门

1.1 MySQL 简介

MySQL 是一个开源的关系型数据库管理系统（RDBMS），由瑞典 MySQL AB 公司开发，现属于 Oracle 公司。其特点包括：

开源免费，性能卓越
支持跨平台（Linux、Windows、macOS）
支持 ANSI SQL99 标准
社区活跃，生态丰富

1.2 MySQL 安装

Linux 安装（Ubuntu/Debian）：

# 安装 MySQL 服务器
sudo apt update
sudo apt install mysql-server

# 启动服务
sudo systemctl start mysql
sudo systemctl enable mysql

# 安全初始化
sudo mysql_secure_installation

Windows 安装：

下载 MySQL Installer from mysql.com，选择 Server Only 或 Full 安装包。

Docker 安装：

docker run -d --name mysql \
  -e MYSQL_ROOT_PASSWORD=123456 \
  -p 3306:3306 \
  mysql:8.0

1.3 连接 MySQL

# 命令行连接
mysql -u root -p

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

# 连接到指定数据库
mysql -u root -p database_name

二、SQL 基础语法

2.1 数据库操作

-- 查看数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE blog DEFAULT CHARSET utf8mb4;

-- 使用数据库
USE blog;

-- 删除数据库
DROP DATABASE blog;

2.2 表操作

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 查看表结构
DESC users;
SHOW CREATE TABLE users;

-- 修改表 - 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- 修改表 - 删除列
ALTER TABLE users DROP COLUMN phone;

-- 修改表 - 修改列
ALTER TABLE users MODIFY COLUMN status TINYINT DEFAULT 1 COMMENT '状态:1启用 0禁用';

-- 删除表
DROP TABLE users;

2.3 增删改查（CRUD）

-- 插入数据（单条）
INSERT INTO users (username, email, password) 
VALUES ('admin', 'admin@example.com', 'hashed_password');

-- 插入数据（批量）
INSERT INTO users (username, email, password) VALUES 
('user1', 'user1@example.com', 'pass1'),
('user2', 'user2@example.com', 'pass2');

-- 查询数据
SELECT * FROM users;
SELECT id, username, email FROM users WHERE status = 1;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- 更新数据
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 1;

-- 清空表（ truncate 更快，自增重置）
TRUNCATE TABLE users;

2.4 条件查询

-- WHERE 子句
SELECT * FROM users WHERE status = 1 AND created_at > '2024-01-01';

-- 模糊查询
SELECT * FROM users WHERE username LIKE '%admin%';
SELECT * FROM users WHERE email LIKE '_@example.com';

-- 范围查询
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE age BETWEEN 18 AND 30;

-- 空值判断
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- 排序与分页
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 0;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;  -- 等效简写

2.5 聚合函数

-- 计数
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE status = 1;

-- 求和、平均、最大、最小
SELECT SUM(score), AVG(score), MAX(score), MIN(score) FROM exam;

-- 分组
SELECT status, COUNT(*) FROM users GROUP BY status;

-- HAVING（过滤分组后的结果）
SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 10;

-- DISTINCT 去重
SELECT DISTINCT status FROM users;

2.6 多表查询

-- 内连接（INNER JOIN）
SELECT u.username, o.order_no, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接（LEFT JOIN）
SELECT u.username, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 右连接（RIGHT JOIN）
SELECT u.username, o.order_no
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 自连接
SELECT a.username AS '用户', b.username AS '上级'
FROM users a
LEFT JOIN users b ON a.manager_id = b.id;

-- 联合查询（UNION）
SELECT username FROM users WHERE status = 1
UNION
SELECT username FROM users WHERE id IN (1, 2, 3);

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

三、索引与优化

3.1 索引类型

类型	说明	使用场景
PRIMARY KEY	主键索引，唯一且非空	主键字段
UNIQUE	唯一索引	需要唯一约束的字段
INDEX	普通索引	高频查询字段
FULLTEXT	全文索引	文本内容搜索
SPATIAL	空间索引	地理坐标数据

3.2 创建索引

-- 创建表时指定索引
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    INDEX idx_name (name),
    UNIQUE INDEX uk_email (email)
);

-- 单独创建索引
CREATE INDEX idx_name ON users(username);
CREATE UNIQUE INDEX uk_email ON users(email);

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

3.3 索引生效规则

索引能被使用的场景：

-- 全值匹配
SELECT * FROM users WHERE username = 'admin';

-- 最左前缀匹配（复合索引）
CREATE INDEX idx_user ON users(username, status, created_at);
-- 可用于: username, username+status, username+status+created_at

-- 非最左前缀（不使用索引）
SELECT * FROM users WHERE status = 1;
SELECT * FROM users WHERE created_at > '2024-01-01';

-- 范围查询（仅用于最左前缀）
SELECT * FROM users WHERE username > 'a' AND username < 'c';

-- 索引列参与运算（不使用）
SELECT * FROM users WHERE SUBSTRING(username, 1, 1) = 'a';

3.4 EXPLAIN 分析查询

EXPLAIN SELECT * FROM users WHERE username = 'admin';

关键字段解读：

type: 连接类型（最好的是 const，最差的是 all）
- const: 主键/唯一索引等值查询
- eq_ref: 多表连接时使用主键/唯一索引
- ref: 非唯一索引等值查询
- range: 范围扫描
- index: 全索引扫描
- all: 全表扫描（需要优化）
key: 实际使用的索引
rows: 扫描行数（越少越好）
Extra: 额外信息

3.5 SQL 优化技巧

-- 使用覆盖索引（避免回表）
SELECT id, username FROM users WHERE username = 'admin';

-- 使用延迟关联（优化分页）
SELECT * FROM users LIMIT 10000000, 10;  -- 慢
-- 优化：先查 ID，再关联
SELECT * FROM users u 
INNER JOIN (SELECT id FROM users LIMIT 10000000, 10) t 
ON u.id = t.id;

-- 使用 EXISTS 代替 IN（子查询）
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);

-- 使用 UNION ALL 代替 UNION（不需要去重）
SELECT username FROM users WHERE status = 1
UNION ALL
SELECT username FROM users WHERE id > 100;

四、事务与��

4.1 事务特性（ACID）

Atomicity（原子性）: 事务要么全部成功，要么全部失败
Consistency（一致性）: 事务前后数据状态一致
Isolation（隔离性）: 并发事务互不干扰
Durability（持久性）: 事务提交后，数据永久保存

4.2 事务操作

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

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT sp1;

-- 回滚到保存点
ROLLBACK TO SAVEPOINT sp1;

-- 自动提交（默认开启）
SET autocommit = 0;  -- 关闭自动提交
SET autocommit = 1;  -- 开启自动提交

4.3 事务隔离级别

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	是	是	是
READ COMMITTED	否	是	是
REPEATABLE READ	否	否	是
SERIALIZABLE	否	否	否

-- 查看隔离级别
SELECT @@transaction_isolation;
SELECT @@tx_isolation;

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

MySQL InnoDB 默认隔离级别为 REPEATABLE READ。

4.4 锁机制

锁类型：

共享锁（S锁）: 允许并发读取
排他锁（X锁）: 独占写入，其他锁不能共存
意向锁: 表级锁，表示行级锁的存在

-- 加共享锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- 加排他锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 查看锁信息
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

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

4.5 死锁处理

-- 查看最近死锁信息
SHOW ENGINE INNODB STATUS;

-- 设置死锁超时
SET innodb_lock_wait_timeout = 10;

避免死锁：

保持一致的锁顺序
尽量使用索引，缩小锁范围
减小事务粒度，及时提交

五、存储过程与函数

5.1 存储过程

-- 创建存储过程
DELIMITER //

CREATE PROCEDURE get_user_by_id(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //

DELIMITER ;

-- 调用存储过程
CALL get_user_by_id(1);

-- 带输出参数
CREATE PROCEDURE get_user_count(OUT count INT)
BEGIN
    SELECT COUNT(*) INTO count FROM users;
END //

-- 调用
CALL get_user_count(@cnt);
SELECT @cnt;

5.2 存储函数

-- 创建存储函数
DELIMITER //

CREATE FUNCTION get_user_name(uid INT)
RETURNS VARCHAR(50)
BEGIN
    DECLARE name VARCHAR(50);
    SELECT username INTO name FROM users WHERE id = uid;
    RETURN name;
END //

DELIMITER ;

-- 使用
SELECT get_user_name(1);

5.3 动态 SQL

-- 预处理语句
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
SET @id = 1;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

六、备份与恢复

6.1 逻辑备份（mysqldump）

# 备份单个数据库
mysqldump -u root -p blog > blog.sql

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

# 备份指定表
mysqldump -u root -p blog users orders > tables.sql

# 备份（仅结构）
mysqldump -u root -p -d blog > blog_structure.sql

# 备份（仅数据）
mysqldump -u root -p -t blog > blog_data.sql

# 备份（带事务）
mysqldump -u root -p --single-transaction blog > blog.sql

6.2 逻辑恢复

# 恢复数据库
mysql -u root -p blog < blog.sql

# 恢复所有数据库
mysql -u root -p < all.sql

6.3 mysqldump 选项说明

选项	说明
–single-transaction	导出时开启事务确保数据一致性（InnoDB）
–lock-tables	锁定表（MyISAM）
–master-data=2	记录 binlog 位置
–routines	包含存储过程/函数
–triggers	包含触发器
–events	包含事件

6.4 xtrabackup 物理备份

# 安装
yum install percona-xtrabackup

# 备份
xtrabackup --backup --target-dir=/backup/full

# 恢复
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full

6.5 binlog 恢复

# 查看 binlog 文件
SHOW MASTER LOGS;

# 基于时间点恢复
mysqlbinlog --stop-datetime='2024-01-01 10:00:00' binlog.000001 | mysql -u root -p

# 基于位置恢复
mysqlbinlog --start-position=1234 --stop-position=5678 binlog.000001 | mysql -u root -p

七、主从复制

7.1 主从复制原理

主库（Master）           从库（Slave）
   MySQL    ───binlog──>   MySQL
     │                    │
     ├─ IO Thread         │
     └─ SQL Thread        │

7.2 配置主库

# my.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
sync_binlog = 1

-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 查看主库状态
SHOW MASTER STATUS;

7.3 配置从库

# my.cnf
[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay
read_only = 1

-- 设置主库信息
CHANGE MASTER TO
    MASTER_HOST='192.168.1.1',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- 启动复制
START SLAVE;

-- 查看复制状态
SHOW SLAVE STATUS\G

7.4 主从复制配置检查

-- 从库查看
SHOW SLAVE STATUS\G

-- 关键检查项
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0  -- 延迟时间

八、性能优化

8.1 配置优化

# my.cnf 关键参数

# 缓冲池大小（���议为可用内存的 70%）
innodb_buffer_pool_size = 4G

# 日志文件大小
innodb_log_file_size = 1G

# 刷新方式
innodb_flush_log_at_trx_commit = 1  -- 最安全
-- = 2: 性能更好，数据有丢失风险

# 连接数
max_connections = 500

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

8.2 监控指标

-- 查看连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- 查看查询缓存
SHOW STATUS LIKE 'Qcache%';

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS;

-- 查看慢查询
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- 查看临时表
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

8.3 慢查询日志

# 启用慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

-- 查看慢查询
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- 使用 mysqldumpslow 分析
mysqldumpslow -s t /var/log/mysql/slow.log

8.4 性能分析工具

EXPLAIN: 分析查询执行计划
EXPLAIN ANALYZE: MySQL 8.0 实时分析
SHOW PROFILE: 查看执行细节
performance_schema: 性能事件收集
sys schema: 性能诊断视图

-- 启用 profile
SET profiling = 1;
SELECT * FROM users WHERE id = 1;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

九、MySQL 8.0 新特性

9.1 新增功能

窗口函数（Window Functions）

-- 排名
SELECT username, score,
    RANK() OVER (ORDER BY score DESC) as 'rank',
    DENSE_RANK() OVER (ORDER BY score DESC) as 'dense_rank',
    ROW_NUMBER() OVER (ORDER BY score DESC) as 'row_number'
FROM exam;

CTE（公用表表达式）

-- 递归 CTE
WITH RECURSIVE cte AS (
    SELECT 1 as n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 10
)
SELECT * FROM cte;

JSON 函数增强

-- JSON 路径查询
SELECT JSON_EXTRACT(data, '$.user.name');

-- JSON 合并
SELECT JSON_MERGE_PATCH('{"a":1}', '{"b":2}');

9.2 性能提升

索引增强（隐藏索引、降序索引）
快速 DDL（ Instant ADD COLUMN）
资源组管理
改进的 CTE 优化

十、常见问题与解决方案

10.1 连接问题

-- 检查最大连接数
SHOW VARIABLES LIKE 'max_connections';

-- 检查活跃连接
SHOW PROCESSLIST;

-- 清理休眠连接
SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist 
WHERE command = 'Sleep' AND time > 600;

10.2 数据库占用空间大

-- 查看表大小
SELECT table_name, ROUND(data_length/1024/1024, 2) AS 'MB'
FROM information_schema.tables 
WHERE table_schema = 'blog'
ORDER BY data_length DESC;

-- 优化表
OPTIMIZE TABLE users;

10.3 字符集问题

-- 查看字符集
SHOW VARIABLES LIKE 'character%';

-- 转换字符集
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4;

-- 确保数据库字符集一致
CREATE DATABASE blog DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;