如何在Azure上部署一個Azure MySQL Database

前言

最近在公司裡,有在使用Azure雲端服務來架設各式各樣的服務,供測試與部署。

那隨著Azure金錢消耗量很大,負責管理Azure的人慢慢意識到Azure怎麼會這麼燒錢?

後來我慢慢的發現,最後得到下面這張結論圖:

原來是一開始大家就對於Azure的使用上弄錯了方向。其主要原因如下:

  • 不懂雲端運算平台的架構,導致大家都在IaaS層建立資源機器
  • 當然我前二個計畫也是在IaaS上開機器做服務出來,當然我開始了一些改變
    • 去年的時候,第一台機器所對應的計畫,就跟其他人一樣,都是開一台運算資源機器出來,上面架設需要的環境與安裝服務。
    • 第二台機器,一樣也是開資源機器起來,但是跟前一個不同的是,我把所有的服務做容器化,讓服務更好掌控與管理,不過還是一樣,還是需要在機器上建置Docker所需要的必要環境
    • 未來計畫,以Azure來說,會朝向使用PaaS,如「Azure Web App」等
      • 將每個需要的服務分開,並使用Azure MySQL/PgSQL/SQL 等DB的PaaS建立資料庫instance實例,以達到共用資料庫的目的

本文章,主要是要探討的內容如下:

  • 使用Azure上屬於PaaS種類包含
    • 「Azure Web App」與「Azure DB」
    • 以「Azure MySQL DB」服務建立MySQL資料庫

Azure Database介紹

在Azure雲端運算平台上,有現成的資料庫的服務可以使用與建立,那這些也算是PaaS期中的應用之一,其最主要的目的就是可以讓開發者省略設定與安裝資料庫的動作,透過Azure針對資料庫配置好的設定來建立資料庫,這樣的好處如下:

  • 開發者可以專心開發並不需要花費額外的心力在建置與設定資料庫上
  • 對於小型團隊,計畫有很大的幫助

Azure MySQL Database建立步驟

首先,我們可以先登入Azure portal,網址:https://portal.azure.com

登入之後,我們可以看到下面的截圖所示:

接著,點擊右邊的「新增資源」並會看到右邊有可以建置的資料庫種類清單,詳細如下面截圖

我們從上面的截圖可以找到我們要的「MySQL」資料庫,因此我們選擇「適用於 MySQL 的 Azure 資料庫」這個來建立本篇文章所要展示的資料庫

點擊下去之後,會有一些相關的設定需要填寫,如下截圖

因為我已經有建立一個名稱叫做「electric-data」的資料庫了,所以上面的截圖在資料庫名稱有紅色驚嘆號是很正常的。

其他的欄位如資料庫名稱,使用者帳號,密碼以及主機座落的位置以及MySQL版本等都可以自行填寫,這邊只是一個範例。

接著,都填寫完成之後,按下「建立」按鈕就可以開始把需要的資料庫建置起來了。

回到首頁,我們在搜尋欄位地方輸入「MySQL」就可以找到「適用於 MySQL 伺服器的 Azure 資料庫」這個名字,點選下去之後,就會看到所有MySQL資料庫的清單列表。

從上面截圖可以得知,我們有一個叫做「electric-data-store」的資料庫可以選擇,那點擊下去之後,就會有此MySQL資料庫相關設定頁面可以選擇。

下面截圖可以得知,裡面會有Azure MySQL資料庫相關的所有設定

從上面截圖,我們可以知道相關設定如下

  • 資料庫系統管理員帳號與密碼設定
  • 資料庫SSL連線啟動與設定
  • 連線設定「Connection security」

資料庫連線設定

點選左邊列表的「Connection security」,我們可以發現有防火牆規則與SSL連線設定可以設置,那這邊就是加入可以允許連線的IP位址範圍。

結論

本文章中,利用Azure portal管理介面去示範新增一個「Azure MySQL Database」並可以透過上面的資料庫設定頁面可以設定相關與MySQL資料庫有關的設定。

當然,Azure雲端運算平台上面還有其他得資料庫可以新增與使用,這就要留到後面有用到再說吧,不過我覺得其他新增資料庫的方式應該也是大同小異,與新建「MySQL」資料庫的方式不會差太多才對。

那些在MySQL上的分區方式

前言

最近因緣際會之下,有一個與能源相關的研究案,需要處理將近2億筆的資料,如果只做到用SQL做查詢的時候,把常搜尋的欄位加入index索引,或是避免一些欄位重複,加入的主鍵primary key。

上述這些方式在資料量大的時候,容易產生一些瓶頸,因為利用BTree概念所作的索引是有搜尋的極限。

為了增加查詢的速度,我們可以考慮的方式如下:

  • 依照年度劃分出個別的資料表
  • 每個年度的資料表用月份為單位劃分成多個分區,總計是12個分區

分區的好處如下:

  • 在資料表中做查詢的時候,可以指定分區,那只會找指定分區中的資料,而不會整張資料表做查詢,進而加快查詢的速度
  • 在分區資料表中,可以允許我們指定那個分區進行刪除資料的動作
  • 從MySQL 5.6之後,可以允許我們指定一個或多個分區中找尋我們要的資料
  • 使用了分區之後,也會影響對資料修改相關的SQL語法,包含: DELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML等

本文章,利用MySQL上面的分區功能實踐查詢的速度。

分區種類介紹

在講分區之前,我們可以從上面的示意圖知道,分區可以在Web應用程式在存取某個大張資料表的時候,先利用分區進行找尋範圍縮小的功能,接著在從指定的分區中尋找我們要的資料出來。分區的方式種類如下:

RANGE Partitioning

範圍分區,指的是自己定義某一個欄位,像是日期這種,就可以定義範圍做分區。

比如說,我們有一個2018 年整年的電量資料表叫做electric,裡面有電號,測量時間,測量的值等。所以依照敘述,我們可以建立出下面的資料表:


CREATE TABLE `electric_data_2018` (
`electricID` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '電號',
`date` datetime NOT NULL COMMENT '當下測量用電時間',
`mwh` double DEFAULT NULL COMMENT '用電量,單位wh'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='1000戶各用戶各時段用電量';

那我們可以利用測量的時間做分區,分區呈現的方式如下12個分區:

  • 2018-01-01到2018-01-31,即是「小於」2018-02-01 00:00:00
  • 2018-02-01到2018-02-28,即是「小於」2018-03-01 00:00:00
  • 2018-03-01到2018-03-31,即是「小於」2018-04-01 00:00:00
  • 2018-04-01到2018-04-30,即是「小於」2018-05-01 00:00:00
  • 2018-05-01到2018-05-31,即是「小於」2018-06-01 00:00:00
  • 2018-06-01到2018-06-30,即是「小於」2018-07-01 00:00:00
  • 2018-07-01到2018-07-31,即是「小於」2018-08-01 00:00:00
  • 2018-08-01到2018-08-31,即是「小於」2018-09-01 00:00:00
  • 2018-09-01到2018-09-30,即是「小於」2018-10-01 00:00:00
  • 2018-10-01到2018-10-31,即是「小於」2018-11-01 00:00:00
  • 2018-11-01到2018-11-30,即是「小於」2018-12-01 00:00:00
  • 2018-12-01到2018-12-31,即是「小於」2019-01-01 00:00:00

那以SQL方式建立帶有分區資料表方式如下:


ALTER TABLE `electric_data_2018`
PARTITION BY RANGE (TO_DAYS(`date`))
(
PARTITION p_201802 VALUES LESS THAN (TO_DAYS('2018-02-01 00:00:00')),
PARTITION p_201803 VALUES LESS THAN (TO_DAYS('2018-03-01 00:00:00')),
PARTITION p_201804 VALUES LESS THAN (TO_DAYS('2018-04-01 00:00:00')),
PARTITION p_201805 VALUES LESS THAN (TO_DAYS('2018-05-01 00:00:00')),
PARTITION p_201806 VALUES LESS THAN (TO_DAYS('2018-06-01 00:00:00')),
PARTITION p_201807 VALUES LESS THAN (TO_DAYS('2018-07-01 00:00:00')),
PARTITION p_201808 VALUES LESS THAN (TO_DAYS('2018-08-01 00:00:00')),
PARTITION p_201809 VALUES LESS THAN (TO_DAYS('2018-09-01 00:00:00')),
PARTITION p_201810 VALUES LESS THAN (TO_DAYS('2018-10-01 00:00:00')),
PARTITION p_201811 VALUES LESS THAN (TO_DAYS('2018-11-01 00:00:00')),
PARTITION p_201812 VALUES LESS THAN (TO_DAYS('2018-12-01 00:00:00')),
PARTITION p_201901 VALUES LESS THAN (TO_DAYS('2019-01-01 00:00:00')),
PARTITION p_max_future_dates VALUES LESS THAN MAXVALUE
);

  • 上述建立分區的意思就是,把「測量日期」欄位轉變成距離year 0有多少天數,將日期轉變成數字之後,我們就可以利用這個天數來進行所謂範圍分區的動作。
  • 其中,…..LESS THAN…..就是小於的意思,舉例來說,第一個分區指的是1月所有電量資料,則小於「2018-02-01 00:00:00」即是這個意思。
  • 當然,只要可以將天數轉換成數字當作範圍,都可以用來進行範圍分區,所以我們也可以轉換成UNIX TIMESTAMP的值做範圍分區。相關建立此分區方式如下:

ALTER TABLE `electric_data_2018`
PARTITION BY RANGE (UNIX_TIMESTAMP(`date`))
(
PARTITION p_201802 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-01 00:00:00')),
PARTITION p_201803 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-01 00:00:00')),
PARTITION p_201804 VALUES LESS THAN (UNIX_TIMESTAMP('2018-04-01 00:00:00')),
PARTITION p_201805 VALUES LESS THAN (UNIX_TIMESTAMP('2018-05-01 00:00:00')),
PARTITION p_201806 VALUES LESS THAN (UNIX_TIMESTAMP('2018-06-01 00:00:00')),
PARTITION p_201807 VALUES LESS THAN (UNIX_TIMESTAMP('2018-07-01 00:00:00')),
PARTITION p_201808 VALUES LESS THAN (UNIX_TIMESTAMP('2018-08-01 00:00:00')),
PARTITION p_201809 VALUES LESS THAN (UNIX_TIMESTAMP('2018-09-01 00:00:00')),
PARTITION p_201810 VALUES LESS THAN (UNIX_TIMESTAMP('2018-10-01 00:00:00')),
PARTITION p_201811 VALUES LESS THAN (UNIX_TIMESTAMP('2018-11-01 00:00:00')),
PARTITION p_201812 VALUES LESS THAN (UNIX_TIMESTAMP('2018-12-01 00:00:00')),
PARTITION p_201901 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-01 00:00:00')),
PARTITION p_max_future_dates VALUES LESS THAN MAXVALUE
);

若我們要看所有分區名稱在此資料表的資訊,我們可以使用下列的SQL做到。


SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='electric_data_2018';

當然,範圍分區也可以用在數值,比如說,我們可以利用用電量「mwh」這個欄位,將數值區分成下列幾塊:

  • partition p0 ( mwh < 100 )
  • partition p1 ( mwh < 500 )
  • partition p2 ( mwh <1000 )
  • partition p3 ( mwh <1500 )

相關SQL語法就會變成:


ALTER TABLE `electric_data_2018`
PARTITION BY RANGE (`mwh`)
(
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (500),
PARTITION p2 VALUES LESS THAN (1000),
PARTITION p3 VALUES LESS THAN (1500)
);

若我們想要刪除某一個分區,我們可以使用下列的SQL語法做到:


ALTER TABLE electric_data_2018 TRUNCATE PARTITION p0;

LIST Partitioning

清單分區,指的是,假設一張資料表,已經有一個預設的集合,假設如下:

上述表格意思是:

  • 有一個欄位叫做「agent_code」表示這三個城市的代碼
  • City A代碼是1,2,3
  • City B代碼是4,5,6
  • City B代碼是7,8,9,10,11

那用LIST分區所建立SQL語法如下:


CREATE TABLE sale_mast2 (bill_no INT NOT NULL, bill_date TIMESTAMP NOT NULL,
agent_codE INT NOT NULL, amount INT NOT NULL)
PARTITION BY LIST(agent_code) (
PARTITION pA VALUES IN (1,2,3),
PARTITION pB VALUES IN (4,5,6),
PARTITION pC VALUES IN (7,8,9,10,11));

意思是:

  • 將欄位「agent_code」城市代碼當作LIST分區
  • 若代碼1,2,3的放在pA分區名稱中,亦即City A
  • 若代碼4,5,6的方在pB分區中,亦即City B
  • 若代碼7,8,9,10,11放在pC分區中,亦即City C

由上述的分區概念,我們可以知道幾件事情:

  • 使用LIST做分區是指的是那個欄位可以分類成不同的種類/集合

COLUMNS Partitioning

以欄位名稱做分區,指的是,將一組欄位並將裡面的值當作範圍或是集合進行分區的概念,那又可以分成:

  • RANGE COLUMN Partitioning
  • LIST COLUMN Partitioning

RANGE COLUMN Partitioning

我們可以直接看下列的SQL語法範例:


CREATE TABLE table3 (col1 INT, col2 INT, col3 CHAR(5), col4 INT)
PARTITION BY RANGE COLUMNS(col1, col2, col3)
(PARTITION p0 VALUES LESS THAN (50, 100, 'aaaaa'),
PARTITION p1 VALUES LESS THAN (100,200,'bbbbb'),
PARTITION p2 VALUES LESS THAN (150,300,'ccccc'),
PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE));

  • 我們建立一個資料表叫做「table3」,而裡面的欄位分別有:「col1」,「col2」,「col3」與「col4」
  • 前三個欄位分別依序排列成col1, col2, clo3並在每一個分區作為一組
  • 每一個值列表使用定義好的條件進行用值當作範圍的分區
  • 篩選的時候,裡面資料的欄位順序需要相同

LIST COLUMNS partitioning

跟先前的「list partitioning」有點類似,不過這是以「COLUMNS」欄位做它配的欄位列表分區。我們城市代碼列表如下:

我們可以從下面SQL語法知道:


CREATE TABLE salemast ( agent_id VARCHAR(15), agent_name VARCHAR(50),
agent_address VARCHAR(100), city_code VARCHAR(10))
PARTITION BY LIST COLUMNS(agent_id) (
PARTITION pcity_a VALUES IN('A1', 'A2', 'A3'),
PARTITION pcity_b VALUES IN('B1', 'B2', 'B3'),
PARTITION pcity_c VALUES IN ('C1', 'C2', 'C3', 'C4', 'C5'));

  • 首先,先建立一個叫做「salemast」的資料表,欄位裡面包含了帳單ID,帳單的地址,名稱城市代碼等
  • 建立三種不同的分區,每個分區分別代表三個城市,因為每一個城市代碼會有不同但是有類似,那就可以用這種方法,利用城市代碼用來「LIST COLUMN Partitioning」

除了使用欄位有城市代碼之外,也可以使用欄位有「DATE」或是「DATETIMESTAMP」的型別來做「LIST COLUMN Partitioning」分區

我們用上述的「electric_data_2018」資料表當作例子,那分區的定就可以改成:


ALTER TABLE `electric_data_2018`

PARTITION BY RANGE COLUMNS (`date`)

(

PARTITION p_201802 VALUES LESS THAN ('2018-02-01 00:00:00'),

PARTITION p_201803 VALUES LESS THAN ('2018-03-01 00:00:00'),

PARTITION p_201804 VALUES LESS THAN ('2018-04-01 00:00:00'),

PARTITION p_201805 VALUES LESS THAN ('2018-05-01 00:00:00'),

PARTITION p_201806 VALUES LESS THAN ('2018-06-01 00:00:00'),

PARTITION p_201807 VALUES LESS THAN ('2018-07-01 00:00:00'),

PARTITION p_201808 VALUES LESS THAN ('2018-08-01 00:00:00'),

PARTITION p_201809 VALUES LESS THAN ('2018-09-01 00:00:00'),

PARTITION p_201810 VALUES LESS THAN ('2018-10-01 00:00:00'),

PARTITION p_201811 VALUES LESS THAN ('2018-11-01 00:00:00'),

PARTITION p_201812 VALUES LESS THAN ('2018-12-01 00:00:00'),

PARTITION p_201901 VALUES LESS THAN ('2019-01-01 00:00:00')

);

上述的分區方式就是指:

  • 利用「date」欄位是「datetime」型別,並用LIST COLUMN分區中的「RANGE COLUMN」分區
  • 分區就是「RANGE BY COLUMN」,並以月份做分區,總共分成12個月

HASH Partitioning

HASH分區指的是,利用某一個欄位,把它做內建的MySQL HASH雜湊之後得到的結果,並指定分區個數讓MySQL進行預先分區的動作。SQL範例如下:


CREATE TABLE student (student_id INT NOT NULL,
class VARCHAR(8), name VARCHAR(40),
date_of_admission DATE NOT NULL DEFAULT '2000-01-01')
PARTITION BY HASH(student_id)
PARTITIONS 4;

KEY Partitioning

此種分區方法是HASH分區中裡面的其中一種,主要是用來把主鍵或是設定UNIQUE唯一值的欄位拿去做雜湊之後並透過指定分區的數量分散到那些分區之中。SQL範例如下:


CREATE TABLE table1 ( id INT NOT NULL PRIMARY KEY,
fname VARCHAR(25), lname VARCHAR(25))
PARTITION BY KEY()
PARTITIONS 2;


CREATE TABLE table2 ( id INT NOT NULL, fname VARCHAR(25),
lname VARCHAR(25),
UNIQUE KEY (id))
PARTITION BY KEY()
PARTITIONS 2;

Sub partitioning

子分區,就是把原先的第一層分區,再做細分下去。其SQL範例如下:


CREATE TABLE table10 (BILL_NO INT, sale_date DATE, cust_code VARCHAR(15),
AMOUNT DECIMAL(8,2))
PARTITION BY RANGE(YEAR(sale_date) )
SUBPARTITION BY HASH(TO_DAYS(sale_date))
SUBPARTITIONS 4 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (2010),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

上述這個分區指的是:

  • 先把銷售日期取得年份,當作第一層分區
  • 接著,再第二層子分區為p0, p1, p2, p3並分成1990, 2000, 2010年份
  • 所以這樣就會有4 * 4 = 16 個分區,即p0, p1, p2, p3各有4個分區

 

參考資料

  • http://acmeextension.com/mysql-table-partitioning
  • https://www.w3resource.com/mysql/mysql-partition.php
  • https://www.w3schools.com/sql/func_mysql_to_days.asp

如何改善MySQL 5.7新增資料的速度

前言

  • 在文章中,我們會介紹與示範MySQL資料庫中,如何改善新增資料的速度
  • 並對照改善前與改善後的速度做一個比較的結果

前置環境

  • Ubuntu 16.04
  • MySQL 5.7版本

MySQL資料庫引擎介紹

在討論增加資料速度之前,需要先探討MySQL兩個常用的資料庫引擎,因為選擇不同的資料庫引擎,在新增資料的速度上也是會有差異的。

MyISAM

  • 此資料庫引擎簡易的設計與使用,給入門MySQL的人好使用
  • 在查詢速度上會比InnoDB快
  • 可以支援全文字搜尋,像是 …..WHERE column_name LIKE ‘%full_text_search%’
    • 這個特性在5.6以後,InnoDB已經有支援了
  • 使用的硬碟量大小會比InnoDB小,因為MyISAM會針對資料量的大小進行壓縮

MyISAM限制

  • 沒有外來鍵(foreign keys)的支援
  • 沒有交易(transaction)的機制可以使用
  • 每一個資料表最多只有有64個索引(index)
  • 每個資料表最大大小上限為 256TB

InnoDB

  • 有支援交易機制(transaction)
  • Row-level table locking 資料表鎖定
    • 這裡鎖定指的是,在寫入資料的同時,有使用者做查詢的動作。
    • 在InnoDB上來說,遭遇到上述的狀況,查詢速度並不會慢到哪裡去。
    • 但在MyISAM上遇到上述情形的話,會從本來同一個SQL查詢時間只要0.01秒,變成要3秒
  • 支援外來鍵(foreign keys)
  • MyISAM相關特性較少會增加,日後版本(MySQL 8.0)上,增加相關新特性都會以InnoDB為主

InnoDB限制

  • 沒有全文搜尋(前面有提過),但在MySQL 5.6版本以後就沒有這個問題
  • 資料量無法有效的壓縮,在MySQL 5.5之後有ROW_FORMAT=COMPRESSED可以設定
  • 資料表最大到64TB儲存(取決於InnoDB page size)

資料庫引擎的選擇方向與重點

  • 以我們所需要儲存的電量資料來說
    • 用MyISAM要承受的缺點
      • 在寫入資料表的時候會鎖定(lock),導致在這同時查詢的速度會變慢(Table locking)
      • 似乎無法做Cluster等分散式資料庫,在MySQL官網中只有提及InnoDB與NDB
      • 只可以做Replication master-slave主從架構
    • 可以選擇MyISAM的原因是
      • 如果大部分時間都是用來讀取電量資料,效能會更好
      • 在此電量資料中,沒有transaction需求
      • 也沒有外來鍵需求
      • 資料量大小較小,有壓縮過
  • 如下圖顯示,我們可以知道,在資料數一樣多的情況下,MyISAM大小比InnoDB小

插入資料步驟

  • 連接到資料庫server
  • 傳送SQL查詢給server
  • server解析SQL query
  • 插入一筆(row) (1 x size of row)
  • 插入索引(indexes) (1 x number of indexes)
  • 關閉server
  • 因為每次的插入資料,都會重複上述一系列的動作,那在打開資料表會造成overhead
  • 資料在插入的時候,會一併插入定義的索引,因此每次插入一筆資料的時候會變慢。(假設index是使用B-tree indexes)

插入資料效能改善

  • 首先,我們先讓bulk_insert_buffer_size設定成較高的大小
  • 用LOAD DATA載入檔案方式取代INSERT,原因是因為用INSERT寫入資料表會較慢
  • 在輸入資料的資料表中,若有欄位有設定預設值,若不需要改變預設值的情況下,不要設定值給此欄位,這樣會增加插入資料的速度

bulk_insert_buffer_size的修改

 

 

 

 

如上附圖可以知道此設定的相關資訊,下面有幾點此設定的特性。

  • 若資料表使用的引擎為MyISAM的話,在加入資料時候,它會使用特別的類似像樹概念快取讓區塊插入資料更快。這會影響到INSERT與LOAD DATA
  • 這個設定的值限制取決於在每一個位元下與thread,快取樹大小
  • 設定0則關閉這個優化,預設設定值為:8 MB

bulk_insert_buffer_size設定

如下的SQL指令可以設定此bulk_insert_buffer_size值

  • SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256
    • 這意思是將bulk_insert_buffer_size設定成:256 MB

MyISAM插入資料優化

  • 把bulk_insert_buffer_size設定成256 MB
    • 寫入2億筆用電量資料
      • 原本時間:2天左右
      • 設定後:5小時

InnoDB插入資料優化

  • 把bulk_insert_buffer_size設定成256 MB
    • 寫入2億筆用電量資料
      • 原本時間:2天左右
      • 設定後:約1天左右

因為InnoDB所支援的特性較多,因此需要將transaction commit,UNIQUE檢查與foreign key檢查關閉之後,再寫入資料會加速插入資料的時間。

關閉transaction auto commit check


set autocommit=0;

SQL query statement;

commit;

關閉unique checks


set unique_checks=0;

SQL query statement;

set unique_checks=1;

關閉foreign key checks


set foreign_key_checks=0;

SQL query statement;

set foreign_key_checks=1;

LOAD DATA使用方式

如下是使用LOAD DATA方式將用電量的CSV檔案匯入到資料表裡面

資料表的綱要如下:

欄位名稱欄位型態編碼與排序
electricIDVARCHAR(15)utf8mb4_unicode_ci
datedatetime
mwhdouble

LOAD DATA相關SQL語法如下:


LOAD DATA LOCAL INFILE '/path/to/electric_data.csv'

INTO TABLE electric_data_table

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS;

由上述的SQL插入資料語法,我們可以得到如下的資訊:

  • 指定本地端的CSV檔,有指定的路徑
  • 並指定寫入的資料表
  • 每個欄位之間設定以逗號「,」隔開
  • 每個封閉的欄位使用「”」隔開
    • 這個意思是,因為有一些欄位中的值會包含「,」,為了要避免解析的錯誤,我們通常會將此資料的值用「”」括起來,這樣的話在解析時候這個值就會成功的解析出來
    • 舉例來說,有一行(row)資料為:fields,”this is for enclosed fields,”,value
      • 有設定enclosed by ‘”‘的話,這個「this is for enclosed fields,」就會當成一個這個欄位的值解析出來了
  • 每一行斷行的字元為「\n」
  • 忽略第一行,這可以取決於CSV檔案內容,因為有一些CSV檔案內容第一行為欄位名稱,我們需要將它省略掉,因為此欄位名稱不需要進入到資料表中

結論

評估項目名稱MyISAMInnoDB
需要全文字搜尋
(full-text search)
Yes5.6.4開始支援
需要交易
(transactions)
Yes
需要頻繁的查詢
(frequent select queries)
Yes
需要頻繁的新增,修改與刪除
(frequent select, insert,update)
Yes
需要資料表在插入同時,也有良好的查詢效果Yes
需要關聯式資料表設計
(foreign keys等)
Yes
  • 如果有頻繁的讀取,沒有頻繁的新增,修改與刪除→MyISAM
  • 若使用MySQL版本比5.6還舊且需要full text search→MyISAM

參考資料

  • https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html
  • https://gist.github.com/peter279k/225b7b4803b4f80400840777ed1583a3

作者群