suellenmao
suellen爱吃肉
采纳率100%
2019-05-06 13:36

mysql 百万级数据 sql优化

5
已采纳

百万级数据,要求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多
求各位大神支招,在线等。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

5条回答

  • Lei_Da_Gou Lei_Da_Gou 2年前
    select * from (
    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' 
    LIMIT 10
    ) AS temp ORDER BY temp.flag ASC,temp.order_time DESC 
    

    应该可以解决,你复制过去执行试试看。

    点赞 评论 复制链接分享
  • weixin_42080504 have_power 2年前

    你查过你索引字段的离散度吗?我建议,where后面那两个条件字段,按照其中一个来进行分区,哪个条件最接近你所查出来的数据量,按哪个来分区。然后加不加索引,你先去查一下你要加索引字段的离散度。

    点赞 评论 复制链接分享
  • qq_21470187 qq_21470187 2年前

    你这查询的字段也太多了吧,建议只查询需要的字段即可!

    点赞 评论 复制链接分享
  • u013434984 潭溪Zerg 2年前

    看你这是用了filesort,而不是使用排序索引,速度肯定提不起来啊
    建议是,新增flag字段,使用你的sql中的case语句给赋值,然后新增索引index_1(is_project_order, del_status, flag, order_time)
    百万级别的数据,使用索引搜索,肯定能控制时间在1s之内的。

    点赞 评论 复制链接分享
  • qq_33427422 子系天下 2年前

    sql优化需要结合执行计划;单看你当前的这段代码,你可以尝试把查询个字段的处理逻辑提出来,不要写在sql内,尽可能少的逻辑在sql内

    点赞 评论 复制链接分享

相关推荐