A tesztelés után egy feltételes lekérdezést hajtottak végre egy több mint 4 millió rekordot tartalmazó táblán, és a lekérdezési idő akár 40 másodpercig is elterjedt. Ezért nagyon fontos, hogyan lehet javítani az SQL utasításlekérdezés hatékonyságát. Az alábbiakban több lekérdezési állítás optimalizálási módszer található, amelyek széles körben elterjednek az interneten:
Először is, amikor az adatmennyiség nagy, érdemes elkerülni a teljes táblázat átolvasását, és fontolóra venni, hogy indexeket építs az oszlopokon a hol és sorrendben, ami jelentősen felgyorsíthatja az adatok visszakeresését. Azonban vannak olyan helyzetek, amikor az indexelés nem működik:
1. Próbáld meg elkerülni a != vagy <> operátorok használatát a where klauzadban, különben a motor felhagy az indexek használatával és teljes táblázatszkennelést végez.
2. Próbáld elkerülni a null értékítéletet a where klaulauzula mezőin, különben a motor feladja az indexek használatát, és teljes táblázatszkennelést végez, például:
Válassz azonosítót a T-ből, ahol null
Beállíthatod az alapértelmezett értéket 0-nak a számon, meggyőződhetsz róla, hogy nincs null érték a táblázat num oszlopában, majd így kérdezheted:
Válassz azonosítót a T-ből, ahol num=0
3. Próbáld meg elkerülni, hogy az OR használata a hol záradékban a feltételek összekapcsolásához, különben a motor feladja az index használatát, és teljes táblázatszkennelést végez, például:
Válassz azonosítót a T-ből, ahol num=10 vagy num=20
Ilyen kérdéseket tehetsz:
Válassz azonosítót a T-ből, ahol num=10
Mindenki egyesülése
Válassz azonosítót a T-ből, ahol num=20
4. Az alábbi lekérdezés teljes táblázatszkennelést is eredményez:
Válassz ID-t a T-ből, ahol a név például '%ABC%'
A hatékonyság növelése érdekében fontoljuk meg a teljes szöveges keresést.
5. Az In és a nem be-ben is óvatosan kell használni, különben teljes táblázatszkenneléshez vezet, például:
Válassz ID-t a T-ből, ahol num in(1,2,3)
Folytonos értékekhez, ha tudsz közösen használni, ne használd in:
Válassz azonosítót a T-ből, ahol az Num 1 és 3 között
6. Ha a hol klauzulatban lévő paramétert használod, az a teljes táblázatot is beolvasztja. Mivel az SQL csak helyi változókat oldja meg futásidőben, de az optimalizáló nem halaszthatja a hozzáférési tervek kiválasztását futásidőre; A fordításkor kell kiválasztani. Azonban, ha a fordításkor létrehoznak hozzáférési tervet, a változó értéke továbbra is ismeretlen, ezért nem használható bemeneti elemként az indexválasztáshoz. Az alábbi nyilatkozatokat teljes egészében átvizsgáljuk:
Válassz azonosítót a t-ből, ahol num=@num
Kényszerítheted a lekérdezést, hogy helyette indexet használjon:
Válassz ID-t a t-ből with(index(index(index name)), ahol num=@num
7. Próbáld elkerülni, hogy a where klauzulatban fejezd ki a mezőket, ami miatt a motor abbahagyja az index használatát és teljes táblázatszkennelést végez. Például:
Válassz azonosítót a T-ből, ahol num/2=100
A következőkre kell változtatni:
Válassz azonosítót a T-ből, ahol num=100*2
8. Próbáld meg elkerülni, hogy a where klauzus mezőin végrehajtsd a függvényműveleteket, ami miatt a motor abbahagyja az indexek használatát és teljes táblázatszkennelést végez. Például:
Válassz azonosítót a t-ből, ahol substring(name,1,3)='ABC' – névazonosító, amely abc-vel kezdődik
Válassz azonosítót a T-ből, ahol datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ generált azonosító
A következőkre kell változtatni:
Válassz azonosítót a T-ből, ahol a név például 'abc%'
Válassz azonosítót a T-ből, ahol createdate>='2005-11-30′ és createdate<'2005-12-1′
9. Ne végezzen funkciókat, aritmetikai műveleteket vagy más kifejezési műveleteket a "=" bal oldalán a where klauzulatban, különben a rendszer nem tudja helyesen használni az indexet.
10. Amikor egy indexmezőt feltételként használunk, ha az index összetett index, akkor az index első mezőjét kell feltételként használni, hogy biztosítsák, hogy a rendszer használja az indexet, különben az index nem lesz használatos, és a mezők sorrendje a lehető leginkább összhangban kell lennie az index sorrendével.
11. Ne írj értelmetlen lekérdezéseket, például üres táblastruktúrát generálni:
Válassz col1,col2 #t-be a t-ből, ahol 1=0
Ez a kódtípus nem ad eredményhalmazt, de rendszererőforrásokat fogyaszt, ezért valami ilyesmire kell változtatni:
create table #t(...)
12. Sokszor jó választás a létező helyeket használni ahelyett, hogy a következőkben szerepelne:
Válassz num-ot az a-ból, ahol num be-be (Válaszd a num-ot a b-ből)
Helyettesítse a következő állítást:
Válassz num-ot az a-ból, ahol létezik (válassz 1-et a b-ből, ahol num=a.num)
Fontos dolgok, amikre érdemes figyelni egy index építésekor:
1. Nem minden index érvényes lekérdezésekhez, az SQL a táblázatban lévő adatokon alapul a lekérdezés optimalizálásához; ha az index oszlopban nagy mennyiségű adatduplikáció van, az SQL lekérdezések nem feltétlenül használják az indexet, például egy táblázatban a nemű, férfi, nő mezők majdnem fele szerepelnek, akkor még ha az index nemre épül, nem is játszik szerepet a lekérdezések hatékonyságában.
2. Minél több index nem jobb, az index kétségtelenül javíthatja a megfelelő kiválasztó hatékonyságát, de csökkenti a behelyezés és frissítés hatékonyságát is, mert az index újraépíthető beillesztés vagy frissítés során, így az index építésének módját alaposan meg kell fontolni, a konkrét helyzettől függően. A legjobb, ha egy táblázatban nem lesz több mint 6 index, és ha túl sok van, fontold meg, szükséges-e indexeket építeni néhány ritkán használt oszlopon.
3. Kerüld a klaszterezett indexadat-oszlopok frissítését, mert a klaszterezett indexelt adatoszlopok sorrendje a táblázat rekordainak fizikai tárolási sorrendje, és ha az oszlop értéke megváltozik, az egész táblázat rekordok sorrendjének módosításához vezet, ami jelentős erőforrásokat igényel. Ha az alkalmazásrendszernek gyakran frissítenie kell a klaszterezett indexoszlopokat, fontolóra kell vennie, hogy az indexet klaszterelt indexként kell-e felépíteni.
További megjegyzésekre érdemes figyelni:
1. Próbálj meg numerikus mezőket használni, és ne tervezz olyan mezőket, amelyek csak numerikus adatokat tartalmaznak karakterként, ami csökkenti a lekérdezések és kapcsolatok teljesítményét, és növeli a tárolási költségeket. Ennek oka, hogy a motor egyenként hasonlítja össze a láncszál minden karakterét lekérdezések és csatlakozások feldolgozásakor, míg numerikus típusoknál csak egyszer kell összehasonlítani.
2. Ne használd a select * from t gombot sehol, ne cseréld le a "*" betűt egy adott mezőlistára, és ne küldj vissza olyan mezőket, amelyeket nem használnak.
3. Próbálj meg táblázatváltozókat használni ideiglenes táblák helyett. Ha a táblaváltozó nagy mennyiségű adatot tartalmaz, vegyük figyelembe, hogy az index nagyon korlátozott (csak az elsődleges kulcsindex).
4. Kerüld a rendszertábla erőforrásainak fogyasztásának csökkentése érdekében a gyakori ideiglenes táblák létrehozását és törlését.
5. Az ideiglenes táblák nem használhatatlanok, és megfelelő használatuk bizonyos rutinokat hatékonyabbá tehet, például amikor ismételten hivatkozni kell egy nagy táblára vagy egy adathalmazra egy gyakran használt táblában. Azonban egyszeri eseményeknél a legjobb exportálási táblát használni.
6. Ideiglenes tábla létrehozásakor, ha az egyszerre beillesztett adatmennyiség nagy, akkor a select in (create table) helyett használhatod a select into (create table) használatát, hogy elkerüld a nagy mennyiségű naplót a sebesség javításában; Ha az adatmennyiség nem nagy, a rendszer tábla erőforrásainak megkönnyítése érdekében először kell létrehozni egy táblát, majd beilleszteni.
7. Ha ideiglenes táblát használsz, mindenképp töröld az összes ideiglenes táblát a tárolt eljárás végén, először vágd le a táblát, majd hagyd el a táblát, hogy elkerüld a rendszer tábla hosszú zárolását.
8. Próbáld meg elkerülni a kurzor használatát, mert a kurzor hatékonysága gyenge; ha a kurzor által működtetett adat meghaladja a 10 000 sort, akkor érdemes átírni.
9. Mielőtt a kurzor-alapú vagy ideiglenes táblázat módszert használnád, először a halmaz-alapú megoldásokat kell keresned a probléma megoldására, és a halmaz-alapú módszer általában hatékonyabb.
10. Mint az ideiglenes tábláknál, a kurzor nem használhatatlan. A FAST_FORWARD kurzorok használata kis adathalmazoknál gyakran jobb, mint más soronként feldolgozási módszerek, különösen, ha több táblára kell hivatkozni a szükséges adatok eléréséhez. Azok a rutinok, amelyek az eredményhalmazban "total" szerepelnek, általában gyorsabbak, mint azok, amelyeket a kurzorral hajtanak végre. Ha a fejlesztési idő engedi, mind kurzor-alapú, mind halmaz-alapú módszereket kipróbálhatunk, hogy megnézzük, melyik működik jobban.
11. Állítsd be a SET NOCOUNT beállítást az összes tárolt eljárás és trigger elején, és a végén a SET NOCOUNT beállítsa a NOCOUNT-t. Nem szükséges DONE_IN_PROC üzeneteket küldeni az ügyfélnek minden tárolt eljárás és trigger utasítás végrehajtása után.
12. Próbáld elkerülni, hogy nagy adatokat küldj vissza az ügyfélnek, ha az adatmennyiség túl nagy, érdemes mérlegelni, hogy a megfelelő igény ésszerű-e.
13. Próbáld elkerülni a nagy tranzakciós műveleteket, és javítsd a rendszer egyidejű képességét.
|
Közzétéve 2018. 05. 11. 13:57:06
|
|
|
Közzétéve 2018. 05. 17. 10:12:27