SQL，還有什麼可以更簡單的呢？ 我們每個人都可以寫一個簡單的請求 - 我們輸入 選擇，列出所需的列，然後 ，表名，一些條件 哪裡 僅此而已 - 有用的數據就在我們的口袋裡，並且（幾乎）無論當時哪個 DBMS 在幕後（或者可能是 ）。 因此，可以從普通程式碼的角度考慮使用幾乎任何資料來源（關係型資料來源或非關係型資料來源）（以及它所暗示的所有內容 - 版本控制、程式碼審查、靜態分析、自動測試等等）。 這不僅適用於資料本身、模式和遷移，而且通常適用於儲存的整個生命週期。 在本文中，我們將在「資料庫即程式碼」的視角下討論使用各種資料庫的日常任務和問題。
讓我們從 。 「SQL vs ORM」類型的第一場戰鬥早在 .
物件關係映射
ORM 支持者傳統上重視開發速度和易用性、獨立於 DBMS 和乾淨的程式碼。 對我們許多人來說，使用資料庫的程式碼（通常是資料庫本身）
它通常看起來像這樣...
@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;
}
...該模型掛有巧妙的註釋，並且在幕後某個地方，勇敢的 ORM 生成並執行大量 SQL 程式碼。 順便說一句，開發人員正在盡最大努力透過數公里的抽象將自己與資料庫隔離，這表明一些 .
在障礙的另一邊，純「手工」SQL 的擁護者註意到無需額外的層和抽象就可以從 DBMS 中榨取所有汁液的能力。 結果，出現了“以數據為中心”的項目，其中經過專門培訓的人員參與數據庫（他們也是“基礎主義者”，他們也是“基礎主義者”，他們也是“basdeners”等），開發人員只需「拉取」現成的視野和預存流程，無需深入細節。
如果我們兩全其美怎麼辦？ 這是如何在一個具有肯定生命名字的奇妙工具中實現的 。 我會在我的意譯中給出幾行一般概念，你可以更詳細地熟悉它 .
Clojure 是一種很酷的用於創建 DSL 的語言，但 SQL 本身也是一種很酷的 DSL，我們不需要另一種。 S 表達式很棒，但它們沒有添加任何新內容。 結果，我們為了括號而得到括號。 不同意？ 然後等待資料庫的抽象開始洩漏的那一刻，你開始與該函數戰鬥 （原始 SQL）
所以我該怎麼做？ 讓我們將 SQL 保留為常規 SQL - 每個請求一個檔案：
-- name: users-by-country
select *
from users
where country_code = :country_code....然後讀取此文件，將其轉換為常規 Clojure 函數：
(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" ...} ...)遵循「SQL 本身，Clojure 本身」原則，您將得到：
- 沒有語法上的意外。 您的資料庫（與其他資料庫一樣）並不 100% 符合 SQL 標準 - 但這對 Yesql 來說並不重要。 您永遠不會浪費時間尋找具有 SQL 等效語法的函數。 你永遠不必回傳一個函數 (raw-sql "some('funky'::SYNTAX)")).
- 最好的編輯器支援。 您的編輯器已經擁有出色的 SQL 支援。 透過將 SQL 儲存為 SQL，您可以簡單地使用它。
- 團隊相容性。 您的 DBA 可以讀取和寫入您在 Clojure 專案中使用的 SQL。
- 更輕鬆的性能調整。 需要為有問題的查詢制定計劃嗎？ 當您的查詢是常規 SQL 時，這不是問題。
- 重用查詢。 將這些相同的 SQL 檔案拖放到其他項目中，因為它只是普通的舊 SQL - 只需共用它即可。
在我看來，這個想法非常酷，同時又非常簡單，因此該專案獲得了很多好評 多種語言版本。 接下來我們將嘗試應用類似的理念，將 SQL 程式碼與 ORM 以外的其他程式碼分開。
IDE 和資料庫管理器
讓我們從一個簡單的日常任務開始。 通常我們必須在資料庫中搜尋一些對象，例如，在模式中找到一個表並研究它的結構（使用了哪些列、鍵、索引、約束等）。 首先，我們期望從任何圖形 IDE 或小型資料庫管理器中獲得這些能力。 這樣速度很快，您不必等待半個小時才能繪製包含必要資訊的視窗（尤其是與遠端資料庫的連線速度較慢），同時收到的資訊是新鮮且相關的，並且沒有快取垃圾。 而且，資料庫越複雜、越大、數量越多，做到這一點就越困難。
但通常我會丟掉滑鼠，只寫程式碼。 假設您需要找出「HR」架構中包含哪些表（以及哪些屬性）。 在大多數 DBMS 中，可以透過 information_schema 中的這個簡單查詢來實現所需的結果：
select table_name
, ...
from information_schema.tables
where schema = 'HR'從資料庫到資料庫，此類參考表的內容會根據每個 DBMS 的功能而有所不同。 例如，對於 MySQL，您可以從同一本參考書中取得特定於該 DBMS 的表格參數：
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle 不知道 information_schema，但它確實有 ，並且不會出現大問題：
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse 也不例外：
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'類似的事情可以在 Cassandra 中完成（它用列族代替表，用鍵空間代替模式）：
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'對於大多數其他資料庫，您也可以提出類似的查詢（甚至 Mongo 也有 ，其中包含有關係統中所有集合的資訊）。
當然，透過這種方式，您不僅可以獲得有關表的信息，還可以獲得有關任何物件的資訊。 有時，好心人會為不同的資料庫分享這樣的程式碼，例如，在 habra 文章「記錄 PostgreSQL 資料庫的函數」系列中（, , ）。 當然，將這整堆查詢保留在我的腦海中並不斷地輸入它們是一種樂趣，因此在我最喜歡的IDE/編輯器中，我有一組預先準備好的常用查詢片段，剩下的就是輸入物件名稱加入到範本中。
因此，這種導航和搜尋對象的方法更加靈活，節省大量時間，並且允許您以現在需要的形式準確獲取資訊（例如，帖子中描述的） ).
對物件的操作
在我們找到並研究了必要的物件之後，是時候用它們做一些有用的事情了。 當然，手指也無需離開鍵盤。
眾所周知，簡單地刪除表在幾乎所有資料庫中看起來都是一樣的：
drop table hr.persons但隨著表的創建，它變得更加有趣。 幾乎任何DBMS（包括許多NoSQL）都可以以一種或另一種形式“建立表格”，其主要部分甚至會略有不同（名稱、列列表、資料類型），但其他細節可能會有很大差異，取決於特定 DBMS 的內部設備和功能。 我最喜歡的例子是，在 Oracle 文件中，「建立表格」語法只有「裸露」的 BNF 。 其他 DBMS 的功能較為有限，但它們也都具有許多有趣且獨特的用於建立表格的功能（, , , ）。 其他 IDE（尤其是通用 IDE）的任何圖形「嚮導」都不太可能完全涵蓋所有這些功能，即使可以，對於膽小的人來說也不會是一個奇觀。 同時，正確、及時的書面陳述 創建表 將使您能夠輕鬆地使用所有這些功能，使資料的儲存和存取變得可靠、優化且盡可能舒適。
此外，許多 DBMS 都有自己特定類型的對象，這些對像在其他 DBMS 中不可用。 此外，我們不僅可以對資料庫物件執行操作，還可以對DBMS本身執行操作，例如「殺死」進程、釋放一些記憶體區域、啟用追蹤、切換到「只讀」模式等等。
現在讓我們畫一點
最常見的任務之一是使用資料庫物件建立圖表，並在漂亮的圖片中查看物件及其之間的連接。 幾乎任何圖形 IDE、單獨的「命令列」實用程式、專用圖形工具和建模器都可以做到這一點。 他們會「盡其所能」為您繪製一些東西，您只需藉助設定檔中的一些參數或介面中的複選框即可稍微影響此過程。
但這個問題可以更簡單、更靈活、更優雅地解決，當然也要藉助程式碼。 為了創建任意複雜的圖表，我們有幾種專門的標記語言（DOT、GraphML 等），以及一系列分散的應用程式（GraphViz、PlantUML、Mermaid），它們可以讀取此類指令並以各種格式將其可視化。 好吧，我們已經知道如何獲取有關對象及其之間的聯繫的資訊。
下面是一個小例子，使用 PlantUML 和 （左邊是一個 SQL 查詢，它將產生 PlantUML 所需的指令，右邊是結果）：
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'如果你嘗試一點，然後基於 你可以得到與真實 ER 圖非常相似的東西：
SQL查詢稍微複雜一些
-- Шапка
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'
如果您仔細觀察，您會發現許多視覺化工具實際上也使用類似的查詢。 確實，這些要求通常是深刻的 ，更不用說對它們進行任何修改。
指標和監控
讓我們繼續討論傳統上複雜的主題—資料庫效能監控。 我記得「我的一個朋友」曾經跟我講過一個真實的小故事。 在另一個專案中，住著一位強大的 DBA，很少有開發人員認識他，或見過他本人（儘管據傳言，他在隔壁大樓的某個地方工作）。 X點，當一家大型零售商的生產系統再次開始「感覺不好」時，他默默地發來了Oracle企業管理器的圖表截圖，並在圖表上仔細地用紅色標記突出顯示了關鍵的地方，以確保「可理解性」（溫和地說，這並沒有多大幫助）。 基於這張「照片卡」我不得不對待。 同時，沒有人能夠訪問寶貴的（在這個詞的兩種意義上）企業管理器，因為該系統既複雜又昂貴，突然間“開發人員偶然發現了一些東西並破壞了一切。” 因此，開發者「憑經驗」找到了煞車的位置和原因，並發布了補丁。 如果近期DBA的威脅信沒有再到達的話，那麼大家就可以鬆口氣，回到目前的任務中了（直到新的信）。
但監控過程可以看起來更有趣和友好，最重要的是，每個人都可以訪問和透明。 至少是它的基本部分，作為主要監控系統的補充（這當然很有用，並且在許多情況下是不可替代的）。 任何 DBMS 都可以自由且絕對免費地共享有關其當前狀態和性能的資訊。 在同一個「血腥」Oracle DB 中，幾乎所有有關效能的資訊都可以從系統視圖中獲取，範圍從進程和會話到緩衝區高速緩存的狀態（例如， ，「監控」部分）。 Postgresql 還有一大堆系統視圖 ，特別是任何DBA日常生活中都不可缺少的，例如 , , 。 MySQL 甚至為此有一個單獨的模式。 。 A In Mongo 內建 將效能資料聚合到系統集合中 .
因此，配備某種可以執行自訂 SQL 查詢的指標收集器（Telegraf、Metricbeat、Collectd）、這些指標的儲存（InfluxDB、Elasticsearch、Timescaledb）和視覺化工具（Grafana、Kibana），您可以獲得相當簡單的結果靈活的監控系統將與其他系統範圍的指標（例如從應用程式伺服器、作業系統等取得）緊密整合。 例如，這是在 pgwatch2 中完成的，它使用 InfluxDB + Grafana 組合和一組對系統視圖的查詢，也可以存取這些視圖 .
在總
這只是使用常規 SQL 程式碼對我們的資料庫可以完成的操作的大致清單。 我相信您可以找到更多用途，請寫在評論中。 我們將討論如何（最重要的是為什麼）自動化所有這些並將其包含在您的 CI/CD 管道中。
來源： www.habr.com