{"id":1231,"date":"2022-02-10T16:53:41","date_gmt":"2022-02-10T08:53:41","guid":{"rendered":"http:\/\/dba.qishuo.xin\/?p=1231"},"modified":"2022-02-10T17:33:51","modified_gmt":"2022-02-10T09:33:51","slug":"mariadb%e6%89%a7%e8%a1%8c%e8%ae%a1%e5%88%92%e6%a1%88%e4%be%8b","status":"publish","type":"post","link":"http:\/\/dba.qishuo.xin\/?p=1231","title":{"rendered":"MariaDB\u6267\u884c\u8ba1\u5212\u6848\u4f8b"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_76 ez-toc-wrap-right counter-hierarchy ez-toc-counter ez-toc-light-blue ez-toc-container-direction\">\n<label for=\"ez-toc-cssicon-toggle-item-6a0e53b270334\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a0e53b270334\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"http:\/\/dba.qishuo.xin\/?p=1231\/#1_%E8%A1%A8%E4%B8%8E%E7%B4%A2%E5%BC%95%E4%BB%8B%E7%BB%8D\" >1. \u8868\u4e0e\u7d22\u5f15\u4ecb\u7ecd<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"http:\/\/dba.qishuo.xin\/?p=1231\/#11_%E8%A1%A8_mc_subsidyrecord%EF%BC%88%E8%A1%A5%E5%8A%A9%E8%AE%B0%E5%BD%95%E8%A1%A8%EF%BC%89\" >1.1. \u8868 mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"http:\/\/dba.qishuo.xin\/?p=1231\/#12_%E8%A1%A8mc_subsidybatch_account\" >1.2. \u8868mc_subsidybatch_account<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"http:\/\/dba.qishuo.xin\/?p=1231\/#13_%E5%85%B3%E8%81%94%E5%85%B3%E7%B3%BB\" >1.3. \u5173\u8054\u5173\u7cfb<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"http:\/\/dba.qishuo.xin\/?p=1231\/#14_%E9%9C%80%E8%A6%81%E8%A7%A3%E5%86%B3%E7%9A%84%E9%97%AE%E9%A2%98\" >1.4. \u9700\u8981\u89e3\u51b3\u7684\u95ee\u9898<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"http:\/\/dba.qishuo.xin\/?p=1231\/#2_%E5%88%86%E6%9E%90JOIN%E7%9A%84%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92\" >2. \u5206\u6790JOIN\u7684\u6267\u884c\u8ba1\u5212<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"http:\/\/dba.qishuo.xin\/?p=1231\/#21_%E6%96%B9%E6%B3%95%E4%B8%80\" >2.1. \u65b9\u6cd5\u4e00<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"http:\/\/dba.qishuo.xin\/?p=1231\/#22_%E6%96%B9%E6%B3%95%E4%BA%8C\" >2.2. \u65b9\u6cd5\u4e8c<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"http:\/\/dba.qishuo.xin\/?p=1231\/#23_%E6%96%B9%E6%B3%95%E4%B8%89\" >2.3. \u65b9\u6cd5\u4e09<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"http:\/\/dba.qishuo.xin\/?p=1231\/#24_%E6%96%B9%E6%B3%95%E5%9B%9B\" >2.4. \u65b9\u6cd5\u56db<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h3><span class=\"ez-toc-section\" id=\"1_%E8%A1%A8%E4%B8%8E%E7%B4%A2%E5%BC%95%E4%BB%8B%E7%BB%8D\"><\/span>1. \u8868\u4e0e\u7d22\u5f15\u4ecb\u7ecd<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h4><span class=\"ez-toc-section\" id=\"11_%E8%A1%A8_mc_subsidyrecord%EF%BC%88%E8%A1%A5%E5%8A%A9%E8%AE%B0%E5%BD%95%E8%A1%A8%EF%BC%89\"><\/span>1.1. \u8868 mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>\u5171\u6709\u8bb0\u5f55\u6570 8959 \u884c\uff0c\u8868\u7ed3\u6784\u4e0e\u7d22\u5f15\u5982\u4e0b\uff1a<\/p>\n<pre><code class=\"line-numbers\">MariaDB [etdb]&gt; desc mc_subsidyrecord;\n+----------------------+---------------+------+-----+---------+-------+\n| Field                | Type          | Null | Key | Default | Extra |\n+----------------------+---------------+------+-----+---------+-------+\n| RecNum               | bigint(20)    | NO   | PRI | NULL    |       |\n| CardAccNum           | bigint(20)    | YES  |     | NULL    |       |\n| BatchNum             | bigint(20)    | NO   | MUL | 0       |       |\n| AccNum               | bigint(20)    | NO   |     | NULL    |       |\n| EpID                 | int(11)       | NO   |     | NULL    |       |\n| EWalletID            | tinyint(4)    | NO   |     | NULL    |       |\n| SubsidyMoney         | decimal(12,2) | NO   |     | NULL    |       |\n| EffectTime           | datetime      | NO   |     | NULL    |       |\n| DisableTime          | datetime      | YES  |     | NULL    |       |\n| IsGenerateRedRedcord | tinyint(4)    | NO   |     | 0       |       |\n| GrantTime            | datetime      | NO   |     | NULL    |       |\n| SubsidySID           | int(11)       | NO   |     | NULL    |       |\n| DealFlag             | tinyint(4)    | NO   | PRI | NULL    |       |\n| DealTime             | datetime      | YES  |     | NULL    |       |\n| GrantRecNum          | bigint(20)    | NO   | MUL | NULL    |       |\n| DealRecNum           | bigint(20)    | YES  |     | NULL    |       |\n| AppCode              | char(3)       | YES  |     | NULL    |       |\n| Remark               | varchar(32)   | YES  |     | NULL    |       |\n+----------------------+---------------+------+-----+---------+-------+\n18 rows in set (0.001 sec)\n\nMariaDB [etdb]&gt; show index from mc_subsidyrecord;\n+------------------+------------+-----------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\n| Table            | Non_unique | Key_name                    | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |\n+------------------+------------+-----------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\n| mc_subsidyrecord |          0 | PRIMARY                     |            1 | RecNum       | A         |        8960 |     NULL | NULL   |      | BTREE      |         |               |\n| mc_subsidyrecord |          0 | PRIMARY                     |            2 | DealFlag     | A         |        8960 |     NULL | NULL   |      | BTREE      |         |               |\n| mc_subsidyrecord |          1 | IDX_SubsidyRecord1          |            1 | DealFlag     | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |\n| mc_subsidyrecord |          1 | IDX_SubsidyRecord1          |            2 | CardAccNum   | A         |        8960 |     NULL | NULL   | YES  | BTREE      |         |               |\n| mc_subsidyrecord |          1 | IDX_SubsidyRecord1          |            3 | EpID         | A         |        8960 |     NULL | NULL   |      | BTREE      |         |               |\n| mc_subsidyrecord |          1 | IDX_SubsidyRecord1          |            4 | EWalletID    | A         |        8960 |     NULL | NULL   |      | BTREE      |         |               |\n| mc_subsidyrecord |          1 | IDX_SubsidyRecord1          |            5 | SubsidySID   | A         |        8960 |     NULL | NULL   |      | BTREE      |         |               |\n| mc_subsidyrecord |          1 | IDX_SubsidyRecord1          |            6 | SubsidyMoney | A         |        8960 |     NULL | NULL   |      | BTREE      |         |               |\n| mc_subsidyrecord |          1 | FK_SubsidyRecord_DealRecNum |            1 | GrantRecNum  | A         |        8960 |     NULL | NULL   |      | BTREE      |         |               |\n| mc_subsidyrecord |          1 | idx_SubsidyRecord_BatchNum  |            1 | BatchNum     | A         |         172 |     NULL | NULL   |      | BTREE      |         |               |\n| mc_subsidyrecord |          1 | idx_SubsidyRecord_BatchNum  |            2 | CardAccNum   | A         |        8960 |     NULL | NULL   | YES  | BTREE      |         |               |\n| mc_subsidyrecord |          1 | idx_SubsidyRecord_BatchNum  |            3 | AccNum       | A         |        8960 |     NULL | NULL   |      | BTREE      |         |               |\n+------------------+------------+-----------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\n12 rows in set (0.000 sec)\n<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"12_%E8%A1%A8mc_subsidybatch_account\"><\/span>1.2. \u8868mc_subsidybatch_account<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>\u5171\u6709\u8bb0\u5f55\u65706259\u884c\uff0c\u8868\u7ed3\u6784\u4e0e\u7d22\u5f15\u5982\u4e0b\uff1a<\/p>\n<pre><code class=\"line-numbers\">MariaDB [etdb]&gt; desc mc_subsidybatch_account\uff08\u6279\u6b21\u4eba\u5458\u8868\uff09;\n+---------------+---------------+------+-----+---------+-------+\n| Field         | Type          | Null | Key | Default | Extra |\n+---------------+---------------+------+-----+---------+-------+\n| RecNum        | bigint(20)    | NO   | PRI | NULL    |       |\n| BatchNum      | bigint(20)    | NO   | MUL | NULL    |       |\n| AccNum        | bigint(20)    | NO   | MUL | NULL    |       |\n| CardAccNum    | bigint(20)    | YES  | MUL | NULL    |       |\n| SubsidyMoney  | decimal(12,2) | NO   |     | NULL    |       |\n| Remark        | varchar(128)  | YES  |     | NULL    |       |\n| GrantFlag     | tinyint(4)    | NO   |     | NULL    |       |\n| ErrorInfo     | varchar(256)  | YES  |     | NULL    |       |\n| IsRed         | tinyint(4)    | NO   |     | 0       |       |\n| RedFlag       | tinyint(4)    | NO   |     | 0       |       |\n| RedRecnum     | bigint(20)    | NO   |     | 0       |       |\n| RedRemark     | varchar(64)   | YES  |     | NULL    |       |\n| SubsidyRecnum | bigint(20)    | NO   |     | 0       |       |\n| DealTime      | datetime      | YES  |     | NULL    |       |\n+---------------+---------------+------+-----+---------+-------+\n14 rows in set (0.001 sec)\n\nMariaDB [etdb]&gt; show index from mc_subsidybatch_account;\n+-------------------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\n| Table                   | Non_unique | Key_name                  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |\n+-------------------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\n| mc_subsidybatch_account |          0 | PRIMARY                   |            1 | RecNum      | A         |        6259 |     NULL | NULL   |      | BTREE      |         |               |\n| mc_subsidybatch_account |          1 | FK_SubsidyBatch_Account_2 |            1 | BatchNum    | A         |         113 |     NULL | NULL   |      | BTREE      |         |               |\n| mc_subsidybatch_account |          1 | FK_SubsidyBatch_Account_1 |            1 | AccNum      | A         |        6259 |     NULL | NULL   |      | BTREE      |         |               |\n| mc_subsidybatch_account |          1 | FK_SubsidyBatch_Account_3 |            1 | CardAccNum  | A         |        6259 |     NULL | NULL   | YES  | BTREE      |         |               |\n+-------------------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\n4 rows in set (0.000 sec)\n<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"13_%E5%85%B3%E8%81%94%E5%85%B3%E7%B3%BB\"><\/span>1.3. \u5173\u8054\u5173\u7cfb<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<ol>\n<li>mc_subsidybatch_account\uff08\u6279\u6b21\u4eba\u5458\u8868\uff09\u4e2d\u5b57\u6bb5 SubsidyRecnum \u6765\u6e90\u4e8e mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09\u4e2d\u5b57\u6bb5 RecNum\uff0c\u4f46\u662f\u5b57\u6bb5 SubsidyRecnum\u4e0a\u6ca1\u6709\u7d22\u5f15\u3002<\/li>\n<li>mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09\u4e2d\u5b57\u6bb5BatchNum \u6765\u6e90\u4e8emc_subsidybatch_account\uff08\u6279\u6b21\u4eba\u5458\u8868\uff09\u4e2d\u5b57\u6bb5 BatchNum\uff0c\u5176\u4e2dBatchNum\u5b57\u6bb5\u5728\u4e24\u4e2a\u8868\u4e2d\u90fd\u6709\u7d22\u5f15\u3002<\/li>\n<li>mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09\u4e2d BatchNum > 0 \u7684\u8bb0\u5f55\u90fd\u6765\u81ea\u4e8emc_subsidybatch_account\uff08\u6279\u6b21\u4eba\u5458\u8868\uff09<\/li>\n<\/ol>\n<h4><span class=\"ez-toc-section\" id=\"14_%E9%9C%80%E8%A6%81%E8%A7%A3%E5%86%B3%E7%9A%84%E9%97%AE%E9%A2%98\"><\/span>1.4. \u9700\u8981\u89e3\u51b3\u7684\u95ee\u9898<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><strong>\u67e5\u8be2\u51fa mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09\u4e2d BatchNum > 0\u7684\u8bb0\u5f55\uff0c\u4e14\u5b83\u7684 RecNum \u4e0d\u5728  mc_subsidybatch_account\uff08\u6279\u6b21\u4eba\u5458\u8868\uff09\u4e2d\u7684 SubsidyRecnum\u5b57\u6bb5\u503c\u4e2d\u3002<\/strong><\/p>\n<h3><span class=\"ez-toc-section\" id=\"2_%E5%88%86%E6%9E%90JOIN%E7%9A%84%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92\"><\/span>2. \u5206\u6790JOIN\u7684\u6267\u884c\u8ba1\u5212<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h4><span class=\"ez-toc-section\" id=\"21_%E6%96%B9%E6%B3%95%E4%B8%80\"><\/span>2.1. \u65b9\u6cd5\u4e00<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql line-numbers\">SELECT ms.*\n  FROM mc_subsidyrecord ms\n  LEFT join mc_subsidybatch_account msa ON ms.RecNum = msa.SubsidyRecnum\n WHERE msa.RecNum IS NULL\n   AND ms.BatchNum &gt; 0;\n<\/code><\/pre>\n<ul>\n<li>\u6267\u884c\u7ed3\u679c\u4e0e\u8017\u65f6<br \/>\n<span style='color:red'> 31 \u884c - 4.452s (+5ms) <\/span><\/p>\n<\/li>\n<li>\n<p>\u6267\u884c\u8ba1\u5212<\/p>\n<\/li>\n<\/ul>\n<pre><code class=\"line-numbers\">+------+-------------+-------+------+----------------------------+------+---------+------+------+-------------------------------------------------------------+\n| id   | select_type | table | type | possible_keys              | key  | key_len | ref  | rows | Extra                                                       |\n+------+-------------+-------+------+----------------------------+------+---------+------+------+-------------------------------------------------------------+\n|    1 | SIMPLE      | ms    | ALL  | idx_SubsidyRecord_BatchNum | NULL | NULL    | NULL | 8960 | Using where                                                 |\n|    1 | SIMPLE      | msa   | ALL  | NULL                       | NULL | NULL    | NULL | 6259 | Using where; Not exists; Using join buffer (flat, BNL join) |\n+------+-------------+-------+------+----------------------------+------+---------+------+------+-------------------------------------------------------------+\n<\/code><\/pre>\n<p>\u5206\u6790\u8fd9\u4e2a\u6267\u884c\u8ba1\u5212\uff1a<\/p>\n<ol>\n<li>\u4e24\u4e2a\u8868\u7684 select_type \u5747\u4e3a SIMPLE\uff0c\u5373\u67e5\u8be2\u4e2d\u4e0d\u5305\u542b\u5b50\u67e5\u8be2\u6216\u8005UNION\u3002<\/li>\n<li>\u5bf9\u4e24\u4e2a\u8868\u90fd\u505a\u4e86\u5168\u8868\u626b\u63cf\uff08type \u90fd\u4e3a ALL\uff09\uff0c\u6ca1\u6709\u7528\u5230\u4efb\u4f55\u7d22\u5f15\uff08key \u90fd\u4e3a NULL\uff09\u3002<\/li>\n<li>mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09\u8868\u4e3a\u9a71\u52a8\u8868\uff0c\u4f18\u5148\u67e5\u8be2\u8be5\u8868\u3002\uff08\u5728\u8fde\u63a5\u67e5\u8be2\u7684\u6267\u884c\u8ba1\u5212\u4e2d\uff0c\u6bcf\u4e2a\u8868\u90fd\u4f1a\u5bf9\u5e94\u4e00\u6761\u8bb0\u5f55\uff0c\u8fd9\u4e9b\u8bb0\u5f55\u7684id\u5217\u7684\u503c\u662f\u76f8\u540c\u7684\uff0c\u51fa\u73b0\u5728\u524d\u8fb9\u7684\u8868\u4e3a\u9a71\u52a8\u8868\uff0c\u51fa\u73b0\u5728\u540e\u8fb9\u7684\u8868\u4e3a\u88ab\u9a71\u52a8\u8868\u3002\uff09<\/li>\n<li>\u4f7f\u7528\u4e86\u7f13\u5b58\u5757\u5d4c\u5957\u5faa\u73af\u8fde\u63a5 Using join buffer \uff08BNL join\uff09\uff1a\u5373\u5c06 mc_subsidybatch_account\uff08\u6279\u6b21\u4eba\u5458\u8868\uff09\u8868\u4e2d\u8bb0\u5f55\u7f13\u5b58\u5230 join_buffer \u4e2d\u518d\u4e0e mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09\u8fdb\u884c\u6bd4\u8f83\u3002\u9700\u8981\u5c06 6259 \u884c\u8bb0\u5f55\u4e0e join_buffer \u4e2d\u7684\u6570\u636e\u4f5c\u6bd4\u8f83\uff0c\u8017\u65f6\u4f53\u73b0\u5728\u8fd9\u91cc\u3002<\/li>\n<li>Not exists:MySQL\u80fd\u591f\u5bf9\u67e5\u8be2\u8fdb\u884cLEFT JOIN\u4f18\u5316,\u53d1\u73b01\u4e2a\u5339\u914dLEFT JOIN\u6807\u51c6\u7684\u884c\u540e,\u4e0d\u518d\u4e3a\u524d\u9762\u7684\u7684\u884c\u7ec4\u5408\u5728\u8be5\u8868\u5185\u68c0\u67e5\u66f4\u591a\u7684\u884c\u3002<\/li>\n<\/ol>\n<h4><span class=\"ez-toc-section\" id=\"22_%E6%96%B9%E6%B3%95%E4%BA%8C\"><\/span>2.2. \u65b9\u6cd5\u4e8c<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql line-numbers\">SELECT ms.*\n  FROM mc_subsidyrecord ms\n  LEFT join mc_subsidybatch_account msa ON ms.RecNum = msa.SubsidyRecnum AND ms.BatchNum = msa.BatchNum\n WHERE msa.RecNum IS NULL\n   AND ms.BatchNum &gt; 0;\n<\/code><\/pre>\n<ul>\n<li>\u6267\u884c\u7ed3\u679c\u4e0e\u8017\u65f6<br \/>\n<span style='color:red'> 31 \u884c - 13.200s (+3ms) <\/span><\/p>\n<\/li>\n<li>\n<p>\u6267\u884c\u8ba1\u5212<\/p>\n<\/li>\n<\/ul>\n<pre><code class=\"line-numbers\">+------+-------------+-------+------+----------------------------+---------------------------+---------+------------------+------+-------------------------+\n| id   | select_type | table | type | possible_keys              | key                       | key_len | ref              | rows | Extra                   |\n+------+-------------+-------+------+----------------------------+---------------------------+---------+------------------+------+-------------------------+\n|    1 | SIMPLE      | ms    | ALL  | idx_SubsidyRecord_BatchNum | NULL                      | NULL    | NULL             | 8960 | Using where             |\n|    1 | SIMPLE      | msa   | ref  | FK_SubsidyBatch_Account_2  | FK_SubsidyBatch_Account_2 | 8       | etdb.ms.BatchNum | 55   | Using where; Not exists |\n+------+-------------+-------+------+----------------------------+---------------------------+---------+------------------+------+-------------------------+\n<\/code><\/pre>\n<p>\u5206\u6790\u8fd9\u4e2a\u6267\u884c\u8ba1\u5212\uff1a<\/p>\n<ol>\n<li>\u4e24\u4e2a\u8868\u7684 select_type \u5747\u4e3a SIMPLE\uff0c\u5373\u67e5\u8be2\u4e2d\u4e0d\u5305\u542b\u5b50\u67e5\u8be2\u6216\u8005UNION\u3002<\/li>\n<li>mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09\u8868\u4e3a\u9a71\u52a8\u8868\uff0c\u4f18\u5148\u67e5\u8be2\u8be5\u8868\u3002<\/li>\n<li>\u5bf9 mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09\u8868\u505a\u4e86\u5168\u8868\u626b\u63cf\uff08type \u4e3a ALL\uff09\uff0c\u6ca1\u6709\u7528\u5230\u4efb\u4f55\u7d22\u5f15\uff08key \u4e3a NULL\uff09\u3002<\/li>\n<li>\u5bf9 mc_subsidybatch_account\uff08\u6279\u6b21\u4eba\u5458\u8868\uff09\u8868\u505a\u4e86\u975e\u552f\u4e00\u7d22\u5f15\u626b\u63cf\uff08type \u4e3a ref\uff09,\u7d22\u5f15\u4e3a\uff1aFK_SubsidyBatch_Account_2\uff0c\u8be5\u7d22\u5f15\u7684\u552f\u4e00\u503c\u6761\u6570\u4e3a 56\uff08\u4e0erows\u5f53\u4e2d\u768455\u63a5\u8fd1\uff09\u3002\u8fd9\u6837\u7684\u8bdd\u5c31\u662f\u5728\u4e24\u8868\u4e2d\u5148\u53d6\u51faBatchNum\u76f8\u540c\u6570\u636e\uff0c\u518d\u5bf9 RecNum \u8fdb\u884c\u6bd4\u8f83\uff08\u5b9e\u9645\u4e0a\u5f53 ms.RecNum = msa.SubsidyRecnum \u65f6\uff0cms.BatchNum \u4e0e msa.BatchNum \u4e00\u5b9a\u76f8\u7b49\uff09\uff0c\u81f3\u5c11\u8981\u626b\u63cf\u88ab\u9a71\u52a8\u886855\u6b21\uff0c\u8fd9\u6837\u5c31\u6bd4BNL join\u66f4\u8017\u65f6\u3002\u53e6\u5916 Extra\u4e2d\u5e76\u6ca1\u6709\u51fa\u73b0\u4e86 Using index\uff0c\u8bf4\u660e\u4ec0\u4e48\u5462\uff1f<\/li>\n<li>Not exists:MySQL\u80fd\u591f\u5bf9\u67e5\u8be2\u8fdb\u884cLEFT JOIN\u4f18\u5316,\u53d1\u73b01\u4e2a\u5339\u914dLEFT JOIN\u6807\u51c6\u7684\u884c\u540e,\u4e0d\u518d\u4e3a\u524d\u9762\u7684\u7684\u884c\u7ec4\u5408\u5728\u8be5\u8868\u5185\u68c0\u67e5\u66f4\u591a\u7684\u884c\u3002<\/li>\n<\/ol>\n<h4><span class=\"ez-toc-section\" id=\"23_%E6%96%B9%E6%B3%95%E4%B8%89\"><\/span>2.3. \u65b9\u6cd5\u4e09<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql line-numbers\">SELECT ms.*\n  FROM mc_subsidyrecord ms\n WHERE ms.BatchNum &gt; 0\n   AND NOT EXISTS(SELECT 1\n                    FROM mc_subsidybatch_account msa\n                   WHERE ms.recnum = msa.subsidyrecnum);\n<\/code><\/pre>\n<p>\u6216<\/p>\n<pre><code class=\"language-sql line-numbers\">SELECT ms.*\n  FROM mc_subsidyrecord ms\n WHERE ms.BatchNum &gt; 0\n   AND ms.RecNum NOT IN(SELECT msa.SubsidyRecNum\n                          FROM mc_subsidybatch_account msa);\n<\/code><\/pre>\n<ul>\n<li>\u6267\u884c\u7ed3\u679c\u4e0e\u8017\u65f6<br \/>\n<span style='color:red'> 31 \u884c - 26ms (+6ms) <\/span><br \/>\n<strong>\u8fd9\u4e2a\u6267\u884c\u901f\u5ea6\u6bd4\u65b9\u6cd5\u4e00\u5feb\u4e86 170 \u500d\u3002<\/strong><\/p>\n<\/li>\n<li>\n<p>\u6267\u884c\u8ba1\u5212<\/p>\n<\/li>\n<\/ul>\n<pre><code class=\"line-numbers\">+------+--------------+-------+------+----------------------------+------+---------+------+------+-------------+\n| id   | select_type  | table | type | possible_keys              | key  | key_len | ref  | rows | Extra       |\n+------+--------------+-------+------+----------------------------+------+---------+------+------+-------------+\n|    1 | PRIMARY      | ms    | ALL  | idx_SubsidyRecord_BatchNum | NULL | NULL    | NULL | 8960 | Using where |\n|    2 | MATERIALIZED | msa   | ALL  | NULL                       | NULL | NULL    | NULL | 6259 |             |\n+------+--------------+-------+------+----------------------------+------+---------+------+------+-------------+\n<\/code><\/pre>\n<p>\u5206\u6790\u8fd9\u4e2a\u6267\u884c\u8ba1\u5212\uff1a<\/p>\n<ol>\n<li>id\u76f8\u540c\u8868\u793a\u52a0\u8f7d\u8868\u7684\u987a\u5e8f\u662f\u4ece\u4e0a\u5230\u4e0b\u3002id\u4e0d\u540cid\u503c\u8d8a\u5927\uff0c\u4f18\u5148\u7ea7\u8d8a\u9ad8\uff0c\u8d8a\u5148\u88ab\u6267\u884c\u3002id\u6709\u76f8\u540c\uff0c\u4e5f\u6709\u4e0d\u540c\uff0c\u540c\u65f6\u5b58\u5728\u3002id\u76f8\u540c\u7684\u53ef\u4ee5\u8ba4\u4e3a\u662f\u4e00\u7ec4\uff0c\u4ece\u4e0a\u5f80\u4e0b\u987a\u5e8f\u6267\u884c\uff1b\u5728\u6240\u6709\u7684\u7ec4\u4e2d\uff0cid\u7684\u503c\u8d8a\u5927\uff0c\u4f18\u5148\u7ea7\u8d8a\u9ad8\uff0c\u8d8a\u5148\u6267\u884c\u3002\u8fd9\u91cc  mc_subsidybatch_account\uff08\u6279\u6b21\u4eba\u5458\u8868\uff09\u88ab\u4f18\u5148\u6267\u884c\u3002<\/li>\n<li>mc_subsidybatch_account\uff08\u6279\u6b21\u4eba\u5458\u8868\uff09\u8868\u7684 select_type \u53d1\u751f\u4e86\u53d8\u5316\uff1a\u4ece SIMPLE \u53d8\u4e3a PRIMARY\uff0c\u67e5\u8be2\u4e2d\u6700\u5916\u5c42\u7684SELECT\uff08\u5982\u4e24\u8868\u505aUNION\u6216\u8005\u5b58\u5728\u5b50\u67e5\u8be2\u7684\u5916\u5c42\u7684\u8868\u64cd\u4f5c\u4e3aPRIMARY\uff0c\u5185\u5c42\u7684\u64cd\u4f5c\u4e3aUNION\uff09<\/li>\n<li>mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09\u8868\u7684 select_type \u53d1\u751f\u4e86\u53d8\u5316\uff1a\u4ece SIMPLE \u53d8\u4e3a MATERIALIZED\uff0c\u5b50\u67e5\u8be2\u7269\u5316\uff0c\u4e0d\u76f4\u63a5\u5c06\u4e0d\u76f8\u5173\u5b50\u67e5\u8be2\u7684\u7ed3\u679c\u96c6\u5f53\u4f5c\u5916\u5c42\u67e5\u8be2\u7684\u53c2\u6570\uff0c\u800c\u662f\u5c06\u8be5\u7ed3\u679c\u96c6\u5199\u5165\u4e00\u4e2a\u4e34\u65f6\u8868(\u7269\u5316\u8868)\u91cc\u3002\uff08\u76f8\u5173\u5b50\u67e5\u8be2\uff1a\u5982\u679c\u5b50\u67e5\u8be2\u7684\u6267\u884c\u9700\u8981\u4f9d\u8d56\u4e8e\u5916\u5c42\u67e5\u8be2\u7684\u503c\uff0c\u6211\u4eec\u5c31\u628a\u8fd9\u4e2a\u5b50\u67e5\u8be2\u79f0\u4e4b\u4e3a\u76f8\u5173\u5b50\u67e5\u8be2\uff0c\u53cd\u4e4b\u79f0\u4e3a\u4e0d\u76f8\u5173\u5b50\u67e5\u8be2\u3002\uff09\u6267\u884c\u8017\u65f6\u5c11\uff0c\u8fd9\u91cc\u662f\u5173\u952e\uff0c\u7b49\u4ef7\u4e8e\u5728mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09\u8868\u4e2d\u79fb\u9664\u5df2\u77e5\u7684\u884c\u8bb0\u5f55\uff08\u5373\u7269\u5316\u7684\u89c6\u56fe\uff09\u3002<\/li>\n<li>\u5bf9\u4e24\u4e2a\u8868\u90fd\u505a\u4e86\u5168\u8868\u626b\u63cf\uff08type \u90fd\u4e3a ALL\uff09\uff0c\u6ca1\u6709\u7528\u5230\u4efb\u4f55\u7d22\u5f15\uff08key \u90fd\u4e3a NULL\uff09\u3002<\/li>\n<\/ol>\n<h4><span class=\"ez-toc-section\" id=\"24_%E6%96%B9%E6%B3%95%E5%9B%9B\"><\/span>2.4. \u65b9\u6cd5\u56db<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>\u6839\u636e\u5173\u8054\u67e5\u8be2\u7684\u539f\u5219\uff0c\u7528\u6765\u8fdb\u884c join \u7684\u5b57\u6bb5\u8981\u52a0\u7d22\u5f15\uff0c\u6211\u4eec\u5728 mc_subsidybatch_account\uff08\u6279\u6b21\u4eba\u5458\u8868\uff09\u8868\u4e2d\u589e\u52a0\u5bf9\u5b57\u6bb5 SubsidyRecnum \u7684\u7d22\u5f15\u3002<\/p>\n<pre><code class=\"language-sql line-numbers\">CREATE INDEX idx_mc_subsidybatch_account1 ON mc_subsidybatch_account(SubsidyRecnum);\n<\/code><\/pre>\n<p>\u518d\u6309\u65b9\u6cd5\u4e00\u7684\u67e5\u8be2\u6765\u5206\u6790\u6267\u884c\u8ba1\u5212<\/p>\n<pre><code class=\"language-sql line-numbers\">SELECT ms.*\n  FROM mc_subsidyrecord ms\n  LEFT join mc_subsidybatch_account msa ON ms.RecNum = msa.SubsidyRecnum\n WHERE msa.RecNum IS NULL\n   AND ms.BatchNum &gt; 0;\n<\/code><\/pre>\n<ul>\n<li>\u6267\u884c\u7ed3\u679c\u4e0e\u8017\u65f6<br \/>\n<span style='color:red'> 31 \u884c - 25ms (+1ms) <\/span><\/p>\n<\/li>\n<li>\n<p>\u6267\u884c\u8ba1\u5212<\/p>\n<\/li>\n<\/ul>\n<pre><code class=\"line-numbers\">+------+-------------+-------+------+------------------------------+------------------------------+---------+----------------+------+--------------------------------------+\n| id   | select_type | table | type | possible_keys                | key                          | key_len | ref            | rows | Extra                                |\n+------+-------------+-------+------+------------------------------+------------------------------+---------+----------------+------+--------------------------------------+\n|    1 | SIMPLE      | ms    | ALL  | idx_SubsidyRecord_BatchNum   | NULL                         | NULL    | NULL           | 8960 | Using where                          |\n|    1 | SIMPLE      | msa   | ref  | idx_mc_subsidybatch_account1 | idx_mc_subsidybatch_account1 | 8       | etdb.ms.RecNum | 1    | Using where; Using index; Not exists |\n+------+-------------+-------+------+------------------------------+------------------------------+---------+----------------+------+--------------------------------------+\n<\/code><\/pre>\n<p>\u5206\u6790\u8fd9\u4e2a\u6267\u884c\u8ba1\u5212\uff1a<\/p>\n<ol>\n<li>\u4e24\u4e2a\u8868\u7684 select_type \u5747\u4e3a SIMPLE\uff0c\u5373\u67e5\u8be2\u4e2d\u4e0d\u5305\u542b\u5b50\u67e5\u8be2\u6216\u8005UNION\u3002<\/li>\n<li>mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09\u8868\u4e3a\u9a71\u52a8\u8868\uff0c\u4f18\u5148\u67e5\u8be2\u8be5\u8868\u3002<\/li>\n<li>\u5bf9 mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09\u8868\u505a\u4e86\u5168\u8868\u626b\u63cf\uff08type \u4e3a ALL\uff09\uff0c\u6ca1\u6709\u7528\u5230\u4efb\u4f55\u7d22\u5f15\uff08key \u4e3a NULL\uff09\u3002<\/li>\n<li>\u5bf9 mc_subsidybatch_account\uff08\u6279\u6b21\u4eba\u5458\u8868\uff09\u8868\u505a\u4e86\u975e\u552f\u4e00\u7d22\u5f15\u626b\u63cf\uff08type \u4e3a ref\uff09,\u7d22\u5f15\u4e3a\uff1aidx_mc_subsidybatch_account1\uff0c\u8be5\u7d22\u5f15\u7684\u552f\u4e00\u503c\u6761\u6570\u4e3a 6259\uff08\u4f46\u662frows\u5374\u663e\u793a 1\uff0c\u4e0d\u77e5\u9053\u4e3a\u4ec0\u4e48\uff1f\uff09\uff0c\u540c\u65f6\u8be5\u7d22\u5f15ref\u6240\u6307\u5411\u7684\u662f mc_subsidyrecord\uff08\u8865\u52a9\u8bb0\u5f55\u8868\uff09\u8868\u7684 RecNum \u7684\u503c\uff0c\u8fd9\u4e2a\u5b57\u6bb5\u662f\u8be5\u8868\u7684\u4e3b\u952e\u3002\u8fd9\u6837\u7684\u8bdd\u5c31\u662f\u6839\u636e\u7d22\u5f15 RecNum \u8fdb\u884c\u6bd4\u8f83\uff0cExtra\u4e2d\u51fa\u73b0\u4e86 Using index\u3002<\/li>\n<li>Not exists:MySQL\u80fd\u591f\u5bf9\u67e5\u8be2\u8fdb\u884cLEFT JOIN\u4f18\u5316,\u53d1\u73b01\u4e2a\u5339\u914dLEFT JOIN\u6807\u51c6\u7684\u884c\u540e,\u4e0d\u518d\u4e3a\u524d\u9762\u7684\u7684\u884c\u7ec4\u5408\u5728\u8be5\u8868\u5185\u68c0\u67e5\u66f4\u591a\u7684\u884c\u3002<\/li>\n<\/ol>\n<p>\u53c2\u8003\uff1a<br \/>\nMySQL\u6267\u884c\u8ba1\u5212Explain\u8be6\u89e3\uff1ahttps:\/\/jishuin.proginn.com\/p\/763bfbd2c67e<br \/>\n\u53c2\u8003\u672c\u7ad9\uff1a<a target=\"_blank\" class=\"wp-editor-md-post-content-link\" href=\"http:\/\/dba.qishuo.xin\/?p=1276\" title=\"MariaDB\u6267\u884c\u8ba1\u5212\u5206\u6790\uff081. Join\u6267\u884c\u8fc7\u7a0b\u8be6\u89e3\uff09\">MariaDB\u6267\u884c\u8ba1\u5212\u5206\u6790\uff081. Join\u6267\u884c\u8fc7\u7a0b\u8be6\u89e3\uff09<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>1. \u8868\u4e0e\u7d22\u5f15\u4ecb\u7ecd 1.1. \u8868 mc_subsi&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,3],"tags":[12,62,182],"class_list":["post-1231","post","type-post","status-publish","format-standard","hentry","category-mariadb","category-mariadbmysql","tag-mariadb","tag-62","tag-182"],"_links":{"self":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/1231","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1231"}],"version-history":[{"count":19,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/1231\/revisions"}],"predecessor-version":[{"id":1311,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/1231\/revisions\/1311"}],"wp:attachment":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1231"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1231"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1231"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}