mysql 8.0 in 索引_MySQL 8.0新特性 — 不可见索引

最新推荐文章于 2022-07-13 10:15:45 发布
最新推荐文章于 2022-07-13 10:15:45 发布
阅读量159 收藏
点赞数
文章标签： mysql 8.0 in 索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39707725/article/details/113589246
版权

前言

在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)

weixin_39707725
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
详解MySQL 8.0 之不可见索引
12-14
言 MySQL 8.0 从第一版release 到现在已经走过了4个年头了，8.0版本在功能和...本文算是MySQL 8.0新特性学习的第一篇吧，聊聊 不可见索引。 不可见索引 不可见索引中的不可见是针对优化器而言的，优化器在做执行计划
Mysql5.7及8.0版本索引失效情况汇总
01-19
没有特殊说明，测试环境均为MySQL8.0，早期版本可能会有更多情况导致索引失效。8.0失效的情况，早期版本也失效；8.0不失效的情况，早期版本可能失效。 所有测试默认不考虑表为空的情况，特殊情况文中会有说明。 本文...
mysql 8.0 in 索引_新特性解读 | MySQL 8.0 索引特性4-不可见索引
weixin_35437233的博客
02-01 370
原创作者：杨涛涛MySQL 8.0 实现了索引的隐藏属性。当然这个特性很多商业数据库早就有了，比如ORACLE，在11g中就实现了。我来介绍下这个小特性。介绍INVISIBLE INDEX，不可见索引或者叫隐藏索引。就是对优化器不可见，查询的时候优化器不会把她作为备选。其实以前要想彻底不可见，只能用开销较大的drop index；现在有了新的方式，可以改变索引的属性，让其不可见，这一操作只更改me...
mysql 8.0 in 索引_牛逼！MySQL 8.0 中的索引可以隐藏了
weixin_39963819的博客
02-01 92
MySQL 8.0 虽然发布很久了，但可能大家都停留在 5.7.x，甚至更老，其实 MySQL 8.0 新增了许多重磅新特性，比如今天要介绍的 "隐藏索引" 或者 "不可见索引"。隐藏索引是什么鬼？隐藏索引字面意思就是把索引进行隐藏，即不可见，它不是用来查询优化的，所以它不会被优化器使用到。隐藏索引适用于除主键索引(显示或者隐式设置)之外的索引，意味着主键索引是不能通过任何方式隐藏的。MySQL ...
MySQL8.0新特性——不可见索引（Invisible Indexes）
weixin_34138255的博客
07-16 336
MySQL8.0新特性——不可见索引（Invisible Indexes）MySQL8.0开始支持看不见的索引。一个看不见的索引根本不被优化器使用，但是通常是保持正常的。默认情况下索引是可见的。不可见的索引使测试在查询性能上删除索引的效果成为可能，而不需要在需要索引的情况下进行破坏性的更改。注意：该特性适用于除主键以外的索引（显式或隐式）默认情况下索引是可见的！。将ind...
mysql用in走索引吗？（mysql-8.0.21、5.5.40是不一样的）实践出真知——看完就要注意in的用法了
秃了也弱了
07-13 3078
注！以下都是在mysql-8.0.21版本下进行测试的。执行计划还不熟悉的小伙伴请先学习执行计划： MySQL高级-索引是个什么东西？explain到底怎么用-MySQL查询优化大全 预先在表中随便插入12条数据。 我们发现type是const，相当于mysql对in做了优化，相当于 id = 1了。 在这里，in了三个数据，mysql认为是一个范围查询，同样也会使用索引。 数据表中总共有12条数据，in的条件有了11条，mysql会做一下优化，不使用索引，会走全表扫描。注：实际生产环境数据量并不会仅
mysql8参考手册_mysql8 参考手册--索引提示
weixin_42360993的博客
01-30 162
索引提示为优化器提供有关在查询处理期间如何选择索引的信息。此处描述的索引提示与第8.9.3节“优化器提示”中描述的 优化器提示不同。索引和优化器提示可以单独使用，也可以一起使用。索引提示仅适用于SELECT 和UPDATE语句。在表名后指定索引提示。(有关在语句中指定表的一般语法 SELECT，请参见 第13.2.10.2节“ JOIN子句”。)引用单个表(包括索引提示)的语法如下所示：tbl_n...
mysql 8.0空间索引_MySQL 8.0新特性 -- 降序索引
weixin_39597323的博客
01-28 127
mysql 8开始支持降序索引：在索引定义中使用desc不再被忽略，存储键值是按照降序的方式存储。在之前的版本中，可以按照反向顺序扫描索引，但是会有性能牺牲。降序索引可以正向顺序扫描，效率更高。当最有效的扫描顺序混合了某些列的升序和其他列的降序时，降序索引也使优化器能够使用多列索引。考虑如下表定义：create table t (c1 int, c2 int,index idx1 (c1 asc,...
mysql 8.0空间索引_MySQL 8.0 中的索引可以隐藏了…
weixin_32459211的博客
02-01 212
MySQL 8.0 虽然发布很久了，但可能大家都停留在 5.7.x，甚至更老，其实 MySQL 8.0 新增了许多重磅新特性，比如栈长今天要介绍的 “隐藏索引“ 或者 “不可见索引“。隐藏索引是什么鬼？隐藏索引字面意思就是把索引进行隐藏，即不可见，它不是用来查询优化的，所以它不会被优化器使用到。隐藏索引适用于除主键索引(显示或者隐式设置)之外的索引，意味着主键索引是不能通过任何方式隐藏的。MySQ...
mysql8添加索引_MySQL8.0新特性-新的索引方式
weixin_30929623的博客
01-19 1312
三种新的索引方式1、隐藏索引MySQL8.0 支持隐藏索引(invisible index)，不可见索引隐藏索引不会被优化器使用，但需要维护。应用场景：软删除、灰度发布。软删除：不确定当前索引是否需要删除的时候，软删除，不会彻底删除，可以恢复索引，不需要重新创建，但需要维护。灰度发布：测试索引，对当前数据库不会参生太多影响，确认有效后，可以取消隐藏，改变为正常索引。操作：create table ...
MySQL8新特性：降序索引详解
09-09
在数据库中我们一般都会对一些字段进行索引操作,这样可以提升数据的查询速度，下面这篇文章主要给大家介绍了关于MySQL8新特性：降序索引的相关资料，需要的朋友可以参考借鉴，下面随着小编来一起学习学习吧
MySQL 8.0 新特性之哈希连接（Hash Join）
01-19
MySQL 开发组于 2019 年 10 月 14 日 正式发布了 MySQL 8.0.18 GA 版本，带来了一些新特性和增强功能。其中最引人注目的莫过于多表连接查询支持 hash join 方式了。我们先来看看官方的描述： MySQL 实现了用于内连接...
新版 MySQL DBA 高级视频 基于MySQL 5.7 MySQL 8.0版本.rar
07-17
│ 第十三课MySQL8.0高可用架构之Mycat.pdf │ 第十九课MySQL备份和恢复.pdf │ 第十二课MySQL5.7复制.pdf │ 第十二课MySQL8.0复制.pdf │ 第十五课MySQL8.0高可用架构之MHA和MMM.pdf │ 第十五课MySQL高可用架构之...
基于Matlab的Elman神经网络的数据预测-电力负荷预测模型研究
06-04
【作品名称】：基于Matlab的Elman神经网络的数据预测—电力负荷预测模型研究 【适用人群】：适用于希望学习不同技术领域的小白或进阶学习者。可作为毕设项目、课程设计、大作业、工程实训或初期项目立项。 【项目介绍】：基于Matlab的Elman神经网络的数据预测—电力负荷预测模型研究
K-means聚类算法C++实现，提供python接口
06-04
K-means聚类算法C++实现，提供python接口
Android奇艺高清UI界面源代码.rar
06-04
android studio开发app项目，Android奇艺高清UI界面源代码
linux常用命令大全.docx
06-04
linux常用命令大全 Linux常用命令大全如下，为了保持回答清晰和易于理解，我将这些命令按照其功能进行分类，并辅以必要的解释和示例： 一、文件和目录管理 ls：列出目录内容 -l：以长格式显示文件和目录的详细信息 -a：显示所有文件和目录，包括隐藏文件 -S：按文件大小排序 cd：切换当前目录 cd ~：切换到用户主目录 cd -：切换到上一次所在的目录 cd ..：切换到上一级目录 pwd：显示当前工作目录的绝对路径 mkdir：创建目录 mkdir dirname：创建名为dirname的目录 mkdir -p dir1/dir2：递归创建多级目录 rm：删除文件或目录 rm filename：删除名为filename的文件 rm -r dirname：递归删除名为dirname的目录及其内容 mv：移动或重命名文件/目录 mv oldname
simfang.ttf.zip
06-04
simfang.ttf.zip
基于AI框架的智能工厂设计思路+zzt.pptx
最新发布
06-04
基于AI框架的智能工厂设计思路+zzt.pptx
mysql8.0配置max_connections，innodb_buffer_pool_size
07-27
在MySQL 8.0中，可以通过配置max_connections和innodb_buffer_pool_size来优化性能。下面是如何配置这些参数的方法： 1. max_connections：这个参数用于设置MySQL服务器允许的最大连接数。默认值为151。可以根据...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值