-
Notifications
You must be signed in to change notification settings - Fork 0
/
index.html
866 lines (620 loc) · 57.8 KB
/
index.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 5.4.0">
<link rel="apple-touch-icon" sizes="180x180" href="/TechBlog/images/apple-touch-icon-next.png">
<link rel="icon" type="image/png" sizes="32x32" href="/TechBlog/images/favicon-32x32-next.png">
<link rel="icon" type="image/png" sizes="16x16" href="/TechBlog/images/favicon-16x16-next.png">
<link rel="mask-icon" href="/TechBlog/images/logo.svg" color="#222">
<link rel="stylesheet" href="/TechBlog/css/main.css">
<link rel="stylesheet" href="/TechBlog/lib/font-awesome/css/all.min.css">
<link rel="stylesheet" href="/TechBlog/lib/pace/pace-theme-minimal.min.css">
<script src="/TechBlog/lib/pace/pace.min.js"></script>
<script id="hexo-configurations">
var NexT = window.NexT || {};
var CONFIG = {"hostname":"shadowboy88.github.io","root":"/TechBlog/","scheme":"Gemini","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":false,"show_result":false,"style":null},"back2top":{"enable":true,"sidebar":false,"scrollpercent":false},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":true,"trigger":"manual","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},"path":"search.xml"};
</script>
<meta name="description" content="Hakuna Matata">
<meta property="og:type" content="website">
<meta property="og:title" content="晨鐘暮鼓">
<meta property="og:url" content="https://shadowboy88.github.io/TechBlog/index.html">
<meta property="og:site_name" content="晨鐘暮鼓">
<meta property="og:description" content="Hakuna Matata">
<meta property="og:locale" content="zh_CN">
<meta property="article:author" content="Mars">
<meta name="twitter:card" content="summary">
<link rel="canonical" href="https://shadowboy88.github.io/TechBlog/">
<script id="page-configurations">
// https://hexo.io/docs/variables.html
CONFIG.page = {
sidebar: "",
isHome : true,
isPost : false,
lang : 'zh-CN'
};
</script>
<title>晨鐘暮鼓</title>
<noscript>
<style>
.use-motion .brand,
.use-motion .menu-item,
.sidebar-inner,
.use-motion .post-block,
.use-motion .pagination,
.use-motion .comments,
.use-motion .post-header,
.use-motion .post-body,
.use-motion .collection-header { opacity: initial; }
.use-motion .site-title,
.use-motion .site-subtitle {
opacity: initial;
top: initial;
}
.use-motion .logo-line-before i { left: initial; }
.use-motion .logo-line-after i { right: initial; }
</style>
</noscript>
</head>
<body itemscope itemtype="http://schema.org/WebPage">
<div class="container use-motion">
<div class="headband"></div>
<header class="header" itemscope itemtype="http://schema.org/WPHeader">
<div class="header-inner"><div class="site-brand-container">
<div class="site-nav-toggle">
<div class="toggle" aria-label="切换导航栏">
<span class="toggle-line toggle-line-first"></span>
<span class="toggle-line toggle-line-middle"></span>
<span class="toggle-line toggle-line-last"></span>
</div>
</div>
<div class="site-meta">
<a href="/TechBlog/" class="brand" rel="start">
<span class="logo-line-before"><i></i></span>
<h1 class="site-title">晨鐘暮鼓</h1>
<span class="logo-line-after"><i></i></span>
</a>
</div>
<div class="site-nav-right">
<div class="toggle popup-trigger">
<i class="fa fa-search fa-fw fa-lg"></i>
</div>
</div>
</div>
<nav class="site-nav">
<ul id="menu" class="main-menu menu">
<li class="menu-item menu-item-home">
<a href="/TechBlog/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>
</li>
<li class="menu-item menu-item-about">
<a href="/TechBlog/about/" rel="section"><i class="fa fa-user fa-fw"></i>关于</a>
</li>
<li class="menu-item menu-item-tags">
<a href="/TechBlog/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a>
</li>
<li class="menu-item menu-item-categories">
<a href="/TechBlog/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a>
</li>
<li class="menu-item menu-item-archives">
<a href="/TechBlog/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a>
</li>
<li class="menu-item menu-item-commonweal">
<a href="/TechBlog/404/" rel="section"><i class="fa fa-heartbeat fa-fw"></i>公益 404</a>
</li>
<li class="menu-item menu-item-search">
<a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
</a>
</li>
</ul>
</nav>
<div class="search-pop-overlay">
<div class="popup search-popup">
<div class="search-header">
<span class="search-icon">
<i class="fa fa-search"></i>
</span>
<div class="search-input-container">
<input autocomplete="off" autocapitalize="off"
placeholder="搜索..." spellcheck="false"
type="search" class="search-input">
</div>
<span class="popup-btn-close">
<i class="fa fa-times-circle"></i>
</span>
</div>
<div id="search-result">
<div id="no-result">
<i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>
</div>
</div>
</div>
</div>
</div>
</header>
<div class="back-to-top">
<i class="fa fa-arrow-up"></i>
<span>0%</span>
</div>
<main class="main">
<div class="main-inner">
<div class="content-wrap">
<div class="content index posts-expand">
<article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="https://shadowboy88.github.io/TechBlog/2021/07/26/hadoop/">
<span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
<meta itemprop="image" content="/TechBlog/images/avatar.png">
<meta itemprop="name" content="Mars">
<meta itemprop="description" content="Hakuna Matata">
</span>
<span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
<meta itemprop="name" content="晨鐘暮鼓">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/TechBlog/2021/07/26/hadoop/" class="post-title-link" itemprop="url">Win10搭建hadoop开发环境</a>
</h2>
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2021-07-26 17:22:58 / 修改时间：17:35:29" itemprop="dateCreated datePublished" datetime="2021-07-26T17:22:58+08:00">2021-07-26</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-folder"></i>
</span>
<span class="post-meta-item-text">分类于</span>
<span itemprop="about" itemscope itemtype="http://schema.org/Thing">
<a href="/TechBlog/categories/%E5%A4%A7%E6%95%B0%E6%8D%AE/" itemprop="url" rel="index"><span itemprop="name">大数据</span></a>
</span>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<p>###1.环境准备<br>下载安装JDK8 window版本：<a target="_blank" rel="noopener" href="https://www.oracle.com/java/technologies/javase/javase-jdk8-downloads.html#license-lightbox">jdk-8u241-windows-x64.exe</a></p>
<p>安装、配置jdk环境变量。</p>
<p>###2.下载hadoop<br>hadoop下载链接：<a target="_blank" rel="noopener" href="https://hadoop.apache.org/release/3.1.0.html">hadoop-3.1.0</a><br>下载winutils，Windows安装hadoop需要这部分文件，链接：<a target="_blank" rel="noopener" href="https://github.com/steveloughran/winutils">steveloughran/winutils</a> ，找到3.0版本，或者cdarlint/winutils 找到 3.2.0，下载bin目录的文件，放入/替换 C:\hadoop\bin 文件夹内的文件，特别是要放入hadoop.dll和winutils.exe文件，并复制一份hadoop.dll放到C:\Windows\System32下。</p>
<p>配置hadoop环境变量，把C:\hadoop添加到HADOOP_HOME，并在Path添加 %HADOOP_HOME%\bin 和 %HADOOP_HOME%\sbin 。</p>
<p>打开CMD，输入hadoop version 测试是否正常显示版本信息。</p>
<p>###3.配置hadoop</p>
<p>切换到hadoop安装路径下。</p>
<p>####3.1修改core-site.xml</p>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
<article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="https://shadowboy88.github.io/TechBlog/2021/07/13/postgre-sql/">
<span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
<meta itemprop="image" content="/TechBlog/images/avatar.png">
<meta itemprop="name" content="Mars">
<meta itemprop="description" content="Hakuna Matata">
</span>
<span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
<meta itemprop="name" content="晨鐘暮鼓">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/TechBlog/2021/07/13/postgre-sql/" class="post-title-link" itemprop="url">Postgre SQL</a>
</h2>
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2021-07-13 16:55:29" itemprop="dateCreated datePublished" datetime="2021-07-13T16:55:29+08:00">2021-07-13</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar-check"></i>
</span>
<span class="post-meta-item-text">更新于</span>
<time title="修改时间：2021-07-16 09:39:02" itemprop="dateModified" datetime="2021-07-16T09:39:02+08:00">2021-07-16</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-folder"></i>
</span>
<span class="post-meta-item-text">分类于</span>
<span itemprop="about" itemscope itemtype="http://schema.org/Thing">
<a href="/TechBlog/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/" itemprop="url" rel="index"><span itemprop="name">数据库</span></a>
</span>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<h1 id="Introduction"><a href="#Introduction" class="headerlink" title="Introduction"></a>Introduction</h1><p>PostgreSQL 是一个免费的<strong>对象-关系数据库</strong>服务器(ORDBMS)，在灵活的BSD许可证下发行。</p>
<p>PostgreSQL 开发者把它念作 post-gress-Q-L。</p>
<p>PostgreSQL 的 Slogan 是 “世界上最先进的开源关系型数据库”。</p>
<p>目前，由于MySql被Oracle公司收购，MySql后续可能也会被迫商业化，就用户量来说MySql和Oracle都呈现下降趋势，反而PostgreSql正在稳步上升。<br><a target="_blank" rel="noopener" href="https://db-engines.com/en/ranking">参考数据库排名网站</a></p>
<h1 id="Install"><a href="#Install" class="headerlink" title="Install"></a>Install</h1><h2 id="Ubuntu-安装-PostgreSQL"><a href="#Ubuntu-安装-PostgreSQL" class="headerlink" title="Ubuntu 安装 PostgreSQL"></a>Ubuntu 安装 PostgreSQL</h2><p>Ubuntu 可以使用 apt-get 安装 PostgreSQL：<br></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$ sudo apt-get update</span><br></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$ sudo apt-get install postgresql postgresql-client</span><br></pre></td></tr></table></figure>
<p>安装完毕后，系统会创建一个数据库超级用户 postgres，密码为空。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$ sudo -i -u postgres</span><br></pre></td></tr></table></figure>
<p>这时使用以下命令进入 postgres，输出以下信息，说明安装成功：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">~$ psql</span><br><span class="line">psql (9.5.17)</span><br><span class="line">Type <span class="string">"help"</span> <span class="keyword">for</span> <span class="built_in">help</span>.</span><br><span class="line"></span><br><span class="line">postgres=<span class="comment"># </span></span><br></pre></td></tr></table></figure>
<p>输入以下命令退出 PostgreSQL 提示符：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">\q</span><br></pre></td></tr></table></figure>
<p>PostgreSQL 安装完成后默认是已经启动的，但是也可以通过下面的方式来手动启动服务。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">sudo /etc/init.d/postgresql start <span class="comment"># 开启</span></span><br><span class="line">sudo /etc/init.d/postgresql stop <span class="comment"># 关闭</span></span><br><span class="line">sudo /etc/init.d/postgresql restart <span class="comment"># 重启</span></span><br></pre></td></tr></table></figure>
<p>#Base Operation</p>
<h2 id="常用命令"><a href="#常用命令" class="headerlink" title="常用命令"></a>常用命令</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">$ sudo su postgres</span><br><span class="line">$ createdb marsblog</span><br><span class="line">$ psql -l </span><br><span class="line">$ psql marsblog</span><br><span class="line">> create table posts(title varchar(255),content text);</span><br><span class="line">> \dt</span><br><span class="line">> \d posts</span><br><span class="line">> alert table posts rename to marsposts;</span><br><span class="line">> \dt</span><br><span class="line">> drop table marsposts;</span><br><span class="line">>\dt</span><br><span class="line">>\q</span><br><span class="line">>nano db.sql</span><br></pre></td></tr></table></figure>
<h2 id="字段类型"><a href="#字段类型" class="headerlink" title="字段类型"></a>字段类型</h2><ul>
<li>数值型<ul>
<li>integer</li>
<li>real</li>
<li>serial</li>
</ul>
</li>
<li>文字型<ul>
<li>char</li>
<li>varchar</li>
<li>text</li>
</ul>
</li>
<li>布尔型<ul>
<li>boolean</li>
</ul>
</li>
<li>日期型 </li>
</ul>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
<article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="https://shadowboy88.github.io/TechBlog/2021/07/13/hello-world/">
<span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
<meta itemprop="image" content="/TechBlog/images/avatar.png">
<meta itemprop="name" content="Mars">
<meta itemprop="description" content="Hakuna Matata">
</span>
<span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
<meta itemprop="name" content="晨鐘暮鼓">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/TechBlog/2021/07/13/hello-world/" class="post-title-link" itemprop="url">Build Blog Via Hexo</a>
</h2>
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2021-07-13 11:16:25 / 修改时间：17:03:14" itemprop="dateCreated datePublished" datetime="2021-07-13T11:16:25+08:00">2021-07-13</time>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<p>Welcome to <a target="_blank" rel="noopener" href="https://hexo.io/">Hexo</a>! This is your very first post. Check <a target="_blank" rel="noopener" href="https://hexo.io/docs/">documentation</a> for more info. If you get any problems when using Hexo, you can find the answer in <a target="_blank" rel="noopener" href="https://hexo.io/docs/troubleshooting.html">troubleshooting</a> or you can ask me on <a target="_blank" rel="noopener" href="https://github.com/hexojs/hexo/issues">GitHub</a>.</p>
<h2 id="Quick-Start"><a href="#Quick-Start" class="headerlink" title="Quick Start"></a>Quick Start</h2><h3 id="Create-a-new-post"><a href="#Create-a-new-post" class="headerlink" title="Create a new post"></a>Create a new post</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$ hexo new <span class="string">"My New Post"</span></span><br></pre></td></tr></table></figure>
<p>More info: <a target="_blank" rel="noopener" href="https://hexo.io/docs/writing.html">Writing</a></p>
<h3 id="Run-server"><a href="#Run-server" class="headerlink" title="Run server"></a>Run server</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$ hexo server</span><br></pre></td></tr></table></figure>
<p>More info: <a target="_blank" rel="noopener" href="https://hexo.io/docs/server.html">Server</a></p>
<h3 id="Generate-static-files"><a href="#Generate-static-files" class="headerlink" title="Generate static files"></a>Generate static files</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$ hexo generate</span><br></pre></td></tr></table></figure>
<p>More info: <a target="_blank" rel="noopener" href="https://hexo.io/docs/generating.html">Generating</a></p>
<h3 id="Deploy-to-remote-sites"><a href="#Deploy-to-remote-sites" class="headerlink" title="Deploy to remote sites"></a>Deploy to remote sites</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">$ hexo deploy</span><br></pre></td></tr></table></figure>
<p>More info: <a target="_blank" rel="noopener" href="https://hexo.io/docs/one-command-deployment.html">Deployment</a></p>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
<article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="https://shadowboy88.github.io/TechBlog/2021/09/03/Postgre-SQL-Optimization/">
<span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
<meta itemprop="image" content="/TechBlog/images/avatar.png">
<meta itemprop="name" content="Mars">
<meta itemprop="description" content="Hakuna Matata">
</span>
<span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
<meta itemprop="name" content="晨鐘暮鼓">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/TechBlog/2021/09/03/Postgre-SQL-Optimization/" class="post-title-link" itemprop="url">Postgre SQL Optimization</a>
</h2>
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2021-09-03 11:16:04 / 修改时间：15:15:25" itemprop="dateCreated datePublished" datetime="2021-09-03T11:16:04+08:00">2021-09-03</time>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<h1 id="introduction"><a href="#introduction" class="headerlink" title="introduction"></a>introduction</h1><p>当查询接口数据量过大，接口响应时间过长的情况下，应当考虑优化sql查询耗时。这种情况下可以考虑适当添加索引，<br>优化查询速度。本文以postgre sql为例，聊聊sql查询优化的案例。</p>
<h1 id="1-sql执行计划"><a href="#1-sql执行计划" class="headerlink" title="1. sql执行计划"></a>1. sql执行计划</h1><p> 在分析sql执行耗时的过程中需要用到sql执行计划来辅助分析sql执行的步骤和每一步的耗时。<br> sql执行计划就相当于程序执行的日志。</p>
<h2 id="1-1-基本语法"><a href="#1-1-基本语法" class="headerlink" title="1.1 基本语法"></a>1.1 基本语法</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">EXPLAIN [ ANALYZE ] [ VERBOSE ] sql语句</span><br></pre></td></tr></table></figure>
<p>各参数含义：</p>
<p>analyze：执行语句并显示真正的运行时间和其它统计信息，会真正执行SQL语句,比较常用；</p>
<p>verbose：显示额外的信息,尤其是计划树中每个节点的字段列表,schema识别表和函数名称。总是打印统计数据中显示的每个触发器的名字；</p>
<p>costs：包括每个计划节点的启动成本预估和总成本的消耗，也包括行数和行宽度的预估；</p>
<p>buffers：使用信息，特别包括共享块命中、读、脏和写的次数，本地块命中、读、脏和写，临时块读和写的次数；</p>
<p>timing：在输出中包含实际启动时间和每个节点花费的时间，重复读系统块在某些系统上会显著的减缓查询的速度，只在ANALYZE也启用的时候使用；</p>
<p>format：声明输出格式，可以为TEXT, XML, JSON 或 YAML，默认text；</p>
<h2 id="1-2-基本输出"><a href="#1-2-基本输出" class="headerlink" title="1.2 基本输出"></a>1.2 基本输出</h2><p>命令的结果是从statement选择的规划的文字描述， 可选的有执行统计数据的注释。如果你想提供EXPLAIN输出给程序用作进一步分析， 你应该使用它的机器可读的输出格式之一(XML, JSON, or YAML)来代替。</p>
<p>查询规划的结构是一个规划节点的树。最底层的节点是表扫描节点： 它们从表中返回原始数据行。不同的表访问模式有不同的扫描节点类型： 顺序扫描（Full scan|Seq scan）、索引扫描(Index scan)、位图索引扫描(Bit index scan)。<br>也有非表行来源，如VALUES子句和FROM中返回行集的函数， 它们有自己的扫描节点类型。 如果查询需要连接、聚合、排序、或者对原始行的其它操作， 那么就会在扫描节点之上有其它额外的节点。并且，做这些操作通常都有多种方法，<br>因此在这些位置也有可能出现不同的节点类型。EXPLAIN给规划树中每个节点都输出一行， 显示基本的节点类型和规划器为执行这个规划节点预计的开销值。 可能会出现附加的行，这些行从节点的汇总行缩进，显示这个节点的额外属性。<br>第一行(最上层的汇总行节点)是对该规划的总执行开销的预计；这个数值就是规划器试图最小化的数值。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">EXPLAIN SELECT * FROM tenk1;</span><br><span class="line"></span><br><span class="line"> QUERY PLAN</span><br><span class="line">-------------------------------------------------------------</span><br><span class="line"> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)</span><br></pre></td></tr></table></figure>
<p>上述举例中即为顺序扫描。由于此查询没有WHERE子句，它必须扫描所有表的行，所以规划器已经选择使用一个简单的顺序扫描计划。 括号中引用的数值是（从左到右）:</p>
<ul>
<li><p>预计的启动开销。在输出扫描开始之前消耗的时间，比如在一个排序节点里执行排序的时间。</p>
</li>
<li><p>预计总开销。这个估算是假设计划节点运行完成做出的，即所有可用行都被检索。 在实际中一个节点的父节点可能会决定不读取所有可用的行（参见LIMIT下面的例子）。</p>
</li>
<li><p>预计这个规划节点输出的行数。同样，这个节点被假定执行到完成为止。</p>
</li>
<li><p>预计这个规划节点的行平均宽度(以字节计算)。</p>
<h1 id="2-Postgre-SQL索引"><a href="#2-Postgre-SQL索引" class="headerlink" title="2 Postgre SQL索引"></a>2 Postgre SQL索引</h1><p>PgSql索引和Mysql索引类似，需要手动创建和维护。</p>
<h2 id="2-1-创建索引"><a href="#2-1-创建索引" class="headerlink" title="2.1 创建索引"></a>2.1 创建索引</h2><p>CREATE INDEX （创建索引）的语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CREATE INDEX index_name ON table_name;</span><br></pre></td></tr></table></figure>
<h3 id="索引类型"><a href="#索引类型" class="headerlink" title="索引类型"></a>索引类型</h3></li>
<li><p>单列索引</p>
<p>单列索引是一个只基于表的一个列上创建的索引，基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">CREATE INDEX index_name</span><br><span class="line">ON table_name (column_name);</span><br></pre></td></tr></table></figure></li>
<li><p>组合索引</p>
</li>
</ul>
<p>组合索引是基于表的多列上创建的索引，基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">CREATE INDEX index_name</span><br><span class="line">ON table_name (column1_name, column2_name);</span><br></pre></td></tr></table></figure>
<ul>
<li>唯一索引</li>
</ul>
<p>使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">CREATE UNIQUE INDEX index_name</span><br><span class="line">on table_name (column_name);</span><br></pre></td></tr></table></figure>
<ul>
<li>局部索引</li>
</ul>
<p>局部索引 是在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行。基础语法如下：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">CREATE INDEX index_name</span><br><span class="line">on table_name (conditional_expression);</span><br></pre></td></tr></table></figure>
<h2 id="2-2-删除索引"><a href="#2-2-删除索引" class="headerlink" title="2.2 删除索引"></a>2.2 删除索引</h2><p>一个索引可以使用 PostgreSQL 的 DROP 命令删除。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DROP INDEX index_name;</span><br></pre></td></tr></table></figure>
<h2 id="2-3-查看索引"><a href="#2-3-查看索引" class="headerlink" title="2.3 查看索引"></a>2.3 查看索引</h2><p>已经创建的索引可以通过以下命令查询：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line">A.SCHEMANAME,</span><br><span class="line">A.TABLENAME,</span><br><span class="line">A.INDEXNAME,</span><br><span class="line">A.TABLESPACE,</span><br><span class="line">A.INDEXDEF,</span><br><span class="line">B.AMNAME,</span><br><span class="line">C.INDEXRELID,</span><br><span class="line">C.INDNATTS,</span><br><span class="line">C.INDISUNIQUE,</span><br><span class="line">C.INDISPRIMARY,</span><br><span class="line">C.INDISCLUSTERED,</span><br><span class="line">D.DESCRIPTION</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">PG_AM B <span class="keyword">left</span> <span class="keyword">join</span> PG_CLASS F <span class="keyword">on</span></span><br><span class="line">B.OID <span class="operator">=</span> F.RELAM <span class="keyword">left</span> <span class="keyword">join</span> PG_STAT_ALL_INDEXES E <span class="keyword">on</span></span><br><span class="line">F.OID <span class="operator">=</span> E.INDEXRELID <span class="keyword">left</span> <span class="keyword">join</span> PG_INDEX C <span class="keyword">on</span></span><br><span class="line">E.INDEXRELID <span class="operator">=</span> C.INDEXRELID <span class="keyword">left</span> <span class="keyword">outer</span> <span class="keyword">join</span> PG_DESCRIPTION D <span class="keyword">on</span></span><br><span class="line">C.INDEXRELID <span class="operator">=</span> D.OBJOID,</span><br><span class="line">PG_INDEXES A</span><br><span class="line"><span class="keyword">where</span></span><br><span class="line">A.SCHEMANAME <span class="operator">=</span> E.SCHEMANAME</span><br><span class="line"><span class="keyword">and</span> A.TABLENAME <span class="operator">=</span> E.RELNAME</span><br><span class="line"><span class="keyword">and</span> A.INDEXNAME <span class="operator">=</span> E.INDEXRELNAME</span><br><span class="line"><span class="keyword">and</span> E.SCHEMANAME <span class="operator">=</span> <span class="string">'public'</span></span><br><span class="line"><span class="keyword">and</span> E.RELNAME <span class="operator">=</span> <span class="string">'tableName'</span>;</span><br></pre></td></tr></table></figure>
<h1 id="3-索引失效"><a href="#3-索引失效" class="headerlink" title="3. 索引失效"></a>3. 索引失效</h1><p>如果where过滤条件设置不合理，即使索引存在，且where过滤条件中包含索引列，也会导致全表扫描，索引不起作用。<br>以下几种场景会导致索引失效：</p>
<ul>
<li>任何计算、函数、类型转换</li>
<li>!=</li>
<li>NOT，相当于使用函数</li>
<li>模糊查询通配符在开头</li>
<li>索引字段在表中占比较高</li>
<li>多字段btree索引查询条件不包含第一列 </li>
<li>多字段索引查询条件使用OR（有时也会走索引扫描，但查询效率不高）</li>
<li><font color=red face="黑体">如果SELECT返回大约超过表中所有行的5-10%，则顺序扫描要比索引扫描快得多。</font></li>
</ul>
<p>本次主要分析最后一种情况。我花费了半天才搞懂这情况。</p>
<h3 id="1-分析执行计划"><a href="#1-分析执行计划" class="headerlink" title="1. 分析执行计划"></a>1. 分析执行计划</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br></pre></td><td class="code"><pre><span class="line">Sort (cost<span class="operator">=</span><span class="number">42520.16</span>.<span class="number">.42520</span><span class="number">.39</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">92</span> width<span class="operator">=</span><span class="number">45</span>) (actual <span class="type">time</span><span class="operator">=</span><span class="number">181.770</span>.<span class="number">.181</span><span class="number">.780</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">72</span> loops<span class="operator">=</span><span class="number">1</span>)</span><br><span class="line">Sort Key: con.id</span><br><span class="line">Sort <span class="keyword">Method</span>: quicksort Memory: <span class="number">32</span>kB</span><br><span class="line"> <span class="operator">-</span><span class="operator">></span> HashAggregate (cost<span class="operator">=</span><span class="number">42515.32</span>.<span class="number">.42517</span><span class="number">.16</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">92</span> width<span class="operator">=</span><span class="number">45</span>) (actual <span class="type">time</span><span class="operator">=</span><span class="number">181.559</span>.<span class="number">.181</span><span class="number">.681</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">72</span> loops<span class="operator">=</span><span class="number">1</span>)</span><br><span class="line">" Group Key: con.id, con.feature_name, con.attr_name, con.attr_value"</span><br><span class="line"><span class="operator">-</span><span class="operator">></span> Hash <span class="keyword">Join</span> (cost<span class="operator">=</span><span class="number">4349.22</span>.<span class="number">.42413</span><span class="number">.72</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">6773</span> width<span class="operator">=</span><span class="number">45</span>) (actual <span class="type">time</span><span class="operator">=</span><span class="number">117.748</span>.<span class="number">.177</span><span class="number">.320</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">2460</span> loops<span class="operator">=</span><span class="number">1</span>)</span><br><span class="line">Hash Cond: ((s.feature_id <span class="operator">=</span> con.feature_id) <span class="keyword">AND</span> ((s.attr_name)::text <span class="operator">=</span> (con.attr_name)::text))</span><br><span class="line"><span class="keyword">Join</span> <span class="keyword">Filter</span>: ((con.attr_value <span class="keyword">IS</span> <span class="keyword">NULL</span>) <span class="keyword">OR</span> ((s.attr_value)::text <span class="operator">=</span> (con.attr_value)::text))</span><br><span class="line"><span class="keyword">Rows</span> Removed <span class="keyword">by</span> <span class="keyword">Join</span> <span class="keyword">Filter</span>: <span class="number">410</span></span><br><span class="line"><span class="operator">-</span><span class="operator">></span> Bitmap Heap Scan <span class="keyword">on</span> stats_result s (cost<span class="operator">=</span><span class="number">4311.10</span>.<span class="number">.39553</span><span class="number">.18</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">67106</span> width<span class="operator">=</span><span class="number">43</span>) (actual <span class="type">time</span><span class="operator">=</span><span class="number">117.118</span>.<span class="number">.172</span><span class="number">.912</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">4435</span> loops<span class="operator">=</span><span class="number">1</span>)</span><br><span class="line">Recheck Cond: (data_version_id <span class="operator">=</span> <span class="number">149</span>)</span><br><span class="line"> " Filter: (""position""((admin)::text, '21009408'::text) > 0)"</span><br><span class="line"><span class="keyword">Rows</span> Removed <span class="keyword">by</span> <span class="keyword">Filter</span>: <span class="number">193518</span></span><br><span class="line">Heap Blocks: exact<span class="operator">=</span><span class="number">1580</span></span><br><span class="line"><span class="operator">-</span><span class="operator">></span> Bitmap Index Scan <span class="keyword">on</span> version_feature_idx (cost<span class="operator">=</span><span class="number">0.00</span>.<span class="number">.4294</span><span class="number">.32</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">201319</span> width<span class="operator">=</span><span class="number">0</span>) (actual <span class="type">time</span><span class="operator">=</span><span class="number">32.292</span>.<span class="number">.32</span><span class="number">.292</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">197953</span> loops<span class="operator">=</span><span class="number">1</span>)</span><br><span class="line">Index Cond: (data_version_id <span class="operator">=</span> <span class="number">149</span>)</span><br><span class="line"> <span class="operator">-</span><span class="operator">></span> Hash (cost<span class="operator">=</span><span class="number">36.75</span>.<span class="number">.36</span><span class="number">.75</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">92</span> width<span class="operator">=</span><span class="number">37</span>) (actual <span class="type">time</span><span class="operator">=</span><span class="number">0.593</span>.<span class="number">.0</span><span class="number">.593</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">92</span> loops<span class="operator">=</span><span class="number">1</span>)</span><br><span class="line">Buckets: <span class="number">1024</span> Batches: <span class="number">1</span> Memory Usage: <span class="number">7</span>kB</span><br><span class="line"> <span class="operator">-</span><span class="operator">></span> Subquery Scan <span class="keyword">on</span> con (cost<span class="operator">=</span><span class="number">35.60</span>.<span class="number">.36</span><span class="number">.75</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">92</span> width<span class="operator">=</span><span class="number">37</span>) (actual <span class="type">time</span><span class="operator">=</span><span class="number">0.454</span>.<span class="number">.0</span><span class="number">.504</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">92</span> loops<span class="operator">=</span><span class="number">1</span>)</span><br><span class="line"><span class="operator">-</span><span class="operator">></span> Sort (cost<span class="operator">=</span><span class="number">35.60</span>.<span class="number">.35</span><span class="number">.83</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">92</span> width<span class="operator">=</span><span class="number">37</span>) (actual <span class="type">time</span><span class="operator">=</span><span class="number">0.453</span>.<span class="number">.0</span><span class="number">.463</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">92</span> loops<span class="operator">=</span><span class="number">1</span>)</span><br><span class="line">Sort Key: u.id</span><br><span class="line">Sort <span class="keyword">Method</span>: quicksort Memory: <span class="number">33</span>kB</span><br><span class="line"> <span class="operator">-</span><span class="operator">></span> Hash <span class="keyword">Join</span> (cost<span class="operator">=</span><span class="number">22.42</span>.<span class="number">.32</span><span class="number">.59</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">92</span> width<span class="operator">=</span><span class="number">37</span>) (actual <span class="type">time</span><span class="operator">=</span><span class="number">0.261</span>.<span class="number">.0</span><span class="number">.366</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">92</span> loops<span class="operator">=</span><span class="number">1</span>)</span><br><span class="line">Hash Cond: (u.feature_id <span class="operator">=</span> f.id)</span><br><span class="line"><span class="operator">-</span><span class="operator">></span> Seq Scan <span class="keyword">on</span> usage_info u (cost<span class="operator">=</span><span class="number">0.00</span>.<span class="number">.8</span><span class="number">.91</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">92</span> width<span class="operator">=</span><span class="number">25</span>) (actual <span class="type">time</span><span class="operator">=</span><span class="number">0.125</span>.<span class="number">.0</span><span class="number">.172</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">92</span> loops<span class="operator">=</span><span class="number">1</span>)</span><br><span class="line"><span class="keyword">Filter</span>: (view_id <span class="operator">=</span> <span class="number">7</span>)</span><br><span class="line"><span class="keyword">Rows</span> Removed <span class="keyword">by</span> <span class="keyword">Filter</span>: <span class="number">302</span></span><br><span class="line"><span class="operator">-</span><span class="operator">></span> Hash (cost<span class="operator">=</span><span class="number">21.63</span>.<span class="number">.21</span><span class="number">.63</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">63</span> width<span class="operator">=</span><span class="number">20</span>) (actual <span class="type">time</span><span class="operator">=</span><span class="number">0.115</span>.<span class="number">.0</span><span class="number">.115</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">63</span> loops<span class="operator">=</span><span class="number">1</span>)</span><br><span class="line">Buckets: <span class="number">1024</span> Batches: <span class="number">1</span> Memory Usage: <span class="number">4</span>kB</span><br><span class="line"> <span class="operator">-</span><span class="operator">></span> Seq Scan <span class="keyword">on</span> feature_info f (cost<span class="operator">=</span><span class="number">0.00</span>.<span class="number">.21</span><span class="number">.63</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">63</span> width<span class="operator">=</span><span class="number">20</span>) (actual <span class="type">time</span><span class="operator">=</span><span class="number">0.006</span>.<span class="number">.0</span><span class="number">.076</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">63</span> loops<span class="operator">=</span><span class="number">1</span>)</span><br><span class="line">Planning <span class="type">time</span>: <span class="number">1.097</span> ms</span><br><span class="line">Execution <span class="type">time</span>: <span class="number">181.969</span> ms</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>分析这个执行计划可以看出 sql连表查询了三张表（feature_info，usage_info，stats_result），前两张表都是顺序扫描，最后一张表用上了位图索引和位图复合索引没有问题。<br>查看feature_info和usage_info两张的索引：</p>
<table>
<thead>
<tr>
<th>schemaname</th>
<th>tablename</th>
<th>indexname</th>
<th>tablespace</th>
<th>indexdef</th>
<th>amname</th>
<th>indexrelid</th>
<th>indnatts</th>
<th>indisunique</th>
<th>indisprimary</th>
</tr>
</thead>
<tbody><tr>
<td>public</td>
<td>usage_info</td>
<td>usage_info_pkey</td>
<td>CREATE UNIQUE INDEX usage_info_pkey ON usage_info USING btree (id)</td>
<td>btree</td>
<td>27066381</td>
<td>1</td>
<td>TRUE</td>
<td>TRUE</td>
<td>FALSE</td>
</tr>
<tr>
<td>public</td>
<td>usage_info</td>
<td>view_id_index</td>
<td>CREATE INDEX view_id_index ON usage_info USING btree (view_id)</td>
<td>btree</td>
<td>28196880</td>
<td>1</td>
<td>FALSE</td>
<td>FALSE</td>
<td>FALSE</td>
</tr>
</tbody></table>
<hr>
<table>
<thead>
<tr>
<th>schemaname</th>
<th>tablename</th>
<th>indexname</th>
<th>tablespace</th>
<th>indexdef</th>
<th>amname</th>
<th>indexrelid</th>
<th>indnatts</th>
<th>indisunique</th>
<th>indisprimary</th>
<th>indisclustered</th>
<th>description</th>
</tr>
</thead>
<tbody><tr>
<td>public</td>
<td>feature_info</td>
<td>feature_info_pkey</td>
<td></td>
<td>CREATE UNIQUE INDEX feature_info_pkey ON feature_info USING btree (id)</td>
<td>btree</td>
<td>27066289</td>
<td>1</td>
<td>true</td>
<td>true</td>
<td>false</td>
<td></td>
</tr>
</tbody></table>
<p>测试usage_info这张表索引：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">explain analyse select * from usage_info where view_id = 7;</span><br></pre></td></tr></table></figure>
<p>执行计划：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">Seq Scan on usage_info (cost=0.00..8.91 rows=92 width=65) (actual time=0.124..0.161 rows=92 loops=1)</span><br><span class="line"> Filter: (view_id = 7)</span><br><span class="line"> Rows Removed by Filter: 302</span><br><span class="line">Planning time: 0.303 ms</span><br><span class="line">Execution time: 0.210 ms</span><br></pre></td></tr></table></figure>
<p>发现是顺序扫描，查询结果92条记录。<br>测试主键索引：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">explain analyse select * from usage_info where id = 7;</span><br></pre></td></tr></table></figure>
<p>执行计划：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">Index Scan using usage_info_pkey on usage_info (cost=0.15..8.17 rows=1 width=65) (actual time=0.017..0.019 rows=1 loops=1)</span><br><span class="line"> Index Cond: (id = 7)</span><br><span class="line">Planning time: 0.104 ms</span><br><span class="line">Execution time: 0.053 ms</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>结果是索引生效。</p>
<p>怀疑是表数据变化索引失效，所以把view_id字段索引删除，重新创建。结果依然是顺序索引，很崩溃。。。</p>
<p>查看PostgreSQL官方文档得出结论：<font color=red face="黑体">如果SELECT返回大约超过表中所有行的5-10%，则顺序扫描要比索引扫描快得多。</font></p>
<p>验证：<br>在usage_info新插入一条view_id= 8的数据：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">explain analyse select * from usage_info where id = 8;</span><br></pre></td></tr></table></figure>
<p>果然：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">Index Scan <span class="keyword">using</span> usage_info_pkey <span class="keyword">on</span> usage_info (cost<span class="operator">=</span><span class="number">0.15</span>.<span class="number">.8</span><span class="number">.17</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">1</span> width<span class="operator">=</span><span class="number">65</span>) (actual <span class="type">time</span><span class="operator">=</span><span class="number">0.030</span>.<span class="number">.0</span><span class="number">.034</span> <span class="keyword">rows</span><span class="operator">=</span><span class="number">1</span> loops<span class="operator">=</span><span class="number">1</span>)</span><br><span class="line"> Index Cond: (id <span class="operator">=</span> <span class="number">8</span>)</span><br><span class="line">Planning <span class="type">time</span>: <span class="number">0.147</span> ms</span><br><span class="line">Execution <span class="type">time</span>: <span class="number">0.081</span> ms</span><br></pre></td></tr></table></figure>
<p>查询view_id = 7时，结果row:92,整张表数据row:394。92/394 = 23%,此时seq scan 效率更高。</p>
<p>数据分布较小(比如 1.7%)，则用 bitmap index scan。数据更少的时候，用的是 index scan。</p>
<p>需要引起注意的是， bitmap index 也可以用在where 条件单一的时候。</p>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
</div>
<script>
window.addEventListener('tabs:register', () => {
let { activeClass } = CONFIG.comments;
if (CONFIG.comments.storage) {
activeClass = localStorage.getItem('comments_active') || activeClass;
}
if (activeClass) {
let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
if (activeTab) {
activeTab.click();
}
}
});
if (CONFIG.comments.storage) {
window.addEventListener('tabs:click', event => {
if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
let commentClass = event.target.classList[1];
localStorage.setItem('comments_active', commentClass);
});
}
</script>
</div>
<div class="toggle sidebar-toggle">
<span class="toggle-line toggle-line-first"></span>
<span class="toggle-line toggle-line-middle"></span>
<span class="toggle-line toggle-line-last"></span>
</div>
<aside class="sidebar">
<div class="sidebar-inner">
<ul class="sidebar-nav motion-element">
<li class="sidebar-nav-toc">
文章目录
</li>
<li class="sidebar-nav-overview">
站点概览
</li>
</ul>
<!--noindex-->
<div class="post-toc-wrap sidebar-panel">
</div>
<!--/noindex-->
<div class="site-overview-wrap sidebar-panel">
<div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
<img class="site-author-image" itemprop="image" alt="Mars"
src="/TechBlog/images/avatar.png">
<p class="site-author-name" itemprop="name">Mars</p>
<div class="site-description" itemprop="description">Hakuna Matata</div>
</div>
<div class="site-state-wrap motion-element">
<nav class="site-state">
<div class="site-state-item site-state-posts">
<a href="/TechBlog/archives/">
<span class="site-state-item-count">4</span>
<span class="site-state-item-name">日志</span>
</a>
</div>
<div class="site-state-item site-state-categories">
<a href="/TechBlog/categories/">
<span class="site-state-item-count">2</span>
<span class="site-state-item-name">分类</span></a>
</div>
<div class="site-state-item site-state-tags">
<a href="/TechBlog/tags/">
<span class="site-state-item-count">3</span>
<span class="site-state-item-name">标签</span></a>
</div>
</nav>
</div>
<div class="links-of-author motion-element">
<span class="links-of-author-item">
<a href="https://github.com/shadowboy88" title="GitHub → https://github.com/shadowboy88" rel="noopener" target="_blank"><i class="fab fa-github fa-fw"></i>GitHub</a>
</span>
<span class="links-of-author-item">
<a href="mailto:[email protected]" title="E-Mail → mailto:[email protected]" rel="noopener" target="_blank"><i class="fa fa-envelope fa-fw"></i>E-Mail</a>
</span>
</div>
<div class="links-of-blogroll motion-element">
<div class="links-of-blogroll-title"><i class="link fa-fw"></i>
链接网站
</div>
<ul class="links-of-blogroll-list">
<li class="links-of-blogroll-item">
<a href="https://baidu.com/" title="https://baidu.com" rel="noopener" target="_blank">百度</a>
</li>
<li class="links-of-blogroll-item">
<a href="https://fishc.com.cn/" title="https://fishc.com.cn" rel="noopener" target="_blank">鱼C论坛</a>
</li>
</ul>
</div>
</div>
</div>
</aside>
<div id="sidebar-dimmer"></div>
</div>
</main>
<footer class="footer">
<div class="footer-inner">
<div class="copyright">
© 2021-07 –
<span itemprop="copyrightYear">2021</span>
<span class="with-love">
<i class="fa fa-heart"></i>
</span>
<span class="author" itemprop="copyrightHolder">Mars</span>
</div>
<div class="powered-by">由 <a href="https://hexo.io/" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.org/" class="theme-link" rel="noopener" target="_blank">NexT.Gemini</a> 强力驱动
</div>
<div class="theme-info">
<div class="powered-by"></div>
<span class="post-count">total:3.1k</span>
</div>
</div>
</footer>
</div>
<script color='0,0,255' opacity='0.5' zIndex='-1' count='99' src="/TechBlog/lib/canvas-nest/canvas-nest.min.js"></script>
<script src="/TechBlog/lib/anime.min.js"></script>
<script src="/TechBlog/lib/velocity/velocity.min.js"></script>
<script src="/TechBlog/lib/velocity/velocity.ui.min.js"></script>
<script src="/TechBlog/js/utils.js"></script>
<script src="/TechBlog/js/motion.js"></script>
<script src="/TechBlog/js/schemes/pisces.js"></script>
<script src="/TechBlog/js/next-boot.js"></script>
<script src="/TechBlog/js/local-search.js"></script>
</body>
</html>