文档章节

MySQL性能优化-临时表

ChinaHYF
 ChinaHYF
发布于 2017/03/02 00:29
字数 1206
阅读 280
收藏 1

 

今天在微信公众号中看到一篇关于讲解MySQL中临时表的文章,根据文章做了一个简单小结,在写sql的时候尽可能注意这些问题。

1. 外部临时表:通过CREATE TEMPORARY TABLE 创建,只对当前用户可见,当前会话结束的时候,该临时表会自动关闭。这种临时表的命名与非临时表可以同名(同名后非临时表将对当前会话不可见,直到临时表被删除)。

2. 内部临时表:会被MySQL自动创建并用来存储某些操作的中间结果。用来进行性能优化,对用户来说是不可见的,但是通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。使用内部临时表需要创建表以及中间数据的存取代价,所以用户在写SQL语句的时候应该尽量的去避免使用临时表。

内部临时表有两种类型:

2.1 一种是HEAP临时表,这种临时表的所有数据都会存在内存中,对于这种表的操作不需要IO操作。

2.2 另一种是OnDisk临时表,这种临时表会将数据存储在磁盘上。OnDisk临时表用来处理中间结果比较大的操作。如果HEAP临时表存储的数据大于MAX_HEAP_TABLE_SIZE,HEAP临时表将会被自动转换成OnDisk临时表。

在书写SQL语句的时候能够尽量少的使用内部临时表进行查询优化,将有效的提高查询执行的效率。

会利用到内部临时表的10种操作(这10种操作在写sql的时候应该尽量避免):

1. 在SQL语句中使用SQL_BUFFER_RESULT 

SQL_BUFFER_RESULT主要用来让MySQL尽早的释放表上的锁。因为如果数据量很大的话,需要较长时间将数据发送到客户端,通过将数据缓冲到临时表中可以有效的减少读锁对表的占用时间。

使用:select SQL_BUFFER_RESULT * from t1;

2. 如果SQL语句中包含了DERIVED_TABLE

在5.7中,由于采用了新的优化方式,我们需要使用 set optimizer_switch=’derived_merge=off’来禁止derived table合并到外层的Query中。

3. 如果我们查询系统表的话,系统表的数据将被存储到内部临时表中。

可以通过SHOW STATUS来查看是否利用到了内部临时表。

4. 如果DISTINCT语句没有被优化掉,即DISTINCT语句被优化转换为GROUP BY操作或者利用UNIQUE INDEX消除DISTINCT, 内部临时表将会被使用。

5. 如果查询带有ORDER BY语句,并且不能被优化掉。下面几种情况会利用到内部临时表缓存中间数据,然后对中间数据进行排序。

5.1 如果连接表使用BNL(Batched Nestloop)/BKA(Batched Key Access)

5.1.1 BNL默认是打开的

5.1.2 关掉BNL后,ORDER BY将直接使用filesort

5.2 ORDER BY的列不属于执行计划中第一个连接表的列

select * from t as t1, t as t2 order by t2.a

5.3 如果ORDER BY的表达式是个复杂表达式

5.3.1 如果排序表达式是SP或者UDF

5.3.2 ORDER BY的列包含聚集函数

5.3.3 ORDER BY的列中包含有SCALAR SUBQUERY,当然该SCALAR SUBQUERY没有被优化掉。

5.4 如果查询既带有ORDER BY同时也有GROUP BY语句,但是两个语句使用的列不相同。

如果是5.7,我们需要将sql_mode设置为非only_full_group_by模式,否则会报错。

6. 如果查询带有GROUP BY语句,并且不能被优化掉

6.1 如果连接表使用BNL(Batched Nestloop)/BKA(Batched Key Access)

6.2 如果GROUP BY的列不属于执行计划中的第一个连接表

6.3 如果GROUP BY语句使用的列与ORDER BY语句使用的列不同

6.4 如果GROUP BY带有ROLLUP并且是基于多表外连接

6.5  如果GROUP BY语句使用的列来自于SCALAR SUBQUERY,并且没有被优化掉

7. IN表达式转换为semi-join进行优化

7.1 如果semi-join执行方式为Materialization

7.2 如果semi-join执行方式为Duplicate Weedout

8. 如果查询语句带有UNION,MySQL将利用内部临时表帮助UNION操作消除重复。

9. 如果查询语句使用多表更新

10. 如果聚集函数中包含如下函数,内部临时表也会被利用

10.1 count(distinct *)

10.2 group_concat

参考链接:

https://mp.weixin.qq.com/s?__biz=MzA5ODM5MDU3MA==&mid=2650862476&idx=1&sn=877e27f5ea97ee41f227b1133757e21b&chksm=8b6614c9bc119ddfe6825e6c52279921a7a6b80a437abaa8e0eea3cf56cc1ee7455b95078c39&mpshare=1&scene=1&srcid=0228rJ9FRnUWhQsVRJiqVZJp&key=8c50eef2791c9cdad6562a0d0bf39cda2106ef6c3854f41bac1fbd93e6a4c4f175fabc5ef290e0fdebb93272fbabaa03ef079bf759e3e7396ab313a5b2b5f4cbd1f3901a7fcbdc1f6a3558281ceab47b&ascene=0&uin=Mjk3NDgwNDg2Mg%3D%3D&devicetype=iMac+MacBookPro11%2C4+OSX+OSX+10.11.3+build(15D21)&version=11020201&pass_ticket=uun%2BnwC9a8EATOZ0uHpzxSnCjpuYa3czQ3zLBrcx9CS35Olw9UfFCVKGAaI%2BkmeL

© 著作权归作者所有

ChinaHYF
粉丝 16
博文 76
码字总数 76119
作品 0
西安
高级程序员
私信 提问
很好的一篇讲解sql优化的文章

【问题现象】 线上mysql数据库爆出一个慢查询,DBA观察发现,查询时服务器IO飙升,IO占用率达到100%, 执行时间长达7s左右。 SQL语句如下: SELECT DISTINCT g.*, cp.name AS cp_name, c.nam...

fzxu_05
2014/06/10
267
0
优化临时表使用,SQL语句性能提升100倍

【问题现象】 线上mysql数据库爆出一个慢查询,DBA观察发现,查询时服务器IO飙升,IO占用率达到100%, 执行时间长达7s左右。 SQL语句如下: SELECT DISTINCT g.*, cp.name AS cp_name, c.nam...

markGao
2014/06/10
100
0
MySQL · 特性分析 · 内部临时表

MySQL中的两种临时表 外部临时表 通过CREATE TEMPORARY TABLE 创建的临时表,这种临时表称为外部临时表。这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭。这种临时表的...

阿里云RDS-数据库内核组
2016/06/08
0
0
迄今最安全的MySQL?细数5.7那些惊艳与鸡肋的新特性(上)

MySQL 5.7版本于2015年10月份左右GA,至今已经大半年了,但作为MySQL DBA的我却一直没时间follow它的特性,实在惭愧,以后会花时间来研究5.7版本的特性并针对部分优化功能做出压力测试。 本系...

杨奇龙
2016/06/27
0
0
MySQL5.7中InnoDB不可不知的新特性

大家好,首先非常感谢社群的引荐,让我有机会在这里跟广大的DBA群友们交流。 今天的分享主要是针对MySQL5.7中InnoDB的新特性,InnoDB大家都应该非常熟悉,作为MySQL的存储引擎,而且现在变成...

2016/09/05
0
0

没有更多内容

加载失败,请刷新页面

加载更多

OSChina 周六乱弹 —— 如果是个帅小伙你愿意和他出去吗

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 小小编辑推荐:《Ghost 》游戏《死亡搁浅》原声 《Ghost 》游戏(《死亡搁浅》原声) - Au/Ra / Alan Walker 手机党少年们想听歌,请使劲儿戳...

小小编辑
今天
176
6
java通过ServerSocket与Socket实现通信

首先说一下ServerSocket与Socket. 1.ServerSocket ServerSocket是用来监听客户端Socket连接的类,如果没有连接会一直处于等待状态. ServetSocket有三个构造方法: (1) ServerSocket(int port);...

Blueeeeeee
今天
6
0
用 Sphinx 搭建博客时,如何自定义插件?

之前有不少同学看过我的个人博客(http://python-online.cn),也根据我写的教程完成了自己个人站点的搭建。 点此:使用 Python 30分钟 教你快速搭建一个博客 为防有的同学不清楚 Sphinx ,这...

王炳明
昨天
5
0
黑客之道-40本书籍助你快速入门黑客技术免费下载

场景 黑客是一个中文词语,皆源自英文hacker,随着灰鸽子的出现,灰鸽子成为了很多假借黑客名义控制他人电脑的黑客技术,于是出现了“骇客”与"黑客"分家。2012年电影频道节目中心出品的电影...

badaoliumang
昨天
16
0
很遗憾,没有一篇文章能讲清楚线程的生命周期!

(手机横屏看源码更方便) 注:java源码分析部分如无特殊说明均基于 java8 版本。 简介 大家都知道线程是有生命周期,但是彤哥可以认真负责地告诉你网上几乎没有一篇文章讲得是完全正确的。 ...

彤哥读源码
昨天
19
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部