mysql 百万级数据 sql优化

百万级数据,要求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个回答

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 

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

suellenmao
suellen爱吃肉 回复Lei_Da_Gou: 失效了好像
一年多之前 回复
Lei_Da_Gou
Lei_Da_Gou 回复suellen爱吃肉: 要的急得话加我Q3045201142,我帮你看下
一年多之前 回复
Lei_Da_Gou
Lei_Da_Gou 索引加上了但是没有用上啊。
一年多之前 回复
suellenmao
suellen爱吃肉 这是先limit 后排序? 出来的数据不对吧
一年多之前 回复

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

qq_33427422
子系天下 回复suellen爱吃肉: 如果用的是navicat,看下概况里,那个阶段耗时多,然后针对性的优化
一年多之前 回复
suellenmao
suellen爱吃肉 回复子系天下: 我已经按楼下的建完了索引 并且执行计划里也显示都用到了,但是还是慢
一年多之前 回复
qq_33427422
子系天下 回复suellen爱吃肉: 建索引是有规则的,不是建了它就会走,在查询的时候执行机制会走它认为最好的方案,你这边各个字段所代表的含义和数据量我这边都无法评估,建议先了解下索引的分类和他们适用的场景,然后调整下你这边的索引,确认能走索引的部分都已经走了之后,继续开始了解下sql执行计划,能够通过执行计划理解当前查询运行时的执行方案,根据方案中的不足之处做出sql调整
一年多之前 回复
suellenmao
suellen爱吃肉 回复子系天下: 我估计是索引失效了 ,但是flag也无法建索引
一年多之前 回复
qq_33427422
子系天下 回复suellen爱吃肉: 全表扫描,没有走任何索引,可能是索引键的有问题
一年多之前 回复
suellenmao
suellen爱吃肉 已贴,麻烦帮忙看看
一年多之前 回复

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

u013434984
潭溪Zerg 回复suellen爱吃肉: 加我qq,我瞅下2658346294,我说的就是这样的情况,你最开始回我的,还是在using filesort,没有使用索引排序
一年多之前 回复
suellenmao
suellen爱吃肉 回复潭溪Zerg: 没明白。现在已经是索引查询了 按你说的改了,但是case里面的是每次都不一样的 传的是当前登录人的id
一年多之前 回复
u013434984
潭溪Zerg 回复suellen爱吃肉: 你如果这么搞用不到索引,七万条数据就十秒了,以后数据量增长了,filesort方式查询的,搜索时间也是线性增长的
一年多之前 回复
u013434984
潭溪Zerg 回复suellen爱吃肉: 是说case里面的限定条件每次搜索都不一样么?如果是写死的应该是不需要的,这种情况,可以宁愿多次搜索啊,上层做逻辑
一年多之前 回复
suellenmao
suellen爱吃肉 回复潭溪Zerg: case语句是业务需要 我没想到其他能检索出正确数据的方法了
一年多之前 回复
u013434984
潭溪Zerg 回复suellen爱吃肉: 你贴一下Show create table T,和这样执行的sql,sql需要改,case语句不需要了
一年多之前 回复
suellenmao
suellen爱吃肉 七万条数据2
一年多之前 回复
suellenmao
suellen爱吃肉 按你说的执行完了,1 SIMPLE T ref del_status,is_project_order,index1 del_status 3 const 37895 Using index condition; Using where; Using filesort
一年多之前 回复

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

suellenmao
suellen爱吃肉 不瞒你说 都是需要的
一年多之前 回复

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

suellenmao
suellen爱吃肉 我刚看到 这个问题已经解决了
一年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐