面试题：复杂场景下MySQL位数据类型与其他数据类型的协同优化

Question

在一个电商系统中，需要记录商品的多种属性，部分属性适合用BIT类型存储（如是否促销、是否新品等），部分属性适合用其他常规数据类型（如价格、库存数量等）。系统面临高并发读写的压力，要求在保证数据一致性的前提下优化性能。请详细阐述如何设计数据库表结构以及制定相应的查询优化策略，以应对该复杂场景。

Answer 1

数据库表结构设计

1. 商品主表：
   • 商品ID：使用自增长整数类型（如INT AUTO_INCREMENT）作为主键，唯一标识每个商品。这可以确保数据的唯一性和快速定位。
   • 商品名称：根据商品名称长度使用合适的字符串类型，如VARCHAR。避免使用TEXT类型，除非商品名称长度极长，因为VARCHAR在存储和查询性能上更优。
   • 价格：使用DECIMAL类型，例如DECIMAL(10,2)，精确表示货币金额，避免使用FLOAT或DOUBLE，因为它们在表示精确小数时可能会出现精度问题。
   • 库存数量：使用整数类型（如INT），能够高效存储和更新库存数量。对于库存数量特别大的场景，可以考虑使用BIGINT。
2. 商品属性表：
   • 属性ID：自增长整数类型作为主键。
   • 商品ID：外键，关联商品主表的商品ID，用于建立商品与属性的关系。
   • 属性名称：VARCHAR类型，存储属性的名称，如“是否促销”“是否新品”等。
   • 属性值：对于BIT类型的属性，使用BIT类型存储，例如“是否促销”可以用BIT(1)，0表示否，1表示是；对于其他常规类型的属性值，根据具体情况选择合适的数据类型，如VARCHAR、INT等。这样将不同类型的属性统一存储在属性表中，便于管理和扩展。
3. 索引设计：
   • 在商品主表的商品ID、价格、库存数量等经常用于查询和排序的字段上创建索引。例如，为价格字段创建普通索引，可以加快按价格范围查询商品的速度。
   • 在商品属性表的商品ID字段上创建索引，以加速根据商品ID查询属性的操作。同时，对于常用的属性名称字段（如“是否促销”“是否新品”），如果经常用于查询，也可以创建索引。

查询优化策略

1. 缓存策略：
   • 应用层缓存：在应用程序端使用缓存技术，如Redis。对于经常查询且不经常变化的数据，如商品基本信息、促销商品列表等，可以缓存到Redis中。当有查询请求时，先从缓存中获取数据，如果缓存中不存在，再查询数据库，并将查询结果存入缓存。这样可以大大减轻数据库的压力，提高响应速度。
   • 数据库查询缓存：如果使用的数据库支持查询缓存（如MySQL的Query Cache），可以根据实际情况开启。但需要注意，查询缓存对于写操作频繁的场景可能效果不佳，因为每次数据更新都需要更新缓存。所以，要根据业务读写比例合理配置查询缓存。
2. 读写分离：
   • 使用主从复制架构，将写操作（如商品信息更新、库存调整等）发送到主数据库，而读操作（如商品查询、属性查询等）发送到从数据库。从数据库可以配置多个，以分担读压力。这样可以有效提高系统在高并发读情况下的性能。同时，要确保主从复制的延迟在可接受范围内，以保证数据一致性。
3. 批量操作：
   • 对于插入、更新操作，尽量采用批量操作。例如，一次性插入多个商品的属性数据，而不是单个插入。这样可以减少数据库的交互次数，提高操作效率。在更新库存时，如果有多个商品库存需要调整，可以批量构建更新语句，而不是逐个更新。
4. 查询语句优化：
   • 避免使用SELECT *，明确指定需要查询的字段。这样可以减少数据库返回的数据量，提高查询速度。
   • 在使用JOIN查询商品主表和属性表时，确保JOIN条件正确，并且在关联字段上有合适的索引。例如，使用INNER JOIN时，ON条件中的商品ID字段应该有索引。
   • 对于复杂查询，可以使用EXPLAIN关键字分析查询计划，找出性能瓶颈，优化查询语句。如调整WHERE子句中的条件顺序，确保索引能够正确使用等。