What is a large table in mysql? What is a big deal? How to deal with it?

The content of this article is about what is a big table in mysql? What is a big deal? How to deal with it? It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Preface

Relatively speaking, under what circumstances can a database table be called a "large table"?

• When the data in a table exceeds tens of millions of rows, it will have an impact on the database

• When the table data file is huge, the table data The file exceeds 10G (the data value is relative to the hardware)

The impact of large tables

The impact of large tables on queries

Slow query: It is difficult to filter within a certain period of time Obtain the required data
(Eg: display orders, few sources, low differentiation, large amount of disk IO, reduced disk efficiency, slow query)

The impact of large tables on DDL operations

1. It takes a lot of time to create an index Long-term

Risk:

MYSQL version < 5.5 will lock the table when creating an index
MYSQL version>= 5.5 Although the table will not be locked, it will cause master-slave delay

2. Modifying the table structure requires locking the table for a long time

Risk:

Will cause long master-slave delay
Affects normal data operations

How to deal with it Large tables in the database

1. Split database and table. Divide a large table into multiple small tables

Difficulty:

Selection of primary keys for split tables
Query of cross-partition data after splitting tables And statistics

2. Historical data archiving of large tables

Advantages:

Reduce the impact on front-end and back-end business

Difficulties:

Archiving time Point selection
How to perform archiving operations

What is a transaction?

• Transactions are one of the important features that distinguish the database system from all other file systems

• A transaction is a set of atomic SQL statements. Or an independent unit of work

Transaction requirements comply with: atomicity, consistency, isolation, durability

Atomicity of transactions

A transaction must be considered The smallest unit of work that cannot be separated. All operations in the entire transaction are either submitted successfully or all fail. For a transaction, it is impossible to perform only part of the operations.

Eg:
1. Check whether the balance in the financial management account is higher than 2,000 yuan
2. Subtract 2,000 yuan from the balance of the financial management account
3. Add to the active deposit account 2000 yuan

All operations in the entire transaction are either submitted successfully, or all operations fail and are rolled back.

Transaction consistency

Consistency means that a transaction converts the database from one consistency state to another consistency state. The integrity of the data in the database is not destroyed before the transaction starts and after the transaction ends.

Isolation of transactions

Isolation requires that the modification of data in the database by a transaction is not visible to other transactions before the submission is completed.

Four types of levels defined in the SQL standard (isolation from low to high) (concurrency from high to low)

READ UNCOMMITED
Committed Read (READ COMMITED)
Repeatable Read (REPEATABLE READ)
Serializable (SERIALIZABLE)

Transaction Persistence

Once a transaction is committed, its modifications will be saved forever In the database, even if the system crashes at this time, the modified data that has been submitted will not be lost.

What is a big deal?

Transactions that take a long time to run and operate a lot of data

Risk:

Lock too much data, causing a lot of blocking and lock timeouts
Return Rolling takes a long time
The execution time is long and it is easy to cause master-slave delay

How to handle large transactions?

• Avoid processing too much data at once

• Move out unnecessary SELECT operations in the transaction

The above is the detailed content of What is a large table in mysql? What is a big deal? How to deal with it?. For more information, please follow other related articles on the PHP Chinese website!