{"id":158417,"date":"2020-09-06T07:31:37","date_gmt":"2020-09-05T23:31:37","guid":{"rendered":"http:\/\/4563.org\/?p=158417"},"modified":"2020-09-06T07:31:37","modified_gmt":"2020-09-05T23:31:37","slug":"%e6%9c%89%e6%9d%a1%e5%be%88%e7%ae%80%e5%8d%95%e7%9a%84-sql-explain-%e5%90%8e-extra-%e6%9c%89%e4%b8%8d%e6%98%8e%e7%99%bd%e7%9a%84%ef%bc%8c%e8%bf%98%e6%9c%9b%e5%a4%a7%e5%ae%b6%e6%8c%87%e7%82%b9%e4%b8%80","status":"publish","type":"post","link":"http:\/\/4563.org\/?p=158417","title":{"rendered":"\u6709\u6761\u5f88\u7b80\u5355\u7684 sql explain \u540e extra \u6709\u4e0d\u660e\u767d\u7684\uff0c\u8fd8\u671b\u5927\u5bb6\u6307\u70b9\u4e00\u4e8c\uff01"},"content":{"rendered":"<div>\n<div>\n<div>\n<h1>                  \u6709\u6761\u5f88\u7b80\u5355\u7684 sql explain \u540e extra \u6709\u4e0d\u660e\u767d\u7684\uff0c\u8fd8\u671b\u5927\u5bb6\u6307\u70b9\u4e00\u4e8c\uff01               <\/h1>\n<p> <\/p>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : aimaodeyuer <\/span>  <span><i><\/i> 9<\/span> <\/div>\n<div> <\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<div isfirst=\"1\"> <\/p>\n<pre><code>CREATE TABLE msg (   id bigint(20) unsigned NOT NULL AUTO_INCREMENT,   msg_id bigint(20) NOT NULL,   status tinyint(4),   content text NOT NULL,   create_time datetime NOT NULL,   PRIMARY KEY (id),   KEY idx_msg_id (msg_id) ) ENGINE=InnoDB AUTO_INCREMENT=3048 DEFAULT CHARSET=utf8mb4 <\/code><\/pre>\n<h4>select id from msg where msg_id = #{param1} order by id desc limit 10;<\/h4>\n<p>\u663e\u793a\u8d70\u4e86 idx_msg_id \u7d22\u5f15\uff0cextra \u662f Using where; Using index \u3002 \u53bb\u6389 order by id \u540e \uff0cextra \u662f Using index \u3002<\/p>\n<p>PS:\u9ed8\u8ba4\u7d22\u5f15\u6269\u5c55 use_index_extensions=on<\/p>\n<p>Q:\u65e2\u7136 idx_msg_id \u7d22\u5f15\u4e0a\u6709 id\uff0c\u4e14 select \u53ea\u67e5\u8be2 id\uff0corder by id \u4e3a\u4f55\u4f1a\u5bfc\u81f4\u51fa\u73b0 Using where;<\/p>\n<p>\u9644\u4e0a mysql \u5b98\u65b9\u6587\u6863\uff1ahttps:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/index-extensions.html \u770b\u5b8c\u4e5f\u6ca1\u5f04\u660e\u767d\u3002<\/p>\n<\/p><\/div>\n<div> <b>\u5927\u4f6c\u6709\u8a71\u8aaa<\/b> (<span>3<\/span>)        <\/div>\n<div> <\/div>\n<\/p><\/div>\n<\/p><\/div>\n<ul>\n<li data-pid=\"3383420\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u4e3b<\/span> <span>\u8cc7\u6df1\u5927\u4f6c : aimaodeyuer <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             \u963f\uff0c\u8fd9\u4e48\u4e45\u4e86\uff0c\u6ca1\u4eba\u5417\uff1f                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"3383421\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : tomy0608 <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             \u6309\u7167\u6587\u6863\u4e0a\u7684\u610f\u601d, \u5c31\u662f\u5728\u4f60\u5f00\u542f\u4e86 index_extensions \u4e4b\u540e, \u4f1a\u5728\u4f60\u81ea\u5efa\u7684\u7d22\u5f15\u57fa\u7840\u4e0a\u62d3\u5c55\u4e00\u4e2a\u7c7b\u4f3c\u8054\u5408\u7d22\u5f15\u7684\u6837\u5b50. \u4ece\u4f60\u8fd9\u4e2a\u8868\u6765\u8bf4\u5c31\u662f `index(msg_id, id)`. <br \/>\u6267\u884c\u5668\u5668\u5728\u6267\u884c\u7684\u65f6\u5019\u80fd\u63d0\u9ad8\u6027\u80fd. \u539f\u8bdd\u662f\u8fd9\u6837\u7684<br \/>&#8220;`<br \/>The optimizer takes into account the primary key columns of the extended secondary index when determining how and whether to use that index. This can result in more efficient query execution plans and better performance.<br \/>&#8220;`<\/p>\n<p>\u4f46\u4f60\u8fd9\u4e2a\u5b9e\u9645\u8ddf\u8fd9\u4e2a\u6ca1\u5565\u5173\u7cfb. `Using where` \u8868\u793a MySQL \u670d\u52a1\u5668\u4f1a\u5728\u641c\u7d22\u5f15\u64ce\u6267\u884c\u5b8c\u4e4b\u540e\u518d\u8fdb\u884c\u8fc7\u6ee4\u64cd\u4f5c. <\/p>\n<p>\u5728\u4f60\u6ca1\u6709 order by \u65f6, \u76f4\u63a5\u901a\u8fc7\u7d22\u5f15\u5b9a\u4f4d\u5230\u4e86, \u6240\u4ee5\u5e76\u4e0d\u9700\u8981\u8fc7\u6ee4\u64cd\u4f5c.(\u53ef\u4ee5\u8fd9\u4e48\u8ba4\u4e3a)<\/p>\n<p>\u4f46\u662f\u4f60\u8981\u6392\u5e8f(order by)\u7684\u8bdd, \u662f\u9700\u8981\u57fa\u4e8e\u4e00\u4e2a\u7ed3\u679c\u6392\u5e8f\u7684, \u8fd9\u4e2a\u7ed3\u679c\u4fbf\u662f Using index \u67e5\u51fa\u6765\u7684\u7ed3\u679c. (\u867d\u7136\u4f60\u8fd9\u67e5\u51fa\u6765\u53ef\u80fd\u53ea\u6709\u4e00\u4e2a)                                                            <\/p><\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"3383422\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u4e3b<\/span> <span>\u8cc7\u6df1\u5927\u4f6c : aimaodeyuer <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             @tomy0608 \u9996\u5148\u611f\u8c22\u8001\u54e5\u7684\u89e3\u7b54\uff0c\u6211\u5148\u7406\u89e3\u4e00\u6ce2\u4f60\u7684\u8bed\u4e49<\/p>\n<p>\u4f46\u662f\u4f60\u8981\u6392\u5e8f(order by)\u7684\u8bdd, \u662f\u9700\u8981\u57fa\u4e8e\u4e00\u4e2a\u7ed3\u679c\u6392\u5e8f\u7684, \u8fd9\u4e2a\u7ed3\u679c\u4fbf\u662f Using index \u67e5\u51fa\u6765\u7684\u7ed3\u679c. (\u867d\u7136\u4f60\u8fd9\u67e5\u51fa\u6765\u53ef\u80fd\u53ea\u6709\u4e00\u4e2a)<\/p>\n<p>1.\u8fd9\u4e2a msg_id \u4e0d\u662f\u552f\u4e00\u952e\uff0c\u662f\u591a\u503c\u7684\u3002<br \/>2.idx_msg_id \u7d22\u5f15\u662f\u6709 id \u7684\u503c\u7684\u6240\u4ee5\u4e0d\u9700\u8981\u56de\u8868\uff0c\u800c\u4e14\u662f\u6709\u5e8f\u7684\uff0c\u6240\u4ee5\u4e0d\u9700\u8981\u989d\u5916\u7684\u5916\u90e8\u6392\u5e8f\uff08 Using filesort \uff09<br \/>\u95ee\u9898\u5c31\u662f\u6211\u8fd8\u662f\u4e0d\u7406\u89e3\u4f55\u6765 using where \u4e00\u8bf4\uff0c\u4e0d\u5e94\u8be5\u76f4\u63a5\u7d22\u5f15\u8986\u76d6\u5c31\u5b8c\u4e8b\u4e86\u5417\uff1f                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li>\n","protected":false},"excerpt":{"rendered":"<p>\u6709\u6761\u5f88\u7b80\u5355\u7684 sql explai&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[],"tags":[],"_links":{"self":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/158417"}],"collection":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=158417"}],"version-history":[{"count":0,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/158417\/revisions"}],"wp:attachment":[{"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=158417"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=158417"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=158417"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}