Shulou(Shulou.com)06/01 Report--

This article mainly introduces what the big table in mysql means. It is very detailed and has certain reference value. Friends who are interested must finish reading it.

Preface

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

When the data of a table exceeds 10 million rows, it will affect the database.

When the table data file is large, the table data file is more than 10G (data value relative to hardware)

The influence of the big table

The influence of large table on query

Slow query: it is difficult to filter out the required data within a certain period of time

(Eg: display orders, few sources, low differentiation, large disk IO, reduced disk efficiency, slow query)

The influence of large Meter on DDL Operation

1. It takes a long time to set up the index

Risk:

MYSQL version

5.5 although the table will not be locked, it will cause master-slave delay

2. It takes a long time to lock the table to modify the table structure.

Risk:

It will cause long master-slave delay.

Affect normal data operations

How to deal with large tables in the database

1. A large table is divided into several small tables

Difficulties:

Selection of primary key of sub-table

Query and statistics of cross-partition data after sub-table

2. Historical data archiving of large tables

Advantages:

Reduce the impact on front and back end business

Difficulties:

Selection of archiving time points

How to perform archiving operation

What is a transaction?

Transaction is 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 a separate unit of work.

Transaction requirements meet: atomicity, consistency, isolation, persistence

Atomicity of transaction

A transaction must be regarded as an inseparable minimum unit of work, and all operations in the whole transaction either commit successfully or fail. For a transaction, it is impossible to perform only part of the operations.

Eg:

1. Check whether the balance in the financial account is higher than 2000 yuan.

2. Subtract 2000 yuan from the balance of the financial account

3. Add 2000 yuan to the active deposit account

All operations in the entire transaction are either committed successfully or all failed rollback.

Consistency of transactions

Consistency means that a transaction transforms a database from one consistency state to another, and the integrity of the data in the database is not broken before and after the transaction.

Isolation of transactions

Isolation requires that one transaction modifies the data in the database and is not visible to other transactions until the commit is completed.

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

Uncommitted read (READ UNCOMMITED)

Read submitted (READ COMMITED)

Repeatable read (REPEATABLE READ)

Serializable (SERIALIZABLE)

Persistence of transactions

Once a transaction commits, its changes are saved to the database forever, and even if the system crashes, the committed modification data will not be lost.

What is the big deal?

Transactions that run for a long time and operate a lot of data

Risk:

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

Rollback takes a long time

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

How to deal with big business?

Avoid dealing with too much data at once

Remove unnecessary SELECT operations in a transaction

The above is all the contents of what the big table means in 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.