MySQL Knowledge summary

MySQL's knowledge interview is often asked, simple use seems unable to meet the interviewer's requirements, a lot of questions about the MySQL storage engine, so here still need to learn some knowledge of MySQL, the interview process.

MySQL Architecture

MySQL is a hierarchical system composed of multiple subsystems.

A.connectors: The database interface used to establish a connection to the client application. (Available in a variety of high-level language versions, such as previous mysql-python-connector).

B.Management Services & Utilities: System Management AIDS related to service control.

C.Connection Pool: Responsible for handling user login, threading, memory and process caching requirements related to user access.

D.SQL Interface: Provides a mechanism for receiving commands from the user and returning the results to the user.

E.Parser: Parsing and parsing SQL statements to construct a data structure for querying.

F.Optimizer: Optimizing the query statement, the efficiency of the data retrieval operation is very close to the optimization. Using a "pick-drop-join" strategy for optimization, first based on the constraints, that is, the WHERE statement to select the data that satisfies the condition, and then according to the properties behind the Select to project, and finally based on the junction conditions to derive the final data.

G.Caches & buffers: Ensure that the most frequently used data can be accessed in the most efficient manner, providing caching methods: Table cache, record cache, key cache, permission cache, host name cache.

H.pluggable Storage Engines: Plug-in storage engine, implementation of the underlying physical structure, responsible for database execution of actual I/O operations, table-based structure, not database-based, can be customized for different tables of the storage engine. Its core is an abstract file access interface, so there is a third-party implementation of the storage engine, using the file access interface established by the new file access mechanism.

MySQL Primary storage engine

The key technology points of the storage engine (each engine's implementation is focused on different points of emphasis, and different technologies are emphasized):

1. Concurrency: The granularity of lock. The greater the granularity of the lock, the less the lock is consumed, and the lower the concurrency level. MySQL provides three types of lock granularity, table level > Page level > Row level.

2. Transaction support: ACID.     Atomicity, consistency, isolation, persistence. The nature of the transaction.

3. Referential integrity: foreign key support. Referential integrity and the relationship of maintaining foreign key references, students (student number, name, gender, age, class number) its secondary number is the main code. Class (class number, number of students, the name of the class teacher), which class number is the main code. The use of integrity is defined as the reference rule between the outside keyword and the primary key word. If you want to delete the referenced object, delete all objects that reference it, or set the reference value to NULL, if allowed.

4. Physical storage: File formats such as tables and indexes.

5. Index support: Indexing policies and methods.

6. Memory Cache: Caching and buffering policies.

7. Other target features: security restrictions, etc.

InnoDB

Support transactions (very important features)

The row lock design provides concurrency while supporting MVCC. (Multi-version concurrency control)

Support for foreign keys to ensure referential integrity

Stand-alone IBD file storage table structure and data

Applies To: emphasizes reliability and requires scenarios that support transactional processing.

MyISAM

Transaction not supported

Table lock Design

Full-Text Indexing

The storage table consists of myd and myi, each of which holds data and indexes

Using data compression and index optimization

Features: High reliability, wide application range, fast retrieval speed

Applies to: Emphasis on data retrieval speed

NDB

Network database, a cluster storage engine

Using share nothing cluster architecture-each node has its own memory and disk

Data is stored in memory, and primary key query speed is fast

Increase NDB storage nodes, linearly improve performance

Network consumption of join connections is large

Large demand for RAM

For: redundancy, high availability, load balancing (need a load balancer, should be designed)

Memory

Table data is stored in memory

Hash index is used by default

The. frm extension indicates the table definition it stores

Server storage, data loss in tables (RAM storage decision)

Only table locks are supported and concurrency is poor

Text and BLOB types are not supported

Storing variable-length varchar types in a fixed-length manner

High speed, low latency, not suitable for frequent read and write (why?) ）

Applies to: temporary storage, Data warehouse dimension table

NDB compared to memory, NDB supports row lock mechanism, higher concurrency, can choose disk storage media storage, and cluster architecture guarantees ndb99.999% availability, supporting data types not supported in memory.

Archive

Provides high-speed insertion and compression capabilities

Row locks, only insert and select are supported, transactions are not supported, data compression

Applies to: Store archived data, log information

Maria

Replaces the original MyISAM storage engine, caches data and index files, row lock design, supports MVCC functions, supports transactional and non-transactional security

MySQL Knowledge summary