Rechercher dans le manuel MySQL

15.8.10.3 Estimating ANALYZE TABLE Complexity for InnoDB Tables

Inhoudsopgave

ANALYZE TABLE complexity for InnoDB tables is dependent on:

  • The number of pages sampled, as defined by innodb_stats_persistent_sample_pages.

  • The number of indexed columns in a table

  • The number of partitions. If a table has no partitions, the number of partitions is considered to be 1.

Using these parameters, an approximate formula for estimating ANALYZE TABLE complexity would be:

The value of innodb_stats_persistent_sample_pages * number of indexed columns in a table * the number of partitions

Typically, the greater the resulting value, the greater the execution time for ANALYZE TABLE.

Note

innodb_stats_persistent_sample_pages defines the number of pages sampled at a global level. To set the number of pages sampled for an individual table, use the STATS_SAMPLE_PAGES option with CREATE TABLE or ALTER TABLE. For more information, see Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.

If innodb_stats_persistent=OFF, the number of pages sampled is defined by innodb_stats_transient_sample_pages. See Section 15.8.10.2, “Configuring Non-Persistent Optimizer Statistics Parameters” for additional information.

For a more in-depth approach to estimating ANALYZE TABLE complexity, consider the following example.

In Big O notation, ANALYZE TABLE complexity is described as: 

 O(n_sample
  * (n_cols_in_uniq_i
     + n_cols_in_non_uniq_i
     + n_cols_in_pk * (1 + n_non_uniq_i))
  * n_part)

where:

  • n_sample is the number of pages sampled (defined by innodb_stats_persistent_sample_pages)

  • n_cols_in_uniq_i is total number of all columns in all unique indexes (not counting the primary key columns)

  • n_cols_in_non_uniq_i is the total number of all columns in all nonunique indexes

  • n_cols_in_pk is the number of columns in the primary key (if a primary key is not defined, InnoDB creates a single column primary key internally)

  • n_non_uniq_i is the number of nonunique indexes in the table

  • n_part is the number of partitions. If no partitions are defined, the table is considered to be a single partition.

Now, consider the following table (table t), which has a primary key (2 columns), a unique index (2 columns), and two nonunique indexes (two columns each):

  1. CREATE TABLE t (
  2.   a INT,
  3.   b INT,
  4.   c INT,
  5.   d INT,
  6.   e INT,
  7.   f INT,
  8.   g INT,
  9.   h INT,
  10.   PRIMARY KEY (a, b),
  11.   UNIQUE KEY i1uniq (c, d),
  12.   KEY i2nonuniq (e, f),
  13.   KEY i3nonuniq (g, h)
  14. );

For the column and index data required by the algorithm described above, query the mysql.innodb_index_stats persistent index statistics table for table t. The n_diff_pfx% statistics show the columns that are counted for each index. For example, columns a and b are counted for the primary key index. For the nonunique indexes, the primary key columns (a,b) are counted in addition to the user defined columns.

Note

For additional information about the InnoDB persistent statistics tables, see Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”

  1. mysql> SELECT index_name, stat_name, stat_description
  2.        FROM mysql.innodb_index_stats WHERE
  3.        database_name='test' AND
  4.        table_name='t' AND
  5.        stat_name like 'n_diff_pfx%';
  6.   +------------+--------------+------------------+
  7.   | index_name | stat_name    | stat_description |
  8.   +------------+--------------+------------------+
  9.   | PRIMARY    | n_diff_pfx01 | a                |
  10.   | PRIMARY    | n_diff_pfx02 | a,b              |
  11.   | i1uniq     | n_diff_pfx01 | c                |
  12.   | i1uniq     | n_diff_pfx02 | c,d              |
  13.   | i2nonuniq  | n_diff_pfx01 | e                |
  14.   | i2nonuniq  | n_diff_pfx02 | e,f              |
  15.   | i2nonuniq  | n_diff_pfx03 | e,f,a            |
  16.   | i2nonuniq  | n_diff_pfx04 | e,f,a,b          |
  17.   | i3nonuniq  | n_diff_pfx01 | g                |
  18.   | i3nonuniq  | n_diff_pfx02 | g,h              |
  19.   | i3nonuniq  | n_diff_pfx03 | g,h,a            |
  20.   | i3nonuniq  | n_diff_pfx04 | g,h,a,b          |
  21.   +------------+--------------+------------------+

Based on the index statistics data shown above and the table definition, the following values can be determined:

  • n_cols_in_uniq_i, the total number of all columns in all unique indexes not counting the primary key columns, is 2 (c and d)

  • n_cols_in_non_uniq_i, the total number of all columns in all nonunique indexes, is 4 (e, f, g and h)

  • n_cols_in_pk, the number of columns in the primary key, is 2 (a and b)

  • n_non_uniq_i, the number of nonunique indexes in the table, is 2 (i2nonuniq and i3nonuniq))

  • n_part, the number of partitions, is 1.

You can now calculate innodb_stats_persistent_sample_pages * (2 + 4 + 2 * (1 + 2)) * 1 to determine the number of leaf pages that are scanned. With innodb_stats_persistent_sample_pages set to the default value of 20, and with a default page size of 16 KiB (innodb_page_size=16384), you can then estimate that 20 * 12 * 16384 bytes are read for table t, or about 4 MiB.

Note

All 4 MiB may not be read from disk, as some leaf pages may already be cached in the buffer pool.


Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-innodb-analyze-table-complexity.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

  1. Bekijk - html-document Taal van het document:en Manuel MySQL : https://dev.mysql.com/

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut