14.4.3 Adaptive Hash Index 自适应hash index

本文详细介绍了MySQL中InnoDB存储引擎的自适应哈希索引(AHI)特性。AHI能够根据查询模式自动创建哈希索引,以加速对B树索引的访问,特别是在内存充足的情况下能显著提升查询性能。文章还讨论了AHI的工作原理、如何监控索引查询以及在何种情况下关闭AHI可能会带来性能上的好处。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

14.4.3 Adaptive Hash Index  自适应hash index

自适应hash index(AHI) 让InnoDB 执行更像内存数据库在系统使用合适的负载组合和足够的内存用于Buffer pool,

基于观察到的搜索模式, MySQL 创建一个hash index 使用一个index key 前缀。

索引的前缀可以任何长度, 可能只有一些值在B树索引里是出现在hash index.


Hash indexes 是基于需求对于那些index 的索引页经常被访问的


如果一个表整个在主内存里, 一个hash index 可以加速查询通过直接查找任何元素,讲索引值变成一个指针。

InnoDB 有一个机制监控索引搜索,如果InnoDB 注意到查询可以从创建的hash index收益,它会自动处理


在一些负载下, 从hash index 查询加速极大的超过了额外的工作用于监控索引查询和维护hash index结构。


有时候, read/write lock 保护访问到自适应的hash index 可以变成连接的来源在高负载下

比如 多核并发关联,查询像%通配也不会从AHI(自适应索引)收益。

mysql> show variables like '%hash%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_adaptive_hash_index    | ON    |

对于负载 自适应索引是不需要的, 关闭它降低不需要的性能负载。

因为它是很难预测的 是否这个功能是合适的对于特定的系统,


考虑运行基准,使用一个现实的负载。


hash index总是基于一个存在的B-tree Index 在表上,InnoDB 可以创建一个hash index 在一个key的任何长度的前缀

定义用于B-tree 取决于搜索的模式 InnoDB观察B树索引。

一个Hash index可以是部分的, 覆盖那些经常被访问的页面

转载于:https://www.cnblogs.com/zhaoyangjian724/p/6199095.html

# 1. 安装并加载所有必要包 > install.packages(c("readxl", "forecast", "tseries", "ggplot2", + "purrr", "dplyr", "tidyr", "ggfortify")) Error in install.packages : Updating loaded packages Restarting R session... > install.packages(c("readxl", "forecast", "tseries", "ggplot2", "purrr", "dplyr", "tidyr", "ggfortify")) WARNING: Rtools is required to build R packages but is not currently installed. Please download and install the appropriate version of Rtools before proceeding: https://cran.rstudio.com/bin/windows/Rtools/ 将程序包安装入‘C:/Users/Lenovo/AppData/Local/R/win-library/4.4’ (因为‘lib’没有被指定) 还安装依赖关系‘farver’, ‘labeling’, ‘RColorBrewer’, ‘viridisLite’, ‘stringi’, ‘colorspace’, ‘fracdiff’, ‘generics’, ‘lmtest’, ‘timeDate’, ‘urca’, ‘withr’, ‘RcppArmadillo’, ‘gtable’, ‘isoband’, ‘scales’, ‘tidyselect’, ‘stringr’, ‘gridExtra’ 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/farver_2.1.2.zip&#39; Content type &#39;application/zip&#39; length 1520066 bytes (1.4 MB) downloaded 1.4 MB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/labeling_0.4.3.zip&#39; Content type &#39;application/zip&#39; length 63169 bytes (61 KB) downloaded 61 KB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/RColorBrewer_1.1-3.zip&#39; Content type &#39;application/zip&#39; length 54471 bytes (53 KB) downloaded 53 KB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/viridisLite_0.4.2.zip&#39; Content type &#39;application/zip&#39; length 1300894 bytes (1.2 MB) downloaded 1.2 MB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/stringi_1.8.7.zip&#39; Content type &#39;application/zip&#39; length 15033853 bytes (14.3 MB) downloaded 14.3 MB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/colorspace_2.1-1.zip&#39; Content type &#39;application/zip&#39; length 2667782 bytes (2.5 MB) downloaded 2.5 MB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/fracdiff_1.5-3.zip&#39; Content type &#39;application/zip&#39; length 107027 bytes (104 KB) downloaded 104 KB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/generics_0.1.4.zip&#39; Content type &#39;application/zip&#39; length 84804 bytes (82 KB) downloaded 82 KB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/lmtest_0.9-40.zip&#39; Content type &#39;application/zip&#39; length 411451 bytes (401 KB) downloaded 401 KB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/timeDate_4041.110.zip&#39; Content type &#39;application/zip&#39; length 1405267 bytes (1.3 MB) downloaded 1.3 MB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/urca_1.3-4.zip&#39; Content type &#39;application/zip&#39; length 1074843 bytes (1.0 MB) downloaded 1.0 MB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/withr_3.0.2.zip&#39; Content type &#39;application/zip&#39; length 232544 bytes (227 KB) downloaded 227 KB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/RcppArmadillo_14.4.3-1.zip&#39; Content type &#39;application/zip&#39; length 2063369 bytes (2.0 MB) downloaded 2.0 MB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/gtable_0.3.6.zip&#39; Content type &#39;application/zip&#39; length 251143 bytes (245 KB) downloaded 245 KB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/isoband_0.2.7.zip&#39; Content type &#39;application/zip&#39; length 1929599 bytes (1.8 MB) downloaded 1.8 MB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/scales_1.4.0.zip&#39; Content type &#39;application/zip&#39; length 881247 bytes (860 KB) downloaded 860 KB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/tidyselect_1.2.1.zip&#39; Content type &#39;application/zip&#39; length 229105 bytes (223 KB) downloaded 223 KB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/stringr_1.5.1.zip&#39; Content type &#39;application/zip&#39; length 324628 bytes (317 KB) downloaded 317 KB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/gridExtra_2.3.zip&#39; Content type &#39;application/zip&#39; length 1109672 bytes (1.1 MB) downloaded 1.1 MB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/readxl_1.4.5.zip&#39; Content type &#39;application/zip&#39; length 750370 bytes (732 KB) downloaded 732 KB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/forecast_8.24.0.zip&#39; Content type &#39;application/zip&#39; length 1909133 bytes (1.8 MB) downloaded 1.8 MB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/tseries_0.10-58.zip&#39; Content type &#39;application/zip&#39; length 386268 bytes (377 KB) downloaded 377 KB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/ggplot2_3.5.2.zip&#39; Content type &#39;application/zip&#39; length 5018490 bytes (4.8 MB) downloaded 4.8 MB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/purrr_1.0.4.zip&#39; Content type &#39;application/zip&#39; length 550822 bytes (537 KB) downloaded 537 KB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/dplyr_1.1.4.zip&#39; Content type &#39;application/zip&#39; length 1589877 bytes (1.5 MB) downloaded 1.5 MB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/tidyr_1.3.1.zip&#39; Content type &#39;application/zip&#39; length 1272796 bytes (1.2 MB) downloaded 1.2 MB 试开URL’https://cran.rstudio.com/bin/windows/contrib/4.4/ggfortify_0.4.17.zip&#39; Content type &#39;application/zip&#39; length 2188148 bytes (2.1 MB) downloaded 2.1 MB 程序包‘farver’打开成功,MD5和检查也通过 程序包‘labeling’打开成功,MD5和检查也通过 程序包‘RColorBrewer’打开成功,MD5和检查也通过 程序包‘viridisLite’打开成功,MD5和检查也通过 程序包‘stringi’打开成功,MD5和检查也通过 程序包‘colorspace’打开成功,MD5和检查也通过 程序包‘fracdiff’打开成功,MD5和检查也通过 程序包‘generics’打开成功,MD5和检查也通过 程序包‘lmtest’打开成功,MD5和检查也通过 程序包‘timeDate’打开成功,MD5和检查也通过 程序包‘urca’打开成功,MD5和检查也通过 程序包‘withr’打开成功,MD5和检查也通过 程序包‘RcppArmadillo’打开成功,MD5和检查也通过 程序包‘gtable’打开成功,MD5和检查也通过 程序包‘isoband’打开成功,MD5和检查也通过 程序包‘scales’打开成功,MD5和检查也通过 程序包‘tidyselect’打开成功,MD5和检查也通过 程序包‘stringr’打开成功,MD5和检查也通过 程序包‘gridExtra’打开成功,MD5和检查也通过 程序包‘readxl’打开成功,MD5和检查也通过 Warning in install.packages : 无法删除软件包 ‘readxl’ 的先前安装 Warning in install.packages : 拷贝C:\Users\Lenovo\AppData\Local\R\win-library\4.4\00LOCK\readxl\libs\x64\readxl.dll到C:\Users\Lenovo\AppData\Local\R\win-library\4.4\readxl\libs\x64\readxl.dll时出了问题:Permission denied Warning in install.packages : 回复了‘readxl’ 程序包‘forecast’打开成功,MD5和检查也通过 程序包‘tseries’打开成功,MD5和检查也通过 Warning in install.packages : 无法删除软件包 ‘tseries’ 的先前安装 Warning in install.packages : 拷贝C:\Users\Lenovo\AppData\Local\R\win-library\4.4\00LOCK\tseries\libs\x64\tseries.dll到C:\Users\Lenovo\AppData\Local\R\win-library\4.4\tseries\libs\x64\tseries.dll时出了问题:Permission denied Warning in install.packages : 回复了‘tseries’ 程序包‘ggplot2’打开成功,MD5和检查也通过 程序包‘purrr’打开成功,MD5和检查也通过 程序包‘dplyr’打开成功,MD5和检查也通过 程序包‘tidyr’打开成功,MD5和检查也通过 程序包‘ggfortify’打开成功,MD5和检查也通过 下载的二进制程序包在 C:\Users\Lenovo\AppData\Local\Temp\Rtmp6XOHT7\downloaded_packages里 > library(readxl) # 数据读取 > library(forecast) # 时间序列分析 Registered S3 method overwritten by &#39;quantmod&#39;: method from as.zoo.data.frame zoo > library(tseries) # 统计检验 ‘tseries’ version: 0.10-58 ‘tseries’ is a package for time series analysis and computational finance. See ‘library(help="tseries")’ for details. > library(ggplot2) # 数据可视化 > library(purrr) # 函数式编程 > library(dplyr) # 数据处理 载入程序包:‘dplyr’ The following objects are masked from ‘package:stats’: filter, lag The following objects are masked from ‘package:base’: intersect, setdiff, setequal, union > library(tidyr) # 数据整理 > library(ggfortify) # 时间序列可视化 Registered S3 methods overwritten by &#39;ggfortify&#39;: method from autoplot.Arima forecast autoplot.acf forecast autoplot.ar forecast autoplot.bats forecast autoplot.decomposed.ts forecast autoplot.ets forecast autoplot.forecast forecast autoplot.stl forecast autoplot.ts forecast fitted.ar forecast fortify.ts forecast residuals.ar forecast > # 2. 数据预处理 > # 读取Excel文件(注意路径使用正斜杠) > raw_data <- read_excel("C:/Users/Lenovo/Desktop/EV Data Explorer 2025.xlsx") %>% + # 数据清洗流程 + select(Country, Year, Production) # 选择需要的列 Error in `select()`: ! Can&#39;t select columns that don&#39;t exist. ✖ Column `Country` doesn&#39;t exist. Run `rlang::last_trace()` to see where the error occurred. > mutate( + Year = as.numeric(Year), # 转换为数值型年份 + Production = as.numeric(Production) # 确保产量为数值型 + ) 错误: 找不到对象&#39;Year&#39; > drop_na(Production) # 删除缺失值 错误: 找不到对象&#39;Production&#39; > filter(Production > 0) # 过滤无效产量 错误: 找不到对象&#39;Production&#39; > group_by(Country) # 按国家分组 错误: 找不到对象&#39;Country&#39; > filter( + between(Year, 2014, 2024), # 保留2014-2024年数据 + n() >= 5 # 至少5个数据点 + ) 错误: 找不到对象&#39;Year&#39; > ungroup() # 取消分组 错误于UseMethod("ungroup"): "ungroup"没有适用于"NULL"目标对象的方法 > # 3. 创建时间序列对象列表 > country_ts <- raw_data > # 按国家拆分数据框 > group_split(Country) 错误: 找不到对象&#39;Country&#39; > # 创建时间序列对象 > map(~ { + ts_data <- ts( + data = .x$Production, + start = min(.x$Year), + frequency = 1 + ) + list( + name = unique(.x$Country), + ts = ts_data, + years = range(.x$Year) + ) + }) Error in `map()`: ! `.x` must be a vector, not a <formula> object. Run `rlang::last_trace()` to see where the error occurred. 警告信息: Use of calls and pairlists in map functions was deprecated in purrr 1.0.0. ℹ Please coerce explicitly with `as.list()` This warning is displayed once every 8 hours. Call `lifecycle::last_lifecycle_warnings()` to see where this warning was generated. > # 过滤有效时间序列 > keep(~ length(.x$ts) >= 5) 错误于keep(~length(.x$ts) >= 5): 缺少参数".p",也缺失默认值 > # 4. 定义增强型分析函数 > analyze_country <- function(ts_obj) { + tryCatch({ + # 平稳性检验(ADF检验) + while(adf.test(ts_obj$ts)$p.value > 0.05 & + length(ts_obj$ts) > 5) { + ts_obj$ts <- diff(ts_obj$ts) + } + + # 自动ARIMA建模 + model <- auto.arima( + ts_obj$ts, + seasonal = FALSE, + stepwise = FALSE, + approximation = FALSE + ) + + # 模型诊断 + residuals_test <- Box.test(residuals(model), type = "Ljung-Box") + + # 未来3年预测 + forecast_obj <- forecast(model, h = 3) + + # 返回结果 + list( + country = ts_obj$name, + model = model, + forecast = forecast_obj, + adf_pvalue = adf.test(ts_obj$ts)$p.value, + residuals_test = residuals_test$p.value, + plot_data = data.frame( + Year = time(ts_obj$ts), + Production = as.numeric(ts_obj$ts) + ) + ) + }, error = function(e) { + message(paste("Error in", ts_obj$name, ":", e$message)) + return(NULL) + }) + } > # 5. 执行批量分析 > #使用安全执行模式 > results <- map(country_ts, safely(analyze_country)) > # 6. 结果可视化与输出 > # 创建结果输出目录 > dir.create("analysis_results", showWarnings = FALSE) > # 处理分析结果 > walk2(results, country_ts, ~ { + if (!is.null(.x$result)) { + # 生成国家专属文件名 + country_name <- gsub("[^[:alnum:]]", "_", .x$result$country) + + # 保存时序图 + png(paste0("analysis_results/", country_name, "_timeseries.png"), + width = 1000, height = 600) + print( + autoplot(.x$result$plot_data$Production) + + ggtitle(paste(country_name, "Production Trend")) + + xlab("Year") + ylab("Production") + ) + dev.off() + + # 保存预测图 + png(paste0("analysis_results/", country_name, "_forecast.png"), + width = 1000, height = 600) + print( + autoplot(.x$result$forecast) + + ggtitle(paste(country_name, "3-Year Forecast")) + ) + dev.off() + + # 保存模型摘要 + sink(paste0("analysis_results/", country_name, "_model.txt")) + print(summary(.x$result$model)) + cat("\nResiduals Test p-value:", .x$result$residuals_test) + sink() + } + }) > # 7. 生成综合报告 > # 汇总所有国家结果 > summary_report <- map_dfr(results, ~ { + if (!is.null(.x$result)) { + data.frame( + Country = .x$result$country, + Model_Type = arima.string(.x$result$model), + AIC = AIC(.x$result$model), + Forecast_2025 = .x$result$forecast$mean[1], + Forecast_2026 = .x$result$forecast$mean[2], + stringsAsFactors = FALSE + ) + } + }) 数据在excel文件中的这个GEVO_EV_2025工作表里,region_country category parameter mode powertrain year unit value Aggregate group这是工作表的几列
最新发布
06-01
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值