Use of Index Extensions

本文涉及的产品
云数据库 Tair(兼容Redis),内存型 2GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
简介: InnoDB通过向辅助索引附加主键列自动扩展索引,优化器利用这一特性可生成更高效的查询执行计划。例如,对于定义了主键(i1,i2)和二级索引k_d的表,InnoDB实际将k_d视为(d,i1,i2)。这种扩展能显著提升查询性能,如使用扩展索引时,`key_len`、`ref`值和所需检查的行数均有所优化,同时`Extra`信息显示为`using index`,意味着仅使用索引即可读取数据行。此外,`SHOW STATUS`命令显示使用扩展索引时`Handler_read_next`值更小,证明索引使用效率更高。

InnoDB通过向每个辅助索引附加主键列来自动扩展它。考虑以下表定义:

image.png

此表定义了列(i1,i2)上的主键。它还在列(d)上定义了一个二级索引k_d,但InnoDB在内部扩展了这个索引,并将其视为列(d,i1,i2)。
优化器在确定如何以及是否使用该索引时,会考虑扩展辅助索引的主键列。这可以带来更高效的查询执行计划和更好的性能。
优化器可以将扩展的辅助索引用于ref、range和index_merge索引访问、松散索引扫描访问、连接和排序优化以及MIN()/MAX()优化。
以下示例显示了优化器是否使用扩展的辅助索引如何影响执行计划。假设t1填充了以下行:

image.png

现在考虑这个查询:

image.png

执行计划取决于是否使用扩展索引
当优化器不考虑索引扩展时,它只将索引k_d视为(d)。查询的EXPLAIN产生以下结果:

image.png

当优化器考虑索引扩展时,它将k_d视为(d,i1,i2)。在这种情况下,它可以使用最左侧的索引前缀(d,i1)来生成更好的执行计划:

image.png

在这两种情况下,key都表示优化器使用了辅助索引k_d,但EXPLAIN输出显示了使用扩展索引后的这些改进:
key_len从4个字节变为8个字节,表示键查找使用列d和i1,而不仅仅是d。
ref值从const变为const,const是因为键查找使用了两个键部分,而不是一个。
行数从5减少到1,表明InnoDB需要检查更少的行来产生结果。
Extra值从Using where更改为;using index。这意味着可以仅使用索引读取行,而无需查询数据行中的列。
使用扩展索引的优化器行为的差异也可以在SHOW STATUS中看到:

image.png

前面的语句包括FLUSH TABLES和FLUSH STATUS,用于刷新表缓存和清除状态计数器。
如果没有索引扩展,SHOW STATUS会产生以下结果:

image.png

使用索引扩展,SHOW STATUS会产生此结果。Handler_read_next值从5减小到1,表示更有效地使用索引:
image.png

optimizer_switch系统变量的use_index_extensions标志允许控制优化器在确定如何使用InnoDB表的辅助索引时是否考虑主键列。默认情况下,use_index_extensions处于启用状态。要检查禁用索引扩展是否会提高性能,请使用以下语句:

image.png

优化器对索引扩展的使用受到索引中关键部分数量(16)和最大密钥长度(3072字节)的通常限制。

备注: select_type type ref 关键字意义参考表格 “EXPLAIN Output Columns”, 前面分享中有详细展示,也可见下面表格

image.png

image.pngimage.png

目录
打赏
0
9
10
0
107
分享
相关文章
Go实现智能指针
用结构体实现Go的智能指针。
263 0
Optimizer Use of Generated Column Indexes
MySQL支持对生成的列进行索引并利用这些索引优化查询执行计划。即使查询未直接引用生成列,只要表达式与生成列定义匹配,优化器也会自动使用索引。但表达式需与生成列定义完全一致且结果类型相同。此功能适用于特定运算符如=、<、BETWEEN等。生成列定义需含函数调用或指定运算符。对于JSON值的比较,使用`JSON_UNQUOTE()`以确保正确匹配。若优化器未选择合适索引,可使用索引提示调整。
205 83
Comparison of B-Tree and Hash Indexes
B树和哈希数据结构对索引查询性能至关重要,尤其是在支持选择B树或哈希索引的MEMORY存储引擎上。B树索引适用于=、>、>=、<、<=及BETWEEN运算符,并能用于特定的LIKE比较;而哈希索引则专长于快速等式比较,但不支持范围查询,也无法用于加速ORDER BY操作。合理选择索引类型可显著提升查询效率。
如何使用 HBase Shell 进行数据的批量导入和导出?
如何使用 HBase Shell 进行数据的批量导入和导出?
568 5
技术好文:Redash(开源轻量级智能)生产环境部署及实践(withoutdocker)
Redash(开源轻量级智能)生产环境部署及实践(withoutdocker)
623 0
WHERE Clause Optimization
本节探讨了WHERE子句的优化方法,虽然示例基于SELECT语句,但也适用于DELETE和UPDATE语句。MySQL自动执行多种优化,例如仅计算一次索引使用的常量表达式、快速检测无效表达式、合并HAVING和WHERE子句、优先读取常量表、寻找最佳连接组合、使用内存中的临时表、选择最佳索引以及在某些情况下仅使用索引树解析查询,从而提升查询效率。
|
9月前
|
【python从入门到精通】-- 第一战:安装python
【python从入门到精通】-- 第一战:安装python
100 0
在Linux中,如何启动、停止、重启一个系统服务?
在Linux中,如何启动、停止、重启一个系统服务?
程序员必知:Word设置标题以及多级自动编号——保姆级教程
程序员必知:Word设置标题以及多级自动编号——保姆级教程
203 0

数据库

+关注
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等

登录插画

登录以查看您的控制台资源

管理云资源
状态一览
快捷访问