改變一個表的分區方案只需使用alter table 加 partition_options 子句就可以了。和創建分區表時的create table語句很像。
創建表
|
1
2
3
4
5
6
7
|
CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)PARTITION BY RANGE( YEAR(purchased) ) (PARTITION p0 VALUES LESS THAN (1990),PARTITION p1 VALUES LESS THAN (1995),PARTITION p2 VALUES LESS THAN (2000),PARTITION p3 VALUES LESS THAN (2005)); |
創建插入數據存儲過程
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
|
delimiter $$drop procedure if exists pr_trb3$$create procedure pr_trb3(in begindate date,in enddate date,in tabname varchar(40))beginwhile begindate<enddate 1="" begindate="date_add(begindate,interval" delimiter="" do="" drop="" end="" execute="" from="" insert="" pre="" prepare="" s="concat_ws('" set="" stmt=""><p>調用存儲過程插入數據</p><pre class="brush:sql;">call pr_trb3('1985-01-01','2004-12-31','trb3');</pre><p>查看數據分布</p><pre class="brush:sql;">select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema = schema() and table_name='trb3';+------+------------------+-------+------------+| part | expr | descr | table_rows |+------+------------------+-------+------------+| p0 | YEAR(purchased) | 1990 | 1826 || p1 | YEAR(purchased) | 1995 | 1826 || p2 | YEAR(purchased) | 2000 | 1826 || p3 | YEAR(purchased) | 2005 | 1826 |+------+------------------+-------+------------+4 rows in set (0.00 sec)</pre><p>改變分區方案</p><pre class="brush:sql;">mysql> ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 4;Query OK, 7304 rows affected (0.07 sec)Records: 7304 Duplicates: 0 Warnings: 0</pre><p>查看數據</p><pre class="brush:sql;">select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema = schema() and table_name='trb3';+------+------+-------+------------+| part | expr | descr | table_rows |+------+------+-------+------------+| p0 | `id` | NULL | 7472 || p1 | `id` | NULL | 0 || p2 | `id` | NULL | 0 || p3 | `id` | NULL | 0 |+------+------+-------+------------+4 rows in set (0.00 sec)mysql> select 1826*4;+--------+| 1826*4 |+--------+| 7304 |+--------+1 row in set (0.00 sec)</pre><p>count(*)行數一致，說明數據沒出問題，但是information_schema.partitions查出來的不對<del>，這就不知道為什么了</del></p><blockquote><p>For partitioned InnoDB tables, the row count given in the TABLE_ROWS column of the INFORMATION_SCHEMA.PARTITIONS table is only an estimated value used in SQL optimization, and is not always exact.</p></blockquote><pre class="brush:sql;">mysql> select count(*) from trb3;+----------+| count(*) |+----------+| 7304 |+----------+但是count(*)還是7304，什么鬼</pre><p>再次改變分區方案</p><pre class="brush:sql;">ALTER TABLE trb3 PARTITION BY RANGE( YEAR(purchased) ) (PARTITION p0 VALUES LESS THAN (1990),PARTITION p1 VALUES LESS THAN (1995),PARTITION p2 VALUES LESS THAN (2000),PARTITION p3 VALUES LESS THAN (2005));mysql> select -> partition_name part, -> partition_expression expr, -> partition_description descr, -> table_rows -> from information_schema.partitions where -> table_schema = schema() -> and table_name='trb3';+------+------------------+-------+------------+| part | expr | descr | table_rows |+------+------------------+-------+------------+| p0 | YEAR(purchased) | 1990 | 1826 || p1 | YEAR(purchased) | 1995 | 1826 || p2 | YEAR(purchased) | 2000 | 0 || p3 | YEAR(purchased) | 2005 | 0 |+------+------------------+-------+------------+4 rows in set (0.00 sec)</pre><p><del>丟數據了。。</del><br>更正，實際沒丟，這個information_shcema.partitions表有延遲，過一會再查就好了</p><pre class="brush:sql;">mysql> select -> partition_name part, -> partition_expression expr, -> partition_description descr, -> table_rows -> from information_schema.partitions where -> table_schema = schema() -> and table_name='trb3';+------+------------------+-------+------------+| part | expr | descr | table_rows |+------+------------------+-------+------------+| p0 | YEAR(purchased) | 1990 | 1826 || p1 | YEAR(purchased) | 1995 | 1826 || p2 | YEAR(purchased) | 2000 | 1826 || p3 | YEAR(purchased) | 2005 | 1826 |+------+------------------+-------+------------+4 rows in set (0.00 sec)</pre><p>官方文檔說：<br>This has the same effect on the structure of the table as dropping the table and re-creating it using CREATE TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;<br>就是說ALTER TABLE trb3 PARTITION BY與 drop table然后重新create table trb3 partition by key(id) partitions 2一樣呢。</p><h3 id="改存儲引擎和普通表沒啥區別">改存儲引擎，和普通表沒啥區別</h3><pre class="brush:sql;">mysql> drop table trb3;Query OK, 0 rows affected (0.01 sec)mysql> CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)-> PARTITION BY RANGE( YEAR(purchased) ) (-> PARTITION p0 VALUES LESS THAN (1990),-> PARTITION p1 VALUES LESS THAN (1995),-> PARTITION p2 VALUES LESS THAN (2000),-> PARTITION p3 VALUES LESS THAN (2005)-> );Query OK, 0 rows affected (0.03 sec)mysql> call pr_trb3('1985-01-01','2004-12-31','trb3');Query OK, 0 rows affected (1.69 sec)mysql> select -> partition_name part, -> partition_expression expr, -> partition_description descr, -> table_rows -> from information_schema.partitions where -> table_schema = schema() -> and table_name='trb3';+------+------------------+-------+------------+| part | expr | descr | table_rows |+------+------------------+-------+------------+| p0 | YEAR(purchased) | 1990 | 1826 || p1 | YEAR(purchased) | 1995 | 1826 || p2 | YEAR(purchased) | 2000 | 1826 || p3 | YEAR(purchased) | 2005 | 1826 |+------+------------------+-------+------------+4 rows in set (0.01 sec)mysql> alter table trb3 engine=myisam;Query OK, 7304 rows affected (0.02 sec)Records: 7304 Duplicates: 0 Warnings: 0mysql> select -> partition_name part, -> partition_expression expr, -> partition_description descr, -> table_rows -> from information_schema.partitions where -> table_schema = schema() -> and table_name='trb3';+------+------------------+-------+------------+| part | expr | descr | table_rows |+------+------------------+-------+------------+| p0 | YEAR(purchased) | 1990 | 1826 || p1 | YEAR(purchased) | 1995 | 1826 || p2 | YEAR(purchased) | 2000 | 1826 || p3 | YEAR(purchased) | 2005 | 1826 |+------+------------------+-------+------------+4 rows in set (0.01 sec)mysql> show create table trb3\G*************************** 1. row ***************************Table: trb3Create Table: CREATE TABLE `trb3` (`id` int(11) DEFAULT NULL,`name` varchar(50) DEFAULT NULL,`purchased` date DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE ( YEAR(purchased))(PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM,PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM) */1 row in set (0.00 sec)</pre><h3 id="將表由分區表改為非分區表">將表由分區表改為非分區表</h3><pre class="brush:sql;">mysql> alter table trb3 remove partitioning;Query OK, 7304 rows affected (0.01 sec)Records: 7304 Duplicates: 0 Warnings: 0mysql> select -> partition_name part, -> partition_expression expr, -> partition_description descr, -> table_rows -> from information_schema.partitions where -> table_schema = schema() -> and table_name='trb3';+------+------+-------+------------+| part | expr | descr | table_rows |+------+------+-------+------------+| NULL | NULL | NULL | 7304 |+------+------+-------+------------+1 row in set (0.00 sec)mysql> show create table trb3\G*************************** 1. row ***************************Table: trb3Create Table: CREATE TABLE `trb3` (`id` int(11) DEFAULT NULL,`name` varchar(50) DEFAULT NULL,`purchased` date DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec)</pre><h3 id="range-list分區管理">Range List分區管理</h3><pre class="brush:sql;">mysql> drop table trb3;Query OK, 0 rows affected (0.01 sec)mysql> CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)-> PARTITION BY RANGE( YEAR(purchased) ) (-> PARTITION p0 VALUES LESS THAN (1990),-> PARTITION p1 VALUES LESS THAN (1995),-> PARTITION p2 VALUES LESS THAN (2000),-> PARTITION p3 VALUES LESS THAN (2005)-> );Query OK, 0 rows affected (0.03 sec)mysql> call pr_trb3('1985-01-01','2004-12-31','trb3');Query OK, 0 rows affected (1.75 sec)mysql> select -> partition_name part, -> partition_expression expr, -> partition_description descr, -> table_rows -> from information_schema.partitions where -> table_schema = schema() -> and table_name='trb3';+------+------------------+-------+------------+| part | expr | descr | table_rows |+------+------------------+-------+------------+| p0 | YEAR(purchased) | 1990 | 1826 || p1 | YEAR(purchased) | 1995 | 1826 || p2 | YEAR(purchased) | 2000 | 1826 || p3 | YEAR(purchased) | 2005 | 1826 |+------+------------------+-------+------------+4 rows in set (0.00 sec)</pre><h4 id="增加分區">增加分區</h4><pre class="brush:sql;">mysql> alter table trb3 add partition (partition p5 values less than(2010));Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0</pre><h4 id="合并分區">合并分區</h4><pre class="brush:sql;">mysql> alter table trb3 reorganize partition p3,p5 into(partition p5 values less than(2010));Query OK, 1826 rows affected (0.03 sec)Records: 1826 Duplicates: 0 Warnings: 0mysql> select -> partition_name part, -> partition_expression expr, -> partition_description descr, -> table_rows -> from information_schema.partitions where -> table_schema = schema() -> and table_name='trb3';+------+------------------+-------+------------+| part | expr | descr | table_rows |+------+------------------+-------+------------+| p0 | YEAR(purchased) | 1990 | 1826 || p1 | YEAR(purchased) | 1995 | 1826 || p2 | YEAR(purchased) | 2000 | 1826 || p5 | YEAR(purchased) | 2010 | 1826 |+------+------------------+-------+------------+4 rows in set (0.00 sec)</pre><h4 id="分裂分區">分裂分區</h4><pre class="brush:sql;">mysql> ALTER TABLE trb3 REORGANIZE PARTITION p5 INTO (-> PARTITION p3 VALUES LESS THAN (2005),-> PARTITION p4 VALUES LESS THAN (2010)-> );Query OK, 1826 rows affected (0.04 sec)Records: 1826 Duplicates: 0 Warnings: 0select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema = schema() and table_name='trb3';+------+------------------+-------+------------+| part | expr | descr | table_rows |+------+------------------+-------+------------+| p0 | YEAR(purchased) | 1990 | 1826 || p1 | YEAR(purchased) | 1995 | 1826 || p2 | YEAR(purchased) | 2000 | 1826 || p3 | YEAR(purchased) | 2005 | 1826 || p4 | YEAR(purchased) | 2010 | 0 |+------+------------------+-------+------------+5 rows in set (0.00 sec)</pre><h3 id="hash-key分區">HASH KEY分區</h3><pre class="brush:sql;">CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)PARTITION BY hash( YEAR(purchased) ) partitions 12;mysql>call pr_trb3('1985-01-01','2004-12-31','trb3');select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema = schema() and table_name='trb3';+------+------------------+-------+------------+| part | expr | descr | table_rows |+------+------------------+-------+------------+| p0 | YEAR(purchased) | NULL | 731 || p1 | YEAR(purchased) | NULL | 365 || p2 | YEAR(purchased) | NULL | 365 || p3 | YEAR(purchased) | NULL | 365 || p4 | YEAR(purchased) | NULL | 366 || p5 | YEAR(purchased) | NULL | 730 || p6 | YEAR(purchased) | NULL | 730 || p7 | YEAR(purchased) | NULL | 730 || p8 | YEAR(purchased) | NULL | 732 || p9 | YEAR(purchased) | NULL | 730 || p10 | YEAR(purchased) | NULL | 730 || p11 | YEAR(purchased) | NULL | 730 |+------+------------------+-------+------------+12 rows in set (0.00 sec)</pre><h4 id="縮建分區從12個到8個">縮建分區從12個到8個</h4><pre class="brush:sql;">mysql> ALTER TABLE trb3 COALESCE PARTITION 4;Query OK, 7304 rows affected (0.13 sec)Records: 7304 Duplicates: 0 Warnings: 0select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema = schema() and table_name='trb3';+------+------------------+-------+------------+| part | expr | descr | table_rows |+------+------------------+-------+------------+| p0 | YEAR(purchased) | NULL | 732 || p1 | YEAR(purchased) | NULL | 1095 || p2 | YEAR(purchased) | NULL | 1095 || p3 | YEAR(purchased) | NULL | 1095 || p4 | YEAR(purchased) | NULL | 1097 || p5 | YEAR(purchased) | NULL | 730 || p6 | YEAR(purchased) | NULL | 730 || p7 | YEAR(purchased) | NULL | 730 |+------+------------------+-------+------------+8 rows in set (0.00 sec)mysql> select count(*) from trb3;+----------+| count(*) |+----------+| 7304 |+----------+1 row in set (0.00 sec)</pre><p>沒丟數據</p><p>收縮前2004年在P0</p><pre class="brush:sql;">mysql> select mod(2004,12);+--------------+| mod(2004,12) |+--------------+| 0 |+--------------+</pre><p>收縮后2004年在P4</p><pre class="brush:sql;">mysql> select mod(2004,8);+-------------+| mod(2004,8) |+-------------+| 4 |+-------------+</pre><h3 id="exchanging-partitions-and-subpartitions-with-tables">Exchanging Partitions and Subpartitions with Tables</h3><h3 id="分區子分區交換">分區(子分區)交換</h3><pre class="brush:sql;"> ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt</pre><p>pt是一個分區表，p是pt的分區或子分區，而nt是一個非分區表</p><h4 id="限制條件">限制條件:</h4><p>1.表nt不是分區表<br>2.表nt不是臨時表<br>3.表pt和nt結構在其他方面是相同的<br>4.表n沒有外鍵約束，也沒有其他表引用它的列為外鍵<br>5.表nt的所有行都包含在表p的分區范圍內(比如p range分區最大values less than 10，那么表nt不能有大于等于10的值)</p><h4 id="權限">權限:</h4><p>除了 ALTER, INSERT, and CREATE 權限外，你還要有DROP權限才能執行ALTER TABLE … EXCHANGE PARTITION.</p><h4 id="其他注意事項">其他注意事項:</h4><p>1.執行ALTER TABLE … EXCHANGE PARTITION 不會調用任何在nt表和p表上的觸發器<br>2.在交換表中的任何AUTO_INCREMENT列會被reset<br>3.IGNORE關鍵字在執行ALTER TABLE … EXCHANGE PARTITION時會失效</p><h4 id="完整實例語句如下">完整實例語句如下:</h4><pre class="brush:sql;">ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt;</pre><p>在一次ALTER TABLE EXCHANGE PARTITION 中，只能有一個分區和一個非分區表被交換<br>想交換多個，就執行多次ALTER TABLE EXCHANGE PARTITION<br>任何MySQL支持的分區類型都可以進行交換</p><h4 id="交換實例">交換實例</h4><pre class="brush:sql;">CREATE TABLE e (id INT NOT NULL,fname VARCHAR(30),lname VARCHAR(30))PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (50),PARTITION p1 VALUES LESS THAN (100),PARTITION p2 VALUES LESS THAN (150),PARTITION p3 VALUES LESS THAN (MAXVALUE));INSERT INTO e VALUES (1669, "Jim", "Smith"),(337, "Mary", "Jones"),(16, "Frank", "White"),(2005, "Linda", "Black");</pre><p>創建一個與e結構一樣的非分區表e2</p><pre class="brush:sql;">mysql> create table e2 like e;Query OK, 0 rows affected (0.01 sec)mysql> show create table e2\G*************************** 1. row ***************************Table: e2Create Table: CREATE TABLE `e2` (`id` int(11) NOT NULL,`fname` varchar(30) DEFAULT NULL,`lname` varchar(30) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (id)(PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB,PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */1 row in set (0.00 sec)mysql> alter table e2 remove partitioning;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table e2\G*************************** 1. row ***************************Table: e2Create Table: CREATE TABLE `e2` (`id` int(11) NOT NULL,`fname` varchar(30) DEFAULT NULL,`lname` varchar(30) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)</pre><p>查看數據在e表中的分布:</p><pre class="brush:sql;">select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema = schema() and table_name='e'+------+------+----------+------------+| part | expr | descr | table_rows |+------+------+----------+------------+| p0 | id | 50 | 1 || p1 | id | 100 | 0 || p2 | id | 150 | 0 || p3 | id | MAXVALUE | 3 |+------+------+----------+------------+4 rows in set (0.00 sec)</pre><p>將分區p0與e2表進行交換:</p><pre class="brush:sql;">mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;Query OK, 0 rows affected (0.01 sec)select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema = schema() and table_name='e';+------+------+----------+------------+| part | expr | descr | table_rows |+------+------+----------+------------+| p0 | id | 50 | 0 || p1 | id | 100 | 0 || p2 | id | 150 | 0 || p3 | id | MAXVALUE | 3 |+------+------+----------+------------+4 rows in set (0.01 sec)mysql> select * from e2;+----+-------+-------+| id | fname | lname |+----+-------+-------+| 16 | Frank | White |+----+-------+-------+1 row in set (0.00 sec) </pre><p>重做實驗，這次在交換前在表e2中插入一些數據</p><pre class="brush:sql;">mysql> insert into e2 values(16,'FAN','BOSHI');Query OK, 1 row affected (0.00 sec)mysql> insert into e2 values(51,'DU','YALAN');Query OK, 1 row affected (0.00 sec)mysql> select * from e2;+----+-------+-------+| id | fname | lname |+----+-------+-------+| 16 | FAN | BOSHI || 51 | DU | YALAN |+----+-------+-------+2 rows in set (0.00 sec)mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;ERROR 1737 (HY000): Found a row that does not match the partition</pre><p>報錯了，因為51超出了p0的范圍。<br>如之前所說，此時使用IGNORE也無濟于事</p><pre class="brush:sql;">mysql> ALTER IGNORE TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;ERROR 1737 (HY000): Found a row that does not match the partition</pre><p>修改id為49，這樣就屬于p0的范圍了</p><pre class="brush:sql;">mysql> update e2 set id=49 where id=51;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;Query OK, 0 rows affected (0.01 sec)mysql> select -> partition_name part, -> partition_expression expr, -> partition_description descr, -> table_rows -> from information_schema.partitions where -> table_schema = schema() -> and table_name='e';+------+------+----------+------------+| part | expr | descr | table_rows |+------+------+----------+------------+| p0 | id | 50 | 2 || p1 | id | 100 | 0 || p2 | id | 150 | 0 || p3 | id | MAXVALUE | 3 |+------+------+----------+------------+4 rows in set (0.00 sec)e2的數據被交換到了p0中mysql> select * from e partition(p0);+----+-------+-------+| id | fname | lname |+----+-------+-------+| 16 | FAN | BOSHI || 49 | DU | YALAN |+----+-------+-------+2 rows in set (0.00 sec)e的p0分區中的數據被交換到了e2中mysql> select * from e2;+----+-------+-------+| id | fname | lname |+----+-------+-------+| 16 | Frank | White |+----+-------+-------+1 row in set (0.01 sec)</pre><h4 id="交換subpartition">交換subpartition</h4><pre class="brush:sql;">CREATE TABLE es (id INT NOT NULL,fname VARCHAR(30),lname VARCHAR(30))PARTITION BY RANGE (id)SUBPARTITION BY KEY (lname)SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN (50),PARTITION p1 VALUES LESS THAN (100),PARTITION p2 VALUES LESS THAN (150),PARTITION p3 VALUES LESS THAN (MAXVALUE));INSERT INTO es VALUES(1669, "Jim", "Smith"),(337, "Mary", "Jones"),(16, "Frank", "White"),(2005, "Linda", "Black");CREATE TABLE es2 LIKE es;ALTER TABLE es2 REMOVE PARTITIONING;</pre><p>盡管我們沒有顯示的指定每個子分區的名字，我們仍可以通過information_schema.partitions表獲取到子分區的名字</p><pre class="brush:sql;">select partition_name part, subpartition_name, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema = schema() and table_name='es';+------+-------------------+------+----------+------------+| part | subpartition_name | expr | descr | table_rows |+------+-------------------+------+----------+------------+| p0 | p0sp0 | id | 50 | 1 || p0 | p0sp1 | id | 50 | 0 || p1 | p1sp0 | id | 100 | 0 || p1 | p1sp1 | id | 100 | 0 || p2 | p2sp0 | id | 150 | 0 || p2 | p2sp1 | id | 150 | 0 || p3 | p3sp0 | id | MAXVALUE | 3 || p3 | p3sp1 | id | MAXVALUE | 0 |+------+-------------------+------+----------+------------+</pre><p>接下來，開始將p3sp0和es進行交換</p><pre class="brush:sql;">mysql> select * from es partition(p3sp0);+------+-------+-------+| id | fname | lname |+------+-------+-------+| 1669 | Jim | Smith || 337 | Mary | Jones || 2005 | Linda | Black |+------+-------+-------+3 rows in set (0.00 sec)mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;Query OK, 0 rows affected (0.00 sec)mysql> select * from es partition(p3sp0);Empty set (0.00 sec)mysql> select * from es2;+------+-------+-------+| id | fname | lname |+------+-------+-------+| 1669 | Jim | Smith || 337 | Mary | Jones || 2005 | Linda | Black |+------+-------+-------+3 rows in set (0.00 sec)</pre><p>如果一個分區表有子分區，那么你只能以子分區為粒度進行交換，而不能直接交換子分區的父分區</p><pre class="brush:sql;">mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition</pre><p>EXCHANGE PARTITION有著嚴格的要求<br>兩個將要交換的表的 列名，列的創建順序，列的數量，以及索引都要嚴格一致。當然存儲引擎也要一致</p><pre class="brush:sql;">mysql> desc es2;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | NO | | NULL | || fname | varchar(30) | YES | | NULL | || lname | varchar(30) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> create index id_name on es2(id,fname);Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;ERROR 1736 (HY000): Tables have different definitions</pre><p>改變es2的存儲引擎</p><pre class="brush:sql;">mysql> drop index id_name on es2;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table es2 engine=myisam;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL</pre><h3 id="分區表的維護">分區表的維護</h3><p>CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE可以被用于維護分區表</p><p>Rebuilding partitions.相當于將分區中的數據drop掉再插入回來,對于避免磁盤碎片很有效<br>Example:</p><pre class="brush:sql;">ALTER TABLE t1 REBUILD PARTITION p0, p1;</pre><p>Optimizing partitions.如果你的表增加刪除了大量數據，或者進行了大量的邊長列的更新操作( VARCHAR, BLOB, or TEXT columns)。那么optimize partition將回收未使用的空間，并整理分區數據文件。<br>Example:</p><pre class="brush:sql;">ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;</pre><p>運行OPTIMIZE PARTITION 相當于做了 CHECK PARTITION, ANALYZE PARTITION, and REPAIR PARTITION</p><blockquote><p>Some MySQL storage engines, including InnoDB, do not support per-partition optimization; in these cases, ALTER TABLE … OPTIMIZE PARTITION rebuilds the entire table. In MySQL 5.6.9 and later, running this statement on such a table causes the entire table to rebuilt and analyzed, and an appropriate warning to be issued. (Bug #11751825, Bug #42822) Use ALTER TABLE … REBUILD PARTITION and ALTER TABLE … ANALYZE PARTITION instead, to avoid this issue.</p></blockquote><p>Analyzing partitions.讀取并保存分區的鍵分布<br>Example:</p><pre class="brush:sql;">ALTER TABLE t1 ANALYZE PARTITION p3;</pre><p>Repairing partitions.修補被破壞的分區<br>Example:</p><pre class="brush:sql;">ALTER TABLE t1 REPAIR PARTITION p0,p1;</pre><p>Checking partitions.可以使用幾乎與對非分區表使用CHECK TABLE 相同的方式檢查分區。<br>Example:</p><pre class="brush:sql;">ALTER TABLE trb3 CHECK PARTITION p1;</pre><p>這個命令可以告訴你表trb3的分區p1中的數據或索引是否已經被破壞。如果發生了這種情況，使用“ALTER TABLE … REPAIR PARTITION”來修補該分區。</p><h4 id="以上每個命令都支持將分區換成all">以上每個命令都支持將分區換成ALL</h4><blockquote><p>The use of mysqlcheck and myisamchk is not supported with partitioned tables.</p></blockquote><p>mysqlcheck和myisamchk不支持分區表</p><p>你可以使用 ALTER TABLE … TRUNCATE PARTITION. 來刪除一個或多個分區中的數據<br>如:ALTER TABLE … TRUNCATE PARTITION ALL刪除所有數據</p><p>ANALYZE, CHECK, OPTIMIZE, REBUILD, REPAIR, and TRUNCATE 操作不支持 subpartitions.</p></enddate> |
以上所述是小編給大家介紹的Mysql分區表的管理與維護，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對服務器之家網站的支持！
