Эффективность в SQL-запросе

#php #mysql

Вопрос:

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

В основном так выглядит функция:

 $selectedFilters = $this->data;
        $selectSQL =    'SELECT
                        agencies.agency, agencies.website_url, agencies.status, agencies.size, agencies.id, OfficeData.id, ContactData.name, ContactData.surname, ContactData.job_title, ContactData.email, 
                        ContactData.mobile, OfficeCountryData.country
                        FROM agencies
                        LEFT JOIN (SELECT agencies_industries.agency_id, agencies_industries.industry_id FROM agencies_industries) AS IndustryData ON agencies.id = IndustryData.agency_id
                        LEFT JOIN (SELECT agencies_professions.agency_id, agencies_professions.profession_id FROM agencies_professions) AS ProfessionData ON agencies.id = ProfessionData.agency_id
                        LEFT JOIN (SELECT agencies_sectors.agency_id, agencies_sectors.sector_id FROM agencies_sectors) AS SectorData ON agencies.id = SectorData.agency_id
                        LEFT JOIN (SELECT agencies_seniorities.agency_id, agencies_seniorities.seniority_id FROM agencies_seniorities) AS SeniorityData ON agencies.id = SeniorityData.agency_id
                        LEFT JOIN (SELECT agencies_zones.agency_id, agencies_zones.zone_id FROM agencies_zones) AS ZonesData ON agencies.id = ZonesData.agency_id
                        LEFT JOIN (SELECT agencies_countries.agency_id, agencies_countries.country_id FROM agencies_countries) AS CountryData ON agencies.id = CountryData.agency_id
                        LEFT JOIN (SELECT agencies_regions.agency_id, agencies_regions.region_id FROM agencies_regions) AS RegionData ON agencies.id = RegionData.agency_id
                        LEFT JOIN (SELECT agencies_cities.agency_id, agencies_cities.city_id FROM agencies_cities) AS CityData ON agencies.id = CityData.agency_id
                        LEFT JOIN (SELECT agencies_specialisms.agency_id, agencies_specialisms.specialism_id FROM agencies_specialisms) AS SpecialismData ON agencies.id = SpecialismData.agency_id
                        LEFT JOIN (SELECT offices.id, offices.agency_id, offices.hq FROM offices WHERE offices.hq = "1") AS OfficeData ON agencies.id = OfficeData.agency_id
                        LEFT JOIN (SELECT countries.id, countries.country FROM countries) AS OfficeCountryData ON OfficeData.hq = OfficeCountryData.id
                        LEFT JOIN (SELECT contacts.name, contacts.surname, contacts.agency_id, contacts.job_title, contacts.email, contacts.mobile FROM contacts) AS ContactData ON agencies.id = ContactData.agency_id
                        ';
        $whereSQL = ' WHERE 1 = 1 ';
            foreach($selectedFilters as $key)
                foreach($key as $name=>$value){
                    if(is_array($key))
                        foreach($key as $key=>$value){
                            $i = 0;
                            $connector = 'AND';
                            if(is_array($value)){
                                foreach($value as $value){
                                    if($i > 0)
                                        $connector = 'OR';
                                    $i  ;
                                    switch($key){
                                        case 'Profession': $whereSQL .= $connector.' ProfessionData.profession_id = ' . $value . ' ';
                                        break;
                                        case 'Specialism': $whereSQL .= $connector.' SpecialismData.specialism_id = ' . $value . ' ';
                                        break;
                                        case 'SubSpecialism': $whereSQL .= ''; //$whereSQL .= $connector.' SubData.sub_specialism_id = ' . $value . ' ';
                                        break;
                                        case 'Seniority': $whereSQL .= $connector.' SeniorityData.seniority_id = ' . $value . ' ';
                                        break;
                                        case 'Industry': $whereSQL .= $connector.' IndustryData.industry_id = ' . $value . ' ';
                                        break;
                                        case 'Zone': $whereSQL .= $connector.' ZonesData.zone_id = ' . $value . ' ';
                                        break;
                                        case 'Country': $whereSQL .= $connector.' CountryData.country_id = ' . $value . ' ';
                                        break;
                                        case 'Region': $whereSQL .= $connector.' RegionData.region_id = ' . $value . ' ';
                                        break;
                                        case 'City': $whereSQL .= $connector.' CityData.city_id = ' . $value . ' ';
                                        break;
                                        case 'Sector': $whereSQL .= $connector.' SectorData.sector_id = ' . $value . ' ';
                                        break;
                                        case 'status': $whereSQL .= $connector.' agencies.status = "' . $value . '" ';
                                        break;
                                        case 'size': $whereSQL .= $connector.' agencies.size = "' . $value . '" ';
                                        break;
                                    }
                                }
                            }
                            else
                                if(!isBlank($value) amp;amp; $key != 'Search')
                                    $whereSQL .= $connector.' agencies.'.$key.' = "'.$value.'" ';
                        }
                }
        $groupBySQL = 'GROUP BY agencies.id ORDER BY agencies.id ASC';
        $resultAgencies = $this->Agency->query($selectSQL . $whereSQL . $groupBySQL);
        $this->set(compact('resultAgencies'));

Проблема, с которой я столкнулся при поиске, заключается в том, что он работает очень медленно. Это происходит из-за использования слишком большого количества LEFT JOIN команд. Каждый LEFT JOIN выбирает данные из разных таблиц и собирает их все, создавая другую таблицу. Затем отображаются данные.

Мне нужно, чтобы кто-нибудь дал мне подсказку, как это сделать, не используя так много LEFT JOINs .

Приветствия.

Ответ №1:

Попробуйте это:

 $selectSQL =    'SELECT
                        agencies.agency, agencies.website_url, agencies.status, agencies.size, agencies.id, OfficeData.id, ContactData.name, ContactData.surname, ContactData.job_title, ContactData.email, 
                        ContactData.mobile, OfficeCountryData.country
                        FROM agencies
                        LEFT JOIN agencies_industries AS IndustryData ON agencies.id = IndustryData.agency_id
                        LEFT JOIN  agencies_professions AS ProfessionData ON agencies.id = ProfessionData.agency_id
                        LEFT JOIN  agencies_sectors AS SectorData ON agencies.id = SectorData.agency_id
                        LEFT JOIN  agencies_seniorities AS SeniorityData ON agencies.id = SeniorityData.agency_id
                        LEFT JOIN  agencies_zones AS ZonesData ON agencies.id = ZonesData.agency_id
                        LEFT JOIN agencies_countries AS CountryData ON agencies.id = CountryData.agency_id
                        LEFT JOIN  agencies_regions AS RegionData ON agencies.id = RegionData.agency_id
                        LEFT JOIN  agencies_cities AS CityData ON agencies.id = CityData.agency_id
                        LEFT JOIN  agencies_specialism AS SpecialismData ON agencies.id = SpecialismData.agency_id
                        LEFT JOIN  offices  AS OfficeData ON (agencies.id = OfficeData.agency_id AND OfficeData.hq = "1")
                        LEFT JOIN countries AS OfficeCountryData ON OfficeData.hq = OfficeCountryData.id
                        LEFT JOIN  contacts AS ContactData ON agencies.id = ContactData.agency_id
                        ';

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

Редактировать: не зная ваших данных и структуры базы данных, трудно сказать. Есть много вещей, которые влияют на скорость вашего запроса. Сначала перепишите свой запрос так, чтобы таблицы, которые не используются для вашего выбора (например, WHERE ) или поля, которые вы хотите отобразить, не использовались в вашем запросе. Поэтому, если вы не делаете выбора (emtpy $ selectedFilters), вам не нужно включать таблицы отраслей, профессий, секторов, пожилых людей и т. Д.:

 $selectedFilters = $this->data;
        $selectSQL =    'SELECT
                        agencies.agency, agencies.website_url, agencies.status, agencies.size, agencies.id, OfficeData.id, ContactData.name, ContactData.surname, ContactData.job_title, ContactData.email, 
                        ContactData.mobile, OfficeCountryData.country
                        FROM agencies';


        $sql2='                LEFT JOIN  offices  AS OfficeData ON (agencies.id = OfficeData.agency_id AND OfficeData.hq = "1")
                        LEFT JOIN countries AS OfficeCountryData ON OfficeData.hq = OfficeCountryData.id
                        LEFT JOIN  contacts AS ContactData ON agencies.id = ContactData.agency_id
                        ';

        $whereSQL = ' WHERE 1 = 1 ';
            foreach($selectedFilters as $key)
                foreach($key as $name=>$value){
                    if(is_array($key))
                        foreach($key as $key=>$value){
                            $i = 0;
                            $connector = 'AND';
                            if(is_array($value)){
                                foreach($value as $value){
                                    if($i > 0)
                                        $connector = 'OR';
                                    $i  ;
                                    switch($key){
                                        case 'Profession': $whereSQL .= $connector.' ProfessionData.profession_id = ' . $value . ' ';
                                        $sql2.=' LEFT JOIN  agencies_professions AS ProfessionData ON agencies.id = ProfessionData.agency_id ';
                                        break;
                                        case 'Specialism': $whereSQL .= $connector.' SpecialismData.specialism_id = ' . $value . ' ';
                                        $sql2.=' LEFT JOIN  agencies_specialism AS SpecialismData ON agencies.id = SpecialismData.agency_id ';
                                        break;
                                        case 'SubSpecialism': $whereSQL .= ''; //$whereSQL .= $connector.' SubData.sub_specialism_id = ' . $value . ' ';
                                        break;
                                        case 'Seniority': $whereSQL .= $connector.' SeniorityData.seniority_id = ' . $value . ' ';
                                        $sql2.=' LEFT JOIN  agencies_seniorities AS SeniorityData ON agencies.id = SeniorityData.agency_id ';
                                        break;
                                        case 'Industry': $whereSQL .= $connector.' IndustryData.industry_id = ' . $value . ' ';
                                        $sql2=' LEFT JOIN agencies_industries AS IndustryData ON agencies.id = IndustryData.agency_id ';
                                        break;
                                        case 'Zone': $whereSQL .= $connector.' ZonesData.zone_id = ' . $value . ' ';
                                        $sql2.=' LEFT JOIN  agencies_zones AS ZonesData ON agencies.id = ZonesData.agency_id ';
                                        break;
                                        case 'Country': $whereSQL .= $connector.' CountryData.country_id = ' . $value . ' ';
                                        $sql2.=' LEFT JOIN agencies_countries AS CountryData ON agencies.id = CountryData.agency_id ';
                                        break;
                                        case 'Region': $whereSQL .= $connector.' RegionData.region_id = ' . $value . ' ';
                                        $sql2.=' LEFT JOIN  agencies_regions AS RegionData ON agencies.id = RegionData.agency_id ';
                                        break;
                                        case 'City': $whereSQL .= $connector.' CityData.city_id = ' . $value . ' ';
                                        $sql2.=' LEFT JOIN  agencies_cities AS CityData ON agencies.id = CityData.agency_id ';
                                        break;
                                        case 'Sector': $whereSQL .= $connector.' SectorData.sector_id = ' . $value . ' ';
                                        $sql2.='LEFT JOIN  agencies_sectors AS SectorData ON agencies.id = SectorData.agency_id ';
                                        break;
                                        case 'status': $whereSQL .= $connector.' agencies.status = "' . $value . '" ';
                                        break;
                                        case 'size': $whereSQL .= $connector.' agencies.size = "' . $value . '" ';
                                        break;
                                    }
                                }
                            }
                            else
                                if(!isBlank($value) amp;amp; $key != 'Search')
                                    $whereSQL .= $connector.' agencies.'.$key.' = "'.$value.'" ';
                        }
                }
        $groupBySQL = 'GROUP BY agencies.id ORDER BY agencies.id ASC';
        $resultAgencies = $this->Agency->query($selectSQL . $sql2 . $whereSQL . $groupBySQL);
        $this->set(compact('resultAgencies'));

Во-вторых, внимательно посмотрите на свои индексы для каждой таблицы. Убедитесь, что у вас есть индекс для полей, которые вы используете в ОБЪЕДИНЕНИЯХ.

В-третьих, посмотрите на типы полей, которые вы используете. Не используйте INT, если SMALLINT достаточно большой.

В заключение: нормализация — это здорово, но иногда лучше объединить некоторые вещи, даже если это означает, что у вас дублирующиеся данные.

1. Unknown column 'offices.hq' in 'on clause' (и да, hq в таблице office есть имя столбца.

2. измените office на OfficeData (также изменено в приведенном выше коде)

3. Все равно придется ждать результатов около минуты (даже без выбора каких-либо данных).

4. Внес некоторые изменения в мой ответ. Суть в том, что нет ни одного быстрого ответа, чтобы ускорить его.

5. Спасибо за код. Я проверю это через час и оставлю отзыв 🙂 Если это сработает, я соглашусь 🙂

Ответ №2:

Вы должны использовать объединения, а не подзапросы. Вам также могут не всегда понадобиться все эти левые соединения; Я вижу, что ваш оператор WHERE является динамическим, поэтому в рамках вашего оператора switch вы можете решить, к каким дополнительным таблицам вам нужно присоединиться.

Итак, начните с объединения только тех таблиц, из которых вам нужны столбцы;

 $selectSQL = "
    SELECT agencies.agency, 
       agencies.website_url, 
       agencies.status, 
       agencies.size, 
       agencies.id, 
       OfficeData.id, 
       ContactData.name, 
       ContactData.surname, 
       ContactData.job_title, 
       ContactData.email,               
       ContactData.mobile, 
       OfficeCountryData.country
  FROM agencies
  LEFT JOIN offices AS OfficeData          ON ( agencies.id = OfficeData.agency_id )
  LEFT JOIN contacts AS ContactData        ON ( agencies.id = ContactData.agency_id )
  LEFT JOIN countries AS OfficeCountryData ON ( OfficeData.hq = OfficeCountryData.id ) "

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

 $whereSQL = 'WHERE OfficeData.hq = "1"';
$joinSQL ='';

# Loop though your filter options and build up the where and joins
foreach(...){
    switch($key){
        case 'Profession': 
            $whereSQL .= $connector.' ProfessionData.profession_id = ' . $value . ' ';
            $joinSQL .= 'LEFT JOIN agencies_professions AS ProfessionData ON (agencies.id = ProfessionData.agency_id)'
        break;
        ....
    }
}

А затем создайте свой окончательный запрос

 $sql = $selectSQL.' '.$joinSQL.' '.$whereSQL;

1. В этом случае при выборе всех возможных вариантов это не сработало бы.

2. Мне нужно было бы знать больше о том, как работают возможные варианты, чтобы понять это.

3. Это просто поля множественного выбора / выбора.

Ответ №3:

Научитесь использовать синтаксис EXPLAIN в MySQL. Отредактируйте свой вопрос и включите выходные данные вашего плана объяснения.

Среди других проблем, вы выполняете левые соединения во многих таблицах, из которых вы не выбираете. Попробуйте это.

 SELECT agencies.agency, agencies.website_url, agencies.status, agencies.size, 
       agencies.id, 
       OfficeData.id, 
       ContactData.name, ContactData.surname, ContactData.job_title, 
       ContactData.email, ContactData.mobile, OfficeCountryData.country
FROM agencies
LEFT JOIN (SELECT offices.id, offices.agency_id, offices.hq 
           FROM offices 
           WHERE offices.hq = "1") AS OfficeData 
       ON agencies.id = OfficeData.agency_id
LEFT JOIN countries AS OfficeCountryData 
       ON OfficeData.hq = OfficeCountryData.id
LEFT JOIN contacts AS ContactData 
       ON agencies.id = ContactData.agency_id

Как это влияет на производительность?

Вероятно, нет веских причин идентифицировать страны, города и регионы с идентификационными номерами; они несут свою идентификацию в своем имени. Протестируйте замену идентификационного номера на правильные имена. (Идентификационные номера всегда требуют операции объединения для получения полезных данных обратно; естественные ключи часто исключают объединения.)

Вы отметили, что производительность без ненужных соединений хорошая, и что switch утверждение не виновато. Если это так, то вам нужно уменьшить количество соединений. К счастью, сокращение объединений является простым и понятным.

Если вам необходимо «сообщить о юниверсе», вы можете попробовать разделить запрос и отправить несколько запросов асинхронно. Сначала верните и отобразите, скажем, данные агентства и контактные данные, и вы значительно увеличите кажущуюся скорость приложения. И СУБД может обрабатывать второй запрос, пока выполняется рендеринг первого. Часто кажущаяся скорость важнее фактической скорости.

1. Мне нужно объединить все таблицы, потому что конечный результат является динамическим.

2. Производительность без объединений такая, какой она должна быть. Работает очень быстро.

3. Когда я комментирую оператор switch и циклы, для загрузки инструкции SQL требуется столько же времени.

4. @PiotrChabros: Когда вы говорите: «для загрузки инструкции SQL требуется столько же времени», означает ли это, что инструкция SQL выполняется быстро или медленно?

5. @PiotrChabros: обновленный ответ. Смотрите последние два абзаца.

Ответ №4:

Не зная, что вы на самом деле делаете, трудно сказать, можно ли упростить ваш запрос. Предполагая, что вам нужна информация из всех таблиц, а все идентификаторы являются первичными ключами, я бы проанализировал предложение WHERE — правильно ли определены индексы? При больших индексах базы данных это имеет огромное значение и может значительно повысить производительность.

Ответ №5:

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

Ответ №6:

Соединения могут быть медленными, но это не ваша проблема здесь. Одно быстрое решение: удалите эти подзапросы, зачем создавать подзапрос вместо всей таблицы? Это делает все намного медленнее.

Второе: убедитесь, что все ключи, которые вы используете для объединения, помечены как индексы, это может сделать все намного быстрее.