Getting Started with MySQL - Concepts

1. What is it?

MySQL is the most popular relational database management system. In terms of WEB applications, MySQL is one of the best RDBMS (Relational Database Management System) application software.

1.1 RDBMS

Features of RDBMS (Relational Database Management System):

1. Data appears in table form

2. Each line is a record name

3. Each column is the data field corresponding to the record name

4. Many rows and columns make up a form

5. Several forms make up the database

1.2 MySQL database

MySQL is a relational database management system developed by the Swedish company MySQL AB and currently owned by Oracle. MySQL is a relational database management system that stores data in different tables instead of putting all data in one large warehouse, which increases speed and flexibility.

• MySQL is open source and is currently a product of Oracle.
• MySQL supports very large databases. Can handle large databases with tens of millions of records.
• MySQL uses the standard SQL data language form.
• MySQL can run on multiple systems and supports multiple languages. These programming languages ​​include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby, and Tcl, among others.
• MySQL has good support for PHP, which is currently the most popular web development language.
• MySQL supports large databases and data warehouses with 50 million records. The maximum table file supported by a 32-bit system is 4GB, and the maximum table file supported by a 64-bit system is 8TB.
• MySQL is customizable and uses the GPL protocol. You can modify the source code to develop your own MySQL system.

2. Working Mechanism

2.1 Overall architecture diagram (C/S architecture)

• Client:

A common toolkit that provides the ability to connect to a MySQL server

• Server:

MySQL instance, the MySQL server process that actually provides data storage and data processing functions

• mysqld:

The MySQL server daemon runs in the background. It manages client requests. mysqld is a multi-threaded process that allows multiple sessions to connect, listens on ports, and manages MySQL instances.

• MySQL memory allocation:

The memory space required by MySQL is dynamic, such as innodb_buffer_pool_size (from 5.7.5), key_buffer_size. Each session has a unique execution plan, and we can only share data sets within the same session scope.

• SESSION

Each client connection is assigned a session, which is dynamically allocated and recycled. Used for query processing, each session has a buffer at the same time. Each session is executed as a thread

• Parser

Detect SQL statement syntax and generate SQL_ID for each SQL statement. User authentication also occurs at this stage.

• Optimizer

Create an efficient execution plan (depending on the storage engine). It will rewrite the query. For example: InnoDB has a shared buffer, so the optimizer will first extract from the pre-cached data. Using the table statistics optimizer will generate an execution plan for the SQL query. User permission checks also occur at this stage.

• Metadata cache

Cache object metadata and statistics

• Query cache

Share the same query statement in memory. If the exact same query hits the cache, the MySQL server will retrieve the result directly from the cache. The cache is shared between sessions, so a result set generated for one client can be used by another client. The query cache is based on SQL_ID. Writing SELECT statements to a view is a good example of query caching.

• key cache

Cache table index. MySQL keys are indexes. If the index data size is small, it will cache the index structure and leaf nodes (which store index data). If the index is large, it will only cache the index structure, usually used by the MyISAM storage engine

2.2 Network Protocol

2.2.1 Communication Protocol

As of MySQL 5.7, there are five types, namely TCP/IP, TLS/SSL, Unix Sockets, Shared Memory, and Named pipes. Let's take a look at the differences between these five types:

2.2.2 Message Format

Protocol header:

Each protocol header has 4 bytes

- Packet data length:

The first three bytes indicate the length of the data part (excluding the protocol header). The maximum length that can be represented by three bytes is 16M-1 (2^24 - 1). If the data part to be sent is larger than this length, it needs to be unpacked, with each 16M-1 length as one packet. When the receiving end receives data, if it detects that the length of the packet is 16M-1, it means that there is more data to follow, until the data packet with a length of <16M-1 is received. This means that the data length of the last packet may be 0.

Serial number:

1 byte, starting from 0 and increasing by one. When a new SQL is sent and the database is reconnected, the value is cleared to 0 (function sql/Net_serv.cc: net_clear).

Data Type:

In addition to fixed-length integers or strings, there are several other types of data. (Fixed-length field data storage and access: include/Mybyte_order.h: store value intstore, get value: intkorr, multi-byte processing is based on little-endian priority)

     1. The variable-length integer access to this type of data is in the function: sql-common/Pack.c: Store integer: net_store_length Read integer: net_field_length
        If the value is less than 251, store the value directly in one byte.
       If 251<=value<2^16, use 3 bytes to store, the first byte is 252, and the other 2 bytes store the integer content. If 2^16<=value<2^24, use 4 bytes to store, the first byte is 252, and the other 3 bytes store the integer content. If 2^24<=value<2^64, use 9 bytes to store, the first byte is 255, and the other 8 bytes store the integer content. If the first byte is 251, it means that the integer field is null.
       If the first byte is 255, it means that this byte is the first byte of the ERR packet. 2. String with encodable length The length of the string is encoded using a variable-length integer.

2.3 SQL Syntax

SQL can be divided into two parts: Data Manipulation Language (DML) and Data Definition Language (DDL). SQL (Structured Query Language) is a syntax for executing queries. But the SQL language also includes syntax for updating, inserting, and deleting records.

Query and update instructions make up the DML portion of SQL:

SELECT - Get data from a database table UPDATE - Update data in a database table DELETE - Delete data from a database table INSERT INTO - Insert data into a database table The Data Definition Language (DDL) portion of SQL gives us the ability to create or delete tables. We can also define indexes (keys), specify links between tables, and impose constraints between tables.

The most important DDL statements in SQL:

CREATE DATABASE - creates a new databaseALTER DATABASE - modifies a databaseCREATE TABLE - creates a new tableALTER TABLE - alters (changes) a database tableDROP TABLE - deletes a tableCREATE INDEX - creates an index (search key)DROP INDEX - deletes an index

3. Summary

This article explains what MySQL is from a macro and usage perspective, and will be discussed in depth later. Please also pay more attention to more content on 123WORDPRESS.COM