营销型网站的建设与推广辅导记录,连锁加盟网络营销公司,短网址转换器,已经买了域名怎么做网站文章目录 前言1. 环境介绍2. MySQL 5.72.1 执行计划2.2 虚拟列优化2.3 原理解析 3. MySQL 8.03.1 执行计划3.2 函数索引优化3.3 原理解析 前言
MySQL 中#xff0c;当 SQL 索引字段使用了函数的话#xff0c;会出现隐式转换的问题#xff0c;导致索引失效#xff0c;从而导… 文章目录 前言1. 环境介绍2. MySQL 5.72.1 执行计划2.2 虚拟列优化2.3 原理解析 3. MySQL 8.03.1 执行计划3.2 函数索引优化3.3 原理解析 前言
MySQL 中当 SQL 索引字段使用了函数的话会出现隐式转换的问题导致索引失效从而导致 SQL 执行效率变慢。本篇文章介绍 MySQL 不同版本此类问题的应对策略。
1. 环境介绍
以下是本篇文章测试需要准备的表结构和环境信息。
数据库版本
MySQL 5.7.37MySQL 8.0.32
测试表结构
CREATE TABLE task_queue (id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 主键,instance_id varchar(50) NOT NULL COMMENT 实例ID,instance_name varchar(50) NOT NULL COMMENT 实例名,create_time datetime NOT NULL COMMENT 任务创建时间,end_time datetime NOT NULL COMMENT 任务结束时间,instance_type varchar(10) NOT NULL COMMENT 数据库类型MySQL、SQLServer,status varchar(3) NOT NULL DEFAULT 未完成 COMMENT 任务状态完成 未完成 异常,PRIMARY KEY (id) USING BTREE,KEY idx_create_time (create_time)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4示例 SQL
select instance_id, instance_name, status from task_queue where DATE_FORMAT(create_time, %Y-%m-%d) 2023-09-102. MySQL 5.7
MySQL 5.7 版本新增了一个虚拟列的新特性可以通过该特性让示例 SQL 可以用到索引。
2.1 执行计划
先看看 SQL 在 5.7 版本的执行计划
{query_block: {select_id: 1,cost_info: {query_cost: 13.20},table: {table_name: task_queue,access_type: ALL,rows_examined_per_scan: 61,rows_produced_per_join: 61,filtered: 100.00,cost_info: {read_cost: 1.00,eval_cost: 12.20,prefix_cost: 13.20,data_read_per_join: 28K},used_columns: [instance_id,instance_name,create_time,status],attached_condition: (date_format(op_service_db_bak.task_queue.create_time,%Y-%m-%d) 2023-11-11)}}
}因为 create_time 字段使用了函数导致 SQL 隐式转换从而导致索引失效。
2.2 虚拟列优化
接下来创建一个虚拟列该列是基于 create_time 计算创建而来。
alter table task_queue add column v_create_time datetime GENERATED ALWAYS AS (DATE_FORMAT(create_time, %Y-%m-%d));然后为该列添加一个索引
alter table task_queue add index idx_v_create_time(v_create_time);此时的表结构如下
CREATE TABLE task_queue (id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 主键,instance_id varchar(50) NOT NULL COMMENT 实例ID,instance_name varchar(50) NOT NULL COMMENT 实例名,create_time datetime NOT NULL COMMENT 任务创建时间,end_time datetime NOT NULL COMMENT 任务结束时间,instance_type varchar(10) NOT NULL COMMENT 数据库类型MySQL、SQLServer,status varchar(3) NOT NULL DEFAULT 未完成 COMMENT 任务状态完成 未完成 异常,v_create_time datetime GENERATED ALWAYS AS (date_format(create_time,%Y-%m-%d)) VIRTUAL,PRIMARY KEY (id) USING BTREE,KEY idx_create_time (create_time),KEY idx_v_create_time (v_create_time)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4再次执行 SQL 查看执行计划
{query_block: {select_id: 1,cost_info: {query_cost: 5.20},table: {table_name: task_queue,access_type: ref,possible_keys: [idx_v_create_time],key: idx_v_create_time,used_key_parts: [v_create_time],key_length: 6,ref: [const],rows_examined_per_scan: 11,rows_produced_per_join: 11,filtered: 100.00,cost_info: {read_cost: 3.00,eval_cost: 2.20,prefix_cost: 5.20,data_read_per_join: 5K},used_columns: [instance_id,instance_name,create_time,status,v_create_time]}}
}通过执行计划可以看到通过创建虚拟列让隐式转换的 SQL 使用上索引且未对原 SQL 进行任何修改只是调整了表结构。
2.3 原理解析
Generated Column 是 MySQL 5.7 引入的新特性就是数据库中的一个字段是由其它字段计算而得。我们以官方文档中的例子给予说明。
例如知道直角三角形的两条直角边要求斜边的长度。很明显斜边的长度可以通过两条直角边计算而得那么这时候就可以在数据库中只存放直角边斜边使用 Generated Column 如下所示
CREATE TABLE triangle (sidea DOUBLE,sideb DOUBLE,sidec DOUBLE AS (SQRT(sidea * sidea sideb * sideb))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);mysql SELECT * FROM triangle;
----------------------------------
| sidea | sideb | sidec |
----------------------------------
| 1 | 1 | 1.4142135623730951 |
| 3 | 4 | 5 |
| 6 | 8 | 10 |
----------------------------------Generated Column 的创建语法如下
col_name data_type [GENERATED ALWAYS] AS (expr)[VIRTUAL | STORED] [NOT NULL | NULL][UNIQUE [KEY]] [[PRIMARY] KEY][COMMENT string]需要注意的是 Generated Column 有两种模式分别是 VIRTUAL 和 STORED默认模式为 VIRTUAL。
VIRTUAL 是将字段的计算逻辑存储在数据字典中既元数据。STORED 是会将计算而得的数据存储到磁盘中会得到持久化存储。
Generated Column 字段支持创建二级索引这也是能解决函数计算造成的索引失效的原因。 推荐阅读13.1.18.7 CREATE TABLE and Generated Columns 3. MySQL 8.0
MySQL 8.0 提供了一个函数索引新特性相比 5.7 的虚拟列可以更方便的解决隐式转换问题。
3.1 执行计划
以下是示例 SQL 在 8.0 版本中的执行计划
{query_block: {select_id: 1,cost_info: {query_cost: 6.35},table: {table_name: task_queue,access_type: ALL,rows_examined_per_scan: 61,rows_produced_per_join: 61,filtered: 100.00,cost_info: {read_cost: 0.25,eval_cost: 6.10,prefix_cost: 6.35,data_read_per_join: 28K},used_columns: [instance_id,instance_name,create_time,status],attached_condition: (date_format(test.task_queue.create_time,%Y-%m-%d) 2023-11-11)}}
}与 MySQL 5.7 版本相同由于隐式转换无法使用 create_time 字段的索引。
3.2 函数索引优化
MySQL 8.0 函数索引创建方式非常简单如下
alter table task_queue add index idx_func_create_time((DATE_FORMAT(create_time, %Y-%m-%d)));通过如下 SQL 可以查看函数索引
mysqlselect table_name,index_name,seq_in_index,column_name,is_visible,expression from information_schema.statistics where table_nametask_queue;
--------------------------------------------------------------------------------------------------------------------------
| TABLE_NAME | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | IS_VISIBLE | EXPRESSION |
--------------------------------------------------------------------------------------------------------------------------
| task_queue | idx_create_time | 1 | create_time | YES | NULL |
| task_queue | idx_func_create_time | 1 | NULL | YES | date_format(create_time,_utf8mb4\%Y-%m-%d\) |
| task_queue | PRIMARY | 1 | id | YES | NULL |
--------------------------------------------------------------------------------------------------------------------------再次执行示例 SQL 查看执行计划
{query_block: {select_id: 1,cost_info: {query_cost: 0.35},table: {table_name: task_queue,access_type: ref,possible_keys: [idx_func_create_time],key: idx_func_create_time,used_key_parts: [date_format(create_time,_utf8mb4%Y-%m-%d)],key_length: 43,ref: [const],rows_examined_per_scan: 1,rows_produced_per_join: 1,filtered: 100.00,cost_info: {read_cost: 0.25,eval_cost: 0.10,prefix_cost: 0.35,data_read_per_join: 520},used_columns: [instance_id,instance_name,create_time,status,date_format(create_time,_utf8mb4%Y-%m-%d)]}}
} 通过执行计划可以看到通过创建函数让隐式转换的 SQL 使用上索引且未对原 SQL 进行任何修改只是调整了表结构。相比于 MySQL 5.7 版本的虚拟列更新便捷。
3.3 原理解析
MySQL 8.0 的函数索引比较好理解就是根据用户指定的计算规则维护一个二级索引来应对更复杂的查询。
以下是函数索引的使用限制。
主键不支持函数索引因为主键以实际列进行存储而函数索引是作为虚拟列存在的在有主键的情况下唯一索引支持函数索引但在无主键的情况下被提升为主键的唯一索引不支持外键不支持函数索引空间索引和全文索引不支持函数索引函数索引不能直接使用列前缀可以通过SUBSTRING()和CAST()来替代在删除列之前要先删除相关的函数索引 推荐阅读Functional Key Parts
