Mysql

mariadb 什麼時候創建 tmp 表？

  • October 21, 2014

我發現，這copying to tmp table是我想要優化的慢查詢的大部分時間。我在終端中觀看了“分析”的輸出。這發生在一個有 6000 行的圖片表和一個有 30 行的使用者表上。

...
| Copying to tmp table | 0.000263 |
| Copying to tmp table | 0.052237 |
| Sorting result | 0.000035 |
...

為什麼（或何時）mariadb 使用 tmp 表？我可以更改查詢以避免使用 tmp 表嗎？

SELECT `pics`.*, `users`.`username`, `users`.`displayname` 
FROM `pics` 
LEFT JOIN `users` 
   ON `users`.`id` = `pics`.`user_id` 
ORDER BY RAND() asc 
LIMIT 4;

我有這些索引：

圖片：

PRIMARY id
UNIQUE  created_at
INDEX   user_id
INDEX   location

使用者：

PRIMARY id
UNIQUE  username

編輯：

我還有一個，也很慢，也是因為copying to tmp table：

SELECT t.tag, count(pt.tag_id) as anzahl 
FROM tags t
JOIN pic_tag pt 
   ON t.id = pt.tag_id
GROUP BY t.tag COLLATE utf8_unicode_ci
ORDER BY RAND() 
LIMIT 12;

顯示查詢 1 的配置文件；

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000149 |
| checking permissions | 0.000009 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000031 |
| After opening tables | 0.000010 |
| System lock          | 0.000010 |
| Table lock           | 0.000008 |
| After table lock     | 0.000012 |
| init                 | 0.000058 |
| optimizing           | 0.000028 |
| statistics           | 0.000061 |
| preparing            | 0.000045 |
| executing            | 0.000007 |
| Copying to tmp table | 0.000101 |
| Copying to tmp table | 0.034019 |
| Sorting result       | 0.000426 |
| Sending data         | 0.000023 |
| end                  | 0.000006 |
| removing tmp table   | 0.000129 |
| end                  | 0.000004 |
| query end            | 0.000003 |
| closing tables       | 0.000011 |
| freeing items        | 0.000006 |
| updating status      | 0.000012 |
| cleaning up          | 0.000003 |
+----------------------+----------+

SHOW STATUS like ‘Hand%’;

+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 13759 |
| Handler_read_last          | 0     |
| Handler_read_next          | 11501 |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 12    |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 4292  |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 9495  |
| Handler_tmp_write          | 2031  |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

解釋

+------+-------------+-------+------+---------------+--------+---------+--------------+------+---------------------------------+
| id   | select_type | table | type | possible_keys | key    | key_len | ref          | rows | Extra                           |
+------+-------------+-------+------+---------------+--------+---------+--------------+------+---------------------------------+
|    1 | SIMPLE      | t     | ALL  | PRIMARY       | NULL   | NULL    | NULL         | 2258 | Using temporary; Using filesort |
|    1 | SIMPLE      | pt    | ref  | tag_id        | tag_id | 4       | website.t.id |    5 | Using index                     |
+------+-------------+-------+------+---------------+--------+---------+--------------+------+---------------------------------+

大約需要 23 毫秒。tags 表有大約 2000 行，而 pic_tag 是一個 pivot(?) 表，其中保存了圖片和標籤的關係。(pic_id - tag_id) 它有大約 11000 行。

我認為 UNION 幾乎是太多的程式碼，對於 12 或 15 或我想顯示的標籤。有沒有辦法以某種方式處理 tmp 表？還是 tmp 表本身不是問題？

根據著名的文章“修復ORDER BY rand()”，特別是通過 union 進行多選，我們可以這樣寫：

(
SELECT   `p1`.*,
         `users`.`username`,
         `users`.`displayname`
FROM     pics p1
JOIN
         (SELECT ceil(rand() *
                 (SELECT max(id)
                  FROM   pics)) AS id
         ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)
UNION ALL
(
SELECT   `p1`.*,
         `users`.`username`,
         `users`.`displayname`
FROM     pics p1
JOIN
         (SELECT ceil(rand() *
                 (SELECT max(id)
                  FROM   pics)) AS id
         ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)
UNION ALL
(
SELECT   `p1`.*,
         `users`.`username`,
         `users`.`displayname`
FROM     pics p1
JOIN
         (SELECT ceil(rand() *
                 (SELECT max(id)
                  FROM   pics)) AS id
         ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)
UNION ALL
(
SELECT   `p1`.*,
         `users`.`username`,
         `users`.`displayname`
FROM     pics p1
JOIN
         (SELECT ceil(rand() *
                 (SELECT max(id)
                  FROM   pics)) AS id
         ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)

此方法不會創建完美的隨機數，因為它假設 id 的分佈是有規律的。此外，聯合都可以選擇一行兩次。這也不是完全相同的查詢，我使用 anINNER JOIN是為了不選擇沒有圖片的使用者，但這很容易修復。

您在 5.6 中的方法 ( ORDER BY rand())（可能已經針對 5.5 進行了優化）：

mysql> SHOW STATUS like 'Hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 4     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 1     |
| Handler_read_key           | 6041  |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 4     |
| Handler_read_rnd_next      | 12122 |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 6060  |
+----------------------------+-------+
18 rows in set (0.00 sec)

我的方法（文章一）：

mysql> SHOW STATUS like 'Hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 24    |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 4     |
| Handler_read_key           | 12    |
| Handler_read_last          | 4     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 9     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 8     |
+----------------------------+-------+
18 rows in set (0.06 sec)

如您所見，讀取和寫入已從數千次更改為 12 次讀取和 8 次寫入。

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/80712

相關問答

Mysql

查詢以根據應用於同一表的比較過濾表？

  • June 25, 2019
檢視問答
Mysql

這個觸發器會顯著降低性能嗎？

  • July 23, 2018
檢視問答
Mysql

查詢中的 int 比較非常慢

  • July 22, 2015
檢視問答
Mysql

使用定義的索引加入操作？

  • October 10, 2014
檢視問答
Mysql

是否有一個布爾列來儲存多值屬性的值是否是一個糟糕的微優化？

  • August 5, 2022
檢視問答
Mysql

優化對字元串中多個術語中的任何一個的查詢

  • June 27, 2022
檢視問答