A brief discussion on the history and architecture of MySQL principles and optimization (1)

MySQL is currently the most widely used database software (DBMS) among Internet companies. Starting companies as small as BAT, GOOGLE, and FACEBOOK all use MySQL as data storage in their businesses. Alibaba Cloud also provides a cloud version of MySQL - Cloud Database RDS MySQL version. This series of articles hopes to help everyone better understand MySQL, better utilize the performance of the database, and make our data storage more efficient.

History of MySQL

MySQL is an open source free software, and we can directly obtain its source code online. So far, MySQL has a history of more than 20 years. The general milestones are as follows:

● In 1996, MySQL 1.0 was released, which was only available to a small group of people. In October 1996, MySQL 3.11.1 was released (MySQL does not have a 2.x version), and initially only a binary version under Solaris was provided. A month later, a Linux version appeared. Over the next two years, MySQL was ported to various platforms in turn.

● From 1999 to 2000, MySQL AB was established in Sweden and developed the Berkeley DB engine. Since BDB supports transaction processing, MySQL began to support transaction processing from then on.

● 2001 V3.23: MyISAM engine, and Innodb engine prototype

● 2003 V4.0: New syntax features, Innodb becomes a standard component, and query_cache is added

● 2006 V5.0: Views, triggers, stored procedures and other functions were added

● 2008 V5.1: Partitioning, row replication

● 2010 V5.5: Innodb became Default engine, semi-synchronous replication

● V5.6 Innodb improvements, replication functions, etc.

● V5.7 adds new storage engines such as mariaDB

MySQL The system architecture

MySQL is not the same as the database of the same period, but adopts its own unique architecture. We can understand this structure with a famous poem by Du Mu, a poet from the Tang Dynasty:

长安回望绣成堆，山顶千门次第开。 一骑红尘妃子笑，无人知是荔枝来。

There are three roles in it:

##●● Concubine: Responsible for making requests for lychees

●

Minister: Responsible for arranging the picking of lychees, which path to take, issuing official documents, etc.

●

Courier: Responsible for delivering lychees

This corresponds to the three roles in the MySQL architecture: client, processing engine, execution engine

Using the architecture diagram to express it is like this

● Client

is equivalent to the role of the concubine: user operation The client issues requests for querying, modifying, adding, and deleting data

● Processing engine

The processing engine is equivalent to the role of a minister, responsible for parsing SQL statements and generating execution plan. In addition, it is also responsible for the following responsibilities, which we can pay attention to when optimizing:

• Responsible for managing connections and threads: using a multi-threading model, setting thread_cache_size can make use of existing threads

• Responsible for managing query cache: use ready-made results to directly cache the result set. When testing statement performance, you need to use sql_no_cache hint to shield

● Storage engine

The storage engine is equivalent to express delivery The role of the administrator is responsible for the actual storage of data as well as data reading, modification and other operations. Different storage engines will show different characteristics in actual execution. This will be introduced in detail in a later article.

To sum up, this is an overview of the MySQL architecture. I hope it will be helpful to everyone.

The above is the detailed content of A brief discussion on the history and architecture of MySQL principles and optimization (1). For more information, please follow other related articles on the PHP Chinese website!