MySQL中creationDate未建索引的SQL查询优化方案咨询

不需要为creationDate列建索引的优化方案如下： #

1. 先修正SQL语法错误

原语句中quote.creationDate between ('2020-03-01' and '2020-03-10')的多余括号属于错误写法，会导致优化器无法正确解析过滤条件，生成低效执行计划，修改后对应WHERE片段为：

where  quote.creationDate between '2020-03-01' and '2020-03-10' 
and opt.pricingMonthlyAmount = 0 
and opt.pricingOneOffCharges = 0

2. 调整关联顺序，优先过滤小数据集

opt.pricingMonthlyAmount = 0 and opt.pricingOneOffCharges = 0的过滤性通常远高于日期范围过滤，可以先筛选出符合条件的opt记录再关联上游表，大幅减少后续关联的计算量，改写示例：

SELECT 
    quoteId as 'ID of the quote',
    requestType as 'Quote Type',
    type as 'New or Change',
    taskName as 'Quote Status',
    currency.currencyCode as 'Currency Code',
    customer.name as 'Customer Name(Contracting Party)',
    quote.contractingParty_customerId as 'Customer IC01(Contracting Party)',
    customer.salesCountryValue as 'Customer sales country',
    item.fpc_financeProductCodeId as 'fpc',
    opt_filtered.number as "option", 
    opt_filtered.pricingMonthlyAmount as "Monthly existing Charges(Eur).", 
    opt_filtered.pricingOneOffCharges as "Total One-off Charges(EUR)", 
    opt_filtered.customerLabel as "Customer Label",
    item.property as "service infomration"
FROM
-- 前置过滤opt表，缩小关联数据集
(SELECT * FROM pqto01.qto_option WHERE pricingMonthlyAmount = 0 and pricingOneOffCharges = 0) opt_filtered
inner join pqto01.qto_product_item item on opt_filtered.options_productItemId = item.productItemId 
inner join pqto01.qto_quote quote on quote.product_productId = item.productItems_productId  
inner join pqto01.qto_customer customer on customer.customerId = quote.contractingParty_customerId 
inner join pqto01.qto_currency currency on quote.pricingCurrency1_currencyId = currency.currencyId 
where quote.creationDate between '2020-03-01' and '2020-03-10';

3. 补全关联字段的索引

不需要给creationDate加索引，只要保证所有JOIN关联字段的索引完备，就能大幅提升多表关联效率，重点检查以下字段是否已建索引：

• qto_option表的options_productItemId
• qto_product_item表的productItemId、productItems_productId
• qto_quote表的product_productId、contractingParty_customerId、pricingCurrency1_currencyId
• qto_customer表的customerId
• qto_currency表的currencyId

4. 建立联合覆盖索引避免回表

针对opt表的高频过滤字段，建立包含所有查询所需字段的联合覆盖索引，不需要回表查询原始数据就能返回结果，索引示例：

CREATE INDEX idx_opt_pricing_cover ON pqto01.qto_option (pricingMonthlyAmount, pricingOneOffCharges, options_productItemId, number, customerLabel);

5. 强制优化器使用更优执行计划

如果MySQL优化器还是选择quote作为驱动表，可以用STRAIGHT_JOIN强制按照你写的表顺序关联，保证过滤后的小表opt作为驱动表，只需要在SELECT后面加STRAIGHT_JOIN关键字即可。

内容的提问来源于stack exchange，提问作者beingumang