SQL, was könnte einfacher sein? Jeder von uns kann eine einfache Abfrage schreiben – wir tippen select, listen die benötigten Spalten auf, dann from, der Tabellenname, ein bisschen Bedingungen in where und fertig – nützliche Daten sind in unserer Tasche, und das (fast) unabhängig davon, welche DBMS gerade unter der Haube steckt (oder vielleicht ). Dadurch kann die Arbeit mit praktisch jeder Datenquelle (relational und nicht ganz so relational) aus der Perspektive regulärer Codes betrachtet werden (mit allen Folgewirkungen – Versionskontrolle, Code-Review, statische Analyse, automatisierte Tests und all das). Es geht hier nicht nur um die Daten selbst, Schemata und Migrationen, sondern um das gesamte Leben des Speichers. In diesem Artikel sprechen wir über alltägliche Aufgaben und Herausforderungen beim Arbeiten mit verschiedenen Datenbanken unter dem Fokus 'Datenbank als Code'.
Und wir fangen gleich mit an. Die ersten Kämpfe der Art 'SQL vs ORM' wurden bereits in .
bemerkenswert.
Objekt-relationale Abbildung
Befürworter von ORM schätzen traditionell die Geschwindigkeit und Einfachheit der Entwicklung, die Unabhängigkeit von DBMS und die Klarheit des Codes. Für viele von uns sieht der Code zur Arbeit mit Datenbanken (und oft die Datenbank selbst)
gewöhnlich etwa so aus…@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; } ... .
"SQL-Feindlichkeit"
Hinzu kommt, dass auf der anderen Seite der Barrikaden die Befürworter von reinem "handmade"-SQL die Möglichkeit hervorheben, aus ihrer DBMS alles herauszuholen, ohne zusätzliche Schichten und Abstraktionen. Infolgedessen entstehen "data-centric"-Projekte, in denen speziell ausgebildete Personen sich um die Datenbank kümmern (sie sind auch 'Basiści', 'Baziwiks', 'Basiendik', usw.), und den Entwicklern bleibt nur, die fertigen Views und gespeicherten Prozeduren zu 'ziehen', ohne ins Detail zu gehen. Yesql gemacht wird. Ich werde ein paar Zeilen aus dem Gesamtkonzept in meiner freien Übersetzung anführen, und anhand dieser kann man sich näher damit beschäftigen. .
Clojure ist eine großartige Sprache für die Erstellung von DSLs, aber SQL ist ohnehin schon ein großartiger DSL und wir brauchen keinen weiteren. S-Ausdrücke sind wunderbar, aber hier bringen sie nichts Neues. Am Ende haben wir nur Klammern um der Klammern willen. Nicht einverstanden? Dann warten Sie auf den Moment, wenn die Abstraktion über die Datenbank bricht und Sie mit der Funktion kämpfen müssen. (raw-sql)
Und was tun? Lassen Sie uns SQL einfach als SQL belassen – eine Datei für eine Abfrage:
-- name: users-by-country
select *
from users
where country_code = :country_code… und lesen Sie dann diese Datei, um sie in eine gewöhnliche Clojure-Funktion zu verwandeln:
(defqueries "some/where/users_by_country.sql"
{:connection db-spec})
;;; Eine Funktion mit dem Namen `users-by-country` wurde erstellt.
;;; Lassen Sie sie uns verwenden:
(users-by-country {:country_code "GB"})
;=> ({:name "Kris" :country_code "GB" ...} ...)Indem wir das Prinzip "SQL getrennt, Clojure getrennt" befolgen, erhalten wir:
- Keine syntaktischen Überraschungen. Ihre Datenbank (wie jede andere auch) entspricht nicht zu 100 % dem SQL-Standard – aber das ist für Yesql nicht wichtig. Sie werden niemals Zeit damit verschwenden, nach Funktionen mit einer SQL-äquivalenten Syntax zu suchen. Sie müssen niemals zur Funktion zurückkehren. (raw-sql "some (‘funky’ :: SYNTAX)")).
- Bessere Editorunterstützung. Ihr Editor hat bereits eine großartige Unterstützung für SQL. Wenn Sie SQL als SQL belassen, können Sie es einfach nutzen.
- Teamkompatibilität. Ihre DBA können SQL lesen und schreiben, das Sie in Ihrem Clojure-Projekt verwenden.
- Einfachere Leistungsoptimierung. Muss ein Plan für eine problematische Abfrage erstellt werden? Das ist kein Problem, wenn Ihre Abfrage normales SQL ist.
- Wiederverwendung von Abfragen. Ziehen Sie diese gleichen SQL-Dateien in andere Projekte, denn es ist einfach das gute alte SQL – teilen Sie es einfach.
Ich finde die Idee wirklich cool und gleichzeitig sehr einfach, wodurch das Projekt viel erreicht hat. in einer Vielzahl von Sprachen. Und wir werden versuchen, eine ähnliche Philosophie anzuwenden, um SQL-Code von allem anderen weit über ORM hinaus zu trennen.
IDE & DB-Manager
Lass uns mit einer einfachen alltäglichen Aufgabe beginnen. Oft müssen wir nach bestimmten Objekten in der Datenbank suchen, beispielsweise eine Tabelle im Schema finden und ihre Struktur untersuchen (welche Spalten, Schlüssel, Indizes, Constraints usw. verwendet werden). Und von jeder grafischen IDE oder einem minderwertigen DB-Manager erwarten wir in erster Linie genau diese Fähigkeiten. Damit es schnell geht und wir nicht eine halbe Stunde warten müssen, bis ein Fenster mit den benötigten Informationen erscheint (insbesondere bei einer langsamen Verbindung zu einer entfernten Datenbank), und damit die erhaltenen Informationen frisch und aktuell sind und nicht alte, zwischengespeicherte Daten. Je komplexer und größer die Datenbank ist und je mehr es gibt, desto schwieriger wird es.
Aber normalerweise lege ich die Maus weit weg und schreibe einfach Code. Angenommen, ich möchte wissen, welche Tabellen (und mit welchen Eigenschaften) im Schema "HR" enthalten sind. In den meisten DBMS kann man mit einer solchen einfachen Abfrage aus information_schema das gewünschte Ergebnis erzielen:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Von Datenbank zu Datenbank variiert der Inhalt solcher Nachschlagetabellen je nach den Fähigkeiten jedes DBMS. For MySQL kann man beispielsweise aus diesem Nachschlagewerk spezifische Parameter für diese Datenbank erhalten:
select table_name
, storage_engine -- Verwendeter "Engine" ("MyISAM", "InnoDB" usw.)
, row_format -- Zeilenformat ("Fixed", "Dynamic" usw.)
, ...
from information_schema.tables
where schema = 'HR'Oracle unterstützt kein information_schema, dafür hat es , und es gibt keine größeren Probleme:
select table_name
, pct_free -- Minimum an freiem Platz im Datenblock (%)
, pct_used -- Minimum an verwendetem Platz im Datenblock (%)
, last_analyzed -- Datum der letzten Statistiksammlung
, ...
from all_tables
where owner = 'HR'Auch ClickHouse bildet da keine Ausnahme:
select name
, engine -- Verwendeter "Engine" ("MergeTree", "Dictionary" usw.)
, ...
from system.tables
where database = 'HR'Etwas Ähnliches kann man auch in Cassandra tun (wo es Columnfamilies anstelle von Tabellen und Keyspaces anstelle von Schemata gibt):
select columnfamily_name
, compaction_strategy_class -- Müllabfuhrstrategie
, gc_grace_seconds -- Lebensdauer des Mülls
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Für die meisten anderen DBs lassen sich ebenfalls ähnliche Abfragen formulieren (sogar in Mongo gibt es , die Informationen über alle Sammlungen im System enthält.)
Natürlich kann man auf diese Weise Informationen nicht nur über Tabellen, sondern über jedes beliebige Objekt erhalten. Gelegentlich teilen hilfsbereite Menschen solchen Code für verschiedene Datenbanken, wie zum Beispiel in der Reihe von Habr-Artikeln "Funktionen zur Dokumentation von PostgreSQL-Datenbanken" (, , ). Es versteht sich von selbst, dass es "nicht so toll" ist, all diese Anfragen im Kopf zu behalten und ständig einzugeben, weshalb ich in meiner bevorzugten IDE/Editor im Voraus ein Set von Snippets für häufig verwendete Abfragen vorbereitet habe, und man muss nur die Objektnamen in die Vorlage eintippen.
Das Ergebnis ist, dass diese Art der Navigation und Objektsuche viel flexibler ist, viel Zeit spart und genau die Informationen in der Form liefert, in der sie jetzt benötigt werden (wie zum Beispiel in dem Post beschrieben, ).
Operationen mit Objekten
Nachdem wir die benötigten Objekte gefunden und untersucht haben, ist es an der Zeit, etwas Nützliches damit zu machen. Natürlich, ohne die Finger von der Tastatur zu nehmen.
Es ist kein Geheimnis, dass das einfache Löschen einer Tabelle fast in allen Datenbanken gleich aussieht:
drop table hr.personsDie Erstellung einer Tabelle ist allerdings interessanter. Fast jede DBMS (einschließlich vieler NoSQL) kann in irgendeiner Form "create table", und der Hauptteil wird sich kaum unterscheiden (Name, Spaltenliste, Datentypen), aber die weiteren Details können stark variieren und hängen von der internen Struktur und den Möglichkeiten des jeweiligen DBMS ab. Mein Lieblingsbeispiel — in der Oracle-Dokumentation gibt es nur "nackte" BNF für die Syntax "create table", . Andere DBMS verfügen über bescheidenere Möglichkeiten, aber jede von ihnen hat auch viele interessante und einzigartige Funktionen zur Erstellung von Tabellen (, , , ). Kaum ein grafischer "Wizard" aus einer beliebigen IDE (besonders universell) wird all diese Fähigkeiten vollständig abdecken können, und wenn doch, wird es ein Anblick für die Nervenstarken sein. Gleichzeitig ermöglicht ein richtig und rechtzeitig geschriebener Befehl create table es, all diese Funktionen mühelos zu nutzen und die Speicherung sowie den Zugriff auf Ihre Daten zuverlässig, optimal und maximal komfortabel zu gestalten.
In vielen DBMS gibt es auch spezifische Objekttypen, die in anderen DBMS fehlen. Dabei können wir nicht nur Operationen an den DB-Objekten durchführen, sondern auch am DBMS selbst, zum Beispiel einen Prozess "beenden", einen bestimmten Speicherbereich freigeben, das Logging aktivieren, in den "read only"-Modus wechseln und vieles mehr.
Jetzt lassen Sie uns ein wenig zeichnen.
Eine der häufigsten Aufgaben besteht darin, ein Diagramm mit DB-Objekten zu erstellen, um die Objekte und deren Beziehungen in einer ansprechenden Darstellung zu sehen. Das beherrscht praktisch jede grafische IDE, verschiedene Kommandozeilen-Utilities, spezialisierte grafische Tools und Modellierer. Diese zeichnen etwas "nach ihrer Art", und den Prozess kann man nur durch einige Parameter in der Konfigurationsdatei oder durch Checkboxen im Interface beeinflussen.
Aber dieses Problem lässt sich viel einfacher, flexibler und eleganter lösen, und natürlich mithilfe von Code. Für die Erstellung von Diagrammen beliebiger Komplexität stehen uns mehrere spezialisierte Markup-Sprachen (DOT, GraphML etc.) zur Verfügung, und dazu eine Vielzahl von Anwendungen (GraphViz, PlantUML, Mermaid), die in der Lage sind, solche Anweisungen zu lesen und in den unterschiedlichsten Formaten zu visualisieren. Die Informationen über Objekte und deren Beziehungen haben wir bereits und wissen, wie wir sie erhalten können.
Hier ist ein kleines Beispiel dafür, wie das aussehen könnte, unter Verwendung von PlantUML und (links der SQL-Befehl, der die benötigte Anweisung für PlantUML generiert, und rechts das Ergebnis):
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'Wenn man ein wenig mehr Mühe investiert, kann man auf Basis etwas sehr Ähnliches wie ein echtes ER-Diagramm erhalten:
Die SQL-Abfrage ist ein wenig komplexer.
-- Kopf
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
-- Tabellen
select format('Table(%s, "%s n Informationen über %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
-- Beziehungen zwischen Tabellen
select distinct ccu.table_name || ' "1" --> "0..N" ' || tc.table_name || format(' : "Eine %s kann viele %s haben"', 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
-- Fußzeile
select '@enduml' 
Wenn man genau hinsieht, verwenden viele Visualisierungstools unter der Haube genau solche ähnlichen Abfragen. Diese Abfragen sind jedoch normalerweise tief , ganz zu schweigen von einer möglichen Modifikation.
Metriken und Überwachung
Lassen Sie uns zum traditionell komplexen Thema der Überwachung der DB-Leistung übergehen. Ich erinnere mich an eine kleine wahre Geschichte, die mir "ein Freund" erzählt hat. In einem kürzlichen Projekt lebte ein mächtiger DBA, und nur wenige Entwickler kannten ihn überhaupt persönlich oder hatten ihn jemals gesehen (obwohl man munkelte, dass er irgendwo im benachbarten Gebäude arbeitete). Um "X Uhr", wenn das Produktionssystem eines großen Einzelhändlers mal wieder begann, sich "schlecht zu fühlen", schickte er stillschweigend Screenshots von Diagrammen aus dem Oracle Enterprise Manager, auf denen er kritisch wichtige Stellen mit einem roten Marker hervorhob, um die "Verständlichkeit" zu erhöhen (das half, milde gesagt, kaum). Und man musste nach diesem "Foto" reparieren. Zudem hatte niemand Zugriff auf den wertvollen (in jeder Hinsicht) Enterprise Manager, da das System kompliziert und teuer war; man könnte sich vorstellen, dass "die Entwickler irgendetwas gegen die Wand fahren und alles kaputt machen". Daher fanden die Entwickler empirisch Ort und Ursache der Verzögerungen und veröffentlichten einen Patch. Wenn das gefürchtete Schreiben vom DBA in naher Zukunft nicht erneut eintraf, atmeten alle erleichtert auf und kehrten zu ihren aktuellen Aufgaben zurück (bis zum neuen Schreiben).
Aber der Überwachungsprozess kann viel freundlicher und zugänglicher, vor allem für alle transparent, gestaltet werden. Zumindest der grundlegende Teil, als Ergänzung zu den Hauptüberwachungssystemen (die ohne Zweifel nützlich und in vielen Fällen unersetzlich sind). Jede DBMS ist bereit, frei und ohne Kosten Informationen über ihren aktuellen Zustand und ihre Leistung zu teilen. In der ebenso "blutigen" Oracle DB kann nahezu jede Leistungsinformation aus den Systemansichten abgerufen werden, beginnend von Prozessen und Sitzungen bis hin zum Zustand des Puffercaches (zum Beispiel, , Abschnitt "Überwachung"). In PostgreSQL gibt es ebenfalls eine ganze Reihe von Systemansichten für , darunter unabdingbare wie , , . In MySQL gibt es sogar ein separates Schema dafür . Und in MongoDB aggregiert der eingebaute Leistungsdaten in einer systemweiten Sammlung .
So kann man mit einem beliebigen Metrik-Sammler (Telegraf, Metricbeat, Collectd), der benutzerdefinierte SQL-Abfragen ausführen kann, einem Speicher dieser Metriken (InfluxDB, Elasticsearch, Timescaledb) und einem Visualisierungstool (Grafana, Kibana) ein relativ einfaches und flexibles Überwachungssystem erstellen, das eng mit anderen allgemeinen Systemmetriken (die beispielsweise vom Anwendungsserver, vom Betriebssystem usw. erhalten werden) integriert ist. Wie dies beispielsweise in pgwatch2 der Fall ist, wo eine Kombination aus InfluxDB + Grafana und einer Reihe von Abfragen an systemische Sichtweisen verwendet wird, zu denen auch benutzerdefinierte Abfragen hinzugefügt werden können. .
Insgesamt
Und das ist nur eine grobe Übersicht dessen, was man mit unserer Datenbank durch regulären SQL-Code machen kann. Ich bin mir sicher, dass es noch viele weitere Anwendungen gibt. Schreibt eure Ideen in die Kommentare. Und darüber, wie (und vor allem warum) man das alles automatisieren und in seine CI/CD-Pipeline integrieren kann, werden wir beim nächsten Mal sprechen.
Quelle: habr.com
