神奇的MySQL分区(一)

mysql> select * from no_part_tab where c1 = '80000';
+-------+--------------------+------------+
| c1    | c2                 | c3         |
+-------+--------------------+------------+
| 80000 | testing partitions | 1995-05-25 |
+-------+--------------------+------------+
1 row in set (1.57 sec)


mysql> select * from part_tab where c1 = '80000';
+-------+--------------------+------------+
| c1    | c2                 | c3         |
+-------+--------------------+------------+
| 80000 | testing partitions | 1995-05-25 |
+-------+--------------------+------------+
1 row in set (0.02 sec)


mysql> update part_tab set c2='zhmsong' where c1 = '80000';
Query OK, 1 row affected (0.21 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> update no_part_tab set c2='zhmsong' where c1 = '80000';
Query OK, 1 row affected (15.55 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> delete from no_part_tab where c1 = '80000';
Query OK, 1 row affected (1.46 sec)


mysql> delete from part_tab where c1 = '80000';
Query OK, 1 row affected (0.02 sec)


mysql> select count(*) from no_part_tab;
+----------+
| count(*) |
+----------+
|  7999999 |
+----------+
1 row in set (0.00 sec)


mysql> select count(*) from part_tab;
+----------+
| count(*) |
+----------+
|  7999999 |
+----------+
1 row in set (0.01 sec)


mysql> select count(*) from part_tab where c1 >= '80000' and c1 <= '150000';
+----------+
| count(*) |
+----------+
|    70000 |
+----------+
1 row in set (0.05 sec)


mysql> select count(*) from no_part_tab where c1 >= '80000' and c1 <= '150000';
+----------+
| count(*) |
+----------+
|    70000 |
+----------+
1 row in set (1.89 sec)


mysql>





 select count(*) from no_part_tab where c2 like '%test%';
+----------+
| count(*) |
+----------+
|  7999999 |
+----------+
1 row in set (1.52 sec)


mysql> select count(*) from part_tab where c2 like '%test%';
+----------+
| count(*) |
+----------+
|  7999999 |
+----------+
1 row in set (1.59 sec)


mysql> select * from no_part_tab where c2 like '%zhmsong%' limit 10 offset 100;
+---------+---------+------------+
| c1      | c2      | c3         |
+---------+---------+------------+
| 1100100 | zhmsong | 2004-05-13 |
| 1100101 | zhmsong | 1999-05-21 |
| 1100102 | zhmsong | 2004-05-26 |
| 1100103 | zhmsong | 1999-06-03 |
| 1100104 | zhmsong | 2004-06-09 |
| 1100105 | zhmsong | 1999-06-16 |
| 1100106 | zhmsong | 2004-06-22 |
| 1100107 | zhmsong | 1999-06-30 |
| 1100108 | zhmsong | 2004-07-05 |
| 1100109 | zhmsong | 1999-07-13 |
+---------+---------+------------+
10 rows in set (0.22 sec)


mysql> select * from part_tab where c2 like '%zhmsong%' limit 10 offset 100;
+---------+---------+------------+
| c1      | c2      | c3         |
+---------+---------+------------+
| 1100100 | zhmsong | 2004-05-13 |
| 1100101 | zhmsong | 1999-05-21 |
| 1100102 | zhmsong | 2004-05-26 |
| 1100103 | zhmsong | 1999-06-03 |
| 1100104 | zhmsong | 2004-06-09 |
| 1100105 | zhmsong | 1999-06-16 |
| 1100106 | zhmsong | 2004-06-22 |
| 1100107 | zhmsong | 1999-06-30 |
| 1100108 | zhmsong | 2004-07-05 |
| 1100109 | zhmsong | 1999-07-13 |
+---------+---------+------------+
10 rows in set (0.22 sec)

＝＝ 实现＝＝

01
DROP TABLE IF EXISTS part_tab;
02
CREATE TABLE part_tab (
03
        c1 int default NULL,
04
        c2 varchar(30) default NULL,
05
        c3 date default NULL
06
) engine=myisam
07
        PARTITION BY RANGE (c1) (
08
        PARTITION p1 VALUES LESS THAN (100000),
09
    PARTITION p2 VALUES LESS THAN (200000),
10
        PARTITION p3 VALUES LESS THAN (300000),
11
    PARTITION p4 VALUES