MySQL分区分表应用场景分析和分区中的问题与实践

1、 MySQL 分区分表应用场景分析和分区中的问题与实践 MySQL的分区和分表应用场景分析在日常工作中当我们的某张表的数据量过大的时候，首当其冲的可能就是进行分区和分表，但是是如何分区或者分表都要结合一点的业务场景下进行分析，才会显著的提升性能，来聊一聊大家都在什么样的场景下做过怎么样的操作吧。hotdb CTO：一、分表的前世今生MySQL5.0以前版本存在单表行数的性能下降拐点，以经典的MySQL4.17或MySQL3.23为例，那个特定年代的存储引擎主要是ISAM或类ISAM存储引擎和BDB存储引擎，运行平台数据库服务器主要是2-4块硬盘，内存几乎都是4G-16G为主。在此背景下，行业内

2、公认ISAM存储引擎的性能拐点为单表500万行记录，故一般会超过拐点的数据量表就会做人为分表和应用程序配合，常见是按日期（例如：每月份一张表）、HASH值等方式。进入MySQL5.0时代，主要是MyISAM或类MyISAM存储引擎和InnoDB存储引擎，但MySQL同其他商业数据库产品相比依然无表分区特性。部分业务场景存在单表数据删除或数据冷热明显的需求，为方便定期删除数据和归档数据，或进行数据冷热分离而提升性能。前者MyISAM和InnoDB存储引擎都存在，通常做法是定期切换表，业务低谷期加表级别锁，完成表名称的RENAME操作；后者主要是针对MyISAM存储引擎，只有索引缓存区，此时就会直

3、接进行分表，通常做法是MyISAM存储引擎的表数据分表后，再借助MERG存储引擎合并回来，减少应用程序改造。二、分区的功能特性进入MySQL5.1及以上的时代，也随着硬件成本的降低和平民化，X86服务器硬盘更多和内存更大（起步都是6块SAS硬盘，64G内存），InnoDB存储引擎得到迅速的普及，且MySQL数据库支持分区特性。InnoDB存储引擎具备元数据和索引数据的自适应调节数据活跃度的缓存功能，内存也足够大，同时具备分区功能和支持交换分区，此时往往不再使用分表的方法，而是借助分区功能实现数据的删除和归档等。三、分库的缘由分表和分区都是基于同一个数据库里的数据分离技巧，对数据库性能有一定提升

4、，对MySQL数据库的吞吐量无质的变化。当业务系统的数据容量接近或超过单台X86服务器的容量、QPS/TPS接近或超过单个MySQL数据库实例的处理极限等，则重点在于扩展MySQL数据库的吞吐量和数据处理量。此时，往往是采用垂直和水平结合的数据拆分方法，把数据服务和数据存储分布到多台MySQL数据库服务器上。分库只是一个通俗说法，更标准名称是数据分片，采用类似分布式数据库理论指导的方法实现，对应用程序达到数据服务的全透明和数据存储的全透明，这个领域的知识内容就更复杂，日后慢慢分篇幅介绍。四、总结补充说明：若MyISAM存储引擎分表后，借助类MyISAM的MERG存储引擎，则不需要改造应用程序，

5、存在少量限制。匿名用户：随着数据量的增加，一般的处理顺序是分区：不同分区可以在不同的存储介质上，提供IO并发度，同时由于分区对应用透明，因此应用不需要改动分表：根据业务主键对业务数据进行分表，这样如果出现故障，只是部分业务受影响，对于日期型日志表等进行truncate也会简单很多。分库：可以应用租户的概念管理资源，将不同租户的数据存放在不同数据库实例中，冯帅 数据库管理员：前面几位说的都很好，大体而言的话数据库数据越来越大，随之而来的是单个表中数据太多。以至于查询书读变慢，而且由于表的锁机制导致应用操作也搜到严重影响，出现了数据库性能瓶颈。分表，即将单个数据库表进行拆分，拆分成多个数据表，然后

6、用户访问的时候，根据一定的算法，让用户访问不同的表，这样数据分散到多个数据表中，减少了单个数据表的访问压力。提升了数据库访问性能。分区，即将单表拆成多个分区，然后用户访问的时候，根据分区键，让用户访问某一个分区，这样数据分散到多个分区中，减少了单分区的访问压力。提升了数据库访问性能。至于采取某一方案还是二者结合起来，主要还是看你的业务。qditz 项目总监：大表可以按照业务规则来分解多个表，如range范围，hash哈希，key键值，list预定义列表，composite复合模式等。也可以自定义规则。分表规则跟分区一样的。MySQL分区中遇到哪些坑点和问题在MySQL 5.7.1之前的分区表不

7、支持HANDLER语句，有时候修改SQL模式可能会导致分区表不可用。mysql5.6中的分区总是有人说有很多坑，尽管5.7之后有了更好的支持，但是还是很多人都不太喜欢用，大家来分享一下使用mysql的分区的时候遇到的问题。韩成亮 数据库架构师：这边应该是说分区表。分区表的坑很多还是在数据量上，查询性能上，维护性不是很可控，可能分区数据不均衡，很容易造成热点数据。还有就是分区键的选择很关键，要贴合业务查询的规则，否则性能很差，对并发的支持不是很好，维护的成本很高。undefined：先说人。一般的MySQL开发不太关注数据量，只有在慢或者不可用的时候才会关注。这个时候需要DBA主动去推，不过由于资料比oracle的少，所以还是需要看官方文档，英文是一道坎。再说使用。分区的片键选择很重要就像1楼所说的，另外需要改表结构，把分区条件放在主键。坑的话开发环境不要滥用，遇到过开发hash分区分了上百个出来；需要改表结构，分区放在主键；还有老版本有handler的问题。冯帅 数据库管理员：有一个先入为主的原则，我们都知道MySQL分区问题很多，导致了很多人不会考虑使用分区，这边我想问下，对于