£¨1£©¿ÉÓÃÐÔÉè¼Æ
½â¾ö˼·£º¸´ÖÆ+ÈßÓà
¸±×÷Ó㺸´ÖÆ+ÈßÓàÒ»¶¨»áÒý·¢Ò»ÖÂÐÔÎÊÌâ
±£Ö¤¡°¶Á¡±¸ß¿ÉÓõķ½·¨£º¸´Öƴӿ⣬ÈßÓàÊý¾Ý£¬ÈçÏÂͼ

´øÀ´µÄÎÊÌ⣺Ö÷´Ó²»Ò»ÖÂ
½â¾ö·½°¸£º¼ûÏÂÎÄ
±£Ö¤¡°Ð´¡±¸ß¿ÉÓõÄÒ»°ã·½·¨£ºË«Ö÷ģʽ£¬¼´¸´ÖÆÖ÷¿â£¨ºÜ¶à¹«Ë¾Óõ¥master£¬´ËʱÎÞ·¨±£Ö¤Ð´µÄ¿ÉÓÃÐÔ£©£¬ÈßÓàÊý¾Ý£¬ÈçÏÂͼ

´øÀ´µÄÎÊÌ⣺˫Ö÷ͬ²½key³åÍ»£¬Òý²»Ò»ÖÂ
½â¾ö·½°¸£º
a£©·½°¸Ò»£ºÓÉÊý¾Ý¿â»òÕßÒµÎñ²ã±£Ö¤keyÔÚÁ½¸öÖ÷Éϲ»³åÍ»
b£©·½°¸¶þ£º¼ûÏÂÎÄ
58ͬ³Ç±£Ö¤¡°Ð´¡±¸ß¿ÉÓõķ½·¨£º¡°Ë«Ö÷¡±µ±¡°Ö÷´Ó¡±Ó㬲»×ö¶Áд·ÖÀ룬ÔÚ¡°Ö÷¡±¹ÒµôµÄÇé¿öÏ£¬¡°´Ó¡±£¨ÆäʵÊÇÁíÍâÒ»¸öÖ÷£©£¬¶¥ÉÏ£¬ÈçÏÂͼ

Óŵ㣺¶Áд¶¼µ½Ö÷£¬½â¾öÁËÒ»ÖÂÐÔÎÊÌ⣻¡°Ë«Ö÷¡±µ±¡°Ö÷´Ó¡±Ó㬽â¾öÁË¿ÉÓÃÐÔÎÊÌâ
´øÀ´µÄÎÊÌ⣺¶ÁÐÔÄÜÈçºÎÀ©³ä£¿½â¾ö·½°¸¼ûÏÂÎÄ
£¨2£©¶ÁÐÔÄÜÉè¼Æ£ºÈçºÎÀ©Õ¹¶ÁÐÔÄÜ
×î³£Óõķ½·¨ÊÇ£¬½¨Á¢Ë÷Òý
½¨Á¢·Ç³£¶àµÄË÷Òý£¬¸±×÷ÓÃÊÇ£º
a£©½µµÍÁËдÐÔÄÜ
b£©Ë÷ÒýÕ¼ÄÚ´æ¶àÁË£¬·ÅÔÚÄÚ´æÖеÄÊý¾Ý¾ÍÉÙÁË£¬Êý¾ÝÃüÖÐÂʾ͵ÍÁË£¬IO´ÎÊý¾Í¶àÁË
µ«ÊÇ·ñÏëµ½£¬²»Í¬µÄ¿â¿ÉÒÔ½¨Á¢²»Í¬µÄË÷ÒýÄØ£¿ÈçÏÂͼ

TIPS£º²»Í¬µÄ¿â¿ÉÒÔ½¨Á¢²»Í¬Ë÷Òý
Ö÷¿âÖ»Ìṩд£¬²»½¨Á¢Ë÷Òý
online´Ó¿âÖ»Ìṩonline¶Á£¬½¨Á¢online¶ÁË÷Òý
offline´Ó¿âÖ»Ìṩoffline¶Á£¬½¨Á¢offline¶ÁË÷Òý
Ìá¸ß¶ÁÐÔÄܳ£¼û·½°¸¶þ£¬Ôö¼Ó´Ó¿â

ÉÏÎÄÒѾÌáµ½£¬ÕâÖÖ·½·¨»áÒý·¢Ö÷´Ó²»Ò»ÖÂÎÊÌ⣬´Ó¿âÔ½¶à£¬Ö÷´ÓʱÑÓÔ½³¤£¬²»Ò»ÖÂÎÊÌâÔ½ÑÏÖØ
ÕâÖÖ·½°¸ºÜ³£¼û£¬µ«58ûÓвÉÓÃ
Ìá¸ß¶ÁÐÔÄÜ·½°¸Èý£¬Ôö¼Ó»º´æ
´«Í³»º´æµÄÓ÷¨ÊÇ£º
a£©·¢ÉúдÇëÇóʱ£¬ÏÈÌÔÌ»º´æ£¬ÔÙдÊý¾Ý¿â
b£©·¢Éú¶ÁÇëÇóʱ£¬ÏȶÁ»º´æ£¬hitÔò·µ»Ø£¬missÔò¶ÁÊý¾Ý¿â²¢½«Êý¾ÝÈ뻺´æ£¨´Ëʱ¿ÉÄܾÉÊý¾ÝÈ뻺´æ£©£¬ÈçÏÂͼ

´øÀ´µÄÎÊÌ⣺
a£©ÈçÉÏÎÄËùÊö£¬Êý¾Ý¸´ÖÆ»áÒý·¢Ò»ÖÂÐÔÎÊÌ⣬ÓÉÓÚÖ÷´ÓÑÓʱµÄ´æÔÚ£¬¿ÉÄÜÒý·¢»º´æÓëÊý¾Ý¿âÊý¾Ý²»Ò»ÖÂ
b£©ËùÓÐappÒµÎñ²ã¶¼Òª¹Ø×¢»º´æ£¬ÎÞ·¨ÆÁ±Î¡°Ö÷+´Ó+»º´æ¡±µÄ¸´ÔÓÐÔ
58ͬ³Ç»º´æÊ¹Ó÷½°¸£º·þÎñ+Êý¾Ý+»º´æ

ºÃ´¦ÊÇ£º
1£©ÒýÈë·þÎñ²ãÆÁ±Î¡°Êý¾Ý¿â+»º´æ¡±
2£©²»×ö¶Áд·ÖÀ룬¶Áд¶¼µ½Ö÷µÄģʽ£¬²»»áÒý·¢²»Ò»ÖÂ
£¨3£©Ò»ÖÂÐÔÉè¼Æ
Ö÷´Ó²»Ò»Ö½â¾ö·½°¸
·½°¸Ò»£ºÒýÈëÖмä¼þ

Öмä¼þ½«keyÉϵÄд·Óɵ½Ö÷£¬ÔÚÒ»¶¨Ê±¼ä·¶Î§ÄÚ£¨Ö÷´Óͬ²½Íê³ÉµÄ¾Ñéʱ¼ä£©£¬¸ÃkeyÉϵĶÁҲ·Óɵ½Ö÷
·½°¸¶þ£º¶Áд¶¼µ½Ö÷

ÉÏÎÄÒѾÌáµ½£¬58ͬ³Ç²ÉÓÃÁËÕâÖÖ·½·¨£¬²»×ö¶Áд·ÖÀ룬²»»á²»Ò»ÖÂ
Êý¾Ý¿âÓ뻺´æ²»Ò»Ö½â¾ö·½°¸
Á½´ÎÌÔÌ·¨

Òì³£µÄ¶ÁдʱÐò£¬»òµ¼Ö¾ÉÊý¾ÝÈ뻺´æ£¬Ò»´ÎÌÔ̲»¹»£¬Òª½øÐжþ´ÎÌÔÌ
a£©·¢ÉúдÇëÇóʱ£¬ÏÈÌÔÌ»º´æ£¬ÔÙдÊý¾Ý¿â£¬¶îÍâÔö¼ÓÒ»¸ötimer£¬Ò»¶¨Ê±¼ä£¨Ö÷´Óͬ²½Íê³ÉµÄ¾Ñéʱ¼ä£©ºóÔÙ´ÎÌÔÌ
b£©·¢Éú¶ÁÇëÇóʱ£¬ÏȶÁ»º´æ£¬hitÔò·µ»Ø£¬missÔò¶ÁÊý¾Ý¿â²¢½«Êý¾ÝÈ뻺´æ£¨´Ëʱ¿ÉÄܾÉÊý¾ÝÈ뻺´æ£¬µ«»á±»¶þ´ÎÌÔÌÌÔ̵ô£¬×îÖÕ²»»áÒý·¢²»Ò»Ö£©
£¨4£©À©Õ¹ÐÔÉè¼Æ
£¨4.1£©58ͬ³ÇÃë¼¶±ðÊý¾ÝÀ©ÈÝ
ÐèÇó£ºÔÀ´Ë®Æ½ÇзÖΪN¸ö¿â£¬ÏÖÔÚÒªÀ©³äΪ2N¸ö¿â£¬Ï£Íû²»Ó°Ïì·þÎñ£¬ÔÚÃë¼¶±ðÍê³É

×ʼ£¬·ÖΪ2¿â£¬0¿âºÍ1¿â£¬¾ù²ÉÓá°Ë«Ö÷µ±Ö÷´ÓÓá±µÄģʽ±£Ö¤¿ÉÓÃÐÔ

½ÓÏÂÀ´£¬½«´Ó¿âÌáÉý£¬²¢Ð޸ķþÎñ¶ËÅäÖã¬Ãë¼¶Íê³ÉÀ©¿â
ÓÉÓÚÊÇ2À©4£¬²»»á´æÔÚÊý¾ÝÇ¨ÒÆ£¬ÔÀ´µÄ0¿â±äΪ0¿â+2¿â£¬ÔÀ´µÄ1¿â±äΪ1¿âºÍ3¿â
´ËʱËðʧµÄÊÇÊý¾ÝµÄ¿ÉÓÃÐÔ

×îºó£¬½â³ý¾ÉµÄË«Ö÷ͬ²½£¨0¿âºÍ2¿â²»»áÊý¾Ý³åÍ»£©£¬ÎªÁ˱£Ö¤¿ÉÓÃÐÔÔö¼ÓеÄË«Ö÷ͬ²½£¬²¢É¾³ýµô¶àÓàµÄÊý¾Ý
ÕâÖÖ·½°¸¿ÉÒÔÃë¼¶Íê³ÉN¿âµ½2N¿âµÄÀ©ÈÝ¡£
´æÔÚµÄÎÊÌ⣺ֻÄÜÍê³ÉN¿âÀ©2N¿âµÄÀ©ÈÝ£¨²»ÐèÒªÊý¾ÝÇ¨ÒÆ£©£¬·ÇͨÓÃÀ©ÈÝ·½°¸£¨ÀýÈç3¿âÀ©4¿â¾ÍÎÞ·¨Íê³É£©
£¨4.2£©·ÇÖ¸ÊýÀ©ÈÝ£¬Êý¾Ý¿âÔö¼Ó×ֶΣ¬Êý¾ÝÇ¨ÒÆ
[ÕâЩ·½·¨ÔÚ£¨ÉÏ£©ÆªÖж¼ÒѾ½éÉܹý£¬´Ë´¦²»ÔÙÈßÓ࣬ÓÐÐËȤµÄÅóÓѻظ´¡°Í¬³Ç¡±»Ø¿´£¨ÉÏ£©Æª]
·½°¸Ò»£º×·ÈÕÖ¾·½°¸
·½°¸¶þ£ºË«Ð´·½°¸
£¨4.3£©Ë®Æ½ÇзÖÔõôÇÐ
ËÄÀೡ¾°¸²¸Ç99%²ð¿âÒµÎñ
a£©¡°µ¥key¡±³¡¾°£¬Óû§¿âÈçºÎ²ð·Ö£º user(uid, XXOO)
b£©¡°1¶Ô¶à¡±³¡¾°£¬Ìû×Ó¿âÈçºÎ²ð·Ö£º tiezi(tid, uid, XXOO)
c£©¡°¶à¶Ô¶à¡±³¡¾°£¬ºÃÓÑ¿âÈçºÎ²ð·Ö£º friend(uid, friend_uid, XXOO)
d£©¡°¶àkey¡±³¡¾°£¬¶©µ¥¿âÈçºÎ²ð·Ö£ºorder(oid, buyer_id, seller_id, XXOO)
[ÕâЩ²ð¿â·½°¸ÔÚ£¨ÉÏ£©ÆªÖж¼ÒѾ½éÉܹý£¬´Ë´¦²»ÔÙÈßÓ࣬ÓÐÐËȤµÄÅóÓѻظ´¡°Í¬³Ç¡±»Ø¿´£¨ÉÏ£©Æª]
£¨5£©º£Á¿Êý¾ÝÏ£¬SQLÔõÃ´Íæ
²»»áÕâÃ´Íæ
a£©¸÷ÖÖÁªºÏ²éѯ
b£©×Ó²éѯ
c£©´¥·¢Æ÷
d£©Óû§×Ô¶¨Ò庯Êý
e£©¡°ÊÂÎñ¡±¶¼ÓõĺÜÉÙ
ÔÒò£º¶ÔÊý¾Ý¿âÐÔÄÜÓ°Ï켫´ó
²ð¿âºó£¬IN²éѯÔõÃ´Íæ[»Ø¸´¡°Í¬³Ç¡±»Ø¿´£¨ÉÏ£©Æª]
²ð¿âºó£¬·ÇPartition keyµÄ²éѯÔõÃ´Íæ[»Ø¸´¡°Í¬³Ç¡±»Ø¿´£¨ÉÏ£©Æª]
²ð¿âºó£¬¿ä¿â·ÖÒ³ÔõÃ´Íæ£¿[»Ø¸´¡°Í¬³Ç¡±»Ø¿´£¨ÉÏ£©Æª]
ÎÊÌâµÄÌá³öÓë³éÏó£ºORDER BY xxx OFFSET xxx LIMIT xxx
µ¥»ú·½°¸£ºORDER BY time OFFSET 10000 LIMIT 100
·Ö¿âºóµÄÄÑÌ⣺ÈçºÎÈ·ÈÏÈ«¾ÖÆ«ÒÆÁ¿
·Ö¿âºó´«Í³½â¾ö·½°¸£º²éѯ¸Äд+ÄÚ´æÅÅÐò
a£©ORDER BY time OFFSET 0 LIMIT 10000+100
b£©¶Ô20200Ìõ¼Ç¼½øÐÐÅÅÐò
c£©·µ»ØµÚ10000ÖÁ10100Ìõ¼Ç¼
ÓÅ»¯·½°¸Ò»£ºÔö¼Ó¸¨Öúid£¬ÒÔ¼õÉÙ²éѯÁ¿
ÓÅ»¯·½°¸¶þ£ºÄ£ºý²éѯ
a£©ÒµÎñÉÏ£º½ûÖ¹²éѯXXÒ³Ö®ºóµÄÊý¾Ý
b£©ÒµÎñÉÏ£ºÔÊÐíÄ£ºý·µ»Ø => µÚ100Ò³Êý¾ÝµÄ¾«È·ÐÔÕæÕâÃ´ÖØÒªÃ´£¿
×îºóµÄ´óÕУ¡£¡£¡£¨ÓÉÓÚʱ¼äÎÊÌ⣬ֻÔÚDTCC2015ÉÏ·ÖÏíÁËÓ´£©
ÓÅ»¯·½°¸Èý£ºÖÕ¼«·½°¸£¬ÒµÎñÎÞË𣬲éѯ¸ÄдÓëÁ½¶Î²éѯ
ÐèÇó£ºORDER BY x OFFSET 10000 LIMIT 4; ÈçºÎÔÚ·Ö¿âÏÂʵÏÖ£¨¼ÙÉè·Ö3¿â£©
²½ÖèÒ»¡¢²éѯ¸Äд£º ORDER BY x OFFSET 3333 LIMIT 4
[4,7,9,10] <= 1¿â·µ»Ø
[3,5,6,7] <= 2¿â·µ»Ø
[6,8,9,11] <= 3¿â·µ»Ø
²½Öè¶þ¡¢ÕÒµ½²½ÖèÒ»·µ»ØµÄminºÍmax£¬¼´3ºÍ11
²½ÖèÈý¡¢Í¨¹ýminºÍmax¶þ´Î²éѯ£ºORDER BY x WHERE x BETWEEN 3 AND 11
[3,4,7,9,10] <= 1¿â·µ»Ø£¬4ÔÚ1¿âoffsetÊÇ3333£¬ÓÚÊÇ3ÔÚ1¿âµÄoffsetÊÇ3332
[3,5,6,7,11] <= 2¿â·µ»Ø£¬3ÔÚ2¿âoffsetÊÇ3333
[3,5,6,8,9,11] <= 3¿â·µ»Ø£¬6ÔÚ3¿âoffsetÊÇ3333£¬ÓÚÊÇ3ÔÚ3¿âµÄoffsetÊÇ3331
²½ÖèËÄ¡¢ÕÒ³öÈ«¾ÖOFFSET
3ÊÇÈ«¾Öoffset3332+3333+3331=9996
µ±µ±µ±µ±£¬Ìø¹ý3,3,3,4£¬ÓÚÊÇÈ«¾ÖOFFSET 10000 LIMIT 4ÊÇ[5,5,6,6]
×ܽ᣺58ͬ³ÇÊý¾Ý¿â¼Ü¹¹Éè¼ÆË¼Â·
£¨1£©¿ÉÓÃÐÔ£¬½â¾ö˼·ÊÇÈßÓࣨ¸´ÖÆ£©
£¨1.1£©¶Á¿ÉÓÃÐÔ£º¶à¸ö´Ó¿â
£¨1.2£©Ð´¿ÉÓÃÐÔ£ºË«Ö÷ģʽ or Ë«Ö÷µ±Ö÷´ÓÓã¨58µÄÍæ·¨£©
£¨2£©¶ÁÐÔÄÜ£¬ÈýÖÖ·½Ê½À©³ä¶ÁÐÔÄÜ
£¨2.1£©Ôö¼ÓË÷Òý£ºÖ÷´ÓÉϵÄË÷Òý¿ÉÒÔ²»Ò»Ñù
£¨2.2£©Ôö¼Ó´Ó¿â
£¨2.3£©Ôö¼Ó»º´æ£º·þÎñ+»º´æ+Êý¾ÝÒ»Ì×£¨58µÄÍæ·¨£©
£¨3£©Ò»ÖÂÐÔ
£¨3.1£©Ö÷´Ó²»Ò»Ö£ºÒýÈëÖмä²ã or ¶Áд¶¼×ßÖ÷¿â£¨58µÄÍæ·¨£©
£¨3.2£©»º´æ²»Ò»Ö£ºË«ÌÔÌÀ´½â¾ö»º´æ²»Ò»ÖÂÎÊÌâ
£¨4£©À©Õ¹ÐÔ
£¨4.1£©Êý¾ÝÀ©ÈÝ£ºÌáÉý´Ó¿â£¬doubleÖ÷¿â£¬Ãë¼¶À©ÈÝ
£¨4.2£©×Ö¶ÎÀ©Õ¹£º×·ÈÕÖ¾·¨ or ˫д·¨
£¨4.3£©Ë®Æ½ÇзÖ
£¨µ¥key£©Óû§¿âÈçºÎ²ð·Ö£º, user(uid XXOO)
£¨1¶Ô¶à£©Ìû×Ó¿âÈçºÎ²ð·Ö£º tiezi(tid, uid, XXOO)
£¨¶à¶Ô¶à£©ºÃÓÑ¿âÈçºÎ²ð·Ö£º friend(uid, friend_uid, XXOO)
£¨¶àkey£©¶©µ¥¿âÈçºÎ²ð·Ö£ºorder(oid, buyer_id, seller_id, XXOO)
£¨5£©SQLÍæ·¨
£¨5.0£©²»ÕâÃ´Íæ£ºÁªºÏ²éѯ£¬×Ó²éѯ£¬´¥·¢Æ÷£¬×Ô¶¨Ò庯Êý£¬ÊÂÎñ
£¨5.1£©IN²éѯ£º·Ö·¢MR or Æ´×°³É²»Í¬SQLÓï¾ä
£¨5.2£©·Çpartition key²éѯ£º¶¨Î»Ò»¸ö¿â or ·Ö·¢MR
£¨5.3£©¿ä¿â·ÖÒ³
£¨5.3.1£©ÐÞ¸ÄsqlÓï¾ä£¬·þÎñÄÚÅÅÐò
£¨5.3.2£©ÒýÈëÌØÊâid£¬¼õÉÙ·µ»ØÊýÁ¿
£¨5.3.3£©ÒµÎñÓÅ»¯£¬ÔÊÐíÄ£ºý²éѯ
£¨5.3.4£©²éѯ¸Äд£¬¶þ¶Î²éѯ |