Other enforcement actions, priority actions are as follows:
MySQL Common hint
For friends who often use Oracle, it is possible to know that Oracle has a wide range of hint features and provides many ways to optimize SQL statements. Similarly, in MySQL, there are similar hint functions. Here are some of the common.
Mandatory Index FORCE Index
Copy Code code as follows:
SELECT * from TABLE1 FORCE INDEX (FIELD1) ...
The above SQL statement uses only the indexes built on the FIELD1, not the indexes on other fields.
Ignoring Indexes IGNORE Index
Copy Code code 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 Code code as follows:
SELECT Sql_no_cache field1, field2 from TABLE1;
There are some SQL statements that need to be queried in real time or infrequently (perhaps one or two times a day), so that the buffers need to be turned off, regardless of whether the SQL statement is executed or not, the server will not look in the buffer and execute it every time.
Force query Buffering Sql_cache
Copy Code code 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 if 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 Code code as follows:
SELECT high_priority * from TABLE1;
hysteresis Operation Low_priority
Low_priority can be used in insert and update operations to let MySQL know that this operation lags behind.
Copy Code code as follows:
Update low_priority table1 set field1= where field1= ...
Delay Inserts insert delayed
Copy Code code as follows:
INSERT delayed into table1 set field1= ...
INSERT delayed into, is the client submits the data to the Mysql,mysql to return OK status to the client. Instead of inserting data into the table, it is stored in memory waiting to be queued. When MySQL is free, insert again. Another important benefit is that inserts 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 automatically incremented ID, and the data will be lost when MySQL doesn't have time to insert data when the system crashes.
Force Connection Order Straight_join
Copy Code code as follows:
SELECT TABLE1. FIELD1, TABLE2. FIELD2 from TABLE1 straight_join TABLE2 WHERE ...
From the above SQL statement, through Straight_join forced MySQL to TABLE1, TABLE2 in order to connect the table. If you think the connection is more efficient in your own order than the one recommended by MySQL, you can determine the connection order by Straight_join.
Enforce use of temporary tables Sql_buffer_result
Copy Code code as follows:
SELECT Sql_buffer_result * from TABLE1 WHERE ...
When we query the result set for a long time, we can use the Sql_buffer_result. Option to force the result sets to be placed in a temporary table so that the MySQL table lock can be released quickly (so that other SQL statements can query the records), and can provide large recordsets for clients over a long period of time.
Group using temporary tables Sql_big_result and Sql_small_result
Copy Code code as follows:
SELECT Sql_buffer_result FIELD1, COUNT (*) from TABLE1 GROUP by FIELD1;
Typically used for grouping or distinct keywords, this option notifies MySQL and, if necessary, places the query results in a temporary table, even sorting in a temporary table. Sql_small_result is much more similar than sql_big_result and rarely used.
