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 withFOUND_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 ofSQL_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 ArticleSELECT
will return the total number of rows in the firstSELECT
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!

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了mysql高级篇的一些问题,包括了索引是什么、索引底层实现等等问题,下面一起来看一下,希望对大家有帮助。


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

SublimeText3 Chinese version
Chinese version, very easy to use

SublimeText3 English version
Recommended: Win version, supports code prompts!

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Dreamweaver CS6
Visual web development tools

WebStorm Mac version
Useful JavaScript development tools