Mysql某个表有近千万数据，CRUD比较慢，如何优化

数据千万级别之多，占用的存储空间也比较大，可想而知它不会存储在一块连续的物理空间上，而是链式存储在多个碎片的物理空间上。可能对于长字符串的比较，就用更多的时间查找与比较，这就导致用更多的时间。

可以做表拆分，减少单表字段数量，优化表结构。

在保证主键有效的情况下，检查主键索引的字段顺序，使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致。

主要两种拆分 垂直拆分，水平拆分。

垂直分表

也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的， 数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。 一般是针对 那种 几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。

垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Product一个库，订单Order一个库。 切分后，要放在多个服务器上，而不是一个服务器上。为什么？ 我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前， 全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上， 随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。 所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。

数据库业务层面的拆分，和服务的“治理”，“降级”机制类似，也能对不同业务的数据分别的进行管理，维护，监控，扩展等。 数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。 数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

水平分表

针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库 *** 作还是有IO瓶颈。不建议采用。

水平分库分表

将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。

水平分库分表切分规则

1 RANGE

从0到10000一个表，10001到20000一个表；

2 HASH取模

一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。

3 地理区域

比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。

4 时间

按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

分库分表后面临的问题

事务支持

分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

跨库join

只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

跨节点的count,order by,group by以及聚合函数问题

这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

数据迁移，容量规划，扩容等问题

来自淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度。

ID问题

一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由

一些常见的主键生成策略

UUID

使用UUID作主键是最简单的方案，但是缺点也是非常明显的。由于UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。

Twitter的分布式自增ID算法Snowflake

在分布式系统中，需要生成全局UID的场合还是比较多的，twitter的snowflake解决了这种需求，实现也还是很简单的，除去配置信息，核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。

跨分片的排序分页

一般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。

51CTOcom快译 众所周知，存储阵列需要巨大的存储容量和高速的网络连接，并在数据中心中扮演着重要的角色。尽管云存储越来越受欢迎，但存储阵列(尤其是全闪存阵列)是许多企业存储基础设施的重要组成部分。而顶级的存储阵列可以提供广泛的数据存储，并允许用户将关键业务工作负载存储到更能支持他们开展业务的位置。

存储阵列可以在两个或多个存储设备上保存块存储、文件存储或对象存储数据。这些设备还可以连接到网络，而存储阵列由控制器管理。

存储区域网络(SAN)连接数据中心或其他本地区域中的多个存储设备，其中包括存储阵列。存储区域网络(SAN)阵列在存储行业中的地位仍在上升，尤其是那些具有高速连接(例如光纤通道)并支持NVMe的阵列。存储区域网络(SAN)可以满足低延迟连接数据中心的需求，并在互联网中连接数据存储。

独立磁盘冗余阵列(RAID)是一种用于HDD磁盘和SSD磁盘的冗余和备份技术。RAID使用几种不同的方法来复制或保留数据，其中包括镜像(将数据准确复制到存储阵列中的下一个磁盘驱动器)和奇偶校验(重新计算丢失数据的一种数学方法)。

最常见的RAID级别是：

一些存储专业人士不再将RAID视为一种可靠的备份或保护技术，因为它容错率低，尤其是在具有更多磁盘的阵列中。RAID 5和RAID 6是具有最佳保护的级别，无法满足当前数据中心环境中理想的备份需求。

NVMe(非易失性存储器快速)是一种SSD技术，它创建与计算机中央处理单元的直接连接。通过绕过SATA使用的控制器并连接到PCIe总线，可以更快、更高效地处理数据。NVMe的速度远远超过其他SSD技术(例如SATA)。

用于数据中心的NVMe-oF使存储的数据可以应用在网络，而不是只在一台计算机或服务器上可用。这对于需要在数据中心内部提供存储数据而不是只是某个硬件上使用的企业来说特别有用。提供NVMe-oF技术的存储阵列仍然很少见;NVMe-oF技术更大程度地利用了NVMe更高的数据处理速率。

数据存储阵列在大小、硬盘驱动器支持以及专业化方面各不相同。有一些支持HDD磁盘，而另一些只支持闪存。以下的大多数存储阵列都将采用闪存存储，这突出了闪存在未来关键工作负载的数据存储中的重要性。

在企业选择存储阵列时，需要考虑以下问题：

以下一些存储阵列是来自五个供应商的存储解决方案。这些包括NAS、全闪存和非结构化数据的首选方案。这个列表中的某些条目涵盖来自同一供应商的多个类似解决方案。

FlashArray适用于需要最佳速度和最高质量的企业。

FlashArray包括用于关键企业工作负载的FlashArray//X和用于非密集型工作负载的FlashArray//C，它提供了令人难以置信的性能，并与其他主要的供应商竞争(该产品2011年推出)。用户可以通过托管目录监控闪存阵列性能，可以选择单个文件系统根目录、每个用户的目录或每个业务部门的目录。

FlashArray为数据库提供快速备份和 *** 作，为具有大量SQL和Oracle数据库需求的企业提供支持。其升级通常不会导致停机，更新也不需要Pure Storage用户进行大量IT管理。而用户也对Pure Storage团队的支持感到满意。虽然FlashArray并不是Hyper-V环境的一个完美解决方案，但很多用户发现在他们的虚拟机上表现良好。

Pure Storage公司在存储行业意识到全闪存系统的重要性之前就推出了全闪存系统，现在他们从中受益匪浅。FlashArray是存储市场上的顶级阵列之一，在存储速度和用户支持方面领先于其他供应商的产品。

由于其极快的速度，FlashArray并不是冷数据或存档数据的理想选择，而是需要极低延迟的工作负载的理想选择。快速访问存储通常比归档存储的成本要昂贵得多，而FlashArray作为冷存储解决方案将会浪费企业的预算。

NetApp AFF适用于需要同时存储冷热数据的用户。

NetApp All-Flash FAS是用于关键工作负载的全闪存存储区域网络(AFF)。AFF相对容易实现，可以处理多个大型工作负载，尤其是数据库、高性能应用程序和虚拟机，同时保持高速存储。

NetApp AFF支持iSCSI和光纤通道网络以及通过光纤通道连接的NVMe。AFF可以为数据备份创建快照。Snap Mirror是一种数据复制和灾难恢复技术，可在灾难破坏初始副本的时候创建数据的异地复制。

AFF的主要优势之一是其使用Fabric Pool技术，NetApp阵列会自动将非活动数据发送到成本较低的对象存储。分层取决于数据的状态(冷数据或热数据)。如果不需要定期的低延迟访问，Fabric Pool可以通过将数据传输到成本更低的存储平台来节省成本。Fabric Pool支持Microsoft Azure Blob、阿里云和IBMCloud等对象存储平台。

HPE Nimble适用于需要内置智能的企业。

HPE公司的全闪存阵列是在2017年HPE公司收购存储提供商Nimble公司时收购的，可以提供可扩展的混合云存储。Nimble公司使用HPE公司的dHCI(分解的超融合基础设施)。dHCI并不是一种完全融合或超融合的基础设施，它允许用户在需要时扩展他们想要的资源(例如存储、计算或网络)。

用户还可以利用智能预测平台HPE InfoSight，该平台会在出现问题、应用程序出现故障或阵列需要扩展以满足需求时通知用户。HPE InfoSight直接连接到dHCI堆栈。

Nimble提供灾难恢复复制快照，包括针对Hyper-V虚拟机的快照。而复制快照可以扩展到其他物理位置的存储阵列。

尽管与Nimble公司相比，一些用户对HPE公司的支持可用性有所不满，但表示HPE公司可以为新用户提供培训和支持。

FlashSystem是IBM公司的全闪存阵列，通过Red Hat和Kubernetes容器存储接口支持容器环境。如果初始硬件出现故障，FlashSystem用户可以使用IBM HyperSwap进行故障转移。

FlashSystem 5200是最新的存储阵列之一，提供NVMe全闪存和超过PB的可用存储容量。FlashSystem还包括IBM公司的新CloudSatellite，它允许用户灵活地管理和部署云计算环境以用于他们的存储。CloudSatellite还兼容各种供应商提供的云平台，以便用户可以选择他们需要的公有云、私有云、内部部署或混合部署环境。

IBM公司提供了有关性能和容量的Storage Insights，用户可以通过管理平台进行管理。Storage Insights还提供智能分析，可以确定问题和优化领域。

FlashSystem最有前途的一个功能可能是其利用NVMe over Fabrics的能力。虽然是一项新的数据中心技术，但NVMe-oF非常具有前途：它将NVMe闪存速度(当今可用的最高持久内存速度)扩展到整个数据中心。这些存储不仅限于一台计算机或设备使用，还可以通过光纤通道或InfiniBand等技术跨整个网络访问。包括NVMe-oF功能是主要存储阵列供应商的一个具有先见之明的决定，它是FlashSystem的突出元素之一。

Synology DiskStation和FlashStation适用于大量使用NAS的企业。

Synology公司是网络附加存储领域的佼佼者。对于小型企业来说，DiskStation系列NAS设备提供可靠性、容量和DiskStation Manager软件，该软件为所有SynologyDS设备提供一种 *** 作系统。许多DiskStation设备还具有NVMe端口，但并非所有企业级NAS硬件都有这样的端口。RX、RS和DX系列也面向中小型企业。

Synology FlashStation(FS)专供企业使用，拥有全闪存的24托盘阵列。Synology还提供扩展单元，例如24托盘Fx2421可以用于通过FlashStation扩展存储。

FlashStation FS6400是Synology公司推出的最新阵列之一，其备份和数据保护功能尤其引人注目。DiskStation Manager提供了对虚拟化的支持，提供用于运行虚拟机和创建备份快照的虚拟机管理器。

FS6400运行iSCSI协议，还支持虚拟环境，如VMWareVSphere和MicrosoftHyper-V。虽然它不提供用于速度更快SSD连接的NVME端口，但它确实有两个千兆以太网端口。对于仍然依赖网络附加存储和SATASSD(仍然是一种低成本、低延迟的选择)的中型企业和企业来说，Synology FlashStation是一种理想的选择。

Dell EMC PowerScale适用于希望将非结构化数据存储在网络附加存储(NAS)中的企业。

PowerScale是戴尔公司最新推出的网络附加存储(NAS)解决方案之一。该阵列将数据存储在一个巨大的数据湖中，旨在通过将所有数据分组到一个地方来减少或消除企业的数据孤岛。

非结构化数据(尤其是对象存储数据)的数量和流行度都在飙升，PowerScale为正在成为大多数业务数据的数据提供存储。用户可以通过简单地添加更多节点来扩展，这样不会降低速度或性能。PowerScale适用于云平台和内部部署设施运行的工作负载。

PowerScale的成本很高昂，就像这一列表中的许多其他解决方案一样，并不是块存储的理想选择。然而，在需要时轻松扩展的能力使其成为需要灵活NAS和增长空间的企业的解决方案。

由于可以容纳大量的非结构化数据，PowerScale是存储大型媒体文件的合适选择。

Pure Storage FlashBlade 适用于具有最高速度和勒索软件保护的本地存储。

Pure Storage公司再次出现的理由很充分：其相对较早的全闪存数据中心存储方法产生了多种出色的产品。FlashBlade与FlashArray的方法不同，它是一种存储解决方案，旨在将公共云级别的功能引入本地存储。FlashBlade可创建易于扩展的存储(如果想要增加存储容量，用户只需添加更多FlashBlade即可)。

FlashBlade旨在存储文件和对象数据，这是数据中心优先考虑对象存储数据的重要一步。对象存储为构成企业数据的很大一部分的非结构化数据提供了无限的存储空间。通过提供对象存储阵列解决方案，Pure Storage公司改进了其产品。

FlashBlade提供文件和对象复制以及快速恢复，这是一个与数据保护供应商集成的程序。用户可以在FlashBlade中获取数据快照，并使用快照执行备份，这是一种旨在防止勒索软件攻击的策略(网络攻击者不能使用快照来索要赎金)。

原文标题：Best Storage and Disk Arrays 2021，作者：Jenna Phipps

51CTO译稿，合作站点转载请注明原文译者和出处为51CTOcom

页是 InnoDB 管理存储空间的最小单位。一个页的大小一般是 16 KB。InnoDB 有许多种页用于不同的作用。其中数据页则是用于存储数据。数据页存储的内容为:

其中 Infimum + supremum 以及 User Records 为页中存储数据的部分。其中 Infimum 表示页中的最小记录，而 supremum 表示页中的最大记录。这两个记录不存储实际的值，而仅仅表示开头以及结尾。User Records 部分按行存储数据。User Records 中的每一条记录格式为:

插入到页中的记录是按主键大小进行排序。利用其中的 next_record 可以查找到下一条记录。在不考虑索引的情况下，如果我们要寻找其中的某条记录可以通过遍历链表的方式进行查找。但是如果当页中的数据过多，o(n) 的时间复杂度明显不满足快速查找的需求。因此 InnoDB 在页中设计了页目录。页目录中有多个槽，其规则如下:

因此实际搜索时，可以利用槽进行二分搜索，将算法复杂度降到了 。这个结构有点类似于一个两层的跳跃表。

由于一个页中实际能存储的数据有限，因此记录会被分配到多个页进行存储。页与页之间有着双向链表的结构。

在 innodb 中使用 B+ 树作为索引。实际上索引在 mysql 中也是作为页进行管理的。例如:

索引页与数据页类似，只是索引页中一条记录只存在两列。分别是页对应的最我号，以及页的页编号。当然，一个 b+ 树肯定存在多个级别，因此实际上的存存储格式为:

这里可以看出索引页与数据页其实并没有太多的区别。只不过数据页中存储着真实的数据，而索引页只存储索引。这里也可以看出主键索引实际上是聚集索引，当查找到最终的数据页时是可以直接获得数据。

许多个页组成的空间之为页空间。每个表空间对应着一个真实的文件 表名ibd。每一个独立表空间中又会分为多个区。每一个区实际上是 64 个连续的页组成。每256个区划又会分为一组。

为什么会提出区的概念呢？原因是查找数据的时候，在页与页之间会通过双向链表进行查找。如果两个页随机分配物理地址，则其之间的物理位置可能非常远。那么在查找的时候无疑会形成大量的随机 IO。降低磁盘的性能。因此，当表中数据过大的时候，以区为单位进行分配连续的磁盘空间，可以减少随机 IO 的数量。

表空间中还有段的概念，当我们利用索引进行查询的时候。很多时候实际上是利用 B+ 树的叶子节点进行范围扫描。但是如果将索引页和数据页都存放在一个区中，那么数据页不一定是连续的磁盘空间。因此当进行范围扫描的时候又会存在随机 IO 的情况。因此索引页和数据页实际上是存放在不同的区中。存放索引页的区的集合又成为一个段，当然非索引页存放的区的集合则为另一个段。

我们知道，磁盘的速度是远远小于内存的速度。因此 InnoDB 会将查询的页缓存在内存 Buffer Pool 中，以免每一次请求都从磁盘中获取，加快查询速度。当然，内存不可能无止尽的使用。因此 InnoDB维护了一个 free 链表。 free 链表指向 Buffer Pool 中可用的部分。

当页面进行修改之后，缓存的中的页页不会马上落盘，这样的页称为脏页。InnoDB 维护了一个 flush 链表指向了脏页。当 buffer 的空间不足时，InnoDB 会进行刷页 *** 作，将脏页写入到磁盘中，腾出内存空间供新的页缓存使用。

一般来说，数据有冷热之分。如果经常刷新热点数据到磁盘中，肯定不划算。因为热点数据经常被查询修改，当写入到磁盘中后又会很快读入到缓存中，做了很多无用功。因此 InnoDB 采用了 LRU 算法统计哪些是热点数据，哪些是非热点数据。每次刷盘时从首先 LRU 链表的尾部将热点数据刷入到磁盘中。

InnoDB 并不是采用最简单的链表，而是划分区域的链表。其设计的原因是，InnoDB 在某些时候会采取预读的 *** 作，将一个区的数据全部读入到内存中。这些数据就会出现在 LRU 链表的头部。如果这些预读的数据最终不能被查询，那么真正的热点数据反而被挤到了链表的尾部，这样一旦存在预读行为 LRU 链表的功能就丧失了。同样，当用户进行扫描全表的 *** 作时，大量的页也会被加载到缓存中将 Buffer 占满。因此 InnoDB 将 LRU 分为两个区域-热数据(young 区)以及冷数据(old 区)。

对于第一种情况，当页被缓存到 Buffer 时首先会被放在 old 区。如果该页后续被继续访问，则会被放到 young 区中。而如果该页后续没有被继续访问到，则会逐渐移动到 old 区尾部。

对于扫描全表的情况，扫描全表有一个特点。即页中的每一条数据都会被访问到，同一个页第一次访问到最后一次访问的间隔时间一定很短。因此 InnoDB 设计了一个策略，如果当一个页加载到内存中，并且该页在第一此访问与最后一次访问间隔相差小于 1s (默认值)，则该页就不会被加入到 young 区中。因此这种方式可以避免全表扫描时对 LRU 链表的污染。

以上就是关于Mysql某个表有近千万数据，CRUD比较慢，如何优化全部的内容，包括:Mysql某个表有近千万数据，CRUD比较慢，如何优化、2021年值得关注的存储和磁盘阵列、MySQL - 页等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！