High performance MySQL (6) query slow and refactoring

Only good library table structure, reasonable index is not enough, we also need reasonable design inquiries, goes hand in hand, a lot of can give full play to the advantages of MySQL.

Why is the query slow?

Each query consists of a series of subtasks, each of which consumes a certain amount of time. This we have introduced in the previous query analysis, of course, there are additional factors, such as network, CPU computing, statistics, execution plan, lock wait operation, or the bottom of the engine in the call memory, CPU operations, I/O operations, such as the consumption of time.

The purpose of the optimization query is to reduce and eliminate the time that these operations take.

The most basic reason for poor query performance is that there is too much data to be accessed, and most performance queries can be optimized by reducing the amount of data accessed, typically with 2 simple analysis methods:

1, to confirm that the application has returned a large number of more than needed data, that is, access to too many rows, and sometimes because of access to too many columns, which will add a lot of extra overhead, including, network, CPU, memory and so on.

Some common examples are:

A, the query does not need to record

A common mistake is often that MySQL will only return the required data, but actually return all the query results before the calculation, a simple and effective solution is to add limit after the query.

B, multiple table associations return all columns

For example, to find all the actors that appear in the movie Hreo, don't write that.

SELECT * FROM actor
inner join Film_actor using (actor_id)
inner join film using (film_id)
where film.title = "h REO ";

This returns all the columns for the three tables, and should return only the columns that you want

Select actor.* from actor
inner join Film_actor using (actor_id)
inner join film using (film_id)
where Film.title = "Hreo";

C, do not always take out all the columns

SELECT * from actor ....

D, duplicate query the same data

For example, in the user comments where the user needs to query the avatar, if the user many comments, may repeatedly query this data, you can first cache, this will be better.

2, confirm the MySQL service layer before returning to retrieve a large number of more than needed data rows.

If the query to return results to scan too much data, then it is not appropriate, generally look at 3 indicators:

A, Response time

Response time is divided into service time and queue time. This is difficult to subdivide, and if it is in a reasonable value, then it is acceptable.

b, scanned rows and returned rows

This can explain to some extent how efficiently the query finds the data it needs. Ideally, the scanned rows and rows returned are the same, but in practice this is difficult, especially when associating queries.

C, scanned rows and types of access

The type column in the Explain statement reflects the access types. Scan from full table to index scan, range Scan, unique index query, constant reference, speed from slow to fast, scan rows from large to small. In general, we can add a suitable index to be very efficient.

CREATE TABLE ' emp5 ' (
  ' id ' int (one) not null DEFAULT ' 0 ',
  ' name ' varchar ' (m) not null,
  ' job ' varchar ' NULL,
  ' NUM1 ' int (a) default null,
  ' num2 ' int (a) default null,
  ' num3 ' int (a) default null,
  ' Job_num ' int (a) default null,
  ' d ' date default NULL,
  PRIMARY key (' id '),
  key ' Job_num ' (' job_num ')
engine= MyISAM DEFAULT Charset=utf8;

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/