{"id":5347,"date":"2020-07-29T21:22:16","date_gmt":"2020-07-29T13:22:16","guid":{"rendered":"http:\/\/39.97.231.10\/?p=5347"},"modified":"2020-07-29T21:22:16","modified_gmt":"2020-07-29T13:22:16","slug":"mysql%e6%80%a7%e8%83%bd%e4%bc%98%e5%8c%96-%e7%b4%a2%e5%bc%95%e6%9d%a1%e4%bb%b6%e4%b8%8b%e6%8e%a8icpindex-condition-pushdown","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=5347","title":{"rendered":"MySQL\u6027\u80fd\u4f18\u5316-\u7d22\u5f15\u6761\u4ef6\u4e0b\u63a8ICP(Index Condition Pushdown)"},"content":{"rendered":"
\n

<\/h2>\n<\/div>\n
\n
\n
\n\u7d22\u5f15\u6761\u4ef6\u4e0b\u63a8\uff0cIndex Condition Pushdown\uff0c\u7b80\u79f0ICP\uff0c\u662fMySQL\u5185\u90e8\u901a\u8fc7\u7d22\u5f15\u67e5\u8be2\u6570\u636e\u7684\u4e00\u79cd\u4f18\u5316\u65b9\u6cd5\uff0c\u7b80\u5355\u6765\u8bf4\u5c31\u662f\u5c06\u539f\u672c\u9700\u8981\u5728Server\u5c42\u5bf9\u6570\u636e\u8fdb\u884c\u8fc7\u6ee4\u7684\u6761\u4ef6\u4e0b\u63a8\u5230\u4e86\u5f15\u64ce\u5c42\u53bb\u505a\uff0c\u5728\u5f15\u64ce\u5c42\u8fc7\u6ee4\u66f4\u591a\u7684\u6570\u636e\uff0c\u8fd9\u6837\u4ece\u5f15\u64ce\u5c42\u53d1\u9001\u5230Server\u5c42\u7684\u6570\u636e\u5c31\u4f1a\u663e\u8457\u51cf\u5c11\uff0c\u4ece\u800c\u4f18\u5316\u6027\u80fd\u3002<\/p>\n

1. ICP\u7d22\u5f15\u4e0b\u63a8\u539f\u7406<\/h4>\n

\u4e3e\u4e00\u4e2a\u4f8b\u5b50\uff0c\u6709\u4e00\u4e2a\u7d22\u5f15\u5982\u4e0b\uff1a
\nidx_all(a,b,c)
\n\u67e5\u8be2\u8bed\u53e5\uff1a
\nselect d from t where a=\\’xx\\’ and b like \\’%xx%\\’ and c like \\’%xx%\\’
\n\u67e5\u8be2\u8d70\u7d22\u5f15idx_all\uff0c\u4f46\u662f\u53ea\u80fd\u4f7f\u7528\u524d\u7f00a\u7684\u90e8\u5206\u3002
\n\u8fd9\u6837\u4e00\u4e2a\u67e5\u8be2\uff0c\u5728\u6ca1\u6709\u4f7f\u7528ICP\u65f6\uff0c\u5b58\u50a8\u5f15\u64ce\u6839\u636e\u7d22\u5f15\u6761\u4ef6a=\\’xx\\’\uff0c\u83b7\u53d6\u8bb0\u5f55\uff0c\u5c06\u8fd9\u4e9b\u8bb0\u5f55\u8fd4\u56de\u7ed9Server\u5c42\uff0cServer\u5c42\u518d\u6839\u636e\u6761\u4ef6 b like \\’%xx%\\’ \u548c c like \\’%xx%\\’ \u6765\u8fdb\u4e00\u6b65\u8fc7\u6ee4\u8bb0\u5f55\uff0c\u6700\u540e\u56de\u8868\u62ff\u5230d\u5b57\u6bb5\u6570\u636e\u8fd4\u56de\u7ed9\u7528\u6237\u3002
\n\u4f7f\u7528ICP\u65f6\uff0c\u5b58\u50a8\u5f15\u64ce\u6839\u636e\u7d22\u5f15\u6761\u4ef6a=\\’xx\\’\uff0c\u83b7\u53d6\u8bb0\u5f55\uff0c\u5e76\u5728\u5f15\u64ce\u5c42\u6839\u636e\u6761\u4ef6 b like \\’%xx%\\’ \u548c c like \\’%xx%\\’ \u6765\u8fc7\u6ee4\u6570\u636e\uff0c\u7136\u540e\u5f15\u64ce\u8fd4\u56de\u8bb0\u5f55\u5230Server\u5c42\uff0c\u663e\u7136\uff0c\u4f7f\u7528ICP\u65f6\uff0c\u8fd4\u56de\u7ed9Server\u5c42\u7684\u8bb0\u5f55\u6570\u91cf\u4f1a\u663e\u8457\u51cf\u5c11\uff0cServer\u5c42\u4e0d\u9700\u8981\u518d\u8fc7\u6ee4\uff0c\u76f4\u63a5\u56de\u8868\u67e5\u8be2\uff0c\u6574\u4f53\u6548\u7387\u5c06\u4f1a\u63d0\u9ad8\u5f88\u591a\u3002
\n\u603b\u7684\u6765\u8bf4\uff0cICP\u4e3b\u8981\u5728\u5f15\u64ce\u5c42\u589e\u52a0\u4e86\u6761\u4ef6\u8fc7\u6ee4\u80fd\u529b\uff0c\u51cf\u5c11\u4e86\u5f15\u64ce\u5c42\u5411Server\u5c42\u4f20\u8f93\u7684\u6570\u636e\u91cf\u3002<\/p>\n

2. ICP\u7d22\u5f15\u4e0b\u63a8\u7684\u9002\u7528\u6761\u4ef6<\/h4>\n
    \n
  1. \u67e5\u8be2\u8d70\u7d22\u5f15\uff0cexplain\u4e2d\u7684\u8bbf\u95ee\u65b9\u5f0f\u4e3arange\uff0cref\uff0ceq_ref\uff0cref_or_null\uff0c\u5e76\u4e14\u9700\u8981\u56de\u8868\u67e5\u8be2\u3002<\/li>\n
  2. ICP\u53ef\u7528\u4e8eInnoDB\u3001MyISAM\u53ca\u5206\u533a\u8868\u3002<\/li>\n
  3. \u5bf9\u4e8eInnoDB\u8868\uff0cICP\u53ea\u9002\u7528\u4e8e\u8d70\u4e8c\u7ea7\u7d22\u5f15\u7684\u67e5\u8be2\u3002<\/li>\n
  4. ICP\u4e0d\u652f\u6301\u5728\u865a\u62df\u5217\u4e0a\u521b\u5efa\u7684\u4e8c\u7ea7\u7d22\u5f15\u3002<\/li>\n
  5. \u5982\u679cwhere\u6761\u4ef6\u6d89\u53ca\u5b50\u67e5\u8be2\uff0c\u5219\u4e0d\u80fd\u4f7f\u7528ICP\u3002<\/li>\n
  6. \u5982\u679cwhere\u6761\u4ef6\u4f7f\u7528\u51fd\u6570\uff0c\u4e0d\u80fd\u4f7f\u7528ICP\u3002<\/li>\n
  7. \u89e6\u53d1\u5668\u6761\u4ef6\u4e5f\u4e0d\u80fd\u4f7f\u7528ICP\u3002<\/li>\n<\/ol>\n

    \u5982\u679c\u4e00\u4e2aSQL\u4f7f\u7528\u4e86ICP\u4f18\u5316\uff0c\u90a3\u4e48\u5728Explain\u7684\u8f93\u51fa\u4e2d\uff0c\u5176Extra\u5217\u4f1a\u663e\u793a Using index condition\u3002<\/p>\n

    3. ICP\u7d22\u5f15\u4e0b\u63a8\u4e3e\u4f8b<\/h4>\n

    \u8868\u7ed3\u6784\uff1a<\/p>\n

    CREATE TABLE `people` (\n\u00a0 `zipcode` varchar(50) DEFAULT NULL,\n\u00a0 `lastname` varchar(50) DEFAULT NULL,\n\u00a0 `address` varchar(50) DEFAULT NULL,\n\u00a0 `age` int(11) DEFAULT \\'0\\',\n\u00a0 KEY `idx_all` (`zipcode`,`lastname`,`address`)\n);\n<\/pre>\n
    select age from people where zipcode=\\’0000005\\’ and lastname like \\’%1%\\’ and address like \\’%1%\\’;
    \n\u8fd9\u4e2aSQL\u8d70\u4e86\u4e8c\u7ea7\u7d22\u5f15 idx_all\uff0c\u5e76\u4e14\u9700\u8981\u56de\u8868\u67e5\u8be2\uff0c\u6240\u4ee5\u6ee1\u8db3ICP\u4f18\u5316\u7684\u6761\u4ef6\uff0c\u4eceexplain\u7684\u7ed3\u679c\u6765\u770b\uff0cExtra\u5b57\u6bb5\u4e5f\u786e\u5b9e\u662fUsing index condition\uff0c\u5982\u4e0b\uff1a<\/p>\n
    mysql> explain select age from people where zipcode=\\'0000005\\' and lastname like \\'%xx%\\' and address like \\'%xx%\\';\n+----+------+---------------+---------+---------+-------+------+----------+-----------------------+\n| id | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |\n+----+------+---------------+---------+---------+-------+------+----------+-----------------------+\n|  1 | ref  | idx_all       | idx_all | 153     | const |    1 |    50.00 | Using index condition |\n+----+------+---------------+---------+---------+-------+------+----------+-----------------------+\n1 row in set, 1 warning (0.00 sec)\n<\/pre>\n
    ICP\u4f18\u5316\u6548\u679c\uff1a<\/p>\n