mysql有行迁移吗_行链接和行迁移的秘密

一、概述：

如果你的Oracle数据库性能低下，行链接和行迁移可能是其中的原因之一。我们能够通过合理的设计或调整数据库来阻止这个现象。

行链接和行迁移是能够被避免的两个潜在性问题。我们可以通过合理的调整来提高数据库性能。本文主要描述的是：

什么是行迁移与行链接

如何判断行迁移与行链接

如何避免行迁移与行链接

当使用索引读取单行时，行迁移影响OLTP系统。最糟糕的情形是，对所有读取操作而言，增加了额外的I/O。行链接则影响索引读和全表扫描。

注：在翻译行(row)时使用记录来描述(便于理解)，如第一行，使用第一条记录。

二、Oralce 块

操作系统块的大小是操作系统读写的最小操作单元，也是操作系统文件的属性之一。当创建一个数据库时，选择一个基于操作系统块的

整数倍大小作为Oracle数据库块的大小。Oracle数据库读写操作则是以Oracle块为最小单位，而非操作系统块。一旦设置了Oracle数据块的大小，

则在整个数据库生命期间不能被更改(除 Oracle 9i之外)。因此为Oracle数据库定制合理的Oralce块大小，象预期数据库总大小以及并发用户数这些

因素应当予以考虑。

数据库块由下列逻辑结构(在整个数据库结构下)

头部

头部包含一些常用信息，象块地址，段的类型(表段、索引段等)。也包含一些表、实际数据行的地址信息等。

空闲空间

用于保留给后续DML(update/insert)操作的空间。通常受pctfree和pctused参数的影响。

数据

实际的数据内容

FREELIST, PCTFREE, PCTUSED

当创建或修改任意表，索引时。Oracle使用两个存储参数来控制空间的分配

PCTFREE 为已存在数据将来更新需要保留空闲空间的百分比

PCTUSED 新插入数据可使用空间的最小百分比，该值决定块何时回收到 freelist 结构中

FREELIST Oracle通过维护该列表来记录或更新所有可用的数据块

Oracle 首先在freelist列表上搜索可用的空闲数据块，搜索成功之后将数据插入到那个空闲块。块在free list 列表中的可用性由pctfree

参数值来决定。起初一个空块在freelist列表上列出，并且会一直保留，直到到空闲空间达到pctfree设定的值。

当一个块被使用且达到pctfree设定的值之后则该块从freelist列表被移除，而当数据块的可用空间低于PCTUSED值的时候，该块又会回收，

即重新回到freelist列表。

Oracle使用freelist方式以提高数据库性能。因此，每一个insert 操作，Oracle 仅仅需要搜索freelist结构，而不是搜索所有数据块。

三、行迁移：

当一个行上的更新操作(原来的数据存在且没有减少)导致当前的数据不能在容纳在当前块，我们需要进行行迁移。一个行迁移意味着整

行数据将会移动，仅仅保留的是一个转移地址。因此整行数据都被移动，原始的数据块上仅仅保留的是指向新块的一个地址信息。

迁移行不影响全表扫描

当使用全表扫描时，转移地址被忽略。因为我们最终能够获得所有的数据，所以能够忽略其转移地址。因此行迁移并不会对全表扫描产

生额外的操作。

迁移行对索引读产生额外的I/O

当使用索引读取数据时将产生额外的I/O。这是由于索引告诉我们通过文件X,块Y,slot槽Z可以获得该行数据。但是当我们根据地址信息

达到所在位置时，从其转移地址得知，其真实的数据是存储在文件A,块B,slot槽C上。因此为了寻找该行数据不得不产生额外的逻辑或物理I/O。

四、行链接

当一行数据太大而不能在一个单数据块容纳时，行链接由此产生。举例来说，当你使用了4kb的Oracle 数据块大小，而你需要插入一行

数据是8k，Oracle则需要使用3个数据块分成片来存储。因此，引起行链接的情形通常是，表上行记录的大小超出了数据库Oracle块的大小。

表上使用了LONG 或 LONG RAW数据类型的时候容易产生行链接。其次表上多于255列时Oracle会将这些过宽的表分片而产生行链接。因此，

与行迁移所不同的是，行迁移是将数据存放到另外一个块，而行链接是数据同时位于多个数据块。

行链接有着不同于行迁移的影响，取决于我们所需的数据。如果我们有一行数据有两个列且跨越两个数据块。假定使用下面的查询：

SELECT column1 FROM table

列1位于第一块，它将不会引起任何table fetch continued row 。由于不需要获得第2列的信息，因此行链接没有任何影响。然而，如

果我们使用下面的查询：

SELECT column2 FROM table

由于 column2 在块2且是一个链接行，因此将产生table fetch continued row

1 [sql] viewplaincopyprint?2 01.--下面的引用Tom Kyte的例子来展示行迁移和行链接. 我们使用了一个4k 的 blocksize.

3 02.4 03.SELECTname,value5 04. FROMv$parameter6 05. WHERE name = 'db_block_size';7 06.8 07.NAME VALUE9 08.-------------- ------

10 09.db_block_size 4096

11 10.12 11.13 12.--使用固定列宽的char类型来创建下面的表

14 13.15 14.CREATE TABLErow_mig_chain_demo (16 15. x int PRIMARY KEY,17 16. a CHAR(1000),18 17. b CHAR(1000),19 18. c CHAR(1000),20 19. d CHAR(1000),21 20. e CHAR(1000)22 21.);23 22.24 23.--上面的这个表很容易产生行链接和行迁移。我们使用了5个列 a,b,c,d,e，以便于整个行的长度增长达到或超过5k以确保产生真实的行链接。

25 24.26 25.INSERT INTO row_mig_chain_demo (x) VALUES (1);27 26.INSERT INTO row_mig_chain_demo (x) VALUES (2);28 27.INSERT INTO row_mig_chain_demo (x) VALUES (3);29 28.COMMIT;30 29.31 30.--a,b,c,d,e列我们并没有兴趣查看，而仅仅是提取他们。由于他们的确太宽，我们在查询结果中没有显示这些列。

32 31.33 32.columna noprint34 33.columnb noprint35 34.columnc noprint36 35.columnd noprint37 36.columne noprint38 37.39 38.SELECT * FROMrow_mig_chain_demo;40 39.41 40. X42 41.----------

43 42. 1

44 43. 2

45 44. 3

46 45.47 46.检查行链接48 47.49 48.SELECTa.name, b.value50 49. FROMv$statname a, v$mystat b51 50. WHERE a.statistic# =b.statistic#52 51. AND lower(a.name) = 'table fetch continued row';53 52.NAME VALUE54 53.---------------------------------------------------------------- ----------

55 54.table fetch continued row 0

56 55.57 56.--现在正如我们所期待的那样，按插入的顺序返回的结果(对这个查询Oracle使用了全表扫描)。也如我们所预料的那样table fetch continued row 为零值。

58 57.--这个数据是现在是如此的小，我们知道三行数据填充到一个数据块，没有行链接。

View Code

六、演示行迁移

1 [sql] viewplaincopyprint?2 01.--接下来，我们基于指定的方式做一些更新来演示行迁移的问题以及他如何影响全扫描。

3 02.4 03.UPDATE row_mig_chain_demo SET a = 'z1', b = 'z2', c = 'z3' WHERE x = 3;5 04.COMMIT;6 05.UPDATE row_mig_chain_demo SET a = 'y1', b = 'y2', c = 'y3' WHERE x = 2;7 06.COMMIT;8 07.UPDATE row_mig_chain_demo SET a = 'w1', b = 'w2', c = 'w3' WHERE x = 1;9 08.COMMIT;10 09.11 10.--注意更新的顺序，最后一行我们首先更新，而第一行则最后更新它。

12 11.13 12.SELECT * FROMrow_mig_chain_demo;14 13.15 14. X16 15.----------

17 16. 3

18 17. 2

19 18. 1

20 19.21 20.SELECTa.name, b.value22 21. FROMv$statname a, v$mystat b23 22. WHERE a.statistic# =b.statistic#24 23. AND lower(a.name) = 'table fetch continued row';25 24.26 25.NAME VALUE27 26.---------------------------------------------------------------- ----------

28 27.table fetch continued row 0

29 28.30 29.--有趣的是，返回的结果是倒序的。是由于我们首先更新了第三行。此时并没有行迁行，但是它填充了整个数据块1。随着第二行的更新，它

31 30.--不得不迁移到第2块，由于第3行几乎占用了整个块。当我们更新第一行的时候，它迁移到块3。最终使第三行数据保留在第一块。

32 31.33 32.--因此，当oralce使用全表扫描时，它首先发现第3条记录在数据块1，第2条记录在数据块2，第1条记录在数据块3。当进行表扫描时，它忽略

34 33.--第一块的头部信息是记录1，记录2的rowid信息。这就是为什么table fetch continued row依旧是零值的原因。此时没有行链接。

View Code

1 [sql] viewplaincopyprint?2 01.--让我们来看一看对table fetch continued row的影响

3 02.4 03.SELECT * FROM row_mig_chain_demo WHERE x = 3;5 04.6 05. X7 06.----------

8 07. 3

9 08.10 09.SELECTa.name, b.value11 10. FROMv$statname a, v$mystat b12 11. WHERE a.statistic# =b.statistic#13 12. AND lower(a.name) = 'table fetch continued row';14 13.15 14.NAME VALUE16 15.---------------------------------------------------------------- ----------

17 16.table fetch continued row 0

18 17.19 18.--这是一个索引范围扫描方式，然后根据主键获得rowid。此时并没有增加table fetch continued row的值，因此记录3并没有发生行迁移。

View Code

七、行链接演示

1 [sql] viewplaincopyprint?2 01.UPDATE row_mig_chain_demo SET d = 'z4', e = 'z5' WHERE x = 3;3 02.COMMIT;4 03.5 04.--update之后记录3新增的内容不在填充到数据块1，因为列d和e之和是5k,因此行链接产生了。

6 05.7 06.SELECT x,a FROM row_mig_chain_demo WHERE x = 3;8 07.9 08. X10 09.----------

11 10. 3

12 11.13 12.SELECTa.name, b.value14 13. FROMv$statname a, v$mystat b15 14. WHERE a.statistic# =b.statistic#16 15. AND lower(a.name) = 'table fetch continued row';17 16.18 17.NAME VALUE19 18.---------------------------------------------------------------- ----------

20 19.table fetch continued row 1

21 20.22 21.23 22.--当从记录3的头部提取列x和列a的时候，将不会产生table fetch continued row，也没有额外的I/O产生。

View Code

1 [sql] viewplaincopyprint?2 01.SELECT x,d,e FROM row_mig_chain_demo WHERE x = 3;3 02.4 03.SELECTa.name, b.value5 04.FROMv$statname a, v$mystat b6 05.WHERE a.statistic# =b.statistic#7 06.AND lower(a.name) = 'table fetch continued row';8 07.9 08.NAME VALUE10 09.---------------------------------------------------------------- ----------

11 10.table fetch continued row 2

12 11.13 12.14 13.--现在当我们通过主键索引扫描从记录3的尾部提取数据时，这将增加table fetch continued row的值。因为需要从行的头部和尾部获取数据来组合。

15 14.16 15.--现在来看看全表扫描是否也有相同的影响。

17 16.18 17.SELECT * FROMrow_mig_chain_demo;19 18.20 19. X21 20.----------

22 21. 3

23 22. 2

24 23. 1

25 24.26 25.SELECTa.name, b.value27 26. FROMv$statname a, v$mystat b28 27. WHERE a.statistic# =b.statistic#29 28. AND lower(a.name) = 'table fetch continued row';30 29.31 30.NAME VALUE32 31.---------------------------------------------------------------- ----------

33 32.table fetch continued row 3

34 33.35 34.--此时table fetch continued row的值被增加，因为不得不对记录3的尾部进行融合。而记录1和2即便是存在迁移现象，但由于是全表扫描，

36 35.--因此不会增加table fetch continued row的值。

37 36.38 37.SELECT x,a FROMrow_mig_chain_demo;39 38.40 39. X41 40.----------

42 41. 3

43 42. 2

44 43. 1

45 44.46 45.SELECTa.name, b.value47 46. FROMv$statname a, v$mystat b48 47. WHERE a.statistic# =b.statistic#49 48. AND lower(a.name) = 'table fetch continued row';50 49.51 50.NAME VALUE52 51.---------------------------------------------------------------- ----------

53 52.table fetch continued row 3

54 53.55 54.--当需要提取的数据是整个表上的头两列的时候，此时table fetch continued row也不会增加。因为不需要对记录3进行数据融合。

56 55.57 56.SELECT x,e FROMrow_mig_chain_demo;58 57.59 58. X60 59.----------

61 60. 3

62 61. 2

63 62. 1

64 63.65 64.SELECTa.name, b.value66 65. FROMv$statname a, v$mystat b67 66. WHERE a.statistic# =b.statistic#68 67. AND lower(a.name) = 'table fetch continued row';69 68.70 69.NAME VALUE71 70.---------------------------------------------------------------- ----------

72 71.table fetch continued row 4

73 72.74 73.--但是当提取列d和e的时候，table fetch continued row的值被增加。通常查询时容易产生行迁移即使是真正存在行链接，因为我们的查询

75 74.--所需的列通常位于表的前几列。

View Code

八、如何鉴别行链接和行迁移

1 [sql] viewplaincopyprint?2 01.--聚合统计所创建的表，这也将使得重构整行而发生table fetch continued row

3 02.4 03.SELECT count(e) FROMrow_mig_chain_demo;5 04.6 05. COUNT(E)7 06.----------

8 07. 1

9 08.10 09.SELECTa.name, b.value11 10. FROMv$statname a, v$mystat b12 11. WHERE a.statistic# =b.statistic#13 12. AND lower(a.name) = 'table fetch continued row';14 13.15 14.NAME VALUE16 15.---------------------------------------------------------------- ----------

17 16.table fetch continued row 5

18 17.19 18.20 19.--通过analyze table来校验表上的链接数

21 20.22 21.ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;23 22.24 23.SELECTchain_cnt25 24. FROMuser_tables26 25. WHERE table_name = 'ROW_MIG_CHAIN_DEMO';27 26.28 27. CHAIN_CNT29 28.----------

30 29. 3

31 30.32 31.--3条记录是链接的。显然，他们中的两条记录是迁移(记录1，记录2)和一记录是链接(记录3).

33 32.34 33.--实例启动后的table fetch continued row的总数

35 34.36 35.--视图v$mystat告诉我们自从实例启动后，所有的表上共有多少次为table fetch continued row.

37 36.38 37.sqlplus system/

39 38.40 39.SELECT 'Chained or Migrated Rows ='||value41 40. FROMv$sysstat42 41. WHERE name = 'table fetch continued row';43 42.44 43.Chained or Migrated Rows = 31637

45 44.46 45.47 46.--上面的查询结果表明，可能有1个表上存在行链接被fetch了31637次，也可能有31637个表，每个表上有一个行链接，每次fetch一次。也有

48 47.--可能是上述情况的组合。

49 48.50 49.--31637次也许是好的，也许是坏的，仅仅是一个值而已。

51 50.--这取决于

52 51.--数据库启动了多久？

53 52.--这个值占总提取数据百分比的多少行？

54 53.--假如它占据了你从表上fetch的0.001%，则无关紧要。

55 54.56 55.--因此，比较table fetch continued row与总提取的记录数是有必要的

57 56.58 57.SELECT name,value FROM v$sysstat WHERE name like '%table%';59 58.60 59.NAME VALUE61 60.---------------------------------------------------------------- ----------

62 61.table scans (short tables) 124338

63 62.table scans (long tables) 1485

64 63.table scans (rowid ranges) 0

65 64.table scans (cache partitions) 10

66 65.table scans (direct read) 0

67 66.table scan rows gotten 20164484

68 67.table scan blocks gotten 1658293

69 68.table fetch by rowid 1883112

70 69.table fetch continued row 31637

71 70.table lookup prefetch client count 0

View Code

九、一个表上链接的行是多少？

1 [sql] viewplaincopyprint?2 01.--通过对表analyze后(未analyze是空值),可以从数据字典user_tales获得链接的记录数。

3 02.ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;4 03.5 04.SELECTchain_cnt,6 05. round(chain_cnt/num_rows*100,2) pct_chained,7 06. avg_row_len, pct_free , pct_used8 07. FROMuser_tables9 08.WHERE table_name = 'ROW_MIG_CHAIN_DEMO';10 09.11 10. CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED12 11.---------- ----------- ----------- ---------- ----------

13 12. 3 100 3691 10 40

14 13.15 14.--PCT_CHAINED 为100%,表明所有的行都是链接的或迁移的。

View Code

十、列出链接行

当使用analyze table中的list chained rows子句能够列出一个表上的链接行。该命令的结果是将所有的链接上存储到一个由list chained rows子句

显示指定的表中。 这些结构有助于决定是否将来有足够的空间实现行更新。

创建CHAINED_ROWS 表

创建一个用于存储analyze ... list chained rows命令结果的表，可以执行位于$ORACLE_HOME/rdbms/admin目录下的UTLCHAIN.SQL或UTLCHN1.SQL 脚本。

这个脚本会在当前schema下创建一个名为chained_rows的表

create table CHAINED_ROWS (

owner_name         varchar2(30),

table_name         varchar2(30),

cluster_name       varchar2(30),

partition_name     varchar2(30),

subpartition_name  varchar2(30),

head_rowid         rowid,

analyze_timestamp  date

);

当chained_rows表创建后，可以使用analyze table命令来指向该表作为输出。

十一、如何避免行链接和行迁移

增加pctfree能够帮助避免行链接。如果我们为块留下更多的可用空间，则行上有空间满足将来的增长。也可以对那些有较高删除率的表采用重新组织

或重建表索引来避免行链接与行迁移。如果表上有些行被频繁的删除，则数据块上会有更多的空闲空间。当被插入的行后续扩展，则被插入的行可能会

分布到那些被删除的行上而仍然没有更多空间来用于扩展。重新组织表则确保主要的空闲空间是完整的空块。

ALTER TABLE ... MOVE 命令允许对一个未分区或分区的表上的数据进行重新分配到一个新的段。也可以分配到一个有配额的不同的表空间。该命令也允许

你在不能使用alter table的情形下来修改表或分区上的一些存储属性。也可以使用ALTER TABLE ... MOVE 命令中的compress关键字在存储到新段时使用压缩选项。

1 [sql] viewplaincopyprint?2 01.

1. ALTER TABLEMOVE3 02.4 03.使用alter tablemove 之前首先统计每个块上的行数.5 04.SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"6 05.  FROMrow_mig_chain_demo7 06.GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;8 07.9 08. Block-Nr        Rows10 09.---------- ----------

11 10. 2066 3

12 11.13 12.--现在消除表上的行链接，使用alter table move来重建row_mig_chain_demo表到一个新段，指定一些新的存储选项。

14 13.ALTER TABLErow_mig_chain_demo MOVE15 14. PCTFREE 20

16 15. PCTUSED 40

17 16. STORAGE (INITIAL 20K18 17. NEXT40K19 18. MINEXTENTS 2

20 19. MAXEXTENTS 20

21 20. PCTINCREASE 0);22 21.23 22.Tablealtered.24 23.25 24.--在alter table move之后再次统计每一块上的行数

26 25.SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"27 26. FROMrow_mig_chain_demo28 27.GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;29 28.30 29. Block-Nr Rows31 30.---------- ----------

32 31. 2322 1

33 32. 2324 1

34 33. 2325 1

35 34.36 35.2. 重建表上的索引37 36.--移动一个表将使得表上记录的rowid发生变化。这将引起表上的索引被置为unusable状态。基于该表使用索引的DML语句将收到ORA-01502 错误。

38 37.--因此表上的索引必须被删除或重建。同样地，表上的统计信息也会变得无效。因此统计信息在表移动之后也应当重新收集。

39 38.ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;40 39.41 40.ERROR at line 1:42 41.ORA-01502: index 'SCOTT.SYS_C003228' or partition of such index is inunusable state43 42.44 43.--表上的主键必须被重建

45 44.ALTER INDEXSYS_C003228 REBUILD;46 45.Indexaltered.47 46.48 47.ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;49 48.Tableanalyzed.50 49.51 50.SELECTchain_cnt,52 51. round(chain_cnt/num_rows*100,2) pct_chained,53 52. avg_row_len, pct_free , pct_used54 53. FROMuser_tables55 54. WHERE table_name = 'ROW_MIG_CHAIN_DEMO';56 55.57 56. CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED58 57.---------- ----------- ----------- ---------- ----------

59 58. 1 33.33 3687 20 40

60 59.--如果表包含LOB 列，用户可以指定该命令连同LOB数据段以及LOB索引段(同该表相关)一起移动(move)。

61 60.--当未指定时，则LOB数据段以及LOB索引段不参与移动。

62 61.

View Code

十二、检测所有表上的行连接与行迁移

可以通过CHAINED_ROWS 表获取所有表上的行链接与行迁移。

1 [sql] viewplaincopyprint?2 01.1.创建chained_rows表3 02.cd $ORACLE_HOME/rdbms/admin4 03.sqlplus scott/tiger5 04.@utlchain.sql6 05.7 06.2.ananlyze 所有表/或指定表8 07.SELECT 'ANALYZE TABLE'||table_name||'LIST CHAINED ROWS INTO CHAINED_ROWS;'

9 08. FROMuser_tables10 09./

11 10.12 11.ANALYZE TABLE ROW_MIG_CHAIN_DEMO LIST CHAINED ROWS INTOCHAINED_ROWS;13 12.ANALYZE TABLE DEPT LIST CHAINED ROWS INTOCHAINED_ROWS;14 13.ANALYZE TABLE EMP LIST CHAINED ROWS INTOCHAINED_ROWS;15 14.ANALYZE TABLE BONUS LIST CHAINED ROWS INTOCHAINED_ROWS;16 15.ANALYZE TABLE SALGRADE LIST CHAINED ROWS INTOCHAINED_ROWS;17 16.ANALYZE TABLE DUMMY LIST CHAINED ROWS INTOCHAINED_ROWS;18 17.19 18.Tableanalyzed.20 19.21 20.3.查看行链接的rowid22 21.SELECTowner_name,23 22. table_name,24 23. count(head_rowid) row_count25 24. FROMchained_rows26 25.GROUP BYowner_name,table_name27 26./

28 27.29 28.OWNER_NAME TABLE_NAME ROW_COUNT30 29.------------------------------ ------------------------------ ----------

31 30.SCOTT ROW_MIG_CHAIN_DEMO 1

View Code

通过该方式可以快速的定位一个表上有多少行链接问题的。如果行链接或行迁移较多，则应当基于该表增加pctfree的值 或重建该表。

十三、结论：

行迁移影响OLTP系统使用索引读取单行。最糟糕的情形所对所有的读都增加额外的I/O。而行链接则影响索引读和全表扫描。

行迁移通常由update操作引起

行链接通常有insert操作引起

基于行链接或行迁移的查询或创建(如索引)由于需要更多的I/O将降低数据库的性能

调试行链接或行迁移使用analyze 命令，查询v$sysdate视图

移出行链接或行迁移使用更大的pctfree参数或使用alter table move命令