preface

Hello, I’m Uncle Ma. I have been doing software development for nearly 6 years, and I am also an Internet veteran. As back-end development, data storage is definitely a basic skill we must master. In the process of learning and using storage services, I encountered many problems and made many detours. At the same time, in the continuous learning, frustration, summary process also learned a lot.

I remember in my first year, the project manager asked me to design a review system (that is, article reviews) from database design to code writing. No programming experience of the “weak chicken” to catch the duck on the shelf, directly engaged. I remember in the first edition review, when the project manager commented on the database design, he just said “go back and look at the three paradigms of database design”. God, NOW that I think about it, I really didn’t know anything. After working for two years, I came to an artificial intelligence company. After a modified stored procedure went online, the entire database became deadlocked, affecting many of the company’s lines of business. At that time really want to look for a roof jump, but think, there are so much less than the joy of development, how can walk away?

Most of the time, when I encounter problems, I can only Google it, and the knowledge I have learned is very fragmented. I once encountered such a problem, an SQL that can be executed in 10 milliseconds, sometimes it takes 100 milliseconds. At that time how also think impassability, until once fell to a cliff, discover a snow ape, snow ape show me a book, I look, it is that “nine Yin true classics MySQL version”. Ha ha, now I can finally figure it out. So I very hard practice skills, and finally a little success.

I know that many friends also want to understand the full picture of MySQL, in this series of courses, I have distilled the essence of nine Yin by MySQL, while doing a lot of basic knowledge supplement, can be said to be notes of technical articles.

If you need to improve your knowledge of MySQL, please take a look, but please be patient and read all the chapters. Welcome to study with me, if you find any questions or mistakes in the article, please also point out.

Here, there is a basic knowledge of the catalog, we first from the overall understanding.

  • How to execute a query statement?
  • How does an update statement execute?
  • MySQL edition: Transaction isolation and MVCC multi-version control
  • MySQL edition: Understanding indexes in depth
  • MySQL global lock and table lock
  • MySQL版 : Row locking affects MySQL performance
  • MySQL版 : Transaction isolation

After learning the foundation, there will be practice, the follow-up content is more wonderful.

Thank you for sharing MySQL Practice 45 lecture

MySQL architecture

The best way to learn “new” knowledge is from the whole to the minute. This chapter is about how a query SQL is executed. It doesn’t seem to have much to do with the MySQL architecture, but it doesn’t. See the MySQL architecture diagram below:Let’s make it familiar here, not necessarily the meaning of each component. We’re going to take MySQL apart into a number of “parts”, and hopefully you’ll gain a deeper understanding of MySQL and be able to get to the heart of the problem when you encounter it. Here, we simplify the logical architecture of MySQL, which can be roughly divided into the following two layers:

  • MySQL Server layer:

The MySQL Server layer includes connectors, query caches, analyzers, optimizers, actuators, and more. This layer contains most of mysql’s core services, including built-in functions (date, time, math), stored procedures, triggers, views, etc.

  • Engine layer:

The engine layer is responsible for data storage and extraction. Its architecture mode is plug-in, supporting InnoDB, MyISAM, Memory and other storage engines. Mysql uses InnoDB as the default storage engine after version 5.5.

Create table student(id int(11) auto_increment, name vARCHar (255) not null, age int(11) not null, address varchar(255) , primary key(id), index idx_name(name) )engine = innoDB default charset=utf8;Copy the code

Here is my logical architecture for MySQL. You can see how a query SQL runs in the MySQL component.

The connector

Responsible for connecting from clients, obtaining user rights, maintaining and managing connections. After a user successfully establishes a connection, the permission of the connection will not be affected even if the administrator changes the permission.

  • Long connection: After a connection is established, the same connection is used all the time if the client has requests. If the client does not send a request for a long time, the connector automatically disconnects, controlled by the wait_time parameter, which defaults to eight hours. Sometimes we find that mysql memory increases because the memory temporarily used by mysql during execution is managed within the connection and is only released when the connection is disconnected. MySQL after version 5.7 can use mysql_reset_connection to re-initialize resources without the need to re-establish connections or authenticate permissions.
  • Short link: a connection is established for each query and destroyed after the query. Frequent connections and disconnections consume a lot of performance.

The query cache

After the connection is created, the query logic comes to the query cache. Previously executed queries are cached directly in memory in K and V formats. The query statement is the key, the query result is the value, and if your current query statement is in the cache, the value in the cache is returned directly. If it is not in the query cache, proceed with the following process.

MySQL 8.0 has recently removed the entire query cache. Why?

The update of the query cache is very fast, and whenever there is an update operation on the table, the query cache will be cleared. Unless the business table is long-term and does not need to be updated, query caching can be used. So the data can be stored with trouble and wiped out before it can be used.

analyzer

  • Lexical analysis: The parser performs lexical analysis on the entered SQL. MySQL needs to identify what the strings are and what they mean.

    Select * from ‘select’; select * from ‘select’; select * from ‘select’

  • Syntax analysis: After the lexical analysis, MySQL will do the syntax analysis. According to the result of the lexical analysis, the parser will determine whether the SQL you entered meets the MySQL syntax.

SELEC * FROM retail_order_redundancy WHERE id = 12 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELEC * FROM Retail_order_redundancy WHERE ID = 12' at line 1 We may often see the above error (select writing error), which is blown by the lexical analyzer.Copy the code

The optimizer

The optimizer determines which index to use when there are multiple indexes in a table, or the order in which tables are joined when multiple tables are joined. Such as:

Select * from T1 join t2 on t1.id = t2.id where t1.c = 10 and t2.d = 20 We can first obtain the records of C =10 from t1 table and then associate T2 with ID to determine whether T2.d is equal to 20. Plan 2: We can also obtain the records of D value 20 from T2 table and then associate T1 to determine whether C of T1 is 10.Copy the code

The results are the same, but the performance can be very different, and the optimizer’s role is to choose which one to use. How the optimizer chooses the index, and whether it might get it wrong, will be explained in more detail in a later article.

actuator

SQL goes through the parser to know what it wants, and the optimizer knows how to do it. Next comes the executor, which executes the SQL statement.

Before executing, determine whether the current user has query permission on the table. If not, a permission error will be returned. If granted, the executor defines the engine based on the table to use the interface provided by the engine.

For example, selec * from T where c = 10(if the c field has no index), the execution flow would look like thisCopy the code
  • Call innoDB engine interface to get the first row of the table and determine if ID is equal to 10. If so, add it to the result set. If not, skip it.
  • Call the engine to fetch the “next row” interface and repeat the same logic until the last row of the table is fetched.
  • The executor returns all the results of the above traversal as a record set to the client.

Assuming that the C field has an index, the procedure is as follows:

  • Call the engine interface “meets the condition of the first row”, if the data is added to the result set. If not, an empty result set is returned.
  • If the value is reached in the first step, the interface is looped to the next line that meets the condition
  • The executor returns all the results of the above traversal as a record set to the client.

At this point, the entire execution is complete. If there is no index, the whole table needs to be traversed. If there is an index, the number in the index tree needs to be fetch. If there is no data in the first step of “the first row that meets the condition”, the whole query ends.

summary

In this chapter, we understand the overall architecture design of MySQL, including the server layer and the engine layer. Server layer contains connector, query cache, analyzer, optimizer and executor, etc. MySQL engine layer has many different implementations. InnoDB engine is used by default after MySQL5.5.

Now you should understand a query execution logic, first of all, the client and connector connection is established, creating a connection, query request first query in the query cache, if the query to return as a result, the query is less than the analyzer to know this request to do after analyzer analysis, analyzer analysis is completed, the optimizer to optimize query logic, At this point MySQL is close to knowing what to do and how to do it. The executor comes into play, calls the engine layer interface, and returns the result set of the query.

The problem

Select * from T where k = 1; select * from T where k = 1; Unknown column ‘k’ in ‘where clause’ Leave it in the comments below and answer next time.