×÷ΪSharding-JDBC ·Ö¿â·Ö±íʵսϵÁеĿªÆªÎÄÕ£¬ÎÒÃÇÔÚÇ°ÎÄÖлعËÁËһϷֿâ·Ö±íµÄ»ù´¡ÖªÊ¶£¬¶Ô·Ö¿â·Ö±íµÄ²ð·Ö·½Ê½ÓÐÁËÒ»¶¨µÄÁ˽⣬ϱßÎÒÃǽéÉÜһϠSharding-JDBC¿ò¼ÜºÍ¿ìËٵĴһ¸ö·Ö¿â·Ö±í°¸Àý£¬Îª½²½âºóÐø¹¦Äܵã×¼±¸ºÃ»·¾³¡£
Ò»¡¢Sharding-JDBC ¼ò½é
Sharding-JDBC ×îÔçÊǵ±µ±ÍøÄÚ²¿Ê¹ÓõÄÒ»¿î·Ö¿â·Ö±í¿ò¼Ü£¬µ½2017ÄêµÄʱºò²Å¿ªÊ¼¶ÔÍ⿪Դ£¬Õ⼸ÄêÔÚ´óÁ¿ÉçÇø¹±Ï×ÕߵIJ»¶Ïµü´úÏ£¬¹¦ÄÜÒ²Öð½¥ÍêÉÆ£¬ÏÖÒѸüÃûΪ ShardingSphere£¬2020Äê4⽉16⽇Õýʽ³ÉΪ Apache Èí¼þ»ù⾦»áµÄ¶¥¼¶Ïî⽬¡£
Ëæ×Å°æ±¾µÄ²»¶Ï¸üµü ShardingSphere µÄºËÐŦÄÜÒ²±äµÃ¶àÔª»¯ÆðÀ´¡£´Ó×ʼ Sharding-JDBC 1.0 °æ±¾Ö»ÓÐÊý¾Ý·ÖƬ£¬µ½ Sharding-JDBC 2.0 °æ±¾¿ªÊ¼Ö§³ÖÊý¾Ý¿âÖÎÀí£¨×¢²áÖÐÐÄ¡¢ÅäÖÃÖÐÐĵȵȣ©£¬ÔÙµ½ Sharding-JDBC 3.0°æ±¾ÓÖ¼Ó·Ö²¼Ê½ÊÂÎñ £¨Ö§³Ö Atomikos¡¢Narayana¡¢Bitronix¡¢Seata£©£¬Èç½ñÒѾµü´úµ½ÁË Sharding-JDBC 4.0 °æ±¾¡£
ÔÚÕâÀï²åÈëͼƬÃèÊö
ÏÖÔÚµÄ ShardingSphere ²»µ¥µ¥ÊÇָij¸ö¿ò¼Ü¶øÊÇÒ»¸öÉú̬Ȧ£¬Õâ¸öÉú̬Ȧ Sharding-JDBC¡¢Sharding-Proxy ºÍ Sharding-Sidecar ÕâÈý¿î¿ªÔ´µÄ·Ö²¼Ê½Êý¾Ý¿âÖмä¼þ½â¾ö·½°¸Ëù¹¹³É¡£
ShardingSphere µÄÇ°Éí¾ÍÊÇ Sharding-JDBC£¬ËùÒÔËüÊÇÕû¸ö¿ò¼ÜÖÐ×îΪ¾µä¡¢³ÉÊìµÄ×é¼þ£¬ÎÒÃÇÏÈ´Ó Sharding-JDBC ¿ò¼ÜÈëÊÖѧϰ·Ö¿â·Ö±í¡£
¶þ¡¢ºËÐĸÅÄî
ÔÚ¿ªÊ¼ Sharding-JDBC·Ö¿â·Ö±í¾ßÌåʵս֮ǰ£¬ÎÒÃÇÓбØÒªÏÈÁ˽â·Ö¿â·Ö±íµÄһЩºËÐĸÅÄî¡£
·ÖƬ
Ò»°ãÎÒÃÇÔÚÌáµ½·Ö¿â·Ö±íµÄʱºò£¬´ó¶àÊÇÒÔˮƽÇзÖģʽ£¨Ë®Æ½·Ö¿â¡¢·Ö±í£©Îª»ù´¡À´ËµµÄ£¬Êý¾Ý·ÖƬ½«Ô±¾Ò»ÕÅÊý¾ÝÁ¿½Ï´óµÄ±í t_order ²ð·ÖÉú³ÉÊý¸ö±í½á¹¹ÍêÈ«Ò»ÖµÄСÊý¾ÝÁ¿±í t_order_0¡¢t_order_1¡¢···¡¢t_order_n£¬Ã¿ÕűíÖ»´æ´¢Ô´ó±íÖеÄÒ»²¿·ÖÊý¾Ý£¬µ±Ö´ÐÐÒ»ÌõSQLʱ»áͨ¹ý ·Ö¿â²ßÂÔ¡¢·ÖƬ²ßÂÔ ½«Êý¾Ý·ÖÉ¢µ½²»Í¬µÄÊý¾Ý¿â¡¢±íÄÚ¡£
ÔÚÕâÀï²åÈëͼƬÃèÊö
Êý¾Ý½Úµã
Êý¾Ý½ÚµãÊÇ·Ö¿â·Ö±íÖÐÒ»¸ö²»¿ÉÔÙ·ÖµÄ×îСÊý¾Ýµ¥Ôª£¨±í£©£¬ËüÓÉÊý¾ÝÔ´Ãû³ÆºÍÊý¾Ý±í×é³É£¬ÀýÈçÉÏͼÖÐ order_db_1.t_order_0¡¢order_db_2.t_order_1 ¾Í±íʾһ¸öÊý¾Ý½Úµã¡£
Âß¼±í
Âß¼±íÊÇÖ¸Ò»×é¾ßÓÐÏàͬÂß¼ºÍÊý¾Ý½á¹¹±íµÄ×ܳơ£±ÈÈçÎÒÃǽ«¶©µ¥±ít_order ²ð·Ö³É t_order_0 ··· t_order_9 µÈ 10ÕÅ±í¡£´ËʱÎÒÃǻᷢÏÖ·Ö¿â·Ö±íÒÔºóÊý¾Ý¿âÖÐÒѲ»ÔÚÓÐ t_order ÕâÕÅ±í£¬È¡¶ø´úÖ®µÄÊÇ t_order_n£¬µ«ÎÒÃÇÔÚ´úÂëÖÐд SQL ÒÀÈ»°´ t_order À´Ð´¡£´Ëʱ t_order ¾ÍÊÇÕâЩ²ð·Ö±íµÄÂß¼±í¡£
Õæʵ±í
Õæʵ±íÒ²¾ÍÊÇÉϱßÌáµ½µÄ t_order_n Êý¾Ý¿âÖÐÕæʵ´æÔÚµÄÎïÀí±í¡£
·ÖƬ¼ü
ÓÃÓÚ·ÖƬµÄÊý¾Ý¿â×ֶΡ£ÎÒÃǽ« t_order ±í·ÖƬÒԺ󣬵±Ö´ÐÐÒ»ÌõSQLʱ£¬Í¨¹ý¶Ô×ֶΠorder_id È¡Ä£µÄ·½Ê½À´¾ö¶¨£¬ÕâÌõÊý¾Ý¸ÃÔÚÄĸöÊý¾Ý¿âÖеÄÄĸö±íÖÐÖ´ÐУ¬´Ëʱ order_id ×ֶξÍÊÇ t_order ±íµÄ·ÖƬ½¡¡£
ÔÚÕâÀï²åÈëͼƬÃèÊö
ÕâÑùÒÔÀ´Í¬Ò»¸ö¶©µ¥µÄÏà¹ØÊý¾Ý¾Í»á´æÔÚͬһ¸öÊý¾Ý¿â±íÖУ¬´ó·ùÌáÉýÊý¾Ý¼ìË÷µÄÐÔÄÜ£¬²»½öÈç´Ë sharding-jdbc »¹Ö§³Ö¸ù¾Ý¶à¸ö×Ö¶Î×÷Ϊ·ÖƬ½¡½øÐзÖƬ¡£
·ÖƬËã·¨
ÉϱßÎÒÃÇÌáµ½¿ÉÒÔÓ÷ÖƬ½¡È¡Ä£µÄ¹æÔò·ÖƬ£¬µ«ÕâÖ»ÊDZȽϼòµ¥µÄÒ»ÖÖ£¬ÔÚʵ¼Ê¿ª·¢ÖÐÎÒÃÇ»¹Ï£ÍûÓà >=¡¢<=¡¢>¡¢<¡¢BETWEEN ºÍ IN µÈÌõ¼þ×÷Ϊ·ÖƬ¹æÔò£¬×Ô¶¨Òå·ÖƬÂß¼£¬Õâʱ¾ÍÐèÒªÓõ½·ÖƬ²ßÂÔÓë·ÖƬËã·¨¡£
´ÓÖ´ÐÐ SQL µÄ½Ç¶ÈÀ´¿´£¬·Ö¿â·Ö±í¿ÉÒÔ¿´×÷ÊÇÒ»ÖÖ·ÓÉ»úÖÆ£¬°Ñ SQL Óï¾ä·Óɵ½ÎÒÃÇÆÚÍûµÄÊý¾Ý¿â»òÊý¾Ý±íÖв¢»ñÈ¡Êý¾Ý£¬·ÖƬËã·¨¿ÉÒÔÀí½â³ÉÒ»ÖÖ·ÓɹæÔò¡£
ÔÛÃÇÏÈÞÛÒ»ÏÂËüÃÇÖ®¼äµÄ¹Øϵ£¬·ÖƬ²ßÂÔÖ»ÊdzéÏó³öµÄ¸ÅÄËüÊÇÓÉ·ÖƬËã·¨ºÍ·ÖƬ½¡×éºÏ¶ø³É£¬·ÖƬËã·¨×ö¾ßÌåµÄÊý¾Ý·ÖƬÂß¼¡£
·Ö¿â¡¢·Ö±íµÄ·ÖƬ²ßÂÔÅäÖÃÊÇÏà¶Ô¶ÀÁ¢µÄ£¬¿ÉÒÔ¸÷×ÔʹÓò»Í¬µÄ²ßÂÔÓëËã·¨£¬Ã¿ÖÖ²ßÂÔÖпÉÒÔÊǶà¸ö·ÖƬËã·¨µÄ×éºÏ£¬Ã¿¸ö·ÖƬËã·¨¿ÉÒÔ¶Ô¶à¸ö·ÖƬ½¡×öÂß¼Åжϡ£
·ÖƬËã·¨ºÍ·ÖƬ²ßÂԵĹØϵ
×¢Ò⣺sharding-jdbc ²¢Ã»ÓÐÖ±½ÓÌṩ·ÖƬËã·¨µÄʵÏÖ£¬ÐèÒª¿ª·¢Õ߸ù¾ÝÒµÎñ×ÔÐÐʵÏÖ¡£
sharding-jdbc ÌṩÁË4ÖÖ·ÖƬËã·¨£º
1¡¢¾«È··ÖƬËã·¨
¾«È··ÖƬËã·¨£¨PreciseShardingAlgorithm£©ÓÃÓÚµ¥¸ö×Ö¶Î×÷Ϊ·ÖƬ¼ü£¬SQLÖÐÓÐ = Óë IN µÈÌõ¼þµÄ·ÖƬ£¬ÐèÒªÔÚ±ê×¼·ÖƬ²ßÂÔ£¨StandardShardingStrategy £©ÏÂʹÓá£
2¡¢·¶Î§·ÖƬËã·¨
·¶Î§·ÖƬËã·¨£¨RangeShardingAlgorithm£©ÓÃÓÚµ¥¸ö×Ö¶Î×÷Ϊ·ÖƬ¼ü£¬SQLÖÐÓÐ BETWEEN AND¡¢>¡¢<¡¢>=¡¢<= µÈÌõ¼þµÄ·ÖƬ£¬ÐèÒªÔÚ±ê×¼·ÖƬ²ßÂÔ£¨StandardShardingStrategy £©ÏÂʹÓá£
3¡¢¸´ºÏ·ÖƬËã·¨
¸´ºÏ·ÖƬËã·¨£¨ComplexKeysShardingAlgorithm£©ÓÃÓÚ¶à¸ö×Ö¶Î×÷Ϊ·ÖƬ¼üµÄ·ÖƬ²Ù×÷£¬Í¬Ê±»ñÈ¡µ½¶à¸ö·ÖƬ½¡µÄÖµ£¬¸ù¾Ý¶à¸ö×ֶδ¦ÀíÒµÎñÂß¼¡£ÐèÒªÔÚ¸´ºÏ·ÖƬ²ßÂÔ£¨ComplexShardingStrategy £©ÏÂʹÓá£
4¡¢Hint·ÖƬËã·¨
Hint·ÖƬËã·¨£¨HintShardingAlgorithm£©ÉÔÓв»Í¬£¬ÉϱߵÄËã·¨ÖÐÎÒÃǶ¼ÊǽâÎöSQL Óï¾äÌáÈ¡·ÖƬ¼ü£¬²¢ÉèÖ÷ÖƬ²ßÂÔ½øÐзÖƬ¡£µ«ÓÐЩʱºòÎÒÃDz¢Ã»ÓÐʹÓÃÈκεķÖƬ¼üºÍ·ÖƬ²ßÂÔ£¬¿É»¹Ï뽫 SQL ·Óɵ½Ä¿±êÊý¾Ý¿âºÍ±í£¬¾ÍÐèҪͨ¹ýÊÖ¶¯¸ÉÔ¤Ö¸¶¨SQLµÄÄ¿±êÊý¾Ý¿âºÍ±íÐÅÏ¢£¬ÕâÒ²½ÐÇ¿ÖÆ·ÓÉ¡£
·ÖƬ²ßÂÔ
Éϱ߽²·ÖƬËã·¨µÄʱºòÒѾ˵¹ý£¬·ÖƬ²ßÂÔÊÇÒ»ÖÖ³éÏóµÄ¸ÅÄʵ¼Ê·ÖƬ²Ù×÷µÄÊÇÓÉ·ÖƬËã·¨ºÍ·ÖƬ½¡À´Íê³ÉµÄ¡£
1¡¢±ê×¼·ÖƬ²ßÂÔ
±ê×¼·ÖƬ²ßÂÔÊÊÓÃÓÚµ¥·ÖƬ¼ü£¬´Ë²ßÂÔÖ§³Ö PreciseShardingAlgorithm ºÍ RangeShardingAlgorithm Á½¸ö·ÖƬËã·¨¡£
ÆäÖÐ PreciseShardingAlgorithm ÊDZØÑ¡µÄ£¬ÓÃÓÚ´¦Àí = ºÍ IN µÄ·ÖƬ¡£RangeShardingAlgorithm ÊÇ¿ÉÑ¡µÄ£¬ÓÃÓÚ´¦ÀíBETWEEN AND£¬ >£¬ <£¬>=£¬<= Ìõ¼þ·ÖƬ£¬Èç¹û²»ÅäÖÃRangeShardingAlgorithm£¬SQLÖеÄÌõ¼þµÈ½«°´ÕÕÈ«¿â·ÓÉ´¦Àí¡£
2¡¢¸´ºÏ·ÖƬ²ßÂÔ
¸´ºÏ·ÖƬ²ßÂÔ£¬Í¬ÑùÖ§³Ö¶Ô SQLÓï¾äÖÐµÄ =£¬>£¬ <£¬ >=£¬ <=£¬INºÍ BETWEEN AND µÄ·ÖƬ²Ù×÷¡£²»Í¬µÄÊÇËüÖ§³Ö¶à·ÖƬ¼ü£¬¾ßÌå·ÖÅäƬϸ½ÚÍêÈ«ÓÉÓ¦Óÿª·¢ÕßʵÏÖ¡£
3¡¢Ðбí´ïʽ·ÖƬ²ßÂÔ
Ðбí´ïʽ·ÖƬ²ßÂÔ£¬Ö§³Ö¶Ô SQLÓï¾äÖÐµÄ = ºÍ IN µÄ·ÖƬ²Ù×÷£¬µ«Ö»Ö§³Öµ¥·ÖƬ¼ü¡£ÕâÖÖ²ßÂÔͨ³£ÓÃÓÚ¼òµ¥µÄ·ÖƬ£¬²»ÐèÒª×Ô¶¨Òå·ÖƬËã·¨£¬¿ÉÒÔÖ±½ÓÔÚÅäÖÃÎļþÖнÓ×Åд¹æÔò¡£
t_order_$->{t_order_id % 4} ´ú±í t_order ¶ÔÆä×ֶΠt_order_idÈ¡Ä££¬²ð·Ö³É4ÕÅ±í£¬¶ø±íÃû·Ö±ðÊÇt_order_0 µ½ t_order_3¡£
4¡¢Hint·ÖƬ²ßÂÔ
Hint·ÖƬ²ßÂÔ£¬¶ÔÓ¦ÉϱߵÄHint·ÖƬËã·¨£¬Í¨¹ýÖ¸¶¨·ÖƬ½¡¶ø·Ç´Ó SQLÖÐÌáÈ¡·ÖƬ½¡µÄ·½Ê½½øÐзÖƬµÄ²ßÂÔ¡£
·Ö²¼Ê½Ö÷¼ü
Êý¾Ý·Ö⽚ºó£¬²»Í¬Êý¾Ý½Úµã⽣³ÉÈ«¾ÖΨ⼀Ö÷¼üÊÇ⾮³£¼¬⼿µÄÎÊÌ⣬ͬ⼀¸öÂß¼±í£¨t_order£©ÄڵIJ»Í¬Õæʵ±í£¨t_order_n£©Ö®¼äµÄ⾃Ôö¼üÓÉÓÚ⽆·¨»¥Ïà¸ÐÖª¶ø²ú⽣Öظ´Ö÷¼ü¡£
¾¡¹Ü¿Éͨ¹ýÉèÖÃ⾃ÔöÖ÷¼ü ³õʼֵ ºÍ ²½⻓ µÄ⽅ʽ±ÜÃâIDÅöײ£¬µ«ÕâÑù»áʹά»¤³É±¾¼Ó´ó£¬·¦ÍêÕûÐԺͿÉÀ©Õ¹ÐÔ¡£Èç¹ûºóÈ¥ÐèÒªÔö¼Ó·ÖƬ±íµÄÊýÁ¿£¬ÒªÖðÒ»Ð޸ķÖƬ±íµÄ²½³¤£¬ÔËά³É±¾·Ç³£¸ß£¬ËùÒÔ²»½¨ÒéÕâÖÖ·½Ê½¡£
ʵÏÖ·Ö²¼Ê½Ö÷¼ü⽣³ÉÆ÷µÄ·½Ê½ºÜ¶à£¬¿ÉÒԲο¼ÎÒ֮ǰдµÄ¡¶9ÖÖ·Ö²¼Ê½IDÉú³É·½Ê½¡·¡£
ΪÁËÈÃÉÏÊÖ¸ü¼Ó¼òµ¥£¬ApacheShardingSphere ÄÚÖÃÁËUUID¡¢SNOWFLAKE Á½ÖÖ·Ö²¼Ê½Ö÷¼ü⽣³ÉÆ÷£¬Ä¬ÈÏʹ⽤Ñ©»¨Ëã·¨£¨snowflake£©⽣³É64bitµÄ⻓ÕûÐÍÊý¾Ý¡£²»½öÈç´ËËü»¹³éÀë³ö·Ö²¼Ê½Ö÷¼ü⽣³ÉÆ÷µÄ½Ó¿Ú£¬⽅±ãÎÒÃÇʵÏÖ⾃¶¨ÒåµÄ⾃ÔöÖ÷¼ü⽣³ÉËã·¨¡£
¹ã²¥±í
¹ã²¥±í£º´æÔÚÓÚËùÓеķÖƬÊý¾ÝÔ´ÖÐµÄ±í£¬±í½á¹¹ºÍ±íÖеÄÊý¾ÝÔÚÿ¸öÊý¾Ý¿âÖоùÍêÈ«Ò»Ö¡£Ò»°ãÊÇΪ×Öµä±í»òÕßÅäÖñí t_config£¬Ä³¸ö±íÒ»µ©±»ÅäÖÃΪ¹ã²¥±í£¬Ö»ÒªÐÞ¸Äij¸öÊý¾Ý¿âµÄ¹ã²¥±í£¬ËùÓÐÊý¾ÝÔ´Öй㲥±íµÄÊý¾Ý¶¼»á¸ú×Åͬ²½¡£
°ó¶¨±í
°ó¶¨±í£ºÄÇЩ·ÖƬ¹æÔòÒ»ÖµÄÖ÷±íºÍ×Ó±í¡£±ÈÈ磺t_order ¶©µ¥±íºÍ t_order_item ¶©µ¥·þÎñÏîÄ¿±í£¬¶¼ÊÇ°´ order_id ×ֶηÖƬ£¬Òò´ËÁ½ÕÅ±í»¥Îª°ó¶¨±í¹Øϵ¡£
Äǰ󶨱í´æÔÚµÄÒâÒåÊÇɶÄØ£¿
ͨ³£ÔÚÎÒÃǵÄÒµÎñÖж¼»áʹÓà t_order ºÍ t_order_item µÈ±í½øÐжà±íÁªºÏ²éѯ£¬µ«ÓÉÓÚ·Ö¿â·Ö±íÒÔºóÕâЩ±í±»²ð·Ö³ÉN¶à¸ö×Ó±í¡£Èç¹û²»ÅäÖð󶨱í¹Øϵ£¬»á³öÏֵѿ¨¶û»ý¹ØÁª²éѯ£¬½«²úÉúÈçÏÂËÄÌõSQL¡£
- SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id
- SELECT * FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id
- SELECT * FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id
- SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id
µÑ¿¨¶û»ý²éѯ
¶øÅäÖð󶨱í¹ØϵºóÔÙ½øÐйØÁª²éѯʱ£¬Ö»Òª¶ÔÓ¦±í·ÖƬ¹æÔòÒ»Ö²úÉúµÄÊý¾Ý¾Í»áÂ䵽ͬһ¸ö¿âÖУ¬ÄÇôֻÐè t_order_0 ºÍ t_order_item_0 ±í¹ØÁª¼´¿É¡£
- SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id
- SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id
°ó¶¨±í¹Øϵ
×¢Ò⣺ÔÚ¹ØÁª²éѯʱ t_order Ëü×÷ΪÕû¸öÁªºÏ²éѯµÄÖ÷±í¡£ËùÓÐÏà¹ØµÄ·ÓɼÆË㶼ֻʹÓÃÖ÷±íµÄ²ßÂÔ£¬t_order_item ±íµÄ·ÖƬÏà¹ØµÄ¼ÆËãÒ²»áʹÓà t_order µÄÌõ¼þ£¬ËùÒÔÒª±£Ö¤°ó¶¨±íÖ®¼äµÄ·ÖƬ¼üÒªÍêÈ«Ïàͬ¡£
Èý¡¢ºÍJDBCµÄèÄå
´ÓÃû×ÖÉϲ»ÄÑ¿´³ö£¬Sharding-JDBC ºÍ JDBCÓкܴó¹Øϵ£¬ÎÒÃÇÖªµÀ JDBC ÊÇÒ»ÖÖ Java ÓïÑÔ·ÃÎʹØϵÐÍÊý¾Ý¿âµÄ¹æ·¶£¬ÆäÉè¼Æ³õÖÔ¾ÍÊÇÒªÌṩһÌ×ÓÃÓÚ¸÷ÖÖÊý¾Ý¿âµÄͳһ±ê×¼£¬²»Í¬³§¼Ò¹²Í¬×ñÊØÕâÌ×±ê×¼£¬²¢Ìṩ¸÷×ÔµÄʵÏÖ·½°¸¹©Ó¦ÓóÌÐòµ÷Óá£
ÔÚÕâÀï²åÈëͼƬÃèÊö
µ«Æäʵ¶ÔÓÚ¿ª·¢ÈËÔ±¶øÑÔ£¬ÎÒÃÇÖ»¹ØÐÄÈçºÎµ÷Óà JDBC API À´·ÃÎÊÊý¾Ý¿â£¬Ö»ÒªÕýȷʹÓà DataSource¡¢Connection¡¢Statement ¡¢ResultSet µÈ API ½Ó¿Ú£¬Ö±½Ó²Ù×÷Êý¾Ý¿â¼´¿É¡£ËùÒÔÈç¹ûÏëÔÚ JDBC ²ãÃæʵÏÖÊý¾Ý·ÖƬ¾Í±ØÐë¶ÔÏÖÓÐµÄ API ½øÐй¦ÄÜÍØÕ¹£¬¶ø Sharding-JDBC ÕýÊÇ»ùÓÚÕâÖÖ˼Ï룬ÖØдÁË JDBC ¹æ·¶²¢ÍêÈ«¼æÈÝÁË JDBC ¹æ·¶¡£
JDBCÁ÷³Ì
¶ÔÔÓÐµÄ DataSource¡¢Connection µÈ½Ó¿ÚÀ©Õ¹³É ShardingDataSource¡¢ShardingConnection£¬¶ø¶ÔÍⱩ¶µÄ·ÖƬ²Ù×÷½Ó¿ÚÓë JDBC ¹æ·¶ÖÐËùÌṩµÄ½Ó¿ÚÍêÈ«Ò»Ö£¬Ö»ÒªÄãÊìϤ JDBC ¾Í¿ÉÒÔÇáËÉÓ¦Óà Sharding-JDBC À´ÊµÏÖ·Ö¿â·Ö±í¡£
Òò´ËËüÊÊÓÃÓÚÈκλùÓÚ JDBC µÄ ORM ¿ò¼Ü£¬È磺JPA£¬ Hibernate£¬Mybatis£¬Spring JDBC Template »òÖ±½ÓʹÓÃµÄ JDBC¡£ÍêÃÀ¼æÈÝÈκεÚÈý·½µÄÊý¾Ý¿âÁ¬½Ó³Ø£¬È磺DBCP£¬ C3P0£¬ BoneCP£¬Druid£¬ HikariCP µÈ£¬¼¸ºõ¶ÔÖ÷Á÷¹ØϵÐÍÊý¾Ý¿â¶¼Ö§³Ö¡£
ÄÇ Sharding-JDBC ÓÖÊÇÈçºÎÍØÕ¹ÕâЩ½Ó¿ÚµÄÄØ£¿ÏëÖªµÀ´ð°¸ÎÒÃǾ͵ĴÓÔ´ÂëÈëÊÖÁË£¬Ï±ßÎÒÃÇÒÔ JDBC API ÖÐµÄ DataSource ΪÀý¿´¿´ËüÊÇÈçºÎ±»ÖØдÀ©Õ¹µÄ¡£
Êý¾ÝÔ´ DataSource ½Ó¿ÚµÄºËÐÄ×÷ÓþÍÊÇ»ñÈ¡Êý¾Ý¿âÁ¬½Ó¶ÔÏó Connection£¬ÎÒÃÇ¿´ÆäÄÚ²¿ÌṩÁËÁ½¸ö»ñÈ¡Êý¾Ý¿âÁ¬½ÓµÄ·½·¨ £¬²¢ÇҼ̳ÐÁË CommonDataSource ºÍ Wrapper Á½¸ö½Ó¿Ú¡£
- public interface DataSource extends CommonDataSource, Wrapper {
- /**
- * <p>Attempts to establish a connection with the data source that
- * this {@code DataSource} object represents.
- * @return a connection to the data source
- */
- Connection getConnection() throws SQLException;
- /**
- * <p>Attempts to establish a connection with the data source that
- * this {@code DataSource} object represents.
- * @param username the database user on whose behalf the connection is
- * being made
- * @param password the user's password
- */
- Connection getConnection(String username, String password)
- throws SQLException;
- }
ÆäÖÐ CommonDataSource ÊǶ¨ÒåÊý¾ÝÔ´µÄ¸ù½Ó¿ÚÕâºÜºÃÀí½â£¬¶ø Wrapper ½Ó¿ÚÔòÊÇÍØÕ¹ JDBC ·ÖƬ¹¦ÄܵĹؼü¡£
ÓÉÓÚÊý¾Ý¿â³§É̵IJ»Í¬£¬ËûÃÇ¿ÉÄÜ»á¸÷×ÔÌṩһЩ³¬Ô½±ê×¼ JDBC API µÄÀ©Õ¹¹¦ÄÜ£¬µ«ÕâЩ¹¦ÄÜ·Ç JDBC ±ê×¼²¢²»ÄÜÖ±½ÓʹÓ㬶ø Wrapper ½Ó¿ÚµÄ×÷ÓþÍÊÇ°ÑÒ»¸öÓɵÚÈý·½¹©Ó¦ÉÌÌṩµÄ¡¢·Ç JDBC ±ê×¼µÄ½Ó¿Ú°ü×°³É±ê×¼½Ó¿Ú£¬Ò²¾ÍÊÇÊÊÅäÆ÷ģʽ¡£
¼ÈÈ»½²µ½ÁËÊÊÅäÆ÷ģʽ¾Í¶à†ªà¼¸¾ä£¬Ò²·½±ãºó±ßµÄÀí½â¡£
ÊÊÅäÆ÷ģʽ¸öÖֱȽϳ£ÓõÄÉè¼Æģʽ£¬ËüµÄ×÷ÓÃÊǽ«Ä³¸öÀàµÄ½Ó¿Úת»»³É¿Í»§¶ËÆÚÍûµÄÁíÒ»¸ö½Ó¿Ú£¬Ê¹Ô±¾Òò½Ó¿Ú²»Æ¥Å䣨»òÕß²»¼æÈÝ£©¶øÎÞ·¨ÔÚÒ»Æð¹¤×÷µÄÁ½¸öÀàÄܹ»ÔÚÒ»Æð¹¤×÷¡£±ÈÈçÓöú»úÌýÒôÀÖ£¬ÎÒÓиöԲͷµÄ¶ú»ú£¬¿ÉÊÖ»ú²å¿×È´ÊDZâ¿ÚµÄ£¬Èç¹ûÎÒÏëҪʹÓöú»úÌýÒôÀ־ͱØÐë½èÖúÒ»¸öת½ÓÍ·²Å¿ÉÒÔ£¬Õâ¸öת½ÓÍ·¾ÍÆðµ½ÁËÊÊÅä×÷Ó᣾ٸöÀõ×Ó£º¼ÙÈçÎÒÃÇ Target ½Ó¿ÚÖÐÓÐ hello() ºÍ word() Á½¸ö·½·¨¡£
- public interface Target {
- void hello();
- void world();
- }
¿ÉÓÉÓÚ½Ó¿Ú°æ±¾µü´úTarget ½Ó¿ÚµÄ word() ·½·¨¿ÉÄܻᱻ·ÏÆúµô»ò²»±»Ö§³Ö£¬Adaptee ÀàµÄ greet()·½·¨½«´úÌæhello() ·½·¨¡£
- public class Adaptee {
- public void greet(){
- }
- public void world(){
- }
- }
µ«´Ëʱ¾É°æ±¾ÈÔÈ»ÓдóÁ¿ word() ·½·¨±»Ê¹ÓÃÖУ¬½â¾ö´ËÊÂ×îºÃµÄ°ì·¨¾ÍÊÇ´´½¨Ò»¸öÊÊÅäÆ÷Adapter£¬ÕâÑù¾ÍÊÊÅäÁË Target À࣬½â¾öÁ˽ӿÚÉý¼¶´øÀ´µÄ¼æÈÝÐÔÎÊÌâ¡£
- public class Adapter extends Adaptee implements Target {
- @Override
- public void world() {
- }
- @Override
- public void hello() {
- super.greet();
- }
- @Override
- public void greet() {
- }
- }
¶ø Sharding-JDBC ÌṩµÄÕýÊÇ·Ç JDBC ±ê×¼µÄ½Ó¿Ú£¬ËùÒÔËüÒ²ÌṩÁËÀàËƵÄʵÏÖ·½°¸£¬Ò²Ê¹Óõ½ÁË Wrapper ½Ó¿Ú×öÊý¾Ý·ÖƬ¹¦ÄܵÄÊÊÅä¡£³ýÁË DataSource Ö®Í⣬Connection¡¢Statement¡¢ResultSet µÈºËÐĶÔÏóÒ²¶¼¼Ì³ÐÁËÕâ¸ö½Ó¿Ú¡£
ÏÂÃæÎÒÃÇͨ¹ý ShardingDataSource ÀàÔ´Âë¼òµ¥¿´ÏÂʵÏÖ¹ý³Ì£¬ÏÂͼÊǼ̳йØϵÁ÷³Ìͼ¡£
ShardingDataSourceʵÏÖÁ÷³Ì
ShardingDataSource ÀàËüÔÚÔ DataSource »ù´¡ÉÏ×öÁ˹¦ÄÜÍØÕ¹£¬³õʼ»¯Ê±×¢²áÁË·ÖƬSQL·ÓÉ°ü×°Æ÷¡¢SQLÖØдÉÏÏÂÎĺͽá¹û¼¯´¦ÀíÒýÇ棬»¹¶ÔÊý¾ÝÔ´ÀàÐÍ×öÁËУÑ飬ÒòΪËüҪͬʱ֧³Ö¶à¸ö²»Í¬ÀàÐ͵ÄÊý¾ÝÔ´¡£µ½ÕâºÃÏñҲû¿´³öÈçºÎÊÊÅ䣬ÄǽÓ×ÅÏòÉÏ¿´ ShardingDataSource µÄ¼Ì³ÐÀà AbstractDataSourceAdapter ¡£
- @Getter
- public class ShardingDataSource extends AbstractDataSourceAdapter {
- private final ShardingRuntimeContext runtimeContext;
- /**
- * ×¢²á·ÓÉ¡¢SQlÖØдÉÏÏÂÎÄ¡¢½á¹û¼¯´¦ÀíÒýÇæ
- */
- static {
- NewInstanceServiceLoader.register(RouteDecorator.class);
- NewInstanceServiceLoader.register(SQLRewriteContextDecorator.class);
- NewInstanceServiceLoader.register(ResultProcessEngine.class);
- }
- /**
- * ³õʼ»¯Ê±Ð£ÑéÊý¾ÝÔ´ÀàÐÍ ²¢¸ù¾ÝÊý¾ÝÔ´ map¡¢·ÖƬ¹æÔò¡¢Êý¾Ý¿âÀàÐ͵õ½Ò»¸ö·ÖƬÉÏÏÂÎÄ£¬ÓÃÀ´»ñÈ¡Êý¾Ý¿âÁ¬½Ó
- */
- public ShardingDataSource(final Map<String, DataSource> dataSourceMap, final ShardingRule shardingRule, final Properties props) throws SQLException {
- super(dataSourceMap);
- checkDataSourceType(dataSourceMap);
- runtimeContext = new ShardingRuntimeContext(dataSourceMap, shardingRule, props, getDatabaseType());
- }
- private void checkDataSourceType(final Map<String, DataSource> dataSourceMap) {
- for (DataSource each : dataSourceMap.values()) {
- Preconditions.checkArgument(!(each instanceof MasterSlaveDataSource), "Initialized data sources can not be master-slave data sources.");
- }
- }
- /**
- * Êý¾Ý¿âÁ¬½Ó
- */
- @Override
- public final ShardingConnection getConnection() {
- return new ShardingConnection(getDataSourceMap(), runtimeContext, TransactionTypeHolder.get());
- }
- }
AbstractDataSourceAdapter ³éÏóÀàÄÚ²¿Ö÷Òª»ñÈ¡²»Í¬ÀàÐ͵ÄÊý¾ÝÔ´¶ÔÓ¦µÄÊý¾Ý¿âÁ¬½Ó¶ÔÏó£¬ÊµÏÖ AutoCloseable ½Ó¿ÚÊÇΪÔÚʹÓÃÍê×ÊÔ´ºó¿ÉÒÔ×Ô¶¯½«ÕâЩ×ÊÔ´¹Ø±Õ£¨µ÷Óà close·½·¨£©£¬ÄÇÔÙ¿´¿´¼Ì³ÐÀà AbstractUnsupportedOperationDataSource ¡£
- @Getter
- public abstract class AbstractDataSourceAdapter extends AbstractUnsupportedOperationDataSource implements AutoCloseable {
- private final Map<String, DataSource> dataSourceMap;
- private final DatabaseType databaseType;
- public AbstractDataSourceAdapter(final Map<String, DataSource> dataSourceMap) throws SQLException {
- this.dataSourceMap = dataSourceMap;
- databaseType = createDatabaseType();
- }
- public AbstractDataSourceAdapter(final DataSource dataSource) throws SQLException {
- dataSourceMap = new HashMap<>(1, 1);
- dataSourceMap.put("unique", dataSource);
- databaseType = createDatabaseType();
- }
- private DatabaseType createDatabaseType() throws SQLException {
- DatabaseType result = null;
- for (DataSource each : dataSourceMap.values()) {
- DatabaseType databaseType = createDatabaseType(each);
- Preconditions.checkState(null == result || result == databaseType, String.format("Database type inconsistent with '%s' and '%s'", result, databaseType));
- result = databaseType; }
- return result;
- }
- /**
- * ²»Í¬Êý¾ÝÔ´ÀàÐÍ»ñÈ¡Êý¾Ý¿âÁ¬½Ó
- */
- private DatabaseType createDatabaseType(final DataSource dataSource) throws SQLException {
- if (dataSource instanceof AbstractDataSourceAdapter) {
- return ((AbstractDataSourceAdapter) dataSource).databaseType;
- }
- try (Connection connection = dataSource.getConnection()) {
- return DatabaseTypes.getDatabaseTypeByURL(connection.getMetaData().getURL());
- }
- }
- @Override
- public final Connection getConnection(final String username, final String password) throws SQLException {
- return getConnection();
- }
- @Override
- public final void close() throws Exception {
- close(dataSourceMap.keySet());
- }
- }
AbstractUnsupportedOperationDataSource ʵÏÖDataSource ½Ó¿Ú²¢¼Ì³ÐÁË WrapperAdapter À࣬ËüÄÚ²¿²¢Ã»ÓÐʲô¾ßÌå·½·¨Ö»Æðµ½ÇŽӵÄ×÷Ó㬵«¿´×ÅÊDz»ÊǺÍÎÒÃÇÇ°±ß½²ÊÊÅäÆ÷ģʽµÄÀý×Ó·½Ê½ÓеãÏàËÆ¡£
- public abstract class AbstractUnsupportedOperationDataSource extends WrapperAdapter implements DataSource {
- @Override
- public final int getLoginTimeout() throws SQLException {
- throw new SQLFeatureNotSupportedException("unsupported getLoginTimeout()");
- }
- @Override
- public final void setLoginTimeout(final int seconds) throws SQLException {
- throw new SQLFeatureNotSupportedException("unsupported setLoginTimeout(int seconds)");
- }
- }
WrapperAdapter ÊÇÒ»¸ö°ü×°Æ÷µÄÊÊÅäÀ࣬ʵÏÖÁË JDBC ÖÐµÄ Wrapper ½Ó¿Ú£¬ÆäÖÐÓÐÁ½¸öºËÐÄ·½·¨ recordMethodInvocation ÓÃÓÚÌí¼ÓÐèÒªÖ´Ðеķ½·¨ºÍ²ÎÊý£¬¶ø replayMethodsInvocation Ôò½«Ìí¼ÓµÄÕâЩ·½·¨ºÍ²ÎÊýͨ¹ý·´ÉäÖ´ÐС£×Ðϸ¿´²»ÄÑ·¢ÏÖÁ½¸ö·½·¨Öж¼Óõ½ÁË JdbcMethodInvocationÀà¡£
- public abstract class WrapperAdapter implements Wrapper {
- private final Collection<JdbcMethodInvocation> jdbcMethodInvocations = new ArrayList<>();
- /**
- * Ìí¼ÓÒªÖ´Ðеķ½·¨
- */
- @SneakyThrows
- public final void recordMethodInvocation(final Class<?> targetClass, final String methodName, final Class<?>[] argumentTypes, final Object[] arguments) {
- jdbcMethodInvocations.add(new JdbcMethodInvocation(targetClass.getMethod(methodName, argumentTypes), arguments));
- }
- /**
- * ͨ¹ý·´ÉäÖ´ÐÐ ÉϱßÌí¼ÓµÄ·½·¨
- */
- public final void replayMethodsInvocation(final Object target) {
- for (JdbcMethodInvocation each : jdbcMethodInvocations) {
- each.invoke(target);
- }
- }
- }
JdbcMethodInvocation ÀàÖ÷ÒªÓ¦Ó÷´Éäͨ¹ý´«ÈëµÄ method ·½·¨ºÍ arguments ²ÎÊýÖ´ÐжÔÓ¦µÄ·½·¨£¬ÕâÑù¾Í¿ÉÒÔͨ¹ý JDBC API µ÷ÓÃ·Ç JDBC ·½·¨ÁË¡£
- @RequiredArgsConstructor
- public class JdbcMethodInvocation {
- @Getter
- private final Method method;
- @Getter
- private final Object[] arguments;
- /**
- * Invoke JDBC method.
- *
- * @param target target object
- */
- @SneakyThrows
- public void invoke(final Object target) {
- method.invoke(target, arguments);
- }
- }
ÄÇ Sharding-JDBC ÍØÕ¹ JDBC API ½Ó¿Úºó£¬ÔÚÐÂÔöµÄ·ÖƬ¹¦ÄÜÀïÓÖ×öÁËÄÄЩÊÂÇéÄØ£¿
Ò»ÕÅ±í¾¹ý·Ö¿â·Ö±íºó±»²ð·Ö³É¶à¸ö×Ó±í£¬²¢·ÖÉ¢µ½²»Í¬µÄÊý¾Ý¿âÖУ¬ÔÚ²»ÐÞ¸ÄÔÒµÎñ SQL µÄÇ°ÌáÏ£¬Sharding-JDBC ¾Í±ØÐë¶Ô SQL½øÐÐһЩ¸ÄÔì²ÅÄÜÕý³£Ö´ÐС£
´óÖµÄÖ´ÐÐÁ÷³Ì£ºSQL ½âÎö -> Ö´⾏Æ÷ÓÅ»¯ -> SQL ·ÓÉ -> SQL ¸Äд -> SQL Ö´⾏ -> ½á¹û¹é²¢ Áù²½×é³É£¬Ò»Æð³ò³òÿ¸ö²½Öè×öÁ˵ãʲô¡£
SQL ½âÎö
SQL½âÎö¹ý³Ì·ÖΪ´Ê·¨½âÎöºÍÓï·¨½âÎöÁ½²½£¬±ÈÈçϱßÕâÌõ²éѯÓû§¶©µ¥µÄSQL£¬ÏÈÓôʷ¨½âÎö½«SQL²ð½â³É²»¿ÉÔÙ·ÖµÄÔ×Óµ¥Ôª¡£ÔÚ¸ù¾Ý²»Í¬Êý¾Ý¿â·½ÑÔËùÌṩµÄ×ֵ䣬½«ÕâЩµ¥Ôª¹éÀàΪ¹Ø¼ü×Ö£¬±í´ïʽ£¬±äÁ¿»òÕß²Ù×÷·ûµÈÀàÐÍ¡£
- SELECT order_no,price FROM t_order_ where user_id = 10086 and order_status > 0
½Ó×ÅÓï·¨½âÎö»á½«²ð·ÖºóµÄSQLת»»Îª³éÏóÓï·¨Ê÷£¬Í¨¹ý¶Ô³éÏóÓï·¨Ê÷±éÀú£¬ÌáÁ¶³ö·ÖƬËùÐèµÄÉÏÏÂÎÄ£¬ÉÏÏÂÎÄ°üº¬²éѯ×Ö¶ÎÐÅÏ¢£¨Field£©¡¢±íÐÅÏ¢£¨Table£©¡¢²éѯÌõ¼þ£¨Condition£©¡¢ÅÅÐòÐÅÏ¢£¨Order By£©¡¢·Ö×éÐÅÏ¢£¨Group By£©ÒÔ¼°·ÖÒ³ÐÅÏ¢£¨Limit£©µÈ£¬²¢±ê¼Ç³ö SQLÖÐÓпÉÄÜÐèÒª¸ÄдµÄλÖá£
³éÏóÓï·¨Ê÷
Ö´⾏Æ÷ÓÅ»¯
Ö´⾏Æ÷ÓÅ»¯¶ÔSQL·ÖƬÌõ¼þ½øÐÐÓÅ»¯£¬´¦ÀíÏñ¹Ø¼ü×Ö ORÕâÖÖÓ°ÏìÐÔÄܵĻµÎ¶µÀ¡£
SQL ·ÓÉ
SQL ·ÓÉͨ¹ý½âÎö·ÖƬÉÏÏÂÎÄ£¬Æ¥Åäµ½Óû§ÅäÖõķÖƬ²ßÂÔ£¬²¢Éú³É·ÓÉ·¾¶¡£¼òµ¥µãÀí½â¾ÍÊÇ¿ÉÒÔ¸ù¾ÝÎÒÃÇÅäÖõķÖƬ²ßÂÔ¼ÆËã³ö SQL¸ÃÔÚÄĸö¿âµÄÄĸö±íÖÐÖ´ÐУ¬¶øSQL·ÓÉÓÖ¸ù¾ÝÓÐÎÞ·ÖƬ½¡Çø·Ö³ö ·ÖƬ·ÓÉ ºÍ ¹ã²¥Â·ÓÉ¡£
¹Ù·½Â·ÓÉͼÆ×
ÓзÖ⽚¼üµÄ·ÓɽзÖƬ·ÓÉ£¬Ï¸·ÖΪֱ½Ó·ÓÉ¡¢±ê׼·Óɺ͵ѿ¨¶û»ý·ÓÉÕâ3ÖÖÀàÐÍ¡£
±ê׼·ÓÉ
±ê׼·ÓÉÊÇ×îÍƼöÒ²ÊÇ×îΪ³£⽤µÄ·Ö⽚⽅ʽ£¬ËüµÄÊÊ⽤·¶Î§ÊDz»°üº¬¹ØÁª²éѯ»ò½ö°üº¬°ó¶¨±íÖ®¼ä¹ØÁª²éѯµÄSQL¡£
µ± SQL·ÖƬ½¡µÄÔËËã·ûΪ = ʱ£¬Â·Óɽá¹û½«Âä⼊µ¥¿â£¨±í£©£¬µ±·Ö⽚ÔËËã·ûÊÇBETWEEN »òIN µÈ·¶Î§Ê±£¬Â·Óɽá¹ûÔò²»⼀¶¨Âä⼊Ψ⼀µÄ¿â£¨±í£©£¬Òò´Ë⼀ÌõÂß¼SQL×îÖÕ¿ÉÄܱ»²ð·ÖΪ¶àÌõ⽤ÓÚÖ´⾏µÄÕæʵSQL¡£
- SELECT * FROM t_order where t_order_id in (1,2)
SQL·ÓÉ´¦Àíºó £º
- SELECT * FROM t_order_0 where t_order_id in (1,2)
- SELECT * FROM t_order_1 where t_order_id in (1,2)
Ö±½Ó·ÓÉ
Ö±½Ó·ÓÉÊÇͨ¹ýʹÓà HintAPI Ö±½Ó½« SQL·Óɵ½Ö¸¶¨⾄¿â±íµÄÒ»ÖÖ·Ö⽚·½Ê½£¬¶øÇÒÖ±½Ó·ÓÉ¿ÉÒÔ⽤ÓÚ·Ö⽚¼ü²»ÔÚSQLÖеij¡¾°£¬»¹¿ÉÒÔÖ´⾏°üÀ¨⼦²éѯ¡¢⾃¶¨Ò庯ÊýµÈ¸´ÔÓÇé¿öµÄÈÎÒâSQL¡£
±ÈÈç¸ù¾Ý t_order_id ×Ö¶ÎΪÌõ¼þ²éѯ¶©µ¥£¬´ËʱϣÍûÔÚ²»ÐÞ¸ÄSQLµÄÇ°ÌáÏ£¬¼ÓÉÏ user_id×÷Ϊ·ÖƬÌõ¼þ¾Í¿ÉÒÔʹÓÃÖ±½Ó·ÓÉ¡£
µÑ¿¨¶û»ý·ÓÉ
µÑ¿¨¶û·ÓÉÊÇÓÉ⾮°ó¶¨±íÖ®¼äµÄ¹ØÁª²éѯ²úÉúµÄ£¬²éѯÐÔÄܽϵ;¡Á¿±ÜÃâ×ß´Ë·ÓÉģʽ¡£
ÎÞ·Ö⽚¼üµÄ·ÓÉÓÖ½Ð×ö¹ã²¥Â·ÓÉ£¬¿ÉÒÔ»®·ÖΪȫ¿â±í·ÓÉ¡¢È«¿â·ÓÉ¡¢ ȫʵÀý·ÓÉ¡¢µ¥²¥Â·ÓɺÍ×è¶Ï·ÓÉÕâ 5ÖÖÀàÐÍ¡£
È«¿â±í·ÓÉ
È«¿â±í·ÓÉÕë¶ÔµÄÊÇÊý¾Ý¿â DQLºÍ DML£¬ÒÔ¼° DDLµÈ²Ù×÷£¬µ±ÎÒÃÇÖ´ÐÐÒ»ÌõÂß¼±í t_order SQLʱ£¬ÔÚËùÓзÖƬ¿âÖжÔÓ¦µÄÕæʵ±í t_order_0 ··· t_order_n ÄÚÖðÒ»Ö´ÐС£
È«¿â·ÓÉ
È«¿â·ÓÉÖ÷ÒªÊǶÔÊý¾Ý¿â²ãÃæµÄ²Ù×÷£¬±ÈÈçÊý¾Ý¿â SET ÀàÐ͵ÄÊý¾Ý¿â¹ÜÀíÃüÁÒÔ¼° TCL ÕâÑùµÄÊÂÎñ¿ØÖÆÓï¾ä¡£
¶ÔÂß¼¿âÉèÖà autocommit ÊôÐÔºó£¬ËùÓжÔÓ¦µÄÕæʵ¿âÖж¼Ö´ÐиÃÃüÁî¡£
- SET autocommit=0;
ȫʵÀý·ÓÉ
ȫʵÀý·ÓÉÊÇÕë¶ÔÊý¾Ý¿âʵÀýµÄ DCL ²Ù×÷£¨ÉèÖûò¸ü¸ÄÊý¾Ý¿âÓû§»ò½ÇɫȨÏÞ£©£¬±ÈÈ磺´´½¨Ò»¸öÓû§ order £¬Õâ¸öÃüÁÔÚËùÓеÄÕæʵ¿âʵÀýÖÐÖ´ÐУ¬ÒÔ´ËÈ·±£ order Óû§¿ÉÒÔÕý³£·ÃÎÊÿһ¸öÊý¾Ý¿âʵÀý¡£
- CREATE USER order@127.0.0.1 identified BY '³ÌÐòÔ±ÄÚµãÊÂ';
µ¥²¥Â·ÓÉ
µ¥²¥Â·ÓÉÓÃÀ´»ñȡijһÕæʵ±íÐÅÏ¢£¬±ÈÈç»ñµÃ±íµÄÃèÊöÐÅÏ¢£º
- DESCRIBE t_order;
t_order µÄÕæʵ±íÊÇ t_order_0 ···· t_order_n£¬ËûÃǵÄÃèÊö½á¹¹ÏàÍêȫͬ£¬ÎÒÃÇÖ»ÐèÔÚÈÎÒâµÄÕæʵ±íÖ´ÐÐÒ»´Î¾Í¿ÉÒÔ¡£
×è¶Ï·ÓÉ
À´ÆÁ±ÎSQL¶ÔÊý¾Ý¿âµÄ²Ù×÷£¬ÀýÈ磺
- USE order_db;
Õâ¸öÃüÁî²»»áÔÚÕæʵÊý¾Ý¿âÖÐÖ´⾏£¬ÒòΪ ShardingSphere ²É⽤µÄÊÇÂß¼ Schema£¨Êý¾Ý¿âµÄ×éÖ¯ºÍ½á¹¹£© ⽅ʽ£¬ËùÒÔÎÞÐ轫Çл»Êý¾Ý¿âµÄÃüÁî·¢ËÍ⾄ÕæʵÊý¾Ý¿âÖС£
SQL ¸Äд
½«»ùÓÚÂß¼±í¿ª·¢µÄSQL¸Äд³É¿ÉÒÔÔÚÕæʵÊý¾Ý¿âÖпÉÒÔÕýÈ·Ö´ÐеÄÓï¾ä¡£±ÈÈç²éѯ t_order ¶©µ¥±í£¬ÎÒÃÇʵ¼Ê¿ª·¢ÖÐ SQLÊÇ°´Âß¼±í t_order дµÄ¡£
- SELECT * FROM t_order
µ«·Ö¿â·Ö±íÒÔºóÕæʵÊý¾Ý¿âÖÐ t_order ±í¾Í²»´æÔÚÁË£¬¶øÊDZ»²ð·Ö³É¶à¸ö×Ó±í t_order_n ·ÖÉ¢ÔÚ²»Í¬µÄÊý¾Ý¿âÄÚ£¬»¹°´ÔSQLÖ´ÐÐÏÔÈ»ÊÇÐв»Í¨µÄ£¬ÕâʱÐèÒª½«·Ö±íÅäÖÃÖеÄÂß¼±íÃû³Æ¸ÄдΪ·ÓÉÖ®ºóËù»ñÈ¡µÄÕæʵ±íÃû³Æ¡£
- SELECT * FROM t_order_n
SQLÖ´⾏
½«Â·Óɺ͸ÄдºóµÄÕæʵ SQL °²È«ÇÒ¸ßЧ·¢Ë͵½µ×²ãÊý¾ÝÔ´Ö´ÐС£µ«Õâ¸ö¹ý³Ì²¢²»ÊǼòµ¥µÄ½« SQL ͨ¹ýJDBC Ö±½Ó·¢ËÍÖÁÊý¾ÝÔ´Ö´ÐУ¬¶øÊÇƽºâÊý¾ÝÔ´Á¬½Ó´´½¨ÒÔ¼°ÄÚ´æÕ¼ÓÃËù²úÉúµÄÏûºÄ£¬Ëü»á×Ô¶¯»¯µÄƽºâ×ÊÔ´¿ØÖÆÓëÖ´ÐÐЧÂÊ¡£
½á¹û¹é²¢
½«´Ó¸÷¸öÊý¾Ý½Úµã»ñÈ¡µÄ¶àÊý¾Ý½á¹û¼¯£¬ºÏ²¢³ÉÒ»¸ö´óµÄ½á¹û¼¯²¢ÕýÈ·µÄ·µ»ØÖÁÇëÇó¿Í»§¶Ë£¬³ÆΪ½á¹û¹é²¢¡£¶øÎÒÃÇSQLÖеÄÅÅÐò¡¢·Ö×é¡¢·ÖÒ³ºÍ¾ÛºÏµÈÓï·¨£¬¾ùÊÇÔڹ鲢ºóµÄ½á¹û¼¯ÉϽøÐвÙ×÷µÄ¡£
ËÄ¡¢¿ìËÙʵ¼ù
ÏÂÃæÎÒÃǽáºÏ Springboot + mybatisplus ¿ìËٴһ¸ö·Ö¿â·Ö±í°¸Àý¡£
1¡¢×¼±¸¹¤×÷
ÏÈ×ö×¼±¸¹¤×÷£¬´´½¨Á½¸öÊý¾Ý¿â ds-0¡¢ds-1£¬Á½¸ö¿âÖзֱ𽨱í t_order_0¡¢t_order_1¡¢t_order_2 ¡¢t_order_item_0¡¢t_order_item_1¡¢t_order_item_2£¬t_config£¬·½±ãºó±ßÑéÖ¤¹ã²¥±í¡¢°ó¶¨±íµÄ³¡¾°¡£
±í½á¹¹ÈçÏ£º
t_order_0 ¶©µ¥±í
- CREATE TABLE `t_order_0` (
- `order_id` bigint(200) NOT NULL,
- `order_no` varchar(100) DEFAULT NULL,
- `create_name` varchar(50) DEFAULT NULL,
- `price` decimal(10,2) DEFAULT NULL,
- PRIMARY KEY (`order_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
t_order_0 Óë t_order_item_0 »¥Îª¹ØÁª±í
- CREATE TABLE `t_order_item_0` (
- `item_id` bigint(100) NOT NULL,
- `order_no` varchar(200) NOT NULL,
- `item_name` varchar(50) DEFAULT NULL,
- `price` decimal(10,2) DEFAULT NULL,
- PRIMARY KEY (`item_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
¹ã²¥±í t_config
- `id` bigint(30) NOT NULL,
- `remark` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
- `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
- `last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (`id`)
- ENGINE=InnoDB DEFAULT CHARSET=latin1;
ShardingSphere ÌṩÁË4ÖÖ·ÖƬÅäÖ÷½Ê½£º
ΪÈôúÂë¿´ÉÏÈ¥¸ü¼ò½àºÍÖ±¹Û£¬ºó±ßͳһʹÓà properties ÅäÖõķ½Ê½£¬ÒýÈë shardingsphere ¶ÔÓ¦µÄ sharding-jdbc-spring-boot-starter ºÍ sharding-core-common °ü£¬°æ±¾Í³Ò»ÓÃµÄ 4.0.0-RC1¡£
2¡¢·ÖƬÅäÖÃ
- <dependency>
- <groupId>org.apache.shardingsphere</groupId>
- <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
- <version>4.0.0-RC1</version>
- </dependency>
- <dependency>
- <groupId>org.apache.shardingsphere</groupId>
- <artifactId>sharding-core-common</artifactId>
- <version>4.0.0-RC1</version>
- </dependency>
×¼±¸¹¤×÷×öÍ꣨ mybatis ´î½¨¾Í²»×¸ÊöÁË£©£¬½ÓÏÂÀ´ÎÒÃÇÖðÒ»½â¶Á·ÖƬÅäÖÃÐÅÏ¢¡£
ÎÒÃÇÊ×Ïȶ¨ÒåÁ½¸öÊý¾ÝÔ´ ds-0¡¢ds-1£¬²¢·Ö±ð¼ÓÉÏÊý¾ÝÔ´µÄ»ù´¡ÐÅÏ¢¡£
- # ¶¨ÒåÁ½¸öÈ«¾ÖÊý¾ÝÔ´
- spring.shardingsphere.datasource.names=ds-0,ds-1
- # ÅäÖÃÊý¾ÝÔ´ ds-0
- spring.shardingsphere.datasource.ds-0.type=com.alibaba.druid.pool.DruidDataSource
- spring.shardingsphere.datasource.ds-0.driverClassName=com.mysql.jdbc.Driver
- spring.shardingsphere.datasource.ds-0.url=jdbc:mysql://127.0.0.1:3306/ds-0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
- spring.shardingsphere.datasource.ds-0.username=root
- spring.shardingsphere.datasource.ds-0.password=root
- # ÅäÖÃÊý¾ÝÔ´ ds-1
- spring.shardingsphere.datasource.ds-1.type=com.alibaba.druid.pool.DruidDataSource
- spring.shardingsphere.datasource.ds-1.driverClassName=com.mysql.jdbc.Driver
- spring.shardingsphere.datasource.ds-1.url=jdbc:mysql://127.0.0.1:3306/ds-1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
- spring.shardingsphere.datasource.ds-1.username=root
- spring.shardingsphere.datasource.ds-1.password=root
ÅäÖÃÍêÊý¾ÝÔ´½ÓÏÂÀ´Îª±íÌí¼Ó·Ö¿âºÍ·Ö±í²ßÂÔ£¬Ê¹Óà sharding-jdbc ×ö·Ö¿â·Ö±íÐèÒªÎÒÃÇΪÿһ¸ö±íµ¥¶ÀÉèÖ÷ÖƬ¹æÔò¡£
- # ÅäÖ÷ÖƬ±í t_order
- # Ö¸¶¨ÕæʵÊý¾Ý½Úµã
- spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds-$->{0..1}.t_order_$->{0..2}
actual-data-nodes ÊôÐÔÖ¸¶¨·ÖƬµÄÕæʵÊý¾Ý½Úµã£¬$ÊÇÒ»¸öռλ·û£¬{0..1}±íʾʵ¼Ê²ð·ÖµÄÊý¾Ý¿â±íÊýÁ¿¡£
ds-$->{0..1}.t_order_$->{0..2} ±í´ïʽÏ൱ÓÚ 6¸öÊý¾Ý½Úµã
- ### ·Ö¿â²ßÂÔ
- # ·Ö¿â·ÖƬ½¡
- spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=order_id
- # ·Ö¿â·ÖƬËã·¨
- spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds-$->{order_id % 2}
Ϊ±íÉèÖ÷ֿâ²ßÂÔ£¬Éϱ߽²ÁË sharding-jdbc ËüÌṩÁËËÄÖÖ·ÖƬ²ßÂÔ£¬Îª¿ìËٴÎÒÃÇÏÈÒÔ×î¼òµ¥µÄÐÐÄÚ±í´ïʽ·ÖƬ²ßÂÔÀ´ÊµÏÖ£¬ÔÚÏÂһƪ»á½éÉÜËÄÖÖ·ÖƬ²ßÂÔµÄÏêϸÓ÷¨ºÍʹÓó¡¾°¡£
database-strategy.inline.sharding-column ÊôÐÔÖÐ database-strategy Ϊ·Ö¿â²ßÂÔ£¬inline Ϊ¾ßÌåµÄ·ÖƬ²ßÂÔ£¬sharding-column ´ú±í·ÖƬ½¡¡£
database-strategy.inline.algorithm-expression Êǵ±Ç°²ßÂÔϾßÌåµÄ·ÖƬËã·¨£¬ds-$->{order_id % 2} ±í´ïʽÒâ˼ÊÇ ¶Ô order_id×ֶνøÐÐÈ¡Ä£·Ö¿â£¬2 ´ú±í·ÖƬ¿âµÄ¸öÊý£¬²»Í¬µÄ²ßÂÔ¶ÔÓ¦²»Í¬µÄËã·¨£¬ÕâÀïÒ²¿ÉÒÔÊÇÎÒÃÇ×Ô¶¨ÒåµÄ·ÖƬËã·¨Àà¡£
- # ·Ö±í²ßÂÔ
- # ·Ö±í·ÖƬ½¡
- spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
- # ·Ö±íËã·¨
- spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 3}
- # ×ÔÔöÖ÷¼ü×Ö¶Î
- spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
- # ×ÔÔöÖ÷¼üID Éú³É·½°¸
- spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
·Ö±í²ßÂÔ ºÍ ·Ö¿â²ßÂÔ µÄÅäÖñȽÏÏàËÆ£¬²»Í¬µÄÊÇ·Ö±í¿ÉÒÔͨ¹ý key-generator.column ºÍ key-generator.type ÉèÖÃ×ÔÔöÖ÷¼üÒÔ¼°Ö¸¶¨×ÔÔöÖ÷¼üµÄÉú³É·½°¸£¬Ä¿Ç°ÄÚÖÃÁËSNOWFLAKE ºÍ UUID Á½ÖÖ·½Ê½£¬»¹ÄÜ×Ô¶¨ÒåµÄÖ÷¼üÉú³ÉËã·¨À࣬ºóÐø»áÏêϸµÄ½²½â¡£
- # °ó¶¨±í¹Øϵ
- spring.shardingsphere.sharding.binding-tables= t_order,t_order_item
±ØÐë°´Ïàͬ·ÖƬ½¡½øÐзÖƬµÄ±í²ÅÄÜ»¥Îª³É°ó¶¨±í£¬ÔÚÁªºÏ²éѯʱ¾ÍÄܱÜÃâ³öÏֵѿ¨¶û»ý²éѯ¡£
- # ÅäÖù㲥±í
- spring.shardingsphere.sharding.broadcast-tables=t_config
¹ã²¥±í£¬¿ªÆô SQL½âÎöÈÕÖ¾£¬ÄÜÇåÎúµÄ¿´µ½ SQL·ÖƬ½âÎöµÄ¹ý³Ì
- # ÊÇ·ñ¿ªÆô SQL½âÎöÈÕÖ¾
- spring.shardingsphere.props.sql.show=true
3¡¢ÑéÖ¤·ÖƬ
·ÖƬÅäÖÃÍêÒÔºóÎÒÃÇÎÞÐèÔÚÐÞ¸ÄÒµÎñ´úÂëÁË£¬Ö±½ÓÖ´ÐÐÒµÎñÂß¼µÄÔö¡¢É¾¡¢¸Ä¡¢²é¼´¿É£¬½ÓÏÂÀ´Ñé֤һϷÖƬµÄЧ¹û¡£
ÎÒÃÇͬʱÏò t_order¡¢t_order_item ±í²åÈë 5Ìõ¶©µ¥¼Ç¼£¬²¢²»¸ø¶¨Ö÷¼ü order_id £¬item_id ×Ö¶ÎÖµ¡£
- public String insertOrder() {
- for (int i = 0; i < 4; i++) {
- TOrder order = new TOrder();
- order.setOrderNo("A000" + i);
- order.setCreateName("¶©µ¥ " + i);
- order.setPrice(new BigDecimal("" + i));
- orderRepository.insert(order);
- TOrderItem orderItem = new TOrderItem();
- orderItem.setOrderId(order.getOrderId());
- orderItem.setOrderNo("A000" + i);
- orderItem.setItemName("·þÎñÏîÄ¿" + i);
- orderItem.setPrice(new BigDecimal("" + i));
- orderItemRepository.insert(orderItem);
- }
- return "success";
- }
¿´µ½¶©µ¥¼Ç¼±»³É¹¦·ÖÉ¢µ½Á˲»Í¬µÄ¿â±íÖУ¬ order_id ×Ö¶ÎÒ²×Ô¶¯Éú³ÉÁËÖ÷¼üID£¬»ù´¡µÄ·ÖƬ¹¦ÄܾÍÍê³ÉÁË¡£
»ù´¡·ÖƬ
ÄÇÏò¹ã²¥±í t_config ÖвåÈëÒ»ÌõÊý¾Ý»áÊÇʲôЧ¹ûÄØ£¿
- public String config() {
- TConfig tConfig = new TConfig();
- tConfig.setRemark("ÎÒÊǹ㲥±í");
- tConfig.setCreateTime(new Date());
- tConfig.setLastModifyTime(new Date());
- configRepository.insert(tConfig);
- return "success";
- }
·¢ÏÖËùÓпâÖÐ t_config ±í¶¼Ö´ÐÐÁËÕâÌõSQL£¬¹ã²¥±íºÍ MQ¹ã²¥¶©ÔĵÄģʽºÜÏàËÆ£¬ËùÓж©ÔĵĿͻ§¶Ë¶¼»áÊÕµ½Í¬Ò»ÌõÏûÏ¢¡£
¹ã²¥±í
¼òµ¥SQL²Ù×÷Ñé֤ûÎÊͨ£¬½ÓÏÂÀ´ÔÚÊÔÊÔ¸´ÔÓÒ»µãµÄÁªºÏ²éѯ£¬Ç°±ßÎÒÃÇÒѾ°Ñ t_order ¡¢t_order_item ±íÉèΪ°ó¶¨±í£¬Ö±½ÓÁª±í²éѯִÐÐһϡ£
¹ØÁª²éѯ
ͨ¹ý¿ØÖÆ̨ÈÕÖ¾·¢ÏÖ£¬Âß¼±íSQL ¾¹ý½âÎöÒÔºó£¬Ö»¶Ô t_order_0 ºÍ t_order_item_0 ±í½øÐÐÁ˹ØÁª²úÉúÒ»ÌõSQL¡£
°ó¶¨±íSQL
ÄÇÈç¹û²»»¥Îª°ó¶¨±íÓÖ»áÊÇʲôÇé¿öÄØ£¿È¥µô spring.shardingsphere.sharding.binding-tablesÊÔһϡ£
·¢ÏÖ¿ØÖÆ̨½âÎö³öÁË 3ÌõÕæʵ±íSQL£¬¶øÈ¥µô order_id ×÷Ϊ²éѯÌõ¼þÔÙ´ÎÖ´Ðк󣬽á¹û½âÎö³öÁË 9ÌõSQL£¬½øÐÐÁ˵ѿ¨¶û»ý²éѯ¡£ËùÒÔÏà±È֮ϰ󶨱íµÄÓŵã¾Í²»ÑÔ¶øÓ÷ÁË¡£
µÑ¿¨¶û»ý²éѯ
Îå¡¢×ܽá
ÒÔÉ϶Էֿâ·Ö±íÖмä¼þ sharding-jdbc µÄ»ù´¡¸ÅÄî×öÁ˼òµ¥ÊáÀí£¬¿ìËٵĴÁËÒ»¸ö·Ö¿â·Ö±í°¸Àý£¬µ«ÕâÖ»ÊÇʵ¼ù·Ö¿â·Ö±íµÄµÚÒ»²½£¬ÏÂһƪÎÒÃÇ»áÏêϸµÄ½éÉÜËÄÖÖ·ÖƬ²ßÂԵľßÌåÓ÷¨ºÍʹÓó¡¾°£¨±ØÖª±Ø»á£©£¬ºó±ß½«Â½Ðø½²½â×Ô¶¨Òå·Ö²¼Ê½Ö÷¼ü¡¢·Ö²¼Ê½Êý¾Ý¿âÊÂÎñ¡¢·Ö²¼Ê½·þÎñÖÎÀí£¬Êý¾ÝÍÑÃôµÈ¡£
20210323µÚÒ»¼ÒÁ¿²ú¹ú²ú»¯À¶ÑÀAOA¸ß¾«¶È¶¨Î»»ùÕ¾Éú̬ºÏÄÜÅàѵ»áÉϺ£Õ¾ÏÖ³¡Ö±²¥ÏÂ...
´ÁÀ¶×Ö¡° CSDNÔƼÆËã ¡±¹Ø×¢ÎÒÃÇŶ ×÷Õß | Áõµ¤ ³öÆ· | CSDNÔƼÆËãIDCSDNcloud ...
IViewLocationExpander API ExpandViewLocations RazorÊÓͼ·¾¶£¬ÊÓͼÒýÇæ»áËÑË÷...
1£®ÏÖÔÚ¸´Ï°µÄ¸Ð¾õ¾ÍÊÇ£ºÂíÉÏÒªÓÐÒ»´ó²¨½©Ê¬Ó¿¹ýÀ´£¬µ«ÀÏ×ÓÁ¬ÏòÈÕ¿û¶¼»¹Ã»ÖÖ£¡ 2...
WebService¶Ë´úÂë ¸´ÖÆ´úÂë ´úÂëÈçÏÂ: /// summary /// ÉÏ´«Îļþµ½Ô¶³Ì·þÎñÆ÷ //...
Epoll ÊǸöºÜÀϵÄ֪ʶµã£¬ÊǺó¶Ë¹¤³ÌʦµÄ¾µä±ØÐ޿Ρ£ÕâÖÖ֪ʶ¾ß±¸µÄÌصã¾ÍÊÇÑÐ...
¶Ñ Heap Heap£º¿ÉÒÔѸËÙÕÒµ½Ò»¶ÑÊýÖÐµÄ ×î´ó »òÕß ×îС ÖµµÄÊý¾Ý½á¹¹¡£ ½«¸ù½Úµã...
ÏîÄ¿±³¾° ×î½üÏîÄ¿ÀïÓиöwebpack°æ±¾½ÏÀϵÄÏîÄ¿£¬ÓÉÓÚÉý¼¶ºÍ»»¿ò¼ÜÔÝʱ²»±»leade...
ÕâÀï×ðÍÐÔÆÊýС±à²Î¿¼Á˼¸ÆªÎÄÕÂÌØΪ´ó¼ÒÕûÀíÏ£¬Óõ½µÄÅóÓѶàÖ§³ÖÒ»ÏÂÁË¡£ ½øÐÐ...
Ïê½âSpring mvc ant pathµÄʹÓ÷½·¨ ¸ÅÒª£º ÈκÎÒ»¸öWEB¶¼ÐèÒª½â¾öURLÓëÇëÇó´¦Àí...