mysql分区和分表优缺点_mysql分区表优劣分析

就比如一个查询，可能一下查询出1万条记录。这种查询很正常，也很普遍。但是，当系统真正上线以后，有可能出现一下子插入1万条记录的情况吗？所以查询出1万条的查询，速度的提高对系统的性能是很有帮助的，而插入1万条记录速度的降低未必会很大的影响系统的性能。

再比如数据仓库中的高耦合度，要是论插入的速度，那简直惨目人睹。但是这相当于将时间的消耗平坦到每一次的插入里面了。当你要查询时，会非常快的查询出结果。

像你现在这个数据量级的话，说句实话，还用MYSQL真是一种冒险。排除这个，将表分开是正确的。当系统真正运行起来，插入时资源消耗的增加不会对系统增加多大压力，但是会极大的降低查询时对系统的压力，而这部分压力，才是属于能让系统DOWN掉的那部分。

系统资源就是这样，用20%的资源做80%的事情，用剩下的80%的资源做20%的事情。插入就是那80%的事情里面的。就让那20%的消耗增加为25%吧，你会发现那80%的消耗可能一下子就减了一半。

MySQL按月自动创建分区表(千万级大表优化)

对用户来说，分区表是一个的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。

分区的意思是指将同一表中不同行的记录分配到不同的物理文件中 ，几个分区就有几个.idb文件。MySQL数据库的分区是局部分区索引，一个分区中既存了数据，又放了索引。也就是说，每个区的聚集索引和非聚集索引都放在各自区的（不同的物理文件）。

1、可以让单表 存储更多的数据 。

2、 分区表的数据更容易维护 ，可以通过删除与那些数据有关的分区，更容易删除数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个分区进行优化、检查、修复等作。

3、部分查询能够从查询条件确定只落在少数分区上， 查询速度会很快 。

4、通过跨多个磁盘来分散数据查询，来 获得更大的查询吞吐量 。

要使定时起作用，MySQL的常量GLOBAL nt_scheduler必须为on或者是1。

1、查看scheduler的当前状态：

2、修改scheduler状态为打开(0:off , 1:on)：

5、临时开启某个

6、临时关闭某个

MySQL使用为什么要分库分表

1 基本思想之什么是分库分表？

从字面上简单理解，就是把原本存储于一个库的数据分块存储到多个库上，把原本存储于一个表的数据分块存储到多个表上。

2 基本思想之为什么要分库分表？

数据库中的数据量不一定是可控的，在未进行分库分表的情况下，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大，相应地，数据作，增删改查的开销也会越来越大；另外，由于无法进行分布式式部署，而一台的资源（CPU、磁盘、内存、IO等）是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。

3 分库分表的实施策略。

分库分表有垂直切分和水平切分两种。

3.1 何谓垂直切分，即将表按照功能模块、关系密切程度划分出来，部署到不同的库上。例如，我们会建立定义数据库workDB、商品数据库payDB、用户数据库userDB、日志数据库logDB等，分别用于存储项目数据定义表、商品定义表、用户数据表、日志数据表等。

3.3 应该使用哪一种2、中间件方式来实施数据库分库分表，这要看数据库中数据量的瓶颈所在，并综合项目的业务类型进行考虑。

如果数据库是因为表太多而造成海量数据，并且项目的各项业务逻辑划分清晰、低耦合，那么规则简单明了、容易实施的垂直切分必是。

而如果数据库中的表并不多，但单表的数据量很大、或数据热度很高，这种情况之下就应该选择水平切分，水平切分比垂直切分要复杂一些，它将原本逻辑上属于一体的数据进行了物理分割，除了在分割时要对分割的粒度做好评估，考虑数据平均和负载平均，后期也将对项目人员及应用程序产生额外的数据管理负担。

在现实项目中，往往是这两种情况兼而有之，这就需要做出权衡，甚至既需要垂直切分，又需要水平切分。我们的游戏项目便综合使用了垂直与水平切分，我们首先对数据库进行垂直切分，然后，再针对一部分表，通常是用户数据表，进行水平切分。

4 分库分表存在的问题。

4.1 事务问题。

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

4.2 跨库跨表的join问题。

在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。

4.3 额外的数据管理负担和数据运算压力。

上述整理于互联网

MySQL 表数据分区，每10000条数据自动分区

My表分区：MySQL在5.1之后才有的，可以看做是水平拆分，分区表需要在建表的需要加上分区参数，用户需要在建表的时候加上分区参数；分区表底层由多个物理子表组成，但是对于代码来说，分区表是透明的；SQL中的条件中能带上分区条件的列，这样可以定位到少量的分区上，否则就会扫描全部分区。sql不能自动创建分区，需要使用mysql nt的方式自动创建分区

1.创建分区的存储过程如下（每次执行先校验当前分区是否存在，如果存在则不处理；不存在则创建）：

DELIMITER $$#该表所在数据库名称USE `demo`$$DROP PROCEDURE IF EXISTS `create_partition_by_day`$$CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))BEGIN #当前日期存在的分区的个数 DECLARE ROWS_CNT INT UNSIGNED; #目前日期，为当前日期的后一天 DECLARE TARGET_DATE TIMESP; #分区的名称，格式为p20180620 DECLARE PARTITIONNAME VARCHAR(9); #当前分区名称的分区值上限，即为 PARTITIONNAME + 1 DECLARE PARTITION_ADD_DAY VARCHAR(9); SET TARGET_DATE = NOW() + INTERVAL 1 DAY; SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m%d' ); SET TARGET_DATE = TARGET_DATE + INTERVAL 1 DAY; SET PARTITION_ADD_DAY = DATE_FORMAT( TARGET_DATE, '%Y%m%d' ); SELECT COUNT() INTO ROWS_CNT FROM rmation_schema.partitions WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME; IF ROWS_CNT = 0 THEN SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`', ' ADD PARTITION (PARTITION ', PARTITIONNAME, " VALUES LESS THAN (", PARTITION_ADD_DAY ,") ENGINE = InnoDB);" ); PREPARE STMT FROM @SQL; EXECUTE STMT; DEALLOCATE PREPARE STMT; ELSE SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result; END IF;END$$DELIMITER ;

2.数据库定时任务(每小时执行一次)

DELIMITER $$#该表所在的数据库名称USE `demo`$$CREATE IF NOT EXISTS `daily_generate_partition`ON SCHEDULE EVERY 1 hour #执行周期，还有天、月等等STARTS '2018-06-20 00:00:00'ON COMPLETION PRESERVEENABLECOMMENT 'Creating partitions'DO BEGIN #调用刚才创建的存储过程，个参数是数据库名称，第二个参数是表名称 CALL datacollectcenter.create_partition_by_day('demo','test1');END$$DELIMITER ;

目前mysql分库分表中间件好用的有哪个

其优点： 分库分表与应用脱离，分库表如同使用单库表一样

减少db 连接数压力

热重启配置

可水平扩容

遵守Mysql原生协议

无语言限制，mysqlclient,c,ja等都可以使用

Heisenberg通过管理命令可以查看，如连接数，线程池，结点等，并可以调整

采用velocity的分库分表脚本进行自定义分库表，相当的灵活

之前在组里有做过简单的分享，这段时间稍微轻松了点，先分享出来，看有没有更好的idea在这块有所提升

下面开始介绍heisenberg

1.heisenberg总体架构

首先这块架构：

应用对于heisenberg集群来说就是mysql客户端，

访问heisenberg集群可以通过像lvs,F5等负载软件/设备解决，

其实一台heisenberg的性能是相当的赞了，我压力到2320TPS load 都还只有0.1-0.3左右（CPU 8core,16G），由于找不到mysql物理机器了，只得做罢

服务端内部结构：