MySQL 8优化器未优先执行子查询致SQL性能差异排查求助

云上老周

2026-6-13

问题背景

我在多台机器上部署了MySQL 8.0.40，使用完全相同的my.cnf配置，每台机器的数据库都是50GB的完全副本。但其中一台机器上，MySQL优化器选择了错误的执行顺序：没有优先执行包含MBRIntersects的子查询，导致这条SQL执行耗时约3分钟，而其他机器仅需3秒。以下是SQL语句和两种执行计划的EXPLAIN ANALYZE结果，求排查思路或强制MySQL优先使用子查询结果的方法。

目标SQL语句

SELECT ST_DISTANCE(ST_SRID(Point(o.OUTLET_LONGITUDE,o.OUTLET_LATITUDE),4326), ST_GeomFromText('POINT(52.579132266113746 13.567292529296857)',4326),'metre') as sDistanz,POLY_ID,CenterPt,OBJ_ID,o.OUTLET_ID,o.OUTLET_TXT,SUBSTRING(o.SAP_CODE, 1, 4) AS SAP_CODE,o.OUTLET_OPENDATE,o.OUTLET_LONGITUDE,o.OUTLET_LATITUDE,o.OUTLET_EMPLOYEES,o.OUTLET_AREA,c.COUNTRY_TAX,z.ZIPPOT_EURO_PLZ,r.RESULTS_CUSTOMER_SHARE,r.RESULTS_SALESVOLUME,z.ZIPPOT_POT_TOTAL,s.OUTLSALES_TOTAL 
FROM (
      SELECT POLY_ID,CenterPt,OBJ_ID 
      FROM rc_poly g1 
      WHERE g1.POLY_TYP_ID=21 AND MBRIntersects(ST_GeomFromText('Polygon((794399.14857904 5820256.9962884,824399.14857904 5820256.9962884,824399.14857904 5850256.9962884,794399.14857904 5850256.9962884,794399.14857904 5820256.9962884))', 25832) , BoundingRect)
      ) p 
JOIN rc_zippot z ON z.ZIPPOT_EURO_PLZ = p.OBJ_ID 
JOIN rc_results_relevant r ON r.ZIPPOT_ID = z.ZIPPOT_ID 
JOIN rc_invest v ON v.INVEST_ID = r.INVEST_ID AND v.invest_flag = 1 
JOIN rc_outlet o ON o.OUTLET_ID = v.OUTLET_ID 
JOIN rc_country c ON c.COUNTRY_ID = o.COUNTRY_ID 
JOIN rc_outlsales s ON s.OUTLSALES_ID = (
      SELECT MAX(OUTLSALES_ID) 
      FROM rc_outlsales 
      WHERE OUTLET_ID=o.OUTLET_ID
      ) 
WHERE ST_Distance(CenterPt, ST_GEomFromText('POINT(809399.1485790391 5835256.996288417)',25832))<15000 
ORDER BY OUTLET_ID

慢执行EXPLAIN ANALYZE结果

-> Sort: o.OUTLET_ID  (actual time=168932..168935 rows=5806 loops=1)
    -> Stream results  (cost=425456 rows=2730) (actual time=2338..168919 rows=5806 loops=1)
        -> Nested loop inner join  (cost=425456 rows=2730) (actual time=2338..168333 rows=5806 loops=1)
            -> Nested loop inner join  (cost=365889 rows=54594) (actual time=0.319..141231 rows=545935 loops=1)
                -> Nested loop inner join  (cost=305837 rows=54594) (actual time=0.302..135994 rows=545935 loops=1)
                    -> Nested loop inner join  (cost=286729 rows=54594) (actual time=0.12..4131 rows=545935 loops=1)
                        -> Nested loop inner join  (cost=267621 rows=54594) (actual time=0.0889..3934 rows=545935 loops=1)
                            -> Nested loop inner join  (cost=248513 rows=54594) (actual time=0.0735..3703 rows=545935 loops=1)
                                -> Table scan on r  (cost=57436 rows=545935) (actual time=0.0242..3041 rows=545935 loops=1)
                                -> Filter: (v.INVEST_FLAG = 1)  (cost=0.25 rows=0.1) (actual time=896e-6..956e-6 rows=1 loops=545935)
                                    -> Single-row index lookup on v using PRIMARY (INVEST_ID=r.INVEST_ID)  (cost=0.25 rows=1) (actual time=580e-6..599e-6 rows=1 loops=545935)
                            -> Single-row index lookup on o using PRIMARY (OUTLET_ID=v.OUTLET_ID)  (cost=0.25 rows=1) (actual time=239e-6..259e-6 rows=1 loops=545935)
                        -> Single-row index lookup on c using PRIMARY (COUNTRY_ID=o.COUNTRY_ID)  (cost=0.25 rows=1) (actual time=175e-6..194e-6 rows=1 loops=545935)
                    -> Filter: (s.OUTLSALES_ID = (select #3))  (cost=0.25 rows=1) (actual time=0.241..0.241 rows=1 loops=545935)
                        -> Single-row index lookup on s using PRIMARY (OUTLSALES_ID=(select #3))  (cost=0.25 rows=1) (actual time=0.123..0.123 rows=1 loops=545935)
                        -> Select #3 (subquery in condition; dependent)
                            -> Aggregate: max(rc_outlsales.OUTLSALES_ID)  (cost=155 rows=1) (actual time=0.119..0.119 rows=1 loops=1.09e+6)
                                -> Index lookup on rc_outlsales using RC_OUTLSALES_1 (OUTLET_ID=o.OUTLET_ID)  (cost=120 rows=344) (actual time=0.00704..0.115 rows=29.3 loops=1.09e+6)
                -> Single-row index lookup on z using PRIMARY (ZIPPOT_ID=r.ZIPPOT_ID)  (cost=1 rows=1) (actual time=0.00933..0.00935 rows=1 loops=545935)
            -> Filter: ((st_distance(g1.CenterPt,<cache>(st_geomfromtext('POINT(809399.1485790391 5835256.996288417)',25832))) < 15000) and mbrintersects(<cache>(st_geomfromtext('Polygon((794399.14857904 5820256.9962884,824399.14857904 5820256.9962884,824399.14857904 5850256.9962884,794399.14857904 5850256.9962884,794399.14857904 5820256.9962884))',25832)),g1.BoundingRect))  (cost=0.991 rows=0.05) (actual time=0.0494..0.0494 rows=0.0106 loops=545935)
                -> Single-row index lookup on g1 using RC_POLY_1 (OBJ_ID=z.ZIPPOT_EURO_PLZ, POLY_TYP_ID=21), with index condition: (z.ZIPPOT_EURO_PLZ = g1.OBJ_ID)  (cost=0.991 rows=1) (actual time=0.0171..0.0172 rows=1 loops=545935)

快执行EXPLAIN ANALYZE结果

-> Sort: o.OUTLET_ID  (actual time=4152..4156 rows=5806 loops=1)
    -> Stream results  (cost=953 rows=91.3) (actual time=2.79..4132 rows=5806 loops=1)
        -> Nested loop inner join  (cost=953 rows=91.3) (actual time=2.26..3237 rows=5806 loops=1)
            -> Nested loop inner join  (cost=921 rows=91.3) (actual time=1.24..286 rows=5806 loops=1)
                -> Nested loop inner join  (cost=889 rows=91.3) (actual time=1.23..281 rows=5806 loops=1)
                    -> Nested loop inner join  (cost=857 rows=91.3) (actual time=1.21..213 rows=5806 loops=1)
                        -> Nested loop inner join  (cost=537 rows=913) (actual time=1.19..126 rows=5806 loops=1)
                            -> Nested loop inner join  (cost=121 rows=101) (actual time=0.681..46 rows=3318 loops=1)
                                -> Filter: ((g1.POLY_TYP_ID = 21) and (st_distance(g1.CenterPt,<cache>(st_geomfromtext('POINT(809399.1485790391 5835256.996288417)',25832))) < 15000) and mbrintersects(<cache>(st_geomfromtext('Polygon((794399.14857904 5820256.9962884,824399.14857904 5820256.9962884,824399.14857904 5850256.9962884,794399.14857904 5850256.9962884,794399.14857904 5820256.9962884))',25832)),g1.BoundingRect))  (cost=9.41 rows=3.5) (actual time=0.62..14 rows=79 loops=1)
                                    -> Index range scan on g1 using Index_MBR over (BoundingRect unprintable_geometry_value)  (cost=9.41 rows=7) (actual time=0.356..4.81 rows=164 loops=1)
                                -> Index lookup on z using RC_ZIPPOT_1 (ZIPPOT_EURO_PLZ=g1.OBJ_ID), with index condition: (z.ZIPPOT_EURO_PLZ = g1.OBJ_ID)  (cost=29.8 rows=29) (actual time=0.0365..0.402 rows=42 loops=79)
                            -> Index lookup on r using ZIPPOT_ID (ZIPPOT_ID=z.ZIPPOT_ID)  (cost=3.21 rows=9) (actual time=0.00186..0.0238 rows=1.75 loops=3318)
                        -> Filter: (v.INVEST_FLAG = 1)  (cost=0.25 rows=0.1) (actual time=0.0145..0.0145 rows=1 loops=5806)
                            -> Single-row index lookup on v using PRIMARY (INVEST_ID=r.INVEST_ID)  (cost=0.25 rows=1) (actual time=0.0137..0.0137 rows=1 loops=5806)
                    -> Single-row index lookup on o using PRIMARY (OUTLET_ID=v.OUTLET_ID)  (cost=0.251 rows=1) (actual time=0.0115..0.0115 rows=1 loops=5806)
                -> Single-row index lookup on c using PRIMARY (COUNTRY_ID=o.COUNTRY_ID)  (cost=0.251 rows=1) (actual time=501e-6..529e-6 rows=1 loops=5806)
            -> Filter: (s.OUTLSALES_ID = (select #3))  (cost=0.251 rows=1) (actual time=0.508..0.508 rows=1 loops=5806)
                -> Single-row index lookup on s using PRIMARY (OUTLSALES_ID=(select #3))  (cost=0.251 rows=1) (actual time=0.349..0.349 rows=1 loops=5806)
                -> Select #3 (subquery in condition; dependent)
                    -> Aggregate: max(rc_outlsales.OUTLSALES_ID)  (cost=9.9 rows=1) (actual time=0.165..0.165 rows=1 loops=17418)
                        -> Index lookup on rc_outlsales using RC_OUTLSALES_1 (OUTLET_ID=o.OUTLET_ID)  (cost=7.7 rows=22) (actual time=0.00823..0.158 rows=33.2 loops=17418)

排查思路与解决方法

一、先确认统计信息是否一致

MySQL优化器依赖表的统计信息生成执行计划，即使数据完全相同，统计信息可能因更新时间、采样率差异导致偏差：

执行ANALYZE TABLE rc_poly, rc_results_relevant, rc_zippot;，强制更新这几个核心表的统计信息，尤其是rc_poly的空间索引统计。
对比两台机器的统计信息：执行SHOW TABLE STATUS LIKE 'rc_poly';和SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME='rc_poly';，检查行数、索引基数等是否一致。

二、强制优化器优先执行子查询的方法

1. 使用`FORCE INDEX`指定空间索引

在rc_poly的查询中强制使用Index_MBR（快执行计划里用到的空间索引），修改子查询部分：

SELECT POLY_ID,CenterPt,OBJ_ID 
FROM rc_poly g1 FORCE INDEX(Index_MBR)
WHERE g1.POLY_TYP_ID=21 AND MBRIntersects(ST_GeomFromText('Polygon((794399.14857904 5820256.9962884,824399.14857904 5820256.9962884,824399.14857904 5850256.9962884,794399.14857904 5850256.9962884,794399.14857904 5820256.9962884))', 25832) , B

MySQL 8优化器未优先执行子查询致SQL性能差异排查求助

目标SQL语句 #

慢执行EXPLAIN ANALYZE结果 #

快执行EXPLAIN ANALYZE结果 #

一、先确认统计信息是否一致 #

二、强制优化器优先执行子查询的方法 #