{"id":2428,"date":"2015-08-02T16:03:16","date_gmt":"2015-08-02T08:03:16","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=2428"},"modified":"2015-08-02T16:03:16","modified_gmt":"2015-08-02T08:03:16","slug":"mysql%e7%9a%84%e7%b4%a2%e5%bc%95%e7%9b%b8%e5%85%b3%e7%9f%a5%e8%af%86%e5%ad%a6%e4%b9%a0","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/2428.html","title":{"rendered":"MySQL\u7684\u7d22\u5f15\u76f8\u5173\u77e5\u8bc6\u5b66\u4e60"},"content":{"rendered":"<h6>\u641c\u7d22\u5173\u952e\u5b57\uff1a<\/h6>\n<ul>\n<li>mysql b tree<\/li>\n<li>mysql \u7d22\u5f15 \u539f\u7406<\/li>\n<li>mysql Composite index<\/li>\n<li>mysql \u8054\u5408\u7d22\u5f15<\/li>\n<\/ul>\n<h6>\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/column-indexes.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/column-indexes.html<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/multiple-column-indexes.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/multiple-column-indexes.html<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/verifying-index-usage.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/verifying-index-usage.html<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/index-btree-hash.html\">https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/index-btree-hash.html <\/a>\u00a0#<span style=\"color: #ff0000;\"><strong>characteristics<\/strong><\/span><\/li>\n<li>=<\/li>\n<li><a href=\"http:\/\/blog.codinglabs.org\/articles\/theory-of-mysql-index.html\" target=\"_blank\">MySQL\u7d22\u5f15\u80cc\u540e\u7684\u6570\u636e\u7ed3\u6784\u53ca\u7b97\u6cd5\u539f\u7406<\/a>\u00a0 #<span style=\"color: #ff0000;\"><strong>Nice<\/strong><\/span><\/li>\n<li><a href=\"http:\/\/tech.meituan.com\/mysql-index.html\" target=\"_blank\">MySQL\u7d22\u5f15\u539f\u7406\u53ca\u6162\u67e5\u8be2\u4f18\u5316<\/a>\u00a0 #<span style=\"color: #ff0000;\"><strong>Nice<\/strong><\/span><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/1823685\/when-should-i-use-a-composite-index\">http:\/\/stackoverflow.com\/questions\/1823685\/when-should-i-use-a-composite-index<\/a><\/li>\n<li><a href=\"https:\/\/www.percona.com\/blog\/2014\/01\/03\/multiple-column-index-vs-multiple-indexes-with-mysql-56\/\">https:\/\/www.percona.com\/blog\/2014\/01\/03\/multiple-column-index-vs-multiple-indexes-with-mysql-56\/<\/a><\/li>\n<li><a href=\"https:\/\/www.percona.com\/blog\/2009\/09\/19\/multi-column-indexes-vs-index-merge\/\">https:\/\/www.percona.com\/blog\/2009\/09\/19\/multi-column-indexes-vs-index-merge\/ <\/a>#<span style=\"color: #ff0000;\">\u4f7f\u7528\u8054\u5408\u7d22\u5f15\u800c\u4e0d\u662f\u5206\u522b\u5efa\u7acb\u7d22\u5f15<\/span><\/li>\n<li><a href=\"http:\/\/segmentfault.com\/q\/1010000000342176\">http:\/\/segmentfault.com\/q\/1010000000342176<\/a><\/li>\n<li><a href=\"http:\/\/blog.csdn.net\/xtdhqdhq\/article\/details\/17582779\">http:\/\/blog.csdn.net\/xtdhqdhq\/article\/details\/17582779<\/a><\/li>\n<li><a href=\"http:\/\/blog.163.com\/wangzhenbo85@126\/blog\/static\/101363282201344112039122\/\">http:\/\/blog.163.com\/wangzhenbo85@126\/blog\/static\/101363282201344112039122\/<\/a><\/li>\n<li><a href=\"http:\/\/database.51cto.com\/art\/201107\/278040.htm\" target=\"_blank\">http:\/\/database.51cto.com\/art\/201107\/278040.htm<\/a><\/li>\n<li>=<\/li>\n<li><a href=\"http:\/\/dba.stackexchange.com\/questions\/24489\/how-are-multiple-indexes-used-in-a-query-by-mysql\">http:\/\/dba.stackexchange.com\/questions\/24489\/how-are-multiple-indexes-used-in-a-query-by-mysql<\/a><\/li>\n<li><a href=\"http:\/\/dba.stackexchange.com\/questions\/12618\/when-to-create-multiple-column-index\">http:\/\/dba.stackexchange.com\/questions\/12618\/when-to-create-multiple-column-index<\/a><\/li>\n<li>=<\/li>\n<li><a href=\"http:\/\/imysql.com\/2014\/09\/19\/mysql-faq-is-composite-index-support-different-sort-order.shtml\">http:\/\/imysql.com\/2014\/09\/19\/mysql-faq-is-composite-index-support-different-sort-order.shtml<\/a><\/li>\n<\/ul>\n<h5>\u5728MySQL\u4e2d\u5efa\u7acb\u7d22\u5f15\u7684\u539f\u5219<\/h5>\n<p>\uff081\uff09<strong><span style=\"color: #ff0000;\">\u6700\u5de6\u524d\u7f00\u5339\u914d\u539f\u5219<\/span><\/strong>\uff0c\u975e\u5e38\u91cd\u8981\u7684\u539f\u5219\uff0cmysql\u4f1a\u4e00\u76f4\u5411\u53f3\u5339\u914d\u76f4\u5230\u9047\u5230\u8303\u56f4\u67e5\u8be2(&gt;\u3001&lt;\u3001between\u3001like)\u5c31\u505c\u6b62\u5339\u914d\uff0c\u6bd4\u5982 a = 1 and b = 2 and c &gt; 3 and d = 4 \u5982\u679c\u5efa\u7acb(a,b,c,d)\u987a\u5e8f\u7684\u7d22\u5f15\uff0cd\u662f\u7528\u4e0d\u5230\u7d22\u5f15\u7684\uff0c\u5982\u679c\u5efa\u7acb(a,b,d,c)\u7684\u7d22\u5f15\u5219\u90fd\u53ef\u4ee5\u7528\u5230\uff0ca,b,d\u7684\u987a\u5e8f\u53ef\u4ee5\u4efb\u610f\u8c03\u6574\u3002<\/p>\n<p>\uff082\uff09=\u548cin\u53ef\u4ee5\u4e71\u5e8f\uff0c\u6bd4\u5982 a = 1 and b = 2 and c = 3 \u5efa\u7acb(a,b,c)\u7d22\u5f15\u53ef\u4ee5\u4efb\u610f\u987a\u5e8f\uff0cmysql\u7684\u67e5\u8be2\u4f18\u5316\u5668\u4f1a\u5e2e\u4f60\u4f18\u5316\u6210\u7d22\u5f15\u53ef\u4ee5\u8bc6\u522b\u7684\u5f62\u5f0f\u3002\uff08\u4e0d\u77e5\u9053\u8fd9\u53e5\u8bdd\u7684\u4f9d\u636e\u662f\u4ec0\u4e48\uff1f\u4e0d\u8fc7\u53ef\u4ee5\u901a\u8fc7\u5b9e\u9645\u6d4b\u8bd5\u6765\u68c0\u9a8c\u4e00\u4e0b\uff09<\/p>\n<p>\uff083\uff09\u5c3d\u91cf\u9009\u62e9\u533a\u5206\u5ea6\u9ad8\u7684\u5217\u4f5c\u4e3a\u7d22\u5f15\uff0c\u533a\u5206\u5ea6\u7684\u516c\u5f0f\u662fcount(distinct col)\/count(*)\uff0c\u8868\u793a\u5b57\u6bb5\u4e0d\u91cd\u590d\u7684\u6bd4\u4f8b\uff0c\u6bd4\u4f8b\u8d8a\u5927\u6211\u4eec\u626b\u63cf\u7684\u8bb0\u5f55\u6570\u8d8a\u5c11\uff0c\u552f\u4e00\u952e\u7684\u533a\u5206\u5ea6\u662f1\uff0c\u800c\u4e00\u4e9b\u72b6\u6001\u3001\u6027\u522b\u5b57\u6bb5\u53ef\u80fd\u5728\u5927\u6570\u636e\u9762\u524d\u533a\u5206\u5ea6\u5c31\u662f0\uff0c\u90a3\u53ef\u80fd\u6709\u4eba\u4f1a\u95ee\uff0c\u8fd9\u4e2a\u6bd4\u4f8b\u6709\u4ec0\u4e48\u7ecf\u9a8c\u503c\u5417\uff1f\u4f7f\u7528\u573a\u666f\u4e0d\u540c\uff0c\u8fd9\u4e2a\u503c\u4e5f\u5f88\u96be\u786e\u5b9a\uff0c\u4e00\u822c\u9700\u8981join\u7684\u5b57\u6bb5\u6211\u4eec\u90fd\u8981\u6c42\u662f0.1\u4ee5\u4e0a\uff0c\u5373\u5e73\u57471\u6761\u626b\u63cf10\u6761\u8bb0\u5f55\u3002<\/p>\n<p>\uff084\uff09\u5c3d\u91cf\u7684\u6269\u5c55\u7d22\u5f15\uff0c\u4e0d\u8981\u65b0\u5efa\u7d22\u5f15\u3002\u6bd4\u5982\u8868\u4e2d\u5df2\u7ecf\u6709a\u7684\u7d22\u5f15\uff0c\u73b0\u5728\u8981\u52a0(a,b)\u7684\u7d22\u5f15\uff0c\u90a3\u4e48\u53ea\u9700\u8981\u4fee\u6539\u539f\u6765\u7684\u7d22\u5f15\u5373\u53ef(\u7d22\u5f15\u8d8a\u5c11\u8d8a\u597d\uff1b\u539f\u56e0\uff1a\u4e3b\u8981\u5728\u4fee\u6539\u6570\u636e\u65f6\uff0c\u6bcf\u4e2a\u7d22\u5f15\u90fd\u8981\u8fdb\u884c\u66f4\u65b0\uff0c\u964d\u4f4e\u5199\u901f\u5ea6)\u3002<\/p>\n<h6>\u53c2\u8003\u8bf4\u660e\uff1a<\/h6>\n<p>MySQL\u4e00\u6b21\u67e5\u8be2\u53ea\u80fd\u4f7f\u7528\u4e00\u4e2a\u7d22\u5f15\u3002\u5982\u679c\u8981\u5bf9\u591a\u4e2a\u5b57\u6bb5\u4f7f\u7528\u7d22\u5f15\uff0c\u5efa\u7acb\u590d\u5408\u7d22\u5f15\u3002\u521b\u5efa\u591a\u5217\u7d22\u5f15\u7684\u610f\u4e49\u5c31\u662f\u4e3a\u4e86\u2018\u51cf\u5c11io\u64cd\u4f5c\u2019\u3002<\/p>\n<p>==<\/p>\n<p>InnoDB\u4f7f\u7528B+Tree\u4f5c\u4e3a\u7d22\u5f15\u7ed3\u6784\uff08\u805a\u96c6\u7d22\u5f15\uff09\u3002\u5728InnoDB\u4e2d\uff0c\u8868\u6570\u636e\u6587\u4ef6\u672c\u8eab\u5c31\u662f\u6309B+Tree\u7ec4\u7ec7\u7684\u4e00\u4e2a\u7d22\u5f15\u7ed3\u6784\uff0c\u8fd9\u68f5\u6811\u7684\u53f6\u8282\u70b9data\u57df\u4fdd\u5b58\u4e86\u5b8c\u6574\u7684\u6570\u636e\u8bb0\u5f55\u3002\u8fd9\u4e2a\u7d22\u5f15\u7684key\u662f\u6570\u636e\u8868\u7684\u4e3b\u952e\uff0c\u56e0\u6b64InnoDB\u8868\u6570\u636e\u6587\u4ef6\u672c\u8eab\u5c31\u662f\u4e3b\u7d22\u5f15\u3002<\/p>\n<p>\u5728\u77e5\u9053\u4e86InnoDB\u7684\u7d22\u5f15\u5b9e\u73b0\u540e\uff0c\u5c31\u5f88\u5bb9\u6613\u660e\u767d\u4e3a\u4ec0\u4e48\u4e0d\u5efa\u8bae\u4f7f\u7528\u8fc7\u957f\u7684\u5b57\u6bb5\u4f5c\u4e3a\u4e3b\u952e\uff0c\u56e0\u4e3a\u6240\u6709\u8f85\u52a9\u7d22\u5f15\u90fd\u5f15\u7528\u4e3b\u7d22\u5f15\uff0c\u8fc7\u957f\u7684\u4e3b\u7d22\u5f15\u4f1a\u4ee4\u8f85\u52a9\u7d22\u5f15\u53d8\u5f97\u8fc7\u5927\u3002\u518d\u4f8b\u5982\uff0c\u7528\u975e\u5355\u8c03\u7684\u5b57\u6bb5\u4f5c\u4e3a\u4e3b\u952e\u5728InnoDB\u4e2d\u4e0d\u662f\u4e2a\u597d\u4e3b\u610f\uff0c\u56e0\u4e3aInnoDB\u6570\u636e\u6587\u4ef6\u672c\u8eab\u662f\u4e00\u9897B+Tree\uff0c\u975e\u5355\u8c03\u7684\u4e3b\u952e\u4f1a\u9020\u6210\u5728\u63d2\u5165\u65b0\u8bb0\u5f55\u65f6\u6570\u636e\u6587\u4ef6\u4e3a\u4e86\u7ef4\u6301B+Tree\u7684\u7279\u6027\u800c\u9891\u7e41\u7684\u5206\u88c2\u8c03\u6574\uff0c\u5341\u5206\u4f4e\u6548\uff0c\u800c\u4f7f\u7528\u81ea\u589e\u5b57\u6bb5\u4f5c\u4e3a\u4e3b\u952e\u5219\u662f\u4e00\u4e2a\u5f88\u597d\u7684\u9009\u62e9\u3002<\/p>\n<p>\u56e0\u6b64\uff0c\u53ea\u8981\u53ef\u4ee5\uff0c\u8bf7\u5c3d\u91cf\u5728InnoDB\u4e0a\u91c7\u7528\u81ea\u589e\u5b57\u6bb5\u505a\u4e3b\u952e\u3002<\/p>\n<p>&nbsp;<\/p>\n<p>==<\/p>\n<p>MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.<\/p>\n<p>==<\/p>\n<p>Always check whether all your queries really use the indexes that you have created in the tables. Use the <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/explain.html\" target=\"_blank\">EXPLAIN <\/a>statement, as described in <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/using-explain.html\" target=\"_blank\">Section 8.8.1, \u201cOptimizing Queries with EXPLAIN\u201d<\/a>.<\/p>\n<p>==<\/p>\n<p>If a multiple-column index exists on\u00a0col1\u00a0and\u00a0col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on\u00a0col1\u00a0and\u00a0col2, the optimizer attempts to use the <span style=\"color: #0000ff;\"><strong>Index Merge optimization<\/strong><\/span> (see\u00a0<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/index-merge-optimization.html\">Section 8.2.1.4, \u201cIndex Merge Optimization\u201d<\/a>), or attempts to find the most restrictive index by deciding which index excludes more rows and using that index to fetch the rows.<\/p>\n<p>If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on\u00a0(col1, col2, col3), you have indexed search capabilities on\u00a0(col1),\u00a0(col1, col2), and\u00a0(col1, col2, col3).<\/p>\n<p>MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. Suppose that you have the <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/select.html\">SELECT <\/a>statements shown here:<\/p>\n<pre class=\"lang:default decode:true\">SELECT * FROM tbl_name WHERE col1=val1;\nSELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;\n\/* ... *\/\nSELECT * FROM tbl_name WHERE col2=val2;\nSELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;<\/pre>\n<p>If an index exists on\u00a0(col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but\u00a0(col2)\u00a0and(col2, col3)\u00a0are not leftmost prefixes of\u00a0(col1, col2, col3).<\/p>\n<h6>\u53c2\u8003\u8bf4\u660e2\uff1a<\/h6>\n<p><a href=\"https:\/\/mariadb.com\/kb\/en\/index-condition-pushdown\/\">https:\/\/mariadb.com\/kb\/en\/index-condition-pushdown\/<\/a><\/p>\n<ul>\n<li>\u521b\u5efa\u591a\u5217\u7d22\u5f15(\u52171\uff0c\u52172\uff0c\u52173)\u540e\u7684\u7ed3\u6784\u7c7b\u4f3c\u8fd9\u6837(index\u7ed3\u6784\uff0c\u9644\u5c5e\u5217\uff0c\u9644\u5c5e\u5217)<\/li>\n<li>\u9644\u5c5e\u5217\u53ef\u80fd\u76f4\u63a5\u8ddf\u5728\u53f6\u8282\u70b9\u4e0a\uff0c\u6216\u5355\u72ec\u5b58\u653e\uff08\u8fd9\u4e2a\u6ca1\u6709\u770b\u5230\u8bf4\u660e\uff09&#8230;<\/li>\n<li>\u9644\u5e26\u53ef\u4ee5\u8bf4\u660e\u5355\u5217\u7d22\u5f15\u5c31\u662f\u591a\u5217\u7d22\u5f15\u4e00\u79cd\u5f62\u5f0f\uff0c\u53ea\u4e0d\u8fc7\u6ca1\u6709\u9644\u5c5e\u5217\u7f62\u4e86&#8230;<\/li>\n<\/ul>\n<h6>\u66f4\u591a\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<ul>\n<li><a href=\"http:\/\/www.quora.com\/Whats-the-essential-difference-in-MySQL-between-index-a-b-and-index-b-a\">http:\/\/www.quora.com\/Whats-the-essential-difference-in-MySQL-between-index-a-b-and-index-b-a<\/a><\/li>\n<li><a href=\"https:\/\/www.percona.com\/blog\/2009\/06\/05\/a-rule-of-thumb-for-choosing-column-order-in-indexes\/\">https:\/\/www.percona.com\/blog\/2009\/06\/05\/a-rule-of-thumb-for-choosing-column-order-in-indexes\/<\/a><\/li>\n<li><a href=\"http:\/\/www.ovaistariq.net\/17\/mysql-indexes-multi-column-indexes-and-order-of-columns\/\">http:\/\/www.ovaistariq.net\/17\/mysql-indexes-multi-column-indexes-and-order-of-columns\/<\/a><\/li>\n<li><a href=\"https:\/\/solutionfactor.net\/blog\/2010\/11\/20\/mysql-index-column-order\/\">https:\/\/solutionfactor.net\/blog\/2010\/11\/20\/mysql-index-column-order\/<\/a><\/li>\n<\/ul>\n<p>=EOF=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u641c\u7d22\u5173\u952e\u5b57\uff1a mysql b tree mysql \u7d22\u5f15 \u539f\u7406 mysql Composite index m [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23],"tags":[542,16],"class_list":["post-2428","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","tag-index","tag-mysql"],"views":4726,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2428","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=2428"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2428\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=2428"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=2428"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=2428"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}