mysql 纵表和横表

基础概念

纵表（垂直表）和横表（水平表）是数据库设计中两种不同的表结构方式。

• 纵表：将一个实体的所有属性都放在一张表中，每个属性对应一个字段。这种方式的优点是结构清晰，易于理解和维护，但缺点是当实体属性非常多时，表结构会变得非常庞大，查询和维护效率可能会降低。
• 横表：将一个实体的多个属性拆分成多张表，每张表存储一部分属性。这种方式的优点是可以有效地减小单张表的规模，提高查询和维护效率，但缺点是结构相对复杂，需要通过关联查询来获取完整的数据。

相关优势

• 纵表的优势：
  • 结构简单，易于理解和维护。
  • 查询时不需要进行多表关联，性能相对较好。
• 横表的优势：
  • 有效地减小单张表的规模，提高查询和维护效率。
  • 适合存储属性较多且不常用的实体，可以节省存储空间。

类型

• 纵表：所有属性都放在一张表中。
• 横表：将属性拆分成多张表，通过关联查询获取完整数据。

应用场景

• 纵表：适用于实体属性较少且常用的情况，例如用户基本信息表。
• 横表：适用于实体属性较多且不常用的情况，例如电商平台的商品属性表，可以将商品的基本信息和扩展属性分开存储。

遇到的问题及解决方法

问题1：纵表表结构庞大，查询效率低下

原因：当实体属性非常多时，纵表会变得非常庞大，导致查询和维护效率降低。

解决方法：

1. 拆分成横表：将实体属性拆分成多张表，通过关联查询获取完整数据。
2. 索引优化：为常用的查询字段添加索引，提高查询效率。
3. 分库分表：将数据分片存储在多个数据库或表中，提高查询和维护效率。

问题2：横表关联查询复杂，性能瓶颈

原因：横表需要通过多表关联查询来获取完整数据，当关联表较多时，查询性能可能会成为瓶颈。

解决方法：

1. 缓存优化：使用缓存技术（如Redis）缓存常用的查询结果，减少数据库查询次数。
2. 预加载：在业务逻辑中提前加载关联数据，减少实时查询的次数。
3. 优化SQL语句：编写高效的SQL语句，减少不必要的关联查询。

示例代码

假设我们有一个电商平台的商品表，实体属性较多，我们可以将其拆分成两张表：商品基本信息表和商品扩展属性表。

商品基本信息表（product_info）

| 字段名 | 类型 | 描述 | |--------------|---------|----------| | product_id | INT | 商品ID | | product_name | VARCHAR | 商品名称 | | category | VARCHAR | 商品分类 |

商品扩展属性表（product_attribute）

| 字段名 | 类型 | 描述 | |--------------|---------|----------------| | attribute_id | INT | 属性ID | | product_id | INT | 商品ID | | attribute_name| VARCHAR | 属性名称 | | attribute_value| VARCHAR | 属性值 |

查询商品完整信息的SQL语句

SELECT 
    pi.product_id,
    pi.product_name,
    pi.category,
    pa.attribute_name,
    pa.attribute_value
FROM 
    product_info pi
LEFT JOIN 
    product_attribute pa ON pi.product_id = pa.product_id
WHERE 
    pi.product_id = 1;

参考链接

• MySQL数据库设计规范
• MySQL性能优化指南