This is my 11th original article

A friend said to talk about SQL tuning, in fact, this kind of article is all over the web. Want to say some of their own understanding, and afraid to write smelly and long. So put the outline in front of you, and if you don’t have any help you can just turn it off.

SQL optimization, a total can be divided into three layers: database system, database design, programming. I divided it into table design optimization, query optimization, index optimization, architecture optimization and business optimization according to the order of easy and difficult.

directory

Generally speaking, SQL optimization refers to query optimization, but the most pit is that our opponents encounter uncomfortable routines, many challenges usually can not be solved with SQL skills.

(1) Reasons for affecting performance

SQL performance can be affected for a number of reasons, the most troubling being on the demand side. The business side doesn’t care how your SQL is written, they just want results, and sometimes even the technical side can make some weird requests.

There are also a lot of problems on the architecture side, typical problems are paging, excessive degradation (business wide table), etc.

In fact, the data side of the problem is relatively easy to reach a consensus, and solve.

Of all the reasons for poor SQL performance, SQL itself is the easiest to address.

② Optimization of table design

  • Coding rule design:

The tree structure of the development design usually only adds a parent ID, but we can also add business code and hierarchy identification. Business codes generally have coding rules. Take the code of Administrative regions in China as an example. Every two bits represent a layer.

It is also very easy to extract the dimensions of the three level regions when building storehouses, and limit the dimensions of the layer.

  • Extended information sheet \

The core information is stored in the main table, and the frequently written and newly added fields are stored in the extended information table to decouple some services. Design is a balanced process, with multiple associations across tables and bloaty results when they are all put together. \

  • Field selection

Tips: Large text, pictures suggested to save the file, the database can save the path.

③ Query optimization

\ \

If you have a cannon, don’t use a pistol. Queries go to the index

2, drowning three thousand only take a ladle, do not select *

Database is used to save, not to calculate, check check, do not calculate

1

Tools (MySQL as an example) :

 \

SQL > select * from ‘Explain’;

You can find the main slowdowns, such as a table with a large amount of data and high resource consumption. \

2. Check the resource consumption of each step through Profile: \

You can find out the resource consumption of each step, how much data is involved, how much time is consumed, and so on.

3, Use the Optimizer Trace table to view how the SQL Optimizer generates an execution plan.

You can basically use Explain and profile to understand all the costs of SQL execution, and then optimize according to the following principles. By enabling Optimizer Trace, you can see if your optimizations are correct:

2

1. Do not use the query preceded by %

SQL > alter table alias = match

3. () 3

4, null value judgment,! The = or <> operators cause a full table scan

5. Use between and exists instead of in and not exists instead of not in

6. Disable select *

Join a small result set to a large result set

8. Use limit

9, reduce where field calculation operations

④ Index optimization

In the SQL optimization tip above, the reason for the prohibition is basically to invalidate indexes. Indexes are so important that they are covered in a separate chapter.

3

Index design:

1. The number of indexes in a single table does not exceed 5;

2. The number of a single index field does not exceed 5.

3. Do not build indexes for fields that are frequently modified. \

4. The greater the differentiation, the better the index effect; Gender field index, equal to not built;

5. Index fields should be as short as possible, and numeric fields should be selected as far as possible

6. Build indexes on fields that are frequently queried, sorted, grouped, and where determined

7, frequently delete data table, regular cleaning

8. Replace subqueries with joins

4

Use of indexes:

Disallow mathematical operations on indexed columns;

When using a federated index, query in order (the leftmost principle of indexing);

Try to satisfy both query and sort in the index; \

String indexes use prefix indexes, which are no longer than 10 characters long.

When index join, use the same field type;

⑤ Architecture optimization

The structure level is not moved easily, the movement is a big project, its core is separated: \

1. Master/slave + Read/write separation

2. Separate database and table

The database transaction is mainly read and write, these two separate read operations can occupy all the resources of the server, naturally fast.

Database segmentation is vertical segmentation. According to the business domain, the closely related tables are divided into the same database. Originally, a database is vertically segmented into N business libraries, so the pressure of each database is reduced and the efficiency is high.

Table segmentation is horizontal segmentation. A table is horizontally divided into N small tables according to the rules. The amount of data in a single table becomes smaller and the query efficiency becomes higher.

Click me to view the article “Single table large data processing – Horizontal cutting method”

⑥ Service Optimization

As mentioned in the beginning, the most difficult problems we encounter are on the demand side, and they are more intractable or even impossible to solve. Here are a few common tips:

5

1, Demand conversion \

Find out what the other person is really looking for. Sometimes you just want to find evidence for your point.

2. Alternatives

The data logic of a new indicator is too complicated, so you can consider replacing it with other existing indicators to express its meaning.

3. Lower expectations

He said that when he was hungry, he didn’t have to give a bowl of abalone fried rice, but a steamed bun was also ok.

If all else fails, buy an ice cream and stick it in his mouth.

It’s on. You’re the best