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

MySQL logical architecture

If you can build an architectural diagram of how the components of MySQL work together in mind, it will help to gain a deeper understanding of the MySQL server. The following figure shows the logical architecture diagram of MySQL.

MySQL logical architecture, from: high performance MySQL

The MySQL logic architecture is divided into three layers, and the top layer is the client layer, which is not unique to MySQL. Functions such as connection processing, authorization authentication, security and so on are handled in this layer.

Most of MySQL's core services are in the middle layer, including query parsing, analysis, optimization, caching, built-in functions (such as time, mathematics, encryption, etc.). All cross-storage engine functions are also implemented at this layer: stored procedures, triggers, views, and so on.

The lowest layer is the storage engine, which is responsible for data storage and extraction in MySQL. Similar to file systems under Linux, each storage engine has its advantages and disadvantages. The middle service layer communicates with the storage engine through API, and these API interfaces shield the differences between different storage engines.

MySQL uses a cost-based optimizer, which attempts to predict the cost of a query when it uses some kind of execution plan, and selects the one with the lowest cost. In MySQL, you can get the cost of calculating the current query by querying the value of the last_query_cost for the current session.

Sample code

Mysql > select * from t_message limit 10. Omit result set mysql > show status like 'last_query_cost';+-+-+ | Variable_name | Value | +-+-+ | Last_query_cost | 6391.799000 | +-+-+

The results in the example indicate that the optimizer believes that a random search of about 6391 data pages is needed to complete the above query. This result is calculated from a number of columns of statistics, including the number of pages per table or index, the cardinality of the index, the length of the index and data rows, the distribution of the index, and so on.

There are many reasons why MySQL chooses the wrong execution plan, such as inaccurate statistics, not considering operation costs beyond its control (user-defined functions, stored procedures), MySQL's view of the best is not what we thought (we want the execution time to be as short as possible, but MySQL values choose what it considers to be low cost, but small cost does not mean short execution time), and so on.

Here the value of last_query_cost is the sum of the costs of io_cost and cpu_cost, and it is usually a common indicator for us to evaluate the execution efficiency of a query.

(1) it is used as a basis for comparing the cost between queries.

(2) it can only detect relatively simple query overhead, but it can not be tested for queries including subqueries and union.

(3) when we execute a query, MySQL will automatically generate an execution plan, that is, query plan, and there are usually many different ways to implement it, it will choose the lowest one, and the cost value is the lowest one.

(4) it is very useful for comparing our expenses, especially when we have several query options.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your 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.