数据库工作的优化
有一个足够大的商品数据库，而且还在稳步增长。现在有超过1000万的商品在数据库中。
有一个好的，也有它的类别。每种商品都有以下属性:名称、价格、售出商品的数量、保修标志和质量等。产品的特征仅限于特定的类别。商品的属性具有下一个格式- 2000:10000 (属性的类别:属性的价值)。某些类别的属性和属性本身可能在不同的类别中重叠，例如品牌。标题和属性的过滤、排序和搜索是通过这些类别和属性来执行的。产品可以链接到一个或多个类别。
起初，我们只使用mysql，并通过为每个类别创建一个表来存储商品。通过这种方式，我们有大约6-7000张桌子和商品。在选择时，我们向它们中的每一个发出请求，并在操作符UNION的帮助下合并请求。随着商品数量和种类的增加，挑选的时间开始变得很长，并铺设了mysql服务器。在此之后，我们将所有产品移动到一个表中。表结构为follows。
有1000万个产品的表格使得mysql现在很难使用。如果不讨论排序，从其中进行选择是不太可能的。我们使用狮身人面像，索引狮身人面像：
sql_query = SELECT \
ti.item_id, \
ti.item_id AS iid, \
crc32(ti.item_nick) AS nick, \
ti.item_title AS title, \
ti.item_sold AS sold, \
ti.item_rating AS rating, \
ti.item_popular AS popular, \
ti.item_warranty AS warranty, \
ROUND(ti.item_price*100, 0) AS price, \
ti.item_props AS props, \
COUNT(c.comment_iid) AS comments, \
GROUP_CONCAT(tcir.category_item_ref_tid) AS tids \
FROM item AS ti \
LEFT JOIN comment AS c ON ti.item_id = c.comment_iid \
INNER JOIN category_item_ref AS tcir ON ti.item_id = tcir.category_item_ref_iid \
WHERE ti.item_id >= $start AND ti.item_id <= $end \
GROUP BY ti.item_id
sql_attr_uint = sold
sql_attr_uint = rating
sql_attr_uint = comments
sql_attr_uint = warranty
sql_attr_bigint = iid
sql_attr_bigint = nick
sql_attr_bigint = price
sql_attr_bigint = popular
sql_attr_multi = uint tids from field;通过Sphinx进行搜索会更快，但是有很多属性，特别是sql_attr_multi tids，这会减慢搜索和排序的速度。60万件商品的抽样时间约为18 ~ 19秒。我们尝试将产品仅绑定到一个类别( tids属性变为sql_attr_uint)。采样时间减少到3~5秒，这也不是很好。
你能告诉我我做错了什么吗，用另一种方式为sphinx建立一个索引可能是值得的，因为我认为它应该工作得更快。也许，我需要以另一种方式构建表结构，或者为数据库使用不同的平台，例如MySQL、MongoDB、PostgreSQL、MariaDB。
回答 1
Stack Overflow用户
发布于 2013-09-26 04:10:21
你正面临着许多其他公司遇到的问题，这些公司都遇到了大型数据集。你很幸运，因为你的用例看起来是大量的读取，但很小的写入，因为这两个问题在一起更糟糕:-)重要的是要理解数据库系统只不过是允许索引和锁以及优化快速搜索(在数据和索引中)的虚拟化文件系统。
使用适当的查询，表中的近10m项没有理由不是必须快速的。但是你需要优化系统和查询。什么意思？
您说过您希望支持对某一类别的商品进行快速排序。我应该如何设计它？
假设有10m个项目，10k个类别，所以每个类别都有100个项目，通过值在一个类别中排序意味着有重复的数据，包括类别和价格，以排序的方式-以索引的形式，包括类别id和价格value
- Performed查询以适当的方式只需使用这个索引。首先，它搜索速度很快的类别，因为它是用某种哈希表以索引的形式表示的，其中10m行的索引可能会在一次读取中被读取，例如，在硬盘(驱动器)读取时，MS SQL缓存512kb。一旦您在索引中找到所需的类别，您就获得了排序的100个项目，因此您获得了需要在驱动器上找到的物理行in的集合。最后一步是物理读取100个数据库行的id，这可能需要几毫秒的时间，即使是随机选择的标识符。
我写这一节是为了说明，即使是一个大型数据库表也可以快速处理您的查询，但是您需要调优查询并提供特定的适当索引。
你应该尝试经典的方法：
- 编写用例-哪些是我希望在系统中优化的顶级查询？
- 利用这些查询优化您的表和索引
对我来说，似乎没有必要在更多的表中削减数据，您应该通过使用上面的方法消除查询需要搜索的数据量-只需使用正确的索引即可。
你提到了表的连接。对于大数据来说，这可能是非常长的操作，所以流行的系统是复制数据，并且只提供一个表(最快的方法)来从其他表中搜索重复的数据。明显的问题是更新这些数据，因为您需要自动更新两个表。一旦你谈到只读，这对你来说似乎不是一个真正的问题-当你更新原始数据时，你可以只更新重复的数据。
还有其他几种方法可以处理大量的阅读和写作。研究像twitter或facebook这样的顶级互联网公司的架构，并找出他们是如何处理类似问题的，这是很好的。
https://stackoverflow.com/questions/19001673
复制相似问题
腾讯云开发者
