mysql分区技术试验测试总结(效率杠杠的)

作者:吴泽鑫 分类: Mysql 发布于:2016-4-9 22:54 ė4320次浏览 60条评论

大家都知道,mysql分区技术主要是为了提高查询效率,今天为了验证一下这个结论,我特意做了下试验,果然速度惊人,试验效果如下。

1、先登陆到mysql,创建一张无分区的表(no_part_tab)和一张有分区的表(part_tab)

mysql> create table part_tab(c1 int default null,c2 varchar(30) default null,c3 date default null) engine=myisam
    -> partition by range(year(c3))(
    -> partition p0 values less than (1995),
    -> partition p1 values less than (1996),
    -> partition p2 values less than (1997), 
    -> partition p3 values less than (1998), 
    -> partition p4 values less than (1999), 
    -> partition p5 values less than (2000), 
    -> partition p6 values less than (2001), 
    -> partition p7 values less than (2002), 
    -> partition p8 values less than (2003), 
    -> partition p9 values less than (2004), 
    -> partition p10 values less than (2010),
    -> partition p11 values less than maxvalue);
Query OK, 0 rows affected (0.04 sec)

mysql> create table no_part_tab(c1 int(11) default null,c2 varchar(30) default null,c3 date default null) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

2、表创建好了之后再往表里面插入内容800万行数据。

#先建立一个存储过程,来完成我们数据写入操作
mysql> \d //  #切换边界符
mysql> create procedure load_part_tab()
    -> begin
    -> declare v int default 0;
    -> while v < 8000000
    -> do
    -> insert into part_tab
    -> values(v,'testing partitions',adddate("1995-01-01",(rand(v)*36520)mod 3652));
    -> set v= v+1;
    -> end while;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> \d ;#切换边界符
mysql> show procedure status;
+------+---------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name          | Type      | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+---------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | load_part_tab | PROCEDURE | root@%  | 2016-04-09 23:03:16 | 2016-04-09 23:03:16 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+------+---------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)
#执行
call load_part_tab;

可以到mysql/data/test/数据库下查看文件,用 watch -n1 ls -lh 命令,每一秒查看内存的变化

blob.png

mysql> call load_part_tab;
Query OK, 1 row affected (5 min 2.45 sec)

mysql> select count(*) from part_tab;
+----------+
| count(*) |
+----------+
|  8000000 |
+----------+
1 row in set (0.01 sec)
#数据插入完毕,再把数据复制到no_part_tab表

mysql> insert into no_part_tab select * from part_tab;
Query OK, 8000000 rows affected (8.11 sec)
Records: 8000000  Duplicates: 0  Warnings: 0

3、数据插入好了之后,我们来测试下sql语句的速度

mysql> select count(*) from part_tab where c3 > date'1995-01-01' and  c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (0.39 sec)
#分区表用了0.39秒
mysql> select count(*) from no_part_tab where c3 > date'1995-01-01' and  c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (2.98 sec)
#未分区表,用了2.98秒
#来分析下sql语句
mysql> desc select count(*) from part_tab where c3 > date'1995-01-01' and  c3 < date '1995-12-31' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part_tab
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 798458 #分区表的受影响行数是798458
        Extra: Using where
1 row in set (0.03 sec)
mysql> desc select count(*) from no_part_tab where c3 > date'1995-01-01' and  c3 < date '1995-12-31' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: no_part_tab
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8000000 #分区表的受影响行数是8000000
        Extra: Using where
1 row in set (0.00 sec)
#因为对分区表(part_tab)来说1995年的分区数据在p0分区中,所以查询只要去P0分区中查找就好,而未分区表的数据在整个表里搜索,所以速度很慢
#接下来都对2个表创建个索引,来看看查找速度如何

mysql> create index idx_of_c3 on part_tab(c3);
Query OK, 8000000 rows affected (23.20 sec)
Records: 8000000  Duplicates: 0  Warnings: 0

mysql> create index idx_of_c3 on no_part_tab(c3);
Query OK, 8000000 rows affected (25.22 sec)
Records: 8000000  Duplicates: 0  Warnings: 0
#再来查一下刚才的sql语句
mysql> select count(*) from part_tab where c3 > date'1995-01-01' and  c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (0.43 sec)

mysql> select count(*) from no_part_tab where c3 > date'1995-01-01' and  c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (0.42 sec)
#可以看出,创建索引之后,两个sql的查询速度都差不多,虽然速度差不多,但是相对对服务器的影响程度,肯定是分区表影响比较小,毕竟文件都拆分成多个了。
#如果where条件出现没有索引的情况,速度会如何呢?下面来测试下
mysql> select count(*) from part_tab where c3 > date'1995-01-01' and  c3 < date '1995-12-31' and c2='jensen';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.26 sec)

mysql> select count(*) from no_part_tab where c3 > date'1995-01-01' and  c3 < date '1995-12-31' and c2='jensen';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)
#可见,在没有索引的情况下,分区表之用了0.26秒,而没分区表用了2.00秒

4、总结:在数据量同等的情况下,用mysql分区技术搜索的速度远远比没分区的表搜索速度要块,大约快7倍左右。当有分区的表和没分区的表的条件都带有索引的情况下,两者的速度都差不多,速度也很快。但是一旦where条件出现没有索引的情况,没分区的表速度又变慢,速度相差大约也在7倍左右。


本文出自 Jensen-吴泽鑫的博客,转载时请注明出处及相应链接:http://www.wuzexin.cn/post-62.html

发表评论

电子邮件地址不会被公开。必填项已用*标注


Ɣ回顶部