MySQL database from getting started to combat (i)

Part I: Understanding the DBA system
One What skills should a novice DBA master? (Operation and maintenance personnel will be knowledge)
1.MySQL Installation Deployment
2. Basic parameter Configuration
3. Backup strategy Design and implementation
Ii. What skills should an intermediate DBA master?
1. Fault handling capability (backup or other means)
2.MySQL monitoring capability
3. Basic optimization capability (index, execution plan, slow log analysis), with a certain amount of SQL analysis capability
4. Master-slave replication of basic management, application, fault handling
Iii. What skills should senior DBAs master?
1.MySQL Performance Tuning: In-depth understanding of the overall architecture, "business", "User Behavior", and a solid SQL capability
2. High-performance, high-availability architecture: In-depth understanding of the overall architecture, "business", "User Behavior"
Iv. Understanding of the data
Abstract display or storage of real objects, events
What data is appropriate for the database to store?
"Important data
"is not binary, it is more suitable for storing in a database than in a text format.
"has a relationship with complex logical data.
"Data Management Complex
Part II: DBMS Database management system
First, the role:
1. More organized storage and querying data
2. Provide more advanced data management methods (backup, recovery, optimization, security, etc.)
Second, classification:
1. Relationship Type
Two-dimensional table
Typical product Oracle Traditional enterprise, MySQL is the Internet enterprise
Data access is via SQL
Maximum characteristics, strong data security (ACID)
2. Non-relational (NoSQL)
Non-relational database (not only SQL)
Not a negative relational database, to complement the relational database
3. Database version
Oracle Database version Introduction (still ranked first)
(8.1.7,9.2.0.8, 10.2.0.4 10.2.0.5,11.2.0.3 11.2.0.4,12.2)
MySQL Database version introduction (open source free)
(5.6.36, 5.6.38, 5.6.34, 5.7.18,5.7.20)
Interview Question summary: Ask your company to use the MySQL version, why use?
Industry specifications, select major version of the mainstream, release more than six months GA version, our company chose this database is based on the company's business characteristics of the decision, the development phase is also used in the 5.6 version. (according to their own business company situation)
Third, add:
MARIADB (a major branch of MySQL) storage engine Tokudb features:
1.insert Operation 3-4 Times higher than InnoDB performance
2. Data compression ratio of more than 8 times times
3. Better query performance than InnoDB
"Enterprise Application Scenario:
Zabbix (Reptile) application Business type: (for the following reasons)
1.insert Insert Multiple
2. Large amount of data, need to store large amount of data
PERCONADB (understand, have the energy to study) research its tools:
Xtrabackup (for Optimizing SQL)

Part III: MySQL architecture
I. Composition of MYSQLD
(1). Connection Layer
Description
"Provides communication protocols
TCP/IP---All systems common
Sockets---Linux-specific
"provides a link thread; As client requests derive a specialized response thread; interaction with the client
To view the number of connection threads for the current database: Show full processlist
process:"The receiving client sent SQL" returned to the client results after the query results "to the lower (SQL Layer) transport received SQL" backend processing completed the result is responsible for receiving, transferred to the client.
(2) SQL layer
SQL thread receives the SQL statement sent over the upper line Cheng
Check if the syntax is correct, throw an error message incorrectly, and authorize the test
"Check semantics, type of statement (DDL, DCL, DML, DQL)
"will give the statement to different parsing to parse
"Parser begins parsing, generating execution plan
Optimizer, determine all generated execution plans, find the lowest cost execution plan, transfer to the actuator
Executor, execute the execution plan, and figure out which page to find which file, which row of data
"Has a dedicated thread that sends the results to the lower (storage engine layer) to continue processing
The data structure of the lower layer is sent to the form of a table, to the upper connection layer dedicated thread
"Hashes the SQL statements executed in the upper layer, generates a hash result (sql_id), and caches the data results (Query_cache).
Add:
"The mainstream solution in the enterprise now closes Query_cache, using Redis or Memcache instead
(3) Storage engine layer
"Receives the data from the upper layer, and finds the corresponding row on the page.
After the row is found, send it to the SQL layer
Second, page, district, paragraph definition
(1) page (default 16k): The minimum physical storage allocation unit for MySQL.
Used to hold rows in a table in a database
(2) Area: MySQL think for a row of data, if you need multiple pages, we try to give him a continuous more efficient, all have the concept of the area.
(3) Multi-zone composition, we believe that the continuous section

MySQL database from getting started to combat (i)