有4,000,000记录的MySQL表？

Question

我必须管理的网站是一个工人搜索引擎(黄页样式)。

我创建了这样一个数据库：

People: <---- 4,000,000 records
id
name
address
id_activity <--- linked to the activites table
tel
fax
id_region <--- linked to the regions table

activites: <---- 1500 activites
id
name_activity

regions: <--- 95 regions
id
region_name

locations: <---- 4,000,000 records
id_people
lat
lon

因此，基本上，我遇到慢问题的要求是选择一个选定城市周围的所有“工人”(由用户选择)。

我创建的请求完全正常，但返回结果需要5-6秒.

基本上，我在桌子上选择位置，选择一定半径内的所有城市，然后加入到people表中。

  SELECT people.*,id, lat, lng, poi,  
         (6371 * acos(cos(radians(plat)) * cos(radians(lat)) * cos(radians(lng) - radians(plon)) + sin(radians(plat)) * sin(radians(lat)))) AS distance 
    FROM locations,
         people 
   WHERE locations.id = people.id 
  HAVING distance < dist 
ORDER BY distance LIMIT 0 , 20; 

我的问题是：

1. 我的数据库设计得好吗？我不知道两张每张有4,000,000条记录的桌子是不是个好主意。在上面做个选择可以吗？
2. 我的要求设计得不好吗？
3. 我怎样才能加快搜索速度？

Answer 1

1. 设计看起来是标准化的。这是我希望在大多数设计良好的数据库中看到的。表中的数据量是重要的，但是是次要的。然而，如果People和Locations之间存在1比1的相关性，从查询中可以看出，我会说表应该是一个表。这当然会有帮助。
2. 您的SQL看起来不错，但是添加约束以减少所涉及的行数会有帮助。
3. 你需要索引您的表。这通常对缓慢(因为大多数开发人员根本不考虑数据库索引)最有帮助。

Answer 2

有几个基本的东西可能会使您的查询运行缓慢。

你的索引在你的桌子上是什么样子的？你在表上声明主键了吗？在没有索引的情况下，将两个表连接起来，每个表都有4M行，因此在DB上做了大量工作。一定要先把这件事做好。

如果您已经为您的DB构建了正确的索引，则可以查看缓存数据。您正在查询中进行计算，位置(lat/lon)通常是固定的吗？他们多久换一次？您的位置中的项目是否列出了实际的地点(城市、建筑物等)，或者它们是否记录了人们所处的位置(比如Foursquare签入)？

如果您的位置是地方，您可以进行许多很好的优化，如果您隔离您的部分数据，不经常变化，并预先计算它们之间的距离。

如果所有这些都失败了，请确保数据库服务器有足够的RAM。如果服务器能够将数据保存在内存中，那么它将大大加快速度。