[SQL] Скорость выполнения запросов

Javekson · 13 Мар 2019

Хочется ради интереса поинтересоваться, приемлема ли такая скорость выполнения запросов?
Статистика игроков на SQLite, обновление данных идет по таймеру раз в 60 секунд.
Итого имеем.

Смутило то, что почему время идет по увеличению прям, и теперь задумываюсь, последняя скорость это скорость выполнения последнего запроса или в общем всех?
Ибо если смотреть по времени создания строки с логами, то буквально за секунду произошел апдейт.

Код:

public DataUpdateTask() {
    new aPlayersID[MAX_PLAYERS], iPlayersNum, iSysTime = get_systime();
    get_players_ex(aPlayersID, iPlayersNum, GetPlayers_ExcludeBots | GetPlayers_ExcludeHLTV);
    
    for(new i; i < iPlayersNum; i++) {
        if(iSysTime - g_aLastDataUpdate[aPlayersID[i]] >= DATA_UPDATE_TIMER) {
            DataUpdate(aPlayersID[i]);
            g_aLastDataUpdate[aPlayersID[i]] = iSysTime;
        }
    }
}


DataUpdate(id) {
    new sQuoteStringPlayerName[PLAYER_NAME_STRLEN * 2];
    SQL_QuoteString(Empty_Handle, sQuoteStringPlayerName, charsmax(sQuoteStringPlayerName), g_eData[id][pd_sName]);
    
    formatex(g_sSQLQuery, charsmax(g_sSQLQuery),
        "UPDATE players_stats SET \
        Name = '%s', \
        Kills = %d, \
        Deaths = %d, \
        Head = %d, \
        Skill = %f, \
        LastSeen = %d \
        WHERE AuthID = '%s'",
        sQuoteStringPlayerName,
        g_eData[id][pd_iKills],
        g_eData[id][pd_iDeaths],
        g_eData[id][pd_iHead],
        g_eData[id][pd_fSkill],
        g_eData[id][pd_iLastSeen],
        g_eData[id][pd_sAuthID]
    );
    
    SQL_ThreadQuery(g_hSQLTuple, "DataUpdateQueryHandler", g_sSQLQuery);
}


public DataUpdateQueryHandler(iFailState, Handle:hQuery, sError[], iErrNum, aData[], iSize, Float:fQueueTime) {
    Logging(g_sLogsDir, "stats_inform_", "^"[DataUpdateQueryHandler] Queue Time %f^"", fQueueTime); //%.2f
    
    if(iFailState != TQUERY_SUCCESS) {
        SQL_GetQueryString(hQuery, g_sSQLQuery, charsmax(g_sSQLQuery));
        QueryHandlerError(g_sSQLQuery, sError, "DataUpdateQueryHandler");
        return PLUGIN_CONTINUE;
    }
    return PLUGIN_CONTINUE;
}

voed · 13 Мар 2019

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

13 Мар 2019

Смотри что будет через 60 секунд, когда пойдет следующая пачка запросов

Javekson · 13 Мар 2019

voed написал(а):
Смотри что будет через 60 секунд, когда пойдет следующая пачка запросов

Что смотреть то? Все то же самое, отправляется новая пачка запросов и так циклично.

В 20:07

В 20:08

Если бы каждый запрос выполнялся действительно за ~0.5 секунд, то в целом пачка бы отправилась за ~10 секунд.
Скорее всего так и получается, что ждет своей очереди, итого все запросы выполнились за 0.8 секунд.

81x08 · 13 Мар 2019

Javekson написал(а):
последняя скорость это скорость выполнения последнего запроса или в общем всех?

Всех запросов вместе.

Javekson написал(а):
действительно за ~0.5 секунд

0.05*

fl0wer · 13 Мар 2019

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

13 Мар 2019

А долго обрабатывается скорее всего из-за обновления 32 раза по AuthID. Попробуй при коннекте сохранять id игрока из бд и через него обновлять, потому что сравнение строк AuthID дорогое удовольствие.

Javekson · 13 Мар 2019

fl0wer, ты имеешь ввиду добавить поле id как AUTO_INCREMENT ?

fl0wer · 13 Мар 2019

Javekson написал(а):
fl0wer, ты имеешь ввиду добавить поле id как AUTO_INCREMENT ?

Да

Javekson · 13 Мар 2019

81x08 написал(а):
Всех запросов вместе.

0.05*

Если сложить в сумме скорость всех запросов и разделить на их общее количество, то получится ~0.5

13 Мар 2019

fl0wer, 1. Ну, получать id при коннекте я опять таки буду по AuthID, иначе как я его получу еще? 2. Если игрока нету в базе, как я ему присвою id(при добавлении нового игрока) который мне неизвестен будет или я сейчас не понимаю что-то?

fl0wer · 13 Мар 2019

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

13 Мар 2019

Javekson написал(а):
fl0wer, ты имеешь ввиду добавить поле id как AUTO_INCREMENT ?

ещё и индекс повесить на authid и на id

Javekson · 13 Мар 2019

fl0wer написал(а):
ещё и индекс повесить на authid и на id

PRIMARY KEY ?

fl0wer · 13 Мар 2019

PRIMARY KEY (`id`),
INDEX (`auth_id`)

Javekson · 14 Мар 2019

fl0wer, По-моему в SQLite этот индекс не работает, ибо ошибку выдало о неверном синтаксисе.

14 Мар 2019

Кажется все же придется на MySQL переходить, дурная затея делать SQLite

BlackSignature · 14 Мар 2019

Javekson написал(а):
fl0wer, По-моему в SQLite этот индекс не работает

Работает (во всяком случае, создавать можно). Пример ниже создаст уникальный индекс (если я правильно понимаю, что это индекс) для authid (т.е. подразумевается, что каждый конкретный `authid` может быть только в одной записи / таблица), что по идее тебе и нужно. Чтобы понять, какой выигрыш в скорости это даёт (и даёт ли), нужно создать 2 идентичные таблицы, с индексом и без, наполнить их идентичной инфой (хотя бы 5к записей), и затем производить тестовые выборки по authid (т.е. "... WHERE `authid` = ..."), сравнивая скорость выполенения.

Код:

CREATE TABLE IF NOT EXISTS `test_table` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  `authid` TEXT NOT NULL UNIQUE,
  `kills` INTEGER NOT NULL,
  `deaths` INTEGER NOT NULL,
  `last_seen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Javekson · 14 Мар 2019

BlackSignature, проверил с индексом и без, выигрыша в скорости нету, 32 запроса выполняются так же ~1 сек.
Правда с ID полем не проверял, не создавал его еще пока что.

BlackSignature · 14 Мар 2019

Код:

CREATE TABLE `test_table` (
					`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
					`authid` TEXT NOT NULL,
					`kills` INTEGER NOT NULL,
					`deaths` INTEGER NOT NULL,
					`last_seen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX "authid"
ON "test_table" ("authid" ASC);

Есть ещё такой вариант (редактор выдал). Возможно в 1-ом варианте совсем и не индекс получается, а просто устанавливается требование к уникальности столбца `authid`

Gudaus · 14 Мар 2019

BlackSignature,

BlackSignature написал(а):
Код:

CREATE TABLE `test_table` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, `authid` TEXT NOT NULL, `kills` INTEGER NOT NULL, `deaths` INTEGER NOT NULL, `last_seen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE UNIQUE INDEX "authid" ON "test_table" ("authid" ASC);

Есть ещё такой вариант (редактор выдал). Возможно в 1-ом варианте совсем и не индекс получается, а просто устанавливается требование к уникальности столбца `authid`

Это так, индекс надо создавать отдельно.
Насчёт CREATE UNIQUE INDEX "authid" не уверен, authid уникально ли? Может, просто CREATE INDEX "authid" ?

CrazyHackGUT · 14 Мар 2019

Javekson написал(а):
32 запроса выполняются так же ~1 сек.

BlackSignature написал(а):
Чтобы понять, какой выигрыш в скорости это даёт (и даёт ли), нужно создать 2 идентичные таблицы, с индексом и без, наполнить их идентичной инфой (хотя бы 5к записей)

BlackSignature написал(а):
хотя бы 5к записей

Индексы дают прирост на выборку с условиями (WHERE), но небольшую потерю на запись, в любом случае.

Javekson · 15 Мар 2019

CrazyHackGUT написал(а):
Индексы дают прирост на выборку с условиями (WHERE), но небольшую потерю на запись, в любом случае.

именно на запись? или update тоже?

15 Мар 2019

Поговорил с BlackSignature и понял, что поиск по поле AuthID с индексом будет осуществляться гораздо быстрее.
Мне кажется статья которую я читал немного недоработанная полнотой информации по этому поводу.

Без индексов поиск осуществляется как:
EXPLAIN QUERY PLAN SELECT * FROM players_stats WHERE AuthID = 'STEAM_1:0:357741490'
SCAN TABLE players_stats
Что является самым глупым поиском по таблице, перебирая и сравнения все значения в базе.

А вот по индексу:
EXPLAIN QUERY PLAN SELECT * FROM players_stats WHERE AuthID = 'STEAM_1:0:357741490'
SEARCH TABLE players_stats USING INDEX indexID (AuthID=?)

Поиск осуществился по индексу, что гораздо быстрее и эффективнее особенно при объемных базах.

Так же BlackSignature приложил видео для визуального понимания.

fantom · 15 Мар 2019

Javekson, Так об этих индексах я говорил еще в чате. Не надо добавлять никакие ID, автоинкременты. Достаточно установить индекс. Но все же есть еще одно поле skill по которому идет поиск. Стоит также расмотреть возможность раставить индекс там. И поздравляю. Ты один из немногих, кто пользуется EXPLAIN

Javekson · 15 Мар 2019

fantom написал(а):
Так об этих индексах я говорил еще в чате.

Я знаю, но тогда я не понимал этого, пока визуально не понял как это работает.

fantom написал(а):
Не надо добавлять никакие ID, автоинкременты

я тоже об этом задумался, что поле id будет не нужен в принципе, если есть индекс на SID'e

fantom написал(а):
И поздравляю. Ты один из немногих, кто пользуется EXPLAIN

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