Mysql

添加第二個 Group By 列會減慢查詢速度

  • October 23, 2015

我有以下查詢：

SELECT 'TEXT', 'TYPE', 'DESTINATION', 'HEADLINE', 'COUNTRY_ID', 'COUNTRY_CODE'
UNION ALL
SELECT Data_1.TEXT, Data_1.TYPE, Data_2.Destination, Data_2.HEADLINE,
   Data_3.COUNTRY_ID, Data_4.Country_Code
FROM Data_1
LEFT JOIN Data_2 on Data_1.GROUP_ID=Data_2.GROUP_ID
LEFT JOIN Data_3 on Data_1.GROUP_ID=Data_3.GROUP_ID
LEFT JOIN Data_4 on Data_3.COUNTRY_ID=Data_4.COUNTRY_ID
WHERE Data_2.DESTINATION LIKE "%88327%"
GROUP BY Data_1.TEXT, Data_1.GROUP_ID

只要我GROUP BY Data_1.TEXT一切都很好並且查詢執行得很快。但是當添加第二個欄位來分組時，GROUP BY Data_1.TEXT, Data_1.GROUP_ID查詢不再結束。（事實上，LIMIT 10 查詢需要大約 2 小時，而GROUP BY Data_1.TEXT僅使用 0.06 秒）

分析結果：

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000075 |
| checking permissions | 0.000005 |
| checking permissions | 0.000002 |
| checking permissions | 0.000004 |
| Opening tables       | 0.000026 |
| init                 | 0.000044 |
| System lock          | 0.000009 |
| optimizing           | 0.000017 |
| statistics           | 0.000053 |
| preparing            | 0.000019 |
| Creating tmp table   | 0.000025 |
| Sorting result       | 0.000004 |
| executing            | 0.000002 |
| Sending data         | 8.388898 |
| end                  | 0.000008 |
| removing tmp table   | 0.000227 |
| end                  | 0.000005 |
| query end            | 0.000007 |
| closing tables       | 0.000013 |
| freeing items        | 0.000016 |
| logging slow query   | 0.000024 |
| cleaning up          | 0.000010 |
+----------------------+----------+`
(stopped the query after approx. 10 sec)

我做了很多關於可能原因的閱讀，所有提到的文章都可能存在錯誤或失去的索引，導致發送數據非常慢。

聲明的解釋如下所示：

+----+-------------+--------+------+---------------+------------+---------+-----------------------------------+--------+---------------------------------+
| id | select_type | table  | type | possible_keys | key        | key_len | ref                               | rows   | Extra                           |
+----+-------------+--------+------+---------------+------------+---------+-----------------------------------+--------+---------------------------------+
|  1 | SIMPLE      | Data_1 | ALL  | GROUP_ID      | NULL       | NULL    | NULL                              | 981494 | Using temporary; Using filesort |
|  1 | SIMPLE      | Data_3 | ref  | GROUP_ID      | GROUP_ID   | 9       | reports_v201506.Data_1.ADGROUP_ID |      2 | NULL                            |
|  1 | SIMPLE      | Data_4 | ref  | COUNTRY_ID    | COUNTRY_ID | 768     | reports_v201506.Data_3.COUNTRY_ID |      1 | NULL                            |
|  1 | SIMPLE      | Data_2 | ref  | GROUP_ID      | GROUP_ID   | 9       | reports_v201506.Data_1.ADGROUP_ID |     36 | Using where                     |
+----+-------------+--------+------+---------------+------------+---------+-----------------------------------+--------+---------------------------------+

似乎 Data_1 表沒有使用任何鍵。但是設置了以下索引：

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Data_1 |          1 | TEXT     |            1 | TEXT        | A         |       75499 |     NULL | NULL   | YES  | BTREE      |         |               |
| Data_1 |          1 | GROUP_ID |            1 | GROUP_ID    | A         |       23368 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Data_2 |          0 | PRIMARY  |            1 | ROW_ID      | A         |      557211 |     NULL | NULL   |      | BTREE      |         |               |
| Data_2 |          1 | GROUP_ID |            1 | GROUP_ID    | A         |       15478 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Data_3 |          0 | PRIMARY    |            1 | ROW_ID      | A         |       44782 |     NULL | NULL   |      | BTREE      |         |               |
| Data_3 |          1 | GROUP_ID   |            1 | GROUP_ID    | A         |       22391 |     NULL | NULL   | YES  | BTREE      |         |               |
| Data_3 |          1 | COUNTRY_ID |            1 | COUNTRY_ID  | A         |         285 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Data_4 |          1 | COUNTRY_ID   |            1 | COUNTRY_ID   | A         |       86652 |     NULL | NULL   | YES  | BTREE      |         |               |
| Data_4 |          1 | COUNTRY_Code |            1 | COUNTRY_Code | A         |        1733 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

請在下面找到結果SHOW CREATE TABLE Data_1

+-------------------+------------------------------------+
| Table             | Create Table                       
+-------------------+------------------------------------+
| Data_1 | CREATE TABLE `Data_1` (
 `ROW_ID` varchar(255) NOT NULL,
 `ACCOUNT_ID` bigint(20) DEFAULT NULL,
 `DATE_END` varchar(255) DEFAULT NULL,
 `DATE_RANGE_TYPE` varchar(255) DEFAULT NULL,
 `DATE_START` varchar(255) DEFAULT NULL,
 `PARTNER_ID` bigint(20) DEFAULT NULL,
 `TIMESTAMP` datetime DEFAULT NULL,
 `TOP_ACCOUNT_ID` bigint(20) DEFAULT NULL,
 `ACCOUNT_DESCRIPTIVE_NAME` varchar(255) DEFAULT NULL,
 `ACCOUNTTIMEZONEID` varchar(255) DEFAULT NULL,
 `CUSTOMER_DESCRIPTIVE_NAME` varchar(255) DEFAULT NULL,
 `DAY` datetime DEFAULT NULL,
 `DAYOFWEEK` varchar(255) DEFAULT NULL,
 `IMPRESSIONS` bigint(20) DEFAULT NULL,
 `MONTH` datetime DEFAULT NULL,
 `MONTH_OF_YEAR` varchar(255) DEFAULT NULL,
 `PRIMARYCOMPANYNAME` varchar(255) DEFAULT NULL,
 `QUARTER` varchar(255) DEFAULT NULL,
 `WEEK` varchar(255) DEFAULT NULL,
 `YEAR` bigint(20) DEFAULT NULL,
 `GROUP_ID` bigint(20) DEFAULT NULL,
 `GROUP_NAME` varchar(255) DEFAULT NULL,
 `TEXT` varchar(255) DEFAULT NULL,
 `STATUS` varchar(32) DEFAULT NULL,
 `URL_CUSTOM_PARAMETERS` longtext,
 KEY `TEXT` (`TEXT`),
 KEY `GROUP_ID` (`GROUP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------------+------------------------------------+

請在下面找到結果SHOW CREATE TABLE Data_2

+-------------+----------------------------------------------+
| Table       | Create Table                                 
+-------------+----------------------------------------------+
| Data_2 | CREATE TABLE `Data_2` (
 `ROW_ID` varchar(255) NOT NULL,
 `ACCOUNT_ID` bigint(20) DEFAULT NULL,
 `DATE_END` varchar(255) DEFAULT NULL,
 `DATE_RANGE_TYPE` varchar(255) DEFAULT NULL,
 `DATE_START` varchar(255) DEFAULT NULL,
 `PARTNER_ID` bigint(20) DEFAULT NULL,
 `TIMESTAMP` datetime DEFAULT NULL,
 `TOP_ACCOUNT_ID` bigint(20) DEFAULT NULL,
 `ACCOUNT_DESCRIPTIVE_NAME` varchar(255) DEFAULT NULL,
 `ACCOUNTTIMEZONEID` varchar(255) DEFAULT NULL,
 `CUSTOMER_DESCRIPTIVE_NAME` varchar(255) DEFAULT NULL,
 `DAY` datetime DEFAULT NULL,
 `DAYOFWEEK` varchar(255) DEFAULT NULL,
 `IMPRESSIONS` bigint(20) DEFAULT NULL,
 `MONTH` datetime DEFAULT NULL,
 `MONTH_OF_YEAR` varchar(255) DEFAULT NULL,
 `PRIMARYCOMPANYNAME` varchar(255) DEFAULT NULL,
 `QUARTER` varchar(255) DEFAULT NULL,
 `WEEK` varchar(255) DEFAULT NULL,
 `YEAR` bigint(20) DEFAULT NULL,
 `GROUP_ID` bigint(20) DEFAULT NULL,
 `GROUP_NAME` varchar(255) DEFAULT NULL,
 `CONTENT_ID` bigint(20) DEFAULT NULL,
 `STATUS` varchar(32) DEFAULT NULL,
 `CONTENT_TYPE` varchar(255) DEFAULT NULL,
 `AVERAGE_PAGEVIEWS` decimal(19,2) DEFAULT NULL,
 `AVERAGE_TIME_ON_SITE` decimal(19,2) DEFAULT NULL,
 `BOUNCE_RATE` decimal(19,2) DEFAULT NULL,
 `DESTINATION` longtext,
 `HEADLINE` varchar(255) DEFAULT NULL,
 `LABELS` longtext,
 `CONTENT1` varchar(128) DEFAULT NULL,
 `CONTENT2` varchar(128) DEFAULT NULL,
 PRIMARY KEY (`ROW_ID`),
 KEY `GROUP_ID` (`GROUP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+----------------------------------------------+

請在下面找到結果SHOW CREATE TABLE Data_3

+--------------+-------------------------------------------------+
| Table        | Create Table                          
+--------------+-------------------------------------------------+
| Data_3 | CREATE TABLE `Data_3` (
 `ROW_ID` varchar(255) NOT NULL,
 `ACCOUNT_ID` bigint(20) DEFAULT NULL,
 `DATE_END` varchar(255) DEFAULT NULL,
 `DATE_RANGE_TYPE` varchar(255) DEFAULT NULL,
 `DATE_START` varchar(255) DEFAULT NULL,
 `PARTNER_ID` bigint(20) DEFAULT NULL,
 `TIMESTAMP` datetime DEFAULT NULL,
 `TOP_ACCOUNT_ID` bigint(20) DEFAULT NULL,
 `ACCOUNT_DESCRIPTIVE_NAME` varchar(255) DEFAULT NULL,
 `ACCOUNTTIMEZONEID` varchar(255) DEFAULT NULL,
 `NETWORK` varchar(32) DEFAULT NULL,
 `DAY` datetime DEFAULT NULL,
 `DAYOFWEEK` varchar(255) DEFAULT NULL,
 `IMPRESSIONS` bigint(20) DEFAULT NULL,
 `MONTH` datetime DEFAULT NULL,
 `MONTH_OF_YEAR` varchar(255) DEFAULT NULL,
 `PRIMARYCOMPANYNAME` varchar(255) DEFAULT NULL,
 `QUARTER` varchar(255) DEFAULT NULL,
 `WEEK` varchar(255) DEFAULT NULL,
 `YEAR` bigint(20) DEFAULT NULL,
 `FORMAT` varchar(255) DEFAULT NULL,
 `GROUP_ID` bigint(20) DEFAULT NULL,
 `GROUP_NAME` varchar(255) DEFAULT NULL,
 `GROUP_STATUS` varchar(32) DEFAULT NULL,
 `CITY_ID` varchar(255) DEFAULT NULL,
 `COUNTRY_ID` varchar(255) DEFAULT NULL,
 `LOCATION_TYPE` varchar(32) DEFAULT NULL,
 `METRO_ID` varchar(255) DEFAULT NULL,
 `REGION_ID` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`ROW_ID`),
 KEY `ADGROUP_ID` (`ADGROUP_ID`),
 KEY `COUNTRY_ID` (`COUNTRY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+-------------------------------------------------+

請在下面找到結果SHOW CREATE TABLE Data_4

+---------------------+----------------------------------------------+
| Table               | Create Table                                 |
+---------------------+----------------------------------------------+
| Data_4 | CREATE TABLE `Data_4` (
 `COUNTRY_ID` varchar(255) DEFAULT NULL,
 `Name` varchar(60) DEFAULT NULL,
 `Canonical_Name` varchar(83) DEFAULT NULL,
 `Parent_ID` varchar(7) DEFAULT NULL,
 `Country_Code` varchar(2) DEFAULT NULL,
 `Target_Type` varchar(22) DEFAULT NULL,
 `Status` varchar(15) DEFAULT NULL,
 KEY `COUNTRY_ID` (`COUNTRY_ID`),
 KEY `Country_Code` (`Country_Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------------------+----------------------------------------------+

快速解釋表格的目的和內容：

  • Data_1 包含有關標籤使用情況的報告數據（使用情況 = 展示次數）
  • Data_2 包含有關內容使用情況的報告數據（使用情況 = 展示次數）
  • Data_3 包含有關地理使用情況的報告數據（使用情況 = 展示次數）
  • Data_4 包含一個用於匹配 ISO 國家程式碼的表

我的錯誤是什麼？

Data_1 需要INDEX(text, group_id)

Data_2 需要`INDEX

其他注意事項：

  • 最好使用現實的限制，而不是盲目的“255”。
  • 最好PRIMARY KEY為 InnoDB 表顯式提供一個。
  • 但是VARCHAR(255)對於一個PRIMARY KEY.
  • group_id和group_name是一對一的關係嗎？如果是這樣，建立一個表來描述它；不要簡單地將其隱藏在其他表格中。
  • BIGINT佔用 8 個字節；INT UNSIGNED處理 0 到 40 億的值。您真的期望超過 40 億個“合作夥伴”、“團體”等嗎？
  • 如果DATE_START是“日期”或“日期時間”，請不要使用VARCHAR(255)，您將無法進行比較、範圍、算術等。
  • 真的Data_2.DESTINATION是一個LONGTEXT可能將“88327”埋在中間某處的東西嗎？如果它在開始時（或單獨），那麼有一種更好的方法來進行查詢。

還…

`Country_Code` varchar(2) DEFAULT NULL,

–>

`Country_Code` CHAR(2) DEFAULT NULL CHARACTER SET ascii,

然後將其PRIMARY KEY設為 Data_4。並擺脫country_code.

在您提出了大部分這些建議之後，我們可以再次查看是否GROUP BY仍然存在問題。

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

相關問答

Mysql

通過自我 JOIN 和 GROUP BY 更新表

  • December 29, 2021
檢視問答
Mysql

獲取多個表上的插入值/唯一鍵/ MySQL

  • September 24, 2021
檢視問答
Mysql

為什麼表 A 上的 GROUP BY 會導致表 B 上“使用臨時；使用文件排序”？

  • August 9, 2021
檢視問答
Mysql

如何在 SQL 中返回帶有內部列表的列表？

  • June 8, 2021
檢視問答
Mysql

如何使用 GROUP BY 更快地更新？

  • May 27, 2021
檢視問答
Mysql

MYSQL：查詢聊天消息的最後轉換並按最新消息排序

  • April 23, 2021
檢視問答