MySQL architecture



From the figure, you can see that the top layer of MySQL is the connection component. The following servers are composed of connection pools, administrative services and tools components, SQL interfaces, query parsers, query optimizers, caches, storage engines, and file systems.

1. Connection pool management, buffering user connections, thread processing and other requirements that need to be cached. The purpose of connection pooling is to cache these connections so that the established connections can be used next time to improve server performance.

Management services and tools Components System management and control tools, such as backup and recovery, Mysql replication, cluster, and so on.

3. The SQL interface accepts the USER’s SQL command and returns the result that the user needs to query.

SQL commands passed to the parser are validated and parsed by the parser (permissions, syntax structures). The parser is a long script implemented by Lex and YACC.

  • The SQL statement is decomposed into a data structure and passed to the following steps, on which the SQL statement is passed and processed
  • If an error is encountered in the decomposition composition, then the SQL statement is unreasonable

The SQL statement uses the query optimizer to optimize the query before it is queried. It uses the select-project-join strategy to query.

Select uid,name from user where gender = 1;

  • The SELECT query selects the table based on the WHERE statement, rather than querying all the tables and then age filtering
  • The select query projects attributes based on ID and name, rather than fetching all attributes and filtering them later
  • Join the two query criteria to generate the final query result

6. Cache If the query cache has a matching query result, the query statement can directly fetch data from the query cache

Plug-in Storage Engine A storage engine is simply a way of managing operational data (storing it, updating it, querying it, etc.). Because the storage of data in a relational database is in the form of tables, a storage engine can also be called a table type (that is, the type that stores and operates on this table)

MySQL storage engine

For MySQL 5.5 and later, the default storage engine is InnoDB. Prior to version 5.5, the default storage engine for MySQL was MyISAM.

What is MySQL storage engine?

MySQL belongs to a relational database, and the storage of a relational database is in the form of tables, the creation of tables, data storage, retrieval, update and so on are completed by the MySQL storage engine, which is also the MySQL storage engine plays an important role in MySQL. Storage engines can be summarized as
The different organization of data files on diskAs readers of SQL Server and Oracle may well know, there is only one storage engine for both databases, while MySQL has a wide variety of storage engines, such as MyISAM, InnoDB and Memory. Multiple storage engines are due to the open source nature of MySQL.

What is the function of MySQL storage engine?

MySQL storage engine plays an important role in MySQL. Its relatively important role can be summarized as follows:

  • Manage table creation, data retrieval, index creation, etc
  • You can customize storage engines.

MySQL storage engine types

Three storage engines are commonly used:

(1) the InnoDB

The default storage engine in MySQL 5.5 and later has the following advantages:

  • Disaster recovery is good
  • Support transactions
  • Use row-level locking
  • Foreign key association is supported
  • Support hot backup
  • For tables in InnoDB engine, the physical organization of data is Cluster Table, primary key index and data are together, physical distribution of data according to the primary key order to achieve buffer management, not only can buffer index and data buffer, and will automatically create hash index to speed up data acquisition

(2) the MyISAM

The storage engine manages non-transactional tables; Use table-level locking; Provides high-speed storage and retrieval; Can cooperate with lock, operating system copy backup, migration; Support full-text search; Data is stored compact, resulting in smaller indexes and faster full table scan performance.

(3) the MEMORY

Provides in-memory tables, formerly known as heaps, that do not support transactions and foreign keys. It processes all data in RAM for faster access than storing it on disk. Used to quickly find references and other identical data.

Storage engines not commonly used:

(4) the MERGE

Grouping multiple similar MyISAM tables into a single table can handle non-transactional tables, which are included by default.

(5) PERFORMANCE_SCHEMA

The engine is mainly used to collect database server performance parameters. This engine provides the following functions: provides detailed information about the process wait, including locks, mutexes, and files; The historical event summary information is saved to provide detailed judgment for MySQL server performance. It is easy to add and remove monitoring event points, and you can change the monitoring CYCLE of the mysql server at will, for example (CYCLE, MICROSECOND). MySQL user cannot create tables with storage engine PERFORMANCE_SCHEMA.

(6) ARCHIVE

After archiving, many advanced functions are no longer supported, only the most basic insert and query functions are supported. Before MySQL 5.5, Archive did not support indexes, but since MySQL 5.5, indexes have been supported. Archive has a good compression mechanism. It uses the Zlib compression library to compress records in real time when they are requested, so it is often used as a repository.

(7) the CSV

Storage engines of this type do not support indexes, that is, tables using this type do not have primary key columns. Also, null fields in the table are not allowed. CSV encoding conversion needs special care.

(8) BLACKHOLE

The storage engine supports transactions and supports MVCC row-level locking, where any data written to the engine table disappears and is mainly used for logging or synchronous archiving relay storage. The storage engine is not suitable for use unless there is a specific purpose.

(9) FEDERATED

The storage engine can be combined with different Mysql servers to logically form a complete database. This storage engine is ideal for database distributed applications. The data is stored in a remote database; no data is stored locally.

MySQL storage engine selection



Third, the paradigm design of database

1. There are six paradigms for relational databases:

First normal form (1NF), second normal form (2NF), third normal form (3NF), Badsco normal Form (BCNF), fourth normal form (4NF) and fifth normal form (5NF). The paradigm that meets the minimum requirements is the first paradigm (1NF). Those that meet more requirements on top of the first normal form are called the second normal form (2NF), and so on. In general, a database only needs to satisfy the third normal form (3NF).

(1) First paradigm

1NF refers to the atomicity of attributes, which requires that attributes have atomicity and cannot be decomposed again.

The first paradigm is the most basic paradigm. A database table satisfies the first normal form if all field values in the table are non-decomposable atomic values. Each column of a database table is an indivisible atomic data item, not a collection, array, record and other non-atomic data item. In short, the first normal form is the domain without repetition.

(2) Second normal form

2NF is the uniqueness of records, requiring records to have a unique identity, that is, the uniqueness of entities, that is, there is no partial dependence;

To satisfy the second normal form, one must first satisfy the first normal form. The second normal form requires that every column in a database table is associated with a primary key, not just a part of the primary key (primarily for union primary keys). That is to say, in a database table, a table can only save one kind of data, can not save multiple data in the same database table.

(3) Third normal form

3NF is the redundancy of fields, requiring that no field can be derived from other fields. It requires that fields have no redundancy, that is, there is no transfer dependency.

The first is 2NF. In addition, non-primary key columns must depend directly on the primary key; there can be no transitive dependencies. That is, non-primary key column A depends on non-primary key column B, and non-primary key column B depends on primary key. In short, the third normal Form (3NF) requires that one relationship does not contain non-primary keyword information that is already contained in other relationships.

2. Advantages and disadvantages of paradigms

Advantages: The paradigm avoids data redundancy, reduces database space, and reduces the hassle of maintaining data integrity.

Disadvantages: Tables designed according to the specification of the paradigm, the higher the level of the paradigm design more tables.

For example, there may only be one table designed in the first normal form, and two or more tables may come out when the table is designed in accordance with the second normal form. If the table is designed in accordance with the third normal form or higher normal form, there will be more tables than the second normal form. The more the number of tables, when we go to query some data, we must go to multiple tables to query data, so that the query time is much higher than the time used in a table query. That is, the higher the paradigm we use, the less performance we can perform with the data. Therefore, when we use the paradigm to design the table, we should weigh whether to use the higher paradigm to design the table according to the specific requirements.

3. Anti-paradigm

The name implies that, contrary to what the paradigm requires, in the anti-paradigm design pattern, we can allow for a modest redundancy of data, with which the time to retrieve operational data is reduced. In other words, space is traded for time, data is redundant in multiple tables, and association between tables can be reduced or avoided when querying.

4. The pros and cons of anti-paradigm

Advantages: The association of tables can be reduced during query. Can better index optimization;

Disadvantages: Data redundancy and data maintenance exceptions exist. Changes to the data cost more

MySQL > select * from ‘MySQL’

1, the principle of database index

The principle is to sift through a large amount of collected data to find the important information you need to obtain. For example, in the database for one line or more lines, screen to meet the number of conditions you set, etc. The advantage of database indexing is that information can be quickly located to help search.

2, MySQL index advantages and disadvantages

Advantages:

  • All MySql column types (field types) can be indexed, meaning that any field can be indexed
  • Greatly speed up the data query speed

Disadvantages:

  • Creating and maintaining indexes takes time and increases as the volume of data increases
  • Indexes also take up space, and we know that the data in the table will also have a maximum limit set. If we have a large number of indexes, the index file will probably reach the maximum value faster than the data file
  • When the data in a table is added, deleted, or modified, the index needs to be dynamically maintained, which reduces the data maintenance speed.

3, MySQL index usage principles

Through the advantages and disadvantages mentioned above, we should know that it is not good to set the index for each field degree, nor is it better to have more indexes, but to use them reasonably.

  • Avoid over-indexing tables that are frequently updated, and create indexes for fields that are frequently used for queries.
  • It is best not to use indexes for small tables, because because there is less data, it may take less time to query all the data than to traverse the index, and indexes may not be optimized.
  • Do not index columns with fewer values in common, such as the “gender” field in the student table with only two different values for male and female. Conversely, a field with many different values can be indexed.

What is said above is only very one-sided some things, index certainly has many other advantages or disadvantages, as well as the principle of use, first basically understand index, and then wait for the real use of later, will slowly know other functions.

4, MySQL database index type

Indexes are divided into four categories: single-column index (common index, unique index, primary key index), composite index, full-text index, and spatial index

Single-column index: An index contains only a single column, but a table can have multiple single-column indexes.

(1) Ordinary index

The basic index type in MySQL, with no restrictions, allows for the insertion of duplicate and null values in the column where the index is defined to speed up queries.

Create method:

A. Create a table together when creating a table

CREATE TABLE mytable ( name VARCHAR(32) , INDEX index_mytable_name (name) );Copy the code

B. After creating a table, create indexes

CREATE INDEX index_mytable_name ON mytable(name);Copy the code

C. Modify the table structure

ALTER TABLE mytable ADD INDEX index_mytable_name (name);Copy the code

Note: If it is a string field, you can also specify the length of the index by following the column command with the index length (e.g. Name (11)).

(2) Unique index

Values in index columns must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique.

Create method:

A. Create a table together when creating a table

CREATE TABLE mytable ( `name` VARCHAR(32) , UNIQUE index_unique_mytable_name (`name`) );Copy the code

B. After creating a table, create indexes

CREATE UNIQUE INDEX index_mytable_name ON mytable(name);Copy the code

C. Modify the table structure

ALTER TABLE mytable ADD UNIQUE INDEX index_mytable_name (name);Copy the code

Note: If it is a string field, you can also specify the length of the index by following the column command with the index length (e.g. Name (11)).



(3) Primary key index

Is a special unique index. A table can have only one primary key and no null values are allowed. The primary key index is typically created while the table is being built.

Create method:

A. Create a table together when creating a table

CREATE TABLE mytable ( `id` int(11) NOT NULL AUTO_INCREMENT , `name` VARCHAR(32) , PRIMARY KEY (`id`) );Copy the code

B. Modify the table structure

ALTER TABLE test.t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);Copy the code

Note: If it is a string field, you can also specify the length of the index by following the column command with the index length (e.g. Name (11)).

(4) Composite index

An index created on a combination of fields in a table is used only if the left field of those fields is used in a query condition, following the leftmost prefix set.

Create method:

A. Create a table together when creating a table

CREATE TABLE mytable ( `id` int(11) , `name` VARCHAR(32) , INDEX index_mytable_id_name (`id`,`name`) );Copy the code

B. After creating a table, create indexes

CREATE INDEX index_mytable_id_name ON mytable(id,name);Copy the code

C. Modify the table structure

ALTER TABLE myTABLE ADD INDEX index_mytable_id_name(id,name);Copy the code

(5) Full-text index

CHAR,VARCHAR,TEXT, CHAR,VARCHAR, CHAR,VARCHAR, CHAR,VARCHAR, CHAR,VARCHAR, CHAR,VARCHAR, CHAR,VARCHAR Pretty girl…” Through the beautiful boy, may be able to find the record. This is possible, because the use of full-text indexes involves a lot of detail, we only need to know the general meaning.

Create method:

A. Create a table together when creating a table

CREATE TABLE `article` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(250) NOT NULL , `contents` text NULL , `create_at` int(10) NULL DEFAULT NULL , PRIMARY KEY (`id`), FULLTEXT (contents) );Copy the code

B. After creating a table, create indexes

CREATE FULLTEXT INDEX index_article_contents ON article(contents);Copy the code

C. Modify the table structure

ALTER TABLE article ADD FULLTEXT INDEX index_article_contents(contents);Copy the code

(6) Spatial index

Spatial indexes are built on fields of spatial data types. There are four spatial data types in MySQL, namely, GEOMETRY, POINT, LINESTRING, and POLYGON. Use the SPATIAL keyword when creating SPATIAL indexes. If the engine is MyISAM, the column that creates the spatial index must be declared NOT NULL.

If you have any questions or suggestions, please leave a message.

Learn more on the path from coder to architect

I hope the above content can help you. Many PHPer will encounter some problems and bottlenecks when they are advanced, and they have no sense of direction when writing too many business codes. I have sorted out some information, including but not limited to: Distributed architecture, high scalability, high performance, high concurrency, server performance tuning, TP6, Laravel, YII2, Redis, Swoole, Swoft, Kafka, Mysql optimization, shell scripting, Docker, microservices, Nginx, etc.