數據庫分庫分表及其配置庫設計 **什么情況下需要數據庫分庫分表？** 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、配置庫工具