1 要優(yōu)化的SQL語句
該查詢主要用于分析在過去5天中經(jīng)常乘坐航班的乘客，幫助航空公司識(shí)別出高頻率乘客（常旅客），從而為他們提供更優(yōu)質(zhì)的服務(wù)或制定更有針對(duì)性的營銷策略。
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)
語句的執(zhí)行時(shí)間長達(dá)2分鐘以上，這條語句能優(yōu)化嗎，讓我們先看一下語句的執(zhí)行計(jì)劃。
2 語句性能分析
2.1 語句的執(zhí)行計(jì)劃
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 |
+----+-------------+-------+------------+--------+---------------------------------------+---------------+---------+--------------------------+-------+----------+---------------------------------+
乍一看，語句的執(zhí)行計(jì)劃好像沒什么問題，該有的索引都有了，表的訪問方式也沒有明顯的問題。這條語句為何執(zhí)行如此之慢，再仔細(xì)分析一下，發(fā)現(xiàn)連接的前兩個(gè)表返回的行數(shù)分別是36169和890，filtered的值是100，沒有過濾到任何行，第三個(gè)操作會(huì)執(zhí)行36169*890次，這是一個(gè)龐大的數(shù)量，而這第三個(gè)操作又是成本較高的回表操作（由extra列的值using where可以看出）。如果減少前兩個(gè)操作的返回的行數(shù)，語句的執(zhí)行時(shí)間應(yīng)該會(huì)減少。
2.2 語句的表結(jié)構(gòu)
mysql> select * from flight limit 2;--航班表，航班id是主鍵，存儲(chǔ)航班號(hào)，航班始發(fā)、到達(dá)時(shí)間、始發(fā)地、目的地。
+-----------+----------+------+-------+---------------------+---------------------+------------+-------------+
| 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，座位號(hào)、乘客id，單價(jià)
+------------+-----------+------+--------------+--------+
| 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是主鍵，存儲(chǔ)乘客信息
+--------------+------------+--------------+----------+
| passenger_id | passportno | firstname | lastname |
+--------------+------------+--------------+----------+
| 4 | P103014 | Buzz | Aldrin |
| 5 | P103015 | Bobby "Blue" | Bland |
+--------------+------------+--------------+----------+
從這三個(gè)表的結(jié)構(gòu)可以看出，booking表里的時(shí)間信息是隱含的，需要從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
這條語句的執(zhí)行計(jì)劃的邏輯是獲得所有用戶的訂票信息，然后檢查這些訂票信息的航班，根據(jù)航班的時(shí)間篩選出近5天的訂票信息，然后統(tǒng)計(jì)出訂票最多的10名旅客。由于沒有對(duì)旅客的
限定條件，相當(dāng)于查詢所有訂票信息后再進(jìn)行篩選。執(zhí)行計(jì)劃的最后一步執(zhí)行了where條件，過濾掉了50%的行，這顯然不符合SQL優(yōu)化的原則。下面看一下語句實(shí)際的執(zhí)行計(jì)劃
2.3 語句實(shí)際的執(zhí)行計(jì)劃
從MySQL8.0之后可以用explain analyze查看語句的實(shí)際執(zhí)行計(jì)劃，explain analyze執(zhí)行要解釋的sql語句并輸出語句實(shí)際執(zhí)行計(jì)劃即實(shí)際時(shí)間、行數(shù)等。
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)
實(shí)際執(zhí)行計(jì)劃中外層的嵌套循環(huán)實(shí)際執(zhí)行actual time=205…182384 rows=722，實(shí)際執(zhí)行時(shí)間很長，優(yōu)化的估計(jì)成本和行數(shù)（cost=33.9e+6 rows=16.1e+6）優(yōu)化器估算成本也是很高，但是估計(jì)的行數(shù)卻與實(shí)際返回的行數(shù)差距很大，如此大的差異，可能會(huì)導(dǎo)致錯(cuò)誤的執(zhí)行計(jì)劃。通過語句實(shí)際執(zhí)行的時(shí)間和返回的行數(shù)也可以看出，可能會(huì)有更好的連接方式。這個(gè)嵌套連接的外表（這個(gè)說法不太準(zhǔn)確，實(shí)際也是一個(gè)兩表連接的結(jié)果）如下
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)
內(nèi)部的的這個(gè)嵌套循環(huán)效率還算可以，actual time=0.734…100832，但是返回的行數(shù)太多 rows=33.4e+6，優(yōu)化器估算的行數(shù)和實(shí)際行數(shù)差別不是太大。根據(jù)嵌套循環(huán)的原理，外表返回的行數(shù)就是內(nèi)表查詢的次數(shù)，看實(shí)際的執(zhí)行計(jì)劃，內(nèi)表的兩個(gè)操作的loop都是loops=33.4e+6，這也是整個(gè)語句的瓶頸所在。看一下后面的兩個(gè)操作
-> 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操作，優(yōu)化器估計(jì)rows=0.5和實(shí)際rows=21.6e-6的差別巨大，這里的條件應(yīng)該可以過濾掉絕大部分?jǐn)?shù)據(jù)，而不僅僅是50%，這和優(yōu)化器對(duì)表達(dá)式的評(píng)估有關(guān)。分析到這里，已經(jīng)可以確認(rèn)語句低效的原因，如果能在嵌套連接的外表過濾掉多數(shù)行，語句的執(zhí)行計(jì)劃就能得到很大提升。
3 優(yōu)化步驟
3.1 檢查條件的選擇性
這條語句的條件作用在flight表上，先檢查一下這個(gè)條件在表上的選擇性，用下面的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表作為外表，總的計(jì)算量可能會(huì)減少2/3，基本確定，將原來的表連接順序調(diào)整為f,p，b或者f,b,p可以提高語句的性能,在這三個(gè)表里，f（flight）和p（passenger）表沒有任何列相關(guān)聯(lián)，因此可以選擇的連接順序只剩下f,b,p。
3.2 優(yōu)化連接順序
看一下表flight和booking連接返回的行數(shù)
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行，看一下上面這個(gè)語句的執(zhí)行計(jì)劃
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，內(nèi)循環(huán)執(zhí)行了loops=147711次，原來執(zhí)行計(jì)劃降低了不止一個(gè)數(shù)量級(jí)，而且還消除了回表的操作。到此為止，可以得出結(jié)論，調(diào)整連接順序可以大幅度提高語句的執(zhí)行性能。下面就是語句改寫，使它執(zhí)行調(diào)整后的連接順序的問題了。
4 語句改寫
查了一下MySQL的官方手冊(cè)，MySQL 8.4.5上調(diào)整連接順序由兩種方法，分別介紹一下
4.1 利用hint改寫
第一種是利用hint，這里又有兩種方式，一種是不該改寫sql的表連接順序，用hint調(diào)節(jié)連接順序，如下所示
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)
這個(gè)hint的書寫需要注意，（數(shù)據(jù)庫版本MySQL 8.4.5，）/*和+join_order(f,b,p)時(shí)間不要有空格，+ 和join之間可以有空格。語句改寫后，性能得到了大幅度提升，執(zhí)行時(shí)間降到了0.30秒。語句的實(shí)際執(zhí)行計(jì)劃如下
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)
執(zhí)行次數(shù)最多的操作（執(zhí)行了loops=162514次）是一個(gè)索引查找工作
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,使用這個(gè)hint可以指定連接第一個(gè)表，對(duì)于本文中的查詢語句，只需要指定第一個(gè)表為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，這個(gè)hint的作用同straight join相同，這種方式需要定義查詢塊名稱，如不定義查詢塊，則會(huì)報(bào)HINT語法錯(cuò)誤，寫法如下
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后面，優(yōu)化器將嚴(yán)格按照語句中的順序連接，第二種把straight_join當(dāng)作一種連接方式使用，使用straight_join連接的表，左邊的總比右邊的要先執(zhí)行，這兩種改寫方式可以達(dá)到同樣的效果，本例中的語句改寫如下：
--作為連接方式
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 寫在后面
連接順序的不同會(huì)導(dǎo)致SQL的性能根本性的變化，這篇文章分享了這條sql的分析和優(yōu)化思路，也列舉了調(diào)整表連接順序的幾個(gè)hint和SQL修飾詞，主要是以后看起來方便。
這里面補(bǔ)充一點(diǎn)，如果寫了hint卻不生效，很大的可能性是寫的hint里面有語法錯(cuò)誤，比如下面的例子
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執(zhí)行的順序沒有生效，命令的輸出提示有兩條告警信息，我們知道，explain命令的輸出一般只有一條告警信息，這里的兩條可能是命令運(yùn)行產(chǎn)生了異常，查看一下
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)
可以看到，第一條告警信息即是優(yōu)化器hint格式錯(cuò)誤告警。
最后修改時(shí)間：2025-08-13 09:46:40
「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵(lì)」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時(shí)必須標(biāo)注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，墨天輪將立刻刪除相關(guān)內(nèi)容。
