sql优化

优化原则:

1.避免排序,可以考虑联合索引
2.jion避免被驱动表全表扫描,增加索引

1.问题点:按照id倒排序和分页放在jion之后导致
解决方案:先进行排序和分页已经条件查询,后join

原有sql

SELECT
    a.id,
    b.bu_name buName,
    c.brand_name brandName,
    e.real_name memberName,
    d.id AS personId,
    a.exchange_time exchangeTime,
    a.exchange_points points,
    a.reason reason,
    a.order_id orderId,
    a.detail_reason description,
    a.due_time dueTime
FROM
    t22000_member_points_log a
JOIN t22000_contact e ON a.member_id = e.member_id
JOIN t22000_person d ON e.id = d.contact_id
JOIN t22000_zt_bu b ON a.bu_code = b.bu_code
JOIN t22000_zt_brand c ON a.brand_code = c.brand_code
WHERE
    a.cal_type = 'add'
ORDER BY a.id DESC
LIMIT 200,10

优化后

 SELECT
    a.id,
    b.bu_name buName,
    c.brand_name brandName,
    e.real_name memberName,
    d.id AS personId,
    a.exchange_time exchangeTime,
    a.exchange_points points,
    a.reason reason,
    a.order_id orderId,
    a.detail_reason description,
    a.due_time dueTime
FROM
(SELECT id,member_id, exchange_time,exchange_points,reason,order_id,detail_reason,due_time,bu_code,brand_code FROM t22000_member_points_log WHERE cal_type = 'add' ORDER BY id DESC LIMIT 200,10) a
JOIN t22000_contact e ON a.member_id = e.member_id
JOIN t22000_person d ON e.id = d.contact_id
JOIN t22000_zt_bu b ON a.bu_code = b.bu_code
JOIN t22000_zt_brand c ON a.brand_code = c.brand_code