第37期：适当的使用 MySQL 原生表分区

localhost:ytt> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `r1` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


localhost:ytt> show create table p1\G
*************************** 1. row ***************************
       Table: p1
Create Table: CREATE TABLE `p1` (
  `id` int NOT NULL,
  `r1` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p0 VALUES LESS THAN (1000000) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (3000000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (4000000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (5000000) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (6000000) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (7000000) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (8000000) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (9000000) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

localhost:ytt> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.94 sec)

localhost:ytt> select count(*) from p1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.92 sec)

localhost:ytt> explain format=tree select count(*) from t1 where id < 1000000\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
    -> Filter: (t1.id < 1000000)  (cost=407495.19 rows=2030006)
        -> Index range scan on t1 using PRIMARY  (cost=407495.19 rows=2030006)

1 row in set (0.00 sec)

localhost:ytt> explain format=tree select count(*) from p1 where id < 1000000\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
    -> Filter: (p1.id < 1000000)  (cost=99980.09 rows=499369)
        -> Index range scan on p1 using PRIMARY  (cost=99980.09 rows=499369)

1 row in set (0.00 sec)

localhost:ytt> explain format=tree select count(*) from t1 where id != 2000001\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
    -> Filter: (t1.id <> 2000001)  (cost=1829866.58 rows=9117649)
        -> Index range scan on t1 using PRIMARY  (cost=1829866.58 rows=9117649)

1 row in set (0.00 sec)

localhost:ytt> explain format=tree select count(*) from p1 where id != 2000001\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
    -> Filter: (p1.id <> 2000001)  (cost=1002750.23 rows=4993691)
        -> Index range scan on p1 using PRIMARY  (cost=1002750.23 rows=4993691)

1 row in set (0.00 sec)

localhost:ytt> explain update t1 set r1 = date_sub(current_date,interval ceil(rand()*5000) day) where id between 1000001 and 2990000\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: t1
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 3938068
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

localhost:ytt> explain update p1 set r1 = date_sub(current_date,interval ceil(rand()*5000) day) where id between 1000001 and 2990000\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: p1
   partitions: p1,p2
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 998738
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

localhost:ytt> create table t_p1 like t1;
Query OK, 0 rows affected (0.06 sec)

localhost:ytt> alter table p1 exchange partition p1 with table t_p1;
Query OK, 0 rows affected (0.07 sec)

localhost:ytt> select count(*) from p1;
+----------+
| count(*) |
+----------+
|  9000000 |
+----------+
1 row in set (0.79 sec)

localhost:ytt> select count(*) from t_p1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.13 sec)

localhost:ytt> alter table p1 exchange partition p1 with table t_p1;
Query OK, 0 rows affected (0.77 sec)

localhost:ytt> select count(*) from p1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.91 sec)

localhost:ytt> select count(*) from t_p1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

localhost:ytt>reset master;

Query OK, 0 rows affected (0.02 sec)

localhost:ytt>alter table p1 exchange partition p2 with table t_p1;
Query OK, 0 rows affected (2.42 sec)

localhost:ytt>alter table p1 exchange partition p2 with table t_p1;
Query OK, 0 rows affected (0.45 sec)

localhost:ytt>show master status;
...
 ytt1.000001 ：47d6eda0-6468-11ea-a026-9cb6d0e27d15:1-2

localhost:ytt>flush logs;
Query OK, 0 rows affected (0.01 sec)


localhost:ytt>insert into t_p1 select * from p1 partition (p2) ;
Query OK, 934473 rows affected (5.25 sec)
Records: 934473  Duplicates: 0  Warnings: 0


localhost:ytt>show master status;
...
 ytt1.000002 ：47d6eda0-6468-11ea-a026-9cb6d0e27d15:1-3

root@ytt-pc:/var/lib/mysql/3306# ls -sihl ytt1.00000*
2109882 4.0K -rw-r----- 1 mysql mysql  588 7月  23 11:13 ytt1.000001
2109868 7.2M -rw-r----- 1 mysql mysql 7.2M 7月  23 11:14 ytt1.000002

localhost:ytt> alter table p1 truncate partition p0;
Query OK, 0 rows affected (0.07 sec)

localhost:ytt> select count(*) from p1;
+----------+
| count(*) |
+----------+
|  9000001 |
+----------+
1 row in set (0.92 sec)

localhost:ytt> delete from t1 where id < 1000000;
Query OK, 999999 rows affected (26.80 sec)