高超SQL技巧助你高效工作
高超SQL技巧助你高效工作
在当今数据驱动的时代，SQL（Structured Query Language）作为与数据库交互的标准语言，已经成为数据处理和分析不可或缺的工具。无论是数据分析师、软件工程师还是数据库管理员，掌握高效的SQL技巧不仅能显著提升工作效率，还能在面试和实际项目开发中为自己加分。本文将从基础优化技巧到高级应用，循序渐进地分享一些实用的SQL技巧。
基础优化技巧
1. 避免使用SELECT *
在编写SQL查询时，我们经常为了方便而直接使用SELECT *来获取表中所有列的数据。然而，这种做法可能会带来性能问题：
- 浪费数据库资源：查询不必要的列会消耗额外的内存和CPU资源
- 增加数据传输时间：多查出来的数据在通过网络IO传输时会增加传输时间
- 影响查询性能：
SELECT *不会走覆盖索引，会导致大量回表操作
正确的做法是只查询需要的列：
-- 错误示例
SELECT * FROM user WHERE id=1;
-- 正确示例
SELECT name, age FROM user WHERE id=1;
2. 使用UNION ALL代替UNION
UNION关键字用于合并两个或多个SELECT语句的结果集，但会自动去除重复的行。而UNION ALL则保留所有行，包括重复的行。在不需要去除重复数据的情况下，使用UNION ALL可以避免额外的排序和比较操作，从而提高性能：
-- 错误示例
(SELECT * FROM user WHERE id=1)
UNION
(SELECT * FROM user WHERE id=2);
-- 正确示例
(SELECT * FROM user WHERE id=1)
UNION ALL
(SELECT * FROM user WHERE id=2);
3. 小表驱动大表
在多表连接查询中，应该让小表驱动大表。例如，如果order表有10000条数据，user表有100条数据，那么在查询有效用户下的订单列表时，应该使用小表（user）来驱动大表（order）：
SELECT * FROM order
WHERE user_id IN (SELECT id FROM user WHERE status=1);
4. 避免在索引列上使用函数
在WHERE子句中，如果索引列被函数或运算符包裹，会导致索引失效。例如：
-- 错误示例
SELECT * FROM user WHERE YEAR(birthdate) = 1990;
-- 正确示例
SELECT * FROM user WHERE birthdate BETWEEN '1990-01-01' AND '1990-12-31';
5. 使用参数化SQL
在应用程序中，使用参数化SQL可以避免SQL注入攻击，同时数据库可以对这些参数化SQL进行预编译，提高执行速度。
6. 调整WHERE子句的连接顺序
DBMS一般采用自下而上的顺序解析WHERE子句，因此应该将过滤条件较多的表放在前面，以减少后续表的扫描范围。
7. 用WITH替换HAVING
避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而WHERE则是在聚合前刷选记录，如果能通过WHERE字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在WHERE字句中。
8. 使用表别名
当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。
9. 考虑使用临时表
将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。
10. 避免不必要的事务
SQL Server中一句SQL语句默认就是一个事务，在该语句执行完成后也是默认commit的。其实，这就是begin tran的一个最小化的形式
子查询和CTE的使用场景
1. CTE（公共表表达式）
CTE具有更好的可读性，支持递归查询，并且不占用存储空间。但是，CTE只能在当前查询中使用。
WITH delivered AS (
SELECT emailaddress, emailid, senddate
FROM emaildelivered
WHERE senddate BETWEEN '2019-01' AND '2019-12'
),
unsubs AS (
SELECT emailaddress, emailid, senddate
FROM emailunsubscribe
WHERE senddate BETWEEN '2019-01' AND '2019-12'
)
SELECT delivered.emailid,
COUNT(delivered.emailaddress) AS countofdelivered,
COUNT(unsubs.emailaddress) AS countofunsubd
FROM delivered
INNER JOIN unsubs ON delivered.emailid = unsubs.emailid
GROUP BY delivered.emailid
ORDER BY delivered.emailid;
2. 子查询
子查询适用于简单的嵌套查询，但多层嵌套时可读性较差。在需要复杂逻辑时，建议使用CTE。
SELECT ord.*, acc.city
FROM (
SELECT accountid, orderid, ordervalue
FROM orderhistory
WHERE ordervalue > '30'
) ord
JOIN account acc ON ord.accountid = acc.accountid
WHERE acc.country = 'China';
聚合函数的高级用法
1. 多层次分组统计
在实际业务中，可能需要对数据进行多层次的分组统计。例如，按年份、月份和部门对销售额进行统计：
SELECT
YEAR(sale_date) AS year,
MONTH(sale_date) AS month,
department,
SUM(amount) AS total_sales
FROM sales
GROUP BY year, month, department
ORDER BY year DESC, month DESC, department;
2. 动态分组
在某些场景下，可能需要根据不同的条件动态分组。例如，按部门和产品类别统计销售总额与平均销售额：
SELECT
department,
product_category,
SUM(amount) AS total_sales,
AVG(amount) AS avg_sales
FROM sales
GROUP BY department, product_category;
3. 使用GROUP_CONCAT合并分组内的元素
在某些情况下，可能需要将分组内的某些列合并成一个字符串。例如，将所有名字拼接成一个字符串：
SELECT
department,
GROUP_CONCAT(name) AS names
FROM employees
GROUP BY department;
SQL索引优化
1. 选择区分度高的列作为索引
索引字段的区分度越高，查询效率越高。可以通过以下方式计算区分度：
SELECT COUNT(DISTINCT c_name) / COUNT(*) AS selectivity
FROM t_name;
2. 合理设置前缀索引长度
对于字符串字段，可以通过测试不同长度的前缀索引，选择既能保证高区分度又节省存储空间的长度：
SELECT
COUNT(DISTINCT LEFT(empname, 3)) / COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(empname, 4)) / COUNT(*) AS sel4,
COUNT(DISTINCT LEFT(empname, 5)) / COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(empname, 6)) / COUNT(*) AS sel6,
COUNT(DISTINCT LEFT(empname, 7)) / COUNT(*) AS sel7
FROM emp;
3. 遵循最左匹配原则
在联合索引中，MySQL会按照从左到右的顺序匹配索引，直到遇到范围查询（>、<、between、like）为止。因此，创建联合索引时需要合理安排列的顺序。
4. 避免索引失效
- 避免在索引列上使用函数或运算符
- 避免不必要的类型转换
- 避免使用模糊查询
%value%
5. 适当使用联合索引
联合索引可以覆盖多个查询条件，但索引数不宜过多，一般不超过6个。在创建联合索引时，需要考虑查询频率和数据分布。
SQL最佳实践
1. 正确性、可读性优先于性能
在编写SQL查询时，优先考虑查询的正确性和代码的可读性，然后再考虑性能优化。
2. 尽量缩小查询范围
在查询开始阶段就尽量缩小数据范围，避免不必要的数据扫描。
3. 熟悉数据表结构和元数据
在编写查询前，先研究表结构和列的元数据，确保对数据有充分的了解。
4. 使用CTE封装复杂逻辑
对于复杂的查询，可以使用CTE将逻辑分层，使代码更清晰易读。
5. 保存常用SQL片段
在Metabase等工具中，可以保存常用的SQL片段，以便在其他查询中复用。
实际案例分析
让我们通过一个实际案例来应用上述技巧。假设我们有以下两个SQL查询语句：
SELECT AVG(`amount`)
FROM `order`
GROUP BY `user_id` AS a
SELECT AVG(a.amount)
FROM a
这两个查询存在以下问题：
GROUP BY子句错误使用别名- 未定义的表别名
a
正确的做法应该是：
SELECT
AVG(avg_amount) AS final_avg
FROM (
SELECT
`user_id`,
AVG(`amount`) AS avg_amount
FROM `order`
GROUP BY `user_id`
) AS a;
在这个修正后的查询中，我们首先计算每个用户的平均订单金额，然后再计算所有用户平均金额的二次平均。通过使用子查询和正确的别名定义，我们解决了原始查询中的问题。
总结
掌握高超的SQL技巧不仅能让你在工作中游刃有余，还能显著提升你的数据处理能力。无论是通过创建合适的索引来加速查询，还是利用子查询解决复杂需求，这些技巧都能帮助你在数据库操作中事半功倍。特别是在面试或实际项目开发中，展示出你的SQL优化能力将大大增加你的竞争力。希望本文分享的这些SQL技巧能帮助你在工作中更加高效！