Являются ли несколько индексов в таблице MYSQL причиной медленных ОБНОВЛЕНИЙ и ВСТАВОК?

Dec 10 2020

Производительность моего сайта (стек LAMP) значительно снизилась за последние пару дней, несмотря на отсутствие обновлений кода. Кажется, что проблема возникает только при вставке, обновлении и удалении конкретной таблицы MySQL. Любая страница, которая обновляет, вставляет или удаляет запись в «таблице» заданий, загружается примерно за 10 секунд. (Например UPDATE jobs SET title = 'sdfldsfjlk' WHERE job_id = 134324)

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

В таблице около 180 000 записей. В представлении PHPMyAdmin я заметил, что в дополнение к «нормальному» индексу в первичном поле есть индекс в поле «entry_date» (см. Изображение). Может ли это быть проблемой в данном случае? Понятия не имею, зачем был создан индекс для этого поля.

Если нет, то что еще может быть источником проблемы? Я проверил место на диске, вроде нормально. (Доступно 7 ГБ) согласно df.

SHOW CREATE TABLE job\G

Create Table: CREATE TABLE `job` (
    `job_id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` int(11) NOT NULL DEFAULT '0', 
    `entry_date` date NOT NULL DEFAULT '0000-00-00', 
    `timescale` varchar(20) COLLATE latin1_german2_ci NOT NULL DEFAULT '0000-00-00', 
    `title` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `description` text COLLATE latin1_german2_ci NOT NULL, 
    `start_date` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `address_town` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `address_county` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode1` varchar(4) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode2` char(3) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `status` tinyint(4) NOT NULL DEFAULT '0', 
    `cat_id` int(4) NOT NULL DEFAULT '0', 
    `price` decimal(4,2) NOT NULL DEFAULT '1.00', 
    `emailcount` smallint(5) NOT NULL DEFAULT '-1', 
    `emailcount2` int(11) NOT NULL DEFAULT '-1', 
    `recemailcount` int(11) NOT NULL DEFAULT '-1', 
    `archive` tinyint(4) NOT NULL DEFAULT '0', 
    `post_url` varchar(100) COLLATE latin1_german2_ci NOT NULL, 
    PRIMARY KEY (`job_id`), 
    KEY `entrydatejob_id` (`entry_date`,`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=235844
           DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

ОБНОВЛЕНИЕ - Во-первых, спасибо всем участникам. Я это очень ценю. Таким образом, за последние пару дней проблема прекратилась, что сделало попытку найти, в чем проблема, может быть еще более трудной. Но теперь, кажется, он снова вернулся. Позвольте мне начать с предоставления типа машины, размещенной в облаке Google: g1-small (1 виртуальный ЦП, 1,7 ГБ памяти). Я продолжу обновлять это с дополнительной информацией, запрошенной теми, кто комментирует.

Ответы

6 ypercubeᵀᴹ Dec 10 2020 at 19:22

Чтобы ответить на конкретный вопрос:

Нет , один (entry_date)или два дополнительных индекса не повлияют на производительность обновления другого titleстолбца.

Дополнительно:

Нет , версия MySQL 5.6 не слишком древняя, даже если некоторые современные функции (например, оконные функции) отсутствуют. У вас должна быть достойная производительность с достойным оборудованием.

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

старое, неэффективное оборудование: проверьте спецификации диска, измерьте его работоспособность.
фрагментированный индекс / таблица. Проверьте здесь документы MySQL и старые вопросы / ответы о том, как дефрагментировать таблицы MyISAM (OPTIMIZE TABLE).

Последний, но тем не менее важный:

Ваша таблица использует движок MyISAM , что уже давно новость. InnODB заменил его как механизм по умолчанию в MySQL много лет назад. Активных разработок этого движка нет. Ему не хватает нескольких функций по сравнению с InnoDB (транзакции, ограничения внешнего ключа и т.д.) и производительности при большинстве рабочих нагрузок . Я настоятельно рекомендую вам изменить свою таблицу (после тестирования, конечно, чтобы ваши приложения и процедуры не ломались), чтобы использовать InnoDB.
Что важно для вашего вопроса, InnoDB может выполнять SELECTи UPDATEодновременно (обычно). MyISAM полностью блокирует таблицу; обновление или выбор должны завершиться до того, как выбор или обновление может начаться.
При переключении с MyISAM на InnoDB обязательно настройте key_buffer_sizeи innodb_buffer_pool_size.

3 J.D. Dec 10 2020 at 19:20

Индексы незначительно влияют на производительность операций INSERT и DELETE, но в целом это незначительно, особенно если ваши индексы хорошо спланированы и вы не переусердствуете, помещая 30 индексов в одну и ту же таблицу, каждый с 30 различными комбинациями полей. (Обычно я придерживаюсь рекомендации 5 на 5 - максимум 5 индексов, максимум 5 полей на индекс. Конечно, это всего лишь рекомендация, а не жесткое правило).

При этом индексы фактически используются для повышения производительности запросов UPDATE и SELECT, поскольку индекс используется для поиска строк для UPDATE или SELECT.

Резкое изменение, которое вы видите, вряд ли связано с обнаруженным вами индексом entry_date (знаете ли вы, было ли оно добавлено недавно или уже было до изменения производительности?).

Вам следует обратить внимание на две вещи:

Если предыдущие индексы для ваших запросов все еще используются, особенно для запросов UPDATE и SELECT (и если они ищутся или выполняется операция сканирования сейчас). Это может измениться из-за изменений в таблице статистики данных с течением времени. Вы можете использовать оператор ANALYZE для проверки статистики таблицы.
Еще вы можете изучить фрагментацию индекса , которая является естественным явлением с течением времени. Это происходит по мере добавления данных в таблицу. (Как правило, это не должно вызывать беспокойства на таком маленьком столе, как ваш, но все равно стоит проверить.)

Я продолжу обновлять свой ответ, добавляя больше вещей, на которые нужно обратить внимание, когда я думаю о них. Выполнение EXPLAIN по вашим запросам также может помочь вам разобраться в проблеме. Это попытка разделить операторы UPDATE и INSERT, которые выполняются медленно, и попытаться понять, затронуты ли они одной и той же основной причиной (поскольку они снова действуют несколько обратно пропорционально индексам, INSERT незначительно медленнее, но UPDATE обычно выигрывают от правильного индекса и должно быть быстрее.)

WilsonHauck Dec 10 2020 at 22:40

Задание OPTIMIZE TABLE; чтобы устранить фрагментацию и воссоздать все индексы.

Затем проверьте время запроса UPDATE.

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