Shulou(Shulou.com)06/01 Report--

After using the MySQL,sql sentence for such a long time, I believe that I am already familiar with it, so I try to understand its implementation principle. Here are the main points of my learning process.

As long as it is the BBUA S architecture, there will be clients and servers, and mysql is no exception.

First of all, the client sends out a request, which is a query request (Select), and the object it requests is the server. How does the server handle this query function?

Logical layering

First of all, we can think of the server as a large container with a four-tier structure. When a request comes, these four layers will be executed and will be returned to us after one execution.

First layer: connection layer

In other words, our client sends a Select directly to the connection layer for processing, and its function is to provide services to connect with the client.

The connection layer only establishes a connection with the client and gets our request (Select), but it does not process it itself, because it only does the connection, so who will handle it? Keep going down.

The second layer: service layer

The service layer can do two things:

1. Provide users with a variety of interfaces that can be used

For example, the query just now is that our common CRUD operations are all here. The connection layer gets the Select and gives it directly to the service layer. In addition to providing the interface to the user, it also provides something claimed to be the Sql optimizer.

2.Sql Optimizer (MySQL QUery Optimier)

When we write the Sql statement for execution, the optimizer will think that the performance of the sql statement I wrote is not good enough. At this time, the optimizer will write a sql statement equivalent to the execution result that I wrote to replace it. This equivalent writing method optimizes your sql through this optimizer, because it thinks that the performance of your writing is too low. So it optimizes the sql you wrote, and that's what the optimizer does.

Obviously, the optimizer can optimize sql statements, which can have some benefits to performance, but it has disadvantages! For example:

"when you optimize yourself, it is a, but the actual execution is not a, because the optimizer will think that your optimization is not good enough, and as a result, it turns an into b, that is to say, I clearly write a, but the program executes b."

This will cause confusion to our development process because it has been optimized. What you wrote is no longer the same as before.

The third layer: the engine layer

It provides a variety of ways to store data, including lnnoDB and MylSAM.

Key differences:

LnnoDB [Mysql default]: when it is designed, it is thing first [suitable for high concurrency operation] principle: because it is a row lock, I have to lock every piece of data, too many locks, the performance is degraded, although the performance is degraded, but I am suitable for high concurrency, it is not easy to make mistakes.

MylSAM: performance first principle: because it is a table lock, it is unaffected for ten pieces of data in the table, and it is finished for ten locks at once, so the performance is fast.

Performance priority is easy to understand. For example, 10,000 pieces of data will be processed faster and more efficient.

The thing is to prevent some concurrent operations, too much concurrency may make mistakes, so it is suitable for high concurrent operations.

So when working on a project, when setting up a database, if performance is priority, choose MylSAM engine; if it is high concurrency, choose lnnoDB engine; how to change engine I will talk about below

Layer 4: storage layer

It's easy to understand that the final data is stored in the storage layer.

After four layers, let's clear our mind.

First, the client sends out a Select operation-> the connection layer receives it and gives it to the service layer-> the service layer optimizes your query and gives the optimization result to the engine layer-- > Select the engine of the current database. After selecting the engine, the engine gives the final data to the storage layer-> storage layer, and uses the storage layer to store the data.

View engine

Show engines; views all the engines supported by Mysql

Execution result:

Yes is supported. When the InnoDB support option is DEFAULT (default), it means that when we create a database, the default engine is InnoDB.

View the engine used by the current database

Show variables like'% storage_engine%'

Execution result:

Obviously, we're using InnoDB right now.

Specify the engine of the database object

I'm going to create a table that I don't use InnoDB, I use MylSAM, because I want it to be performance first, so I'm going to create the table using the mysql command line.

1. Specify database directive: use database name

two。 Creates a simple table in the specified database

Create table tb (id int (4), name varchar (5), primary key (id)) ENGINE=MyISAM

Execution result:

Indicates that the creation was successful, and that the engine of the table is specified by us manually, not by default.

The above is all the relevant MySQL implementation principle, logical layering, change database processing engine knowledge point content, thank you for your reading and support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.