我在一个网站上工作,这个网站现在有数百万条记录(很抱歉,不能透露是哪个网站),最初它有几百条记录,所以下面的查询是可以接受的
查询:SELECT*FROM…WHERE类别LIKE"%,3,%";
但现在它只会杀死数据库,因为对于每个查询,它都必须使用上面的查询遍历整个2Mil记录
类别表
ID名称
1名女性
2时尚
3服装
4配件
5顶部
6连衣裙
7耳环
8短裙
9长裙
10男
产品表
ID…。。类别….其他位
1,1,2,3,6,9,……
2,1,2,4,7,
3、1、2、3、5,
4,10,2,3,4,
你看到了上面发生的事情。现在,如果我对产品表中的类别行进行全文索引,它只给出1个基数:(
我该如何克服这一点?我已经考虑过用每个类别复制行,但数据库很大,目前有2个GIG,如果有重复,它将变成大约10个GIG……更像是一个问题,然后是一个解决方案
请记住,将数字存储为字符串,每个数字的字节数大约是将数字存储成整数的两倍。加上所有的逗号。
因此,如果你关心空间,以规范化的方式存储数据不会像你担心的那样扩展。
它将允许您编写利用索引的适当查询。因此,如果进行了一些扩展,您将用一点存储空间换取速度的大幅提高。
提示:如果使用InnoDB,主键不需要任何存储,因为表本身是作为主键索引存储的。如果需要按类别优化搜索,则应该首先定义规范化表的类别id,然后再定义产品id。
CREATE TABLE CategoryProduct (
categoryid INT,
productid INT,
PRIMARY KEY (categoryid, productid)
);
另请参阅我对以下问题的回答:在数据库列中存储分隔列表真的那么糟糕吗?了解使用逗号分隔列表的更多缺点。
我会考虑一个新表,比如Product_Category
(我知道这是缺乏想象力的),其中每一行都包含一列用于与Product.id
的外键(FK)关系,以及一列用于类别。
category
列可能是只需要1个字节存储的TINYINT
,而我猜FK列与Product.id列相同(可能是INT
-4个字节),然后可以对这两列进行索引,这样你就可以找出产品属于哪个类别,也可以找出哪个产品属于一个类别。此外,该表不需要有Primary Key
(即id
),可以额外节省4个字节。
(请参阅MySQL数据类型存储要求)
使用这个解决方案,这个新数据库中的每一行将占用大约5个字节。由于字符串中的每个字符占用1个字节(假设ASCII和latin1编码),通过删除Product.category
并将项目放入Product_Category
,每个产品的每个类别将增加3个字节(包括逗号),但这并不像复制整个产品行那样大。然而,更改代码是有代价的(除非你在joins
方面比我好得多)。
这有帮助吗?
我看到的一个解决方案是使用三个表:
- categories列出您的类别
- products列出您的产品,没有任何附加的类别信息
- category_map是一个特殊的表:每一行都将一个product_id链接到一个category_id
要按类别查找产品,可以将category_map中的行与产品中的行进行匹配。
这是一个不完美的例子,但它得到了要点:
SELECT * FROM
(
SELECT * FROM category_map
WHERE category_id=1
) AS map
INNER JOIN products
ON products.id = map.product_id;
表联接是一个非常强大的工具;如果你是新手,你可能想花一些时间来阅读它们。编码恐怖有一个视觉解释,跳过了细节。
最好设置外键约束,或者以其他方式确保category_map中的条目与产品和类别的现有条目相对应。