sql优化

最新推荐文章于 2024-11-12 20:37:49 发布

学会专注

最新推荐文章于 2024-11-12 20:37:49 发布

阅读量224

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/qq_30869501/article/details/97259274

本文探讨了SQL查询优化的重要原则，包括避免排序操作，通过增加索引减少全表扫描，以及调整JOIN操作顺序以提高效率。通过具体案例展示了如何将排序和分页操作前置，显著提升了查询性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

优化原则:

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