SELECT re.operator_id AS operator_account, t3.cn_name AS operator_name, t3.email AS operator_email, user.account AS user_account, user.cn_name AS user_name, user.email AS user_email, re.change_detail, re.change_time FROM t_user_permission_change_record re INNER JOIN sec_user ON (re.user_id = user.id) INNER JOIN sec_user t3 ON (re.operator_id = t3.account) WHERE1=1 ORDER BY re.change_time DESC; 帮我优化这句sql，使得查询更加快速

这是一句查询语句，其中有多个表（t_user_permission_change_record、sec_user、sec_user）被连接起来，并且使用了 INNER JOIN 操作符进行连接。

为了优化这句查询语句，可以考虑以下几点：

1. 使用索引来加快查询速度：如果你的表中有大量数据，那么创建索引可以加快查询的速度。你可以创建索引的字段包括 WHERE 子句中的条件字段、INNER JOIN 子句中的连接字段以及 ORDER BY 子句中的排序字段。

2. 优化 INNER JOIN 连接的顺序：在这句语句中，三个表都使用了 INNER JOIN 连接。通常来说，把数据量少的表放在前面，数据量大的表放在后面可以加快查询速度。

3. 使用 LIMIT 子句限制返回的记录数：如果你只需要返回一小部分记录，可以使用 LIMIT 子句来限制返回的记录数。这样可以避免查询出大量不必要的记录，从而加快查询速度。

希望这些建议对你有所帮助。

相关问题

帮我优化一下这个sql select e.id, e.mobile, e.encoded, CASE e.is_echo WHEN 1 THEN '已回传' else '未回传' END AS isEcho , e.order_no AS orderNo, e.pay_amount AS payAmount, e.operator_id AS operatorId, e.operator_name AS operatorName, e.operator_time AS operatorTime, e.remarks AS remarks, e.`status`, CASE p.pay_status WHEN 1 THEN '支付成功' WHEN 2 THEN '支付失败' END AS payStatus , CASE e.status WHEN 1 THEN '待支付' WHEN 2 THEN '已支付' WHEN 3 THEN '已退款' WHEN 4 THEN '订单关闭' WHEN 5 THEN '退款中' WHEN 6 THEN '退款关闭' END AS statusName, e.create_time AS createTime, u.id AS userId, p.pay_channel AS payChannel, CASE p.pay_channel WHEN 1 THEN '支付宝' WHEN 2 THEN '微信' END AS payChannelName , p.out_trade_no AS outTradeNo, e.third_party_channel AS thirdPartyChannel, info.return_amount AS returnAmount, info.return_phone AS returnPhone, info.return_name AS returnName from equity.equity_order_info e left join equity.user_info u on e.mobile = u.user_photo LEFT JOIN ( SELECT * FROM equity.pay_message GROUP BY order_no ) AS p ON p.order_no = e.order_no left join equity.refund_info AS info ON info.order_no=e.order_no <where> <if test="mobile != null and mobile != ''">and e.mobile = #{mobile}</if> <if test="orderNo != null and orderNo != ''">and e.order_no = #{orderNo}</if> <if test="payAmount != null ">and e.pay_amount = #{payAmount}</if> <if test="thirdPartyChannel != null and thirdPartyChannel != ''">and e.third_party_channel = #{thirdPartyChannel} </if> <if test="outTradeNo != null and outTradeNo != ''">and p.out_trade_no = #{outTradeNo} </if> <if test="startTime != null and startTime != '' and endTime != null and endTime != ''"> and DATE_FORMAT(e.create_time,'%Y-%m-%d') BETWEEN DATE_FORMAT(#{startTime},'%Y-%m-%d') AND DATE_FORMAT(#{endTime},'%Y-%m-%d') </if> <if test="status != null and status > 0">and e.status = #{status}</if> <if test="userId != null ">and u.id = #{userId}</if> </where> ORDER BY e.create_time DESC

#{mobile}</if>
<if test="startTime != null">and e.create_time &gt;= #{startTime}</if>
<if test="endTime != null">and e.create_time &lt;= #{endTime}</if>
<if test="status != null">and e.`status` = #{status}</if>
<if test="payStatus != null">and p.pay_status = #{payStatus}</if>
<if test="payChannel != null">and p.pay_channel = #{payChannel}</if>
</where>
ORDER BY e.create_time DESC, e.id DESC
LIMIT #{start},#{length}</code></pre>

优化建议：
1. 尽可能避免使用SELECT *
2. group by 关键字比其他的聚合函数效率低，尽量避免使用
3. 减少使用CASE WHEN，可以使用join来加快速度
4. 尽量减少WHERE子句中的嵌套IF条件，可以使用join来加快速度
5. 为需要检索的列创建索引，如E.mobile、P.pay_channel、E.`status`等

SELECT PIS.SHOW_FLT_DETAIL AS SHOW_FLT_DETAIL -- new , PIS.SHOW_AWB_DETAIL AS SHOW_AWB_DETAIL -- new , PIS.DISPLAY_AIRLINE_CODE AS CARRIER_CODE , DECODE(PIS.REVERT_FLOW,'N',PIS.FLOW_TYPE,DECODE(PIS.FLOW_TYPE,'I','E','I')) AS FLOW_TYPE , PIS.SHIP_TO_LOCATION AS SHIP_TO_LOCATION , PIS.INVOICE_SEQUENCE AS INVOICE_SEQUENCE , PFT.FLIGHT_DATE AS FLIGHT_DATE , PFT.FLIGHT_CARRIER_CODE AS FLIGHT_CARRIER_CODE , PFT.FLIGHT_SERIAL_NUMBER AS FLIGHT_SERIAL_NUMBER , PFT.FLOW_TYPE AS AIRCRAFT_FLOW , FAST.AIRCRAFT_SERVICE_TYPE AS AIRCRAFT_SERVICE_TYPE , PPT.AWB_NUMBER AS AWB_NUMBER , PPT.WEIGHT AS WEIGHT , PPT.CARGO_HANDLING_OPERATOR AS CARGO_HANDLING_OPERATOR , PPT.SHIPMENT_PACKING_TYPE AS SHIPMENT_PACKING_TYPE , PPT.SHIPMENT_FLOW_TYPE AS SHIPMENT_FLOW_TYPE , PPT.SHIPMENT_BUILD_TYPE AS SHIPMENT_BUILD_TYPE , PPT.SHIPMENT_CARGO_TYPE AS SHIPMENT_CARGO_TYPE , PPT.REVENUE_TYPE AS REVENUE_TYPE , PFT.JV_FLIGHT_CARRIER_CODE AS JV_FLIGHT_CARRIER_CODE , PPT.PORT_TONNAGE_UID AS PORT_TONNAGE_UID , PPT.AWB_UID AS AWB_UID , PIS.INVOICE_SEPARATION_UID AS INVOICE_SEPARATION_UID , PFT.FLIGHT_TONNAGE_UID AS FLIGHT_TONNAGE_UID FROM PN_FLT_TONNAGES PFT , FZ_AIRLINES FA , PN_TONNAGE_FLT_PORTS PTFP , PN_PORT_TONNAGES PPT , FF_AIRCRAFT_SERVICE_TYPES FAST , SR_PN_INVOICE_SEPARATIONS PIS --new , SR_PN_INVOICE_SEP_DETAILS PISD--new , SR_PN_INV_SEP_PORT_TONNAGES PISPT --new WHERE PFT.FLIGHT_OPERATION_DATE >= trunc( CASE :rundate WHEN TO_DATE('01/01/1900', 'DD/MM/YYYY') THEN ADD_MONTHS(SYSDATE,-1) ELSE ADD_MONTHS(:rundate,-1) END, 'MON') AND PFT.FLIGHT_OPERATION_DATE < trunc( CASE :rundate WHEN TO_DATE('01/01/1900', 'DD/MM/YYYY') THEN TRUNC(SYSDATE) ELSE TRUNC(:rundate) END, 'MON') AND PFT.TYPE IN ('C', 'F') AND PFT.RECORD_TYPE = 'M' AND (PFT.TERMINAL_OPERATOR NOT IN ('X', 'A') OR (PFT.TERMINAL_OPERATOR <> 'X' AND FA.CARRIER_CODE IN (SELECT * FROM SPECIAL_HANDLING_AIRLINE) AND PPT.REVENUE_TYPE IN (SELECT * FROM SPECIAL_REVENUE_TYPE) AND PPT.SHIPMENT_FLOW_TYPE IN (SELECT * FROM SPECIAL_SHIPMENT_FLOW_TYPE) AND PFT.FLIGHT_OPERATION_DATE >= (select EFF_DATE from SPECIAL_HANDLING_EFF_DATE) )) AND PFT.DELETING_DATETIME IS NULL AND FA.AIRLINE_UID = PFT.AIRLINE_UID AND FA.DELETING_DATETIME IS NULL AND PTFP.FLIGHT_TONNAGE_UID = PFT.FLIGHT_TONNAGE_UID AND PTFP.RECORD_TYPE = 'M' AND PTFP.DELETING_DATETIME IS NULL AND PPT.TONNAGE_FLIGHT_PORT_UID (+)= PTFP.TONNAGE_FLIGHT_PORT_UID AND PPT.RECORD_TYPE (+)= 'M' AND PPT.DISCREPANCY_TYPE (+)= 'NONE' AND PPT.ADJUSTMENT_INC_FLAG (+)= 'Y' AND PPT.DELETING_DATETIME (+) IS NULL AND FAST.AIRCRAFT_SERVICE_TYPE_UID = PFT.AIRCRAFT_SERVICE_TYPE_UID AND FAST.DELETING_DATETIME IS NULL AND PIS.TEMPORAL_NAME = TO_CHAR((CASE :rundate --new WHEN TO_DATE('01/01/1900', 'DD/MM/YYYY') THEN TRUNC(SYSDATE) ELSE TRUNC(:rundate) END ), 'YYYYMM') || '00' AND PIS.INVOICE_SEPARATION_UID = PISD.INVOICE_SEPARATION_UID --new AND PISD.INVOICE_SEP_DETAIL_UID = PISPT.INVOICE_SEP_DETAIL_UID --new AND PISPT.PORT_TONNAGE_UID = PPT.PORT_TONNAGE_UID --new AND PIS.PRINT_SUPPORTING_DOC = 'Y';上面是oracle的写法，请转成spark SQL的写法。

以下是转换为 Spark SQL 查询的代码：

SELECT 
    PIS.SHOW_FLT_DETAIL AS SHOW_FLT_DETAIL, 
    PIS.SHOW_AWB_DETAIL AS SHOW_AWB_DETAIL, 
    PIS.DISPLAY_AIRLINE_CODE AS CARRIER_CODE, 
    CASE 
        WHEN PIS.REVERT_FLOW = 'N' THEN PIS.FLOW_TYPE 
        ELSE CASE 
            WHEN PIS.FLOW_TYPE = 'I' THEN 'E' 
            ELSE 'I' 
        END 
    END AS FLOW_TYPE, 
    PIS.SHIP_TO_LOCATION AS SHIP_TO_LOCATION, 
    PIS.INVOICE_SEQUENCE AS INVOICE_SEQUENCE, 
    PFT.FLIGHT_DATE AS FLIGHT_DATE, 
    PFT.FLIGHT_CARRIER_CODE AS FLIGHT_CARRIER_CODE, 
    PFT.FLIGHT_SERIAL_NUMBER AS FLIGHT_SERIAL_NUMBER, 
    PFT.FLOW_TYPE AS AIRCRAFT_FLOW, 
    FAST.AIRCRAFT_SERVICE_TYPE AS AIRCRAFT_SERVICE_TYPE, 
    PPT.AWB_NUMBER AS AWB_NUMBER, 
    PPT.WEIGHT AS WEIGHT, 
    PPT.CARGO_HANDLING_OPERATOR AS CARGO_HANDLING_OPERATOR, 
    PPT.SHIPMENT_PACKING_TYPE AS SHIPMENT_PACKING_TYPE, 
    PPT.SHIPMENT_FLOW_TYPE AS SHIPMENT_FLOW_TYPE, 
    PPT.SHIPMENT_BUILD_TYPE AS SHIPMENT_BUILD_TYPE, 
    PPT.SHIPMENT_CARGO_TYPE AS SHIPMENT_CARGO_TYPE, 
    PPT.REVENUE_TYPE AS REVENUE_TYPE, 
    PFT.JV_FLIGHT_CARRIER_CODE AS JV_FLIGHT_CARRIER_CODE, 
    PPT.PORT_TONNAGE_UID AS PORT_TONNAGE_UID, 
    PPT.AWB_UID AS AWB_UID, 
    PIS.INVOICE_SEPARATION_UID AS INVOICE_SEPARATION_UID, 
    PFT.FLIGHT_TONNAGE_UID AS FLIGHT_TONNAGE_UID 
FROM 
    PN_FLT_TONNAGES PFT 
    JOIN FZ_AIRLINES FA ON FA.AIRLINE_UID = PFT.AIRLINE_UID AND FA.DELETING_DATETIME IS NULL 
    JOIN PN_TONNAGE_FLT_PORTS PTFP ON PTFP.FLIGHT_TONNAGE_UID = PFT.FLIGHT_TONNAGE_UID AND PTFP.RECORD_TYPE = 'M' AND PTFP.DELETING_DATETIME IS NULL 
    LEFT JOIN PN_PORT_TONNAGES PPT ON PPT.TONNAGE_FLIGHT_PORT_UID = PTFP.TONNAGE_FLIGHT_PORT_UID AND PPT.RECORD_TYPE = 'M' AND PPT.DISCREPANCY_TYPE = 'NONE' AND PPT.ADJUSTMENT_INC_FLAG = 'Y' AND PPT.DELETING_DATETIME IS NULL 
    JOIN FF_AIRCRAFT_SERVICE_TYPES FAST ON FAST.AIRCRAFT_SERVICE_TYPE_UID = PFT.AIRCRAFT_SERVICE_TYPE_UID AND FAST.DELETING_DATETIME IS NULL 
    JOIN SR_PN_INVOICE_SEPARATIONS PIS ON PIS.TEMPORAL_NAME = CONCAT(YEAR(:rundate), RIGHT(CONCAT('0', MONTH(:rundate)), 2), '00') AND PIS.INVOICE_SEPARATION_UID = PISD.INVOICE_SEPARATION_UID AND PIS.PRINT_SUPPORTING_DOC = 'Y' 
    JOIN SR_PN_INVOICE_SEP_DETAILS PISD ON PISD.INVOICE_SEP_DETAIL_UID = PISPT.INVOICE_SEP_DETAIL_UID 
    JOIN SR_PN_INV_SEP_PORT_TONNAGES PISPT ON PISPT.PORT_TONNAGE_UID = PPT.PORT_TONNAGE_UID 
WHERE 
    PFT.FLIGHT_OPERATION_DATE >= trunc(CASE 
        WHEN :rundate = TO_DATE('01/01/1900', 'DD/MM/YYYY') THEN ADD_MONTHS(SYSDATE, -1) 
        ELSE ADD_MONTHS(:rundate, -1) 
    END, 'MON') 
    AND PFT.FLIGHT_OPERATION_DATE < trunc(CASE 
        WHEN :rundate = TO_DATE('01/01/1900', 'DD/MM/YYYY') THEN TRUNC(SYSDATE) 
        ELSE TRUNC(:rundate) 
    END, 'MON') 
    AND PFT.TYPE IN ('C', 'F') 
    AND PFT.RECORD_TYPE = 'M' 
    AND (
        PFT.TERMINAL_OPERATOR NOT IN ('X', 'A') 
        OR (
            PFT.TERMINAL_OPERATOR <> 'X' 
            AND FA.CARRIER_CODE IN (SELECT * FROM SPECIAL_HANDLING_AIRLINE) 
            AND PPT.REVENUE_TYPE IN (SELECT * FROM SPECIAL_REVENUE_TYPE) 
            AND PPT.SHIPMENT_FLOW_TYPE IN (SELECT * FROM SPECIAL_SHIPMENT_FLOW_TYPE) 
            AND PFT.FLIGHT_OPERATION_DATE >= (SELECT EFF_DATE FROM SPECIAL_HANDLING_EFF_DATE)
        )
    )
    AND PFT.DELETING_DATETIME IS NULL;

请注意，需要将 `TRUNC` 和 `CONCAT` 函数替换为 Spark SQL 中的相应函数，并使用 `JOIN` 语句替换 `LEFT JOIN` 语句。还需要更改零散的子查询，以便在 Spark SQL 中使用。