---
# System prepended metadata

title: PostgreSQL資料庫優化案例
tags: [database]

---

# PostgreSQL資料庫優化案例

## 前言

在大數據資料庫中，因為動輒幾百萬幾千萬甚至上億筆的資料，在做資料搜尋時，效能的提升變非常重要，提升手段有：

1. 語法的優化。
2. 資料庫表格的優化。
3. 資料本身的優化。



## 範例 - 資料庫表格優化
##### 1. 刪除Table（如果已經存在的話）
```
-- 刪除Table
DROP TABLE IF EXISTS public.my_test_01;
DROP TABLE IF EXISTS public.my_test_02;
```
##### 2. 建立Table
```
-- 建立Table
CREATE TABLE public.my_test_01
(
	key integer,
	score bigint,
	repeat text
);
```
##### 3. 建立測試資料
```
-- 建立測試資料
INSERT INTO my_test_01
SELECT (random()*1000000)::numeric(10,0) as key,
	   (random()*1000000000)::numeric(10,0) as score,
	   repeat('1',(random()*25)::integer) FROM generate_series(1, 50000000);
```
##### 4. 複製相同資料到另一個Table
```
-- 不需事先建立Table
SELECT * INTO my_test_02 FROM my_test_01;

-- 需事先建立Table
-- INSERT INTO my_test_02 SELECT * from my_test_01;
```
##### 5. 幫其中一個Table建立索引
```
-- 建立索引
CREATE INDEX my_index_02 ON my_test_02 (score);
```
##### 6. 確認兩個Table的資料筆數
```
-- 確認資料
SELECT count(*) as my_test_01 from my_test_01;
SELECT count(*) as my_test_02 from my_test_02;
```
##### 7. 測試搜尋速度並比較
```
-- 條件搜尋
SELECT * from my_test_01 WHERE score BETWEEN 30 AND 30000;
SELECT * from my_test_02 WHERE score BETWEEN 30 AND 30000;
```
> 會發現my_test_02 table即使一樣的搜尋也會比my_test_01 table快很多。

##### 8. 刪除索引（如果需要）
```
-- 刪除索引
DROP INDEX my_index_01;
```


