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