Optimizer Use of Generated Column Indexes

本文涉及的产品
云数据库 Tair(兼容Redis),内存型 2GB
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: MySQL支持对生成的列进行索引并利用这些索引优化查询执行计划。即使查询未直接引用生成列,只要表达式与生成列定义匹配,优化器也会自动使用索引。但表达式需与生成列定义完全一致且结果类型相同。此功能适用于特定运算符如=、<、BETWEEN等。生成列定义需含函数调用或指定运算符。对于JSON值的比较,使用`JSON_UNQUOTE()`以确保正确匹配。若优化器未选择合适索引,可使用索引提示调整。

MySQL支持对生成的列进行索引。例如:

image.png

生成的列gc定义为表达式f1+1。该列也被索引,优化器可以在执行计划构建过程中考虑该索引。在以下查询中,WHERE子句引用gc,优化器考虑该列上的索引是否会产生更有效的计划:
image.png

优化器可以在生成的列上使用索引来生成执行计划,即使在查询中没有按名称直接引用这些列的情况下也是如此。如果WHERE、ORDER BY或GROUP BY子句引用的表达式与某个索引生成列的定义匹配,则会发生这种情况。以下查询不直接引用gc,但确实使用了与gc定义匹配的表达式:

image.png

优化器识别出表达式f1+1与gc的定义匹配,并且gc已被索引,因此它在执行计划构建过程中会考虑该索引。您可以使用EXPLAIN看到这一点:

image.png

实际上,优化器已将表达式f1+1替换为与表达式匹配的生成列的名称。这在SHOW WARNINGS显示的扩展EXPLAIN信息中提供的重写查询中也很明显:

image.png

以下限制和条件适用于优化器对生成的列索引的使用:
为了使查询表达式与生成的列定义匹配,该表达式必须相同,并且必须具有相同的结果类型。例如,如果生成的列表达式是f1+1,如果查询使用1+f1,或者如果将f1+1(整数表达式)与字符串进行比较,优化器将无法识别匹配。
优化适用于这些运算符:=、<、<=、>、>=、BETWEEN和IN()。
对于BETWEEN和IN()以外的运算符,任何一个操作数都可以被匹配的生成列替换。对于BETWEEN和IN(),只有第一个参数可以被匹配的生成列替换,其他参数必须具有相同的结果类型。BETWEEN和IN()尚不支持涉及JSON值的比较。
生成的列必须定义为至少包含一个函数调用或前一项中提到的运算符之一的表达式。表达式不能包含对另一列的简单引用。例如,gc INT AS(f1)STORED仅包含一个列引用,因此不考虑gc上的索引。
为了将字符串与从返回引号字符串的JSON函数计算值的索引生成列进行比较,需要在列定义中使用JSON_UNQUOTE()来删除函数值中的额外引号。(对于字符串与函数结果的直接比较,JSON比较器会处理引号删除,但索引查找不会发生这种情况。)例如,与其编写这样的列定义:

image.png

这样写:

image.png

使用后一种定义,优化器可以检测到这两种比较的匹配:

image.png

如果列定义中没有JSON_UNQUOTE(),优化器只会检测到第一个比较的匹配。
如果优化器未能选择所需的索引,则可以使用索引提示迫使优化器做出不同的选择。

相关文章
|
22天前
|
存储 关系型数据库 MySQL
Column Indexes
常见的索引类型通过复制列值至高效数据结构(如B树),实现快速查找。B树助力WHERE子句中=、&gt;、≤、BETWEEN等运算符对应值的检索。每表至少支持16个索引,总长不少于256字节,具体限制依存储引擎而定。字符串列索引可指定前N字符,减少索引文件大小;BLOB或TEXT列索引需指定前缀长度。全文索引用于全文搜索,适用于InnoDB和MyISAM引擎的CHAR、VARCHAR、TEXT列;空间索引则针对空间数据类型,MyISAM和InnoDB采用R树索引。MEMORY引擎默认使用HASH索引,也支持BTREE索引。
|
22天前
|
关系型数据库 MySQL 索引
Multiple-Column Indexes
MySQL 支持创建复合索引(多列索引),最多由 16 列组成,适用于查询中所有或部分列的查找。复合索引如同排序数组,通过连接索引列值创建。正确排列的单个复合索引能加速多种查询。若索引列非最左侧前缀,MySQL 无法使用索引查找。此外,还可引入基于其他列信息“哈希”的列作为替代方案,提高查询效率。
|
22天前
|
存储 关系型数据库 MySQL
Optimization and Indexes
MySQL通过索引快速定位具有特定列值的行,避免全表扫描,提高查询效率。常用的索引如PRIMARY KEY、UNIQUE等大多存储在B树中,特殊情况使用R树或哈希索引。索引帮助快速匹配WHERE子句条件的行,减少候选行数,并在多列索引和表连接操作中优化查询。具体特性如B树和哈希索引的比较见特定章节。
|
2月前
|
SQL
[Err] 1052 - Column ‘roleId‘ in where clause is ambiguous
这篇文章解释了SQL查询中出现"Column ‘roleId’ in where clause is ambiguous"错误的原因,即在多表查询中,如果没有明确指定表名,相同的列名在where子句中会产生歧义,并提供了修正方法,即明确指定条件中所引用的列属于哪个表。
RuntimeError: a view of a leaf Variable that requires grad is being used in an in-place operation.
RuntimeError: a view of a leaf Variable that requires grad is being used in an in-place operation.
2504 0
1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause
1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause
195 0
1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause
|
PHP
【PHP报错集锦】Integrity constraint violation: 1052 Column 'vt_id' in where clause is ambiguous
【PHP报错集锦】Integrity constraint violation: 1052 Column 'vt_id' in where clause is ambiguous
318 0
【PHP报错集锦】Integrity constraint violation: 1052 Column 'vt_id' in where clause is ambiguous
|
关系型数据库 MySQL 数据库
MySQL - Column 'id' in where clause is ambiguous
MySQL - Column 'id' in where clause is ambiguous
773 0
|
SQL 数据库
Database specific hint in One order search
Database specific hint in One order search
Database specific hint in One order search
|
SQL 关系型数据库
[WorkLog] InnoDB Faster truncate/drop table space
这个系列, 介绍upstream 一些有意思的worklog **问题** 在InnoDB 现有的版本里面, 如果一个table space 被truncated 或者 drop 的时候, 比如有一个连接创建了临时表, 连接断开以后, 对应的临时表都需要进行drop 操作. InnoDB 是需要将该tablespace 对应的所有的page 从LRU/FLUSH li
445 0