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

This article introduces you to the database sub-table and optimization need to pay attention to, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Why do you divide tables when designing a database? Sub-library? In general, how much data begins to be divided into tables? Sub-library? What is the purpose of sub-database and sub-table? What is a vertical split of the database? Horizontal split? Zoning, etc.

One: why do you want a separate table?

When the data of a table reaches millions, it takes more time for you to query at one time, and if you have a federated query, you may die there. The purpose of sub-table is to reduce the burden of the database and shorten the query time. In daily development, we often encounter the situation of large tables, the so-called large tables are tables that store millions or even tens of millions of records. Such tables are too large, resulting in database query and insert time is too long, poor performance, if federated queries are involved, the performance will be even worse. The purpose of table partition and table partition is to reduce the burden of the database and improve the efficiency of the database. Generally speaking, it is to improve the efficiency of table addition, deletion, modification and query. The amount of data in the database is not necessarily controllable. without dividing the database and tables, with the development of time and business, there will be more and more tables in the database, and the amount of data in the tables will become larger and larger. Accordingly, the cost of adding, deleting, modifying and querying will become more and more expensive. In addition, because it is impossible to carry out distributed deployment, and the resources of a server (CPU, disk, memory, IO, etc.) are limited, the amount of data and data processing capacity that the database can carry will encounter bottlenecks.

Second: the scheme of sub-table

1. If you are doing mysql cluster, some people will ask mysql cluster, does the root table have anything to do with it? Although it is not a sub-table in the actual sense, but it enables the role of sub-table, what is the significance of clustering? To lighten the burden on a database, to put it bluntly, is to reduce the number of sql in the sql queue. For example, if you have 10 sql requests, if you put them in the queue of a database server, it will take a long time for him to wait. If you assign these 10 sql requests to the queue of 5 database servers, there are only 2 of them in the queue of one database server. Is the waiting time greatly shortened?

Installation, configuration, and read-write separation of linux mysql proxy

Installation and configuration of mysql replication as master and slave, as well as data synchronization

Advantages: good scalability, no complex operations after multiple sub-tables (php code)

Disadvantages: the amount of data in a single table remains the same, the time spent on one operation is still that much, and the hardware cost is high.

two。 What are the database optimizations? What do you need to pay attention to respectively?

The principle of SQL optimization is to minimize the number of BLOCK to be read in a single operation, that is, to achieve maximum data throughput in the shortest possible time.

Maladjusted SQL can usually be cut from the following points:

Check for defective SQL and consider whether there is any optimizable content in its writing

Check the subquery to consider whether the SQL subquery can be rewritten in a simple join

Check the use of optimized indexes

Consider the optimizer of database

Avoid SELECT * FROM table statements and identify the fields you want to check out.

In a SQL statement, the more database records a where condition filters and the more accurate the location, the more the where condition should be moved forward.

Use index overrides whenever possible when querying. That is, a composite index is established on the fields of SELECT, so that only index scanning is performed during the query, and no data blocks are read.

It is recommended not to use SELECT COUNT () and select top 1 statements when judging whether there are eligible records or not.

Using the inner limit principle, when spelling SQL statements, the query conditions are decomposed, classified, and limited in the innermost layer of SQL statements as far as possible, so as to reduce the amount of data processing.

You should absolutely avoid using expressions in the order by clause.

If you need to read data from associated tables, there are generally no more than 7 associated tables.

When using IN and OR carefully, you need to pay attention to the amount of data in the In collection. It is recommended that there are no more than 200 data in the collection.

Use

Replace, > replace with > =, < use

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.