what is mysql hint

In mysql, hint refers to "query optimization hint", which will prompt the optimizer to generate an execution plan in a certain way for optimization, making the user's SQL statement more flexible; Hint can be based on the table's Rules such as connection order, method, access path, and parallelism have an effect on DML (Data Manipulation Language) statements.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

We can add comment when operating tables, fields or indexes to enhance the readability of the code so that others can quickly understand the code. This is a tip for people who use databases; similarly, there is another Hints, called hints, are hints to the database.

What is hint

hint refers to "query optimization hint", which will prompt the optimizer to optimize in a certain way, allowing you to The SQL statement is more flexible, which will make your query faster, of course, it may also be slower, it all depends on your understanding of the optimizer and your understanding of the scenario.

We know that when executing a SQL statement, MySQL will generate an execution plan, and hint tells the query optimizer to generate the execution plan in the way we tell it.

Hint can act on DML (Data Manipulation Language) statements based on the table's connection order, method, access path, parallelism and other rules. The range is as follows:

使用的优化器类型；
基于代价的优化器的优化目标，是all_rows还是first_rows；
表的访问路径，是全表扫描，还是索引扫描，还是直接用rowid；
表之间的连接类型；
表之间的连接顺序；
语句的并行程度；

Commonly used hints

• Force index FORCE INDEX
  SELECT * FROM tbl FORCE INDEX (FIELD1) …

• Ignore index IGNORE INDEX
  SELECT * FROM tbl IGNORE INDEX (FIELD1, FIELD2) …

• Close Query buffer SQL_NO_CACHE
  SELECT SQL_NO_CACHE field1, field2 FROM tbl;
  When you need to query real-time data and the frequency is not high, you can consider turning off the buffer, that is, regardless of whether this SQL has been executed, MySQL will Will not look in the buffer.

• Force query cache SQL_CACHE
  SELECT SQL_CACHE * FROM tbl;
  The function is the opposite of the previous one, but only the query_cache_type in my.ini is set to It works at 2 o'clock.

• Priority operation HIGH_PRIORITY
  HIGH_PRIORITY can be used in select and insert operations to let MYSQL know that this operation takes priority.
  SELECT HIGH_PRIORITY * FROM tbl;

• ##Lagging operation LOW_PRIORITY

  LOW_PRIORITY can be used in insert and update operations to let mysql know that this operation is lagging .
  
  update LOW_PRIORITY tbl set field1= where field1= …

• Delayed insertion INSERT DELAYED

  
  INSERT DELAYED INTO tbl set field1= … means that when the client submits an application for inserting data, MySQL returns the OK status but is not actually executed. Instead, it is stored in the memory and queued, and then inserted when mysql is free.
  An important benefit is that insert requests from multiple clients are grouped together and written into a block, which is much faster than performing many inserts independently.
  The disadvantage is that the auto-increment ID cannot be returned, and when the system crashes, the data that MySQL has not yet had time to insert will be lost.
  

• Force the connection sequence STRAIGHT_JOIN

  
  SELECT tbl.FIELD1, tbl2.FIELD2 FROM tbl STRAIGHT_JOIN tbl2 WHERE…As can be seen from the above SQL statement, through STRAIGHT_JOIN forces MySQL to join tables in the order of tbl, tbl2. If you think it is more efficient to join in your own order than the order recommended by MySQL, you can use STRAIGHT_JOIN to determine the connection order.
  

• Force the use of temporary table SQL_BUFFER_RESULT

  
  SELECT SQL_BUFFER_RESULT * FROM tbl WHERE … When there is a lot of data in the result set of our query, we can force the result set into a temporary table through the SQL_BUFFER_RESULT. option, so that the MySQL table lock can be quickly released (so that other SQL statements can query these records) ) and can serve large recordsets to clients for long periods of time.
  

• Group using temporary tables SQL_BIG_RESULT and SQL_SMALL_RESULT

  
  SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM tbl GROUP BY FIELD1; is valid for SELECT statements, Tell MySQL optimization to use temporary table sorting for GROUP BY and DISTINCT queries. SQL_SMALL_RESULT indicates that the result set is small and can be sorted directly on the temporary table in memory; otherwise, if it is large, disk temporary table sorting is required.
  

• SQL_CALC_FOUND_ROWS
  It is not actually an optimizer prompt, nor does it affect the optimizer's execution plan, but it will cause the result set returned by mysql to include the total number of rows affected by this operation, which needs to be combined with FOUND_ROWS() Combined use.
  SQL_CALC_FOUND_ROWS Notifies MySQL to record the number of rows processed this time; FOUND_ROWS() is used to retrieve the number of recorded rows and can be applied to paging scenarios.
  The general paging method is: first check the total number, calculate the number of pages, and then query the details of a certain page.
  SELECT COUNT(*) from tbl WHERE …
SELECT * FROM tbl WHERE … limit m,n
  But with the help of SQL_CALC_FOUND_ROWS, It can be simplified to the following writing:
  SELECT SQL_CALC_FOUND_ROWS * FROM tbl WHERE … limit m,n;
SELECT FOUND_ROWS();
  Second Article SELECT will return the total number of rows in the first SELECT without limit, so you only need to execute a complex query that takes time to get the total number of rows at the same time.

• LOCK IN SHARE MODE, FOR UPDATE
  Similarly, these two are not optimization tips. They are the locking mechanism that controls the SELECT statement. They are only effective for row-level locks, which is supported by InnoDB. .

Expand knowledge:

Concepts and differences

SELECT ... LOCK IN SHARE MODE What is added is IS lock (intention shared lock), that is, shared locks are added to qualified rows. Other sessions can read records and continue to add IS locks. But it cannot be modified until the locked session is done (otherwise the direct lock wait times out).

SELECT ... FOR UPDATE What is added is an IX lock (intention exclusive lock), that is, exclusive is added to the rows that meet the conditions, and other sessions cannot add any to these records. S lock or X lock. If there is no consistent non-locking read, other sessions cannot read and modify these records, but innodb has non-locking read (snapshot read does not require locking).
Therefore, the locking method of for update only blocks the query of select...lock in share mode more than the method of lock in share mode. This method does not block snapshot reading.

Application Scenario

LOCK IN SHARE MODE is applicable to the writing scenario of two tables with a relationship. Taking the official mysql example, One table is the child table and the other is the parent table. Assume that a certain column child_id of the child table is mapped to the c_child_id column of the parent table. From a business perspective, it is risky to directly insert a child_id=100 record into the child table at this time, because insert At the same time, the record of c_child_id=100 may be deleted from the parent table, and the business data may be inconsistent. The correct method is to first execute select * from parent where c_child_id=100 lock in share mode, lock this record in the parent table, and then execute insert into child(child_id) values ​​(100) .

[Related recommendations: mysql video tutorial]

The above is the detailed content of what is mysql hint. For more information, please follow other related articles on the PHP Chinese website!