MySql - 我怎樣才能加快這個查詢

我有以下表格：

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `last_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `account_data` text COLLATE utf8_unicode_ci,
 `created_at` datetime DEFAULT NULL,
 `updated_at` datetime DEFAULT NULL,
 `twitter_username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `crypted_password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `password_salt` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `persistence_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `single_access_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `perishable_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `login_count` int(11) NOT NULL DEFAULT '0',
 `failed_login_count` int(11) NOT NULL DEFAULT '0',
 `last_request_at` datetime DEFAULT NULL,
 `current_login_at` datetime DEFAULT NULL,
 `last_login_at` datetime DEFAULT NULL,
 `current_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `last_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `is_admin` tinyint(1) DEFAULT '0',
 `referrer_id` int(11) DEFAULT NULL,
 `partner` tinyint(1) DEFAULT '0',
 `subscription_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'free',
 `workflow_state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `persona_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `persona_index` (`persona_id`)
) ENGINE=InnoDB 

和表格：

CREATE TABLE `user_actions` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` int(11) DEFAULT NULL,
 `action_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `module` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `data` text COLLATE utf8_unicode_ci,
 `timestamp` datetime DEFAULT NULL,
 `created_at` datetime DEFAULT NULL,
 `updated_at` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id_index` (`user_id`),
 KEY `action_type_index` (`action_type`),
 KEY `user_action_type_index` (`user_id`,`action_type`),
 KEY `timestamp_index` (`timestamp`),
 KEY `user_id_timestamp_index` (`user_id`,`timestamp`)
) ENGINE=InnoDB 

問題在於以下查詢：

   SELECT user_actions.*, users.twitter_username, users.email FROM `user_actions` 
INNER JOIN users ON (user_actions.user_id=users.id) ORDER BY timestamp DESC LIMIT 0, 30

這是解釋：

user_actions    
The table was retrieved with this index: user_id_timestamp_index
You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
Approximately 76 rows of this table were scanned.
users   
This table was retrieved with a full table scan, which is often quite bad for performance, unless you only retrieve a few rows.
The table was retrieved with this index:
No index was used in this part of the query.
A temporary table was created to access this part of the query, which can cause poor performance. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.
MySQL had to do an extra pass to retrieve the rows in sorted order, which is a cause of poor performance but sometimes unavoidable.
You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
Approximately 3445 rows of this table were scanned.

這個查詢需要很長時間才能執行，有什麼想法可以改進嗎？

這是您的原始查詢：

SELECT
   user_actions.*,
   users.twitter_username,
   users.email
FROM
   `user_actions`  
   INNER JOIN users
   ON (user_actions.user_id=users.id)
   ORDER BY timestamp
   DESC LIMIT 0, 30
;

我注意到的第一件事是您要加入兩個完整的表。由於您只需要twitter_username和email來自users表，因此您應該只users使用三列連接id：twitter_username和email。

第二件事是LIMIT條款。它在加入後執行。您應該在加入之前執行它。在您的情況下，您請求 30 個最近的使用者操作。如果您可以保證從 中僅檢索 30 行user_actions，則連接應該執行得更快。

如果您從 @DTest 閱讀答案，他的前兩個要點已經告訴您查詢出了什麼問題，因為 mysql 在從每個表中收集數據時將採取的操作。關鍵是要了解在處理查詢時臨時表的外觀以及數據將駐留的位置（記憶體或磁碟）。

您需要做的是重構查詢以欺騙 MySQL 查詢優化器。強制查詢生成較小的臨時表。在大多數情況下，my.cnf 中的配置更改應該會產生巨大的影響。在其他情況下，例如這種情況，重構查詢可能就足夠了。

這是我對您的查詢的建議更改，它應該可以更快地工作：

SELECT
   ua.*,
   u.twitter_username,
   u.email
FROM
   (SELECT * FROM `user_actions`
   ORDER BY timestamp DESC LIMIT 30) ua
   LEFT JOIN
   (SELECT id,twitter_username,email FROM `users`) u
   ON (ua.user_id=u.id)
;

以下是重構查詢的原因：

原因 #1

如果您查看內聯表ua，我只使用LIMIT. 無論桌子有**多大，都會發生這種情況user_actions。它已經被訂購，因為ORDER BY timestamp DESC發生在LIMIT.

原因 #2

如果你看一下內聯表u，它有id, twitter_username, email. id是實現連接所必需的。

原因 #3

我使用LEFT JOIN而不是INNER JOIN出於兩（2）個原因：

1. 保留基於查詢的順序ua
2. 如果表中的 user_idua不再存在，則顯示所有使用者操作users。

做這些事情會迫使臨時表變小。儘管如此，您仍然需要從@DTest 的回答中實施要點#3，以搶占臨時表在磁碟上的位置。

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