百万级数据,要求3s内出结果,索引都加上了
贴上sql
SELECT
T.lockbalance_status AS lockbalanceStatus,
T.order_merinstalltype AS orderMerinstalltype,
T.order_id AS orderId,
T.serverUserId,
date_format( T.order_time, '%Y-%m-%d %H:%i:%s' ) AS orderTime,
T.order_no AS orderNo,
T.is_project_order AS isProjectOrder,
T.STATUS,
T.user_id AS userId,
T.insert_user AS userName,
T.serverUserId AS serverUserName,
T.organ_name AS organName,
T.leadingoffice_name AS leadingofficeName,
T.order_type AS orderType,
T.is_self_paying AS isSelfPaying,
T.account_name AS accountName,
T.account_mobile AS accountMobile,
T.account_address AS accountAddress,
T.province_id AS provinceId,
T.city_id AS cityId,
T.area_id AS areaId,
T.install_userid AS installUserName,
date_format( T.install_time, '%Y-%m-%d %H:%i:%s' ) AS installTime,
date_format( T.order_completetime, '%Y-%m-%d %H:%i:%s' ) AS completetime,
T.is_ergent AS isErgent,
date_format( T.allocationtime, '%Y-%m-%d %H:%i:%s' ) AS allocationtime,
date_format( T.install_time_again, '%Y-%m-%d %H:%i:%s' ) AS installTimeAgain,
date_format( T.assign_again_time, '%Y-%m-%d %H:%i:%s' ) AS assignAgainTime,
T.dealOver AS dealOver,
date_format( T.alarm_time_again, '%Y-%m-%d %H:%i:%s' ) AS alarmTimeAgain,
date_format( T.order_takingtime, '%Y-%m-%d %H:%i:%s' ) AS orderTakingtime,
date_format( T.logistics_time, '%Y-%m-%d %H:%i:%s' ) AS logisticsTime,
T.is_logistics_time AS isLogisticsTime,
T.firstperson AS firstPersonId,
T.firstperson AS firstPersonName,
T.program_id AS programId,
T.program_name AS programName,
T.program_manager AS programManager,
date_format( T.insert_time, '%Y-%m-%d %H:%i:%s' ) AS programTime,
date_format( T.p_setProject_time, '%Y-%m-%d' ) AS setProjectTime,
date_format( T.noSettlementTime, '%Y-%m-%d %H:%i:%s' ) AS noSettlementTime,
T.installremark AS installRemark,
T.lockSmithRemark AS lockSmithRemark,
T.order_remark AS orderRemark,
T.audit_status AS auditStatus,
CASE
WHEN T.firstperson = 'cdbcd902dbe411e89c777cd30ae00302' THEN
0
WHEN T.serverUserId = 'cdbcd902dbe411e89c777cd30ae00302' THEN
1 ELSE 2
END AS flag
FROM
t_order T
WHERE
T.is_project_order = '0'
AND T.del_status = '0'
ORDER BY
flag,
T.order_time DESC
LIMIT 10
截图为原来执行计划
根据各位大神提供的方案,优化了一波,贴上优化后的执行计划
但是依旧达不到速度要求
flag 是业务需要,客户希望与自己有关的数据优先展示
目前执行下来sql需要10s多
求各位大神支招,在线等。