CN111221864B - 一种基于mysql慢查询日志词频分析的索引智能推荐方法 - Google Patents

一种基于mysql慢查询日志词频分析的索引智能推荐方法 Download PDF

Info

Publication number
CN111221864B
CN111221864B CN201911377159.4A CN201911377159A CN111221864B CN 111221864 B CN111221864 B CN 111221864B CN 201911377159 A CN201911377159 A CN 201911377159A CN 111221864 B CN111221864 B CN 111221864B
Authority
CN
China
Prior art keywords
query
index
node
field
group
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Active
Application number
CN201911377159.4A
Other languages
English (en)
Other versions
CN111221864A (zh
Inventor
陈旋
王冲
张平
张鹏
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Jiangsu Aijia Household Products Co Ltd
Original Assignee
Jiangsu Aijia Household Products Co Ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Jiangsu Aijia Household Products Co Ltd filed Critical Jiangsu Aijia Household Products Co Ltd
Priority to CN201911377159.4A priority Critical patent/CN111221864B/zh
Publication of CN111221864A publication Critical patent/CN111221864A/zh
Application granted granted Critical
Publication of CN111221864B publication Critical patent/CN111221864B/zh
Active legal-status Critical Current
Anticipated expiration legal-status Critical

Links

Images

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2457—Query processing with adaptation to user needs
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/242—Query formulation
    • G06F16/2433—Query languages
    • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
    • Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
    • Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • Computational Linguistics (AREA)
  • Data Mining & Analysis (AREA)
  • Databases & Information Systems (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Mathematical Physics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

本发明涉及一种基于mysql慢查询日志词频分析的索引智能推荐方法，采用全新设计策略，基于mysql慢查询日志，对所记录的查询语句进行模式匹配、算出各查询字段、以及查询字段组合的出现次数，将查询字段作为无向图的节点，字段组合作为图的边，对生成的无向图进行改进的广度优先遍历，以寻找最长子序列作为最优索引；如此基于慢查询sql作分析，得到的索引更加客观、有效；并且通过所设计方法尽量寻找覆盖率最大的组合索引，可以尽量避免多单列索引的情况。

Description

一种基于mysql慢查询日志词频分析的索引智能推荐方法
技术领域
本发明涉及一种基于mysql慢查询日志词频分析的索引智能推荐方法，属于数据索引推荐技术领域。
背景技术
索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的存储结构，提供指向存储在表的指定列中的数据值的指针，好的索引可以大大减少sql的查询时间，提高程序的响应速度。
当前创建索引主要依赖于研发工程师的经验，研发工程师在实现系统功能的时候，通过项目中的sql、以及以往经验，在建表的时候同时创建索引，但是这里存在两个主要的问题：1.缺少项目实际运行数据，索引的创建偏主观，有时无法与实际sql执行很好的匹配；2.偏向于单列索引，无法充分发挥组合索引的优势。
发明内容
本发明所要解决的技术问题是提供一种基于mysql慢查询日志词频分析的索引智能推荐方法，采用全新设计策略，能够有效提高索引的准确性与效率。
本发明为了解决上述技术问题采用以下技术方案：本发明设计了一种基于mysql慢查询日志词频分析的索引智能推荐方法，用于针对mysql中需要推荐索引的目标表t，获得目标表t所对应的最优索引，包括如下步骤：
步骤A.针对mysql慢查询日志中预设时间范围内的所有sql查询语句，获得其中所有包含目标表t字段的sql查询语句，构成集合R，其中，集合R中的sql查询语句的数目为N，然后进入步骤B；
步骤B.获得集合R中的各查询字段，并获得各查询字段分别在集合R中出现的次数，以及获得各个分别包含两个查询字段的字段组合，且字段组合中的两个查询字段同时出现在集合R中的同一sql查询语句中，并获得各字段组合分别在集合R中出现的次数，然后进入步骤C；
步骤C.以各查询字段作为节点、查询字段的出现次数作为节点的权重，同时以各字段组作为其所包含两查询字段分别所对应节点之间的边、字段组的出现次数作为边的权重，由此构成无向图，然后定义参数in表示第n组索引中的第i个访问点，并初始化n＝1，i＝1，然后进入步骤D；
步骤D.选择无向图中最大权重所对应的节点，作为第n组索引中的第i个访问点in，并进入步骤E；
步骤E.判断无向图中是否存在与in直接关联、且未被作为访问点的节点，是则进入步骤F；否则进入步骤H；
步骤F.分别针对无向图中与in直接关联、且未被作为访问点的各个节点，应用in的权重减去其与节点之间边的权重，并用所获结果针对该节点的权重进行更新，完成该各个节点的权重的更新，然后进入步骤G；
步骤G.针对i的值进行加1更新，并针对无向图中与in直接关联、且未被作为访问点的各个节点，选择其中最小权重的节点，作为第n组索引中的第i个访问点in，并返回步骤E；
步骤H.针对第n组索引中的各个访问点in，获得其中第一个访问点权重与N的比值，并判断此比值是否大于预设覆盖率阈值，是则进入步骤I；否则索引智能推荐方法停止，所获各组最优索引即为目标表t所对应的各组最优索引；
步骤I.第n组索引中各个访问点in所对应的各查询字段构成一组最优索引，并删除无向图中该组最优索引中的各个访问点，更新无向图，进入步骤J；
步骤J.判断无向图中是否存在节点，是则针对n的值进行加1更新，并返回步骤D；否则即获得目标表t所对应的各组最优索引。
作为本发明的一种优选技术方案：所述预设覆盖率阈值为15％。
本发明所述一种基于mysql慢查询日志词频分析的索引智能推荐方法，采用以上技术方案与现有技术相比，具有以下技术效果：
本发明所设计基于mysql慢查询日志词频分析的索引智能推荐方法，采用全新设计策略，基于mysql慢查询日志，对所记录的查询语句进行模式匹配、算出各查询字段、以及查询字段组合的出现次数，将查询字段作为无向图的节点，字段组合作为图的边，对生成的无向图进行改进的广度优先遍历，以寻找最长子序列作为最优索引；如此基于慢查询sql作分析，得到的索引更加客观、有效；并且通过所设计方法尽量寻找覆盖率最大的组合索引，可以尽量避免多单列索引的情况。
附图说明
图1是本发明设计实施例应用中原始无向图；
图2是本发明设计实施例应用中第1组索引中第1个访问点相邻各节点权重更新示意图；
图3是本发明设计实施例应用中第1组索引中第2个访问点相邻各节点权重更新示意图；
图4是本发明设计实施例应用中获得第1组最优索引后更新的无向图。
具体实施方式
下面结合说明书附图对本发明的具体实施方式作进一步详细的说明。
本发明设计了一种基于mysql慢查询日志词频分析的索引智能推荐方法，用于针对mysql中需要推荐索引的目标表t，获得目标表t所对应的最优索引，包括如下步骤。
步骤A.针对mysql慢查询日志中预设时间范围内的所有sql查询语句，获得其中所有包含目标表t字段的sql查询语句，构成集合R，其中，集合R中的sql查询语句的数目为N，然后进入步骤B。
步骤B.获得集合R中的各查询字段，并获得各查询字段分别在集合R中出现的次数，以及获得各个分别包含两个查询字段的字段组合，且字段组合中的两个查询字段同时出现在集合R中的同一sql查询语句中，并获得各字段组合分别在集合R中出现的次数，然后进入步骤C。
步骤C.以各查询字段作为节点、查询字段的出现次数作为节点的权重，同时以各字段组作为其所包含两查询字段分别所对应节点之间的边、字段组的出现次数作为边的权重，由此构成无向图，然后定义参数in表示第n组索引中的第i个访问点，并初始化n＝1，i＝1，然后进入步骤D。
步骤D.选择无向图中最大权重所对应的节点，作为第n组索引中的第i个访问点in，并进入步骤E。
步骤E.判断无向图中是否存在与in直接关联、且未被作为访问点的节点，是则进入步骤F；否则进入步骤H。
步骤F.分别针对无向图中与in直接关联、且未被作为访问点的各个节点，应用in的权重减去其与节点之间边的权重，并用所获结果针对该节点的权重进行更新，完成该各个节点的权重的更新，然后进入步骤G。
步骤G.针对i的值进行加1更新，并针对无向图中与in直接关联、且未被作为访问点的各个节点，选择其中最小权重的节点，作为第n组索引中的第i个访问点in，并返回步骤E。
步骤H.针对第n组索引中的各个访问点in，获得其中第一个访问点权重与N的比值，并判断此比值是否大于预设覆盖率阈值，是则进入步骤I；否则索引智能推荐方法停止，所获各组最优索引即为目标表t所对应的各组最优索引；实际应用当中，预设覆盖率阈值为经验值，可以参考DBA的建议，诸如设计预设覆盖率阈值为15％。
步骤I.第n组索引中各个访问点in所对应的各查询字段构成一组最优索引，并删除无向图中该组最优索引中的各个访问点，更新无向图，进入步骤J。
步骤J.判断无向图中是否存在节点，是则针对n的值进行加1更新，并返回步骤D；否则即获得目标表t所对应的各组最优索引。
将上述所设计基于mysql慢查询日志词频分析的索引智能推荐方法，应用于实际当中，user表中出现的查询字段为c1，c2，c3，c4，c5，c6，c7(user表可以包含更多的字段)，则收集mysql慢查询日志近一个月的所有sql查询语句，经过步骤A至步骤B，获得如下表1、表2所示。
表1
查询字段 次数统计
c1 55
c2 65
c3 28
c4 25
c5 20
c6 10
c7 38
表2
查询字段组合 次数统计
(c1，c2) 55
(c1，c4) 25
(c2，c4) 25
(c2，c6) 10
(c2，c7) 10
(c6，c7) 10
(c3，c7) 28
接着执行步骤C，获得无向图如图1所示，然后执行步骤D至步骤J，具体首先c2的权重最大，选择c2作为第1组索引中的第1个访问点，更新与c2直接关联、且未被作为访问点的节点(c4,c1,c7,c6)的权重，结果如图2所示，可以看出，其中权重最小的是c1，即作为第1组索引中的第2个访问点。
继续针对c1，更新与c1直接关联、且未被作为访问点的节点(c4)的权重，结果如图3所示，由于c4是唯一个，因此c4作为第1组索引中的第3个访问点，接下来，由于c4不存在与之直接关联、且未被作为访问点的节点，因此停止第1次索引，输出第1次索引从c2到本节点的索引(c2,c1,c4)，计算其覆盖率为
Figure GDA0003593620450000051
大于预设覆盖率阈值为15％，因此是一组最优索引。
然后在无向图中删除(c2,c1,c4)，针对无向图进行更新如图4所示，并针对更新后的无向图继续上述执行，得到第二组索引(c7,c3)，并且其覆盖率为33.6％，因此同样也是一组最优索引；最后循环执行上述过程，获得剩余索引(c5)，其覆盖率为17.7％，同样也是一组最优索引，所以最终获得user表所对应的各组最优索引。
上述技术方案所设计基于mysql慢查询日志词频分析的索引智能推荐方法，采用全新设计策略，基于mysql慢查询日志，对所记录的查询语句进行模式匹配、算出各查询字段、以及查询字段组合的出现次数，将查询字段作为无向图的节点，字段组合作为图的边，对生成的无向图进行改进的广度优先遍历，以寻找最长子序列作为最优索引；如此基于慢查询sql作分析，得到的索引更加客观、有效；并且通过所设计方法尽量寻找覆盖率最大的组合索引，可以尽量避免多单列索引的情况。
上面结合附图对本发明的实施方式作了详细说明，但是本发明并不限于上述实施方式，在本领域普通技术人员所具备的知识范围内，还可以在不脱离本发明宗旨的前提下做出各种变化。

Claims (2)

1.一种基于mysql慢查询日志词频分析的索引智能推荐方法，用于针对mysql中需要推荐索引的目标表t，获得目标表t所对应的最长子序列并作为最优索引，其特征在于，包括如下步骤：
步骤A.针对mysql慢查询日志中预设时间范围内的所有sql查询语句，获得其中所有包含目标表t字段的sql查询语句，构成集合R，其中，集合R中的sql查询语句的数目为N，然后进入步骤B；
步骤B.获得集合R中的各查询字段，并获得各查询字段分别在集合R中出现的次数，以及获得各个分别包含两个查询字段的字段组合，且字段组合中的两个查询字段同时出现在集合R中的同一sql查询语句中，并获得各字段组合分别在集合R中出现的次数，然后进入步骤C；
步骤C.以各查询字段作为节点、查询字段的出现次数作为节点的权重，同时以各字段组作为其所包含两查询字段分别所对应节点之间的边、字段组的出现次数作为边的权重，由此构成无向图，然后定义参数in表示第n组索引中的第i个访问点，并初始化n＝1，i＝1，然后进入步骤D；
步骤D.选择无向图中最大权重所对应的节点，作为第n组索引中的第i个访问点in，并进入步骤E；
步骤E.判断无向图中是否存在与in直接关联、且未被作为访问点的节点，是则进入步骤F；否则进入步骤H；
步骤F.分别针对无向图中与in直接关联、且未被作为访问点的各个节点，应用in的权重减去其与节点之间边的权重，并用所获结果针对该节点的权重进行更新，完成该各个节点的权重的更新，然后进入步骤G；
步骤G.针对i的值进行加1更新，并针对无向图中与in直接关联、且未被作为访问点的各个节点，选择其中最小权重的节点，作为第n组索引中的第i个访问点in，并返回步骤E；
步骤H.针对第n组索引中的各个访问点in，获得其中第一个访问点权重与N的比值，并判断此比值是否大于预设覆盖率阈值，是则进入步骤I；否则索引智能推荐方法停止，所获各组最优索引即为目标表t所对应的各组最优索引；
步骤I.第n组索引中各个访问点in所对应的各查询字段构成一组最优索引，并删除无向图中该组最优索引中的各个访问点，更新无向图，进入步骤J；
步骤J.判断无向图中是否存在节点，是则针对n的值进行加1更新，并返回步骤D；否则即获得目标表t所对应的各组最优索引。
2.根据权利要求1所述一种基于mysql慢查询日志词频分析的索引智能推荐方法，其特征在于：所述预设覆盖率阈值为15％。
CN201911377159.4A 2019-12-27 2019-12-27 一种基于mysql慢查询日志词频分析的索引智能推荐方法 Active CN111221864B (zh)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN201911377159.4A CN111221864B (zh) 2019-12-27 2019-12-27 一种基于mysql慢查询日志词频分析的索引智能推荐方法

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN201911377159.4A CN111221864B (zh) 2019-12-27 2019-12-27 一种基于mysql慢查询日志词频分析的索引智能推荐方法

Publications (2)

Publication Number Publication Date
CN111221864A CN111221864A (zh) 2020-06-02
CN111221864B true CN111221864B (zh) 2022-07-08

Family

ID=70829212

Family Applications (1)

Application Number Title Priority Date Filing Date
CN201911377159.4A Active CN111221864B (zh) 2019-12-27 2019-12-27 一种基于mysql慢查询日志词频分析的索引智能推荐方法

Country Status (1)

Country Link
CN (1) CN111221864B (zh)

Family Cites Families (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN103544291B (zh) * 2013-10-29 2016-05-18 东北林业大学 道路网络中基于RRN-Tree的移动对象CKNN查询方法
CN110059264B (zh) * 2019-04-24 2023-07-07 东南大学 基于知识图谱的地点检索方法、设备及计算机存储介质

Also Published As

Publication number Publication date
CN111221864A (zh) 2020-06-02

Similar Documents

Publication Publication Date Title
Marcus et al. Plan-structured deep neural network models for query performance prediction
US11372851B2 (en) Systems and methods for rapid data analysis
US8112421B2 (en) Query selection for effectively learning ranking functions
US7272589B1 (en) Database index validation mechanism
US8880511B2 (en) Database query optimization and cost estimation
JP2004518226A (ja) データベースシステムおよびクエリオプティマイザ
US10452676B2 (en) Managing database with counting bloom filters
CN105279397A (zh) 一种识别蛋白质相互作用网络中关键蛋白质的方法
CN103136260A (zh) 评估过滤因子用于数据库中访问路径优化的方法和装置
CN108710662B (zh) 语言转换方法和装置、存储介质、数据查询系统和方法
CN109308303B (zh) 一种基于马尔可夫链的多表连接在线聚集方法
CN115794833A (zh) 数据处理方法、服务器和计算机存储介质
US20240177077A1 (en) Attribution analysis method, electronic device, and storage medium
CN111221864B (zh) 一种基于mysql慢查询日志词频分析的索引智能推荐方法
Rebola-Pardo et al. Complete and efficient DRAT proof checking
CN113076332B (zh) 一种数据库预编译查询语句的执行方法
CN115391424A (zh) 数据库查询的处理方法、存储介质与计算机设备
CN117390064B (zh) 一种基于可嵌入子图的数据库查询优化方法
CN108776707B (zh) 针对探索性查询的抽样方法
Van Den Brink et al. Quality metrics for SQL queries embedded in host languages
Arsov et al. Prediction of Horizontal Data Partitioning Through Query Execution Cost Estimation
Koteska et al. Prediction of Horizontal Data Partitioning Through Query Execution Cost Estimation
CN112783900A (zh) 数据库管理方法、装置、设备及存储介质
CN117725101A (zh) 一种基于模拟退火的间隔项近似统计方法
CN116522204A (zh) 一种基于特征模型的业务流程模型查询方法

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination
CB02 Change of applicant information
CB02 Change of applicant information

Address after: 211100 floor 5, block a, China Merchants high speed rail Plaza project, No. 9, Jiangnan Road, Jiangning District, Nanjing, Jiangsu (South Station area)

Applicant after: JIANGSU AIJIA HOUSEHOLD PRODUCTS Co.,Ltd.

Address before: 211100 No. 18 Zhilan Road, Science Park, Jiangning District, Nanjing City, Jiangsu Province

Applicant before: JIANGSU AIJIA HOUSEHOLD PRODUCTS Co.,Ltd.

GR01 Patent grant
GR01 Patent grant