mysql hint的概念是什么

在mysql中，hint指的是“查询优化提示”，会提示优化器按照一定的方式来生成执行计划进行优化，让用户的sql语句更具灵活性；Hint可基于表的连接顺序、方法、访问路径、并行度等规则对DML（数据操纵语言，Data Manipulation Language）语句产生作用。

本教程操作环境：windows7系统、mysql8版本、Dell G3电脑。

我们在操作表、字段或索引时可以添加 comment 来增强代码可读性，以便他人快速读懂代码，这是对使用数据库的人的一种提示；同样的，还有一种提示，叫做hint，是给数据库的提示。

何谓 hint

hint指的是“查询优化提示”，它会提示优化器按照一定的方式去优化，让你的sql语句更具灵活性，这会让你的查询更快，当然也可能更慢，这完全取决于你对优化器的理解和场景的了解。

我们知道在执行一条SQL语句时，MySQL会生成一个执行计划，而hint就是告诉查询优化器需要按照我们告诉它的方式来生成执行计划。

Hint可基于表的连接顺序、方法、访问路径、并行度等规则对DML（数据操纵语言，Data Manipulation Language）语句产生作用，范围如下：

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

常用 hint

• 强制索引 FORCE INDEX
  SELECT * FROM tbl FORCE INDEX (FIELD1) …

• 忽略索引 IGNORE INDEX
  SELECT * FROM tbl IGNORE INDEX (FIELD1, FIELD2) …

• 关闭查询缓冲 SQL_NO_CACHE
  SELECT SQL_NO_CACHE field1, field2 FROM tbl;
  需要查询实时数据且频率不高时，可以考虑把缓冲关闭，即不论此SQL是否曾被执行，MySQL都不会在缓冲区中查找。

• 强制查询缓冲 SQL_CACHE
  SELECT SQL_CACHE * FROM tbl;
  功能同上一条相反，但仅在my.ini中的query_cache_type设为2时起作用。

• 优先操作 HIGH_PRIORITY
  HIGH_PRIORITY可以使用在select和insert操作中，让MYSQL知道，这个操作优先进行。
  SELECT HIGH_PRIORITY * FROM tbl;

• 滞后操作 LOW_PRIORITY
  LOW_PRIORITY可以使用在insert和update操作中，让mysql知道，这个操作滞后。
  update LOW_PRIORITY tbl set field1= where field1= …

• 延时插入 INSERT DELAYED
  INSERT DELAYED INTO tbl set field1= …
  指客户端提交插入数据申请，MySQL返回OK状态却并未实际执行，而是存储在内存中排队，当mysql有空余时再插入。
  一个重要的好处是，来自多个客户端的插入请求被集中在一起，编写入一个块，比独立执行许多插入要快很多。
  坏处是，不能返回自增ID，以及系统崩溃时，MySQL还未来得及被插入的数据将会丢失。

• 强制连接顺序 STRAIGHT_JOIN
  SELECT tbl.FIELD1, tbl2.FIELD2 FROM tbl STRAIGHT_JOIN tbl2 WHERE …
  由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按tbl、tbl2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。

不常用

• 强制使用临时表 SQL_BUFFER_RESULT
  SELECT SQL_BUFFER_RESULT * FROM tbl WHERE …
  当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。

• 分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT
  SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM tbl GROUP BY FIELD1;
  对SELECT语句有效，告诉MySQL优化去对GROUP BY和DISTINCT查询如何使用临时表排序，SQL_SMALL_RESULT表示结果集很小，可以直接在内存的临时表排序；反之则很大，需要使用磁盘临时表排序。

• SQL_CALC_FOUND_ROWS
  它其实不是优化器提示，也不影响优化器的执行计划，但会让mysql返回的结果集中包含本次操作影响的总行数，需与 FOUND_ROWS() 联用。
  SQL_CALC_FOUND_ROWS 通知MySQL将本次处理的行数记录下来； FOUND_ROWS() 用于取出被记录的行数，可以应用到分页场景。
  一般的分页写法为：先查总数，计算页数，再查询某一页的详情。
  SELECT COUNT(*) from tbl WHERE …
SELECT * FROM tbl WHERE … limit m,n
  但借助SQL_CALC_FOUND_ROWS，可以简化成如下写法：
  SELECT SQL_CALC_FOUND_ROWS * FROM tbl WHERE … limit m,n;
SELECT FOUND_ROWS();
  第二条SELECT将返回第一条SELECT不带limit时的总行数，如此只需执行一次较耗时的复杂查询就可同时得到总行数。

• LOCK IN SHARE MODE、 FOR UPDATE
  同样的，这俩也不是优化提示，是控制SELECT语句的锁机制，只对行级锁有效，即InnoDB支持。

扩展知识：

概念和区别

SELECT ... LOCK IN SHARE MODE添加的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，其他session可读取记录，亦可继续添加IS锁，但无法修改，直到这个加锁的session done(否则直接锁等待超时)。

SELECT ... FOR UPDATE 添加的是IX锁(意向排它锁)，即符合条件的rows上都加了排它，其他session无法给这些记录添加任何S锁或X锁。如果不存在一致性非锁定读的话，则其他session是无法读取和修改这些记录的，但innodb有非锁定读(快照读不需要加锁)。
因此，for update的加锁方式只是比lock in share mode的方式多阻塞了select...lock in share mode的查询方式，并不会阻塞快照读。

应用场景

LOCK IN SHARE MODE的适用于两张存在关系的表的写场景，以mysql官方例子来说，一个表是child表，一个是parent表，假设child表的某一列child_id映射到parent表的c_child_id列，从业务角度讲，此时直接insert一条child_id=100记录到child表是存在风险的，因为insert的同时可能存在parent表执行了删除c_child_id=100的记录，业务数据有不一致的风险。正确方法是先执行select * from parent where c_child_id=100 lock in share mode，锁定parent表的这条记录，然后执行insert into child(child_id) values (100)。