Таблица выглядит так:

CREATE TABLE IF NOT EXISTS `map` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`userID` int(11) NOT NULL,
`objectID` int(11) NOT NULL,
`positionX` int(11) NOT NULL,
`positionY` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `userID` (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1057669 ;

Количество записей - 1057668
Данные 53.6 МБ
Индекс 14.5 МБ
Всего 68.1 МБ

Выполнение запроса вида "UPDATE map SET positionX=12 WHERE ID=777" на базе с нулевой нагрузкой происходит за 0.06-0.08 сек. Можно ли как-нибудь сократить это время, или это объективный предел (в смысле, оптимизировать тут просто нечего)?

В будущем планируется, что в таблице будет 40-50 млн. записей. Будут ли какие проблемы со скоростью апдейта? Индекс, по идее, вырастет примерно в 50 раз, т.е., составит 750Мб. Подозреваю, если по какой-то причине он не поместится в память (это не единственная таблица в БД), время апдейта увеличится существенно? Что можно предпринять заранее?

Ну, как сказать...

...
PRIMARY KEY (`ID`),
...

В апдейте:
UPDATE map SET positionX=12 WHERE ID=777

AkinaА каким боком тут ваш этот индекс? Используемый индекс (который PRIMARY KEY (`ID`) ) - он вообще кластерный и места на диске не занимает

Да, так и оказалось. Удалил второй индекс и действительно ноль.

Так какое резюме? 50 млн - чепуховый объем и мне даже не париться на этот счет?
Текущий размер таблицы на диске 53.6Мб, в 50 раз больше - примерно 2.5Гб.

Это просто пример запроса. Любое число от 1 до миллиона, результат тот же.

Cygapb-007Возможно, я и не прав, но (ПМСМ) поле ID в таблице MAP не может быть получено ниоткуда, кроме самой этой таблицы. Тогда каким образом в программе можно узнать, что нам требуется именно строка с id=777?

Сервер обрабатывает пакеты действий, присланные клиентом. Сначала действия накатываются на модели, если все действия валидны, измнения модели отражаются в базе. К моменту сохранения изменений модели в базе все первичные ключи всех записей, которые нужно проапдейтить, известны. Просто потому что они есть в модели.

Cygapb-007Но на вопрос, откуда в модели возник номер 777 — вы так и не ответили :)


Сейчас модель считывается из БД перед обработкой пакета запросов.

В дальнейшем планируется использовать мемкеш, чтобы считывать ее один раз при коннекте и затем лазить в бд только с апдейтами. Естественно, про негарантированное хранение в мемкеше все в курсе.

Cygapb-007Где-то в интерфейсе осущестляется связка по user_id и object_id с данными этой таблицы, вносятся изменения в какую-то строку, и в сервер посылается запрос на изменение по id этой строки (вместо изменения по user_id и object_id). Не совсем логично - ну да пусть его, это ваш проект.

На самом деле, идея не моя, но суть пакетной обработки действий через модель заключается как раз в том, что сокращается количество апдейтов бд, поскольку не пишутся промежуточные варианты. Например, пользователь userID передвигал объект objectID в перемешку с другими действиями 10 раз. Если каждому его действию будет соответствовать один запрос, то это будет 10 апдейтов. А при прогонке через модель - только один. То же самое, например, при покупке разных предметов на склад. При стандартной схеме у нас на каждую операцию один апдейт баланса в таблице пользователя и один инсерт/апдейт в таблице склада. При пакетной обработке 10 запросов на покупку разных предметов на слад у нас только один апдейт баланса в таблице пользователя и гарантированно не больше инсертов/апдейтов в таблице склада (позиции с одним itemID будут объединены в один инсерт/апдейт). А, например, в случае, когда предмет покупается на склад, а потом идет в производство, то при пакетной обработке таблица склада вообще не трогается, а при стандартном подходе - сначала будет запись прихода в таблицу склада, потом запись списания.

Короче, не все так очевидно, как кажется на первый взгляд.

Cygapb-007с идеей пакетной обработки я не спорю, но ей ни в коей мере не противоречит фиксация изменений в базе не по надуманному «номеру строки», а по «родной» связке юзера и объекта, по которой, собственно, и была доставлена в обработку исправленная запись (то есть по «естественному первичному ключу»).

Вы предлагаете мне завести дополнительный составной индекс по двум полям и искать по нему, хотя я УЖЕ знаю значение праймери кей. И что, я выиграю в скорости? Если да, я так сделаю, но я не понимаю, почему я должен выиграть в скорости. Может быть, объясните?

Cygapb-007, я так понимаю, Вы смотрите на данные с уровня БД. На самом деле, если смотреть на данные с уровня сервера приложения, то некоторые вещи выглядят иначе.

Cygapb-007Не совсем так... Я просто ну никак не могу понять - каким образом вы узнали ID. Вы так и не ответили на этот вопрос.

Нет, понятно, что к моменту, когда данные отображаются в интерфейсе, вы уже знаете все ID, - но по какому критерию они отбираются для отображения? Ну, или представлены в виде порции данных, соответствующей заданным критериям отбора, если так яснее смысл вопроса...


На самом деле, я ответил. Причем уже раза два или три. Я знаю все ID, потому что они есть в модели. Кстати, интерфейс и модель - вещи разные, даже не совсем понимаю, откуда всплыл этот "интерфейс", речь же не о сайте идет, а об игровом приложении.

Общая логика такова.

При инициализации юзера в игре из базы считываются все данные по юзеру, которыми заполняется класс User - это модель юзера. Данные модели сериализуются в json и отправляются на клиент для визуализации. Перед отправкой данных на клиент, модель в виде объекта сохраняется в хранилище мемкеш (memcache). Это позволит при следующих пакетных запросах со стороны клиента не лазить в базу за считыванием актуального состояния модели пользователя, а получать ее из мемкеша с фактически нулевыми накладными расходами (что такое memcache гуглим самостоятельно).

Пакет, поступивший от клиента, прогоняется по модели и, если все в порядке, изменения накатываются на модель, записываются в базу (формируется пакет запросов update/insert/delete, которые отрабатываются на базе) а сама модель обновляется в мемкеше и цикл отработки пакетов запросов с клиента повторяется.

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

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

Как-то так, если общими словами.

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


ЗЫ. Соответственно, когда и я упомянул про работу с данными на уровне сервера приложения - я лишь имел в виду, что за счет организации данных и использования различных механизмов хранения информации можно существенно (по факту - в несколько раз) снизить нагрузку на базу.

Cygapb-007Вопрос: а нафига в таком случае сервер базы (раз сервер приложений может хранить все-все "в себе") ?


Нет, ну можно, конечно, хранить данные в текстовых файлах...
Но если есть такой механизм, как база данных - чего бы его не использовать? :)

Cygapb-007Сервер приложений закачивает в себя все-все что есть в сервере базы, далее все пользователи работают только с сервером приложений, при валидности транзакции в модели она фиксируется в сервере базы данных.


Вот тут ошибка.
Не всю информацию, естественно, а только постоянную информацию по объектам/айтемам (она подкачивается по мере необходимости) и данные по активным пользователям в приложении. Как только пользователь выходит из приложения, его модель через некоторое время сбрасывается автоматически (если точнее - затирается другими данными)


Cygapb-007Составные индексы в БД - вредны, в том числе (14695748) KEY `userID` (`userID`)


Это теперь тоже составной индекс? :)
Индекс по userID нужен, чтобы выдернуть из таблицы map объекты конкретного юзера.


Кстати, что интересно, я так и не получил ответ на тот вопрос который изначально задавал. Перефразирую.
Может ли кто-нибудь обеспечить на своей таблице innoDB в млн записей и больше апдейт одной конкретной записи за время, скажем, меньшее, чем 0.01 сек и, если да, то как он этого добивается (какое железо, какие настройки и т.д.).

Cygapb-007Повторюсь, вопрос возник после фразы об удалении индекса

Не-не :)
Это я скопировал таблицу и удалил из нее индекс, чтобы проверить, как изменился размер индекса.
В реальной таблице индекс остался.
Но, как я понимаю, на апдейт он не влияет, только на инсерт при создании профиля нового пользователя.


Cygapb-007То есть у игрового приложения нет интерфейса?? В котором в той или иной форме отображаются данные, представленные в модели? Вопрос риторический

Тут другое.

Когда сервер приложения представляет из себя сервер сайта, он сам формирует интерфейс и он доверяет тем данным, которые идут от интерфейса (ну, с соблюдением параметров авторизации, естественно). Есть разновидности, связанные с тем же ajax'ом, но сути это не меняет.

Когда сервер входит в игровое приложение, он НЕ формирует интерфейс и НЕ доверяет данным поступающим от клиента. Поэтому никаких апдейтов по ID, поступающим от клиента быть, естественно не может. Без проверки по базе, модели или еще как. Поэтому я и говорю, что интерфейс тут ни при чем.