High Performance MySQL chapter 6th query performance Optimization (2)

Limitations of Union:

MySQL cannot push the limit condition from the outer to the inner layer.

Such as:

Can be optimized to:

Parallel execution:

MySQL cannot use multi-core features to execute queries in parallel.

Hash Association:

MySQL does not support hash associations.

Skip Index Scan

Not supported. Tested, supported in version 5.6.

Restrictions on updating on the same table:

MySQL queries and updates to the same table are not allowed at the same time. This is not actually the optimizer's limit, and the SQL below cannot be run, which attempts to record the number of similar rows in the two tables into the field cnt :

The above restrictions can be bypassed by generating a table, because MySQL This table will only be treated as a temporary table. In fact, this executes two queries: One is a select statement in a subquery and the other is a multi-table associated update, except that the associated table is a temporary table. The subquery is completed before the update statement opens the table, so the following query will execute normally:

Hint Tips:

DELAYED: For insert and replace. Returns immediately after execution, and then the data is written to the hard disk when it is idle. More appropriate for logging.

Straight_join: Defines the association order.

Sql_small_result,sql_big_result: For querying, the size of the flag result set, the boot sort operation in memory or hard disk execution.

Sql_buffer_result: Put the result of the query into a temporary table and release the table lock as soon as possible.

Sql_cache,sql_no_cache: whether to cache.

Use Index,ignore Index,force index: Enforces indexes, and does not apply.

Refine the associated query:

• Try to make sure that the on column has an index.
• Make sure that group by and order by only involve a table column. This allows the index to be used. PS (Order by good understanding, group by their own thinking will also understand that if the group by does not have an index, must be the whole table and sort, or use a temporary table to do group by)
• Internal MySQL may automatically convert the equivalent distinct and GROUP by syntax.

User-defined variables:

This chapter is a unique feature of MySQL, not the SQL standard, you can use the custom variables in the query, to achieve line numbers, statistics and other functions. Here I did not look closely, listed two articles can be consulted:

Http://www.cnblogs.com/guaidaodark/p/6037040.html

http://blog.csdn.net/muzizhuben/article/details/49449853

High Performance MySQL chapter 6th query performance Optimization (2)