За последние 24 часа нас посетили 20347 программистов и 1088 роботов. Сейчас ищет 791 программист ...

Оптимизировать запрос UPDATE.

Тема в разделе "MySQL", создана пользователем Phantik, 18 авг 2009.

  1. Phantik

    Phantik Активный пользователь

    С нами с:
    2 июл 2009
    Сообщения:
    163
    Симпатии:
    0
    Есть таблица следующей структуры:

    CREATE TABLE `wins` (
    `LID` smallint(6) unsigned NOT NULL,
    `DrawNum` smallint(6) unsigned NOT NULL,
    `Module` smallint(6) unsigned NOT NULL,
    `Tour` smallint(6) unsigned NOT NULL,
    `TckNum` int(11) unsigned NOT NULL,
    `PayDT` double DEFAULT 0.0,
    `WorkST` smallint(6) unsigned NOT NULL,
    `CurTax` smallint(6) unsigned NOT NULL,
    `FieldCont` BLOB,
    PRIMARY KEY (`LID`,`DrawNum`,`Module`,`Tour`,`TckNum`)
    ) ENGINE=MyISAM;

    Таблица содержит в среднем 3-5 миллионов записей. Каждая строчка описывает выигрыш в лотерее. Задача основного кода - получить на вход: Данные: LID - код лотереи DrawNum-номер тиража и TckNum номер билета и проверить выиграл билет или нет (т.е. присутствует ли запись с указаными LID DrawNum TckNum). И если билет выигрышный, то отметить его оплату(проапдейтить поля PayDT - дата\время оплаты. WorkST - номер рабочей станции CurTax - налог).

    Соответственно выполняется 2 запроса:
    1) SELECT * FROM winst WHERE LID=a, DrawNum=b, TckNum=с
    2) UPDATE wins SET (PayDT=x,WorkST=y,....) WHERE LID=a, DrawNum=b, TckNum=с


    Работает все хорошо за исключением скорости 2го запроса. Потестировав скорость выполнения первого запроса
    получил 1-50 мили сек. второй же выполняется овсреднем от 400 до 1200 мили сек. Такой временной интервал меня никак не устраивает так как билеты могут вводиться со сканера со скоростью до 2-3 билетов в секунду. А у меня получается что один билет программа обрабатывает около секунды.

    Что можно сделать с запросом UPDATE? Как увеличить скорость его выполнения? Для сравнения аналогичные запросы по Access и MSSQL выполнялись на порядок быстрее. Заметил еще особенность что скорость апдейта не особо зависит от количества записей - примерно одинакова что для 100 тысяч что для 3 миллионов. Пробовал тоже самое для таблиц InnoDB - результат схожий.
     
  2. armadillo

    armadillo Активный пользователь

    С нами с:
    6 апр 2007
    Сообщения:
    2.380
    Симпатии:
    0
    Адрес:
    Russia, Moscow
    оригинальненько.
    написать аналогичный запрос селект и его оптимизировать.
    гугль-"explain mysql"
     
  3. Phantik

    Phantik Активный пользователь

    С нами с:
    2 июл 2009
    Сообщения:
    163
    Симпатии:
    0
    Ну так аналогичный селект работает гораздо быстрее.
     
  4. 440Hz

    440Hz Старожил
    Команда форума Модератор

    С нами с:
    21 дек 2012
    Сообщения:
    8.003
    Симпатии:
    1
    Адрес:
    Оттуда
    сделайте id AUTO INCREMENT с PRIMARY и тогда UPADTE по этому ключу будет намнонго самого SELECT
     
  5. Phantik

    Phantik Активный пользователь

    С нами с:
    2 июл 2009
    Сообщения:
    163
    Симпатии:
    0
    Попробовал изменить таблицу на
    CREATE TABLE `wins` (
    `Rec_ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `LID` smallint(6) unsigned NOT NULL,
    `DrawNum` smallint(6) unsigned NOT NULL,
    `Module` smallint(6) unsigned NOT NULL,
    `Tour` smallint(6) unsigned NOT NULL,
    `TckNum` int(11) unsigned NOT NULL,
    `PayDT` double DEFAULT 0.0,
    `WorkST` smallint(6) unsigned NOT NULL,
    `CurTax` smallint(6) unsigned NOT NULL,
    `FieldCont` BLOB,
    PRIMARY KEY (`Rec_ID`),
    INDEX(`LID`,`DrawNum`,`Module`,`Tour`,`TckNum`)
    ) ENGINE=MyISAM;

    Запрос на выборку стал работать на порядок медленееЮ запрос на апдейт стал выполняться для некоторых записей быстрее дл некоторых медленее. Разброс был примерно от 400 до 2500 мили сек. Селект прада с Inner Join - Ом может это как-то повлияло.
     
  6. armadillo

    armadillo Активный пользователь

    С нами с:
    6 апр 2007
    Сообщения:
    2.380
    Симпатии:
    0
    Адрес:
    Russia, Moscow
    а, то есть нам загадки загадываем...

    гугль - команда Explain
     
  7. Phantik

    Phantik Активный пользователь

    С нами с:
    2 июл 2009
    Сообщения:
    163
    Симпатии:
    0
    Описание команды explain нашел.
    Проблема в том что аналогичный селект работает на 2 порядка быстрее.
     
  8. 440Hz

    440Hz Старожил
    Команда форума Модератор

    С нами с:
    21 дек 2012
    Сообщения:
    8.003
    Симпатии:
    1
    Адрес:
    Оттуда
    .
    тогда покажите что НА САМОМ ДЕЛЕ происходит, а не ваши выдумки?

    а то мы тут пыжимся а у вас все совсем не так, как нам кажется...

    и КАК ЧАСТО выполняются оба запроса?
     
  9. Phantik

    Phantik Активный пользователь

    С нами с:
    2 июл 2009
    Сообщения:
    163
    Симпатии:
    0
    Просто боялся дать избыток информации, а то никто читать не будет. Теперь более подробно.

    Так устроен типичный запрос на поиск\проверку билета:
    Код (Text):
    1.  
    2. SELECT wins.LID,wins.DrawNum,wins.Module,wins.Tour,wins.TckNum,wins.PayDT,wins.WorkST,wins.CurTax,wins.FieldCont,
    3. summ.Money, summ.Descr1, summ.Descr2,
    4. mods.ModType, mods.LongName, mods.ShortName
    5. FROM wins  
    6. INNER JOIN summ ON (wins.LID=summ.LID AND wins.DrawNum=summ.DrawNum AND wins.Tour=summ.Tour)
    7. INNER JOIN mods ON (wins.LID=mods.LID AND wins.DrawNum=mods.DrawNum AND wins.Module=mods.Module)
    8. WHERE wins.LID=1 AND wins.DrawNum=532 AND wins.TckNum=358015"
    Теперь описание таблиц участвующих в запросе.

    Таблица wins. В ней хранятся все выигрыши. Количество записей всреднем 3-5 миллионов.
    Код (Text):
    1.  
    2. CREATE TABLE `wins` (
    3.   `LID` smallint(6) unsigned NOT NULL,                         // Код лотереи        
    4.   `DrawNum` smallint(6) unsigned NOT NULL,                // Номер тиража
    5.   `Module` smallint(6) unsigned NOT NULL,                    // Код типа розыгрыша
    6.   `Tour` smallint(6) unsigned NOT NULL,                        // Тур в котором выиграл билет
    7.   `TckNum` int(11) unsigned NOT NULL,                        // Номер билета
    8.   `PayDT` double DEFAULT 0.0,                                    // Дата\Время оплаты (если выигрыш был оплачен)
    9.   `WorkST` smallint(6) unsigned NOT NULL,                   // Номер рабочей станции оплатившей билет
    10.   `CurTax` smallint(6) unsigned NOT NULL,                    // Налог в % который был уплачен с выигрыша
    11.   `FieldCont` BLOB,                                                     // Игровое поле билета.
    12.     PRIMARY KEY(`LID`,`DrawNum`,`Module`,`Tour`,`TckNum`)
    13. ) ENGINE=MyISAM;
    Таблица summ. В ней хранятся описания призов и денежные суммы призов. Записей в среднем 3-5 тысяч.
    Связана с таблицей wins по полям LID, DrawNum, Module, Tour. Связь один(summ) ко многим(wins). Связь логическая, физически (средствами СУБД) не реализована.
    Код (Text):
    1.  
    2. CREATE TABLE `summ` (
    3.   `LID` smallint(6) unsigned NOT NULL,                            // Код лотереи
    4.   `DrawNum` smallint(6) unsigned NOT NULL,                   // Номер тиража
    5.   `Module` smallint(6) unsigned NOT NULL,                       // Код типа розыгрыша
    6.   `Tour` smallint(6) unsigned NOT NULL,                          //  Тур
    7.   `Money` bigint(20) unsigned NOT NULL,                         // Сумма
    8.   `Descr1` char(64)  DEFAULT NULL,                                // Текстовое описание приза
    9.   `Descr2` char(64)  DEFAULT NULL,                                // Текстовое описание приза №2
    10.    PRIMARY KEY(`LID`,`DrawNum`,`Module`,`Tour`)
    11. ) ENGINE=MyISAM;
    Таблица mods. В ней хранятся типы розыгрыша(особенности правил игры в определенном тираже)
    Записей в среднем до 1000.
    Связана с таблицей wins по полям LID,DrawNum,Module
    Код (Text):
    1.  
    2. CREATE TABLE `mods` (
    3.   `LID` smallint(6) unsigned NOT NULL,
    4.   `DrawNum` smallint(6) unsigned NOT NULL,
    5.   `Module` smallint(6) unsigned NOT NULL,
    6.   `ModType` smallint(6) unsigned NOT NULL,
    7.   `LongName` char(64)  DEFAULT NULL,
    8.   `ShortName` char(32)  DEFAULT NULL,
    9.   PRIMARY KEY (`LID`,`DrawNum`,`Module`)
    10. ) ENGINE=MyISAM;
    Так вот. Первый запрос выполняется приемлимо быстро. В среднем 1-50 мили сек. Особенности работы оператора состоят в том что если билет выигрышный(т.е. первый запрос вернулся не пустым), то ему надо зафиксировать оплату билета. Т.е. проапдейтить поля PayDT, WorkST, CurTax в таблице wins. Для этого выполняется следующий запрос:
    Код (Text):
    1.  
    2. UPDATE wins SET PayDT=40044.4448122338,WorkST=1,CurTax=0
    3. WHERE LID=1 AND DrawNum=532 AND TckNum=358015
    Так вот. ПРоблема в том что этот запрос уже выполняется по времени порядка 1 секунды. Что абсолютно неприемлимо ибо скорость оператора по вводу билетов может достигать 3-4 билетов в секунду. Отсюда следует собственно вопрос, как существенно ускорить запрос на изменение. Уверен что это возможно т.к. базы аналогичной структуры в СУБД Access и MSSQL выполняют такой запрос практически на порядок быстрее.

    Теперь какие у меня есть мысли по всему этому.
    1) Попробовать BLOB поле заменить на текстовое ибо тип хранимых данных это позволяет сделать.
    2) С учетом пункта 1) (или без учета) вынести Blob(текстовое) поле в другую таблицу. Связь будет по LID,DrawNum,TckNum
    3) Создать временную таблицу Tmp со структурой аналогичной таблице wins и все фиксации оплаты заносить туда с Помощью INSERT. (Протестил добавление - оно работает приемлемо быстро порядка 500 записей в секунду). И далее(например перед закрытием программы) либо писать хитрый запрос на вроде(проапдейтить в wins те записи которые присутствуют в Tmp). Такой запрос с помощью SQL вроде можно написать. Либо запускать параллельный поток, который будет брать запись из Tmp апдейтить соответствующую в wins и удалять из Tmp.
    4) Мне кажется, что есть среди настроек MySQL те, которые отвечают за работу с кешем. Что бы апдейты записывались не напрямую в базу(А судя по времени, у меня возникает ощущение что СУБД пишет напрямую), а через кеш. Очень надеюсь что знающие люди подскажут эти настройки.

    p.s. Приношу извинения за слишком большой текст.
     
  10. 440Hz

    440Hz Старожил
    Команда форума Модератор

    С нами с:
    21 дек 2012
    Сообщения:
    8.003
    Симпатии:
    1
    Адрес:
    Оттуда
    мысли у меня ОДНИ:


    1. всем таблицам делаем ID PRIMARY KEY AUTOINCREMENT

    2. JOIN штука красивая, но тебе НАХ не нужная. Зачем MySQL лопатить каженный раз 3-5 лимонов записей да еще на двух джойнах? Селай выборку из билетиков, а потом дерни из других таблиц то, что тебе надо. Будет в сотни раз быстрее. Повторю. В сотни.

    3. если идет большая работа с апдейтами и селектами то перейти на InnoDB. там блокировка на уровне строки, а не как в MyISAM на уровне таблицы.

    4. сделать ключи по нужным полям, а не один ключ на все поля.

    5. посмотреть что выдает EXPLAIN и поразмыслить над результатом. Будет не понятно - пиши. Ответим.
     
  11. 440Hz

    440Hz Старожил
    Команда форума Модератор

    С нами с:
    21 дек 2012
    Сообщения:
    8.003
    Симпатии:
    1
    Адрес:
    Оттуда
    на счет JOIN и как с ним бороться.


    пример:

    есть 2 тыблоицы по лимону записей. ты делаешь выборку из первой и джойнишь туда из второй. выводишь ОДНУ запись.

    что происходит:

    mysql начинает выборку из первой таблицы. на каждую запись она пробезает вторую таблицу на предмет получения джойна и ТОЛЬКО ПОТОМ выполняется WHERE.

    понимаешь смысл? она пробегает лимон записей и на каждую делает выборку еще из лимона записей в другой таблице.

    лимон*лимон = кол-во пробежек по строкам. (ОХУЕТЬ)

    вариант 2:

    дергаешь нужную запись из 1-ой таблицы.
    дергаешь нужную запись из 2-ой таблицы на основе 1-ой

    лимон ПЛЮС лимон = 2 лимона пробежек.

    мысля понятна?
     
  12. 440Hz

    440Hz Старожил
    Команда форума Модератор

    С нами с:
    21 дек 2012
    Сообщения:
    8.003
    Симпатии:
    1
    Адрес:
    Оттуда
    с двумя джойнами - повесится можно...
     
  13. kostyl

    kostyl Guest

    Странно а я думал она пробегает меньше лимона если есть индекс во второй таблице по условию джоина???
     
  14. 440Hz

    440Hz Старожил
    Команда форума Модератор

    С нами с:
    21 дек 2012
    Сообщения:
    8.003
    Симпатии:
    1
    Адрес:
    Оттуда
    ты попробуй суть понять?

    или лимон^(кол-во джойнов)

    или лимон*(кол-во джойнов)

    индексы не поправят ситуации в целом
     
  15. 440Hz

    440Hz Старожил
    Команда форума Модератор

    С нами с:
    21 дек 2012
    Сообщения:
    8.003
    Симпатии:
    1
    Адрес:
    Оттуда
    извечный пример:


    есть таблица юзеров. она связана с таблицами стран, городов, улиц, пола, школы, универа и т.д. , т.е. НОРМАЛИЗОВАННАЯ БД.

    красивым, но ТУПЫМ решением является написание выборки с JOIN. да. тут все красиво и умело. Одним запросом получили все данные, НО это хорошо и красиво когда у тебя юзеров 1000 и посещаемость 100 чел в день

    когда у тебя данных будет по лимому в каждой таблице посчитай сколько данных придется перелопатить MySQL для выполнения этого?

    а так будет несколько ОЧЕНЬ БЫСТРЫХ селектов по PR KEY.
     
  16. kostyl

    kostyl Guest

    да суть я понял
    Допустим берем первую запись в первой таблице и по джоин условию ищем во второй. Если есть индекс я ДУМАЮ начинается поиск по индексу, тобишь бинарный и того по второй таблице на одну запись из первой поиски пробежит меньше чем количество записей во второй таблице. Если идекса нет, то максимум по всем если тип LEFT.
    Так?
    А суть я понял типа "подзапрос идет после условия WHERE"?
     
  17. Phantik

    Phantik Активный пользователь

    С нами с:
    2 июл 2009
    Сообщения:
    163
    Симпатии:
    0
    Запрос выборки с джоинами, работает очень быстро, от общего времени занимает не более 5%. В контектсте поставленой задачи это устраивает более чем.
    Проблема в запросе UPDATE, который выполняется секунду и более. Что толку от того, что будет он выполняться не 20 милисекунд а 1? Всеравно 1000 милисекунд придется ждать UPDATE-а. И иа-за этого общее время останется около 1 секунды.
     
  18. 440Hz

    440Hz Старожил
    Команда форума Модератор

    С нами с:
    21 дек 2012
    Сообщения:
    8.003
    Симпатии:
    1
    Адрес:
    Оттуда
    1. перейди на InnoDB
    2. апдейт делай по одному ID
     
  19. kostyl

    kostyl Guest

    Phantik
    сделай праймари кеи отдельными полями с id а не такими комплексными...
     
  20. armadillo

    armadillo Активный пользователь

    С нами с:
    6 апр 2007
    Сообщения:
    2.380
    Симпатии:
    0
    Адрес:
    Russia, Moscow
    440Hz
    у него проблема в размере ключа. селект по тому же условию не тормозит.
     
  21. Phantik

    Phantik Активный пользователь

    С нами с:
    2 июл 2009
    Сообщения:
    163
    Симпатии:
    0
    У меня уже есть реализация той же задачи но с InnoDB. Вводил ее из-за поддержки транзакций. По тестам работает примерно так же как и MyISAM где-то на 10% быстрее где-то на 10% медленее, но в целом те же яйца.

    По поводу праймери кея:
    Набор LID`,`DrawNum`,`Module`,`Tour` - уникальный в силу специфики решаемой задачи.
    TckNum я могу вынести из праймери кея но индекс по нему должен буду построить всеравно т.к. это поле участвует во всех запросах выборки. И без индекса запрос выполняется по несколько секунд(это я проверял)

    Далее когда я вводил дополнительное поле Автоинкеремент, то апдейт выполнялся странным образом: Для записей которые были добавлены в последнюю очередь время снижалось до 300-500 мили сек. Зато при апдейте записей, добавленых в первую очередь время достигало 1500-2500 мили сек.

    Так же хочу добавить что, скорее всего дело не вструктуре таблиц не в ключах и индексах и не в запросах. Т.к. эта структура уже около 10 лет работает под MSSQL и MSAccess и Апдейты там проходят на порядок быстрее.
    Все-таки мне кажется что надо копать куда-то в сторону настроек кеша MySQL т.к. думается что пишет он так долго потому что пишет напрямую в файл а не в кеш. И Кажется мне что это можно выправить настройками субд. Просто я еще не шибко в мускуле разбираюсь. Либо все-таки блоб поле виновато и его надо выносить из таблицы либо в текстовое переводить. Но это все и некоторые другие варианты я сейчас пробую, посмотрим что получится.
     
  22. Phantik

    Phantik Активный пользователь

    С нами с:
    2 июл 2009
    Сообщения:
    163
    Симпатии:
    0
    Если еще кому-то интересно, то существенно помогла замена блоба на char. Специфика задачи это позволила сделать. Вообщем первые тесты дали результат порядка 10-20 милисекунд.
     
  23. 440Hz

    440Hz Старожил
    Команда форума Модератор

    С нами с:
    21 дек 2012
    Сообщения:
    8.003
    Симпатии:
    1
    Адрес:
    Оттуда
    так ты EXPLAIN делал?
    смотрел как что куда?
     
  24. Phantik

    Phantik Активный пользователь

    С нами с:
    2 июл 2009
    Сообщения:
    163
    Симпатии:
    0
    Если Update заменить на SELECT то explain выдает


    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE wins ref PRIMARY PRIMARY 4 const,const 103610 Using where

    только мне эти данные ничего не говорят. Может кто в курсе подскажите, какой можно сделать вывод по ним?
     
  25. Phantik

    Phantik Активный пользователь

    С нами с:
    2 июл 2009
    Сообщения:
    163
    Симпатии:
    0
    Чуть погорячился с выводами. Замена Блоба на Текст не сильно ускорила процесс. Апдейт стал выполняться 500-900 мили секунд, но это всеравно очень много.