As a popular database in the Internet, MySQL has become the first choice of many Internet companies’ business systems due to its unique advantages in cost and performance. According to developer Toutiao’s 2019-2020 China Developer Survey, 83% of developers choose to use MySQL database. It is no exaggeration to say that MySQL proficiency has become an important criterion in determining the salary of developers.

Today we bring MySQL, including MySQL basic practice guide, MySQL performance optimization, and advanced design of MySQL architecture, including how to manage and operate MySQL, can be said to be from the shallow to the deep, some knowledge points necessary to learn MySQL are included, I believe this document will be helpful to you.

The manual is divided into three parts: MySQL foundation +MySQL performance optimization +MySQL architecture design

Based on article

Introduction to MySQL

1. Introduction of MySQLServer
2. A simple comparison of MySQL to other databases
3. This section describes the application scenarios of MySQL

MySQL Architecture Composition

1. MySQL physical file composition
2. MySQLServer system architecture
3. This section describes how to use the MySQL built-in tools

Introduction to MySQL Storage engine

1. MySQL Storage Engine Overview
2. MylSAM Storage Engine Introduction
3. Lnnodb Storage engine Introduction
4. INDBCluster Storage engine overview

MySQL Security Management

1. Database system security related factors
2. MySQL Permission system introduction
3. MySQL access authorization policy
4. Precautions for security Settings

MySQL backup and restore

1. Database backup scenarios
2. Logical backup and restore tests
3. Physical backup and restore tests
4. Backup policy design roadmap

Performance Optimization

Factors that affect MySQLServer performance

1. Impact of business requirements on performance
2. Impact of system architecture and implementation on performance
3. The impact of the Query statement on system performance
4. Impact of Schema design on system performance
5. The impact of the hardware environment on system performance

MySQL database locking mechanism

1. Introduction to the MySQL locking mechanism
2. Analysis of various locking mechanisms
3. Use lock mechanism to optimize MySQL

MySQL database Query optimization

1. Understand MySQL’s QueryOptimizer
2. Basic ideas and principles of Query statement optimization
3. Take advantage of Explain and Profiling
4. Design and use indexes wisely
5. Implementation principle and optimization idea of Join
6. ORDERBY, GROUPBY, and DISTINCT optimizations

MySQL database Schema design performance optimization

1. Efficient model design
2. Appropriate data type
3. Canonical object naming

MySQLServer performance optimization

1. MySQL Installation optimization
2. MySQL Log Settings Optimization 10.3QueryCache Optimization 10.4MySQLServer Other Common Optimizations

Common storage engine optimization

1. MyISAM storage engine optimization
2. Innodb storage engine optimization

Architectural Design

Basic principles of MySQL extensible design

1. What is scalability
2. The transaction correlation minimization principle
3. Data-induced principle
4. High availability and data security principles

Extensible design for MySQLReplication

1. What Replication means for scalable design
2. How Replication works
3. Replication Common architecture
4. Replication construction implementation

Scalable design for data sharding

1. What is data sharding
2. Vertical segmentation of data
3. Horizontal segmentation of data
4. Use of vertical and horizontal joint segmentation
5. Data segmentation and integration solutions
6. Possible problems in data segmentation and integration

Use of Cache and Search in scalable design

1. Extensible design extends beyond the database
2. Properly utilize third-party Cache solutions
3. Implement the Cache service by itself
4. Use Search to realize efficient full-text retrieval
5. High performance computation with large data volume is realized by distributed parallel computing

MySQLCluster

1. MySQLCluster introduction
2. The MySQLCluster environment is built
3. MySQLCluster configuration details (config.ini)
4. MySQLCluster basic management and maintenance
5. Basic optimization idea

High availability design ideas and schemes

1. Leverage Replication to implement a highly available architecture
2. Use MySQLCluster to achieve overall high availability
3. DRBD is used to ensure high security and reliability of data
4. Other high availability design solutions
5. Comparing the pros and cons of various high availability solutions

MySQL monitoring in a highly available design

1. Design of monitoring system
2. Performance status Monitoring

Outline of the directory

Because the content is too much, here only part of the contents of the directory. Need this “electronic” information partners, fast access: pay attention to the end of the public account to obtain, full of sincerity!!

This MySQL advanced god technology, interested partners can help click a like, fast start channel: pay attention to the end of the public account to get! Sincerity full!!

Total directory

Internet companies for Java application scenarios are also constantly upgrading, from single deployment to distributed, from SOA to micro service, Java back-end technology stack become more big, more and more is also high to the requirement of the engineer, we in the Java programmers to write good business code not only, more to master the back-end technology, in addition to the database, Technologies such as caching, message queuing, scheduled tasks, microservices, etc., are also things we need to master.

Today bring you the MySQL advanced magic, I believe that can bring you quality improvement in this technical aspect.

Quick start channel: pay attention to the following public number can be free access! Sincerity full!!

Another big factory Java interview selection questions, architecture actual combat documents

Sorting is not easy, feel helpful friends can help like share support small series ~

Your support, my motivation; I wish you a bright future and continuous offer!!