鐩綍 闂
server灞傚拰瀛樺偍寮曟搸灞? 閭IMIT鏄粈涔堥锛? 鎬庝箞鍔烇紵
鍚愪釜妲? 鏈�杩戞湁澶氫釜灏忎紮浼村湪绛旂枒缇ら噷闂簡灏忓瀛愬叧浜嶭IMIT鐨勪竴涓棶棰橈紝涓嬭竟鎴戞潵澶ц嚧鎻忚堪涓�涓嬭繖涓棶棰樸�?/p>
闂
涓轰簡鏁呬簨鐨勯『鍒╁彂灞曪紝鎴戜滑寰楀厛鏈変釜琛細
CREATE TABLE t (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1)
) Engine=InnoDB CHARSET=utf8;
琛╰鍖呭惈3涓垪锛宨d鍒楁槸涓婚敭锛宬ey1鍒楁槸浜岀骇绱㈠紩鍒椼�傝〃涓寘鍚?涓囨潯璁板綍銆?/p>
褰撴垜浠墽琛屼笅杈硅繖涓鍙ョ殑鏃跺�欙紝鏄娇鐢ㄤ簩绾х储寮昳dx_key1鐨勶細
mysql> EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| 1 | SIMPLE | t | NULL | index | NULL | idx_key1 | 303 | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
杩欎釜寰堝ソ鐞嗚В锛屽洜涓哄湪浜岀骇绱㈠紩idx_key1涓紝key1鍒楁槸鏈夊簭鐨勩�傝�屾煡璇㈡槸瑕佸彇鎸夌収key1鍒楁帓搴忕殑绗?鏉¤褰曪紝閭ySQL鍙渶瑕佷粠idx_key1涓幏鍙栧埌绗竴鏉′簩绾х储寮曡褰曪紝鐒跺悗鐩存帴鍥炶〃鍙栧緱瀹屾暣鐨勮褰曞嵆鍙�?/p>
浣嗘槸濡傛灉鎴戜滑鎶婁笂杈硅鍙ョ殑LIMIT 1鎹㈡垚LIMIT 5000, 1锛屽垯鍗撮渶瑕佽繘琛屽叏琛ㄦ壂鎻忥紝骞惰繘琛宖ilesort锛屾墽琛岃鍒掑涓嬶細
mysql> EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 9966 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
鏈夌殑鍚屽灏卞緢涓嶇悊瑙ｄ簡锛歀IMIT 5000, 1涔熷彲浠ヤ娇鐢ㄤ簩绾х储寮昳dx_key1鍛�锛屾垜浠彲浠ュ厛鎵弿鍒扮5001鏉′簩绾х储寮曡褰曪紝瀵圭5001鏉′簩绾х储寮曡褰曡繘琛屽洖琛ㄦ搷浣滀笉灏卞ソ浜嗕箞锛岃繖鏍风殑浠ｄ环鑲畾姣斿叏琛ㄦ壂鎻?filesort寮哄憖銆?/p>
寰堥仐鎲剧殑鍛婅瘔鍚勪綅锛岀敱浜嶮ySQL瀹炵幇涓婄殑缂洪櫡锛屼笉浼氬嚭鐜颁笂杩扮殑鐞嗘兂鎯呭喌锛屽畠鍙細绗ㄧ鐨勫幓鎵ц鍏ㄨ〃鎵弿+filesort锛屼笅杈规垜浠敔鍙ㄤ竴涓嬪埌搴曟槸鍜嬪洖浜嬪効銆?/p>
server灞傚拰瀛樺偍寮曟搸灞?br />
澶у閮界煡閬擄紝MySQL鍐呴儴鍏跺疄鏄垎涓簊erver灞傚拰瀛樺偍寮曟搸灞傜殑锛?/p>
server灞傝礋璐ｅ鐞嗕竴浜涢�氱敤鐨勪簨鎯咃紝璇稿杩炴帴绠＄悊銆丼QL璇硶瑙ｆ瀽銆佸垎鏋愭墽琛岃鍒掍箣绫荤殑涓滆タ
瀛樺偍寮曟搸灞傝礋璐ｅ叿浣撶殑鏁版嵁瀛樺偍锛岃濡傛暟鎹槸瀛樺偍鍒版枃浠朵笂杩樻槸鍐呭瓨閲岋紝鍏蜂綋鐨勫瓨鍌ㄦ牸寮忔槸浠�涔堟牱鐨勪箣绫荤殑銆傛垜浠幇鍦ㄥ熀鏈兘浣跨敤InnoDB瀛樺偍寮曟搸锛屽叾浠栧瓨鍌ㄥ紩鎿庝娇鐢ㄧ殑闈炲父灏戜簡锛屾墍浠ユ垜浠篃灏变笉娑夊強鍏朵粬瀛樺偍寮曟搸浜嗐�?/li>
MySQL涓竴鏉QL璇彞鐨勬墽琛屾槸閫氳繃server灞傚拰瀛樺偍寮曟搸灞傜殑澶氭浜や簰鎵嶈兘寰楀埌鏈�缁堢粨鏋滅殑銆傛瘮鏂硅涓嬭竟杩欎釜鏌ヨ锛?/p>
SELECT * FROM t WHERE key1 > 'a' AND key1 < 'b' AND common_field != 'a';
server灞備細鍒嗘瀽鍒颁笂杩拌鍙ュ彲浠ヤ娇鐢ㄤ笅杈逛袱绉嶆柟妗堟墽琛岋細
鏂规涓�锛氫娇鐢ㄥ叏琛ㄦ壂鎻?/li>
鏂规浜岋細浣跨敤浜岀骇绱㈠紩idx_key1锛屾鏃堕渶瑕佹壂鎻弅ey1鍒楀�煎湪('a', 'b')涔嬮棿鐨勫叏閮ㄤ簩绾х储寮曡褰曪紝骞朵笖姣忔潯浜岀骇绱㈠紩璁板綍閮介渶瑕佽繘琛屽洖琛ㄦ搷浣溿�?/li>
server灞備細鍒嗘瀽涓婅堪涓や釜鏂规鍝釜鎴愭湰鏇翠綆锛岀劧鍚庨�夊彇鎴愭湰鏇翠綆鐨勯偅涓柟妗堜綔涓烘墽琛岃鍒掋�傜劧鍚庡氨璋冪敤瀛樺偍寮曟搸鎻愪緵鐨勬帴鍙ｆ潵鐪熸鐨勬墽琛屾煡璇簡銆?/p>
杩欓噷鍋囪閲囩敤鏂规浜岋紝涔熷氨鏄娇鐢ㄤ簩绾х储寮昳dx_key1鎵ц涓婅堪鏌ヨ銆傞偅涔坰erver灞傚拰瀛樺偍寮曟搸灞傜殑瀵硅瘽鍙互濡備笅鎵�绀猴細
server灞傦細鈥渉ey锛岄夯鐑﹀幓鏌ユ煡idx_key1浜岀骇绱㈠紩鐨?'a', 'b')鍖洪棿鐨勭涓�鏉¤褰曪紝鐒跺悗鎶婂洖琛ㄥ悗鎶婂畬鏁寸殑璁板綍杩旂粰鎴戝搱鈥?/p>
InnoDB锛氣�滄敹鍒帮紝杩欏氨鍘绘煡鈥濓紝鐒跺悗InnoDB灏遍�氳繃idx_key1浜岀骇绱㈠紩瀵瑰簲鐨凚+鏍戯紝蹇�熷畾浣嶅埌鎵弿鍖洪棿('a', 'b')鐨勭涓�鏉′簩绾х储寮曡褰曪紝鐒跺悗杩涜鍥炶〃锛屽緱鍒板畬鏁寸殑鑱氱皣绱㈠紩璁板綍杩斿洖缁檚erver灞傘�?/p>
server灞傛敹鍒板畬鏁寸殑鑱氱皣绱㈠紩璁板綍鍚庯紝缁х画鍒ゆ柇common_field!='a'鏉′欢鏄惁鎴愮珛锛屽鏋滀笉鎴愮珛鍒欒垗寮冭璁板綍锛屽惁鍒欏皢璇ヨ褰曞彂閫佸埌瀹㈡埛绔�傜劧鍚庡瀛樺偍寮曟搸璇达細鈥滆鎶婁笅涓�鏉¤褰曠粰鎴戝搱鈥?/p>
灏忚创澹細
姝ゅ灏嗚褰曞彂閫佺粰瀹㈡埛绔叾瀹炴槸鍙戦�佸埌鏈湴鐨勭綉缁滅紦鍐插尯锛岀紦鍐插尯澶у皬鐢眓et_buffer_length鎺у埗锛岄粯璁ゆ槸16KB澶у皬銆傜瓑缂撳啿鍖烘弧浜嗘墠鐪熸鍙戦�佺綉缁滃寘鍒板鎴风銆?/p>
InnoDB锛氣�滄敹鍒帮紝杩欏氨鍘绘煡鈥濄�侷nnoDB鏍规嵁璁板綍鐨刵ext_record灞炴�ф壘鍒癷dx_key1鐨?'a', 'b')鍖洪棿鐨勪笅涓�鏉′簩绾х储寮曡褰曪紝鐒跺悗杩涜鍥炶〃鎿嶄綔锛屽皢寰楀埌鐨勫畬鏁寸殑鑱氱皣绱㈠紩璁板綍杩斿洖缁檚erver灞傘�?/p>
灏忚创澹細
server灞傛敹鍒板畬鏁寸殑鑱氱皣绱㈠紩璁板綍鍚庯紝缁х画鍒ゆ柇common_field!='a'鏉′欢鏄惁鎴愮珛锛屽鏋滀笉鎴愮珛鍒欒垗寮冭璁板綍锛屽惁鍒欏皢璇ヨ褰曞彂閫佸埌瀹㈡埛绔�傜劧鍚庡瀛樺偍寮曟搸璇达細鈥滆鎶婁笅涓�鏉¤褰曠粰鎴戝搱鈥?/p>
... 鐒跺悗灏变笉鍋滅殑閲嶅涓婅堪杩囩▼銆?/p>
鐩村埌锛?/p>
涔熷氨鏄洿鍒癐nnoDB鍙戠幇鏍规嵁浜岀骇绱㈠紩璁板綍鐨刵ext_record鑾峰彇鍒扮殑涓嬩竴鏉′簩绾х储寮曡褰曚笉鍦?'a', 'b')鍖洪棿涓紝灏辫窡server灞傝锛氣�滃ソ浜嗭紝('a', 'b')鍖洪棿娌℃湁涓嬩竴鏉¤褰曚簡鈥?/p>
server灞傛敹鍒癐nnoDB璇寸殑娌℃湁涓嬩竴鏉¤褰曠殑娑堟伅锛屽氨缁撴潫鏌ヨ銆?/p>
鐜板湪澶у灏辩煡閬撲簡server灞傚拰瀛樺偍寮曟搸灞傜殑鍩烘湰浜や簰杩囩▼浜嗐�?/p>
閭IMIT鏄粈涔堥锛?br />
璇村嚭鏉ュぇ瀹跺彲鑳芥湁鐐瑰効鎯婅锛孧ySQL鏄湪server灞傚噯澶囧悜瀹㈡埛绔彂閫佽褰曠殑鏃跺�欐墠浼氬幓澶勭悊LIMIT瀛愬彞涓殑鍐呭銆傛嬁涓嬭竟杩欎釜璇彞涓句緥瀛愶細
SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;
濡傛灉浣跨敤idx_key1鎵ц涓婅堪鏌ヨ锛岄偅涔圡ySQL浼氳繖鏍峰鐞嗭細
server灞傚悜InnoDB瑕佺1鏉¤褰曪紝InnoDB浠巌dx_key1涓幏鍙栧埌绗竴鏉′簩绾х储寮曡褰曪紝鐒跺悗杩涜鍥炶〃鎿嶄綔寰楀埌瀹屾暣鐨勮仛绨囩储寮曡褰曪紝鐒跺悗杩斿洖缁檚erver灞傘�俿erver灞傚噯澶囧皢鍏跺彂閫佺粰瀹㈡埛绔紝姝ゆ椂鍙戠幇杩樻湁涓狶IMIT 5000, 1鐨勮姹傦紝鎰忓懗鐫�绗﹀悎鏉′欢鐨勮褰曚腑鐨勭5001鏉℃墠鍙互鐪熸鍙戦�佺粰瀹㈡埛绔紝鎵�浠ュ湪杩欓噷鍏堝仛涓粺璁★紝鎴戜滑鍋囪server灞傜淮鎶や簡涓�涓О浣渓imit_count鐨勫彉閲忕敤浜庣粺璁″凡缁忚烦杩囦簡澶氬皯鏉¤褰曪紝姝ゆ椂灏卞簲璇ュ皢limit_count璁剧疆涓?銆?/li>
server灞傚啀鍚慖nnoDB瑕佷笅涓�鏉¤褰曪紝InnoDB鍐嶆牴鎹簩绾х储寮曡褰曠殑next_record灞炴�ф壘鍒颁笅涓�鏉′簩绾х储寮曡褰曪紝鍐嶆杩涜鍥炶〃寰楀埌瀹屾暣鐨勮仛绨囩储寮曡褰曡繑鍥炵粰server灞傘�俿erver灞傚湪灏嗗叾鍙戦�佺粰瀹㈡埛绔殑鏃跺�欏彂鐜發imit_count鎵嶆槸1锛屾墍浠ュ氨鏀惧純鍙戦�佸埌瀹㈡埛绔殑鎿嶄綔锛屽皢limit_count鍔?锛屾鏃秎imit_count鍙樹负浜?銆?/li>
... 閲嶅涓婅堪鎿嶄綔
鐩村埌limit_count绛変簬5000鐨勬椂鍊欙紝server灞傛墠浼氱湡姝ｇ殑灏咺nnoDB杩斿洖鐨勫畬鏁磋仛绨囩储寮曡褰曞彂閫佺粰瀹㈡埛绔�?/li>
浠庝笂杩拌繃绋嬩腑鎴戜滑鍙互鐪嬪埌锛岀敱浜嶮ySQL涓槸鍦ㄥ疄闄呭悜瀹㈡埛绔彂閫佽褰曞墠鎵嶄細鍘诲垽鏂璍IMIT瀛愬彞鏄惁绗﹀悎瑕佹眰锛屾墍浠ュ鏋滀娇鐢ㄤ簩绾х储寮曟墽琛屼笂杩版煡璇㈢殑璇濓紝鎰忓懗鐫�瑕佽繘琛?001娆″洖琛ㄦ搷浣溿�俿erver灞傚湪杩涜鎵ц璁″垝鍒嗘瀽鐨勬椂鍊欎細瑙夊緱鎵ц杩欎箞澶氭鍥炶〃鐨勬垚鏈お澶т簡锛岃繕涓嶅鐩存帴鍏ㄨ〃鎵弿+filesort蹇憿锛屾墍浠ュ氨閫夋嫨浜嗗悗鑰呮墽琛屾煡璇€�?/p>
鎬庝箞鍔烇紵
鐢变簬MySQL瀹炵幇LIMIT瀛愬彞鐨勫眬闄愭�э紝鍦ㄥ鐞嗚濡侺IMIT 5000, 1杩欐牱鐨勮鍙ユ椂灏辨棤娉曢�氳繃浣跨敤浜岀骇绱㈠紩鏉ュ姞蹇煡璇㈤�熷害浜嗕箞锛熷叾瀹炰篃涓嶆槸锛屽彧瑕佹妸涓婅堪璇彞鏀瑰啓鎴愶細
SELECT * FROM t, (SELECT id FROM t ORDER BY key1 LIMIT 5000, 1) AS d
WHERE t.id = d.id;
杩欐牱锛孲ELECT id FROM t ORDER BY key1 LIMIT 5000, 1浣滀负涓�涓瓙鏌ヨ鍗曠嫭瀛樺湪锛岀敱浜庤瀛愭煡璇㈢殑鏌ヨ鍒楄〃鍙湁涓�涓猧d鍒楋紝MySQL鍙互閫氳繃浠呮壂鎻忎簩绾х储寮昳dx_key1鎵ц璇ュ瓙鏌ヨ锛岀劧鍚庡啀鏍规嵁瀛愭煡璇腑鑾峰緱鍒扮殑涓婚敭鍊煎幓琛╰涓繘琛屾煡鎵俱�?/p>
杩欐牱灏辩渷鍘讳簡鍓?000鏉¤褰曠殑鍥炶〃鎿嶄綔锛屼粠鑰屽ぇ澶ф彁鍗囦簡鏌ヨ鏁堢巼锛?/p>
鍚愪釜妲?br />
璁捐MySQL鐨勫ぇ鍙斿暐鏃跺�欒兘鏀规敼LIMIT瀛愬彞鐨勮繖绉嶈秴绗ㄧ殑瀹炵幇鍛紵杩樺緱鐢ㄦ埛鎵嬪姩鎯虫楠椾紭鍖栧櫒鐨勬柟妗堟墠鑳芥彁鍗囨煡璇㈡晥鐜噡
鍒版杩欑瘒鍏充簬MySQL涓璍IMIT璇彞鐨勬枃绔犲氨浠嬬粛鍒拌繖浜?鏇村鐩稿叧MySQL鐨凩IMIT璇彞鍐呭璇锋悳绱㈣剼鏈箣瀹朵互鍓嶇殑鏂囩珷鎴栫户缁祻瑙堜笅闈㈢殑鐩稿叧鏂囩珷甯屾湜澶у浠ュ悗澶氬鏀寔鑴氭湰涔嬪锛?/p>
