MySQL 数据库开发实战指南：从设计到优化

-- 表名：小写 + 下划线，名词复数或业务含义
CREATE TABLE user_orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    order_no VARCHAR(32) NOT NULL COMMENT '订单号',
    amount DECIMAL(10,2) NOT NULL COMMENT '金额',
    status TINYINT NOT NULL DEFAULT 0 COMMENT '状态: 0待支付 1已支付 2已取消',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id),
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user_id (user_id),
    KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 避免 SELECT *
SELECT id, username, email FROM users WHERE status = 1;

-- 分页优化（大偏移量）
-- 慢：SELECT * FROM orders LIMIT 100000, 10;
-- 快：
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

-- 批量插入
INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com');

-- EXISTS vs IN（子查询大表用 EXISTS）
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 窗口函数（MySQL 8.0+）
SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders;

-- 常用聚合函数
SELECT
    COUNT(*) AS total,
    SUM(amount) AS sum_amount,
    AVG(amount) AS avg_amount,
    MAX(amount) AS max_amount,
    MIN(amount) AS min_amount
FROM orders
WHERE status = 1;

-- HAVING 过滤分组结果
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING total > 1000;

-- GROUP_CONCAT
SELECT user_id, GROUP_CONCAT(order_no SEPARATOR ', ') AS order_nos
FROM orders
GROUP BY user_id;

-- 内连接：两边都匹配
SELECT u.name, o.order_no
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接：保留左表全部
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 自连接
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 主键索引
PRIMARY KEY (id)

-- 唯一索引
UNIQUE KEY uk_email (email)

-- 普通索引
KEY idx_name (name)

-- 联合索引（最左前缀原则）
KEY idx_status_time (status, created_at)

-- 全文索引
FULLTEXT INDEX ft_content (content)

-- 联合索引 (a, b, c)
-- 以下查询能命中索引：
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3        -- 只用到 a
WHERE a = 1 ORDER BY b       -- a 等值 + b 排序

-- 以下不能命中：
WHERE b = 2                   -- 跳过了 a
WHERE b = 2 AND c = 3        -- 跳过了 a

-- 1. 对索引列使用函数
WHERE YEAR(created_at) = 2026     -- 失效
WHERE created_at >= '2026-01-01'  -- 生效

-- 2. 隐式类型转换
WHERE phone = 13800138000         -- 失效（phone 是 VARCHAR）
WHERE phone = '13800138000'      -- 生效

-- 3. LIKE 左模糊
WHERE name LIKE '%张'             -- 失效
WHERE name LIKE '张%'            -- 生效

-- 4. OR 条件中有非索引列
WHERE indexed_col = 1 OR non_indexed_col = 2  -- 失效

-- 5. 不等于（!= 或 <>）
WHERE status != 1                 -- 可能失效

EXPLAIN SELECT * FROM orders WHERE user_id = 100;

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

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

-- 行锁（InnoDB）
SELECT * FROM orders WHERE id = 1 FOR UPDATE;  -- 排他锁
SELECT * FROM orders WHERE id = 1 LOCK IN SHARE MODE;  -- 共享锁

-- 间隙锁（防止幻读）
-- 锁定范围，防止其他事务在间隙中插入数据

-- 死锁排查
SHOW ENGINE INNODB STATUS;

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

-- 开启
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录

-- 1. 避免回表（覆盖索引）
-- 如果索引已包含查询字段，无需回表
SELECT user_id, status FROM orders WHERE user_id = 100;
-- 索引：KEY idx_user_status (user_id, status)

-- 2. 批量操作代替循环
-- 慢：1000 次 INSERT
-- 快：一次批量 INSERT 1000 条

-- 3. 合理使用临时表
CREATE TEMPORARY TABLE tmp_user_ids (user_id BIGINT);
INSERT INTO tmp_user_ids VALUES (1), (2), (3);
SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM tmp_user_ids);

-- 4. 大表分批处理
DELETE FROM logs WHERE created_at < '2025-01-01' LIMIT 1000;
-- 循环执行直到影响行数为 0