Na 8 laboratorium zajmiemy się kwestią optymalizacji zapytań w SQL. Tak jak w poprzednich tygodniach pracujemy na bazie employees.
Spróbujemy w praktyce sprawdzić jak działają rady przedstawione na wykładzie.
Skoncentrujemy się na trzech zagadnieniach.
Po pierwsze, dowiemy się jak mierzyć czas zapytań w SQL i sprawdzać strategię wykonania zapytań. Dzięki temu będziemy mogli sprawdzić jak zoptymalizować zapytanie, a także czy nasze wysiłki rzeczywiście wpływają na przyspieszenie wykonania zapytań.
Po drugie, sprawdzimy korzyści z używania indeksów. Ustawimy kilka rodzajów indeksów i sprawdzimy jak bardzo jest to opłacalne.
Po trzecie, zbadamy nieefektywność tabel tymczasowych. Jednym z rozwiązań będzie tworzenie widoków, które poznamy później.
Używamy profilera. To wspólna, dla wielu języków programowania, nazwa dla narzędzi, które pozwalają na analizę wykonywanych komend.
Użyjemy nieaktualnej, ale póki co wspieranej, wersji.
SET profiling=1;
SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
SHOW PROFILES;Komenda EXPLAIN. W linku i w materiałach z wykładu opisane jest jak należy interpretować wynik tej komendy.
EXPLAIN SELECT 2000-YEAR(employees.hire_date), MAX(salary), MIN(salary), MAX(salary)-MIN(salary)
FROM salaries JOIN employees ON salaries.emp_no = employees.emp_no
WHERE to_date=MAKEDATE(9999,1)
GROUP BY YEAR(employees.hire_date);Poniższy fragment pochodzi ze strony http://20bits.com
Learn Your Indices
Often your choice of indices will make or break your database. For those who haven’t progressed this far in their database studies, an index is a sort of hash. If we issue the query SELECT * FROM users WHERE last_name = ‘Goldstein’ and last_name has no index then your DBMS must scan every row of the table and compare it to the string ‘Goldstein.’ An index is usually a B-tree (though there are other options) which speeds up this comparison considerably. You should probably create indices for any field on which you are selecting, grouping, ordering, or joining. Obviously each index requires space proportional to the number of rows in your table, so too many indices winds up taking more memory. You also incur a performance hit on write operations, since every write now requires that the corresponding index be updated. There is a balance point which you can uncover by profiling your code. This varies from system to system and implementation to implementation.
Przykład pochodzi stąd.
ALTER TABLE salaries ADD INDEX ( salary );
SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
SHOW PROFILES;Otrzymaliśmy kilkukrotne przyspieszenie!
Indeks usuwamy poleceniem
DROP INDEX salary ON salaries;Znajdź liczbę wszystkich obecnie wypłacanych pensji. Porównaj wyniki dla przypadku bez indeksu na to_date i z takim indeksem. Zauważ, że czas wykonania zależy od tego jak wybierzesz obecnie wypłacane pensje.
Znajdź liczbę inżynierów (Engineer) pracujących w poszczególnych działach. Jak duży jest zysk z użycia indeksu w tym przypadku?
JOIN i połączenie tabel przez warunek
SELECT COUNT(*) FROM
employees, salaries
WHERE to_date>NOW() AND employees.emp_no = salaries.emp_no AND salary>150000;
SELECT COUNT(*) FROM
employees JOIN (SELECT emp_no FROM salaries WHERE to_date>NOW() AND salary>150000) t ON employees.emp_no = t.emp_no;
SHOW PROFILES;Z bazy ratings: Wybierz wszystkie filmy, które mają średnią liczbę gwiazdek większa niż średnia liczba gwiazdek, jakie otrzymały wszystkie filmy reżysera.
SELECT m1.title, m1.director, AVG(stars) as avgStars
FROM Rating JOIN Movie m1 ON Rating.mID=m1.mID GROUP BY m1.mID
HAVING avgStars> (SELECT AVG(stars) FROM Rating JOIN Movie ON Rating.mID=Movie.mID
WHERE Movie.director=m1.director);Z bazy employees: Znajdź liczbę pracowników, którzy zarabiają więcej niż przeciętne wynagrodzenie na ich stanowisku. Dla uproszczenia nie patrzymy na to czy pracują obecnie. Tylko czy kiedykolwiek zarabiali więcej niż przeciętna
SELECT COUNT(DISTINCT titles.emp_no)
FROM titles JOIN salaries ON titles.emp_no=salaries.emp_no
WHERE salary > (
SELECT AVG(salary)
FROM salaries JOIN titles t2 ON salaries.emp_no=t2.emp_no
WHERE t2.title = titles.title);Niefektywne, dla każdego wiersza wykonujemy złożone podzapytanie. Zamiast tego możemy zrobić JOIN trzech tabel
SELECT COUNT(DISTINCT titles.emp_no)
FROM titles INNER JOIN salaries ON
titles.emp_no=salaries.emp_no INNER JOIN
(SELECT title, AVG(salary) AS avgTitleSalary
FROM salaries JOIN titles t2 ON salaries.emp_no=t2.emp_no
GROUP BY t2.title) AS titleSalaryAvg ON titles.title=titleSalaryAvg.title
WHERE salaries.salary>titleSalaryAvg.avgTitleSalary;