MYSQL慢查询优化-工作记录-ZFMES项目-优快云博客

SELECT  feedback_id,unique_product_code,item_code,unique_code,workstation_id,workstation_code,workstation_name,workline_id,workline_code,workline_name,user_name,nick_name,feedback_time_first,ok_flag,workshop_id,workshop_code,workshop_name,process_id,create_by,create_time,update_by,update_time,remark  FROM md_process_feedback      WHERE  (workline_id IN (205,205,205,205) AND process_id = 262 AND create_time >= '2025-06-27 00:00:00' AND oK_flag = 'Y' AND feedback_id NOT IN (952168,952253,952255,952256,952262,952279,952280,952287,952336,952344,952345,952346,952348,952350,952351,952352,952353,952354,952363,952370,952403,952404,952411,952415,952420,952426,952460,952477,952478,952482,952484,952495,952498,952508,952510,952516,952518,952526,952531,952533,952543,952552,952553,952557,952558,952569,952570,952576,952578,952580,952582,952587,952588,952596,952599,952602,952603,952608,952614,952615,952623,952629,952631,952644,952646,952647,952659,952662,952666,952669,952676,952684,952691,952694,952696,952700,952706,952708,952714,952715,952718,952720,952729,952740,952741,952742,952749,952760,952762,952764,952768,952769,952775,952776,952787,952788,952790,952795,952796,952803,952807,952812,952816,952823,952828,952830,952897,952898,952899,952900,952901,952902,952903,952904,952905,952906,952907,952908,952909,952910,952911,952912,952915,952916,952918,952922,952924,952929,952937,952941,952943,952950,952952,952953,952954,952958,952959,952960,952961,952962,952963,952967,952975,952977,952980,952986,952987,952993,952995,953001,953005,953007,953014,953018,953022,953028,953032,953041,953042,953049,953050,953057,953058,953067,953082,953083,953095,953096,953101,953112,953115,953117,953121,953131,953133,953135,953138,953145,953148,953149,953156,953165,953176,953177,953178,953188,953192,953196,953197,953199,953205,953207,953209,953216,953225,953228,953230,953233,953243,953249,953254,953255,953261,953265,953272,953274,953283,953300,953301,953302,953306,953308,953312,953314,953319,953320,953325,953331,953334,953339,953344,953347,953351,953353,953364,953365,953368,953372,953374,953382,953383,953391,953398,953403,953404,953409,953411,953414,953415,953419,953427,953429,953433,953437,953440,953445,953451,953463,953466,953475,953480,953483,953487,953491,953492,953497,953498,953503,953512,953514,953516,953519,953520,953526,953530,953532,953539,953543,953550,953556,953567,953569,953570,953573,953575,953576,953577,953578,953579,953580,953581,953582,953583,953584,953585,953586,953587,953588,953589,953590,953591,953592,953593,953594,953595,953596,953597,953598,953599,953600,953601,953602,953603,953604,953605,953606,953607,953691,953696,953703,953716,953721,953722,953723,953726,953727,953729,953730,953731,953734,953739,953742,953744,953747,953748,953758,953762,953764,953766,953770,953772,953785,953786,953792,953794,953796,953801,953803,953809,953813,953818,953819,953820,953824,953830,953839,953843,953844,953859,953860,953864,953866,953870,953873,953880,953886,953888,953894,953897,953898,953901,953903,953905,953908,953916,953918,953920,953921,953927,953935,953938,953939,953946,953949,953957,953958,953965,953968,953970,953972,953977,953978,953984,953994,954000,954003,954006,954010,954016,954018,954020,954028,954031,954035,954038,954039,954043,954051,954055,954056,954063,954066,954070,954078,954079,954086,954089,954092,954097,954101,954103,954106,954112,954118,954119,954121,954127,954132,954133,954136,954145,954146,954147,954148,954151,954154,954161,954164,954172,954179,954184,954185,954189,954194,954196,954199,954211,954217,954218,954219,954220,954225,954226,954229,954233,954244,954254,954256,954257,954259,954260,954261,954269,954273,954280,954283,954284,954287,954288,954289,954300,954304,954309,954319,954320,954321,954322,954324,954325,954332,954344,954350,954354,954365,954367,954372,954381,954387,954388,954389,954391,954393,954399,954400,954409,954411,954417,954418,954429,954430,954432,954436,954444,954445,954449,954453,954456,954459,954469,954470,954474,954476,954477,954478,954479,954492,954515,954530,954533,954534,954538,954546,954547,954551,954552,954555,954559,954563,954564,954566,954569,954571,954572,954575,954585,954589,954590,954592,954602,954604,954606,954607,954609,954612,954615,954616,954621,954622,954624,954634,954637,954641,954643,954649,954651,954653,954657,954664,954667,954668,954670,954671,954678,954680,954681,954683,954685,954687,954688,954697,954698,954703,954705,954712,954717,954718,954722,954727,954735,954738,954739,954741,954747,954750,954756,954760,954765,954767,954768,954770,954774,954828,954829,954830,954831,954832,954833,954834,954835,954836,954837,954838,954839,954840,954841,954842,954843,954845,954846,954848,954850,954860,954861,954863,954868,954875,954879,954885,954886,954888,954894,954899,954905,954906,954908,954921,954922,954923,954924,954927,954934,954950,954955,954958,954972,954974,954975,954981,954985,954990,954994,955005,955007,955010,955011,955012,955014,955018,955021,955027,955030,955034,955035,955046,955049,955051,955053,955063,955066,955067,955068,955070,955074,955078,955080,955083,955085,955090,955093,955094,955099,955101,955104,955107,955116,955119,955120,955122,955123,955127,955131,955132,955142,955144,955146,955147,955152,955153,955154,955160,955161,955167,955170,955173,955184,955188,955189,955196,955203,955205,955206,955220,955224,955225,955226,955228,955232,955233,955238,955242,955244,955246,955247,955262,955263,955265,955266,955275,955281,955282,955284,955291,955292,955297,955301,955304,955306,955309,955312,955314,955318,955319,955321,955325,955327,955331,955336,955340,955348,955349,955352,955354,955359,955366,955368,955370,955372,955373,955374,955379,955381,955383,955390,955395,955399,955402,955403,955405,955415,955418,955421,955422,955426,955427,955431,955436,955439,955445,955449,955454,955456,955458,955460,955470,955472,955475,955481,955487,955489,955490,955497,955499,955502,955510,955517,955519,955520,955532,955540,955542,955546,955548,955549,955550,955556,955560,955564,955566,955572,955574,955579,955580,955581,955584,955585,955593,955595,955599,955602,955609,955613,955623,955626,955631,955633,955634,955636,955638,955639,955640,955641,955642,955643,955644,955645,955646,955647,955648,955649,955650,955651,955652,955653,955654,955655,955656,955657,955658,955659,955660,955661,955850,955855,955862,955863,955866,955867,955870,955872,955877,955881,955887,955893,955895,955897,955902,955907,955909,955912,955918,955919,955921,955923,955929,955938,955943,955946,955947,955949,955953,955955,955961,955967,955971,955973,955974,955982,955983,955985,955988,955993,955997,956001,956002,956007,956009,956014,956020,956021,956026,956027,956038,956039,956043,956046,956049,956057,956059,956063,956065,956071,956072,956076,956090,956099,956102,956107,956108,956110,956111,956115,956120,956121,956126,956134,956143,956145,956148,956149,956151,956160,956165,956171,956174,956179,956181,956184,956186,956189,956198,956204,956205,956211,956217,956221,956225,956226,956231,956236,956238,956242,956243,956255,956263,956264,956267,956269,956271,956274,956281,956282,956288,956292,956293,956295,956300,956308,956315,956316,956321,956326,956332,956336,956345,956346,956348,956351,956357,956361,956364,956368,956375,956376,956380,956382,956386,956389,956397,956400,956404,956406,956414,956416,956417,956425,956428));

原来的sql，未加索引前：5秒多

# Time: 2025-06-27T11:37:12.574136Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 209809
# Query_time: 5.002059 Lock_time: 0.001058 Rows_sent: 2 Rows_examined: 954551
SET timestamp=1751024232;

加了索引，且改变了查询结构:

<changeSet id="2025062801-add-idx-feedback-perf" author="keyi">
        <createIndex indexName="idx_feedback_perf" tableName="md_process_feedback">
            <column name="create_time"/>
            <column name="workline_id"/>
            <column name="process_id"/>
        </createIndex>
    </changeSet>


QueryWrapper<MdProcessFeedback> queryWrapper = new QueryWrapper<>();
            queryWrapper.select("feedback_id","unique_product_code", "ok_flag","workline_id", "feedback_time_first","process_id", "create_time");
            queryWrapper.ge("create_time",formattedDate);
            queryWrapper.eq("workline_id", workline.getWorklineId());
            queryWrapper.eq("process_id",keyProcessId);
            if (CollectionUtil.isNotEmpty(dayPlanFeedbacks)) {
                List<Long> existedFeedbackIds = dayPlanFeedbacks.stream().map(DayPlanFeedback::getFeedbackId).collect(Collectors.toList());
                queryWrapper.notIn("feedback_id", existedFeedbackIds);

            }
            List<MdProcessFeedback> feedbacks = this.iMdProcessFeedbackService.getBaseMapper().selectList(queryWrapper);

速度：几百毫秒

后来发现把create_time放在前面的话，因为它的条件不是等值，而是大于或小于，根据最左匹配原则，这条sql不走完整的索引

又改了索引:

<changeSet id="20250628-02-recreate-idx-feedback-perf" author="keyi">
        <!-- 1. 删除原索引 -->
        <dropIndex indexName="idx_feedback_perf" tableName="md_process_feedback"/>

        <!-- 2. 创建新索引（workline_id, process_id, create_time） -->
        <createIndex indexName="idx_feedback_perf" tableName="md_process_feedback">
            <column name="workline_id"/>
            <column name="process_id"/>
            <column name="create_time"/>
        </createIndex>
    </changeSet>

改了查询语句:

//找到关键工序今日该产线的报工数据
            QueryWrapper<MdProcessFeedback> queryWrapper = new QueryWrapper<>();
            queryWrapper.select("feedback_id","unique_product_code", "ok_flag","workline_id", "feedback_time_first","process_id", "create_time");
            queryWrapper.eq("workline_id", workline.getWorklineId());
            queryWrapper.eq("process_id",keyProcessId);
            queryWrapper.ge("create_time",formattedDate);
            if (CollectionUtil.isNotEmpty(dayPlanFeedbacks)) {
                List<Long> existedFeedbackIds = dayPlanFeedbacks.stream().map(DayPlanFeedback::getFeedbackId).collect(Collectors.toList());
                queryWrapper.notIn("feedback_id", existedFeedbackIds);

            }
            List<MdProcessFeedback> feedbacks = this.iMdProcessFeedbackService.getBaseMapper().selectList(queryWrapper);


QueryWrapper<MdProcessFeedback> queryWrapper = new QueryWrapper<>();
            queryWrapper.select("feedback_id","unique_product_code", "ok_flag","workline_id", "feedback_time_first","process_id", "create_time");
            queryWrapper.eq("workline_id", workline.getWorklineId());
            queryWrapper.eq("process_id",inlineId);
            queryWrapper.ge("create_time",formattedDate);
            queryWrapper.eq("oK_flag","Y");
            if (CollectionUtil.isNotEmpty(dayPlanFeedbacks)) {
                List<Long> existedFeedbackIds = dayPlanFeedbacks.stream().map(DayPlanFeedback::getFeedbackId).collect(Collectors.toList());
                queryWrapper.notIn("feedback_id", existedFeedbackIds);
            }
            List<MdProcessFeedback> feedbacks = this.iMdProcessFeedbackService.getBaseMapper().selectList(queryWrapper);


//直接获取报工数据
        //下线数据
        QueryWrapper<MdProcessFeedback> fbquery = new QueryWrapper<>();

        fbquery.eq("workline_id",worklineId);
        Long keyProcessId = mdWorkline.getKeyProcessId();
        if (Objects.isNull(keyProcessId)){
            String key = this.sysConfigService.selectConfigByKey("md.default.key.process.id");
            if (StringUtils.isEmpty(key)){
                throw new ServiceException("该产线未配置关键下线工序，请联系管理员");
            }
            try {
                keyProcessId = Long.valueOf(key);
            }catch (Exception e){
                throw new ServiceException("该产线关键下线工序配置有误，请联系管理员");
            }

        }
        fbquery.eq("process_id",keyProcessId);
        fbquery.ge("create_time",startDate+" 00:00:00");
        fbquery.le("create_time",endDate+" 23:59:59");
//        fbquery.ge("feedback_time_first",startDate+" 00:00:00");
//        fbquery.le("feedback_time_first",endDate+" 23:59:59");
        long todayProduced = iMdProcessFeedbackService.count(fbquery);
        if(todayProduced>0l){
            dto.setTodayProduced( Math.toIntExact(todayProduced));
        }

        //上线数据
        QueryWrapper<MdProcessFeedback> fbquery2 = new QueryWrapper<>();
        fbquery2.select("feedback_id","ok_flag","workline_id", "feedback_time_first","create_time");
        fbquery2.eq("workline_id",worklineId);
        Long inlineProcessId = mdWorkline.getInlineProcessId();
        if (Objects.isNull(inlineProcessId)){
            String inline = this.sysConfigService.selectConfigByKey("md.default.inline.process.id");
            if (StringUtils.isEmpty(inline)){
                throw new ServiceException("该产线未配置上线工序，请联系管理员");
            }
            try {
                inlineProcessId = Long.valueOf(inline);
            }catch (Exception e){
                throw new ServiceException("该产线上线工序配置有误，请联系管理员");
            }

        }

        fbquery2.eq("process_id",inlineProcessId);
        fbquery2.ge("create_time",startDate+" 00:00:00");
        fbquery2.le("create_time",endDate+" 23:59:59");
        fbquery2.orderByAsc("create_time");
        List<MdProcessFeedback> feedbackList = iMdProcessFeedbackService.list(fbquery2);
        long todayOnline = feedbackList.size();

速度提升了很多，其中有的只走了20ms，扫描的行从上万降到了几百。

SELECT * FROM workorder_product_num WHERE workorder_id = 649

SQL 用了 2157 毫秒（= 2.157 秒）

加了workorder_id索引之后：0.3秒