preface
MySQL architecture, how does this database come together
MySQL is a typical C/S architecture application. The MySQL Server provides database services and completes Client requests and operations. The Client is responsible for connecting to the Server. It is not easy for beginners to distinguish between MySQL Server and Client, because when we install MySQL, both Server and Client are available by default. We connect to and log in to the MySQL service from the command line. This is actually the service provided by the Client. What differentiates MySQL from other relational databases is its flexibility and the ability to provide different types of storage engines in the form of plug-ins. The processing of MySQL requests varies according to different storage engines.
1.MySQL storage engine
Here I mainly introduce the history of MySQL storage engine, as for the characteristics of each storage engine, Songko will be in the later articles and you will be introduced in detail.
MySQL was designed from the very beginning to be pluggable, allowing companies and individuals to customize their own storage engines to suit their own needs (of course, the average company or individual does not have this capability). MyISAM, a storage engine developed by MySQL, is widely used. MyISAM supports table locks rather than row locks, so it is less efficient in handling high concurrent write operations. In addition, MyISAM does not support foreign keys (although foreign keys are rarely used in actual projects).
MyISAM may seem a bit crude, but that doesn’t affect the popularity of MySQL, which leads to InnoDB, another well-known storage engine in MySQL.
The InnoDB storage engine, developed by Innobase Oy, a company based in Helsinki, Finland, is even older than MySQL. Some of you may decide that plugins are older than their hosts.
When InnoDB was first developed, it was developed as a complete database, so it has complete functions. Once developed, the founders tried to sell the database, but failed to find a buyer.
When MySQL2.0 came out, the pluggable storage engine caught the eye of Heikki Tuuri, founder of Innobase Oy. After talking to MySQL, Decided to introduce InnoDB as a storage engine to MySQL (which is why InnoDB is older than MySQL), MySQL supports InnoDB, but is in fact the main driver of its own MyISAM.
But InnoDB was so good that it finally caught the eye of Oracle in 2006 and snapped InnoDB up.
MySQL, which launched MyISAM, floundered, eventually being bought by Sun for $1 billion in 2008, a move that cemented Sun’s position as a leader in open source, but which has long been a cash-poor company. Sun itself was eventually acquired by Oracle in 2009.
After Oracle acquired Sun, InnoDB and MySQL became Oracle products, which made integration easier, and InnoDB gradually became the default storage engine for MySQL in later releases. In the latest MySQL8, InnoDB is also used as the storage engine for metadata tables.
InnoDB storage engine has the following features:
Support for transactions Support for 4 levels of transaction isolation Support for multiple versions of read support for row-level locks read and write blocking depending on the transaction isolation level Support for caching, caching both indexes and data entire tables and primary keys are stored in a Cluster as a balanced tree and of course InnoDB is not necessarily good, In actual development, the choice between InnoDB and MyISAM will depend on the specific scenario.
MyIASM (the default storage engine in MySQL databases prior to 5.5) features:
MyISAM does not provide support for database transactions. Row-level locks and foreign keys are not supported. Because of 2, when an INSERT or UPDATE statement is executed, the entire table needs to be locked. When you run the SELECT COUNT(*) FROM TABLE command, you can read related values directly without scanning all tables. Differences between the two:
MyISAM is non-transactional-safe while InnoDB is transactional-safe. MyISAM locks are table-level in granularity, while InnoDB supports row-level locks. MyISAM supports full-text indexes, which InnoDB did not support before MySQL5.6. Since after MySQL5.6 support FULLTEXT indexing (dev.mysql.com/doc/refman/… Comparison of usage scenarios:
MyISAM should be used if you want to perform a lot of SELECT operations InnoDB should be used if you want to perform a lot of INSERT and update operations Large data sets tend to be the InnoDB engine because it supports transaction processing and failover. The size of the database determines how long it takes to recover from a failure. InnoDB can use transaction logs for data recovery, which is faster. Primary key queries are also fairly fast in The InnoDB engine, but it is important to note that if the primary key is too long it can also cause performance problems. InnoDB is used more by Internet companies.
This is a brief introduction to the MySQL storage engine. Songko will write a special article to introduce the features of each storage engine in detail. Welcome to discuss it together.
2. MySQL architecture
Let’s take a look at the software architecture of MySQL
Client (application layer) Service layer Storage engine layer let’s look at them separately.
2.1 the client
Basically, all C/S architecture programs have a client layer, which mainly contains the following three aspects:
Connection processing: When a client sends a connection request to the Server, the MySQL Server allocates a thread from the thread pool to connect to the client. Subsequent requests from the client are allocated to this thread. To improve performance, MySQL Server provides thread pools that reduce the time it takes to create and release threads. Major programming languages can use their own apis to connect to MySQL. User authentication: When a client initiates a connection request to the MySQL Server, the MySQL Server authenticates the user based on the user name, client host ADDRESS, and user password. User authentication: After a customer connects to the MySQL Server, the MySQL Server determines the operations that can be performed by the user based on the user permissions.
2.2 the service layer
There are six main aspects of MySQL service layer, let’s take a look at them one by one.
2.2.1 System management and control tools
Database backup and restoration Database security management, such as user and rights management database replication management database cluster management database partition, database, table management database metadata management
2.2.2 the connection pool
As mentioned earlier, connection pooling is responsible for storing and managing client connections to the database, with one thread managing one connection.
2.2.3 SQL interface
The SQL interface is used to accept the various SQL commands sent by the client and return the query results required by the user.
Such as:
DDL DML stored procedure view triggers and so on are handled here.
2.2.4 parser
The function of the parser is mainly to parse query statements. The SQL requested by the client is parsed to generate a “parse tree”. Then, the parse tree is checked according to the syntax rules of MySQL.
After the syntax check passes, the parser queries the cache, and if there is a statement in the cache, returns the result without further optimization.
2.2.5 Query optimizer
As the name suggests, this step optimizes the query after the parser has parsed and checked the SQL syntax. The main optimization methods include selecting the appropriate index and reading the data.
2.2.6 cache
Global and engine-specific caches are included to improve query efficiency. If there is a matching query result in the query cache, the query statement can fetch data from the cache without parsing and execution. This caching mechanism consists of a series of small caches, such as table cache, record cache, key cache, permission cache, and so on.
2.3 Storage Engine Layer
Storage engine is responsible for storage and extraction of data in MySQL and interaction with the underlying system files.
The MySQL storage engine is pluggable. The query execution engine in the server communicates with the storage engine through interfaces that mask the differences between storage engines. There are a variety of storage engines, each with its own characteristics, the most common being MyISAM and InnoDB.
It is important to note that the MySQL storage engine is for tables, not databases; in other words, it is possible to use multiple storage engines simultaneously within the same database (technically possible, but not recommended).
3. Summary
For example, the specific working mechanism of the storage engine is often transparent to developers. We don’t need to know how InnoDB works, we just need to write our own SQL (some storage engines support different SQL, this is another topic). This is much friendlier to the developer.
Ok, today and small partners first pull so much ~
