sql查询优化实际案例

1、第一步：sql优化

正对于海量数据的查询优化，且外键关联比较多的情况，通常情况是下sql层面的优化，有些时候是由于sql不合理的编写导致，如尽量少使用sql内查询等

如：避免使用 left join  (select * form table) a 等查询，一般这种都可以在sql 层面进行优化操作

2、第二步：创建索引

创建索引首当注意的是要匹配最佳左前缀法则，避免创建的索引失效，其实使用EXPLAIN关键字最索引进行分析，查看命中情况，然后适当调整索引的命中策略

3、第三步：通过前面两种还是未能显著提高效率，可分两次查询，减少索引的回表次数

如：第一次通过复杂的操作只差数据的主键ID，第二次直接通过主键ID的集合去查找完整的数据记录，即可获得不错的效果提升

        实操案例：

        第一次查询：

SELECT
	a.id,
FROM
    LEFT JOIN device_info b on a.terminal_id = b.id
    LEFT JOIN car_info c ON c.id = b.carid
    LEFT JOIN device_alarm_settings d on d.alarmnumber = a.alarm_number
    LEFT JOIN sys_auth_dept f on f.deptid = b.deptid and f.is_delete = 0
WHERE
	a.start_time >= '2023-11-23 00:00:00' 
	AND a.start_time <= '2023-11-25 10:55:38' 
	AND f.deptidnew LIKE CONCAT( '001', '%' ) 
GROUP BY
	a.id 
ORDER BY
	a.start_time DESC 
	LIMIT 50,
	10

        第二次查询只需要通过第一次的ID集合查询，即可获得数据，且全部命中索引：

SELECT
        a.id,
        a.is_handle AS isHandle,
        m.driver_code AS driverCode,
        m.drivername,
        c.carnumber,
        d.minclass_name AS minclassName,
        d.confirm,
        a.start_time AS startTime,
        a.end_time AS endTime,
        a.alarm_info AS alarmInfo,
        a.start_location AS startLocation,
        b.carid,
        a.end_location AS endLocation,
        a.handle_content AS handleContent,
        e.loginname,
        a.handle_time AS handleTime,
        a.end_lon AS endLon,
        a.end_lat AS endLat,
        a.start_lon AS startLon,
        a.start_lat AS startLat,
        b.terminal,
        a.alarm_number AS alarmNumber,
        f.deptname AS deptname,
        b.channeltotals,
        a.alarm_duration alarmDuration,
        mrm.rule_name AS ruleName,
        v.logic_channel AS videoChannel,
        v.url AS videoUrl,
        p.channelid AS photoChannel,
        p.multimediapath AS photoUrl,
        att.file_path,
        att.file_type
        FROM
        device_alarm a
        LEFT JOIN device_info b ON a.terminal_id = b.id
        LEFT JOIN car_info c ON c.id = b.carid
        LEFT JOIN device_alarm_settings d ON d.alarmnumber = a.alarm_number
        LEFT JOIN sys_auth_user e ON a.handle_by = e.userid
        LEFT JOIN car_driver_link cdl ON c.id = cdl.carid
        LEFT JOIN car_driver_info m ON m.id = cdl.driverid
        LEFT JOIN device_video_file_record v ON v.alarm_id = a.id
        LEFT JOIN device_media_info p ON a.id = p.alarmid
        LEFT JOIN monitor_rule_maintain mrm ON mrm.id = a.rule_id
        LEFT JOIN device_safety_alarm sda ON sda.device_alarm_id = a.id
        LEFT JOIN device_safety_attachment att ON att.safety_alarm_id = sda.id
        JOIN sys_auth_dept f ON f.deptid = b.deptid
        AND f.is_delete = 0
        WHERE
            a.id in 
        <foreach collection="idList" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>

先后两次关联表相同，只有where条件不同，却能得出意想不到的效果，其中奥妙一言以概之：“通过减少回表次数，增加查找次数来提高整体数据的相应速度”