Witam,

mam taki problem: chcę wrzucić do tabeli 16 milionów rekordów zawierających losowe unikalne liczby z wybranego zakresu.

Tabela jest prosta:

[SQL] pobierz, plaintext 
CREATE TABLE `codes` (
  `code_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` bigint(20) UNSIGNED NOT NULL,
  `is_used` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`code_id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
[SQL] pobierz, plaintext 

I teraz... Stworzyłem funkcję, która na wejściu ma zakres (min, max) oraz ilość rekordów, a która generuje losowe liczby i zapisuje je do tabeli. Działa na zasadzie dopełnienia liczby rekordów do wymaganej ilości.

[SQL] pobierz, plaintext 
DELIMITER $$
 
CREATE DEFINER=`root`@`localhost` FUNCTION `codes`(`minRange` BIGINT UNSIGNED, `maxRange` BIGINT UNSIGNED, `_amount` BIGINT UNSIGNED) RETURNS tinyint(1)
    MODIFIES SQL DATA
BEGIN
 DECLARE pick BIGINT;
 
 
 while (SELECT count(*) FROM codes) < _amount do
   begin
     SET pick = minRange + FLOOR(RAND() * (maxRange - minRange + 1));
     INSERT IGNORE INTO codes (code) VALUES (pick);
 
   end;
 end while;
 
 RETURN 1;
END$$
 
DELIMITER ;
[SQL] pobierz, plaintext 

Funkcja jest meganieoptymalna, bo za każdym przejściem pętli sprawdza, ile jest już rekordów w tabeli i kontynuuje działanie, dopóki nie zostanie osiągnięta docelowa liczba rekordów. Czas wstawiania 20 000 rekordów z zakresu 1 000 000 000 - 9 999 999 999 (zależy mi na liczbach 10-cyfrowych) to 2,5 minuty. Wstawienie 16 milionów rekordów to szacunkowo 33 godziny.


Druga funkcja działa na zasadzie wstawienia określonej liczby rekordów (nie dopełnienia):

[SQL] pobierz, plaintext 
DELIMITER $$
--
-- Funkcje
--
CREATE DEFINER=`root`@`localhost` FUNCTION `codes`(`minRange` BIGINT UNSIGNED, `maxRange` BIGINT UNSIGNED, `_amount` BIGINT UNSIGNED) RETURNS tinyint(1)
    MODIFIES SQL DATA
BEGIN
 DECLARE pick BIGINT;
 DECLARE i BIGINT;
 SET i = 1;
 
 
 while i <= _amount do
   begin
     SET pick = minRange + FLOOR(RAND() * (maxRange - minRange + 1));
     INSERT IGNORE INTO codes (code) VALUES (pick);
     SET i = i+1;
   end;
 end while;
 
 RETURN 1;
END$$
 
DELIMITER ;
[SQL] pobierz, plaintext 

Funkcja jest o niebo szybsza, 20 000 rekordów to ~1 sek. Wada: nie zawsze wstawi wymaganą liczbę rekordów, bo nie ma sprawdzania, ile jest rekordów w tabeli, a jest INSERT IGNORE, żeby uniknąć duplikatów.

To co chciałbym osiągnąć, to optymalizacja pierwszej funkcji - tak, żeby dopełniła mi tabelę do wymaganej liczby rekordów. Będę wdzięczny za wskazówki.

1. Skorzystaj z prepared statements do dodawania rekordów - powinno jeszcze nieco przyspieszyć działanie.
2. Połącz sposób działania obu funkcji, tj. w pętli sprawdzaj ile rekordów jeszcze pozostało do dodania (COUNT(*) - _amount), następnie w pętli dodaj n rekordów, gdzie n to MIN(_amount, COUNT(*) - _amount). Dzięki temu powinno wykonywać zapytania dodania rekordów do czasu osiągnięcia wymaganej ilości.

Dzięki.
Mam jeszcze info, że jedno z szybszych rozwiązań to zapisanie liczb do pliku tekstowego, a potem załadowanie ich używając LOAD DATA INFILE. I oczywiście należy usunąć (tymczasowo) indeksy, które są aktualizowane po każdym insercie.

Zrob tak jak pisze Crozin

czyli robisz dwie petle:

pseldo kod zarys logiki

[SQL] pobierz, plaintext 
 while (SELECT count(*) FROM codes) < _amount do // tu sprawdzasz czy jest juz tyle ile chcesz
 
SET ile_jeszcze_dodac=_amount - (SELECT count(*) FROM codes); // tu ustawiasz ile jeszcze musisz dodac
SET i=0; // zerowanie licznika
 
 while (i < ile_jeszcze_dodac) do // tu wykonujesz petle tyle razy ile musisz dodac, i nie przejmujesz sie ze cos zduplikowane. Petla wyzej na nowo spradzi i ustai ile zabraklo
   begin
     SET pick = minRange + FLOOR(RAND() * (maxRange - minRange + 1));
     INSERT IGNORE INTO codes (code) VALUES (pick);
   i=i+1; // tu zwiekszasz licznik petli
   end;
 end while;
 
end while;
[SQL] pobierz, plaintext 

W php (szybko ale wymaga duzo pamieci)

[PHP] pobierz, plaintext 
$tab=range($min,$max); // generuje tablice wartosci od do (czyli podajesz -99999999 do 9999999) ale to zajmuje mase pamieci
shuffle($tab); // miesza ta tablice, czyli robi losowosc;p
$tab=array_slice($tab, 0, $ile_liczb); // wycina od 0 do ilu tam elementow chcesz... i w $tab masz juz losowe liczby UNIKALNE, pozniej zrobienie z tego CSV to pikus
 
[PHP] pobierz, plaintext 

a odwracając problem?
tworzysz tabelę zawierającą autoincrement w pełnym zadanym zakresie a losowanie realizujesz przez select z order by rand(), ew z limit

order by rand() reklamowane jest jako niewydajne ale przy innodb z indeksem w ramie byłoby pewnie o rzędy szybkości szybsze niż te 33 godziny

autoincrement nie jest ujemny, ale to nie problem aby obejsc;) wystarczy max(id)/2-id i mamy odpowiednia liczbe