Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "the implementation process of MySQL Infrastructure". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The architecture of MySQL is generally divided into two layers: Server layer and storage engine layer.

Server layer: connector, query cache, analyzer, optimizer, etc., covering most of the core service functions of MySQL, level 1 all built-in functions (such as date, time, mathematics and encryption functions, etc.), all boast storage functions are implemented in this layer, such as: memory, trigger, view, etc.

Storage engine is responsible for data storage and extraction: MySql5.5.5 versions such as innoDB,MyISAM,Memory start to default to InnoDB.

Division of labor at all levels:

1. Connectors: as the name implies, connectors are responsible for establishing connections with clients, obtaining permissions, maintaining and managing connections

You can see it in the show processlist command. The display of the Command column as Sleep indicates that the connection is an idle link.

two。 Query cache: after the connection is established, you can execute the select statement. Execute the logic step 2 to query the cache.

Advantages: improve query efficiency, suitable for table data not often updated.

Disadvantages: a table has the opportunity to update and empty the cache, and the hit rate will be very low.

If the parameter query_cache_type is set to DEMAND, the default SQL statement is not suitable for query caching. The query cache will only be as follows when specified:

Select SQL_CACHE * FROM T WHERE ID = 10

3. Parser: if the cache is not hit, the SQL statement is parsed to generate a parsing tree.

4. After going through the analyzer, MySQL knows what you're going to do. It has to be processed by the optimizer before execution, including deciding which index to use when there are multiple indexes in the table, and determining the join order of each table when a statement has multiple table associations.

For example:

Mysql > select * from T1 join T2 using (ID) where t1.c=10 and t2.d=20

It is possible to first take the ID value of the record of clocked 10 from table T1, and then associate it to table T2 according to the ID value, and then judge whether the value of d in T2 is equal to 20.

You can also first take the ID value of the record of daddy 20 from table T2, then associate it to T1 according to the ID value, and then judge whether the value of c in T1 is equal to 10.

After a careful analysis of the selection of the index

5. Executor: MySql knows what you want to do through the parser and what to do through the optimizer, so it enters the server phase and starts executing statements.

Such as the execution of the following statement:

Mysql > select * from T where ID=10

1)。 The executor invokes the interface provided by this engine based on the engine defined in the table. For example, the table provided in our sample sentence without an index will:

Call the InnoDB engine interface to fetch the first row of the table to determine whether the ID is 10, skip it if not, and store the row in the result set if it is

2)。 Call the engine interface to take the "next row", repeat the same judgment logic, and guide the last row of the table.

3)。 The executor returns the recordset of all the conditional rows satisfied in the above traversal process to the client as a result.

At this point, the execution of the statement is complete.

For indexed tables, the same logic is performed. The first call is to * * the interface "the first line that meets the condition", and then cycle through the "next line that meets the condition" interface. * * these APIs are defined in storage hospitality.

* * rows_examined * *: indicates the number of rows scanned by the statement, which is accumulated each time the executor calls the engine to get the data rows.

In some scenarios, the executor calls once and scans multiple lines inside the engine, so the number of rows scanned by the engine is not exactly the same as that of rows_examined (the number of calls may be less than the number of lines scanned).

This is the end of the content of "execution process of MySQL Infrastructure". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.