一种并行查询技术介绍

原文: MySQL Query Execution using Multiple Threads

http://www.paralleluniverse-inc.com/MySQL_query_execution_using_multiple_threads_technology.txt

 

Technology White Paper - MySQL Query Execution using Multiple Threads


1. Motivation for the New Technology

   Today microprocessors which provide computational resource to RDBMS (Relational Data Base
   Management System) contain multiple CPU cores where each core is capable of executing its
   own code independently.
   If RDBMS server (hereafter referred as the server) can break down its task into a number of
   smaller subtasks then these subtasks may be performed by those multiple cores concurrently
   resulting in faster execution.
   This new technology speeds up the execution phase of query tasks.  



2. Background Information

   MySQL query determines combinations of records from given tables which satisfy a given
   condition.

   SELECT field_list FROM table_list WHERE condition;

   where field_list represents fields from the tables to be output, each table consists of a
   number of records and each record is comprised of a number of fields(attributes) and the
   condition specifies field relationships.

   The server parses and translates the query into the optimum query execution plan which
   specifies a order by which the tables are processed, how records are read from the tables,
   conditions which must be satisfied by these records and process/output record method.

Current Technology (All operations carried out by a single thread)


        --- Optimization Phase ---- ------------------ Execution Phase -----------------
       |                           |                                                    |
       |                           |                                                    |

           ----------                             -----------               ---------
          |          |       Table Order         |           |             | #Process|  
Query --> | Parse &  | --Table Access Methods--> | Execute   | --Record--> | Records | --> Result
          | Optimize |    Record Match Conds     | *Thread 1 |   Combo     | Thread 1|       
           --------                               -----------               ---------        
                        #Process Record Method                                               
                      |                        |                                          
                       ------------------------                                          
                         Query Execution Plan                                            

   *Thread is a flow of code execution scheduled by the host operating system to run on a
   particular core of the CPU.



New Technology

   This new technology provides multiple (n) threads execution of this plan.

                                    ------------------ Execution Phase -----------------
                                   |                                                    |
                                   |                                                    |

                                                  -----------               ---------
                             Table Order         |           |             | Process |  
                         Table Access Methods--> | Execute   | --Record--> | Records | --> Result
                          Record Match Conds     |Threads 1~n|   Combo     | Thread n|       
                                                  -----------               ---------        
                         Process Record Method                                               
                                                                                                  
                                                                                                  

3. Current Technology, example with 3 tables, recursive execution by one thread

   1st table(t1) Read a record (may use an index) and store (used fields only) and if it satisfies
   the condition associated with this table then move down to the next table otherwise continue
   reading records when there is no more record to read, the procedure is finished.

   2nd table(t2) Read a record (may use an index derived from record of t1) and if it satisfies
   the condition associated with this table which depends on records from this table and t1 then
   move down to the next table otherwise continue reading records when there is no more record
   to read, move back up to the previous table.

   Last table(t3) Read a record (may use an index derived from records of t2 and t1) and if it
   satisfies the condition associated with this table which depends on records from this table,
   t2 and t1 then process and output this particular combination of records according to the
   process record method of the query execution plan in any case continue reading records when
   there is no more record to read, move back up to the previous table.

                                                              Disk Storage     
                                                            ------------------
                                                           |                  |
                                                           |                  |
           ----------------------------                    |                  |
          |                            |                   |                  |
          |                            |                   |                  |
          |          Processing of     |                   |                  |
          |              t1            |                   |                  |
          |          (by thread 1)     |                   |                  |
          |                            |                   |    ----------    |
          |              record        |                   |   |          |   |
          |  -----       ------        |       read        |   |          |   |
          | |cond | <-- |      |       |    <----------    |   |    t1    |   |
          |  -----       ------        |                   |   |          |   |
          |                            |                   |   |          |   |
           ----------------------------                    |    ----------    |
                                                           |                  |
                                                           |                  |
                                                           |                  |
           ----------------------------                    |                  |
          |                            |                   |                  |
          |                            |                   |                  |
          |          Processing of     |                   |                  |
          |              t2            |                   |                  |
          |     1    (by thread 1)     |                   |                  |
          |     |                      |                   |    ----------    |
          |     V        record        |                   |   |          |   |
          |  -----       ------        |       read        |   |          |   |
          | |cond | <-- |      |       |    <----------    |   |    t2    |   |
          |  -----       ------        |                   |   |          |   |
          |                            |                   |   |          |   |
           ----------------------------                    |    ----------    |
           1 represents a record of t1                     |                  |
                                                           |                  |
                                                           |                  |
           ----------------------------                    |                  |
          |                            |                   |                  |
          |                            |                   |                  |
          |          Processing of     |                   |                  |
          |              t3            |                   |                  |
          |   2 1    (by thread 1)     |                   |                  |
          |   | |                      |                   |    ----------    |
          |   V V        record        |                   |   |          |   |
          |  -----       ------        |       read        |   |          |   |
          | |cond | <-- |      |       |    <----------    |   |    t3    |   |
          |  -----       ------        |                   |   |          |   |
          |                            |                   |   |          |   |
           ----------------------------                    |    ----------    |
           2 represents a record of t2                     |                  |
                                                           |                  |
                                                           |                  |
                                                           |                  |
                                                           |                  |
                                                            ------------------



4. New Technology, same example, execution by 3 threads

   1st table(t1, executed by thread 1) Read a record and if it satisfies the condition then insert
   this record into inter table buffer between this table and the next (possibly waits in case the
   buffer is full). Continue reading records when there is no more record to read, processing of
   this table is finished.

   2nd table(t2, executed by thread 2) Wait for a record from t1 to be available in the inter
   table buffer between this table and the previous, read a record of this table and if it
   satisfies the condition then insert this record and the record of t1 into the inter table
   buffer. Continue reading records when there is no more record to read, remove the record of
   t1 from the buffer and wait for the next record.

   Last table(t3, executed by thread 3) Wait for a record set of t2 and t1 to be available in the
   inter table buffer then read a record of this table and if it satisfies the condition then
   process and output this particular combination of records. Continue reading records when there
   is no more record to read remove the record set of t2 and t1 from the buffer and wait for the
   next record set.

           ----------------------------                
          |                            |               
          |                            |               
          |          Processing of     |               
          |              t1            |               
          |          (by thread 1)     |               
          |                            |               
          |              record        |               
          |  -----       ------        |       read    
          | |cond | <-- |      |       |    <----------
          |  -----       ------        |               
          |                            |               
           ----------------------------                

                        |
                        V          
                 
       Inter Table    -----
       Buffer        |     |
                     |     |
                     |-----|
                     |  1  |
                     |-----|
                     |  1  |
                     |-----|
                     |  1  |
                     |-----|
                     |  1  |
                     |-----|
                     |  1  |
                     |-----| --
                     |  1  |   | Record Set
                      -----  --
                           
                        |
                        V    
                                                       
           ----------------------------                
          |                            |               
          |                            |               
          |          Processing of     |               
          |              t2            |               
          |     1    (by thread 2)     |               
          |     |                      |               
          |     V        record        |               
          |  -----       ------        |       read    
          | |cond | <-- |      |       |    <----------
          |  -----       ------        |               
          |                            |               
           ----------------------------                

                        |     
                        V  
                 
                      -----
                     |     |
                     |     |
                     |-----|
                     |  2  |
                     |-----|
                     |  1  |
                     |-----|
                     |  2  |
                     |-----|
                     |  1  |
                  -- |-----|
                 |   |  2  |
      Record Set |   |-----|
                 |   |  1  |
                  --  -----
                                   
                        |           
                        V            
                                                       
           ----------------------------                
          |                            |               
          |                            |               
          |          Processing of     |               
          |              t3            |               
          |   2 1    (by thread 3)     |               
          |   | |                      |               
          |   V V        record        |               
          |  -----       ------        |       read    
          | |cond | <-- |      |       |    <----------
          |  -----       ------        |               
          |                            |               
           ----------------------------