数据库分库分表及其配置库设计 **什么情况下需要数据库分库分表？** 1.数据设计结构是否优化 2.数据是否做了缓存 3.数据库是否开启缓存配置 4.操作系统是否做了配置 如果以上都做了，负载还大的话才考虑 同一个服务器可以有多个实例，每个实例下可以放多个数据库，在不同端口，实现业务上的水平垂直切分 #数据库扩展的方式 * 数据保存和访问是应用的核心 • 调试程序原则 数据访问遇到瓶颈时如何扩展? 1、同一数据并发访问瓶颈 • 主从同步扩展 – 建立主从同步,读写分离架构 2、不同数据访问量瓶颈 • 数据切分扩展 – 将数据依据不同的标准进行切分,不在同一个服务器 上,不在同一个数据库上,甚至不在同一个表中 3、关系数据库瓶颈 – 缓存、全文检索、NOSQL等 #分库分表的定义与方式 1. 分库分表,也叫 切分(Sharding) 2. 分为垂直切分(根据业务切分)和水平切分(根据表中 数据的条件切分) 3. 也有组合切分的方式,先将表进行业务拆分,再根据某 些数据条件对数据进行切分 4. 一台机器也可以实现切分?分库如何切?分表如何切?    #分库分表的优势与劣势 ##分库分表的优缺点 优点 1、实现数据承压的分离 2、实现数据的安全,没有把鸡蛋放在一个篮子里 • 可访问 • 保密 **缺点** 1、事务的失效 – 不苛求实时一致性、保障最终一致性 2、关联功能的失效,不能用join – 用多个简单查询来实现复杂查询 – 使用支持分库分表的中间件(MySQL Proxy或者自行开发) 3、自增长主键的失效 – 偏移增长的id,不推荐 – 实现ID生成器  ##id分表解决方案及特点: 1、按步长偏移id  不需要程序介入,但不好扩容 2、按时间  只适合热点、归档性质数据 3、按数据量  扩展方便,但访问不均,修改困难 4、取模  分散均匀,但扩容需要一定规则 5、映射表  修改方便,算法复杂且难以预测需求 ##分库分表的经验和依据 1. 一般情况下,不需要分库分表 2. 每个表,如果在可以预见的空间内,在百万级以内都不需要分表 百万级?什么叫可预见? 3. 访问量大的数据取模分表,相对小的可以尝试范围分表,动态等过 期很少访问的按时间分表 4. 如果用户要分表,一般来讲,一个用户会生成多条的,需要分表 也看业务 ##分库分表的经验和依据 1. 一般情况下,不需要分库分表 2. 每个表,如果在可以预见的空间内,在百万级以内都不需要分表 百万级?什么叫可预见? 3. 访问量大的数据取模分表,相对小的可以尝试范围分表,动态等过 期很少访问的按时间分表 4. 如果用户要分表,一般来讲,一个用户会生成多条的,需要分表 也看业务 #分库分表的配置库设计 **PHP直接访问分库分表** ~~~ if(uid % 4 == 0 || uid % 4 == 1){ $host = '192.168.0.1'; }else{ $host = '192.168.0.1'; } mysql_connect($host); ~~~ ##为什么需要配置库? **配置库屏蔽分库分表的复杂性** 1. 由conf配置信息统一提供 ~~~ $host = query($conf, $tbl) mysql_connect($host) ~~~  2. 配置信息放到后台服务 对php完全透明 ##配置库设计? 1. 纵向分表: 服务器分组 2. 横向: 采用相同的数据库名 3. 主辅库: 属于同一服务器分组,组内数据完全相同 ##配置库需要几个表? 1. 服务器配置 – 服务器里有哪些属性 – 支持不支持一个IP多个服务器实例 2. 表类型配置 – 为什么要有表类型 3. 表配置 – 具体的表配置 – 哪个分表,在哪个服务器,哪个端口的哪个库下 ###分库分表的服务器配置 ~~~ create table uc_server_setting ( sid int not null primary key auto_increment COMMENT ' DB 服务器 ID', master_sid int not null COMMENT ' 对应的主库ID', host varchar(255) not null COMMENT ' 数据库连接host', port int unsigned not null COMMENT ' 数据库连接port', user varchar(32) not null COMMENT ' 数据库连接user', passwd varchar(32) not null COMMENT ' 数据库连接password', active boolean not null default 1 COMMENT ' 是否在线提供服务', backup boolean not null default 0 COMMENT ' 是否是备份机', remark text not null default '' COMMENT ' 说明', index(master_sid), unique(host, port) )ENGINE=InnoDB DEFAULT CHARSET=UTF8; ~~~ ###一个服务器分组的配置 ~~~ 3=>array( "sid"=>3, "master_sid"=>0, "host"=>"10.70.58.56", "port"=>3308, "user"=>”ucai", "passwd"=>”ucai_pass", "active"=>1, "backup"=>0, "slaves"=>array( 0=>array( "sid"=>5, "master_sid"=>3, "host"=>"10.70.58.56", "port"=>3310, "user"=>”ucai", "passwd"=>"ucai_pass", "active"=>1, "backup"=>0, ), ),), ~~~ ###分库分表的表类型设置 ~~~ create table uc_kind_setting ( kind varchar(64) not null primary key COMMENT ' 表类型', table_num int not null COMMENT ' 对象表的拆分数量', table_prefix varchar(64) not null COMMENT ' 表名前缀', -- 真正的表名是这样的 <table_prefix>_<no> id_field varchar(64) not null COMMENT ' 拆分标示字段', -- 根据这个字段取模对数据进行拆分 remark text not null default '' COMMENT ' 说明', unique (table_prefix) )ENGINE=InnoDB DEFAULT CHARSET=UTF8; ~~~ ###分库分表的表类型设置 ~~~ ”uc_group_user"=>array( "kind"=>"uc_group_user", "table_num"=>4, "table_prefix"=>"uc_group_user", "id_field"=>"gid”), ~~~ ###分库分表的表配置 ~~~ create table cg_table_setting ( kind varchar(64) not null COMMENT ' 表类型', no int not null COMMENT ' 表序号, 在 [0, table_num) 范围内', sid int not null COMMENT ' 所在的DB服务器主库ID, (辅库ID不 需要写在这个表中)', db_name varchar(64) not null COMMENT ' 表所在的数据库名', unique (kind, no) )ENGINE=InnoDB DEFAULT CHARSET=UTF8; ~~~ ###分库分表的表配置 ~~~ "uc_group_user"=>array( 0=>array( "sid"=>4, "no"=>3, "db_name"=>"ucai_group", ), 1=>array( ), 2=>array( ), 3=>array( "sid"=>2, "no"=>0, "db_name"=>"ucai_group", "sid"=>4, "no"=>1, "db_name"=>"ucai_group", "sid"=>2, "no"=>2, "db_name"=>"ucai_group", ), ), ~~~ #分库分表的配置工具 ##服务器配置 dbtool.php A、查询数据库服务器信息 B、添加数据库服务器,建立主从关系 C、修改表结构,批量修改表结构 D、在线、下线服务器 E、显示在线下线服务器 ~~~ Usage: php dbtool.php act=query host=<ip> act=query msid=<master_sid> act=query sid=<sid> act=add host=<host> user=<user> passwd=<passwd> msid=<master_sid> port=<port> act=alter kind=<kind> cmd=<cmd> act=online host=<host> port=<port> act=offline host=<host> port=<port> act=liston act=listoff ~~~ ##表管理 Usage: php split_db_sql.php dbfile=<dbfile> dbname=<dbname> A、自动切分文件 B、自动实现分表检测 #配置导出与生成 Usage: newtable.php kind=<kind> sid=<sid‐sid> db=<db_name> sqlfile=<sqlfile> num=[num] A、指定表格文件 B、批量建表 C、自动添加到配置库 #总结 1、数据库扩展方式 2、分库分表原则及优缺点 3、配置库设计 4、配置库工具