MySQL分库分表杂谈

这篇文章：https://blog.csdn.net/ClearLoveQ/article/details/101382536  介绍了Mysql的水平分表技术

接下来我们来看几篇文章来更深入地了解分表带来的问题和初步解决办法，但从我个人看来，所有这些都要结合具体的数据来看，如果你写了一个9个表关联的贼复杂查询，分10000个表也没用，技术服务于业务，技术不能滥用

文章1：转载自：https://blog.csdn.net/u013043762/article/details/79912632
有遇到过这种需求，想法： 
1.有损服务，只给他查一年内的数据，或者只存1kw条数据。建一个表存一年内的数据，每隔一个月把表最旧的数据迁到分表上面。如果需求方要查所有数据，让他自己选年份去查。

2.，根据前端传来的页面数请求（即limit，start），确定需要查询的数据在哪一个年份，或者数据是多个年份组合出来。 
假如 
2012 25,2013 40,2014 15 ，共 80条 
limit 0,20 ＝>落到2012年，那么只需查2012就够了; 
limit 20,20 =>2012 后5条 +2013 15条 以此类推。。 
如果再折腾一下，可以以关键字+年份为key，把非当前年份的条数存个cache，减少计算次数 
3.最后是无脑union了，应该会很慢 
4.如果是针对特定的关键字做报表统计，一次性的那就随意了

果断的选了1，因为老数据基本是没什么人关心的了。。

 

文章2：MySQL优化分库分表，为什么要分表，分表以后如何进行排序查询，业务如何设计？

转载自：https://www.cnblogs.com/aij-ling/p/10000259.html

昨天面试新人的时候，遇到了这么一个问题，按照自己的想法大体聊了一些，但大多是感性的，并没有完整的了解why and how.

今天查了一些相关的资料，包括《MySQL性能调优与架构设计》、《高性能Mysql》，慢慢的整体理解，请大家指正。

之一，为什么要分表？

分表，按形式，有水平分表和主附分表。
水平分表常见于按ID取模或者按日期将相同表结构的内容散列到不同的表上，主附分表常见于有对应关系的多张表，通过主外键进行关联。

1，解决磁盘系统最大文件限制

如大家所知，各文件系统对单个文件大小的限制是不一样的。
虽然在现代网站架构设计中，这个问题基本上不用考虑，但在面试时，如果能回答上来这点，可以让体现出较不错的计算机基础功底。
FAT16(最大分区2GB,最大文件2GB ,最大容量？)
FAT32(最大分区32GB，最大容量2TB，最大文件32G)
NTFS(最大分区2TB，最大容量，最大文件2TB)
ext3(最大文件大小: 2TB，最大文件极限: 仅受文件系统大小限制，最大分区/文件系统大小: 4TB，最大文件名长度: 255 字符)

2，减少增量数据写入时的锁对查询的影响，减少长时间查询造成的表锁，影响写入操作等锁竞争的情况
数据越来越多，查询逻辑比较复杂的情况下，可能造成表锁和行锁，会影响到正常的读写需求，如果把数据进行分表，典型的情况有按ID和日期进行分表，读取旧数据和写入新数据的操作在不同的表里，这样就可以避免单张表间产生的锁竞争，节省排队的时间开支，增加呑吐量。
需要注意的是，这里说的减少锁和排队的时间开支，并没有确切的说减少了cpu的运算开支，因为分表以后的运算需求还可能在同一台设备上。如果使用了分库分表则能取得更好的运算速度提升。

3，同2，由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短。

 文章3：分库分表存在的问题

转载自：https://blog.csdn.net/u012129558/article/details/51966444

分库分表存在的问题。
1 事务问题。
在执行分库分表之后，由于数据存储到了不同的库上，数据库事务管理出现了困难。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。
2 跨库跨表的join问题。
在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。
3 额外的数据管理负担和数据运算压力。
额外的数据管理负担，最显而易见的就是数据的定位问题和数据的增删改查的重复执行问题，这些都可以通过应用程序解决，但必然引起额外的逻辑运算，例如，对于一个记录用户成绩的用户数据表userTable，业务要求查出成绩最好的100位，在进行分表之前，只需一个order by语句就可以搞定，但是在进行分表之后，将需要n个order by语句，分别查出每一个分表的前100名用户数据，然后再对这些数据进行合并计算，才能得出结果。

分库分表存在的问题解决方案。

1.事务问题：
解决事务问题目前有两种可行的方案：分布式事务和通过应用程序与数据库共同控制实现事务下面对两套方案进行一个简单的对比。
方案一：使用分布式事务
    优点：交由数据库管理，简单有效
    缺点：性能代价高，特别是shard越来越多时
方案二：由应用程序和数据库共同控制
     原理：将一个跨多个数据库的分布式事务分拆成多个仅处
           于单个数据库上面的小事务，并通过应用程序来总控
           各个小事务。
     优点：性能上有优势
     缺点：需要应用程序在事务控制上做灵活设计。如果使用   
           了spring的事务管理，改动起来会面临一定的困难。
2.跨节点Join的问题
      只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

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

一些常见的主键生成策略

一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由。目前几种可行的主键生成策略有：
1. UUID：使用UUID作主键是最简单的方案，但是缺点也是非常明显的。由于UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。
2. 结合数据库维护一个Sequence表：此方案的思路也很简单，在数据库中建立一个Sequence表，表的结构类似于：
 CREATE TABLE `SEQUENCE` (  

      `tablename` varchar(30) NOT NULL,  

      `nextid` bigint(20) NOT NULL,  

       PRIMARY KEY (`tablename`)  

 ) ENGINE=InnoDB   

每当需要为某个表的新纪录生成ID时就从Sequence表中取出对应表的nextid,并将nextid的值加1后更新到数据库中以备下次使用。此方案也较简单，但缺点同样明显：由于所有插入任何都需要访问该表，该表很容易成为系统性能瓶颈，同时它也存在单点问题，一旦该表数据库失效，整个应用程序将无法工作。有人提出使用Master-Slave进行主从同步，但这也只能解决单点问题，并不能解决读写比为1:1的访问压力问题。

除此之外，还有一些方案，像对每个数据库结点分区段划分ID,以及网上的一些ID生成算法，因为缺少可操作性和实践检验，本文并不推荐。实际上，接下来，我们要介绍的是Fickr使用的一种主键生成方案，这个方案是目前我所知道的最优秀的一个方案，并且经受了实践的检验，可以为大多数应用系统所借鉴。