Warning: Cannot use a scalar value as an array in /home/admin/public_html/forum/include/fm.class.php on line 757

Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/include/fm.class.php on line 770

Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/topic.php on line 737
Форумы портала PHP.SU :: Оптимизация запроса UPDATE в MySQL

 PHP.SU

 Программирование на PHP, MySQL и другие веб-технологии
PHP.SU Портал     На главную страницу форума Главная     Помощь Помощь     Поиск Поиск     Поиск Яндекс Поиск Яндекс     Вакансии  Пользователи Пользователи


 Страниц (1): [1]   

> Без описания
Поиск в теме | Версия для печати
Phantik
Отправлено: 19 Августа, 2009 - 14:35:13
Post Id


Посетитель


Покинул форум
Сообщений всего: 305
Дата рег-ции: Июнь 2009  


Помог: 0 раз(а)
Сразу приношу извинения за большой текст. Задавал этот вопрос кратко на другом форуме, но людям не хватило информации, поэтому пришлось выложить более подробно:

Так устроен типичный запрос на поиск\проверку билета:
CODE (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 миллионов.
CODE (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). Связь логическая, физически (средствами СУБД) не реализована.
CODE (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;
  12.  


Таблица mods. В ней хранятся типы розыгрыша(особенности правил игры в определенном тираже)
Записей в среднем до 1000.
Связана с таблицей wins по полям LID,DrawNum,Module
CODE (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;
  11.  


Так вот. Первый запрос выполняется приемлимо быстро. В среднем 1-50 мили сек. Особенности работы оператора состоят в том что если билет выигрышный(т.е. первый запрос вернулся не пустым), то ему надо зафиксировать оплату билета. Т.е. проапдейтить поля PayDT, WorkST, CurTax в таблице wins. Для этого выполняется следующий запрос:
CODE (text):
скопировать код в буфер обмена
  1.  
  2. UPDATE wins SET PayDT=40044.4448122338,WorkST=1,CurTax=0
  3. WHERE LID=1 AND DrawNum=532 AND TckNum=358015
  4.  


Так вот. ПРоблема в том что этот запрос уже выполняется по времени порядка 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 те, которые отвечают за работу с кешем. Что бы апдейты записывались не напрямую в базу(А судя по времени, у меня возникает ощущение что СУБД пишет напрямую), а через кеш. Очень надеюсь что знающие люди подскажут эти настройки.
 
 Top
Champion Супермодератор
Отправлено: 19 Августа, 2009 - 14:39:55
Post Id



Активный участник


Покинул форум
Сообщений всего: 4350
Дата рег-ции: Авг. 2008  
Откуда: Москва


Помог: 57 раз(а)
Самым быстрым образом апдейт выполнится, когда есть индексы по тому, что участвует в WHERE и нет индексов по всему остальному. Первое гораздо значительнее. Выводы делайте сами.
(Добавление)
Phantik пишет:
1) Попробовать BLOB поле заменить на текстовое ибо тип хранимых данных это позволяет сделать
Обязательно.
(Добавление)
А вообще, написано очень много.
 
 Top
Stierus Супермодератор
Отправлено: 19 Августа, 2009 - 14:57:13
Post Id



Рекордсмен по количеству сообщений за 7 дней


Покинул форум
Сообщений всего: 2132
Дата рег-ции: Дек. 2008  
Откуда: Москваль


Помог: 52 раз(а)
а экслэйн этого запроса что выдает?
 
My status
 Top
Phantik
Отправлено: 19 Августа, 2009 - 15:30:39
Post Id


Посетитель


Покинул форум
Сообщений всего: 305
Дата рег-ции: Июнь 2009  


Помог: 0 раз(а)
Champion пишет:
Самым быстрым образом апдейт выполнится, когда есть индексы по тому, что участвует в WHERE и нет индексов по всему остальному. Первое гораздо значительнее. Выводы делайте сами.
(Добавление)
Phantik пишет:
1) Попробовать BLOB поле заменить на текстовое ибо тип хранимых данных это позволяет сделать
Обязательно.
(Добавление)
А вообще, написано очень много.


BLOB на char(n) заменил. Прирост получился несущественный.
Все поля которые в WHERE являются составным Primary Key т.е. я так понимаю индексированы автоматически. Поля которые обновляются - без индексов.

Есть аналогичная структура таблиц\запросов под MSSQL и MSAccess - запрос Update работает на порядок быстрее. Пытаюсь того же добиться на MySQL.
(Добавление)
Stierus пишет:
а экслэйн этого запроса что выдает?


id 1

select_type SIMPLE

table wins

type ref

possible_keys PRIMARY

key PRIMARY

key_len 4

ref const,const

rows 103610

Extra Using Where

Я правда не знаю какой из этого следует вывод Не понял
 
 Top
Champion Супермодератор
Отправлено: 19 Августа, 2009 - 15:57:33
Post Id



Активный участник


Покинул форум
Сообщений всего: 4350
Дата рег-ции: Авг. 2008  
Откуда: Москва


Помог: 57 раз(а)
Phantik пишет:
Все поля которые в WHERE являются составным Primary Key
А, значит обновляется всего одна запись. А селект с тем же условием быстро выполняется.
И еще. Этот апдейт не вызывает каскадных апдейтов, триггеров не запускает?
(Добавление)
А может индекс пересоздать?
 
 Top
Phantik
Отправлено: 19 Августа, 2009 - 17:48:54
Post Id


Посетитель


Покинул форум
Сообщений всего: 305
Дата рег-ции: Июнь 2009  


Помог: 0 раз(а)
Прошу всех прощения за то что из мухи сделал слона. Вообщем разобрался окончательно. Правильный ответ был:

alter table wins add index xxx (LID,DrawNum,TckNum);

И все работает мгновенно.

А до этого меня приглючило что будет достаточно только PRIMARY KEY(`LID`,`DrawNum`,`Module`,`Tour`,`TckNum`)
 
 Top
Champion Супермодератор
Отправлено: 19 Августа, 2009 - 18:03:26
Post Id



Активный участник


Покинул форум
Сообщений всего: 4350
Дата рег-ции: Авг. 2008  
Откуда: Москва


Помог: 57 раз(а)
Ну не знаю, как в MySQL, но обычно, если в запросе сравниваются на = часть полей составного ключа, он используется на ура.
(Добавление)
Обычно - это я про MSSQL, Access, Firebird, Interbase, Yaffil
 
 Top
EuGen Администратор
Отправлено: 19 Августа, 2009 - 18:06:23
Post Id


Профессионал


Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007  
Откуда: Berlin


Помог: 707 раз(а)
Вот Вам вывод получается - индекс был неверен. Собственно, на них нужно было смотреть в первую очередь.
Про EXPLAIN рекомендую прочитать, полезная вещь. Так же как и про список процессов.


-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
 
 Top
Phantik
Отправлено: 19 Августа, 2009 - 18:39:25
Post Id


Посетитель


Покинул форум
Сообщений всего: 305
Дата рег-ции: Июнь 2009  


Помог: 0 раз(а)
Champion пишет:
Ну не знаю, как в MySQL, но обычно, если в запросе сравниваются на = часть полей составного ключа, он используется на ура.
(Добавление)
Обычно - это я про MSSQL, Access, Firebird, Interbase, Yaffil


Я тоже с этим разобрался только сегодня. До этого такие же таблицы с такими же ключами в асексе и МС Скуэле работали мгновенно.
 
 Top
Поиск в теме | Версия для печати
Страниц (1): [1]
Сейчас эту тему просматривают: 0 (гостей: 0, зарегистрированных: 0)
« SQL и Архитектура БД »


Все гости форума могут просматривать этот раздел.
Только зарегистрированные пользователи могут создавать новые темы в этом разделе.
Только зарегистрированные пользователи могут отвечать на сообщения в этом разделе.
  



Powered by PHP  Powered By MySQL  Powered by Nginx  Valid CSS  RSS

 
Powered by ExBB FM 1.0 RC1. InvisionExBB