{"version":"1.0","provider_name":"Jui-Nan Lin&#039;s Blog","provider_url":"https:\/\/jnlin.org","author_name":"jnlin","author_url":"https:\/\/jnlin.org\/author\/jnlin\/","title":"MySQL 5.6 \u7684 Index Condition Pushdown","html":"MySQL 5.6 \u4ee5\u524d\u7684 Multi-column Index\uff0c\u7576\u4f4d\u65bc index \u4e2d\u9593\u7684 column\uff08\u5982\u4e0b\u4f8b\u7684 j\uff09\u9700\u8981\u9032\u884c range query \u7684\u6642\u5019\uff0c\u53ea\u80fd\u5229\u7528\u5230\u90e8\u5206\u7684 index\uff0c\u9700\u8981\u53e6\u5916\u8b80\u53d6\u8cc7\u6599\u5217\u7684\u5167\u5bb9\u4f86\u9032\u884c\u5224\u65b7\u3002\u8209\u4f8b\u4f86\u8aaa\uff0c\u5982\u679c\u6709\u4e00\u500b Table \u7d50\u69cb\u662f\u9019\u6a23\uff1a\r\n<code>\r\nCREATE TABLE mytable (\r\n  id int not null auto_increment primary key,\r\n  i int(11) NOT NULL,\r\n  j int(11) NOT NULL,\r\n  k int(11) NOT NULL,\r\n  val char(10) NOT NULL,\r\n  KEY ijk (i,j,k)\r\n) ENGINE=InnoDB;\r\n<\/code>\r\n\r\n\u5728 MySQL 5.6 \u4e4b\u524d\uff0cSQL Query \u300c<code>SELECT sum(length(val)) FROM T WHERE i=1 AND j&lt;100 AND k=100<\/code>\u300d \u6703\u628a\u6240\u6709 i=1 \u7684\u8cc7\u6599\u5217\u62c9\u51fa\u4f86\uff0c\u9010\u4e00\u6bd4\u8f03 j \u8207 k \u7684\u503c\u3002MySQL 5.6 \u7684 Index Condition Pushdown (ICP) \u529f\u80fd\uff0c\u628a\u9019\u500b\u52d5\u4f5c\u6539\u70ba\u6bd4\u8f03 Index tuple \u800c\u975e\u8cc7\u6599\u5217\u5167\u5bb9\uff0c\u53ef\u907f\u514d\u62c9\u51fa\u6574\u500b\u8cc7\u6599\u5217\uff0c\u964d\u4f4e\u78c1\u789f IO\uff08\u56e0\u70ba\u8cc7\u6599\u5217\u901a\u5e38\u6bd4 Index \u5927\uff09\u3002\r\n\r\n\u8a73\u7d30\u7684\u8aaa\u660e\u53ef\u4ee5\u770b MySQL \u7684\u5b98\u65b9\u6587\u4ef6\uff1a<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/index-condition-pushdown-optimization.html\">Index Condition Pushdown Optimization<\/a> \u8207 Percona \u7684\u6e2c\u8a66\uff1a<a href=\"http:\/\/www.mysqlperformanceblog.com\/2014\/01\/03\/multiple-column-index-vs-multiple-indexes-with-mysql-56\/\">Multiple column index vs multiple indexes with MySQL 5.6<\/a>\u3002","type":"rich"}