MySQL 有些查询永远不会执行

Question

我正在进行我的第一个数据科学项目，但在使用 MySQL Workbench 进行极其缓慢的查询时遇到了一些问题。

这是我的3个表(每个表都来自各个网站的数据集，已清理并插入MySQL):

CREATE TABLE IF NOT EXISTS `starbucks` (
   `STORE_NUMBER` varchar(20) NOT NULL,
   `CITY` varchar(50) NOT NULL,
   `STATE` char(2) NOT NULL,
   `ZIPCODE` char(5) NOT NULL,
   `LONG` varchar(10) NOT NULL,
   `LAT` varchar(10) NOT NULL,
   PRIMARY KEY (`STORE_NUMBER`)
   )ENGINE=InnoDB")

CREATE TABLE IF NOT EXISTS `income`(
   `STATEFIPS` char(2) NOT NULL,
   `STATE` char(2) NOT NULL,
   `ZIPCODE` char(5) NOT NULL,
   `AGI_STUB` tinyint NOT NULL,
   `NUM_RETURNS` float(15,4) NOT NULL,
   `TOTAL_INCOME` float(15,4) NOT NULL,
   PRIMARY KEY (`STATE`, `ZIPCODE`, `AGI_STUB`)
   )ENGINE=InnoDB")

CREATE TABLE IF NOT EXISTS `diversity`(
   `COUNTY` varchar(50) NOT NULL,
   `STATE` char(2) NOT NULL,
   `INDEX` float(7,6) NOT NULL,
   `1` float(3,1) NOT NULL,
   `2` float(3,1) NOT NULL,
   `3` float(3,1) NOT NULL,
   `4` float(3,1) NOT NULL,
   `5` float(3,1) NOT NULL,
   `6` float(3,1) NOT NULL,
   `7` float(3,1) NOT NULL,
   PRIMARY KEY (`COUNTY`, `STATE`)
   )ENGINE=InnoDB")

starbucks 有 13,608 条记录，收入有166,740条记录，diversity 有 3,143 条记录。

我尝试运行的查询:

SELECT  i.TOTAL_INCOME,
    CASE
        WHEN s.STORE_NUMBER IS NOT NULL THEN 1
        ELSE 0
    END AS has_starbucks
  FROM  income as i
  LEFT  OUTER JOIN starbucks as s
    ON  i.ZIPCODE = s.ZIPCODE

如果我将结果限制为 1,000 行，它将运行得很快，但是我需要获取所有记录(无行限制)，这会导致查询永远不会返回，并最终超时并断开我与 MySQL 的连接服务器。过去，当我为拥有数百万条记录的数据库的公司工作时，我从未遇到过这么大的麻烦。

我需要进行哪些表优化才能解决此问题？我需要更改哪些 MySQL 设置？欢迎任何其他建议。

编辑看起来查询的“持续时间”永远不会超过 0.500 秒，“获取”部分持续 > 120 秒。我不确定这是否是有用的信息。

Answer 1

第一个问题是在连接列上创建适当的索引

 CREATE INDEX idx1 ON starbucks (ZIPCODE );
 CREATE INDEX idx2 ON income (ZIPCODE );

或添加您选择的列的详细索引

CREATE INDEX idx2 ON income (ZIPCODE , TOTAL_INCOME);

并使用解释计划检查行为