技术名称
智能索引技术
作用或背景
在大型关系型数据库中,索引的设计和优化对SQL语句的执行效率至关重要。一直以来,数据库管理人员往往基于相关理论知识和经验,进行人工设计和调整索引。这样做的缺点在于,消耗了大量的时间和人力,同时人工设计的方式往往不能确保索引是最优的。
智能索引技术将索引设计的流程自动化、标准化,可分别针对单条查询语句和工作负载推荐最优的索引,提升作业效率、减少数据库管理人员的运维操作。
技术源头
智能索引技术起源可以追溯到 1950 年代,当时英国数学家和计算机科学家艾伦·图灵(Alan Turing)预测未来将存在具有类人智能的超级计算机,科学家们开始尝试使用超级计算机初步模拟人脑。到今天数据库已经和人工智能深度结合,通过使用AI4DB、DB4AI等技术来为数据库提供AI能力。
国外技术现状
国外主流数据库产品均采用基于优化器的代价估计的方法实现智能索引推荐功能,如Oracle和DB2。
国内技术现状
当前国内主流数据库产品在智能索引技术的两种实现算法上均有探索且形成相对成熟的产品,如PolarDB-X通过基于优化器的代价估计的方法索引的推荐;MogDB的智能索引推荐通过基于优化器的代价估计的方法实现了对单条语句的索引推进,通过基于机器学习的方法实现基于工作负载的索引推荐;。
国外代表产品
(1) Oracle
Oracle 19c引入了自动索引(Automatic index),Automatic index由索引管理后台进程TASK调用, 可以自动的create, rebuild , drop 索引。基于传统手动优化SQL的思路,Automatic index可以自动地通过SQL中使用的列识别可以创建的索引,然后验证自动索引对性能的影响,之后按预设的值去创建索引。主要流程如下
① 捕捉Capture
定期的捕获应用程序SQL历史进SQL仓库,包括SQL的文本、执行计划、绑定变量,执行统计信息等。
② 视别后选索引Identify Candidates
识别有益于新SQL的候选索引,创建这个只有元数据的候选索引unusable\\invisible index, 删除新创建的obsoloted索引。
③ 验证Verify
验证优化器后期捕捉的SQL是否会使用新创建的索引, 如果这个索引可以提升SQL的性能,就会物化该索引。
④ 决策Decide
如果该索引可以提升所有或者大部分SQL的性能,会把该自动索引更改为visible, 如果该索引会导致所有SQL性能更差,该索引会保持invisible。
⑤ 在线确认Online Validation
为其它SQL在线确认新索引的使用情况,开始是只允许一个会话使用一个SQL命令使用该索引,这样出问题也不会是大面积。
⑥ 监控Monitor
对于自动索引提供连续不断的监控,若长时间不使用自动创建的索引该索引会被自动删除。
(2) DB2
Db2 Design Advisor 支持就具体化查询表 (MQT) 和索引的创建、表的重新分区、多维集群 (MDC) 表的转换以及未使用对象的删除向用户提供建议。
基于收集到的用户的工作负载,对用户的多个工作SQL进行排序。用户也可以对工作负载中每个语句的重要性进行排名,并指定工作负载中每个语句的执行频率。之后Design Advisor会输出一个包含 CREATE INDEX、CREATE Summary TABLE (MQT) 和 CREATE
TABLE 语句的 DDL CLP 脚本,DBA可以依据实际需求创建推荐的对象。
1、MogDB
MogDB的智能索引技术可覆盖多种任务级别和使用场景,具体包含以下三个特性。
(1) 单条查询语句的索引推荐。该特性可基于查询语句的语义信息和数据库的统计信息,对用户输入的单条查询语句生成推荐的索引。
(2) 虚拟索引。该特性可模拟真实索引的建立,同时避免真实索引创建所需的时间和空间开销,用户可通过优化器评估虚拟索引对指定查询语句的代价影响。
(3) 基于工作负载的索引推荐。该特性将包含有多条DML语句的工作负载作为任务的输入,最终生成一批可优化整体工作负载执行时间的索引。该功能适用于多种使用场景,例如,当面对一批全新的业务SQL且当前系统中无索引,本功能将针对该工作负载量身定制,推荐出效果最优的一批索引;当系统中已存在索引时,本功能仍可查漏补缺,对当前生产环境中运行的作业,通过获取日志来推荐可提升工作负载执行效率的索引,或者针对极个别的慢SQL进行单条查询语句的索引推荐。
2、PolarDB-X
PolarDB-X数据库的SQL Advisor利用基于代价的优化器提供索引推荐功能,智能化地解决如何挑选索引的问题。通过分析SQL,枚举可能的索引组合,并通过优化器What-If的能力,选出其中收益最高的索引组合推荐给用户。
详细描述
智能索引技术按照任务级别划分,可分为基于单条查询语句的索引推荐和基于工作负载的索引推荐。对于基于单条查询语句的索引推荐,使用者每次向索引设计工具提供一个查询语句,工具会针对该语句生成最佳的索引。目前的主流算法是首先提取该查询语句的语义信息和数据库中的统计信息,然后基于相关的索引设计和优化理论,对各子句中的谓词进行分析和处理,启发式地推荐最优索引。此类任务主要是针对个别查询时间慢的SQL进行索引优化,应用场景较为有限。
一般来说,更广泛使用的任务场景是基于工作负载的索引推荐,即给定一个包含多种类型SQL语句的工作负载,生成使得系统在该工作负载下的运行时间降至最低的索引集合。在索引选择算法中,核心是量化和估计索引对于工作负载的收益,这里的收益是指,当该索引应用于指定工作负载时,工作负载的总代价的减少量。根据代价估计的方式的不同,目前的算法可分为两大类。
(1) 基于优化器的代价估计的方法。采用优化器的代价模型来对索引进行代价估计是较为准确的,因为优化器负责查询计划和索引的选择。同时,一些数据库系统支持虚拟索引的功能,虚拟索引并没有在存储空间中创建物理上的索引,而是通过模拟索引的效果来影响优化器的代价估计。目前的主流数据库产品均采用了该种方法,如SQL Server的AutoAdmin、DB2的DB2 Advisor等。
(2) 基于机器学习的方法。上一种方法由于优化器的局限性,会导致索引收益的估计发生偏差,例如选择度的错误估算或者代价估计模型不准确。在学术界的最新进展中,一些方法采用了机器学习算法来预测和分类哪种查询计划更加有效,或者是采用基于神经网络的代价模型来缓解传统模型带来的问题。但是此类方法往往需要大量的训练数据,并不适用于全部的业务环境。
参考材料
邱涛, 王斌,
舒昭维, 赵智博, 宋子文, & 钟延辉. (2020). 面向关系数据库的智能索引调优方法∗.
软件学报(3), 634-647.
Wang H, Raj B. On the origin of deep learning[J].
arXiv preprint arXiv:1702.07800, 2017.
Turing, Alan M. "Computing machinery and
intelligence." Parsing the turing test. Springer, Dordrecht, 2009. 23-65.
openGauss数据库源码解析系列文章——AI技术(三):智能索引推荐:https://zhuanlan.zhihu.com/p/440249864
Oracle19c新特性: 自动索引(Automatic indexing):https://www.modb.pro/db/20962