MySQL 核心知识详解

一、SQL 基础语句

1. 数据定义语言（DDL）

sql

-- 创建数据库
CREATE DATABASE IF NOT EXISTS mydb;

-- 使用数据库
USE mydb;

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INT CHECK (age > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 修改表结构
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN age TINYINT;
ALTER TABLE users DROP COLUMN phone;

-- 删除表
DROP TABLE IF EXISTS users;

2. 数据操作语言（DML）

sql

-- 插入数据
INSERT INTO users (username, email, age) VALUES 
('john', 'john@example.com', 25),
('mary', 'mary@example.com', 30);

-- 查询数据
SELECT * FROM users WHERE age > 20 ORDER BY created_at DESC LIMIT 10;

-- 更新数据
UPDATE users SET age = age + 1 WHERE username = 'john';

-- 删除数据
DELETE FROM users WHERE username = 'john';

3. 数据查询语言（DQL）

sql

-- 连接查询
SELECT u.username, o.order_id 
FROM users u 
JOIN orders o ON u.id = o.user_id;

-- 子查询
SELECT username FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- 聚合函数
SELECT COUNT(*), AVG(age), MAX(age), MIN(age) 
FROM users;

-- 分组查询
SELECT age, COUNT(*) 
FROM users 
GROUP BY age 
HAVING COUNT(*) > 1;

二、MySQL 存储引擎

MySQL 支持多种存储引擎，最常用的是 InnoDB 和 MyISAM，区别如下：

特性	InnoDB	MyISAM
事务支持	支持（ACID）	不支持
外键支持	支持	不支持
锁粒度	行级锁（适合高并发）	表级锁
索引与数据存储	聚簇索引（索引和数据存储在一起）	非聚簇索引（索引和数据分离）
全文索引	5.6+ 支持	支持
崩溃恢复	支持	不支持
存储限制	64TB	256TB

选择建议：

InnoDB：适合需要事务、高并发、外键的场景（如电商、金融）
MyISAM：适合读多写少、不需要事务的场景（如日志、统计）

sql

-- 查看支持的存储引擎
SHOW ENGINES;

-- 创建表时指定存储引擎
CREATE TABLE mytable (
    id INT PRIMARY KEY
) ENGINE=InnoDB;

-- 修改表的存储引擎
ALTER TABLE mytable ENGINE=MyISAM;

三、事务管理

1. 事务的四大特性（ACID）

原子性（Atomicity）：事务中的操作要么全部成功，要么全部失败
一致性（Consistency）：事务执行前后数据库状态保持一致
隔离性（Isolation）：多个事务相互隔离，互不干扰
持久性（Durability）：事务提交后，数据永久保存

2. 事务控制语句

sql

-- 显式开启事务
START TRANSACTION;

-- 示例业务逻辑
INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 500 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE user_id = 2;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT sp1;
ROLLBACK TO sp1;

3. 事务隔离级别

MySQL 支持四种隔离级别，通过 SET TRANSACTION ISOLATION LEVEL 设置：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	允许	允许	允许
READ COMMITTED	禁止	允许	允许
REPEATABLE READ	禁止	禁止	允许
SERIALIZABLE	禁止	禁止	禁止

sql

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

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

InnoDB 默认隔离级别：REPEATABLE READ（通过 MVCC 解决幻读）

4. 处理死锁

死锁是指两个或多个事务互相等待对方释放锁的情况。

sql

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

-- 设置事务超时时间
SET innodb_lock_wait_timeout = 50;

四、高级特性

1. 索引优化

sql

-- 创建普通索引
CREATE INDEX idx_username ON users (username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);

-- 创建复合索引
CREATE INDEX idx_name_age ON users (username, age);

-- 查看查询执行计划
EXPLAIN SELECT * FROM users WHERE username = 'john';

2. 视图

sql

-- 创建视图
CREATE VIEW user_orders AS
SELECT u.username, o.order_id, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 查询视图
SELECT * FROM user_orders;

3. 存储过程

sql

-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE GetUserCount(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM users;
END$$
DELIMITER ;

-- 调用存储过程
CALL GetUserCount(@total);
SELECT @total;

4. 触发器

sql

-- 创建触发器
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END;

五、性能优化建议

索引优化：
- 为经常用于查询条件和排序的字段创建索引
- 避免过多索引（会影响写性能）
- 使用复合索引时注意最左前缀原则
查询优化：
- 避免 SELECT *，只查询需要的字段
- 使用 EXPLAIN 分析查询执行计划
- 避免子查询，尽量用连接查询替代
架构优化：
- 分库分表（垂直拆分和水平拆分）
- 读写分离
- 适当使用缓存（如 Redis）
配置优化：
- 调整 innodb_buffer_pool_size（建议为物理内存的 70-80%）
- 优化日志配置（innodb_log_file_size）
- 定期执行 OPTIMIZE TABLE 整理碎片

通过掌握以上内容，你可以熟练编写 MySQL 语句，理解不同存储引擎的适用场景，并灵活运用事务特性处理复杂业务逻辑。