Rechercher dans le manuel MySQL

13.1.20.10 Secondary Indexes and Generated Columns

Inhaltsverzeichnis

InnoDB supports secondary indexes on virtual generated columns. Other index types are not supported. A secondary index defined on a virtual column is sometimes referred to as a “virtual index”.

A secondary index may be created on one or more virtual columns or on a combination of virtual columns and regular columns or stored generated columns. Secondary indexes that include virtual columns may be defined as UNIQUE.

When a secondary index is created on a virtual generated column, generated column values are materialized in the records of the index. If the index is a covering index (one that includes all the columns retrieved by a query), generated column values are retrieved from materialized values in the index structure instead of computed “on the fly”.

There are additional write costs to consider when using a secondary index on a virtual column due to computation performed when materializing virtual column values in secondary index records during INSERT and UPDATE operations. Even with additional write costs, secondary indexes on virtual columns may be preferable to generated stored columns, which are materialized in the clustered index, resulting in larger tables that require more disk space and memory. If a secondary index is not defined on a virtual column, there are additional costs for reads, as virtual column values must be computed each time the column's row is examined.

Values of an indexed virtual column are MVCC-logged to avoid unnecessary recomputation of generated column values during rollback or during a purge operation. The data length of logged values is limited by the index key limit of 767 bytes for COMPACT and REDUNDANT row formats, and 3072 bytes for DYNAMIC and COMPRESSED row formats.

Adding or dropping a secondary index on a virtual column is an in-place operation.

Indexing a Generated Column to Provide a JSON Column Index

As noted elsewhere, JSON columns cannot be indexed directly. To create an index that references such a column indirectly, you can define a generated column that extracts the information that should be indexed, then create an index on the generated column, as shown in this example:

  1. mysql> CREATE TABLE jemp (
  2.     ->     c JSON,
  3.     ->     g INT GENERATED ALWAYS AS (c->"$.id")),
  4.     ->     INDEX i (g)
  5.     -> );
  6. Query OK, 0 rows affected (0.28 sec)
  7.  
  8. mysql> INSERT INTO jemp (c) VALUES
  9.      >   ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
  10.      >   ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
  11. Query OK, 4 rows affected (0.04 sec)
  12. Records: 4  Duplicates: 0  Warnings: 0
  13.  
  14. mysql> SELECT c->>"$.name" AS name
  15.      >     FROM jemp WHERE g > 2;
  16. +--------+
  17. | name   |
  18. +--------+
  19. | Barney |
  20. | Betty  |
  21. +--------+
  22. 2 rows in set (0.00 sec)
  23.  
  24. mysql> EXPLAIN SELECT c->>"$.name" AS name
  25.      >    FROM jemp WHERE g > 2\G
  26. *************************** 1. row ***************************
  27.            id: 1
  28.   select_type: SIMPLE
  29.         table: jemp
  30.    partitions: NULL
  31.          type: range
  32. possible_keys: i
  33.           key: i
  34.       key_len: 5
  35.           ref: NULL
  36.          rows: 2
  37.      filtered: 100.00
  38.         Extra: Using where
  39. 1 row in set, 1 warning (0.00 sec)
  40.  
  41. mysql> SHOW WARNINGS\G
  42. *************************** 1. row ***************************
  43.   Level: Note
  44.    Code: 1003
  45. Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))
  46. AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)
  47. 1 row in set (0.00 sec)

(We have wrapped the output from the last statement in this example to fit the viewing area.)

When you use EXPLAIN on a SELECT or other SQL statement containing one or more expressions that use the -> or ->> operator, these expressions are translated into their equivalents using JSON_EXTRACT() and (if needed) JSON_UNQUOTE() instead, as shown here in the output from SHOW WARNINGS immediately following this EXPLAIN statement:

  1. mysql> EXPLAIN SELECT c->>"$.name"
  2.      > FROM jemp WHERE g > 2 ORDER BY c->"$.name"\G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: jemp
  7.    partitions: NULL
  8.          type: range
  9. possible_keys: i
  10.           key: i
  11.       key_len: 5
  12.           ref: NULL
  13.          rows: 2
  14.      filtered: 100.00
  15.         Extra: Using where; Using filesort
  16. 1 row in set, 1 warning (0.00 sec)
  17.  
  18. mysql> SHOW WARNINGS\G
  19. *************************** 1. row ***************************
  20.   Level: Note
  21.    Code: 1003
  22. Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name')) AS
  23. `c->>"$.name"` from `test`.`jemp` where (`test`.`jemp`.`g` > 2) order by
  24. json_extract(`test`.`jemp`.`c`,'$.name')
  25. 1 row in set (0.00 sec)

See the descriptions of the -> and ->> operators, as well as those of the JSON_EXTRACT() and JSON_UNQUOTE() functions, for additional information and examples.

This technique also can be used to provide indexes that indirectly reference columns of other types that cannot be indexed directly, such as GEOMETRY columns.

Inhaltsverzeichnis Haut

JSON columns and indirect indexing in NDB Cluster

It is also possible to use indirect indexing of JSON columns in MySQL NDB Cluster, subject to the following conditions:

  1. NDB handles a JSON column value internally as a BLOB. This means that any NDB table having one or more JSON columns must have a primary key, else it cannot be recorded in the binary log.

  2. The NDB storage engine does not support indexing of virtual columns. Since the default for generated columns is VIRTUAL, you must specify explicitly the generated column to which to apply the indirect index as STORED.

The CREATE TABLE statement used to create the table jempn shown here is a version of the jemp table shown previously, with modifications making it compatible with NDB:

  1. CREATE TABLE jempn (
  2.   a BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.   c JSON DEFAULT NULL,
  4.   g INT GENERATED ALWAYS AS (c->"$.name") STORED,
  5.   INDEX i (g)
  6. ) ENGINE=NDB;

We can populate this table using the following INSERT statement:

  1. INSERT INTO jempn (a, c) VALUES  
  2.   (NULL, '{"id": "1", "name": "Fred"}'),
  3.   (NULL, '{"id": "2", "name": "Wilma"}'),  
  4.   (NULL, '{"id": "3", "name": "Barney"}'),
  5.   (NULL, '{"id": "4", "name": "Betty"}');

Now NDB can use index i, as shown here:

  1. mysql> EXPLAIN SELECT c->>"$.name" AS name        
  2.           FROM jempn WHERE g > 2\G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: jempn
  7.    partitions: p0,p1
  8.          type: range
  9. possible_keys: i
  10.           key: i
  11.       key_len: 5
  12.           ref: NULL
  13.          rows: 3
  14.      filtered: 100.00
  15.         Extra: Using where with pushed condition (`test`.`jempn`.`g` > 2)
  16. 1 row in set, 1 warning (0.00 sec)
  17.  
  18. mysql> SHOW WARNINGS\G
  19. *************************** 1. row ***************************
  20.   Level: Note
  21.    Code: 1003
  22. Message: /* select#1 */ select
  23. json_unquote(json_extract(`test`.`jempn`.`c`,'$.name')) AS `name` from
  24. `test`.`jempn` where (`test`.`jempn`.`g` > 2)  
  25. 1 row in set (0.00 sec)

You should keep in mind that a stored generated column uses DataMemory, and that an index on such a column uses IndexMemory.


Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-create-table-secondary-indexes.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut