mysql数据压缩性能对比(二)

2011/03/18 Posted by strong

在上一篇文章中,我们用生产环境的真实数据与真实SQL测试了archive和myisampack两种方式下的性能对比情况。我们得到一个对这个测试case有效的结论,那就是在240万数据量的情况下,采用archive引擎将使得某些查询慢得无法忍受!

那么,原因是什么呢?

我们知道,mysql提供archive这种存储引擎是为了降低磁盘开销,但还有一个前提,那就是被归档的数据不需要或者很少被在线查询,偶尔的查询慢一些也是没关系的。鉴于上述原因,archive表是不允许建立自增列之外的索引的。

有了这个共识,我们拿一条测试SQL来分析一下不用索引前后的查询性能差别为什么这么大。在我们的测试SQL中有这么一条:

SELECT c1,c2,...,cn FROM  mysqlslap.rpt_topranks_v3
WHERE ... AND partition_by1 = '50008090'
ORDER BY added_quantity3 DESC
LIMIT 500

我们前边说过,测试的这个表在partition_by1这个字段上建立了索引,那么,我们初步判断在基准表和myisampack表上,这个查询应该用到了partition_by1的索引;EXPLAIN一下:

mysql> EXPLAIN
    -> SELECT ... FROM  mysqlslap.rpt_topranks_v3
    -> WHERE ... AND partition_by1 = '50008090'
    -> ORDER BY added_quantity3 DESC
    -> LIMIT 500\G
*************************** 1. ROW ***************************
           id: 1
  select_type: SIMPLE
        TABLE: rpt_topranks_v3
         TYPE: REF
possible_keys: idx_toprank_pid,idx_toprank_chg
          KEY: idx_toprank_pid
      key_len: 99
          REF: const
         ROWS: 2477
        Extra: USING WHERE; USING filesort
1 ROW IN SET (0.00 sec)

正如我们所料,这个查询用到了建立在partition_by1这个字段上的索引,匹配的目标行数为2477,然后还有一个在added_quantity3字段上的排序。由于added_quantity3没有索引,所以用到了filesort。

我们再看一下这条SQL在归档表上的EXPLAIN结果:

mysql> EXPLAIN
    -&gt; SELECT ... FROM  mysqlslap.rpt_topranks_v3_<strong>archive</strong>
    -&gt; WHERE ... AND partition_by1 = '50008090'
    -&gt; ORDER BY added_quantity3 DESC
    -&gt; LIMIT 500\G
*************************** 1. ROW ***************************
           id: 1
  select_type: SIMPLE
        TABLE: rpt_topranks_v3_archive
         TYPE: ALL
possible_keys: NULL
          KEY: NULL
      key_len: NULL
          REF: NULL
         ROWS: 2424753
        Extra: USING WHERE; USING filesort
1 ROW IN SET (0.00 sec)

EXPLAIN说:“我没有索引可用,所以只能全表扫描2424753行记录,然后再来个filesort。”你要追求性能,那显然是委屈MySQL了。

扩展阅读:

关于MYSQL的ORDER BY实现原理,请参考 http://isky000.com/database/mysql_order_by_implement

 

Leave a Reply

Your email address will not be published. Required fields are marked *