mysql 表压缩性能下降_数据库表太大造成的查询效率低下、SQL优化压缩表提高查询效率...

最新推荐文章于 2024-08-01 17:48:45 发布

暂停一下下

最新推荐文章于 2024-08-01 17:48:45 发布

阅读量568

点赞数

文章标签： mysql 表压缩性能下降

本文链接：https://blog.csdn.net/weixin_32017023/article/details/114811259

版权

本文记录了一次SQL优化的过程，通过分析执行计划发现由于表`product_flag_m`过大导致查询效率低下。采取将12月数据抽取并压缩成新表的方法，但效果不明显。进一步分析发现`TRADE_LIST`表中的过滤条件可以提前应用，创建视图优化后，查询效率显著提升。在某些环境下，压缩表和视图优化可能比添加索引更为实用。

摘要由CSDN通过智能技术生成

今天收到一个同事的问题，有一段SQL跑了很久很久，根本没有结果，根据同事的反映，这个SQL一个月比一个月要慢。这是不被允许的事情，我们要做的就是对这个SQL进行一次优化。下面就是这次优化的记录。

首先说SQL：select t.month_id,

t1.area_id,

t1.local_id,

count(distinct case

when t.type_id = '02' and t.valid_flag = 1 and

t3.trade_id = '1008601' then

t.user_id

else

null

end),

count(distinct case

when t.type_id = '02' and t.valid_flag = 1 and

t3.trade_id = '1008602' then

t.user_id

else

null

end)

from product_flag_m t,

... --省略部分都是类似上面的运算，很多，为了节省篇幅都取消了

left join VW_CODE_LOCALNET t1

on t.local_id = t1.root_local_id

LEFT JOIN TRADE_LIST T3

ON T.id2 = T3.id2

AND T3.trade_id IN ('1008601', '1008602')

where t.month_id = '201212'

group by t.month_id, t1.area_id, t1.local_id;

这段代码隐藏了敏感信息，可能会有一些修改的时候错漏的问题。

接下来就是比较老的套路了，查看这段SQL的执行计划：

这个时候可以初步判断是因为product_flag_m表太大造成的查询效率低下。既然只需要12月的数据，那么我自然而然的想到了将12月的分区压缩一下，利用压缩表的特点进行查询效率的提高。但是这是张生产表，不能随便操作，于是我就将12月份的type_id='02'的数据单独抽取出来形成一张新的表，当然这张表是压缩过的，而且我抽取的时候只抽取自己需要的字段，这样做的好处是尽量减少数据量，减轻数据库的负担。

下面就是使用了压缩表之后的执行计划：

可以看到COST是有所降低，但是这个和没有降低没什么区别。还是面临执行不出来的问题。

这个时候我注意到了ID=2的这一部执行计划。在id=3的hash join right outer之后，不管是COST还是BYTES都是在一个比较正常的水平之内的，那么问题就应该出在TRADE_LIST这个表上。

这个表是一张编码表，本身并不大，但是注意这里：

上图所示应该就是罪魁了。于是我想到了，既然最后需要过滤一下trade_id，那么为什么不直接就用一张只有trade_id为1008601和1008602的表呢？

于是我鬼使神差的建立了一张视图，这个视图就是只取了上面说的那么多数据，然后替换掉原来的SQL中的TRADE_LIST，删除了其中的

AND T3.trade_id IN ('1008601', '1008602') 语句，再看执行计划：

这个效果就非常好了。

我本身很担心这个视图用了以后会影响查询结果集。于是我自己造了一张表做了一个小测试。test3中有object_id为2， 3， 4， 5， 6， 7的记录，编码表中只有id为2， 3, 4， 5， 6的编码记录，SQL如下：select t1.object_id, t2.id, t2.name

from test3 t1

left join test4 t2

on t1.object_id = t2.id

and t2.id in (2, 3);

这个结果有48行。制造一个视图：create view test5 as select * from test4 where id in (2, 3)

然后替换成视图：select t1.object_id, t2.id, t2.name

from test3 t1

left join test5 t2

on t1.object_id = t2.id;

结果还是48行。也就是说这个方法是可行的。

这样的话，如果在原来的SQL上加上并行提示，效果会更好。经过我的实际测试，3分钟以内就跑出了所有的结果。

或许会有人问我，为什么不加上索引？我并不是反对加索引，我不习惯使用索引的习惯是因为我们的现实环境所限，我们的磁盘空间基本上每隔一段时间就会满，所以我没办法随心所欲的添加会占用空间的索引，而是更倾向于使用压缩表，节省表空间。而且，id2字段进行关联的时候有一个隐式类型转换，这个字段起码没有办法加索引。至于其他字段，我没办法实验，如果有机会，可以做个实验试试。