Join vs Subquery: The Problem Of MySQL Query Optimizer?

Câu chuyện muôn thuở của MySQL và được nêu đi nêu lại ở các blog khác nhau, là sự so sánh giữa Join và Subquery. Căn bản thì tất cả đều đi đến một kết luận chung, đó là MySQL có cơ chế hỗ trợ Join, khiến nó nhanh hơn Subquery, và do vậy chúng ta nên dùng Join, hạn chế dùng Subquery.

Vậy cụ thể nguyên lý ở đây là gì, và có phải lúc nào Join cũng nhanh hơn Subquery không, chúng ta cùng tìm hiểu cách thức hoạt động của MySQL.

High Performance MySQL, 3rd edition - P. 211

Hình vẽ trên chắc không xa lạ với nhưng ai hiểu biết về MySQL. Sau khi parser phân tích câu query và cùng preprocessor tạo nên parse tree, "Query Optimizer" sẽ thực hiện công đoạn hết sức quan trọng là phân tích và tối ưu câu query để tạo nên "Query execution plan". Subquery có nhanh hơn hay chậm hơn Join thì hầu hết đều được quyết định ở quá trình này.

Query Optimizer có vai trò quan trọng giúp câu query được thực hiện tốt nhất có thể. Tuy nhiên, công cụ nào cũng có hạn chế của nó, và Query Optimizer cũng vậy, đặc biệt là hạn chế khiến cho Subquery trở thành truy vấn bị "ruồng bỏ" trong MySQL.

Chúng ta đến với bài toàn cụ thể sau đây. Giả sử chúng ta có một bản ghi lưu các bộ phim và diễn viên, trong đó một diễn viên có thể tham gia nhiều bộ phim và một bộ phim có thể có nhiều diễn viên. Chúng ta có 3 bảng như sau.

mysql> DESC films;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(256) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> DESC actors;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(256) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> DESC film_actors;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| film_id  | int(11) | YES  | MUL | NULL    |                |
| actor_id | int(11) | YES  | MUL | NULL    |                |
+----------+---------+------+-----+---------+----------------+

Không có gì quá phức tạp, và chúng ta lưu 2 bảng quan hệ nhiều nhiều bằng một bảng trung gian film_actors.

Câu hỏi đơn giản: Tìm danh sách bộ phim mà diễn viên có id là 1 tham gia. Vâng, bài toán level "tiểu học", không khó để viết ra 1 query có sử dụng subquery.

SELECT * FROM films WHERE id IN (
    SELECT film_id FROM film_actors WHERE film_actors.actor_id = 1
);

Khi viết ra truy vấn loại này, chúng ta mong muốn MySQL, trước tiên là tìm hết ra danh sách các bộ phim mà diễn viên tham dự từ bảng trung gian, sau đó thì tìm thông tin chi tiết ở bảng phim.

Cụ thể, chúng ta mong MySQL sẽ chạy như thế này

SELECT GROUP_CONCAT(film_id) FROM film_actors WHERE actor_id = 1
/* Giả sử kết quả là 1,3,5,15,8,20,100 */
SELECT * FROM films WHERE id IN (1,3,5,15,8,20,100);

Vâng, có vẻ khá tự nhiên và nếu được như vậy thì quá tốt. Tuy nhiên mọi việc không phải lúc nào cũng suôn sẻ. MySQL Optimizer lại nghĩ khác @@

MySQL Optimizer cho rằng, để hỗ trợ việc tìm kiếm phim dễ dàng hơn, nó viết lại câu Query và chèn thêm id của phim vào subquery, với mong muốn quá trình tìm kiếm nhanh hơn.

SELECT * FROM films WHERE EXISTS(
    SELECT * FROM film_actors WHERE actor_id = 1 AND films.id = film_actors.film_id
);

Công việc tưởng như giúp đỡ cho quá trình truy vấn lại có tác dụng ngược lại. Từ một truy vấn mà có thể chạy độc lập subquery để thực hiện query bên ngoài, MySQL Optimizer buộc subquery không thể chay độc lập do có ràng buộc với query chính, biến subquery của chúng ta thành loại dependency subquery (query phụ thuộc). Với loại subquery mà bản thân nó không thể chạy độc lập với query bên ngoài, thì việc chậm là điều không có gì bàn cãi.

Câu hỏi đặt ra, làm cách nào mà chúng ta biết MySQL thực hiện câu truy vấn "kém thông minh" thế kia để còn biết mà tránh. Vâng, không có gì xa lạ, dùng EXPLAIN sẽ giúp ta thấy điều đó.

Trước tiên chúng ta thử EXPLAIN truy vấn ra. (Một phiên bản cao cấp hơn của EXPLAIN là EXPLAIN với tham số EXTENDED)

mysql> EXPLAIN EXTENDED SELECT * FROM films WHERE id IN (
    ->     SELECT film_id FROM film_actors WHERE film_actors.actor_id = 1
    -> );
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: films
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: film_actors
         type: index_subquery
possible_keys: film_id,actor_id
          key: film_id
      key_len: 5
          ref: func
         rows: 1
     filtered: 100.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

Chúng ta có thể thấy select_type ở dòng thứ 2 là DEPENDENT SUBQUERY. Đây thực sự là một dấu hiệu không lành. Có 1 warning ở kết quả, và chúng ta sẽ kiểm tra xem nó là gì.

mysql> SHOW WARNINGSG
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `high_performance_mysql`.`films`.`id` AS `id`,`high_performance_mysql`.`films`.`name` AS `name` from `high_performance_mysql`.`films` where <in_optimizer>(`high_performance_mysql`.`films`.`id`,<exists>(<index_lookup>(<cache>(`high_performance_mysql`.`films`.`id`) in film_actors on film_id where ((`high_performance_mysql`.`film_actors`.`actor_id` = 1) and (<cache>(`high_performance_mysql`.`films`.`id`) = `high_performance_mysql`.`film_actors`.`film_id`)))))
1 row in set (0.00 sec)

Như vậy là bằng việc kiểm tra truy vấn và warnings, chúng ta đã thấy được query mà MySQL thực sự sử dụng (message của warning). Không có gì lạ khi trường hợp này, Subquery lại chậm đến vậy. Lỗi thuộc về Optimizer.

Chúng ta thử tìm hiểu xem Join sẽ hoạt động thế nào trong trường hợp này.

mysql> explain select * from films inner join film_actors on (films.id = film_actors.film_id) where actor_id = 1G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: films
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actors
         type: ref
possible_keys: film_id,actor_id
          key: film_id
      key_len: 5
          ref: high_performance_mysql.films.id
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

Cả 2 dòng đều cho kết quả select_type là SIMPLE, đủ thấy không có gì quá nguy hiểm ở đây. Đó là lý do tại sao người ta nói rằng, MySQL được tối ưu cho việc sử dụng JOIN.

Ở ví dụ trên, chúng ta thấy rõ ràng Subquery chậm hơn hẳn Join, do việc bản thân nó bị biến thành dependency subquery do hiểu lầm của MySQL Query Optimizer. Vậy, liệu có phải lúc nào Subquery cũng chậm hơn Join. Chúng ta đến với ví dụ tiếp theo.

Vẫn là 3 bảng lưu trữ thông tin bộ phim và diễn viên, bài toán của chúng ta là tìm ra danh sách các diễn viên tham gia ít nhất một bộ phim.

Nếu theo lời khuyên thông thường, dùng Join nhanh hơn Subquery, chúng ta làm như sau.

 SELECT DISTINCT(actors.id) FROM actors INNER JOIN film_actors ON (actors.id = film_actors.actor_id);

Câu truy vấn không có gì phức tạp, nhưng chú ý là chúng ta cần thêm truy vấn DISTINCT để đảm bảo id của phim sẽ không bị trùng lặp, do việc sử dụng phép JOIN sẽ trả về nhiều hơn 1 bản ghi với 1 films.id.

Tuy nhiên, hãy cùng xem lại xem, dùng Join ở đây có thực sự "tự nhiên". Điều chúng ta cần là tìm xem có "tồn tại" id của phim trong bảng film_actors hay không. Vậy tại sao không sử dụng từ khóa tồn tại - EXISTS của MySQL. Nó giúp chúng ta tránh phải sử dụng DISTINCT, từ khóa có thể làm ảnh hưởng đến hiệu năng.

Do vậy, chúng ta thử dùng EXISTS và Subquery.

SELECT id FROM actors WHERE EXISTS (SELECT * FROM film_actors WHERE actors.id = film_actors.actor_id);

Vậy là ta đã thử cả hai cách, dùng Join hoặc Subquery. Đương nhiên là cả 2 đều ra kết quả chính xác. Tuy nhiên, chúng ta cùng so sánh hiệu năng trong trường hợp này.

Với JOIN

mysql> EXPLAIN SELECT DISTINCT(actors.id) FROM actors INNER JOIN film_actors ON (actors.id = film_actors.actor_id)G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actors
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using index; Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actors
         type: ref
possible_keys: actor_id
          key: actor_id
      key_len: 5
          ref: high_performance_mysql.actors.id
         rows: 1
        Extra: Using where; Using index; Distinct
2 rows in set (0.00 sec)

Với Subquery

mysql> EXPLAIN SELECT id FROM actors WHERE EXISTS (SELECT * FROM film_actors WHERE actors.id = film_actors.actor_id)G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: actors
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: film_actors
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: high_performance_mysql.actors.id
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)

Nếu chỉ nhìn vào trường select_type của kết quả trả về, hẳn các bạn sẽ không ngần ngại khi cho rằng Subquery chậm hơn, do nó sử dụng Dependency Subquery. Như đã nói ở ví dụ trước, việc này làm ảnh hưởng rất lớn đến hiệu năng.

Tuy nhiên, chờ một chút. Để ý rằng cột Extra của cách dùng Join, chúng ta phải sử dụng Using temporary. Vấn đề chính là ở đây. Do việc cần thực hiện SELECT DISTINCT để loại bỏ các id trùng lặp, MySQL buộc phải tạo ra một bảng tạm thời, dùng cho việc loại trừ id này. Công việc này thuộc diện nên tránh trong MySQL, bởi nó làm tăng các thao tác I/O khiến cho hiệu năng giảm sút.

Vậy, so sánh giữa Subquery với Join lúc này, là so sánh dependency subquery với using temporary. Cái nào nhanh hơn, cái nào chậm hơn?

Không có quyết định chính xác cho tất cả các trường hợp, chỉ có thực hiện với ví dụ thức tế. Trong một ví dụ tương tự ở cuốn "High Performance MySQL, 3rd Edition", thì Subquery nhanh hơn.

Join nhanh hơn hay Subquery nhanh hơn, nguyên nhân xuất phát từ cách phân tích của MySQL Optimizer, và các trường hợp sẽ có kết quả khác nhau phụ thuộc vào tùy hoàn cảnh sử dụng của bạn. Mặc dù trong hầu hết các trường hợp Join nhanh hơn, thì chúng ta cũng nên lưu ý dành thời gian EXPLAIN câu truy vấn để có lựa chọn đúng đắn nhất.

• High Performance MySQL, 3rd Edition
• Sử dụng EXPLAIN để tối ưu câu lệnh MySQL