Now I'm going to look at the cluster of TiDB servers also known as the SQL layer.

Thanks to this layer TiDB understands SQL statements.

One of the most important features of SQL layer is managing the mapping between the table row in relational database domain and the key value pair in KV domain.

Here is how the mapping works.

TiDB assigns a table ID to the table.

The table ID is an integer that is unique across the cluster.

TiDB also assigns a row ID to each row.

The row ID is also an integer being unique within the table.

In the slide, on the left side, there are three rows with four columns.

The first column is the primary key of the table.

The KV domain is on the right side.

The key is shown in red.

The value is enclosed in the square brackets.

Additionally, for the row ID, TiDB server has made a small optimization, which is, if the primary key column for your table is integer data type, then SQL layer will use the primary key as the row ID directly to compose the key for the KV record.

This is exactly the case shown on a slide.

On the other hand, if the primary key column is a non integer data type, the SQL layer will generate and encode a row ID for the key in KV record.

We will cover more details related to the row ID in the clustered index section, in another course named TiDB Specific features and transaction control.

The row ID can be used effectively in a unique get or point get query.

The table prefix can be used in full table scan.

From TiKV map point of view, the full table scan in relational domain is an actually a render scan in KV demand.

Now let's do a deep dive into a single TiKV server to see what modules the TiDB server has on it and what are the related functions.

Suppose the client issues an SQL statement over the network.

The front end handles the request is the protocol layer.

The protocol layer actually makes the TiDB server a network server.

Then you reach the session context stage after you have been authenticated.

After you get a session context, the request enters the passing stage.

Parser is going to treat your SQL statement text as a logical tree structure, and then analyze your intention, such as what database objects you would like to access and what operations you would like to do on these database objects.

After that it will be sent to a logical optimizer which is a rule-based optimizer.

The logical optimizer may rewrite your SQL statement into a good shape following a series of rules.

This can help the following step to generate a better execution plan.

The output is the logical excursion plan.

After that it is sent to the physical optimizer which is a cost based optimizer.

The physical optimizer is aware of some other important information, such as the statistics of the objects your SQL statement needs directly or indirectly.

For example it might include how many rows the table has, what is the average length of each row, and the information about the indexes.

Statistics like this can help the physical optimization to get a better exclusion plan, which the logical optimization cannot.

Now we have the physical execution plan it can be passed into the local and the distributed executor to execute.

Another thing, we should keep in mind, is that a client can only connect to one TiDB server instance at the same time, which means the relationship between client session and TiDB server instance is one to one.

The local execution means that the task is executed on the TiDB server instance where the session is connected to.

Distributed execution means the distributed SQL instructions will be sent to TiKV servers for executions.

We already know that there can be a lot of TiKV servers in the backend.

So multiple TiKV servers may receive the distributed SQL instructions and leads parallel execution.

We also have a course available called TiDB Performance Tuning which covers the topics including SQL tuning, query optimizer and the distributed SQL.

If you are interested in the performance tuning topics on TiDB, I highly recommend your enrol this course.

One question you might have is that since the client session can only connect to one TiDB server at a time, what if this TiDB node is down?

In this case, what should the client application do?

In fact the answer is very simple, since the TiDB server is stateless, meaning no business data persisted on TiDB server node, when it goes down you can just reconnect and recreate your session to a healthy TiDB server.

The client session should be able to handle any unfinished transaction before and after the reconnecting.

We do have a course talking able transaction handling.

Next I'm going to show you an example.

I will kill my TiDB server instance in the playground sandbox, and see what happens.

Two simple client programs are running at the same time.

One programs keeps inserting new planets into planets table and the other one is querying on the same table.

Both application implements the basic reconnect and a simple transaction recommit mechanism in case of connection loss.

Let's check the status of my playground cluster first.

Now we have the PID of each instance, my target is the TiDB server instance right now.

Let's spin up our little client programs.

The first one is the planet's creator with random planet names.

Then, let's query against the planet table.

You can see the new planets are being discovered.

Now two tiny programs are running.

Please note both programs are aware of three TiDB server instances, they are designed to connect to any TiDB service in case of the connection failure.

They are written in Python and very straightforward.

You can go through the code if you like Let me kill one TiDB instance.

Both programs encountered exceptions and they did not give up easily.

Within a short time, they can reconnect to another TiDB server and continue their tasks.

Let me kill one more TiDB instance.

As you can see the failover happens again Let me kill the last TiDB instance.

There will be no TiDB server after this kill operation.

As I only have three TiDB server instance.

You can see now my programs have no TiDB server to talk to.

My business is literally paused.

What can I do now?

Do I lost any data?

No, because the TiDB server is stateless.

What I should do is add TiDB servers back to the cluster to fit my business requirements.

For my dummy business here, actually one TiDB server is enough.

So I am going to add one TiDB server instance.

This operation is actually a scaling out operation.

I can do that with very intuitive command line.

Let me run it now.

Everything is back to normal.

I lost three old TiDB instances, and then one new TiDB instance joined the cluster.

No data is lost in this simple demo.

I added TiDB instance back manually.

In production, you don't have to do this manually.

You have multiple options to do it in an automatic way.

If you are in TiDB Cloud, it will be even more convenient.