Wypełnienie tabeli (mysql) losowymi danymi (int vs string)

Chciałem przetestować wydajność indeksowania w MySQL, ale nie miałem dość dużej tabeli z danymi.

Trochę szperania w sieci, i napisałem 2 procedury (można byłoby to zamknąć w jednej):

DROP PROCEDURE  IF EXISTS LOSOWO;

CREATE PROCEDURE  LOSOWO(OUT wyjscie INT)
BEGIN
	SELECT FLOOR(1 + RAND() * 99) INTO wyjscie;
END;

DROP PROCEDURE IF EXISTS WYPELNIJ_INT;

CREATE PROCEDURE  WYPELNIJ_INT(IN ilosc INT)
BEGIN
DECLARE i INT;

SET i = 0;
WHILE(i &lt; ilosc) DO
	CALL LOSOWO(@liczba);
	INSERT INTO liczby(liczba) VALUES(@liczba);
    SET i=i+1;
END WHILE;
END;

call WYPELNIJ_INT(2000000);

DROP PROCEDURE IF EXISTS LOSOWO;

CREATE PROCEDURE LOSOWO(OUT wyjscie INT)

BEGIN

SELECT FLOOR(1 + RAND() * 99) INTO wyjscie;

END;

DROP PROCEDURE IF EXISTS WYPELNIJ_INT;

CREATE PROCEDURE WYPELNIJ_INT(IN ilosc INT)

BEGIN

DECLARE i INT;

SET i = 0;

WHILE(i < ilosc) DO

CALL LOSOWO(@liczba);

INSERT INTO liczby(liczba) VALUES(@liczba);

SET i=i+1;

END WHILE;

END;

call WYPELNIJ_INT(2000000);

Procedura LOSOWO zwraca nam na wyjściu losową liczbę z zakresu 1-99, natomiast WYPELNIJ_INT wypełnia nam tabelę liczby podaną na wejściu liczbę danych.

A wyniki badań dla integer?

SELECT COUNT(id) FROM liczby
1 rows fetched (844 ms)

SELECT id FROM liczby WHERE liczba = 67
55 rows fetched (1,078 s)

CREATE INDEX liczba ON liczby(liczba)
2000000 rows affected (21,312 sec)

SELECT id FROM liczby WHERE liczba = 67
55 rows fetched (141 ms)

SELECT COUNT(id) FROM liczby

1 rows fetched (844 ms)

SELECT id FROM liczby WHERE liczba = 67

55 rows fetched (1,078 s)

CREATE INDEX liczba ON liczby(liczba)

2000000 rows affected (21,312 sec)

SELECT id FROM liczby WHERE liczba = 67

55 rows fetched (141 ms)

Dodałem również procedury do wypełnienia znakami tabeli stringi:

DROP PROCEDURE  IF EXISTS LOSOWE_ZNAKI;

CREATE PROCEDURE  LOSOWE_ZNAKI(OUT wyjscie VARCHAR(20))
BEGIN
DECLARE i INT;
DECLARE poz INT;

SET wyjscie = '';
SET i = 0;
WHILE (i &lt; 20) DO
	SET poz = FLOOR(1 + RAND() * 9);
    CASE poz
    	when 1 then set wyjscie = CONCAT(wyjscie, 'A');
    	when 2 then set wyjscie = CONCAT(wyjscie, 'B');
    	when 3 then set wyjscie = CONCAT(wyjscie, 'C');
    	when 4 then set wyjscie = CONCAT(wyjscie, 'D');
    	when 5 then set wyjscie = CONCAT(wyjscie, 'E');
    	when 6 then set wyjscie = CONCAT(wyjscie, 'F');
    	when 7 then set wyjscie = CONCAT(wyjscie, 'G');
    	when 8 then set wyjscie = CONCAT(wyjscie, 'H');
    	when 9 then set wyjscie = CONCAT(wyjscie, 'I');
    END CASE;

    SET i=i+1;
END WHILE;
END;

DROP PROCEDURE IF EXISTS WYPELNIJ_STR;

CREATE PROCEDURE  WYPELNIJ_STR(IN ilosc INT)
BEGIN
DECLARE i INT;

SET i=0;
WHILE(i &lt; ilosc) DO
	CALL LOSOWE_ZNAKI(@znaki);
	INSERT INTO stringi(string) VALUES(@znaki);
    SET i=i+1;
END WHILE;
END;

CALL WYPELNIJ_STR(2000000);

DROP PROCEDURE IF EXISTS LOSOWE_ZNAKI;

CREATE PROCEDURE LOSOWE_ZNAKI(OUT wyjscie VARCHAR(20))

BEGIN

DECLARE i INT;

DECLARE poz INT;

SET wyjscie = '';

SET i = 0;

WHILE (i < 20) DO

SET poz = FLOOR(1 + RAND() * 9);

CASE poz

when 1 then set wyjscie = CONCAT(wyjscie, 'A');

when 2 then set wyjscie = CONCAT(wyjscie, 'B');

when 3 then set wyjscie = CONCAT(wyjscie, 'C');

when 4 then set wyjscie = CONCAT(wyjscie, 'D');

when 5 then set wyjscie = CONCAT(wyjscie, 'E');

when 6 then set wyjscie = CONCAT(wyjscie, 'F');

when 7 then set wyjscie = CONCAT(wyjscie, 'G');

when 8 then set wyjscie = CONCAT(wyjscie, 'H');

when 9 then set wyjscie = CONCAT(wyjscie, 'I');

END CASE;

SET i=i+1;

END WHILE;

END;

DROP PROCEDURE IF EXISTS WYPELNIJ_STR;

CREATE PROCEDURE WYPELNIJ_STR(IN ilosc INT)

BEGIN

DECLARE i INT;

SET i=0;

WHILE(i < ilosc) DO

CALL LOSOWE_ZNAKI(@znaki);

INSERT INTO stringi(string) VALUES(@znaki);

SET i=i+1;

END WHILE;

END;

CALL WYPELNIJ_STR(2000000);

I wyniki dla znakowej:

SELECT COUNT(id) FROM stringi;
1 rows fetched (859 ms)

SELECT id FROM stringi WHERE string="HDDFBAGFHCABIBIAFAAE";
1 rows fetched (1,172 s)

CREATE INDEX string ON stringi(string);
2000000 rows affected (39,140 sec)

SELECT id FROM stringi WHERE string="HDDFBAGFHCABIBIAFAAE";
1 rows fetched (16 ms)

SELECT COUNT(id) FROM stringi;

1 rows fetched (859 ms)

SELECT id FROM stringi WHERE string="HDDFBAGFHCABIBIAFAAE";

1 rows fetched (1,172 s)

CREATE INDEX string ON stringi(string);

2000000 rows affected (39,140 sec)

SELECT id FROM stringi WHERE string="HDDFBAGFHCABIBIAFAAE";

1 rows fetched (16 ms)

Z tego by wynikało, że lepiej tworzyć zindeksowane pola tekstowe (16 ms), niż liczbowe (141 ms). Ale czy aby na pewno?
Uważne oko zauważyło, że zapytanie dla tekstowego zwróciło unikatowy wiersz (1 rows fetched), natomiast dla liczby było to aż 55 wyników.
Dodajmy do tabeli liczby pole z unikatową wartością dla kolumny liczba i sprawdźmy szybkość zapytania:

insert into liczby(liczba) values(666);
1 rows affected (47 ms)

SELECT id FROM liczby WHERE liczba = 666
1 rows fetched (32 ms)

insert into liczby(liczba) values(666);

1 rows affected (47 ms)

SELECT id FROM liczby WHERE liczba = 666

1 rows fetched (32 ms)

W tym momencie wydobycie wiersza z liczb trwa tylko 2x dłużej (32 ms vs 16ms). Czyli można nieoficjalnie uznać, że wiedza wszechmocna ma rację, że indeksy stringów są lepszy niż liczb.

Dodam tylko że przy 0,6 mln pozycjach w każdej z tabel wygrywa prędkością select na liczbach, dopiero przy przykładzie jak wyżej (2 mln) widać przewagę indeksowania stringów.

Related