BazyDanych Info

Wydajność baz danych i zapytań SQL

Porady dotyczące wydajności baz danych i zapytań SQL
Informacje zawarte w tej sekcji nie są ściśle związane z konkretnym silnikiem bazodanowym, czyli rady płynące z tego artykułu mogą się odnosić zarówno do komercyjnych baz Microsoft SQL Server, Oracle, Sybase, DB2, jak i darmowych MySQL i PostgreSQL. Porady dedykowane i specyficzne dla danej platformy bazodanowej zostały odpowiednio oznaczone.

Rada 1 – Nazewnictwo
Standaryzacja nazewnictwa obiektów bazodanowych – pozwala uniknąć wielu nieporozumień, poprawia czytelność i znacznie ułatwia poznanie schematu bazy danych nowej osobie. Szerokie użycie komentarzy również pozytywnie powinno wpłynąć na szybkie zrozumienie architektury osobom poznającym bazę danych.

Rada 2 – Bez gwiazdek
Dobrze jest nie używać gwiazdek w zapytaniach SQL. Zamiast “SELECT *” powinno się zawsze wymieniać nazwy kolumn poleceniu SELECT i wypisywać tylko te, które są rzeczywiście niezbędne. Redukuje to ilość operacji I/O na dysku, przez co wpływa na poprawę wydajności. Przykładowo, zamiast polecenia SELECT * FROM klienci powinno się używać polecenia SELECT id_kli, nazwa, status FROM klienci. Czasami wyliczanie nazw kolumn może być uciążliwe i czasochłonne, jednak większość edytorów SQL dostarczanych z systemami baz danych posiada wbudowane funkcje pozwalające zautomatyzować ten proces.
Ta sama zasada odnosi się do funkcji COUNT(*)! W każdym wypadku lepiej będzie użyć COUNT(1)

Rada 3 – Użycie tabel tymczasowych
Użycie tabel tymczasowych nie jest zalecane z uwagi na fakt, że ich użycie zwiększa objętość dyskową i powodują więcej operacji We/Wy. Nawet kosztem czytelności zapytania lepiej jest użyć bardziej skomplikowanych, zagnieżdżonych zapytań SQL lub widoków (views).

Rada 4 – Plan wykonania zapytania
Przed wykonaniem przyjrzyj się planowi wykonania zapytania (EXPLAIN PLAN). Jest to swego rodzaju symulacja która pokazuje w jaki sposób silnik bazy danych będzie wykonywał zapytanie na bazie. Z planu wykonania zapytania można odczytać jakie indeksy będą użyte, które tabele będą w całości skanowane, jaka będzie kolejność wykonania zapytania, itp. Funkcja ta jest w różny sposób obsługiwana przez różnych producentów: - MS SQL Server dostarcza narzędzia Query Analyzer - W Oracle jest to polecenie EXPLAIN PLAN. Użytkownicy frontendu Oraclowego Toad mogą w łatwy sposób analizować plan wykonania każdego zapytania (poprzez naciśnięcie Ctrl+E). - Baza hurtowni danych Teradata ma bardzo mocno rozbudowany mechanizm optymalizacji zapytań. Wystarczy umieścić słowo EXPLAIN przed poleceniem SQL i optimizer wypisze na ekranie dokładny przebieg wykonania zapytania SQL.

Rada 5 – Indeksy
Zawsze szukaj możliwości użycia indeksu jeżeli zapytanie ma zwrócić niewielki procent wierzy przetwarzanych tabel. Pełne skanowanie tabeli może wykonywać się bardzo długo. Zasady i wytyczne pomagające właściwie dobrać indeksy i zarządzać nimi w bazie danych:

  • Przeanalizuj kolumny najczęściej występujące w zapytaniach SQL w poleceniach WHERE, ORDER BY i GROUP BY – pozwoli to wytypować odpowiednich kandydatów do stworzenia indeksu.
  • Staraj się tworzyć wąskie indeksy, tzn takie które obejmują jak najmniejszą liczbę kolumn. Ograniczy to użycie dysku i zwiększy wydajność.
  • Usuń nieużywane indeksy. Należy pamiętać o tym, że każdy indeks zajmuje miejsce na dysku i spowalnia wykonanie instrukcji INSERT, UPDATE i DELETE. W wielu przypadkach należy sobie odpowiedzieć na pytanie czy korzyści płynące z przyspieszenia odczytu danych po stworzeniu indeksu równoważą koszty związane ze spowolnieniem operacji modyfikujących.
  • Zdecydowanie lepiej jest indeksować kolumny numeryczne w odróżnieniu do tekstowych, ponieważ pola numeryczne zajmują mniej pamięci.
  • Nie ma sensu tworzyć indeksu na kolumnie która ma małe zróżnicowanie wartości (np. pola logiczne, płeć, stan cywilny, itp.)
  • Kolejność indeksowania kolumn indeksu kompozytowego (składającego się z wielu kolumn) ma kluczowe znaczenie dla wykorzystania indeksu. W pierwszej kolejności z lewej strony w indeksie powinny występować kolumny o największym zróżnicowaniu wartości (np. indeks złożony z 3 kolumn o definicji: id_klienta - miasto - płeć jest zdecydowanie lepiej zdefiniowany niż: płeć - id_klienta - miasto ).
    Dodatkowych informacji na temat mierzenia zróżnicowania wartości kolumn możemy zaczerpnąć tutaj (źródło w j. angielskim): Index selectivity

    Warto też poeksperymentować i wypróbować innych sposobów na poprawienie wydajności zapytania. Każda baza danych jest inna i wypracowanie optymalnych procesów nieraz wymaga sporego zaangażowania i czasu poświęconego na testy i analizy. Czas ten jednak bardzo szybko się zwraca!

    Hurtownie danych
    Usługi konsultingowe i wdrożenia systemów Business Intelligence i projektowania hurtowni danych.
    www.goliinfo.com