什么是存储用于自动完成查询的巨大城市的最佳方式

我有城市列表，大约有2712406条记录。每个都有状态代码，用户首先选择状态代码。

StateCode和Name都已编入索引

我当前对数据库的查询以/cities?code=[StateCode]&name=[Alphabets] 的形式命中

内部翻译为

return db.Cities.Where(x=>x.StateCode == stateCode && x.Name.StartsWith(name))
                .OrderBy( x=> x.Name )
                .Take(10);

当数据库忙于执行一些更大的操作时，此查询会超时。

我想了两种方法，

存储S3对象

我计算出用户需要的组合总数是1000万。因此，假设5KB的JSON只用于检索前10个城市。它需要50GB的存储空间。大约每月5美元。哪一个是好的，但如果我将json存储为/stateCode/a /stateCode/b等，将更快地为使用CDN的用户服务？

合并州和城市密钥

假设我有一个单独的列组合在一起，索引为"StateCode/Name"。在这里，我知道查询将命中索引的B+树。在一列索引上查找会有更大的好处吗？或者它会和当前查询几乎相同，我对S3更满意。我不知道S3是否会提供和DB相同的性能，我所能做的就是在我的每个应用服务器中设置多个只读DB。

关于在SQL DB中组合State和City Key：如果将列分开，也可以使用B+树索引。您只需要使用[combined|concated|multi-column]索引。例如

CREATE INDEX blah ON cities (statecode, name);

注意：一个索引同时包含两列。与非精确运算符（StartWith而不是等于）一起使用的列排在最后。

如果StartWith方法转换为类似SQL的，则此索引支持您的查询

SELECT name
  FROM cities
 WHERE state = ?
   AND name LIKE 'b%'

如果StartsWith是以另一种方式实现的，您可能需要篡改以获得适当的响应时间。最好打开SQL查询日志并查看一下。您的ORM工具完全有可能将StartsWith转换为无法正确使用该索引的内容，但它可能很容易解决，因此工作良好（快速）。

ps：您在使用"take（10）"时没有给出ORDERBY（sql），这会产生未定义的结果。

pps.：我觉得S3的想法也很有趣；）

事实证明，组合TEXT INDEX效果良好。联接是一种昂贵的操作。

查询的性能优于两个不同结果集的AND。

@CityPath = StateCode + '/' + CharactersTyped + '%'
SELECT TOP 10 * FROM Cities WHERE CityPath LIKE @CityPath

在上面的查询中，B+索引必须搜索有限的子树，因为StateCode本身就是根。然而，在下面的查询中，B+索引必须搜索更大的子树，并且必须比较每个结果的StateCode等效性。CityName搜索的B+索引扫描的结果集在第二个查询中较大，这会导致微秒的超时。

@StateCode = StateCode
@CityName = CharactersTyped    
SELECT TOP 10 * FROM Cities WHERE CityName LIKE @CityName
     AND StateCode = @StateCode

如果数据存在于如上所示的层次结构中，则计算列索引可以改进对数据的搜索。

相关内容