woaidongmao

文章均收录自他人博客,但不喜标题前加-[转贴],因其丑陋,见谅!~
随笔 - 1469, 文章 - 0, 评论 - 661, 引用 - 0
数据加载中……

mysql5.1 数据库分区测试(range)

什么是数据库分区?
数据库分区是一种物理数据库设计技术,DBA和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果,但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。
分区主要有两种形式://这里一定要注意行和列的概念(row是行,column是列)
水平分区(Horizontal Partitioning) 这种形式分区是对表的行进行分区,通过这样的方式不同分组里面的物理列分割的数据集得以组合,从而进行个体分割(单分区)或集体分割(1个或多个分区)。所有在表中定义的列在每个数据集中都能找到,所以表的特性依然得以保持。
举个简单例子:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。(朋奕注:这里具体使用的分区方式我们后面再说,可以先说一点,一定要通过某个属性列来分割,譬如这里使用的列就是年份)
垂直分区(Vertical Partitioning) 这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度,使某些特定的列被划分到特定的分区,每个分区都包含了其中的列所对应的行。
举个简单例子:一个包含了大textBLOB列的表,这些textBLOB列又不经常被访问,这时候就要把这些不经常使用的textBLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。
在数据库供应商开始在他们的数据库引擎中建立分区(主要是水平分区)时,DBA和建模者必须设计好表的物理分区结构,不要保存冗余的数据(不同表中同时都包含父表中的数据)或相互联结成一个逻辑父对象(通常是视图)。这种做法会使水平分区的大部分功能失效,有时候也会对垂直分区产生影响。

分区详细地址:http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html
我们不再多说.

初次测试结论:

range
————————————-800
万条数据测试————————————

range类型按时间分区无主键数据.

  1. #查询一个数据量的集合比较
  2. mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
  3.  
  4. +----------+
  5. | count(*) |
  6. +----------+
  7. |   795181 |
  8. +----------+
  9. 1 row in set (4.11 sec)-未分区
  10.  
  11. mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
  12. +----------+
  13. | count(*) |
  14. +----------+
  15. |   795181 |
  16. +----------+
  17. 1 row in set (0.41 sec)-已分区
  18.  
  19. #查询一个值的集合
  20. mysql> select count(*) from no_part_tab where c3'1996-01-01';
  21. +----------+
  22. | count(*) |
  23. +----------+
  24. |     2186 |
  25. +----------+
  26. 1 row in set (2.94 sec)-未分区
  27.  
  28. mysql> select count(*) from part_tab where c3'1996-01-01';
  29. +----------+
  30. | count(*) |
  31. +----------+
  32. |     2186 |
  33. +----------+
  34. 1 row in set (0.28 sec)-已分区
  35.  
  36. #根据非关联列查询的集合,根据c1(id)查询
  37. mysql> select count(*) from no_part_tab where c1 > '5000000';
  38. +----------+
  39. | count(*) |
  40. +----------+
  41. 2999999 |
  42. +----------+
  43. 1 row in set (3.11 sec)-未分区
  44.  
  45. mysql> select count(*) from part_tab where c1'5000000';
  46. +----------+
  47. | count(*) |
  48. +----------+
  49. 2999999 |
  50. +----------+
  51. 1 row in set (3.07 sec)-已分区
  52.  
  53.  
  54. mysql> select count(*) from no_part_tab where c1 = '5000000';
  55. +----------+
  56. | count(*) |
  57. +----------+
  58. |        1 |
  59. +----------+
  60. 1 row in set (3.24 sec)-未分区
  61.  
  62. mysql> select count(*) from part_tab where c1'5000000';
  63. +----------+
  64. | count(*) |
  65. +----------+
  66. |        1 |
  67. +----------+
  68. 1 row in set (3.03 sec)-已分区

————————————-25万条数据测试————————————

range类型按user_id分区主键数据.

  1. #查询一个的数据量的集合比较
  2. mysql> select count(*) from user where  user_id > 100000 and  user_id < 150000;
  3. | count(*) |
  4. +----------+
  5. |    46515 |
  6. +----------+
  7. 1 row in set (0.03 sec)-未分区
  8.  
  9. mysql> select count(*) from `user_part` where  user_id > 100000 and  user_id < 150000;
  10. +----------+
  11. | count(*) |
  12. +----------+
  13. |    46515 |
  14. +----------+
  15. 1 row in set (0.05 sec)-已分区
  16.  
  17. #查询一个值的集合
  18.  
  19. mysql> select count(*) from user where  user_id=296401;
  20. +----------+
  21. | count(*) |
  22. +----------+
  23. |        1 |
  24. +----------+
  25. 1 row in set (0.00 sec)-未分区
  26.  
  27. mysql> select count(*) from `user_part` where  user_id=296401;
  28. +----------+
  29. | count(*) |
  30. +----------+
  31. |        1 |
  32. +----------+
  33. 1 row in set (0.00 sec)-已分区
  34.  
  35. #查询ID集合
  36. mysql> select count(*) from user where  user_id > 100000 and  user_id < 150000;
  37. | count(*) |
  38. +----------+
  39. |    46515 |
  40. +----------+
  41. 1 row in set (0.03 sec)-未分区
  42.  
  43. mysql> select count(*) from `user_part` where  user_id > 100000 and  user_id < 150000;
  44. +----------+
  45. | count(*) |
  46. +----------+
  47. |    46515 |
  48. +----------+
  49. 1 row in set (0.05 sec)-已分区
  50.  
  51. #根据非关联列查询的集合,根据时间查询
  52.  
  53. mysql> select count(*) from `user` where `user_regdate` > date '2006-01-01' and user_regdate < date '2006-12-31';
  54. select count(*) from `user_part` where `user_regdate` > date '2006-01-01' and user_regdate < date '2006-12-31';
  55. +----------+
  56. | count(*) |
  57. +----------+
  58. |   100946 |
  59. +----------+
  60. 1 row in set (0.75 sec)-未分区
  61.  
  62. mysql> select count(*) from `user_part` where `user_regdate` > date '2006-01-01' and user_regdate < date '2006-12-31';
  63. +----------+
  64. | count(*) |
  65. +----------+
  66. |   100946 |
  67. +----------+
  68. 1 row in set (0.67 sec)-已分区
  69.  
  70. #根据username查询
  71.  
  72. mysql> select count(*) from user where  user_name='l007@xok.cn';
  73. | count(*) |
  74. +----------+
  75. |        1 |
  76. +----------+
  77. 1 row in set (0.02 sec)-未分区
  78.  
  79. mysql> select count(*) from `user_part` where  user_name='l007@xok.cn';
  80. +----------+
  81. | count(*) |
  82. +----------+
  83. |        1 |
  84. +----------+
  85. 1 row in set (0.64 sec)-已分区
  86.  
  87. #模糊查询
  88.  
  89. mysql> select count(*) from user where user_name like '%qq%';
  90. | count(*) |
  91. +----------+
  92. |    37143 |
  93. +----------+
  94. 1 row in set (0.33 sec)-未分区
  95.  
  96. mysql> select count(*) from user_part where user_name like '%qq%';
  97. +----------+
  98. | count(*) |
  99. +----------+
  100. |    37143 |
  101. +----------+
  102. 1 row in set (0.72 sec)-已分区

我们假设PARTITION BY RANGE (user_id)user_id”PARTITION关联的列
当数量两很大时,根据PARTITION关联的列查询多条记录,分区后效率提速90%左右
但通过与 PARTITION关联的列 之外的列的查询 分区后效率低8%左右,模糊查询分区后效率低45%左右

数据越大,效果越明显,如果只有几百万的数据,分区和加了索引的效果差不多

 

posted on 2009-06-10 14:35 肥仔 阅读(409) 评论(0)  编辑 收藏 引用 所属分类: 数据库


只有注册用户登录后才能发表评论。
【推荐】超50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理