SQL, cái gì có thể đơn giản hơn? Mỗi người trong chúng ta có thể viết một yêu cầu đơn giản - chúng ta gõ chọn, liệt kê các cột được yêu cầu, sau đó từ, tên bảng, một số điều kiện trong Ở đâu và chỉ vậy thôi - dữ liệu hữu ích nằm trong túi của chúng tôi và (gần như) bất kể DBMS nào đang được quản lý tại thời điểm đó (hoặc có thể ). Do đó, việc làm việc với hầu hết mọi nguồn dữ liệu (quan hệ và không phải như vậy) có thể được xem xét theo quan điểm của mã thông thường (với tất cả những gì nó ngụ ý - kiểm soát phiên bản, đánh giá mã, phân tích tĩnh, kiểm tra tự động, v.v.). Và điều này không chỉ áp dụng cho chính dữ liệu, lược đồ và quá trình di chuyển mà còn áp dụng cho toàn bộ thời gian lưu trữ. Trong bài viết này, chúng ta sẽ nói về các công việc hàng ngày và các vấn đề khi làm việc với các cơ sở dữ liệu khác nhau dưới lăng kính “cơ sở dữ liệu dưới dạng mã”.
Và hãy bắt đầu ngay từ . Những trận chiến đầu tiên kiểu "SQL vs ORM" được chú ý trở lại vào năm .
Bản đồ quan hệ giữa các đối tượng
Những người ủng hộ ORM theo truyền thống coi trọng tốc độ và tính dễ phát triển, tính độc lập với DBMS và mã sạch. Đối với nhiều người trong chúng ta, mã để làm việc với cơ sở dữ liệu (và thường là chính cơ sở dữ liệu đó)
nó thường trông giống như thế này...
@Entity
@Table(name = "stock", catalog = "maindb", uniqueConstraints = {
@UniqueConstraint(columnNames = "STOCK_NAME"),
@UniqueConstraint(columnNames = "STOCK_CODE") })
public class Stock implements java.io.Serializable {
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "STOCK_ID", unique = true, nullable = false)
public Integer getStockId() {
return this.stockId;
}
...Mô hình được treo với các chú thích thông minh và ở đâu đó đằng sau hậu trường, một ORM dũng cảm tạo ra và thực thi hàng tấn mã SQL. Nhân tiện, các nhà phát triển đang cố gắng hết sức để tách mình khỏi cơ sở dữ liệu của họ bằng hàng km trừu tượng, điều này cho thấy một số .
Ở phía bên kia của rào cản, những người ủng hộ SQL “thủ công” thuần túy lưu ý khả năng vắt hết nước trái cây ra khỏi DBMS của họ mà không cần thêm lớp và trừu tượng. Kết quả là, các dự án “tập trung vào dữ liệu” xuất hiện, trong đó những người được đào tạo đặc biệt tham gia vào cơ sở dữ liệu (họ cũng là “những người theo chủ nghĩa cơ bản”, họ cũng là “những người theo chủ nghĩa cơ bản”, họ cũng là “những người theo chủ nghĩa cơ bản”, v.v.) và các nhà phát triển chỉ phải “kéo” các khung nhìn và thủ tục lưu sẵn có sẵn mà không đi sâu vào chi tiết.
Điều gì sẽ xảy ra nếu chúng ta có được điều tốt nhất của cả hai thế giới? Làm thế nào điều này được thực hiện trong một công cụ tuyệt vời với cái tên khẳng định cuộc sống . Tôi sẽ đưa ra một vài dòng từ khái niệm chung trong bản dịch miễn phí của mình và bạn có thể làm quen với nó một cách chi tiết hơn .
Clojure là một ngôn ngữ tuyệt vời để tạo DSL, nhưng bản thân SQL là một DSL tuyệt vời và chúng ta không cần một ngôn ngữ khác. Biểu thức chữ S rất tuyệt nhưng chúng không thêm bất cứ điều gì mới vào đây. Kết quả là, chúng tôi nhận được dấu ngoặc vì mục đích của dấu ngoặc. Không đồng ý? Sau đó đợi thời điểm sự trừu tượng hóa cơ sở dữ liệu bắt đầu bị rò rỉ và bạn bắt đầu đấu tranh với hàm (thô-sql)
Vậy tôi nên làm gì? Hãy để SQL như SQL thông thường - một tệp cho mỗi yêu cầu:
-- name: users-by-country
select *
from users
where country_code = :country_code... và sau đó đọc tệp này, biến nó thành hàm Clojure thông thường:
(defqueries "some/where/users_by_country.sql"
{:connection db-spec})
;;; A function with the name `users-by-country` has been created.
;;; Let's use it:
(users-by-country {:country_code "GB"})
;=> ({:name "Kris" :country_code "GB" ...} ...)Bằng cách tuân thủ nguyên tắc "SQL tự nó, Clojure tự nó", bạn sẽ nhận được:
- Không có cú pháp bất ngờ. Cơ sở dữ liệu của bạn (giống như bất kỳ cơ sở dữ liệu nào khác) không tuân thủ 100% với tiêu chuẩn SQL - nhưng điều này không quan trọng đối với Yesql. Bạn sẽ không bao giờ lãng phí thời gian để tìm kiếm các hàm có cú pháp tương đương với SQL. Bạn sẽ không bao giờ phải quay lại một chức năng (raw-sql "some('funky'::SYNTAX)")).
- Hỗ trợ biên tập tốt nhất. Trình soạn thảo của bạn đã có hỗ trợ SQL tuyệt vời. Bằng cách lưu SQL dưới dạng SQL, bạn có thể sử dụng nó một cách đơn giản.
- Khả năng tương thích của nhóm. Các DBA của bạn có thể đọc và ghi SQL mà bạn sử dụng trong dự án Clojure của mình.
- Điều chỉnh hiệu suất dễ dàng hơn. Cần xây dựng một kế hoạch cho một truy vấn có vấn đề? Đây không phải là vấn đề khi truy vấn của bạn là SQL thông thường.
- Sử dụng lại các truy vấn Kéo và thả các tệp SQL tương tự đó vào các dự án khác vì đó chỉ là SQL cũ - chỉ cần chia sẻ nó.
Theo tôi, ý tưởng này rất hay và đồng thời cũng rất đơn giản, nhờ đó mà dự án đã đạt được nhiều thành tựu bằng nhiều ngôn ngữ khác nhau. Và tiếp theo chúng tôi sẽ cố gắng áp dụng triết lý tương tự để tách mã SQL khỏi mọi thứ khác ngoài ORM.
Trình quản lý IDE & DB
Hãy bắt đầu với một công việc đơn giản hàng ngày. Thông thường chúng ta phải tìm kiếm một số đối tượng trong cơ sở dữ liệu, chẳng hạn như tìm một bảng trong lược đồ và nghiên cứu cấu trúc của nó (cột, khóa, chỉ mục, ràng buộc, v.v. được sử dụng). Và từ bất kỳ IDE đồ họa hoặc trình quản lý DB nhỏ nào, trước hết, chúng tôi mong đợi chính xác những khả năng này. Vì vậy, nó nhanh chóng và bạn không phải đợi nửa giờ cho đến khi một cửa sổ có thông tin cần thiết được rút ra (đặc biệt là khi kết nối chậm với cơ sở dữ liệu từ xa), đồng thời, thông tin nhận được luôn mới và phù hợp, và không lưu trữ rác. Hơn nữa, cơ sở dữ liệu càng phức tạp, càng lớn và số lượng cơ sở dữ liệu càng nhiều thì việc thực hiện việc này càng khó khăn hơn.
Nhưng thường thì tôi vứt chuột đi và chỉ viết mã. Giả sử bạn cần tìm ra bảng nào (và thuộc tính nào) có trong lược đồ "HR". Trong hầu hết các DBMS, có thể đạt được kết quả mong muốn bằng truy vấn đơn giản này từ information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Từ cơ sở dữ liệu này sang cơ sở dữ liệu khác, nội dung của các bảng tham chiếu đó khác nhau tùy thuộc vào khả năng của từng DBMS. Và, ví dụ, đối với MySQL, từ cùng một cuốn sách tham khảo, bạn có thể lấy các tham số bảng cụ thể cho DBMS này:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle không biết lược đồ thông tin, nhưng nó có và không có vấn đề lớn nào phát sinh:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse cũng không ngoại lệ:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Một cái gì đó tương tự có thể được thực hiện trong Cassandra (có các họ cột thay vì bảng và không gian khóa thay vì lược đồ):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Đối với hầu hết các cơ sở dữ liệu khác, bạn cũng có thể đưa ra các truy vấn tương tự (ngay cả Mongo cũng có , chứa thông tin về tất cả các bộ sưu tập trong hệ thống).
Tất nhiên, bằng cách này, bạn có thể nhận được thông tin không chỉ về các bảng mà còn về bất kỳ đối tượng nào nói chung. Đôi khi, những người tốt bụng chia sẻ mã như vậy cho các cơ sở dữ liệu khác nhau, chẳng hạn như trong loạt bài viết habra “Các chức năng ghi lại cơ sở dữ liệu PostgreSQL” (, , ). Tất nhiên, giữ cả núi truy vấn này trong đầu và liên tục gõ chúng là một điều thú vị, vì vậy trong IDE/trình soạn thảo yêu thích của tôi, tôi có một bộ đoạn mã được chuẩn bị sẵn cho các truy vấn được sử dụng thường xuyên và tất cả những gì còn lại là gõ tên đối tượng vào mẫu.
Do đó, phương pháp điều hướng và tìm kiếm đối tượng này linh hoạt hơn nhiều, tiết kiệm rất nhiều thời gian và cho phép bạn lấy chính xác thông tin ở dạng cần thiết (ví dụ như được mô tả trong bài viết). ).
Các thao tác với đối tượng
Sau khi chúng ta đã tìm thấy và nghiên cứu những đồ vật cần thiết, đã đến lúc phải làm điều gì đó hữu ích với chúng. Đương nhiên, cũng không cần rời ngón tay khỏi bàn phím.
Không có gì bí mật khi chỉ cần xóa một bảng sẽ trông giống nhau trong hầu hết các cơ sở dữ liệu:
drop table hr.personsNhưng với việc tạo ra bảng, nó trở nên thú vị hơn. Hầu như bất kỳ DBMS nào (bao gồm nhiều NoSQL) đều có thể “tạo bảng” ở dạng này hay dạng khác và phần chính của nó thậm chí sẽ hơi khác một chút (tên, danh sách các cột, kiểu dữ liệu), nhưng các chi tiết khác có thể khác nhau đáng kể và phụ thuộc vào thiết bị nội bộ và khả năng của một DBMS cụ thể. Ví dụ yêu thích của tôi là trong tài liệu của Oracle chỉ có các BNF “trần trụi” cho cú pháp “tạo bảng” . Các DBMS khác có khả năng khiêm tốn hơn, nhưng mỗi loại đều có nhiều tính năng thú vị và độc đáo để tạo bảng (, , , ). Khó có khả năng bất kỳ “thuật sĩ” đồ họa nào từ một IDE khác (đặc biệt là một IDE phổ thông) có thể bao quát đầy đủ tất cả các khả năng này, và ngay cả khi có thể, nó sẽ không phải là một cảnh tượng dành cho người yếu tim. Đồng thời, viết văn bản chính xác, kịp thời tạo bảng sẽ cho phép bạn dễ dàng sử dụng tất cả chúng, giúp việc lưu trữ và truy cập vào dữ liệu của bạn trở nên đáng tin cậy, tối ưu và thoải mái nhất có thể.
Ngoài ra, nhiều DBMS có các loại đối tượng cụ thể riêng mà các DBMS khác không có. Hơn nữa, chúng ta có thể thực hiện các thao tác không chỉ trên các đối tượng cơ sở dữ liệu mà còn trên chính DBMS, chẳng hạn như “hủy” một tiến trình, giải phóng một số vùng bộ nhớ, bật theo dõi, chuyển sang chế độ “chỉ đọc”, v.v.
Bây giờ hãy vẽ một chút
Một trong những nhiệm vụ phổ biến nhất là xây dựng sơ đồ với các đối tượng cơ sở dữ liệu và xem các đối tượng cũng như kết nối giữa chúng trong một bức tranh đẹp. Hầu như bất kỳ IDE đồ họa nào, các tiện ích “dòng lệnh” riêng biệt, các công cụ đồ họa chuyên dụng và trình tạo mô hình đều có thể thực hiện việc này. Họ sẽ vẽ thứ gì đó cho bạn “tốt nhất có thể” và bạn chỉ có thể tác động lên quá trình này một chút với sự trợ giúp của một vài tham số trong tệp cấu hình hoặc các hộp kiểm trong giao diện.
Nhưng vấn đề này có thể được giải quyết đơn giản hơn, linh hoạt hơn và tinh tế hơn nhiều, và tất nhiên là có sự trợ giúp của mã. Để tạo sơ đồ ở bất kỳ mức độ phức tạp nào, chúng tôi có một số ngôn ngữ đánh dấu chuyên dụng (DOT, GraphML, v.v.) và dành cho chúng một loạt ứng dụng (GraphViz, PlantUML, Nàng tiên cá) có thể đọc các hướng dẫn đó và hiển thị chúng ở nhiều định dạng khác nhau . Chà, chúng ta đã biết cách lấy thông tin về các đối tượng và kết nối giữa chúng.
Đây là một ví dụ nhỏ về giao diện của nó khi sử dụng PlantUML và (bên trái là một truy vấn SQL sẽ tạo ra hướng dẫn cần thiết cho PlantUML và bên phải là kết quả):
select '@startuml'||chr(10)||'hide methods'||chr(10)||'hide stereotypes' union all
select distinct ccu.table_name || ' --|> ' ||
tc.table_name as val
from table_constraints as tc
join key_column_usage as kcu
on tc.constraint_name = kcu.constraint_name
join constraint_column_usage as ccu
on ccu.constraint_name = tc.constraint_name
where tc.constraint_type = 'FOREIGN KEY'
and tc.table_name ~ '.*' union all
select '@enduml'Và nếu bạn cố gắng một chút, thì dựa vào bạn có thể nhận được một cái gì đó rất giống với sơ đồ ER thực sự:
Truy vấn SQL phức tạp hơn một chút
-- Шапка
select '@startuml
!define Table(name,desc) class name as "desc" << (T,#FFAAAA) >>
!define primary_key(x) <b>x</b>
!define unique(x) <color:green>x</color>
!define not_null(x) <u>x</u>
hide methods
hide stereotypes'
union all
-- Таблицы
select format('Table(%s, "%s n information about %s") {'||chr(10), table_name, table_name, table_name) ||
(select string_agg(column_name || ' ' || upper(udt_name), chr(10))
from information_schema.columns
where table_schema = 'public'
and table_name = t.table_name) || chr(10) || '}'
from information_schema.tables t
where table_schema = 'public'
union all
-- Связи между таблицами
select distinct ccu.table_name || ' "1" --> "0..N" ' || tc.table_name || format(' : "A %s may haven many %s"', ccu.table_name, tc.table_name)
from information_schema.table_constraints as tc
join information_schema.key_column_usage as kcu on tc.constraint_name = kcu.constraint_name
join information_schema.constraint_column_usage as ccu on ccu.constraint_name = tc.constraint_name
where tc.constraint_type = 'FOREIGN KEY'
and ccu.constraint_schema = 'public'
and tc.table_name ~ '.*'
union all
-- Подвал
select '@enduml'
Nếu bạn nhìn kỹ, nhiều công cụ trực quan cũng sử dụng các truy vấn tương tự. Đúng, những yêu cầu này thường sâu sắc , chưa kể đến bất kỳ sửa đổi nào của chúng.
Số liệu và giám sát
Hãy chuyển sang một chủ đề truyền thống phức tạp - giám sát hiệu suất cơ sở dữ liệu. Tôi nhớ một câu chuyện nhỏ có thật được “một người bạn của tôi” kể cho tôi nghe. Trong một dự án khác, có một DBA quyền lực nhất định sống và rất ít nhà phát triển biết cá nhân anh ta hoặc đã từng gặp anh ta trực tiếp (mặc dù thực tế là, theo tin đồn, anh ta đã làm việc ở đâu đó trong tòa nhà bên cạnh). Vào giờ “X”, khi hệ thống phân tích của một nhà bán lẻ lớn bắt đầu “cảm thấy tồi tệ” một lần nữa, anh ấy đã âm thầm gửi ảnh chụp màn hình đồ thị từ Oracle Enterprise Manager, trên đó anh ấy cẩn thận đánh dấu những vị trí quan trọng bằng điểm đánh dấu màu đỏ cho “mức độ dễ hiểu” ( điều này, nói một cách nhẹ nhàng, không giúp được gì nhiều). Và dựa trên “thẻ ảnh” này, tôi đã phải xử lý. Đồng thời, không ai có quyền truy cập vào Trình quản lý doanh nghiệp quý giá (theo cả hai nghĩa của từ này), bởi vì hệ thống phức tạp và đắt tiền thì đột nhiên “các nhà phát triển vấp phải thứ gì đó và phá vỡ mọi thứ”. Vì vậy, các nhà phát triển đã “theo kinh nghiệm” tìm ra vị trí và nguyên nhân gây ra phanh và tung ra bản vá. Nếu lá thư đe dọa từ DBA không đến nữa trong thời gian sắp tới, thì mọi người sẽ thở phào nhẹ nhõm và quay trở lại nhiệm vụ hiện tại của mình (cho đến khi có Thư mới).
Nhưng quá trình giám sát có thể trông vui vẻ và thân thiện hơn và quan trọng nhất là mọi người đều có thể truy cập và minh bạch. Ít nhất là phần cơ bản của nó, như một phần bổ sung cho các hệ thống giám sát chính (chắc chắn hữu ích và trong nhiều trường hợp không thể thay thế). Mọi DBMS đều được chia sẻ thông tin về trạng thái và hiệu suất hiện tại của nó một cách tự do và hoàn toàn miễn phí. Trong cùng một Oracle DB “máu máu”, hầu hết mọi thông tin về hiệu suất đều có thể được lấy từ các chế độ xem hệ thống, từ các tiến trình và phiên cho đến trạng thái của bộ đệm đệm (ví dụ: , phần "Giám sát"). Postgresql cũng có rất nhiều chế độ xem hệ thống cho , đặc biệt là những thứ không thể thiếu trong cuộc sống hàng ngày của bất kỳ DBA nào, chẳng hạn như , , . MySQL thậm chí còn có một lược đồ riêng cho việc này. . A tích hợp sẵn trong Mongo tổng hợp dữ liệu hiệu suất vào một bộ sưu tập hệ thống .
Do đó, được trang bị một số loại trình thu thập số liệu (Telegraf, Metricbeat, Collectd) có thể thực hiện các truy vấn sql tùy chỉnh, bộ lưu trữ các số liệu này (InfluxDB, Elaticsearch, Timescaledb) và trình hiển thị trực quan (Grafana, Kibana), bạn có thể có được một cách khá dễ dàng. và một hệ thống giám sát linh hoạt sẽ được tích hợp chặt chẽ với các số liệu khác trên toàn hệ thống (ví dụ: thu được từ máy chủ ứng dụng, từ HĐH, v.v.). Ví dụ: điều này được thực hiện trong pgwatch2, sử dụng kết hợp InfluxDB + Grafana và một tập hợp các truy vấn tới chế độ xem hệ thống, cũng có thể được truy cập .
trong tổng số
Và đây chỉ là danh sách gần đúng về những gì có thể thực hiện được với cơ sở dữ liệu của chúng tôi bằng cách sử dụng mã SQL thông thường. Tôi chắc rằng bạn có thể tìm thấy nhiều công dụng hơn nữa, hãy viết trong phần bình luận. Và chúng ta sẽ nói về cách (và quan trọng nhất là tại sao) tự động hóa tất cả những điều này và đưa nó vào quy trình CI/CD của bạn vào lần tới.
Nguồn: www.habr.com