Több SELECT COUNT egy MySQL lekérdezésben. MySQL lekérdezések optimalizálása Sql több lekérdezés egyben

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 lehet rekordokat kiválasztani.

Amikor te és én egy asztalról válogattunk, minden nagyon egyszerű volt:

SELECT_a_kötelező_mezők neve 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 vesz mintát, akkor valamivel bonyolultabbá válik. Az egyik nehézség a mezőnevek egyeztetése. Például minden táblának van azonosítómezője.

Nézzük ezt a lekérdezé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

Sokan, akik még nem foglalkoztak ilyen lekérdezésekkel, azt gondolják, hogy minden nagyon egyszerű, mert azt gondolják, hogy csak a táblanevek kerültek a mezőnevek elé. Valójában ez elkerüli az azonos mezőnevek közötti ütközéseket. A nehézség azonban nem ebben, hanem egy ilyen SQL lekérdezés algoritmusában rejlik.

A munkaalgoritmus a következő: az első rekord a_1 táblából származik. Ennek a rekordnak az azonosítója a_1 táblából származik. Ekkor 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ónál 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 már 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. Egyszerűen a többtáblás mintavételi algoritmus magyarázatára adták. Most nézzünk meg 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ő, ami a termék tulajdonosának azonosítójáért felelős) és felhasználókkal (van egy id mező). Az összes rekordot egyetlen SQL-lekérdezésben szeretnénk megkapni, és mindegyik a felhasználóról és az egyetlen termékéről tartalmaz információkat. 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 a termékei elfogynak, lépjen tovább a következő felhasználóra. Í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 hasonló lekérdezés 2 SQL lekérdezést vált ki: külön kiválasztani a táblából az árukat és a táblából a felhasználókat. 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éktáblázat ö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ótól származó összes áru összegyűjtésre kerül. A második iteráció során a második felhasználó összes termékét összegyűjti, és így tovább.

Amint látható, a több táblából történő kiválasztásra szolgáló SQL-lekérdezések nem a legegyszerűbbek, de az előnyök óriásiak lehetnek, ezért az ilyen lekérdezések ismerete és használatának lehetősége nagyon kívánatos.

Ebben a rövid cikkben az adatbázisokról, különösen a MySQL-ről, a mintavételezésről és a számlálásról fogunk beszélni. Amikor adatbázisokkal dolgozik, gyakran meg kell számolnia a COUNT() sorok számát egy 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.

Számlálás feltétellel

Kód megtekintése MYSQL

A lekérdezés egy értéket ad vissza a táblázatban a következő feltételnek megfelelő sorok számával: var = 1

Ha több sorszám-értéket szeretne elérni különböző feltételekkel, például több lekérdezést futtathat egyenként

Kód megtekintése MYSQL

De bizonyos esetekben ez a megközelítés nem praktikus és nem optimális. Ezért fontossá válik egy lekérdezés megszervezése több allekérdezéssel annak érdekében, hogy egy lekérdezésben egyszerre több eredményt kapjunk. Például

Kód megtekintése MYSQL

Így ha csak egy lekérdezést hajtunk végre az adatbázisban, akkor több feltételhez tartozó sorok számát tartalmazó eredményt kapunk, amely több számlálóértéket tartalmaz, 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.

A következő példa egy MySQL-lekérdezésben több COUNT-ot tartalmazó lekérdezésre némileg eltérően épül fel, IF(feltétel, érték1, érték2) konstrukciókat, valamint SUM() konstrukciókat használ. Lehetővé teszi, hogy egy lekérdezésben meghatározott kritériumok szerint válasszon ki adatokat, majd összegezze azokat, és ennek eredményeként több értéket jelenítsen meg.

Kód megtekintése MYSQL

A kérésből látható, hogy meglehetősen tömören készült, de a végrehajtás gyorsasága sem volt tetszetős, ennek a kérésnek az eredménye a következő lesz:

Kód megtekintése SZÖVEG

összesen|c1|c2|c3 --------------- 14 |1 |5 |8

Ezután összehasonlító statisztikát adok a 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. Ezenkívül minden alkalommal, amikor a kérés SQL_NO_CACHE-t tartalmazott, az eredmények adatbázis általi gyorsítótárazásának letiltására szolgál.

Végrehajtási sebesség
Három külön kérés: 0,9 mp
Egy lekérdezés segédlekérdezésekkel: 0,95 mp
Egy kérés IF és SUM konstrukcióval: 1,5 mp

Következtetés. Így több lehetőségünk is van a MySQL adatbázis lekérdezésének felépítésére több COUNT() értékkel, az első opció külön lekérdezéssel nem túl kényelmes, de a legjobb sebességet eredményezi. 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 harmadik lakonikus, IF és SUM konstrukciós 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 egy adatbázis működésének optimalizálásakor javaslom az allekérdezéseket tartalmazó lekérdezés második verzióját a COUNT()-al, egyrészt a végrehajtási sebessége közel van a leggyorsabb eredményhez, másrészt egy ilyen szervezés egy lekérdezésben meglehetősen kényelmes. .

Az utolsó órán egy kellemetlenséggel találkoztunk. Amikor tudni akartuk, hogy ki hozta létre a „kerékpárok” témát, egy megfelelő kéré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 megtudtuk a téma szerzőjének azonosítóját, újabb 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 fogjuk végrehajtani:

Vagyis a kulcsszó után AHOL, egy másik kérést írunk a feltételbe. A MySQL először feldolgozza az allekérdezést, 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.

Ennek megszilárdítása érdekében tegyünk még egy kérést, és nézzük meg, milyen üzeneteket hagyott a „kerékpár” téma szerzője a fórumon:

Most bonyolítsuk a feladatot, nézzük meg, mely témákban hagyott üzenetet a „kerékpár” téma szerzője:

Kitaláljuk, hogyan működik.

• A MySQL először a legmélyebb lekérdezést hajtja végre:


• Az eredmény (id_author=2) egy külső kérésnek lesz átadva, amely a következő formában lesz:


• A kapott eredmény (id_topic:4,1) egy külső kérésnek lesz átadva, amely a következő formában lesz:


• És ez adja a végeredményt (topic_name: horgászatról, horgászatról). Azok. a "kerékpárok" téma írója a Szergej által létrehozott "A horgászatról" témában (id=1) és a Sveta által létrehozott "A horgászatról" témában (id=4) hagyott üzenetet.

Ennyit akartam mondani a beágyazott lekérdezésekről. Két pontra azonban érdemes odafigyelni:

• Nem ajánlott háromnál nagyobb beágyazási fokozatú lekérdezéseket létrehozni. Ez megnöveli a végrehajtási időt és megnehezíti a kód megértését.
• A beágyazott lekérdezések adott szintaxisa valószínűleg a leggyakoribb, de nem az egyetlen. Például kérdezés helyett
  
  ír
  
  Azok. bármilyen, a WHERE kulcsszóval használt operátort használhatunk (az utolsó leckében ezeket tanulmányoztuk).

2008. október 9., 23:37 MySQL lekérdezések optimalizálása

• MySQL

A mindennapi munkában meglehetősen hasonló hibákkal találkozik a lekérdezések írásakor.

Ebben a cikkben arra szeretnék példákat mondani, hogyan NE írjunk lekérdezéseket.

• Válassza ki az összes mezőt
  SELECT * FROM táblázat

  Lekérdezések írásakor ne használja az összes 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 akkor is, ha valóban szüksége van a táblázat összes mezőjére, jobb felsorolni őket. Először is javítja a kód olvashatóságát. Ha csillagot használ, nem lehet tudni, 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 van, akkor egy hónap múlva TEXT és BLOB mezők kerülhetnek hozzáadásra, ami lelassítja a kijelölést.

• Kérések ciklusban.
  Világosan meg kell értenie, hogy az SQL egy set-operációs nyelv. Néha azoknak a programozóknak, akik hozzászoktak ahhoz, hogy eljárási nyelveken gondolkodjanak, nehezen tudják átállítani gondolkodásukat a halmazok nyelvére. Ez egyszerűen megtehető egy egyszerű szabály elfogadásával – „soha ne hajtson végre lekérdezéseket ciklusban”. Példák arra, hogyan lehet ezt megtenni:

  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ály). Ne feledkezzünk meg az IN() konstrukcióról. 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 egy lekérdezés összefűzése és végrehajtása:
  INSERT INTO naplók (érték) ÉRTÉKEK (...), (...)

  3. Frissítések
  Néha egy táblázatban több sort is frissítenie kell. 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 rekordná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 kérés 2-3-szor gyorsabb, mint több külön kérés.

• Műveletek végrehajtása indexelt mezőkön
  SELECT user_id FROM felhasználók WHERE blogok_száma * 2 = $érték

  Ez a lekérdezés nem használja az indexet, még akkor sem, ha a blogs_count oszlop indexelve van. Egy 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.

• A sorok lekérése csak a számuk megszámlálásához
  $eredmény = mysql_query("SELECT * FROM table", $link);
  $szám_sorok = mysql_szam_sorok($eredmény);
  Ha ki kell választania egy bizonyos feltételnek megfelelő sorok számát, használja a SELECT COUNT(*) FROM tábla lekérdezést ahelyett, hogy az összes sort jelölné ki a sorok számának megszámlálásához.
• További sorok lekérése
  $eredmény = mysql_query("SELECT * FROM table1", $link);
  while($row = mysql_fetch_assoc($eredmény) && $i< 20) {
  …
  }
  Ha csak n lekérési sorra van szüksége, használja a LIMIT parancsot ahelyett, hogy eldobná az alkalmazásban lévő extra sorokat.
• Az ORDER BY RAND() használata
  SELECT * FROM tábla ORDER BY RAND() LIMIT 1;

  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 lenne két lekérdezés futtatása:

  Ha a táblának van egy auto_increment elsődleges kulcsa, és nincsenek hézagok:
  $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, ha nagyon sok sor van a táblázatban.

• Nagyszámú JOIN használata
  KIVÁLASZTÁS
  v.video_id
  egy név,
  g.műfaj
  TÓL TŐL
  videók AS v
  BAL CSATLAKOZÁS
  link_actors_videos AS la ON la.video_id = v.video_id
  BAL CSATLAKOZÁS
  szereplők AS a ON a.actor_id = la.actor_id
  BAL CSATLAKOZÁS
  link_genre_video AS lg ON lg.video_id = v.video_id
  BAL CSATLAKOZÁS
  műfajok AS g ON g.genre_id = lg.genre_id

  Emlékeztetni kell arra, hogy a táblák egy-a többhez csatlakoztatásakor a kijelölésben lévő sorok száma minden következő JOIN-nel növekszik, ilyen esetekben gyorsabb egy ilyen lekérdezést több egyszerűre felosztani.

• LIMIT használatával
  KIVÁLASZTÁS… A táblázatból LIMIT $kezdet, $oldalonként

  Sokan azt gondolják, hogy egy ilyen lekérdezés oldalanként $ rekordot 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 tábla 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. lehet, hogy egyáltalán nem 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éseket lefedő indexek vagy harmadik féltől származó megoldások (például szfinx) használatával is felgyorsíthatja.

• Nem használja az ON DUPLICATE KEY UPDATE
  $row = query("SELECT * FROM table WHERE id=1");

  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ó helyettesíthető egyetlen lekérdezéssel, 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

Olvas