1 表维护简介

尽管某些应用程序仅包含静态内容，但它们异常时，将不得不一直进行数据修改，如何进行表维护？

虽然这似乎是SQL 中最简单的功能，它仍然有改进的空间应用程序。

永远记住，磁盘在一个周期内可以执行的写入操作数非常有限。

如果你可以减少每秒的操作次数，你的应用程序将是性能更高。

数据操作章节将教你根据信息更新行的技巧其他表，删除重复行或通过删除锁使您的应用程序更快争论。

你应该仔细研究最后一个提示，因为我经常发现这是一个性能问题

1.1 防止对热行更新的锁争用

		-- MySQL
		INSERT INTO tweet_statistics (
		 tweet_id, fanout, likes_count
		) VALUES (
		 1475870220422107137, FLOOR(RAND() * 10), 1
		) ON DUPLICATE KEY UPDATE likes_count = likes_count + VALUES(likes_count);

		INSERT INTO videos (id, created_at, url, founded) VALUES (1b88d1019f1cce9e83667267fd9a500f44, FLOOR(RAND() * 10),https://www.youtube.com/watch?v=b34OKii4mG0) ON DUPLICATE KEY UPDATE founded = founded + VALUES(founded);


		-- PostgreSQL
		INSERT INTO tweet_statistics (
		 tweet_id, fanout, likes_count
		) VALUES (
		 1475870220422107137, FLOOR(RANDOM() * 10), 1
		) ON CONFLICT (tweet_id, fanout) DO UPDATE SET likes_count = tweet_statistics.likes_count + excluded.likes_count;

在某些应用程序中，例如计数器推文的点赞数会不断更新。
在一个流量高峰或流行内容中，计数器可能会在一段时间内更新无数次

其次，由于数据库的并发控制，更新将开始干扰彼此作为一行只能一次被一个事务（查询）锁定。
每次更新将一个接一个地执行，而不是独立行并行执行。

不是更新单行，而是将增量扇出到例如100 个不同的行专用柜台。

比例因子现在增加额外的行数计数器被写入。
这些值稍后聚合为一个值并保存在它们的本来会有锁争用的原始列中。

1.2 基于选择查询的更新

  	-- MySQL
	UPDATE products
	JOIN categories USING(category_id)
	SET price = price_base - price_base * categories.discount;
	-- PostgreSQL
	UPDATE products
	SET price = price_base - price_base * categories.discount
	FROM categories
	WHERE products.category_id = categories.category_id;

表往往不是孤立更新的，而是值信息更新的存儲在其他表中。
例如所有产品打折，每个产品都打折將应用全部产品类別。

而不是执行更新查询的基本方法：對於每個类执行別，您可以通过將产品加入其类別來更新产品。

这个应用程序中的人工连接被数据库以更高效的连接所取代。
关于数据库中有关于此主題的更广泛的文本，可以搜索：UPDATE FROM a select.

1.3 删除重复行

	-- MySQL
	WITH duplicates AS (
	 SELECT id, ROW_NUMBER() OVER(
	 PARTITION BY firstname, lastname, email
	 ORDER BY age DESC
	 ) AS rownum
	 FROM contacts
	)
	DELETE contacts
	FROM contacts
	JOIN duplicates USING(id)
	WHERE duplicates.rownum > 1;

	-- PostgreSQL
	WITH duplicates AS (
	 SELECT id, ROW_NUMBER() OVER(
	 PARTITION BY firstname, lastname, email
	 ORDER BY age DESC
	 ) AS rownum
	 FROM contacts
	)
	DELETE FROM contacts
	USING duplicates
	WHERE contacts.id = duplicates.id AND duplicates.rownum > 1;

一段时间后，大多數程序都会有重复的行，从而导致不良用戶经验、更高的存儲要求和更低的数据库性能。

清洁进程通常在具有复杂分块行为的应用程序代码中实现，因為数据不完全适合內存。

通过使用公用表表达式 (CTE)可以根据保留它们的重要性來识别和排序重复行。
单个delete 查询之后可以刪除所有重复項，但要保留的特定数量除外。
以前复杂的逻辑是通过一个简单的SQL查询來完成的。

更多主题搜索： Delete Duplicate Rows

1.4 批量修改后的表维护

	-- MySQL
	ANALYZE TABLE users;
	-- PostgreSQL
	ANALYZE SKIP_LOCKED users;

数据库需要有关您的表的最新统计信息，例如大約的数量行、数据分佈值等计算最有效的执行方式您的查询。

与每當影响其行的行时自动更改的索引相反数据被创建、更新或刪除，统计数据不會在每次更改時都發发生变化。

仅当超过表的更改阈值時才會触发重新計算。

每當您更改表的很大一部分時，受影响的行可能仍低於統計重新計算阈值，但足以使統計不正確。

某些查詢可能會变得非常慢，数据库根据表的現在不正確的信息预测最佳查詢计划。
因此，您应该分析一個表以在每次重大更改后触发统计重新計算以確保快速查詢。

2 小结

备注和记录，在服务器执行维护时可用。