{"id":31860,"date":"2023-05-26T22:27:53","date_gmt":"2023-05-26T14:27:53","guid":{"rendered":"https:\/\/docs.pingcode.com\/?p=31860"},"modified":"2023-07-31T01:56:31","modified_gmt":"2023-07-30T17:56:31","slug":"using-whereusing-index%e5%92%8cusing-index-condition%e5%8c%ba%e5%88%ab%e6%98%af%e4%bb%80%e4%b9%88","status":"publish","type":"post","link":"https:\/\/docs.pingcode.com\/ask\/31860.html","title":{"rendered":"Using where;Using index\u548cUsing index condition\u533a\u522b\u662f\u4ec0\u4e48"},"content":{"rendered":"
\n

Using where;Using index\u548cUsing index condition\u533a\u522b\u662f\u4ec0\u4e48\uff1ausing index\u4ee3\u8868\u4f7f\u7528\u8986\u76d6\u7d22\u5f15\uff0cusing where\u4ee3\u8868\u6570\u636e\u5e93\u5f15\u64ce\u8fd4\u56de\u7ed3\u679c\u540emysql server\u8fd8\u4f1a\u518d\u6b21\u7b5b\u9009\uff0cusing condition index\u4ee3\u8868\u4f7f\u7528\u4e8c\u7ea7\u7d22\u5f15\u4e0d\u591f\u8fd8\u8981\u56de\u8868\u3002<\/p>\n<\/blockquote>\n

\n
\"\"<\/figure>\n<\/div>\n

\u4e00\u3001Using where;Using index\u548cUsing index condition\u533a\u522b\u662f\u4ec0\u4e48<\/h2>\n

using index\u4ee3\u8868\u4f7f\u7528\u8986\u76d6\u7d22\u5f15\uff0c\u4e0d\u7528\u56de\u8868using where\u4ee3\u8868\u6570\u636e\u5e93\u5f15\u64ce\u8fd4\u56de\u7ed3\u679c\u540emysql server\u8fd8\u4f1a\u518d\u6b21\u7b5b\u9009\u3002\uff08\u5f15\u64ce\u5c31\u662finnodb\u8fd9\u79cd\uff0c\u8981\u6ce8\u610f\u533a\u5206\u5f15\u64ce\u548cmysql server\u7684\u533a\u522b\u3002\uff09using condition index\u4ee3\u8868\u4f7f\u7528\u4e8c\u7ea7\u7d22\u5f15\u4e0d\u591f\u8fd8\u8981\u56de\u8868\uff0c\u4f46\u56de\u8868\u4e4b\u524d\u4f1a\u8fc7\u6ee4\u6b64\u4e8c\u7ea7\u7d22\u5f15\u80fd\u8fc7\u6ee4\u7684where\u6761\u4ef6\u3002\u603b\u4e4b\uff0cUsing where\u3001Using index\u3001Using index condition\u4e09\u8005\u7684\u533a\u522b\u4e3b\u8981\u5728\u4e8e\u662f\u5426\u9700\u8981\u5168\u8868\u626b\u63cf\u3001\u662f\u5426\u4f7f\u7528\u4e86\u8986\u76d6\u7d22\u5f15\u3001\u4ee5\u53ca\u662f\u5426\u91c7\u7528\u4e86\u7d22\u5f15\u6761\u4ef6\u8fc7\u6ee4\u7b49\u6280\u672f\u6765\u63d0\u9ad8\u67e5\u8be2\u6027\u80fd\u3002\u4f7f\u7528\u5408\u9002\u7684\u7d22\u5f15\u548c\u67e5\u8be2\u65b9\u5f0f\uff0c\u53ef\u4ee5\u663e\u8457\u63d0\u9ad8MySQL\u7684\u67e5\u8be2\u6027\u80fd\u548c\u6548\u7387\u3002<\/p>\n

\u4e8c\u3001MySQL\u7d22\u5f15\u4f18\u5316Using where\u3001Using filesort<\/h2>\n

1\u3001\u5b98\u65b9\u5b9a\u4e49<\/h3>\n

Explain\u5206\u6790SQL\u8bed\u53e5\u7684\u65f6\u5019\uff0c\u7ecf\u5e38\u53d1\u73b0\u6709\u7684\u8bed\u53e5\u5728Extra\u5217\u4f1a\u51fa\u73b0Using filesort\uff0c\u6839\u636eMySQL\u5b98\u65b9\u6587\u6863\u5bf9\u4ed6\u7684\u63cf\u8ff0\uff1a<\/p>\n

MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause\u3002<\/p>\n

\u4e2d\u6587\u624b\u518c\u4e0a\u7684\u7ffb\u8bd1\uff1aMysql\u9700\u8981\u989d\u5916\u7684\u4e00\u6b21\u4f20\u9012\uff0c\u4ee5\u627e\u51fa\u5982\u4f55\u6309\u6392\u5e8f\u987a\u5e8f\u68c0\u7d22\u884c\uff0c\u901a\u8fc7\u6839\u636e\u8054\u63a5\u7c7b\u578b\u6d4f\u89c8\u6240\u6709\u884c\u5e76\u4e3a\u6240\u6709\u5339\u914dwhere\u5b50\u53e5\u7684\u884c\u4fdd\u5b58\u6392\u5e8f\u5173\u952e\u5b57\u548c\u884c\u7684\u6307\u9488\u6765\u5b8c\u6210\u6392\u5e8f\uff0c\u7136\u540e\u5173\u952e\u5b57\u88ab\u6392\u5e8f\uff0c\u5e76\u6309\u6392\u5e8f\u987a\u5e8f\u68c0\u7d22\u884c\u3002<\/p>\n

\u603b\u7684\u6765\u8bf4\uff0cUsing filesort \u662fMysql\u91cc\u4e00\u79cd\u901f\u5ea6\u6bd4\u8f83\u6162\u7684\u5916\u90e8\u6392\u5e8f\uff0c\u5982\u679c\u80fd\u907f\u514d\u662f\u8f83\u597d\u7684\u4e86\uff0c\u5f88\u591a\u65f6\u5019\uff0c\u6211\u4eec\u53ef\u4ee5\u901a\u8fc7\u4f18\u5316\u7d22\u5f15\u6765\u5c3d\u91cf\u907f\u514d\u51fa\u73b0Using filesort\uff0c\u4ece\u800c\u63d0\u9ad8\u901f\u5ea6\u3002<\/p>\n

2\u3001\u5b9e\u4f8b<\/h3>\n

\u8fd9\u91cc\u4e3e\u4e2a\u7b80\u5355\u7684\u4f8b\u5b50\uff1a<\/p>\n

CREATE TABLE testing (\nid int(10) unsigned NOT NULL auto_increment,\nroom_number int(10) unsigned NOT NULL default '0',\nPRIMARY KEY (id),\nKEY room_number (room_number)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1<\/code><\/pre>\n
\u5199\u4e2a\u5b58\u50a8\u8fc7\u7a0baskwan\uff0c\u63d2\u516510\u4e07\u6761\u6d4b\u8bd5\u6570\u636e\uff1a<\/p>\n
mysql> DELIMITER $$\n\nDROP PROCEDURE IF EXISTS askwan.askwan\nCREATEPROCEDURE\u2018askwan\u2018.\u2018askwan\u2018()BEGINDECLAREvINTDEFAULT1;WHILEv<100000;DOINSERTINTOtestingVALUES(v,v);SETv=v+1;ENDWHILE;END\n\nmysql> DELIMITER ;\n\nmysql> CALL askwan();\nQuery OK, 1 row affected (13.21 sec)<\/code><\/pre>\n
\u5f00\u59cb\u8bd5\u9a8c\uff0c\u7531\u4e0a\u9762\u4f8b\u5b50\u4e2d\u5efa\u7acb\u7684\u8868\u4fe1\u606f\uff0c\u6211\u5df2\u7ecf\u5efa\u7acb\u4e86\u4e24\u4e2a\u7d22\u5f15\uff0c\u4e00\u4e2a\u4e3b\u952eid\uff0c\u4e00\u4e2aroom_number\u5217\u7d22\u5f15
\u90a3\u73b0\u5728\u6765\u770b\u4e00\u6761SQL\uff1a<\/p>\n
SELECT id FROM testing WHERE room_number=1000 ORDER BY id ;<\/code><\/pre>\n
\u5206\u6790\u4e00\u4e0b\uff1a<\/p>\n
mysql> EXPLAIN SELECT id FROM testing WHERE room_number=1000 ORDER BY id ;+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+| 1 | SIMPLE | testing | ref | room_number | room_number | 4 | const | 1 | Using where; Using filesort |+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+1 row in set (0.00 sec)<\/code><\/pre>\n
\u51fa\u73b0\u4e86Using filesort\uff0c\u5e76\u4e14\u7528\u5230\u4e86room_number\u8fd9\u5217\u7d22\u5f15\uff0c\u4f46\u662f\uff0c\u5728\u8fd9\u91cc\u7528\u5230\u7684\u7d22\u5f15\u662f\u9488\u5bf9WHERE\u540e\u9762\u7684room_number\u6761\u4ef6\u7684\uff0c\u800c\u6700\u540e\u9762\u7684\u6392\u5e8f\u662f\u6839\u636eid\u6765\u7684\uff0c\u8fd9\u5c31\u662f\u624b\u518c\u4e2d\u8bf4\u7684\uff0c\u201c\u989d\u5916\u7684\u4e00\u6b21\u6392\u5e8f\u201d\uff0c\u4e8e\u662f\u5c31\u4f1a\u51fa\u73b0Using filesort\uff0c\u518d\u5efa\u7acb\u4e00\u4e2a\u8054\u5408\u7d22\u5f15 room_number_id\uff1a<\/p>\n
alter table testing add index room_number_id(room_number,id);<\/code><\/pre>\n
\u518d\u6765\u5206\u6790\u4e00\u4e0b\uff1a<\/p>\n
mysql> EXPLAIN SELECT id FROM testing WHERE room_number=1000 ORDER BY id ;+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+--------------------------+| 1 | SIMPLE | testing | ref | room_number,room_number_id | room_number_id | 4 | const | 1 | Using where; |+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+--------------------------+1 row in set (0.00 sec)<\/code><\/pre>\n
\u73b0\u5728Using filesort\u4e0d\u51fa\u73b0\u4e86\u3002<\/p>\n
3\u3001\u603b\u7ed3<\/h3>\n
    \n
  • \u4e00\u822c\u6709order by\u8bed\u53e5\uff0c\u5728\u7d22\u5f15\u52a0\u5f97\u4e0d\u5f53\u7684\u60c5\u51b5\u4e0b\uff0c\u90fd\u6709\u53ef\u80fd\u51fa\u73b0Using filesort\uff0c\u8fd9\u65f6\u5019\u5c31\u8981\u5bf9SQL\u8bed\u53e5\u548c\u7d22\u5f15\u8fdb\u884c\u4f18\u5316\u4e86\uff0c\u4f46\u662f\uff0c\u5e76\u4e0d\u662f\u8bf4\u51fa\u73b0Using filesort\u5c31\u662f\u4e2a\u4e25\u91cd\u7684\u95ee\u9898\uff0c\u4e0d\u662f\u8fd9\u6837\u7684\uff0c\u6b64\u6b21\u4e3e\u7684\u4f8b\u5b50\u6bd4\u8f83\u6781\u7aef\uff0c\u51e0\u4e4e\u4e0d\u592a\u53ef\u80fd\u51fa\u73b0\u8fd9\u4e48\u50bb\u74dc\u7684\u67e5\u8be2\uff0c\u4f18\u5316\u548c\u4e0d\u4f18\u5316\uff0c\u8981\u770b\u5b83\u662f\u4e0d\u662f\u5f71\u54cd\u4e86\u4e1a\u52a1\u6027\u80fd\u3002<\/li>\n
  • \u4ece\u4e0a\u9762\u53ef\u4ee5\u770b\u5230\u8054\u5408\u7d22\u5f15\uff0c\u4e5f\u53ef\u4ee5\u53eb\u591a\u5217\u7d22\u5f15\uff0c\u5f62\u5982 key (\u2018A1\u2032,\u2019A2\u2032,\u2019A3\u2032 ,\u2019A4\u2019)\u7b49\u7684\uff0c\u6392\u5e8f\u7684\u601d\u8def\u4e00\u822c\u662f\uff0c\u5148\u6309\u7167A1\u6765\u6392\u5e8f\uff0cA1\u76f8\u540c\uff0c\u7136\u540e\u6309\u7167A2\u6392\u5e8f\uff0c\u4ee5\u6b64\u7c7b\u63a8\uff0c\u8fd9\u6837\u5bf9\u4e8e\uff08A1\uff09,(A1,A2),(A1,A2,A3)\u7684\u7d22\u5f15\u90fd\u662f\u6709\u6548\u7684\uff0c\u4f46\u662f\u5bf9\u4e8e\uff08A2,A3\uff09\u8fd9\u6837\u7684\u7d22\u5f15\u5c31\u65e0\u6548\u4e86\u3002<\/li>\n<\/ul>\n
    \u9700\u8981\u4e86\u89e3MySQL \u7684\u7279\u6027<\/strong>\uff1a<\/p>\n
      \n
    • \u4e00\u6761 SQL \u8bed\u53e5\u53ea\u80fd\u4f7f\u7528 1 \u4e2a\u7d22\u5f15 (5.0-)\uff0cMySQL \u6839\u636e\u8868\u7684\u72b6\u6001\uff0c\u9009\u62e9\u4e00\u4e2a\u5b83\u8ba4\u4e3a\u8f83\u597d\u7684\u7d22\u5f15\u7528\u4e8e\u4f18\u5316\u67e5\u8be2\uff1b<\/li>\n
    • \u8054\u5408\u7d22\u5f15\uff0c\u53ea\u80fd\u6309\u4ece\u5de6\u5230\u53f3\u7684\u987a\u5e8f\u4f9d\u6b21\u4f7f\u7528\uff1b<\/li>\n
    • \u4ece\u4e0a\u8fb9\u53ef\u4ee5\u770b\u5230\u7ed3\u5408\u7d22\u5f15\uff0c\u4e5f\u53ef\u4ee5\u53eb\u591a\u5217\u7d22\u5f15\uff0c\u5f62\u5982 key (\u2018B1\u2032,\u2019B2\u2032,\u2019B3\u2032 ,\u2019B4\u2019)\u7b49\u7684\uff0c\u6392\u5e8f\u7684\u601d\u8def\u901a\u5e38\u4e3a\uff0c\u5148\u6309\u7167B1\u6765\u6392\u5e8f\uff0cB1\u76f8\u540c\uff0c\u7136\u540e\u6309\u7167B2\u6392\u5e8f\uff0c\u4ee5\u6b64\u7c7b\u63a8\uff0c\u8fd9\u6837\u5bf9\u4e8e\uff08B1\uff09,(B1,B2), (B1,B2,B3)\u7684\u7d22\u5f15\u90fd\u662f\u6709\u6548\u7684\uff0c\u53ef\u662f\u5bf9\u4e8e\uff08B2,B3\uff09\u8fd9\u6837\u7684\u7d22\u5f15\u5c31\u65e0\u6548\u4e86\u3002<\/li>\n<\/ul>\n
      \u6839\u636e\u8fd9\u4e2a\u7279\u6027\u5c31\u53ef\u4ee5\u89e3\u51b3\u95ee\u9898\uff1a<\/strong><\/p>\n
      user_id \u548c item_id \u662f 2 \u4e2a\u7d22\u5f15\uff0c\u6211\u7684\u8bed\u53e5\u4e2d\uff0cMySQL \u9009\u62e9\u4e86 user_id\uff0c\u90a3\u4e48 item_id \u7684\u7d22\u5f15\u6ca1\u6709\u8d77\u5230\u4efb\u4f55\u7528\u5904\uff0c\u6240\u4ee5\uff0c\u5f53\u8981\u6392\u5e8f\u7684\u65f6\u5019\uff0c\u7531\u4e8e\u8bb0\u5f55\u6570\u8f83\u591a\uff0c\u5185\u5b58\u4e2d\u7684\u6392\u5e8f buffer \u6ee1\u4e86\uff0c\u53ea\u80fd Using filesort \u8fdb\u884c\u5916\u90e8\u6392\u5e8f\uff0c\u56e0\u6b64\u6bcf\u6b21\u67e5\u8be2\u8981\u4ece\u78c1\u76d8\u8bfb\u53d6\u51e0\u5341 M \u7684\u6570\u636e\uff0c\u901f\u5ea6\u5f88\u6162\u3002<\/p>\n
      \u4fee\u6539\u8868\u7ed3\u6784\uff0c\u5220\u9664 user_id \u548c item_id \u7684 INDEX \u7d22\u5f15\uff0c\u5efa\u7acb\u4e00\u4e2a\u540d\u4e3a user_item \u7684\u8054\u5408 UNIQUE \u7d22\u5f15\uff0c\u987a\u5e8f\u662f\u5148 user_id \u540e item_id\uff0c\u518d EXPLAIN\uff0c\u8fd9\u56de\u53ea\u6709 Using where \u4e86\u3002<\/p>\n
      \u5ef6\u4f38\u9605\u8bfb1\uff1aMySQL\u7248\u672c\u4ecb\u7ecd<\/em><\/strong><\/p>\n
      \u9488\u5bf9\u4e0d\u540c\u7684\u7528\u6237\uff0cMySQL\u5206\u4e3a\u4e24\u4e2a\u4e0d\u540c\u7684\u7248\u672c\uff1a<\/p>\n
        \n
      • MySQL Community Server\uff08\u793e\u533a\u7248\uff09<\/strong>\uff1a\u8be5\u7248\u672c\u5b8c\u5168\u514d\u8d39\uff0c\u4f46\u662f\u5b98\u65b9\u4e0d\u63d0\u4f9b\u6280\u672f\u652f\u6301\u3002\u7528\u6237\u53ef\u4ee5\u81ea\u7531\u4e0b\u8f7d\u4f7f\u7528\u3002<\/li>\n
      • MySQL Enterprise Server(\u4f01\u4e1a\u7248\u670d\u52a1\u5668)<\/strong>\uff1a\u4e3a\u4f01\u4e1a\u63d0\u4f9b\u6570\u636e\u5e93\u5e94\u7528\uff0c\u652f\u6301ACID\u4e8b\u52a1\u5904\u7406\uff0c\u63d0\u4f9b\u5b8c\u6574\u7684\u63d0\u4ea4\u3001\u56de\u6eda\u3001\u5d29\u6e83\u6062\u590d\u548c\u884c\u653f\u9501\u5b9a\u529f\u80fd\u3002\u9700\u8981\u4ed8\u8d39\u4f7f\u7528\uff0c\u5b98\u65b9\u63d0\u4f9b\u6280\u672f\u652f\u6301\u3002<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"Using where;Using index\u548cUsing index condition\u533a\u522b\u662f\u4ec0\u4e48\uff1ausin […]","protected":false},"author":1,"featured_media":31863,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[37],"tags":[119],"acf":[],"_links":{"self":[{"href":"https:\/\/docs.pingcode.com\/wp-json\/wp\/v2\/posts\/31860"}],"collection":[{"href":"https:\/\/docs.pingcode.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/docs.pingcode.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/docs.pingcode.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/docs.pingcode.com\/wp-json\/wp\/v2\/comments?post=31860"}],"version-history":[{"count":0,"href":"https:\/\/docs.pingcode.com\/wp-json\/wp\/v2\/posts\/31860\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/docs.pingcode.com\/wp-json\/wp\/v2\/media\/31863"}],"wp:attachment":[{"href":"https:\/\/docs.pingcode.com\/wp-json\/wp\/v2\/media?parent=31860"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/docs.pingcode.com\/wp-json\/wp\/v2\/categories?post=31860"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/docs.pingcode.com\/wp-json\/wp\/v2\/tags?post=31860"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}