A brief discussion on MySQL select optimization solution

Examples from real life

Have we seen that many query statements in the company are select * xxxx

The idea in my mind must be that others have written select *, so I will write it and save a lot of trouble.

Slow query

• First of all, think about whether the database query statement we use has too much data accessed.
• In fact, most low-performance queries can often be optimized by reducing the amount of data accessed.
• Because select * will bring additional I/O, memory and CPU consumption to the server

Three indicators of slow query overhead in the database

• Corresponding time
• Number of rows scanned
• Number of rows returned

If you go to index

select * from stu where id = 1;

The index will optimize the query and only return ten data. If there is no id index, it will be estimated that hundreds or thousands of rows of data will be accessed.

Tips: Use the EXPLAIN command in MySQL to view the number of rows corresponding to the rows

In fact, the best situation is to filter out unmatched records through where at the storage engine level. The second best situation is to cover the index hit scan and filter out unmatched records through where at the server level without returning to the table for query. The best situation is to return data from the data table and then filter out records that do not meet the conditions.

How to optimize

Use index coverage to put all the columns we need in the index, so that we can avoid going back to the table to query and can separate the table

3. Refactor the query (you can break down a large query into smaller ones)

For example: count, limit, max()

count

The best optimization for count is to add a summary table, because count inevitably needs to scan a large number of rows.

limit

Limit is very common when we do paging, as shown in the following code

select id from stu order by id limit 1000,20;

This statement will query 1020 data and then discard the first 1000 and return the 20 data from 1000 to 1020. The best way to optimize is to use the index, so that the limit query can be turned into a query with a known position.

Maximum value and minimum value min&max

First, let's imagine that if we use the primary key index, then the first value when we query is the minimum value we want to return. We can also use the primary key index and use limit to control the amount of data, thus achieving the effect of the min() function, thereby replacing min

select id 
from stu 
use index(primay) where address = 'bj' limit 1;

This will scan as few records as possible.

Finally, let’s do two questions to relax.

Find the second highest salary in the `Employee` table (Salary)
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
Results +---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
Answer: select max(Salary) SecondHighestSalary
from Employee 
where salary < (select max(salary)
from Employee)

Find all duplicate email addresses in the `Person` table.
+----+---------+
| Id | Email |
+----+---------+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
+----+---------+

Results+---------+
| Email |
+---------+
| [email protected] |
+---------+

Answer: select Email from Person 
group by Email 
having count(Email) >= 2;

This is the end of this article about MySQL select optimization solutions. For more relevant MySQL select optimization content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!