MySQL query prompt and MySQL prompt

MySQL query prompt and MySQL prompt

 

MySQL query prompt:

1. LOW_PROPRITY, HIGHT_PRIORITY
Purpose: Specify the running priority of SQL statements. The SQL statements with the HIGHT_PROPRITY prompt are scheduled to the beginning of the Table Access queue.
Restrictions: only the engine for table-level locks is valid (MyISAM engine), and the lock for non-table-level engines is invalid, such as the innodb Engine
Usage: update test LOW_PROPRITY set name = 'abc' where id = 1

2. DELAYED
Purpose: For Insert or replace operations, put the data to be written into the buffer, and perform real Insert when the table is idle.
Restrictions: there is a risk of data loss. Not all engines Support the DELAYED operation. mysql5.7 does not seem to support operator change.
Usage: insert DELAYED into test values (1, 'aaa ')

3. Force join order of straight_join
Purpose: force the join order to specify the order in which the table is associated Based on the Writing sequence or the order before and after writing.
Restrictions:
Usage: 1. select * from t1 a straight_join t2 B on a. id = B. id1 fix the Association Sequence between table t1 and table t2,
2. select straight_join * from t1 a inner join t2 B on. id = B. id1 inner join test c on B. id1 = c. id: Associate all tables in the query in the writing order.
4. SQL _SMALL_RESULT and SQL _BIG_RESULT
Purpose: when processing DISTINCT or group by, the optimizer is prompted to process the result set in a small (memory space) or large (temporary disk control) manner.
Restriction: only valid for select statements
Usage: select SQL _SMALL_RESULT a. id, count (1) from t1 a straight_join t2 B on a. id = B. id1 group by a. id

5. SQL _BUFFER_RESULT
Purpose: add the query result set to a temporary table and release the table lock as soon as possible.
Restrictions:
Usage: select SQL _BUFFER_RESULT * from testbak

6. SQL _CACHE and SQL _NO_CACHE
Purpose: tell whether the query results are cached in the query cache.
Restrictions:
Usage: select SQL _NO_CACHE/* SQL _CACHE */* from testbak

7. SQL _CALC_FOUND_ROWS
Purpose: if there is a page, the system prompts to ignore the page restriction when calculating the total line.
Restrictions:
Usage: select SQL _CALC_FOUND_ROWS * from testbak LIMIT 100; -- the LIMIT is 100 page.
Select FOUND_ROWS (); -- calculates the total number of rows without LIMIT 100 in the preceding statement.

8. for update and LOCK IN SHARE MODE
Role: Lock prompt
Restrictions: INNODB only causes support for these two tips
Usage: select * from testbak where id = 8888 for update

9. use index, ignore index, FORCE INDEX
Purpose: Force Index prompt
Usage: select count (1) from testbak USE index (idx_id );
Select count (1) from testbak IGNORE index (idx_id );
Select count (1) from testbak FORCE index (idx_id );

10. optimizer_search_depth
Control the limit of the optimizer when the execution plan is exhausted. If the query is in the Statistics status for a long time, you can consider calling the local parameters.
Optimizer_prune_level
By default, the optimizer enables the optimizer to determine whether to skip certain execution plans based on the number of rows to be scanned.
Optimizer_switch
Contains the flag for enabling/disabling optimizer features
The first two parameters allow the optimizer to be more flexible when generating execution plans, but may miss some of the most optimized execution plans,
For example, it takes the optimizer 10 seconds to find a "most" optimized execution plan,
However, you can find an execution plan for "secondary" Optimization in three seconds. The execution plan for "secondary" optimization can be queried within two seconds.
This query took 3 + 2 = 5 seconds.
However, if it takes 10 seconds to find the "most" optimized execution plan, it takes 0.5 seconds to complete the query.
This query took 10 + 0.5 + 2 = 10.5 seconds, not worth the candle
This means that it does not take more time to find a method than to do the task itself.