5.11.4. Эффективное построение запросов

Производительность сервера базы данных при выполнении доступа к информации зависит не только от наличия или отсутствия индексов, буферизации журнала транзакций и т.д., но и от того, какие запросы исполняются. Не секрет, что одно и тоже действие (например, построение списка сотрудников какой-либо фирмы) можно запрограммировать по‑разному. Суммарная скорость исполнения данного действия зависит от того, какими операторами на SQL оно представлено. Рассмотрим пример. Допустим, у нас есть таблицы persons и companies (их структура привевдена выше, в пункте 5.11.3). Требуется построить поименный список сотрудников, работающих в фирме “АО Рога и Копыта”. Первый вариант реализации данного действия выглядит так:

SELECT lname, fname FROM persons, companies WHERE persons.company = companies.company_id AND companies.name = “АО Рога и Копыта”

При исполнении данного запроса при отсутствии индексов сервер базы данных должен будет перебрать все возможные комбинации записей из таблиц persons и companies, и для каждой комбинации проверить, выполняется ли условие из раздела WHERE. Если в таблице persons находится M записей, а в таблице companies - N записей, то всего будет проверено M*N комбинаций.

Другой запрос, функцмионально реализующий тоже самое действие, а именно, формирование списка сотрудников АО Рога и Копыта выглядит следующим образом:

SELECT lname, fname FROM persons WHERE persons.company IN (SELECT company_id FROM companies WHERE name = “АО Рога и Копыта”)

При исполнении этого запроса, сервер вначале просмотрит таблицу companies и найдет одно требуемое значение, затем он просмотрит таблицу persons и сравнит поле company с найденным значением. В итоге, он просмотрит M+N записей. Если значения M и N достаточно велики (порядка сотен или более), то второй запрос будет исполняться много быстрее первого. Естественно, это очень грубая оценка без учета наличия индексов, возможностей сервера по оптимальному исполнению запросов и т.д., но выигрыш в полмиллиона раз раз для таблиц с миллионом записей каждая уже впечатляет.

Приведенный выше пример позволяет сформулировать следующее общее правило: уменьшайте число таблиц, задействованных в одном запросе.

На основании этого правила можно сделать вывод о том, что иногда выгоднее разбить сложный запрос на несколько простых (с использованием временной таблицы), нежели пытаться уместить какое-либо действие в один запрос. Допустим, вас отправляют в командировку в г. Тверь, и вы хотите посетить директоров всех имеющихся в этом городе фирм-партнеров. Если данный запрос представить на SQL, то первый, неоптимизированный вариант запроса мог бы выгшлядеть следующим образом:

SELECT persons.lname, persons.fname, persons.sname, companies.address FROM persons, companies WHERE companies.address MATCHES "*Тверь*" AND companies.companies_id = persons.company AND persons.position = “директор”

Привести данный запрос к виду

SELECT . . . FROM persons WHERE . . .

подобно тому, как мы это делали ранее, невозможно, поскольку поле address из таблицы companies нам нужно в конечном результате. Но число фирм, расположенных в Твери (как и в любом другом городе), по сравнению с общим числом фирм в нашей базе данных невелико. Поэтому следующий набор операторов будет, скорее всего, выполняться быстрее, нежели один первоначальный оператор:

CREATE TEMP TABLE local_companies ( company_id INTEGER, name CHAR(40), address CHAR(40) )

{наполнение временной таблицы списком фирм, распорложенных в Твери} INSERT INTO local_companies(company_id, name, address) SELECT company_id, name, address FROM companies WHERE address MATCHES "*Тверь*"

{получение требуемых данных} SELECT persons.lname, persons.fname, persons.sname, local_companies.address, local_companies.name FROM persons, local_companies WHERE local_companies.companies_id = persons.company AND persons.position = “директор”

DROP TABLE local_companies

Существуют множество рекомендаций, по написанию эффективных запросов, но практически все они основаны или на разделении одного сложного запроса на несколько простых, или на более аккуратном и тщательном написании условия с тем, чтобы оптимизатор смог понять, какие же данные требуются.