Rechercher dans le manuel MySQL

13.1.20.10 Secondary Indexes and Generated Columns

Inhoudsopgave

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.

Inhoudsopgave 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.


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-create-table-secondary-indexes.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