日常工作中数据特征引发的慢查询

我就是我 2022-04-17 02:13 145阅读 0赞

在进入主题之前，首先来了解一个概念“过滤因子”。  
**过滤因子：**  
影响 SQL 查询的除了查询本身还与数据库表中的数据特征有关。一个 SQL 查询扫描的索引片大小其实是由过滤因子决定的，也就是满足查询条件的记录行数所占的比例。  
users 表，有主键(id)、姓名(name)、性别(sex)、年龄(age)字段。  
![在这里插入图片描述][20181113095547770.png]  
对于 users 表来说，sex=”male” 就不是一个好的过滤因子，它会选择整张表中一半的数据，所以在一般情况下我们最好不要使用 sex 列作为整个索引的第一列；而 name=”draven” 的使用就可以得到一个比较好的过滤因子了，它的使用能过滤整个数据表中 99.9% 的数据。  
当然我们也可以将这三个过滤进行组合，创建一个新的索引 (name, age, sex) 并同时使用这三列作为过滤条件：  
![在这里插入图片描述][20181113095641215.png]  
组合条件的过滤因子就可以达到十万分之 6 了，如果整张表中有 10w 行数据，也只需要在扫描薄索引片后进行 6 次随机读取，这种直接使用乘积来计算组合条件的过滤因子其实有一个比较重要的问题：列与列之间不应该有太强的相关性，如果不同的列之间有相关性，那么得到的结果就会比直接乘积得出的结果大一些，比如：所在的城市和邮政编码就有非常强的相关性，两者的过滤因子直接相乘其实与实际的过滤因子会有很大的偏差，不过这在多数情况下都不是太大的问题。  
对于一张表中的同一个列，不同的值也会有不同的过滤因子，这也就造成了同一列的不同值最终的查询性能也会有很大差别：  
![在这里插入图片描述][20181113095854659.png]  
当我们评估一个索引是否合适时，需要考虑极端情况下查询语句的性能，比如 0% 或者 50% 等；最差的输入往往意味着最差的性能，在平均情况下表现良好的 SQL 语句在极端的输入下可能就完全无法正常工作，这也是在设计索引时需要注意的问题。

*以上内容来自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列创建了索引，查询效率也很快。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70]  
但是如果需要获取默认价格数据，`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<endId;
    ｝while(endId<=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

[20181113095547770.png]: /images/20220417/6ad286e3cb814b719c4b97311c6be639.png
[20181113095641215.png]: /images/20220417/db0b4ebf041744c9b1ee3e19eb0725e6.png
[20181113095854659.png]: /images/20220417/71da558041754529a85b6ee4fb67ec7e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpcHNob3BfZmluX2Rldg_size_16_color_FFFFFF_t_70]: /images/20220417/f38905173e8f4c008dcb8a568731275f.png