
本文共 3386 字,大约阅读时间需要 11 分钟。
在进入主题之前,首先来了解一个概念“过滤因子”。
过滤因子: 影响 SQL 查询的除了查询本身还与数据库表中的数据特征有关。一个 SQL 查询扫描的索引片大小其实是由过滤因子决定的,也就是满足查询条件的记录行数所占的比例。 users 表,有主键(id)、姓名(name)、性别(sex)、年龄(age)字段。


以上内容来自http://blog.jobbole.com/112487/
在日常工作中,我们需要创建索引,来提升查询效率。有些索引,在平均情况下表现良好,在极端的输入下可能就完全无法正常工作。
场景一:
表结构如下
CREATE TABLE `job_queue` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `status` varchar(1) NOT NULL DEFAULT '0' COMMENT '状态,0:未处理,1:成功,2:失败', PRIMARY KEY (`id`), KEY `idx_jq_status` (`status`)) ;
生产者往job_queue表插入status=0记录。
消费者执行SQL:select * from job_queue where status=0 order by id asc limit 500;
扫描status=0的记录,处理完之后更新记录status=1。正常情况下, status=0的记录总数在1000以内,job_queue表数量级在100W以上,status=0过滤性很强,可以过滤掉99.9%的记录。 原则上对于值比较单一的列(如status的值只有0,1,2三个)是不允许创建索引的。 但针对这种场景是有必要创建索引的。索引创建之后效率确实提升不少,一切相安无事。 在一次版本发布后,因为疏忽了某些场景,status没有由0改成1。随着时间的推移,status=0记录增长到10W以上,status=0过滤性下降。一切就变得不那么美好,程序修复之后,焦急地等待status=0数量赶紧下降,尽快恢复到之前美好的时光。 思考: 将job_queue表拆分成两个表job_queue_to_do(待处理表)和job_queue_result(结果表)。job_queue_to_do表只记录status=0的数据,处理完之后数据迁移到job_queue_result表。规避掉status=0数量不可控,导致出现慢查询的风险。 场景二:
索引列绝大部分(99%以上)值分布均匀,且记录不多,索引过滤效果明显;在某些值上聚集了大量数据,查询这些值对应的数据时,出现了慢查询,这些聚集了大量数据的值很容易被忽视。例如:
CREATE TABLE `retail_price` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `store_id` varchar(50) NOT NULL DEFAULT '' COMMENT '商品店铺ID', `product_id` varchar(50) NOT NULL DEFAULT '' COMMENT '商品ID', `retail_price` decimal(12,2) NOT NULL DEFAULT '-100000000.00' COMMENT '零售价', PRIMARY KEY (`id`), KEY `idx_rp_store_id` (`store_id`)) ;
retail_price表记录零售价,store_id为空字符串表示商品默认价格,store_id不为空字符串表示店铺价格。每个商品都有默认价格和店铺价格。店铺价格大致分布如下,每个店铺的价格记录数都不多,分布很均匀,store_id不为空字符串时过滤性很好。如果需要支持按店铺ID查询价格,对store_id列创建了索引,查询效率也很快。

select * from retail_price where
store_id=''
,此时sql就会出现慢查询。 针对这种情况,我们也可以像场景一 一样把retail_price拆分成default_retail_price(默认价格)和store_retail_price(店铺价格)两个表。 以为这样做就万事大吉了吗? 过了一段时间,有些店铺悄悄上架了几万个商品,每个商品又改过好几次价,最终这些店铺下的价格记录数达到十几万,甚至百万。这时候慢查询又来了,继续拆分表肯定不现实。该怎么办? 假设: 每个店铺下的价格主键ID是连续的,我们可以先获取该店铺价格maxId和minId。select max(id),min(id) from store_retail_price where store_id='ST00001'
。然后按ID范围条件,从minId开始按固定步长,循环从表里查找数据,直到id范围大于maxId。 long startId = 0;long endId = minId;int size=1000;do{ startId = endId; endId+=size; `select * from store_retail_price where store_id='ST00001' and id>=startId and id<=maxId)
这个假设是一种比较理想的情况,大多数场景下主键id是不连续的。这时候需要降低粒度,从store_id级别细分到store_id+product_id级别,创建组合索引(store_id,product_id)。当然如果你有store_id信息,如何将store_id级别细分到store_id+product_id级别也是一个难题。有一个办法是统计store_id下去重的product_id,select distinct product_id from store_retail_price where store_id='ST00001'
,这个sql语句可能也是一个慢SQL。感觉又进入了死角,恳请大神指点迷津。
思考:
创建表的时候,应该尽量一个表对应一种业务,避免多种业务数据糅合到一个表(虽然可以减少表的数量,提高开发效率,但是一旦某种业务的数据量超出预期的时候,这个表上的所有业务都会受影响)。写代码的时候应当考虑某个维度下数据量暴涨的情况,尽量将业务处理逻辑降到最细粒度,不仅可以避免因数据量过大导致程序OOM,也可以提高数据库查询性能。by:Dani.he
转载地址:https://blog.csdn.net/vipshop_fin_dev/article/details/83963989 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
关于作者
