Database query optimization methods, apparatuses, and computer devices

Patent metadata

Field	Value
Publication number	US-2021117424-A1
Application number	US-202017084840-A
Country	US
Kind code	A1
Filing date	Oct 30, 2020
Priority date	Aug 31, 2018
Publication date	Apr 22, 2021
Grant date	—

Abstract

A database query optimization computer-implemented method, medium, and system are disclosed. In one computer-implemented method, a data query request sent by a client device is received and parsed. An execution plan for executing the data query request is determined based on a parsing result. If the execution plan is a nested loop anti-join, whether there is a possibility that a to-be-queried field in a to-be-queried data table indicated by the data query request contains a NULL value is determined. If there is a possibility that the to-be-queried field contains a NULL value, a filter condition is generated and the execution plan is optimized based on the filter condition.

First claim

What is claimed is:

1 . A computer-implemented method for database query optimization, comprising:
receiving a data query request sent by a client device; parsing the data query request, and determining an execution plan for executing the data query request based on a parsing result; in response to determining that the execution plan is a nested loop anti-join, determining whether a probability that a to-be-queried field in a to-be-queried data table indicated by the data query request contains a NULL value is greater than zero; and in response to determining that the probability that the to-be-queried field contains a NULL value is greater than zero, generating a filter condition and optimizing the execution plan based on the filter condition generated.

2 . The computer-implemented method of claim 1 , wherein determining whether a probability that a to-be-queried field in a to-be-queried data table indicated by the data query request contains a NULL value is greater than zero comprises:
determining a NULL attribute of a to-be-queried field in a to-be-queried data table indicated by the data query request.

3 . The computer-implemented method of claim 2 , further comprising:
in response to determining that the NULL attribute of the to-be-queried field is in a default state, determining that the probability that the to-be-queried field contains a NULL value is zero, wherein the default state indicates that a value of the to-be-queried field is not allowed to be a NULL value; and in response to determining that the NULL attribute of the to-be-queried field is in a non-default state, determining that the probability that the to-be-queried field contains a NULL value is greater than zero, wherein the non-default state indicates that the value of the to-be-queried field is allowed to be a NULL value.

4 . The computer-implemented method of claim 1 , wherein generating a filter condition comprises:
determining a data representation form of a field value of the to-be-queried field in the to-be-queried data table, wherein the data representation form comprises a single-value form or a vector form; and generating a filter condition based on the data representation form, wherein different data representation forms correspond to different filter conditions.

5 . The computer-implemented method of claim 4 , wherein generating a filter condition based on the data representation form comprises:
in response to the data representation form being a single-value form, generating a first filter condition.

6 . The computer-implemented method of claim 5 , further comprising:
in response to the data representation form being a vector form, generating a second filter condition.

7 . The computer-implemented method of claim 1 , wherein optimizing the execution plan based on the filter condition generated comprises:
applying the filter condition generated as a join condition for inner loop scanning in the nested loop anti-join.

8 . A non-transitory, computer-readable medium storing one or more instructions executable by a computer system to perform operations comprising:
receiving a data query request sent by a client device; parsing the data query request, and determining an execution plan for executing the data query request based on a parsing result; in response to determining that the execution plan is a nested loop anti-join, determining whether a probability that a to-be-queried field in a to-be-queried data table indicated by the data query request contains a NULL value is greater than zero; and in response to determining that the probability that the to-be-queried field contains a NULL value is greater than zero, generating a filter condition and optimizing the execution plan based on the filter condition generated.

9 . The non-transitory, computer-readable medium of claim 8 , wherein determining whether a probability that a to-be-queried field in a to-be-queried data table indicated by the data query request contains a NULL value is greater than zero comprises:
determining a NULL attribute of a to-be-queried field in a to-be-queried data table indicated by the data query request.

10 . The non-transitory, computer-readable medium of claim 9 , further comprising:
in response to determining that the NULL attribute of the to-be-queried field is in a default state, determining that the probability that the to-be-queried field contains a NULL value is zero, wherein the default state indicates that a value of the to-be-queried field is not allowed to be a NULL value; and in response to determining that the NULL attribute of the to-be-queried field is in a non-default state, determining that the probability that the to-be-queried field contains a NULL value is greater than zero, wherein the non-default state indicates that the value of the to-be-queried field is allowed to be a NULL value.

11 . The non-transitory, computer-readable medium of claim 8 , wherein generating a filter condition comprises:
determining a data representation form of a field value of the to-be-queried field in the to-be-queried data table, wherein the data representation form comprises a single-value form or a vector form; and generating a filter condition based on the data representation form, wherein different data representation forms correspond to different filter conditions.

12 . The non-transitory, computer-readable medium of claim 11 , wherein generating a filter condition based on the data representation form comprises:
in response to the data representation form being a single-value form, generating a first filter condition.

13 . The non-transitory, computer-readable medium of claim 12 , further comprising:
in response to the data representation form being a vector form, generating a second filter condition.

14 . The non-transitory, computer-readable medium of claim 8 , wherein optimizing the execution plan based on the filter condition generated comprises:
applying the filter condition generated as a join condition for inner loop scanning in the nested loop anti-join.

15 . A computer-implemented system, comprising:
one or more computers; and one or more computer memory devices interoperably coupled with the one or more computers and having tangible, non-transitory, machine-readable media storing one or more instructions that, when executed by the one or more computers, perform one or more operations comprising:
receiving a data query request sent by a client device;
parsing the data query request, and determining an execution plan for executing the data query request based on a parsing result;
in response to determining that the execution plan is a nested loop anti-join, determining whether a probability that a to-be-queried field in a to-be-queried data table indicated by the data query request contains a NULL value is greater than zero; and
in response to determining that the probability that the to-be-queried field contains a NULL value is greater than zero, generating a filter condition and optimizing the execution plan based on the filter condition generated.

16 . The computer-implemented system of claim 15 , wherein determining whether a probability that a to-be-queried field in a to-be-queried data table indicated by the data query request contains a NULL value is greater than zero comprises:
determining a NULL attribute of a to-be-queried field in a to-be-queried data table indicated by the data query request.

17 . The computer-implemented system of claim 16 , further comprising:
in response to determining that the NULL attribute o

Assignees

• Advanced New Technologies Co Ltd

Inventors

• Yao Yingying
• Xiao Yi
• Li Yang
• Pan Yi
• Jiang Zhiyong

Classifications

• G06F16/24537 Primary

  of operators · CPC title

• G06F16/2445

  Data retrieval commands; View definitions · CPC title

• G06F16/24544 Primary

  Join order optimisation · CPC title