Delta RuBoard Member Редактировать | Профиль | Сообщение | ICQ | Цитировать | Сообщить модератору Доброго времени суток.     Есть таблица, в которой 2,726,505 строк.     Поля: id group_id user_id      time      vcount   все ячейки имеют тип  INT (10).   к таблице делаются запросы по полям group_id && time / group_id && vcount, group_id && user_id && vcount   Подскажите пожалуйста, как правильно для нее расставить индексы? думаю стоит отметить то, что данные довольно часто изменяются, например добавляются новые ряды или обновляются уже существующие.     и иногда бывает необходимость делать выборку например по GROUP BY user_id. ---------- мой маленький блог Создание on-line игр Всего записей: 382 | Зарегистр. 21-06-2005 | Отправлено: 15:24 24-03-2011 | Исправлено: Delta RuBoard, 15:43 24-03-2011

myo Member Редактировать | Профиль | Сообщение | ICQ | Цитировать | Сообщить модератору При вставке и обновлении теоретически можно забить на индексы, если скорость при этом не важна. Индексы я бы поставил на все поля, далее эксплейн нужных запросов, находим узкие места, дописываем индексы по необходимости. Всего записей: 270 | Зарегистр. 09-09-2002 | Отправлено: 21:24 24-03-2011

Delta RuBoard Member Редактировать | Профиль | Сообщение | ICQ | Цитировать | Сообщить модератору скорость очень важна, к таблице идет очень много запросов в секунду =\ ---------- мой маленький блог Создание on-line игр Всего записей: 382 | Зарегистр. 21-06-2005 | Отправлено: 06:24 25-03-2011

myo Member Редактировать | Профиль | Сообщение | ICQ | Цитировать | Сообщить модератору Тогда: 1. таблички надо сделать MyISAM, если они другого типа; 2. Ключики на все поля; 3. Парные ключики для выборок по полям; 4. Порядок ключей важен, т.е. в ключе поля должны идти в том же порядке, что и в запросе; 5. Необходимо избегать REPLACE'ов, лучше делать INSERT ... ON DUPLICATE KEY UPDATE Вот такие вот рекомендации, ну и EXPLAIN делать нужно, чтоб видеть узкие места.   Добавлено: 2.7кк записей - не так уж много, я сейчас работаю с таблицами более 10кк записей - статистика летает Всего записей: 270 | Зарегистр. 09-09-2002 | Отправлено: 09:27 25-03-2011

Delta RuBoard Member Редактировать | Профиль | Сообщение | ICQ | Цитировать | Сообщить модератору myo, спасибо! а можете более подробно рассказать почему именно MyISAM ? Почему не InnoDB?   попарные ключи это   KEY `group_id` (`group_id`,`user_id`) ? важен порядок ключей или порядок ячеек в ключе?   то есть, что важнее   KEY `group_id1` (`group_id`,`user_id`), KEY `group_id2` (`group_id`,`vcount`), KEY `group_id3` (`group_id`,`time`), или KEY `group_id1` (`user_id`,`group_id`) ?   Цитата: 5. Необходимо избегать REPLACE'ов, лучше делать INSERT ... ON DUPLICATE KEY UPDATE   Вот такие вот рекомендации, ну и EXPLAIN делать нужно, чтоб видеть узкие места.     можно по этому пункту более подробно своими словами?     ---------- мой маленький блог Создание on-line игр Всего записей: 382 | Зарегистр. 21-06-2005 | Отправлено: 20:12 25-03-2011 | Исправлено: Delta RuBoard, 20:14 25-03-2011

myo Member Редактировать | Профиль | Сообщение | ICQ | Цитировать | Сообщить модератору MyISAM быстрее работает на вставку, нежели InnoDB, зато InnoDB быстрее делает выборки, тут вам надо решить самому, какой из движков использовать. Реплейсы юзают ключи всегда, т.е. дополнительно нагружают табличку, а нам как раз это не нужно. Вообще, это я написал для случая, если вы используете REPLACE'ы. По поводу ключей - порядок полей в ключе желательно, чтобы соответствовал порядку полей в таблице, соответственно, чтобы в запросах эти поля в WHERE следовали бы в том же порядке (желательно). Всего записей: 270 | Зарегистр. 09-09-2002 | Отправлено: 22:26 25-03-2011

Delta RuBoard Member Редактировать | Профиль | Сообщение | ICQ | Цитировать | Сообщить модератору myo, еще раз спасибо. а можете подсказать пример правильного INSERT ... ON DUPLICATE KEY UPDATE для такого инсерта   Код: INSERT INTO table SET group_id = ".$_SESSION['pageID'].", group_id = ".$_SESSION['userID'].", time = ".time().", vcount = 1   то есть, если уже есть запись с таким group_id и user_id то необходимо просто сделать апдейт ячейки vcount и time   сейчас у таблицы есть кулючи PRIMARY (group_id) и group_id ( group_id, user_id) ---------- мой маленький блог Создание on-line игр Всего записей: 382 | Зарегистр. 21-06-2005 | Отправлено: 01:51 26-03-2011 | Исправлено: Delta RuBoard, 01:51 26-03-2011

myo Member Редактировать | Профиль | Сообщение | ICQ | Цитировать | Сообщить модератору Во-первых у вас ошибка в коде, два раза group_id, скорее всего во втором случае user_id. Во-вторых, при наличии уникального или первичного ключа по полям group_id и user_id: Код: INSERT INTO table SET group_id = ".$_SESSION['pageID'].", user_id = ".$_SESSION['userID'].", time = ".time().", vcount = 1 ON DUPLICATE KEY UPDATE time = ".time().", vcount = 1   Всего записей: 270 | Зарегистр. 09-09-2002 | Отправлено: 23:30 26-03-2011

Delta RuBoard Member Редактировать | Профиль | Сообщение | ICQ | Цитировать | Сообщить модератору Хм.. Странно как то себя mysql ведет..   Есть таблица     Цитата: CREATE TABLE IF NOT EXISTS `users` (   `user_id` int(11) NOT NULL,   `vcount` int(11) NOT NULL default '1',   `birth` varchar(10) NOT NULL,   PRIMARY KEY  (`user_id`),   KEY `birth` (`user_id`,`birth`) )   В ней, на данный момент 1,124,487 записей.     Время запроса   Цитата: INSERT INTO users SET user_id = ".$_SESSION['userID']." ON DUPLICATE KEY UPDATE vcount = vcount + 1     5 секунд   также есть таблица, 16,158,640 записей   Цитата: CREATE TABLE IF NOT EXISTS `user_2_page` (   `id` int(11) NOT NULL auto_increment,   `user_id` int(11) NOT NULL,   `page_id` int(11) NOT NULL,   `time` int(11) NOT NULL,   PRIMARY KEY  (`id`),   KEY `user_id` (`user_id`,`page_id`) )     время выполнения запроса   Цитата: INSERT INTO user_2_page SET user_id = ".$_SESSION['userID'].", page_id = ".$_SESSION['pageID'].", time = ".time()."   30 секунд       можно ли как то вылечить? ---------- мой маленький блог Создание on-line игр Всего записей: 382 | Зарегистр. 21-06-2005 | Отправлено: 14:25 08-04-2011 | Исправлено: Delta RuBoard, 14:35 08-04-2011

ak13boda Junior Member Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Надеюсь, тесты проводились без нагрузки на сервера? По существу: 1. varchar(10) смело меняйте на char(10) для первой таблицы 2. OPTIMIZE TABLE для обеих таблиц   Имейте в виду, обе операции могут сделать таблицы недоступными на довольно длительное время. Всего записей: 93 | Зарегистр. 23-11-2004 | Отправлено: 14:51 09-04-2011

Delta RuBoard Member Редактировать | Профиль | Сообщение | ICQ | Цитировать | Сообщить модератору ak13boda, спасибо!   Цитата: Надеюсь, тесты проводились без нагрузки на сервера? в том то и проблема, что пока нет нагрузок и проблем тоже нет.     сложность заключается в том, что к обеим таблицам происходить очень большое количество запросов. на данный момент их число может достигать до 10-20 в секунду (речь идет именно про инсерт запросы).   сделал небольшой обход, путем занесения данных в временную таблицу, и последующим переносом их через INSERT INTO ... ON DUPLICATE KEY UPDATE ... с помощью крона 1 раз в минуту, но проблема решилась только частично. то есть опять же, сервер нормально справляется пока не начинается пик тайм. после этого он начинает дико тупить, и доходит до того, что вместо 1000 временных записей в таблице со временем собирается десятки тысяч, сегодня дошло до 50к записей, что выполняется по 2 часа времени.     единственное, что еще может давать проблемы с этими таблицами - периодические запросы SELECT с них, во время инсертов, но не знаю, в этом ли проблема, либо же в чем то другом...     оптимайз тейбл на сколько сильно может помочь в данной ситуации? так как действительно время выполнения его будет довольно длительным, и мало того, еще и приведет к большой нагрузке на MySQL, что, я пологаю, может повлечь трудности для пользователей.   на данный момент думаю на счет разбиения данных по отдельным таблицам для каждой page_id (для второго примера), и переносом их в отдельную БД, что в дальнейшем времени можно будет перенести на отдельный сервер, но не уверен в правильности решения, так как подобную систему делал изначально, но в следствии чего MySQL начал ругать на большое количество одновременно открытых таблиц, и немного подтормаживал... хотя может и не из-за этого, точно сейчас сказать не могу, так как кроме изменения структуры БД так же изменил и алгоритмы кода, поэтому нагрузка уменьшилась довольно сильно, и отделить что на что повлияло не смог... ---------- мой маленький блог Создание on-line игр Всего записей: 382 | Зарегистр. 21-06-2005 | Отправлено: 00:14 10-04-2011 | Исправлено: Delta RuBoard, 00:18 10-04-2011

Компьютерный форум Ru.Board » Интернет » Web-программирование » Ключи для больших MySQL таблиц

Переход по форумам  Закладки   » Компьютеры Цифровое изображение Программы Microsoft Windows UNIX Другие OS Hardware В помощь сисадмину Прикладное программирование Игры Форумные игры   » Интернет Web-программирование В помощь вебмастеру Графика Хостинг Зацените-ка!   » Тематические Ikonboard v.2 Ikonboard v.3 Invision Board Другие форумы PHP-Nuke и другие порталы Мобила   » Общие Спорт Флейм Темы по региональным встречам Музыка и Кино Юмор Литература и Живопись   » Ru.Board Новости Помощь по форуму   » Андеграунд eBookz Варезник Раздачи и акции Мобильный варезник Андеграунд Игры и фильмы   » Специальные Тестирование