六、性能优化：让查询飞起来
6.1 慢查询日志
-- 开启慢查询日志（MySQL）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2; -- 超过2秒的查询
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未使用索引的查询
-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';
6.2 SQL 优化技巧
-- 1. 只查需要的列（不要 SELECT *）
-- ❌ 差
SELECT * FROM users WHERE age > 18;
-- ✅ 好
SELECT id, username, email FROM users WHERE age > 18;
-- 2. 用 LIMIT 限制返回行数
SELECT * FROM logs ORDER BY id DESC LIMIT 100;
-- 3. 用 EXISTS 代替 IN（子查询结果集大时）
-- ❌ 差
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- ✅ 好
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- 4. 批量操作
-- ❌ 差：1000次单独插入
INSERT INTO users (name) VALUES ('张三');
INSERT INTO users (name) VALUES ('李四');
-- ✅ 好：1次批量插入
INSERT INTO users (name) VALUES ('张三'), ('李四');
-- 5. 避免使用 SELECT DISTINCT
-- 很多时候可以用 GROUP BY 代替
SELECT DISTINCT user_id FROM orders; -- 可能慢
SELECT user_id FROM orders GROUP BY user_id; -- 可能更快
-- 6. 优化分页查询（深分页问题）
-- ❌ 差：OFFSET 越大越慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- ✅ 好：记住上一页的最大ID
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;
6.3 数据库结构优化
-- 1. 分区表（适合大数据量）
CREATE TABLE logs (
id INT,
log_date DATE,
content TEXT
) PARTITION BY RANGE (YEAR(log_date)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);
-- 2. 分表策略
-- 水平分表：按用户ID哈希，分成 user_0, user_1, user_2...
-- 垂直分表：把不常用的列拆分到另一张表
-- 3. 使用覆盖索引
-- 索引包含了查询需要的所有列，不需要回表
CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = '张三'; -- 覆盖索引，快
七、数据库安全与备份
7.1 用户权限管理
-- 创建用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
-- 授予权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app_user'@'localhost';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'%' WITH GRANT OPTION;
-- 撤销权限
REVOKE DELETE ON mydb.* FROM 'app_user'@'localhost';
-- 查看权限
SHOW GRANTS FOR 'app_user'@'localhost';
-- 删除用户
DROP USER 'app_user'@'localhost';
-- 刷新权限
FLUSH PRIVILEGES;
7.2 备份与恢复
# 1. 使用 mysqldump 备份
# 备份整个数据库
mysqldump -u root -p mydb > mydb_backup.sql
# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql
# 备份特定表
mysqldump -u root -p mydb users orders > users_orders_backup.sql
# 只备份结构，不备份数据
mysqldump -u root -p --no-data mydb > mydb_structure.sql
# 只备份数据，不备份结构
mysqldump -u root -p --no-create-info mydb > mydb_data.sql
# 2. 恢复备份
mysql -u root -p mydb < mydb_backup.sql
# 3. 定时备份（使用 cron）
# 每天凌晨2点备份
0 2 * * * mysqldump -u root -p mydb > /backups/mydb_$(date +\%Y\%m\%d).sql
附：知识体系总结
数据库基础
├── SQL 语言
│ ├── DDL: CREATE, ALTER, DROP
│ ├── DML: INSERT, UPDATE, DELETE
│ └── DQL: SELECT (WHERE, JOIN, GROUP BY, HAVING, ORDER BY)
├── 表设计
│ ├── 数据类型选择
│ ├── 约束 (主键、外键、唯一、检查)
│ ├── 三大范式
│ └── 索引设计
├── 性能优化
│ ├── 索引优化（最左前缀、覆盖索引）
│ ├── SQL 优化（避免函数、类型转换、SELECT *）
│ └── 执行计划分析 (EXPLAIN)
├── 事务
│ ├── ACID
│ ├── 隔离级别
│ └── 锁机制
└── 管理
├── 用户权限
└── 备份恢复
