面试题：MariaDB动态列：复杂应用场景与性能考量

Question

在一个电商系统中，商品的属性种类繁多且不固定，如何利用MariaDB动态列来设计数据库表结构以高效存储商品属性？同时，分析这种设计在查询性能方面可能面临的挑战及应对策略。

Answer 1

数据库表结构设计

1. 创建主商品表：
   • 创建一个主商品表，例如 products，用于存储商品的基本信息，如 product_id（主键，唯一标识每个商品）、product_name、description 等通用字段。

   CREATE TABLE products (
       product_id INT AUTO_INCREMENT PRIMARY KEY,
       product_name VARCHAR(255),
       description TEXT
   );
   

2. 使用动态列存储商品属性：
   • MariaDB 从 10.2 版本开始支持动态列。可以创建一个表来存储商品的动态属性，例如 product_attributes。

   CREATE TABLE product_attributes (
       product_id INT,
       attributes JSON,
       PRIMARY KEY (product_id),
       FOREIGN KEY (product_id) REFERENCES products(product_id)
   );
   

   • 在 product_attributes 表中，product_id 作为外键关联 products 表的 product_id，确保商品属性与商品的对应关系。attributes 列使用 JSON 类型来存储商品的动态属性。例如，一个商品可能有颜色、尺寸等属性，可以存储为 {"color":"red","size":"L"}。

查询性能挑战及应对策略

挑战

1. 全表扫描：
   • 由于动态列（如 JSON 类型列）的查询通常不能直接利用普通索引。如果要查询具有特定属性值的商品，例如查询颜色为红色的商品，可能会导致全表扫描。因为数据库无法像对常规列那样快速定位到符合条件的行。
2. 复杂查询性能：
   • 对于涉及多个属性组合的复杂查询，性能会更差。例如，要查询颜色为红色且尺寸为 L 的商品，数据库需要逐行解析 JSON 数据来判断是否符合条件，这在大数据量下会消耗大量的时间和资源。

应对策略

1. 创建虚拟列和索引：
   • 可以基于 JSON 数据中的属性创建虚拟列，并为虚拟列创建索引。例如，如果经常查询商品的颜色属性：

   ALTER TABLE product_attributes
   ADD COLUMN color VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color'))) VIRTUAL,
   ADD INDEX idx_color (color);
   

   • 这样，查询颜色相关的条件时，就可以利用 idx_color 索引，大大提高查询性能。
2. 优化查询语句：
   • 尽量避免在 JSON 列上进行复杂的函数操作，因为这会阻止索引的使用。例如，不要使用 JSON_EXTRACT 函数在查询条件中直接过滤，而是尽量使用基于虚拟列和索引的查询方式。
3. 数据分区：
   • 根据商品的某些属性或范围（如商品类别等）对表进行分区。例如，如果商品分为电子产品、服装等类别，可以按类别分区。这样在查询时，如果查询条件能命中分区键，可以减少扫描的数据量，提高查询性能。

   -- 假设商品表按类别分区
   CREATE TABLE products (
       product_id INT AUTO_INCREMENT PRIMARY KEY,
       product_name VARCHAR(255),
       description TEXT,
       category VARCHAR(50)
   )
   PARTITION BY LIST (category) (
       PARTITION p1 VALUES IN ('electronics'),
       PARTITION p2 VALUES IN ('clothing')
   );
   

   • 对应的 product_attributes 表也可以根据 product_id 进行相同的分区策略，以配合整体查询优化。