• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 二级索引和生成的列

    InnoDB支持虚拟生成的列上的二级索引。不支持其他索引类型。在虚拟列上定义的二级索引有时称为“虚拟索引”。

    可以在一个或多个虚拟列上或在虚拟列和常规列或存储的生成列的组合上创建二级索引。包含虚拟列的二级索引可以定义为UNIQUE

    在虚拟生成的列上创建二级索引时,生成的列值将在索引记录中实现。如果索引是覆盖索引(包括查询所检索的所有列的索引),则从索引结构中的物化值中检索生成的列值,而不是“即时”计算。

    在虚拟列上使用二级索引时,由于在INSERTUPDATE操作期间在二级索引记录中实现虚拟列值时要执行计算,因此要考虑额外的写入成本。即使有额外的写入成本,虚拟列上的二级索引也可能比生成的存储列更好,后者在聚簇索引中实现,从而导致需要更多磁盘空间和内存的较大表。如果未在虚拟列上定义二级索引,则读取会产生额外成本,因为每次检查列的行时都必须计算虚拟列值。

    对索引的虚拟列的值进行MVCC记录,以避免在回滚或清除操作期间对生成的列值进行不必要的重新计算。记录的值的数据长度是由767个字节的索引关键字限制的限制COMPACTREDUNDANT排格式,并为3072个字节DYNAMICCOMPRESSED列格式。

    在虚拟列上添加或删除二级索引是就地操作。

    索引生成的列以提供JSON列索引

    如其他地方所述,JSON不能直接对列进行索引。要创建间接引用该列的索引,您可以定义一个生成的列,该列提取应建立索引的信息,然后在生成的列上创建索引,如本示例所示:

    mysql> CREATE TABLE jemp (
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id")),
    ->     INDEX i (g)
    -> );
    Query OK, 0 rows affected (0.28 sec)
    
    mysql> INSERT INTO jemp (c) VALUES
    >   ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
    >   ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
    Query OK, 4 rows affected (0.04 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    
    mysql> SELECT c->>"$.name" AS name
    >     FROM jemp WHERE g > 2;
    +--------	+
    | name   	|
    +--------	+
    | Barney 	|
    | Betty  	|
    +--------	+
    2 rows in set (0.00 sec)
    
    mysql> EXPLAIN SELECT c->>"$.name" AS name
    >    FROM jemp WHERE g > 2\G
    *************************** 1. row 	***************************
               id	: 1
      select_type	: SIMPLE
            table	: jemp
       partitions	: NULL
             type	: range
    possible_keys	: i
              key	: i
          key_len	: 5
              ref	: NULL
             rows	: 2
         filtered	: 100.00
            Extra	: Using where
    1 row in set, 1 warning (0.00 sec)
    
    mysql> SHOW WARNINGS\G*************************** 1. row 	***************************
      Level	: Note
       Code	: 1003
    Message	: /	* select#1 	*/ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))
    AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)
    1 row in set (0.00 sec)
    

    (在此示例中,我们包装了最后一条语句的输出以适合参见区域。)

    EXPLAINSELECT包含一个或多个使用->or ->>运算符的一个或其他SQL语句上使用时,这些表达式将使用JSON_EXTRACT()和(如果需要)转换为它们的等价形式JSON_UNQUOTE(),如SHOW WARNINGSEXPLAIN语句紧随其后的输出所示:

    mysql> EXPLAIN SELECT c->>"$.name"
    > FROM jemp WHERE g > 2 ORDER BY c->"$.name"\G
    *************************** 1. row 	***************************
               id	: 1
      select_type	: SIMPLE
            table	: jemp
       partitions	: NULL
             type	: range
    possible_keys	: i
              key	: i
          key_len	: 5
              ref	: NULL
             rows	: 2
         filtered	: 100.00
            Extra	: Using where; Using filesort
    1 row in set, 1 warning (0.00 sec)
    
    mysql> SHOW WARNINGS\G*************************** 1. row 	***************************
      Level	: Note
       Code	: 1003
    Message	: /	* select#1 	*/ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name')) AS
    `c->>"$.name"` from `test`.`jemp` where (`test`.`jemp`.`g` > 2) order by
    json_extract(`test`.`jemp`.`c`,'$.name')
    1 row in set (0.00 sec)
    

    有关其他信息和示例,请参见->->>运算符的说明以及JSON_EXTRACT()JSON_UNQUOTE()功能的说明。

    此技术还可用于提供间接引用无法直接建立索引的其他类型的列(例如GEOMETRY列)的索引。

    NDB群集中的JSON列和间接索引

    在满足以下条件的情况下,还可以在MySQL NDB Cluster中使用JSON列的间接索引:

    1. NDBJSON内部将列值作为来处理BLOB。这意味着任何NDB具有一个或多个JSON列的表都必须具有主键,否则无法将其记录在二进制日志中。
    2. NDB存储引擎不支持虚拟列的索引。由于生成列的默认值为VIRTUAL,您必须明确指定间接索引应用到的生成列STORED

    CREATE TABLE用于创建jempn此处显示的表的语句是jemp先前显示的表的版本,经过修改使其与NDB

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

    我们可以使用以下INSERT语句填充该表:

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

    现在NDB可以使用index i,如下所示:

    mysql> EXPLAIN SELECT c->>"$.name" AS name
              FROM jempn WHERE g > 2\G
    *************************** 1. row 	***************************
               id	: 1
      select_type	: SIMPLE
            table	: jempn
       partitions	: p0,p1
             type	: range
    possible_keys	: i
              key	: i
          key_len	: 5
              ref	: NULL
             rows	: 3
         filtered	: 100.00
            Extra	: Using where with pushed condition (`test`.`jempn`.`g` > 2)
    1 row in set, 1 warning (0.00 sec)
    
    mysql> SHOW WARNINGS\G*************************** 1. row 	***************************
      Level	: Note
       Code	: 1003
    Message	: /	* select#1 	*/ select
    json_unquote(json_extract(`test`.`jempn`.`c`,'$.name')) AS `name` from
    `test`.`jempn` where (`test`.`jempn`.`g` > 2)
    1 row in set (0.00 sec)
    

    您应该记住,使用存储的生成列DataMemory,并使用该列上的索引IndexMemory