wangtaotao个人页面-阿里云开发者社区

mysql分区方案的研究

笔者觉得，分库分表确实好的。但是，动不动搞分库分表，太麻烦了。分库分表虽然是提高数据库性能的常规办法，但是太麻烦了。所以，尝试研究mysql的分区到底如何。之前写过一篇文章，http://www.cnblogs.com/wangtao_20/p/7115962.html 讨论过订单表的分库分表，折腾起来工作量挺大的，需要多少技术去折腾。做过的人才知道有多麻烦要按照什么字段切分，切分数据后，要迁移数据;分库分表后，会涉及到跨库、跨表查询，为了解决查询问题，又得用其他方案来弥补(比如为了应对查询得做用户订单关系索引表)。工作量确实不小。从网上也可以看到，大部分实施过的人(成功的)的经验总结：水平分表，不是必须的，能不做，尽量不做。像阿里这些系统，数据库单表数量十多亿，达到瓶颈了，不得不做分库分表，扩容也方便。没有选择。那么，针对起步阶段的业务，技术人员不够，产品还处在试错阶段。是不是可以考虑一下分区方案。笔者几年前，也犯了思维错误，在小公司做系统，产品还在开发，有待推向市场验证。那个时候，笔者就去考虑什么评论表数据量大的情况下要怎么做，其实伤脑，又费时间，业务没有做起来，其实没多少用处。架构是演变出来的，不是设计出来的。企图一开始就设计大炮，结果只有蚊子。笔者做试验看看mysql的分区到底是什么个原理。研究发现，其实跟分表差不多，比如按hash打散数据、按值范围分散数据。一、探讨分区的原理了解分区到底在做什么，存储的数据文件有什么变化，这样知道分区是怎么提高性能的。实际上：每个分区都有自己独立的数据、索引文件的存放目录。本质上，一个分区，实际上对应的是一个磁盘文件。所以分区越多，文件数越多。现在使用innodb存储较多，mysql默认的存储引擎从mysiam变为了innodb了。以innodb来讨论： innodb存储引擎一张表，对应两个文件：表名.ibd、表名.frm。如果分区后，一个分区就单独一个ibd文件，如下图：将fs_punch_in_log表拆分成4个分区，上图中看到，每个分区就变成一个单独的数据文件了。mysql会使用"#p#p1"来命名数据文件，1是分区的编号。总共4个分区，最大值是4。分表的原理，实际上类似，一个表对应一个数据文件。分表后，数据分散到多个文件去了。性能就提高了。分区后的查询语句语句还是按照原来的使用。但为了提高性能。还是尽量避免跨越多个分区匹配数据。如下图，由于表是按照id字段分区的。数据分散在多个分区。现在使用user_id作为条件去查询。mysql不知道到底分配在哪个分区。所以要去全部分区扫描，如果每个分区的数据量大，这样就耗时很长了。分区思路和分区语句 id字段的值范围来分区：在1-2千万分到p0分区，4千万到-6千万p1分区。6千万到8千万p2分区。依此推算下去。这样可以分成很多的分区了。为了保持线性扩容方便。那么只能使用range范围来算了。 sql如下 CREATE TABLE `fs_punch_in_log` ( `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键自增' , `user_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '签到的用户id' , `punch_in_time` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '打卡签到时间戳' , PRIMARY KEY (`id`) ) partition BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (40000000), PARTITION p2 VALUES LESS THAN (80000000), PARTITION p3 VALUES LESS THAN (120000000), PARTITION p4 VALUES LESS THAN MAXVALUE ); 　　以上语句经过笔者测验，注意点：按照hash均匀分散。传递给分区的hash()函数的值，必须是一个整数(hash计算整数计算，实现均匀分布）。上面的id字段就是表的主键，满足整数要求。 partition BY RANGE 中的partition BY表示按什么方式分区。RANGE告诉mysql，我使用范围分区。情况：如果表结构已经定义好了，里面有数据了，怎么进行分区呢？使用alter语句修改即可，经过笔者测验了。 ALTER TABLE `fs_punch_in_log` PARTITION BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (40000000), PARTITION p2 VALUES LESS THAN (80000000), PARTITION p3 VALUES LESS THAN (120000000), PARTITION p4 VALUES LESS THAN MAXVALUE ) 　　注：由于表里面已经存在数据了，进行重新分区，mysql会把数据按照分区规则重新移动一次，生成新的文件。如果数据量比较大，耗时间比较长。二、四种分区类型 mysql分区包括四种分区方式：hash分区、按range分区、按key分区、list分区。四种有点多，实际上，为了好记，把类再缩小点，就两大类方式进行分区：一种是计算hash值、一种是按照范围值。其实分库分表的时候，也会用到两大类，hash运算分、按值范围分。 1、HASH分区有常规hash和线性hash两种方式。常规hash是基于分区个数取模（%）运算。根据余数插入到指定的分区。打算分4个分区，根据id字段来分区。怎么算出新插入一行数据，需要放到分区1，还是分区4呢? id的值除以4，余下1，这一行数据就分到1分区。常规hash，可以让数据非常平均的分布每一个分区。比如分为4个取，取余数，余数总是0-3之间的值(总到这几个分区去)。分配打散比较均匀。但是也是有缺点的：由于分区的规则在创建表的时候已经固定了，数据就已经打散到各个分区。现在如果需要新增分区、减少分区，运算规则变化了，原来已经入库的数据，就需要适应新的运算规则来做迁移。实际上在分库分表的时候，使用hash方式，也是数据量迁移的问题。不过还好。针对这个情况，增加了线性hash的方式。线性HASH(LINEAR HASH)稍微不同点。实际上线性hash算法，就是我们memcache接触到的那种一致性hash算法。使用虚拟节点的方式，解决了上面hash方式分区时，当新增加分区后，涉及到的数据需要大量迁移的问题。也不是不需要迁移，而是需要迁移的数据量小。在技术实现上：线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY”子句中添加“LINEAR”关键字。两者也有有相同的地方：都是均匀分布的，预先指定n个分区，然后均匀网几个分区上面分布数据。根据一个字段值取hash值，这样得到的结果是一个均匀分布的值。后面添加新的分区多少需要考虑数据迁移。常规HASH和线性HASH，因为都是计算整数取余的方式，那么增加和收缩分区后，原来的数据会根据现有的分区数量重新分布。 HASH分区不能删除分区，所以不能使用DROP PARTITION操作进行分区删除操作；考虑以后迁移数据量少，使用线性hash。 2、按照range范围分区范围分区，可以自由指定范围。比如指定1-2000是一个分区，2000到5000千又是一个分区。范围完全可以自己定。后面我要添加新的分区，很容易吗? 3、按key分区类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。 4、按list方式分区可以把list看成是在range方式的基础上的改进版。list和range本质都是基于范围，自己控制范围。 range是列出范围，比如1-2000范围算一个分区，这样是一个连续的值。而list分区方式是枚举方式。可以指定在1,5,8,9,20这些值都分在第一个分区。从list单词的字面意思命名暗示就是列表，指定列表中出现的值都分配在第几个分区。三、如何根据业务选择分区类型 1、何时选择分区,何时选择分表分表还是比分区更加灵活。在代码中可以自己控制。一般分表会与分库结合起来使用的。在进行分表的时候，顺带连分库方案也一起搞定了。分表分库，性能和并发能力要比分区要强。分表后，有个麻烦点：自己需要修改代码去不同的表操作数据。比如用户表分表后，计划分4个表，每个表4千万用户。按照用户编号取模为4。代码很多处要做专门的匹配如下：每次操作用户资料，先要根据uid算出是哪个表名称。然后再去写sql查询。当然，是可以使用数据库中间件来做完成分库、分表。应用代码不用修改。大部分中间件是根据他们自己的业务特点定制的，拿来使用，不见得适合自己的业务。所以目前缺少通用的。如果使用分区的方式。代码不用修改。sql还是按照原来的方式写。mysql内部自动做了匹配了。非常适合业务刚刚起步的时候，能不能做起来，存活期是多久不知。不用把太多精力花费在分库分表的适应上。考虑到现在业务才起步，使用分区不失为一种既省事又能提高数据库并发能力的办法。等以后业务发展起来了，数据量过亿了，那个时候经济实力已增强，再做改进方案不迟。架构是演变出来的，不是设计出来的。适应当前业务的方案，就是好的方案。过度设计也是一种负担：很多技术，企图一开始就设计出一个多大量的系统，实际上没有那种量，为了显示自己技术牛逼。总结：访问量不大，但是数据表行数很多。可以使用分区的方式。访问量大，数据量大，可以重构成分表的方式。这是因为虽然数据量多，但是访问量不大，如果使用分表的话，要修改代码很多地方，弄起来太麻烦了。投入多，产出少就没必要了。 2、如何选择适合自己的分区类型使用分区和分表一样的思想：尽量让数据均匀分散，这样达到分流、压力减小的效果。如果不能均匀分布，某个分区的操作量特别大，出现单点瓶颈。虽然4种类型的分区方式。其实总共两大类，按范围分区和按hash运算分区。 range范围分区，适合按照范围来切分数据。比如按时间范围分区。 hash，适合均匀分散数据。使用hash分区，麻烦点是后续增加分区，数据要迁移。有了线性hash分区法，这个迁移量减低了很多。以用户表为例子，如果要使用分区方案。改使用哪种分区类型呢？考虑到user_id一般不会设计成自增数字。有人会奇怪，怎么不是自增的，我见过好多用户编号都是自增的！的确，有自增数字做uid的，不过一般是开源系统为了省事，比如discuz、ecshop等。人家没那么多工作量给你设计用户编号方案。自增的用户编号，由于是每次加1进行递增的。这规律太明显了，很容易被别有用途的人猜测user_id。再说，别人一看就知道你有多少用户! 有个原则，设计编号的时候，尽量不要让外部知道你的生成规律。比如订单号，如果是逐个加1的订单号，外界可以猜测出你的业务订单总数出来。说一个自增用户编号的例子。笔者曾经在一家上市互联网公司，有几千万的用户，uid过去是discuz那一套自增的方式。后来不得不改掉user_id的生成方式。笔者当时负责了这个改造方案。不是自增的数字，会是这种：注册一个是1897996，再注册一个是9689898，外界完全找不到数字的规律。不是自增的编号，如果使用范围来分区，各个分区的数据做不到均匀分布的。原因如下：比如说用户编号为1-200000000的用户分配到p1分区，20000000-40000000分配到p2分区，40000000-60000000分配到p3区，这样类推下去。由于用户编号不是自增，注册分配到的用户编号，可能是1到2千万之间的数字，也可能是4千万到6千万之间的一个数字。如果被分配到4千万到6千万的数字会更多，那么各个分区给到的数据是不均匀的。故不好使用范围来分区。比较好的办法是，使用hash取模，user_id%分区数。数据就可以分散均匀到4个分区去了。

抢购(秒杀)业务的技术要点

本文为原创文章，转载希望注明出处。抢购业务数据库需要考虑的点如下：一、超卖现象场景如下：库存数是5。现在3个用户来购买，a用户购买2个，b用户购买3个，c用户购买1个。合起来就是准备购买6个。如果三个用户是同时并发购买，会出现怎样的情况呢? 每个用户进行减库存的时候，语句类似于： update goods set amount=amount-购买数量 where goods_id=xxx。　　 mysql会锁定这一行数据(使用innodb存储引擎)，数据库加的是排他锁。根据排他锁的特点：其他线程不能读、不能写此行数据。排他锁情况下，那么其他用户就是等待状态了。 1、A用户执行update的时候，锁定库存数据。update执行完毕后，减去了2个后，mysql自动释放锁。 2、b用户执行，减去了3个。此时，已经卖掉5个库存了。库存数为0了。 3、但是c用户接着执行，Update goods set amount=amount-1 where goods_id=xxx 结果库存数量变成-1了。思考：把库存数量字段的类型，设计成正数类型，不允许出现负数，会怎么样呢? 测验结果：数据库会直接报错。通不过。解决办法：只有库存数量，大于或等于购买数量的时候，才能去减库存。其他情况，提示信息，库存不足。 sql语句如下： update goods set amount=amount-购买数量 where goods_id=xxx AND amount>=购买数量这样，轮到c执行的时候，由于使用了amount>=购买数量做限制条件，update语句返回的受影响的行数是0,意味着执行失败了。直接提示，库存不足。二、并发抢购造成的速度慢问题 1、实现方式对比：悲观锁与乐观锁第一种问题中描述的超卖现象，其实是并发抢购时出现的情况。用到的是数据库内带的加排他锁方式，阻止了其他线程读取、访问数据，这要等待操作完毕后其他线程才能操作，这种方式是悲观锁方式。这样会等待下去。使用数据库的悲观锁，是避免了数据并发更新，但是，加锁毕竟是很耗服务器资源的，用户要等待下去。所以并不能达到好的性能和高并发。业界使用乐观锁的办法来解决：使用数据库的乐观锁是通用解决办法。通用锁实现了版本控制。不会进行排斥掉。减少资源的消耗。乐观锁是相对悲观锁而言的，使用的是更加宽松的锁定方式。乐观锁，通俗说就是：修改数据的时候，不给数据加锁。既然不加锁，其他线程也是可以访问、修改数据。但是，修改的时候会获取一个版本号，只有版本号符合了，才算更新成功。不成功的，都算抢购失败。 2、乐观锁的具体实现方式乐观锁的机制与代码版本库svn很相似，这种方式，叫做多版本记录方式。如果在我提交代码之前用本地代码的版本号与服务器做对比，如果本地版本号小于服务器上最新版本号，则提交失败，产生冲突代码，让用户决定选择哪个版本继续使用。逻辑描述： if(之前读取到行的版本号+1=数据库此行现在的版本号+1){ //符合预期，数据库的数据没有给其他用户修改掉。可以直接写入数据了 }else{ //数据已经被修改了。所以当前的版本已经落后了。不能进行更新 } 例子：给表goods加一个版本字段version,用来记录行数据值的版本号。版本号version字段，设计成一个正整数，比如是时间戳。每次修改后，要将version字段的值加1: 1496916794、1496916795、1496916796、1496916797、1496916798................. 读数据的时候，顺便将版本号的值读取出来。update时，做版本号对比，如下： 1、先读取这个商品的信息，顺便将版本号读取出来 select amount,version from t_goods where goods_id=8899; 　　 2、更新数据 update goods set amount=amount-2,version=version+1 where goods_id=8899 and version=#{version} and amount>=2; 　　 sql解释： #{version}是之前select读取到的版本号，填入进去，意思是只能修改这样的。修改的时候，限制条件-必须版本号等于原来的版本号才能去修改。否则不能修改。更新成功的同时，版本号要加1。优点：使用数据库的乐观锁是通用解决办法。通用锁实现了版本控制。线程之间同时操作，不加锁，线程不用等待了。减少了数据库资源的消耗。缺点：会增加cpu的计算开销。不过值得这样做，由于没有加锁进行阻塞，用户不用等待结果，很快能等到执行结果了，用户体验更好。抢购的并发数其实提高了。三、减库存和下单保持在一个事务内如果不在一个事务内，可能出现两种现象： 1、订单入库失败、减库存成功。发现订单入库失败，减库存就不要继续进行下去了。 2、订单入库成功、减库存失败。实际下了20个订单，库存却没有减。数据不一致了。四、虚拟库存和真实库存两套方案考虑几种情况： 1、有些人下单完后，最终并不会去付款。如果一下单就马上减库存,很多人下单，最终并不会去付款，可能导致库存数最后为0，别的用户无法下单了。而实际中仓库中却有库存在，这样库存数据是不准确的。 2、什么时候减库存? 是下单完成减库存、还是付款完后减库存呢? 付款后，才减库存，可能出现的现象：用户下完单，接着去付款，结果库存不够了。这样用户体验很不好。下完单就减库存，能够保证用户下单只要付款，就一定能买到这个商品。用户体验较好。针对一些人下单后，不付款，占着库存资源，其他人无法下单。这个问题好解决，给付款设置一个有效期限，比如30分钟。超过这个时间，库存就释放掉了。具体技术实现办法：下单后，马上减去库存。另外设置一个定时脚本，扫描超过30分未支付的订单，把订单中的商品数量返回到库存中去。为什么使用虚拟库存和真实库存两套方案？假设库存数是50，a订单购买了5个件商品，支付完毕，库存数减去5，库存数变成了45件。由于还没有发货，实际库存中还有50件商品。这样会出现混淆了。使用两套库存记录方案是有必要的！下单-操作虚拟库存数商品发货出库-操作真实库存数真实库存数，记录下仓库中这件商品真有多少件。真实库存，其实非常方便内部人员查看，它只有商品出库，这个库存才减。虚拟库存，用来应对商品购买的。表明，还有多少数量可以给用户去购买。并不表示仓库中的库存数。五、频繁读库存的压力因为，每次点击，都要读取库存，判断：有没有库存。如果读库存走的是数据库判断，很多人来抢购的情况下，数据库的压力会很大。假设是1万个用户同时访问抢购页面。数据库接受的访问次数是1万个并发。用户还要进行刷新页面操作，由于每次刷新都会走数据库判断库存。数量会更大。数据库的压力就更大了。所以最好是，把库存总数，缓存在redis中去。内存中缓存的库存数量，只用来做读判断。这样压力扛住了。而更改数据库的库存总数了，程序马上要把库存总数，同步到缓存中去。系统抗压力问题一、如何限流二、如何防止恶意刷数据。防止的就是写代码去频繁请求，为了识别是机器还是人工。加友好一点的验证码。

wangtaotao个人页面-阿里云开发者社区

个人介绍

擅长的技术

业务初期野蛮生长阶段,微服务化比较麻烦

使用nginx搭建rtmp服务器

mysql分区方案的研究

抢购(秒杀)业务的技术要点

mysql主从复制延迟问题的相关知识与解决方案

js开发积累

php不设置脚本执行时间的方法

SQL编写总结

js代码定义类代码的领悟

遇到mysql数据表不存在的问题

include、ruquire使用相对路径总结

php中未定义的变量使用技巧

头信息已输出的报错信息位置定位

数据库设计字段类型设置经验

mysql replace into语句学习

js操作的跨域问题:js实现自动设置框架载入页面高度的问题

插入数据sql使用“insert into set”形式的理解

mysql变量使用总结

get方式传递参数问题解决经验

mysql优化学习

insert 语句后面的一些参数使用知识

看mysql手册中模式(SQL_MODE)学到的知识

group by 深入总结

深入update语句(延伸学习)

《LINUX与UNIX SHELL编程指南》学习笔记