Đôi khi, cần phải thực hiện các truy vấn liên quan đến các bảng thực tế của chúng tôi cùng với một số dữ liệu tạm thời hoặc có nguồn gốc
Một loại dữ liệu tạm thời phổ biến có thể được sử dụng trong trường hợp này là danh sách giao dịch bên ngoài [có thể ở định dạng CSV], mà chúng tôi sẽ nhập vào cơ sở dữ liệu của mình để chúng tôi có thể kết hợp chúng [mệnh đề THAM GIA] với các bảng thực tế của chúng tôi trong
Một loại dữ liệu dẫn xuất phổ biến là dữ liệu xuất phát từ việc kết hợp nhiều bảng hoặc từ các phép tính phức tạp [đôi khi rất tốn kém để xử lý lại]. Chúng tôi có thể sử dụng loại tập hợp kết quả trung gian này để lọc hoặc tham gia thêm các hoạt động
Một chiến lược tốt là tạo các bảng tạm thời để lưu trữ loại dữ liệu trung gian này để xử lý thêm trong phiên của chúng tôi. Ưu điểm chính của việc sử dụng các bảng tạm thời là chúng chỉ hiển thị với phiên hiện tại và chúng sẽ tự động bị hủy khi đóng phiên
Trong bài viết này, tôi muốn giải thích cách MySQL hoạt động với các bảng tạm thời;
Tạo các bảng tạm thời một cách rõ ràng
Giả sử rằng ứng dụng của bạn yêu cầu thực hiện một phép tính phức tạp trên một tập hợp các bảng và sử dụng lại kết quả này trong logic nghiệp vụ của bạn
Nếu bạn thực hiện phép tính mỗi khi cần sử dụng kết quả, thì bạn có thể làm giảm hiệu suất của giải pháp và sử dụng các tài nguyên không cần thiết. Vì vậy, ý tưởng là lưu trữ kết quả trong một cấu trúc trung gian. Bạn có thể sử dụng một bảng khác hoặc có thể là một chế độ xem cụ thể hóa, nhưng dữ liệu dẫn xuất chỉ được sử dụng trên logic nghiệp vụ được liên kết với phiên của bạn. Để quản lý cấu trúc dữ liệu dễ dàng, tốt hơn nên sử dụng bảng tạm thời, vì bảng này sẽ tự động bị hủy khi đóng phiên/kết nối
Bạn có thể sử dụng câu lệnh CREATE TEMPORARY TABLE để tạo các bảng tạm thời, như trong danh sách 01. Hãy nhớ rằng chúng chỉ hiển thị trong phiên hiện tại và chúng tự động bị xóa khi đóng phiên. Do đó, hai phiên khác nhau có thể sử dụng cùng một tên bảng tạm thời trong logic nghiệp vụ của chúng mà không xung đột với nhau. Tất nhiên, trong cùng một phiên, hai bảng tạm thời không thể trùng tên
mysql> CREATE TEMPORARY TABLE temp_table [id int primary key auto_increment, payload int] ENGINE=MyISAM;mysql> INSERT INTO temp_table [payload] VALUES [100], [200], [300];
Liệt kê 01
Điều đáng chú ý là chúng ta có thể chỉ định công cụ cho các bảng tạm thời, như trong danh sách 01. Nếu chúng tôi không chỉ định công cụ, thì bảng được tạo bằng công cụ lưu trữ mặc định. Ví dụ: công cụ mặc định là InnoDB trong MySQL 5. 6
Ví dụ: chúng tôi có thể chỉ định công cụ MEMORY để dữ liệu được lưu trữ trong bộ nhớ, như trong danh sách 02
mysql> CREATE TEMPORARY TABLE temp_table_myisam [x int] ENGINE=MyISAM;mysql> CREATE TEMPORARY TABLE temp_table_memory [x int] ENGINE=MEMORY;
Liệt kê 02
Nếu chúng tôi thực thi các câu lệnh SHOW TABLES, chúng tôi sẽ không thấy bảng tạm thời nào được liệt kê
Nếu chúng tôi đăng xuất khỏi phiên MySQL và sau đó đăng nhập lại và cố gắng thực thi lệnh CHỌN trên bảng tạm thời, chúng tôi sẽ gặp lỗi vì bảng không tồn tại
Hãy tìm xem các bảng tạm thời thực sự nằm ở đâu trong hệ thống tệp, bằng cách truy vấn MySQL về vị trí, như trong danh sách 03
mysql> SHOW GLOBAL VARIABLES like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir | /tmp |
+---------------+-------+
Liệt kê 03
Sau đó, chúng ta có thể liệt kê thư mục /tmp trong hệ thống tập tin, như trong danh sách 04
$ ls -l /tmp-rw-r----- 1 mysql mysql 8592 Sep 23 10:21 #sql429_3_0.frm
-rw-r----- 1 mysql mysql 0 Sep 23 10:21 #sql429_3_0.MYD
-rw-r----- 1 mysql mysql 1024 Sep 23 10:21 #sql429_3_0.MYI
Liệt kê 04
Chúng ta có thể thấy rằng các bảng tạm thời được tạo trong thư mục /tmp và chúng có các tên khác thường [tên ngẫu nhiên bắt đầu bằng ký tự #] để tránh xung đột giữa cùng một tên bảng trong các phiên khác nhau. Chúng ta có thể kiểm tra các tệp frm, myd và myi như trong danh sách 04
Theo mặc định, tất cả các bảng tạm thời sẽ bị MySQL xóa khi đóng kết nối. Tuy nhiên, nếu bạn phát triển ứng dụng sử dụng nhóm kết nối [thường trong ứng dụng Web] hoặc kết nối liên tục [thường trong ứng dụng máy tính để bàn], thì không có gì đảm bảo rằng các bảng tạm thời sẽ tự động bị xóa khi logic nghiệp vụ của bạn được thực thi, bởi vì cơ sở dữ liệu
Do đó, cách tốt nhất là luôn xóa các bảng tạm thời theo cách thủ công khi logic nghiệp vụ không sử dụng chúng. Thực tiễn này sẽ tránh xung đột khi phục vụ các yêu cầu khác nhau sử dụng bảng tạm thời và ứng dụng sử dụng kết nối liên tục vì bảng tạm thời hoạt động trong khi kết nối vẫn mở
Bạn có thể xóa một bảng tạm thời như trong danh sách 05
mysql> DROP TEMPORARY TABLE temp_table;
Liệt kê 05
Tạo các bảng tạm thời hoàn toàn
Có những trường hợp MySQL tạo bảng tạm thời nội bộ trong khi xử lý truy vấn
Làm cách nào để biết liệu MySQL có đang tạo các bảng tạm thời bên trong không?
Đơn giản. Chỉ cần kiểm tra kế hoạch thực hiện của truy vấn cho câu "Sử dụng tạm thời"
Lưu ý rằng câu "Sử dụng tạm thời" không có nghĩa là bảng tạm thời đã được tạo trên đĩa. Nó chỉ cho biết rằng truy vấn muốn tạo một bảng tạm thời. Các bảng tạm thời tiềm ẩn vẫn đang được tạo trong bộ nhớ bằng cách sử dụng công cụ MEMORY trong khi công cụ MyISAM đang được sử dụng cho các bảng trên đĩa
Một bảng tạm thời ẩn đã được tạo bằng công cụ MEMORY [theo mặc định] sẽ tự động được chuyển đổi thành bảng MyISAM khi
- Có các trường TEXT hoặc BLOB [công cụ MEMORY không hỗ trợ các loại này]
- Kích thước của bảng tạm thời ẩn kết quả lớn hơn giá trị thấp hơn của tmp_table_size và max_heap_table_size. Nói cách khác, kích thước tối đa cho các bảng tạm thời trong bộ nhớ được xác định từ giá trị nào của tmp_table_size và max_heap_table_size nhỏ hơn. Thông báo rằng
- tmp_table_size là bảng lớn nhất có thể có trong bộ nhớ khi nó được tạo tự động bởi một truy vấn. Tuy nhiên, kích thước này không được lớn hơn max_heap_table_size
- max_heap_table_size là kích thước lớn nhất mà một bảng có thể có trong công cụ lưu trữ MEMORY, cho dù bảng đó là bảng tạm thời hay bảng không tạm thời
- Việc đặt hai biến này có cùng giá trị là điều bình thường
- Khi tăng giới hạn của tmp_table_size và max_heap_table_size, chúng tôi cho phép các bảng tạm thời lớn hơn tồn tại trong bộ nhớ
- Một chiến lược là cho phép hầu hết các bảng tạm thời tồn tại trong bộ nhớ để cải thiện hiệu suất [giả sử 90% bảng tạm thời] và chỉ di chuyển các bảng rất lớn vào đĩa [10% còn lại]
- Một cột có hơn 512 byte được sử dụng với GROUP BY hoặc UNION hoặc ORDER BY
Phần kết luận
Trong bài viết này, tôi đã giải thích các khái niệm, trường hợp sử dụng phổ biến và lợi ích liên quan đến bảng tạm thời trong MySQL. Bạn đã học cách MySQL quản lý nội bộ các bảng tạm thời
Bây giờ bạn có thể bắt đầu sử dụng chiến lược giải pháp này trong các ứng dụng cơ sở dữ liệu của riêng mình
thẻ. mysql
Được viết bởi
Giám đốc điều hành và người sáng lập Chuyên gia kiến trúc phần mềm doanh nghiệp Nubisera. cố vấn. doanh nhân. Oracle ACE. MVP của Microsoft. Tư vấn kỹ thuật bên ngoài cho Microsoft, Oracle, HP và Dell. Blogger phong phú về tất cả các chủ đề liên quan đến công nghệ phần mềm và tinh thần kinh doanh. Bằng tốt nghiệp Khoa học Máy tính Thạc sĩ Khoa học Tin học Kinh doanh [MBA]