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!

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.


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

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

SublimeText3 Chinese version
Chinese version, very easy to use

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

WebStorm Mac version
Useful JavaScript development tools

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool
