search
HomeDatabaseMysql Tutorialwhat is mysql hint
what is mysql hintJun 27, 2022 pm 02:12 PM
mysql

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.

what is mysql hint

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.

Not commonly used

  • 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!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
图文详解mysql架构原理图文详解mysql架构原理May 17, 2022 pm 05:54 PM

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

mysql的msi与zip版本有什么区别mysql的msi与zip版本有什么区别May 16, 2022 pm 04:33 PM

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

mysql怎么去掉第一个字符mysql怎么去掉第一个字符May 19, 2022 am 10:21 AM

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

mysql怎么替换换行符mysql怎么替换换行符Apr 18, 2022 pm 03:14 PM

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

mysql怎么将varchar转换为int类型mysql怎么将varchar转换为int类型May 12, 2022 pm 04:51 PM

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

MySQL复制技术之异步复制和半同步复制MySQL复制技术之异步复制和半同步复制Apr 25, 2022 pm 07:21 PM

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

mysql怎么判断是否是数字类型mysql怎么判断是否是数字类型May 16, 2022 am 10:09 AM

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

带你把MySQL索引吃透了带你把MySQL索引吃透了Apr 22, 2022 am 11:48 AM

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

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

MantisBT

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

Dreamweaver CS6

Visual web development tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools