Summary: Basics of the CentOS 6.5 MySQL database and in-depth explanations

Summary: Basics of the CentOS 6.5 MySQL database and in-depth explanations

Original: http://tanxw.blog.51cto.com/4309543/1395539

Objective

Early MySQL AB was sold to Sun for $1 billion in 2009, but unfortunately sun was acquired by Oracle in 2010, and when Sun was acquired by Oracle, many knew that Oracle was limbo and no longer selling, This is the way it used to be, although the promise of 5 years will not do to MySQL, but 5 years soon passed.

After Sun was acquired by Oracle, the original author of MySQL made a separate portal, providing another branch on the basis of MySQL, that is, Mariadb,maria is the author's little daughter, so the data is named after the author's little daughter. MARIADB on a MySQL-compatible basis also integrates the strong support power of the open source community, while Percona is committed to MySQL optimization, and has also achieved a non-gate result in an organization, Percona in the improvement of the early INNODB based on the provision of the enhanced version of the XTRADB, which is the enhanced version of the InnoDB, and maridb with XTRADB is no longer innodb, in addition to MARIADB also introduced the enhanced version like MyISAM, improved version, So mariadb in the storage engine may be in a small difference, but this difference is based on the previous evolution, so the knowledge of MySQL still applies to mariadb, in general, mariadb than MySQL.

Sql:structure query Language structure querying language
DDL (data definition Language): CREATE, DROP, ALTER
DML (Data Manipulation language): SELECT, INSERT, UPDATE,
DCL (Data Control Language): GRANT, REVOKE

The so-called relational model: is a two-dimensional relationship structure, there are so-called rows and columns of the table, one or more tables combined together database.

how MySQL works on the inside:
First Floor:This layer is responsible for connecting with the user request, each client connection will have a thread on this layer, MySQL is a single-process multi-pedigree model, MySQL threads have many types, a class is responsible for connecting with the user called the connection thread, The other is constantly in the buffer pool of data synchronization of the disk called Brush write thread, and even different storage engine behind the maintenance of a variety of threads, so there are many kinds of MySQL threads, then responsible for connecting with the user we are often called the connection thread.
The number of concurrent connections supported by MySQL is limited because our resources are limited, and MySQL, especially MARIADB, is implemented by thread pool when it comes to threading management, in fact, many thread-based mechanisms are managed based on a thread pool. MySQL will create some idle threads in the pool at startup, and when the user request arrives, it will use these idle threads to receive the user's request, a user connects to a thread, and when the MySQL connection reaches the limit, the new incoming connection will be queued. MySQL or mariadb maintain a queue, the front processing thread once completed the thread will be idle, the idle thread will not be destroyed, but the data inside to clean up, restore the original look, and then receive new user requests, so this mechanism is called thread reuse.
While the client and MySQL server connection to the MySQL protocol to establish communication, MySQL this protocol generally has two formats, the HTTP protocol is a file format, and HTTPS is binary, so our protocol is the two formats, However, the MySQL protocol is supported in both format protocols, but the binary format of the protocol to be more efficient, so the two formats are supported by default binary format, the MySQL protocol by default is clear, and the binary is actually through the binary encoding after the format, If you use an anti-coding tool to easily crack the data in this format, so the data is not secure, we introduced the encryption, and his protocol based on SSL encryption sent.

Second Floor:MySQL parsing, parser is a parser, also known as the parser, his main function is to analyze query statements, first of all to do lexical analysis, to the SELECT statement cut into pieces of fragments, and then do the grammar analysis, see the whole sentence there is no grammatical errors, and even to do semantic analysis, These are done by the parser, of course, MySQL did not develop their own analyzers, yacc,lex,mysql directly borrowed these analyzers to do two development and integration into MySQL, parser is the analysis of statements to ensure that the statement is not a problem, there is no problem requesting permission, Then the analysis is done to the optimizer (Optimizer) to do the optimization, the optimizer to do include rewrite query, if the query statement is not good enough will be rewritten, the statement is rebuilt, but the results must be the same, the optimizer also includes the decision table reading order, If we do a long table query, there will be a sequence, if you write the query statement is a,b,c, but the optimizer thinks b,a,c more efficient, so the optimizer will adjust the table reading order, and another if we query on multiple tables when there are multiple indexes, The optimizer will also choose an index with the least cost, but the optimizer can also accept the user's suggestion, and if we know how to write to make the query better, then we should write as good as possible to affect the human optimizer.
MySQL in the query when the use of the index and what kind of index can be used in a statement to analyze, called explain, So explain is a very important analysis tool for us to do MySQL statement optimization or to see if MySQL can use the index for our intended purpose, of course explain results are only reference

Third Floor:The optimizer does not care about what storage engine is actually used on the bottom of MySQL, but we know that the storage engine offers different performance depending on the characteristics of his work. But fortunately, the optimizer can determine how much the query overhead is based on this by making a call request to the storage engine's API to get the storage engine to return the statistics for the table under the storage engine, so that optimizer could interact with the storage engine. and using the storage engine internal statistical data to make optimization decisions, here we are talking about the generalized query, Update,insert,delete, these are to query, and if it is a real query, MySQL can also use the query cache to function, So the query cache is only valid for our SELECT statement, and only select is cached.

The SQL Layer contains multiple sub-modules, and I'll take a brief look at the following:

Logical module composition

1. Initialize the module
As implies, the initialization module is the beginning of the MySQL server, the whole system to do a variety of initialization operations, such as the initialization of various buffer,cache structures and memory space applications, the initialization of various system variables, various storage engine initialization settings, and so on.
2. Core API
The core API module is designed to provide some optimization implementations that require very efficient underlying operations, including implementations of various underlying data structures, implementation of special algorithms, string processing, digital processing, small file I/O, formatted output, and most important memory management. All the source code of the core API module is concentrated under the Mysys and Strings folders, and interested readers can study it.
3. Network Interaction Module
The underlying network interaction module abstracts the interface APIs used by the underlying network interaction, enabling the receiving and sending of the underlying network data to facilitate the invocation of other modules, as well as the maintenance of this part. All source code is under the Vio folder.
4. Client & Server Interaction Protocol Module
Any software system of C/s structure will certainly have its own unique information interaction protocol, MySQL is no exception. MySQL's Client & Server Interaction Protocol Module Section implements all of the protocols that clients have with MySQL during the interaction process.
Of course, these protocols are built on existing OS and network protocols, such as TCP/IP and Unix sockets.
5. User Module
The user module realizes the function, mainly includes the user's login connection permission control and the user's authorization management. He decided to "open the door" to the visitor, just like the door guard at MySQL.
6. Access Control Module
What do you want to do when you visit the guests? For security reasons, it must not be so casual. The access control module is required to monitor the guest's actions in real time and give different privileges to different guests. The function of the access control module is to control the user's access to the data according to the authorization information of each user in the user module and various constraints peculiar to the database itself. Both the user module and the Access control module combine to form the function of security management of the entire MySQL database system.
7. Connection management, connection threading, and thread management
The connection Management module is responsible for listening to various requests to MySQL Server, receiving connection requests, and forwarding all connection requests to the thread management module. Each client request to a MySQL Server on a connection is assigned (or created) by a connection thread for its individual service. The main task of connection thread is to be responsible for the communication between MySQL server and client, accept the command request from client, pass the result information of server side and so on. The thread management module is responsible for managing and maintaining these connection threads. This includes the creation of threads, the cache of threads, and so on.
8. Query parsing and forwarding module
In MySQL we are accustomed to the command that all client side sends to Server side is called query, in MYSQ lserver, the connection thread receives a query from the client, it will pass the query directly to the specialized responsible for the various query The classification is then forwarded to each corresponding processing module, which is the query parsing and forwarding module. The main task is to make the query statement semantic and grammatical analysis, and then according to different types of operation classification, and then make targeted forwarding.
9. Query Cache Module
The query cache module is a very important module in MySQL, his main function is to submit the client to the MySQL Select class query request return result set Cache into memory, and a hash value of the query to do a corresponding. After any data changes occur to the base table of the data taken by the query, MySQL automatically invalidates the query's cache. In applications with very high read-write ratios, Query Cache is a significant improvement in performance. Of course, its memory consumption is also very large.
10. Query Optimizer Module
Query optimizer, as the name implies, is to optimize the client request query, according to the client request query statement, and the database of some statistical information, based on a series of algorithms to analyze, to obtain an optimal strategy, tell the following program how to get the results of this query statement.
11. Table Change Management module
Table Change Management module is mainly responsible for the completion of some DML and DDL query, such as: Update, DELTE, Inse RT, CREATE TABLE, ALTER TABLE and other statements processing.
12. Table Maintenance Module
Table status checks, bug fixes, and optimizations and analyses are all things that the table maintenance module needs to do.
13. System State Management Module
The System State Management module is responsible for returning various state data to the user when the client requests the state of the system, such as the various show status commands commonly used by DBAs, the show variables command, and so on, and the resulting results are returned by this module.
14. Table Manager
This module is easily confused with the table change and table maintenance module, but its function is completely different from the change and maintenance module. As you know, every MySQL table has a table definition file, which is the *.f RM file. The main task of the Table manager is to maintain these files, as well as a cache, where the main content of the cache is the structure information of the individual tables. In addition, it maintains table-level lock management.
15. Log Recording Module
The logging module is responsible for logging the entire system-level logic layer, including error log, Binar ylog, slow query log, and so on.
16. Copy Module
Replication module can be divided into Master module and Slave module two parts, Master module is mainly responsible for reading the binary log of the master side in the replication environment, and the interaction with the I/O thread of the Slave side. The Slave module has a little more to do than the Master module, which is mainly embodied on two threads in the system. One is responsible for requesting and accepting binary logs from Master and writing the I/O thread in the local relay log. The other one is responsible for reading the related log events from relay log and parsing them into a command that can be executed correctly on the Slave side and get exactly the same results as the master, and then handed to the SQL thread executed by Slave.
17. Storage Engine Interface Module
The storage Engine interface module can be said to be one of the most distinctive features of the MySQL database. Currently, only MySQL can implement the plug-in management of its underlying data storage engine in a variety of database products. This module is actually an abstract class, but it is precisely because it successfully abstracts the various data processing, it is the feature of today's MySQL pluggable storage engine.

After understanding the entire process, we can map the diagram of each of the above modules:

working with each module
After learning the various MySQL modules, let's look at how MySQL works together with each other. Next, we analyze the entire process by starting MySQL, client connection, request query, get return results, and finally exit.

When we execute the start MySQL command, the MySQL initialization module reads the system parameters and command line parameters from the system configuration file and initializes the entire system with parameters, such as requesting and allocating buffer, initializing global variables, and various structures. At the same time, each storage engine is also started, and the respective initialization work is done. When the entire system is initialized, it is taken over by the connection management module. The connection Management module initiates a listener that handles client connection requests, including TCP/IP network snooping, and a UNIX socket. At this point, MySQL Server completes the basic boot and is ready to accept the client request.

When the connection management module hears the client's connection request (with the help of the Network interaction module), the connection Management module forwards the connection request to the thread management module and requests a connection thread after the two parties have "exchanged greetings" with the protocol defined by the Client & Server Interaction Protocol module.

The thread management module will now hand over the control to the connection thread module, telling the connection thread module: Now I have a connection request coming over, need to establish a connection, you quickly deal with. Connection thread module after receiving the connection request, first checks whether there is an idle connection thread in the current connection thread pool, and if so, takes out one and the client request connection, and if there is no idle connection thread, establishes a new connection thread to connect with the client request. Of course, the connection thread module does not immediately take out a connection request after receiving a connection thread attached and a client connection, but rather first by invoking the user module for authorization checks, only after the client request passed the authorization check, he will be the client request and responsible for the requested connection line thread attached.

In MySQL, there are two types of client requests: A query that calls Parser, which is the query parsing and forwarding module parsing to execute, and a command that does not need to call Parser to execute the request directly. If the function of full Query Logging is turned on in our initialization configuration, then the query parsing and forwarding module invokes the logging module to write the request to the log, either a Query-type request or a command-type request, which will be logged into the log. Therefore, for performance reasons, it is generally very rare to turn on fullquery Logging functionality.

When the client request and the connection thread "exchange the password (interworking protocol)", the connection thread begins processing various commands (or query) sent by the client request, accepting the request. It forwards the received query statement to the query parsing and forwarding module, and the query parser makes basic semantic and syntactic parsing of the query, and then depending on the type of command, some will be processed directly and some will be distributed to other modules for processing.

If it is a query-type request, control is given to the query parser. The query parser first parses to see if it is a select type of query, and if it does, call the querying cache module and let it check whether the query already exists in query cache. If so, the data in the cache is returned directly to the connection thread module, which then transmits data to the client through the thread connected to the client. If it is not a query type that can be used by the cache, or the data in the cache does not have the query, then query will be passed back to the query parser, so that the Qu Ery parser is processed accordingly and distributed to the relevant processing module via the query dispatcher.

If the parser resolves the result to be a SELECT statement that is not the cache, the control is given to Optimizer, the Query optimizer module, and if it is DML or a DDL statement, it is given to the table change management module, if it is some update statistics, detection, repair, and collation of the class Query will be given to the table maintenance module to process, copy the relevant query to the replication module to do the corresponding processing, the request status of the query is forwarded to the State Collection report module. The fact that the table change management module differs from the corresponding processing request is that the Insert processor, the delete processor, the update processor, the Create processor, and the Alt ER processor are the small modules responsible for different DML and DDL.

After each module receives the Query resolution and the distribution module to distribute the request, first through the Access control module to check whether the connection user has access to the target table and the Target field permissions, if any, will call the Table Management module request the corresponding table, and obtain the corresponding lock. The table Management module first checks to see if the table already exists in table cache, if it is already open, locks-related processing, if not in the cache, you need to open the table file to get the lock, and then hand the open table to the table change management module.

When the table change management module "gets" The open table, the table's storage engine type and other related information are judged based on the relevant meta information for that table. According to the storage engine type of the table, submit the request to the Storage Engine interface module, call the corresponding storage engine implementation module, and handle accordingly.

However, for the table Change management module, only the storage Engine interface module provides a series of "standard" interface, the underlying storage engine implementation of the module implementation, for the table change management module is transparent. He only needs to invoke the corresponding interface and indicate the table type, and the interface module calls the correct storage engine according to the table type to handle it accordingly.

When a query or command processing completes (success or failure), control is returned to the connection thread module. If the processing succeeds, the processing result (either a result set or a successful or failed identity) is fed back to the client through the connection thread. If an error occurs during processing, the appropriate error message is also sent to the client, and then the thread module cleans up and continues to wait for the subsequent request, repeating the process mentioned above, or completing a client disconnect request.

If, in the process above, the relevant module changes the data in the database and MySQL opens the Bin-log function, the corresponding processing module also invokes the log processing module to record the corresponding change statement as an update event in the binary log file specified by the relevant parameter.

During the processing of each of the above modules, the core arithmetic processing functions of each module are highly dependent on the core API modules of MySQL, such as memory management, file I/O, digital and string processing, etc.

1, the client and the server side of the interaction is always done through the protocol, here is the MySQL protocol, whether through the way of the API, or through the way of special libraries, must be through the client and server-side connection to establish, and all the query needs to send to the server side, Server-side MySQL through the way he works, a single-process multithreaded model, the server side will use a thread to receive client requests.
2, such as the server side received the client sent by the query statement, the first in the server or local check whether the cache hit, if the cache hit, immediately return the results to the client, if the query cache is not found in the corresponding cache, then this query process is consumed in vain, So the criteria for using the cache should be more than 30% of the resource cost, the cache is not all necessary, and the cache is not valid because it also consumes resource overhead.
3, if the cache does not hit, will be the cache to the parser, the parser is responsible for parsing will be handed to the parse tree tool, if necessary, the parse tree with the preprocessor for parsing, after parsing this will have multiple paths, and then the optimizer will choose the least cost of a road for the next operation, And then the result of the rewrite to the query plan, in fact, is handed to the query execution engine, and MySQL database is a multi-user connection, and our query engine only one, if the query engine queries to a lot of words that will be queued, in the queue, but the query engine can not go to the disk to read data, The data is fetched from the table, and the table is stored on disk by default, so the query execution engine simply transforms the optimizer's chosen query into the query engine's API call, and invokes the corresponding storage engine, which gets the data required by the query execution engine's API call. The data is returned to the execution engine, and then the layer is returned to the client.
Caching can also be judged, and when we write a query we can explicitly specify whether or not to cache, of course, the specified cache may not be able to cache, but we define some query statements are expected to be cached.

Mysql Sql_mode: SQL model is commonly used in three, SQL model is simply to define the working mechanism of MySQL, like above that the character length is intercepted or not to allow the insertion, which requires us to define, this model can also be to some extent to simulate the performance of other databases. MySQL Default uses empty mode

Traditional: Indicates the use of traditional mode
Strict_trans_tables: Use strict mode only for tables that support things
Strict_all_tables: Strict mode is used for all tables, and strict mode is not allowed to insert data when characters are exceeded.
Mysql> show GLOBAL VARIABLES like ' Sql_mode '; see which mode is used in the current data
Set the value of the server variable: used only to support dynamic variables, not all of the server's variable values can be set

There are two types of server variables that support modification:
Dynamic variables: can be modified at MySQL runtime
Static variables: modify their values in the configuration file and reboot to take effect
Modify Server variables: Server variables in terms of their function or scope of effect, there are two categories:
Global variables:mysql global settings, or server level, will not take effect immediately after modification, only valid for newly established sessions.
Session Variables: session level, valid only for the current session, and expire after exiting.
When a session is established, the variables are inherited from the global
View server variables: Here are three ways to view server variables

123456789

mysql > show{GLOBAL|SESSION} VARIABLES [LIKE ‘’];mysql > SELECT @@{GLOBAL|SESSION}.VARLABLE_NAME;mysql > select* from information_schema.global_variables where variables_name=’SOME_VARIABLES_NAME’;修改全局变量仅管理员有权限，或者被授权，大多数普通用户仅能够修改会话变量mysql > setSQL_MODE=’’;设置sql_mode模块是否记用mysql > set{GLOBAL|SESSION} VARIABLE_NAME=’VALUE’;mysql > show table status like ‘t3’ \G：查看某表上的存储引擎相关信息mysql > show engines;显示当前服务器上的存储引擎MariaDB [test]>CREATE TABLE t1 (Name VARCHAR(30) NOT NULL, Age TINYINT UNSIGNED NOTNULL,PRIMARY KEY(Name,Age)) ENGINE=‘InnoDB‘;创建表t1，指定表的存储引擎为InnoDB

Note: Both global and session-level dynamic variable modifications, after restarting MySQL, will expire, want to be permanent, can be defined in the corresponding section of the configuration file [Mysqld]

Problem with character case in MySQL:
1, SQL keyword and function name do not distinguish between character case, but in order to improve the cache hit rate, the habit is best to unify;
2, the database, table and view names in case sensitivity and whether or not depends on the low-level OS and FS;
3, stored procedures, storage functions and event scheduler names are not case-sensitive, but the trigger is case-sensitive;
4, the alias of the table is case-sensitive character;
5, String values, the data in the field, if the field type is binary type, it is case-sensitive, non-binary case-insensitive;

End

Collation of some of the basic knowledge of MySQL and work principles, are text, lengthy, but serious seems to be relatively basic, due to time problems, if there is no place to look for more guidance.

This article is from the "boiled frog with boiling water" blog, please make sure to keep this source http://tanxw.blog.51cto.com/4309543/1395539

(RPM) Summary: Basics of the CentOS 6.5 MySQL database and in-depth explanations