MySQL零散知识点总结

发表于 分类于 ， ，

MySQL零散知识点总结

1. COUNT(*) vs COUNT(1) vs COUNT(列)

  • COUNT(*)：统计所有行，包括NULL
  • COUNT(1)：统计结果与COUNT(*)等价，1可以是任意值
  • COUNT(列)：只统计非NULL值

性能：InnoDB中COUNT(*)和COUNT(1)性能相当，因为有专门的优化。

2. WHERE vs HAVING

  • WHERE：过滤行，在分组前生效
  • HAVING：过滤分组后的结果，在GROUP BY后生效
1
2
3
4
5
6
-- 正确用法
SELECT dept, COUNT(*) 
FROM employee 
WHERE dept != 'IT'
GROUP BY dept
HAVING COUNT(*) > 5;

3. DISTINCT 的注意事项

  • DISTINCT只能放在第一个字段前
  • 多字段去重是基于全部字段的组合去重
  • DISTINCT会触发排序操作，大数据量时性能差

4. UNION vs UNION ALL

  • UNION：去重 + 排序，性能较低
  • UNION ALL：不去重，性能高

5. 分页查询优化

延迟关联

1
2
3
4
5
6
7
8
-- 低效
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

-- 高效（延迟关联）
SELECT o.* 
FROM orders o 
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t 
ON o.id = t.id;

游标分页

记录上一页最后一条的ID，用ID范围分页

6. 字段类型选择

  • 整型优先于字符串
  • 避免NULL，使用NOT NULL + 默认值
  • 避免TEXT/BLOB作为主键
  • 枚举类型用ENUM

7. 字符串存储

  • 定长用CHAR，变长用VARCHAR
  • VARCHAR(100)和VARCHAR(1000)存储空间相同，但内存占用不同
  • 手机号、身份证用字符串而非数字

8. 时间类型选择

  • 日期用DATE
  • 日期时间用DATETIME或TIMESTAMP
  • 需要时区用TIMESTAMP
  • 需要高精度用DATETIME(6)

9. COUNT(1) 性能优化

  • 近似值用EXPLAIN SELECT COUNT(*) FROM table
  • 加条件用COUNT(*) WHERE status = 1
  • 预先建立计数表

10. INSERT 批量插入

1
2
3
4
5
-- 低效
INSERT INTO t VALUES (1,'a'), (2,'b'), (3,'c');

-- 高效（控制在1000条以内）
INSERT INTO t VALUES (1,'a'), (2,'b'), (3,'c');

11. 常见SQL技巧

  • IFNULL处理NULL值
  • IF(CONDITION, TRUE_VAL, FALSE_VAL) 条件表达式
  • COALESCE 返回第一个非NULL值

12. 容易忽略的问题

  • 字符串比较要加引号
  • 避免在索引列上使用函数
  • 使用EXPLAIN分析SQL
  • 注意SQL注入风险

总结

本文汇总了MySQL开发中常见的零散知识点，虽然都是细节，但在实际开发中能避免很多问题。