Sokféle SQL lekérdezésről írtam már, de itt az ideje, hogy bonyolultabb dolgokról is beszéljünk, például egy SQL lekérdezés, amellyel több táblából is lehet rekordokat kiválasztani.
Amikor egy táblázatból válogattunk, minden nagyon egyszerű volt:
SELECT kívánt_mezőnevek FROM tábla_neve WHERE kiválasztási_feltétel
Minden nagyon egyszerű és triviális, de ha egyszerre több táblázatból választunk, minden kicsit bonyolultabbá válik. Az egyik nehézség a mezőnevek egybeesése. Például minden táblának van azonosítómezője.
Vegyünk egy ilyen kérést:
SELECT * FROM tábla_1, tábla_2 WHERE tábla_1.azonosítója > tábla_2.felhasználói_azonosítója
Sokak számára, akik még nem foglalkoztak ilyen lekérdezésekkel, úgy tűnik, hogy minden nagyon egyszerű, ha azt gondolják, hogy csak a táblák nevei kerültek ide a mezők neve elé. Valójában ez elkerüli az azonos mezőnevek közötti ütközéseket. A bonyolultság azonban nem ebben, hanem egy ilyen SQL lekérdezés algoritmusában van.
A munka algoritmusa a következő: az első rekord a_1 táblából származik. Ennek a bejegyzésnek az azonosítója a_1 táblából származik. Továbbá a táblázat_2 teljesen kinéz. És minden olyan rekord hozzáadásra kerül, ahol a user_id mező értéke kisebb, mint a kiválasztott rekord azonosítója a_1 táblázatban. Így az első iteráció után 0-tól végtelen számú rekord lehet. A következő iteráció során a tábla_1 következő rekordját veszik fel. A teljes tábla_2. tábla ellenőrzése újra megtörténik, és a táblázat_1.azonosító > táblázat_2.felhasználói_azonosító kijelölési feltétele ismét aktiválódik. Minden rekord, amely megfelel ennek a feltételnek, hozzáadódik az eredményhez. A kimenet hatalmas számú rekord lehet, sokszorosa mindkét tábla teljes méretének.
Ha az első alkalom után megérted, hogyan működik, akkor nagyszerű, de ha nem, akkor olvass, amíg teljesen meg nem érted. Ha ezt megérted, akkor könnyebb lesz.
Az előző SQL-lekérdezést, mint olyant, ritkán használják. Csak a többtáblás kiválasztási algoritmus magyarázatára adták. És most elemezzünk egy zökkenőmentesebb SQL-lekérdezést. Tegyük fel, hogy két táblánk van: termékekkel (van egy tulajdonos_id mező, amely a terméktulajdonos azonosítójáért felelős) és felhasználókkal (van egy id mező). Az összes rekordot egyetlen SQL-lekérdezéssel szeretnénk megszerezni, és úgy, hogy mindegyik információt tartalmazzon a felhasználóról és az egyetlen termékéről. A következő bejegyzés ugyanarról a felhasználóról és a következő termékéről tartalmazott információkat. Ha ennek a felhasználónak az árui elfogynak, lépjen a következő felhasználóhoz. Így össze kell kapcsolnunk két táblát, és olyan eredményt kell kapnunk, amelyben minden rekord információt tartalmaz a felhasználóról és az egyik termékéről.
Egy ilyen lekérdezés 2 SQL lekérdezést vált ki: külön kiválasztással a termékeket tartalmazó táblázatból és a felhasználókat tartalmazó táblázatból. Ezenkívül egy ilyen kérés azonnal megfelel a felhasználónak és termékének.
Maga a kérés nagyon egyszerű (ha megértette az előzőt):
SELECT * FROM felhasználók, termékek WHERE user.id = products.owner_id
Az algoritmus itt már egyszerű: az első rekordot a felhasználók táblájából veszik. Ezután a rendszer veszi az azonosítóját, és elemzi a termékek tábla összes rekordját, hozzáadva az eredményhez azokat, amelyek tulajdonosa_azonosítója megegyezik a felhasználók tábla azonosítójával. Így az első iteráció során az első felhasználó összes terméke összegyűjtésre kerül. A második iterációnál a második felhasználó összes áruja összegyűjtésre kerül, és így tovább.
Mint látható, az SQL lekérdezések több táblából történő kiválasztáshoz nem a legegyszerűbbek, de a hasznuk óriási lehet, ezért nagyon kívánatos az ilyen lekérdezések ismerete és használata.
2008. október 9., 23:37 MySQL lekérdezés optimalizálásA mindennapi munkában a lekérdezések írása során meglehetősen hasonló hibákkal kell megküzdenie.
Ebben a cikkben arra szeretnék példákat mondani, hogyan NE írjunk lekérdezéseket.
Lekérdezések írásakor ne jelöljön ki minden mezőt - "*". Csak azokat a mezőket sorolja fel, amelyekre valóban szüksége van. Ez csökkenti a lekért és elküldött adatok mennyiségét. Ne feledkezzünk meg az indexek fedezéséről sem. Még ha valóban szüksége van a táblázat összes mezőjére, a legjobb, ha felsorolja őket. Először is javítja a kód olvashatóságát. Csillag használatakor lehetetlen megtudni, hogy mely mezők vannak a táblázatban anélkül, hogy megnéznénk. Másodszor, idővel változhat a táblázat oszlopainak száma, és ha ma öt INT oszlop, akkor egy hónap múlva TEXT és BLOB mezők kerülhetnek fel, ami lelassítja a kijelölést.
1. Minták
$hírazonosítók = get_list("SELECT news_id A mai_hírekből ");
while($hírazonosító = get_next($news_ids))
$hírek = get_row("SELECT title, body FROM news WHERE news_id = ". $hírazonosító);
A szabály nagyon egyszerű - minél kevesebb kérés, annál jobb (bár ez alól is vannak kivételek, mint minden szabálynál). Ne felejtsük el az IN() konstrukciót. A fenti kód egy lekérdezéssel írható:
SELECT cím, törzs FROM today_news BELSŐ CSATLAKOZÁS Hírek HASZNÁLATA(hír_azonosító)
2. Betétek
$log = parse_log();
while($rekord = next($log))
query("INSERT INTO naplók SET value = ". $log["value"]);!}
Sokkal hatékonyabb egyetlen lekérdezés ragasztása és végrehajtása:
INSERT INTO naplók (érték) ÉRTÉKEK (...), (...)
3. Frissítések
Néha több sort is frissítenie kell ugyanabban a táblázatban. Ha a frissített érték megegyezik, akkor minden egyszerű:
UPDATE news SET title="test" WHERE id IN (1, 2, 3).!}
Ha a módosítandó érték minden bejegyzésnél eltérő, akkor ezt a következő lekérdezéssel lehet megtenni:
UPDATE hírek SET
cím = CASE
WHEN news_id = 1 THEN "aa"
WHEN news_id = 2 THEN "bb" VÉGE
WHERE news_id IN (1, 2)
Teszteink azt mutatják, hogy egy ilyen lekérdezés 2-3-szor gyorsabb, mint több különálló lekérdezés.
Ez a lekérdezés akkor sem használ indexet, ha a blogs_count oszlop indexelve van. Az index használatához nem kell átalakításokat végrehajtani a lekérdezés indexelt mezőjén. Ilyen kérések esetén helyezze át a konverziós függvényeket egy másik részre:
SELECT user_id FROM felhasználók WHERE blogok_száma = $érték / 2;
Hasonló példa:
SELECT user_id FROM felhasználók WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(regisztrált) = DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
akarat.
Ha a táblázat több mint 4-5 ezer sorból áll, akkor az ORDER BY RAND () nagyon lassan fog működni. Sokkal hatékonyabb lesz két lekérdezés futtatása:
Ha a táblázat auto_increment" elsődleges kulcsés nincs passz
$rnd = rand(1, query("SELECT MAX(id) FROM table"));
$row = query("SELECT * FROM table WHERE id = ".$rnd);
Vagy:
$cnt = query("SELECT COUNT(*) FROM FROM");
$row = query("SELECT * FROM table LIMIT ".$cnt.", 1");
ami azonban lassú is lehet a táblázat nagyon sok sorával.
Emlékeztetni kell arra, hogy ha a táblákat egy a többhez kapcsolják, a kijelölésben lévő sorok száma minden következő JOIN-nál nőni fog. hasonló esetek gyorsabb egy ilyen kérést több egyszerűre bontani.
Sokan azt gondolják, hogy egy ilyen lekérdezés $per_page rekordokat ad vissza (általában 10-20), és ezért gyorsan működik. Gyorsan működik az első néhány oldalon. De ha a rekordok száma nagy, és egy SELECT ... FROM table LIMIT 1000000, 1000020 lekérdezést kell végrehajtania, akkor egy ilyen lekérdezés végrehajtásához a MySQL először kiválaszt 1000020 rekordot, eldobja az első milliót, és 20-at ad vissza. egyáltalán ne legyen gyors. Nincsenek triviális módszerek a probléma megoldására. Sokan egyszerűen korlátozzák a rendelkezésre álló oldalak számát ésszerű számra. Az ilyen lekérdezések felgyorsítása fedőindexek vagy harmadik féltől származó megoldások (pl. szfinx) használatával is lehetséges.
Ha($sor)
query("UPDATE table SET oszlop = oszlop + 1 WHERE id=1")
más
query("INSERT INTO table SET oszlop = 1, id=1");
Egy hasonló konstrukció egyetlen kéréssel helyettesíthető, feltéve, hogy az id mezőben van elsődleges vagy egyedi kulcs:
INSERT INTO tábla SET oszlop = 1, id=1 ON DUPLICATE KEY UPDATE oszlop = oszlop + 1
Az utolsó órán egy kellemetlenséggel találkoztunk. Amikor tudni akartuk, hogy ki hozta létre a "kerékpárok" témát, és ennek megfelelő lekérdezést tettünk:
A szerző neve helyett az azonosítóját kaptuk. Ez érthető is, mert lekérdeztünk egy táblát - Témák, és a téma szerzőinek neveit egy másik táblában tároltuk - Users. Ezért, miután megtanultuk a téma szerzőjének azonosítóját, még egy lekérdezést kell végrehajtanunk - a Felhasználók táblába, hogy megtudjuk a nevét:
Az SQL lehetőséget biztosít az ilyen lekérdezések összekapcsolására úgy, hogy az egyiket részlekérdezéssé (beágyazott lekérdezéssé) alakítja. Tehát, hogy megtudjuk, ki hozta létre a "kerékpárok" témát, a következő lekérdezést hajtjuk végre:
Vagyis a kulcsszó után AHOL, még egy lekérdezést írunk a feltételbe. A MySQL először az allekérdezést dolgozza fel, visszaadja az id_author=2 értéket, és ezt az értéket adja át a záradéknak AHOL külső kérés.
Egy lekérdezésben több allekérdezés is lehet, egy ilyen lekérdezés szintaxisa a következő: Vegye figyelembe, hogy az allekérdezések csak egy oszlopot választhatnak ki, amelynek értékeit visszaadják a külső lekérdezésnek. Ha több oszlopot próbál kijelölni, az hibát eredményez.
Tegyünk még egy konszolidációs kérést, nézzük meg, milyen üzeneteket hagyott a fórumon a „bicikli” téma szerzője:
Most bonyolítjuk a feladatot, nézzük meg, mely témákban hagyott üzenetet a „kerékpárok” témakör szerzője:
Lássuk, hogyan működik.
Ez a rövid cikk az adatbázisokra, különösen a MySQL-re, a mintavételre és a számlálásra összpontosít. Adatbázisokkal való munka során gyakran meg kell számolni a COUNT() sorok számát bizonyos feltétellel vagy anélkül, ez rendkívül egyszerűen megtehető a következő lekérdezéssel
Kód megtekintése MySQL
A lekérdezés egy értéket ad vissza a táblázatban lévő sorok számával.
Feltételes számolás
Kód megtekintése MySQL
A lekérdezés egy értéket ad vissza a táblázatban a megadott feltételnek megfelelő sorok számával: var = 1
Ha több sorszám-értéket szeretne kapni különböző feltételekkel, végrehajthat egyenként több lekérdezést, pl.
Kód megtekintése MySQL
De bizonyos esetekben ez a megközelítés nem praktikus és nem optimális. Ezért egy lekérdezés szervezése válik relevánssá, több részlekérdezéssel, hogy egy lekérdezésben egyszerre több eredményt kapjunk. Például
Kód megtekintése MySQL
Így egyetlen lekérdezés végrehajtásával az adatbázisban olyan eredményt kapunk, amely több feltétel szerint számolja a sorok számát, amelyek több számláló értéket tartalmaznak, pl.
Kód megtekintése SZÖVEG
| c1|c2|c3 -------- 1 |5 |8 |
Az allekérdezések használatának hátránya a több különálló lekérdezéshez képest a végrehajtás sebessége és az adatbázis terhelése.
Az egyetlen MySQL-lekérdezésben több COUNT-ot tartalmazó lekérdezés következő példája kissé eltérő szerkezetű, IF(feltétel, érték1, érték2) konstrukciók, valamint SUM() összegzés segítségével. Lehetővé teszi, hogy egyetlen lekérdezésben meghatározott kritériumok szerint válasszon adatokat, majd összegezze azokat, és ennek eredményeként több értéket jelenítsen meg.
Kód megtekintése MySQL
Ahogy a lekérdezésből is látszik, elég tömören van felépítve, de a végrehajtás sebessége sem tetszett, az eredmény adott kérés lesz a következő
Kód megtekintése SZÖVEG
| összesen|c1|c2|c3 --------------- 14 |1 |5 |8 |
Ezután összehasonlító statisztikát adok három lekérdezési lehetőség végrehajtási sebességéről több COUNT () kiválasztásához. A lekérdezés végrehajtási sebességének tesztelésére minden típusból 1000 lekérdezést hajtottak végre, több mint háromezer rekordot tartalmazó táblával. Ugyanakkor minden alkalommal, amikor a lekérdezés SQL_NO_CACHE-t tartalmazott, hogy letiltja az eredmények adatbázis általi gyorsítótárazását.
Végrehajtási sebesség
Három külön kérés: 0,9 mp
Egy kérés segédlekérdezésekkel: 0,95 mp
Egy kérés IF és SUM konstrukcióval: 1,5 mp
Következtetés. Így számos lehetőségünk van lekérdezések létrehozására az adatbázisban MySQL adatok több COUNT() esetén az első opció külön lekérdezéssel nem túl kényelmes, de a sebesség szempontjából a legjobb eredményt adja. A második lehetőség az allekérdezésekkel valamivel kényelmesebb, de a végrehajtási sebessége valamivel alacsonyabb. És végül a lekérdezés IF és SUM konstrukciókat tartalmazó harmadik tömör változata, amely a legkényelmesebbnek tűnik, a legalacsonyabb végrehajtási sebességgel rendelkezik, ami majdnem kétszer alacsonyabb, mint az első két lehetőség. Ezért az adatbázis működésének optimalizálásakor javaslom a második lekérdezési opciót, amely COUNT ( ) allekérdezéseket tartalmaz, egyrészt a végrehajtási sebessége közel áll a leggyorsabb eredményhez, másrészt egy ilyen szervezés egyetlen lekérdezésben meglehetősen kényelmes.
