Mandatory operations commonly used in MySQL (e.g. forced indexing)

MySQL's common hint

For friends who often use Oracle, it is possible to know that Oracle has a wide variety of hint features and provides many ways to optimize SQL statements. Similarly, in MySQL, there are similar hint functions. Here are some common ones.

Force index

Copy CodeThe code is as follows: SELECT * from TABLE1 Force INDEX (FIELD1) ...

The above SQL statements use only the indexes built on FIELD1, not the indexes on other fields.

Ignore Indexes IGNORE Index

Copy CodeThe code is as follows: SELECT * from TABLE1 IGNORE INDEX (FIELD1, FIELD2) ...

In the above SQL statement, the indexes on FIELD1 and FIELD2 in the TABLE1 table are not used.

Turn off query buffering Sql_no_cache

Copy CodeThe code is as follows: SELECT Sql_no_cache field1, field2 from TABLE1;

There are some SQL statements that need to query the data in real time, or not often (maybe one or two times a day), so you need to turn the buffer off, regardless of whether the SQL statement is executed, the server does not look in the buffer and executes it every time.

Force query Buffering Sql_cache

Copy CodeThe code is as follows: SELECT Sql_calhe * from TABLE1;

If the Query_cache_type in My.ini is set to 2, then query buffering is used only after Sql_cache is used.

Priority Operation High_priority
High_priority can be used in select and insert operations to let MySQL know that this operation takes precedence.

Copy CodeThe code is as follows: SELECT high_priority * from TABLE1;

hysteresis Operation Low_priority
Low_priority can be used in the insert and update operations to let MySQL know that this operation is lagging.

Copy CodeThe code is as follows: Update low_priority table1 set field1= where field1= ...

Delay inserting insert DELAYED

Copy CodeThe code is as follows: INSERT DELAYED into table1 set field1= ...

INSERT DELAYED into, is the client submits data to Mysql,mysql return OK state to the client. Instead of inserting data into a table, it is stored in memory waiting to be queued. When MySQL is free, insert again. Another important benefit is that insertions from many clients are lumped together and written into a block. This is much faster than performing many separate inserts. The downside is that you can't return an auto-incrementing ID, and when the system crashes, MySQL hasn't had time to insert the data, and that data will be lost.

Force Connection Order Straight_join

Copy CodeThe code is as follows: SELECT TABLE1. FIELD1, TABLE2. FIELD2 from TABLE1 straight_join TABLE2 WHERE ...

The SQL statement above shows that by Straight_join forcing MySQL to join the table in TABLE1, TABLE2 order. You can determine the connection order by Straight_join If you think it is more efficient to connect in your own order than the MySQL recommended sequence.

Forcing the use of temporary tables Sql_buffer_result

Copy CodeThe code is as follows: SELECT Sql_buffer_result * from TABLE1 WHERE ...

When the data in the result set of our query is relatively long, we can force the result set to be placed in the temporary table through the Sql_buffer_result option, so that the MySQL table lock can be released quickly (so that other SQL statements can query the records). And can provide a large set of records for clients for a long time.

Grouping using temporal tables Sql_big_result and Sql_small_result

Copy CodeThe code is as follows: SELECT Sql_buffer_result FIELD1, COUNT (*) from TABLE1 GROUP by FIELD1;

Typically used for grouping or distinct keywords, this option notifies MySQL that if necessary, the query results are placed in a temporary table and even sorted in a temporary table. Sql_small_result is similar to Sql_big_result and seldom used.

Ext.: https://www.jb51.net/article/49807.htm

Mandatory operations commonly used in MySQL (e.g. forced indexing)