Statistiche sulle tabelle indipendenti dallo Storage Engine

Questa è una documentazione preliminare circa le statistiche sulle tabelle indipendenti dallo Storage Engine.

Introduzione

Prima di questo progetto, l'ottimizzatore di MySQL/MariaDB si affidava agli Storage Engine (come InnoDB) per le statistiche necessarie all'ottimizzazione. Questo approccio funzionava, tuttavia vi erano alcune inefficienze:

• Gli Storage Engine fornivano statistiche povere (questo problema è stato risolto fino a un certo punto in MySQL 5.6)
• Le statistiche venivano fornite attraverso l'interfaccia degli Storage Engine, che pone molte restrizioni sui tipi di dati che è possibile fornire (per esempio non c'è alcun modo per ottenere dati sulla distribuzione dei valori in una colonna non indicizzata)
• Vi era poco controllo sulle statistiche. Non si poteva "fissare" i valori correnti delle statistiche o fornire valori arbitrari, etc.

Questo progetto elimina tali restrizioni.

• Le statistiche vengono scritte in normali tabelle nel database mysql.
  • Per il DBA è ora possibile leggere e modificare i valori
• Una maggiore varietà di dati può essere raccolta e utilizzata.

Attualmente, le statistiche si trovano in queste tre tabelle:

+--------------------------+
| Tables_in_mysql (%stat%) |
+--------------------------+
| column_stat              |
| index_stat               |
| table_stat               |
+--------------------------+

L'uso e la modifica dei dati di queste tabelle si può controllare attraverso la variabile use_stat_tables##. I valori possibili sono:

• 'never' - l'ottimizzatore non usa i dati delle tabelle delle statistiche
• 'complementary' - l'ottimizzatore usa i dati delle tabelle delle statistiche se lo Storage Engine non fornisce dati dello stesso tipo
• 'preferably' - preferisce i dati delle tabelle delle statistiche, ma se non sono disponibili usa i dati dello Storage Engine.

Estensioni di ANALYZE TABLE

MySQL/MariaDB supporta il comando ANALYZE TABLE:

ANALYZE TABLE table_name;

Quando viene eseguito, il livello SQL effettua una chiamata allo Storage Engine per aggiornare le statistiche. L'effetto di questa chiamata dipende dallo Storage Engine.

Con le statistiche indipendenti dallo Storage Engine:

• Se @@use_stat_tables='never', vengono aggiornate solo le statistiche dello Storage Engine.
• Se @@use_stat_tables ha qualsiasi altro valore, vengono aggiornate sia le statistiche dello Storage Engine sia quelle indipendenti dallo Storage Engine.

Nota: attualmente, le statistiche indipendenti dallo Storage Engine vengono raccolte tramite scansioni completa delle tabelle e degli indici. Questa operazione può essere costosa.

La sintassi di ANALYZE TABLE è stata estesa per poter raccogliere le statistiche solo per determinate colonne o indici:

ANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES (idx1,idx2,...);

-- è possibile specificare liste vuote:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES ();
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES (idx1,idx2,...);

-- il comando seguente aggiorna solo i campi di mysql.table_stat:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES ();

-- sinonimo del vecchio 'analyze table':
ANALYZE TABLE tbl PERSISTENT FOR ALL;

Le tabelle delle statistiche

index_stat table

CREATE TABLE index_stat (
  db_name varchar(64) COLLATE utf8_bin NOT NULL,
  table_name varchar(64) COLLATE utf8_bin NOT NULL,
  index_name varchar(64) COLLATE utf8_bin NOT NULL,
  prefix_arity int(11) unsigned NOT NULL,
  avg_frequency double DEFAULT NULL,
  PRIMARY KEY (db_name,table_name,index_name,prefix_arity)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Indexes';

table_stat table

CREATE TABLE table_stat (
  db_name varchar(64) COLLATE utf8_bin NOT NULL,
  table_name varchar(64) COLLATE utf8_bin NOT NULL,
  cardinality bigint(21) unsigned DEFAULT NULL,
  PRIMARY KEY (db_name,table_name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables';

column_stat table

CREATE TABLE column_stat (
  db_name varchar(64) COLLATE utf8_bin NOT NULL,
  table_name varchar(64) COLLATE utf8_bin NOT NULL,
  column_name varchar(64) COLLATE utf8_bin NOT NULL,
  min_value varchar(255) COLLATE utf8_bin DEFAULT NULL,
  max_value varchar(255) COLLATE utf8_bin DEFAULT NULL,
  nulls_ratio double DEFAULT NULL,
  avg_length double DEFAULT NULL,
  avg_frequency double DEFAULT NULL,
  PRIMARY KEY (db_name,table_name,column_name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Columns';

Dove ottenerlo

• Il codice è disponibile su: lp:maria-captains/maria/maria-5.5-mwl248