MySQL Tens database table optimization?

1. The capacity of the data: how many data in 1-3 years, how many bytes per piece of data;

2. Data items: whether there are large segments, the values of those fields are often updated;
3. Data query SQL Condition: Which data item's column name often appears in where, GROUP by, ORDER BY clause medium;
4. Data Update class SQL Condition: How many columns often appear in the WHERE clause of the update or delete;
The statistic ratio of 5.SQL quantity, such as: select:update+delete:insert= how much?

6. What is the magnitude of the total daily amount of execution expected for large tables and associated SQL?
7. Data in the table: Update the main business or query-oriented business
8. What database tutorial do you intend to use as a physical server and database server architecture?
9. How is concurrency?
10. Storage Engine Select InnoDB or MyISAM?

Roughly understand the above 10 questions, as to how to design such a large table, should be clear everything!

As for optimization, if it means creating a table and not changing the structure of the table, it is recommended that the InnoDB engine, the use of point memory, reduce disk IO load, because IO is often the bottleneck of the database server

In addition to the optimization of the index structure to solve performance problems, we recommend that you modify the class of SQL statements, so that they are faster, forced to rely only on the way the organization structure, of course, if this is the case,
The index has been created very well, if read-oriented, you can consider opening Query_cache,

and adjust some parameter values: sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size

Other people suggest:

1. Index, avoid scanning, based on the primary key of the search, billions of data is also very fast;
2. Anti-normal design, to space for time to avoid join, some join operations can be implemented in code, there is no need to use the database to achieve;

1, only to return the required data

Return data to the client needs at least database extraction data, network transmission data, client receive data and client processing data, and so on, if the return of unwanted data, will increase the server, network and client ineffective labor, the harm is obvious, to avoid such incidents need attention:

A, landscape, do not write select * statements, but select the fields you need.

B, portrait, a reasonable write WHERE clause, do not write the SQL statement without where.

C, note the WHERE clause in the SELECT INTO, because select into inserts the data into the temporary table, which locks some system tables, and if the WHERE clause returns too much data or is too slow, it can cause the system table to lock up for a long time, blocking other processes.

D, for aggregate queries, you can further qualify the returned rows with the HAVING clause.

　　

2, as little as possible to do repetitive work

This point is the same as the previous point, is to minimize the ineffective work, but this focus on the client program, you need to pay attention to the following:

A, control the multiple execution of the same statement, especially some basic data multiple execution is a lot of programmers pay little attention to.

B, reduce the number of data conversion, may require data conversion is the problem of design, but the reduction of times is the programmer can do.

C, to eliminate unnecessary subqueries and join tables, subqueries in the execution plan generally interpreted as an external connection, redundant connection table brings additional overhead.

D, merging multiple updates for the same condition of the same table, such as

UPDATE EMPLOYEE SET fname= ' haiwer ' WHERE emp_id= ' vpa30890f '

UPDATE EMPLOYEE SET lname= ' yang ' WHERE emp_id= ' vpa30890f '

The two statements should be merged into one of the following statements

UPDATE EMPLOYEE SET fname= ' haiwer ', lname= ' Yang '
WHERE emp_id= ' vpa30890f '
E, update operations do not split into the delete operation +insert operation form, although the same function, but the performance difference is very large.

F, do not write some meaningless query, such as: SELECT * from EMPLOYEE WHERE 1=2

　　

3, attention to business and locks

The transaction is the database application and the important tool, it has the atomicity, the consistency, the isolation, the persistence these four attributes, many operations we all need to use the transaction to guarantee the data correctness. In the use of transactions we need to do to avoid deadlocks, as far as possible to reduce congestion. Particular attention needs to be paid to:

A, the transaction operation process to be as small as possible, can split the transaction to split apart.

B, the transaction operation process should not have interaction, because the interaction is waiting, the transaction does not end, may lock a lot of resources.

C, the transaction operation process to access the object in the same order.

D, increasing the efficiency of each statement in a transaction, using indexes and other methods to improve the efficiency of each statement can effectively reduce the execution time of the entire transaction.

E, try not to specify the lock type and index, SQL Server allows us to specify the type and index of locks used by the statement, but in general, the lock type and index chosen by the SQL Server optimizer are optimal under the current data volume and query conditions, and we may specify only in the present case, But data volumes and data distributions will change in the future.

F, the query can be at a lower isolation level, especially when reporting queries, you can choose the lowest isolation level (uncommitted read).