[go: up one dir, main page]

WO2018157680A1 - Procédé et dispositif de génération de plan d'exécution, et serveur de base de données - Google Patents

Procédé et dispositif de génération de plan d'exécution, et serveur de base de données Download PDF

Info

Publication number
WO2018157680A1
WO2018157680A1 PCT/CN2018/074034 CN2018074034W WO2018157680A1 WO 2018157680 A1 WO2018157680 A1 WO 2018157680A1 CN 2018074034 W CN2018074034 W CN 2018074034W WO 2018157680 A1 WO2018157680 A1 WO 2018157680A1
Authority
WO
WIPO (PCT)
Prior art keywords
data
execution plan
clustering factor
generating
index
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.)
Ceased
Application number
PCT/CN2018/074034
Other languages
English (en)
Chinese (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.)
Huawei Technologies Co Ltd
Original Assignee
Huawei Technologies 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 Huawei Technologies Co Ltd filed Critical Huawei Technologies Co Ltd
Publication of WO2018157680A1 publication Critical patent/WO2018157680A1/fr
Anticipated expiration legal-status Critical
Ceased legal-status Critical Current

Links

Images

Classifications

    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F9/00—Arrangements for program control, e.g. control units
    • G06F9/06—Arrangements for program control, e.g. control units using stored programs, i.e. using an internal store of processing equipment to receive or retain programs
    • G06F9/44—Arrangements for executing specific programs
    • G06F9/455—Emulation; Interpretation; Software simulation, e.g. virtualisation or emulation of application or operating system execution engines
    • G06F9/45533—Hypervisors; Virtual machine monitors
    • G06F9/45558—Hypervisor-specific management and integration aspects
    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F9/00—Arrangements for program control, e.g. control units
    • G06F9/06—Arrangements for program control, e.g. control units using stored programs, i.e. using an internal store of processing equipment to receive or retain programs
    • G06F9/44—Arrangements for executing specific programs
    • G06F9/455—Emulation; Interpretation; Software simulation, e.g. virtualisation or emulation of application or operating system execution engines
    • 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

Definitions

  • the present invention relates to the field of computers, and in particular, to a method, an apparatus, and a database server for generating an execution plan.
  • the development of database technology provides more and more data storage capabilities, users can query the massive data storage through the network and other methods to obtain the required data.
  • the database technology includes a data manipulation language DML, such as a query (select), an insert, a delete, an update, and the like from a client.
  • DML data manipulation language
  • the DML statement needs to be parsed, pre-compiled, optimized, etc., and then an execution plan is generated.
  • the clustering factor is usually used by the optimizer of the Database Management System to calculate the cost in the process of generating the execution plan, and the overhead directly affects the operators used in the execution plan. For example, when the clustering factor is small, if the order of the key values accesses the tablespace data according to the RID, a large amount of random I/O is generated, which affects the query performance. Therefore, the optimizer instructs the data of the tablespace to be pre-sorted first. Read.
  • Embodiments of the present invention provide a method and apparatus for executing a statement generating method to execute a statement of a data manipulation language DML, which can more objectively consider the characteristics of data storage, so that the data reading method used in the generated execution plan is more in conformity with the data.
  • the actual situation in the storage medium thus makes the overhead of running the generated execution plan smaller, ie avoiding unnecessary overhead. Improve the performance of the database to operate on data, and save resources.
  • an execution plan generation method is employed, the execution plan including a first minute execution plan and a second minute execution plan, the method comprising: parsing the obtained data manipulation language DML statement to determine the DML a first data table corresponding to the statement, the first data table including first data stored on a solid state drive SSD medium, and second data stored on a mechanical hard disk HDD medium; generating, for the first data a first sub-execution plan, the first sub-execution plan includes reading the first data by a table scan; and generating, by the second data, the second sub-execution plan, where the second sub-execution plan is included in If the second data needs to be pre-sorted, after the second data is pre-sorted, the pre-sorted second data is read; and when the second data is not pre-sorted, the table scan is performed. Reading the second data.
  • the characteristics of the data storage can be considered more objectively, and the data read in the generated execution plan is more consistent with the data in the storage medium for the data stored in different media.
  • the actual situation so that the execution of the generated execution plan is less expensive, improves the performance of the database to operate on the data, and saves resources.
  • the clustering factor is used to mark the degree of similarity of the data in the table and the order of the data in the index. The larger the clustering factor, the higher the order similarity, the smaller the clustering factor, and the lower the sequence similarity.
  • an improvement of the representation method of the clustering factor is proposed, for example, when calculating the clustering factor, considering the difference in the kind of the storage medium, for example, the first layer clustering factor and the second layer are proposed.
  • the layer clustering factor optimizes the cost estimate for different levels of operation through a multi-layer clustering factor during the generation of the execution plan. This enables a more optimized execution plan to be generated. Since the clustering factor is also present in the prior art, the above concentration of the present application
  • random I/O represents the cost of the operation of accessing data with discontinuous addresses.
  • the method further includes: determining, according to a value of a logical clustering factor of the first data table, a first operator in the execution plan, where the first operator corresponds to the first data a first operation between the table and the second data table, the logical clustering factor of the first data table is used to replace the clustering factor of the first data table, and is used to represent the first data table as a whole Reading the cost of the first data table by index.
  • the second layer logical clustering factor that is, the original clustering factor corresponding to the HDD medium and the virtual clustering factor corresponding to the SSD medium are used.
  • averaging the first layer of clustering factors can be a weighted average or directly averaged. It should be understood that if a weighted average is used, the weights may be preset in advance, for example, according to the ratio of the data of the data table in each storage medium. For another example, based on the distribution of the data, the statistical principle can be used, the existing statistical formula can be used, or the existing statistical formula can be simply adjusted and calculated.
  • a mapping relationship may be established, that is, a mapping relationship between the second layer clustering factor and some quantity, and the quantity may be a value of a first layer clustering factor of a table, and the data used to represent the table is At least one of a value of a distribution condition in the storage medium, a weighting factor for indicating an algorithm to be executed by the table, and the like.
  • the first operation is a join
  • the first data table is an outer part in the operation
  • the operator is a hybrid connection (Hybrid Join), a nested loop connection. (Nested Loops Join), Hash Join, and sort merge join.
  • the method further includes: determining first data and second data in the first data table according to the first identifier, where the first identifier is used to indicate a storage medium storing data in the data table Types of.
  • an apparatus for generating an execution plan including a first minute execution plan and a second minute execution plan
  • the generating means comprising: a parser for parsing the obtained data manipulation language DML statement to determine a first data table corresponding to the DML statement, the first data table includes first data stored on a solid state drive SSD medium, and second data stored on a mechanical hard disk HDD medium; and an optimizer for Generating, by the first data, the first minute execution plan, the first minute execution plan includes reading the first data by a table scan; and generating the second minute execution plan for the second data
  • the second sub-execution plan includes: after the second data is pre-sorted, the pre-sorted second data is read after the second data is pre-sorted; and the second data is read The second data is read by a table scan without pre-sorting.
  • the execution plan generation device is the SQL engine in the database management system.
  • the second aspect is the apparatus corresponding to the first aspect, and the specific implementation description and technical effects can refer to various implementations of the first aspect.
  • a physical machine in a third aspect, the execution plan generated by the physical machine includes a first minute execution plan and a second minute execution plan, the physical machine comprising: at least one processor, non-transients storing executable code a computer readable medium comprising a solid state hard disk SSD medium and a mechanical hard disk HDD medium; the executable code being configured to perform any of the first aspects when executed by the at least one processor Implementation.
  • a database system comprising: a client and a generating device of any of the foregoing execution plans.
  • a cluster database system comprising: a hardware layer and a virtual machine monitor (VMM) running on the hardware layer, and a plurality of virtual machines.
  • the virtual machine runs an executable program based on the VMM and hardware resources provided by the hardware layer to implement some or all of the functions of the above-described execution plan generation device.
  • 1A is a schematic diagram of a stand-alone database system
  • FIG. 1B is a schematic diagram of a cluster database system adopting a shared-storage architecture
  • FIG. 1D is a schematic diagram of a database server according to an embodiment of the present invention.
  • FIG. 2 is a schematic diagram of a method for generating an execution plan according to an embodiment of the present invention
  • FIG. 3 is a schematic diagram of a clustering factor of a data table according to an embodiment of the present invention.
  • FIG. 5 is a schematic diagram of an apparatus for generating an execution plan according to an embodiment of the present invention.
  • FIG. 6 is a schematic diagram of a physical machine according to an embodiment of the present invention.
  • FIG. 8 is a schematic diagram of a database server according to an embodiment of the present invention.
  • A/B can be understood as A or B.
  • Cluster factor Also known as CF value.
  • the data in the data table is stored in the unordered library. When we retrieve the data, it is very resource-intensive to search. So we need to create an index for the table. The role of the index is to make the data in the table The order of the columns is preserved, so there is a problem. The order of the data and index in some tables is very similar, while the data in other tables is far from the order of the indexes.
  • the clustering factor is used to mark the degree of similarity between the data in the table and the order in which the data is in the index. The larger the clustering factor, the higher the order similarity, the smaller the clustering factor, and the lower the sequence similarity.
  • the data in a data table is sequentially stored in the storage unit according to the RID order of the data table, and the data is sorted according to the key values in the index, and the order of sorting is different from the data stored in the data table, then
  • the address of the accessed data is not continuous, thereby generating random I/O, and the random I/O indicates the cost of the operation of accessing the data with discontinuous addresses.
  • the clustering factor is based on a value on the index column on the table, and each index has a clustering factor.
  • the clustering factor is used to describe the degree of similarity between the index blocks and the data stored on the table block, that is, whether the data rows on the table are stored in the same order as the index columns.
  • the value of CF is essentially equivalent to the physical I/O or block access number, and if the same block is read consecutively, it is considered that only one physical I/O is required. If it is not continuous, the physical I/O of reading data will increase greatly.
  • a good CF value is close to the number of blocks on the table, and a poor CF value is close to the number of rows on the table.
  • the clustering factor is calculated by the row that exists on the table and the index block when the index is created.
  • the value of the clustering factor can take many forms, mainly by indicating the order of the index key value and the RID order by the relative value.
  • the value can be between [0, 1], and the closer to 1 is the clustering.
  • the value of the factor ranges from [0, 1]. The larger the value, the more consistent the order of the index key values with the RID order.
  • the closer the CF value is to the number of blocks on the table the more the order of the index key values is consistent with the RID order, and the CF value is close to the number of rows on the table. Indicates that the order of the index key values is more consistent with the RID order, and is called a bad CF.
  • the clustering factor is calculated by the rows existing on the table and the index block during the index creation process.
  • Random I/O input/output
  • the random I/O mentioned in this application is mainly used to represent the cost of random access.
  • the feature of random access is that the data of each I/O request has a large span on the disk (for example, distributed in different sectors), so N very small I/O requests (such as 1K) must be N times of I/O requests can get the corresponding data.
  • the characteristics of sequential access follow the machine access instead, and the data it requests is contiguous on the disk.
  • Solid state drive SSD
  • Solid State Drive Solid State Drive
  • Solid State Drive is a hard disk made of a solid state electronic memory chip array, which is composed of a control unit and a storage unit (such as a FLASH chip, a DRAM chip, etc.).
  • a list prefetch technique sorts the RIDs first, and then sorts them.
  • the RID list performs sequential access of the table data, which can greatly reduce random I/O.
  • This method also derives the join type of the table connection.
  • the optimizer determines whether to adopt a list prefetching technique by using the index clustering characteristics reflected by the clustering factor, and finally generates a single execution plan for runtime execution.
  • generating an execution plan does not make reference to the optimal execution plan for each block based on the underlying data storage medium variability.
  • the generation method may be used for a cluster of database systems, such as a distributed cluster, which may share a storage medium, ie, share memory, or The distributed cluster does not share a storage medium, that is, share-nothing, and the distributed cluster includes a plurality of nodes that deploy a database system, and the nodes may be physical nodes (such as physical machines) or logical nodes (such as virtual machines or container).
  • the generation method can also be used in a single physical machine, which is not limited in the embodiment of the present invention.
  • the Database System is an ideal data processing system developed to meet the needs of data processing.
  • the database system generally consists of the following three parts: (1) database (database, DB), refers to a collection of organized, shareable data that is stored in the computer for a long time. The data in the database is organized, described and stored according to a certain mathematical model, with less redundancy, higher data independence and scalability, and can be shared by various users. (2) Hardware, including data storage required to store data, such as memory and/or disk.
  • DBMS database management system
  • DBMS database management system
  • DBMS database management system
  • the database engine is DBMS. core content. It should be understood that the present application is directed to the case where different types of storage media are included in the data storage.
  • FIG. 1A is a schematic diagram of a stand-alone database system, including a database management system and a data store (Data Store) for providing services such as querying and modifying a database, and the database management system stores data in the data store.
  • the database management system and data storage are usually located on a single server, such as an SMP (Symmetric Multi-Processor) server.
  • the SMP server includes multiple processors, all of which share resources such as bus, memory, and I/O systems.
  • the functionality of the database management system can be implemented by one or more processors executing programs in memory.
  • FIG. 1B is a schematic diagram of a cluster database system adopting a shared-storage architecture, including multiple nodes (such as nodes 1-N in FIG. 1B), each node is deployed with a database management system, and can provide a database query for the user. And modifying services, multiple database management systems store shared data in the shared data store, and perform read and write operations on the data in the data store through the switch.
  • the shared data store can be a shared disk array, it being understood that the shared data store includes the various storage media mentioned above in this application.
  • a node in a clustered database system can be a physical machine, such as a database server, or a virtual node, such as a virtual machine or container running on an abstract hardware resource. If the node is a physical machine, the switch is a Storage Area Network (SAN) switch, an Ethernet switch, a fiber switch, or other physical switching device. If the node is a virtual machine, the switch is a virtual switch.
  • SAN Storage Area Network
  • FIG. 1C is a schematic diagram of a cluster database system adopting a shared-nothing architecture, each node has its own unique hardware resources (such as data storage), an operating system, and a database, and nodes communicate through a network. Under this system, the data will be distributed to each node according to the database model and application characteristics. The query task will be divided into several parts, executed in parallel on all nodes, and coordinated with each other to provide database services as a whole. All communication functions are in the same way. Implemented on a high-bandwidth network interconnection system.
  • the nodes can be either physical nodes, such as physical machines, or virtual nodes, such as virtual machines or containers running on abstract hardware resources.
  • the architecture of FIG. 1C and FIG. 1B may include a host A and a plurality of standby machines B connected to the host, wherein a database system is running on both the host and the standby.
  • a database system is running on both the host and the standby.
  • the database system running on the host is usually called the primary database system, and the database system running on each standby machine is called the standby database system.
  • the data store of the database system includes, but is not limited to, a solid state drive (SSD), a disk array, or other type of non-transitory computer readable medium.
  • SSD solid state drive
  • the database is not shown in Figures 1A-1C, it should be understood that the database is stored in a data store.
  • a database system may include fewer or more components than those shown in Figures 1A-1C, or include components other than those shown in Figures 1A-1C, Figures 1A-1C only Components that are more relevant to the implementations disclosed by embodiments of the present invention are shown.
  • a cluster database system can include any number of nodes.
  • the database management system functions of each node may be implemented by appropriate combinations of software, hardware, and/or firmware running on each node, respectively.
  • an embodiment of the present invention provides a database server 100, including: at least one processor 104, a non-transitory computer-readable medium 106 and database management for storing executable code.
  • System 108 The executable code, when executed by at least one processor 104, is configured to implement the components and functions of database management system 108.
  • the non-transitory computer readable medium 106 is an HHD medium or includes a variety of storage media such as SSD media and HDD media.
  • the non-transitory computer readable medium 106 can include one or more non-volatile memories.
  • the non-volatile memory includes a semiconductor memory device, such as an EPROM (Erasable Programmable Read Only Memory).
  • the non-transitory computer readable medium 106 can also include any device that is configured as a main memory.
  • the at least one processor 104 can include any type of general purpose computing circuit or special purpose logic circuit, such as an FPGA (Field Programmable Gate Array) or an ASIC (Application Specific Integrated Circuit).
  • the at least one processor 104 can also be one or more processors, such as a CPU, coupled to one or more semiconductor substrates.
  • the database management system 108 may be an RDBMS (Relational Database Management System).
  • the database management system 108 supports SQL (Structured Query Language).
  • SQL refers to a specialized programming language that is dedicated to managing data stored in relational databases.
  • SQL can refer to various types of data-related languages, including, for example, data definition languages and data manipulation languages, where SQL can include data insertion, query, update and delete, schema creation and modification, and data access control.
  • SQL can include descriptions related to various language elements, including clauses, expressions, predicates, queries, and statements.
  • a clause can refer to various components of a statement and a query, and in some cases, a clause can be considered optional.
  • the expression can be configured to generate scalar values and/or tables including data columns and/or rows.
  • predicates can be configured to specify conditions for adjusting the effects of statements and queries.
  • Database client 102 can include any type of device or application that is configured to interact with database management system 108.
  • database client 102 includes one or more application servers.
  • the embodiment of the present application relates to the SQL engine 110, which may be a process and a program for collecting data storage information (for example, an analyze index).
  • the SQL engine 110 includes an optimizer 114, for example, optimization.
  • the device can be a query optimizer that generates a valid execution plan for the SQL statement, generates a set of execution plans that can be used for the statement, estimates the cost of each execution plan, compares the cost of the plan, and ultimately selects a least costly execution. plan.
  • the optimizer may include information about the types of media on the hybrid, the distribution ratio of the data on each medium, and the distribution of a certain field or a certain type of value, as described in the present application.
  • the SQL engine 110 further includes a parser 112 for parsing the semantics and syntax of the DML statement, for example, determining whether the syntax of a SQL statement is correct, determining whether a table of a certain name exists, whether a certain field exists, for example, understanding in a mixed memory. The type of each medium, etc.
  • a query is a request to view, access, and/or manipulate data stored in a database.
  • Database management system 108 can receive queries in SQL format (referred to as SQL queries) from database client 102.
  • SQL queries queries in SQL format
  • the database management system 108 generates query results corresponding to the query by accessing relevant data from the database and manipulating the relevant data, and returns the query results to the database client 102.
  • a database is a collection of data organized, described, and stored in a mathematical model that can include one or more database structures or formats, such as row storage and column storage.
  • the database is typically stored in a data store, such as external data store 120 in FIG. 1D, or non-transitory computer readable medium 106.
  • the database management system 108 is an in-memory database management system.
  • an improvement of the representation method of the clustering factor is proposed, for example, when calculating the clustering factor, considering the difference in the kind of the storage medium, for example, the first layer clustering factor and the second layering layer are proposed.
  • the cluster factor optimizes the cost estimate for different levels of operation through a multi-layer clustering factor in the process of generating an execution plan. That is to say, before using these optimized clustering factors, these optimized clustering factors should be obtained according to the storage of the data. This process can be done with the creation of a data table or data index, or before an execution plan is generated from the parsed DML statements.
  • the first data table may also establish a log in the process of storing to the medium to record the storage distribution of each segment or group of data of the first data table.
  • the characteristics of the data storage can be considered more objectively, and the data read in the generated execution plan is more consistent with the data in the storage medium for the data stored in different media.
  • the actual situation so that the execution of the generated execution plan is less expensive, improves the performance of the database to operate on the data, and saves resources.
  • the read data is read by the table scan
  • the corresponding second sub-execution plan is , similar to the prior art, analyzes the data on the HDD to determine whether pre-sorting is to be performed. For example, whether a pre-sorting is to be performed can be determined by a clustering factor of data on the HDD. In this way, when generating the execution plan, the read characteristics of different storage media are considered, and the differential read mode suitable for each part of the data is performed.
  • generating, by the first data, a first sub-execution plan including: generating, according to a value of a virtual clustering factor of the first data, the first sub-execution plan, the first data
  • the virtual clustering factor represents the cost of reading the first data stored in the SSD storage medium by an index.
  • a clustering factor obtained using a determination method similar to the clustering factor in the prior art is referred to as an original clustering factor, or a clustering factor directly referred to as a certain data.
  • the original clustering factors are all based on the I/O characteristics of the HDD storage medium, and do not distinguish between SSD and HDD.
  • the clustering factor is not for a part of the data in the table, but for the entire table, that is, for the first data and the second data, the prior art has no clustering factor.
  • the logical clustering factor described in this application is similar to the clustering factor in the prior art, and is directed to the entire data table, but the computing method of the logical clustering factor is clustered with the prior art. Different factors, the logical clustering factor is no longer used to determine how to read the data in the corresponding data table.
  • the first layer clustering factor includes an original clustering factor and a fake clustering factor.
  • the original clustering factor is obtained in the same way as the clustering factor in the prior art, except that the original clustering factor is for the part of the data stored in the HDD storage medium in the data table, and the clustering factor in the prior art is For the entire data table, of course, it can also be simply referred to as a clustering factor of a certain data, such as the clustering factor of the second data mentioned herein.
  • the original clustering factor is used for the data stored in the HDD storage medium.
  • the virtual clustering factor is used for data stored in an SSD storage medium.
  • the virtual clustering factor is a large clustering factor constant within the range of the clustering factor.
  • the purpose is to simulate the SSD random I/O cost is small and the clustering is not good but the overall I/ O is not a costly feature.
  • the virtual clustering factor is related to the consumption of random I/O of the corresponding SSD storage medium and the degree of continuity of the index corresponding to the data in the SSD storage medium. It should be understood that the virtual clustering factor and the original clustering factor are calculated in a similar manner, depending mainly on the continuity of the index corresponding to the data in the storage medium.
  • the clustering factor of the SSD storage medium is calculated according to the I/O characteristics of the HDD storage medium and the degree of continuity of the index corresponding to the data in the SSD storage medium.
  • the virtual clustering factor is calculated according to the I/O characteristics of the SDD storage medium and the degree of continuity of the index corresponding to the data in the SSD storage medium. Therefore, in one embodiment of the present application, various clustering factors have a value ranging from 0 to 1, and the virtual clustering factor is generally a number closer to 1, for example, 0.8, 0.85, 0.88, 0.93, 0.95, 0.97, etc.
  • the logical clustering factor is obtained by the first layer clustering factor.
  • the second layer clustering factor is used for a data table in which a portion of the data is stored in the HDD storage medium and another portion is stored in the SSD storage medium.
  • the second layer clustering factor is calculated based on the original clustering factor and the virtual clustering factor. It should be understood that as long as the difference of the clustering factors of various storage media is considered in the second layer logical clustering factor, that is, the virtual clustering factor corresponding to the SSD medium is used, or the corresponding
  • the original clustering factor of the HDD medium and the virtual clustering factor corresponding to the SSD medium may be used.
  • specific calculation methods refer to some specific calculation examples in the following, and there are many calculation methods, and the present application is not limited.
  • the first layer clustering factor and the second layer clustering factor are calculated and generated in the statistical information collecting stage, and the generated result may be stored in a corresponding table of the database system.
  • the operation is a join
  • the first data table is a look in the operation
  • the operator is one of a hybrid connection (Hybrid Join), a nested loops join (Hest Join Loops Join), a hash join (Hash Join), and a sort merge join.
  • Hybrid Join a hybrid connection
  • nested loops join Hest Join Loops Join
  • Hash Join a hash join
  • sort merge join a sort merge join
  • step 201 includes: generating, according to the second identifier, the first minute execution plan, where the second identifier is used to mark data stored in the data table on the solid state drive SSD medium.
  • an identifier is set, which is used to record a storage medium corresponding to different data blocks (which may be columns or rows or partitions, etc.) in the data table, for example, a flag is set, in the case of SSD media, and In the case of HDD media, different values are taken.
  • the table scan is directly performed, and if the operator corresponds to the data Stored in the SSD medium, the table space data identifier RID of the result after the index scan is sorted, and then the table data is read according to the sort result.
  • the prior art query only applies one execution plan, and there is no SSD and HDD data block for different random I/O capabilities to perform sub-planning for each characteristic.
  • the query from the index to the table data; and, the second layer optimization of the index clustering factor which is not available in the prior art is proposed, and the logical clustering factor obtained by the second layer optimization can represent the overall situation of a data table, so that The cost of various algorithms involving inter-table operations can be more accurately evaluated, thereby selecting a more appropriate algorithm.
  • the proposed multi-level clustering index is used to implement different index-to-table data preparation.
  • the prior art since the prior art also uses the clustering factor, the modification to the existing system is small, and it is well compatible with the prior art, saving development and maintenance costs.
  • the query data is taken as an example.
  • the table data of the database is stored on the hybrid storage medium, and the query involves an index query and a query based on the index to the table data.
  • the first layer clustering factor primary clustering factor and virtual clustering factor
  • the second layer clustering factor logical clustering factor
  • a data table is distributed on a plurality of disk media, including at least one HDD and at least one SSD, and the proportion of data distribution on each disk medium is represented by a distribution ratio using a variable p i , 0 ⁇ p i ⁇ 1, i is a positive integer greater than one. It can be understood that the sum of p i corresponding to each disk medium of one data table is 1.
  • the data of the data table is distributed in an HDD storage medium and an SSD storage medium.
  • the data stored in the HDD storage medium is represented by CK1
  • the data stored in the SSD storage medium is represented by CK2
  • the ratio of data distribution is CK1 accounting for 20%, and CK2 accounting for 80%.
  • the calculation of the second layer logical clustering factor is performed according to the determined first layer clustering factor. It should be understood that as long as the difference of the clustering factors of various different storage media is considered in the second layer logical clustering factor, that is, the original clustering factor corresponding to the HDD medium and the virtual aggregate corresponding to the SSD medium are used.
  • the cluster factor can be. There are many specific calculation methods, and the present application is not limited. For example, averaging the first layer of clustering factors can be a weighted average or directly averaged. It should be understood that if a weighted average is used, the weights may be preset in advance, for example, according to the ratio of the data of the data table in each storage medium.
  • the logical clustering factor value mapped to is 0.88.
  • the logical clustering factor can be determined directly through a preset mapping relationship.
  • the first and second layer clustering factors are derived to perform DML statements in a more appropriate manner.
  • the first layer clustering factor of course optimizes the index-to-table data reading, which can more objectively consider the characteristics of the data storage, thereby reducing the overhead of random I/O in the process of reading data, so as to improve the The performance of the data for operation.
  • the use of the first layer of clustering factor is also to some extent compatible with the prior art, for example, in the process of generating the execution plan, the clustering factor is originally required. In this way, the second layer clustering factor can be obtained according to the first layer clustering factor, instead of the original clustering factor which cannot be accurately calculated.
  • the first layer of clustering factor can be used independently, that is, if the operation to be considered only involves the reading of the data indexed to the table (also known as the underlying sub-planning), Then there is no need to calculate and use the second layer of clustering factor.
  • Figure 4 can be understood as receiving a connection instruction A, which indicates the connection of the data of the table and another table, and the SQL engine needs to determine the information of the table corresponding to Figure 3 and some preset rules based on the determined
  • the execution plan of the connection instruction A is, for example, the rule includes a threshold of 0.8 using the RID list ordering and the corresponding clustering factor of the hybrid connection.
  • a mixed storage data table (part of the data of the table is stored on the solid state hard disk SSD medium, and a part is stored on the mechanical hard disk HDD medium) is used.
  • the virtual clustering factor and the logical clustering factor in the embodiments of the present application can more objectively consider the characteristics of the data storage, so that the data reading manner used in the generated execution plan is more in line with the actual characteristics of the data, thereby reducing the running of the execution. Unnecessary consumption in the plan to improve the performance of the data.
  • the execution plan includes a first minute execution plan and a second minute execution plan.
  • the device includes a parser 501 for parsing the obtained data manipulation language DML statement to Determining a first data table corresponding to the DML statement, the first data table including first data stored on a solid state hard disk SSD medium, and second data stored on a mechanical hard disk HDD medium; and an optimizer 502, Generating the first sub-execution plan for the first data, the first sub-execution plan includes reading the first data by a table scan; and generating the second score for the second data Executing a plan, the second sub-execution plan includes: after pre-sorting the second data, reading the pre-sorted second data after the second data needs to be pre-sorted; In the case where the two data are not pre-sorted, the second data is read by a table scan.
  • the characteristics of the data storage can be considered more objectively, and the data read in the generated execution plan is more consistent with the data in the storage medium for the data stored in different media.
  • the actual situation so that the execution of the generated execution plan is less expensive, improves the performance of the database to operate on the data, and saves resources.
  • the device is a device corresponding to the method for generating the execution plan described in the embodiment of the present application, various implementation details and technical effects thereof are referred to the relevant paragraphs of the method described in the present application, and details are not described herein again.
  • the processor 40 can be implemented by one or more processors, and FIG. 6 is exemplarily illustrated by taking only one processor as an example.
  • the processor 40 can be a central processing unit (English: central processing unit, abbreviated: CPU).
  • the processor 40 can also be other general-purpose processors, digital signal processing (English: digital signal processing, abbreviation: DSP), application specific integrated circuit (ASIC), field programmable gate array (English) : field-programmable gate array, abbreviation: FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc.
  • the general purpose processor may be a microprocessor or the processor or any conventional processor or the like.
  • the interface circuit 41 may specifically be a communication interface on a physical machine.
  • the communication interface can be a wireless communication interface.
  • the wireless communication interface may be a wireless module of a physical machine or the like.
  • the processor 40 transmits and receives data to and from other devices, such as other physical devices, through the interface circuit 41.
  • the embodiment further provides a readable storage medium, where the readable storage medium includes computer execution instructions, when the physical machine runs, the processor of the physical machine executes the computer to execute instructions, so that the physical machine executes the present invention.
  • an embodiment of the present invention further provides a cluster database system 500, including: a hardware layer 1007 and a virtual machine monitor (VMM) 1001 running on the hardware layer 1007, and a plurality of virtual machines 1002.
  • a virtual machine can be used as a data node of the cluster database system 500.
  • Hardware layer 1007 A hardware platform running in a virtualized environment, which can be abstracted from hardware resources of one or more physical hosts.
  • the hardware layer may include a variety of hardware, including, for example, a processor 1004 (eg, a CPU) and a memory 1005, and may also include a network card 1003 (eg, an RDMA network card), a high speed/low speed input/output (I/O, Input/Output) device. And other devices with specific processing capabilities.
  • Hardware layers 906 and 816 contain the basic hardware elements required for the operating system and applications to run, such as processors, such as CPUs, memory, input/output devices, network interfaces, and the like.
  • executable program should be interpreted broadly to include, but not be limited to, instructions, instruction sets, code, code segments, subroutines, software modules, applications, software packages, Threads, processes, functions, firmware, middleware, etc.
  • the size of the sequence of the method steps described in the above embodiments does not mean that the order of execution is sequential, and the order of execution of each process should be determined by its function and internal logic, and should not constitute any limitation on the actual implementation process of the embodiment of the present invention.
  • the disclosed systems, devices, and methods may be implemented in other manners.
  • the device embodiments described above are merely illustrative.
  • the division of the unit is only a logical function division.
  • there may be another division manner for example, multiple units or components may be combined or Can be integrated into another system, or some features can be ignored or not executed.
  • the mutual coupling or direct coupling or communication connection shown or discussed may be an indirect coupling or communication connection through some interface, device or unit, and may be in an electrical, mechanical or other form.
  • each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.
  • the functions may be stored in a computer readable storage medium if implemented in the form of a software functional unit and sold or used as a standalone product.
  • the technical solution of the present invention which is essential or contributes to the prior art, or a part of the technical solution, may be embodied in the form of a software product, which is stored in a storage medium, including
  • the instructions are used to cause a computer device (which may be a personal computer, server, or network device, etc.) to perform all or part of the steps of the methods described in various embodiments of the present invention.
  • the foregoing storage medium includes: a U disk, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk, and the like.
  • the storage medium includes a solid state hard disk SSD medium and a mechanical hard disk HDD medium.
  • the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, may be located in one place, or may be distributed to multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of the embodiment.
  • each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.
  • the above integrated unit can be implemented in the form of hardware or in the form of a software functional unit.
  • the integrated unit if implemented in the form of a software functional unit and sold or used as a standalone product, may be stored in a computer readable storage medium.
  • the technical solution of the present invention which is essential or contributes to the prior art, or all or part of the technical solution, may be embodied in the form of a software product stored in a storage medium.
  • a number of instructions are included to cause a computer device (which may be a personal computer, server, or network device, etc.) or processor to perform all or part of the steps of the methods described in various embodiments of the present invention.
  • the foregoing storage medium includes various media that can store program codes, such as a USB flash drive, a mobile hard disk, a ROM, a RAM, a magnetic disk, or an optical disk.

Landscapes

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

Abstract

L'invention concerne un procédé de génération d'un plan d'exécution, le plan d'exécution comprenant un premier sous-plan d'exécution et un second sous-plan d'exécution. Le procédé consiste à : analyser une instruction de langage de manipulation de données (DML) obtenue pour déterminer une première table de données, la première table de données comprenant des premières données stockées dans un support de disque à semi-conducteurs (SSD), et des secondes données stockées sur un disque dur mécanique (HDD); générer le premier sous-plan d'exécution pour les premières données, le premier sous-plan d'exécution comprenant la lecture des premières données au moyen d'un balayage de table ; et générer le second sous-plan d'exécution pour les secondes données. De cette manière, un plan d'exécution qui est plus aligné avec les caractéristiques de stockage de données est généré, de sorte que davantage de ressourçage peuvent être sauvegardés lorsque le plan d'exécution est exécuté.
PCT/CN2018/074034 2017-03-01 2018-01-24 Procédé et dispositif de génération de plan d'exécution, et serveur de base de données Ceased WO2018157680A1 (fr)

Applications Claiming Priority (2)

Application Number Priority Date Filing Date Title
CN201710118119.2A CN108536692B (zh) 2017-03-01 2017-03-01 一种执行计划的生成方法、装置及数据库服务器
CN201710118119.2 2017-03-01

Publications (1)

Publication Number Publication Date
WO2018157680A1 true WO2018157680A1 (fr) 2018-09-07

Family

ID=63370551

Family Applications (1)

Application Number Title Priority Date Filing Date
PCT/CN2018/074034 Ceased WO2018157680A1 (fr) 2017-03-01 2018-01-24 Procédé et dispositif de génération de plan d'exécution, et serveur de base de données

Country Status (2)

Country Link
CN (1) CN108536692B (fr)
WO (1) WO2018157680A1 (fr)

Cited By (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN110908993A (zh) * 2018-09-14 2020-03-24 北京京东尚科信息技术有限公司 分析数据库索引合理性的方法和装置
CN111767279A (zh) * 2019-04-01 2020-10-13 北京百度网讯科技有限公司 数据合并方法和装置
CN111831425A (zh) * 2019-04-18 2020-10-27 阿里巴巴集团控股有限公司 一种数据处理方法、装置及设备
CN114996303A (zh) * 2022-06-08 2022-09-02 平凯星辰(北京)科技有限公司 调用数据库的代价因子的校准方法、装置、设备及介质
CN119646048A (zh) * 2025-02-12 2025-03-18 恒生电子股份有限公司 执行计划构建方法及系统

Families Citing this family (10)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN110968579B (zh) * 2018-09-30 2023-04-11 阿里巴巴集团控股有限公司 执行计划的生成与执行方法、数据库引擎及存储介质
CN109508335B (zh) * 2018-12-03 2022-10-28 中国电波传播研究所（中国电子科技集团公司第二十二研究所） 一种海量地杂波数据分类存储方法
US11163756B2 (en) * 2019-04-16 2021-11-02 Snowflake Inc. Querying over external tables in database systems
CN110532245B (zh) * 2019-08-22 2022-03-11 华侨大学 一种存储端数据处理模式中的算子问题信息处理方法
CN110569257B (zh) * 2019-09-16 2022-04-01 上海达梦数据库有限公司 数据处理方法、相应装置、设备及存储介质
CN110807030B (zh) * 2019-09-27 2021-03-16 蚂蚁金服（杭州）网络技术有限公司 一种数据连接的方法、装置及电子设备
CN113934763B (zh) * 2021-12-17 2022-04-12 北京奥星贝斯科技有限公司 分布式数据库的sql查询方法及装置
CN114860625A (zh) * 2022-03-31 2022-08-05 网宿科技股份有限公司 数据访问方法、装置、设备及可读存储介质
CN114443670B (zh) * 2022-04-07 2022-07-08 北京奥星贝斯科技有限公司 数据的存储、读取方法及装置
CN115114359B (zh) * 2022-05-27 2023-11-14 马上消费金融股份有限公司 用户数据处理方法及装置

Citations (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN101408900A (zh) * 2008-11-24 2009-04-15 中国科学院地理科学与资源研究所 一种网格计算环境下的分布式空间数据查询优化方法
CN105243068A (zh) * 2014-07-09 2016-01-13 华为技术有限公司 数据库系统的查询方法、服务器和能耗测试系统
CN105518674A (zh) * 2013-09-05 2016-04-20 华为技术有限公司 优化对称资源上的并行查询执行的机制
WO2016146057A2 (fr) * 2015-03-16 2016-09-22 Huawei Technologies Co., Ltd. Procédé et appareil d'optimisation de plan permettant d'optimiser un plan d'exécution de requêtes

Family Cites Families (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US8903801B2 (en) * 2007-09-14 2014-12-02 Oracle International Corporation Fully automated SQL tuning
CN103473260B (zh) * 2013-06-25 2017-05-03 北京控制工程研究所 一种面向并发olap的测试数据分层聚簇查询处理系统及方法
CN103377292B (zh) * 2013-07-02 2017-02-15 华为技术有限公司 数据库结果集缓存方法及设备

Patent Citations (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN101408900A (zh) * 2008-11-24 2009-04-15 中国科学院地理科学与资源研究所 一种网格计算环境下的分布式空间数据查询优化方法
CN105518674A (zh) * 2013-09-05 2016-04-20 华为技术有限公司 优化对称资源上的并行查询执行的机制
CN105243068A (zh) * 2014-07-09 2016-01-13 华为技术有限公司 数据库系统的查询方法、服务器和能耗测试系统
WO2016146057A2 (fr) * 2015-03-16 2016-09-22 Huawei Technologies Co., Ltd. Procédé et appareil d'optimisation de plan permettant d'optimiser un plan d'exécution de requêtes

Cited By (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN110908993A (zh) * 2018-09-14 2020-03-24 北京京东尚科信息技术有限公司 分析数据库索引合理性的方法和装置
CN111767279A (zh) * 2019-04-01 2020-10-13 北京百度网讯科技有限公司 数据合并方法和装置
CN111831425A (zh) * 2019-04-18 2020-10-27 阿里巴巴集团控股有限公司 一种数据处理方法、装置及设备
CN114996303A (zh) * 2022-06-08 2022-09-02 平凯星辰(北京)科技有限公司 调用数据库的代价因子的校准方法、装置、设备及介质
CN119646048A (zh) * 2025-02-12 2025-03-18 恒生电子股份有限公司 执行计划构建方法及系统

Also Published As

Publication number Publication date
CN108536692A (zh) 2018-09-14
CN108536692B (zh) 2022-03-11

Similar Documents

Publication Publication Date Title
CN108536692B (zh) 一种执行计划的生成方法、装置及数据库服务器
US12248476B2 (en) System and method for dynamic database split generation in a massively parallel or distributed database environment
US10089377B2 (en) System and method for data transfer from JDBC to a data warehouse layer in a massively parallel or distributed database environment
US10380114B2 (en) System and method for generating rowid range-based splits in a massively parallel or distributed database environment
US10528596B2 (en) System and method for consistent reads between tasks in a massively parallel or distributed database environment
US10180973B2 (en) System and method for efficient connection management in a massively parallel or distributed database environment
CN103309958B (zh) Gpu和cpu混合架构下的olap星型连接查询优化方法
US11544268B2 (en) System and method for generating size-based splits in a massively parallel or distributed database environment
US10089357B2 (en) System and method for generating partition-based splits in a massively parallel or distributed database environment
US10078684B2 (en) System and method for query processing with table-level predicate pushdown in a massively parallel or distributed database environment
US20100235344A1 (en) Mechanism for utilizing partitioning pruning techniques for xml indexes
US11734308B2 (en) Autonomous caching for views
Theocharidis et al. SRX: efficient management of spatial RDF data
US11947537B1 (en) Automatic index management for a non-relational database
US8832157B1 (en) System, method, and computer-readable medium that facilitates efficient processing of distinct counts on several columns in a parallel processing system
US11275737B2 (en) Assignment of objects to processing engines for efficient database operations
CN113806190A (zh) 一种预测数据库管理系统的性能的方法、装置及系统
US12346329B2 (en) Range partitioned in-memory joins
WO2025081420A1 (fr) Procédé et appareil de traitement d'interrogation basés sur une jointure multi-table
Sangat et al. Distributed ATrie group join: Towards zero network cost
US20170031909A1 (en) Locality-sensitive hashing for algebraic expressions
O'Connell et al. Optimizer and parallel engine extensions for handling expensive methods based on large objects
CN117529714A (zh) 用于为迁移rdbms推荐存储格式的方法和系统
CN113742346A (zh) 资产大数据平台架构优化方法
US12321361B2 (en) Unified structured and semi-structured data types in database systems

Legal Events

Date Code Title Description
121 Ep: the epo has been informed by wipo that ep was designated in this application

Ref document number: 18761607

Country of ref document: EP

Kind code of ref document: A1

NENP Non-entry into the national phase

Ref country code: DE

122 Ep: pct application non-entry in european phase

Ref document number: 18761607

Country of ref document: EP

Kind code of ref document: A1