1 要优化的SQL语句
该查询主要用于分析在过去5天中经常乘坐航班的乘客，帮助航空公司识别出高频率乘客（常旅客），从而为他们提供更优质的服务或制定更有针对性的营销策略。
SELECT p.firstname, p.lastname, COUNT(b.booking_id) AS total_bookings, AVG(b.price) AS average_price FROM passenger p JOIN booking b ON p.passenger_id = b.passenger_id JOIN flight f ON b.flight_id = f.flight_id WHERE f.departure > NOW() - INTERVAL 5 day GROUP BY p.passenger_id ORDER BY total_bookings DESC LIMIT 10; +-----------+----------------+----------------+---------------+
| firstname | lastname | total_bookings | average_price |
+-----------+----------------+----------------+---------------+
| Nick | Thomas-Webster | 2 | 1409.190000 |
| Mieko | Suzuki | 2 | 782.110000 |
| Vinnie | Vincent | 2 | 808.345000 |
| Brandon | Prust | 2 | 1405.685000 |
| Franco | Harris | 2 | 1370.855000 |
| Milford | Brown | 2 | 188.660000 |
| Kenny | G | 2 | 617.530000 |
| Michael | Warren | 2 | 641.410000 |
| Bill | Russell | 2 | 1218.665000 |
| Allen | York | 1 | 1369.390000 |
+-----------+----------------+----------------+---------------+
10 rows in set (2 min 47.44 sec)
语句的执行时间长达2分钟以上，这条语句能优化吗，让我们先看一下语句的执行计划。
2 语句性能分析
2.1 语句的执行计划
mysql> explain SELECT p.firstname, p.lastname, COUNT(b.booking_id) AS total_bookings, AVG(b.price) AS average_price FROM passenger p JOIN booking b ON p.passenger_id = b.passenger_id JOIN flight f ON b.flight_id = f.flight_id WHERE f.departure > NOW() - INTERVAL 5 day GROUP BY p.passenger_id ORDER BY total_bookings DESC LIMIT 10;
+----+-------------+-------+------------+--------+---------------------------------------+---------------+---------+--------------------------+-------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------------------+---------------+---------+--------------------------+-------+----------+---------------------------------+
| 1 | SIMPLE | p | NULL | index | PRIMARY,pass_unq | PRIMARY | 4 | NULL | 36169 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | b | NULL | ref | seatplan_unq,flight_idx,passenger_idx | passenger_idx | 4 | airportdb.p.passenger_id | 890 | 100.00 | NULL |
| 1 | SIMPLE | f | NULL | eq_ref | PRIMARY,departure_idx | PRIMARY | 4 | airportdb.b.flight_id | 1 | 50.00 | Using where |
+----+-------------+-------+------------+--------+---------------------------------------+---------------+---------+--------------------------+-------+----------+---------------------------------+
乍一看，语句的执行计划好像没什么问题，该有的索引都有了，表的访问方式也没有明显的问题。这条语句为何执行如此之慢，再仔细分析一下，发现连接的前两个表返回的行数分别是36169和890，filtered的值是100，没有过滤到任何行，第三个操作会执行36169*890次，这是一个庞大的数量，而这第三个操作又是成本较高的回表操作（由extra列的值using where可以看出）。如果减少前两个操作的返回的行数，语句的执行时间应该会减少。
2.2 语句的表结构
mysql> select * from flight limit 2;--航班表，航班id是主键，存储航班号，航班始发、到达时间、始发地、目的地。
+-----------+----------+------+-------+---------------------+---------------------+------------+-------------+
| flight_id | flightno | from | to | departure | arrival | airline_id | airplane_id |
+-----------+----------+------+-------+---------------------+---------------------+------------+-------------+
| 1 | AF1078 | 9084 | 2658 | 2025-06-01 10:15:00 | 2025-06-01 20:46:00 | 1 | 2635 |
| 2 | AF1377 | 4478 | 12595 | 2025-06-01 23:41:00 | 2025-06-02 10:05:00 | 1 | 2607 |
+-----------+----------+------+-------+---------------------+---------------------+------------+-------------+
mysql> select * from booking limit 2;--订票表，订单id是主键，另有航班id，座位号、乘客id，单价
+------------+-----------+------+--------------+--------+
| booking_id | flight_id | seat | passenger_id | price |
+------------+-----------+------+--------------+--------+
| 1 | 3863 | NULL | 2947 | 110.10 |
| 2 | 3863 | 25F | 20814 | 173.10 |
+------------+-----------+------+--------------+--------+
mysql> select * from passenger limit 2;--乘客表，乘客id是主键，存储乘客信息
+--------------+------------+--------------+----------+
| passenger_id | passportno | firstname | lastname |
+--------------+------------+--------------+----------+
| 4 | P103014 | Buzz | Aldrin |
| 5 | P103015 | Bobby "Blue" | Bland |
+--------------+------------+--------------+----------+
从这三个表的结构可以看出，booking表里的时间信息是隐含的，需要从flight表里查询，也就是说，条件f.departure > NOW() - INTERVAL 5 day是通过flight表间接作用到booking表里的记录。
格式化一下语句，
SELECT
p.firstname,
p.lastname,
COUNT(b.booking_id) AS total_bookings,
AVG(b.price) AS average_price
FROM
passenger p
JOIN booking b ON
p.passenger_id = b.passenger_id
JOIN flight f ON
b.flight_id = f.flight_id
WHERE
f.departure > NOW() - INTERVAL 5 DAY
GROUP BY
p.passenger_id
ORDER BY
total_bookings DESC
LIMIT 10
这条语句的执行计划的逻辑是获得所有用户的订票信息，然后检查这些订票信息的航班，根据航班的时间筛选出近5天的订票信息，然后统计出订票最多的10名旅客。由于没有对旅客的
限定条件，相当于查询所有订票信息后再进行筛选。执行计划的最后一步执行了where条件，过滤掉了50%的行，这显然不符合SQL优化的原则。下面看一下语句实际的执行计划
2.3 语句实际的执行计划
从MySQL8.0之后可以用explain analyze查看语句的实际执行计划，explain analyze执行要解释的sql语句并输出语句实际执行计划即实际时间、行数等。
mysql> explain analyze SELECT p.firstname, p.lastname, COUNT(b.booking_id) AS total_bookings, AVG(b.price) AS average_price FROM passenger p JOIN booking b ON p.passenger_id = b.passenger_id JOIN flight f ON b.flight_id = f.flight_id WHERE f.departure > NOW() - INTERVAL 5 day GROUP BY p.passenger_id ORDER BY total_bookings DESC LIMIT 10; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s) (actual time=182397..182397 rows=10 loops=1)
-> Sort: total_bookings DESC, limit input to 10 row(s) per chunk (actual time=182397..182397 rows=10 loops=1)
-> Stream results (cost=35.5e+6 rows=36169) (actual time=873..182396 rows=713 loops=1)
-> Group aggregate: count(b.booking_id), avg(b.price) (cost=35.5e+6 rows=36169) (actual time=873..182389 rows=713 loops=1)
-> Nested loop inner join (cost=33.9e+6 rows=16.1e+6) (actual time=205..182384 rows=722 loops=1)
-> Nested loop inner join (cost=20.1e+6 rows=32.2e+6) (actual time=0.734..100832 rows=33.4e+6 loops=1)
-> Index scan on p using PRIMARY (cost=3657 rows=36169) (actual time=0.0881..52.6 rows=36095 loops=1)
-> Index lookup on b using passenger_idx (passenger_id=p.passenger_id) (cost=466 rows=891) (actual time=0.346..2.74 rows=926 loops=36095)
-> Filter: (f.departure > <cache>((now() - interval 5 day))) (cost=0.328 rows=0.5) (actual time=0.00236..0.00236 rows=21.6e-6 loops=33.4e+6)
-> Single-row index lookup on f using PRIMARY (flight_id=b.flight_id) (cost=0.328 rows=1) (actual time=0.00218..0.0022 rows=1 loops=33.4e+6)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (3 min 2.40 sec)
实际执行计划中外层的嵌套循环实际执行actual time=205…182384 rows=722，实际执行时间很长，优化的估计成本和行数（cost=33.9e+6 rows=16.1e+6）优化器估算成本也是很高，但是估计的行数却与实际返回的行数差距很大，如此大的差异，可能会导致错误的执行计划。通过语句实际执行的时间和返回的行数也可以看出，可能会有更好的连接方式。这个嵌套连接的外表（这个说法不太准确，实际也是一个两表连接的结果）如下
Nested loop inner join (cost=20.1e+6 rows=32.2e+6) (actual time=0.734..100832 rows=33.4e+6 loops=1) -> Index scan on p using PRIMARY (cost=3657 rows=36169) (actual time=0.0881..52.6 rows=36095 loops=1) -> Index lookup on b using passenger_idx (passenger_id=p.passenger_id) (cost=466 rows=891) (actual time=0.346..2.74 rows=926 loops=36095)
内部的的这个嵌套循环效率还算可以，actual time=0.734…100832，但是返回的行数太多 rows=33.4e+6，优化器估算的行数和实际行数差别不是太大。根据嵌套循环的原理，外表返回的行数就是内表查询的次数，看实际的执行计划，内表的两个操作的loop都是loops=33.4e+6，这也是整个语句的瓶颈所在。看一下后面的两个操作
-> Filter: (f.departure > <cache>((now() - interval 5 day))) (cost=0.328 rows=0.5) (actual time=0.00236..0.00236 rows=21.6e-6 loops=33.4e+6)
-> Single-row index lookup on f using PRIMARY (flight_id=b.flight_id) (cost=0.328 rows=1) (actual time=0.00218..0.0022 rows=1 loops=33.4e+6)
这是典型的通过主键回表的操作，这样部分的问题在Filter操作，优化器估计rows=0.5和实际rows=21.6e-6的差别巨大，这里的条件应该可以过滤掉绝大部分数据，而不仅仅是50%，这和优化器对表达式的评估有关。分析到这里，已经可以确认语句低效的原因，如果能在嵌套连接的外表过滤掉多数行，语句的执行计划就能得到很大提升。
3 优化步骤
3.1 检查条件的选择性
这条语句的条件作用在flight表上，先检查一下这个条件在表上的选择性，用下面的SQL语句查询
select count(*),"filtered" from flight where departure > NOW() - INTERVAL 5 day union select count(*),"ALL" from flight
;
+----------+----------+
| count(*) | filtered |
+----------+----------+
| 147789 | filtered |
| 462553 | ALL |
+----------+----------+
语句的查询条件在 462553 行中选出了147789，如果将flight表作为外表，总的计算量可能会减少2/3，基本确定，将原来的表连接顺序调整为f,p，b或者f,b,p可以提高语句的性能,在这三个表里，f（flight）和p（passenger）表没有任何列相关联，因此可以选择的连接顺序只剩下f,b,p。
3.2 优化连接顺序
看一下表flight和booking连接返回的行数
select count(*) from flight f
-> inner join booking b on b.flight_id=f.flight_id
-> where f.departure > NOW() - INTERVAL 5 day;
+----------+
| count(*) |
+----------+
| 638 |
+----------+
总共返回不到700行，看一下上面这个语句的执行计划
mysql> explain analyze select count(*) from flight f inner join booking b on b.flight_id=f.flight_id where f.departure >
NOW() - INTERVAL 5 day;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0) (cost=7.58e+6 rows=1) (actual time=290..290 rows=1 loops=1)
-> Nested loop inner join (cost=3.95e+6 rows=36.4e+6) (actual time=1.88..290 rows=638 loops=1)
-> Filter: (f.departure > <cache>((now() - interval 5 day))) (cost=46320 rows=230643) (actual time=0.405..54 rows=147711 loops=1)
-> Covering index range scan on f using departure_idx over ('2025-08-03 06:52:22' < departure) (cost=46320 rows=230643) (actual time=0.402..41.8 rows=147711 loops=1)
-> Covering index lookup on b using flight_idx (flight_id=f.flight_id) (cost=1.15 rows=158) (actual time=0.00149..0.0015 rows=0.00432 loops=147711)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.29 sec)
返回loops=638，内循环执行了loops=147711次，原来执行计划降低了不止一个数量级，而且还消除了回表的操作。到此为止，可以得出结论，调整连接顺序可以大幅度提高语句的执行性能。下面就是语句改写，使它执行调整后的连接顺序的问题了。
4 语句改写
查了一下MySQL的官方手册，MySQL 8.4.5上调整连接顺序由两种方法，分别介绍一下
4.1 利用hint改写
第一种是利用hint，这里又有两种方式，一种是不该改写sql的表连接顺序，用hint调节连接顺序，如下所示
4.1.1 使用join_order 改写
SELECT /*+join_order(f,b,p)*/p.firstname, p.lastname, COUNT(b.booking_id) AS total_bookings, AVG(b.price) AS average_price FROM passenger p JOIN booking b ON p.passenger_id = b.passenger_id JOIN flight f ON b.flight_id = f.flight_id WHERE f.departure > NOW() - INTERVAL 5 day GROUP BY p.passenger_id ORDER BY total_bookings DESC LIMIT 10; +-----------+----------------+----------------+---------------+
| firstname | lastname | total_bookings | average_price |
+-----------+----------------+----------------+---------------+
| Michael | Warren | 2 | 641.410000 |
| Kenny | G | 2 | 617.530000 |
| Vinnie | Vincent | 2 | 808.345000 |
| Mieko | Suzuki | 2 | 782.110000 |
| Franco | Harris | 2 | 1370.855000 |
| Bill | Russell | 2 | 1218.665000 |
| Milford | Brown | 2 | 188.660000 |
| Nick | Thomas-Webster | 2 | 1409.190000 |
| Brandon | Prust | 2 | 1405.685000 |
| Melanie | Blatt | 1 | 202.340000 |
+-----------+----------------+----------------+---------------+
10 rows in set (0.30 sec)
这个hint的书写需要注意，（数据库版本MySQL 8.4.5，）/*和+join_order(f,b,p)时间不要有空格，+ 和join之间可以有空格。语句改写后，性能得到了大幅度提升，执行时间降到了0.30秒。语句的实际执行计划如下
explain analyze SELECT /*+join_order(f,b,p)*/p.firstname, p.lastname, COUNT(b.booking_id) AS total_bookings, AVG(b.pri
ce) AS average_price FROM passenger p JOIN booking b ON p.passenger_id = b.passenger_id JOIN flight f ON b.flight_id
= f.flight_id WHERE f.departure > NOW() - INTERVAL 5 day GROUP BY p.passenger_id ORDER BY total_bookings DESC LIMIT
10;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s) (actual time=329..329 rows=10 loops=1)
-> Sort: total_bookings DESC, limit input to 10 row(s) per chunk (actual time=329..329 rows=10 loops=1)
-> Table scan on <temporary> (actual time=329..329 rows=713 loops=1)
-> Aggregate using temporary table (actual time=329..329 rows=713 loops=1)
-> Nested loop inner join (cost=35.4e+6 rows=36.4e+6) (actual time=0.889..327 rows=722 loops=1)
-> Nested loop inner join (cost=22.7e+6 rows=36.4e+6) (actual time=0.881..325 rows=722 loops=1)
-> Filter: (f.departure > <cache>((now() - interval 5 day))) (cost=46193 rows=230643) (actual time=0.34..58.1 rows=162514 loops=1)
-> Covering index range scan on f using departure_idx over ('2025-07-31 07:14:35' < departure) (cost=46193 rows=230643) (actual time=0.336..44.5 rows=162514 loops=1)
-> Index lookup on b using flight_idx (flight_id=f.flight_id) (cost=82.5 rows=158) (actual time=0.00156..0.00156 rows=0.00444 loops=162514)
-> Single-row index lookup on p using PRIMARY (passenger_id=b.passenger_id) (cost=0.25 rows=1) (actual time=0.00301..0.00305 rows=1 loops=722)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.33 sec)
执行次数最多的操作（执行了loops=162514次）是一个索引查找工作
Index lookup on b using flight_idx (flight_id=f.flight_id) (cost=82.5 rows=158) (actual time=0.00156..0.00156 rows=0.00444 loops=162514)
4.1.2 使用JOIN_PREFIX改写
第二种方式是利用JOIN_PREFIX,使用这个hint可以指定连接第一个表，对于本文中的查询语句，只需要指定第一个表为flight表即可，改写如下：
select /*+ JOIN_PREFIX(f)*/ p.firstname, p.passenger_id, p.lastname,count(c.booking_id) AS total_bookings, AVG(c.price) AS average_price from passenger p inner join (select f.flight_id,f.departure , b.booking_id, b.price ,b.passenger_id from flight f inner join booking b on b.flight_id=f.flight_id where f.departure > NOW() - INTERVAL 5 day) c on c.passenger_id=p.passenger_id GROUP BY p.passenger_id HAVING COUNT(c.booking_id) > 1 ORDER BY total_bookings DESC LIMIT 10;
4.1.3 使用JOIN_FIXED_ORDER改写
第三种方式是使用JOIN_FIXED_ORDER hint，这个hint的作用同straight join相同，这种方式需要定义查询块名称，如不定义查询块，则会报HINT语法错误，写法如下
SELECT /*+ qb_name(qb1) JOIN_FIXED_ORDER(@qb1) */ p.firstname, p.lastname, COUNT(b.booking_id) AS total_bookings, AVG(b.price) AS average_price FROM flight f inner join booking b ON b.flight_id = f.flight_id inner JOIN passenger p ON p.passenger_id = b.passenger_id WHERE f.departure > NOW() - INTERVAL 5 day GROUP BY p.passenger_id having COUNT(b.booking_id)>1 ORDER BY total_bookings DESC LIMIT 10;
4.2 利用strait_join改写
MySQL还提供了另外一种控制表的连接顺序的语法，straight_join有两种用法，一种是写在select后面，优化器将严格按照语句中的顺序连接，第二种把straight_join当作一种连接方式使用，使用straight_join连接的表，左边的总比右边的要先执行，这两种改写方式可以达到同样的效果，本例中的语句改写如下：
--作为连接方式
SELECT p.firstname, p.lastname, COUNT(b.booking_id) AS total_bookings, AVG(b.price) AS average_price FROM flight f straight_JOIN booking b ON b.flight_id = f.flight_id inner JOIN passenger p ON p.passenger_id = b.passenger_id WHERE f.departure > NOW() - INTERVAL 5 day GROUP BY p.passenger_id having COUNT(b.booking_id)>1 ORDER BY total_bookings DESC LIMIT 10;
--作为select 修饰词
SELECT straight_join p.firstname, p.lastname, COUNT(b.booking_id) AS total_bookings, AVG(b.price) AS average_price FROM flight f inner join booking b ON b.flight_id = f.flight_id inner JOIN passenger p ON p.passenger_id = b.passenger_id WHERE f.departure > NOW() - INTERVAL 5 day GROUP BY p.passenger_id having COUNT(b.booking_id)>1 ORDER BY total_bookings DESC LIMIT10;
5 写在后面
连接顺序的不同会导致SQL的性能根本性的变化，这篇文章分享了这条sql的分析和优化思路，也列举了调整表连接顺序的几个hint和SQL修饰词，主要是以后看起来方便。
这里面补充一点，如果写了hint却不生效，很大的可能性是写的hint里面有语法错误，比如下面的例子
explain SELECT /*+ JOIN_FIXED_ORDER*/p.firstname, p.lastname, COUNT(b.booking_id) AS total_bookings, AVG(b.price) AS average_price FROM flight f inner join booking b ON b.flight_id = f.flight_id inner JOIN passenger p ON p.passenger_id = b.passenger_id WHERE f.departure > NOW() - INTERVAL 5 day GROUP BY p.passenger_id having COUNT(b.booking_id)>1 ORDER BY total_bookings DESC LIMIT 10;
+----+-------------+-------+------------+--------+--------------------------------------------------------------------------+-------------------------------------+---------+--------------------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+--------------------------------------------------------------------------+-------------------------------------+---------+--------------------------+-------+----------+----------------------------------------------+
| 1 | SIMPLE | p | NULL | index | PRIMARY,pass_unq,idx_passenger_id_firstname_lastname | idx_passenger_id_firstname_lastname | 808 | NULL | 36169 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | b | NULL | ref | seatplan_unq,flight_idx,passenger_idx,idx_booking_passenger_flight_price | idx_booking_passenger_flight_price | 4 | airportdb.p.passenger_id | 877 | 100.00 | Using index |
| 1 | SIMPLE | f | NULL | eq_ref | PRIMARY,departure_idx,idx_flight_id_departure | PRIMARY | 4 | airportdb.b.flight_id | 1 | 50.00 | Using where |
+----+-------------+-------+------------+--------+--------------------------------------------------------------------------+-------------------------------------+---------+--------------------------+-------+----------+----------------------------------------------+
3 rows in set, 2 warnings (0.01 sec)
这里hint执行的顺序没有生效，命令的输出提示有两条告警信息，我们知道，explain命令的输出一般只有一条告警信息，这里的两条可能是命令运行产生了异常，查看一下
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1064 | Optimizer hint syntax error near '*/' at line 1 |
| Note | 1003 | /* select#1 */ select `airportdb`.`p`.`firstname` AS `firstname`,`airportdb`.`p`.`lastname` AS `lastname`,count(`airportdb`.`b`.`booking_id`) AS `total_bookings`,avg(`airportdb`.`b`.`price`) AS `average_price` from `airportdb`.`flight` `f` join `airportdb`.`booking` `b` join `airportdb`.`passenger` `p` where ((`airportdb`.`f`.`flight_id` = `airportdb`.`b`.`flight_id`) and (`airportdb`.`b`.`passenger_id` = `airportdb`.`p`.`passenger_id`) and (`airportdb`.`f`.`departure` > <cache>((now() - interval 5 day)))) group by `airportdb`.`p`.`passenger_id` having (count(`airportdb`.`b`.`booking_id`) > 1) order by `total_bookings` desc limit 10 |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
可以看到，第一条告警信息即是优化器hint格式错误告警。
最后修改时间：2025-08-13 09:46:40
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
