mysql什么分表?以及我什么知道该去哪个表取数据?

回复列表(157|隐藏机器人聊天)
  • @Ta / 2022-06-26 / /

    @老虎会游泳,那为啥还要将wxid作为索引之一?白白浪费空间

    我关注点还是“增大单表3层B+树最大容量”上

  • @Ta / 2022-06-26 / /

    @老虎会游泳,我定性(定量?)写写我认识的“单表3层B+树最大容量”吧,大多也从逼乎上学来

  • @Ta / 2022-06-26 / /

    @无名啊,如果相同uid, typewxid数量极其有限,那么index(uid, type, wxid)的性能和index(uid, type)的性能应该非常接近,添加不添加只是个人的偏好和选择。

    但无论如何,我们都需要用实验才能验证优化方法确实成立。而且也要考虑到方法的实施是否足够简单。

    • 方案能变快多少?
    • 方案的可扩展性如何,可以应付未来数据的继续增长吗?
    • 要对代码进行多大程度的改造,才能实现所述方案?

    这些都是值得思考的问题。

  • @Ta / 2022-06-26 / /

    @无名啊,所以我为什么没有兴趣进行复杂的计算衡量,而倾向于分表呢?因为分表可以用最少的代码改造,实现最高的可扩展性。
    如果当前性能出现瓶颈,只需要继续加大分表数量就能实现性能的接近翻倍。
    如果单个MySQL实例不足以应付这么多表,还可以把不同的表分配到不同的MySQL实例里,只需要编写一段根据查询条件选择数据库连接的代码即可。

    单个MySQL表和单个MySQL实例总有一天会接近性能极限,就算使用MySQL集群,也很快会遇到边际效应递减。所以既然总有一天要分表,为什么不一开始就分?

  • @Ta / 2022-06-27 / /

    @大尨@无名啊,我们再来谈一下扩展的问题。以2的N次方作为分表数量,以后就能较为轻松的扩展。
    比如,最开始2个表,如果未来想变成4个,那只需要先重命名表:

    表0 -> 表0
    表1 -> 表2
    

    然后再创建表1表3,再把表0中的一半数据移到表1表2中的一半数据移到表3,扩展就完成了。扩展工作简单到只需要修改一个变量(表的数量),再执行几行SQL语句。

    这样的扩展可以不断进行下去,如果单个MySQL实例性能不满足需求,就把一半的表扩展到另一个MySQL实例,直到满足性能需求为止。

    当然如果使用的是MySQL集群,还有更简单的选择,直接向集群添加实例即可。这样一来,不同的表就会交由集群中不同的实例来处理,因为各个表相互独立,不会遇到明显的边际效应递减,表的数量增加一倍,性能也会提升近一倍。

  • @Ta / 2022-06-27 / /

    @老虎会游泳,我对@大尨 的B表了解如下:

    1. MySQL背景

    下面很多参考了这篇文章

    1.1 MySQL 一页结构如何?

    preview

    1.2 B+树长啥样?查询一次表长啥样?

    img

    1.3 InnoDB引擎 Compact格式下,一行记录(叶子结点)占多少空间?

    (我没查到 Dynamic 的。。据说和 Compact 很像)

    类型 变长字段长度列表 NULL标志位 记录头信息 rowid 事务ID 回滚指针 列1 列2
    字节数 每个字段占 1~4 B 每个字段占 1 bit,至少 1 B 5 6(若无主键) 6 7

    1.4 一行索引(非叶结点)又占多少呢?

    (我也没查到 非叶子结点 长啥样,逼乎文章说是主键字段1, 主键字段2, ..., 行记录所在页号(4 B)

    1.5 单表最多允许多少行数据?

    img

    1. 1.1可知,假设 页头+页目录+页尾 占用 1KB,剩余15KB可用作存储
    2. 一个非叶结点(存索引的)记录数 X = 15K * 1024B/K / (sum(各主键字段长度) + 4)
    3. 一个叶子结点(存数据的)记录数 Y = 15K * 1024B/K / 1.3所述长度
    4. 单表最多行数:x ^ {z-1} * y

    2. 约定

    2.1 B表各字段占用多少空间?

    字段 id uid type crc wxid appid extend ctime
    实际平均占用字节数 4 4 2 4 20 20 ? 4

    3. B表3层B+树最多能存多少行数据?

    最多能存:x ^ {z-1} * y = {\frac{15 * 1024}{4+2+20+4}}^{3-1}*\frac{15 * 1024}{5+6+7+4+4+2+4+20+20+?+4} 

    @大尨 说 300W 就会性能下降,我假设为层数变高导致,代入上式求得extend长度约为 1266

    (后来想到,随机插入会导致页分裂,假设每页只能用一半,那 extend 实际会更长)

  • @Ta / 2022-06-27 / /
    @老虎会游泳,一个库下面有多少张表这个有限制吗?我没遇到过,评论中好像提到有数量限制的意思,有没有知道具体多少数量参考
  • @Ta / 2022-06-27 / /

    @无名啊,你为什么要考虑除了uid, type, wxid之外的其他字段,它们又不在index(uid, type, wxid)索引里。index(uid, type, wxid)索引里面有且只有这三个字段,而索引的值就直接是该行记录在哪个磁盘文件中的什么位置。从索引读取到位置之后直接去磁盘上提取最终数据就可以了,其他没被索引的数据有多长和这个索引有什么关系?

  • @Ta / 2022-06-27 / /

    @老虎会游泳,嗯,我想清楚了,分表确实能很大程度上缓解“单表记录数过多”问题(每张表记录数变成原来的\frac{1}{10或100或更多}

    上面只是“保证单表B+树层数不高情况下,如何扩容单表行数”角度的思考

    为什么要考虑除了uid, type, wxid之外的其他字段

    原理展示,索引字段/一行数据占用空间越少,单表B+树(固定层数情况下)允许的最大行记录数越多

    其他没被索引的数据有多长和这个索引有什么关系

    没关系

  • @Ta / 2022-06-27 / /

    @大尨,没有限制,但是单机应付大量表肯定会变慢啊(这就是边际效应递减,刚开始分表可以性能翻倍,但是如果查询数量继续提升,继续加表的话,很快单机CPU和磁盘就应付不过来了)。所以,如果是MySQL集群,就得往里面不断加机器,让一部分机器处理一部分表,才能真的变快。如果不是集群,就得不同的数据库实例承载不同的表,这也就是“分库”。

    不过我猜你可能一时半会到不了要分库的程度,现阶段分表可能已经足够了。但是就算到了要分库的时候,也不难实现,只是按需创建数据库连接嘛。

  • @Ta / 2022-06-27 / /

    @老虎会游泳,实际 MySQL 分区使用的多吗?还是手动分表?

  • @Ta / 2022-06-27 / /

    @无名啊,然而B表的主键只是id啊,其他字段都只是单纯的数据,数据的值是多少不会影响记录在磁盘文件中的位置啊,MySQL又不需要根据数据的值对记录进行索引。

    所以,分母加的那一堆都不正确,其实只有一个4而已。只有id的值关系到数据在树中的位置。

  • @Ta / 2022-06-27 / /

    @老虎会游泳,是噢,将index(uid, type, wxid)当作非叶节点了

  • @Ta / 2022-06-27 / /

    @无名啊,非索引字段长度对性能无明显影响的最好证据:

    虎绿林帖子内容的平均长度250.8887字,最大长度925676字。

    数年来,从0楼到568396楼,性能没有明显下降。

    因为只有自增id是主键,568396个几乎连续的id,并不会在树中占用多大的空间。

    Screenshot_20220627_003552.jpg

  • @Ta / 2022-06-27 / /

    @老虎会游泳,重新算一下,

    x ^ {z-1} * y = {\frac{\frac{15 * 1024}{4+4}}{2}}^{3-1}*\frac{15 * 1024}{5+6+7+4+4+2+4+20+20+extend+4} = 3,000,000

    求得 extend = 4642

    15*1024 / (3000000 / ( ((15*1024/8)/2) ** 2 )) - (5+6+7+4+4+2+4+20+20+4)
    

    看来不是所有页恰好裂成两半,和 @大尨 说的 extend 不超过 4096 类似了

  • @Ta / 2022-06-27 / /

    @老虎会游泳

    非索引字段长度对性能无明显影响的最好证据

    我的逻辑是:

    索引字段/数据字段长度小 → 当前表B+树所需层数小 → 单次查询随机读取硬盘次数少 → 快

  • @Ta / 2022-06-27 / /

    @无名啊,然而这个查询根本不走主键啊,它走的是索引index(uid, type, wxid)。索引可以直接告诉你数据在什么地方,为什么还需要额外的随机读取?

    Db::table('ulogs')->where('uid',1)->where('type',1)->where('wxid',$wxid)->first();
    
  • @Ta / 2022-06-27 / /
    @老虎会游泳
    
    CREATE TABLE `app_ulogsxx` (
      `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
      `uid` int UNSIGNED NOT NULL,
      `type` int UNSIGNED NOT NULL,
      `wxid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `appid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `extend` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `ctime` int NOT NULL,
      PRIMARY KEY (`id`),
      KEY `uid` (`uid`,`type`,`appid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    
    
    CREATE TABLE `app_ulogsxx` (
      `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
      `uid` int UNSIGNED NOT NULL,
      `type` int UNSIGNED NOT NULL,
      `wxid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `appid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `extend` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `ctime` int NOT NULL,
       PRIMARY KEY (`id`),
       KEY `uid` (`uid`),
       KEY `type` (`type`),
       KEY `wxid` (`wxid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    
    
    


     这两种方式的设置索引有什么性能的影响的?
    其中:

    KEY `uid` (`uid`,`type`,`appid`)

    跟这个
       KEY `uid` (`uid`),
       KEY `type` (`type`),
       KEY `wxid` (`wxid`)

    什么不一样的地方 ,对对条件查询来说影响大不大
  • @Ta / 2022-06-27 / /

    @老虎会游泳,索引不也是B+树吗?查索引也要随机读取吧

    另外,索引存的是index(uid, type, wxid, 主键id),还是index(uid, type, wxid, 记录所在页号)

  • @Ta / 2022-06-27 / /

    @无名啊,整个查询过程:

    1. 搜索索引index(uid, type, wxid),找到对应查询条件的记录位置。(如果数据量小,它甚至可能直接在内存。)
    2. 现在我们已经知道记录的位置了,直接去磁盘读取就好了。

    既然如此,那就只有索引index(uid, type, wxid)的规模和查询速度有关系。

    此外,主键primary key(id)的规模,以及索引index(uid, type, wxid)的规模两者一起,影响了插入速度(因为插入需要同时更新这两个索引)。

    除此之外,其他数据的长度又要如何对这一切造成影响呢?

添加新回复
回复需要登录