
备份、权限、故障定位——运维的三条底线
导语 前三篇讲的是“会用”:建库建表、增删改查、关联统计。这一篇讲“敢运维”。运维和开发的区别不在于会不会写 SQL,而在于出了问题能不能兜住:备份能不能恢复、权限给得对不对、慢查
INNER JOIN 和 LEFT JOIN 的结果差异人人都能说清楚,但为什么专家总说“能写 INNER JOIN 就别写 LEFT JOIN”?为什么 COUNT(*) 和 COUNT(o.id) 在左连接里结果不一样?为什么 user_id 这种重复度很高的列还值得建索引?
本篇是系列第三篇。接着前两篇的 test_ops 库和 users 表(5 个用户,赵六 id=4),建 orders 表并插入订单,重点讲三件事:JOIN 算法与优化器自由度、NULL 在聚合中的语义、索引选择性的判断。
CREATE TABLE orders (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) DEFAULT 'paid',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
KEY idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
两个设计决策:
不建物理外键(FOREIGN KEY),只在 user_id 上建普通索引。 互联网业务里物理外键会带来跨表锁和级联操作的不可控性,应用层保证引用完整性更常见。但索引必须建——user_id 是 JOIN 的连接键,没索引就是全表扫描。
金额用 DECIMAL(10,2) 而不是 FLOAT。 浮点数在二进制下无法精确表示大部分十进制小数,金额计算会出现精度误差。DECIMAL 是精确十进制类型,这是财务相关字段的底线。
插入订单,赵六(id=4)故意不插,供后面对比:
INSERT INTO orders (user_id, amount, status) VALUES
(1, 199.00, 'paid'), (1, 88.50, 'paid'),
(2, 320.00, 'paid'), (3, 45.00, 'refund'),
(5, 660.00, 'paid');
INNER JOIN 与 LEFT JOIN:结果之外,还有执行计划SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
结果差异:INNER JOIN 不出现赵六,LEFT JOIN 出现赵六且 amount 为 NULL。这是语义层。
执行层的关键区别:LEFT JOIN 要求左表所有行保留,优化器不能随意调整驱动表顺序,执行计划的选择空间比 INNER JOIN 小。 能表达成 INNER JOIN 的查询,优化器可以自由选择用 users 还是 orders 做驱动表、是否走 hash join;写成 LEFT JOIN 就把这条路堵死了一部分。
用 EXPLAIN 验证:
EXPLAIN SELECT u.name, o.amount
FROM users u LEFT JOIN orders o ON u.id = o.user_id;
看 table 列的执行顺序,看被驱动表的 type 是不是 ref(走了 idx_user_id)。EXPLAIN 是判断 JOIN 好坏的唯一客观依据,不靠猜。
NULL:COUNT(*) 和 COUNT(o.id) 不是一回事SELECT u.name,
COUNT(o.id) AS order_count,
IFNULL(SUM(o.amount), 0) AS total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
两个语义细节:
COUNT(o.id) 而不是 COUNT(\*)。 在左连接结果里,赵六那一行的 o.id 是 NULL。COUNT(*) 数的是行数,赵六会算 1;COUNT(o.id) 只数非 NULL 值,赵六算 0。要统计“订单数”,必须用 COUNT(o.id)。
IFNULL 处理 NULL 参与运算。 NULL + 任何值 = NULL,赵六的 SUM(o.amount) 返回 NULL 而不是 0,直接展示会误导。IFNULL(..., 0) 把 NULL 转成 0,结果才符合业务语义。
这两点不是语法问题,是语义问题。写错了不报错,但结果错。
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
判断索引是否值得建,看选择性:COUNT(DISTINCT column) / COUNT(*)。user_id 在订单表里重复度高(一个用户多单),选择性不算高。但它值得建,原因有两个:
反过来,status 只有 paid/refund 几个值,选择性极低,单独建索引基本没用——优化器很可能直接忽略它走全表扫描。索引价值由选择性和使用场景共同决定,不是“有列就建”。
顺手练一下改表结构:
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);
DESC users;
ALTER 在大表上可能锁表,生产环境需先评估或用在线 DDL 工具,测试库可放心练。这个坑留到第四篇讲备份和变更时再展开。
JOIN 的写法影响优化器自由度,聚合函数对 NULL 的处理是语义问题,索引价值由选择性和使用场景共同决定。下一篇是运维自保篇:备份一致性从哪来、权限模型怎么生效、慢查询和锁等待怎么定位。
评论 0