Điều gì làm cho PostgreSQL hơn các CSDL SQL khác? - P2 Trong bài này, chúng ta sẽ xem điều gì làm cho PostgreSQL khác biệt với những cơ sở dữ liệu SQL mã nguồn mở khác, như MySQL, MariaDB và Firebird. Trong phần 1 của loạt bài viết này chúng ta đã xem cách lưu trữ dữ liệu - mô hình, các cấu trúc, các kiểu và các giới hạn kích thước. Trong phần 2 này, chúng ta sẽ xem các thao tác và truy vấn, bao gồm đánh chỉ mục, các tính năng bảng ảo và các khả năng truy vấn. Đánh chỉ mục PostgreSQL cung cấp khả năng đánh chỉ mục mà những cơ sở dữ liệu mã nguồn mở khác không có. PostgreSQL hỗ trợ các loại chỉ mục partial, expression, GiST và GIN, và nhiều chuẩn khác. Chúng ta hãy xem xét một số loại chỉ mục đặc biệt này. Partial Indexes (Đánh chỉ mục từng phần) Partial Indexes có thể được tạo ra khi bạn chỉ muốn đánh chỉ mục một tập hợp con của bảng, chẳng hạn như chỉ một số dòng nơi giá trị của cột phù hợp với một điều kiện cụ thể. Lợi thế của tính năng này giúp chỉ mục của bạn có kích thước hợp lý, với mục tiêu để cải thiện hiệu năng và giảm kích thước trên ổ đĩa. Một khía cạnh quan trọng của partial indexes là cột được đánh chỉ mục có thể khác so với các điều kiện được cung cấp. Ví dụ, bạn có thể muốn đánh chỉ mục chỉ các tài khoản của những khách hàng trả tiền thực, chứ không phải những tài khoản được tạo ra để kiểm thử nội bộ: -- create index of only paying customersCREATE INDEX paying_accounts_idx ON accounts (account_id) WHERE account_type <> 'test'; Điều quan trọng phải lưu ý rằng thỉnh thoảng trong MySQL thuật ngữ "partial indexes" được sử dụng để đề cập tới cắt giảm các giá trị được đánh chỉ mục thành một số byte nhất định, không giới hạn số lượng các dòng được đánh chỉ mục dựa trên điều kiện. Partial indexes như chúng ta đang mô tả ở đây không được hỗ trợ bởi MySQL. Expression Indexes Expression indexes có thể được tạo ra bằng cách sử dụng bất kỳ function nào để tính toán trước một cột trong chỉ mục. Các giá trị mới được đánh chỉ mục và được xem như các hằng số để truy vấn, đối lập với việc phải tính toán mỗi khi một truy vấn chạy. Một ví dụ là, nếu bạn có một trang web hit log thu thập số truy cập URL trong bất cứ định dạng nào mà chúng nhận được, bạn có thể muốn tạo ra một index của những URL dạng viết thường để chuẩn hóa dữ liệu (PostgreSQL có phân biệt chữ hoa chữ thường - compose.io và Compose.io được xem là các kết quả khác nhau): -- create index of lower-cased URLsCREATE INDEX webhits_lower_urls_idx ON webhits (lower(url)); GIST và GIN (và BRIN đang trong quá trình phát triển) GiST (Generalized Search Tree) cho phép kết hợp B-tree, R-tree, và các kiểu đánh chỉ mục người dùng tự định nghĩa để tạo các chỉ mục tùy chỉnh với khả năng truy vấn tiên tiến. GiST đã được sử dụng trong PostGIS (nó đã được chúng tôi thực hiện chuẩn hóa với tất cả các triển khai PostgreSQL từ tháng Giêng), và OpenFTS (một cỗ máy tìm kiếm full text mã nguồn mở). PostgreSQL cũng hỗ trợ SP-GiST cho phép tạo phân vùng chỉ mục tìm kiếm để tăng tốc độ truy xuất. GIN (Generalized Inverted Index) cho phép đánh chỉ mục các kiểu dữ liệu kết hợp. Các kiểu dữ liệu kết hợp cho phép bạn kết hợp các kiểu dữ liệu khác nhau theo nhiều cách để tạo ra một cái gì đó hoàn toàn tùy chỉnh. Xem phần 1 của loạt bài viết này về tổng quan các kiểu dữ liệu kết hợp. Để tạo các chỉ mục GIST và GIN, ta dùng cú pháp là: CREATE INDEX... ON... USING GIST|GIN... Khá đơn giản. Trong PostgreSQL 9.5 (hiện tại đang là bản beta), BRIN (Block Range Index) sẽ được giới thiệu. BRIN cho phép chia các bảng lớn thành các khoảng dựa trên cột đã được đánh chỉ muc. Điều này có nghĩa là các kế hoạch truy vấn có thể quét trong khoảng đã được chỉ định bởi truy vấn. Vì thế, nhờ các khoảng chỉ mục, số lượng kích thước đĩa cần thiết cho các chỉ mục sẽ nhỏ hơn một chỉ mục chuẩn B-Tree. So sánh Các cơ sở dữ liệu SQL khác mà chúng ta thấy đang thu hẹp khoảng cách khi nói đến expression indexes. Trong MySQL 5.7.6, generated columns đã được giới thiệu có thể sử dụng như một expression index. Với MariaDB, các cột ảo (“virtual” colums cũng được biết đến như là "generated" hoặc "computed") đã được giới thiệu trong phiên bản 5.2, nhưng chỉ hỗ trợ sử dụng các hàm xây dựng sẵn cho việc tạo các cột (không có các hàm người dùng tự định nghĩa). Trong phiên bản 2.0 của Firebird, expression indexing sử dụng để tính toán các cột đã được giới thiệu. Tuy nhiên, không có cơ sở dữ liệu nào hỗ trợ đánh chỉ mục partial, GiST hoặc GIN. Vì thế, như chúng ta đã đề cập trong phần 1, các kiểu dữ liệu JSON tự nhiên không thể đánh chỉ mục trong các cơ sở dữ liệu này. Và bạn đừng quên đọc bài viết: Kiểm tra chỉ mục đơn giản với PostgreSQL (Simple Index Checking with PostgreSQL) của tác giả Matt Barr khi bạn có các chỉ mục và muốn phân tích hiệu suất của chúng. Các tính năng bảng ảo Các bảng ảo là cần thiết trong nhiều truy vấn. Tất cả các cơ sở dữ liệu SQL chúng tôi đã so sánh cung cấp một số chức năng bảng ảo. PostgreSQL còn cung cấp cho bạn nhiều hơn thế. CTEs và đệ quy PostgreSQL hỗ trợ Common Table Expressions (CTEs) sử dụng mệnh đề WITH. Chúng tôi đã minh họa ví dụ này trong bài viết: PostgreSQL - Series, Random and With. CTEs cho phép bạn tạo các bảng ảo nối tiếp trong các truy vấn của mình, thể hiện hợp lý thứ tự các thao tác để có thể dễ đọc và đảm bảo chất lượng hơn trong việc tạo các bảng ảo sử dụng các truy vấn con ở nơi khác trong truy vấn. CTEs trong PostgreSQL cũng có thể sử dụng đệ quy. Khả năng tiện dụng này cho phép bạn duyệt qua một hệ thống phân cấp, với các truy vấn self-referring (gọi đến chính nó) liên tục cho đến khi không còn level dữ liệu nào có thể được trả về nữa. Dưới đây là một ví dụ của một CTE đệ quy xác định các level, chủ đề, và các quan hệ cha con trong phân loại chủ đề. -- query with recursiveCTE WITH RECURSIVE topic_taxonomy_recursive (level, parent_topic_name, topic_name)AS ( SELECT 1, tt.parent_topic_name, tt.topic_name FROM topic_taxonomy tt WHERE tt.parent_topic_name = 'All Topics' UNION ALL SELECT ttr.level + 1, tt.parent_topic_name, tt.topic_name FROM topic_taxonomy_recursive ttr, topic_taxonomy tt WHERE ttr.topic_name = tt.parent_topic_name) SELECT level, parent_topic_name, topic_name FROM topic_taxonomy_recursive; MySQL và MariaDB không sử dụng mệnh đề WITH và vì thế không chính thức hỗ trợ CTEs. Trong khi bạn có thể tạo các bảng có nguồn gốc sử dụng các truy vấn phụ (subqueries) trong các cơ sở dữ liệu này, chúng không cho phép đệ quy. Ngoài ra, mặc dù truy vấn tối ưu trong MySQL đã được cải thiện từ phiên bản 5.6, việc lồng các truy vấn phụ được biết đến là khá phiền hà và có thể ảnh hưởng đáng kể đến hiệu suất trong cơ sở dữ liệu này. Firebird đi trước MySQL và MariaDB ở đây, phù hợp với các chức năng của PostgreSQL bằng cách hỗ trợ CTEs sử dụng WITH và cũng cung cấp khả năng đệ quy. Materialized views Materialized views là tính năng bảng ảo tiện lợi khác đã được hỗ trợ bởi PostgreSQL. Materialized views giống như các view thông thường trong việc trình diễn tập kết quả của một truy vấn mà bạn muốn sử dụng thường xuyên, ngoại trừ tập kết quả thực sự được lưu trữ trên ổ đĩa giống như các bảng thông thường. Materialized views cũng có thể được đánh chỉ mục, không giống các views thông thường được tạo ra mỗi lần chúng được gọi, materialized views là các ảnh chụp tại một thời điểm. Chúng không được làm mới (refreshed) ngoại trừ được chỉ định. Điều này có thể tăng đáng kể tốc độ với các truy vấn chạy sử dụng materialized views. Thay vì sử dụng các views thông thường hoặc phải làm các join phức tạp trên các bảng hoặc thực hiện các hàm tập hợp (aggregation funcitons) trong truy vấn, sử dụng materialized vies với các dữ liệu cần thiết sẵn sàng và chờ đợi trên ổ đĩa để tăng hiệu quả. Khi bạn sẵn sàng cập nhật dữ liệu trong materialized view, nó có thể làm mới sử dụng lệnh REFRESH. Dưới đây là một ví dụ của một materialized view tạo ra dữ liệu doanh thu tổng hợp: -- create an aggregated revenue result as a materialized viewCREATE MATERIALIZED VIEW aggregatedMonthlyRevenue (year, month, total_revenue)AS ( SELECT date_part('year', date) AS year, date_part('month', date) AS month, SUM(revenue) AS total_revenue FROM revenue WHERE date >= '2014-01-01' GROUP BY date_part('year', date), date_part('month', date) ORDER BY date_part('year', date), date_part('month', date)); -- refresh the materialized view as neededREFRESH MATERIALIZED VIEW aggregatedMonthlyRevenue; Firebird, MySQL và MariaDB không hỗ trợ materialized views, mặc dù có thể tạo ra một cách giải quyết trong các cơ sở dữ liệu này bằng cách tạo một bảng thông thường sử dụng một stored procedure hoặc trigger để cập nhật các yêu cầu. Các khả năng truy vấn Các khả năng truy vấn được mở rộng. Chúng ta đã nói về WITH một chút trong phần trước. Bây giờ hãy xem xét một vài tính năng tùy chọn khác có thể được sử dụng trong các câu lệnh SELECT. Kết hợp các truy vấn PostgreSQL cung cấp các mệnh đề UNION, INTERSECT và EXCEP để tương tác giữa các câu lệnh SELECT. UNION sẽ nối các kết quả của câu lệnh SELECT thứ 2 tới kết quả của câu lệnh đầu tiên. INTERSECT chỉ trả lại các hàng phù hợp với tất cả các câu lệnh SELECT. EXCEPT chỉ trả lại các dòng từ câu lệnh SELECT đầu tiên mà không phù hợp với các hàng từ câu lệnh SELECT thứ 2. Hãy xem một ví dụ sử dụng EXCEPT nơi chúng ta trả lại thông tin liên hệ của khách hàng ngoại trừ trường hợp khách hàng đã nhận và trả lời một email trong tuần trước. /*query to get customer info where the customer has not been contacted and responded in the past week */SELECT c.lastName, c.firstName, c.email FROM customers c EXCEPT SELECT e.lastName, e.firstName, e.email FROM email_log e WHERE e.email_date > current_date - interval '7 days' AND e.email_action_date > current_date - interval '7 days' AND email_action_type = 'response'; Trong khi MySQL, MariaDB, và Firebird đều hỗ trợ UNION, không cơ sở dữ liệu nào hỗ trợ INTERSECT hoặc EXCEPT. Tuy nhiên, mặc dù sử dụng các join trong truy vấn và các điều kiện EXISTS, các kết quả tương tự có thể thu được từ PostgreSQL. Mặc dù điều này đòi hỏi các truy vấn phức tạp hơn. Window functions Window functions thực hiện các hàm tổng hợp trên một vài dòng của tập kết quả (cung cấp một "window" trong tập con), có thể cực kỳ hữu dụng. Chúng chủ yếu cho phép bạn lặp qua các dòng trong phân vùng liên quan tới dòng hiện tại để thực hiện các hàm. Các hàm phổ biến gồm có ROW_NUMBER(), RANK(), DENSE_RANK() và PERCENT_RANK(). Từ khóa OVER, không bắt buộc sử dụng với PARTITION BY và ORDER BY, chỉ ra rằng window function đang được sử dụng. Một ví dụ trong phần dưới đây, chúng tôi sử dụng một Window function với ROW_NUMBER() OVER... để xác định giá trị trung bình trong một loạt các giá trị số. Chú ý rằng mệnh đề WINDOW không bắt buộc trong các truy vấn với Window functions, nhưng nó cho phép bạn tạo ra tên các window để giúp giữ cho mọi thứ đơn giản. Firebird, MySQL và MariaDB hiện tại không hỗ trợ window functions, mặc dù window functions đã được tuyên bố cách đây vài năm trong kế hoạch cho Firebird. Lateral subqueries Từ khóa LATERAL có thể áp dụng cho các truy vấn con trong mệnh đề FROM để cho phép bạn tham chiếu chéo giữa truy vấn con và các bảng khác hoặc các bảng ảo đã được tạo trước đó. Các truy vấn có thể viết đơn giản hơn theo cách này. Ngoài ra, cách nó hoạt động là mỗi hàng được đánh giá đối với các bảng tham chiếu chéo, trong đó có thể cải thiện tốc độ trong quá trình xử lý truy vấn. Đây là một ví dụ nơi chúng ta muốn một danh sánh các sinh viên và để biết liệu họ đã đọc chủ đề công nghệ theo định hướng thời gian gần đây hay chưa: -- query using a lateral subquerySELECT s.firstName, s.LastName, x.topic_name FROM students s JOIN content_log c ON c.student_id = s.id LEFT OUTER JOIN LATERAL ( SELECT t.topic_name FROM content_topics t WHERE t.parent_topic_name = 'Technology' AND t.id = c.topic_id AND c.date > current_date - interval '30 days') x ON true; MySQL, Firebird và MariaDB hiện tại không hỗ trợ lateral subqueries. Nhưng có một vài cách giải quyết có thể thực hiện được, nhưng các truy vấn sẽ phức tạp hơn. Các hàm và hơn nữa PostgreSQL cung cấp các hàm và các thao tác mạnh mẽ được xây dựng sẵn bao gồm hỗ trợ các kiểu dữ liệu chuyên biệt chúng ta đã xem trong phần một của loạt bài viết này, nhưng nó cũng cho phép bạn tạo ra các hàm (gồm aggregates) và các thao tác của riêng bạn cũng như tùy chỉnh stored procedures và triggers. Chúng ta không có khả năng đi vào chi tiết tất cả ở đây bởi vì có rất nhiều, nhưng hãy xem một vài ví dụ cho các hàm. PostgreSQL hỗ trợ 4 loại hàm người dùng tự định nghĩa: ngôn ngữ truy vấn, ngôn ngữ thủ tục, ngôn ngữ C và nội bộ. Mỗi loại có thể lấy và trả lại đồng thời dựa trên các kiểu hỗn hợp. Chú ý trong PostgreSQL lệnh CREATE FUNCTION được sử dụng để tạo stored procedures cũng như các hàm. Hãy xem một ví dụ tạo một hàm trả lại một kiểu dữ liệu hỗn hợp: -- create a new composite type called "datetext"CREATE TYPE datetext AS ( date date, date_as_text text);/*create a function that takes the date then returns the date and the datetext */CREATE FUNCTION show_date_as_text(date) RETURNS datetext -- this is our composite type AS $$ SELECT $1, to_char($1, 'FMMonth FMDD, YYYY') $$LANGUAGE SQL;-- query using the functionSELECT show_date_as_text('2015-01-01');-- Returns: (2015-01-01,"January 1, 2015") Dưới đây là một chức năng tùy chỉnh thiết thực cho việc tìm kiếm giá trị trung bình trong một loạt dữ liệu số: -- create a function that finds the median in a numeric data seriesCREATE FUNCTION median(numeric[]) RETURNS numeric AS $$ SELECT AVG(x.result) FROM ( SELECT result, ROW_NUMBER() OVER (ORDER BY val) as ra, ROW_NUMBER() OVER (ORDER BY val DESC) as rd FROM unnest($1) result -- notice the use of array "unnest" ) AS x WHERE x.ra BETWEEN x.rd - 1 AND x.rd + 1;$$LANGUAGE SQL;-- query using the functionSELECT median(ARRAY[1,2,3,4,5,6,7]);-- Returns: 4 Trong các cơ sở dữ liệu SQL mã nguồn mở chúng ta đang so sánh cũng cho phép bạn tạo các hàm, stored procedures và triggers của riêng mình, chúng không có nhiều kiểu dữ liệu và các tùy chọn tùy chỉnh như PostgreSQL cung cấp. Ngoài ra, trong PostgreSQL bạn có thể tạo ra các thao tác của riêng mình. Các cơ sở dữ liệu so sánh khác không hỗ trợ các thao tác người dùng tự định nghĩa. Các khả năng tùy chỉnh của PostgreSQL là không có đối thủ khi so sánh với MySQL, MariaDB và Firebird. Các mở rộng ngôn ngữ PostgreSQL đi kèm với một loạt các phần mở rộng ngôn ngữ, một vài phần của bản phân phối và nhiều cái có sẵn của bên thứ 3. Có nhiều cách hơn nữa (More on the way) PostgreSQL chỉ mới công bố việc phát hành bản Beta 1 của phiên bản 9.5 cuối tuần trước. Chúng tôi đã làm việc trên nó, học tất cả các tính năng mới vì thế chúng tôi có thể sử dụng phiên bản 9.5 ngay sau khi nó ổn định. Trong một vài tháng qua chúng tôi đã xem xét một số tính năng mới trong phiên bản 9.5, chẳng hạn như đánh chỉ mục BRIN đã đề cập ở trên. Tổng kết PostgreSQL với các tính năng có sẵn cực kỳ phong phú và có vô số cách để bạn có thể tùy chỉnh hoặc mở rộng cho phù hợp với nhu cầu của bạn. Thêm vào đó là sự trưởng thành và độ tin cậy đã được công nhận và đó là lý do rõ ràng tại sao đây là giải pháp cơ sở dữ liệu xứng đáng cho mọi doanh nghiệp. Mặc dù vậy, nó vẫn có thể truy cập và hiệu quả cho các dự án chỉ mới bắt đầu phát triển. Mặc dù chúng tôi chỉ đề cập một số ít các tính năng làm cho PostgreSQL khác biệt với các giải pháp SQL mã nguồn mở khác - có nhiều hơn (và nhiều cách hơn nữa trong phiên bản 9.5), chúng tôi hy vọng rằng phần 2 của loạt bài viết này đã cung cấp cho bạn một cái nhìn tổng quan về lý do tại sao bạn nên chọn PostgreSQL. Tags: