1. 数据库查询基础SELECT与WHERE的核心作用在数据库操作中SELECT和WHERE这对黄金组合承担着80%以上的日常查询工作。SELECT语句负责指定要检索的列而WHERE子句则像筛子一样过滤出符合条件的行记录。这种组合查询的效率直接决定了应用系统的响应速度。我见过太多新手在刚开始接触SQL时要么SELECT *全表扫描要么WHERE条件写得乱七八糟导致索引失效。实际上一个优化的SELECT WHERE查询可以比原始写法快上百倍。比如在百万级用户表中查某个人的信息好的查询能在毫秒级返回而糟糕的写法可能让数据库直接卡死。关键认知WHERE条件筛选的本质是让数据库引擎快速定位目标数据块而不是逐行检查每条记录2. 条件筛选的六种核心写法2.1 等值查询最常用SELECT user_id, username, email FROM users WHERE user_id 10086;这是最基本的精确匹配查询当user_id字段有索引时这种查询效率极高。注意字段类型必须匹配用字符串条件查整型字段会导致索引失效。2.2 范围查询BETWEEN优化SELECT product_name, price FROM products WHERE price BETWEEN 50 AND 100;BETWEEN是闭区间包含边界值等效于price 50 AND price 100。对于数值型和日期型字段特别有用但要注意确保范围字段有索引范围不宜过大超过表记录15%时可能全表扫描避免对字符串使用BETWEEN2.3 模糊查询LIKE陷阱SELECT * FROM articles WHERE title LIKE %数据库%;这个%通配符在前的查询会导致全表扫描在百万级数据下是性能杀手。如果必须用LIKE尽量写成数据库%让通配符只出现在末尾。2.4 多条件组合AND/OR优先级SELECT order_id, amount FROM orders WHERE status completed AND (payment_method alipay OR create_time 2023-01-01);注意括号改变运算优先级没有括号时AND会优先于OR执行。复杂的条件组合建议用括号明确逻辑关系。2.5 空值判断IS NULL陷阱SELECT customer_name FROM customers WHERE phone_number IS NOT NULL;判断空值必须用IS NULL/IS NOT NULL用 NULL会得到错误结果。这是SQL标准中一个常见的坑。2.6 集合查询IN的性能秘密SELECT * FROM products WHERE category_id IN (3, 7, 12);IN列表在元素较少时通常100会走索引元素过多会转为全表扫描。大数据量时考虑改用JOIN临时表。3. 高级筛选技巧与优化3.1 索引命中原理WHERE条件能否命中索引取决于字段是否有索引条件写法是否满足最左前缀原则是否使用了索引排斥操作如NOT、!、函数包裹复合索引(a,b,c)的生效示例WHERE a1 AND b2 -- 命中a和b WHERE b1 -- 不命中违反最左前缀3.2 执行计划分析用EXPLAIN查看查询执行计划EXPLAIN SELECT * FROM orders WHERE user_id1001;重点关注type列最好看到const/ref/rangekey列显示实际使用的索引rows列预估扫描行数3.3 参数化查询防注入永远不要拼接SQL字符串# 错误示范SQL注入风险 sql fSELECT * FROM users WHERE username{input_name} # 正确做法参数化查询 cursor.execute(SELECT * FROM users WHERE username%s, (input_name,))4. 实战中的复杂场景处理4.1 分页查询优化典型错误写法性能随页码下降SELECT * FROM logs ORDER BY create_time DESC LIMIT 100000, 10;优化方案记住上一页最后一条的IDSELECT * FROM logs WHERE id last_seen_id ORDER BY id DESC LIMIT 10;4.2 JSON字段查询MySQL 5.7支持JSON类型字段的条件筛选SELECT * FROM products WHERE JSON_EXTRACT(specs, $.weight) 10;4.3 全文检索方案对于文本内容的搜索LIKE效率低下应该用-- MySQL全文索引 SELECT * FROM articles WHERE MATCH(title,content) AGAINST(数据库优化); -- PostgreSQL的tsvector SELECT * FROM docs WHERE to_tsvector(english, body) to_tsquery(sql optimization);5. 性能对比实测数据我在100万记录的测试表中对比了不同写法的执行时间查询类型条件写法执行时间(ms)是否走索引等值查询user_id100860.5是范围查询age BETWEEN 20 AND 3012是模糊查询name LIKE %张%1200否错误空值判断phone NULL800否正确空值判断phone IS NULL1.2是6. 常见错误排查指南6.1 查询结果不符合预期检查清单确认WHERE条件的逻辑关系AND/OR优先级检查字段类型是否匹配字符串vs数字验证NULL值的处理方式查看是否有触发器或视图影响结果6.2 查询性能突然下降可能原因索引失效统计信息过时数据量突变执行计划改变系统负载升高解决方案ANALYZE TABLE users; -- 更新统计信息 FLUSH TABLES; -- 清除缓存6.3 锁等待超时长时间运行的SELECT也可能阻塞其他操作可以-- 设置锁等待超时秒 SET SESSION innodb_lock_wait_timeout 30; -- 使用低优先级读 SELECT /*! LOW_PRIORITY */ * FROM large_table;7. 不同数据库的语法差异7.1 字符串比较MySQL默认不区分大小写PostgreSQL默认区分大小写SQL Server取决于排序规则设置7.2 分页语法-- MySQL SELECT * FROM t LIMIT 10 OFFSET 20; -- PostgreSQL SELECT * FROM t LIMIT 10 OFFSET 20; -- SQL Server SELECT * FROM t ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- Oracle (12c) SELECT * FROM t OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;7.3 日期处理-- MySQL SELECT * FROM events WHERE event_date BETWEEN 2023-01-01 AND 2023-01-31; -- PostgreSQL SELECT * FROM events WHERE event_date BETWEEN 2023-01-01::date AND 2023-01-31::date; -- SQL Server SELECT * FROM orders WHERE order_date CONVERT(datetime, 20230101);8. 实际案例电商系统查询优化某电商平台的商品搜索功能原始SQLSELECT * FROM products WHERE name LIKE %手机% OR description LIKE %手机% ORDER BY create_time DESC LIMIT 100;优化方案建立全文索引拆分查询条件使用延迟关联最终优化版SELECT p.* FROM products p JOIN ( SELECT id FROM products WHERE MATCH(name,description) AGAINST(手机 IN BOOLEAN MODE) ORDER BY create_time DESC LIMIT 100 ) AS tmp ON p.id tmp.id;执行时间从原来的2.3秒降到0.15秒提升超过15倍。 1. 数据库查询基础SELECT与WHERE的核心作用在数据库操作中SELECT和WHERE这对黄金组合承担着80%以上的日常查询工作。SELECT语句负责指定要检索的列而WHERE子句则像筛子一样过滤出符合条件的行记录。这种组合查询的效率直接决定了应用系统的响应速度。我见过太多新手在刚开始接触SQL时要么SELECT *全表扫描要么WHERE条件写得乱七八糟导致索引失效。实际上一个优化的SELECT WHERE查询可以比原始写法快上百倍。比如在百万级用户表中查某个人的信息好的查询能在毫秒级返回而糟糕的写法可能让数据库直接卡死。关键认知WHERE条件筛选的本质是让数据库引擎快速定位目标数据块而不是逐行检查每条记录2. 条件筛选的六种核心写法2.1 等值查询最常用SELECT user_id, username, email FROM users WHERE user_id 10086;这是最基本的精确匹配查询当user_id字段有索引时这种查询效率极高。注意字段类型必须匹配用字符串条件查整型字段会导致索引失效。2.2 范围查询BETWEEN优化SELECT product_name, price FROM products WHERE price BETWEEN 50 AND 100;BETWEEN是闭区间包含边界值等效于price 50 AND price 100。对于数值型和日期型字段特别有用但要注意确保范围字段有索引范围不宜过大超过表记录15%时可能全表扫描避免对字符串使用BETWEEN2.3 模糊查询LIKE陷阱SELECT * FROM articles WHERE title LIKE %数据库%;这个%通配符在前的查询会导致全表扫描在百万级数据下是性能杀手。如果必须用LIKE尽量写成数据库%让通配符只出现在末尾。2.4 多条件组合AND/OR优先级SELECT order_id, amount FROM orders WHERE status completed AND (payment_method alipay OR create_time 2023-01-01);注意括号改变运算优先级没有括号时AND会优先于OR执行。复杂的条件组合建议用括号明确逻辑关系。2.5 空值判断IS NULL陷阱SELECT customer_name FROM customers WHERE phone_number IS NOT NULL;判断空值必须用IS NULL/IS NOT NULL用 NULL会得到错误结果。这是SQL标准中一个常见的坑。2.6 集合查询IN的性能秘密SELECT * FROM products WHERE category_id IN (3, 7, 12);IN列表在元素较少时通常100会走索引元素过多会转为全表扫描。大数据量时考虑改用JOIN临时表。3. 高级筛选技巧与优化3.1 索引命中原理WHERE条件能否命中索引取决于字段是否有索引条件写法是否满足最左前缀原则是否使用了索引排斥操作如NOT、!、函数包裹复合索引(a,b,c)的生效示例WHERE a1 AND b2 -- 命中a和b WHERE b1 -- 不命中违反最左前缀3.2 执行计划分析用EXPLAIN查看查询执行计划EXPLAIN SELECT * FROM orders WHERE user_id1001;重点关注type列最好看到const/ref/rangekey列显示实际使用的索引rows列预估扫描行数3.3 参数化查询防注入永远不要拼接SQL字符串# 错误示范SQL注入风险 sql fSELECT * FROM users WHERE username{input_name} # 正确做法参数化查询 cursor.execute(SELECT * FROM users WHERE username%s, (input_name,))4. 实战中的复杂场景处理4.1 分页查询优化典型错误写法性能随页码下降SELECT * FROM logs ORDER BY create_time DESC LIMIT 100000, 10;优化方案记住上一页最后一条的IDSELECT * FROM logs WHERE id last_seen_id ORDER BY id DESC LIMIT 10;4.2 JSON字段查询MySQL 5.7支持JSON类型字段的条件筛选SELECT * FROM products WHERE JSON_EXTRACT(specs, $.weight) 10;4.3 全文检索方案对于文本内容的搜索LIKE效率低下应该用-- MySQL全文索引 SELECT * FROM articles WHERE MATCH(title,content) AGAINST(数据库优化); -- PostgreSQL的tsvector SELECT * FROM docs WHERE to_tsvector(english, body) to_tsquery(sql optimization);5. 性能对比实测数据我在100万记录的测试表中对比了不同写法的执行时间查询类型条件写法执行时间(ms)是否走索引等值查询user_id100860.5是范围查询age BETWEEN 20 AND 3012是模糊查询name LIKE %张%1200否错误空值判断phone NULL800否正确空值判断phone IS NULL1.2是6. 常见错误排查指南6.1 查询结果不符合预期检查清单确认WHERE条件的逻辑关系AND/OR优先级检查字段类型是否匹配字符串vs数字验证NULL值的处理方式查看是否有触发器或视图影响结果6.2 查询性能突然下降可能原因索引失效统计信息过时数据量突变执行计划改变系统负载升高解决方案ANALYZE TABLE users; -- 更新统计信息 FLUSH TABLES; -- 清除缓存6.3 锁等待超时长时间运行的SELECT也可能阻塞其他操作可以-- 设置锁等待超时秒 SET SESSION innodb_lock_wait_timeout 30; -- 使用低优先级读 SELECT /*! LOW_PRIORITY */ * FROM large_table;7. 不同数据库的语法差异7.1 字符串比较MySQL默认不区分大小写PostgreSQL默认区分大小写SQL Server取决于排序规则设置7.2 分页语法-- MySQL SELECT * FROM t LIMIT 10 OFFSET 20; -- PostgreSQL SELECT * FROM t LIMIT 10 OFFSET 20; -- SQL Server SELECT * FROM t ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- Oracle (12c) SELECT * FROM t OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;7.3 日期处理-- MySQL SELECT * FROM events WHERE event_date BETWEEN 2023-01-01 AND 2023-01-31; -- PostgreSQL SELECT * FROM events WHERE event_date BETWEEN 2023-01-01::date AND 2023-01-31::date; -- SQL Server SELECT * FROM orders WHERE order_date CONVERT(datetime, 20230101);8. 实际案例电商系统查询优化某电商平台的商品搜索功能原始SQLSELECT * FROM products WHERE name LIKE %手机% OR description LIKE %手机% ORDER BY create_time DESC LIMIT 100;优化方案建立全文索引拆分查询条件使用延迟关联最终优化版SELECT p.* FROM products p JOIN ( SELECT id FROM products WHERE MATCH(name,description) AGAINST(手机 IN BOOLEAN MODE) ORDER BY create_time DESC LIMIT 100 ) AS tmp ON p.id tmp.id;执行时间从原来的2.3秒降到0.15秒提升超过15倍。