1. 从“增删改查”到“调优排错”一名后端工程师的SQL实战手册干了这么多年后端开发数据库打交道是家常便饭。SQL这玩意儿你说它简单吧无非就是“增删改查”四个字你说它复杂吧一个没写好的查询就能让整个系统卡成PPT。网上所谓的“SQL大全”一搜一大把但很多要么是干巴巴的命令列表要么是脱离实际场景的语法讲解。今天我想从一个一线工程师的视角跟你聊聊那些真正高频、实用并且藏着不少“坑”的SQL语句。这不仅仅是语法罗列更是我踩过无数坑后总结出的“怎么用”和“为什么这么用”的经验之谈。无论你是刚入门的新手还是想查漏补缺的老鸟希望这篇结合了基础、进阶与实战优化的内容能成为你手边常备的参考。2. 基石篇你必须滚瓜烂熟的CRUD与基础查询所有复杂的查询和操作都建立在最基础的CRUDCreate, Read, Update, Delete之上。这一部分看似简单但细节决定成败。2.1 数据操作语言DML增删改的精准控制插入INSERT不只是加数据更要加对地方插入语句的核心是明确目标字段和值的对应关系。我最推荐、也最规范的写法是列出所有字段名。-- 规范写法明确字段易于维护避免表结构变更导致错误 INSERT INTO users (username, email, created_at, status) VALUES (‘张三‘, ‘zhangsanexample.com‘, NOW(), 1);注意即使你打算为所有字段插入值也建议写上字段名。因为表结构可能会增加新字段使用INSERT INTO table_name VALUES (...)这种省略字段名的写法在表结构变更后极易导致“列数不匹配”的错误。更新UPDATE务必带上WHERE否则就是“血案”这是最需要警惕的语句。没有WHERE条件的UPDATE会更新整张表。-- 安全做法先SELECT确认再UPDATE -- 1. 先确认要更新的记录 SELECT * FROM orders WHERE status ‘pending‘ AND created_at ‘2023-10-01‘; -- 2. 执行更新条件与SELECT一致 UPDATE orders SET status ‘expired‘ WHERE status ‘pending‘ AND created_at ‘2023-10-01‘;实操心得在生产环境执行UPDATE或DELETE前我养成了一个肌肉记忆般的习惯先把WHERE条件放到SELECT里跑一遍确认影响的行数确实是预期的那几条然后再把SELECT换成UPDATE/DELETE。这个习惯至少救过我三次。删除DELETE软删除还是硬删除物理删除数据风险极高且难以追溯。在实际业务中“软删除”是更通用的做法。即通过一个标记位如is_deleted来标识数据是否有效。-- 硬删除慎用 DELETE FROM products WHERE id 100; -- 软删除推荐 UPDATE products SET is_deleted 1, deleted_at NOW() WHERE id 100; -- 查询时排除已删除数据 SELECT * FROM products WHERE is_deleted 0;2.2 数据查询语言DQLSELECT的艺术基础SELECT语句是根本而WHERE、ORDER BY、LIMIT的组合则是满足日常需求的利器。精准过滤WHERE与排序ORDER BY-- 基础查询选择特定列按条件过滤并排序 SELECT id, name, price FROM products WHERE category_id 5 AND price 100 AND stock 0 ORDER BY price DESC, created_at ASC; -- 先按价格降序价格相同再按创建时间升序分页查询LIMIT OFFSET这是实现列表分页的核心。但需要注意性能问题特别是OFFSET值很大时。-- 获取第6页的数据每页20条假设page6, size20 SELECT * FROM articles ORDER BY id DESC LIMIT 20 OFFSET 100; -- OFFSET (6-1)*20避坑指南当OFFSET达到几十万时数据库需要先扫描并跳过这么多行性能会急剧下降。对于深度分页更好的方法是使用“游标分页”或“基于ID的范围查询”例如WHERE id last_id ORDER BY id LIMIT 20。去重DISTINCT与数据聚合GROUP BYDISTINCT用于返回唯一不同的值而GROUP BY则用于结合聚合函数如COUNT, SUM, AVG根据一个或多个列对结果集进行分组。-- 查找所有不同的城市 SELECT DISTINCT city FROM customers; -- 统计每个城市的客户数量 SELECT city, COUNT(*) as customer_count FROM customers GROUP BY city; -- 统计每个类别下价格超过50的商品数量 SELECT category_id, COUNT(*) FROM products WHERE price 50 GROUP BY category_id;一个常见的坑SELECT列表中的非聚合字段必须出现在GROUP BY子句中否则在某些数据库严格模式下会报错或者返回不确定的值。这是SQL标准的要求目的是保证分组后每一行数据的确定性。3. 进阶篇连接、子查询与函数让数据“活”起来当数据分散在多张表中时如何将它们关联起来并计算出你想要的结果是SQL进阶的关键。3.1 多表连接JOIN关系的桥梁JOIN的类型和使用场景是面试常客也是日常工作的核心。INNER JOIN内连接只返回两个表中匹配的行。这是最常用的连接类型。-- 查询所有下了订单的客户信息 SELECT c.name, o.order_no, o.amount FROM customers c INNER JOIN orders o ON c.id o.customer_id;LEFT JOIN左连接返回左表的所有行即使右表中没有匹配。如果右表无匹配则结果中右表部分为NULL。-- 查询所有客户及其订单即使客户没下过单 SELECT c.name, o.order_no FROM customers c LEFT JOIN orders o ON c.id o.customer_id; -- 可以用于查找“没有订单的客户” SELECT c.name FROM customers c LEFT JOIN orders o ON c.id o.customer_id WHERE o.id IS NULL; -- 右表关键字段为NULL说明没匹配上RIGHT JOIN 与 FULL JOINRIGHT JOIN与LEFT JOIN原理相同方向相反。FULL JOIN返回左右两表的所有行不匹配的部分用NULL填充。在实际业务中LEFT JOIN的使用频率远高于RIGHT JOIN因为通常我们有一个明确的主表如客户、商品FULL JOIN则更为罕见。实操心得关于JOIN的性能JOIN操作可能非常消耗资源尤其是大表关联。务必确保ON后面的连接条件字段如c.id o.customer_id建立了索引。没有索引的JOIN在大数据量下就是性能灾难。另外尽量使用INNER JOIN而非LEFT JOIN因为前者结果集通常更小数据库优化器有更多优化空间。3.2 子查询查询嵌套的威力子查询即一个查询嵌套在另一个查询内部。它常用于WHERE、FROM和SELECT子句中。在WHERE中使用常用于过滤-- 找出价格高于平均价格的所有商品 SELECT * FROM products WHERE price (SELECT AVG(price) FROM products); -- 查找购买了“特定商品”的客户 SELECT name FROM customers WHERE id IN (SELECT customer_id FROM order_items WHERE product_id 100);在FROM中使用派生表将一个子查询的结果当作临时表来使用。-- 统计每个类别的商品数量和平均价格 SELECT cat.name, p_stats.count, p_stats.avg_price FROM categories cat JOIN ( SELECT category_id, COUNT(*) as count, AVG(price) as avg_price FROM products GROUP BY category_id ) p_stats ON cat.id p_stats.category_id;注意子查询虽然灵活但有时性能不如JOIN。特别是WHERE ... IN (SELECT ...)这种形式如果子查询结果集很大效率会很低。现代数据库优化器对EXISTS的处理有时更好上述例子可以改写为SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.customer_id c.id AND oi.product_id 100)。关键在于EXISTS只要找到一条匹配记录就返回真而IN需要处理整个结果集。3.3 常用函数处理数据的瑞士军刀SQL内置了大量函数用于处理字符串、日期和数值。字符串函数-- 拼接、大小写、截取、替换 SELECT CONCAT(last_name, ‘, ‘, first_name) AS full_name FROM users; SELECT UPPER(username), LOWER(email) FROM users; SELECT SUBSTRING(description, 1, 100) AS brief FROM articles; -- 截取前100个字符 SELECT REPLACE(comment, ‘垃圾‘, ‘**‘) AS cleaned_comment FROM posts; -- 敏感词替换日期时间函数日期处理是业务逻辑的重灾区务必小心时区问题。-- 获取当前时间、日期加减、格式化 SELECT NOW(), CURDATE(); -- 当前日期时间、当前日期 -- 计算三天后的日期 SELECT DATE_ADD(NOW(), INTERVAL 3 DAY); -- 计算两个日期之差天数 SELECT DATEDIFF(‘2023-12-31‘, ‘2023-01-01‘); -- 格式化日期输出 SELECT DATE_FORMAT(created_at, ‘%Y年%m月%d日 %H:%i:%s‘) AS create_time FROM orders;数值与条件函数-- 四舍五入、取整 SELECT ROUND(price, 2), CEIL(price), FLOOR(price) FROM products; -- 条件判断CASE WHEN SELECT name, price, CASE WHEN price 1000 THEN ‘高价‘ WHEN price 100 THEN ‘中价‘ ELSE ‘低价‘ END AS price_level FROM products; -- 空值处理IFNULL 或 COALESCE (COALESCE更通用可处理多个参数) SELECT name, COALESCE(mobile, phone, ‘暂无联系方式‘) AS contact FROM customers;4. 性能篇识别与优化慢SQL告别系统卡顿写出一条能跑出结果的SQL只是第一步写出一条能高效运行的SQL才是工程师的价值所在。慢SQL是系统性能的“头号杀手”。4.1 如何发现慢SQL开启慢查询日志这是最直接的方法。在MySQL等数据库中配置long_query_time例如设为2秒所有执行时间超过此阈值的SQL都会被记录到日志中供你分析。利用监控工具云数据库服务如RDS或APM应用性能监控工具通常都提供了慢SQL统计和排名功能能直观地看到哪些SQL最耗资源。执行计划EXPLAIN这是最核心的分析工具。在任何SELECT语句前加上EXPLAIN关键字数据库就会告诉你它打算如何执行这条语句。4.2 解读EXPLAIN执行计划执行计划返回的结果包含多列关键要看这几项type访问类型从好到坏大致是system const eq_ref ref range index ALL。要尽量避免ALL全表扫描。key实际使用的索引。如果为NULL则说明没用到索引。rows预估需要扫描的行数。这个值越小越好。Extra额外信息。如果出现Using filesort需要额外排序或Using temporary需要创建临时表通常意味着性能瓶颈。实战分析示例 假设我们有一条慢查询SELECT * FROM orders WHERE user_id 100 AND status ‘completed‘ ORDER BY created_at DESC;我们加上EXPLAINEXPLAIN SELECT * FROM orders WHERE user_id 100 AND status ‘completed‘ ORDER BY created_at DESC;假设返回结果中type是ALLkey是NULLrows是几十万。这说明数据库正在对orders表进行全表扫描来寻找user_id100且status‘completed‘的记录效率极低。4.3 优化策略索引是王道针对上面的例子优化方法就是创建合适的索引。单列索引如果user_id的选择性不同值的数量/总行数很高可以单独为它建索引CREATE INDEX idx_user_id ON orders(user_id);。联合索引复合索引由于查询条件有user_id和status两个字段并且有ORDER BY created_at创建联合索引效率更高。联合索引的列顺序至关重要需要遵循“最左前缀原则”。方案ACREATE INDEX idx_user_status ON orders(user_id, status);这个索引可以高效定位到user_id100的所有行然后再从中过滤status。但对于ORDER BY created_at没有帮助可能仍需要额外的排序操作。方案BCREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);这是更优解。这个索引可以快速定位到user_id100 AND status‘completed‘的所有行。因为这些行在索引中已经是按照created_at排序的created_at是索引的第三列所以数据库可以直接按索引顺序读取数据完全避免了对结果集的额外排序Using filesort。创建索引的注意事项索引不是越多越好索引会占用磁盘空间并降低写操作INSERT/UPDATE/DELETE的速度因为数据变更时需要同步更新索引。优先考虑高频查询和慢查询。区分度低的字段不适合建索引例如“性别”字段只有‘男‘/‘女‘建索引意义不大。对于LIKE ‘%keyword%‘这种前置模糊查询普通B-Tree索引是无效的需要考虑全文索引FULLTEXT或搜索引擎。4.4 其他常见优化技巧避免使用SELECT *只取出需要的列。这可以减少网络传输的数据量特别是当表中有TEXT/BLOB等大字段时。优化JOIN操作确保JOIN字段有索引并尽量用小表驱动大表即数据量小的表作为驱动表。合理使用分页如前所述对于深度分页使用WHERE id last_id代替LIMIT N OFFSET M。批量操作大量数据插入时使用INSERT INTO ... VALUES (...), (...), (...);的批量语句比循环执行单条INSERT快一个数量级。5. 安全篇严防SQL注入守住数据大门SQL注入是Web安全中最经典、也最危险的漏洞之一。攻击者通过构造特殊的输入欺骗数据库执行非预期的SQL命令。5.1 SQL注入原理与示例假设有一段登录验证的代码原始SQL是这样拼接的String sql “SELECT * FROM users WHERE username ‘“ username “’ AND password ‘“ password “’”;如果用户输入的username是admin‘ --那么拼接后的SQL就变成了SELECT * FROM users WHERE username ‘admin‘ -- ‘ AND password ‘...‘--在SQL中是注释符这意味着后面的密码检查被注释掉了攻击者可以直接以admin身份登录。更危险的如果输入是admin‘; DROP TABLE users; --可能会导致整个用户表被删除。5.2 根本解决方案使用参数化查询预编译语句这是防止SQL注入的唯一正确且彻底的方法。其原理是将SQL语句的结构命令和参数占位符与数据用户输入分开发送给数据库。数据库先编译SQL结构知道这是一个查询语句有哪里是参数位点然后再将用户输入的数据当作纯数据处理即使数据中包含SQL关键字也不会被解释为命令。各语言示例Java (JDBC):String sql “SELECT * FROM users WHERE username ? AND password ?”; PreparedStatement stmt connection.prepareStatement(sql); stmt.setString(1, username); stmt.setString(2, password); ResultSet rs stmt.executeQuery();Python (PyMySQL/sqlite3):sql “SELECT * FROM users WHERE username %s AND password %s” cursor.execute(sql, (username, password))PHP (PDO):$stmt $pdo-prepare(“SELECT * FROM users WHERE username :username AND password :password”); $stmt-execute([‘username‘ $username, ‘password‘ $password]);重要区别参数化查询与“转义”或“过滤”不同。转义如PHP的mysqli_real_escape_string试图让危险字符“无害化”但逻辑复杂容易遗漏。而参数化查询是从机制上杜绝了数据和指令混合的可能性更加安全可靠。5.3 ORM框架的安全性与局限性现代开发中我们常使用ORM对象关系映射框架如Java的MyBatis、JPA/HibernatePython的SQLAlchemy、Django ORM等。安全性主流ORM框架的查询方法如MyBatis的#{}、JPA的Query配合参数、Django ORM的filter底层都使用了参数化查询因此能有效防止SQL注入。局限性ORM框架为了灵活性有时会提供“执行原生SQL”的接口。当你使用这些原生SQL接口并且仍然采用字符串拼接的方式时SQL注入风险就回来了// 危险MyBatis中错误的用法 Select(“SELECT * FROM users WHERE username ‘${username}‘“) // 使用 ${} 是直接拼接 User findByUsername(Param(“username”) String username); // 安全MyBatis中正确的用法 Select(“SELECT * FROM users WHERE username #{username}“) // 使用 #{} 是参数化 User findByUsername(Param(“username”) String username);核心原则无论使用原生SQL还是ORM只要涉及将用户输入拼接到SQL语句中就必须使用参数化查询接口绝对不要手动拼接字符串。6. 实战锦囊高频场景与疑难杂症处理最后分享几个我工作中遇到的高频场景和对应的SQL写法以及一些“奇怪”问题的排查思路。6.1 高频场景SQL模板1. 查询每个分类下最新的一条记录这是一个典型的“分组取最大/最新”问题可以使用子查询或窗口函数如果数据库支持如MySQL 8.0 PostgreSQL等。-- 方法1使用子查询关联通用 SELECT a.* FROM articles a INNER JOIN ( SELECT category_id, MAX(created_at) as latest_time FROM articles GROUP BY category_id ) b ON a.category_id b.category_id AND a.created_at b.latest_time; -- 方法2使用窗口函数ROW_NUMBER()更简洁MySQL 8.0 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY created_at DESC) as rn FROM articles ) t WHERE t.rn 1;2. 实现“存在则更新不存在则插入”UPSERTMySQL使用ON DUPLICATE KEY UPDATEPostgreSQL使用INSERT ... ON CONFLICT ... DO UPDATE。-- MySQL INSERT INTO user_stats (user_id, login_count, last_login) VALUES (123, 1, NOW()) ON DUPLICATE KEY UPDATE login_count login_count 1, last_login NOW(); -- 前提是user_id字段有UNIQUE约束或主键。 -- PostgreSQL INSERT INTO user_stats (user_id, login_count, last_login) VALUES (123, 1, NOW()) ON CONFLICT (user_id) DO UPDATE SET login_count user_stats.login_count 1, last_login NOW();3. 递归查询查询树形结构如部门层级使用公共表表达式CTE的递归查询。-- 查询ID为5的部门及其所有下级部门 WITH RECURSIVE dept_tree AS ( SELECT id, name, parent_id FROM departments WHERE id 5 -- 锚点 UNION ALL SELECT d.id, d.name, d.parent_id FROM departments d INNER JOIN dept_tree dt ON d.parent_id dt.id -- 递归连接 ) SELECT * FROM dept_tree;6.2 常见疑难杂症排查问题1明明有索引为什么查询还是慢可能原因1索引失效。例如对索引列使用了函数或计算WHERE YEAR(create_time)2023、使用了OR连接多个条件且部分列无索引、字符串查询未使用最左前缀LIKE ‘%abc‘。可能原因2数据分布不均。例如索引列“状态”有99%的值都是‘A‘查询WHERE status‘A‘时数据库优化器可能认为全表扫描比走索引更快。排查方法使用EXPLAIN查看执行计划确认是否真的使用了索引key列以及扫描行数rows列是否合理。问题2COUNT(*)、COUNT(1)、COUNT(列名)有什么区别哪个快COUNT(*)统计所有行数包括NULL值。这是标准写法也是性能最优的数据库会进行优化。COUNT(1)与COUNT(*)在性能上几乎没有区别统计所有行数。COUNT(列名)统计该列非NULL的行数。如果该列有索引数据库可能会选择更小的索引来计数有时会快一点但语义不同。结论统计总行数无脑用COUNT(*)即可。问题3如何清理数据库中的重复数据-- 假设表duplicate_data中email列有重复我们想保留id最小的那条 DELETE a FROM duplicate_data a INNER JOIN duplicate_data b WHERE a.email b.email AND a.id b.id; -- 或者使用窗口函数更现代 DELETE FROM duplicate_data WHERE id NOT IN ( SELECT MIN(id) FROM duplicate_data GROUP BY email ); -- 注意执行删除前务必先备份或开启事务SQL的世界远不止这些窗口函数、CTE、事务隔离级别、锁机制等等每一个深挖下去都是一片天地。但掌握以上这些内容足以让你应对90%以上的日常开发场景并能写出安全、高效的数据库代码。记住写SQL时心里要时刻装着三件事结果对不对逻辑、跑得快不快性能、安不安全注入。多使用EXPLAIN分析多关注慢查询日志在实践中不断积累感觉你就能从“会写SQL”成长为“懂SQL”的工程师。 后端工程师必备：从CRUD到性能优化的SQL实战指南
1. 从“增删改查”到“调优排错”一名后端工程师的SQL实战手册干了这么多年后端开发数据库打交道是家常便饭。SQL这玩意儿你说它简单吧无非就是“增删改查”四个字你说它复杂吧一个没写好的查询就能让整个系统卡成PPT。网上所谓的“SQL大全”一搜一大把但很多要么是干巴巴的命令列表要么是脱离实际场景的语法讲解。今天我想从一个一线工程师的视角跟你聊聊那些真正高频、实用并且藏着不少“坑”的SQL语句。这不仅仅是语法罗列更是我踩过无数坑后总结出的“怎么用”和“为什么这么用”的经验之谈。无论你是刚入门的新手还是想查漏补缺的老鸟希望这篇结合了基础、进阶与实战优化的内容能成为你手边常备的参考。2. 基石篇你必须滚瓜烂熟的CRUD与基础查询所有复杂的查询和操作都建立在最基础的CRUDCreate, Read, Update, Delete之上。这一部分看似简单但细节决定成败。2.1 数据操作语言DML增删改的精准控制插入INSERT不只是加数据更要加对地方插入语句的核心是明确目标字段和值的对应关系。我最推荐、也最规范的写法是列出所有字段名。-- 规范写法明确字段易于维护避免表结构变更导致错误 INSERT INTO users (username, email, created_at, status) VALUES (‘张三‘, ‘zhangsanexample.com‘, NOW(), 1);注意即使你打算为所有字段插入值也建议写上字段名。因为表结构可能会增加新字段使用INSERT INTO table_name VALUES (...)这种省略字段名的写法在表结构变更后极易导致“列数不匹配”的错误。更新UPDATE务必带上WHERE否则就是“血案”这是最需要警惕的语句。没有WHERE条件的UPDATE会更新整张表。-- 安全做法先SELECT确认再UPDATE -- 1. 先确认要更新的记录 SELECT * FROM orders WHERE status ‘pending‘ AND created_at ‘2023-10-01‘; -- 2. 执行更新条件与SELECT一致 UPDATE orders SET status ‘expired‘ WHERE status ‘pending‘ AND created_at ‘2023-10-01‘;实操心得在生产环境执行UPDATE或DELETE前我养成了一个肌肉记忆般的习惯先把WHERE条件放到SELECT里跑一遍确认影响的行数确实是预期的那几条然后再把SELECT换成UPDATE/DELETE。这个习惯至少救过我三次。删除DELETE软删除还是硬删除物理删除数据风险极高且难以追溯。在实际业务中“软删除”是更通用的做法。即通过一个标记位如is_deleted来标识数据是否有效。-- 硬删除慎用 DELETE FROM products WHERE id 100; -- 软删除推荐 UPDATE products SET is_deleted 1, deleted_at NOW() WHERE id 100; -- 查询时排除已删除数据 SELECT * FROM products WHERE is_deleted 0;2.2 数据查询语言DQLSELECT的艺术基础SELECT语句是根本而WHERE、ORDER BY、LIMIT的组合则是满足日常需求的利器。精准过滤WHERE与排序ORDER BY-- 基础查询选择特定列按条件过滤并排序 SELECT id, name, price FROM products WHERE category_id 5 AND price 100 AND stock 0 ORDER BY price DESC, created_at ASC; -- 先按价格降序价格相同再按创建时间升序分页查询LIMIT OFFSET这是实现列表分页的核心。但需要注意性能问题特别是OFFSET值很大时。-- 获取第6页的数据每页20条假设page6, size20 SELECT * FROM articles ORDER BY id DESC LIMIT 20 OFFSET 100; -- OFFSET (6-1)*20避坑指南当OFFSET达到几十万时数据库需要先扫描并跳过这么多行性能会急剧下降。对于深度分页更好的方法是使用“游标分页”或“基于ID的范围查询”例如WHERE id last_id ORDER BY id LIMIT 20。去重DISTINCT与数据聚合GROUP BYDISTINCT用于返回唯一不同的值而GROUP BY则用于结合聚合函数如COUNT, SUM, AVG根据一个或多个列对结果集进行分组。-- 查找所有不同的城市 SELECT DISTINCT city FROM customers; -- 统计每个城市的客户数量 SELECT city, COUNT(*) as customer_count FROM customers GROUP BY city; -- 统计每个类别下价格超过50的商品数量 SELECT category_id, COUNT(*) FROM products WHERE price 50 GROUP BY category_id;一个常见的坑SELECT列表中的非聚合字段必须出现在GROUP BY子句中否则在某些数据库严格模式下会报错或者返回不确定的值。这是SQL标准的要求目的是保证分组后每一行数据的确定性。3. 进阶篇连接、子查询与函数让数据“活”起来当数据分散在多张表中时如何将它们关联起来并计算出你想要的结果是SQL进阶的关键。3.1 多表连接JOIN关系的桥梁JOIN的类型和使用场景是面试常客也是日常工作的核心。INNER JOIN内连接只返回两个表中匹配的行。这是最常用的连接类型。-- 查询所有下了订单的客户信息 SELECT c.name, o.order_no, o.amount FROM customers c INNER JOIN orders o ON c.id o.customer_id;LEFT JOIN左连接返回左表的所有行即使右表中没有匹配。如果右表无匹配则结果中右表部分为NULL。-- 查询所有客户及其订单即使客户没下过单 SELECT c.name, o.order_no FROM customers c LEFT JOIN orders o ON c.id o.customer_id; -- 可以用于查找“没有订单的客户” SELECT c.name FROM customers c LEFT JOIN orders o ON c.id o.customer_id WHERE o.id IS NULL; -- 右表关键字段为NULL说明没匹配上RIGHT JOIN 与 FULL JOINRIGHT JOIN与LEFT JOIN原理相同方向相反。FULL JOIN返回左右两表的所有行不匹配的部分用NULL填充。在实际业务中LEFT JOIN的使用频率远高于RIGHT JOIN因为通常我们有一个明确的主表如客户、商品FULL JOIN则更为罕见。实操心得关于JOIN的性能JOIN操作可能非常消耗资源尤其是大表关联。务必确保ON后面的连接条件字段如c.id o.customer_id建立了索引。没有索引的JOIN在大数据量下就是性能灾难。另外尽量使用INNER JOIN而非LEFT JOIN因为前者结果集通常更小数据库优化器有更多优化空间。3.2 子查询查询嵌套的威力子查询即一个查询嵌套在另一个查询内部。它常用于WHERE、FROM和SELECT子句中。在WHERE中使用常用于过滤-- 找出价格高于平均价格的所有商品 SELECT * FROM products WHERE price (SELECT AVG(price) FROM products); -- 查找购买了“特定商品”的客户 SELECT name FROM customers WHERE id IN (SELECT customer_id FROM order_items WHERE product_id 100);在FROM中使用派生表将一个子查询的结果当作临时表来使用。-- 统计每个类别的商品数量和平均价格 SELECT cat.name, p_stats.count, p_stats.avg_price FROM categories cat JOIN ( SELECT category_id, COUNT(*) as count, AVG(price) as avg_price FROM products GROUP BY category_id ) p_stats ON cat.id p_stats.category_id;注意子查询虽然灵活但有时性能不如JOIN。特别是WHERE ... IN (SELECT ...)这种形式如果子查询结果集很大效率会很低。现代数据库优化器对EXISTS的处理有时更好上述例子可以改写为SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.customer_id c.id AND oi.product_id 100)。关键在于EXISTS只要找到一条匹配记录就返回真而IN需要处理整个结果集。3.3 常用函数处理数据的瑞士军刀SQL内置了大量函数用于处理字符串、日期和数值。字符串函数-- 拼接、大小写、截取、替换 SELECT CONCAT(last_name, ‘, ‘, first_name) AS full_name FROM users; SELECT UPPER(username), LOWER(email) FROM users; SELECT SUBSTRING(description, 1, 100) AS brief FROM articles; -- 截取前100个字符 SELECT REPLACE(comment, ‘垃圾‘, ‘**‘) AS cleaned_comment FROM posts; -- 敏感词替换日期时间函数日期处理是业务逻辑的重灾区务必小心时区问题。-- 获取当前时间、日期加减、格式化 SELECT NOW(), CURDATE(); -- 当前日期时间、当前日期 -- 计算三天后的日期 SELECT DATE_ADD(NOW(), INTERVAL 3 DAY); -- 计算两个日期之差天数 SELECT DATEDIFF(‘2023-12-31‘, ‘2023-01-01‘); -- 格式化日期输出 SELECT DATE_FORMAT(created_at, ‘%Y年%m月%d日 %H:%i:%s‘) AS create_time FROM orders;数值与条件函数-- 四舍五入、取整 SELECT ROUND(price, 2), CEIL(price), FLOOR(price) FROM products; -- 条件判断CASE WHEN SELECT name, price, CASE WHEN price 1000 THEN ‘高价‘ WHEN price 100 THEN ‘中价‘ ELSE ‘低价‘ END AS price_level FROM products; -- 空值处理IFNULL 或 COALESCE (COALESCE更通用可处理多个参数) SELECT name, COALESCE(mobile, phone, ‘暂无联系方式‘) AS contact FROM customers;4. 性能篇识别与优化慢SQL告别系统卡顿写出一条能跑出结果的SQL只是第一步写出一条能高效运行的SQL才是工程师的价值所在。慢SQL是系统性能的“头号杀手”。4.1 如何发现慢SQL开启慢查询日志这是最直接的方法。在MySQL等数据库中配置long_query_time例如设为2秒所有执行时间超过此阈值的SQL都会被记录到日志中供你分析。利用监控工具云数据库服务如RDS或APM应用性能监控工具通常都提供了慢SQL统计和排名功能能直观地看到哪些SQL最耗资源。执行计划EXPLAIN这是最核心的分析工具。在任何SELECT语句前加上EXPLAIN关键字数据库就会告诉你它打算如何执行这条语句。4.2 解读EXPLAIN执行计划执行计划返回的结果包含多列关键要看这几项type访问类型从好到坏大致是system const eq_ref ref range index ALL。要尽量避免ALL全表扫描。key实际使用的索引。如果为NULL则说明没用到索引。rows预估需要扫描的行数。这个值越小越好。Extra额外信息。如果出现Using filesort需要额外排序或Using temporary需要创建临时表通常意味着性能瓶颈。实战分析示例 假设我们有一条慢查询SELECT * FROM orders WHERE user_id 100 AND status ‘completed‘ ORDER BY created_at DESC;我们加上EXPLAINEXPLAIN SELECT * FROM orders WHERE user_id 100 AND status ‘completed‘ ORDER BY created_at DESC;假设返回结果中type是ALLkey是NULLrows是几十万。这说明数据库正在对orders表进行全表扫描来寻找user_id100且status‘completed‘的记录效率极低。4.3 优化策略索引是王道针对上面的例子优化方法就是创建合适的索引。单列索引如果user_id的选择性不同值的数量/总行数很高可以单独为它建索引CREATE INDEX idx_user_id ON orders(user_id);。联合索引复合索引由于查询条件有user_id和status两个字段并且有ORDER BY created_at创建联合索引效率更高。联合索引的列顺序至关重要需要遵循“最左前缀原则”。方案ACREATE INDEX idx_user_status ON orders(user_id, status);这个索引可以高效定位到user_id100的所有行然后再从中过滤status。但对于ORDER BY created_at没有帮助可能仍需要额外的排序操作。方案BCREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);这是更优解。这个索引可以快速定位到user_id100 AND status‘completed‘的所有行。因为这些行在索引中已经是按照created_at排序的created_at是索引的第三列所以数据库可以直接按索引顺序读取数据完全避免了对结果集的额外排序Using filesort。创建索引的注意事项索引不是越多越好索引会占用磁盘空间并降低写操作INSERT/UPDATE/DELETE的速度因为数据变更时需要同步更新索引。优先考虑高频查询和慢查询。区分度低的字段不适合建索引例如“性别”字段只有‘男‘/‘女‘建索引意义不大。对于LIKE ‘%keyword%‘这种前置模糊查询普通B-Tree索引是无效的需要考虑全文索引FULLTEXT或搜索引擎。4.4 其他常见优化技巧避免使用SELECT *只取出需要的列。这可以减少网络传输的数据量特别是当表中有TEXT/BLOB等大字段时。优化JOIN操作确保JOIN字段有索引并尽量用小表驱动大表即数据量小的表作为驱动表。合理使用分页如前所述对于深度分页使用WHERE id last_id代替LIMIT N OFFSET M。批量操作大量数据插入时使用INSERT INTO ... VALUES (...), (...), (...);的批量语句比循环执行单条INSERT快一个数量级。5. 安全篇严防SQL注入守住数据大门SQL注入是Web安全中最经典、也最危险的漏洞之一。攻击者通过构造特殊的输入欺骗数据库执行非预期的SQL命令。5.1 SQL注入原理与示例假设有一段登录验证的代码原始SQL是这样拼接的String sql “SELECT * FROM users WHERE username ‘“ username “’ AND password ‘“ password “’”;如果用户输入的username是admin‘ --那么拼接后的SQL就变成了SELECT * FROM users WHERE username ‘admin‘ -- ‘ AND password ‘...‘--在SQL中是注释符这意味着后面的密码检查被注释掉了攻击者可以直接以admin身份登录。更危险的如果输入是admin‘; DROP TABLE users; --可能会导致整个用户表被删除。5.2 根本解决方案使用参数化查询预编译语句这是防止SQL注入的唯一正确且彻底的方法。其原理是将SQL语句的结构命令和参数占位符与数据用户输入分开发送给数据库。数据库先编译SQL结构知道这是一个查询语句有哪里是参数位点然后再将用户输入的数据当作纯数据处理即使数据中包含SQL关键字也不会被解释为命令。各语言示例Java (JDBC):String sql “SELECT * FROM users WHERE username ? AND password ?”; PreparedStatement stmt connection.prepareStatement(sql); stmt.setString(1, username); stmt.setString(2, password); ResultSet rs stmt.executeQuery();Python (PyMySQL/sqlite3):sql “SELECT * FROM users WHERE username %s AND password %s” cursor.execute(sql, (username, password))PHP (PDO):$stmt $pdo-prepare(“SELECT * FROM users WHERE username :username AND password :password”); $stmt-execute([‘username‘ $username, ‘password‘ $password]);重要区别参数化查询与“转义”或“过滤”不同。转义如PHP的mysqli_real_escape_string试图让危险字符“无害化”但逻辑复杂容易遗漏。而参数化查询是从机制上杜绝了数据和指令混合的可能性更加安全可靠。5.3 ORM框架的安全性与局限性现代开发中我们常使用ORM对象关系映射框架如Java的MyBatis、JPA/HibernatePython的SQLAlchemy、Django ORM等。安全性主流ORM框架的查询方法如MyBatis的#{}、JPA的Query配合参数、Django ORM的filter底层都使用了参数化查询因此能有效防止SQL注入。局限性ORM框架为了灵活性有时会提供“执行原生SQL”的接口。当你使用这些原生SQL接口并且仍然采用字符串拼接的方式时SQL注入风险就回来了// 危险MyBatis中错误的用法 Select(“SELECT * FROM users WHERE username ‘${username}‘“) // 使用 ${} 是直接拼接 User findByUsername(Param(“username”) String username); // 安全MyBatis中正确的用法 Select(“SELECT * FROM users WHERE username #{username}“) // 使用 #{} 是参数化 User findByUsername(Param(“username”) String username);核心原则无论使用原生SQL还是ORM只要涉及将用户输入拼接到SQL语句中就必须使用参数化查询接口绝对不要手动拼接字符串。6. 实战锦囊高频场景与疑难杂症处理最后分享几个我工作中遇到的高频场景和对应的SQL写法以及一些“奇怪”问题的排查思路。6.1 高频场景SQL模板1. 查询每个分类下最新的一条记录这是一个典型的“分组取最大/最新”问题可以使用子查询或窗口函数如果数据库支持如MySQL 8.0 PostgreSQL等。-- 方法1使用子查询关联通用 SELECT a.* FROM articles a INNER JOIN ( SELECT category_id, MAX(created_at) as latest_time FROM articles GROUP BY category_id ) b ON a.category_id b.category_id AND a.created_at b.latest_time; -- 方法2使用窗口函数ROW_NUMBER()更简洁MySQL 8.0 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY created_at DESC) as rn FROM articles ) t WHERE t.rn 1;2. 实现“存在则更新不存在则插入”UPSERTMySQL使用ON DUPLICATE KEY UPDATEPostgreSQL使用INSERT ... ON CONFLICT ... DO UPDATE。-- MySQL INSERT INTO user_stats (user_id, login_count, last_login) VALUES (123, 1, NOW()) ON DUPLICATE KEY UPDATE login_count login_count 1, last_login NOW(); -- 前提是user_id字段有UNIQUE约束或主键。 -- PostgreSQL INSERT INTO user_stats (user_id, login_count, last_login) VALUES (123, 1, NOW()) ON CONFLICT (user_id) DO UPDATE SET login_count user_stats.login_count 1, last_login NOW();3. 递归查询查询树形结构如部门层级使用公共表表达式CTE的递归查询。-- 查询ID为5的部门及其所有下级部门 WITH RECURSIVE dept_tree AS ( SELECT id, name, parent_id FROM departments WHERE id 5 -- 锚点 UNION ALL SELECT d.id, d.name, d.parent_id FROM departments d INNER JOIN dept_tree dt ON d.parent_id dt.id -- 递归连接 ) SELECT * FROM dept_tree;6.2 常见疑难杂症排查问题1明明有索引为什么查询还是慢可能原因1索引失效。例如对索引列使用了函数或计算WHERE YEAR(create_time)2023、使用了OR连接多个条件且部分列无索引、字符串查询未使用最左前缀LIKE ‘%abc‘。可能原因2数据分布不均。例如索引列“状态”有99%的值都是‘A‘查询WHERE status‘A‘时数据库优化器可能认为全表扫描比走索引更快。排查方法使用EXPLAIN查看执行计划确认是否真的使用了索引key列以及扫描行数rows列是否合理。问题2COUNT(*)、COUNT(1)、COUNT(列名)有什么区别哪个快COUNT(*)统计所有行数包括NULL值。这是标准写法也是性能最优的数据库会进行优化。COUNT(1)与COUNT(*)在性能上几乎没有区别统计所有行数。COUNT(列名)统计该列非NULL的行数。如果该列有索引数据库可能会选择更小的索引来计数有时会快一点但语义不同。结论统计总行数无脑用COUNT(*)即可。问题3如何清理数据库中的重复数据-- 假设表duplicate_data中email列有重复我们想保留id最小的那条 DELETE a FROM duplicate_data a INNER JOIN duplicate_data b WHERE a.email b.email AND a.id b.id; -- 或者使用窗口函数更现代 DELETE FROM duplicate_data WHERE id NOT IN ( SELECT MIN(id) FROM duplicate_data GROUP BY email ); -- 注意执行删除前务必先备份或开启事务SQL的世界远不止这些窗口函数、CTE、事务隔离级别、锁机制等等每一个深挖下去都是一片天地。但掌握以上这些内容足以让你应对90%以上的日常开发场景并能写出安全、高效的数据库代码。记住写SQL时心里要时刻装着三件事结果对不对逻辑、跑得快不快性能、安不安全注入。多使用EXPLAIN分析多关注慢查询日志在实践中不断积累感觉你就能从“会写SQL”成长为“懂SQL”的工程师。