Оптимизация запросов для большой базы данных

Привет мне нужна помощь, чтобы оптимизировать запрос для больших записей базы данных более 1 миллиона. Текущий запрос занимает 27-30 секунд для выполнения.

SELECT SQL_CALC_FOUND_ROWS
candidate.candidate_id AS candidateID,
candidate.candidate_id AS exportID,
candidate.is_hot AS isHot,
candidate.date_modified AS dateModifiedSort,
candidate.date_created AS dateCreatedSort,
candidate.first_name AS firstName,
candidate.last_name AS lastName,
candidate.city AS city,
candidate.state AS state,
candidate.key_skills AS keySkills,
owner_user.first_name AS ownerFirstName,
owner_user.last_name AS ownerLastName,
CONCAT(owner_user.last_name,
        owner_user.first_name) AS ownerSort,
DATE_FORMAT(candidate.date_created, '%m-%d-%y') AS dateCreated,
DATE_FORMAT(candidate.date_modified, '%m-%d-%y') AS dateModified,
candidate.email2 AS email2 FROM
candidate
    LEFT JOIN
user AS owner_user ON candidate.owner = owner_user.user_id
    LEFT JOIN
saved_list_entry ON saved_list_entry.data_item_type = 100
    AND saved_list_entry.data_item_id = candidate.candidate_id WHERE
is_active = 1 GROUP BY candidate.candidate_id ORDER BY    dateModifiedSort 
DESC LIMIT 0 , 15

Есть ли способ уменьшить время выполнения запроса. Я также добавил индекс в таблицах, но он не работает нормально.

Индексы

Источник

4 ответа

Решение

Я изменил псевдоним таблицы в приведенном ниже запросе, используйте это, это должно решить вашу проблему

SELECT SQL_CALC_FOUND_ROWS
candidate.candidate_id AS candidateID,
candidate.candidate_id AS exportID,
candidate.is_hot AS isHot,
candidate.date_modified AS dateModifiedSort,
candidate.date_created AS dateCreatedSort,
candidate.first_name AS firstName,
candidate.last_name AS lastName,
candidate.city AS city,
candidate.state AS state,
candidate.key_skills AS keySkills,
user.first_name AS ownerFirstName,
user.last_name AS ownerLastName,
CONCAT(user.last_name,
        user.first_name) AS ownerSort,
DATE_FORMAT(candidate.date_created, '%m-%d-%y') AS dateCreated,
DATE_FORMAT(candidate.date_modified, '%m-%d-%y') AS dateModified,
candidate.email2 AS email2 FROM
candidate
    LEFT JOIN
user ON candidate.owner = user.user_id
    LEFT JOIN
saved_list_entry ON saved_list_entry.data_item_type = 100
    AND saved_list_entry.data_item_id = candidate.candidate_id WHERE
is_active = 1 GROUP BY candidate.candidate_id ORDER BY    dateModifiedSort 
DESC LIMIT 0 , 15

используйте приведенные ниже запросы для создания индексов для условий соединения

create index index_user user(user_id);

create index index_saved_list_entry saved_list_entry(data_item_type,data_item_id);

create index index_candidate candidate(is_active,candidate_id,dateModifiedSort);
Источник

Вы используете шаблон запроса

     SELECT a vast bunch of stuff
       FROM a complex assembly of JOIN operations
      ORDER BY some variable DESC
      LIMIT 0,small number

Это по своей сути неэффективно: чтобы удовлетворить ваш запрос, сервер MySQL должен создать обширный набор результатов, затем он должен отсортировать все целиком, затем он берет первые пятнадцать строк и отбрасывает остальные.

Чтобы сделать это более эффективным, вам нужно сортировать меньше вещей. Вот способ сделать это. Похоже, вы хотите найти пятнадцать последних измененных кандидатов. Этот запрос довольно дешево получит идентификаторы этих кандидатов. Он использует один из ваших индексов.

                   SELECT candidate_id
                     FROM candidate
                    ORDER BY date_modified DESC
                    LIMIT 0, 15

Затем вы можете использовать это как подзапрос в вашем основном запросе. Добавьте предложение как это:

  WHERE candidate.candidate_id IN (
                   SELECT candidate_id
                     FROM candidate
                    ORDER BY date_modified DESC
                    LIMIT 0, 15)

на ваш запрос в соответствующем месте.

Также обратите внимание, что вы используете нестандартное и потенциально опасное специфичное для MySQL расширение для GROUP BY. Ваш запрос работает, но если у кандидата несколько владельцев, он вернется только один после случайного выбора.

Наконец, вы, кажется, поместили индексы в один столбец во многие столбцы вашей большой таблицы. Это пресловутый антипаттерн SQL: все эти индексы замедляют операции INSERT и UPDATE, и большинство из них, вероятно, бесполезны для ускорения запроса. Конечно, для этого запроса единственными полезными индексами являются date_modified и первичный ключ.

Многие сложные запросы лучше всего выполняются с использованием определенных многостолбцовых индексов. Куча одноколоночных индексов не помогает таким запросам.

Источник

  1. Избавляться от saved_list_entry, это ничего не добавляет.

  2. Задержка присоединения к user, Это позволит вам избавиться от GROUP BY, что добавляет кучу времени и, возможно, завышает значение FOUND_ROWS(),

Что-то вроде:

SELECT  c2.*,
        ou.first_name AS ownerFirstName,
        ou.last_name AS ownerLastName,
        CONCAT(ou.last_name, ou.first_name) AS ownerSort,
    FROM  
      ( SELECT  SQL_CALC_FOUND_ROWS
                c.candidate_id AS candidateID, c.candidate_id AS exportID,
                c.is_hot AS isHot, c.date_modified AS dateModifiedSort,
                c.date_created AS dateCreatedSort, c.first_name AS firstName,
                c.last_name AS lastName, c.city AS city, c.state AS state,
                c.key_skills AS keySkills,
                DATE_FORMAT(c.date_created, '%m-%d-%y') AS dateCreated,
                DATE_FORMAT(c.date_modified, '%m-%d-%y') AS dateModified,
                c.email2 AS email2
            FROM  candidate AS c
            WHERE  is_active = 1
            GROUP BY  c.candidate_id
            ORDER BY  c.date_modified DESC  -- note change here
            LIMIT  0 , 15 
      ) AS c2
    LEFT JOIN  user AS ou  ON c2.owner = ou.user_id;

(Я испортил порядок столбцов, но вы можете это исправить.)

Индекс необходим:

candidate:  INDEX(is_active, candidate_id, date_modified)
Источник

Во-первых, кандидат, я подозреваю, это всегда одна запись по идентификатору, поэтому, почему вы делаете GROUP BY, вне меня, это может быть ВЕРОЯТНО удалено и немного улучшено.

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

В-третьих, учитывая, что GROUP BY больше не применяется, я бы предложил обновить ваши индексы следующим образом:

table             index
CANDIDATE         ( is_active, date_modified, candidate_id, owner )
user              ( user_id )
saved_list_entry  ( data_item_id, data_item_type )

Так как ваш заказ на дату изменен в порядке убывания, имея IT во второй позиции по отношению к is_active (условие Где), он быстро пробежит ваши первые 15. Тем не менее, ваш SQL_CALC_FOUND_ROWS все равно должен будет пролистывать все другие квалификации, но набор результатов будет предварительно упорядочен индексом для соответствия.

SELECT SQL_CALC_FOUND_ROWS
      c.candidate_id AS candidateID,
      c.candidate_id AS exportID,
      c.is_hot AS isHot,
      c.date_modified AS dateModifiedSort,
      c.date_created AS dateCreatedSort,
      c.first_name AS firstName,
      c.last_name AS lastName,
      c.city AS city,
      c.state AS state,
      c.key_skills AS keySkills,
      u.first_name AS ownerFirstName,
      u.last_name AS ownerLastName,
      CONCAT(u.last_name, u.first_name) AS ownerSort,
      DATE_FORMAT(c.date_created, '%m-%d-%y') AS dateCreated,
      DATE_FORMAT(c.date_modified, '%m-%d-%y') AS dateModified,
      c.email2 AS email2 
   FROM
      candidate c
         LEFT JOIN user u
            ON c.owner = u.user_id
         LEFT JOIN saved_list_entry s
            ON c.candidate_id = s.data_item_id
            AND s.data_item_type = 100
   WHERE
      c.is_active = 1 
   GROUP BY 
      c.candidate_id 
   ORDER BY    
      c.date_modified DESC 
   LIMIT 
      0, 15
Источник
Другие вопросы по тегам