Dữ liệu trong cơ sở dữ liệu không chỉ quan trọng về khối lượng và lưu trữ, mà còn quan trọng về cách dữ liệu được tổ chức và lưu trữ. Hiện nay, mô hình quan hệ, sử dụng các bảng tương tác, là phương pháp lưu trữ dữ liệu phổ biến nhất và hiệu quả nhất trong cơ sở dữ liệu.
Thách thức tiếp theo là lấy dữ liệu từ bộ nhớ lưu trữ đó và đảm bảo rằng chính xác những gì chúng ta cần ở đây và bây giờ. Ngôn ngữ SQL mà chúng ta sử dụng để tương tác với cơ sở dữ liệu cung cấp nhiều toán tử hiệu quả để đạt được điều này. Một trong số đó là UNION.
Toán tử MySQL UNION: Định nghĩa và mục đích
Hệ quản trị cơ sở dữ liệu quan hệ đề xuất lưu trữ dữ liệu trong các bảng liên quan đến nhau. Thông thường, chúng ta giữ dữ liệu của mình trong các bảng khác nhau và cần lấy và kết hợp chúng thành một kết quả duy nhất. Toán tử UNION của MySQL cho phép chúng ta thực hiện thao tác này dễ dàng hơn so với việc sử dụng nhiều câu lệnh SELECT với các điều kiện WHERE và sau đó kết hợp kết quả của chúng thủ công.
Toán tử UNION làm gì? Nó kết hợp kết quả của một số câu lệnh SELECT, loại bỏ các kết quả trùng lặp và tạo ra một tập kết quả duy nhất.
Các tham số của UNION là:
column1, column2,... column_N: các cột mà chúng ta muốn lấy. Có thể có bất kỳ số lượng cột nào được chỉ định trong câu truy vấn.table1, table2, v.v.: các bảng chúng ta truy vấn để lấy bản ghi từ chúng. Bạn cần ít nhất một bảng trong mệnh đề FROM. Lưu ý rằng toán tử UNION có thể lấy dữ liệu từ cùng một bảng nếu cần.
Quy tắc và giới hạn của MySQL UNION
Khi sử dụng UNION với cơ sở dữ liệu MySQL, bạn cần tuân thủ một số quy tắc sau:
- Số lượng cột trong mỗi câu lệnh SELECT trong truy vấn UNION kết hợp phải giống nhau. Chúng ta sẽ nhận được một lỗi nếu các câu lệnh SELECT được sử dụng trong truy vấn UNION có số lượng cột khác nhau.
- Các cột tương ứng trong mỗi câu lệnh SELECT phải thuộc cùng một kiểu dữ liệu.
- Bộ dữ liệu kết quả sẽ có số lượng cột giống như câu lệnh SELECT đầu tiên và tên cột của câu lệnh SELECT đầu tiên được áp dụng làm tên cột của bộ dữ liệu.
- Không có câu lệnh SELECT nào được chứa các cột có giá trị NULL.
- Không được phép sử dụng UNION trong các câu lệnh con (subqueries).
Mệnh đề UNION phục vụ tốt nhất khi bạn kết hợp dữ liệu từ nhiều bảng tương tự nhau, chẳng hạn như dữ liệu hoạt động và lưu trữ, danh sách học sinh từ các nhóm khác nhau, danh sách mua hàng, v.v.
MySQL UNION so với JOIN
Mệnh đề JOIN có thể nói là mệnh đề phổ biến nhất được sử dụng để kết hợp dữ liệu từ nhiều bảng thành một tập kết quả. Tuy nhiên, nếu chúng ta so sánh JOIN với UNION trong MySQL, chúng ta sẽ thấy sự khác biệt đáng kể trong phương pháp kết hợp dữ liệu.
Chúng ta có thể hình dung cách thức làm việc của mệnh đề JOIN so với mệnh đề UNION như sau:
MySQL UNION so với UNION ALL
Việc sử dụng toán tử UNION xóa các giá trị trùng lặp trong bộ kết quả. Tuy nhiên, trong một số trường hợp, điều này không được chấp nhận. Chúng ta cần tất cả các hàng, bao gồm cả các bản ghi trùng lặp. Để lấy tất cả dữ liệu, chúng ta cần sử dụng toán tử được sửa đổi - UNION ALL.
UNION ALL hoạt động tương tự như UNION - nó kết hợp kết quả của một số truy vấn SELECT thành một bộ kết quả duy nhất. Tuy nhiên, UNION ALL vẫn bao gồm các bản ghi trùng lặp.
Cú pháp cơ bản của UNION ALL với một số câu lệnh SELECT như sau:
SELECT column1, column2, column3,... column_N FROM table1 UNION ALL SELECT column1, column2, column3,.. column_N FROM table2;Các tham số, quy tắc sử dụng và giới hạn của UNION ALL giống như toán tử UNION đơn giản. Chúng ta đã định nghĩa chúng trước đó trong bài viết này.
Một khía cạnh khác cần chú ý là hiệu suất của các toán tử UNION và UNION ALL. UNION ALL nhanh hơn vì nó không tốn tài nguyên và thời gian kiểm tra kết quả để loại bỏ các bản ghi trùng lặp.
Ví dụ và ứng dụng thực tế của MySQL UNION
Hãy xem xét các ví dụ về việc sử dụng các mệnh đề UNION và UNION ALL trong thực tế. Để minh họa các ví dụ thực tế này, chúng ta sẽ sử dụng cơ sở dữ liệu kiểm tra MySQL nổi tiếng sakila và dbForge Studio for MySQL - một công cụ đồ họa đa tính năng cho MySQL cho phép chúng ta viết các câu truy vấn SQL hiệu quả một cách dễ dàng và thực hiện tất cả các tác vụ liên quan đến cơ sở dữ liệu trên các hệ quản trị cơ sở dữ liệu này.
Ví dụ 1: UNION cơ bản
Trong ví dụ đầu tiên, chúng ta muốn lấy tất cả địa chỉ duy nhất từ kết quả của hai câu lệnh SELECT.
SELECT address, CONCAT_WS(' ',first_name,last_name) Customer_Name FROM address INNER JOIN customer ON customer.address_id = address.address_id UNION SELECT address, CONCAT_WS(' ',first_name,last_name) Customer_Name FROM address_out INNER JOIN customer ON customer.address_id = address_out.address_id;Ví dụ 2: UNION ALL
Ví dụ dưới đây là một câu truy vấn kết hợp kết quả của hai câu lệnh SELECT. Câu lệnh đầu tiên lấy các tiêu đề phim và năm phát hành của chúng (trong trường hợp này, năm là 2006). Câu lệnh SELECT thứ hai truy xuất tên của các danh mục (năm phát hành được đặt là 0 vì giá trị này không được áp dụng cho tên danh mục).
Với UNION ALL, chúng ta kết hợp kết quả của hai SELECT này thành một tập dữ liệu. Chúng ta cũng sắp xếp nó theo năm phát hành và tiêu đề bằng cách sử dụng mệnh đề ORDER BY để dễ đọc hơn.
SELECT title, release_year FROM film WHERE release_year = 2006 UNION ALL SELECT name, 0 AS release_year FROM category ORDER BY release_year DESC, title ASC;Ví dụ 3: UNION với điều kiện
Tình huống này đề xuất sử dụng UNION với mệnh đề WHERE để lọc kết quả. Câu lệnh SELECT đầu tiên yêu cầu tên, họ, và địa chỉ email của người mua hàng sử dụng cửa hàng 1. Truy vấn SELECT khác truy xuất cùng dữ liệu cho tất cả người mua hàng sử dụng cửa hàng 2. Với UNION, chúng ta có thể tạo một tập kết quả duy nhất chứa dữ liệu về khách hàng của cả hai cửa hàng.
Lưu ý: Vì chúng ta đang sử dụng UNION, và không phải UNION ALL, tập kết quả sẽ chỉ có các giá trị duy nhất. Đó là lý do tại sao, ngay cả khi một số người là khách hàng của cả hai cửa hàng, tên của họ sẽ chỉ xuất hiện một lần duy nhất trong kết quả.
SELECT CONCAT_WS(' ',first_name,last_name) Customer_Name, email FROM customer WHERE store_id = 1 UNION SELECT CONCAT_WS(' ',first_name,last_name) Customer_Name, email FROM customer WHERE store_id = 2;Ví dụ 4: UNION với DISTINCT
Trong trường hợp này, chúng ta sử dụng UNION với DISTINCT để kết hợp kết quả của hai câu lệnh SELECT: câu lệnh truy xuất danh sách thành phố ở Texas và câu lệnh truy xuất danh sách thành phố ở California. Bằng cách áp dụng DISTINCT cho mỗi SELECT, chúng ta loại bỏ tên thành phố trùng lặp khỏi tập kết quả cuối cùng.
SELECT DISTINCT city FROM address,city WHERE address.city_id=city.city_id AND district = 'Texas' UNION SELECT DISTINCT city FROM address,city WHERE address.city_id=city.city_id AND district = 'California';Ví dụ 5: UNION đơn giản cho tìm kiếm từ khóa
Ở đây, chúng ta áp dụng UNION để kết hợp nhiều kết quả SELECT, trong đó mỗi truy vấn lấy các tiêu đề phim và mô tả chứa từ khóa "action". Lưu ý rằng việc sử dụng UNION có nghĩa là loại bỏ tất cả các giá trị trùng lặp. Do đó, các bộ phim phù hợp với tiêu chí tìm kiếm sẽ chỉ xuất hiện một lần trong tập kết quả cuối cùng.
SELECT title, description FROM film WHERE title LIKE '%action%' UNION SELECT title, description FROM film WHERE description LIKE '%action%';Ví dụ 6: UNION cho nhiều tìm kiếm từ khóa
Trong ví dụ này, chúng ta có hai truy vấn SELECT và mỗi truy vấn sẽ mang lại danh sách các bộ phim phù hợp với từ khóa tìm kiếm "action" hoặc "adventure". Mỗi SELECT lấy tiêu đề và mô tả của những bộ phim có chứa từ "action" hoặc "adventure".
SELECT title, description FROM film WHERE title LIKE '%action%' OR title LIKE '%adventure%' UNION SELECT title, description FROM film WHERE description LIKE '%action%' OR description LIKE '%adventure%';Hiệu suất và tối ưu hóa của MySQL UNION
Tốc độ hiệu suất và hiệu quả là các chỉ số quan trọng đối với mọi truy vấn chúng ta soạn thảo. Chúng ta có nhiều phương pháp thực tế để tăng tốc truy vấn trên MySQL và giảm chi phí của chúng. Nói về các truy vấn UNION và UNION ALL, chúng ta có thể áp dụng các phương pháp sau đây.
-
Thêm chỉ mục cho các cột: Chỉ mục là phương pháp tối ưu hóa cơ sở dữ liệu. Mặc dù chỉ mục trong MySQL là các đối tượng vật lý được lưu trữ riêng biệt, hiệu quả của chúng trong việc tăng tốc truy vấn bù lại cho không gian đĩa. Thêm chỉ mục cho các cột, đặc biệt là các cột được sử dụng với các mệnh đề WHERE, ORDER BY và GROUP BY. Điều này sẽ tăng tốc đáng kể hiệu suất.
-
Tránh sử dụng DISTINCT trong truy vấn UNION: Mệnh đề DISTINCT là tùy chọn trong cú pháp truy vấn UNION. Mục đích của mệnh đề đó là loại bỏ các giá trị trùng lặp trong kết quả. Tuy nhiên, toán tử UNION mặc định đã loại bỏ các bản ghi giống nhau và việc sử dụng DISTINCT không đem lại bất kỳ điều gì cho truy vấn.
-
Tránh sử dụng LIKE với các ký tự đại diện đứng đầu: Nếu truy vấn của bạn bao gồm toán tử LIKE với các ký tự đại diện đứng đầu, nó buộc truy vấn bỏ qua các chỉ mục hiện có. Kết quả là nó chạy lâu hơn và tiêu thụ nhiều tài nguyên hơn để thực hiện quét toàn bộ bảng. Khi cơ sở dữ liệu lớn, điều này có thể là một vấn đề. Vì vậy, đừng bao gồm các yếu tố như LIKE '%mysearch%' trong truy vấn UNION của bạn.
-
Truy vấn chỉ cần các cột cần thiết: Đây là khuyến nghị chung khác. Việc quét các bảng rộng yêu cầu rất nhiều thời gian và tài nguyên CPU. Vì vậy, luôn chỉ định các cột bạn cần xử lý và lấy dữ liệu từ chúng.
-
Ưu tiên UNION ALL hơn UNION nếu thích hợp: Yếu tố chính làm tăng thời gian và chi phí CPU là quá trình xóa các giá trị trùng lặp khỏi kết quả SELECT. Nó đòi hỏi quá trình quét thêm của tất cả các kết quả để xác định và xóa các bản ghi trùng lặp. Nếu tình huống làm việc của bạn không đòi hỏi giá trị duy nhất trong tập kết quả kết hợp, hãy sử dụng UNION ALL như tùy chọn nhanh hơn và hiệu quả hơn.
Các mẹo tối ưu cơ bản cho các truy vấn MySQL hoạt động tương tự như toán tử UNION. Và, tất nhiên, có một số khuyến nghị cụ thể. Tốt nhất là đánh giá truy vấn UNION MySQL của bạn trước khi thực thi chúng. Công cụ cơ sở dữ liệu hiện đại với giao diện đồ họa cung cấp chức năng này.
Trực quan hóa và thực thi UNION MySQL một cách hiệu quả bằng các công cụ GUI
dbForge Studio for MySQL bao gồm công cụ Query Profiler giúp thu thập thống kê về hiệu suất truy vấn và giúp quản trị cơ sở dữ liệu xác định vấn đề, giải quyết nó và tinh chỉnh truy vấn trước khi thực thi. Trong số nhiều tính năng, Query Profiler cung cấp kế hoạch EXPLAIN với các khả năng sau:
- Dễ dàng phát hiện các nút cổ chai trong các truy vấn.
- Dễ dàng phát hiện thời gian chờ tại các giai đoạn thực hiện truy vấn khác nhau.
- Điều tra và chẩn đoán nguyên nhân của các truy vấn chậm.
- Lập hồ sơ truy vấn và so sánh kết quả của chúng theo chế độ xem trực quan.
Kế hoạch này cho phép người dùng đánh giá các truy vấn UNION/UNION ALL (và bất kỳ truy vấn nào khác mà bạn có thể cần) trước khi thực thi chúng và tiêu thụ tài nguyên. Phân tích chất lượng cho phép các chuyên gia cơ sở dữ liệu viết lại bất kỳ truy vấn MySQL nào để làm cho chúng nhanh hơn.
Rất khuyến nghị để luôn phân tích truy vấn trước khi thực thi chúng. Cho dù đó là UNION của MySQL hay truy vấn khác, hãy áp dụng kế hoạch EXPLAIN trước tiên, xác định bất kỳ vấn đề nào và sửa chúng trước khi chạy script. Ghi lại kết quả của bạn để lưu chúng cho tham khảo sau này và theo dõi hiệu suất truy vấn hàng ngày. Query Profiler cung cấp tất cả các chức năng cần thiết ngoài kế hoạch EXPLAIN để giúp bạn điều chỉnh truy vấn và đạt hiệu suất cao.
Ngoài ra, đừng quên về các trợ lý mã lệnh để viết mã sạch và hiệu quả nhanh hơn. Tự động hoàn thành, kiểm tra cú pháp, gỡ lỗi mã lệnh và định dạng cùng với Query Builder cung cấp phương pháp thuận tiện nhất để xây dựng các truy vấn phức tạp một cách trực quan luôn sẵn sàng phục vụ bạn với dbForge Studio for MySQL.
Các phương pháp thay thế cho UNION trong MySQL
Lấy các phần dữ liệu cụ thể từ các bảng khác nhau và kết hợp chúng theo một số tiêu chí là một nhiệm vụ thường xuyên cho tất cả người dùng cơ sở dữ liệu. MySQL cung cấp các phương tiện khác nhau - toán tử - để đạt được mục tiêu đó. Chúng ta có các JOINs phổ biến và cũng là các UNIONs. Ngoài ra, đáng đề cập đến các toán tử INTERSECT và EXCEPT.
Toán tử INTERSECT
MySQL INTERSECT cho phép bạn truy vấn nhiều bảng và trả về một bộ kết quả duy nhất chỉ chứa các bản ghi xuất hiện trong tất cả các câu lệnh SELECT tham gia.
Chúng ta có thể hình dung cách thức hoạt động của toán tử INTERSECT như sau:
Cú pháp cơ bản của truy vấn INTERSECT là:
SELECT column1, column2, column3,... column_N FROM table1 INTERSECT SELECT column1, column2, column3,... column_N FROM table2;Toán tử EXCEPT
MySQL EXCEPT cho phép chúng ta truy vấn nhiều bảng bằng cách sử dụng câu lệnh SELECT và lấy các bản ghi chỉ xuất hiện trong kết quả của truy vấn SELECT đầu tiên. Nó cho phép chúng ta tìm ra sự khác biệt giữa dữ liệu trong hai bảng hoặc xác định các bản ghi duy nhất trong bảng.
Chúng ta có thể hình dung cách thức hoạt động của toán tử EXCEPT như sau:
Cú pháp cơ bản của toán tử EXCEPT như sau:
SELECT column1, column2, column3,... column_N FROM table1 EXCEPT SELECT column1, column2, column3,... column_N FROM table2;Cả INTERSECT và EXCEPT đều loại bỏ các giá trị trùng lặp khỏi tập kết quả kết hợp theo mặc định. Tên cột và số lượng cột trong kết quả đầu ra giống như câu lệnh SELECT đầu tiên.
Kết luận
MySQL UNION là một công cụ hữu ích cho phép chúng ta truy xuất và kết hợp dữ liệu từ các truy vấn khác nhau và trình bày nó một cách phù hợp cho công việc tiếp theo. Đó là lý do tại sao rất quan trọng để hiểu cách toán tử UNION hoạt động, lợi ích và giới hạn của nó và cách áp dụng nó đúng cách, đặc biệt là đối với các cơ sở dữ liệu lớn.
Công cụ GUI rất hữu ích cho tất cả người dùng cơ sở dữ liệu cần phương pháp hiệu quả hơn để làm việc với các toán tử UNION (và các toán tử khác) trên MySQL. Công cụ GUI phổ biến như dbForge Studio for MySQL mà chúng tôi đã sử dụng để minh họa bài viết của mình cung cấp nhiều tính năng để đơn giản hóa cuộc sống của người dùng.
Cụ thể, công cụ Query Builder giúp bạn xây dựng ngay cả các truy vấn phức tạp một cách dễ dàng, các tính năng tối ưu hóa đề xuất cải tiến để tăng tốc truy vấn và làm cho chúng hiệu quả hơn, và biểu đồ hiển thị tổng thể minh họa kết quả của các phép UNION một cách thân thiện với người dùng.