前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL8.0新特性——invisible indexes

MySQL8.0新特性——invisible indexes

原创
作者头像
沃趣科技
发布2018-05-15 15:40:47
1.3K2
发布2018-05-15 15:40:47
举报
文章被收录于专栏:沃趣科技

|  导语

MySQL 8.0版本中新增了invisible indexes(不可见索引)特性,索引被invisible后,MySQL优化器就会忽略该索引(无此特性时需要删除索引才能达到相同效果),由此能够验证在该索引被删除的情况下对性能的影响程度。

|  怎么创建一个invisible indexes或者修改索引为invisible

在新建表,新建索引时,可以显式声明某索引为invisible。  示例-新建表&新建索引时声明invisible indexes

代码语言:javascript
复制
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:(none) 14:59:15]>use employees;
Database changed
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:05:22]> CREATE TABLE `employees_1` (
-> `emp_no` int(11) NOT NULL,
-> `birth_date` date NOT NULL,
-> `first_name` varchar(14) COLLATE utf8_bin NOT NULL,
-> `last_name` varchar(16) COLLATE utf8_bin NOT NULL,
-> `gender` enum('M','F') COLLATE utf8_bin NOT NULL,
-> `hire_date` date NOT NULL,
-> PRIMARY KEY (`emp_no`),
-> KEY `idx_hire_date` (`hire_date`) INVISIBLE
-> ) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;
Query OK, 0 rows affected (1.34 sec)
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:05:25]>ALTER TABLE employees_1 ADD INDEX idx_birth_date (birth_date) INVISIBLE;
Query OK, 0 rows affected (0.71 sec)
Records: 0 Duplicates: 0 Warnings: 0
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:06:39]>show  create table employees_1\G
*************************** 1. row ***************************
   Table: employees_1
Create Table: CREATE TABLE `employees_1` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`last_name` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`gender` enum('M','F') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`),
KEY `idx_hire_date` (`hire_date`) /*!80000 INVISIBLE */,
KEY `idx_birth_date` (`birth_date`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

对于已有索引,可以修改它为invisible的。对于在线系统,修改索引为invisible能够验证在该索引被删除的情况下对性能的影响程度。

修改某个索引为invisible 示例-修改索引为invisble/visible

代码语言:javascript
复制
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:08:41]>ALTER TABLE employees_1 ALTER INDEX idx_birth_date VISIBLE;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:10:30]>ALTER TABLE employees_1 ALTER INDEX idx_birthd_hired INVISIBLE;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:10:36]>show create table employees_1\G *************************** 1. row ***************************
   Table: employees_1
Create Table: CREATE TABLE `employees_1` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`last_name` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`gender` enum('M','F') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`),
KEY `idx_hire_date` (`hire_date`) /*!80000 INVISIBLE */,
KEY `idx_birth_date` (`birth_date`),
KEY `idx_birthd_hired` (`birth_date`,`hire_date`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

|  验证invisible indexes效果

我们可以通过执行计划来简单验证索引invisible 后的效果  示例-visible indexes:

代码语言:javascript
复制
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:25:59]>explain select emp_no,first_name,last_name,birth_date, hire_date from employees_1 where birth_date='1953-09-02' order by hire_date limit 5;
+----+-------------+-------------+------------+------+----------------+----------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+----------------+----------------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | employees_1 | NULL | ref | idx_birth_date | idx_birth_date | 3 | const | 63 | 100.00 | Using index condition; Using filesort |
+----+-------------+-------------+------------+------+----------------+----------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select `employees`.`employees_1`.`emp_no` AS `emp_no`,`employees`.`employees_1`.`first_name` AS `first_name`,`employees`.`employees_1`.`last_name` AS `last_name`,`employees`.`employees_1`.`birth_date` AS `birth_date`,`employees`.`employees_1`.`hire_date` AS `hire_date` from `employees`.`employees_1` where (`employees`.`employees_1`.`birth_date` = '1953-09-02') order by `employees`.`employees_1`.`hire_date` limit 5

修改为invisible indexes后的效果  示例-修改索引为invisible后explain效果:

代码语言:javascript
复制
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:27:09]>ALTER TABLE employees_1 ALTER INDEX idx_birth_date INVISIBLE;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:27:14]>show create table employees_1\G
*************************** 1. row ***************************
   Table: employees_1
Create Table: CREATE TABLE `employees_1` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`last_name` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`gender` enum('M','F') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`),
KEY `idx_hire_date` (`hire_date`) /*!80000 INVISIBLE */,
KEY `idx_birth_date` (`birth_date`) /*!80000 INVISIBLE */,
KEY `idx_birthd_hired` (`birth_date`,`hire_date`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:27:19]>explain select emp_no,first_name,last_name,birth_date, hire_date from employees_1 where birth_date='1953-09-02' order by hire_date limit 5;
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| 1 | SIMPLE | employees_1 | NULL | ALL | NULL | NULL | NULL | NULL | 283562 | 0.02 | Using where; Using filesort |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select `employees`.`employees_1`.`emp_no` AS `emp_no`,`employees`.`employees_1`.`first_name` AS `first_name`,`employees`.`employees_1`.`last_name` AS `last_name`,`employees`.`employees_1`.`birth_date` AS `birth_date`,`employees`.`employees_1`.`hire_date` AS `hire_date` from `employees`.`employees_1` where (`employees`.`employees_1`.`birth_date` = '1953-09-02') order by `employees`.`employees_1`.`hire_date` limit 5

可以看到,索引被invisible以后, MySQL优化器就看不到这个索引,从而走上了主键索引扫描。

|  invisible indexes原理和让invisible索引优化器可见的黑科技

其实invisible indexes和普通的可见索引是一样维护的,唯一性约束要检查还是得检查。  示例-invisible indexes唯一约束仍然有效:

代码语言:javascript
复制
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:29:18]>ALTER TABLE employees_1 ADD UNIQUE KEY `idx_fn_ln` (first_nam
e, last_name) INVISIBLE;
ERROR 1062 (23000): Duplicate entry 'Erez-Ritzmann' for key 'idx_fn_ln'
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:33:04]>ALTER TABLE departments ALTER INDEX dept_name INVISIBLE;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:33:54]>show create table departments\G
*************************** 1. row ***************************
   Table: departments
Create Table: CREATE TABLE `departments` (
`dept_no` char(4) COLLATE utf8_bin NOT NULL,
`dept_name` varchar(40) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`dept_no`),
UNIQUE KEY `dept_name` (`dept_name`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:34:44]>insert into departments (dept_no,dept_name) values ('d010', 'Sales');
ERROR 1062 (23000): Duplicate entry 'Sales' for key 'dept_name'

但是invisible indexes让MySQL的优化器忽略这个索引,仅此而已。  是否能让优化器不忽略invisible indexes列,MySQL也提供了这个选项 (虽然比较奇怪)- 在 optimizer_switch中设置use_invisible_indexes选项就可以让优化器使用invisible indexes。

继续刚才的例子:  示例-optimizer_switch中设置use_invisible_indexes的explain效果

代码语言:javascript
复制
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:38:19]>show variables like '%optimizer_switch%'\G *************************** 1. row ***************************
Variable_name: optimizer_switch
    Value: 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
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:38:38]>set 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';
Query OK, 0 rows affected (0.00 sec)
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:38:49]>show variables like '%optimizer_switch%'\G *************************** 1. row ***************************
Variable_name: optimizer_switch
    Value: 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
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:38:51]>explain select emp_no,first_name,last_name,birth_date, hire_date from employees_1 where birth_date='1953-09-02' order by hire_date limit 5; +----+-------------+-------------+------------+------+---------------------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------------------------+------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | employees_1 | NULL | ref | idx_birth_date,idx_birthd_hired | idx_birthd_hired | 3 | const | 63 | 100.00 | Using index condition |
+----+-------------+-------------+------------+------+---------------------------------+------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select `employees`.`employees_1`.`emp_no` AS `emp_no`,`employees`.`employees_1`.`first_name` AS `first_name`,`employees`.`employees_1`.`last_name` AS `last_name`,`employees`.`employees_1`.`birth_date` AS `birth_date`,`employees`.`employees_1`.`hire_date` AS `hire_date` from `employees`.`employees_1` where (`employees`.`employees_1`.`birth_date` = '1953-09-02') order by `employees`.`employees_1`.`hire_date` limit 5

可以看到,设置了use_invisible_indexes=off以后,INVISIBLE的索引优化器都可以用到了。

|  主键索引无法invisible

invisible indexes对主键索引无效。对InnoDB来说,数据都存放在主键索引中,主键索引都看不到,优化器没法做全表扫描了。

有一种特殊的场景:隐性主键。表没有定义主键的情况下,会把第一个非空唯一索引当成主键(UNIQUE & NOT NULL),此时这个索引作为隐性主键也无法设置为invisible。  示例-隐性主键无法修改为invisible:

代码语言:javascript
复制
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:46:46]>CREATE TABLE `departments_1` (
-> `dept_no` char(4) COLLATE utf8_bin NOT NULL,
-> `dept_name` varchar(40) COLLATE utf8_bin NOT NULL,
-> UNIQUE KEY `dept_no` (`dept_no`),
-> UNIQUE KEY `dept_name` (`dept_name`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.12 sec)
[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:46:50]>ALTER TABLE departments_1 ALTER INDEX dept_no INVISIBLE;
ERROR 3522 (HY000): A primary key index cannot be invisible

思考:

  • 是否可以设置某些列为invisiable?
  • 修改列为invisiable/visiable会锁表吗?

参考:

https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html  https://mysqlserverteam.com/mysql-8-0-invisible-indexes/

|  作者简介

李春,沃趣科技首席架构师

曾就职于阿里巴巴,全程参与阿里数据架构从Oracle迁移到MySQL过程,参与分布式中间件Cobar设计。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • |  导语
  • |  怎么创建一个invisible indexes或者修改索引为invisible
  • |  验证invisible indexes效果
  • |  invisible indexes原理和让invisible索引优化器可见的黑科技
  • |  主键索引无法invisible
  • 思考:
  • 参考:
  • |  作者简介
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档