面试题：MySQL执行计划优化时索引选择性的分析与应用

计算索引选择性

索引选择性是指索引中不同值的数量与表中记录总数的比率。计算公式为： [选择性 = \frac{索引中不同值的数量}{表中的记录总数}]

例如，假设有一个 users 表，记录总数为1000条，某个索引 index_column 中不同值的数量为100，则该索引的选择性为： [ \frac{100}{1000} = 0.1 ]

根据索引选择性调整或创建索引

高选择性索引：选择性接近1的索引是高选择性索引，这类索引在查询时能快速定位到少量匹配的行，MySQL在生成执行计划时更倾向于使用高选择性索引。例如，在 users 表中，email 字段通常具有较高的选择性（假设每个用户的email都不同），为 email 字段创建索引后，在查询特定 email 用户时效率会很高。
低选择性索引：选择性接近0的索引是低选择性索引，如一个表示性别的字段，只有“男”“女”两个值，这样的索引选择性较低，MySQL可能不会优先使用此类索引，甚至在某些情况下使用索引还不如全表扫描高效。对于低选择性索引，如果查询场景中经常基于该字段过滤大量数据，可能需要考虑其他优化方式，如分区表等，而不是单纯依赖索引。

复杂查询场景及分析

假设有如下三张表：

orders 表：记录订单信息，包含字段 order_id（主键），customer_id，order_date 等。
order_items 表：记录订单中的商品项，包含字段 item_id（主键），order_id，product_id，quantity 等。
products 表：记录商品信息，包含字段 product_id（主键），product_name，category_id 等。

复杂查询如下：

SELECT 
    p.product_name, 
    SUM(oi.quantity) AS total_quantity, 
    AVG(oi.quantity) AS average_quantity
FROM 
    orders o
JOIN 
    order_items oi ON o.order_id = oi.order_id
JOIN 
    products p ON oi.product_id = p.product_id
WHERE 
    o.customer_id = 123
    AND o.order_date BETWEEN '2023 - 01 - 01' AND '2023 - 12 - 31'
    AND p.category_id = 456
GROUP BY 
    p.product_name;

索引选择性分析及优化

orders 表：
- customer_id 字段：如果客户数量较多，该字段选择性较高，为其创建索引可以加快 WHERE o.customer_id = 123 条件的过滤速度。
- order_date 字段：由于日期范围有一定的离散度，选择性也较高，为其创建索引对 o.order_date BETWEEN '2023 - 01 - 01' AND '2023 - 12 - 31' 条件过滤有帮助。可以考虑创建联合索引 (customer_id, order_date)，这样在查询时能更有效地利用索引。
order_items 表：
- order_id 字段：用于连接 orders 表和 order_items 表，是外键，通常具有较高选择性，创建索引可以加快连接速度。
products 表：
- category_id 字段：如果类别数量较多，选择性较高，为其创建索引能加快 p.category_id = 456 条件的过滤。

通过分析索引选择性，并针对上述字段创建合适的索引，可以显著优化该复杂查询的执行计划，提高查询性能。

星途面试题库

面试题：MySQL执行计划优化时索引选择性的分析与应用

知识考点

面试题答案

计算索引选择性

根据索引选择性调整或创建索引

复杂查询场景及分析

索引选择性分析及优化