Supports SQL tuning and SQL TUNING

Supports SQL tuning and SQL TUNING

A friend of mine found me a few days ago and said that an SQL Performance problem exists to see if it can be optimized. The process is as follows:

Snow Leopard 9:35:10

Are you there?

Orchid island master 15:07:39

I forgot. What's the matter?

Snow Leopard 15:07:49

Hmm

Snow Leopard 15:07:54

Database Optimization Problems

Orchid island master 15:08:04

Oh, you said.

Snow Leopard 15:09:09

Select distincta. suite_no, B. bd_nm, B. crt_date from (select suite_no from all_suite where cus_id = 1) a left join

(Select B. suite_no, B. bd_nm, B. crt_date from building B, customer c where c. cus_no = B. cus_no

And c. cus_serial = '000000' and B. back_date is null) B on B. suite_no = a. suite_no;

Snow Leopard 15:09:35

Snow Leopard 15:09:58

If the data level is tens of millions, it will be slow.

Snow Leopard 15:10:35

Is there a better way to write it?

Orchid island master 15:10:58

Which is tens of millions?

Snow Leopard 15:11:10

Building table

Orchid island master 15:11:25

What about customer?

Snow Leopard 15:11:26

The clustomer table is small.

Snow Leopard 15:11:36

Tens of thousands of data entries

Snow Leopard 15:12:00

All_suite this table also contains tens of thousands of data records.

Orchid island master 15:13:38

What about the index on your building table?

Orchid island master 15:13:40

Let me see?

Snow Leopard 15:15:10

Building_idx1 (bd_nm, area_id, cus_no, back_date, suite_no)

Building_idx2 (crt_date)

Building_idx3 (back_date)

Building_pkey (sid)

Snow Leopard 15:16:27

Can I optimize the building index?

Orchid island master 15:16:36

What is the selectivity of this column?

Orchid island master 15:16:48

That is, how many duplicate values are there?

Snow Leopard 15:17:12

Not many crt_date

Orchid island master 15:17:42

This is not the case. cus_serial

Snow Leopard 15:17:57

Area_id, cus_id, and suite_no have multiple duplicate values.

Snow Leopard 15:18:27

This is not repeated

Snow Leopard 15:18:30

They are all unique.

Snow Leopard 15:18:44

Is this the table of customer?

Snow Leopard 15:18:48

He is not an index

Orchid island master 15:19:17

What is the final result?

Snow Leopard 15:19:37

Query data?

Orchid island master 15:19:51

Yes, the result set.

Snow Leopard 15:21:22

Query all corresponding all_suite rooms for each cus_serial and find the last entry personnel.

Snow Leopard 15:21:52

The number of rooms is not large.

Orchid island master 15:22:29

How many customers does a cum_serial correspond?

Orchid island master 15:23:38

Probably?

Snow Leopard 15:23:57

One-to-one

Orchid island master 15:24:21

A cus_serial corresponds to a customer?

Orchid island master 15:25:38

Building_idx1 has too many index columns.

Snow Leopard 15:26:02

Yes

Snow Leopard 15:26:37

Several indexes are retained.

Orchid island master 15:26:54

No. Create an index on cus_no.

Orchid island master 15:27:03

How long will the result be returned?

Snow Leopard 15:28:01

About 30 s

Orchid island master 15:28:12

Well.

Orchid island master 15:28:39

See an index as I said. It is estimated that the index will not exceed 1 s.

Orchid island master 15:28:44

Create.

Snow Leopard 15:29:10

OK

Orchid island master 15:29:16

In this case, your SQL optimization space is still relatively large.

Orchid island master 15:29:48

Results should be generated in a maximum of several hundred ms.

Orchid island master 15:29:53

Optimized.

Snow Leopard 15:32:57

Yes

Snow Leopard 15:33:04

0.5 seconds

Snow Leopard 15:33:22

Faster than before

Orchid island master 15:34:11

In the last 0.5 s, did you build the index according to the method I mentioned?

Snow Leopard 15:34:25

After the index is created

Snow Leopard 15:34:37

Yes

Orchid island master 15:34:51

Oh.

Orchid island master 15:35:12

Others. I don't know much about your data and it's not very fine-tuned.

Orchid island master 15:35:44

It should be faster.

Snow Leopard 15:36:34

Okay.

Snow Leopard 15:36:36

Let's see

Orchid island master 15:37:16

For example, the indexes of the other two tables

Snow Leopard 15:37:31

Hmm

Orchid island master 15:37:43

The full table scan, although the table is small, should be a small proportion for 0.5 s.

Snow Leopard 15:37:55

Understand

Orchid island master 15:38:04

Right.

Snow Leopard 15:41:30

Optimized to 0.18 seconds

Snow Leopard 15:44:52

0.07 seconds

Orchid island master 15:45:00

All_suite?

Orchid island master 15:45:07

Well, that's almost the case.

Orchid island master 15:45:20

Dozens of ms

Snow Leopard 15:45:28

All_suite suit_no is indexed

Snow Leopard 15:45:40

This is almost the case.

Orchid island master 15:45:44

Well, it's almost done.

At this point, the other party is quite satisfied, and the performance has also been greatly improved. In view of many factors, we have made necessary processing on the document and recorded it here to share it with you.