8.2.1-optimize the SELECT statement

 

8.2.1. Optimize the SELECT statement

 

Queries composed of select statements execute all the queries in the data. Optimization of such statements is at the top, whether it is tuning the second-level response time of dynamic web pages, or reducing
The time when a massive overnight report was generated.

In addition, the SELECT statement optimization technology is also applicable to create table... as select, insert into... select, and delete statements with where.
These statements also have other performance overhead because these operations are also written during read operations.

MySQL cluster supports the optimization of join Pushdown. The Join Operation is sent to the actual MySQL cluster data node, so that the join operation can be distributed to achieve parallel operations.

For more information about this optimization, see conditions for NDB Pushdown joins.

Accelerate select statements

The following methods are used to optimize the query:

1. to make the slow select... where queries become faster. The first thing to do is to check whether you can add an index. adding indexes to the columns used in the where statement can accelerate estimation and filter.
To avoid wasting disk space, build a small index to speed up most of the related queries in your application.

Indexes are also important for queries that reference other tables. For example, you can use a query similar to joins and Foreign keys. You can use the explain statement to check which index the SELECT statement uses.
View Section 8.3.1, "how MySQL uses indexes" and section 8.8.1, "optimizing queries with explain"

2. separate and adjust the sub-parts of the query. For example, function calling takes a lot of time. A function calls each row in the result set once, or only calls all rows in the table once. This greatly improves the efficiency,
This depends on the composition of the query.

3. Minimize full table queries, especially for large data tables.

4. Periodically execute the analyze TABLE statement to keep the table statistics up-to-date so that the optimizer has enough information to generate an efficient execution plan.

5. Learn optimization technology, indexing technology, and storage engine configuration parameters. InnoDB and MyISAM both have a series of manuals for efficient query execution.
For more information, see section 8.5.6, "optimizing InnoDB querire" and section 8.6.1, "optimizing MyISAM queries ".

6. in MySQL and later versions, you can optimize a single query transaction for the InnoDB data table through read-only transactions. section 8.5.3, "optimizing InnoDB read-only transactions"

7. Avoid conversion that makes the query statement hard to understand, especially when the optimizer performs the same conversion.

8. If the basic manual is difficult to solve the performance problem, read the explain execution plan output, go deep into the internal details, and adjust your index (where statement, join statement ).
If your skills are superb, reading the execution plan may be your first step in optimizing each query.

9. adjust the memory size that MySQL uses to cache. the InnoDB buffer pool, mysiam key cache, and MySQL query cache are effectively used, and repeated query execution is faster, because after the first query, results are retrieved from the memory (MySQL query cache ).

10. Even if a query uses a cache that has run fast enough, you may continue to optimize it to make it use less space for caching, making your program more scalable. Scalability means that your program can accommodate more users at the same time,
Larger requests, but there will be no major performance gap.

11. When dealing with the lock issue, your query speed may be affected by other sessions that are using tables at the same time.

8.2.1-optimize the SELECT statement