MySQL5.5分区之高级分区实例及性能分析

上次我们介绍了：MySQL 5.5 分区之truncate分区详解，本文我们主要介绍一下MySQL 5.5 分区之高级分区实例及性能分析，接下来我们就开始一一介绍这部分内容。

创新互联-专业网站定制、快速模板网站建设、高性价比伊美网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式伊美网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖伊美地区。费用合理售后完善，十余年实体公司更值得信赖。

为了帮助你更好地掌握新功能，我们再来看一个更高级一点的例子，对于比较务实的读者更有意义，下面是表的定义脚本：

 
 
 
    
  
  
  
  1. CREATE TABLE employees (    
    2.   
  
  
  2.   emp_no int(11) NOT NULL,    
    3.   
  
  
  3.   birth_date date NOT NULL,    
    4.   
  
  
  4.   first_name varchar(14) NOT NULL,    
    5.   
  
  
  5.   last_name varchar(16) NOT NULL,    
    6.   
  
  
  6.   gender char(1) DEFAULT NULL,    
    7.   
  
  
  7.   hire_date date NOT NULL   
    8.   
  
  
  8. ) ENGINE=MyISAM    
    9.   
  
  
  9. PARTITION BY RANGE  COLUMNS(gender,hire_date)    
    10.   
  
  
  10. (PARTITION p01 VALUES LESS THAN ('F','1990-01-01') ,    
    11.   
  
  
  11. PARTITION p02 VALUES LESS THAN ('F','2000-01-01') ,    
    12.   
  
  
  12. PARTITION p03 VALUES LESS THAN ('F',MAXVALUE) ,    
    13.   
  
  
  13. PARTITION p04 VALUES LESS THAN ('M','1990-01-01') ,    
    14.   
  
  
  14. PARTITION p05 VALUES LESS THAN ('M','2000-01-01') ,    
    15.   
  
  
  15. PARTITION p06 VALUES LESS THAN ('M',MAXVALUE) ,    
    16.   
  
  
  16. PARTITION p07 VALUES LESS THAN (MAXVALUE,MAXVALUE)  
    17.

和上面的例子不同，这个例子更好理解，***个分区用来存储雇佣于1990年以前的女职员，第二个分区存储股用于1990-2000年之间的女职员，第三个分区存储所有剩下的女职员。对于分区p04到p06，我们策略是一样的，只不过存储的是男职员。***一个分区是控制情况。

看完后你可能要问，我怎么知道某一行存储在那个分区中的？有两个办法，***个办法是使用与分区定义相同的条件作为查询条件进行查询。

 
 
 
    
  
  
  
  1. SELECT   
    2.   
  
  
  2. CASE   
    3.   
  
  
  3.   WHEN gender = 'F' AND hire_date < '1990-01-01'   
    4.   
  
  
  4.   THEN 'p1'   
    5.   
  
  
  5.   WHEN gender = 'F' AND hire_date < '2000-01-01'   
    6.   
  
  
  6.   THEN 'p2'   
    7.   
  
  
  7.   WHEN gender = 'F' AND hire_date < '2999-01-01'   
    8.   
  
  
  8.   THEN 'p3'   
    9.   
  
  
  9.   WHEN gender = 'M' AND hire_date < '1990-01-01'   
    10.   
  
  
  10.   THEN 'p4'   
    11.   
  
  
  11.   WHEN gender = 'M' AND hire_date < '2000-01-01'   
    12.   
  
  
  12.   THEN 'p5'   
    13.   
  
  
  13.   WHEN gender = 'M' AND hire_date < '2999-01-01'   
    14.   
  
  
  14.   THEN 'p6'   
    15.   
  
  
  15. ELSE   
    16.   
  
  
  16.   'p7'   
    17.   
  
  
  17. END as p,    
    18.   
  
  
  18. COUNT(*) AS rows   
    19.   
  
  
  19. FROM employees    
    20.   
  
  
  20. GROUP BY p;    
    21.   
  
  
  21.  
    22.   
  
  
  22. +------+-------+    
    23.   
  
  
  23. | p    | rows  |    
    24.   
  
  
  24. +------+-------+    
    25.   
  
  
  25. | p1   | 66212 |    
    26.   
  
  
  26. | p2   | 53832 |    
    27.   
  
  
  27. | p3   |     7 |    
    28.   
  
  
  28. | p4   | 98585 |    
    29.   
  
  
  29. | p5   | 81382 |    
    30.   
  
  
  30. | p6   |     6 |    
    31.   
  
  
  31. +------+-------+  
    32.

如果表是MyISAM或ARCHIVE，你可以信任由INFORMATION_SCHEMA提供的统计信息。

 
 
 
    
  
  
  
  1. SELECT   
    2.   
  
  
  2.   partition_name part,    
    3.   
  
  
  3.   partition_expression expr,    
    4.   
  
  
  4.   partition_description descr,    
    5.   
  
  
  5.   table_rows    
    6.   
  
  
  6. FROM   
    7.   
  
  
  7.   INFORMATION_SCHEMA.partitions    
    8.   
  
  
  8. WHERE   
    9.   
  
  
  9.   TABLE_SCHEMA = schema()    
    10.   
  
  
  10.   AND TABLE_NAME='employees';    
    11.   
  
  
  11. +------+------------------+-------------------+------------+    
    12.   
  
  
  12. | part | expr             | descr             | table_rows |    
    13.   
  
  
  13. +------+------------------+-------------------+------------+    
    14.   
  
  
  14. | p01  | gender,hire_date | 'F','1990-01-01'  |      66212 |    
    15.   
  
  
  15. | p02  | gender,hire_date | 'F','2000-01-01'  |      53832 |    
    16.   
  
  
  16. | p03  | gender,hire_date | 'F',MAXVALUE      |          7 |    
    17.   
  
  
  17. | p04  | gender,hire_date | 'M','1990-01-01'  |      98585 |    
    18.   
  
  
  18. | p05  | gender,hire_date | 'M','2000-01-01'  |      81382 |    
    19.   
  
  
  19. | p06  | gender,hire_date | 'M',MAXVALUE      |          6 |    
    20.   
  
  
  20. | p07  | gender,hire_date | MAXVALUE,MAXVALUE |          0 |    
    21.   
  
  
  21. +------+------------------+-------------------+------------+  
    22.

如果存储引擎是InnoDB，上面的值就是一个近似值，如果你需要确切的值，那你就不能信任它们。

另一个问题是它的性能，这些增强触发了分区修整吗？答案毫不含糊，是的。与MySQL 5.1有所不同，在5.1中日期分区只能与两个函数工作，在MySQL 5.5中，任何使用了COLUMNS关键字定义的分区都可以使用分区修整，下面还是测试一下吧。

 
 
 
    
  
  
  
  1. select count(*) from employees where gender='F' and hire_date < '1990-01-01';    
    2.   
  
  
  2. +----------+    
    3.   
  
  
  3. | count(*) |    
    4.   
  
  
  4. +----------+    
    5.   
  
  
  5. |    66212 |    
    6.   
  
  
  6. +----------+    
    7.   
  
  
  7. 1 row in set (0.05 sec)    
    8.   
  
  
  8.  
    9.   
  
  
  9. explain partitions select count(*) from employees where gender='F' and hire_date < '1990-01-01'\G    
    10.   
  
  
  10. *************************** 1. row ***************************    
    11.   
  
  
  11.            id: 1    
    12.   
  
  
  12.   select_type: SIMPLE    
    13.   
  
  
  13.         table: employees    
    14.   
  
  
  14.    partitions: p01    
    15.   
  
  
  15.          type: ALL   
    16.   
  
  
  16. possible_keys: NULL   
    17.   
  
  
  17.           key: NULL   
    18.   
  
  
  18.       key_len: NULL   
    19.   
  
  
  19.           ref: NULL   
    20.   
  
  
  20.          rows: 300024    
    21.   
  
  
  21.         Extra: Using where  
    22.

使用定义***个分区的条件，我们获得了一个非常优化的查询，不仅如此，部分条件也将从分区修整中受益。

 
 
 
    
  
  
  
  1. select count(*) from employees where gender='F';    
    2.   
  
  
  2. +----------+    
    3.   
  
  
  3. | count(*) |    
    4.   
  
  
  4. +----------+    
    5.   
  
  
  5. |   120051 |    
    6.   
  
  
  6. +----------+    
    7.   
  
  
  7. 1 row in set (0.12 sec)    
    8.   
  
  
  8.  
    9.   
  
  
  9. explain partitions select count(*) from employees where gender='F'\G    
    10.   
  
  
  10. *************************** 1. row ***************************    
    11.   
  
  
  11.            id: 1    
    12.   
  
  
  12.   select_type: SIMPLE    
    13.   
  
  
  13.         table: employees    
    14.   
  
  
  14.    partitions: p01,p02,p03,p04    
    15.   
  
  
  15.          type: ALL   
    16.   
  
  
  16. possible_keys: NULL   
    17.   
  
  
  17.           key: NULL   
    18.   
  
  
  18.       key_len: NULL   
    19.   
  
  
  19.           ref: NULL   
    20.   
  
  
  20.          rows: 300024    
    21.   
  
  
  21.         Extra: Using where 
    22.

它和复合索引的算法一样，如果你的条件指的是索引最左边的部分，MySQL将会使用它。与此类似，如果你的条件指的是分区定义最左边的部分，MySQL将会尽可能修整。它和复合索引一起出现，如果你只使用最右边的条件，分区修整不会工作。

 
 
 
    
  
  
  
  1. select count(*) from employees where hire_date < '1990-01-01';    
    2.   
  
  
  2. +----------+    
    3.   
  
  
  3. | count(*) |    
    4.   
  
  
  4. +----------+    
    5.   
  
  
  5. |   164797 |    
    6.   
  
  
  6. +----------+    
    7.   
  
  
  7. 1 row in set (0.18 sec)    
    8.   
  
  
  8.  
    9.   
  
  
  9. explain partitions select count(*) from employees where hire_date < '1990-01-01'\G    
    10.   
  
  
  10. *************************** 1. row ***************************    
    11.   
  
  
  11.            id: 1    
    12.   
  
  
  12.   select_type: SIMPLE    
    13.   
  
  
  13.         table: employees    
    14.   
  
  
  14.    partitions: p01,p02,p03,p04,p05,p06,p07    
    15.   
  
  
  15.          type: ALL   
    16.   
  
  
  16. possible_keys: NULL   
    17.   
  
  
  17.           key: NULL   
    18.   
  
  
  18.       key_len: NULL   
    19.   
  
  
  19.           ref: NULL   
    20.   
  
  
  20.          rows: 300024    
    21.   
  
  
  21.         Extra: Using where 
    22.

如果不用分区定义的***部分，使用分区定义的第二部分，那么将会发生全表扫描，在设计分区和编写查询时要紧记这一条。

关于MySQL 5.5 分区之高级分区实例及性能分析的知识就介绍到这里了，希望本次的介绍能够对您有所收获！

原文出处：http://lujia35.iteye.com/blog/718899。

当前文章：MySQL5.5分区之高级分区实例及性能分析
转载来于：http://www.shufengxianlan.com/qtweb/news46/141146.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

网站维护知识

同城分类信息

边坡防护网厂家　　 　木包装箱　　 　重庆APP开发　　 　成都网站维护　　 　高端网站策划　　 　达州服务器托管　　 　德阳定制网站建设　　 　温江做网站　　 　乐山托管服务器　　 　成都做手机网站　　 　眉山电信机房　　 　成都全网营销　　 　成都App定制　　 　达州主机托管　　 　成都网站设计　　 　成都网站建设　　 　钢筋机械设备　　 　LOGO设计　　 　成都主机租用　　 　营销网站建设