免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 3700 | 回复: 1

MySQL Hints [复制链接]

论坛徽章:
3
数据库技术版块每日发帖之星
日期:2015-06-25 22:20:00数据库技术版块每日发帖之星
日期:2015-08-06 06:20:00数据库技术版块每日发帖之星
日期:2015-08-28 06:20:00
发表于 2015-09-11 15:49 |显示全部楼层

我们可以对MySQL的对象(表、索引、触发器、自建函数、存储过程等)做注释(comment),这样做的目的是标识该对象的作用等以增强代码的可读性、方便其他同事快速读懂我们写的代码或某个数据库对象的作用,说白了,comment就是对我们人的一个提示。在MySQL中还有一种提示,叫做hint,hint是对数据库的提示,我们都知道, 在执行一条SQL语句的时候,MySQL都会生成一个执行计划,而hint就是用来告诉优化器按照我们告诉它的方式生成执行计划。Hint可以基于表连接的顺序、表连接的方法、访问路径、并行度等规则对DML语句、或者查查询语句产生作用,通过它我们可以实现:
1) 使用的优化器的类型
2) 基于代价的优化器的优化目标,是all_rows还是first_rows。
3) 表的访问路径,是全表扫描,还是索引扫描,还是直接利用rowid。
4) 表之间的连接类型
5) 表之间的连接顺序
6) 语句的并行程度

下面我们看下MySQL中hint都能实现那些功能:

1、Index Hints
在MySQL中,查询优化器用来解析查询语句,给出最优的查询路径,然而查询优化器并不是一直能给出最优的执行计划,比如某张表经过大量的insert和update操作后,索引的分布会受到很大的影响(此时我们可以使用ANALYZE TABLE来重新分析表)。当优化器不能给出合理的执行计划时,我们可以使用索引hints来告诉MySQL优化器使用某个索引或忽略某个索引,从而达到优化查询的目的。
语法:
tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [, index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...
index_hint语句放在表名后面,index_list选项后面跟索引名,不要跟列名,对于一些表可能后面跟列名也可以,但这样是不标准的,对于有主键索引的表, USE | FORCE  | IGNORE {INDEX|KEY} 后面必须跟primary代替在索引的名字。
SELECT * FROM t1 USE INDEX (PRIMARY) ...;
USE INDEX
后面添加你希望MySQL查询使用的索引列表,就可以让MySQL不再考虑其他可用的索引。
SELECT * FROM t1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;
IGNORE INDEX
禁止查询优化器使用指定的索引。在具有多个索引的查询时,可以用来指定不需要优化器使用的那个索引,还可以在删除不必要的索引之前在查询中禁止使用该索引。比如,在一个有主键索引的表中,默认的执行计划是按照主键索引进行扫描,如果我们使用IGNORE INDEX忽略主键索引,则会按照全表扫描执行。
SELECT * FROM table1 IGNORE INDEX (col3_index)
  WHERE col1=1 AND col2=2 AND col3=3;
FORCE INDEX
强制MySQL使用一个或者多个索引
MySQL 通常会根据统计信息选择正确的索引,但是当查询优化器选择了错误的索引或者根本没有使用索引的时候,可以使用FORCE INDEX选项来让MySQL使用正确的所以。
SELECT * FROM t1 FORCE INDEX (col1_index) ...;

在USE INDEX后面,如果省略了索引列表(USE INDEX ()WHERE ...)则表示不使用任何索引,如果在FORCE|IGNORE INDEX后面省略了索引名,则报错。

我们可以指定索引hint的范围:
index_hint:
    USE | IGNORE | FORCE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
分别是用于表连接(JOIN)、排序(ORDER BY)、分组(GROUP BY)
如果不跟FOR语句来指定hint的范围,则默认是应用上述所有的范围,也就是说,下面2条语句是等同的:
IGNORE INDEX (i1)
IGNORE INDEX FOR JOIN (i1)
IGNORE INDEX FOR ORDER BY (i1)
IGNORE INDEX FOR GROUP BY (i1)
在MySQL 5.0及以前,如果不知道FOR选项,则默认只是在行检索使用hint,在MySQL 5.0以后的版本都是默认应用所有范围,当然我们还可以修改OLD参数来改变这一功能:
mysql> show variables like 'old';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old           | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
但在MySQL Replication环境下,最好不要修改此参数,二进制日志格式是statement的时候,此参数主从不一致会报错。

我们还可以同时使用多个hints:
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;
不能在同一个hint里面使用相同的索引:
SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1);
一张表中不能同时使用USE INDEX和FORCE INDEX:
SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);

Hints在全文索引中的应用:
natural language mode :默认IGNORE INDEX无效,即便加上此选项也会忽略它,但不会报错。
boolean mode:会自动IGNORE FOR ORDER BY | GROUP BY,也就是下面2个语句是等效的:
SELECT * FROM t
  USE INDEX (index1)
  IGNORE INDEX (index1) FOR ORDER BY
  IGNORE INDEX (index1) FOR GROUP BY
  WHERE ... IN BOOLEAN MODE ... ;

SELECT * FROM t
  USE INDEX (index1)
  WHERE ... IN BOOLEAN MODE ... ;



2、在SQL语句级别决定是否使用查询缓存
MySQL查询缓存是用来缓存SQL文本以及查询结果,如果运行相同的SQL,则会直接从缓存中提取缓存结果,这样就不用重新解析和执行SQL语句,从而提高查询效率。查询缓存由参数query_cache_type控制,0表示不使用缓冲,1表示使用缓冲,2表示根据需要使用。
查看使用缓存查询的SQL语句数量:
show status like 'qcache_hits';

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

强制查询缓冲(SQL_CACHE)
SELECT SQL_CACHE col1,col2 FROM t1;
如果在my.cnf|my.ini中的query_cache_type设成2,这样只有在使用了SQL_CACHE后,才使用查询缓冲。

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

SQL_CALC_FOUND_ROWS关键字告诉MySQL计算符合查询(不需要考虑可能设置的任何LIMIT)的总行数,即加上此参数后,当执行了SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 2,5;此时结果返回5行,但我们不知道此查询语句一共有多少行符合查询要求,此时就可以通过调用FOUND_ROWS()函数可以得到总行数。

SQL_BIG_RESULT 和SQL_SMALL_RESULT关键字可以指定结果集的期望大小,因此可帮助找到最佳的方法对返回的记录进行排序和存储(基于磁盘或者内存中的临时表)


3、修改语句的优先级
提升语句的优先级(HIGH_PRIORITY)
HIGH_PRIORITY可以使用在select和insert操作中,让MySQL知道,这个操作优先进行。
SELECT HIGH_PRIOPRITY * FROM t1;
降低语句的优先级(LOW_PRIORITY)
应用于delete、insert、load data和update中
UPDATE LOW_PRIOPRITY t1 SET col1='...' WHERE col2='...';

如果你希望所有支持LOW_PRIORITY选项的语句都默认地按照低优先级来处理,可以在配置文件里加上--low-priority-updates选项。此时,如果想要把INSERT语句提高到正常的写入优先级,则使用INSERT HIGH_PRIORITY来把可以消除该选项对单个INSERT语句的影响。

延时插入(INSERT DELAYED)
应用于insert和replace语句中
INSERT DELAYED INTO t1 SET col1='...';
INSERT DELAYED INTO,是客户端提交数据给MySQL,MySQL返回OK状态给客户端。而这是并不是已经将数据插入表,而是存储在内存里面等待排队,当mysql有空余时,再插入。这样做好处在于来自许多客户端的插入被集中在一起,并被编写入一个块,这比执行许多独立的插入要快很多。
缺点是:不能返回自动递增的ID,以及系统崩溃时,MySQL还没有来得及插入数据的话,这些数据将会丢失。



4、强制改变连接顺序(STRAIGHT_JOIN)
SELECT t1.col1,t2.col2 FROM t1 STRAIGHT_JOIN t2 WHERE ...;
通过STRAIGHT_JOIN强迫MySQL按t1、t2的顺序连接表。
如果认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话,就可以通过STRAIGHT_JOIN来确定连接顺序。

MySQL对多表连接的处理方式,首先MySQL优化器要确定以谁为驱动表,也就是说以哪个表为基准,在处理此类问题时,MySQL优化器采用了简单粗暴的解决方法:哪个表的结果集小,就以哪个表为驱动表。
SELECT t1.* FROM t1
    INNER JOIN t2 ON t1.id = t2.tid
    WHERE t1.status = 1 AND t2.gid = 123
    ORDER BY t1.created DESC LIMIT 100;

SELECT t1.* FROM t1
    STRAIGHT_JOIN t2 ON t1.id = t2.tid
    WHERE t1.status = 1 AND t2.gid = 123
    ORDER BY t1.created DESC LIMIT 100;
上面2个语句的不同之处就在于采用了STRAIGHT_JOIN,当t2表的结果集小于t1表的时候,MySQL优化器会采用t1表作为驱动表,但我们后面的ORDER BY语句中用到t1表的created列,此时如果created中有索引无疑会增加检索速度,如果默认是t2表做驱动表,执行计划中可能会出现Using filesort,甚至Using temporary,这样是很耗费资源的。

更多MySQL学习文章,关注我哦
宋康-更多精彩视频,尽在我赢职场.png

论坛徽章:
33
ChinaUnix元老
日期:2015-02-02 08:55:39CU十四周年纪念徽章
日期:2019-08-20 08:30:3720周年集字徽章-周	
日期:2020-10-28 14:13:3020周年集字徽章-20	
日期:2020-10-28 14:04:3019周年集字徽章-CU
日期:2019-09-08 23:26:2519周年集字徽章-19
日期:2019-08-27 13:31:262016科比退役纪念章
日期:2022-04-24 14:33:24
发表于 2015-09-12 16:50 |显示全部楼层
感谢分享
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP