mysql 查询优化器提示（hint）

转载：https://www.cnblogs.com/duanxz/p/7458450.html

上文我们有提及到优化器的一些相关信息，如优化器的一些优化特性和限制，由此看出mysql优化器也并不是万能的。

所以mysql提供了另一种神奇的功能让我们去引导优化器进行更好的优化。

它就是 查询优化提示（Query Optimizer Hints）;

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

现在让我们来了解有哪些查询优化提示：

优先操作 HIGH_PRIORITY

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

滞后操作 LOW_PRIORITY

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

这两个提示都只在基于表锁的存储引擎非常有效。在innoDB和其他基于行锁的存储引擎，你可能永远用不上。在MyISAM中使用它们时，也要十分小心，因为它们会让并发插入失效，可能会严重下降性能。

延时插入 DELAYED
这个操作只能用于 insert 和 replace
INSERT DELAYED INTO table1 set field1= …
INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。
当mysql有 空余时，再插入。另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块。这比执行许多独立的插入要快很多。
坏处是，不能返回自动递增 的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。并且导致last_insert_id()无法正常工作。


强制连接顺序straight_join
SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE...;

由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。

分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT

SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;
这两个提示只对select语句有效，它们告诉优化器对 group by 或者 distinct 查询如何使用临时表及排序。
sql_small_result 告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，以避免排序操作；
sql_big_result 则告诉优化器结果集会很大，建议使用磁盘临时表做排序操作；

强制使用临时表sql_buffer_result

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …;
这个提示告诉优化器将查询放入到一个临时表，然后尽可能地释放锁。这和前面提到的由客户端缓存结果不同。当你设法使用客户端缓存的时候，使用服务器端的缓存通常很有效。
带来的好处是无须在客户端消耗太多的内存，还可以尽可能快的释放对应的表锁。代价是，服务器端需要更多的内存。
查询缓冲
sql_cache 和 sql_no_cache
这个提示告诉mysql是否讲结果集缓存在查询缓存中。

关闭查询缓冲 SQL_NO_CACHE

SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;

有一些SQL语句需要实时地查询数据，或者并不经常使用(可能一天就执行一两次),这样就需要把缓冲关了,不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。
强制查询缓冲 SQL_CACHE

SELECT SQL_CALHE * FROM TABLE1;
如果在my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。
sql_calc_found_rows
严格来说，这并不是一个优化器提示。它不会告诉优化器任何关于执行计划的东西。
它会让mysql返回的结果集包含更多的信息。查询中加上该提示，mysql会计算出去limit子句后这个查询返回的结果集的总数。
而实际上只返回limit要求的结果集。可以通过函数found_row()获得这个值。

锁相关 for update 和 lock in share mode
这两个提示主要控制select 语句的锁机制。但只对实现了行级锁的存储引擎有效。使用该提示会对符合查询条件的数据加锁。
对于insert...select 语句不需要这两个提示，因为会默认添加上锁。
唯一内置的支持这两个提示的引擎是innoDB。另外需要记住的是，这两个提示会让某些优化无法进行。例如索引覆盖扫描。
innoDB不能在不访问主键的情况下用排他锁锁定行，因为行的信息锁定在主键中。

详细见《MySQL锁之三：MySQL的共享锁与排它锁编码演示》

索引相关

use index, ignore index 和 force index：这几个提示用来告诉优化器是否使用索引来查询记录。

force index 和 use index 基本相同，除了一点：force index 会告诉优化器全表扫描的成本会远远高于索引扫描，哪怕实际该索引用处不大。

强制索引 FORCE INDEX
SELECT * FROM TABLE1 FORCE INDEX (FIELD1);
以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。
忽略索引 IGNORE INDEX

SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2)；

在上面的SQL语句中，TABLE1表中FIELD1和FIELD2上的索引不被使用。



新增参数控制优化器的行为：
optimizer_search_depth

是否跳过执行计划optimizer_prune_level
该参数默认打开的，这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。

optimizer_switch
这个变量包含了一些开启/关闭优化器特性的标志位。例如mysql5.1 可以通过控制这个参数来控制禁用索引合并的特性。

分类: mysql , mysql事物和锁 , mysql优化
标签: mysql优化 , mysql , mysql事物和锁
好文要顶 关注我 收藏该文
duanxz
粉丝 - 2365 关注 - 22
+加关注
00
« 上一篇： mysql查询优化之二：查询优化器的局限性
» 下一篇： mysql查询优化之四：优化特定类型的查询
posted on 2017-08-31 15:21 duanxz 阅读(5197) 评论(0) 编辑 收藏 举报
刷新评论刷新页面返回顶部
登录后才能查看或发表评论，立即 登录 或者 逛逛 博客园首页
【推荐】社区发展的崎岖：园子的商业化努力-困境求助：开设捐助通道
【推荐】中国云计算领导者：阿里云轻量应用服务器2核2G低至108元/年

编辑推荐：
· 调试器是个大骗子
· [MAUI] 模仿 iOS 多任务切换卡片滑动的交互实现
· golang 中一种不常见的 switch 语句写法
· 当 SQL Server（mssql-jdbc） 遇上 BigDecimal → 精度丢失
· 一文讲透 RocketMQ 消费者是如何负载均衡的
即构专区：
· 泛娱乐出海实时化，技术驱动新场景开拓
· 即构电商直播方案，支持万商之家撬动千亿商业直播市场
· ZEGO自研RTC+直播系统架构，如何支撑一场高质量直播
· 音视频开发进阶｜第四讲：音频自动增益控制 AGC
· 如何0代码实现多人音视频通话？【内附源码/Demo】
昵称： duanxz
园龄： 12年1个月
粉丝： 2365
关注： 22
+加关注
< 2023年5月 >
日 一 二 三 四 五 六
30 1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31 1 2 3
4 5 6 7 8 9 10

搜索


常用链接
我的随笔
我的评论
我的参与
最新评论
我的标签
我的标签
mysql(121)
spring(103)
springcloud(95)
linux命令(83)
cache(81)
monitor(78)
juc(60)
多线程(57)
distributed(54)
JVM虚拟机(53)
更多
随笔分类
high-concurrence(6)
alibaba(23)
Android(2)
annotation(11)
Application Server(34)
app-server管理与优化(2)
Architecture(38)
build(13)
cache(84)
cache-memcache(20)
cache-redis(20)
configserver(1)
configuration-center(2)
consistency(20)
CXF(1)
db操作(3)
DB基础(16)
design discipline(8)
DevOps(1)
distributed(72)
doc(2)
docker(9)
dubbo(9)
eclipse(18)
effective java笔记(8)
更多
随笔档案
2023年1月(2)
2022年8月(3)
2022年5月(1)
2022年3月(1)
2022年2月(16)
2022年1月(9)
2021年12月(4)
2021年11月(18)
2021年10月(1)
2021年9月(2)
2021年8月(3)
2021年7月(11)
2021年6月(11)
2021年5月(32)
2021年4月(23)
2021年3月(2)
2021年1月(1)
2020年12月(4)
2020年10月(2)
2020年9月(2)
2020年7月(1)
2020年4月(4)
2020年3月(12)
2019年12月(1)
2019年11月(23)
更多
文章分类
cloud(2)
mysql(1)
阅读排行榜
1. spring4.0之二：@Configuration的使用(423563)
2. springboot中使用@Value读取配置文件(154409)
3. java类中serialversionuid 作用 是什么?举个例子说明(129641)
4. ES之五：ElasticSearch聚合(127065)
5. shell函数（调用、返回值，返回值获取）(119846)
评论排行榜
1. spring4.0之二：@Configuration的使用(30)
2. Thread之三：Thread Join()的用法(12)
3. UML类图符号 各种关系说明以及举例(10)
4. RocketMQ之六：RocketMQ消息存储(9)
5. java对象在内存中的结构（HotSpot虚拟机）(7)
推荐排行榜
1. spring4.0之二：@Configuration的使用(80)
2. java类中serialversionuid 作用 是什么?举个例子说明(31)
3. UML类图符号 各种关系说明以及举例(30)
4. CAP原则(CAP定理)、BASE理论(27)
5. 服务链路追踪(Spring Cloud Sleuth)(17)
最新评论
1. Re:API网关之Kong网关简介
API级别的超时、重试指的是什么？kong是和nginx一样提供超时配置的 不是在route上配置 通过admin-api接口，可以设置service的超时配置 : 连接超时、读超时、写超时以及重试次...
--Bright-Z
2. Re:websocket之三：Tomcat的WebSocket实现
@强哥叨逼叨 老哥，你对内置Tomcat实现websocket这块的内容熟么？我最近自己想通过内置Tomcat实现websocket功能，但是遇到了些问题。...
--雷霆逐日
3. Re:Java性能调优：利用JMC分析性能
学习了

--夏末陌路
4. Re:Spring Boot Actutaur + Telegraf + InFluxDB + Grafana 构建监控平台
牛的

--huskyui
5. Re:微服务架构下的安全认证与鉴权
请问个问题，服务之间的鉴权是否有必要？通过feign注入id-token或其他命名key的token，mvc interceptor拦截校验，服务之间的调用基本都是在你代码里进行，理论上来说来源都是可...
--xl。十一
6. Re:Java堆外内存之七：JVM NativeMemoryTracking 分析堆外内存泄露
楼主换的jdk版本是多少的版本

--FR-A21
7. Re:Synchronized之三：实现原理
这图真形象

--vgshots
8. Re:JAVA CAS原理、unsafe、AQS
深度好文，感谢博主！

--vgshots
9. Re:spring扩展点之五：ApplicationContextInitializer实现与使用
博主 你文章中这句 没太看明白 "那么spring-boot自带的ApplicationContextInitializer会先按顺序执行，再执行我们手动添加的自定义ApplicationContex...
--mumucgq
10. Re:java对象在内存中的结构（HotSpot虚拟机）
受益匪浅

--vgshots
11. Re:RocketMQ之六：RocketMQ消息存储
没怎么看懂messageQueues和ConsumeQueue的关系，到底是从哪里拿到消息的偏移量，再到CommitLog找到消息实体呢？两个组件你都有相同的论述

--null9527
12. Re:Thread之三：Thread Join()的用法
@程序员的文娱情怀 总结得很到位，面试被问到如何按顺序启动ABC三个线程，就是用join来实现...
--鹤滨
13. Re:无限级分类实现思路 (组织树的分级管理)
第三种方案：无限级分类----改进前序遍历树。支持两个节点的树吧。不确定子节点的数量应该是无法做的吧。

--可以了交作业
14. Re:使用wireshark观察SSL/TLS握手过程--双向认证/单向认证
我也想看下 抓的是哪个网址的包啊

--章靖
15. Re:JVM内存模型
make.来迟了。

--hkshadow
16. Re:spring4.0之二：@Configuration的使用
@Configuration不可以是匿名类?
为什么呢

--明宇鹤
17. Re:Maven详解之仓库------本地仓库、远程仓库
目前主要用官方自带的仓库或者阿里云的仓库，用这两个足够了。更多的中央仓库介绍可以参考：《Maven中央仓库地址大全》

--南风99
18. Re:Jasypt 加密
学习了 感谢

--柳暗花溟
19. Re:Thread之三：Thread Join()的用法
@古代超人 wait()释放锁等待，不占用cpu时间片 sleep()抱着锁等待，...
--万雨
20. Re:事务之一：数据库事务基础知识回顾
博主，似乎事务系列里唯独缺少了事务之五？

--HAL--9000
21. Re:事务之六：spring 嵌套事务
@须臾静静 看最上面的图，1,2,3代表不同的代码段...
--duanxz
22. Re:事务之六：spring 嵌套事务
@zombiesoul @sdm_seven --“1”可插入，“2”可插入，“3”不可插入： 结果是“1”，“2”，“3”都不能插入，“1”，“2”被回滚。 --“1”可插入，“2”不可插入，“3”...
--须臾静静
23. Re:Thread之三：Thread Join()的用法
join() 提供了一种线程顺序的机制，是将别的线程执行完再执行自己的线程

--程序员的文娱情怀
24. Re:YAML配置，spring boot 配置文件
请问大佬，博客中的截图对应哪本书啊

--明天只会更好
25. Re:redis之set应用 实现对账(集合比较)功能
工具类没法用啊

--d0001
Powered by: 博客园 Copyright © 2023 duanxz
Powered by .NET 7.0 on Kubernetes