Andrew Vogel profilképe

byAndrew VogelonApril 2, 2019

a múlt héten felhívtak, hogy ugorjak egy Rails projektre, amely némi teljesítményoptimalizálást igényelt. A választott megközelítésünk az volt, hogy tömeges keresési és frissítési folyamatot hajtottunk végre az ActiveRecord land-ből, és mindent áthelyeztünk a Postgres-be. A teljesítmény javulása hatalmas volt a nagy rekordkészleteknél. A csúcs végén láttuk, hogy a kérési idők > 5 percről 2 másodpercre mennek.

korábban már használtam SQL-t és Postgres-t, de nem voltam teljesen tisztában a funkciók teljes készletével. Először, párosítottam egy munkatárssal, aki mélyebb ismeretekkel rendelkezik a Postgres-ről. Néhány napos lekérdezések írása és a tesztcsomag refaktorálása után minden zöld volt,és készen álltam a tesztelésre.

itt van néhány érdekes SQL és PostgreSQL dolog, amit megtanultam az út során.

a CTE

bár nem kizárólag a PostgreSQL, a CTE vagy a Common Table kifejezéseket használja, így újrafelhasználható lekérdezéseket írhat egy adatbázis-rendszerben.

tegyük fel, hogy 2 táblánk van – books és authors.

Könyvek táblázat
id cím szerző_azonosító műfaj
1 digitális erőd 1 thriller
2 A Da Vinci-kód 1 thriller
3 Harry Potter és a Titkok Kamrája 2 Fantázia
szerzők táblázat
id név életkor
1 Dan Brown 54
2 J. K. Rowling 53

írhatunk egy nagyon egyszerű CTE – t, hogy megkapjuk a könyveket a szerzőkkel:

with authors_and_books as ( SELECT b.id as book_id, b.title, a.name as author_name FROM books b JOIN authors a on b.author_id = a.id; ); 

ezután a CTE-ből választva a következőket kapjuk:

SELECT * FROM authors_and_books; 
book_id cím szerző neve
1 digitális erőd Dan Brown
2 A Da Vinci-kód Dan Brown
3 Harry Potter és a Titkok Kamrája J. K. Rowling

ez a példa egyszerű, de láthatja, hogy ez valóban hasznos lehet, ha összetett lekérdezéseket kell újrafelhasználnia.

Temp Tables (PostgreSQL)

egy másik nagyon hasznos adatbázis-funkció Az ideiglenes táblák létrehozásának képessége. Az én esetemben ez nagyon hasznos volt a “feldolgozatlan” adatok előkészítéséhez, amelyeket a kezelőfelületről tettek közzé. Ideiglenes tábla létrehozásához meg kell határoznia annak sémáját, mint a normál create table .. utasítás.

CREATE TEMPORARY TABLE temp_isbns ( title varchar(255) NOT NULL, isbn varchar(255) NOT NULL, author_name varchar(255) NOT NULL ) ON COMMIT DROP; 

a fontos rész itt a ON COMMIT a végén. El kell mondania a Postgres-nek, hogyan kell kezelni az ideiglenes táblát a tranzakciós blokk végén. DROP azt mondja a Postgres-nek, hogy dobja el az ideiglenes táblát a tranzakciós blokk végén. A Postgres dokumentumok többet írnak le az on COMMIT opciókról.

COALESCE

a COALESCE függvény az első nem null értéket adja vissza. Ez a függvény korlátlan számú argumentumot fogad el, és az első nem null argumentumot adja vissza, balról jobbra kiértékelve.

Íme néhány példa arra, hogy ez hogyan nézhet ki:

Coalesce példa 1

select coalesce(null, 1); 

coalesce

Coalesce példa 2

select coalesce(null, null, 1, null); 

coalesce

Coalesce példa 3

select coalesce(2, null, 1); 

coalesce

Upserts

Upserting, vagy updating-and-inserting, egy szuper funkció Postgres. Ez lehetővé teszi a Beszúrások konfliktusmegoldással történő kezelését, ha egy rekord már létezik az adatbázisban.

emlékszel a könyvasztalunkra?

Könyvek táblázat
id cím szerző_azonosító műfaj
1 digitális erőd 1 thriller
2 A Da Vinci-kód 1 thriller
3 Harry Potter és a Titkok Kamrája 2 Fantázia

Upserts példa 1

néhány rekordot beillesztünk a könyvekbe, de ha a könyv létezik, úgy döntünk, hogy nem teszünk semmit:

INSERT INTO books (id, title, author_id, genre) VALUES (3, 'Harry Potter and The Chamber of Secrets', 2, 'fantasy'), (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy') ON CONFLICT DO NOTHING; 

most nézzük meg az asztalunkat:

SELECT * FROM books; 
id cím szerző_azonosító műfaj
1 digitális erőd 1 thriller
2 A Da Vinci-kód 1 thriller
3 Harry Potter és a Titkok Kamrája 2 Fantázia
4 Harry Potter és a Félvér Herceg 2 Fantázia

Upserts Example 2

más viszont, írjunk be rekordokat a könyvekbe és frissítsük a rögzítse a megfelelő azonosítóval. A Postgres dokumentumok szerint a EXCLUDED táblázatot kell használnia a beillesztésre javasolt értékek hivatkozásához.

INSERT INTO books (id, title, author_id, genre) VALUES (3, 'Harry Potter and The Goblet of Fire', 2, 'fantasy'), (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy') ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title; 

Utoljára nézzük az asztalunkat:

SELECT * FROM books; 
id cím szerző_azonosító műfaj
1 digitális erőd 1 thriller
2 A Da Vinci-kód 1 thriller
3 Harry Potter és a Tűz Serlege 2 Fantázia
4 Harry Potter és a Félvér Herceg 2 Fantázia

Köszönjük, hogy követte ezt a blogbejegyzést. Ha van egy adott Postgres funkció, amelyről szeretne hallani, nyugodtan keresse meg a [email protected]

hasznos volt ez a bejegyzés? Ossza meg másokkal.

  • Twitter logó Tweet
  •  Facebook logóMegosztás
  • Linkedin logóhozzászólás

Vélemény, hozzászólás?

Az e-mail-címet nem tesszük közzé.