MySQL 8.x에 추가된 세 가지 새로운 인덱스 방법에 대한 상세 분석(요약 공유)
- WBOY앞으로
- 2021-12-30 18:39:222498검색
이 기사에서는 MySQL 8.x 버전에 추가된 세 가지 새로운 인덱스에 대한 정보를 제공합니다. MySQL 8.x에는 숨겨진 인덱스, 내림차순 인덱스, 함수 인덱스라는 세 가지 새로운 인덱스 방법이 있습니다. 이것이 모든 사람에게 도움이 되기를 바랍니다.
1. 히든 인덱스
1. 히든 인덱스 개요
- MySQL 8.0에서 보이지 않는 인덱스, 보이지 않는 인덱스가 지원됩니다.
- 숨겨진 인덱스는 최적화 프로그램에서 사용되지 않지만 여전히 유지 관리되어야 합니다.
- 응용 프로그램 시나리오: 소프트 삭제, 회색조 게시.
이전 버전의 MySQL에서는 인덱스를 명시적으로만 삭제할 수 있었습니다. 삭제 후 잘못된 인덱스가 발견되면 삭제된 인덱스는 데이터베이스의 데이터 양이 너무 많은 경우에만 인덱스를 생성하여 다시 추가할 수 있었습니다. 크거나 테이블이 상대적으로 크면 이 작업 비용이 매우 높습니다.
MySQL 8.0에서는 쿼리 최적화 프로그램이 더 이상 이 인덱스를 사용하지 않도록 먼저 이 인덱스를 숨겨진 인덱스로 설정해야 합니다. 그러나 이 인덱스는 현재로서는 MySQL 백엔드에서 유지 관리해야 합니다. 이 인덱스를 숨겨진 인덱스로 설정하십시오. 시스템이 영향을 받지 않으면 인덱스를 완전히 삭제하십시오. 일시 삭제 기능입니다.
그레이스케일 퍼블리싱이란 인덱스를 생성할 때 먼저 인덱스를 숨겨진 인덱스로 설정하고, 쿼리 옵티마이저의 스위치를 수정하여 숨겨진 인덱스가 쿼리 옵티마이저에 보이도록 하고, explain을 통해 인덱스를 테스트하여 인덱스가 맞는지 확인한다는 뜻입니다. 일부 쿼리에서 이 인덱스를 사용할 수 있는 경우 이를 가시적 인덱스로 설정하여 그레이스케일 게시 효과를 얻을 수 있습니다.
2. 숨겨진 인덱스 작업
(1) MySQL에 로그인하고 testdb 데이터베이스를 생성한 후 데이터베이스에 테스트 테이블 t1을 생성합니다.
mysql> create database if not exists testdb; Query OK, 1 row affected (0.58 sec) mysql> use testdb; Database changed mysql> create table if not exists t1(i int, j int); Query OK, 0 rows affected (0.05 sec)
(2) 아래와 같이 필드 i에 인덱스를 생성합니다.
mysql> create index i_idx on t1(i); Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0
(3) j 필드에 숨겨진 인덱스를 생성합니다. 숨겨진 인덱스를 생성할 때는 아래와 같이 인덱스를 생성하는 문 뒤에 visible 키워드만 추가하면 됩니다.
mysql> create index j_idx on t1(j) invisible; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
(4) 에서 인덱스 상황을 확인하세요. t1 테이블, 아래에 표시된 대로
mysql> show index from t1 \G *************************** 1. row *************************** Table: t1 Non_unique: 1 Key_name: i_idx Seq_in_index: 1 Column_name: i Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: t1 Non_unique: 1 Key_name: j_idx Seq_in_index: 1 Column_name: j Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: NO Expression: NULL 2 rows in set (0.02 sec)
t1 테이블에 두 개의 인덱스가 있음을 알 수 있습니다. 하나는 i_idx이고 다른 하나는 j_idx입니다. i_idx의 Visible 속성은 YES이며, 이는 이 인덱스가 Visibles 속성을 볼 수 있음을 나타냅니다. j_idx가 NO이며 이는 이 인덱스가 표시되지 않음을 나타냅니다.
(5) 쿼리 최적화 프로그램에서 이 두 인덱스의 사용을 확인합니다.
먼저 아래와 같이 i 필드를 사용하여 쿼리합니다.
mysql> explain select * from t1 where i = 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: i_idx key: i_idx key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.02 sec) 可以看到，查询优化器会使用i字段的索引进行优化。 接下来，使用字段j进行查询，如下所示。 mysql> explain select * from t1 where j = 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
쿼리 최적화 프로그램이 j 필드에 숨겨진 인덱스를 사용하지 않고 전체 테이블 스캔을 사용하여 데이터를 쿼리하는 것을 볼 수 있습니다.
(6) 숨겨진 인덱스를 옵티마이저에 표시
MySQL 8.x에서는 새로운 테스트 방법을 제공합니다. 옵티마이저의 스위치를 통해 특정 설정을 활성화하여 숨겨진 인덱스를 쿼리 옵티마이저에 표시할 수 있습니다.
아래와 같이 쿼리 최적화 스위치를 확인하세요.
mysql> select @@optimizer_switch \G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on 1 row in set (0.00 sec)
여기에서 다음 속성 값을 볼 수 있습니다.
use_invisible_indexes=off
는 최적화 프로그램이 보이지 않는 인덱스를 사용하는지 여부를 나타냅니다. 기본값은 꺼져 있으며 사용되지 않습니다.
다음으로 아래와 같이 쿼리 최적화 프로그램이 MySQL의 세션 수준에서 보이지 않는 인덱스를 사용할 수 있도록 활성화합니다.
mysql> set session optimizer_switch="use_invisible_indexes=on"; Query OK, 0 rows affected (0.00 sec)
다음으로, 아래와 같이 쿼리 옵티마이저의 스위치 설정을 다시 확인해보세요
mysql> select @@optimizer_switch \G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=on,skip_scan=on,hash_join=on 1 row in set (0.00 sec)
이때, use_invisible_indexes=on이 보이는데, 이는 숨겨진 인덱스가 쿼리 옵티마이저에 표시됨을 나타냅니다.
아래와 같이 t1 테이블의 j 필드를 사용하여 쿼리 데이터를 다시 분석합니다.
mysql> explain select * from t1 where j = 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: j_idx key: j_idx key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
쿼리 최적화 프로그램이 j 필드의 숨겨진 인덱스를 사용하여 쿼리를 최적화하는 것을 볼 수 있습니다.
(7) 표시 및 비가시 인덱스 설정
아래와 같이 필드 j의 숨겨진 인덱스를 표시로 설정합니다.
mysql> alter table t1 alter index j_idx visible; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
아래 그림과 같이 j 필드의 인덱스를 보이지 않음으로 설정하세요.
mysql> alter table t1 alter index j_idx invisible; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
(8) MySQL의 기본 키는 보이지 않는 인덱스로 설정할 수 없습니다.
MySQL에서는 기본 키를 보이지 않는 인덱스로 설정할 수 없다는 점에 주목할 필요가 있습니다.
아래와 같이 testdb 데이터베이스에 테스트 테이블 t2를 생성합니다.
mysql> create table t2(i int not null); Query OK, 0 rows affected (0.01 sec)
다음으로, t2 테이블에 아래와 같이 보이지 않는 기본 키를 생성합니다.
mysql> alter table t2 add primary key pk_t2(i) invisible; ERROR 3522 (HY000): A primary key index cannot be invisible
SQL 문에서 오류가 발생하여 기본 키를 보이지 않는 인덱스로 설정할 수 없는 것을 확인할 수 있습니다.
2. 내림차순 인덱스
1. 내림차순 인덱스 개요
- MySQL 8.0은 내림차순 인덱스를 완벽하게 지원합니다.
- InnoDB 스토리지 엔진만 내림차순 인덱스를 지원하고, BTREE 내림차순 인덱스만 지원합니다.
- MySQL 8.0은 더 이상 GROUP BY 연산에 대해 암시적 정렬을 수행하지 않습니다.
2. 내림차순 인덱스 연산
(1) MySQL 5.7에서 지원되는 구문
먼저 MySQL 5.7에서 테스트 데이터베이스 testdb를 생성하고, 데이터베이스 testdb에서 테스트 테이블을 생성합니다. t2는 아래와 같습니다.
mysql> create database if not exists testdb; Query OK, 0 rows affected (0.71 sec) mysql> use testdb; Database changed mysql> create table if not exists t2(c1 int, c2 int, index idx1(c1 asc, c2 desc)); Query OK, 0 rows affected (0.71 sec)
그 중 t2 테이블에 idx1이라는 인덱스가 생성되었습니다. 인덱스의 c1 필드는 오름차순으로 정렬되고, c2 필드는 내림차순으로 정렬됩니다.
다음으로, 아래와 같이 t2 테이블의 생성 정보를 확인합니다.
mysql> show create table t2 \G*************************** 1. row *************************** Table: t2Create Table: CREATE TABLE `t2` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `idx1` (`c1`,`c2`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.16 sec)
MySQL 버전 5.7의 경우 테이블 생성 정보에서 c1, c2 필드에 대한 정렬 정보가 없고, 기본값이 오름차순임을 알 수 있습니다.
(2) MySQL 8.0에서 지원되는 구문
MySQL 8에서 t2 테이블을 생성합니다. MySQL 8.x에서는 생성된 인덱스에 필드의 정렬 정보가 존재합니다.
(3) MySQL 5.7의 쿼리 최적화 프로그램에 의한 인덱스 사용
먼저 아래와 같이 테이블 t2에 일부 데이터를 삽입합니다.mysql> insert into t2(c1, c2) values(1, 100), (2, 200), (3, 150), (4, 50); Query OK, 4 rows affected (0.19 sec) Records: 4 Duplicates: 0 Warnings: 0
接下来，查询t2表中的数据，如下所示。
mysql> select * from t2; +------+------+ | c1 | c2 | +------+------+ | 1 | 100 | | 2 | 200 | | 3 | 150 | | 4 | 50 | +------+------+ 4 rows in set (0.00 sec)
可以看到，t2表中的数据插入成功。
接下来，查看查询优化器对索引的使用情况，这里，查询语句按照c1字段升序，按照c2字段降序，如下所示。
mysql> explain select * from t2 order by c1, c2 desc \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: indexpossible_keys: NULL key: idx1 key_len: 10 ref: NULL rows: 4 filtered: 100.00 Extra: Using index; Using filesort1 row in set, 1 warning (0.12 sec)
可以看到，在MySQL 5.7中，按照c2字段进行降序排序，并没有使用索引。
（4）MySQL 8.x中查询优化器对降序索引的使用情况。
查看查询优化器对降序索引的使用情况。
首先，在表t2中插入一些数据，如下所示。
mysql> insert into t2(c1, c2) values(1, 100), (2, 200), (3, 150), (4, 50); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
接下来，查询t2表中的数据，如下所示。
mysql> select * from t2; +------+------+ | c1 | c2 | +------+------+ | 1 | 100 | | 2 | 200 | | 3 | 150 | | 4 | 50 | +------+------+ 4 rows in set (0.00 sec)
可以看到，t2表中的数据插入成功。
在MySQL中如果创建的是升序索引，则指定查询的时候，只能按照升序索引的方式指定查询，这样才能使用升序索引。
接下来，查看查询优化器对索引的使用情况，这里，查询语句按照c1字段升序，按照c2字段降序，如下所示。
mysql> explain select * from t2 order by c1, c2 desc \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: indexpossible_keys: NULL key: idx1 key_len: 10 ref: NULL rows: 4 filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.00 sec)
可以看到，在MySQL 8.x中，按照c2字段进行降序排序，使用了索引。
使用c1字段降序，c2字段升序排序，如下所示。
mysql> explain select * from t2 order by c1 desc, c2 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: indexpossible_keys: NULL key: idx1 key_len: 10 ref: NULL rows: 4 filtered: 100.00 Extra: Backward index scan; Using index1 row in set, 1 warning (0.00 sec)
可以看到，在MySQL 8.x中仍然可以使用索引，并使用了索引的反向扫描。
（5）MySQL 8.x中不再对GROUP BY进行隐式排序
在MySQL 5.7中执行如下命令，按照c2字段进行分组，查询每组中数据的记录条数。
mysql> select count(*), c2 from t2 group by c2; +----------+------+ | count(*) | c2 | +----------+------+ | 1 | 50 | | 1 | 100 | | 1 | 150 | | 1 | 200 | +----------+------+ 4 rows in set (0.18 sec)
可以看到，在MySQL 5.7中，在c2字段上进行了排序操作。
在MySQL 8.x中执行如下命令，按照c2字段进行分组，查询每组中数据的记录条数。
mysql> select count(*), c2 from t2 group by c2; +----------+------+ | count(*) | c2 | +----------+------+ | 1 | 100 | | 1 | 200 | | 1 | 150 | | 1 | 50 | +----------+------+ 4 rows in set (0.00 sec)
可以看到，在MySQL 8.x中，在c2字段上并没有进行排序操作。
在MySQL 8.x中如果需要对c2字段进行排序，则需要使用order by语句明确指定排序规则，如下所示。
mysql> select count(*), c2 from t2 group by c2 order by c2; +----------+------+ | count(*) | c2 | +----------+------+ | 1 | 50 | | 1 | 100 | | 1 | 150 | | 1 | 200 | +----------+------+ 4 rows in set (0.00 sec)
三、函数索引
1.函数索引概述
- MySQL 8.0.13开始支持在索引中使用函数（表达式）的值。
- 支持降序索引，支持JSON数据的索引
- 函数索引基于虚拟列功能实现
2.函数索引操作
（1）创建测试表t3
在testdb数据库中创建一张测试表t3，如下所示。
mysql> create table if not exists t3(c1 varchar(10), c2 varchar(10)); Query OK, 0 rows affected (0.01 sec)
（2）创建普通索引
在c1字段上创建普通索引
mysql> create index idx1 on t3(c1); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
（3）创建函数索引
在c2字段上创建一个将字段值转化为大写的函数索引，如下所示。
mysql> create index func_index on t3 ((UPPER(c2))); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
（4）查看t3表上的索引信息，如下所示。
mysql> show index from t3 \G*************************** 1. row *************************** Table: t3 Non_unique: 1 Key_name: idx1 Seq_in_index: 1 Column_name: c1 Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL*************************** 2. row *************************** Table: t3 Non_unique: 1 Key_name: func_index Seq_in_index: 1 Column_name: NULL Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: upper(`c2`)2 rows in set (0.01 sec)
（5）查看查询优化器对两个索引的使用情况
首先，查看c1字段的大写值是否等于某个特定的值，如下所示。
mysql> explain select * from t3 where upper(c1) = 'ABC' \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)
可以看到，没有使用索引，进行了全表扫描操作。
接下来，查看c2字段的大写值是否等于某个特定的值，如下所示。
mysql> explain select * from t3 where upper(c2) = 'ABC' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: ref possible_keys: func_index key: func_index key_len: 43 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)
可以看到，使用了函数索引。
（6）函数索引对JSON数据的索引
首先，创建测试表emp，并对JSON数据进行索引，如下所示。
mysql> create table if not exists emp(data json, index((CAST(data->>'$.name' as char(30))))); Query OK, 0 rows affected (0.02 sec)
上述SQL语句的解释如下：
- JSON数据长度不固定，如果直接对JSON数据进行索引，可能会超出索引长度，通常，会只截取JSON数据的一部分进行索引。
- CAST()类型转换函数，把数据转化为char(30)类型。使用方式为CAST(数据 as 数据类型)。
- data ->> '$.name’表示JSON的运算符
简单的理解为，就是取name节点的值，将其转化为char(30)类型。
接下来，查看emp表中的索引情况，如下所示。
mysql> show index from emp \G *************************** 1. row *************************** Table: emp Non_unique: 1 Key_name: functional_index Seq_in_index: 1 Column_name: NULL Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: cast(json_unquote(json_extract(`data`,_utf8mb4\'$.name\')) as char(30) charset utf8mb4) 1 row in set (0.00 sec)
（7）函数索引基于虚拟列实现
首先，查看t3表的信息，如下所示。
mysql> desc t3; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c1 | varchar(10) | YES | MUL | NULL | | | c2 | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
在c1上建立了普通索引，在c2上建立了函数索引。
接下来，在t3表中添加一列c3，模拟c2上的函数索引，如下所示。
mysql> alter table t3 add column c3 varchar(10) generated always as (upper(c1)); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
c3列是一个计算列，c3字段的值总是使用c1字段转化为大写的结果。
接下来，向t3表中插入一条数据，其中，c3列是一个计算列，c3字段的值总是使用c1字段转化为大写的结果，在插入数据的时候，不需要为c3列插入数据，如下所示。
mysql> insert into t3(c1, c2) values ('abc', 'def');
Query OK, 1 row affected (0.00 sec)
查询t3表中的数据，如下所示。
mysql> select * from t3; +------+------+------+ | c1 | c2 | c3 | +------+------+------+ | abc | def | ABC | +------+------+------+ 1 row in set (0.00 sec)
可以看到，并不需要向c3列中插入数据，c3列的数据为c1字段的大写结果数据。
如果想模拟函数索引的效果，则可以使用如下方式。
首先，在c3列上添加索引，如下所示。
mysql> create index idx3 on t3(c3); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0
接下来，再次查看c1字段的大写值是否等于某个特定的值，如下所示。
mysql> explain select * from t3 where upper(c1) = 'ABC' \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: ref possible_keys: idx3 key: idx3 key_len: 43 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)
此时，就使用了idx3索引。
推荐学习：mysql视频教程
위 내용은 MySQL 8.x에 추가된 세 가지 새로운 인덱스 방법에 대한 상세 분석(요약 공유)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!