Benchmark
A medição de desempenho é baseada no tempo gasto para receber uma resposta dos exports.
As velocidades reais das consultas serão relatadas na interface de depuração e no console do servidor com mysql_debug
ativado.
As velocidades podem variar consideravelmente com base no hardware do sistema, configurações do banco de dados, versão do banco de dados e a carga de trabalho atual.
Script
Este script testa os tempos de exportação ao usar o runtime Lua 5.4 com a sintaxe lib/MySQL.lua
.
local function execQuery(msg, fn, query, parameters)
local start = os.nanotime()
local result = fn(query, parameters)
local finish = os.nanotime()
print()
print(msg)
print('Executou ' .. (type(query) == 'string' and 1 or #query) .. ' consultas em ' .. (finish - start) / 1e6 .. 'ms')
return result
end
CreateThread(function()
local initTable = {
'DROP TABLE `test_table`',
[[CREATE TABLE `test_table` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL DEFAULT '0',
`identifier` VARCHAR(50) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
)]],
'TRUNCATE `test_table`',
'ALTER TABLE `test_table` AUTO_INCREMENT = 1',
}
execQuery('Inicializando test_table', MySQL.transaction.await, initTable)
local queries = {
{ query = 'INSERT INTO `test_table` (identifier) VALUES (?)', values = { 'abcdef1' }},
{ query = 'UPDATE `test_table` SET `username` = ? WHERE `id` = LAST_INSERT_ID()', values = { 'bob1' }},
}
execQuery('{ query: string, values: string }[] transação', MySQL.transaction.await, queries)
queries = {
{ 'INSERT INTO `test_table` (identifier) VALUES (?)', { 'abcdef2' } },
{ 'UPDATE `test_table` SET `username` = ? WHERE `id` = LAST_INSERT_ID()', { 'bob2' } },
}
execQuery('[string, any[]][] transação', MySQL.transaction.await, queries)
queries = {
'INSERT INTO `test_table` (identifier) VALUES ("abcdef3")',
'UPDATE `test_table` SET `username` = "bob3" WHERE `id` = LAST_INSERT_ID()',
}
execQuery('string[] transação', MySQL.transaction.await, queries)
local insertUsers = {}
for i = 1, 10000 do
insertUsers[i] = { 'INSERT INTO `test_table` (username, identifier) VALUES (?, ?)',
{ 'Testuser_' .. i, 'abcdef' .. i } }
end
execQuery('inserir ' .. #insertUsers .. ' test users', MySQL.transaction.await, insertUsers)
local selectUserIds = {}
for i = 1, 10000 do
if i % 4 == 0 then
selectUserIds[math.tointeger(i / 4)] = { 'abcdef' .. i }
end
end
execQuery('selecionar todo 4º userid', MySQL.prepare.await, 'SELECT `id` FROM `test_table` WHERE `identifier` = ? LIMIT 1', selectUserIds)
local insertid = execQuery('inserir', MySQL.insert.await, 'INSERT INTO `test_table` (identifier) VALUES (?)', { 'abcdef' })
local update = execQuery('atualizar', MySQL.update.await, 'UPDATE `test_table` SET `username` = ? WHERE `id` = ?', { 'bobby', insertid })
local scalar = execQuery('escala', MySQL.scalar.await, 'SELECT `username` FROM `test_table` WHERE `id` = ?', { insertid })
local single = execQuery('único', MySQL.single.await, 'SELECT * FROM `test_table` WHERE `id` = ?', { insertid })
print(json.encode(execQuery('preparar', MySQL.prepare.await, 'SELECT `username` FROM `test_table` WHERE `id` = ?', { insertid })))
print(insertid, update, scalar, json.encode(single))
execQuery('consulta', MySQL.query.await, 'SELECT `username` FROM `test_table` WHERE `id` = ? LIMIT 1', { 419 })
end)
Resultados
[script:test] initialise test_table
[script:test] Executed 4 queries in 42.7789ms
[script:test]
[script:test] { query: string, values: string }[] transaction
[script:test] Executed 2 queries in 8.0145ms
[script:test]
[script:test] [string, any[]][] transaction
[script:test] Executed 2 queries in 4.0278ms
[script:test]
[script:test] string[] transaction
[script:test] Executed 2 queries in 3.4743ms
[script:test]
[script:test] insert 10000 test users
[script:test] Executed 10000 queries in 2064.211ms
[script:test]
[script:test] select every 4th userid
[script:test] Executed 1 queries in 2803.7134ms
[script:test]
[script:test] insert
[script:test] Executed 1 queries in 2.8743ms
[script:test]
[script:test] update
[script:test] Executed 1 queries in 2.8675ms
[script:test]
[script:test] scalar
[script:test] Executed 1 queries in 1.5387ms
[script:test]
[script:test] single
[script:test] Executed 1 queries in 1.3389ms
[script:test]
[script:test] prepare
[script:test] Executed 1 queries in 1.6577ms
[script:test] "bobby"
[script:test] 10004 1 bobby {"identifier":"abcdef","username":"bobby","id":10004}
[script:test]
[script:test] query
[script:test] Executed 1 queries in 1.1588ms