Skąd MySQL Query Optimizer odczytuje statystyki indeksu?

Próbuję ustalić, skąd optymalizator MySQL uzyskuje listę indeksów, które są dostępne dla tabeli, gdy szacuje koszt (przygotowania) zapytania.

Bezpośrednia odpowiedź na to pytanie byłaby

information_schema.statistics

mysql> desc information_schema.statistics;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)  | NO   |     |         |       |
| TABLE_SCHEMA  | varchar(64)   | NO   |     |         |       |
| TABLE_NAME    | varchar(64)   | NO   |     |         |       |
| NON_UNIQUE    | bigint(1)     | NO   |     | 0       |       |
| INDEX_SCHEMA  | varchar(64)   | NO   |     |         |       |
| INDEX_NAME    | varchar(64)   | NO   |     |         |       |
| SEQ_IN_INDEX  | bigint(2)     | NO   |     | 0       |       |
| COLUMN_NAME   | varchar(64)   | NO   |     |         |       |
| COLLATION     | varchar(1)    | YES  |     | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |     | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |     | NULL    |       |
| PACKED        | varchar(10)   | YES  |     | NULL    |       |
| NULLABLE      | varchar(3)    | NO   |     |         |       |
| INDEX_TYPE    | varchar(16)   | NO   |     |         |       |
| COMMENT       | varchar(16)   | YES  |     | NULL    |       |
| INDEX_COMMENT | varchar(1024) | NO   |     |         |       |
+---------------+---------------+------+-----+---------+-------+
16 rows in set (0.01 sec)

Możesz wybrać z tej tabeli za pomocą

SELECT * FROM information_schema.statistics
WHERE table_schema='mydb' AND table_name='mytable';

lub zobacz statystyki, wykonując

POKAŻ INDEKSY z mydb.mytable;

Należy pamiętać, że ta tabela nie zawsze jest dokładna w środowisku o dużym obciążeniu zapisu. Okresowo trzeba będzie uruchomić ANALYSE TABELA przeciwko wszystkich tabel MyISAM, które są często aktualizowane. W przeciwnym razie Optymalizator zapytań MySQL, który opiera się na informacjach_schema.statistics, może czasami dokonywać złych wyborów podczas opracowywania planów EXPLAIN dla zapytań. Statystyka indeksu musi być jak najbardziej aktualna.

TABELA ANALIZOWA NIE MA ABSOLUTNIE ŻADNEGO WPŁYWU na stoły InnoDB. Wszystkie statystyki indeksów dla InnoDB są obliczane na żądanie za pomocą nurkowań na stronach BTREE. Dlatego po uruchomieniu SHOW INDEXES FROM dla tabeli InnoDB wyświetlane liczności są zawsze przybliżone.

AKTUALIZACJA 21.06.2011 12:17 EDT

Aby wyjaśnić TABELĘ ANALIZOWĄ, pozwólcie, że przeformułuję. Uruchamianie tabeli ANALIZA na tabelach InnoDB jest całkowicie bezużyteczne. Nawet jeśli uruchomiłeś tabelę ANALIZA na tabeli InnoDB, silnik pamięci InnoDB wykonuje nurkowania w indeksie dla przybliżeń liczności w kółko, w ten sposób niszcząc właśnie skompilowane statystyki . W rzeczywistości Percona przeprowadziła kilka testów na ANALYZE TABLE i doszła do tego wniosku.

Re: TABELA ANALIZOWA NIE MA ABSOLUTNIE BRAKU WPŁYWU na tabele InnoDB.

Nie jestem pewien, czy to stwierdzenie jest prawdziwe. Mamy intensywnie czytające i piszące tabele innodb, a kiedy optymalizator mysql dokonuje złego wyboru, wynik wyjaśnienia zapytania pokazuje złą strategię. a także POKAŻ INDEKSY z tabeli Innodb pokazuje tak dużą zmienność ich wartości liczności. Ale uruchomienie komendy ANALIZA na tych tabelach innodb naprawia plan wyjaśniania, a także usuwa zachowanie wariancji liczności. Nie wiem, czy polecenie tabeli ANALIZA w tabelach Innodb cały czas pomaga, czy nie, ale w naszym przypadku pomogło około 99% czasu.

Całkowicie wyeliminowaliśmy zły wybór optymalizatora mysql, dodając do naszych zapytań „STRAIGHT_JOIN”. Zmusiło to optymalizator mysql, aby nie dokonywał złych wyborów ani żadnych wyborów, ale po prostu przestrzegał warunku DOŁĄCZ tego, co zdefiniowaliśmy w zapytaniu.

TABELA ANALIZOWA dla MyISAM skanuje całą tabelę i odbudowuje statystyki, które są zapisywane (myślę) w pliku .MYI. Jest rzadko potrzebny.

TABELA ANALIZOWA dla InnoDB działa coś zrobić - to robi nurkowanie wspomniałem. Problem polega na tym, że może pomóc, może pogorszyć sytuację lub (najprawdopodobniej) nie zrobi żadnej widocznej różnicy (z wyjątkiem liczności).

Nowsze wersje obiecują umożliwić zmianę 8 niezbyt przypadkowych sond na (1) bardziej losowe, (2) pozwalające na zmianę „8” (są plusy i minusy tego!), Oraz (3) oszczędzanie pomiędzy restartami.

Konkluzja: InnoDB wciąż nie zrozumiał „dobrze”. Wykonuj ANALIZĘ, kiedy masz na to ochotę, ale nie wstrzymuj oddechu.

Aktualizacja

Ponowne wyrażenie ... ANALYZE TABLEma charakter tymczasowy wpływ (być może korzystny, a może nie) na optymalizacje tabel InnoDB.

„Nowsza wersja”: Począwszy od 5.6.6 (2012) i MariaDB 10.1 (2014), statystyki są obsługiwane znacznie lepiej, a ANALYZEteraz (1) rzadziej potrzebne i (2) bardziej trwałe.