Before starting to summarize MySQL formally, first introduce to you what the meaning of “broken wall” is. Long ago, my boss once said that “The Internet had no industry barriers, but by making some professional terms, artificially created a lot of industry barriers out of”, I deeply believe that.

Computers were zeros and ones in nature, and then layers of encapsulation and abstraction built the Internet world we see today.

I do not deny that computer knowledge is very difficult, but the knowledge I have access to so far has not reached that level. I think the core knowledge, which can be called industry barrier, accounts for less than 0.01% of the total knowledge, and most people will never encounter these problems in their life.

What remains is an artificial “industry barrier” that can be mastered over time.

Breaking the wall means to break these man-made “industry barriers”, so that we technical personnel like dragons.

For the first topic in the Wall Breaking series, I chose MySQL, the data storage cornerstone of the Internet.

This topic will summarize the core contents of MySQL, including transactions, indexes, SQL performance optimization, database and table partitioning, and master/slave replication. See the mind map below for details:

What is the MySQL

MySQL is a free, open source relational database management software, widely used in the Internet industry.

MySQL architecture

The plug-in storage engine architecture of MySQL has always been the highlight of its characteristics. Its flexible processing mode, highly customizable and fully open implementation has been recognized by many advanced users. The following is an architecture diagram in the official document:

  • Connection layer: This layer provides functions such as authentication and connection processing.

    • On this layer, the concept of thread pools is introduced to provide threads for client requests that pass security authentication.
    • SSL – based secure links can also be implemented at this layer.
    • This layer assigns permissions to authenticated client requests.

  • Service layer: This layer implements many core functions such as query resolution, analysis, optimization, caching, and implementation of built-in functions, as well as all cross-storage engine functions such as triggers, stored procedures, views, and so on.

  • Engine layer: Storage engine is responsible for storage and extraction of data in MySQL. Server communicates with storage engine through API. The features of plug-in storage engine architecture in MySQL enable us to select storage engine according to our actual needs.

  • Storage layer: Stores data on file systems.

Often meet try:

What is the query flow of MySQL? How does an SQL statement execute in MySQL? This section describes the architecture of MySQL

  1. The customer initiates a request.
  2. At the connection layer for permission verification, thread allocation.
  3. Query cache (if cache exists, return directly; if cache does not exist, perform subsequent operations)
  4. Profiler (performs lexical and parsing operations on SQL)
  5. Optimizer (selects the best execution scheme method for SQL optimizations performed)
  6. Executor (execution depends on whether the user has execution permission before using the interface provided by the engine)
  7. Go to the engine layer to retrieve data returns (query results are cached if query caching is enabled)

MySQL Storage Engine

MySQL uses a plug-in storage engine architecture. You can choose different storage engines as required.

InnoDB storage engine is widely used on the Internet now. The common interview questions are InnoDB storage engine and MyISAM storage comparison, for example:

What is the difference between Innodb and MyISAM?

The answer can be given from the following angles:

  • Transactions: InnoDB supports transactions, MyISAM does not
  • Locking: InnoDB supports row-level locking, MyISAM only supports table-level locking
  • Foreign keys: InnoDB supports foreign keys
  • InnoDB supports online hot backup
  • MyISAM supports spatial indexing

summary

MySQL is the relational database management software that is used by the mainstream of Internet factories. It is also developed around it in daily work, so it is almost an interview question.

This article is the first in the first feature in the Broken Wall series.

In this article, I first introduced the content I will write next (the first topic MySQL), followed by two aspects of the technical principle and questions introduced MySQL architecture and MySQL storage engine.

The resources

  • The official documentation
  • MySQL 30 thousand words essence summary

The article will be updated every Saturday. You can search “Ancient Tales” on wechat to read it first.