Small ant learns MySQL performance optimization (4)--sql and index optimization--specific optimization methods

Optimization of Max ()

For example: Explain select Max (' Payment_date ') from payment; The number of rows that can be viewed to row is very much, which means that the entire table is scanned.

Add an index to payment_date.

CREATE INDEX Idx_payment on payment (' payment_date ');

Execute SQL statement explain select MAX (' Payment_date ') from payment order by Payment_date; You can see that rows is null.

Extra:select tables optinized away;

Does not scan the entire table, just take out through the index.

Indexes are arranged sequentially, so it is very clear how the last Payment_date value is to be known by the statistical information of the index. Therefore, the operation of the table is not necessary, which greatly increases the efficiency of the SQL execution, while minimizing the IO operation, so that the execution efficiency is basically constant, no matter how large the data volume is.

Idea: the query for Max () can be indexed to optimize it.

The difference between count (*) and COUNT (num)

If NUM has a behavior of null,count (num), it is not included. The Count (*) is included. That's why there are times when differences occur.

For example:

ID num

1 1

2 2

3 null

COUNT (*) =3 but count (ID) =2

Optimization of sub-queries

Sub-query with connection mode to pay attention to whether it is a 1-to-many relationship, if so, data results will be duplicated, need to use distinct to heavy.

Limit () optimization

Optimization Step 1: Use the indexed column or the primary key column for the order by operation. The truth is the same as Max ().

Optimization Step 2: Record the last returned primary key and use primary key filtering at the next query.

For example: Select File_id,description from file where file_id >55 and file_id <= the order by film_id limit 1, 5;

The disadvantage of this approach is that in case the primary key is missing, it is not necessarily 5.

d===== (￣▽￣*) B Center Optimization idea: Avoid scanning too many records when the data volume is large.

Small ant learns MySQL performance optimization (4)--sql and index optimization--specific optimization methods