mysql数据库热块_演示热块——表数据块

最新推荐文章于 2021-02-18 23:01:03 发布

bh lin

最新推荐文章于 2021-02-18 23:01:03 发布

阅读量547

点赞数

文章标签： mysql数据库热块

本文链接：https://blog.csdn.net/weixin_35671110/article/details/113293345

版权

当访问频率非常高的数据块称为热块(hot block)，当很多用户一起去访问某几个数据块时，就会导致一些 Latch 争用。最常见 Latch 争用是： buffer busy waits

cache buffer chain

这两个 Latch 争用分别发生在访问数据块的不同时刻。

当一个会话需要去访问一个内存块时，它首先要去一个像链表的结构中去检索这个数据块是否存在在内存中，会话访问这个链表时需要获得一个 Latch，如果获得失败，就会产生 Latch cache buffer chains 等待，导致这个等待的原因是访问相同数据块的会话太多或者这个列表太长(如果读到内存中的数据块太多，需要管理数据块的 hash 列表就会很长，这样会话扫描列表的时间就会增加，持有 cache buffer chain latch 的时间就会变长，其他会话获得这个 Latch 的机会就会降低，等待就会增加)。

当一个会话需要访问一个数据块，而这个数据块正在被另一个用户从磁盘读取到内存中或者这个数据块正在被另一个会话修改时，当前会话就需要等待，就会产生一个 buffer busy waits 等待。

产生这些 Latch 争用的直接原因是太多的会话去访问相同的数据块导致热块问题，造成热块的原因可能是数据库设置导致或重复执行 SQL 频繁访问一些相同数据块导致。

热块产生的原因，按数据块类型，可以分为如下类型，不同热块类型处理的方式不同。表数据块

索引数据块

索引根数据块

文件头数据块

本文演示表数据块。

比如在 OLTP 系统中，一些小表，会出现某些数据块被频繁查询或修改的操作，此时，这些被频繁访问数据块就会成为热块，导致内存中的 Latch 争用。

SQL> create table t as select * from dba_objects;

表已创建。

SQL> create index t_idx on t(object_id);

索引已创建。

SQL> exec dbms_stats.gather_table_stats('test','t',cascade=>true);

PL/SQL 过程已成功完成。

SQL> select 't' tbl_name, rows_per_block, count(*) number_of_such_blocks

2 from (select dbms_rowid.rowid_block_number(rowid), count(*) rows_per_block

3 from t

4 group by dbms_rowid.rowid_block_number(rowid))

5 group by 't', rows_per_block;

T ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS

- -------------- ---------------------

t 69 28

t 64 117

t 58 1

t 60 1

t 80 10

t 88 2

t 62 10

t 76 28

t 63 53

t 78 13

t 83 1

T ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS

- -------------- ---------------------

t 71 23

t 75 27

t 74 45

t 89 2

t 66 170

t 67 80

t 85 1

t 23 1

t 79 11

t 73 38

t 65 227

T ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS

- -------------- ---------------------

t 68 44

t 77 25

t 72 28

t 70 37

t 81 3

t 90 2

t 61 2

已选择29行。

SQL>

可以看到 t 表，每个数据块平均存放 70 行左右。

SQL> create table t1 as

2 select * from dba_objects where rownum < 3;

表已创建。

SQL> select 't1' tbl_name, rows_per_block, count(*) number_of_such_blocks

2 from (select dbms_rowid.rowid_block_number(rowid), count(*) rows_per_bloc

3 from t1

4 group by dbms_rowid.rowid_block_number(rowid))

5 group by 't1', rows_per_block;

TB ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS

-- -------------- ---------------------

t1 2 1

SQL> alter table t1 minimize records_per_block;

表已更改。

SQL> insert into t1 select * from dba_objects;

已创建70020行。

SQL> commit;

提交完成。

SQL> create index t1_idx on t1(object_id);

索引已创建。

SQL> exec dbms_stats.gather_table_stats('test','t1',cascade=>true);

PL/SQL 过程已成功完成。

SQL> select 't1' tbl_name, rows_per_block, count(*) number_of_such_blocks

2 from (select dbms_rowid.rowid_block_number(rowid), count(*) rows_per_bloc

3 from t1

4 group by dbms_rowid.rowid_block_number(rowid))

5 group by 't1', rows_per_block;

TB ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS

-- -------------- ---------------------

t1 2 35011

SQL>

可以看到 t1 表上，每个数据块上存放的记录减少到2条，这样 t1 表上的数据块会远远多于 t 表。

分别检索 t 表和 t1 表。

SQL> set autotrace traceonly;

SQL> select * from t where object_id < 1000;

已选择940行。

执行计划

----------------------------------------------------------

Plan hash value: 470836197

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 537 | 54237 | 14 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 537 | 54237 | 14 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_IDX | 537 | | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"<1000)

统计信息

----------------------------------------------------------

1 recursive calls

0 db block gets

140 consistent gets

2 physical reads

0 redo size

94726 bytes sent via SQL*Net to client

1098 bytes received via SQL*Net from client

128 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

940 rows processed

SQL> select * from t1 where object_id < 1000;

已选择942行。

执行计划

----------------------------------------------------------

Plan hash value: 546753835

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 537 | 54237 | 280 (0)| 00:00:04 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 537 | 54237 | 280 (0)| 00:00:04 |

|* 2 | INDEX RANGE SCAN | T1_IDX | 537 | | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"<1000)

统计信息

----------------------------------------------------------

1 recursive calls

0 db block gets

616 consistent gets

471 physical reads

0 redo size

94886 bytes sent via SQL*Net to client

1098 bytes received via SQL*Net from client

128 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

942 rows processed

SQL>

结果相同，执行计划相同。

但是 t1 表一致性读为 616，明显比 t 表的 140 大很多。因为 t1 表中的行分布在更多的数据块上，导致 Oracle 需要读取更多的数据块来完成查询。但是，从另一个角度看待，把数据分布到更多的数据块上，可以大大降低一个数据块被重复读取的概率。

这种方式缺点显而易见，降低了数据读取的性能。但是，很多用户值修改一条数据并且各自更新的数据不同，那么这样做的好处就非常明显了。在 t 表，一个数据块上大约有70条记录，如果可能有70个用户的同时访问一个数据块，就必然导致热块；当我们让每个数据块只存放2条记录时，最多只会有2个用户同时访问一个数据块，这样，热块的概率将会被大大降低。