Jak oszacować / przewidzieć rozmiar danych i rozmiar indeksu tabeli w MySQL

znajduję , co jest najlepszym sposobem na oszacowanie wielkości stołu do tego Badałem wiele blogów i forów, ale nie można znaleźć żadnych dokładnych odpowiedzi

Na przykład mamy tabelę Miasto z silnikiem InnoDB , powiedzmy w przyszłości (za 1 rok), że będzie miała 1 milion rekordów, więc jaka będzie szacowana wielkość danych i rozmiar indeksu tej tabeli w tym okresie.

mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

AKTUALIZACJA

Jaka będzie szacowana górna granica (maksymalny rozmiar tabeli) z 1 milionem rekordów i jak możemy to oszacować.

Rozumiem, biorąc pod uwagę opis tabeli

• 66 bajtów na wiersz danych
• 4 bajty na wiersz dla klucza podstawowego
• 7 bajtów na wiersz dla indeksu kodu kraju
  • 3 bajty dla kraju
  • 4 bajty dla klucza klastrowego dołączonego do kodu kraju
• Łącznie 77 bajtów danych i kluczy
• Nie uwzględnia to faktoringu dla BTREE ani fragmentacji przestrzeni tabel

W przypadku miliona wierszy byłoby to 77 000 000 bajtów (73,43 MB)

Jeśli chodzi o pomiar tabeli, dla danej tabeli mydb.mytable możesz uruchomić to zapytanie

SELECT 
    CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,
    CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,
    CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE
FROM
(
    SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM 
    (
        SELECT data_length DAT,index_length NDX,data_length+index_length TBL,
        FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,
        FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,
        FLOOR(LOG(data_length+index_length)/LOG(1024)) pz
        FROM information_schema.tables
        WHERE table_schema='mydb'
        AND table_name='mytable'
    ) AA
) A,(SELECT 'B KBMBGBTB' units) B;

Aby zmierzyć wszystkie tabele pogrupowane według silnika bazy danych i pamięci

SELECT
    IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
    CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT DB,ENGINE,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM 
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
    FROM
    (SELECT
        DB,ENGINE,
        SUM(data_length) DAT,
        SUM(index_length) NDX,
        SUM(data_length+index_length) TBL
    FROM
    (
       SELECT table_schema DB,ENGINE,data_length,index_length FROM
       information_schema.tables WHERE table_schema NOT IN
       ('information_schema','performance_schema','mysql')
       AND ENGINE IS NOT NULL
    ) AAA GROUP BY DB,ENGINE WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

Uruchom te zapytania, aby śledzić zmiany w użyciu dysku bazy danych / silnika.

Spróbuj !!!

Jeśli używasz tabel InnoDB, możesz uzyskać rozmiar danych / poszczególnych indeksów z mysql.innodb_index_stats. Statystyka „size” zawiera odpowiedź na stronach, więc należy pomnożyć ją przez rozmiar strony, czyli domyślnie 16 KB .

select database_name, table_name, index_name, stat_value*@@innodb_page_size
from mysql.innodb_index_stats where stat_name='size';

Indeks PODSTAWOWY to same dane.

SELECT  Table_NAME "tablename",
           data_length   "table data_length in Bytes",
           index_length  "table index_length in Bytes",
           data_free  "Free Space in Bytes"
    FROM  information_schema.TABLES  where  Table_schema = 'databasename';

przez wykonywanie kwerendy można uzyskać rozmiar używany Datai Indexod stołu, można to sprawdzić rozmiar przed # wierszy i przewidywania na 1 milion wierszy

Jeśli nie masz jeszcze danych, oto kilka wskazówek. Poniższe informacje dotyczą InnoDB. (MyISAM jest znacznie prostszy i mniejszy).

Nie używaj CHARdo kolumn o zmiennej długości. Z CHARACTER SETczego korzystasz Ascii potrzebuje jednego bajtu na znak; utf8mb4 potrzebuje od 1 do 4.

4 bytes per INT
35 for CHAR(35), if ascii or latin1; varchar is probably less
3 for the country code; it is fixed length
etc

Łącznie = około 80 bajtów.

Pomnóż 80 przez 2 do 3, aby uwzględnić różne koszty ogólne. Najprawdopodobniej tabela wierszy 1M będzie zawierać się między 160 MB a 240 MB.

Aby zmierzyć pojedynczy indeks, powiedzmy CountryCode3 bajty:

3 bytes data
4 bytes for the PK (implicitly included with any secondary key)
25 bytes basic overhead
32 total
times 1.5 -- overhead for BTree that was randomly inserted into
48MB -- total for 1M rows.

Uwagi:

• Należy obliczyć tylko węzły liści (BTrees); koszty ogólne dla węzłów innych niż liście wynoszą zwykle 1%.

• PRIMARY KEYJest „skupione” z danymi, więc nie ma potrzeby, aby ją obliczyć.

• Jeśli nie masz wyraźnego PK, musisz dodać 6 bajtów do rozmiaru wiersza, aby umożliwić sfabrykowane PK.

• ROW_FORMAT = COMPRESSEDdaje ci skurcz 2: 1. (To nie jest tak dobre jak typowy współczynnik kompresji zip (itp.) 3: 1.)

• SHOW TABLE STATUS LIKE "tablename";to szybki sposób na obliczenie „rzeczywistego” rozmiaru. Zobacz Data_lengthdane i PK; Index_lengthdla indeksów wtórnych i Data_freedla niektórych innych rzeczy.

• Jest rzadko Index_lengthprzekracza Data_length. Jednak nie jest to „złe”, aby tak się stało.

To nużące. Ale szczegóły są w dokumentach .

Aby być możliwie najdokładniejszym, co rzadko jest konieczne, musisz także przeczytać o strukturze tabeli i strukturze indeksu.

Gdybym był w twoich butach, zbudowałbym tabelę, zapełniłem ją milionem wierszy danych testowych i zmierzyłem zmianę rozmiaru. W zależności od aplikacji może być konieczne uwzględnienie rozmiaru plików dziennika transakcji.