Лучший способ индексировать инструкцию "UPDATE" с помощью "WHERE" в mysql

#mysql #laravel

Вопрос:

Я использую Laravel amp; MySQL, и у меня есть эта инструкция «UPDATE», выполнение которой занимает очень много времени, и я пытаюсь оптимизировать ее наилучшим возможным способом.

Вот мой запрос, который занимает около 700 мс:

 UPDATE
  table_1
SET
  woman_id = 48,
  table_1.updated_at = '2020-08-26 12:18:48'
WHERE
  woman_id is null
  AND last_date >= '2020-08-26'
  AND man_id = 1
ORDER BY
  last_date ASC
LIMIT
  100

Я добавил индекс для (man_id, woman_id, last_date), чтобы достичь 700 мс, в то время как он достигает 4 мс без индекса.
Таблица содержит около 90 тыс. записей.

Есть идеи о том, как это оптимизировать?

Ответ №1:

Рассмотрим эту стратегию,

Добавьте индекс для (last_date, man_id, woman_id)

измените, ГДЕ last_date >= ‘2020-08-26’ И man_id = 1, А woman_id равен нулю

сообщите нам результаты.

УДАЛИТЕ другой многоколоночный индекс.

1. теперь требуется 6 секунд, чтобы завершить ОГРАНИЧЕНИЕ 50, что на самом деле было хуже, чем старый индекс

2. @PedroMarthon Пожалуйста, опубликуйте текстовые результаты ОБЪЯСНЕНИЯ обработки 6s. Рассмотрите возможность запуска ANALYZE table_name, чтобы убедиться, что индексы актуальны, прежде чем запускать с помощью EXPLAIN SELECT ….

Ответ №2:

К сожалению, чем больше столбцов вы обновляете, тем медленнее это происходит, но есть несколько способов; Один из них заключается в обновлении данных на стороне PHP по частям. Другой способ заключается в том, чтобы Mysql АНАЛИЗИРОВАЛ ТАБЛИЦУ table_1 для оптимизации индекса. Также, поскольку вы обновляете данные, вы можете удалить предложение order by .

     $result = $db->rawquery("select * from table1 WHERE 
    woman_id is null AND last_date >= '2020-08-26' AND man_id = 1");
    
    // Chunk Results
    $chunk = array_chunk($result, 5000);

         foreach ($chunk as $chunk_arr) {

                  $tmp_arr = array();
                  foreach ($chunk_arr as $segment) {
                       $tmp_arr [] = $segment;
                  }
                  
                  $sql = "insert into table_1 (
                                         colum1,colum2, and so on
                                         ) values ";
                            
                  $array_keys = array_keys($tmp_arr);
                  $last_key = end($array_keys);

                  foreach ($tmp_arr as $key => $data) {
                  if ($last_key == $key) {
                  $sql .= "(" . implode(",", $data) . ") ON DUPLICATE KEY UPDATE woman_id=VALUES(48), updated_at=VALUES('2020-08-26 12:18:48') ";
                  } else {
                  $sql .= "(" . implode(",", $data) . "),";
                  }
                  }

                 

                 $db->rawQuery($sql);
                 unset($tmp_arr);
       }