SQL优化案例-通过添加主表的条件来影响优化器的行为
SQL语句：
SELECT pai.*, pa.apply_no, pa.apply_type, pa.create_by, pa.`status` AS applyStatus,
pa.applier_user_code,
pa.apply_name, pa.business_type, pa.store_id, pa.store_name, erp_spec_id,
erp_product_id,
CASE pa.STATUS WHEN 8 THEN 0 WHEN 10 THEN 0 ELSE CASE
WHEN '2000-01-01' >= pai.newest_
expect_delivery_date THEN 0 ELSE TIMESTAMPDIFF
(DAY, pai.newest_expect_delivery_date, NOW()) END END AS OverDay FROM
purchase_apply_item pai
LEFT JOIN purchase_apply pa ON pai.apply_id = pa.apply_id LEFT JOIN product pro
ON pro.product_id = pai.product_id
LEFT JOIN product_spec spec ON spec.spec_id = pai.spec_id WHERE pa.business_type = 'B2C'
AND pa.business_type = 'B2C' ORDER BY pai.apply_item_id LIMIT 10
SQL语句执行计划：
从执行执行计划可以看出，优化器没有按照表的查询顺序生成正确的执行计划
优化建议：
建议通过给表purchase_apply_item添加WHERE条件，引导优化器使用purchase_apply_item做为主表
建议修改后的SQL如下：
SELECT pai.*, pa.apply_no, pa.apply_type, pa.create_by, pa.`status` AS applyStatus,
pa.applier_user_code, pa.apply_name, pa.business_type, pa.store_id, pa.store_name,
erp_spec_id,
erp_product_id, CASE pa.STATUS WHEN 8 THEN 0 WHEN 10 THEN 0 ELSE CASE
WHEN '2000-01-01' >= pai.newest_expect_delivery_date THEN 0 ELSE TIMESTAMPDIFF
(DAY, pai.newest_expect_delivery_date,
NOW())
END END AS OverDay
FROM purchase_apply_item pai
LEFT JOIN purchase_apply pa ON pai.apply_id = pa.apply_id
LEFT JOIN product pro ON pro.product_id = pai.product_id
LEFT JOIN product_spec spec ON spec.spec_id = pai.spec_id
WHERE pa.business_type = 'B2C' AND pai.space_name in('abc','def')
ORDER BY pai.apply_item_id
LIMIT 10
也可以添加purchase_apply_item表的其它字段，具体看业务上的需求
修改后的执行计划：
修改后的SQL执行时间：
通过添加主表的查询条件，引导主表做为驱动表，从而达到优化的目标，时间从5秒，优化到0.00秒
