AI-assisted optymalizacja SQL – LLM + EXPLAIN + Blackfire, 5x przyspieszenie

Przez lata diagnostyka wolnych zapytań SQL w Magento 2 wyglądała tak: slow query log, EXPLAIN, długie wpatrywanie się w plan zapytania. W 2026 mam nową warstwę: wklejam EXPLAIN output i schemat tabeli do LLM i dostaję sensowne sugestie indeksów i przepisania zapytania w kilka sekund. Nie zastępuje rozumienia SQL, ale dramatycznie przyspiesza cykl diagnoza-hipoteza-weryfikacja. Pokazuję workflow na realnym przykładzie z projektu Magento 2.

Problem – wolna strona kategorii z filtrowaniem

Projekt: sklep B2B, 80 000 produktów, 120 atrybutów filtrowania w layered navigation. Strona kategorii ładuje się 4.2 sekundy. Blackfire wskazuje winowajcę:

# Blackfire profile - top 5 SQL queries
# 1. SELECT ... FROM catalog_product_index_eav ... - 1240ms - 847 calls
# 2. SELECT ... FROM catalog_product_entity_decimal ... - 890ms - 412 calls
# 3. SELECT ... FROM cataloginventory_stock_status ... - 340ms - 847 calls
# 4. SELECT ... FROM catalog_category_product_index ... - 280ms - 1 call
# 5. SELECT ... FROM eav_attribute_option_value ... - 195ms - 234 calls

# Włącz slow query log i zbierz konkrety
# my.cnf: slow_query_log=1, long_query_time=0.5
tail -100 /var/log/mysql/slow.log | grep -A 20 "catalog_product_index_eav"

# Jedno z powtarzających się zapytań - 847 razy w jednym page load!
SELECT DISTINCT e.entity_id
FROM catalog_product_entity e
INNER JOIN catalog_product_index_eav idx
    ON idx.entity_id = e.entity_id
    AND idx.attribute_id = 142
    AND idx.store_id = 1
WHERE idx.value IN (1847, 1848, 1851)
  AND e.entity_id IN (
      SELECT entity_id FROM catalog_category_product_index
      WHERE category_id = 58 AND store_id = 1
  )
ORDER BY e.entity_id;

# Query time: 1.47s
# Rows examined: 2,847,291

Workflow z LLM – diagnoza w minuty

# Prompt który wysyłam do Claude:
# ---
# Mam wolne zapytanie SQL w Magento 2 (MySQL 8.0):
#
# [wklejam zapytanie]
#
# EXPLAIN output:
# [wklejam EXPLAIN FORMAT=JSON output]
#
# Rozmiary tabel:
# catalog_product_index_eav: 12.4M wierszy
# catalog_category_product_index: 380k wierszy
# catalog_product_entity: 80k wierszy
#
# Aktualnie istniejące indeksy:
# [SHOW INDEX FROM catalog_product_index_eav]
#
# Pytania:
# 1. Dlaczego 2.8M wierszy przy 80k produktach?
# 2. Jaki indeks composite by pomógł?
# 3. Czy zapytanie można przepisać żeby było szybsze?
# ---

# Odpowiedź LLM (streszczenie):
# 1. Brak composite indeksu na (attribute_id, store_id, value) – MySQL skanuje
#    indeks na attribute_id, potem filtruje store_id i value wiersz po wierszu
# 2. Sugerowany indeks: CREATE INDEX ... ON catalog_product_index_eav (attribute_id, store_id, value, entity_id)
# 3. Subquery IN (...SELECT...) można zastąpić JOIN-em który MySQL planuje lepiej

Weryfikacja i implementacja sugestii

# Zawsze weryfikuj sugestie LLM przez EXPLAIN przed wdrożeniem na produkcję!

# Sprawdź czy indeks już nie istnieje
SHOW INDEX FROM catalog_product_index_eav;
# idx_attribute_id_value - istnieje ale bez store_id i entity_id

# Przetestuj nowy indeks na kopii bazy deweloperskiej
EXPLAIN SELECT DISTINCT e.entity_id
FROM catalog_product_entity e
INNER JOIN catalog_product_index_eav idx
    ON idx.entity_id = e.entity_id
    AND idx.attribute_id = 142
    AND idx.store_id = 1
WHERE idx.value IN (1847, 1848, 1851)
  AND e.entity_id IN (
      SELECT entity_id FROM catalog_category_product_index
      WHERE category_id = 58 AND store_id = 1
  );
# rows: 2847291, Extra: Using filesort

# Po dodaniu indeksu composite:
ALTER TABLE catalog_product_index_eav
    ADD INDEX idx_attr_store_value_entity
    (attribute_id, store_id, value, entity_id);

EXPLAIN SELECT ... -- ten sam query
# rows: 847, Extra: Using index
# 3360x mniej rows examined!

<?php

declare(strict_types=1);

// Indeks przez Magento setup script - zamiast ręcznego ALTER TABLE
namespace Vendor\Module\Setup\Patch\Data;

use Magento\Framework\Setup\Patch\DataPatchInterface;
use Magento\Framework\App\ResourceConnection;

class AddEavIndexPatch implements DataPatchInterface
{
    public function __construct(
        private ResourceConnection $resourceConnection
    ) {}

    public function apply(): static
    {
        $connection = $this->resourceConnection->getConnection();
        $tableName  = $this->resourceConnection->getTableName('catalog_product_index_eav');

        // Sprawdź czy indeks już istnieje
        $indexes = $connection->getIndexList($tableName);

        if (!array_key_exists('IDX_ATTR_STORE_VALUE_ENTITY', $indexes)) {
            $connection->addIndex(
                $tableName,
                $connection->getIndexName($tableName, ['attribute_id', 'store_id', 'value', 'entity_id']),
                ['attribute_id', 'store_id', 'value', 'entity_id']
            );
        }

        return $this;
    }

    public static function getDependencies(): array { return []; }
    public function getAliases(): array { return []; }
}

Przepisanie zapytania – JOIN zamiast subquery

<?php

// LLM zasugerował przepisanie subquery IN (SELECT ...) na JOIN
// Sprawdziłem EXPLAIN i faktycznie MySQL planuje JOIN lepiej

// Oryginalne zapytanie przez Magento Collection
$collection = $this->productCollectionFactory->create();
$collection->addCategoryFilter($category);
$collection->addAttributeToFilter('color', ['in' => [1847, 1848, 1851]]);

// Rezultat: subquery w WHERE – wolne

// Alternatywa – własne zapytanie z JOIN przez ResourceConnection
// (gdy Collection API nie daje wystarczającej kontroli)

$connection = $this->resourceConnection->getConnection();
$select = $connection->select()
    ->distinct()
    ->from(
        ['e' => $this->resourceConnection->getTableName('catalog_product_entity')],
        ['entity_id']
    )
    ->join(
        ['idx' => $this->resourceConnection->getTableName('catalog_product_index_eav')],
        'idx.entity_id = e.entity_id AND idx.attribute_id = 142 AND idx.store_id = 1',
        []
    )
    // JOIN zamiast subquery IN (SELECT ...)
    ->join(
        ['cat' => $this->resourceConnection->getTableName('catalog_category_product_index')],
        'cat.entity_id = e.entity_id AND cat.category_id = 58 AND cat.store_id = 1',
        []
    )
    ->where('idx.value IN (?)', [1847, 1848, 1851]);

$result = $connection->fetchCol($select);
// Czas: 45ms zamiast 1470ms – 32x szybciej

Gdzie LLM pomaga, gdzie zawodzi

Wynik końcowy

# Pomiar przed i po optymalizacji (Blackfire, 5 prób)

# Przed:
# Wall Time: 4.2s | SQL queries: 847 | Peak memory: 112MB

# Po (nowy indeks + przepisane zapytanie w Collection plugin):
# Wall Time: 0.8s | SQL queries: 24 | Peak memory: 68MB

# Przyspieszenie: 5.25x
# Czas diagnozy + implementacji z pomocą LLM: ~2 godziny
# Czas bez LLM (historycznie): ~6-8 godzin

# Kluczowe: LLM skrócił czas "co tu jest nie tak i jak to naprawić",
# ale każda sugestia była zweryfikowana przez EXPLAIN i benchmark

Podsumowanie

AI-assisted optymalizacja SQL to realna zmiana w workflow – nie zastępuje znajomości MySQL i Magento, ale działa jak doświadczony kolega który szybko interpretuje EXPLAIN i sugeruje kierunek. Kluczowa zasada: traktuj sugestie LLM jako hipotezy do weryfikacji, nie gotowe rozwiązania. EXPLAIN przed i po każdej zmianie, benchmark na staging przed wdrożeniem na produkcję. Z tym podejściem cykl diagnoza-implementacja skraca się 3-4x.