数据库中表维护

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 Tair(兼容Redis),内存型 2GB
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
简介: 【5月更文挑战第7天】本文介绍了提高数据库性能的五个技巧。1) 使用`ON DUPLICATE KEY UPDATE`或`ON CONFLICT DO UPDATE`避免锁竞争,尤其在高并发更新计数器场景下。2) 通过JOIN查询进行基于选择的更新。3) 使用公式表达式-CTE-删除重复行 。4) 定期运行`ANALYZE`命令更新表统计信息。这些方法有助于优化数据库性能,减少锁等待和提高查询速度。

1 前言

尽管某些应用程序仅包含静态内容,但它们是异常,您将不得不一直进行数据修改。

question_ans.png

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

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

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

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

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

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

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

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


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

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

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

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

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

3 基于选择查询的更新

      -- 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.

4 删除重复行

    -- 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

5 批量修改后的表维护

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

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

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

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

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

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

6 小结

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

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
SQL 关系型数据库 MySQL
【MySQL】— —熟练掌握用SQL语句实现数据库和基本表的创建。熟练掌握MySQL的安装、客户端登录方法;熟练掌握MySQL的编码、数据类型等基础知识;掌握实体完整性的定义和维护方法、掌握参照完整性
【MySQL】— —熟练掌握用SQL语句实现数据库和基本表的创建。熟练掌握MySQL的安装、客户端登录方法;熟练掌握MySQL的编码、数据类型等基础知识;掌握实体完整性的定义和维护方法、掌握参照完整性
|
SQL 数据库 数据库管理
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(一)模式、表、索引与视图
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(一)模式、表、索引与视图
324 11
|
SQL 监控 关系型数据库
实时计算 Flink版操作报错合集之在设置监控PostgreSQL数据库时,将wal_level设置为logical,出现一些表更新和删除操作报错,怎么办
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
SQL 存储 数据库
SQL 撤销索引、撤销表以及撤销数据库
SQL 撤销索引、撤销表以及撤销数据库
152 4
|
SQL 存储 数据管理
数据管理DMS产品使用合集之如何把整个数据库的表和数据全部导出来
阿里云数据管理DMS提供了全面的数据管理、数据库运维、数据安全、数据迁移与同步等功能,助力企业高效、安全地进行数据库管理和运维工作。以下是DMS产品使用合集的详细介绍。
240 2
|
SQL Java 持续交付
实时计算 Flink版产品使用问题之源数据库一直在新增表或修改表结构,需要进行相应的修改和重启,该如何简化
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
SQL 关系型数据库 MySQL
【MySQL】——用SQL语句实现数据库和基本表的创建
【MySQL】——用SQL语句实现数据库和基本表的创建
【MySQL】——用SQL语句实现数据库和基本表的创建
|
SQL Apache 数据库
doris数据库的表重命名语法是什么
【6月更文挑战第24天】doris数据库的表重命名语法是什么
2552 0
|
存储 数据库连接 数据库
逆向学习数据库篇:表设计和数据库操作的核心概念与流程
逆向学习数据库篇:表设计和数据库操作的核心概念与流程
86 0
|
NoSQL 关系型数据库 MySQL
Redis进阶-select 1. /xxx 切换数据库DBSIZE- 获取当前数据库中的key的个数flushdb-删除当前数据的所有keyflushall-删除所有表的所有库Re
Redis进阶-select 1. /xxx 切换数据库DBSIZE- 获取当前数据库中的key的个数flushdb-删除当前数据的所有keyflushall-删除所有表的所有库Re