MySQL 8.0新特性 — 不可见索引
原创MySQL 8.0新特性 — 不可见索引
原创
brightdeng@DBA
修改于 2020-09-22 21:21:28
修改于 2020-09-22 21:21:28
文章被收录于专栏：腾讯云数据库专家服务
前言
在MySQL 8.0中，引入了不可见索引的新特性；不可见索引，是指实际存在但不会被优化器选用的索引。有童鞋就会问，不可见索引究竟有什么用？虽然在大多数情况下，业务系统新模块的上线，是需要经过充分测试；索引的创建与删除，也是需要经过测试环境的验证；但是生产环境的复杂性，有时候是测试环境无法完全模拟的，包括环境配置不一样、并发量不一样、模块间关联未充分测试等等。在这个时候，不可见索引的作用就体现出来了，它可以替代索引的创建与删除，并对其造成的性能影响进行充分验证，一旦出现系统性能急剧下降的情况，DBA可以进行快速回退，而不需要真正地重新创建或删除索引。
不可见索引
创建、修改与删除
（1）先创建一张测试表
mysql> show create table sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`k` int unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
1 row in set (0.00 sec)（2）可以通过下列语句，创建不可见索引
mysql> create unique index idx_c on sbtest1(c) invisible;
Query OK, 0 rows affected (48.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table sbtest1 add unique index idx_c(c) invisible;
Query OK, 0 rows affected (46.46 sec)
Records: 0 Duplicates: 0 Warnings: 0（3）可以通过下列语句，查看索引是否可见
mysql> select table_name,index_name,is_visible from statistics where table_name='sbtest1';
+------------+------------+------------+
| TABLE_NAME | INDEX_NAME | IS_VISIBLE |
+------------+------------+------------+
| sbtest1 | idx_c | NO |
| sbtest1 | k_1 | YES |
| sbtest1 | PRIMARY | YES |
+------------+------------+------------+
3 rows in set (0.00 sec)（3）可以通过下列语句，修改索引为可见/不可见，操作瞬间完成
mysql> alter table sbtest1 alter index idx_c visible;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select table_name,index_name,is_visible from statistics where table_name='sbtest1';
+------------+------------+------------+
| TABLE_NAME | INDEX_NAME | IS_VISIBLE |
+------------+------------+------------+
| sbtest1 | idx_c | YES |
| sbtest1 | k_1 | YES |
| sbtest1 | PRIMARY | YES |
+------------+------------+------------+
3 rows in set (0.00 sec)
mysql> alter table sbtest1 alter index idx_c invisible;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select table_name,index_name,is_visible from statistics where table_name='sbtest1';
+------------+------------+------------+
| TABLE_NAME | INDEX_NAME | IS_VISIBLE |
+------------+------------+------------+
| sbtest1 | idx_c | NO |
| sbtest1 | k_1 | YES |
| sbtest1 | PRIMARY | YES |
+------------+------------+------------+
3 rows in set (0.00 sec)（4）可以通过下列语句，删除不可见索引，和普通语法没有区别
mysql> alter table sbtest1 drop index idx_c;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0主键与唯一索引
（1）主键不能设置为不可见索引，否则会报错
ERROR 3522 (HY000): A primary key index cannot be invisible.（2）唯一索引设置为不可见索引，其唯一性约束仍然起作用；这也验证了不可见索引是实际存在的，只是不会被优化器选用而已
mysql> insert into sbtest1(k,c,pad) values(100000000,'99907662367-62033881009-89908444702-51825593866-93211481039-94506998046-78149782577-98198214485-50816401066-69413755460','100000000');
ERROR 1062 (23000): Duplicate entry '99907662367-62033881009-89908444702-51825593866-93211481039-9450' for key 'sbtest1.idx_c'优化器与hint
（1）不可见索引是不会被优化器选用的，即使指定hint也会报错
mysql> select count(*) from sbtest1 force index(idx_c);
ERROR 1176 (42000): Key 'idx_c' doesn't exist in table 'sbtest1'
mysql> select count(*) from sbtest1 ignore index(idx_c);
ERROR 1176 (42000): Key 'idx_c' doesn't exist in table 'sbtest1'（2）其实不可见索引是否会被优化器选用，是由系统参数optimizer_switch里面的use_invisible_indexes决定的，默认值为off，即不选用
mysql> show global variables like 'optimizer_switch';
+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)（3）修改系统参数optimizer_switch为use_invisible_indexes=on，不可见索引也可以被优化器使用
mysql> set optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'optimizer_switch';
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)
mysql> explain select * from sbtest1 where c='99907662367-62033881009-89908444702-51825593866-93211481039-94506998046-78149782577-98198214485-50816401066-69413755460';
+----+-------------+---------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | sbtest1 | NULL | const | idx_c | idx_c | 480 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)案例分享
下面分享一个案例，由于涉及到业务数据，按照惯例还是采用模拟场景的方式进行。
（1）先看一下表结构，并对数据进行一些处理
mysql> show create table sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`k` int unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
1 row in set (0.00 sec)
mysql> update sbtest1 set c=id;
Query OK, 1000000 rows affected (45.63 sec)
Rows matched: 1000000 Changed: 1000000 Warnings: 0
mysql> update sbtest1 set k=50000 where id>=1 and id<=500000;
Query OK, 500000 rows affected (14.88 sec)
Rows matched: 500000 Changed: 500000 Warnings: 0
mysql> update sbtest1 set k=100000 where id>=500001 and id<=1000000;
Query OK, 500000 rows affected (7.73 sec)
Rows matched: 500000 Changed: 500000 Warnings: 0
mysql> update sbtest1 set k=90000 where id>=990000;
Query OK, 10000 rows affected (0.23 sec)
Rows matched: 10001 Changed: 10000 Warnings: 0（2）在系统正常运行时，SQL执行时间不到0.05s，走的是索引k_1，IO消耗为2000
mysql> explain select * from sbtest1 where k=90000 order by c limit 10;
+----+-------------+---------+------------+------+---------------+------+---------+-------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+-------+----------+----------------+
| 1 | SIMPLE | sbtest1 | NULL | ref | k_1 | k_1 | 4 | const | 18648 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+-------+-------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from sbtest1 where k=90000 order by c limit 10;
+---------+-------+---------+-------------------------------------------------------------+
| id | k | c | pad |
+---------+-------+---------+-------------------------------------------------------------+
| 1000000 | 90000 | 1000000 | 21274613911-54598120456-20906051591-52129483536-98716060176 |
| 990000 | 90000 | 990000 | 81569048735-26923836594-41822463918-98923634868-51879762493 |
| 990001 | 90000 | 990001 | 57782065461-88000091385-17234986881-85737038863-66040422981 |
| 990002 | 90000 | 990002 | 32760496433-85468684257-56773927923-76775144432-32331188931 |
| 990003 | 90000 | 990003 | 85353406403-85871958237-23382069380-38907624866-56114779853 |
| 990004 | 90000 | 990004 | 16494105521-13670330246-31726652156-68602608347-72711713042 |
| 990005 | 90000 | 990005 | 68732492107-91658633940-55334370011-34107784397-36039660021 |
| 990006 | 90000 | 990006 | 50074262561-59558744241-89592634212-37169183025-95728156487 |
| 990007 | 90000 | 990007 | 02898862065-51887606772-80955920346-60902214697-18429343536 |
| 990008 | 90000 | 990008 | 61394882410-01601169839-80366386107-42429142286-75028463116 |
+---------+-------+---------+-------------------------------------------------------------+
10 rows in set (0.05 sec)
mysql> show profiles;
+----------+------------+---------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------+
| 1 | 0.04385100 | select * from sbtest1 where k=90000 order by c limit 10 |
+----------+------------+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile all for query 1;
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
| starting | 0.000104 | 0.000000 | 0.000095 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | NULL | NULL | NULL |
| Executing hook on transaction | 0.000009 | 0.000000 | 0.000009 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1119 |
| starting | 0.000010 | 0.000000 | 0.000010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1121 |
| checking permissions | 0.000008 | 0.000000 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | check_access | sql_authorization.cc | 2203 |
| Opening tables | 0.000042 | 0.000000 | 0.000042 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | open_tables | sql_base.cc | 5605 |
| init | 0.000006 | 0.000000 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 687 |
| System lock | 0.000010 | 0.000000 | 0.000010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 329 |
| optimizing | 0.000012 | 0.000000 | 0.000012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | optimize | sql_optimizer.cc | 282 |
| statistics | 0.005436 | 0.000000 | 0.000395 | 10 | 0 | 96 | 0 | 0 | 0 | 0 | 14 | 0 | optimize | sql_optimizer.cc | 502 |
| preparing | 0.000023 | 0.000000 | 0.000022 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | optimize | sql_optimizer.cc | 583 |
| executing | 0.038095 | 0.015096 | 0.000758 | 140 | 0 | 2000 | 0 | 0 | 0 | 0 | 20 | 0 | ExecuteIteratorQuery | sql_union.cc | 1082 |
| end | 0.000016 | 0.000011 | 0.000013 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 740 |
| query end | 0.000006 | 0.000004 | 0.000004 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4618 |
| waiting for handler commit | 0.000012 | 0.000005 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ha_commit_trans | handler.cc | 1590 |
| closing tables | 0.000010 | 0.000005 | 0.000005 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4669 |
| freeing items | 0.000024 | 0.000017 | 0.000019 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5348 |
| cleaning up | 0.000029 | 0.000021 | 0.000022 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2183 |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
17 rows in set, 1 warning (0.00 sec)（3）由于新模块上线，新增索引idx_c(c)，直接导致系统崩溃；再次对上述SQL进行分析，执行时间约为7s，走的是索引idx_c，IO消耗571568；无论是执行效率还是资源消耗，都远远高于之前，这也就不难理解为什么会导致系统崩溃
mysql> alter table sbtest1 add index idx_c(c);
Query OK, 0 rows affected (53.91 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from sbtest1 where k=90000 order by c limit 10;
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sbtest1 | NULL | index | k_1 | idx_c | 480 | NULL | 508 | 1.97 | Using where |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from sbtest1 where k=90000 order by c limit 10;
+---------+-------+---------+-------------------------------------------------------------+
| id | k | c | pad |
+---------+-------+---------+-------------------------------------------------------------+
| 1000000 | 90000 | 1000000 | 21274613911-54598120456-20906051591-52129483536-98716060176 |
| 990000 | 90000 | 990000 | 81569048735-26923836594-41822463918-98923634868-51879762493 |
| 990001 | 90000 | 990001 | 57782065461-88000091385-17234986881-85737038863-66040422981 |
| 990002 | 90000 | 990002 | 32760496433-85468684257-56773927923-76775144432-32331188931 |
| 990003 | 90000 | 990003 | 85353406403-85871958237-23382069380-38907624866-56114779853 |
| 990004 | 90000 | 990004 | 16494105521-13670330246-31726652156-68602608347-72711713042 |
| 990005 | 90000 | 990005 | 68732492107-91658633940-55334370011-34107784397-36039660021 |
| 990006 | 90000 | 990006 | 50074262561-59558744241-89592634212-37169183025-95728156487 |
| 990007 | 90000 | 990007 | 02898862065-51887606772-80955920346-60902214697-18429343536 |
| 990008 | 90000 | 990008 | 61394882410-01601169839-80366386107-42429142286-75028463116 |
+---------+-------+---------+-------------------------------------------------------------+
10 rows in set (6.84 sec)
mysql> show profiles;
+----------+------------+---------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------+
| 1 | 6.83620800 | select * from sbtest1 where k=90000 order by c limit 10 |
+----------+------------+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile all for query 1;
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
| starting | 0.000091 | 0.000084 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | NULL | NULL | NULL |
| Executing hook on transaction | 0.000008 | 0.000007 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1119 |
| starting | 0.000009 | 0.000009 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1121 |
| checking permissions | 0.000009 | 0.000009 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | check_access | sql_authorization.cc | 2203 |
| Opening tables | 0.000038 | 0.000037 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | open_tables | sql_base.cc | 5605 |
| init | 0.000006 | 0.000006 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 687 |
| System lock | 0.000009 | 0.000010 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 329 |
| optimizing | 0.000011 | 0.000011 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 282 |
| statistics | 0.000081 | 0.000081 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 | 0 | optimize | sql_optimizer.cc | 502 |
| preparing | 0.000021 | 0.000020 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 583 |
| executing | 6.835797 | 1.482490 | 0.148684 | 27759 | 0 | 571568 | 0 | 0 | 0 | 0 | 157 | 0 | ExecuteIteratorQuery | sql_union.cc | 1082 |
| end | 0.000017 | 0.000006 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 740 |
| query end | 0.000005 | 0.000004 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4618 |
| waiting for handler commit | 0.000009 | 0.000006 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ha_commit_trans | handler.cc | 1590 |
| closing tables | 0.000010 | 0.000007 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4669 |
| freeing items | 0.000023 | 0.000015 | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5348 |
| logging slow query | 0.000038 | 0.000025 | 0.000013 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | log_slow_do | log.cc | 1637 |
| cleaning up | 0.000028 | 0.000018 | 0.000009 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2183 |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
18 rows in set, 1 warning (0.01 sec)（4）原因定位后，可以快速将索引idx_c置为不可见，系统恢复正常
mysql> alter table sbtest1 alter index idx_c invisible;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from sbtest1 where k=90000 order by c limit 10;
+----+-------------+---------+------------+------+---------------+------+---------+-------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+-------+----------+----------------+
| 1 | SIMPLE | sbtest1 | NULL | ref | k_1 | k_1 | 4 | const | 18648 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+-------+-------+----------+----------------+
1 row in set, 1 warning (0.01 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from sbtest1 where k=90000 order by c limit 10;
+---------+-------+---------+-------------------------------------------------------------+
| id | k | c | pad |
+---------+-------+---------+-------------------------------------------------------------+
| 1000000 | 90000 | 1000000 | 21274613911-54598120456-20906051591-52129483536-98716060176 |
| 990000 | 90000 | 990000 | 81569048735-26923836594-41822463918-98923634868-51879762493 |
| 990001 | 90000 | 990001 | 57782065461-88000091385-17234986881-85737038863-66040422981 |
| 990002 | 90000 | 990002 | 32760496433-85468684257-56773927923-76775144432-32331188931 |
| 990003 | 90000 | 990003 | 85353406403-85871958237-23382069380-38907624866-56114779853 |
| 990004 | 90000 | 990004 | 16494105521-13670330246-31726652156-68602608347-72711713042 |
| 990005 | 90000 | 990005 | 68732492107-91658633940-55334370011-34107784397-36039660021 |
| 990006 | 90000 | 990006 | 50074262561-59558744241-89592634212-37169183025-95728156487 |
| 990007 | 90000 | 990007 | 02898862065-51887606772-80955920346-60902214697-18429343536 |
| 990008 | 90000 | 990008 | 61394882410-01601169839-80366386107-42429142286-75028463116 |
+---------+-------+---------+-------------------------------------------------------------+
10 rows in set (0.11 sec)
mysql> show profiles;
+----------+------------+---------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------+
| 1 | 0.10889400 | select * from sbtest1 where k=90000 order by c limit 10 |
+----------+------------+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile all for query 1;
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
| starting | 0.000100 | 0.000000 | 0.000092 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | NULL | NULL | NULL |
| Executing hook on transaction | 0.000008 | 0.000000 | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1119 |
| starting | 0.000009 | 0.000000 | 0.000009 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1121 |
| checking permissions | 0.000009 | 0.000000 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | check_access | sql_authorization.cc | 2203 |
| Opening tables | 0.000045 | 0.000000 | 0.000046 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | open_tables | sql_base.cc | 5605 |
| init | 0.000006 | 0.000000 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 687 |
| System lock | 0.000010 | 0.000000 | 0.000009 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 329 |
| optimizing | 0.000011 | 0.000000 | 0.000012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 282 |
| statistics | 0.000086 | 0.000000 | 0.000086 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 | 0 | optimize | sql_optimizer.cc | 502 |
| preparing | 0.000022 | 0.000000 | 0.000022 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 583 |
| executing | 0.108490 | 0.019756 | 0.000084 | 262 | 0 | 4752 | 0 | 0 | 0 | 0 | 15 | 0 | ExecuteIteratorQuery | sql_union.cc | 1082 |
| end | 0.000020 | 0.000006 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 740 |
| query end | 0.000005 | 0.000002 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4618 |
| waiting for handler commit | 0.000012 | 0.000006 | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ha_commit_trans | handler.cc | 1590 |
| closing tables | 0.000011 | 0.000005 | 0.000005 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4669 |
| freeing items | 0.000023 | 0.000011 | 0.000012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5348 |
| cleaning up | 0.000029 | 0.000014 | 0.000016 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2183 |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
17 rows in set, 1 warning (0.00 sec)总结
生产无小事，每一个操作都有可能会引发重大故障。MySQL 8.0引入的不可见索引，可以很好地起到验证创建/删除索引对系统性能的影响，在得到充分验证后，再进行实际的索引创建/删除操作。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
腾讯云开发者
