Блог

Главная - Вопросы по программированию - Как я могу оптимизировать этот запрос MYSQL

Как я могу оптимизировать этот запрос MYSQL

#mysql #performance

#mysql #Производительность

Вопрос:

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

 SELECT
    Jobs.id AS `Jobs__id`,
    Jobs.name AS `Jobs__name`,
    Jobs.media_id AS `Jobs__media_id`,
    Jobs.job_cate&ory_id AS `Jobs__job_cate&ory_id`,
    Jobs.job_type_id AS `Jobs__job_type_id`,
    Jobs.description AS `Jobs__description`,
    Jobs.detail AS `Jobs__detail`,
    Jobs.business_skill AS `Jobs__business_skill`,
    Jobs.knowled&e AS `Jobs__knowled&e`,
    Jobs.location AS `Jobs__location`,
    Jobs.activity AS `Jobs__activity`,
    Jobs.academic_de&ree_doctor AS `Jobs__academic_de&ree_doctor`,
    Jobs.academic_de&ree_master AS `Jobs__academic_de&ree_master`,
    Jobs.academic_de&ree_professional AS `Jobs__academic_de&ree_professional`,
    Jobs.academic_de&ree_bachelor AS `Jobs__academic_de&ree_bachelor`,
    Jobs.salary_statistic_&roup AS `Jobs__salary_statistic_&roup`,
    Jobs.salary_ran&e_first_year AS `Jobs__salary_ran&e_first_year`,
    Jobs.salary_ran&e_avera&e AS `Jobs__salary_ran&e_avera&e`,
    Jobs.salary_ran&e_remarks AS `Jobs__salary_ran&e_remarks`,
    Jobs.restriction AS `Jobs__restriction`,
    Jobs.estimated_total_workers AS `Jobs__estimated_total_workers`,
    Jobs.remarks AS `Jobs__remarks`,
    Jobs.url AS `Jobs__url`,
    Jobs.seo_description AS `Jobs__seo_description`,
    Jobs.seo_keywords AS `Jobs__seo_keywords`,
    Jobs.sort_order AS `Jobs__sort_order`,
    Jobs.publish_status AS `Jobs__publish_status`,
    Jobs.version AS `Jobs__version`,
    Jobs.created_by AS `Jobs__created_by`,
    Jobs.created AS `Jobs__created`,
    Jobs.modified AS `Jobs__modified`,
    Jobs.deleted AS `Jobs__deleted`,
    JobCate&ories.id AS `JobCate&ories__id`,
    JobCate&ories.name AS `JobCate&ories__name`,
    JobCate&ories.sort_order AS `JobCate&ories__sort_order`,
    JobCate&ories.created_by AS `JobCate&ories__created_by`,
    JobCate&ories.created AS `JobCate&ories__created`,
    JobCate&ories.modified AS `JobCate&ories__modified`,
    JobCate&ories.deleted AS `JobCate&ories__deleted`,
    JobTypes.id AS `JobTypes__id`,
    JobTypes.name AS `JobTypes__name`,
    JobTypes.job_cate&ory_id AS `JobTypes__job_cate&ory_id`,
    JobTypes.sort_order AS `JobTypes__sort_order`,
    JobTypes.created_by AS `JobTypes__created_by`,
    JobTypes.created AS `JobTypes__created`,
    JobTypes.modified AS `JobTypes__modified`,
    JobTypes.deleted AS `JobTypes__deleted`
FROM
    jobs Jobs
    LEFT JOIN jobs_personalities JobsPersonalities ON Jobs.id = (JobsPersonalities.job_id)
    LEFT JOIN personalities Personalities ON (Personalities.id = (JobsPersonalities.personality_id)
            AND(Personalities.deleted) IS NULL)
    LEFT JOIN jobs_practical_skills JobsPracticalSkills ON Jobs.id = (JobsPracticalSkills.job_id)
    LEFT JOIN practical_skills PracticalSkills ON (PracticalSkills.id = (JobsPracticalSkills.practical_skill_id)
            AND(PracticalSkills.deleted) IS NULL)
    LEFT JOIN jobs_basic_abilities JobsBasicAbilities ON Jobs.id = (JobsBasicAbilities.job_id)
    LEFT JOIN basic_abilities BasicAbilities ON (BasicAbilities.id = (JobsBasicAbilities.basic_ability_id)
            AND(BasicAbilities.deleted) IS NULL)
    LEFT JOIN jobs_tools JobsTools ON Jobs.id = (JobsTools.job_id)
    LEFT JOIN affiliates Tools ON (Tools.type = 1
            AND Tools.id = (JobsTools.affiliate_id)
            AND(Tools.deleted) IS NULL)
    LEFT JOIN jobs_career_paths JobsCareerPaths Pa&e 3 / 4 ON Jobs.id = (JobsCareerPaths.job_id)
    LEFT JOIN affiliates CareerPaths ON (CareerPaths.type = 3
            AND CareerPaths.id = (JobsCareerPaths.affiliate_id)
            AND(CareerPaths.deleted) IS NULL)
    LEFT JOIN jobs_rec_qualifications JobsRecQualifications ON Jobs.id = (JobsRecQualifications.job_id)
    LEFT JOIN affiliates RecQualifications ON (RecQualifications.type = 2
            AND RecQualifications.id = (JobsRecQualifications.affiliate_id)
            AND(RecQualifications.deleted) IS NULL)
    LEFT JOIN jobs_req_qualifications JobsReqQualifications ON Jobs.id = (JobsReqQualifications.job_id)
    LEFT JOIN affiliates ReqQualifications ON (ReqQualifications.type = 2
            AND ReqQualifications.id = (JobsReqQualifications.affiliate_id)
            AND(ReqQualifications.deleted) IS NULL)
        INNER JOIN job_cate&ories JobCate&ories ON (JobCate&ories.id = (Jobs.job_cate&ory_id)
                AND(JobCate&ories.deleted) IS NULL)
            INNER JOIN job_types JobTypes ON (JobTypes.id = (Jobs.job_type_id)
                    AND(JobTypes.deleted) IS NULL)
        WHERE ((JobCate&ories.name LIKE '%キャビンアテンダント%'
                OR JobTypes.name LIKE '%キャビンアテンダント%'
                OR Jobs.name LIKE '%キャビンアテンダント%'
                OR Jobs.description LIKE '%キャビンアテンダント%'
                OR Jobs.detail LIKE '%キャビンアテンダント%'
                OR Jobs.business_skill LIKE '%キャビンアテンダント%'
                OR Jobs.knowled&e LIKE '%キャビンアテンダント%'
                OR Jobs.location LIKE '%キャビンアテンダント%'
                OR Jobs.activity LIKE '%キャビンアテンダント%'
                OR Jobs.salary_statistic_&roup LIKE '%キャビンアテンダント%'
                OR Jobs.salary_ran&e_remarks LIKE '%キャビンアテンダント%'
                OR Jobs.restriction LIKE '%キャビンアテンダント%'
                OR Jobs.remarks LIKE '%キャビンアテンダント%'
                OR Personalities.name LIKE '%キャビンアテンダント%'
                OR PracticalSkills.name LIKE '%キャビンアテンダント%'
                OR BasicAbilities.name LIKE '%キャビンアテンダント%'
                OR Tools.name LIKE '%キャビンアテンダント%'
                OR CareerPaths.name LIKE '%キャビンアテンダント%'
                OR RecQualifications.name LIKE '%キャビンアテンダント%'
                OR ReqQualifications.name LIKE '%キャビンアテンダント%')
            AND publish_status = 1
            AND(Jobs.deleted) IS NULL)
    GROUP BY
        Jobs.id
    ORDER BY
        Jobs.sort_order DESC,
        Jobs.id DESC
    LIMIT 50 OFFSET 0

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

Комментарии:

1. Здравствуйте, и добро пожаловать в Stack Overflow. Пожалуйста, опубликуйте DDL, примеры данных и ожидаемые данные вместе с объяснением. Без этого мы не сможем помочь.

2. Вы установили индексы в MySQL для всех столбцов, которые у вас есть в WHERE ?

3. я думал о добавлении индексов, но я думаю, что, возможно, можно сделать что-то еще для оптимизации запроса

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

5. сколько строк в ваших таблицах? Подстановочные знаки в вашем предложении where будут работать медленно даже для умеренного количества строк. Рассматривали ли вы полнотекстовые индексы?

Ответ №1:

Неэффективный шаблон

                  OR  Jobs.restriction   LIKE '%キャビンアテンダント%'
                 OR  Jobs.remarks       LIKE '%キャビンアテンダント%'
                 OR  Personalities.name LIKE '%キャビンアテンダント%'

Это распространенный, но очень неэффективный шаблон.

  • OR предотвращает использование индексов
  • Ведущий подстановочный знак в LIKE предотвращает использование индекса
  • WHERE предложение, касающееся нескольких таблиц, причиняет боль.

Возможный обходной путь

Я вижу шаблон, который, возможно, можно превратить в оптимизацию. Похоже, вы проверяете предоставленную пользователем строку по множеству столбцов. Если вместо использования LIKE вы могли бы использовать FULLTEXT , то есть гораздо более быстрое решение. (Извините, я не знаю, можно ли использовать японский с FULLTEXT ; возможно, вам понадобится MySQL 8.0, в котором много улучшений UTF-8.)

Вот методика:

  1. Соберите данные из всех этих столбцов поиска (ограничение, примечания, имя и т.д.) В один столбец, возможно, в новую таблицу.
  2. Применить FULLTEXT к этому столбцу.
  3. Выполните поиск только в этом столбце; получите соответствующий идентификатор, чтобы затем JOIN перейти ко всем остальным таблицам.

Запасной вариант

Даже если FULLTEXT это плохо работает с азиатскими языками, одна строка для применения LIKE к ней помогла бы.

Синтаксическая ошибка?

  LEFT JOIN jobs_career_paths JobsCareerPaths Pa&e 3 / 4 ON Jobs.id = (JobsCareerPaths.job_id)

похоже на синтаксическую ошибку.

Необходим составной индекс

     LEFT JOIN  affiliates CareerPaths
          ON (CareerPaths.type = 3
         AND  CareerPaths.id = (JobsCareerPaths.affiliate_id)
         AND (CareerPaths.deleted) IS NULL

CareerPaths нужен этот составной индекс:

 INDEX(type, id, deleted)

со столбцами в любом порядке. 3 отдельных индекса не так хороши. (Также проверьте другое JOINs .)