mysql hint介绍

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

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

何谓 hint

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

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

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

1

2

3

4

5

6

使用的优化器类型;

基于代价的优化器的优化目标,是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)

 

原文链接:https://www.php.cn/mysql-tutorials-493264.html

作者:HappyDeveloper原文地址:https://www.cnblogs.com/Terry-Wu/p/16910802.html

%s 个评论

要回复文章请先登录注册