Запрос MySQL 8.4 невероятно медленный, несмотря на индексацию ⇐ MySql

[Anonymous]

Я работаю над проектом Laravel 12 под управлением MySQL 8.4. Мой проект содержит модель ApplicationFingerprint с несколькими столбцами, предназначенными для выявления «подозрительных потенциальных клиентов» с помощью некоторых основных запросов.
По мере поступления потенциальных клиентов данные о них сохраняются в этой таблице, включая их дату рождения, номер мобильного телефона, банковский счет/сортировку и некоторые другие поля.
Затем системе необходимо найти все «подозрительные потенциальные клиенты» и количество каждой комбинации, например, для дедупликации по их «банку». и «мобильный» в течение определенного периода и дайте мне количество для каждой строки.
Эти данные затем передаются в экспортер CSV, где имеются сотни тысяч строк. Длина этой таблицы составляет 5 миллионов.
Для оптимизации был создан составной индекс для созданного_on, мобильного телефона и банка, и этот индекс действительно выбирается, но его запуск занимает несколько минут, хотя на самом деле, учитывая, что индекс используется, должно быть значительно быстрее.
Я не могу сейчас сильно изменить индексацию из-за размера, но какую оптимизацию я мог бы сделать, чтобы запрос выполнялся быстрее, если я удалю группу by и наличие, то это почти мгновенно, но тогда у меня нет подсчетов.
Я ожидаю, что это займет менее 3 секунд на 100 строк.
Вот миграция

Код: Выделить всё

Schema::create('application_fingerprints', function (Blueprint $table) {
$table->id();
$table->foreignId('application_id')->constrained()->cascadeOnUpdate()->cascadeOnDelete();
$table->foreignId('product_id')->constrained()->cascadeOnUpdate();
$table->string('email')->index();
$table->string('mobile', 20)->nullable()->index();
$table->string('bank_account', 25)->nullable();
$table->string('bank_sort', 15)->nullable();

match (DB::connection($this->getConnection())->getDriverName()) {
'mariadb', 'mysql' => $table->string('bank', 40)->virtualAs("CONCAT(`bank_account`, '', `bank_sort`)"),
'sqlite' => $table->string('bank')->nullable()
};

$table->date('birthday')->nullable();
$table->ipAddress('request_ipv4')->nullable();
$table->ipAddress('request_ipv6')->nullable();
$table->text('request_user_agent')->nullable();
$table->string('request_fingerprint')->nullable();
$table->date('created_on')->useCurrent();
$table->timestamps();

// extra indexes
$table->index(['created_on', 'mobile', 'birthday']);
$table->index(['created_on', 'mobile', 'bank']);
$table->index(['created_on', 'bank']);

// individual indexes
$table->index('application_id');
$table->index('created_on');
});

Вот красноречивый запрос:

Код: Выделить всё

$from = Carbon::parse(Arr::get($filters, 'from', 'now'))->toDateString();
$to = Carbon::parse(Arr::get($filters, 'to', 'now'))->toDateString();

$columns = match (Arr::get($filters, 'group_by', 'bank') == 'bank') {
true => ['mobile', 'bank'],
default => ['mobile', 'birthday']
};

return ApplicationFingerprint::query()->select(array_merge($columns, [
DB::raw('COUNT(id) AS dedupe_count'),
DB::raw('MAX(application_id) AS app_id')
]))->whereBetween('created_on', [
$from, $to
])->when(filled(Arr::get($filters, 'product_id')), function ($query) use ($filters) {
$query->where('product_id', $request->input('product_id'));
})->when(filled(Arr::get($filters, 'phone')), function ($query) use ($filters) {
if (filled(Arr::get($filters, 'phone'))) {
if ($request->boolean('is_input_addon_wildcard_phone_checked')) {
$query->where('mobile', 'like', '%' . Arr::get($filters, 'phone') .  '%');
} elseif ($request->boolean('is_input_addon_multiple_phone_checked')) {
$phones = Str::of(Arr::get($filters, 'phone'))->split('/[\s,]+/')->filter()->all();
$query->whereIn('mobile', $phones);
} else {
$query->where('mobile', Arr::get($filters, 'phone'));
}
}
})->when(filled(Arr::get($filters, 'bank')), function ($query) use ($filters) {
$query->where('bank', Arr::get($filters, 'bank'));
})->when(filled(Arr::get($filters, 'birthday')), function ($query) use ($filters) {
$query->where('birthday', Arr::get($filters, 'birthday'));
})->groupBy($columns)->having(
'dedupe_count', '>=', intval(Arr::get($filters, 'occurences', 2))
);

Не все поля заполнены, поэтому генерируется запрос:

Код: Выделить всё

select
mobile,
bank,
COUNT(id) AS dedupe_count,
MAX(application_id) AS app_id
from
application_fingerprints
where
created_on between '2025-10-01' and '2025-10-31'
group by
mobile,
bank
having
dedupe_count >= 2
limit 100 offset 0

Выбран составной индекс:

Код: Выделить всё

$table->index(['created_on', 'mobile', 'bank']);

Ограничение и смещение связано с тем, что это часть фрагментированного экспорта CSV, но даже без них выполнение занимает несколько минут


Подробнее здесь: https://stackoverflow.com/questions/798 ... e-indexing