Tidb source reading series (3) the life of SQL

Time:2020-5-22

summary

The previous article explained the structure and three core parts of the tidb project. Starting from the SQL processing flow, this article introduces where is the entry, what operations need to be done for SQL, and knows where a SQL comes in, where to process and where to return.

There are many kinds of SQL, such as read, write, modify, delete and manage SQL. Each kind of SQL has its own execution logic, but the process is similar in general, and it runs in a unified framework.

frame

Let’s take a look at the work of a statement as a whole. If you still remember the three core parts mentioned in the previous article, you can think that the first step is to get the statement content through protocol analysis and conversion, then through the logic processing of SQL core layer, generate a query plan, and finally go to the storage engine to get the data for calculation and return the results. This is a rough processing framework, which will be continuously refined in this article.

For the first part, protocol parsing and transformation, all the logic is in the server package, and the main logic is divided into two parts: one is the establishment and management of connections, each connection corresponds to a session; the other is the processing logic on a single connection. The first point is not involved in this article for the time being. Interested students can turn over the code to see how to establish a connection, how to shake hands, and how to destroy it. There will also be a special article to explain it later. For the execution process of SQL, the second point is more important, that is, the connection has been established. The operation on this connection will be explained in detail in this article.

For the second part, the processing of SQL layer is the most complex part of the whole tidb. Why is this part complicated? There are three reasons:

  1. SQL language itself is a complex language. There are many kinds of statements, data types, operators and syntax combinations. These “many” will become “many” and “very many” after permutation and combination, so a lot of code needs to be written to handle them.
  2. SQL is a ideographic language. It just says “what data is needed”, not “how to get data”. Therefore, it requires some complex logic to choose “how to get data”, that is, to choose a good query plan.
  3. The bottom layer is a distributed storage engine, which will face many problems that a single storage engine will not encounter. For example, when making a query plan, it is necessary to consider that the data in the bottom layer is fragmented and how to deal with the network is not available. Therefore, it requires some complex logic to deal with these situations, and a good mechanism to encapsulate these processing logic. These complexities are big obstacles to understand the source code, so this article will try to eliminate these interferences and explain the core logic to you.

There are several core concepts in this layer. If you master these concepts, you will master the framework of this layer. Please pay attention to the following interfaces:

  • Session
  • RecordSet
  • Plan
  • LogicalPlan
  • PhysicalPlan
  • Executor

In the following details, we will explain these interfaces and use them to clear the whole logic.

For the third part, we can think of two parts. The first part is the kV interface layer, which is mainly used to route the request to the correct kV server, receive the return message and pass it to the SQL layer, and handle various abnormal logic in the process. The second part is the specific implementation of kV server. Because tikv is relatively complex, we can first look at the implementation of mock tikv, which has all the SQL Logic related to distributed computing.
In the next few sections, the above three parts will be described in detail.

Protocol layer entrance

After the connection with the client is established, there will be a goroutine listening port in tidb, waiting for the packets sent from the client, and processing the packets sent. This logic is on the server/ conn.go This section describes the logic. First of all clientConn.Run (), in a loop, the network packets will be read continuously:

    445:    data, err := cc.readPacket()

The dispatch () method is then used to process the received requests:

    465:        if err = cc.dispatch(data); err != nil {

Next, enter clientConn.dispatch () method:

    func (cc *clientConn) dispatch(data []byte) error {

The package to be processed here is the original byte array. Readers can refer to the MySQL protocol for its contents. The first byte is the type of command:

        580:     cmd := data[0]

Then call the corresponding processing function according to the command type. The most common command is com_ Query. For most SQL statements, as long as they are not prepared, they are com_ Query, this article will only introduce this command. For other commands, please refer to the MySQL document to see the code. For command query, the main message sent from the client is SQL text, and the processing function is handlequery():

    func (cc *clientConn) handleQuery(goCtx goctx.Context, sql string) (err error) {

This function calls the specific execution logic:

    850:  rs, err := cc.ctx.Execute(goCtx, sql)

The implementation of this execute method is in server / driver_ tidb.go Middle,

    func (tc *TiDBContext) Execute(goCtx goctx.Context, sql string) (rs []ResultSet, err error) {
        rsList, err := tc.session.Execute(goCtx, sql)

The most important thing is to call tc.session.Execute , this session.Execute The implementation of session.go From then on, we will enter the SQL core layer, and the detailed implementation will be described in the following chapters.

After a series of processing, after getting the result of the SQL statement, the writeresultset method will be called to write the result back to the client:

        857:        err = cc.writeResultset(goCtx, rs[0], false, false)

Protocol layer exit

The exit is relatively simple, which is the writeresultset method mentioned above. According to the requirements of MySQL protocol, write the result (including field list and data per row) back to the client. Readers can refer to com in MySQL protocol_ Query response understands this code.

In the next few sections, we will enter the core process to see how a text SQL is handled. I will introduce all the processes first, and then use a diagram to string all the processes.

Session

The most important function in the session is execute, which will call the following modules to complete the statement execution. Note that the session environment variables, such as whether or notAutoCommit, what is the time zone.

Lexer & Yacc

These two components together constitute the parser module. When calling the parser, the text can be parsed into structured data, that is, the abstract syntax tree (AST):

    session.go 699:     return s.parser.Parse(sql, charset, collation)

In the parsing process, the text will be continuously converted into token by lexer and delivered to the parser, which is generated according to yacc syntax. According to the syntax, the token sequence sent from lexer can match which syntax rule, and finally the structured node will be output. For example, for such a statementSELECT * FROM t WHERE c > 1;, can match the rules of selectstmt, and is converted into the following data structure:

    type SelectStmt struct {
        dmlNode
        resultSetNode
    
        // SelectStmtOpts wraps around select hints and switches.
        *SelectStmtOpts
        // Distinct represents whether the select has distinct option.
        Distinct bool
        // From is the from clause of the query.
        From *TableRefsClause
        // Where is the where clause in select statement.
        Where ExprNode
        // Fields is the select expression list.
        Fields *FieldList
        // GroupBy is the group by expression list.
        GroupBy *GroupByClause
        // Having is the having condition.
        Having *HavingClause
        // OrderBy is the ordering expression list.
        OrderBy *OrderByClause
        // Limit is the limit clause.
        Limit *Limit
        // LockTp is the lock type
        LockTp SelectLockType
        // TableHints represents the level Optimizer Hint
        TableHints []*TableOptimizerHint
    }

Among them,FROM tWill be resolved toFROMField,WHERE c > 1Resolved toWhereField,*Resolved toFieldsField. All statements have enough structure to be abstracted into oneast.StmtNode, readers of this interface can read the notes and understand it. It’s just mentioned here that most of the data structures in the ast package have been implementedast.NodeInterface, this interface has aAcceptMethod, the subsequent processing of AST mainly depends on this accept method, traversing all nodes in visitor mode and making structural transformation for ast.

Develop query plan and optimization

After getting the ast, you can do various verifications, changes and optimizations. The entrance of this series of actions is here:

    session.go 805:             stmt, err := compiler.Compile(goCtx, stmtNode)

When we enter the compile function, we can see three important steps:

  • plan.Prepprocess: do some legitimacy checks and name binding;
  • plan.OptimizeTo make a query plan and optimize it is one of the core steps, which will be highlighted in the following article;
  • structureexecutor.ExecStmtStructure: this execstmt structure holds the query plan, which is the basis of subsequent execution, and is very important, especially the exec method.

Build actuator

In this process, the plan will be converted into executor, and the execution engine can execute the query plan determined before through executor. For the specific code, see ExecStmt.buildExecutor ():

    executor/adpter.go 227:  e, err := a.buildExecutor(ctx)

After the actuator is generated, it is encapsulated in arecordSetIn the structure:

        return &recordSet{
            executor:    e,
            stmt:        a,
            processinfo: pi,
            txnStartTS:  ctx.Txn().StartTS(),
        }, nil

This structure realizesast.RecordSetInterface. You can see that this interface represents the abstraction of query result set. Let’s take a look at its several methods:

    // RecordSet is an abstract result set interface to help get data from Plan.
    type RecordSet interface {
        // Fields gets result fields.
        Fields() []*ResultField
    
        // Next returns the next row, nil row means there is no more to return.
        Next(ctx context.Context) (row types.Row, err error)
    
        // NextChunk reads records into chunk.
        NextChunk(ctx context.Context, chk *chunk.Chunk) error
    
        // NewChunk creates a new chunk with initial capacity.
        NewChunk() *chunk.Chunk
    
        // SupportChunk check if the RecordSet supports Chunk structure.
        SupportChunk() bool
    
        // Close closes the underlying iterator, call Next after Close will
        // restart the iteration.
        Close() error
    }

You can see the function of this interface through comments. In short, you can call fields() to get the type of each column in the result set, next / nextchunk() to get a row or a batch of data, and close() to close the result set.

Run actuator

The execution engine of tidb runs in the volcano model. All the physical executors form a tree structure. Each layer obtains the results by calling the next / nextchunk() method of the next layer. For example, suppose the statement isSELECT c1 FROM t WHERE c2 > 1;, and the query plan selects full table scanning + filtering. Then the executor tree will be as follows:

You can see the call relationship between executors and the flow of data. So where is the top next called, that is, where is the starting point of the whole calculation? Who will drive the process? There are two places that we need to pay attention to. These two places deal with two kinds of statements respectively. The first type of statement is the select query statement, which needs to return results to the client. The executor call point of this type of statement is where the data is returned to the client:

            row, err = rs.Next(ctx)

therersIs aRecordSetInterface, calling it constantlyNext(), get more results and return them to MySQL client. The second kind of statement is insert, which does not need to return data. It only needs to execute the statement. This kind of statement also passesNextDrive execution, drive point in constructionrecordSetBefore structure:

        // If the executor doesn't return any result to the client, we execute it without delay.
        if e.Schema().Len() == 0 {
            return a.handleNoDelayExecutor(goCtx, e, ctx, pi)
        } else if proj, ok := e.(*ProjectionExec); ok && proj.calculateNoDelay {
            // Currently this is only for the "DO" statement. Take "DO 1, @a=2;" as an example:
            // the Projection has two expressions and two columns in the schema, but we should
            // not return the result of the two expressions.
            return a.handleNoDelayExecutor(goCtx, e, ctx, pi)
        }

summary

The execution framework of the whole SQL layer is described above. Here, a diagram is used to describe the whole process:

Through this article, I believe you have understood the statement execution framework in tidb, and the whole logic is relatively simple. The detailed explanation of specific modules in the framework will be given in the following chapters. The next article will use specific sentences as an example to help you understand this article.

Author: Shen gravel

Recommended Today

Python basics Chinese series tutorial · translation completed

Original: Python basics Python tutorial Protocol: CC by-nc-sa 4.0 Welcome anyone to participate and improve: a person can go very fast, but a group of people can go further. Online reading Apache CN learning resources catalog introduce Seven reasons to learn Python Why Python is great Learn Python introduction Executing Python scripts variable character string […]