临时表简介
MySQL在执行SQL查询时可能会用到临时表，一般而言，用到临时表就意味着性能较低。MySQL临时表可分：内存临时表和磁盘临时表，磁盘临时表使用MyISAM存储，而内存临时表则使用Memory引擎。MySQL使用内存临时表来存放查询的中间结果集，如果中间结果集大于临时表的容量设定，又或者中间结果集含TEXT或BLOB列类型字段，则MySQL会把其转化为磁盘临时表。Linux平台缺省是/tmp,/tmp目录小的系统要注意啦。
临时表特性
下面列举几个内存临时表的特性
1.表结构(.frm)在磁盘，数据在内存
2.缺省使用哈希索引
3.定长存储(BTW：即使是varchar也是定长)
4.只支持表锁
5.不支持TEXT和BLOB列类型
相关参数
1 大小参数
MIN{tmp_table_size,max_heap_table_size}
mysql> show global variables like ‘%table_size‘; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | max_heap_table_size | 16777216 | | tmp_table_size | 16777216 | +---------------------+----------+ 2 rows in set (0.00 sec)
2 数量参数
mysql> show global status like ‘created_tmp%‘; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 7 | | Created_tmp_files | 6 | | Created_tmp_tables | 90 | +-------------------------+-------+ 3 rows in set (0.00 sec)
通过ps_helper我们可以细化这些临时表到底是谁的临时表
mysql> SELECT query, exec_count, memory_tmp_tables, disk_tmp_tables, avg_tmp_tables_per_query, tmp_tables_to_disk_pct FROM statements_with_temp_tables LIMIT 5; +-------------------------------------------------------------------+------------+-------------------+-----------------+--------------------------+------------------------+ | query | exec_count | memory_tmp_tables | disk_tmp_tables | avg_tmp_tables_per_query | tmp_tables_to_disk_pct | +-------------------------------------------------------------------+------------+-------------------+-----------------+--------------------------+------------------------+ | SELECT IF ( ( `locate` ( ? , ` ... . `COMPRESSED_SIZE` ) ) DESC | 2 | 4 | 2 | 2 | 50 | | SELECT IF ( ( `locate` ( ? , ` ... MPRESSED_SIZE` = ? ) , ? , ... | 2 | 4 | 2 | 2 | 50 | | SELECT IF ( `isnull` ( `inform ... ` = `performance_schema` . ... | 2 | 4 | 2 | 2 | 50 | | SELECT IF ( `isnull` ( `inform ... by_thread_by_event_name` . ... | 2 | 4 | 2 | 2 | 50 | | SHOW FULL FIELDS FROM `stateme ... ` , `performance_schema` . ... | 2 | 4 | 2 | 2 | 50 | +-------------------------------------------------------------------+------------+-------------------+-----------------+--------------------------+------------------------+ 5 rows in set (0.00 sec)
优化临时表
分2个阶段：系统设计初期和产品上线后
在系统设计初期，优化却入点有：
1.创建索引 <==对ORDER BY 或 GROUP BY的列上创建索引
2.拆分表 <==大的列(如BLOB或TEXT)一般不会用作谓词，在表设计时可独立到另一张表
而产品上线后，我们只能对业务或SQL进行优化
1.拆分SQL <==临时表主要用于排序和分组，很多业务都是要求排序后再取出详细的数据，这种情况下可以把排序操作和查询所有信息的操作分开，以降低排序或分组时临时表的大小，提升排序或分组的效率
2.优化业务，去掉排序分组等操作
dstat MySQL 临时表监控插件开发
### Author: linwaterbin@gmail.com
### UPDATE: 2014-2-24
### FUNCTION: analyze mysql temp table use
# init MySQL authority
global mysql_user
mysql_user = os.getenv(‘DSTAT_MYSQL_USER‘)
global mysql_pwd
mysql_pwd = os.getenv(‘DSTAT_MYSQL_PWD‘)
global mysql_host
mysql_host = os.getenv(‘DSTAT_MYSQL_HOST‘)
global mysql_db
mysql_db = os.getenv(‘DSTAT_MYSQL_DB‘)
class dstat_plugin(dstat):
"""
Plugin for MySQL 5 Temp Table Usage.
"""
def __init__(self):
self.name = ‘mysql5 tmp usage‘
#self.format = (‘d‘,12,50)
self.nick = (‘mem‘, ‘disk‘,‘mem-disk-pct‘,)
self.vars = (‘memory_tmp_tables‘, ‘disk_tmp_tables‘,‘avg_mem_to_disk_pct‘,)
self.type = ‘s‘
self.width = 12
self.scale = 50
def check(self):
global MySQLdb
import MySQLdb
try:
self.db = MySQLdb.connect(user=mysql_user,passwd=mysql_pwd,host=mysql_host,db=mysql_db)
except:
raise Exception, ‘Cannot interface with MySQL server‘
def extract(self):
try:
query="""select sum(memory_tmp_tables) as memory_tmp_tables,sum(disk_tmp_tables) as disk_tmp_tables,avg(tmp_tables_to_disk_pct) as avg_mem_to_disk_pct from statements_with_temp_tables;"""
cur = self.db.cursor(MySQLdb.cursors.DictCursor)
cur.execute(query)
for record in cur.fetchall():
self.val[‘memory_tmp_tables‘] =record[‘memory_tmp_tables‘]
self.val[‘disk_tmp_tables‘] = record[‘disk_tmp_tables‘]
self.val[‘avg_mem_to_disk_pct‘] = record[‘avg_mem_to_disk_pct‘]
if step == op.delay:
self.set1.update(self.set2)
except Exception, e:
for name in self.vars:
self.val[name] = -1监控测试图如下：
By DataHacker
2014-2-26
Good Luck！