0.1、分库分表的基本原则

亦凉 2022-11-19 09:42 151阅读 0赞

### 文章目录 ###

*   *   *  前言
         *  一图胜千言
         *  默认16kb的页表
         *   *  从16 kb到8kb的限制
             *  索引和页表结构的关系
             *  Innode 页表结构
             *   *  Innodb 行结构
             *  验证行空间8000字节的限制—— 1030错误信息：Got error 139 from storage engine
         *  数据库空间、表空间、索引空间
         *  单表2000万条数据是理性状态
         *  分表的原则
         *  分库的原则
         *  参考资料

### 前言 ###

本文基于 Mysql 数据库进行，并且围绕 Innodb 存储引擎。

### 一图胜千言 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70]

### 默认16kb的页表 ###

Page是MySQL Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位，其默认大小为16kb。  
InnoDB逻辑存储单元主要分为表空间、段、区和页。层级关系为tablespace -> segment -> extent(64个page，1M) -> page  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70 1]

#### 从16 kb到8kb的限制 ####

Innodb 默认数据存储的最小单元是页表，值为16kb，Innodb 存储引擎保证了每一页至少有两条记录。由此页表中最大行长度略小于页大小的一半，即8000字节。

> 16kb=16*1024=16384字节  
> 存储两行数据来算 16384-8000*2=384字节 剩余的384字节用于存储一些其他必要的信息，这个在下面page结构中会说明  
> 这个限制行长度限制又体现在每一列的长度限制上，如果某一列的长度超过768字节，则多余的内容会存储到溢出页，此时查询就会增加一次IO,所以尽量避免。  
> 768字节的前缀伴有一个20字节的值，该值存储列的真实长度，并指向存储其余值的溢出列表。

#### 索引和页表结构的关系 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70 2]

#### Innode 页表结构 ####

上面说到，Innode 会保证 16kb Page大小情况下，会存储2行数据，占用不多于8000*2=16000字节的存储空间，剩余的16*1024-16000=384字节用于存储其他内容  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70 3]

<table> 
 <thead> 
  <tr> 
   <th>名称</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>File Header</td> 
   <td>字段用于记录 Page 的头信息，其中比较重要的是 FIL_PAGE_PREV 和 FIL_PAGE_NEXT 字段，通过这两个字段，我们可以找到该页的上一页和下一页，实际上所有页通过两个字段可以形成一条双向链表</td> 
  </tr> 
  <tr> 
   <td>Page Header</td> 
   <td>字段用于记录 Page 的状态信息</td> 
  </tr> 
  <tr> 
   <td>Infimum 和 Supremum</td> 
   <td>是两个伪行记录，Infimum（下确界）记录比该页中任何主键值都要小的值，Supremum （上确界）记录比该页中任何主键值都要大的值，这个伪记录分别构成了页中记录的边界</td> 
  </tr> 
  <tr> 
   <td>User Records</td> 
   <td>中存放的是实际的数据行记录，默认行记录长度不超过8000字节，单列不超过768字节，超过的部分会存储到溢出页，768字节的前缀伴有一个20字节的值，该值存储列的真实长度，并指向存储其余值的溢出列表。Innodb支持事务，因此还会有一些隐藏列，看附图</td> 
  </tr> 
  <tr> 
   <td>Free Space</td> 
   <td>存放的是空闲空间，被删除的行记录会被记录成空闲空间</td> 
  </tr> 
  <tr> 
   <td>Page Directory</td> 
   <td>记录着与二叉查找相关的信息</td> 
  </tr> 
  <tr> 
   <td>File Trailer</td> 
   <td>存储用于检测数据完整性的校验和等数据</td> 
  </tr> 
 </tbody> 
</table>

##### Innodb 行结构 #####

*  Innodb 存储引擎提供了两种格式的行记录：Compact 和 Redundant。
 *  用于支持事务、默认主键  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70 4]

#### 验证行空间8000字节的限制—— 1030错误信息：Got error 139 from storage engine ####

*  制造错误  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70 5]  
    ![在这里插入图片描述][20210122003554264.png]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70 6]
 *  对这个问题的解释  
    具体可查看 http://blog.haohtml.com/archives/12886  
    摘取最关键的信息

In 4.1, to support at least 256-character UTF-8 column prefix indexes, InnoDB stores at  
least 768 bytes of each column ‘internally’ to the record. With 11 TEXT fields you will  
run over the 8000 byte record len limit.

### 数据库空间、表空间、索引空间 ###

Innodb 数据库的索引和行数据都需要占据空间，并且，某些时候索引所占据的存储空间要多于数据本身。  
下面给出常用的查询数据库、表、索引占用空间的查询语句

# mysql 查询表存储、索引存储所占空间大小
    select table_schema as '数据库',
    table_name as '表名',
    table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as '索引容量(MB)'from information_schema.tables
    where table_schema='数据库名'order by data_length desc, index_length desc;

![在这里插入图片描述][20210122004148998.png]

### 单表2000万条数据是理性状态 ###

我们在市面上见到过类似的建议，mysql单表数据量不宜超过2000万条，这是一个什么概念呢，假如我们使用 Innodb 存储引擎来存储300G数据，按照8KB的行存储空间限制  
300*1024*1024/8/10000 约= 3932万  
但是我们一般还需要存储索引，而索引的存储空间要比行数据还要大，这里我们按照一样大来计算  
3932万/2=1966万  
所以可以看到，单表2000万条数据是存储300G的左右的数据量，事实上，300G也是单库的一个理想状态，所以从这个角度看，2000万条数据已经有些多了。

### 分表的原则 ###

考虑到数据增长和数据库的查询能力，在进行分表时需要考虑5年左右单表的数据量在1000万条左右。

### 分库的原则 ###

考虑到数据的增长和数据库的查询能力，在进行分库时需要考虑5年左右单库的数据总量，这个限度为 300G,如果超过这个限制则应该进行分库操作。  
事实上，数据库的存储总量是基于其包含的表的。

### 参考资料 ###

\[1\]、https://www.cnblogs.com/bdsir/p/8745553.html  
\[2\]、https://www.cnblogs.com/libra0920/p/6411051.html  
\[3\]、https://www.sohu.com/a/434529381\_505827?sec=wd  
\[4\]、http://blog.haohtml.com/archives/12886

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70]: /images/20221119/e123082503564328881833ca1e230541.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70 1]: /images/20221119/0bd874c042654afea83d7d428f923c51.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70 2]: /images/20221119/5d4947990a7846be820e1856f23c789e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70 3]: /images/20221119/be35bb1a27974b8fa24c7744f7563166.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70 4]: /images/20221119/1a2c00ea80594332915a1859097477e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70 5]: /images/20221119/46812ff522fb4ae393881af3f4026652.png
[20210122003554264.png]: /images/20221119/b9a4035f76314214b2c6837321d6cae0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jlc3RjeHg_size_16_color_FFFFFF_t_70 6]: /images/20221119/65a1d3f1ce0346f593d371ac1804830d.png
[20210122004148998.png]: /images/20221119/b1e4f29487ef4c48b9f5b796330f8536.png