改變一個(gè)表的分區(qū)方案只需使用alter table 加 partition_options 子句就可以了。和創(chuàng)建分區(qū)表時(shí)的create table語句很像。
創(chuàng)建表
|
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)); |
創(chuàng)建插入數(shù)據(jù)存儲(chǔ)過程
|
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>調(diào)用存儲(chǔ)過程插入數(shù)據(jù)</p><pre class="brush:sql;">call pr_trb3('1985-01-01','2004-12-31','trb3');</pre><p>查看數(shù)據(jù)分布</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>改變分區(qū)方案</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>查看數(shù)據(jù)</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(*)行數(shù)一致，說明數(shù)據(jù)沒出問題，但是information_schema.partitions查出來的不對(duì)<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>再次改變分區(qū)方案</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>丟數(shù)據(jù)了。。</del><br>更正，實(shí)際沒丟，這個(gè)information_shcema.partitions表有延遲，過一會(huì)再查就好了</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="改存儲(chǔ)引擎和普通表沒啥區(qū)別">改存儲(chǔ)引擎，和普通表沒啥區(qū)別</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="將表由分區(qū)表改為非分區(qū)表">將表由分區(qū)表改為非分區(qū)表</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分區(qū)管理">Range List分區(qū)管理</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="增加分區(qū)">增加分區(qū)</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="合并分區(qū)">合并分區(qū)</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="分裂分區(qū)">分裂分區(qū)</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分區(qū)">HASH KEY分區(qū)</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="縮建分區(qū)從12個(gè)到8個(gè)">縮建分區(qū)從12個(gè)到8個(gè)</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>沒丟數(shù)據(jù)</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="分區(qū)子分區(qū)交換">分區(qū)(子分區(qū))交換</h3><pre class="brush:sql;"> ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt</pre><p>pt是一個(gè)分區(qū)表，p是pt的分區(qū)或子分區(qū)，而nt是一個(gè)非分區(qū)表</p><h4 id="限制條件">限制條件:</h4><p>1.表nt不是分區(qū)表<br>2.表nt不是臨時(shí)表<br>3.表pt和nt結(jié)構(gòu)在其他方面是相同的<br>4.表n沒有外鍵約束，也沒有其他表引用它的列為外鍵<br>5.表nt的所有行都包含在表p的分區(qū)范圍內(nèi)(比如p range分區(qū)最大values less than 10，那么表nt不能有大于等于10的值)</p><h4 id="權(quán)限">權(quán)限:</h4><p>除了 ALTER, INSERT, and CREATE 權(quán)限外，你還要有DROP權(quán)限才能執(zhí)行ALTER TABLE … EXCHANGE PARTITION.</p><h4 id="其他注意事項(xiàng)">其他注意事項(xiàng):</h4><p>1.執(zhí)行ALTER TABLE … EXCHANGE PARTITION 不會(huì)調(diào)用任何在nt表和p表上的觸發(fā)器<br>2.在交換表中的任何AUTO_INCREMENT列會(huì)被reset<br>3.IGNORE關(guān)鍵字在執(zhí)行ALTER TABLE … EXCHANGE PARTITION時(shí)會(huì)失效</p><h4 id="完整實(shí)例語句如下">完整實(shí)例語句如下:</h4><pre class="brush:sql;">ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt;</pre><p>在一次ALTER TABLE EXCHANGE PARTITION 中，只能有一個(gè)分區(qū)和一個(gè)非分區(qū)表被交換<br>想交換多個(gè)，就執(zhí)行多次ALTER TABLE EXCHANGE PARTITION<br>任何MySQL支持的分區(qū)類型都可以進(jìn)行交換</p><h4 id="交換實(shí)例">交換實(shí)例</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>創(chuàng)建一個(gè)與e結(jié)構(gòu)一樣的非分區(qū)表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>查看數(shù)據(jù)在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>將分區(qū)p0與e2表進(jìn)行交換:</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>重做實(shí)驗(yàn)，這次在交換前在表e2中插入一些數(shù)據(jù)</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>報(bào)錯(cuò)了，因?yàn)?1超出了p0的范圍。<br>如之前所說，此時(shí)使用IGNORE也無濟(jì)于事</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的數(shù)據(jù)被交換到了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分區(qū)中的數(shù)據(jù)被交換到了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>盡管我們沒有顯示的指定每個(gè)子分區(qū)的名字，我們?nèi)钥梢酝ㄟ^information_schema.partitions表獲取到子分區(qū)的名字</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進(jìn)行交換</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>如果一個(gè)分區(qū)表有子分區(qū)，那么你只能以子分區(qū)為粒度進(jìn)行交換，而不能直接交換子分區(qū)的父分區(qū)</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有著嚴(yán)格的要求<br>兩個(gè)將要交換的表的 列名，列的創(chuàng)建順序，列的數(shù)量，以及索引都要嚴(yán)格一致。當(dāng)然存儲(chǔ)引擎也要一致</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的存儲(chǔ)引擎</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="分區(qū)表的維護(hù)">分區(qū)表的維護(hù)</h3><p>CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE可以被用于維護(hù)分區(qū)表</p><p>Rebuilding partitions.相當(dāng)于將分區(qū)中的數(shù)據(jù)drop掉再插入回來,對(duì)于避免磁盤碎片很有效<br>Example:</p><pre class="brush:sql;">ALTER TABLE t1 REBUILD PARTITION p0, p1;</pre><p>Optimizing partitions.如果你的表增加刪除了大量數(shù)據(jù)，或者進(jìn)行了大量的邊長(zhǎng)列的更新操作( VARCHAR, BLOB, or TEXT columns)。那么optimize partition將回收未使用的空間，并整理分區(qū)數(shù)據(jù)文件。<br>Example:</p><pre class="brush:sql;">ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;</pre><p>運(yùn)行OPTIMIZE PARTITION 相當(dāng)于做了 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.讀取并保存分區(qū)的鍵分布<br>Example:</p><pre class="brush:sql;">ALTER TABLE t1 ANALYZE PARTITION p3;</pre><p>Repairing partitions.修補(bǔ)被破壞的分區(qū)<br>Example:</p><pre class="brush:sql;">ALTER TABLE t1 REPAIR PARTITION p0,p1;</pre><p>Checking partitions.可以使用幾乎與對(duì)非分區(qū)表使用CHECK TABLE 相同的方式檢查分區(qū)。<br>Example:</p><pre class="brush:sql;">ALTER TABLE trb3 CHECK PARTITION p1;</pre><p>這個(gè)命令可以告訴你表trb3的分區(qū)p1中的數(shù)據(jù)或索引是否已經(jīng)被破壞。如果發(fā)生了這種情況，使用“ALTER TABLE … REPAIR PARTITION”來修補(bǔ)該分區(qū)。</p><h4 id="以上每個(gè)命令都支持將分區(qū)換成all">以上每個(gè)命令都支持將分區(qū)換成ALL</h4><blockquote><p>The use of mysqlcheck and myisamchk is not supported with partitioned tables.</p></blockquote><p>mysqlcheck和myisamchk不支持分區(qū)表</p><p>你可以使用 ALTER TABLE … TRUNCATE PARTITION. 來刪除一個(gè)或多個(gè)分區(qū)中的數(shù)據(jù)<br>如:ALTER TABLE … TRUNCATE PARTITION ALL刪除所有數(shù)據(jù)</p><p>ANALYZE, CHECK, OPTIMIZE, REBUILD, REPAIR, and TRUNCATE 操作不支持 subpartitions.</p></enddate> |
以上所述是小編給大家介紹的Mysql分區(qū)表的管理與維護(hù)，希望對(duì)大家有所幫助，如果大家有任何疑問請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)服務(wù)器之家網(wǎng)站的支持！
