Optimizing design to improve the performance of SQL class database

Objective

In a project, the unity of technology is the most important, the database design is the focus of the emphasis. NoSQL is currently the most popular database, but its practicality and functionality are far less useful than SQL database.

Many of the real-world SQL database is criticized for the performance of most of the problem stems from the programmer's unreasonable design, a good design can make SQL class database to improve the performance of several times .

1. Optimization of details

The field is set to NOT NULL as much as possible.

Standardize the size of the field, the smaller the better.

Table name specification prefix.

A table tries to store an object as much as possible.

Char is always more efficient than varchar.

Timestamp is one-fold smaller than datetime.

Avoid string IDs.

A single query ends with limit 1.

Do not use MySQL built-in functions because the query cache is not established.

Avoid DNS resolution by using IP instead of domain name as the path to the database.

2. Using SQL built-in features

For example Trigger,procedure,event ... And so on, can effectively reduce the use of back-end code, but is not suitable for handling high-triggered projects.

3. Select the appropriate storage engine

The most common is InnoDB and MyISAM. The difference between the two please own Baidu.

4. Save the data to memory

Read data from memory to minimize disk operation, and the relevant content will be explained in detail later.

5. Improve disk read and write speed

6. Make full use of indexed index

Mysql> DROP INDEX index_name on tab;  Add indexmysql> ALTER TABLE tab DROP INDEX index_name; Delete Index

7. Using a Memory disk

Now that the infrastructure is strong, you can migrate the SQL directory to a memory disk.

8. Read/write separation design

As systems become more and more large, especially when they have poor SQL, a single database server is often not enough to handle the load. But multiple databases mean duplication, unless you detach the data. More generally, this means establishing a master/slave replica system where the program writes all UPDATE, insert, and delete change statements to the main library, and all select data is read from the database (or multiple from the database).

Although conceptually simple, it is not easy to achieve a reasonable and precise implementation, which may require a lot of code work. Therefore, even if you start with the same database server, you should plan to use a separate DB connection for read and write operations in PHP as early as possible. If the work is done correctly, the system can scale to 2, 3, or even 12 servers, with high availability and stability.

9. Using memcache or redis

The previous blog has a related introduction.

10.SQL Database Decentralized layout

Spread the database across multiple servers, sharing the workload of the database.

Optimizing design to improve the performance of SQL class database