61 .

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

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

(无名啊/@Ta/2022-06-26 23:31/样/源)

62 .

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

(无名啊/@Ta/2022-06-26 23:33/样/源)

63 .

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

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

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

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

(老虎会游泳/@Ta/2022-06-26 23:37/样/源)

64 .

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

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

(老虎会游泳/@Ta/2022-06-26 23:47/样/源)

65 .

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

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

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

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

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

(老虎会游泳/@Ta/2022-06-27 00:02/样/源)

66 .

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

1. MySQL背景

下面很多参考了这篇文章

1.1 MySQL 一页结构如何？

preview

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

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 单表最多允许多少行数据？

由1.1可知，假设页头+页目录+页尾占用 1KB，剩余15KB可用作存储
一个非叶结点（存索引的）记录数 X = 15K * 1024B/K / (sum(各主键字段长度) + 4)
一个叶子结点（存数据的）记录数 Y = 15K * 1024B/K / 1.3所述长度
单表最多行数：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 00:05/样/源)

67 . @老虎会游泳，一个库下面有多少张表这个有限制吗？我没遇到过，评论中好像提到有数量限制的意思，有没有知道具体多少数量参考

(大尨/@Ta/2022-06-27 00:13/样/源)

68 .

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

(老虎会游泳/@Ta/2022-06-27 00:14/样/源)

69 .

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

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

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

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

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

没关系

(无名啊/@Ta/2022-06-27 00:18/样/源)

70 .

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

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

(老虎会游泳/@Ta/2022-06-27 00:18/样/源)

71 .

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

(无名啊/@Ta/2022-06-27 00:29/样/源)

72 .

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

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

(老虎会游泳/@Ta/2022-06-27 00:32/样/源)

73 .

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

(无名啊/@Ta/2022-06-27 00:35/样/源)

74 .

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

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

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

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

(老虎会游泳/@Ta/2022-06-27 00:44/样/源)

75 .

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

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 00:45/样/源)

76 .

@老虎会游泳，

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

我的逻辑是：

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

(无名啊/@Ta/2022-06-27 00:47/样/源)

77 .

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

Db::table('ulogs')->where('uid',1)->where('type',1)->where('wxid',$wxid)->first();

(老虎会游泳/@Ta/2022-06-27 00:49/样/源)

78 . @老虎会游泳，


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 00:53/样/源)

79 .

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

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

(无名啊/@Ta/2022-06-27 00:53/样/源)

80 .

@无名啊，整个查询过程：

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

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

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

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

(老虎会游泳/@Ta/2022-06-27 00:55/样/源)