UUID vs AUTO INCREMENT vs Hash – co lepsze w MySQL?

Słysząc i widząc często stwierdzenia że nie ważne co damy na primary key i tak będzie okej albo po co dawać dodatkową kolumne hash/uuid skoro możemy zamienić ją z id byłem od pewnego czasu temu przeciwny. W moich myślach było to że przecież UUID czy hash są dłuższe i zawierają więcej bajtów niż zwykły poczciwy INT. Długo w mojej głowie ten temat wisiał aż przyszedł czas podzielić się odrobiną przemyśleń. 🙂

W tym bardzo krótkim wpisie zobaczysz co warto a czego nie warto stosować jako PRIMARY KEY oraz dlaczego.

Cały kod możesz zobaczyć [KLIKNIJ TUTAJ].

Wygenerowanie danych

Abyśmy mogli mieć jakikolwiek zbiór danych rozpoczniemy od jego wygenerowania (całe wykonanie commanda chwile trwa ale możesz zmienić ilość rekordów i przyspieszyć zakończenie).

<?php

namespace App\Command;

use App\Entity\AddressUuid;
use App\Factory\AddressFactory;
use App\Repository\AddressHashRepository;
use App\Repository\AddressIntRepository;
use App\Repository\AddressUuidRepository;
use Faker\Factory;
use Symfony\Component\Console\Attribute\AsCommand;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Helper\ProgressBar;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

#[AsCommand(name: 'create:address')]
class CreateAddressCommand extends Command
{
    private const RECORDS = 200_000;

    public function __construct(
        private readonly AddressUuidRepository $uuidRepository,
        private readonly AddressHashRepository $hashRepository,
        private readonly AddressIntRepository $intRepository,
    ) {
        parent::__construct();
    }

    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        $progressBar = new ProgressBar($output, self::RECORDS);

        $faker = Factory::create('pl_PL');

        for ($i = 0; $i <= self::RECORDS; $i++) {
            $progressBar->advance();

            $addresses = AddressFactory::build($faker);

            $this->uuidRepository->add($addresses['uuid'], ($i % 10_000) === 0);
            $this->hashRepository->add($addresses['hash'], ($i % 10_001) === 0);
            $this->intRepository->add($addresses['int'], ($i % 10_002) === 0);
        }

        $progressBar->finish();

        return Command::SUCCESS;
    }
}

W przypadku tego wpisu sample danych będzie się składał z 200 tys rekordów.

Dobranie indeksu

Na tabele zostaną nałożone po 3 indeksy. Pierwszy z nich będzie na kolumnę city, drugi na city oraz postCode a ostatni na country, city oraz postCode. Długość wartości indeksu dla kolumny city oraz country zostanie dobrana na podstawie ilości unikalnych rekordów oraz średniej ilości rekordów dla jednej wartości. W ramach wpisu przejdziemy przez dobieranie długości wartości dla kolumny city.

Na podstawie takiego zapytania SELECT SUBSTRING(a.city, 1, 2) AS cut_city, COUNT(DISTINCT a.city) AS unique_city, COUNT(a.id) AS all_records FROM address_hash a GROUP BY cut_city możemy dobrać odpowiednią długość wartości indeksu. W moim przypadku to 2 znaki ponieważ mam 99 unikalnych wartości (z 250). Maksymalnie jeden indeks będzie przetrzymywać 10 miast oraz 7925 wszystkich rekordów. W identyczny sposób wybrałem ilość znaków dla kolumny country.

PS na temat indeksów kiedyś stworzę osobny wpis, bo powyższy sposób jest bardzo uproszczony i na oko. Poprawnie powinno się dobierać na podstawie testów wydajnościowych zapytania itd. 😉

Po dobraniu długości stwórzmy indeksy.

CREATE INDEX country_idx ON address_hash (country(2), city(2), post_code);
CREATE INDEX search_city_idx ON address_hash (city(2), post_code);
CREATE INDEX city_idx ON address_hash (city(2));

CREATE INDEX country_idx ON address_uuid (country(2), city(2), post_code);
CREATE INDEX search_city_idx ON address_uuid (city(2), post_code);
CREATE INDEX city_idx ON address_uuid (city(2));

CREATE INDEX country_idx ON address_int (country(2), city(2), post_code);
CREATE INDEX search_city_idx ON address_int (city(2), post_code);
CREATE INDEX city_idx ON address_int (city(2));

Sprawdźmy które rozwiązanie jest lepsze

Za pomocą poniższego zapytania, MySQL wylistuje Ci wszystkie indeksy wraz z ich wielkością w MB.

SELECT database_name, table_name, index_name,
       ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
ORDER BY size_in_mb DESC;

Jak widzisz po tabeli w każdym indeksie najniżej była tabela address_int która zawierał PRIMARY KEY jako zwykły AUTO INCREMENT.

Ten przykład bazuję na niezbyt dużym zbiorze danych 200 tys. jeżeli chcesz możesz zwiększyć tą ilość do kilku milionów. W głowie lub innym narzędziu możesz sobie także zwizualizować, jak wielkość danych rośnie liniowo z każdym rekordem a to tylko o niewinną kolumnę ID.

Źródła:

• https://dev.mysql.com/doc/