主题
MySQL 数据量瓶颈
B+Tree索引与数据量
MySQL我们已经很熟悉了，我们常常用它做业务数据存储查询以及信息管理的工作。相信你也听过“一张表不要超过2000万行数据”这句话，为什么会有这样的说法呢？
核心在于MySQL数据库的索引，实现上和我们的需求上有些冲突。具体点说，我们对外的服务基本都要求实时处理，在保证高并发查询的同时，还需要在一秒内找出数据并返回给用户，这意味着对数据大小以及数据量的要求都非常高高。
MySQL为了达到这个效果，几乎所有查询都是通过索引去缩小扫描数据的范围，然后再回到表中对范围内数据进行遍历加工、过滤，最终拿到我们的业务需要的数据。
事实上，并不是MySQL不能存储更多的数据，而限制我们的多数是数据查询效率问题。
那么MySQL限制查询效率的地方有哪些？请看下图：
众所周知，MySQL的InnoDB数据库的索引是B+Tree，B+Tree的特点在于只有在最底层才会存储真正的数据ID，通过这个ID就可以提取到数据的具体内容，同时B+Tree索引最底层的数据是按索引字段顺序进行存储的。
通过这种设计方式，我们只需进行1～3次IO（树深度决定了IO次数）就能找到所查范围内排序好的数据，而树形的索引最影响查询效率的是树的深度以及数据量（数据越独特，筛选的数据范围就越少）。
数据量我么很好理解，只要我们的索引字段足够独特，筛选出来的数据量就是可控的。
但是什么会影响到索引树的深度个数呢？这是因为MySQL的索引是使用Page作为单位进行存储的，而每页只能存储16KB（innodb_page_size）数据。如果我们每行数据的索引是1KB，那么除去Page页的一些固定结构占用外，一页只能放16条数据，这导致树的一些分支装不下更多数据时，我么就需要对索引的深度再加一层。
我们从这个Page就可以推导出：索引第一层放16条，树第二层大概能放2万条，树第三层大概能放2400万条，三层的深度B+Tree按主键查找数据每次查询需要3次IO（一层索引在内存，IO两次索引，最后一次是拿数据）。
不过这个2000万并不是绝对的，如果我们的每行数据是0.5KB，那么大概在4000万以后才会出现第四层深度。而对于辅助索引，一页Page能存放1170个索引节点（主键bigint8字节+数据指针6字节），三层深度的辅助索引大概能记录10亿条索引记录。
可以看到，我们的数据存储数量超过三层时，每次数据操作需要更多的IO操作来进行查询，这样做的后果就是查询数据返回的速度变慢。所以，很多互联网系统为了保持服务的高效，会定期整理数据。
通过上面的讲解，相信你已经对整个查询有画面感了：当我们查询时，通过1～3次IO查找辅助索引，从而找到一批数据主键ID。然后，通过MySQL的MMR算法将这些ID做排序，再回表去聚簇索引按取值范围提取在子叶上的业务数据，将这些数据边取边算或一起取出再进行聚合排序后，之后再返回结果。
可以看到，我们常用的数据库之所以快，核心在于索引用得好。由于加工数据光用索引是无法完成的，我们还需要找到具体的数据进行再次加工，才能得到我们业务所需的数据，这也是为什么我们的字段数据长度和数据量会直接影响我们对外服务的响应速度。
同时请你注意，我们一个表不能增加过多的索引，因为索引太多会影响到表插入的性能。并且我们的查询要遵循左前缀原则来逐步缩小查找的数据范围，而不能利用多个CPU并行去查询索引数据。这些大大限制了我们对大数据的处理能力。
另外，如果有数据持续高并发插入数据库会导致MySQL集群工作异常、主库响应缓慢、主从同步延迟加大等问题。从部署结构上来说，MySQL只有主从模式，大批量的数据写操作只能由主库承受，当我们数据写入缓慢时客户端只能等待服务端响应，严重影响数据写入效率。
为什么mysql索引b+树达到四层查询就变慢
- 树的高度增加(次要因素) : 三层到四层差一次IO
- 缓存大小不足(主要因素)（如innodb_buffer_pool_size）: mysql查询先去自己内存的buffer pool里查，buffer pool里面没有才去走索引查询。数据量过大导致超出缓存容量, 导致更多的磁盘访问。
- 页分裂和页合并的影响(主要因素) : 当聚簇索引B+tree的数据更新引发叶子节点分裂时，原page中的数据会被移动至新page，这会导致二级索引gpp_no变为不准确的值，同理，叶子节点发生合并时，也会有pk记录的搬迁，造成命中率下降的问题。越后面的层级索引越靠后，越容易出现这个问题。而且涉及大量的数据移动和重组操作。这些操作会增加数据库的I/O负担和CPU消耗，从而影响数据库的整体性能
分区表的使用
简介
数据库分区是将一个表的数据分布到多个物理存储区域（分区）中，而这些分区在逻辑上仍然是一个表。每个分区可以单独处理，比如进行不同的备份、索引或存储。
数据库分区主要有以下几种类型：
- 范围分区 (Range Partitioning)：根据某个列的值范围来分区。例如，根据日期范围将数据分为不同的分区。
- 列表分区 (List Partitioning)：根据某个列的离散值来分区。例如，按照地区或类别来分区。
- 哈希分区 (Hash Partitioning)：使用哈希函数将数据分配到多个分区，适合于均匀分布数据。
- 键分区 (Key Partitioning)：类似于哈希分区，但使用 MySQL 内置的哈希算法。
注意事项：唯一索引
在 MySQL 数据库中，分区表的索引都是局部，而非全局。也就是说，索引在每个分区文件中都是独立的，所以分区表上的唯一索引必须包含分区列信息，否则创建会报。如果原来ID是主键的话, 现在向对日期字段进行分区, 需要把ID, 日期字段都作为主键。
为什么能提升性能
如果你说要查所有数据, 一步到位当然是最简单也是最快的。但这个快是相对的, 如果数据量上千万后查询数据也需要个几秒钟, 可以想象一下查询业务数据每翻一页等几秒钟的感觉, 也许他就是想知道最近的数据, 但他不知道的是查询时遍历了所有的数据。
分区表的核心在于数据拆分, 拆分后的数据可以独立存储, 并且可以独立索引。这样，当查询时，只需要查询到需要的分区，而不需要遍历整个表，从而大大提高查询效率。
- 减少扫描的数据量
分区表通过将数据拆分为多个分区，查询时只会扫描与查询条件相关的分区，而不会扫描整个表。这样，即使表中的数据量非常庞大，数据库也能避免不必要的全表扫描，从而大幅度降低查询的时间。
- 独立存储和索引
每个分区的数据可以单独存储，并且每个分区可以拥有独立的索引。数据库在查询时会直接访问与查询条件匹配的分区，并利用该分区的索引进行查询，避免了跨分区的操作和全表扫描，进一步提高了查询性能。
- 查询效率提高
对于基于分区键的查询，分区表能显著提升效率。例如，按日期范围查询最新数据时，数据库只需查询相关日期范围的分区，而不是整个表。由于分区裁剪（Partition Pruning）机制，数据库能够智能地过滤掉不相关的分区，只扫描需要的数据。
- 提高数据维护效率
分区表使得对大数据表的管理更加灵活。例如，可以按日期、范围或哈希等方式对数据进行分区。在需要删除、归档或清理旧数据时，分区表可以通过删除或移动整个分区来快速完成操作，而不需要对整张表进行操作，减少了数据库的负担。
- 并行查询
分区表还可以利用数据库的并行查询功能。在某些情况下，数据库可以在多个分区上并行执行查询，从而提升查询速度。尤其是在大数据量和复杂查询的场景下，分区表能有效降低查询的响应时间。
- 优化数据分布和负载均衡
通过合理的分区策略，数据可以在不同的磁盘和存储节点上进行分布，达到负载均衡的效果。当某个分区的数据量过大时，可以通过重新分区或调整分区策略来避免热点数据，提升系统的稳定性和可扩展性。
- 提高数据插入效率
在许多业务场景中，特别是订单、支付等流水数据，都涉及强时效性的数据处理。为了应对高并发和大数据量的挑战，可以通过对时间字段进行分区来优化性能。例如，将数据按月分区，或者在单月数据量接近千万时，将一个月的数据拆分为多个分区。这样做有助于提高查询效率和存储管理能力。
实践
在很多业务中都是有强时效性的数据，例如订单、支付等流水数据。这是可以对时间字段进行分区, 划分为一个月一个区或者一个月两个区(如果单月数据依然接近千万)。
查询时
根据时间字段进行分区，查询时只需要访问特定的分区。例如，查询"最近一个月"或"从月初到当前时间"的数据时，只会命中1到3个分区。这样，查询的数据范围就固定在这几个分区内，避免了全表扫描，显著提高了查询效率。尤其是在高频查询的情况下，数据库通常会将这些频繁访问的分区数据缓存到内存中，从而加快查询响应时间。
存储时
通过对时间字段进行分区，插入数据时会根据时间自动分配到相应的分区，这样数据就按时间顺序存储在分区内。分区内的数据自然是按时间顺序排列的，这不仅提升了数据插入效率，还减少了数据重排的开销，进一步优化了存储管理。
在分区的加持下, 逻辑上的“单表”可以支持亿级数据的存储, 因为并不是MySQL不能存储更多的数据，而限制我们的多数是数据查询效率问题。