1. 项目概述为什么我们需要系统性地复习SQL干了这么多年数据开发从写第一行SELECT * FROM users到现在处理上亿级别的数据流水线我越来越觉得SQL这东西入门容易精通难。很多人包括我自己在职业生涯早期都容易陷入一个误区觉得SQL不就是SELECT、INSERT、UPDATE、DELETE那几样吗会写查询不就够用了直到在线上环境因为一个没加索引的JOIN导致全库锁死或者因为一个WHERE子句里的函数调用让查询慢了100倍才真正意识到对SQL语句的系统性理解和复习绝不是“应试”或“面试八股”而是保障数据系统稳定、高效运行的基石。这次整理的初衷源于团队里一位新同事在排查一个慢查询时暴露出的知识断层。问题很简单一个用于报表的查询在测试环境飞快上了生产就超时。一看语句多个表关联WHERE条件里用了DATE_FORMAT(create_time, ‘%Y-%m’) ‘2024-05’而create_time字段上恰巧有个索引。问题就出在这里对索引列使用函数会让数据库优化器无法使用这个索引导致全表扫描。这个点在很多SQL教程里可能就一句话带过但没踩过坑的人很难有深刻体会。所以这篇复习整理我不想做成简单的命令列表或语法手册。那东西网上一搜一大把。我想做的是结合我这些年踩过的坑、优化的案例、面试别人时问过的问题以及从网络社区比如大家常搜的“sql优化”、“慢sql优化”、“sql注入”里看到的常见困惑把SQL中那些容易忽略、容易出错但又至关重要的知识点掰开了、揉碎了讲清楚背后的“为什么”。无论是刚入门的新手还是有一定经验但想查漏补缺的同行希望都能从中找到对自己有用的东西。我们会从最基础的查询逻辑开始深入到性能优化、安全避坑以及一些高级但实用的特性。2. 核心需求解析我们到底要复习什么面对“SQL语句复习”这个标题如果只是罗列语法意义不大。我们需要明确一次有效的复习应该覆盖哪些维度解决哪些实际问题。结合我自己的经验和高频搜索词我梳理了以下几个核心需求层面。2.1 构建牢固的概念与语法体系这是地基。很多奇怪的错误和低效的写法根源在于概念模糊。比如“sql数据库入门基础知识”里常提到的几个关键点集合思维SQL是对结果集的操作要时刻想着你在处理一个集合而不是一行行数据。这是理解JOIN、GROUP BY、子查询的关键。执行顺序这是很多人混淆的地方。书写顺序是SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT但数据库的实际执行顺序是FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT。理解这个顺序就能明白为什么不能在WHERE子句中使用SELECT中定义的别名而HAVING可以。三值逻辑TRUE, FALSE, UNKNOWN这是处理NULL值的基础。NULL NULL的结果不是TRUE而是UNKNOWN。所有与NULL的比较操作, , 等结果都是UNKNOWN。这直接影响了WHERE条件的筛选和JOIN的匹配行为需要用IS NULL或IS NOT NULL来判断。2.2 掌握性能优化的核心方法论“慢sql优化”、“sql调优”是永恒的热点。优化不是玄学有迹可循。复习时需要聚焦索引的艺术什么时候建索引高筛选度字段、JOIN字段、ORDER BY/GROUP BY字段。什么样的查询能用上索引最左前缀原则。哪些写法会导致索引失效如前文提到的对索引列运算或使用函数、类型隐式转换、OR条件不当使用等。执行计划解读EXPLAIN是你的眼睛。要能看懂type字段ALL,index,range,ref,const等key字段实际使用的索引rows字段预估扫描行数Extra字段Using filesort,Using temporary等额外信息。这是诊断慢查询的第一步。避免常见性能陷阱SELECT *特别是宽表、大表JOIN尤其是笛卡尔积、嵌套过深的子查询、在WHERE子句中使用非SARGable可搜索参数的条件等。2.3 筑牢安全防线防范注入风险“sql注入”、“php sql注入”、“sql注入万能密码绕过”这些搜索词背后是大量仍然存在的安全漏洞。复习SQL必须包含安全章节理解注入原理攻击者如何利用应用程序未正确过滤的用户输入拼接出恶意SQL语句达到窃取、篡改、破坏数据的目的。掌握绝对安全的实践使用参数化查询Prepared Statements。这是防止SQL注入的唯一根本方法。无论是Java的PreparedStatement、Python的cursor.execute(“SELECT * FROM users WHERE id %s”, (user_id,))还是PHP的PDO绑定参数原理都是将SQL语句结构与数据分离数据库引擎不会将输入的数据当作代码执行。摒弃危险习惯永远不要使用字符串拼接来构造SQL语句。即使做了转义也可能因漏网之鱼或二次编码问题导致漏洞。2.4 熟悉现代SQL的高级与便利特性SQL标准在不断发展数据库厂商也推出了许多强大特性可以让我们写出更简洁、高效的语句。例如窗口函数用于进行“分组内排序、计算移动平均、累计求和”等复杂分析避免低效的自连接。通用表表达式尤其是递归CTE用于处理树形或图状数据如组织架构、评论链。JSON函数在现代应用中处理半结构化数据越来越常见。MERGE语句实现“有则更新无则插入”的UPSERT操作比先查询再判断更高效。3. 语法精要与深度解析这一部分我们抛开简单的语法罗列深入那些容易产生误解和性能问题的语法细节。我会用“正确写法 vs 错误/低效写法”对比的方式并解释背后的原因。3.1 数据查询SELECT的学问远比你想象的大SELECT语句是SQL的脊梁但90%的性能问题也出自于此。核心要点1明确你需要的数据列-- 低效做法网络慢、浪费内存、可能阻碍覆盖索引 SELECT * FROM orders WHERE user_id 100; -- 高效做法 SELECT order_id, amount, status FROM orders WHERE user_id 100;注意SELECT *在以下场景尤其有害1表字段多宽表2网络传输环境差3某些ORM框架会用它来反射获取元数据频繁使用会导致性能下降。明确列出字段是良好习惯的开始。核心要点2理解WHERE与HAVING的本质区别这是执行顺序决定的。WHERE在分组前GROUP BY过滤原始行。它不能包含聚合函数。HAVING在分组后过滤分组的结果。它通常包含聚合函数。-- 找出总金额超过10000的用户正确 SELECT user_id, SUM(amount) as total_amount FROM orders GROUP BY user_id HAVING SUM(amount) 10000; -- 错误的尝试在WHERE中使用聚合函数 SELECT user_id, SUM(amount) as total_amount FROM orders WHERE SUM(amount) 10000 -- 错误WHERE执行时还没有进行分组和聚合。 GROUP BY user_id;核心要点3JOIN的多种写法与性能考量INNER JOIN、LEFT JOIN是最常用的。要特别注意ON和WHERE在LEFT JOIN中的不同。-- 场景查询所有用户及其订单没有订单的用户也要显示 SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.status ‘paid’; -- 错误这会将没有订单o.status为NULL的用户过滤掉LEFT JOIN失效效果等同于INNER JOIN。 -- 正确写法将针对右表的过滤条件放在ON子句中 SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.status ‘paid’; -- 这样即使o.status条件不满足用户记录仍然会保留关联的订单字段为NULL。3.2 数据操作INSERT,UPDATE,DELETE的陷阱INSERT的批量操作-- 低效多次网络往返 INSERT INTO log (message) VALUES (‘msg1’); INSERT INTO log (message) VALUES (‘msg2’); -- 高效一次插入多行 INSERT INTO log (message) VALUES (‘msg1’), (‘msg2’), (‘msg3’);对于大量数据插入还应考虑使用LOAD DATA INFILEMySQL或COPY命令PostgreSQL等批量导入工具。UPDATE与DELETE务必使用WHERE子句这是老生常谈但血泪教训无数。在执行前先用SELECT确认要影响的数据范围。-- 危险会更新整张表 UPDATE products SET price price * 0.9; -- 安全做法先SELECT确认 SELECT * FROM products WHERE category ‘electronics’ AND stock 0; -- 确认无误后 UPDATE products SET price price * 0.9 WHERE category ‘electronics’ AND stock 0;对于DELETE在重要数据表上我强烈建议采用“软删除”添加一个is_deleted标志位而非物理删除。3.3 数据定义与控制CREATE,ALTER, 权限管理复习时要关注那些影响性能和后续维护的细节。字段类型选择用INT而不是VARCHAR存数字用DATETIME/TIMESTAMP而不是字符串存时间。合适的类型节省空间提升比较和排序速度。索引设计复习“最左前缀原则”。对于复合索引INDEX idx_name (col_a, col_b, col_c)能使用该索引的查询条件包括(col_a),(col_a, col_b),(col_a, col_b, col_c)。但(col_b),(col_c),(col_b, col_c)是无法使用的。约束的使用NOT NULL,DEFAULT,UNIQUE,FOREIGN KEY,CHECK。这些约束不仅保证数据完整性还能给优化器提供更多信息。例如NOT NULL的字段在比较时可以简化逻辑。4. 性能优化实战从执行计划到索引策略理论说再多不如看一个真实的优化案例。假设我们有一张订单表orders结构简化如下CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(20) NOT NULL, -- ‘pending’, ‘paid’, ‘shipped’, ‘cancelled’ create_time DATETIME NOT NULL, INDEX idx_user_id (user_id), INDEX idx_create_time (create_time) );初始问题查询查找2024年5月所有已支付订单按金额降序排列并分页。SELECT id, user_id, amount, create_time FROM orders WHERE DATE_FORMAT(create_time, ‘%Y-%m’) ‘2024-05’ AND status ‘paid’ ORDER BY amount DESC LIMIT 20 OFFSET 0;这个查询很直观但很可能很慢。4.1 第一步查看执行计划在MySQL中使用EXPLAIN FORMATJSON或EXPLAINEXPLAIN SELECT id, user_id, amount, create_time FROM orders WHERE DATE_FORMAT(create_time, ‘%Y-%m’) ‘2024-05’ AND status ‘paid’ ORDER BY amount DESC LIMIT 20;我们可能会看到type:ALL全表扫描key:NULL没有使用索引rows: 非常巨大的数字Extra:Using where; Using filesort诊断Using filesort表示在磁盘或内存中进行了排序开销大。更严重的是因为WHERE条件中对create_time使用了DATE_FORMAT函数导致建立在create_time上的索引idx_create_time失效。数据库只能选择全表扫描并在扫描结果上应用status ‘paid’过滤最后对过滤后的所有结果进行排序。数据量大时性能灾难。4.2 第二步优化改写优化1让索引生效避免对索引列进行运算或使用函数。我们将范围查询改写为BETWEENSELECT id, user_id, amount, create_time FROM orders WHERE create_time ‘2024-05-01 00:00:00’ AND create_time ‘2024-06-01 00:00:00’ AND status ‘paid’ ORDER BY amount DESC LIMIT 20 OFFSET 0;现在idx_create_time索引有可能被用于快速定位2024年5月的记录。但status条件仍在索引之外且排序字段amount没有索引。再次EXPLAINtype可能变为range使用了create_time的范围索引但Extra可能仍有Using filesort并且rows预估还是很多因为status’paid’的筛选是在索引扫描后进行的。优化2建立更合适的复合索引我们的查询条件是create_time和status排序是amount。一个经典的索引设计策略是(筛选列 排序列)。但这里有两个筛选列。考虑到create_time的范围查询是必须的而status是等值查询我们可以建立索引(status, create_time, amount)。注意顺序status等值查询放最左。create_time范围查询放在等值查询之后。amount排序字段放在最后。CREATE INDEX idx_status_createtime_amount ON orders(status, create_time, amount);优化后的查询索引idx_status_createtime_amount可以完美覆盖这个查询status ‘paid’可以直接定位到索引树中status’paid’的这部分。在这部分中create_time是有序的可以快速进行范围查找 ‘2024-05-01’ AND ‘2024-06-01’。查找到的数据行其amount值也在索引中且对于相同的status和create_time范围amount在索引中的顺序就是物理顺序如果索引是(status, create_time, amount)。但是请注意ORDER BY amount DESC要求的是按amount全局降序而我们的索引在status’paid’且create_time在特定范围内的数据块中amount是有序的。如果status’paid’的数据量仍然很大可能还需要filesort。但对于分页取前20条的场景如果status’paid’在5月的记录不多性能提升会非常显著。更极致的优化有时需要根据业务特点调整。例如如果paid状态的订单是少数这个索引很好。如果paid订单是大多数那么这个索引效果会打折扣。4.3 第三步分页深度优化当OFFSET非常大时例如LIMIT 20 OFFSET 100000即使有索引数据库也需要扫描并跳过前10万行成本很高。优化技巧记住上一次的位置-- 第一页 SELECT id, user_id, amount, create_time FROM orders WHERE status’paid’ AND create_time ‘2024-05-01’ ORDER BY amount DESC, id DESC LIMIT 20; -- 假设最后一条记录的amount是 500.00, id是 12345 -- 第二页使用WHERE条件“跳过”上一页的数据 SELECT id, user_id, amount, create_time FROM orders WHERE status’paid’ AND create_time ‘2024-05-01’ AND (amount 500.00 OR (amount 500.00 AND id 12345)) ORDER BY amount DESC, id DESC LIMIT 20;这种方法要求排序字段组合这里是amount DESC, id DESC是唯一的并且前端需要记录最后一条记录的值。5. 安全加固彻底杜绝SQL注入关于SQL注入我的态度是零容忍。这不是一个可以“稍微注意一下”的问题而必须通过工程实践彻底杜绝。5.1 注入原理再现假设一个登录场景的PHP旧代码$username $_POST[‘username’]; $password $_POST[‘password’]; $sql “SELECT * FROM users WHERE username ‘“ . $username . “‘ AND password ‘“ . md5($password) . “‘“;如果用户输入的用户名是admin’ --那么拼接后的SQL变成SELECT * FROM users WHERE username ‘admin’ -- ‘ AND password ‘...’--在SQL中是注释符后面的密码验证条件被注释掉了攻击者可以直接以admin身份登录。这就是经典的“万能密码”绕过。5.2 绝对安全的做法参数化查询在任何语言、任何框架中都使用参数化查询预编译语句。Java (JDBC):String sql “SELECT * FROM users WHERE username ? AND password ?“; PreparedStatement stmt connection.prepareStatement(sql); stmt.setString(1, username); stmt.setString(2, hashedPassword); ResultSet rs stmt.executeQuery();Python (DB-API):cursor.execute(“SELECT * FROM users WHERE username %s AND password %s“, (username, hashed_password))PHP (PDO):$stmt $pdo-prepare(“SELECT * FROM users WHERE username :username AND password :password“); $stmt-execute([‘username’ $username, ‘password’ $hashedPassword]);原理数据库引擎会先将SQL语句模板带占位符进行编译确定执行计划。然后将用户输入的数据作为纯粹的“参数”传入。参数中的内容无论是否包含’、--、;等特殊字符都会被当作数据值处理而永远不会被解释为SQL代码的一部分。这是从机制上根除注入的可能。5.3 其他辅助安全措施最小权限原则应用程序连接数据库的账号不应拥有DROP、DELETE全部表等高危权限。根据操作类型分配只读、只写特定表等权限。输入验证与过滤虽然不能替代参数化查询但作为辅助手段。对输入的类型、长度、格式如邮箱、手机号进行校验。避免动态拼接SQL除了参数化查询在复杂场景如动态表名、列名下如果必须拼接请使用白名单机制。# 错误直接拼接表名 table_name request.args.get(‘table’) sql f“SELECT * FROM {table_name}“ # 危险 # 正确白名单校验 valid_tables {‘users’, ‘products’, ‘orders’} table_name request.args.get(‘table’) if table_name not in valid_tables: raise ValueError(“Invalid table name“) sql f“SELECT * FROM {table_name}“错误信息处理生产环境不要将详细的数据库错误信息直接返回给前端用户避免暴露表结构等敏感信息。6. 高级特性与应用场景掌握了基础和优化、安全后了解一些现代SQL高级特性能让你如虎添翼。6.1 窗口函数超越GROUP BY的分析能力窗口函数可以在不减少行数的情况下进行分组计算。典型场景计算每个部门内员工的薪水排名。SELECT department_id, employee_name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_salary_rank FROM employees;PARTITION BY定义了窗口的分区类似GROUP BY的分组ORDER BY决定了分区内的排序。RANK()是窗口函数之一其他还有ROW_NUMBER(),DENSE_RANK(),SUM() OVER (),AVG() OVER ()等。这比用子查询做自关联要高效和清晰得多。6.2 通用表表达式让复杂查询清晰化CTE可以将一个复杂的查询分解成多个逻辑步骤提高可读性。递归CTE更是处理层次结构数据的利器。-- 非递归CTE计算每个类别的销售总额和占比 WITH category_sales AS ( SELECT category_id, SUM(amount) as total FROM orders GROUP BY category_id ), total_sales AS ( SELECT SUM(total) as grand_total FROM category_sales ) SELECT c.name, cs.total, (cs.total / ts.grand_total) * 100 as percentage FROM category_sales cs JOIN categories c ON cs.category_id c.id CROSS JOIN total_sales ts; -- 递归CTE查询组织架构下所有子部门 WITH RECURSIVE sub_orgs AS ( -- 锚点找到根部门 SELECT id, name, parent_id FROM departments WHERE id 1 UNION ALL -- 递归成员找到锚点或上一轮结果的所有子部门 SELECT d.id, d.name, d.parent_id FROM departments d INNER JOIN sub_orgs so ON d.parent_id so.id ) SELECT * FROM sub_orgs;6.3 关于NULL处理的深入理解NULL是“未知”或“不存在”不是空字符串或0。处理NULL需要特别小心。比较使用IS NULL或IS NOT NULL。聚合函数COUNT(*)计算所有行数COUNT(column)忽略该列为NULL的行。SUM(),AVG(),MAX(),MIN()等都会忽略NULL。与IN和NOT IN的坑SELECT * FROM table_a WHERE id NOT IN (SELECT id FROM table_b);如果table_b中的id有NULL值那么整个NOT IN子查询的结果可能永远是FALSE或UNKNOWN导致查不出任何数据。安全的做法是使用NOT EXISTS或在子查询中排除NULL。7. 常见问题与排查技巧实录这里记录一些我实际工作中遇到的高频问题和解决方法。7.1 索引失效的常见场景速查表场景示例原因与解决方案对索引列使用函数或运算WHERE YEAR(create_time) 2024索引存储的是原始值运算后无法匹配。改为范围查询WHERE create_time ‘2024-01-01’ AND create_time ‘2025-01-01’隐式类型转换WHERE user_id ‘123’(user_id是INT)数据库会将user_id列转换为字符串再比较导致索引失效。确保比较双方类型一致。使用OR连接条件WHERE a 1 OR b 2(a,b有独立索引)可能使索引合并(index_merge)效率低下或直接全表扫描。考虑改用UNION或调整查询逻辑。LIKE以通配符开头WHERE name LIKE ‘%abc%’无法使用B-Tree索引的最左前缀匹配。考虑全文索引或使用LIKE ‘abc%’。复合索引未遵循最左前缀索引(a,b,c)查询WHERE b1 AND c2无法使用该索引。调整查询条件顺序或创建新的索引。数据区分度极低在gender只有’M’,’F’列建索引优化器可能认为全表扫描更快。这种列通常不适合单独建索引。7.2 慢查询日志分析与优化流程开启慢查询日志在MySQL配置中设置long_query_time如2秒并开启slow_query_log。定位慢SQL使用mysqldumpslow或pt-query-digest工具分析慢日志找出最耗时、执行最频繁的语句。使用EXPLAIN分析对找出的慢SQL逐一执行EXPLAIN关注type,key,rows,Extra。针对性优化type为ALL考虑增加索引。Extra出现Using filesort或Using temporary考虑优化ORDER BY/GROUP BY或增加包含排序字段的索引。rows预估远大于实际可能是统计信息过时执行ANALYZE TABLE更新统计信息。测试与验证在测试环境或低峰期验证优化后的SQL再次EXPLAIN确认执行计划是否改善。7.3 连接池与事务管理问题连接数暴涨检查应用代码中是否每次执行SQL都新建连接用完未关闭。务必使用连接池并确保在finally块或使用try-with-resourcesJava等方式释放连接。长事务阻塞一个未提交的写事务可能会阻塞其他读写操作。监控数据库的InnoDB状态查找长时间运行的事务SHOW ENGINE INNODB STATUS。优化业务逻辑避免在事务中进行耗时过长的操作如循环调用RPC、处理大量文件等。死锁死锁在高并发更新时难以完全避免。确保应用有重试机制。可以通过调整事务中语句的顺序尽量以相同的顺序访问资源、减少事务粒度、使用SELECT … FOR UPDATE时尽量精确锁定所需行而不是锁全表来降低死锁概率。7.4 数据库设计与维护心得范式与反范式遵循三范式可以减少数据冗余保证一致性。但在需要极致查询性能的场景如数据仓库、复杂报表适度的反范式设计如增加冗余字段可以避免大量的JOIN操作是空间换时间的典型实践。没有银弹需要权衡。字段选择VARCHAR长度不要随意设得很大够用即可。使用UNSIGNED属性存储非负整数。时间字段统一使用DATETIME或TIMESTAMP并注意时区问题。定期维护对于频繁增删改的表索引碎片会增多。定期如在业务低峰期执行OPTIMIZE TABLEMySQL或REINDEXPostgreSQL可以回收空间、提升性能。同时也要定期清理无用数据归档历史数据。SQL的世界博大精深一次复习不可能面面俱到。但抓住“理解集合操作本质”、“善用索引与执行计划”、“严防死守注入安全”、“适时运用高级特性”这几条主线就能解决工作中绝大多数问题。最重要的还是实践多写、多试、多调优遇到问题勤查文档和社区。把这次整理当作一个知识地图在实际项目中不断去填充和验证各个节点你的SQL功力自然会稳步提升。 1. 项目概述为什么我们需要系统性地复习SQL干了这么多年数据开发从写第一行SELECT * FROM users到现在处理上亿级别的数据流水线我越来越觉得SQL这东西入门容易精通难。很多人包括我自己在职业生涯早期都容易陷入一个误区觉得SQL不就是SELECT、INSERT、UPDATE、DELETE那几样吗会写查询不就够用了直到在线上环境因为一个没加索引的JOIN导致全库锁死或者因为一个WHERE子句里的函数调用让查询慢了100倍才真正意识到对SQL语句的系统性理解和复习绝不是“应试”或“面试八股”而是保障数据系统稳定、高效运行的基石。这次整理的初衷源于团队里一位新同事在排查一个慢查询时暴露出的知识断层。问题很简单一个用于报表的查询在测试环境飞快上了生产就超时。一看语句多个表关联WHERE条件里用了DATE_FORMAT(create_time, ‘%Y-%m’) ‘2024-05’而create_time字段上恰巧有个索引。问题就出在这里对索引列使用函数会让数据库优化器无法使用这个索引导致全表扫描。这个点在很多SQL教程里可能就一句话带过但没踩过坑的人很难有深刻体会。所以这篇复习整理我不想做成简单的命令列表或语法手册。那东西网上一搜一大把。我想做的是结合我这些年踩过的坑、优化的案例、面试别人时问过的问题以及从网络社区比如大家常搜的“sql优化”、“慢sql优化”、“sql注入”里看到的常见困惑把SQL中那些容易忽略、容易出错但又至关重要的知识点掰开了、揉碎了讲清楚背后的“为什么”。无论是刚入门的新手还是有一定经验但想查漏补缺的同行希望都能从中找到对自己有用的东西。我们会从最基础的查询逻辑开始深入到性能优化、安全避坑以及一些高级但实用的特性。2. 核心需求解析我们到底要复习什么面对“SQL语句复习”这个标题如果只是罗列语法意义不大。我们需要明确一次有效的复习应该覆盖哪些维度解决哪些实际问题。结合我自己的经验和高频搜索词我梳理了以下几个核心需求层面。2.1 构建牢固的概念与语法体系这是地基。很多奇怪的错误和低效的写法根源在于概念模糊。比如“sql数据库入门基础知识”里常提到的几个关键点集合思维SQL是对结果集的操作要时刻想着你在处理一个集合而不是一行行数据。这是理解JOIN、GROUP BY、子查询的关键。执行顺序这是很多人混淆的地方。书写顺序是SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT但数据库的实际执行顺序是FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT。理解这个顺序就能明白为什么不能在WHERE子句中使用SELECT中定义的别名而HAVING可以。三值逻辑TRUE, FALSE, UNKNOWN这是处理NULL值的基础。NULL NULL的结果不是TRUE而是UNKNOWN。所有与NULL的比较操作, , 等结果都是UNKNOWN。这直接影响了WHERE条件的筛选和JOIN的匹配行为需要用IS NULL或IS NOT NULL来判断。2.2 掌握性能优化的核心方法论“慢sql优化”、“sql调优”是永恒的热点。优化不是玄学有迹可循。复习时需要聚焦索引的艺术什么时候建索引高筛选度字段、JOIN字段、ORDER BY/GROUP BY字段。什么样的查询能用上索引最左前缀原则。哪些写法会导致索引失效如前文提到的对索引列运算或使用函数、类型隐式转换、OR条件不当使用等。执行计划解读EXPLAIN是你的眼睛。要能看懂type字段ALL,index,range,ref,const等key字段实际使用的索引rows字段预估扫描行数Extra字段Using filesort,Using temporary等额外信息。这是诊断慢查询的第一步。避免常见性能陷阱SELECT *特别是宽表、大表JOIN尤其是笛卡尔积、嵌套过深的子查询、在WHERE子句中使用非SARGable可搜索参数的条件等。2.3 筑牢安全防线防范注入风险“sql注入”、“php sql注入”、“sql注入万能密码绕过”这些搜索词背后是大量仍然存在的安全漏洞。复习SQL必须包含安全章节理解注入原理攻击者如何利用应用程序未正确过滤的用户输入拼接出恶意SQL语句达到窃取、篡改、破坏数据的目的。掌握绝对安全的实践使用参数化查询Prepared Statements。这是防止SQL注入的唯一根本方法。无论是Java的PreparedStatement、Python的cursor.execute(“SELECT * FROM users WHERE id %s”, (user_id,))还是PHP的PDO绑定参数原理都是将SQL语句结构与数据分离数据库引擎不会将输入的数据当作代码执行。摒弃危险习惯永远不要使用字符串拼接来构造SQL语句。即使做了转义也可能因漏网之鱼或二次编码问题导致漏洞。2.4 熟悉现代SQL的高级与便利特性SQL标准在不断发展数据库厂商也推出了许多强大特性可以让我们写出更简洁、高效的语句。例如窗口函数用于进行“分组内排序、计算移动平均、累计求和”等复杂分析避免低效的自连接。通用表表达式尤其是递归CTE用于处理树形或图状数据如组织架构、评论链。JSON函数在现代应用中处理半结构化数据越来越常见。MERGE语句实现“有则更新无则插入”的UPSERT操作比先查询再判断更高效。3. 语法精要与深度解析这一部分我们抛开简单的语法罗列深入那些容易产生误解和性能问题的语法细节。我会用“正确写法 vs 错误/低效写法”对比的方式并解释背后的原因。3.1 数据查询SELECT的学问远比你想象的大SELECT语句是SQL的脊梁但90%的性能问题也出自于此。核心要点1明确你需要的数据列-- 低效做法网络慢、浪费内存、可能阻碍覆盖索引 SELECT * FROM orders WHERE user_id 100; -- 高效做法 SELECT order_id, amount, status FROM orders WHERE user_id 100;注意SELECT *在以下场景尤其有害1表字段多宽表2网络传输环境差3某些ORM框架会用它来反射获取元数据频繁使用会导致性能下降。明确列出字段是良好习惯的开始。核心要点2理解WHERE与HAVING的本质区别这是执行顺序决定的。WHERE在分组前GROUP BY过滤原始行。它不能包含聚合函数。HAVING在分组后过滤分组的结果。它通常包含聚合函数。-- 找出总金额超过10000的用户正确 SELECT user_id, SUM(amount) as total_amount FROM orders GROUP BY user_id HAVING SUM(amount) 10000; -- 错误的尝试在WHERE中使用聚合函数 SELECT user_id, SUM(amount) as total_amount FROM orders WHERE SUM(amount) 10000 -- 错误WHERE执行时还没有进行分组和聚合。 GROUP BY user_id;核心要点3JOIN的多种写法与性能考量INNER JOIN、LEFT JOIN是最常用的。要特别注意ON和WHERE在LEFT JOIN中的不同。-- 场景查询所有用户及其订单没有订单的用户也要显示 SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.status ‘paid’; -- 错误这会将没有订单o.status为NULL的用户过滤掉LEFT JOIN失效效果等同于INNER JOIN。 -- 正确写法将针对右表的过滤条件放在ON子句中 SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.status ‘paid’; -- 这样即使o.status条件不满足用户记录仍然会保留关联的订单字段为NULL。3.2 数据操作INSERT,UPDATE,DELETE的陷阱INSERT的批量操作-- 低效多次网络往返 INSERT INTO log (message) VALUES (‘msg1’); INSERT INTO log (message) VALUES (‘msg2’); -- 高效一次插入多行 INSERT INTO log (message) VALUES (‘msg1’), (‘msg2’), (‘msg3’);对于大量数据插入还应考虑使用LOAD DATA INFILEMySQL或COPY命令PostgreSQL等批量导入工具。UPDATE与DELETE务必使用WHERE子句这是老生常谈但血泪教训无数。在执行前先用SELECT确认要影响的数据范围。-- 危险会更新整张表 UPDATE products SET price price * 0.9; -- 安全做法先SELECT确认 SELECT * FROM products WHERE category ‘electronics’ AND stock 0; -- 确认无误后 UPDATE products SET price price * 0.9 WHERE category ‘electronics’ AND stock 0;对于DELETE在重要数据表上我强烈建议采用“软删除”添加一个is_deleted标志位而非物理删除。3.3 数据定义与控制CREATE,ALTER, 权限管理复习时要关注那些影响性能和后续维护的细节。字段类型选择用INT而不是VARCHAR存数字用DATETIME/TIMESTAMP而不是字符串存时间。合适的类型节省空间提升比较和排序速度。索引设计复习“最左前缀原则”。对于复合索引INDEX idx_name (col_a, col_b, col_c)能使用该索引的查询条件包括(col_a),(col_a, col_b),(col_a, col_b, col_c)。但(col_b),(col_c),(col_b, col_c)是无法使用的。约束的使用NOT NULL,DEFAULT,UNIQUE,FOREIGN KEY,CHECK。这些约束不仅保证数据完整性还能给优化器提供更多信息。例如NOT NULL的字段在比较时可以简化逻辑。4. 性能优化实战从执行计划到索引策略理论说再多不如看一个真实的优化案例。假设我们有一张订单表orders结构简化如下CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(20) NOT NULL, -- ‘pending’, ‘paid’, ‘shipped’, ‘cancelled’ create_time DATETIME NOT NULL, INDEX idx_user_id (user_id), INDEX idx_create_time (create_time) );初始问题查询查找2024年5月所有已支付订单按金额降序排列并分页。SELECT id, user_id, amount, create_time FROM orders WHERE DATE_FORMAT(create_time, ‘%Y-%m’) ‘2024-05’ AND status ‘paid’ ORDER BY amount DESC LIMIT 20 OFFSET 0;这个查询很直观但很可能很慢。4.1 第一步查看执行计划在MySQL中使用EXPLAIN FORMATJSON或EXPLAINEXPLAIN SELECT id, user_id, amount, create_time FROM orders WHERE DATE_FORMAT(create_time, ‘%Y-%m’) ‘2024-05’ AND status ‘paid’ ORDER BY amount DESC LIMIT 20;我们可能会看到type:ALL全表扫描key:NULL没有使用索引rows: 非常巨大的数字Extra:Using where; Using filesort诊断Using filesort表示在磁盘或内存中进行了排序开销大。更严重的是因为WHERE条件中对create_time使用了DATE_FORMAT函数导致建立在create_time上的索引idx_create_time失效。数据库只能选择全表扫描并在扫描结果上应用status ‘paid’过滤最后对过滤后的所有结果进行排序。数据量大时性能灾难。4.2 第二步优化改写优化1让索引生效避免对索引列进行运算或使用函数。我们将范围查询改写为BETWEENSELECT id, user_id, amount, create_time FROM orders WHERE create_time ‘2024-05-01 00:00:00’ AND create_time ‘2024-06-01 00:00:00’ AND status ‘paid’ ORDER BY amount DESC LIMIT 20 OFFSET 0;现在idx_create_time索引有可能被用于快速定位2024年5月的记录。但status条件仍在索引之外且排序字段amount没有索引。再次EXPLAINtype可能变为range使用了create_time的范围索引但Extra可能仍有Using filesort并且rows预估还是很多因为status’paid’的筛选是在索引扫描后进行的。优化2建立更合适的复合索引我们的查询条件是create_time和status排序是amount。一个经典的索引设计策略是(筛选列 排序列)。但这里有两个筛选列。考虑到create_time的范围查询是必须的而status是等值查询我们可以建立索引(status, create_time, amount)。注意顺序status等值查询放最左。create_time范围查询放在等值查询之后。amount排序字段放在最后。CREATE INDEX idx_status_createtime_amount ON orders(status, create_time, amount);优化后的查询索引idx_status_createtime_amount可以完美覆盖这个查询status ‘paid’可以直接定位到索引树中status’paid’的这部分。在这部分中create_time是有序的可以快速进行范围查找 ‘2024-05-01’ AND ‘2024-06-01’。查找到的数据行其amount值也在索引中且对于相同的status和create_time范围amount在索引中的顺序就是物理顺序如果索引是(status, create_time, amount)。但是请注意ORDER BY amount DESC要求的是按amount全局降序而我们的索引在status’paid’且create_time在特定范围内的数据块中amount是有序的。如果status’paid’的数据量仍然很大可能还需要filesort。但对于分页取前20条的场景如果status’paid’在5月的记录不多性能提升会非常显著。更极致的优化有时需要根据业务特点调整。例如如果paid状态的订单是少数这个索引很好。如果paid订单是大多数那么这个索引效果会打折扣。4.3 第三步分页深度优化当OFFSET非常大时例如LIMIT 20 OFFSET 100000即使有索引数据库也需要扫描并跳过前10万行成本很高。优化技巧记住上一次的位置-- 第一页 SELECT id, user_id, amount, create_time FROM orders WHERE status’paid’ AND create_time ‘2024-05-01’ ORDER BY amount DESC, id DESC LIMIT 20; -- 假设最后一条记录的amount是 500.00, id是 12345 -- 第二页使用WHERE条件“跳过”上一页的数据 SELECT id, user_id, amount, create_time FROM orders WHERE status’paid’ AND create_time ‘2024-05-01’ AND (amount 500.00 OR (amount 500.00 AND id 12345)) ORDER BY amount DESC, id DESC LIMIT 20;这种方法要求排序字段组合这里是amount DESC, id DESC是唯一的并且前端需要记录最后一条记录的值。5. 安全加固彻底杜绝SQL注入关于SQL注入我的态度是零容忍。这不是一个可以“稍微注意一下”的问题而必须通过工程实践彻底杜绝。5.1 注入原理再现假设一个登录场景的PHP旧代码$username $_POST[‘username’]; $password $_POST[‘password’]; $sql “SELECT * FROM users WHERE username ‘“ . $username . “‘ AND password ‘“ . md5($password) . “‘“;如果用户输入的用户名是admin’ --那么拼接后的SQL变成SELECT * FROM users WHERE username ‘admin’ -- ‘ AND password ‘...’--在SQL中是注释符后面的密码验证条件被注释掉了攻击者可以直接以admin身份登录。这就是经典的“万能密码”绕过。5.2 绝对安全的做法参数化查询在任何语言、任何框架中都使用参数化查询预编译语句。Java (JDBC):String sql “SELECT * FROM users WHERE username ? AND password ?“; PreparedStatement stmt connection.prepareStatement(sql); stmt.setString(1, username); stmt.setString(2, hashedPassword); ResultSet rs stmt.executeQuery();Python (DB-API):cursor.execute(“SELECT * FROM users WHERE username %s AND password %s“, (username, hashed_password))PHP (PDO):$stmt $pdo-prepare(“SELECT * FROM users WHERE username :username AND password :password“); $stmt-execute([‘username’ $username, ‘password’ $hashedPassword]);原理数据库引擎会先将SQL语句模板带占位符进行编译确定执行计划。然后将用户输入的数据作为纯粹的“参数”传入。参数中的内容无论是否包含’、--、;等特殊字符都会被当作数据值处理而永远不会被解释为SQL代码的一部分。这是从机制上根除注入的可能。5.3 其他辅助安全措施最小权限原则应用程序连接数据库的账号不应拥有DROP、DELETE全部表等高危权限。根据操作类型分配只读、只写特定表等权限。输入验证与过滤虽然不能替代参数化查询但作为辅助手段。对输入的类型、长度、格式如邮箱、手机号进行校验。避免动态拼接SQL除了参数化查询在复杂场景如动态表名、列名下如果必须拼接请使用白名单机制。# 错误直接拼接表名 table_name request.args.get(‘table’) sql f“SELECT * FROM {table_name}“ # 危险 # 正确白名单校验 valid_tables {‘users’, ‘products’, ‘orders’} table_name request.args.get(‘table’) if table_name not in valid_tables: raise ValueError(“Invalid table name“) sql f“SELECT * FROM {table_name}“错误信息处理生产环境不要将详细的数据库错误信息直接返回给前端用户避免暴露表结构等敏感信息。6. 高级特性与应用场景掌握了基础和优化、安全后了解一些现代SQL高级特性能让你如虎添翼。6.1 窗口函数超越GROUP BY的分析能力窗口函数可以在不减少行数的情况下进行分组计算。典型场景计算每个部门内员工的薪水排名。SELECT department_id, employee_name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_salary_rank FROM employees;PARTITION BY定义了窗口的分区类似GROUP BY的分组ORDER BY决定了分区内的排序。RANK()是窗口函数之一其他还有ROW_NUMBER(),DENSE_RANK(),SUM() OVER (),AVG() OVER ()等。这比用子查询做自关联要高效和清晰得多。6.2 通用表表达式让复杂查询清晰化CTE可以将一个复杂的查询分解成多个逻辑步骤提高可读性。递归CTE更是处理层次结构数据的利器。-- 非递归CTE计算每个类别的销售总额和占比 WITH category_sales AS ( SELECT category_id, SUM(amount) as total FROM orders GROUP BY category_id ), total_sales AS ( SELECT SUM(total) as grand_total FROM category_sales ) SELECT c.name, cs.total, (cs.total / ts.grand_total) * 100 as percentage FROM category_sales cs JOIN categories c ON cs.category_id c.id CROSS JOIN total_sales ts; -- 递归CTE查询组织架构下所有子部门 WITH RECURSIVE sub_orgs AS ( -- 锚点找到根部门 SELECT id, name, parent_id FROM departments WHERE id 1 UNION ALL -- 递归成员找到锚点或上一轮结果的所有子部门 SELECT d.id, d.name, d.parent_id FROM departments d INNER JOIN sub_orgs so ON d.parent_id so.id ) SELECT * FROM sub_orgs;6.3 关于NULL处理的深入理解NULL是“未知”或“不存在”不是空字符串或0。处理NULL需要特别小心。比较使用IS NULL或IS NOT NULL。聚合函数COUNT(*)计算所有行数COUNT(column)忽略该列为NULL的行。SUM(),AVG(),MAX(),MIN()等都会忽略NULL。与IN和NOT IN的坑SELECT * FROM table_a WHERE id NOT IN (SELECT id FROM table_b);如果table_b中的id有NULL值那么整个NOT IN子查询的结果可能永远是FALSE或UNKNOWN导致查不出任何数据。安全的做法是使用NOT EXISTS或在子查询中排除NULL。7. 常见问题与排查技巧实录这里记录一些我实际工作中遇到的高频问题和解决方法。7.1 索引失效的常见场景速查表场景示例原因与解决方案对索引列使用函数或运算WHERE YEAR(create_time) 2024索引存储的是原始值运算后无法匹配。改为范围查询WHERE create_time ‘2024-01-01’ AND create_time ‘2025-01-01’隐式类型转换WHERE user_id ‘123’(user_id是INT)数据库会将user_id列转换为字符串再比较导致索引失效。确保比较双方类型一致。使用OR连接条件WHERE a 1 OR b 2(a,b有独立索引)可能使索引合并(index_merge)效率低下或直接全表扫描。考虑改用UNION或调整查询逻辑。LIKE以通配符开头WHERE name LIKE ‘%abc%’无法使用B-Tree索引的最左前缀匹配。考虑全文索引或使用LIKE ‘abc%’。复合索引未遵循最左前缀索引(a,b,c)查询WHERE b1 AND c2无法使用该索引。调整查询条件顺序或创建新的索引。数据区分度极低在gender只有’M’,’F’列建索引优化器可能认为全表扫描更快。这种列通常不适合单独建索引。7.2 慢查询日志分析与优化流程开启慢查询日志在MySQL配置中设置long_query_time如2秒并开启slow_query_log。定位慢SQL使用mysqldumpslow或pt-query-digest工具分析慢日志找出最耗时、执行最频繁的语句。使用EXPLAIN分析对找出的慢SQL逐一执行EXPLAIN关注type,key,rows,Extra。针对性优化type为ALL考虑增加索引。Extra出现Using filesort或Using temporary考虑优化ORDER BY/GROUP BY或增加包含排序字段的索引。rows预估远大于实际可能是统计信息过时执行ANALYZE TABLE更新统计信息。测试与验证在测试环境或低峰期验证优化后的SQL再次EXPLAIN确认执行计划是否改善。7.3 连接池与事务管理问题连接数暴涨检查应用代码中是否每次执行SQL都新建连接用完未关闭。务必使用连接池并确保在finally块或使用try-with-resourcesJava等方式释放连接。长事务阻塞一个未提交的写事务可能会阻塞其他读写操作。监控数据库的InnoDB状态查找长时间运行的事务SHOW ENGINE INNODB STATUS。优化业务逻辑避免在事务中进行耗时过长的操作如循环调用RPC、处理大量文件等。死锁死锁在高并发更新时难以完全避免。确保应用有重试机制。可以通过调整事务中语句的顺序尽量以相同的顺序访问资源、减少事务粒度、使用SELECT … FOR UPDATE时尽量精确锁定所需行而不是锁全表来降低死锁概率。7.4 数据库设计与维护心得范式与反范式遵循三范式可以减少数据冗余保证一致性。但在需要极致查询性能的场景如数据仓库、复杂报表适度的反范式设计如增加冗余字段可以避免大量的JOIN操作是空间换时间的典型实践。没有银弹需要权衡。字段选择VARCHAR长度不要随意设得很大够用即可。使用UNSIGNED属性存储非负整数。时间字段统一使用DATETIME或TIMESTAMP并注意时区问题。定期维护对于频繁增删改的表索引碎片会增多。定期如在业务低峰期执行OPTIMIZE TABLEMySQL或REINDEXPostgreSQL可以回收空间、提升性能。同时也要定期清理无用数据归档历史数据。SQL的世界博大精深一次复习不可能面面俱到。但抓住“理解集合操作本质”、“善用索引与执行计划”、“严防死守注入安全”、“适时运用高级特性”这几条主线就能解决工作中绝大多数问题。最重要的还是实践多写、多试、多调优遇到问题勤查文档和社区。把这次整理当作一个知识地图在实际项目中不断去填充和验证各个节点你的SQL功力自然会稳步提升。 
)
