mysql sharding ，大家有什么好的方案吗？

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

This topic created in 4143 days ago, the information mentioned may be changed or developed.

现在数据库层想做这样的方案，cluster+sharding，cluster方面资料挺多的，也基本上实现了，但是sharding方面好复杂，一种方案是业务层改变，另一种方案是加一层透明proxy，找了一下好像目前没有好用的proxy。业务层方面改又很费劲，面临以下问题：

Do I need to shard all of my tables or just the big tables?
How do I ensure my data is evenly distributed between shards?
How does sharding affect referential integrity constraints?
How do I use auto increment values and ensure unique values across all shards?
How do I perform joins between my sharded tables and non-sharded tables?
How do I run aggregrate queries that need data from multiple shards?
What if I need to add more shards later on or change the sharding strategy?
How do I perform the initial sharding of my existing data?
What about joins between shards and transactions involving multiple shards?
How do I ensure data is going to the correct shard?
How do I implement HA in a sharded environment?
How does sharding affect my backup/recovery procedures?

请问大家都是怎么sharding的？最好是生产环境验证过的，谢谢

sharding

shards

tables

3 replies • 2015-05-25 06:10:41 +08:00

Actrace

May 23, 2015

分发的话,Haproxy不就够了吗..

zaishanfeng

May 23, 2015

@Actrace sharding 比如单表一亿条数据分成十个子表每个一千条

siteshen

May 25, 2015

1. 用户产生的可能“超载”都需要 sharding
2. sharding时指定个权重即可，刚开始3个sharding时，1:1:1，时隔半年增加3个sharding,1:1:1:5:5:5 即可（具体比例根据业务发展调整）
3. 外键都得删掉，人工检查维护，当然完整性不能得到DB层保证
4. ((timestamp -CONSTANTS) << 23) + (sharding_id << 10) + (auto_increment & (2<<10))（支持2^13个sharding）
5. 避免join
6. 每个sharding里aggregrate，代码里合并
7. 一致性hash
8. return sharding0 if id < 10^9
9. WTF?
10. 代码保证啊
11. WTF?
12. 和去掉外键一样，没法保证

没在生产环境验证过，不谢。

参考文献：
[1] http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
[2] http://media.postgresql.org/sfpug/instagram_sfpug.pdf