SQL慢查询性能优化（生产案例）

SELECT 
    COUNT(1)
FROM
    tb_fault_code tfc
        LEFT JOIN
    tb_pile tp ON tfc.equipment_id = tp.equipment_id
        LEFT JOIN
    tb_fault_desc tfd ON tfc.fault_code = tfd.fault_code
        AND tfc.delete_ind = 0
WHERE
    tp.delete_ind = 0;

SELECT 
    tfc.equipment_id,
    tfc.fault_code,
    tfd.fault_desc AS faultDesc,
    tp.owner_name AS userName,
    tp.tel_number AS phoneNumber,
    tp.city_code AS areaCode,
    tfd.error_type,
    tfd.fault_type,
    FROM_UNIXTIME(tfc.happen_time) AS happenDate,
    tfd.affect_status
FROM
    tb_fault_code tfc
        LEFT JOIN
    tb_pile tp ON tfc.equipment_id = tp.equipment_id
        AND tp.delete_ind = 0
        LEFT JOIN
    tb_fault_desc tfd ON tfc.fault_code = tfd.fault_code
WHERE
    tfc.delete_ind = 0
ORDER BY tfc.update_time DESC
LIMIT 30 OFFSET 0;

ALTER TABLE `tb_fault_code` 
ADD INDEX `idx_update_time` USING BTREE (`update_time`);
ALTER TABLE `tb_fault_code` 
ADD INDEX `idx_fault_code` USING BTREE (`fault_code`);
ALTER TABLE `tb_fault_code` 
ADD INDEX `idx_delete_ind` USING BTREE (`delete_ind`);

ALTER TABLE `tb_pile` 
ADD INDEX `idx_delete_ind` USING BTREE (`delete_ind`);