Помогите с оптимизацией запросов MySQL

Тема в разделе "Базы данных", создана пользователем danneo, 29 май 2018.

Модераторы: latteo
  1. danneo

    danneo Честный

    Регистр.:
    13 ноя 2007
    Сообщения:
    1.485
    Симпатии:
    114
    Смотрю на логи, но понять ничего не могу. Проблема в том, что по статистике запросы грузять базу на хостинге, на 700% от допустимого. Жалобы от сапорта нет, но все же...
    Запросы на апдейт идут каждве 15 секунд вроде, пока юзер смотрит страницу. Но это всего 1 запрос, что плохого то?
    Нужно как-то оптимизировать.

    Почему запрос обновления состоит из двух (update и select), разбитых строками:
    И почему INSERT запрос работает от 0,1 до 10+сек? Обычный INSERT, просто вставка без других подзапросов и вычислений. Есть только обработка IP адреса через INET_ATON(). Но не в этом же пробелма?
     
    Последнее редактирование: 29 май 2018
  2. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    163
    Симпатии:
    108
    Это не запрос разбит. Сам запрос такой:
    Код:
    UPDATE dle_rev_views SET
                       `action` = CONCAT_WS('|', `action`, '49,1434,0|49,1441,0|49,1447,0|49,1454,0' ),
                       `time_view` = 1454
                 
                      WHERE `userid` = 0
                      AND `postid` = 0
                      AND dle_rev_views.sid =  'ab66809906207c49714eb43d55c25a60'\G
    
    Далее штука, которая сформировала этот лог (напишите что за штука, предполагаю что это из набора pt-query), дает вам подсказку, как посмотреть индексы и статистику выполнения этого запроса с помощью EXPLAIN.
    Дело в том, что "EXPLAIN UPDATE ..." не работает на мускуле до версии 5.6.3, только "EXPLAIN SELECT", и поэтому этот запрос с UPDATE трансформировали, заменив UPDATE на SELECT.
    Выполните
    Код:
    EXPLAIN
    select
                       `action` = CONCAT_WS('|', `action`, '49,1434,0|49,1441,0|49,1447,0|49,1454,0' ),
                       `time_view` = 1454 from dle_rev_views where  `userid` = 0
                      AND `postid` = 0
                      AND dle_rev_views.sid =  'ab66809906207c49714eb43d55c25a60'\G
    
    И получите интересную картинку, на основе которой сможете заоптимизировать запрос. Когда люди просят помощи с оптимизацией, я всегда пишу, чтобы предоставили "EXPLAIN SELECT ..." и схему таблиц.
    Правильно думаете, INET_ATON выполняется мгновенно, запрос один, 10 секунд, как и 0,1 - это слишком много для него.
    Полагаю, что таблица огромная, и на ней слишком много индексов.
    Поэтому
    Код:
    SHOW CREATE TABLE dle_rev_views;
    
    и
    Код:
    SELECT COUNT(*) FROM dle_rev_views;
    
    в студию
     
    Последнее редактирование: 30 май 2018
    Amazko нравится это.
  3. danneo

    danneo Честный

    Регистр.:
    13 ноя 2007
    Сообщения:
    1.485
    Симпатии:
    114
    1-й запрос:

    2-й запрос посчитал кол-в строк в 60255 штук :) Так много, потому что это сбор свой метрики :)
     
  4. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    163
    Симпатии:
    108
    1. Переделать Engine

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

    Код:
    ALTER TABLE `таблица` ENGINE=INNODB;
    
    Вообще я с MyISAM уже не работаю, поэтому советы ниже работают для INNODB

    2. Чтобы быстрее шло обновление, нужно добавить составной индекс:
    Код:
    ALTER TABLE `dle_rev_views` ADD KEY `upd_idx` (sid, postid, userid);
    
    Порядок (sid, postid, userid) я выбрал такой, потому что самым уникальным (больше вариантов данных) мне кажется sid, и затем уже postid, userid. Вообще проверьте это утверждение - посмотрите сколько уникальных значений:

    Код:
    select count(distinct(sid)) from dle_rev_views;
    
    Аналогично для остальных полей: postid, userid. Там где вариантов больше - должны идти первыми в составном индексе.

    Также, конкретно для ускорения этого запроса, необходимо:
    • `postid` перевести в UNSIGNED
    • `userid` перевести в NOT NULL - если логика приложения не поломается. Посмотрите, есть ли вообще NULL в данных.
    Это улучшит работу с этим индексом `upd_idx`.

    3. Запрос на вставку.

    Он составлен с ошибками. На MySQL 5.7.22 он вообще не выполнился:
    Код:
    ERROR 1265 (01000): Data truncated for column 'cost_view' at row 1
    
    На версии 5.5.41 выдает 4 предупреждения. Попробуйте выполнить вручную этот запрос в консоли MySQL и потом SHOW WARNINGS; Получите это:

    Код:
    +---------+------+------------------------------------------------+
    | Level   | Code | Message                                        |
    +---------+------+------------------------------------------------+
    | Warning | 1364 | Field 'time_view' doesn't have a default value |
    | Warning | 1364 | Field 'action' doesn't have a default value    |
    | Warning | 1364 | Field 'params' doesn't have a default value    |
    | Warning | 1265 | Data truncated for column 'cost_view' at row 1 |
    +---------+------+------------------------------------------------+
    
    Я полагал, что вставка была долгой по причине кучи индексов, которые при каждой вставке перестаиваются. Теперь мое предположение - тормозит лог предупреждений.

    Выполните эти два запроса и вывод - сюда:

    Код:
    SHOW VARIABLES LIKE '%warn%';
    SHOW VARIABLES LIKE '%log_error%';
    
     
    Последнее редактирование: 1 июн 2018
  5. danneo

    danneo Честный

    Регистр.:
    13 ноя 2007
    Сообщения:
    1.485
    Симпатии:
    114
    Это безопасно? Хуже не будет? :)
    Получается: 61549

    Почему так? По умолчанию же указаны данные: "0.00"
    Попробовал через phpmyadmin, вставка прошла. Может быть из-за версии mysql...

    Получается:
    и еще:
     
  6. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    163
    Симпатии:
    108
    Еще раз перечитал сообщение. Вставка в таблицу на 60 тыс записей, без доп индексов за 10 секунд - это нонсенс. У вас какой хостинг - шаред? Они могут искусственно занижать скорость, либо сервер БД перегружен.
    В любом случае, меняйте хостинг, или просите дать другой сервак баз данных.

    Раз начал отвечать на вопросы, оставлю ответы:

    Безопасно. Сделайте
    Код:
    SHOW ENGINES;
    В колонке Support будет показано, поддерживается или нет. И ENGINE всегда можно вернуть назад на MyISAM.
    По другим колонкам будущего индекса (postid, userid) тоже.
    Потому что
    Код:
    ERROR 1265 (01000:( Data truncated for column 'cost_view' at row 1
    
    Более внимательно посмотрите запрос INSERT и поймете.
    Да, в старых версиях вставляется с 4 предупреждениями, которые пишутся в лог. В новой версии запрос вообще не работает.
    Это мое предположение - запрос выполняется дольше из-за предупреждений, которые сбрасываются в лог (а он ведется). Попробуем исправить, чтобы вставка происходила без предупреждений.
    Вообще я сомневаюсь что логирование ошибок настолько может увеличить время вставки. Милисекунды - да, секунды - дико сомневаюсь.
     
  7. danneo

    danneo Честный

    Регистр.:
    13 ноя 2007
    Сообщения:
    1.485
    Симпатии:
    114
    да

    Получается:
    sid - 63482
    postid - 887
    userid - 58

    Еще бы быть профи... посмотрел, ничего не увидел :) Нужно указать значения по умолчанию вместо пустых значений?
    Что в инсерте не так, можете подсказать?
     
  8. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    163
    Симпатии:
    108
    Вот видите, самое уникальное значение - sid, затем postid, затем userid. Поэтому именно в таком порядке будет самый эффективный индекс:
    Код:
    ALTER TABLE `dle_rev_views` ADD KEY `upd_idx` (sid, postid, userid);
    
    Если в условии `WHERE` есть `sid = ...` (а он есть в вашем запросе с UPDATE), то сразу все количество строк "будет поделено" на 63482 частей, возьмется подходящая часть, и уже среди нее будет вестись поиск по `postid`, который еще раз поделит этот кусочек на 887 частей, и в конце уже поиск по `userid`. Так работают составные индексы.

    Код:
    `userid` int(11) DEFAULT NULL,
    
    поиск по полю, которое может быть NULL, ведется медленнее, так как идет дополнительная проверка на NULL. Так индекс будет работать лучше, но нужно быть осторожным и не сломать логику CMS:
    Код:
    ALTER TABLE `dle_rev_views` MODIFY `userid` int(11) UNSIGNED NOT NULL DEFAULT '0';
    
    Я еще добавил "UNSIGNED", с беззнаковыми числами быстрее. Ну и для `postid`можно убрать знак:

    Код:
    ALTER TABLE `dle_rev_views` MODIFY `postid` int(11) UNSIGNED NOT NULL;
    
    поле `cost_view` должно быть числом:
    Код:
    `cost_view` float(2,2) NOT NULL DEFAULT '0.00' COMMENT '',
    
    вместо этого вставляется строка:

    Код:
    INSERT INTO dle_rev_views
    (... `cost_view` ...) VALUES
    (... '' ...)
    
    MySQL преобразует строку в "0", и выдает предупреждение. С полем `num_letters` - аналогично.
    Правильно:

    Код:
    INSERT INTO dle_rev_views
    (... `cost_view` ...) VALUES
    (... 0 ...)
    
    На шаред хостинге у вас очень мало возможностей. Я бы посмотрел через htop и mytop загрузку сервера, чтобы прийти к окончательному выводу. Но на шареде этого не сделать. Меняйте сервер БД, такая долгая вставка - не порядок.
     
    danneo нравится это.
  9. sentance

    sentance Создатель

    Регистр.:
    8 дек 2015
    Сообщения:
    10
    Симпатии:
    1
    Автор, у меня подобная проблема на хостинге. У вас получилось? На сколько повысилась производительность?
     
  10. danneo

    danneo Честный

    Регистр.:
    13 ноя 2007
    Сообщения:
    1.485
    Симпатии:
    114
    Немного улучшилось