mysql tmp_table_size最佳化之設定多大合適_Mysql

通過設定tmp_table_size選項來增加一張暫存資料表的大小，例如做進階GROUP BY操作產生的暫存資料表。如果調高該值，MySQL同時將增加heap表的大小，可達到提高聯結查詢速度的效果，建議盡量最佳化查詢，要確保查詢過程中產生的暫存資料表在記憶體中，避免暫存資料表過大導致產生基於硬碟的MyISAM表。

mysql> show global status like ‘created_tmp%‘;

+——————————–+———+

| Variable_name　　 | Value　|

+———————————-+———+

| Created_tmp_disk_tables | 21197 |

| Created_tmp_files　　　| 58　　|

| Created_tmp_tables　　| 1771587 |

+——————————–+———–+

每次建立暫存資料表，Created_tmp_tables增加，如果暫存資料表大小超過tmp_table_size，則是在磁碟上建立暫存資料表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務建立的臨時檔案檔案數，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的伺服器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，應該相當好了

預設為16M，可調到64-256最佳，線程獨佔，太大可能記憶體不夠I/O堵塞

如果動態網頁面要調大點，100M以上，如果網站大部分都是靜態內容，一般64M足夠。

tmp_table_size最佳化

資料庫連接突然增多到1000的問題

查看了一下，未有LOCK動作陳述式。

但是明顯有好多copy to tmp table的SQL語句，這條語讀的時間比較長，且這個表會被加讀鎖，相關表的update語句會被排進隊列。如果多執行幾次這樣的copyt to tmp table 語句，會造成更多的語句被阻塞。
串連太多造成mysql處理慢。

copy to tmp talbe 語句產生的原因是查詢需要Order By 或者Group By等需要用到結果集時，參數中設定的暫存資料表的大小小於結果集的大小時，就會將該表放在磁碟上，這個時候在硬碟上的IO要比內銷差很多。所耗費的時間也多很多。另外Mysql的另外一個參數max_heap_table_size比tmp_table_size小時，則系統會把max_heap_table_size的值作為最大的記憶體暫存資料表的上限，大於這個時，改寫硬碟。
我們的mysql這兩個參數為：

tmp_table_size 33554432 (33.5M)
max_heap_table_size 16777216 (16.7M)
比較小。
建議增加到上百M。我們的記憶體應該夠吧。

另外join_buffer_size（影響 表之間join效能的緩衝）為131072 (131K)較小，可以增加一點。

[root@mail ~]# vi /etc/my.cnf

[mysqld]
tmp_table_size=200M

mysql> show processlist;
mysql> show columns from wp_posts;

SQL 陳述式的第一個 LEFT JOIN ON 子句中： LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid _mydata 的 userid 被參與了條件比較運算。為 _mydata 表根據欄位 userid 建立了一個索引： mysql> ALTER TABLE `_mydata` ADD INDEX ( `userid` )  增加 tmp_table_size 值。
mysql 的設定檔中，tmp_table_size 的預設大小是 32M。如果一張暫存資料表超出該大小，MySQL產生一個 The table tbl_name is full 形式的錯誤，如果你做很多進階 GROUP BY 查詢，增加 tmp_table_size 值。 這是 mysql 官方關於此選項的解釋：

tmp_table_size

This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory.
對 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的條件判斷中用到的欄位,應該根據其建立索引INDEX。
索引被用來快速找出在一個列上用一特定值的行。沒有索引，MySQL不得不首先以第一條記錄開始並然後讀完整個表直到它找出相關的行。表越大，花費時間越多。如果表對於查詢的列有一個索引，MySQL能快速到達一個位置去搜尋到資料檔案的中間，沒有必要考慮所有資料。如果一個表有1000行，這比順序讀取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B樹中儲存。
根據 mysql 的開發文檔:

索引 index 用於：
快速找出匹配一個WHERE子句的行
當執行連接(JOIN)時，從其他表檢索行。
對特定的索引列找出MAX()或MIN()值
如果排序或分組在一個可用鍵的最左面首碼上進行(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有索引值部分跟隨DESC，鍵以倒序被讀取。
在一些情況中，一個查詢能被最佳化來檢索值，不用諮詢資料檔案。如果對某些表的所有使用的列是數字型的並且構成某些鍵的最左面首碼，為了更快，值可以從索引樹被檢索出來。
假定你發出下列SELECT語句：

mysql> select * FROM tbl_name WHERE col1=val1 AND col2=val2;如果一個多列索引存在於col1和col2上，適當的行可以直接被取出。如果分開的單行列索引存在於col1和col2上，最佳化器試圖通過決定哪個索引將找到更少的行並來找出更具限制性的索引並且使用該索引取行。
一般動態設定tmp_table_size的大小的時候，要使用:

set global tmp_table_size=64*1024*1024
set global tmp_table_size=64M
#1232 - Incorrect argument type to variable 'tmp_table_size'