mysql - 查询花费太多时间(100k 条记录需要 3 分钟)

Question

下面的查询和解释计划需要 3 分钟以上，请建议我如何减少时间:Select 语句和 count(*) 都花费相同的时间。

SELECT
     s.sap_id AS 'SAP ID', 
     z.name AS 'Circle' , 
     m.mme_host_name AS 'MME HostName',  
     case when mmelog.retry_count is null then  m.mme_status  when mmelog.retry_count is not null then  mmelog.status else null end AS 'Status',   
     case when mmelog.retry_count is null then m.description   when mmelog.retry_count is not null then mmelog.reason   else null end AS 'Description'  , 
     m.modifiedTime AS Date, 
     ifnull(mmelog.retry_count,0)as 'Retry Count'
FROM
    site s
INNER JOIN zone z ON s.zone_id = z.id
INNER JOIN mme_result m ON m.site_id = s.id
LEFT OUTER JOIN mme_connection_log mmelog ON mmelog.sap_id = s.sap_id
WHERE
    m.modifiedTime >= '2015-07-25'
AND m.modifiedTime <= '2016-07-25'
ORDER BY
    m.modifiedTime,
    s.sap_id;

下面是解释计划:

----+-------------+--------+-------+--------------------------------------+---------------------+---------+----------------+-------+-----------------------------------------------------------------+
    | id | select_type | table  | type  | possible_keys                        | key                 | key_len | ref            | rows  | Extra                                                           |
    +----+-------------+--------+-------+--------------------------------------+---------------------+---------+----------------+-------+-----------------------------------------------------------------+
    |  1 | SIMPLE      | z      | index | PRIMARY                              | creator             | 5       | NULL           |    24 | Using index                                                     |
    |  1 | SIMPLE      | s      | ref   | PRIMARY,site_region_fk               | site_region_fk      | 5       | siteforge.z.id |  1915 | NULL                                                            |
    |  1 | SIMPLE      | m      | ref   | FK89FCE6D7459A62FF,modifiedTimeIndex | FK89FCE6D7459A62FF  | 5       | siteforge.s.id |     2 | Using where                                                     |
    |  1 | SIMPLE      | mmelog | index | NULL                                 | idx_sapid_mmeconlog | 258     | NULL           | 16616 | Using where; Using index; Using join buffer (Block Nested Loop) |
    +----+-------------+--------+-------+--------------------------------------+---------------------+---------+----------------+-------+---

| mme_连接_日志 |

CREATE TABLE `mme_connection_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mme_host_name` varchar(255) DEFAULT NULL,
  `modifiedTime` datetime DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `retry_count` int(11) DEFAULT NULL,
  `sap_id` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `createdTime` datetime DEFAULT NULL,
  `creator` int(10) unsigned DEFAULT NULL,
  `last_modifier` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sap_id` (`sap_id`,`mme_host_name`),
  KEY `FKAD24771D82DD582F` (`creator`),
  KEY `FKAD24771D43DE0263` (`last_modifier`),
  KEY `mmehostINdex` (`mme_host_name`),
  KEY `idx_sapid_mmeconlog` (`sap_id`),
  CONSTRAINT `FKAD24771D43DE0263` FOREIGN KEY (`last_modifier`) REFERENCES `users` (`userid`),
  CONSTRAINT `FKAD24771D82DD582F` FOREIGN KEY (`creator`) REFERENCES `users` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=55047 DEFAULT CHARSET=latin1 |

| mme_结果 |

CREATE TABLE `mme_result` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  `mme_host_name` varchar(255) DEFAULT NULL,
  `mme_status` varchar(255) DEFAULT NULL,
  `modifiedTime` datetime DEFAULT NULL,
  `zone_id` int(10) unsigned DEFAULT NULL,
  `site_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mme_host_name` (`mme_host_name`,`site_id`),
  KEY `FK89FCE6D7C1D1CCDF` (`zone_id`),
  KEY `FK89FCE6D7459A62FF` (`site_id`),
  KEY `mmehostINdex` (`mme_host_name`),
  KEY `modifiedTimeIndex` (`modifiedTime`),
  CONSTRAINT `FK89FCE6D7459A62FF` FOREIGN KEY (`site_id`) REFERENCES `site` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK89FCE6D7C1D1CCDF` FOREIGN KEY (`zone_id`) REFERENCES `zone` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=914671 DEFAULT CHARSET=latin1 |

SAPID 不明确

Answer 1

当您拥有UNIQUE(a,b)时，您不需要KEY(a)。每个表都有一个这样的冗余索引。

如果您从查询中删除zone，您会得到正确的答案吗？似乎只是说“我们必须有一个区域入口”。

m 可能会受益于 INDEX(site_id,modifiedTime)(按顺序)。

大多数列都是NULL。那些永远不会被设置为NULL的应该声明为NOT NULL。

不要盲目地说varchar(255)，尤其是对于键。看看是否可以让它们变小(没有溢出的风险)。

LEFT JOIN 和 COUNT(*) 看起来很奇怪。这意味着您正在计算“右”表中缺失的行；你能证明LEFT吗？

您能给我们看看另外两张 table 吗？可能还有进一步的建议。