2 查询数据的技巧

我们编写和执行的大多數 SQL 查詢都是在数据库执行。它是您程序的基石，没有数据会让它沒用。

但这也是刪除大量程序样板的最佳机会，通過使用更花哨的查询方法。
在许多用例中，这些方法还在数据所在的位置进行数据处理时提高性能將其全部转移到您的程序。

这部分將向您展示特殊功能，例如 SQL 中的 for-each 循环，一些 null 处理技巧，您可能会犯的分页错误等等。

你需要仔細查看有关内容使用通用表表达式进行数据优化的技巧；一旦您了解它，你将常常使用它。相信我。

2.1 减少按列分组的数量

		SELECT actors.firstname, actors.lastname, COUNT(*) as count
		FROM actors
		JOIN actors_movies USING(actor_id)
		GROUP BY actors.id

您可能很久以前就知道，在对某些列进行分组时，您必须添加所有 SELECT 列到 GROUP BY。

但是，当您对主键进行分组时，所有列同一张表的可以省略，因为数据库会自动为你添加它们。

您的查询將更短，因此更易阅读和理解。

2.2 用大量测试数据填充表格

		-- MySQL
		SET cte_max_recursion_depth = 4294967295;
		INSERT INTO contacts (firstname, lastname)
		WITH RECURSIVE counter(n) AS(
		 SELECT 1 AS n
		 UNION ALL
		 SELECT n + 1 FROM counter WHERE n < 100000
		)
		SELECT CONCAT('firstname-', counter.n), CONCAT('lastname-', counter.n)
		FROM counter
		-- PostgreSQL
		INSERT INTO contacts (firstname, lastname)
		SELECT CONCAT('firstname-', i), CONCAT('lastname-', i)
		FROM generate_series(1, 100000) as i;

有时您可能需要用许多行填充数据库以进行某些性能测试。

这些数据通常是用假数据生成库和大量代码生成的数据看起來最真实。

而不是慢慢地一个接一个地插入大量测试数据，你让数据库生成许多虚拟行填充表以测试索引的效率。

对于有意义的基准，表格需要真实的数据和值分配。但是您可以使用这种方法用更多的行填充表而不是受您查询的影响。

2.3 使用可空列简化不等式检查

	-- MySQL
	SELECT * FROM example WHERE NOT(column <> 'value');
	-- PostgreSQL
	SELECT * FROM example WHERE column IS DISTINCT FROM 'value';

搜索具有不等于特定值的可空列的行很复杂。

标准方法 col != ‘value’ 不起作用，因為 null 与任何东西都不可比因此不会包含在結果中。

或者总是使用更复杂的(col IS NULL OR col != ‘value’) 获得正确結果的方法。
幸运的是，兩者数据库支持一个特殊的功能來比较不平等的列与包含的空值处理。

2.4 防止除以零错误

  	SELECT visitors_today / NULLIF(visitors_yesterday, 0) FROM logs_aggregated;

在数据库中计算统计数据很容易，您可能已完成了几百次。
但是您可能会在几个月后从这些查詢中收到错误，因为编写查询所做的假设不再有效。

可能是你的网站由於停机，特定日期沒有任何访客，或者您的网上商店昨天第一次没有卖出任何产品。

由于那天沒有可用的行，除以SUM(visitors_yesterday) 將触发错误除法。你应该始終保证你不除以零以防某些数据丟失。

通过將除数从零到空值，您可以消除该问题。

2.5 可空列的排序顺序

  	-- MySQL: NULL values placed first (default)
	SELECT * FROM customers ORDER BY country ASC;
	SELECT * FROM customers ORDER BY country IS NOT NULL, country ASC;
	-- MYSQL: NULL values placed last
	SELECT * FROM customers ORDER BY country IS NULL, country ASC;
	-- PostgreSQL: NULL values placed first
	SELECT * FROM customers ORDER BY country ASC NULLS FIRST;
	-- PostgreSQL_ NULL values placed last (default)
	SELECT * FROM customers ORDER BY country ASC;
	SELECT * FROM customers ORDER BY country ASC NULLS LAST;

对于 MySQL，在升序排序時，任何 NULL 值都將放在所有內容之前方向，但对于 PostgreSQL，它们將在最后。

由于 SQL 标准忘记指定NULL 值的排序順序，他们都必须发明自己的规则。

但是那些值排名不应该由您的数据库技术決定。

应用程序应该控制它以改善用戶体验：用戶排序，例如名单中的名字升序或降序对首先看到空值不感兴趣搜索特定行。

它们应该放在最后，因为它们是最不重要的。但是当搜索缺少信息的行來填充時，它们应该是第一个。

更多内容：: Placement of NULL values for ORDER BY with nullable columns

2.6 分页的确定性排序

	SELECT *
	FROM users
	ORDER BY firstname ASC, lastname ASC, user_id ASC
	LIMIT 20 OFFSET 60;

分页的每一个教程和解释都是错误的！最简单的分页使用 LIMIT 和 OFFSET 关键字跳过某些行的方法实际上存在严重程序问题。

当多行具有相同的值时，不能保证它们的順序，例如许多具有相同名字和姓氏的联系人。

由于并发修改在兩个 SQL 查询或不同执行策略之间的表上，排序结果可能兩次都不同。

如果运气不好，最后一行将显示在当前頁面和下一頁的第一頁。

您应该始终进行订购通过添加更多列去确定，因此每一行都是唯一的，添加主鍵是最直接的方法。

2.7 比 LIMIT OFFSET 更高效的分页

		-- MySQL, PostgreSQL
		SELECT *
		FROM users
		WHERE (firstname, lastname, id) > ('John', 'Doe', 3150)
		ORDER BY firstname ASC, lastname ASC, user_id ASC
		LIMIT 30

虽然前一个提示是朝著正確方向迈出的一步，但它仍然是错误的。插入行時或同時刪除，頁面偏移量將是错误的，因为它沒有正确反映表。

一些联系人將在行被刪除時再次显示或永远不会显示偏移太大或太小。

这些问题的解決方案是一个名为 keyset 的方法分页。
当前頁面上最后一項的值被传达以获取以下內容頁面結果。

对于高页码，它也比 LIMIT OFFSET 分頁快得多但不支持跳转到任意頁面。

2.8 具有安全保证的数据库支持的锁

  		START TRANSACTION;
		SELECT balance FROM account WHERE account_id = 7 FOR UPDATE;
		-- Race condition free update after processing the data
		UPDATE account SET balance = 540 WHERE account_id = 7;
		COMMIT;

我见过的几乎所有程序都容易有竞争条件：从数据库，程序处理一些数据，用新数据更新值。

但该程序不受计算期间发生的任何更新的保护。
一些关键部件受到锁定解決方案的保护，以防止竞争条件。

然而，锁定算法很难构建，因为崩溃的程序逻辑可能错过锁定。

解決問題的时间的自动锁释放方法仍然有效锁太长了。

当你运行任何数据修改查询（例如 UPDATE），数据库会自行所得任何受影响的行，直到事务結束，以保证数据的正確性。

代替在您的程序中使用锁定方法，您可以使用FOR UPDATE 在您的 SELECT 查询上锁定读取数据以防止竞争条件。

这些事务完成或客戶端断开时，锁自动释放。

注意：

更新值的程序逻辑都应该使用锁定，否則会竞争条件將以不需要的结果結束。

而且你必須在任何地方都这样做，只是在某个時候一小部分沒有解決竞争条件问题。

更多内容： Transactional Locking to Prevent Race Conditions

2.9 使用公用表表达式细化数据

	WITH most_popular_products AS (
	 SELECT products.*, COUNT(*) as sales
	 FROM products
	 JOIN users_orders_products USING(product_id)
	 JOIN users_orders USING(order_id)
	 WHERE users_orders.created_at BETWEEN '2022-01-01' AND '2022-06-30'
	 GROUP BY products.product_id
	 ORDER BY COUNT(*) DESC
	 LIMIT 10
	), applicable_users (
	 SELECT DISTINCT users.*
	 FROM users
	 JOIN users_raffle USING(user_id)
	 WHERE users_raffle.correct_answers > 8
	), applicable_users_bought_most_popular_product AS (
	 SELECT applicable_users.user_id, most_popular_products.product_id
	 FROM applicable_users
	 JOIN users_orders USING(order_id)
	 JOIN users_orders_products USING(product_id)
	 JOIN most_popular_products USING(product_id)
	) raffle AS (
	 SELECT product_id, user_id, RANK() OVER(
	 PARTITION BY product_id
	 ORDER BY RANDOM()
	 ) AS winner_order
	 FROM applicable_users_bought_most_popular_product
	)
	SELECT product_id, user_id FROM raffle WHERE winner_order = 1;

当您必須使用复杂的规则从数据库中获取行时，这些规则很难一次完成单個查询，您可以使用公用表表达式 (CTE) 拆分它。

每一步，你可以細化數據並在以後的細化中使用它（甚至多次）以最終得到期望的結果。

而不是传统的方法，例如许多嵌套的子查詢或几十個连接，CTE 更易於阅读，您可以单独调试迭代步驟。

为了性能，这两个方法有些相同。数据可能會改变它在嵌套子查詢內部或通过緩存单个步驟找到更有效的方法多次使用。

2.10 许多相似的第一行 仅支持 PGSQL

	-- PostgreSQL
		SELECT DISTINCT ON (customer_id) *
		FROM orders
		WHERE EXTRACT (YEAR FROM created_at) = 2022
		ORDER BY customer_id ASC, price DESC;

有时你有很多行，而你只想要一个，例如每个客戶。

你可以如前所述坚持 for-each-loop-like lateral join 或使用 PostgreSQL 的与众不同发明。

标准的 DISTINCT 查询將通过与一行的所有列。但是使用展示的功能，您可以指定列的子集去make distinct 並且仅保留排序后的第一個匹配行。

2.11 一个查询中的多个聚合 22

	-- MySQL
	SELECT
	 SUM(released_at = 2001) AS released_2001,
	 SUM(released_at = 2002) AS released_2002,
	 SUM(director = 'Steven Spielberg') AS director_stevenspielberg,
	 SUM(director = 'James Cameron') AS director_jamescameron
	FROM movies
	WHERE streamingservice = 'Netflix';
	-- PostgreSQL
	SELECT
	 COUNT(*) FILTER (WHERE released_at = 2001) AS released_2001,
	 COUNT(*) FILTER (WHERE released_at = 2002) AS released_2002,
	 COUNT(*) FILTER (WHERE director = 'Steven Spielberg') AS
	director_stevenspielberg,
	 COUNT(*) FILTER (WHERE director = 'James Cameron') AS
	director_jamescameron
	FROM movies
	WHERE streamingservice = 'Netflix';

在某些情況下，您需要计算多個不同的统计数据。而不是执行很多查詢，你可以写一個，一次通过数据收集所有信息。

根据您的数据和索引，这可能加快或減慢你的执行时间。你绝对应该在你的应用程序上测试它。

更多内容： Multiple Aggregates in One Query

2.12 限制行Rows也包括Ties 仅支持 PGSQL

		-- PostgreSQL
		SELECT *
		FROM teams
		ORDER BY winning_games DESC
		FETCH FIRST 3 ROWS WITH TIES;

想像一下，您想要对体育联盟的球队进行排名並显示前三名。

在罕見的在这个情況下，至少有 2 支球队在比賽结束时的胜场数相同季。
如果他們都排在第三位，你可能想扩大你的限制以包括两者他們。

WITH TIES 选项正好可以满足这样做的。

每当一些行会是尽管与包含的值具有相同的值，但仍被排除在外，但它们也被包含在內，尽管超出限制。

2.13 快速行计数估计

		-- MySQL
		EXPLAIN FORMAT=TREE SELECT * FROM movies WHERE rating = 'NC-17' AND
		price < 4.99;
		-- PostgreSQL
		EXPLAIN SELECT * FROM movies WHERE rating = 'NC-17' AND price < 4.99;

显示匹配行数是大多数应用程序的关键功能，但它有时难以为大型数据库实施。数据库越大，速度越慢將计算行数。

沒有索引時查詢會很慢帮助计算计数。但即使是現有的索引也无法统计数百個或以千計的索引快。

然而，大概的行数可能就足够了一些用例。

数据库的查询规划器总是计算一个大概的行數对于可以通过向数据库询问执行计划來提取的查询。

2.14 具有间隙填充的基于日期的统计查询

		-- MySQL
		SET cte_max_recursion_depth = 4294967295;
		WITH RECURSIVE dates_without_gaps(day) AS (
		 SELECT DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY) as day
		 UNION ALL
		 SELECT DATE_ADD(day, INTERVAL 1 DAY) as day
		 FROM dates_without_gaps
		 WHERE day < CURRENT_DATE
		)
		SELECT dates_without_gaps.day, COALESCE(SUM(statistics.count), 0)
		FROM dates_without_gaps
		LEFT JOIN statistics ON(statistics.day = dates_without_gaps.day)
		GROUP BY dates_without_gaps.day;
		-- PostgreSQL
		SELECT dates_without_gaps.day, COALESCE(SUM(statistics.count), 0)
		FROM generate_series(
		 CURRENT_DATE - INTERVAL '14 days',
		 CURRENT_DATE,
		 '1 day'
		) as dates_without_gaps(day)
		LEFT JOIN statistics ON(statistics.day = dates_without_gaps.day)
		GROUP BY dates_without_gaps.day;

一些统计计算的结果会有差距，因为沒有信息是保存特定日期。

但不是用应用程序代码回填这些漏洞，可以重組数据库查詢：创建一系列无间隙值作为源加入统计数据。

对于 PostgreSQL，generate_series 函数可能用於创建序列，而对于 MySQL，同样需要使用递归公用表表达式 (CTE)。

更多内容： Fill Gaps in Statistical Time Series Results

2.15 使用 For-Each 循环连接表

		-- MySQL, PostgreSQL
		SELECT customers.*, recent_sales.*
		FROM customers
		LEFT JOIN LATERAL (
		 SELECT *
		 FROM sales
		 WHERE sales.customer_id = customers.id
		 ORDER BY created_at DESC
		 LIMIT 3
		) AS recent_sales ON true;

连接表时，两个表的行根据某些条件连接在一起。

但是，连接条件只能包括不同表的所有匹配行。

这是无法控制的，每次迭代的行数不同，例如限制为每個客戶购买产品，只为最后三個客戶购买产品。

特殊的橫向连接类型结合了连接和子查询。將执行子查询连接源表的每一行。

在该子查询中，您可以例如只选择最后一個一位顾客购买了三类产品。

由于您已经选择了仅匹配的销售每个客戶，一个特殊的 true 连接条件表示將使用所有行。

你可以现在在你的数据中进行 for-each 循环。您已经了解了 SQL 的圣杯！

更多内容： For each loops with LATERAL Joins