面试题答案
一键面试1. 性能问题分析
- 全表扫描：在高并发读场景下，如果没有合适的索引，查询某个分类下价格在一定范围内且库存大于某个值的商品时，数据库可能需要对
products表进行全表扫描。随着数据量的增加，全表扫描的性能开销会变得非常大，导致查询响应时间变长，无法满足高并发场景下的性能要求。 - 排序性能：按销量降序排列时，如果没有对销量字段建立合适的索引，数据库可能需要先读取满足条件的所有数据，然后在内存中进行排序，这对于大数据量来说性能较低。
2. 初始查询及 EXPLAIN 分析
假设初始查询语句为：
EXPLAIN SELECT product_id, product_name, price, stock, sales_volume, category_id
FROM products
WHERE category_id = 10 AND price BETWEEN 100 AND 200 AND stock > 50
ORDER BY sales_volume DESC;
- EXPLAIN 结果分析：
- id：表示查询的序列号，通常为 1。
- select_type：一般为
SIMPLE，表示简单查询，不包含子查询或联合查询。 - table：显示查询涉及的表，这里是
products。 - partitions：如果表是分区表，会显示分区信息，否则为
NULL。 - type：可能为
ALL，表示全表扫描，这是性能最差的连接类型。 - possible_keys：可能显示为空，表明没有可用的索引来优化查询。
- key：为
NULL，表示实际没有使用任何索引。 - key_len：
NULL。 - ref：
NULL。 - rows：显示估计需要扫描的行数，数据量越大，该值越大，性能开销越大。
- filtered：表示满足条件的行的百分比，可能较低。
3. 索引优化策略
- 复合索引：创建一个复合索引，包含
category_id、price、stock和sales_volume字段。复合索引的顺序很重要，要遵循最左前缀原则。因为查询条件中category_id是等值查询，price是范围查询，stock也是范围查询，而排序字段是sales_volume。所以索引顺序为(category_id, price, stock, sales_volume)。
CREATE INDEX idx_category_price_stock_sales ON products (category_id, price, stock, sales_volume);
- 原理：复合索引遵循最左前缀原则，查询优化器会优先使用索引的最左前缀来过滤数据。这样在查询时，可以快速定位到满足
category_id条件的记录，然后在这些记录中再根据price和stock进一步过滤，最后按sales_volume排序。因为索引是有序的，所以排序操作可以直接利用索引的顺序，避免了在内存中进行排序，大大提高了查询性能。
4. 优化后查询及 EXPLAIN 验证
优化后的查询语句：
EXPLAIN SELECT product_id, product_name, price, stock, sales_volume, category_id
FROM products
WHERE category_id = 10 AND price BETWEEN 100 AND 200 AND stock > 50
ORDER BY sales_volume DESC;
- EXPLAIN 结果分析：
- id：依然为 1。
- select_type：还是
SIMPLE。 - table：
products。 - partitions：
NULL。 - type：可能变为
range，表示使用索引范围扫描，性能优于全表扫描。 - possible_keys：显示为
idx_category_price_stock_sales，表明优化器找到了可用的索引。 - key：显示为
idx_category_price_stock_sales，说明实际使用了该索引。 - key_len：显示索引使用的长度，根据索引字段类型和定义计算得出。
- ref：可能显示为
const，表示使用常量值进行索引查找。 - rows：估计扫描的行数会大幅减少，因为索引快速定位了满足条件的数据。
- filtered：满足条件的行的百分比可能会提高。
通过以上优化，在高并发读的电商场景下，该查询的性能会得到显著提升，能够更好地满足业务需求。