sql数据库优化从哪些方面(sql数据库优化的几种方法)

导语：分享一则sql优化案例：生产数据库从385s优化到16.8s

概述

这条sql是典型的在数据量增加的情况下，mysql数据库自动选择了另一个执行计划，这里只要通过改写sql来实现该sql的优化，仅供参考。

1、定位慢sql

至于怎么获取到该问题sql，实际上只需要跑一下慢查询查一下就可以看到了..

有兴趣的朋友也可以看下之前介绍的慢查询平台来获取慢sql...

pt-query-digest slow.log --since &39; --until &39;  > /tmp/tms-slow.log

2、分析问题sql

可以看出只是查询一条记录但耗时385秒

SELECT DISTINCTt1.id,t1.shipment_no,t1.vehicle_no,t1.driver1_name,DATE_FORMAT( t1.latest_pickup_time, &39; ) AS latest_pickup_date,DATE_FORMAT( t1.latest_pickup_time, &39; ) AS latest_pickup,t1.latest_pickup_time,t1.version,t1.domain_name,t1.insert_user FROMfsl_shipment t1LEFT JOIN fsl_order_movement_unit t2 ON t1.id = t2.shipmentLEFT JOIN fsl_order_release t3 ON t2.order_release = t3.id WHEREt1.project_code = &39; AND t1.shipment_no IS NOT NULL AND t1.shipment_status IN ( &39;, &39; ) AND t1.is_a_shipment = &39; AND t1.sendncicflag IS NULL AND t3.customer = &39; AND t1.custom_type IN (&39;,&39;)

对应的执行计划如下：

对应的表数据量情况如下：

3、业测环境测试

这里要说一下为什么在业测环境之所以只需要0.7s，其实是因为生产环境的t3表customer结果集比较大，导致先筛选t1表，在筛选t2表，最后筛选t3表，导致耗时接近400s；而UAT环境的t3表customer结果集小时则先筛选t3表，最后再筛选t1表，速度在1秒内。

4、改写sql优化

这里耗时16s。

SELECT DISTINCTt1.id,t1.shipment_no,t1.vehicle_no,t1.driver1_name,DATE_FORMAT( t1.latest_pickup_time, &39; ) AS latest_pickup_date,DATE_FORMAT( t1.latest_pickup_time, &39; ) AS latest_pickup,t1.latest_pickup_time,t1.version,t1.domain_name,t1.insert_user FROMfsl_shipment t1LEFT JOIN fsl_order_movement_unit t2 ON t1.id = t2.shipmentLEFT JOIN (select id from fsl_order_release  where  customer = &39;) t3   ON t2.order_release = t3.id WHEREt1.project_code = &39; AND t1.shipment_no IS NOT NULL AND t1.shipment_status IN ( &39;, &39; ) AND t1.is_a_shipment = &39; AND t1.sendncicflag IS NULL AND t1.custom_type IN (&39;,&39;);

对应的执行计划如下;

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下！

本文内容由小鸣整理编辑！