Version sans cache

Mise en cache désactivé. Réglage défaut pour cette page : actif (code DEF204)
Si l'affichage est trop lent, vous pouvez désactiver le mode utilisateur pour visualiser la version en cache.

Rechercher dans le manuel MySQL

8.3.10 Use of Index Extensions

InnoDB automatically extends each secondary index by appending the primary key columns to it. Consider this table definition:

  1.   i1 INT NOT NULL DEFAULT 0,
  2.   i2 INT NOT NULL DEFAULT 0,
  3.   PRIMARY KEY (i1, i2),
  4.   INDEX k_d (d)

This table defines the primary key on columns (i1, i2). It also defines a secondary index k_d on column (d), but internally InnoDB extends this index and treats it as columns (d, i1, i2).

The optimizer takes into account the primary key columns of the extended secondary index when determining how and whether to use that index. This can result in more efficient query execution plans and better performance.

The optimizer can use extended secondary indexes for ref, range, and index_merge index access, for Loose Index Scan access, for join and sorting optimization, and for MIN()/MAX() optimization.

The following example shows how execution plans are affected by whether the optimizer uses extended secondary indexes. Suppose that t1 is populated with these rows:

  1. (1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
  2. (1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
  3. (1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
  4. (2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
  5. (2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
  6. (3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
  7. (3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
  8. (3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
  9. (4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
  10. (4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
  11. (5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
  12. (5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
  13. (5, 5, '2002-01-01');

Now consider this query:

  1. EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'

The execution plan depends on whether the extended index is used.

When the optimizer does not consider index extensions, it treats the index k_d as only (d). EXPLAIN for the query produces this result:

  1. mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t1
  6.          type: ref
  7. possible_keys: PRIMARY,k_d
  8.           key: k_d
  9.       key_len: 4
  10.           ref: const
  11.          rows: 5
  12.         Extra: Using where; Using index

When the optimizer takes index extensions into account, it treats k_d as (d, i1, i2). In this case, it can use the leftmost index prefix (d, i1) to produce a better execution plan:

  1. mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t1
  6.          type: ref
  7. possible_keys: PRIMARY,k_d
  8.           key: k_d
  9.       key_len: 8
  10.           ref: const,const
  11.          rows: 1
  12.         Extra: Using index

In both cases, key indicates that the optimizer will use secondary index k_d but the EXPLAIN output shows these improvements from using the extended index:

  • key_len goes from 4 bytes to 8 bytes, indicating that key lookups use columns d and i1, not just d.

  • The ref value changes from const to const,const because the key lookup uses two key parts, not one.

  • The rows count decreases from 5 to 1, indicating that InnoDB should need to examine fewer rows to produce the result.

  • The Extra value changes from Using where; Using index to Using index. This means that rows can be read using only the index, without consulting columns in the data row.

Differences in optimizer behavior for use of extended indexes can also be seen with SHOW STATUS:

  1. FLUSH TABLE t1;
  2. FLUSH STATUS;
  3. SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
  4. SHOW STATUS LIKE 'handler_read%'

The preceding statements include FLUSH TABLES and FLUSH STATUS to flush the table cache and clear the status counters.

Without index extensions, SHOW STATUS produces this result:

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 5     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

With index extensions, SHOW STATUS produces this result. The Handler_read_next value decreases from 5 to 1, indicating more efficient use of the index:

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

The use_index_extensions flag of the optimizer_switch system variable permits control over whether the optimizer takes the primary key columns into account when determining how to use an InnoDB table's secondary indexes. By default, use_index_extensions is enabled. To check whether disabling use of index extensions will improve performance, use this statement:

  1. SET optimizer_switch = 'use_index_extensions=off';

Use of index extensions by the optimizer is subject to the usual limits on the number of key parts in an index (16) and the maximum key length (3072 bytes).


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-index-extensions.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