MySQL查询优化: how can I speed up this query?

Question

这是我的 table :

  CREATE TABLE `tab_adasf` (
  `adasf_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `adasf_shopId` int(10) unsigned NOT NULL,
  `adasf_localId` bigint(20) unsigned NOT NULL,
  `adasf_shopState` varchar(255) DEFAULT NULL,
  `adasf_shopCity` varchar(255) DEFAULT NULL,
  `adasf_shopName` varchar(255) DEFAULT NULL,
  `adasf_shopDoor` varchar(255) DEFAULT NULL,
  `adasf_computerName` varchar(255) DEFAULT NULL,
  `adasf_channel` bigint(20) NOT NULL,
  `adasf_totalInside` bigint(20) NOT NULL,
  `adasf_totalOutside` bigint(20) NOT NULL,
  `adasf_createdAt` datetime NOT NULL,
  PRIMARY KEY (`adasf_id`),
  KEY `adasf_shopId` (`adasf_shopId`),
  KEY `adasf_localId` (`adasf_localId`),
  KEY `adasf_shopState` (`adasf_shopState`,`adasf_shopCity`,`adasf_shopName`,`adasf_shopDoor`),
  KEY `adasf_computerName` (`adasf_computerName`,`adasf_channel`,`adasf_createdAt`),
  CONSTRAINT `tab_adasf_ibfk_1` FOREIGN KEY (`adasf_shopId`) REFERENCES `tab_shop` (`shop_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1453500 DEFAULT CHARSET=utf8

正如 AUTO_INCRMENT 的值所示:它有 1453500 行。

为了生成 XML 文件，我需要如下结果集:

SELECT
    UPPER(adasf_shopState) AS adasf_shopState,
    UPPER(adasf_shopCity) AS adasf_shopCity,
    UPPER(adasf_shopName) AS adasf_shopName,
    UPPER(adasf_shopDoor) AS adasf_shopDoor,
    adasf_computerName,
    adasf_channel,
    SUM(adasf_totalInside) AS adasf_totalInside,
    SUM(adasf_totalOutside) AS adasf_totalOutside,
    YEAR(adasf_createdAt) AS year,
    MONTH(adasf_createdAt) AS month,
    DAY(adasf_createdAt) AS day,
    HOUR(adasf_createdAt) AS hour
    FROM tab_adasf 
WHERE 1=1 AND adasf_shopId = '1' AND HOUR(adasf_createdAt) BETWEEN '10:00' AND '21:00' 
GROUP BY
    UPPER(adasf_shopState),
    UPPER(adasf_shopCity),
    UPPER(adasf_shopName),
    UPPER(adasf_shopDoor),
    adasf_computerName,
    adasf_channel,
    YEAR(adasf_createdAt),
    MONTH(adasf_createdAt),
    DAY(adasf_createdAt),
    HOUR(adasf_createdAt)
ORDER BY
    UPPER(adasf_shopState),
    UPPER(adasf_shopCity),
    UPPER(adasf_shopName),
    UPPER(adasf_shopDoor),
    UPPER(adasf_computerName),
    adasf_channel,
    adasf_createdAt

运行和获取需要 3 分钟。

我的问题是:我做错了什么？如何加快此查询或表的速度？

提前致谢!

Answer 1

为了加快查询速度，您可以在tab_adasf(adasf_shopId)上创建索引。如果您有很多商店，这应该会对性能有很大帮助。

如果您需要执行大量此类查询，请考虑将 adasf_createdAt 列拆分为日期组件和时间组件。然后您可以在 tab_adasf(adasf_shopId,adasf_createdAt_time) 上创建索引，进一步帮助查询。

一般来说，除非有充分的理由，否则不建议将时间与日期时间分开。提高此类查询的性能构成了“充分的理由”。