MySQL分区之RANGE分区

最新推荐文章于 2023-12-04 18:19:45 发布

原创

最新推荐文章于 2023-12-04 18:19:45 发布 · 2k 阅读

1 ·

CC 4.0 BY-SA版权

本文深入探讨了MySQL中的RANGE分区技术，介绍了如何根据数值范围对数据进行分区，以优化查询性能。内容涵盖分区的基本概念，RANGE分区的语法，以及在实际数据库设计中如何选择合适的分区策略。

环境：

mysql> select version()\G;
*************************** 1. row ***************************
version(): 5.5.28

㈠主要应用场景

RANGE分区主要用于日期列的分区
例如销售类的表，可以根据年份来分区存储销售记录
如下是对sales表进行分区

mysql> create table sales(money int unsigned not null,
    -> date datetime
    -> )engine=innodb
    -> partition by range (year(date)) (
    -> partition p2008 values less than (2009),
    -> partition p2009 values less than (2010),
    -> partition p2010 values less than (2011)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into sales SELECT 100,'2008-01-01';
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into sales SELECT 100,'2008-02-01';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into sales SELECT 200,'2008-01-02';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into sales SELECT 100,'2008-03-01';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into sales SELECT 100,'2009-03-01';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into sales SELECT 200,'2010-03-01';
Query OK, 1 row affected (0.00 sec)
Records: 1