php - 大表上的 MySQL 查询性能问题

Question

我遇到的性能问题太大了。我正在使用 PHP 脚本从数据库中获取数据并将其插入到另一个表中(该脚本只是在数据库上运行查询，我粘贴在下面以获取数据，然后 INSERT)。我需要连接两个表并输出结果(填写第 3 个表)，但其中一个表有 390 万行数据，第二个表有 416k 行。问题是我必须在 varchar 类型上使用连接，因为表没有以任何方式连接(通过“名称”列连接)，因为它是它们唯一共有的列。表结构是这样的:

  TABLE streets
  id (PK, int), 
  name varchar(70), 
  postnrid (FK, int)

  TABLE csvstreets 
  id (PK, int),
  lat decimal(12,10),
  lng decimal(12,10),
  streetname varchar(70)

streets 表有 390 万行，查询如下所示:

 SELECT s.id, c.lat, c.lng FROM streets AS s JOIN csvstreets AS c ON LOWER(s.name) = LOWER(c.streetname) 

查询连续运行了 2 天，但没有完成，所以我不得不取消它。我已经为两个表上的两个“名称”列编制了索引。在对查询执行 EXPLAIN 时，它会输出:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'c', 'ALL', NULL, NULL, NULL, NULL, '416240', ''
'1', 'SIMPLE', 's', 'index', NULL, 'name', '73', NULL, '3890226', 'Using where; Using index; Using join buffer (flat, BNL join)'

“csvstreets”表似乎没有使用我为其创建的索引。我可以使用任何建议，因为我已经尝试了所有可以找到的提高 varchar 连接性能的方法。我也不能限制结果输出，因为我需要它返回的所有数据(大约 380k)。我在脚本中添加了 echo 以查看它停止的位置，但它只是运行 SELECT 查询 2 天，从来没有到 INSERT 部分。

Answer 1

去掉 lower()s。它阻止连接使用您的索引，除非您设置了区分大小写的排序规则，否则您不需要它。当然没有必要区分“Balsta 300”和“Balsta 301”。

如果在此之后您的查询仍未完成，请显示所有的输出:

show create table streets 
show create table csvstreets
explain SELECT s.id, c.lat, c.lng FROM streets AS s JOIN csvstreets AS c ON s.name=c.streetname