记一次MYSQL的查询优化（主要集中在建立索引消除USING filesort ）

最新推荐文章于 2025-07-20 01:15:02 发布
最新推荐文章于 2025-07-20 01:15:02 发布
阅读量423 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： Java2019
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_38005943/article/details/99432489
本文详述了一次对复杂MYSQL查询的优化过程，通过建立索引、子查询优化和调整JOIN顺序，将原本耗时较长的查询（50秒）优化至0.01秒。关键步骤包括将全表查询改为子查询，使用延迟关联优化和通过索引消除USING filesort。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

回顾一下最近做的一个查询优化 ，该sql是一个页面查询，历史遗留问题就不多说。。当我接手的时候（去掉搜索条件）的sql为

MYSQL 5.7.23

original版本

select a.*, m.fitment_contract_amount
        from (select t.id,
            t.task_no,
            t.apply_time,
            t.urgency_level,
            t.product_id,
            t.operator_id auditUserId,
            b.customer_name,
            b.customer_id_no,
            b.customer_phone,
            ifnull(f.merchant_code,cmi.merchant_code) merchant_code,
            ifnull(f.merchant_name,cmi.merchant_name) merchant_name,
            ifnull(f.store_code,cmi.store_code) store_code,
            ifnull(f.store_name,cmi.store_name) store_name,
            a.apply_amount,
            a.pre_amount,
            s.loan_amount,
            s.term,
            s.loan_time,
            (select result from customer_audit_phone_result where task_no=t.task_no and delete_flag=0 order by id desc limit 1) phoneResult,
            
            (select id from customer_audit_result where task_no = a.task_no order by id desc limit 1) audit_result_id
        from customer_audit_task t
        left join customer_base_info b on t.task_no = b.task_no and b.delete_flag=0
        left join customer_fitment_info f on t.task_no = f.task_no and f.delete_flag=0
        left join customer_apply_info a on t.task_no = a.task_no and a.delete_flag=0
        left join customer_loan_success_info s on t.task_no = s.task_no and s.delete_flag=0
        left join customer_merchant_info cmi on t.task_no=cmi.task_no and cmi.delete_flag=0
        where t.delete_flag=0) a
        left join customer_material_info m on m.customer_audit_result_id = a.audit_result_id and m.delete_flag=0
        where find_in_set(a.product_id,"1,6")
        order by a.apply_time desc
        limit 0,10 

可以看出该sql原来的写法是将customer_audit_task 的全表查询出 然后再排序分页（耗时很长，页面接口请求大约5万条记录需要60S），第一步先想到将子查询去掉，省去全表查出的过程，同时关注到audit_result_id的逻辑是取一条，将其通过group by的方式获取

VERSION 1.0

 

select   t.id,
            t.task_no,
            t.apply_time,
            t.urgency_level,
            t.product_id,
            t.operator_id auditUserId,
            b.customer_name,
            b.customer_id_no,
            b.customer_phone,
            ifnull(f.merchant_code,cmi.merchant_code) merchant_code,
            ifnull(f.merchant_name,cmi.merchant_name) merchant_name,
            ifnull(f.store_code,cmi.store_code) store_code,
            ifnull(f.store_name,cmi.store_name) store_name,
            a.apply_amount,
            a.pre_amount,
            s.loan_amount,
            s.term,
            s.loan_time,
            (select result from customer_audit_phone_result where task_no=t.task_no and delete_flag=0 order by id desc limit 1) phoneResult,
            
            car.id audit_result_id,
        m.fitment_contract_amount
        from customer_audit_task t
        left join customer_base_info b on t.task_no = b.task_no and b.delete_flag=0
        left join customer_fitment_info f on t.task_no = f.task_no and f.delete_flag=0
        left join customer_apply_info a on t.task_no = a.task_no and a.delete_flag=0
        left join customer_loan_success_info s on t.task_no = s.task_no and s.delete_flag=0
        left join customer_merchant_info cmi on t.task_no=cmi.task_no and cmi.delete_flag=0
        left join(select task_no, max(id) id  from customer_audit_result where delete_flag = 0 group by task_no )car on car.task_no = t.task_no 
        left join customer_material_info m on m.customer_audit_result_id = car.id and m.delete_flag=0
        where find_in_set(t.product_id,"1,6") and t.delete_flag=0
         order by t.apply_time desc
        limit 0,10 

结果虽然有所改善，但是依然很慢（10S左右），可以看到主要还是查询customer_audit_task表，在关联查询中可以通过延迟关联进行优化

举个例子

正常写法

select id, apply_code, request_str from manage_inner_api_log order by id desc limit 97000,10;

延迟关联优化（通过子查询覆盖索引）

select id, apply_code, request_str from manage_inner_api_log INNER JOIN (select id from manage_inner_api_log order by id desc limit 97000, 10)as limt USING(id);

即在INNER JOIN中通过覆盖索引将符合条件的主键筛选出提高查询效率

VERSION 2.0

 select t.id,
             t.task_no,
            t.apply_time,
          t.urgency_level,
            t.product_id,
        t.operator_id auditUserId,
            b.customer_name,
            b.customer_id_no,
            b.customer_phone,
            ifnull(f.merchant_code,cmi.merchant_code) merchant_code,
            ifnull(f.merchant_name,cmi.merchant_name) merchant_name,
            ifnull(f.store_code,cmi.store_code) store_code,
            ifnull(f.store_name,cmi.store_name) store_name,
            a.apply_amount,
            a.pre_amount,
            s.loan_amount,
            s.term,
            s.loan_time,
            (select result from customer_audit_phone_result where task_no=t.task_no and delete_flag=0 order by id desc limit 1) phoneResult,
            t.real_status audit_status,
            car.id audit_result_id,
        m.fitment_contract_amount
        from customer_audit_task t 
        inner join (select id from customer_audit_task
                where 
                    find_in_set(product_id,"1,6") 

 and audit_status not in(100, 103, 1001, 1002) 
                    and delete_flag=0 
                  
                        

                    order by apply_time desc limit 0,10
        )a on t.id = a.id 
        left join customer_base_info b on t.task_no = b.task_no and b.delete_flag=0
        left join customer_fitment_info f on t.task_no = f.task_no and f.delete_flag=0
        left join customer_apply_info a on t.task_no = a.task_no and a.delete_flag=0

        left join customer_loan_success_info s on t.task_no = s.task_no and s.delete_flag=0
        left join customer_merchant_info cmi on t.task_no=cmi.task_no and cmi.delete_flag=0
        left join(select task_no, max(id) id  from customer_audit_result where delete_flag = 0 group by task_no )car on car.task_no = t.task_no 
        left join customer_material_info m on m.customer_audit_result_id = car.id and m.delete_flag=0
         order by t.apply_time desc
 

优化后的查询时间大概在1S，但是之前都未曾考虑到搜索条件的问题（有关联表的字段需要作为搜索条件），所以将从表的搜索条件放在 INNER JOIN查询中

VERSION 3.0

 

select t.id,
t.task_no,
t.apply_time,
t.urgency_level,
t.product_id,
t.operator_id auditUserId,
b.customer_name,
b.customer_id_no,
b.customer_phone,
ifnull(f.merchant_code,cmi.merchant_code) merchant_code,
ifnull(f.merchant_name,cmi.merchant_name) merchant_name,
ifnull(f.store_code,cmi.store_code) store_code,
ifnull(f.store_name,cmi.store_name) store_name,
a.apply_amount,
a.pre_amount,
s.loan_amount,
s.term,
s.loan_time,
(select result from customer_audit_phone_result where task_no=t.task_no and delete_flag=0 order by id desc limit 1) phoneResult,
t.real_status audit_status,
car.id audit_result_id,
m.fitment_contract_amount
from customer_audit_task t
inner join (select si.id from customer_audit_task si
left join customer_base_info b on si.task_no = b.task_no and b.delete_flag=0
left join customer_apply_info a on si.task_no = a.task_no and a.delete_flag=0
left join customer_loan_success_info s on s.task_no = si.task_no and s.delete_flag=0
left join (select
ifnull(f.merchant_code,cmi.merchant_code) merchant_code,
ifnull(f.merchant_name,cmi.merchant_name) merchant_name,
ifnull(f.store_code,cmi.store_code) store_code,
f.task_no,
ifnull(f.store_name,cmi.store_name) store_name
from customer_fitment_info f
left join customer_merchant_info cmi on f.task_no = cmi.task_no
and f.delete_flag=0
and cmi.delete_flag=0
)ms on ms.task_no = si.task_no
where
find_in_set(si.product_id,#{productCodes})
and si.delete_flag=0
and si.real_status not in(100, 103, 1001, 1002)
<if test="custNameLike !=null and custNameLike != ''">
   and b.customer_name = #{custNameLike}
</if>
<if test="mobileLike !=null and mobileLike != ''">
   and b.customer_phone = #{mobileLike}
</if>
<if test="productLike !=null and productLike != ''">
   and si.product_id = #{productLike}
</if>
<!-- 资料审核中 -->
<if test="auditStatusLike == 11">
   and (si.real_status = 101 or si.real_status = 104)
</if>
<!-- 审批拒绝：资料审核拒绝或电核拒绝 -->
<if test="auditStatusLike == 16">
   and (si.real_status = 106 or si.real_status = 203)
</if>
<if test="auditStatusLike != 11 and auditStatusLike != 16 and auditStatusLike !=null and auditStatusLike != ''">
   and si.real_status = #{auditStatusLike}
</if>
<if test="applyTimeStartLike !=null and applyTimeStartLike != ''">
   <![CDATA[ and DATE_FORMAT(si.apply_time, '%Y-%m-%d') >= #{applyTimeStartLike} ]]>
</if>
<if test="applyTimeEndLike !=null and applyTimeEndLike != ''">
   <![CDATA[ and DATE_FORMAT(si.apply_time, '%Y-%m-%d') <= #{applyTimeEndLike} ]]>
</if>
<if test="userId !=null and userId != ''">
   and si.operator_id = #{userId}
</if>
<if test="loanTimeStartLike !=null and loanTimeStartLike != ''">
   <![CDATA[ and DATE_FORMAT(s.loan_time, '%Y-%m-%d') >= #{loanTimeStartLike} ]]>
</if>
<if test="loanTimeEndLike !=null and loanTimeEndLike != ''">
   <![CDATA[ and DATE_FORMAT(s.loan_time, '%Y-%m-%d') <= #{loanTimeEndLike} ]]>
</if>
<if test="merchantLike !=null and merchantLike != ''">
   and ms.merchant_code = #{merchantLike}
</if>
<if test="storeLike !=null and storeLike != ''">
   and ms.store_code = #{storeLike}
</if>
order by si.apply_time desc limit 0，10
)a on t.id = a.id
left join customer_base_info b on t.task_no = b.task_no and b.delete_flag=0
left join customer_apply_info a on t.task_no = a.task_no and a.delete_flag=0
left join customer_loan_success_info s on t.task_no = s.task_no and s.delete_flag=0
left join(select task_no, max(id) id  from customer_audit_result where delete_flag = 0 group by task_no )car on car.task_no = t.task_no
left join customer_material_info m on m.customer_audit_result_id = car.id and m.delete_flag=0
left join customer_fitment_info f on t.task_no = f.task_no and f.delete_flag=0
left join customer_merchant_info cmi on t.task_no=cmi.task_no and cmi.delete_flag=0
order by t.apply_time desc

通过EXPLAIN发现查询主要耗时集中在INNER JOIN子查询之中，主要耗时在ORDER BY 操作上（如果去掉耗时在0.01s左右得出），所以通过索引来优化LEFT JOIN中的排序（去除USING filesort部分）

SELECT
        si.id
    FROM
        customer_audit_task si
    LEFT JOIN customer_base_info b ON si.task_no = b.task_no
    AND b.delete_flag = 0
     LEFT JOIN customer_apply_info a ON si.task_no = a.task_no
    AND a.delete_flag = 0
     LEFT JOIN customer_loan_success_info s ON s.task_no = si.task_no
     AND s.delete_flag = 0
         LEFT JOIN (
         SELECT
             ifnull(
                 f.merchant_code,
                 cmi.merchant_code
             ) merchant_code,
             ifnull(
                 f.merchant_name,
                 cmi.merchant_name
             ) merchant_name,
             ifnull(
                 f.store_code,
                 cmi.store_code
             ) store_code,
             f.task_no,
             ifnull(
                 f.store_name,
                  cmi.store_name
              ) store_name
          FROM
              customer_fitment_info f
         LEFT JOIN customer_merchant_info cmi ON f.task_no = cmi.task_no
         AND f.delete_flag = 0
         AND cmi.delete_flag = 0
     ) ms ON ms.task_no = si.task_no
    WHERE
        find_in_set(si.product_id, '1,6')
and real_status not in(100, 103, 1001, 1002)
    AND si.delete_flag = 0
     ORDER BY
         si.apply_time DESC
    LIMIT 0，10;

对于主表增加查询条件和排序字段索引
alter table customer_audit_task add index IDX_AUDIT_TASK_DELETEFLAG_APPLYTIME (delete_flag,  apply_time) ;

对于从表增加关于task_no字段的索引（只列出一个从表，其他从表同理）

alter table customer_loan_success_info add index IDX_LOAN_SUCCESS_INFO_TASKNO (task_no) ;
 

这次sql优化大概就是这样，从原来5万条记录的50S变成最后的0.01S，应该还有优化的空间（例如INNER JOIN中关联从表查过一次，外部查询时又left join了从表，这部分应该还有优化空间,大概还可以提高10%-20%（粗略统计）），主要的花费时间在如何在LEFT JOIN查询中建议合适的索引消除USING filesort，希望可以给看到的同学一点帮助。

 

 

 

 

mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
mysql 优化(消除filesort)
技术分享
03-21 1630
针对select * from table where clo1> number order by clo2 desc。 其实按照常规的方法可以这样设计：key(clo1, clo2) 但是这种办法在mysql里不算是理想的，where条件里限定索引前部分是一个范围的情况下后面的order by还是会有filesort。如果where条件里限定索引前部分是一个常量，那么order by就会有效
mysql using filesort 的解决方案
climbs的专栏
10-02 2935
在explain我们所使用的sql的时候，经常会遇到using filesort这种情况，原以为是由于有相同列值的原因引起，结果昨天看到公司的一个sql，跟同事讨论了下加上自己又做了一些测试，突然发现自己原来的想法是错误的。 首先，只有在order by 数据列的时候才可能会出现using filesort，而且如果你不对进行order by的这一列设置索引的话，无论列值是否有相同的都会出现
mysql filesort 的解决方案
静宁宇思
06-28 1127
只要用到order by 的这一列都应该为其建立一个索引
order by 利用索引有序性，避免出现 file_sort 的情况，提高查询性能
qq_52477177的博客
08-23 299
优化索引排序就两种，一种利用索引有序性的，不用额外排序，直接查出就是有序的；另一种就需要额外排序的。这需要理解组合索引的有序性来进行Sql的优化，并不是格式一定的，可以灵活变化，比如组合索引的三个字段都来使用作为过滤条件呢，什么条件会不用额外排序呢？更多的条件呢？阿里巴巴开发手册的着重点是想要我们利用索引的有序性，来完成我们的排序目的，而不需要额外的排序操作。额外的排序操作有些糟糕的情况是很费性能的，这部分可以了解order by的单路排序和双路排序。
记一次MySQL using filesort的优化
weixin_38888144的博客
02-29 900
背景 最近开始整理业务数据的指标体系，所以有一些表整理和指标生成的相关工作。在这个过程中遇到了一些SQL逻辑中的“using filesort”的问题，简单记录一下解决过程。 我做的这个指标是群聊业务的指标，这也是一个通用的业务场景。我们的App中有不同类型的聊天IM群，我需要统计不同类型的IM群每天总的群数量、新增的群数量和相应的聊天消息数。当然其中还有更多相关的指标，为了不涉及具体的业务详情，...
Mysql索引下推、Order by优化和Using filesort文件排序原理
swadian2008的博客
04-15 2325
目录 1、like 'KK%' 和索引下推 2、Order by 和 Group by 优化 3、Using filesort文件排序原理 // 以下建表结构和数据同《Mysql explain 索引优化案例》 1、like 'KK%' 和索引下推 like 'KK%'一般情况都会走索引，原因是like 'KK%' 用到了索引下推优化 EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND posi.
MySQL查询优化–索引优化
12-14
MySQL查询优化中的索引优化是提高数据库性能的关键技术之一。索引的作用在于加快数据检索速度，减少磁盘I/O操作，提升查询效率。在给定的例子中，我们首先创建了一个名为`article`的表，包含`id`（主键）、`author_...
MySQL中出现using filesort现象的一般情况与分析
m0_46405589的博客
01-14 2万+
在使用explain命令优化SQL语句的时候常常会在Extra列的描述中发现Using filesort选项，其实这个名字很容易造成误解，一开始我以为是“文件排序”的意思，进一步说可能就是使用了磁盘空间来进行排序，但是这个理解是错误的，Using filesort真正含义其实只有sort这一个单词，和file没有什么关系，Mysql一般是通过内存进行排序的，不过，要是超过了配置中的限制，应该会生成临时表。 Using filesort表示在索引之外，需要额外进行外部的排序动作。导致该问...
✅线上紧急问题之Using filesort 能优化吗，怎么优化？
AlbenXie的博客
09-20 235
上一篇文章中，提到了如何分析 SQL 的执行计划，从而更好的应对 SQL 性能过低等问题。但是我们也常遇到Extra字段是的时候，上篇文章有描述：在 InnoDB 存储引擎中，当执行计划中出现"Using filesort"时，表示 MySQL 需要对结果集进行外部排序，以满足查询中的 ORDER BY 条件。比如，下面这个执行计划中的"Extra"部分出现了"Using filesort"，表明需要进行文件排序。代码语言：sql在下面这篇文章中，我们已经介绍了 ORDER BY 的实现原理。
详解“消除Using filesort等性能瓶颈“
最新发布
weixin_47242663的博客
07-20 636
摘要：本文详细解析了MySQL中"Using filesort"性能瓶颈的形成原理及优化方案。当ORDER BY/GROUP BY操作无法利用索引时，MySQL需额外排序导致性能下降。通过创建包含查询条件和排序字段的联合索引（如idx_comp_create(company_code, create_time)），并配合覆盖索引技术，可将"Using filesort"转为"Using index"。该优化使查询响应时间降低30%（TP99从800
Mysql之Using filesort优化
Jasonzhou
10-29 8556
一、前言 在使用 explain 命令优化SQL语句的时候常常会在Extra列的描述中发现 Using filesort 选项，其实这个名字很容易造成误解，一开始我以为是“文件排序”的意思，进一步说可能就是使用了磁盘空间来进行排序，但是这个理解是错误的，Using filesort 真正含义其实只有 sort 这一个单词，和 file 没有什么关系，仅仅表示没有使用索引的排序。filesort使用的算法是QuickSort，即对需要排序的记录生成元数据进行分块排序，然后再使用mergesort方法合并块。其
MySql中explain的时候出现using filesort，优化之
热门推荐
imzoer的专栏
01-09 5万+
在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序。 【这个 filesort 并不是说通过磁盘文件进行排序，而只是告诉我们进行了一个排序操作而已】。 当然，using filesort不一定引起mysql的性能问题。但是如果查询次数非常多，那么每次在mysql中进行排序，还是会有影响的。 此时，可以进行的优化： 1
【Mysql】mysql | sql优化 | 优化using filesort | select insert | insert into select
hgSuper的博客
09-21 907
1、sql优化2、基于sql的优化。
多表关联group by优化：去除Using temporary; Using filesort
Jerry_Pan1990的博客
03-06 1万+
最近因为项目很赶，很久没写博客了，今天算是已经把项目进度超前完成了，写一个今天解决的SQL优化问题。 这次优化花了半小时，但很值得，之前作为一个单纯后端，很少关注SQL效率，但是自从上个项目当上临时DBA后，或多或少的都会关注自己书写的SQL执行计划。 原始第一映像写出的脚本： EXPLAIN SELECT service_id,service_name,cou...
MySQL倒序如何避免filesort_mysql order by 出现Using filesort的解决办法
weixin_26905423的博客
01-28 460
http://blog.youkuaiyun.com/yangyu112654374/article/details/4251624比如一条sql语句select * from TABLE1 where A1=1 and A2=2 and A3=3 order by A4 ASC;优化后是这样： select A4,A1,A2 ... FROM TABLE1 whereA1=1 and A2=2 and A...
Using temporary与Using filesort
meser88的博客
11-22 1341
通过explain查看sql的执行计划时，Extra字段的值往往会看到Using where; Using index; Using temporary; Using filesort，其中此次重点关注Using temporary; Using filesort。 Using temporary Using temporary表示由于排序没有走索引、使用union、子查询连接查询、使用某些视图等原因（详见internal-temporary-tables），因此创建了一个内部临时表。注意这里的临时表可
mysql 优化 ALL 1342 0.07 Using where; Using filesort
05-11
可能的解决方案是创建一个包含k和id的复合索引，这样在查询时，可以同时利用k的条件和id的排序。例如，索引(k, id)，这样WHERE k 可以使用k的索引，同时索引中已经包含id的值，因此排序可能可以利用索引。但需要确认...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值