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

This article mainly introduces what are the advanced knowledge points of MYSQL. What is introduced in this article is very detailed and has a certain reference value. Interested friends must finish reading it!

Article catalogue

1 preface

1.1 Database architecture

1.2 Monitoring Information

2 factors affecting the database

2.6.1 what is a transaction

2.6.2 atomicity of transactions (ATOMICITY)

2.6.3 transaction consistency (CONSISTENCY)

2.6.4 isolation of transactions (ISOLATION)

2.6.5 persistence of transactions (DURABILITY)

2.6.7 what is a big deal?

2.5.1 impact of large tables on queries

2.5.2 the influence of large table on DDL operation

2.5.3 how to deal with large tables in a database

2.1 Super high QPS and TPS

2.2 massive concurrency and ultra-high CPU utilization

2.3 disk IO

2.4 Network Card Traffic

2.5 large table

2.6 Major transactions

1 preface

A large part of the pressure on the server comes from the performance of the database. If there is no stable database and server environment, then the server is prone to some failures or even downtime, and the consequences are inestimable. Therefore, database performance optimization is essential.

1.1 Database architecture

The general database architecture is a master server, which carries several or more than a dozen slave servers for master-slave synchronization. when the master server goes down, programmers need to manually select the latest data slave server to replace the master server. then synchronize the new master server. However, sometimes because of the large number of servers, this process is quite time-consuming, and in this process is also a challenge to the capacity of the network card.

1.2 Monitoring Information

QPS & TPS: the higher the number the better.

Concurrency: the number of requests processed at the same time.

CPU usage: the lower the better.

Disk IO: the higher the read and write performance, the better.

Note: in general, before and after the promotion activities, it is best not to backup the database on the main database, or to cancel such plans before large-scale events, as this will seriously damage the performance of the server.

2 factors affecting the database

There are many factors that affect the database, such as: sql query speed, server hardware, network card traffic, disk IO and so on. We will talk about it in detail later. Let's introduce some of the monitoring information feedback to us, and how we should optimize it.

2.1 Super high QPS and TPS

Due to the inefficiency of SQL, it often causes ultra-high risk of QPS and TPS. During the general promotion period, the number of visits to the website will be greatly increased, and the QPS and TPS of the database will also be improved.

What is QPS: the number of queries processed per second. For example, if we have a cpu and 10ms can handle one sql, then 1s can handle 100s of sql,QPS repeatable readable > serializable.

Unsubmitted read < submitted read < repeatable read < serializable

The highest level of isolation. To put it simply, locks are added on every piece of data read, so it may lead to a large number of lock timeouts and lock occupancy problems, so we rarely use this isolation level in real business. We will not consider using this isolation level unless data consistency is strictly required and it is acceptable to use this isolation level without concurrency.

At a higher level than committed read, in a repeatable isolation level transaction, the data in a table is queried in an uncommitted transaction, and a piece of data is inserted into the table and committed in another transaction. However, when you go back to the newly uncommitted transaction and query the table again, the result is the same as the last query, and the newly inserted data is not found.

But the data can be found in the isolation level that has been submitted for reading.

View the isolation level statement for the current database:

Show variables like'% iso%'

Modify the current database isolation level statement:

Set session tx_isolation='read-committed'

The default isolation level in many data is that the data can only be read after the transaction is committed, that is, the transaction is invisible to the outside world.

Uncommitted transactions are visible to the outside world, which is what we often call dirty reading, and the queried data is called dirty data.

Uncommitted read (READ UNCOMMITED)

Read submitted (READ COMMITED)

Repeatable read (REPEATABLE READ)

Serializable (SERIALIZABLE)

Isolation:

Concurrency:

2.6.5 persistence of transactions (DURABILITY)

Definition: once a transaction commits, its changes are permanently saved to the database. Even if the system crashes, the modified data that has been submitted will not be lost (excluding physical factors such as disk corruption).

Example:

User An in the bank deposits 1000 yuan in the account. After the transaction is committed, even if the banking system collapses, the 1000 yuan in the An account will not change unless An operates the balance, which is the persistence of the transaction.

2.6.7 what is a big deal?

After talking so much, what is a big deal?

A large transaction refers to a transaction that takes a long time to run and operates more data. For example, if a wealth management product counts the income of each user the day before, then if you need to count the income of all users and update them to the user balance, hundreds of millions of updates will take several hours. If a rollback of a failure occurs midway, the time required for the transaction will be even more inestimable, not including locking the user's balance during the update process. It makes it impossible for all users to use the balance.

What are the risks of big business:

Locking too much data, causing a lot of blocking and lock timeouts

It takes a long time to roll back

The execution time is long, which is easy to cause master-slave delay.

How to avoid big business?

Avoid dealing with too much data at once.

Remove SELECT operations from unnecessary transactions.

Can do the above two points can basically avoid the emergence of big business.

These are all the contents of the article "what are the Advanced knowledge points of MYSQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.