{"id":403,"date":"2022-02-11T17:05:10","date_gmt":"2022-02-11T09:05:10","guid":{"rendered":"http:\/\/47.98.165.52\/?p=403"},"modified":"2022-02-11T17:05:40","modified_gmt":"2022-02-11T09:05:40","slug":"postgresql%e5%a6%82%e4%bd%95%e4%bb%8e%e8%a1%a8%e4%b8%ad%e9%ab%98%e6%95%88%e7%9a%84%e9%9a%8f%e6%9c%ba%e8%8e%b7%e5%8f%96%e4%b8%80%e6%9d%a1%e8%ae%b0%e5%bd%95","status":"publish","type":"post","link":"\/?p=403","title":{"rendered":"postgresql\u5982\u4f55\u4ece\u8868\u4e2d\u9ad8\u6548\u7684\u968f\u673a\u83b7\u53d6\u4e00\u6761\u8bb0\u5f55"},"content":{"rendered":"<h2>postgresql\u5982\u4f55\u4ece\u8868\u4e2d\u9ad8\u6548\u7684\u968f\u673a\u83b7\u53d6\u4e00\u6761\u8bb0\u5f55<\/h2>\n<pre><code class=\"language-sql\">select C_BH from db_scld.t_scld_cprscjl  order by `random()` LIMIT 1;\nselect  c_jdrybm from db_scld.t_jdry \n      where c_bmbm = v_scdd and c_sfyx =&#039;1&#039; and c_ryzszt not in (&#039;05&#039;,&#039;12&#039;,&#039;11&#039;,&#039;07&#039;,&#039;09&#039;,&#039;13&#039;)  order by `random()` limit 1 \n\n db_jdsjpt=# explain analyze select C_BH from db_scld.t_scld_cprscjl  order by random() LIMIT 1;\n                                                             QUERY PLAN                                                         \n---------------------------------------------------------------------------------------------------\n Limit  (cost=61029.94..61029.94 rows=1 width=41) (actual time=587.193..587.193 rows=1 loops=1)\n   -&gt;  Sort  (cost=61029.94..63172.22 rows=856911 width=41) (actual time=587.185..587.185 rows=1 loops=1)\n         Sort Key: (random())\n         Sort Method: top-N heapsort  Memory: 25kB\n         -&gt;  Seq Scan on t_scld_cprscjl  (cost=0.00..56745.39 rows=856911 width=41) (actual time=0.019..380.139 rows=854682 loop\ns=1)\n Planning time: 1.179 ms\n Execution time: 587.242 ms\n(7 rows)\n--\u8868\u603b\u6570\u91cf\n db_jdsjpt=# select count(*) from db_scld.t_scld_cprscjl;\n count  \n--------\n 854682\n(1 row)<\/code><\/pre>\n<h2>\u968f\u673a\u83b7\u53d6\u4e00\u6761\u8bb0\u5f55random()<\/h2>\n<p>random()\u8017\u65f6\uff1aTime: 389.818 ms<\/p>\n<pre><code class=\"language-sql\">--\u968f\u673a\u83b7\u53d6\u4e00\u6761\u8017\u65f6\ndb_jdsjpt=# select C_BH from db_scld.t_scld_cprscjl  order by random() LIMIT 1;\n               c_bh               \n----------------------------------\n 6d861b011c854040bf5b731f49d40b48\n(1 row)\n\nTime: 389.818 ms<\/code><\/pre>\n<h2>\u6539\u51991<\/h2>\n<p>offset\u8017\u65f6\uff1aTime: 60.022 ms<\/p>\n<pre><code class=\"language-sql\">--offset\u53ef\u4ee5\u8d70\u7d22\u5f15\uff0c\u5c11\u4e86\u6392\u5e8f\u64cd\u4f5c\ndb_jdsjpt=# select C_BH from db_scld.t_scld_cprscjl  offset floor(random()*854682) LIMIT 1;\n               c_bh               \n----------------------------------\n f90301bd8ac2485196ffae32ee70345c\n(1 row)\n\nTime: 60.022 ms\n\ndb_jdsjpt=# explain analyze select C_BH from db_scld.t_scld_cprscjl  offset floor(random()*854682) LIMIT 1;\n                                                                         QUERY PLAN                \n\n---------------------------------------------------------------------------------------------------\n Limit  (cost=3747.64..3747.68 rows=1 width=33) (actual time=30.758..30.759 rows=1 loops=1)\n   -&gt;  Index Only Scan using i_corscjl_cprscbh_ on t_scld_cprscjl  (cost=0.42..37472.65 rows=854682 width=33) (actual time=0.\n047..25.808 rows=81993 loops=1)\n         Heap Fetches: 0\n Planning time: 0.228 ms\n Execution time: 30.802 ms\n(5 rows)\n\nTime: 31.779 ms<\/code><\/pre>\n<h2>\u6539\u51992<\/h2>\n<p>pg\u4ece9.5\u5f00\u59cb\u63d0\u4f9b\u62bd\u6837\u51fd\u6570<\/p>\n<p>\u4f7f\u7528<code>tablesample<\/code>\u62bd\u6837\u7684\u8fc7\u7a0b\u4e2d\u6bd4\u4f8b\u4e0d\u80fd\u592a\u4f4e,\u5426\u5219\u53ef\u80fd\u83b7\u53d6\u4e0d\u5230\u7ed3\u679c<\/p>\n<p><code>system<\/code>\u8017\u65f6\uff1a Time: 0.639 ms<\/p>\n<p><code>system<\/code>\uff1a\u968f\u673a\u6027\u8f83\u5dee\uff0c\u6548\u7387\u9ad8<\/p>\n<pre><code class=\"language-sql\">--\u6539\u5199\u540e\u8017\u65f6\ndb_jdsjpt=# select c_bh from db_scld.t_scld_cprscjl  tablesample system(0.1) limit 1;\n               c_bh               \n----------------------------------\n e2fce25399db42f0bf49faf8e7214d5f\n(1 row)\n\nTime: 0.639 ms\n\n--system\u968f\u673a\u62bd\u6837\u4ee5\u5757\u4e3a\u5355\u4f4d\u6240\u4ee5\u66f4\u5feb\ndb_jdsjpt=# explain analyze  select c_bh from db_scld.t_scld_cprscjl  tablesample system(0.1) limit 1;\n                                                      QUERY PLAN                                                      \n---------------------------------------------------------------------------------------------------\n Limit  (cost=0.00..0.23 rows=1 width=33) (actual time=0.105..0.105 rows=1 loops=1)\n   -&gt;  Sample Scan on t_scld_cprscjl  (cost=0.00..192.55 rows=855 width=33) (actual time=0.102..0.102 rows=1 loops=1)\n         Sampling: system (&#039;0.1&#039;::real)\n Planning time: 0.190 ms\n Execution time: 0.134 ms\n(5 rows)\n\nTime: 1.182 ms<\/code><\/pre>\n<h2>\u6539\u51993<\/h2>\n<p><code>bernoulli<\/code>:\u968f\u673a\u6027\u66f4\u597d\uff0c\u4f46\u6548\u7387\u6bd4system\u8981\u4f4e<\/p>\n<p><code>bernoullih<\/code>\u8017\u65f6\uff1aTime: 0.822 ms<\/p>\n<pre><code class=\"language-sql\">db_jdsjpt=# select c_bh from db_scld.t_scld_cprscjl  tablesample bernoulli(0.1) limit 1;\n               c_bh               \n----------------------------------\n 7ec30761ffd04bd9ad77797a33645a84\n(1 row)\n\nTime: 0.822 ms\n\n--bernoulli\u4ee5\u884c\u4e3a\u5355\u4f4d\u8fdb\u884c\u62bd\u6837\uff0c\u6bd4system\u6548\u7387\u4f4e\u70b9\ndb_jdsjpt=# explain analyze select c_bh from db_scld.t_scld_cprscjl  tablesample bernoulli(0.1) limit 1;\n                                                       QUERY PLAN                                                       \n---------------------------------------------------------------------------------------------------\n Limit  (cost=0.00..53.85 rows=1 width=33) (actual time=1.410..1.411 rows=1 loops=1)\n   -&gt;  Sample Scan on t_scld_cprscjl  (cost=0.00..46042.55 rows=855 width=33) (actual time=1.408..1.408 rows=1 loops=1)\n         Sampling: bernoulli (&#039;0.1&#039;::real)\n Planning time: 0.446 ms\n Execution time: 1.436 ms\n(5 rows)\n\nTime: 25.770 ms<\/code><\/pre>\n<p>\u540c\u7406\u53e6\u5916\u4e00\u6761sql\u4e5f\u53ef\u7528\u540c\u6837\u7684\u65b9\u5f0f\uff0c\u4e14\u5728<code>c_bmbm<\/code>\u5b57\u6bb5\u4e0a\u9762\u52a0\u4e0a\u7d22\u5f15<\/p>\n<p>\u5f53\u6709\u6761\u4ef6\u7684\u65f6\u5019\u53ef\u4ee5\u4f7f\u7528<code>offset<\/code>\u83b7\u53d6\uff0c<code>offset<\/code>\u7684\u503c\u4e5f\u53ef\u4ee5\u901a\u8fc7<code>for<\/code>\u5faa\u73af\u4f20\u5165<\/p>\n<pre><code class=\"language-sql\">db_jdsjpt=# select count(*) from db_scld.t_jdry;\n count  \n--------\n 214819\n(1 row)\n\ndb_jdsjpt=# select  c_jdrybm from db_scld.t_jdry  where c_bmbm = &#039;4402222804&#039; and c_sfyx =&#039;1&#039; and c_ryzszt not in (&#039;05&#039;,&#039;12&#039;,&#039;11&#039;,&#039;07&#039;,&#039;09&#039;,&#039;13&#039;) offset floor(random()*214819) limit 1; \n c_jdrybm \n----------\n(0 rows)\n\nTime: 1.924 ms<\/code><\/pre>\n<h2>\u5bf9\u6bd4<\/h2>\n<table>\n<thead>\n<tr>\n<th><strong>\u65b9\u6cd5<\/strong><\/th>\n<th><strong>\u8017\u65f6<\/strong><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>order by random()<\/td>\n<td>389.818 ms<\/td>\n<\/tr>\n<tr>\n<td>offset n<\/td>\n<td>60.022 ms-240ms<\/td>\n<\/tr>\n<tr>\n<td>system()<\/td>\n<td>0.639 ms<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u4f7f\u7528<code>offset<\/code>\u7684\u65f6\u5019\u548cn\u7684\u5927\u5c0f\u6709\u5173\u7cfb\uff0c\u5f53n\u8d8a\u5927\uff0c\u626b\u63cf\u7684\u7d22\u5f15\u5757\u8d8a\u591a\uff0c\u5c31\u8d8a\u5927\uff0c\u4f46\u662f\u76f8\u5bf9\u4e8e<code>order by random()<\/code>\u7684\u65b9\u5f0f\u4ecd\u7136\u8981\u5feb\u3002<\/p>\n<h2>\u6ce8\u610f<\/h2>\n<p><code>system(0.1)<\/code>\u7b49\u4e8e\u767e\u5206\u4e4b\u96f6\u70b9\u4e00\uff0c\u4e5f\u5c31\u662f\u62bd\u6837\u5343\u5206\u4e4b\u4e00 <code>854682*0.001=854<\/code>\uff0c\u5927\u6982\u6bcf\u6b21\u62bd\u53d6<code>854<\/code>\u6761\u8bb0\u5f55<\/p>\n<pre><code class=\"language-sql\">--system\ndb_jdsjpt=#  select count(*) from db_scld.t_scld_cprscjl  tablesample system(0.1) ;\n count \n-------\n   592\n(1 row)\n\nTime: 1.499 ms\n\n--bernoulli\ndb_jdsjpt=#  select count(*) from db_scld.t_scld_cprscjl  tablesample bernoulli(0.1) ;\n count \n-------\n   840\n(1 row)\n\nTime: 86.037 ms\n\u8fd9\u91cc\u53ef\u4ee5\u770b\u51fabernoulli\u6548\u7387\u6bd4system\u8981\u4f4e<\/code><\/pre>\n<h2>\u7ed3\u8bed<\/h2>\n<p>1.\u968f\u673a\u83b7\u53d6\u8868\u4e2d\u7684\u4e00\u6761\u6570\u636e\uff0c\u5f53\u8868\u4e2d\u6570\u636e\u8f83\u5c0f\u65f6\u4f7f\u7528random\u611f\u89c9\u4e0d\u660e\u663e\uff0c\u5f53\u6570\u636e\u91cf\u5927\u65f6random\u7531\u4e8e\u6bcf\u6b21\u90fd\u9700\u8981\u6392\u5e8f\u64cd\u4f5c\uff0c\u5bfc\u81f4\u968f\u673a\u83b7\u53d6\u4e00\u6761\u7684\u6210\u672c\u8f83\u9ad8<\/p>\n<p>2.\u968f\u673a\u83b7\u53d6\u4e00\u6761\u8bb0\u5f55\u53ef\u4ee5\u4f7f\u7528limit 1 offset n-1\u7684\u65b9\u5f0f\uff0c\u6216\u8005\u4f7f\u7528\u968f\u673a\u62bd\u6837\u7684\u65b9\u5f0f<\/p>\n<p>3.\u65e0\u8bba\u662f\u4f7f\u7528limit 1 offset n\u8fd8\u662f\u4f7f\u7528tablesample\u968f\u673a\u62bd\u6837\u65b9\u5f0f\u90fd\u9700\u8981\u77e5\u9053\u8868\u4e2d\u7684\u6570\u636e\u91cf\uff0c\u4e0d\u80fd\u8d85\u8fc7\u8868\u6570\u636e\u91cf<\/p>\n<h2>\u6211\u7684\u5b9e\u4f8b<\/h2>\n<p>\u53d620\u6761\u968f\u673a\u6570\u636e\uff0c\u4e14\u5e26\u6709\u7b5b\u9009\u6761\u4ef6\uff1a<\/p>\n<pre><code class=\"language-sql\">SELECT id, regist_capital,regist_capital_rmb FROM service.eoias_company_base_info tablesample system(0.01) where (regist_capital is not null) and (regist_capital_rmb is null)   LIMIT 20<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>postgresql\u5982\u4f55\u4ece\u8868\u4e2d\u9ad8\u6548\u7684\u968f\u673a\u83b7\u53d6\u4e00\u6761\u8bb0\u5f55 select C_BH from db_scld.t_scld_cprscjl o   \u2026 &#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0},"categories":[36],"tags":[],"_links":{"self":[{"href":"\/index.php?rest_route=\/wp\/v2\/posts\/403"}],"collection":[{"href":"\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=403"}],"version-history":[{"count":2,"href":"\/index.php?rest_route=\/wp\/v2\/posts\/403\/revisions"}],"predecessor-version":[{"id":405,"href":"\/index.php?rest_route=\/wp\/v2\/posts\/403\/revisions\/405"}],"wp:attachment":[{"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=403"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=403"},{"taxonomy":"post_tag","embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=403"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}