Virtual column trong MySQL
Mysql
44
Database
22
White

ShinaBR2 viết ngày 07/07/2017

Trước khi viết bài này, mình cũng đã chịu khó ngồi search kipalog về từ khóa này, và chưa thấy ai viết về cái này, cá nhân mình nghĩ nó là một tính năng hữu ích, và mục đích của nó không gì khác giúp tối ưu database và nâng cao hiệu quả truy vấn, điều mà ai làm database cũng hướng tới. Đây là một tính năng được hỗ trợ từ phiên bản MySQL 5.7 trở lên và MariaDB 10.

Khái niệm

Như tên gọi, virtual column (hay còn được gọi là generated column) nghĩa là một cột "ảo", được định nghĩa trong cấu trúc bảng, và khi có một truy vấn tới bảng, giá trị trong cột này sẽ được tính toán "on the fly", nghĩa là bạn không phải lấy máy tính ra tính rồi điền giá trị vào để insert vào bảng, mà chỉ cần quy định hàm để tính toán ra giá trị đó, còn lại để cho MySQL làm cho chúng ta. Một trong những mục đích rất rõ ràng của virtual column, đó chính là để đánh index trên 1 cột mà cột đó được tính toán từ một cột khác. Ví dụ cụ thể và đơn giản nhất đó chính là, bạn có 1 bảng lưu tất cả các giao dịch của một ngân hàng, trong đó có 1 cột là ngày tạo với kiểu là DATETIME, câu hỏi đặt ra làm sao để tìm kiếm 1 giao dịch vào năm 2016? À, có một chút lưu ý nhỏ ở đây. Hãy xét câu truy vấn:

SELECT * FROM transaction WHERE YEAR(datecreated) = 2016

Nếu bạn đánh index (nếu có) vào cột datecreated, thì câu lệnh này không được query theo index, cơ bản là vì MySQL nó không đủ thông minh để query theo index khi mà biểu thức WHERE là 1 hàm được tính toán từ 1 cột, dù cột đó được đánh index, cho dù hàm đó là hàm có sẵn của MySQL. Vậy nếu không đánh index, thì việc truy vấn tất cả giao dịch trong một năm sẽ là cơn ác mộng!
Tới đây, bạn có thể nói, vậy thì tạo ra thêm một cột là year để lưu năm. Được, nhưng nếu vậy thì bạn phải tốn thêm vài chỗ trong ổ cứng chỉ để lưu 1 giá trị, trong khi giá trị đó hoàn toàn tính được? Có vẻ không hợp lý lắm. Virtual column ra đời để giải quyết vấn đề này, bạn sẽ không phải mất thêm một chút ổ cứng vật lý nào, trong khi vẫn có một cột để đánh index, giúp giải quyết vấn đề ở trên: tìm tất cả giao dịch theo năm.

Đặc điểm, phân loại virtual column

Đặc điểm

  1. Virtual column và index trên virtual column chỉ được hỗ trợ trong 1 vài storage engine nhất định, nếu bạn thường chỉ sử dụng InnoDB hay MyISAM thì yên tâm cả 2 engine phổ biến này đều hỗ trợ.
  2. Virtual column không được lưu trong các dòng của bảng, giúp cho phép truy vấn nhanh hơn, như đã nói ở trên.
  3. Khi thêm hoặc xóa 1 virtual column sẽ không cần phải build lại bảng.
  4. Khi tạo index trên virtual column, dữ liệu sẽ được lưu trên secondary index nhưng không lưu trên clustered index, điều này rất quan trọng giúp cho bảng không bị phình to ra mà vẫn có thể truy vấn nhanh. Bạn có thể xem lại về clustered index và secondary index ở đây.
  5. Không thể dùng virtual column làm primary key.
  6. Còn một vài lợi ích khác, nhưng để bớt phức tạp, mình xin phép không nói ở đây. Bạn có thể xem một bài viết rất hay về vấn đề này ở đây.

Phân loại

Có 2 loại virtual column là STORED (trong MySQL) hay PERSISTENT (trong MariaDB), và VIRTUAL, và trong MariaDB 10.2 trở lên, từ khóa STORED không còn được hỗ trợ, mặc định là loại VIRTUAL. Loại VIRTUAL thì giá trị của cột đó được tính toán mỗi khi xảy ra truy vấn, không được lưu trong database, còn loại PERSISTENT thì được lưu như một cột thực sự. Có một vấn đề hơi nhức nhối ở đây, MySQL 5.7 trở lên thì có thể đánh index cho loại VIRTUAL nhưng MariaDB từ 10.2.3 trở về trước, chỉ có PERSISTENT mới được hỗ trợ index. Hiện mình đang dùng MariaDB 10.2.7 và đã hỗ trợ index cho VIRTUAL. Bạn có thể xem cụ thể ở đây.

Sử dụng VIRTUAL COLUMN

Sau khi đã hiểu được công dụng của virtual column, thì mình xin phép đưa ra một vài tình huống cụ thể để sử dụng nó một cách hiệu quả:

  • Lưu một cột virtual fullname được concat từ hai cột là last namefirst name.
  • Tạo ra các cột date, month, year từ một cột datecreated để dễ dàng query theo ngày tháng năm.

Index cho VIRTUAL COLUMN

Ta nên hiểu rõ một chút về điều này. Mặc định khi tạo một virtual column, bạn cần có 1 hàm để tính toán ra giá trị column đó từ column có sẵn. Và tùy vào độ phức tạp của hàm này, thì thời gian để tính toán ra giá trị cũng khác nhau. Điều này sẽ ảnh hưởng tới tốc độ thêm/xóa/sửa khi có thêm vài cột virtual. Bởi vậy, nếu chưa đánh index, việc thêm/sửa/xóa sẽ tốn ít nhiều tài nguyên hơn. Khi đã đánh index, lợi thế của index mang lại lớn hơn nhiều so với chi phí tính toán giá trị của cột. Dĩ nhiên, hàm tính toán nên đơn giản càng tốt.
Với câu lệnh INSERT vào bảng có virtual column, bạn có thể bỏ qua giá trị cho virtual colum, hoặc gán bằng từ khóa DEFAULT. Lưu ý, bạn không thể gán trực tiếp giá trị cho một virtual column.

Hi vọng với những kiến thức nhỏ này của mình, sẽ giúp ích được cho các bạn. Cảm ơn vì đã theo dõi bài viết :)

Bình luận


White
{{ comment.user.name }}
Bỏ hay Hay
{{comment.like_count}}
Male avatar
{{ comment_error }}
Hủy
   

Hiển thị thử

Chỉnh sửa

White

ShinaBR2

10 bài viết.
93 người follow
Kipalog
{{userFollowed ? 'Following' : 'Follow'}}
Cùng một tác giả
White
57 16
Đây là một vấn đề kinh điển, và có rất nhiều bài viết về nó, tuy nhiên đa phần là dịch từ bản gốc ra và sao chép lại một vài câu lệnh, và câu hỏi t...
ShinaBR2 viết 1 năm trước
57 16
White
39 10
Vào một ngày đẹp trời, khi bạn nhận được yêu cầu phải thiết kế database cho một hệ thống, câu hỏi đầu tiên được đặt ra, quy trình làm ra nó sẽ cụ t...
ShinaBR2 viết 1 năm trước
39 10
White
32 10
Bàn về code thối Hãy tự đặt câu hỏi cho bạn, khi bắt đầu lập trình, bạn nghĩ tới điều gì? Đi phỏng vấn Điều đầu tiên tôi muốn nói về những câu hỏ...
ShinaBR2 viết 8 tháng trước
32 10
Bài viết liên quan
White
20 0
Mysql innodb internal là một chủ đề khá sâu. Bản thân tôi cũng chưa bao quát hết. Bài viết này chỉ cung cấp một cái nhìn sơ lược. Để các bạn khôn...
manhdung viết 3 năm trước
20 0
White
25 2
(Ảnh) Tiêu đề chỉ là câu khách :v, thực ra là có một vài điểm về mysql explain mà chắc chưa nhiều bạn biết, tớ cũng hay quên nên note lại cho nhớ ...
LinhPT viết gần 3 năm trước
25 2
White
2 2
Đôi khi cài đặt MySQL trên Ubuntu hoặc trên các HĐH khác, trình cài đặt sẽ tự động set một mật khẩu ngẫu nhiên cho tài khoản root, nếu không để ý t...
Dang Viet Ha viết hơn 2 năm trước
2 2
{{like_count}}

kipalog

{{ comment_count }}

bình luận

{{liked ? "Đã kipalog" : "Kipalog"}}


White
{{userFollowed ? 'Following' : 'Follow'}}
10 bài viết.
93 người follow

 Đầu mục bài viết

Vẫn còn nữa! x

Kipalog vẫn còn rất nhiều bài viết hay và chủ đề thú vị chờ bạn khám phá!