冗余字段详讲

淩亂°似流年 2024-03-23 11:59 50阅读 0赞

在MySQL表中，冗余字段是指一个表中已经包含的数据可以通过其他表或其他字段计算出来的数据，但是又在此表中以某种形式出现。冗余字段的存在主要基于数据查询性能和数据一致性的考虑。

冗余字段一般被用作优化查询性能。常见的情况是，某些查询需要聚合或计算大量的数据，如果每次查询都需要重新计算，那么查询的效率将会降低。在这种情况下，可以在表中添加一个冗余字段来存储已经计算好的聚合结果或计算结果，以便查询时直接使用该字段的值，避免重新计算。这样可以提高查询性能，减少数据库负载。

而冗余字段的另一个作用是提高数据的一致性。有些数据由于多个表或多个字段共同决定，如果其中一个表或字段的数据发生变化，那么其他表或字段的数据也会受到影响。此时，可以在表中添加一个冗余字段来储存已经计算好的数据，以保证数值的一致性不会受到影响。

举个例子，比如一个电商网站中有商品表和订单表，订单表需要关联商品表，显示每个订单对应的商品名称和单价。如果每次查询都需要通过商品表来查询，显然会影响查询性能。在这种情况下，可以在订单表中添加冗余字段，将商品名称和单价存储在订单表中，以便每次查询时直接使用冗余字段的值，提高查询性能。

总之，冗余字段在MySQL表中的作用是可以优化查询性能、提高数据的一致性及减少数据库负载等效果。

举一个常见的案例是，在电商网站中，一个订单里需要显示所有商品的总件数和总价格。在不使用冗余字段的情况下，需要在查询订单的时候，对该订单包含的所有商品进行遍历，累加总件数和总价格。如果订单包含的商品数量很多，这个操作可能会消耗大量的时间和数据库资源。

而如果使用冗余字段，在订单表中新增两个字段：`total_quantity` 和 `total_price`，表示该订单包含的所有商品的总件数和总价格。每次插入、删除或修改订单时，自动更新这两个字段的值。这样，在查询订单时，就可以直接从单个表中读取这两个字段的值，而无需进行复杂的计算。这种方式可以大幅提升订单查询的速度，减轻数据库负担。

以下是一个示例的MySQL表定义：

在上述例子中，每次修改、插入和删除订单项时，需要更新订单表中的 `total_quantity` 和 `total_price` 字段。例如，在添加订单项时，可以使用以下SQL语句：

INSERT INTO order (id, customer_id, total_quantity, total_price, created_time, updated_time) 
    VALUES (1, 1, (SELECT IFNULL(SUM(quantity), 0) FROM order_item WHERE order_id = 1), (SELECT IFNULL(SUM(price), 0) FROM order_item WHERE order_id = 1), NOW(), NOW())
    ON DUPLICATE KEY UPDATE 
    customer_id = VALUES(customer_id), 
    total_quantity = (SELECT IFNULL(SUM(quantity), 0) FROM order_item WHERE order_id = 1), 
    total_price = (SELECT IFNULL(SUM(price), 0) FROM order_item WHERE order_id = 1), 
    updated_time = NOW();

这样，当查询订单时，就可以直接从 `order` 表中读取 `total_quantity` 和 `total_price` 字段的值，而无需再计算，从而提升了查询性能。

2023/4/13 23:02:48

![dcbfdc149ebd3d48c5b97066608cb690.png][]

给我设计一个简单的带有雪花id和冗余字段的俩表，以此举例

重试

复制

好的，以下给您设计一个带有雪花ID和冗余字段的订单表和订单商品表：

订单表(`orders`)：

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>BIGINT</td> 
   <td>雪花ID</td> 
  </tr> 
  <tr> 
   <td>user_id</td> 
   <td>BIGINT</td> 
   <td>用户ID</td> 
  </tr> 
  <tr> 
   <td>product_count</td> 
   <td>INT</td> 
   <td>商品数量</td> 
  </tr> 
  <tr> 
   <td>total_price</td> 
   <td>DECIMAL(10,2)</td> 
   <td>订单总价</td> 
  </tr> 
  <tr> 
   <td>created_time</td> 
   <td>DATETIME</td> 
   <td>创建时间</td> 
  </tr> 
  <tr> 
   <td>updated_time</td> 
   <td>DATETIME</td> 
   <td>更新时间</td> 
  </tr> 
 </tbody> 
</table>

订单商品表(`order_items`)：

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>order_id</td> 
   <td>BIGINT</td> 
   <td>订单ID</td> 
  </tr> 
  <tr> 
   <td>product_id</td> 
   <td>BIGINT</td> 
   <td>商品ID</td> 
  </tr> 
  <tr> 
   <td>quantity</td> 
   <td>INT</td> 
   <td>商品数量</td> 
  </tr> 
  <tr> 
   <td>price</td> 
   <td>DECIMAL(10,2)</td> 
   <td>商品价格</td> 
  </tr> 
  <tr> 
   <td>created_time</td> 
   <td>DATETIME</td> 
   <td>创建时间</td> 
  </tr> 
  <tr> 
   <td>updated_time</td> 
   <td>DATETIME</td> 
   <td>更新时间</td> 
  </tr> 
  <tr> 
   <td>total_price</td> 
   <td>DECIMAL(10,2)</td> 
   <td>商品总价</td> 
  </tr> 
 </tbody> 
</table>

在上面的表结构中，`orders` 表用来存储订单信息，`order_items` 表存储订单商品信息。为了更好地维护数据的一致性和查询效率，我们采用了冗余字段 `total_price`，用来存储订单总价和 `order_items` 表中每个商品的总价。

在订单商品表中，`total_price` 字段表示一条订单商品记录的总价，该字段可以通过数量和单价进行计算得到。同时，在插入和更新订单商品记录时，需要更新该字段的值，以保证数据的一致性。

在订单表中，`total_price` 字段表示整个订单的总价，该字段可以通过所有订单商品记录的总价进行累加得到。同时，在插入和更新订单时，也需要更新该字段的值，以保证数据的一致性。

使用雪花ID可以避免ID重复的问题，而冗余字段可以提高查询效率和数据的一致性。

冗余字段怎么提高查询效率的，可以举个例子吗，有或者没有这个字段，效率有何区别

好的，这里举一个例子说明冗余字段如何提高查询效率。

假设我们有一个订单表 `orders`，包含以下字段：

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>BIGINT</td> 
   <td>订单ID</td> 
  </tr> 
  <tr> 
   <td>user_id</td> 
   <td>BIGINT</td> 
   <td>用户ID</td> 
  </tr> 
  <tr> 
   <td>total_price</td> 
   <td>DECIMAL(10,2)</td> 
   <td>订单总价</td> 
  </tr> 
  <tr> 
   <td>order_time</td> 
   <td>DATETIME</td> 
   <td>下单时间</td> 
  </tr> 
  <tr> 
   <td>pay_time</td> 
   <td>DATETIME</td> 
   <td>支付时间</td> 
  </tr> 
  <tr> 
   <td>is_paid</td> 
   <td>BOOL</td> 
   <td>是否支付</td> 
  </tr> 
  <tr> 
   <td>status</td> 
   <td>VARCHAR(50)</td> 
   <td>订单状态</td> 
  </tr> 
 </tbody> 
</table>

假设我们要查询某个用户下的所有已支付且订单状态为 `已完成` 的订单数量，可以写一个SQL查询语句：

SELECT COUNT(*) FROM orders WHERE user_id = 1234 AND is_paid = TRUE AND status = '已完成'

如果没有冗余字段，那么每次查询都需要进行聚合计算，算出满足条件的订单数量。这个计算需要遍历整个订单表，可能会导致比较慢的查询速度，特别是在订单数量非常大的情况下。此时，可以通过添加冗余字段来提高查询效率。

比如，我们可以添加一个 `paid_order_count` 字段，用来记录用户下已支付的订单数量：

每次添加、修改或删除订单时，都需要更新用户的已支付订单数量。可以使用以下SQL语句来更新该字段的值：

UPDATE orders SET paid_order_count = (SELECT COUNT(*) FROM orders WHERE user_id = 1234 AND is_paid = TRUE) WHERE user_id = 1234;

这样，在查询用户下的已支付且订单状态为 `已完成` 的订单数量时，可以直接从订单表中查询 `paid_order_count` 字段的值，而无需进行聚合计算，从而大大提高了查询效率。此时的SQL查询语句为：

SELECT paid_order_count FROM orders WHERE user_id = 1234;

通过冗余字段，可以减少查询的消耗，从而更快地获取到需要的数据信息。

2023/4/13 23:06:18

![6d1af60221f213244537eccfcf747481.png][]

SELECT paid\_order\_count FROM orders WHERE user\_id = 1234;这句不会查询出很多记录吗？

重试

复制

不会的，因为 `paid_order_count` 是一个冗余字段，它的值是每个用户下已支付订单的数量，而不是每个订单的数量。因此，通过查询 `paid_order_count` 来获取某个用户下已支付订单数量的过程，只会返回一行记录，而记录中的 `paid_order_count` 字段值是该用户已支付订单的数量。

在上面的例子中，我们每个订单都会更新 `paid_order_count` 字段，即使这部分数据是相同的。这种情况下可能会导致数据冗余的问题，但是相比于聚合操作所带来的性能开销而言，这种冗余数据所带来的性能损失是可以忽略不计的。

同时，我们也可以在查询语句中添加对 `paid_order_count` 字段的限制，防止该字段数据出现错误或其他非预期的情况。例如：

SELECT paid_order_count FROM orders WHERE user_id = 1234 AND paid_order_count >= 0;

这样，我们就可以保证 `paid_order_count` 字段值是大于等于 0 的数值。

SELECT paid\_order\_count FROM orders WHERE user\_id = 1234;这句话不需要加上已支付的条件吗？

重试

复制

非常抱歉，我的回答不够准确，确实需要添加已支付的限制条件。正确的查询语句应该是：

SELECT paid_order_count FROM orders WHERE user_id = 1234 AND is_paid = TRUE;

这样可以确保查询的是该用户下已支付的订单数量，同时也避免了数据冗余可能带来的错误。感谢您的提醒和指正。

[dcbfdc149ebd3d48c5b97066608cb690.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/3636defce6de49db97834847d145d4f3.png
[6d1af60221f213244537eccfcf747481.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/ec12697a76cd4a95a48272ca0cbfd391.png