面试题：MySQL应用程序性能剖析之高级难度题

数据库架构层面

分库分表：
- 水平分表：将一张表按某一列（如时间、ID等）进行拆分，例如按时间将订单表按月份拆分为不同的表，可减少单表数据量，提升查询性能。例如订单表数据量巨大，按月份拆分后，查询特定月份订单时，只需在对应的表中查找。
- 垂直分表：将大表中不常用或字段长度大的列拆分出去，比如用户表中，将用户简介等大字段拆分到另一张表，查询基本用户信息时可减少I/O。
- 水平分库：按业务或数据特征将数据分布到不同数据库，例如电商系统中，将商品数据和用户数据分别存到不同数据库，降低单个数据库压力。
读写分离：
- 主库负责写操作，从库负责读操作。应用程序根据操作类型选择连接主库或从库。例如，在新闻网站中，文章发布时写主库，用户浏览文章读从库，可有效减轻主库压力，提升读性能。
缓存策略：
- 查询结果缓存：使用Memcached或Redis缓存经常查询且不频繁变动的结果。如电商首页商品列表，可缓存一段时间，减少数据库查询次数。
- 表数据缓存：对于静态数据或变动少的表，可缓存全表数据，如地区表，查询时优先从缓存获取。

索引优化层面

分析查询语句：使用EXPLAIN关键字分析查询语句，查看索引使用情况。例如：

EXPLAIN SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date > '2023 - 01 - 01';

创建复合索引：根据查询条件，将多个字段组合创建索引。比如上述查询，可创建(order_date, customer_id)的复合索引，注意索引字段顺序应与查询条件顺序匹配，以提高索引利用率。
删除冗余索引：避免创建重复或不必要的索引，冗余索引会增加写操作开销，定期分析索引使用情况，删除长时间未使用的索引。

查询语句重写层面

减少子查询：将子查询转换为连接查询，例如：
- 子查询：

SELECT * FROM orders WHERE customer_id = (SELECT customer_id FROM customers WHERE customer_name = 'John');

- 连接查询：

SELECT o.* FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_name = 'John';

避免全表扫描：确保查询条件中有索引字段，若查询条件不适合现有索引，可考虑调整条件或创建新索引。如查询SELECT * FROM products WHERE product_price > 100;，若product_price字段无索引则可能全表扫描，可创建该字段索引优化查询。
使用覆盖索引：查询字段刚好被索引覆盖，减少回表操作。例如：

CREATE INDEX idx_product_name_price ON products (product_name, product_price);
SELECT product_name, product_price FROM products WHERE product_name LIKE 'iPhone%';

具体查询场景优化示例

假设查询场景为：查询2023年购买了电子产品的用户姓名和购买金额。涉及orders表（包含order_id, customer_id, order_date, product_id, amount），customers表（包含customer_id, customer_name），products表（包含product_id, product_name）。

数据库架构优化：
- 若数据量巨大，可按年份对orders表进行水平分表，2023年数据单独存表，减少单表数据量。
- 采用读写分离，读操作走从库，减轻主库压力。
索引优化：
- 对orders表创建(order_date, product_id, customer_id, amount)复合索引，满足查询条件。
- 对customers表按customer_id创建索引，products表按product_id创建索引，用于连接查询。
查询语句重写：
- 原始查询：

SELECT c.customer_name, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE o.order_date >= '2023 - 01 - 01' AND o.order_date < '2024 - 01 - 01' AND p.product_name LIKE '%电子产品%';

- 优化：确保`LIKE`操作尽量用在索引字段前缀匹配，若`product_name`字段无索引可创建。同时分析`EXPLAIN`结果，若索引使用不合理，调整索引或查询条件。如：

-- 假设创建了合适索引
SELECT c.customer_name, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE o.order_date BETWEEN '2023 - 01 - 01' AND '2023 - 12 - 31' AND p.product_name LIKE '电子产品%';

星途面试题库

面试题：MySQL应用程序性能剖析之高级难度题

知识考点

面试题答案

数据库架构层面

索引优化层面

查询语句重写层面

具体查询场景优化示例