Shulou(Shulou.com)05/31 Report--

This article will explain in detail the sample analysis of the basic knowledge in mysql. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Mysql architecture

I. Network connection layer

Client Connector (Client Connectors): provides support for establishing with the MySQL server. At present, almost all the mainstream server-side programming technologies are supported, such as Java, C #, Python, .NET, etc., which connect to MySQL through their respective API technologies.

2. Service layer (MySQL Server)

The service layer is the core of MySQL Server, which mainly includes six parts: system management and control tools, connection pool, SQL interface, parser, query optimizer and cache.

Connection pool (Connection Pool): responsible for storing and managing client connections to the database, and a thread is responsible for managing a connection.

System management and control tools (Management Services & Utilities): such as backup and recovery, security management, cluster management, etc.

SQL API (SQL Interface): used to accept various SQL commands sent by the client and return the results of the query that the user needs. Such as DML, DDL, stored procedures, views, triggers, and so on.

Parser (Parser): responsible for generating a "parsing tree" from the requested SQL parsing. Then further check whether the parsing tree is legal according to some MySQL rules.

Query optimizer (Optimizer): when the parse tree passes the parser syntax check, it is handed over to the optimizer to convert it into an execution plan and then interact with the storage engine.

Select uid,name from user where gender=1

Select-"projection -" connection strategy

1) select selects according to the where statement first, instead of querying all the data and then filtering

2) select query projects attributes according to uid and name, and does not take out all fields

3) join the previous selection and projection to generate the query result

Cache&Buffer: the caching mechanism consists of a series of small caches. Such as table cache, record cache, permission cache, engine cache and so on. If the query cache has a hit query result, the query statement can directly fetch data from the query cache.

3. Storage engine layer (Pluggable Storage Engines)

The storage engine is responsible for the storage and extraction of data in MySQL and interacts with the underlying system files. The MySQL storage engine is plug-in, and the query execution engine in the server communicates with the storage engine through the interface, which shields the differences between different storage engines. There are many kinds of storage engines, each with its own characteristics, the most common of which are MyISAM and InnoDB.

IV. System file layer (File System)

This layer is responsible for storing the data and logs of the database on the file system and completing the interaction with the storage engine, which is the physical storage layer of the file. Mainly includes log files, data files, configuration files, pid files, socket files and so on.

Log file

Error log (Error log)

Enabled by default, show variables like'% log_error%'

General query Log (General query log)

Record general query statement, show variables like'% general%'

Binary log (binary log)

The change operations made to the MySQL database are recorded, and the occurrence time and execution time of the statement are recorded; but it does not record the SQL that select, show, and so on do not modify the database. It is mainly used for database recovery and master-slave replication.

Whether show variables like'% log_bin%'; / / is enabled or not

Show variables like'% binlog%'; / / Parameter view

Show binary logs;// slow to view log files

Query log (Slow query log)

Record the SQL of all queries with timeout execution times. The default is 10 seconds.

Whether show variables like'% slow_query%'; / / is enabled or not

Show variables like'% long_query_time%'; / / duration

Configuration file

Used to store all the configuration information files of MySQL, such as my.cnf, my.ini, etc.

Data file

Db.opt file: record the default character set and verification rules used by this library.

Frm file: stores metadata (meta) information related to tables, including table structure definition information, and so on. Each table has an frm file.

MYD file: dedicated to the MyISAM storage engine, stores the data of the MyISAM table (data), and each table has a .MYD file.

MYI files: dedicated to the MyISAM storage engine, storing information related to the index of MyISAM tables, and each MyISAM table corresponds to a .MYI file.

Ibd files and IBDATA files: data files (including indexes) that store InnoDB. InnoDB storage engine has two tablespace modes: exclusive tablespace and shared tablespace. Exclusive tablespaces use .ibd files to hold data, and each InnoDB table corresponds to an .ibd file. Shared tablespaces use .ibdata files, and all tables share one (or more, self-configured) .ibdata files.

Ibdata1 files: system tablespace data files, storing table metadata, Undo logs, etc.

Ib_logfile0, ib_logfile1 files: Redo log log files.

Pid file

Pid file is a process file of mysqld application in Unix/Linux environment. Like many other Unix/Linux server programs, it stores its own process id.

Socket file

Socket files are also available in the Unix/Linux environment. In the Unix/Linux environment, users can connect to MySQL directly using Unix Socket without going through the TCP/IP network.

InnoDB and MyISAM

Transactions and foreign keys

InnoDB supports transactions and foreign keys, has security and integrity, and is suitable for a large number of insert or update operations

MyISAM does not support transactions and foreign keys. It provides high-speed storage and retrieval, and is suitable for a large number of select query operations.

Locking mechanism

InnoDB supports row-level locks to lock specified records. Lock the implementation based on the index.

MyISAM supports table-level locks to lock the entire table.

Index structure

InnoDB uses clustered indexes (clustered indexes), where indexes and records are stored together, caching both indexes and records.

MyISAM uses nonclustered indexes (non-clustered indexes), where indexes and records are separated.

Concurrent processing capacity

MyISAM uses table locks, which will result in low write concurrency, no blocking between reads, and read-write blocking.

InnoDB read and write blocking can be related to isolation levels, and multi-version concurrency control (MVCC) can be used to support high concurrency

Storage file

The InnoDB table corresponds to two files, an .frm table structure file and an .ibd data file. Maximum support for 64TB in InnoDB table

The MyISAM table corresponds to three files, a .frm table structure file, a MYD table data file, and a .MYI index file. Since MySQL5.0, the default limit is 256TB.

The difference between Redo Log and Binlog

Redo Log belongs to the InnoDB engine function, Binlog belongs to the MySQL Server native function, and is recorded as a binary file.

Redo Log is a physical log, which records the update status content of the data page. Binlog is a logical log that records the update process.

Redo Log log is a circular write, the log space size is fixed, Binlog is an additional write, write one after the next, will not be overwritten.

Redo Log is used for automatic recovery of transaction data after abnormal server downtime, and Binlog can be used as master-slave replication and data recovery. Binlog does not have automatic crash-safe capability.

In the application, multiple indexes can be appended from the database to optimize the query, and these indexes in the main database can not be added to improve the writing efficiency.

Read-write separation scheme

Read immediately after writing

After writing to the database, the read operation goes to the master library within a certain period of time, and then the read operation accesses the slave library.

2 second inquiry

First read the data from the library, and if you can't find it, go to the main library for data reading. This operation is easy to return the read pressure to the main database. In order to avoid malicious attacks, it is recommended to encapsulate the database access API operation, which is conducive to security and low coupling.

3 according to the special treatment of the business

Adjust according to the business characteristics and importance, such as important, real-time requirements of business data reading and writing can be placed in the main database. For secondary services, real-time requirements are not high, can be read-write separation, query from the library when querying.

This is the end of the article on "sample Analysis of basic knowledge in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.