【mysql】像很多网站(比如电商)里的筛选功能一般是如何实现的？

Z时代
2024-01-10
分类：技术分享

我的思路如下:

数据表:
分类(编号,名称)
筛选项(编号,名称,分类编号,显示顺序) 如"品牌"就是一个筛选项.
筛选点(编号,名称,所属筛选项编号,显示顺序) 如"小米"就是一个筛选点.
商品表(sku编号,分类编号)
商品属性表(sku编号docid,筛选点编号集合content) 【全文检索表】

其中筛选点编号集合content字段存储内容如:
101 102 108 110 230

SQL逻辑:
根据"分类编号"查询"筛选项"表获得"筛选项编号".
根据"筛选项编号"查询"筛选点"表获得"筛选点编号".
上面两点就可以拿到上图筛选条件的界面数据了.

筛选链接的SQL逻辑:

1.筛选链接形如:

list.php?category=分类编号&property=筛选点编号集合list.php?category=1&property=2_4_5

2.全文检索逻辑:
为了避免MySQL和PG之争,这里以SQLite为例.

商品属性表sku_property(sku编号docid,属性点集合content)
交集:
SELECT docid FROM sku_property
WHERE content MATCH '2 4 5'
ORDER BY docid DESC LIMIT 10 OFFSET 20;
分页:
ORDER BY docid DESC LIMIT 10 OFFSET 20;
排序:
SELECT * FROM sku WHERE id IN ($docids)
ORDER BY 销量|价格|评论数|上架时间 DESC;
其中"销量|价格|评论数|上架时间"都是商品表sku里的字段.
本质上相当于对多个字段进行排序:
ORDER BY id DESC, 评论数 DESC
这就存在一个问题,第二页的商品的评论数可能比第一页的多,这是不符合要求的.
如果要符合要求,可以考虑使用表连接:
select * from sku
inner join sku_property on sku.id = sku_property.docid
where sku.category = 1
and sku_property.content MATCH '2 4 5'order by sku.评论数 DESC LIMIT 10 OFFSET 20;

3.$_GET['property']筛选点参数验证逻辑:
首先property的元素数量不能多于当前分类的所有筛选项的数量.
注意不是筛选点的数量,因为筛选项里只能单选筛选点.

//筛选点集合:$_GET['property']
$property = array(筛选点_2, 筛选点_4, 筛选点_5);
//防御式编程:如果$property跟"筛选项_1"中的array(筛选点_1, 筛选点_2)的交集大于1,则直接拒绝搜索.
$cat = array(
筛选项_1 => array(筛选点_1, 筛选点_2),
筛选项_2 => array(筛选点_3, 筛选点_4),
筛选项_3 => array(筛选点_5, 筛选点_6),);

可行性:

京东:
平板电视商品筛选共 4007个商品
笔记本商品筛选共 49431个商品 (单条件页面耗时:0.241秒,条件越多,页面耗时越少)
手机商品筛选共 6152个商品

全文检索中,条件越多(交集),应该是越慢吧?求解惑.
京东不知道是用什么实现的,条件越多,耗时反而越少?
京东是用solr的facet的思路吗?如果是,facet的原理是什么?

SQLite全文检索:
单个关键词,50万条记录,0.03秒(官方测试).
10个关键词,5万条记录,0.03秒(个人简单粗暴的估算,并不准确).
一个商品分类使用一个SQLite数据库来存储商品的属性(筛选点).
如果是用MySQL的话则可以一个分类对应一张全文检索的属性表.
我猜性能应该还是可以接受的.