SQL, wat is eenvoudiger? Ieder van ons kan een eenvoudig verzoek schrijven - we typen kiezen, vermeld vervolgens de vereiste kolommen oppompen van, tabelnaam, enkele voorwaarden in WAAR en dat is alles: nuttige gegevens zitten in onze zak, en (bijna) ongeacht welk DBMS er op dat moment onder de motorkap zit (of misschien ). Als gevolg hiervan kan het werken met vrijwel elke gegevensbron (relationeel en niet zo) worden beschouwd vanuit het gezichtspunt van gewone code (met alles wat dit inhoudt: versiebeheer, codebeoordeling, statische analyse, autotests, en dat is alles). En dit geldt niet alleen voor de data zelf, schema’s en migraties, maar in het algemeen voor de gehele levensduur van de opslag. In dit artikel zullen we het hebben over alledaagse taken en problemen bij het werken met verschillende databases onder de lens van "database als code".
En laten we meteen beginnen . De eerste gevechten van het type "SQL vs ORM" werden weer opgemerkt .
Object-relationele mapping
ORM-supporters hechten traditioneel waarde aan snelheid en ontwikkelingsgemak, onafhankelijkheid van het DBMS en schone code. Voor velen van ons is de code voor het werken met de database (en vaak de database zelf)
meestal ziet het er zo uit...
@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;
}
...Het model is voorzien van slimme annotaties, en ergens achter de schermen genereert en voert een dappere ORM tonnen SQL-code uit en voert deze uit. Overigens doen ontwikkelaars hun best om zichzelf te isoleren van hun database met kilometers aan abstracties, wat erop wijst dat er enkele zijn .
Aan de andere kant van de barricades merken aanhangers van pure ‘handgemaakte’ SQL het vermogen op om al het sap uit hun DBMS te persen zonder extra lagen en abstracties. Als gevolg hiervan verschijnen er ‘datacentrische’ projecten, waarbij speciaal opgeleide mensen betrokken zijn bij de database (het zijn ook ‘basicisten’, ze zijn ook ‘basicisten’, ze zijn ook ‘basdeners’, enz.), en de ontwikkelaars hoeft alleen maar de kant-en-klare weergaven en opgeslagen procedures te “halen”, zonder in details te treden.
Wat als we het beste van beide werelden hadden? Hoe dit wordt gedaan in een prachtig hulpmiddel met een levensbevestigende naam . Ik zal in mijn gratis vertaling een paar regels uit het algemene concept geven, zodat u er in meer detail kennis mee kunt maken .
Clojure is een coole taal voor het maken van DSL's, maar SQL zelf is een coole DSL, en we hebben geen andere nodig. S-uitdrukkingen zijn geweldig, maar voegen hier niets nieuws toe. Als resultaat krijgen we haakjes omwille van de haakjes. Niet eens? Wacht dan op het moment dat de abstractie over de database begint te lekken en je gaat vechten met de functie (onbewerkte SQL)
Dus wat moet ik doen? Laten we SQL als gewone SQL laten - één bestand per verzoek:
-- name: users-by-country
select *
from users
where country_code = :country_code... en lees dan dit bestand en verander het in een gewone Clojure-functie:
(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" ...} ...)Door vast te houden aan het principe ‘SQL op zichzelf, Clojure op zichzelf’ krijg je:
- Geen syntactische verrassingen. Uw database (zoals elke andere) is niet 100% compatibel met de SQL-standaard - maar dat maakt voor Yesql niet uit. U zult nooit meer tijd verspillen aan het zoeken naar functies met een SQL-equivalente syntaxis. Je hoeft nooit meer terug te keren naar een functie (raw-sql "sommige('funky'::SYNTAX)")).
- Beste redactieondersteuning. Uw editor beschikt al over uitstekende SQL-ondersteuning. Door SQL op te slaan als SQL kunt u deze eenvoudig gebruiken.
- Teamcompatibiliteit. Uw DBA's kunnen de SQL lezen en schrijven die u in uw Clojure-project gebruikt.
- Gemakkelijker afstemmen van prestaties. Wilt u een plan opstellen voor een problematische zoekopdracht? Dit is geen probleem als uw query gewone SQL is.
- Query's hergebruiken. Sleep diezelfde SQL-bestanden naar andere projecten, omdat het gewoon oude SQL is - deel het gewoon.
Naar mijn mening is het idee erg cool en tegelijkertijd heel eenvoudig, waardoor het project er veel heeft gewonnen in verschillende talen. En we zullen vervolgens proberen een soortgelijke filosofie toe te passen, namelijk het scheiden van SQL-code van al het andere ver buiten de ORM.
IDE & DB-managers
Laten we beginnen met een eenvoudige dagelijkse taak. Vaak moeten we naar bepaalde objecten in de database zoeken, bijvoorbeeld een tabel in het schema vinden en de structuur ervan bestuderen (welke kolommen, sleutels, indexen, beperkingen, enz. Worden gebruikt). En van elke grafische IDE of een kleine DB-manager verwachten we in de eerste plaats precies deze mogelijkheden. Zodat het snel gaat en u geen half uur hoeft te wachten tot een venster met de benodigde informatie wordt getekend (vooral bij een langzame verbinding met een externe database), en tegelijkertijd de ontvangen informatie actueel en relevant is, en niet in de cache opgeslagen rommel. Bovendien, hoe complexer en groter de database en hoe groter het aantal, hoe moeilijker het is om dit te doen.
Maar meestal gooi ik de muis weg en schrijf ik gewoon code. Stel dat u moet uitzoeken welke tabellen (en met welke eigenschappen) zich in het 'HR'-schema bevinden. In de meeste DBMS'en kan het gewenste resultaat worden bereikt met deze eenvoudige query uit information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Van database tot database varieert de inhoud van dergelijke referentietabellen, afhankelijk van de mogelijkheden van elk DBMS. En voor MySQL kunt u bijvoorbeeld uit hetzelfde naslagwerk tabelparameters verkrijgen die specifiek zijn voor dit DBMS:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle kent information_schema niet, maar dat is wel zo , en er doen zich geen grote problemen voor:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse is geen uitzondering:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Iets soortgelijks kan worden gedaan in Cassandra (die kolomfamilies heeft in plaats van tabellen en sleutelruimten in plaats van schema's):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Voor de meeste andere databases kun je ook soortgelijke zoekopdrachten bedenken (zelfs Mongo heeft , die informatie bevat over alle collecties in het systeem).
Op deze manier kunt u natuurlijk niet alleen informatie krijgen over tabellen, maar over elk object in het algemeen. Van tijd tot tijd delen aardige mensen dergelijke code voor verschillende databases, zoals bijvoorbeeld in de reeks habra-artikelen “Functies voor het documenteren van PostgreSQL-databases” (, , ). Natuurlijk is het zo leuk om deze hele berg aan vragen in mijn hoofd te houden en ze voortdurend te typen, dus in mijn favoriete IDE/editor heb ik een vooraf voorbereide set fragmenten voor veelgebruikte vragen, en het enige dat overblijft is het typen van de objectnamen in de sjabloon.
Als gevolg hiervan is deze manier van navigeren en zoeken naar objecten veel flexibeler, bespaart u veel tijd en kunt u precies de informatie krijgen in de vorm waarin deze nu nodig is (zoals bijvoorbeeld beschreven in het bericht ).
Bewerkingen met objecten
Nadat we de benodigde voorwerpen hebben gevonden en bestudeerd, is het tijd om er iets nuttigs mee te doen. Uiteraard ook zonder je vingers van het toetsenbord te halen.
Het is geen geheim dat het simpelweg verwijderen van een tabel er in bijna alle databases hetzelfde uit zal zien:
drop table hr.personsMaar met de creatie van de tafel wordt het interessanter. Bijna elk DBMS (waaronder veel NoSQL) kan in een of andere vorm een tabel maken, en het grootste deel ervan zal zelfs enigszins verschillen (naam, lijst met kolommen, gegevenstypen), maar andere details kunnen dramatisch verschillen en zijn afhankelijk van de intern apparaat en mogelijkheden van een specifiek DBMS. Mijn favoriete voorbeeld is dat er in de Oracle-documentatie alleen ‘naakte’ BNF’s staan voor de ‘create table’-syntaxis . Andere DBMS'en hebben bescheidener mogelijkheden, maar elk van hen heeft ook veel interessante en unieke functies voor het maken van tabellen (, , , ). Het is onwaarschijnlijk dat een grafische "tovenaar" van een andere IDE (vooral een universele) al deze mogelijkheden volledig zal kunnen dekken, en zelfs als dat wel lukt, zal het geen spektakel zijn voor bangeriken. Tegelijkertijd een correcte en tijdige schriftelijke opgave tafel maken zorgt ervoor dat u ze allemaal gemakkelijk kunt gebruiken en de opslag en toegang tot uw gegevens betrouwbaar, optimaal en zo comfortabel mogelijk maakt.
Bovendien hebben veel DBMS'en hun eigen specifieke typen objecten die niet beschikbaar zijn in andere DBMS'en. Bovendien kunnen we niet alleen bewerkingen uitvoeren op databaseobjecten, maar ook op het DBMS zelf, bijvoorbeeld een proces 'killen', wat geheugenruimte vrijmaken, tracering inschakelen, overschakelen naar de 'alleen-lezen'-modus en nog veel meer.
Laten we nu een beetje tekenen
Een van de meest voorkomende taken is het bouwen van een diagram met databaseobjecten en het bekijken van de objecten en verbindingen daartussen in een mooi plaatje. Bijna elke grafische IDE, afzonderlijke “opdrachtregel”-hulpprogramma's, gespecialiseerde grafische tools en modelbouwers kunnen dit doen. Ze zullen “zo goed als ze kunnen” iets voor je tekenen, en je kunt dit proces slechts een beetje beïnvloeden met behulp van een paar parameters in het configuratiebestand of selectievakjes in de interface.
Maar dit probleem kan veel eenvoudiger, flexibeler en eleganter worden opgelost, en uiteraard met behulp van code. Om diagrammen van elke complexiteit te maken, hebben we verschillende gespecialiseerde opmaaktalen (DOT, GraphML enz.), en daarvoor een hele reeks applicaties (GraphViz, PlantUML, Mermaid) die dergelijke instructies kunnen lezen en visualiseren in verschillende formaten . Welnu, we weten al hoe we informatie kunnen krijgen over objecten en de verbindingen daartussen.
Hier is een klein voorbeeld van hoe dit eruit zou kunnen zien, met behulp van PlantUML en (aan de linkerkant staat een SQL-query die de vereiste instructie voor PlantUML genereert, en aan de rechterkant is het resultaat):
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'En als je het een beetje probeert, dan gebaseerd op je kunt iets krijgen dat erg lijkt op een echt ER-diagram:
De SQL-query is iets ingewikkelder
-- Шапка
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'
Als je goed kijkt, gebruiken veel visualisatietools onder de motorkap ook soortgelijke zoekopdrachten. Het is waar dat deze verzoeken meestal diepgaand zijn , om nog maar te zwijgen van eventuele wijzigingen ervan.
Metrieken en monitoring
Laten we verder gaan met een traditioneel complex onderwerp: monitoring van databaseprestaties. Ik herinner me een klein, waargebeurd verhaal dat mij werd verteld door ‘een van mijn vrienden’. Bij een ander project leefde een zekere krachtige DBA, en weinig ontwikkelaars kenden hem persoonlijk, of hadden hem ooit persoonlijk gezien (ondanks het feit dat hij volgens geruchten ergens in het volgende gebouw werkte). Op uur “X”, toen het productiesysteem van een grote retailer opnieuw “slecht” begon te voelen, stuurde hij in stilte screenshots van grafieken van Oracle Enterprise Manager, waarop hij kritische plaatsen zorgvuldig markeerde met een rode markering voor “begrijpelijkheid” ( dit hielp, op zijn zachtst gezegd, niet veel). En op basis van deze “fotokaart” moest ik behandelen. Tegelijkertijd had niemand toegang tot de kostbare (in beide betekenissen van het woord) Enterprise Manager, omdat het systeem is complex en duur, plotseling “stuiteren de ontwikkelaars ergens op en maken alles kapot.” Daarom hebben de ontwikkelaars ‘empirisch’ de locatie en oorzaak van de remmen gevonden en een patch uitgebracht. Als de dreigende brief van de DBA in de nabije toekomst niet opnieuw zou arriveren, zou iedereen opgelucht ademhalen en terugkeren naar hun huidige taken (tot de nieuwe brief).
Maar het monitoringproces kan er leuker en vriendelijker uitzien, en vooral toegankelijk en transparant voor iedereen. In ieder geval het basisonderdeel, als aanvulling op de belangrijkste monitoringsystemen (die zeker nuttig en in veel gevallen onvervangbaar zijn). Elk DBMS is vrij en geheel kosteloos om informatie over de huidige staat en prestaties te delen. In dezelfde ‘bloedige’ Oracle DB kan vrijwel alle informatie over de prestaties worden verkregen uit systeemweergaven, variërend van processen en sessies tot de status van de buffercache (bijvoorbeeld , sectie "Toezicht"). Postgresql heeft ook een hele reeks systeemweergaven voor , in het bijzonder degenen die onmisbaar zijn in het dagelijks leven van elke DBA, zoals , , . MySQL heeft hiervoor zelfs een apart schema. . A In Mongo ingebouwd verzamelt prestatiegegevens in een systeemverzameling .
Dus gewapend met een soort metrische gegevensverzamelaar (Telegraf, Metricbeat, Collectd) die aangepaste SQL-query's kan uitvoeren, een opslag van deze statistieken (InfluxDB, Elasticsearch, Timescaledb) en een visualisatieprogramma (Grafana, Kibana), kunt u een vrij eenvoudig en een flexibel monitoringsysteem dat nauw zal worden geïntegreerd met andere systeembrede meetgegevens (verkregen bijvoorbeeld van de applicatieserver, van het besturingssysteem, enz.). Zoals dit bijvoorbeeld gebeurt in pgwatch2, dat gebruik maakt van de combinatie InfluxDB + Grafana en een reeks queries naar systeemweergaven, die ook toegankelijk zijn .
In totaal
En dit is slechts een geschatte lijst van wat er met onze database kan worden gedaan met behulp van reguliere SQL-code. Ik weet zeker dat je nog veel meer toepassingen kunt vinden, schrijf in de reacties. En we zullen het hebben over hoe (en vooral waarom) u dit allemaal kunt automatiseren en de volgende keer in uw CI/CD-pijplijn kunt opnemen.
Bron: www.habr.com