Rechercher dans le manuel MySQL

13.7.3.1 ANALYZE TABLE Syntax

Table des matières
  1. ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
  2.     TABLE tbl_name [, tbl_name] ...
  3.  
  4. ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
  5.     TABLE tbl_name
  6.     UPDATE HISTOGRAM ON col_name [, col_name] ...
  7.         [WITH N BUCKETS]
  8.  
  9. ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
  10.     TABLE tbl_name
  11.     DROP HISTOGRAM ON col_name [, col_name] ...

ANALYZE TABLE generates table statistics:

  • ANALYZE TABLE without either HISTOGRAM clause performs a key distribution analysis and stores the distribution for the named table or tables. For MyISAM tables, ANALYZE TABLE for key distribution analysis is equivalent to using myisamchk --analyze.

  • ANALYZE TABLE with the UPDATE HISTOGRAM clause generates histogram statistics for the named table columns and stores them in the data dictionary. Only one table name is permitted for this syntax.

  • ANALYZE TABLE with the DROP HISTOGRAM clause removes histogram statistics for the named table columns from the data dictionary. Only one table name is permitted for this syntax.

Note

If the innodb_read_only system variable is enabled, ANALYZE TABLE may fail because it cannot update statistics tables in the data dictionary, which use InnoDB. For ANALYZE TABLE operations that update the key distribution, failure may occur even if the operation updates the table itself (for example, if it is a MyISAM table). To obtain the updated distribution statistics, set information_schema_stats_expiry=0.

This statement requires SELECT and INSERT privileges for the table.

ANALYZE TABLE works with InnoDB, NDB, and MyISAM tables. It does not work with views.

ANALYZE TABLE is supported for partitioned tables, and you can use ALTER TABLE ... ANALYZE PARTITION to analyze one or more partitions; for more information, see Section 13.1.9, “ALTER TABLE Syntax”, and Section 23.3.4, “Maintenance of Partitions”.

During the analysis, the table is locked with a read lock for InnoDB and MyISAM.

By default, the server writes ANALYZE TABLE statements to the binary log so that they replicate to replication slaves. To suppress logging, specify the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

ANALYZE TABLE Output

ANALYZE TABLE returns a result set with the columns shown in the following table.

Column Value
Table The table name
Op analyze or histogram
Msg_type status, error, info, note, or warning
Msg_text An informational message

 

Key Distribution Analysis

ANALYZE TABLE without either HISTOGRAM clause performs a key distribution analysis and stores the distribution for the table or tables. Any existing histogram statistics remain unaffected.

If the table has not changed since the last key distribution analysis, the table is not analyzed again.

MySQL uses the stored key distribution to decide the order in which tables should be joined for joins on something other than a constant. In addition, key distributions can be used when deciding which indexes to use for a specific table within a query.

For more information on how key distribution analysis works within InnoDB, see Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters” and Section 15.8.10.3, “Estimating ANALYZE TABLE Complexity for InnoDB Tables”. Also see Section 15.6.1.7, “Limits on InnoDB Tables”. In particular, when you enable the innodb_stats_persistent option, you must run ANALYZE TABLE after loading substantial data into an InnoDB table, or creating a new index for one.

To check the stored key distribution cardinality, use the SHOW INDEX statement or the INFORMATION_SCHEMA STATISTICS table. See Section 13.7.6.22, “SHOW INDEX Syntax”, and Section 25.25, “The INFORMATION_SCHEMA STATISTICS Table”.

 

Histogram Statistics Analysis

ANALYZE TABLE with the HISTOGRAM clauses enables management of histogram statistics for table column values. For information about histogram statistics, see Section 8.9.6, “Optimizer Statistics”.

These histogram operations are available:

  • ANALYZE TABLE with an UPDATE HISTOGRAM clause generates histogram statistics for the named table columns and stores them in the data dictionary. Only one table name is permitted for this syntax.

    The optional WITH N BUCKETS clauses specifies the number of buckets for the histogram. The value of N must be an integer in the range from 1 to 1024. If this clause is omitted, the number of buckets is 100.

  • ANALYZE TABLE with a DROP HISTOGRAM clause removes histogram statistics for the named table columns from the data dictionary. Only one table name is permitted for this syntax.

Stored histogram management statements affect only the named columns. Consider these statements:

  1. ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c2, c3 WITH 10 BUCKETS;
  2. ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c3 WITH 10 BUCKETS;
  3. ANALYZE TABLE t DROP HISTOGRAM ON c2;

The first statement updates the histograms for columns c1, c2, and c3, replacing any existing histograms for those columns. The second statement updates the histograms for c1 and c3, leaving the c2 histogram unaffected. The third statement removes the histogram for c2, leaving those for c1 and c3 unaffected.

Histogram generation is not supported for encrypted tables (to avoid exposing data in the statistics) or TEMPORARY tables.

Histogram generation applies to columns of all data types except geometry types (spatial data) and JSON.

Histograms can be generated for stored and virtual generated columns.

Histograms cannot be generated for columns that are covered by single-column unique indexes.

Histogram management statements attempt to perform as much of the requested operation as possible, and report diagnostic messages for the remainder. For example, if an UPDATE HISTOGRAM statement names multiple columns, but some of them do not exist or have an unsupported data type, histograms are generated for the other columns, and messages are produced for the invalid columns.

The histogram_generation_max_mem_size system variable controls the maximum amount of memory available for histogram generation. The global and session values may be set at runtime.

Changing the global histogram_generation_max_mem_size value requires privileges sufficient to set global system variables. Changing the session histogram_generation_max_mem_size value requires privileges sufficient to set restricted session system variables. See Section 5.1.9.1, “System Variable Privileges”.

For information about memory allocations performed for histogram generation, monitor the Performance Schema memory/sql/histograms instrument. See Section 26.12.16.10, “Memory Summary Tables”.

Histograms are affected by these DDL statements:

  • DROP TABLE removes histograms for columns in the dropped table.

  • DROP DATABASE removes histograms for any table in the dropped database because the statement drops all tables in the database.

  • RENAME TABLE does not remove histograms. Instead, it renames histograms for the renamed table to be associated with the new table name.

  • ALTER TABLE statements that remove or modify a column remove histograms for that column.

  • ALTER TABLE ... CONVERT TO CHARACTER SET removes histograms for character columns because they are affected by the change of character set. Histograms for noncharacter columns remain unaffected.


Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-analyze-table.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut