Как оптимизировать запрос в MySQL?

Question

Есть большой список имен, я заношу эти имена во временную таблицу

using var connect = DBUtils.GetDBConnection(_DbName, _DbUserName, _DbPassword);
await connect.OpenAsync().ConfigureAwait(false);
string drop = $"DROP TEMPORARY TABLE IF EXISTS {_DbName}.params";
using (var dropCmd = new MySqlCommand(drop, connect))
{
    await dropCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
var createSql = $"CREATE TEMPORARY TABLE {_DbName}.params (Name VARCHAR(30))";
using (var createCmd = new MySqlCommand(createSql, connect))
{
    await createCmd.ExecuteNonQueryAsync().ConfigureAwait(false); ;
}

var insertSql = $"INSERT INTO {_DbName}.params (Name) values (@name)";
using (var insertCmd = new MySqlCommand(insertSql, connect))
{
    var nameParam = insertCmd.Parameters.Add("name", MySqlDbType.VarChar);
    using (var tran = await connect.BeginTransactionAsync().ConfigureAwait(false))
    {
        for (int i = 0; i < Names.Count; i++)
        {
            nameParam.Value = Names[i];
            await insertCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
        }
    await tran.CommitAsync().ConfigureAwait(false);
    }
}

И использую временную таблицу для поиска имен в основной таблице и на этот моменте возникает исключение "Fatal error encountered during command execution" из-за того что процесс зависает

var updateSql = $"UPDATE {_DbName}.dbUserTable SET IsOnline = 1 WHERE Name IN (SELECT Name FROM {_DbName}.params)";
using (var updateCmd = new MySqlCommand(updateSql, connect))
{
    await updateCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
    //тут зависает
}
updateSql = $"UPDATE {_DbName}.dbUserTable SET IsOnline = 0 WHERE Name NOT IN (SELECT Name FROM {_DbName}.params)";
using (var updateCmd = new MySqlCommand(updateSql, connect))
{
    await updateCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
await connect.CloseAsync().ConfigureAwait(false);

Можно как-то оптимизировать этот запрос или нужно сделать по другому ? п.с. количество строк в списке ~60к, количество строк в основной таблице ~20к.

Сделал тестовый запрос в mysql бенче

drop table if exists foo;
create table foo
(
   id int unsigned not null auto_increment primary key,
   val varchar(5) not null default 'test'
)
engine=innodb;
drop procedure if exists load_foo_test_data;

delimiter #
create procedure load_foo_test_data()
begin
    declare v_max int unsigned default 10000;
    declare v_counter int unsigned default 0;
    truncate table foo;
    start transaction;
    while v_counter < v_max do
       insert into foo (val) values ('test');
       set v_counter=v_counter+1;
    end while;
    commit;
end #
delimiter ;
call load_foo_test_data();
UPDATE lec.dbUserTable SET IsOnline = 1 WHERE Name IN (SELECT val 
FROM foo);

на выполнение запроса UPDATE уходит аж 17 секунд, при этом в таблице lec.dbUserTable всего 4000 записей

ответ: из-за старой версии сервера ubuntu (16.04) и mysql 5.7 запрос выполнялся очень долго, не свежей версии ubuntu 22.04 и mysql 8.0, 40кк сравнений выполняются за 0.14 сек.

Answer 1

Измените структуру таблицы на

CREATE TABLE foo (
   val VARCHAR(5) PRIMARY KEY DEFAULT 'test'
) ENGINE=Memory;

Для проверки на вхождение в список дубликаты с списке только мешают.

Измените

insert into foo (val) values ('test');

на

INSERT IGNORE INTO foo (val) VALUES ('test');

Соответственно дубликаты и не вставляем.

Замените

UPDATE lec.dbUserTable SET IsOnline = 1 WHERE Name IN (SELECT val 
FROM foo);

на

UPDATE lec.dbUserTable bar
JOIN foo ON bar.Name = foo.val
SET bar.IsOnline = 1

Из всех способов проверки на вхождение WHERE IN - пожалуй, самый медленный.

Оцените количество записей, который обновляются запросом. Если это менее 5-7% от общего числа записей - убедитесь, что в таблице имеется индекс, в котором Name является префиксом индексного выражения.

БЛОГ НА HUSL

Как оптимизировать запрос в MySQL?

Ответы (1 шт):