场景当我们业务数据库表中的数据越来越多如果你也和我遇到了以下类似场景那让我们一起来解决这个问题数据的插入,查询时长较长后续业务需求的扩展 在表中新增字段 影响较大表中的数据并不是所有的都为有效数据 需求只查询时间区间内的评估表数据体量我们可以从表容量/磁盘空间/实例容量三方面评估数据体量接下来让我们分别展开来看看表容量表容量主要从表的记录数、平均长度、增长量、读写量、总大小量进行评估。一般对于OLTP的表建议单表不要超过2000W行数据量总大小15G以内。访问量单表读写量在1600/s以内查询行数据的方式我们一般查询表数据有多少数据时用到的经典sql语句如下select count(*) from table select count(1) from table但是当数据量过大的时候这样的查询就可能会超时所以我们要换一种查询方式use 库名 show table status like 表名 ; 或show table status like 表名\G ;上述方法不仅可以查询表的数据还可以输出表的详细信息 , 加\G可以格式化输出。包括表名 存储引擎 版本 行数 每行的字节数等等大家可以自行试一下哈磁盘空间查看指定数据库容量大小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 order by data_length desc, index_length desc;查询单个库中所有表磁盘占用大小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_schemamysql order by data_length desc, index_length desc;查询出的结果如下图片建议数据量占磁盘使用率的70%以内。同时对于一些数据增长较快可以考虑使用大的慢盘进行数据归档归档可以参考方案三实例容量MySQL是基于线程的服务模型因此在一些并发较高的场景下单实例并不能充分利用服务器的CPU资源吞吐量反而会卡在mysql层可以根据业务考虑自己的实例模式出现问题的原因上面我们已经查到我们数据表的体量了 那么为什么单表数据量越大 业务的执行效率就越慢 根本原因是什么呢一个表的数据量达到好几千万或者上亿时加索引的效果没那么明显啦。性能之所以会变差是因为维护索引的B树结构层级变得更高了查询一条数据时需要经历的磁盘IO变多因此查询性能变慢。❝大家是否还记得一个B树大概可以存放多少数据量呢❞InnoDB存储引擎最小储存单元是页一页大小就是16k。B树叶子存的是数据内部节点存的是键值指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中进而再去数据页中找到需要的数据图片假设B树的高度为2的话即有一个根结点和若干个叶子结点。这棵B树的存放总记录数为根结点指针数*单个叶子节点记录行数。如果一行记录的数据大小为1k那么单个叶子节点可以存的记录数 16k/1k 16.非叶子节点内存放多少指针呢我们假设主键ID为bigint类型长度为8字节(面试官问你int类型一个int就是32位4字节)而指针大小在InnoDB源码中设置为6字节所以就是8614字节16k/14B 16*1024B/14B 1170因此一棵高度为2的B树能存放1170 * 1618720条这样的数据记录。同理一棵高度为3的B树能存放1170 *1170 *16 21902400也就是说可以存放两千万左右的记录。B树高度一般为1-3层已经满足千万级别的数据存储。如果B树想存储更多的数据那树结构层级就会更高查询一条数据时需要经历的磁盘IO变多因此查询性能变慢。如何解决单表数据量太大查询变慢的问题知道了根本原因之后我们就需要考虑如何优化数据库来解决问题了这里提供了三种解决方案包括数据表分区分库分表冷热数据归档 了解完这些方案之后大家可以选取适合自己业务的方案方案一数据表分区我们首先看一下分区有什么优缺点表分区有什么好处与单个磁盘或文件系统分区相比可以存储更多的数据。对于那些已经失去保存意义的数据通常可以通过删除与那些数据有关的分区很容易地删除那些数据。相反地在某些情况下添加新数据的过程又可以通过为那些新数据专门增加一个新的分区来很方便地实现。一些查询可以得到极大的优化这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改所以在第一次配置分区方案时还不曾这么做时可以重新组织数据来提高那些常用查询的效率。涉及到例如SUM()和COUNT()这样聚合函数的查询可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id”。通过“并行”这意味着该查询可以在每个分区上同时进行最终结果只需通过总计所有分区得到的结果。通过跨多个磁盘来分散数据查询来获得更大的查询吞吐量。表分区的限制因素一个表最多只能有1024个分区。MySQL5.1中分区表达式必须是整数或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。如果分区字段中有主键或者唯一索引的列那么多有主键列和唯一索引列都必须包含进来。即分区字段要么不包含主键或者索引列要么包含全部主键和索引列。分区表中无法使用外键约束。MySQL的分区适用于一个表的所有数据和索引不能只对表数据分区而不对索引分区也不能只对索引分区而不对表分区也不能只对表的一部分数据分区。在进行分区之前可以用如下方法 看下数据库表是否支持分区哈mysql show variables like %partition%; -------------------------- | Variable_name | Value | -------------------------- | have_partitioning | YES | -------------------------- 1 row in set (0.00 sec)方案二数据库分表为什么要分表分表后显而易见单表数据量降低树的高度变低查询经历的磁盘io变少则可以提高效率 mysql 分表分为两种 水平分表和垂直分表分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题将原来独立的数据库拆分成若干数据库组成 将数据大表拆分成若干数据表组成使得单一数据库、单一数据表的数据量变小从而达到提升数据库性能的目的。水平分表定义数据表行的拆分通俗点就是把数据按照某些规则拆分成多张表或者多个库来存放。分为库内分表和分库。比如一个表有4000万数据查询很慢可以分到四个表每个表有1000万数据图片垂直分表定义列的拆分根据表之间的相关性进行拆分。常见的就是一个表把不常用的字段和常用的字段就行拆分然后利用主键关联。或者一个数据库里面有订单表和用户表数据量都很大进行垂直拆分用户库存用户表的数据订单库存订单表的数据图片缺点垂直分隔的缺点比较明显数据不在一张表中会增加join 或 union之类的操作知道了两个知识后我们来看一下分库分表的方案1.取模方案拆分之前先预估一下数据量。比如用户表有4000w数据现在要把这些数据分到4个表user1 user2 uesr3 user4。比如id 1717对4取模为1加上 所以这条数据存到user2表。❝注意进行水平拆分后的表要去掉auto_increment自增长。这时候的id可以用一个id 自增长临时表获得或者使用redis incr的方法。❞图片优点数据均匀的分到各个表中出现热点问题的概率很低。缺点以后的数据扩容迁移比较困难难当数据量变大之后以前分到4个表现在要分到8个表取模的值就变了需要重新进行数据迁移。2.range 范围方案以范围进行拆分数据就是在某个范围内的订单存放到某个表中。比如id12存放到user1表id1300万的存放到user2 表。图片优点有利于将来对数据的扩容缺点如果热点数据都存在一个表中则压力都在一个表中其他表没有压力。❝我们看到以上两种方案 都存在缺点 但是却又是互补的那么我们将这两个方案结合会怎样呢❞3.hash取模和range方案结合如下图 我们可以看到 group 组存放id 为0~4000万的数据然后有三个数据库 DB0 DB1 DB2DB0里面有四个数据库DB1 和DB2 有三个数据库假如id为15000 然后对10取模为啥对10 取模 因为有10个表取0 然后 落在DB_0,然后在根据range 范围落在Table_0里面。总结采用hash取模和range方案结合 既可以避免热点数据的问题也有利于将来对数据的扩容我们已经了解了 mysql分区和分表的知识 那我们看一下这两个技术有何不同以及适用场景分区分表的区别1、实现方式上mysql的分表是真正的分表一张表分成很多表后每一个小表都是完整的一张表都对应三个文件一个.MYD数据文件.MYI索引文件.frm表结构分区不一样一张大表进行分区后他还是一张表不会变成二张表但是他存放数据的区块变多了。2、提高性能上分表重点是存取数据时如何提高mysql并发能力上而分区呢如何突破磁盘的读写能力从而达到提高mysql性能的目的。3、实现的难易度上1、分表的方法有很多用merge来分表是最简单的一种方式。这种方式根分区难易度差不多并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。2、分区实现是比较简单的建立分区表根建平常的表没什么区别并且对开代码端来说是透明的分区分表的联系1、都能提高mysql的性高在高并发状态下都有一个良好的表现。2、分表和分区不矛盾可以相互配合的对于那些大访问量并且表数据比较多的表我们可以采取分表和分区结合的方式访问量不大但是表数据很多的表我们可以采取分区的方式等。分库分表存在的问题1、事务问题在执行分库分表之后由于数据存储到了不同的库上数据库事务管理出现了困难。如果依赖数据库本身的分布式事务管理功能去执行事务将付出高昂的性能代价如果由应用程序去协助控制形成程序逻辑上的事务又会造成编程方面的负担。2、跨库跨表的join问题在执行了分库分表之后难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上这时表的关联操作将受到限制我们无法join位于不同分库的表也无法join分表粒度不同的表结果原本一次查询能够完成的业务可能需要多次查询才能完成。3、额外的数据管理负担和数据运算压力额外的数据管理负担最显而易见的就是数据的定位问题和数据的增删改查的重复执行问题这些都可以通过应用程序解决但必然引起额外的逻辑运算。例如对于一个记录用户成绩的用户数据表userTable业务要求查出成绩最好的100位在进行分表之前只需一个order by语句就可以搞定但是在进行分表之后将需要n个order by语句分别查出每一个分表的前100名用户数据然后再对这些数据进行合并计算才能得出结果。方案三冷热归档为什么要冷热归档其实原因和方案二类似都是降低单表数据量树的高度变低查询经历的磁盘io变少则可以提高效率 如果大家的业务数据有明显的冷热区分比如只需要展示近一周或一个月的数据。那么这种情况这一周喝一个月的数据我们称之为热数据其余数据为冷数据。那么我们可以将冷数据归档在其他的库表中提高我们热数据的操作效率。接下来讲一下归档的过程创建归档表 创建的归档表 原则上要与原表保持一致归档表数据的初始化图片业务增量数据处理过程图片数据的获取过程图片以上三种方案我们如何选型