Non-primary key query method and distributed NewSQL database system
Technical Field
The invention relates to the technical field of big data, in particular to a non-primary key query method and a distributed NewSQL database system.
Background
Hbase is currently one of the most well-known distributed NoSQL databases in the Hadoop ecosystem. The design concept is derived from the Bigtable of Google. The Hbase main component comprises an HMmaster and an HRegionserver, a table type data model is provided for a user, a main key range is divided into a plurality of regions, the HMmaster is responsible for managing and distributing the regions, and the HRegionserver is responsible for reading and writing data of the regions. However, as more and more applications attempt to migrate to Hbase, Hbase defects become more and more exposed: in practical application, a user often needs to perform multi-dimensional query, and the existing Hbase cannot effectively support non-primary key query.
Disclosure of Invention
The embodiment of the invention aims to provide a non-primary key query method and a distributed NewSQL database system, which can effectively support secondary indexes so as to efficiently solve the non-primary key query requirement.
In order to achieve the above object, an embodiment of the present invention provides a non-primary key query method, which is applicable to a distributed NewSQL database system, including;
accessing a user request in an interface mode of JDCB/ODBC; the user request comprises a query condition, and the query result is obtained according to the query condition;
analyzing the user request, compiling and generating a corresponding execution plan;
acquiring the query condition query index table requested by the user according to an execution plan to acquire corresponding index data; inquiring a data table according to the acquired index data so as to acquire the corresponding inquiry result;
and returning the query result to the user.
Further, the method also comprises the following steps: and converting the user request into an SQL request in an SQL statement form.
Further, the analyzing the user request, compiling, and generating the corresponding execution plan includes:
judging whether a pre-stored SQL statement corresponding to the SQL request exists in the shared cache pool, if so, outputting an execution plan corresponding to the pre-stored SQL statement, otherwise,
and carrying out syntax check on the SQL request, if the syntax error returns error information to a user, otherwise,
semantic check is carried out on the SQL request, if the semantic error returns error information to the user, otherwise,
carrying out view and expression conversion on the SQL request to obtain a corresponding conversion result;
selecting an optimizer according to the conversion result to obtain a corresponding optimizer selection result;
selecting a corresponding data connection mode and a corresponding connection sequence according to the selection result of the optimizer;
selecting a searched path according to the connection mode and the connection sequence;
and generating an execution plan according to the search path, and outputting the execution plan.
Correspondingly, an embodiment of the present invention further provides a distributed NewSQL database system, including:
the JDCB/ODBC interface unit is used for carrying out interactive operation with a user, receiving a user request and returning a query result; the user request comprises a query condition, and the query result is obtained according to the query condition;
the system comprises a master unit, an SQLPLaner unit and a data processing unit, wherein the master unit is used for accessing a user request in a JDCB/ODBC interface mode, coordinating data communication among a plurality of processors and managing the whole process, and preferentially sending the user request to the SQLPLaner unit; and the master unit is also used for returning the query result of the data to the JDCB/ODBC interface unit.
The SQLPLaner unit is used for analyzing the user request, compiling and customizing an execution plan according to the user request;
a worker unit for executing the plan in parallel; the Hbase unit is also used for returning the query result of the Hbase unit to the master unit;
the Hbase unit is used for acquiring the query condition query index table requested by the user and acquiring the corresponding index data; inquiring a data table according to the acquired index data so as to acquire the corresponding inquiry result; the Hbase unit stores the data table and the index table;
and the distributed transaction manager is used for coordinating multiple parties to finish distributed transaction management when the worker unit execution plan relates to a transaction.
Further, the JDCB/ODBC interface unit is further configured to convert the user request into an SQL request in the form of an SQL statement.
Further, the SQLPlaner unit is configured to:
judging whether a pre-stored SQL statement corresponding to the SQL request exists in the shared cache pool, if so, outputting an execution plan corresponding to the pre-stored SQL statement, otherwise,
and carrying out syntax check on the SQL request, if the syntax error returns error information to a user, otherwise,
semantic check is carried out on the SQL request, if the semantic error returns error information to the user, otherwise,
carrying out view and expression conversion on the SQL request to obtain a corresponding conversion result;
selecting an optimizer according to the conversion result to obtain a corresponding optimizer selection result;
selecting a corresponding data connection mode and a corresponding connection sequence according to the selection result of the optimizer;
selecting a searched path according to the connection mode and the connection sequence;
and generating an execution plan according to the search path, and outputting the execution plan.
Further, the method also comprises the following steps:
a monitor for taking charge of metadata management, monitoring the load of Region of the hbase unit, and reallocating the Region through the coprocessor module of the hbase unit; the monitor is connected with the master unit.
Further, the monitoring the load of the Region of the hbase unit and the redistributing the Region through the coprocessor module of the hbase unit includes:
receiving data distribution information of the hbase unit, and receiving load information of the worker unit in the master unit, wherein the load information comprises a load deviation value of the worker unit;
comparing the load deviation value of the worker unit with a preset load deviation threshold, and if the load deviation value is judged to exceed the threshold, triggering the hbase unit to perform secondary distribution on the Region on the server with higher hit rate and the Region on the server with lower hit rate;
acquiring the data volume of each Region, judging the data volume of each Region and a preset data volume threshold, and triggering the hbase unit to divide the regions exceeding the preset data volume threshold into two regions if the data volume of the regions is judged to exceed the threshold.
Further, the JDCB/ODBC interface unit includes:
the JDBC application program module is used for receiving the user request, calling the JDBC object method to give an SQL statement and extracting a result to return to the user;
the JDBC driver manager module is used for loading and calling the JDBC driver module for the JDBC application program module;
the JDBC driver module is used for executing the calling of the JDBC object method, sending the SQL statement corresponding to the user request to the bottom database and returning the result obtained from the bottom database to the JDBC application module.
Compared with the prior art, the non-primary key query method and the distributed NewSQL database disclosed by the invention access the user request in a JDCB/ODBC interface mode and are also used for returning the query result to the user; the user request comprises a query condition, and the query result is obtained according to the query condition; analyzing the user request, compiling and generating a corresponding execution plan; acquiring the query condition query index table requested by the user according to an execution plan to acquire corresponding index data; inquiring a data table according to the acquired index data so as to acquire the corresponding inquiry result; the technical scheme of returning the query result to the user can realize secondary indexing, solve the problem that non-primary key query cannot be effectively supported in the prior art, and meet the requirement of non-primary key query of the user.
Drawings
Fig. 1 is a schematic flowchart of a non-primary key query method according to embodiment 1 of the present invention;
fig. 2 is a schematic structural diagram of a distributed NewSQL database provided in embodiment 2 of the present invention.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
Referring to fig. 1, fig. 1 is a schematic structural diagram of a non-primary key query method provided in embodiment 1 of the present invention, where this embodiment is applicable to a distributed NewSQL database system, and the present embodiment specifically includes the following steps:
s1, accessing user request in JDCB/ODBC interface mode; the user request comprises a query condition, and the query result is obtained according to the query condition;
s2, analyzing the user request, compiling and generating a corresponding execution plan;
s3, acquiring the query condition query index table requested by the user according to the execution plan, and acquiring the corresponding index data; inquiring a data table according to the acquired index data so as to acquire the corresponding inquiry result;
and S4, returning the query result to the user.
Further, step S1 further includes: and converting the user request into an SQL request in an SQL statement form.
Further, the parsing, compiling and generating the corresponding execution plan in step S2 includes:
s21, judging whether the shared cache pool has the pre-stored SQL sentence corresponding to the SQL request, if yes, outputting the execution plan corresponding to the pre-stored SQL sentence, if not,
s22, syntax checking the SQL request, if the syntax error returns error information to the user, otherwise,
s23, semantic checking the SQL request, if the semantic error returns error information to the user, otherwise,
s24, carrying out view and expression conversion on the SQL request to obtain a corresponding conversion result;
s25, selecting an optimizer according to the conversion result to obtain a corresponding optimizer selection result;
s26, selecting a corresponding data connection mode and a connection sequence according to the result of the optimizer selection;
s27, selecting the searched path according to the connection mode and the connection sequence;
and S28, generating an execution plan according to the search path and outputting the execution plan.
When the method is specifically implemented, firstly, a user request is accessed in an interface mode of JDCB/ODBC, and the method is also used for returning a query result to a user; the user request comprises a query condition, and the query result is obtained according to the query condition; then, analyzing the user request, compiling and generating a corresponding execution plan; then, according to an execution plan, acquiring the query condition query index table requested by the user, and acquiring the corresponding index data; inquiring a data table according to the acquired index data so as to acquire the corresponding inquiry result; and finally, returning the query result to the user.
The embodiment realizes secondary index, solves the problem that non-primary key query cannot be effectively supported in the prior art, and meets the requirement of non-primary key query of a user.
Referring to fig. 2, fig. 2 is a schematic structural diagram of a distributed NewSQL database system according to embodiment 2 of the present invention, where the embodiment includes:
the JDCB/ODBC interface unit 1 is used for carrying out interactive operation with a user, receiving a user request and returning a query result; the user request comprises a query condition, and the query result is obtained according to the query condition;
the master unit 2 is used for accessing a user request in a mode of the JDCB/ODBC interface unit 1, coordinating data communication among a plurality of processors and managing the whole process, and preferentially sending the user request to the SQLPLanner unit 3; the master unit 2 is also used for returning the query result of the data to the JDCB/ODBC interface unit 1.
The SQLPLaner unit 3 is used for analyzing the user request, compiling and customizing an execution plan according to the user request;
a worker unit 4 for executing the plan in parallel; also used for returning the query result 6 of the hbase unit 6 to the master unit 2;
the hbase unit 6 is configured to obtain the query condition query index table requested by the user, and obtain the corresponding index data; inquiring a data table according to the acquired index data so as to acquire the corresponding inquiry result; the hbase unit stores the data table and the index table;
generally, the distributed NewSQL database system of the embodiment allows a user to flexibly establish a secondary index according to specific business logic, in practical application, the user often establishes a plurality of secondary indexes, and dynamically calculates the cost of using the indexes according to query conditions during use, and automatically selects the most appropriate index. The query for rowkey is extremely efficient, so the implementation of the secondary index is to generate an index table for data by using the coprocessors module 61 and the Filter module 62 of the hbase unit 6. The query stage coprocessors module 61 will query the index table according to the index definition and the query condition, and query the data table again according to the query result of the index table. Meanwhile, the overall query speed is improved by utilizing the parallelism of the coprocessors module 61.
And the distributed transaction manager 5 is used for coordinating multiple parties to complete distributed transaction management when the worker unit 4 execution plan relates to a transaction.
Further, the JDCB/ODBC interface unit 1 is further configured to convert the user request into an SQL request in the form of an SQL statement.
Further, the SQLPlaner unit 3 is configured to:
judging whether a pre-stored SQL statement corresponding to the SQL request exists in the shared cache pool, if so, outputting an execution plan corresponding to the pre-stored SQL statement, otherwise,
and carrying out syntax check on the SQL request, if the syntax error returns error information to a user, otherwise,
semantic check is carried out on the SQL request, if the semantic error returns error information to the user, otherwise,
carrying out view and expression conversion on the SQL request to obtain a corresponding conversion result;
selecting an optimizer according to the conversion result to obtain a corresponding optimizer selection result;
selecting a corresponding data connection mode and a corresponding connection sequence according to the selection result of the optimizer;
selecting a searched path according to the connection mode and the connection sequence;
and generating an execution plan according to the search path, and outputting the execution plan.
Further, this embodiment further includes:
a monitor 8 for managing metadata, monitoring the load of Region of said hbase unit 6, and reallocating Region by the coprocessors module 61 of said hbase unit 6; the monitor is connected with the master unit.
Further, the monitoring the load of the Region of the hbase unit 6 and the redistribution of the Region by the coprocessor module 61 of the hbase unit 6 includes:
receiving data distribution information of the hbase unit 6, and receiving load information of the worker unit 4 in the master unit, wherein the load information comprises a load deviation value of the worker unit;
comparing the load deviation value of the worker unit 4 with a preset load deviation threshold, and if the load deviation value is judged to exceed the threshold, triggering the hbase unit 6 to newly distribute the Region on the server with higher hit rate and the Region on the server with lower hit rate;
acquiring the data volume of each Region, judging the data volume of each Region and a preset data volume threshold, and triggering the hbase unit to divide the regions exceeding the preset data volume threshold into two regions if the data volume of the regions is judged to exceed the threshold.
Further, the JDCB/ODBC interface unit 1 includes:
the JDBC application program module 11 is used for receiving a user request, calling a JDBC object method to give an SQL statement, and extracting a result to return to a user;
a JDBC driver manager module 12, configured to load and call a JDBC driver module 13 for the JDBC application module 11;
the JDBC driver module 13 is configured to execute the invocation of the JDBC object method, send an SQL statement corresponding to the user request to the underlying database, and return a result obtained from the underlying database to the JDBC application module 11.
When the method is implemented specifically, a user request is received through the JDCB/ODBC interface unit 1; then, a user request is accessed through the master unit 2 in a JDCB/ODBC interface mode, and the user request is preferentially sent to the SQLPLanner unit 3; then, the SQLPLaner unit 3 analyzes the user request, and compiles and customizes an execution plan according to the user request; then, the plan is executed in parallel by the worker unit 4; the Hbase unit is also used for returning the query result of the Hbase unit to the master unit; finally, acquiring the query condition query index table requested by the user through an hbase unit 6 to acquire the corresponding index data; inquiring a data table according to the acquired index data so as to acquire the corresponding inquiry result; the hbase unit 1 stores the data table and the index table; and the master unit 2 returns the query result of the data to the JDCB/ODBC interface unit 1 to return to the user.
The embodiment realizes secondary index, solves the problem that non-primary key query cannot be effectively supported in the prior art, and meets the requirement of non-primary key query of a user.
While the foregoing is directed to the preferred embodiment of the present invention, it will be understood by those skilled in the art that various changes and modifications may be made without departing from the spirit and scope of the invention.