MySQL Sakila 与 World 练习答案（三）：性能与综合接口
对应题目：mysql-sakila-world-query-exercises.md。
分册导航：Sakila S01～S24 · World W01～W16
执行计划会随 MySQL 版本、索引、统计信息和数据量变化。下面给出检查方法和通常预期，不能伪造某台数据库的 rows、actual time。
P01 时间函数过滤与范围过滤
EXPLAIN
SELECT payment_id, payment_date
FROM payment
WHERE DATE(payment_date) = ''2005-08-01'';
EXPLAIN
SELECT payment_id, payment_date
FROM payment
WHERE payment_date >= ''2005-08-01 00:00:00''
AND payment_date < ''2005-08-02 00:00:00'';
SHOW INDEX FROM payment;
两者在相同会话时区和 DATETIME 语义下目标结果相同。DATE(payment_date) 对列做函数运算，普通 payment_date B-Tree 通常不能直接做范围定位；左闭右开写法具有可搜索性。若表上根本没有 payment_date 索引，两者仍可能全表扫描，此时不能把“都扫描”误解为写法没有区别。可在测试环境增加合适索引后再比较。
P02 WHERE 与 HAVING
EXPLAIN
SELECT customer_id, SUM(amount) AS total_amount
FROM payment
WHERE customer_id = :customer_id
GROUP BY customer_id;
EXPLAIN
SELECT customer_id, SUM(amount) AS total_amount
FROM payment
GROUP BY customer_id
HAVING customer_id = :customer_id;
客户 ID 是明细行条件，放 WHERE 更符合语义，也明确要求聚合前缩小输入。MySQL 可能把第二种条件下推，但是否下推应查看 EXPLAIN，业务代码不应依赖优化器替开发者表达正确阶段。
P03 深分页与游标分页
SELECT ID, Name, Population
FROM city
ORDER BY Population DESC, ID DESC
LIMIT 4000, 20;
SELECT ID, Name, Population
FROM city
WHERE (Population, ID) < (:last_population, :last_id)
ORDER BY Population DESC, ID DESC
LIMIT 20;
OFFSET 查询至少要定位并丢弃前 4000 行，再返回 20 行；游标查询可从边界继续。若业务规模需要，可评估：
CREATE INDEX idx_city_population_id
ON city (Population DESC, ID DESC);
建索引前必须检查现有索引、写入成本和真实执行计划。一亿行下，大 OFFSET 的扫描、排序和回表成本会明显扩大。
P04 SELECT 星号与明确字段
EXPLAIN
SELECT *
FROM film
WHERE film_id BETWEEN 1 AND 100;
EXPLAIN
SELECT film_id, title, rating, rental_rate
FROM film
WHERE film_id BETWEEN 1 AND 100;
主键范围定位可能相同，但明确字段减少网络、驱动解码和 Node.js 对象内存。只有查询所需列全部存在于某个索引中时才可能成为覆盖索引；第二条并不自动保证覆盖。包含 TEXT/BLOB 的宽表中差异更明显。
P05 检查 JOIN 放大
SELECT COUNT(*) FROM film WHERE film_id = :film_id;
SELECT COUNT(*)
FROM film AS f
JOIN inventory AS i ON i.film_id = f.film_id
WHERE f.film_id = :film_id;
SELECT COUNT(*)
FROM film AS f
JOIN inventory AS i ON i.film_id = f.film_id
JOIN rental AS r ON r.inventory_id = i.inventory_id
WHERE f.film_id = :film_id;
SELECT COUNT(*)
FROM film AS f
JOIN inventory AS i ON i.film_id = f.film_id
JOIN rental AS r ON r.inventory_id = i.inventory_id
JOIN payment AS p ON p.rental_id = r.rental_id
WHERE f.film_id = :film_id;
再同时观察业务键：
SELECT
COUNT(DISTINCT i.inventory_id) AS inventories,
COUNT(DISTINCT r.rental_id) AS rentals,
COUNT(p.payment_id) AS payments,
SUM(p.amount) AS payment_amount
FROM inventory AS i
LEFT JOIN rental AS r ON r.inventory_id = i.inventory_id
LEFT JOIN payment AS p ON p.rental_id = r.rental_id
WHERE i.film_id = :film_id;
一部 film 有多份 inventory，一份 inventory 有多次 rental，一次 rental 在模式层可能对应支付记录。聚合前必须明确要统计库存、租赁还是支付。
P06 EXISTS 与 JOIN
存在性写法：
SELECT cu.customer_id, cu.first_name, cu.last_name
FROM customer AS cu
WHERE EXISTS (
SELECT 1
FROM rental AS r
WHERE r.customer_id = cu.customer_id
)
ORDER BY cu.customer_id;
JOIN 写法先把右侧压到每客户一行：
SELECT cu.customer_id, cu.first_name, cu.last_name
FROM customer AS cu
JOIN (
SELECT customer_id
FROM rental
GROUP BY customer_id
) AS customers_with_rentals
ON customers_with_rentals.customer_id = cu.customer_id
ORDER BY cu.customer_id;
业务只问“是否存在”时 EXISTS 更直接，也不需要用 DISTINCT 掩盖一对多结果。
P07 低选择性字段
SELECT active, COUNT(*) AS customer_count
FROM customer
GROUP BY active;
EXPLAIN
SELECT customer_id, last_name
FROM customer
WHERE active = 1;
如果绝大多数客户 active=1，单列索引可能要访问表中大部分记录，优化器可能认为全表扫描更便宜。索引价值取决于选择性、返回列和查询组合；例如真实查询经常使用状态、创建时间和稳定排序时，才评估相应联合索引，而不是看见 WHERE 字段就建索引。
P08 EXPLAIN ANALYZE
测试示例：
EXPLAIN ANALYZE
SELECT
cu.customer_id,
cu.last_name,
SUM(p.amount) AS total_amount
FROM customer AS cu
JOIN payment AS p ON p.customer_id = cu.customer_id
WHERE cu.active = 1
AND p.payment_date >= ''2005-08-01''
AND p.payment_date < ''2005-09-01''
GROUP BY cu.customer_id, cu.last_name
ORDER BY total_amount DESC, cu.customer_id
LIMIT 20;
记录每个节点的预计 rows、实际 rows、actual time、loops、访问方式和索引。预计与实际差异很大可能意味着统计信息、列相关性或过滤估算不准；loops 很高的内层节点可能放大成本。EXPLAIN ANALYZE 会真实执行查询，只能在受控环境使用。
C01 客户管理列表
避免 payment 与 rental 明细互相相乘，先分别聚合：
WITH payment_totals AS (
SELECT
customer_id,
COUNT(*) AS payment_count,
SUM(amount) AS total_amount
FROM payment
GROUP BY customer_id
),
ranked_rentals AS (
SELECT
r.customer_id,
r.rental_id,
r.rental_date,
ROW_NUMBER() OVER (
PARTITION BY r.customer_id
ORDER BY r.rental_date DESC, r.rental_id DESC
) AS row_number_in_customer
FROM rental AS r
)
SELECT
cu.customer_id,
cu.first_name,
cu.last_name,
cu.email,
cu.active,
co.country,
COALESCE(pt.payment_count, 0) AS payment_count,
COALESCE(pt.total_amount, 0.00) AS total_amount,
rr.rental_date AS latest_rental_date
FROM customer AS cu
JOIN address AS a ON a.address_id = cu.address_id
JOIN city AS ci ON ci.city_id = a.city_id
JOIN country AS co ON co.country_id = ci.country_id
LEFT JOIN payment_totals AS pt ON pt.customer_id = cu.customer_id
LEFT JOIN ranked_rentals AS rr
ON rr.customer_id = cu.customer_id
AND rr.row_number_in_customer = 1
WHERE (:active IS NULL OR cu.active = :active)
AND (:last_name_prefix IS NULL
OR cu.last_name LIKE CONCAT(:last_name_prefix, ''%''))
AND (:country_id IS NULL OR co.country_id = :country_id)
ORDER BY total_amount DESC, cu.customer_id
LIMIT :page_size OFFSET :offset;
可选条件的 OR 写法方便展示，但可能影响索引选择。真实后端可使用白名单动态组装“是否加入整个条件”，值仍参数化。total_amount 与 customer_id 共同保证稳定排序；大数据应改为包含两者的游标分页。total 查询应单独执行与筛选条件一致的 COUNT，不要对分页结果取 length。
C02 电影详情
详情接口不必强行一条巨大 SQL。推荐少量职责明确的查询，在同一请求中有限并发执行。
基础信息与语言：
SELECT
f.film_id, f.title, f.description, f.release_year,
f.rental_duration, f.rental_rate, f.length,
f.replacement_cost, f.rating,
l.name AS language_name
FROM film AS f
JOIN language AS l ON l.language_id = f.language_id
WHERE f.film_id = :film_id;
分类：
SELECT c.category_id, c.name
FROM film_category AS fc
JOIN category AS c ON c.category_id = fc.category_id
WHERE fc.film_id = :film_id
ORDER BY c.name, c.category_id;
演员：
SELECT
a.actor_id,
CONCAT_WS('' '', a.first_name, a.last_name) AS actor_name
FROM film_actor AS fa
JOIN actor AS a ON a.actor_id = fa.actor_id
WHERE fa.film_id = :film_id
ORDER BY a.last_name, a.first_name, a.actor_id;
各门店库存和可出租数量：
SELECT
s.store_id,
COUNT(i.inventory_id) AS inventory_count,
SUM(
CASE WHEN i.inventory_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM rental AS r
WHERE r.inventory_id = i.inventory_id
AND r.return_date IS NULL
)
THEN 1 ELSE 0 END
) AS available_inventory_count
FROM store AS s
LEFT JOIN inventory AS i
ON i.store_id = s.store_id
AND i.film_id = :film_id
GROUP BY s.store_id
ORDER BY s.store_id;
累计出租次数：
SELECT COUNT(r.rental_id) AS rental_count
FROM inventory AS i
LEFT JOIN rental AS r ON r.inventory_id = i.inventory_id
WHERE i.film_id = :film_id;
先验证基础电影存在，再组合结果。不要把分类、演员、库存、租赁全部 JOIN 后依靠 GROUP_CONCAT(DISTINCT …) 修补乘法膨胀。
C03 国家统计列表
城市和语言分别聚合，最大城市单独排名：
WITH city_statistics AS (
SELECT
CountryCode,
COUNT(*) AS city_count,
SUM(Population) AS city_population
FROM city
GROUP BY CountryCode
),
ranked_cities AS (
SELECT
CountryCode,
ID,
Name,
Population,
ROW_NUMBER() OVER (
PARTITION BY CountryCode
ORDER BY Population DESC, ID
) AS position_in_country
FROM city
),
official_language_statistics AS (
SELECT
CountryCode,
COUNT(*) AS official_language_count
FROM countrylanguage
WHERE IsOfficial = ''T''
GROUP BY CountryCode
)
SELECT
co.Code AS country_code,
co.Name AS country_name,
co.Continent AS continent,
co.Region AS region,
co.Population AS country_population,
COALESCE(cs.city_count, 0) AS city_count,
COALESCE(cs.city_population, 0) AS city_population,
rc.Name AS largest_city_name,
rc.Population AS largest_city_population,
COALESCE(ols.official_language_count, 0)
AS official_language_count,
capital.Name AS capital_name
FROM country AS co
LEFT JOIN city_statistics AS cs ON cs.CountryCode = co.Code
LEFT JOIN ranked_cities AS rc
ON rc.CountryCode = co.Code
AND rc.position_in_country = 1
LEFT JOIN official_language_statistics AS ols
ON ols.CountryCode = co.Code
LEFT JOIN city AS capital ON capital.ID = co.Capital
WHERE (:continent IS NULL OR co.Continent = :continent)
ORDER BY co.Population DESC, co.Code
LIMIT :page_size OFFSET :offset;
因为 city 和 countrylanguage 已各自压缩为每国家一行，所以不会交叉相乘。可选筛选、LIMIT 参数和深分页处理方式与 C01 相同；列表 total 另做相同 WHERE 条件的 COUNT。