在上一篇文章中,我们用生产环境的真实数据与真实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 -> SELECT ... FROM mysqlslap.rpt_topranks_v3_<strong>archive</strong> -> WHERE ... AND partition_by1 = '50008090' -> ORDER BY added_quantity3 DESC -> 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