面试题：特定场景下字符集对复杂查询性能的优化

Question

假设你正在处理一个多语言的电商数据库，有大量商品描述存储在不同字符集中，并且需要进行复杂的联合查询，包含模糊匹配、排序等操作。请说明如何选择和优化字符集以提升查询性能，并给出具体的优化步骤和可能涉及的MySQL配置参数。

Answer 1

选择合适字符集

1. 通用性：优先考虑UTF - 8或UTF - 8mb4字符集。UTF - 8mb4是UTF - 8的超集，支持完整的Unicode字符集，能处理全球各种语言，通用性强。在电商场景中，面对来自不同地区、不同语言的商品描述，UTF - 8mb4能满足字符存储需求。
2. 性能考量：虽然UTF - 8mb4通用性好，但每个字符占用字节数较多。如果应用场景主要是英文或ASCII字符为主，可考虑latin1字符集，它每个字符仅占1字节，存储和查询性能相对较高。不过要注意，latin1无法处理非ASCII字符。

优化步骤

1. 数据迁移：若当前使用的字符集不合适，需要迁移数据。例如从GBK迁移到UTF - 8mb4，可使用ALTER TABLE语句：

ALTER TABLE products CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

这里products是商品表名，可根据实际情况替换。 2. 索引优化： - 对于模糊匹配，如LIKE操作，建立前缀索引能提升性能。例如：

CREATE INDEX product_desc_index ON products (product_description(20));

这里product_description是商品描述字段名，20表示前缀长度，可根据实际情况调整。 - 对于排序操作，确保排序字段有索引。若同时涉及多个字段排序和联合查询，创建复合索引。例如：

CREATE INDEX combined_index ON products (category, price, product_description(20));

这里category是商品分类字段，price是价格字段，复合索引顺序要根据查询中字段出现顺序和过滤效果确定。 3. 查询优化： - 避免在LIKE操作中使用前置通配符（如LIKE '%keyword'），因为这会导致全表扫描。尽量使用后置通配符（如LIKE 'keyword%'），以便利用前缀索引。 - 在联合查询中，合理使用JOIN类型。例如，使用INNER JOIN代替CROSS JOIN，以减少不必要的数据连接。

可能涉及的MySQL配置参数

1. 字符集相关：
   • character - set - server：设置服务器默认字符集，在my.cnf（Linux）或my.ini（Windows）配置文件中设置为所需字符集，如character - set - server = utf8mb4。
   • collation - server：设置服务器默认排序规则，如collation - server = utf8mb4_unicode_ci。
2. 查询优化相关：
   • innodb_buffer_pool_size：InnoDB存储引擎的缓冲池大小，适当增大可缓存更多数据和索引，提升查询性能。一般可设置为服务器物理内存的60% - 80%，如innodb_buffer_pool_size = 8G。
   • query_cache_type和query_cache_size：查询缓存相关参数，可缓存查询结果，减少重复查询开销。但在高并发写操作场景下可能影响性能，需谨慎使用。例如，设置query_cache_type = 1开启查询缓存，query_cache_size = 64M设置缓存大小。