The 21st century: Database

顯示具有 Database 標籤的文章。顯示所有文章

手機 APP 持久化儲存的方案整理

https://www.youtube.com/watch?v=ICSmvT1Wk8A&list=PLzKtnppOmiXAlARyp-aRjP0e-RsWiS4UW&index=48

https://stackoverflow.com/questions/7058858/should-i-use-nsuserdefaults-or-a-plist-to-store-data

https://www.cnblogs.com/coderwayne/p/3441898.html

Preference (偏好設定) 可以視為是一種 iOS 已經封裝好，方便開發者使用的 key-value 小型資料庫，少量資料時使用 Preference (偏好設定) 的效能會優於 SQL 資料庫，反之，大量資料就應該使用 SQL 資料庫。

Preference 會由 iOS 自動轉換為 Plist 進行持久化儲存，所以 Preference 與 Plist 兩者之間沒有效能差異的問題。

但是 Preference (偏好設定) 是自動存放於 Library/Preference，會被 iTunes 自動備份的，所以不可以拿來存放那些不應該被 iTunes 同步的資料，如果濫用會導致無法上架。

Plist 檔案和 archiving (封存) 都是一次讀取整個檔案到記憶體中，所以如果需要持久化儲存的資料龐大到一定程度時，則應該使用 SQL 資料庫。

Q. Plist 檔案提取 UserDefaults 時會拉回整個 Array 再尋找相關 key 值，那麼分開儲存或合併儲存在同一個 Array，那種方式效率較高？

https://ithelp.ithome.com.tw/articles/10200504

網友實測結果是合併儲存在同一個 Array 較高。

=> 不過，原本就不應該使用 Plist 檔案儲存龐大資料的。分開存的優點是不用一次讀取所有資料到記憶體，所以應該仍是端視實際場景的需求做規劃。

plist vs sqlite 性能問題：

http://hk.uwenku.com/question/p-trlgrsbh-v.html

https://stackoverflow.com/questions/1090391/plist-or-sqlite/1090414#1090414

https://softwareengineering.stackexchange.com/questions/279080/is-there-a-detriment-to-using-sqlite-over-a-plist-for-persistence

PList 是一種文件格式，用於存儲少量結構數據（小於數百KB），通常是字典。儘管可以輕鬆編寫代碼對 PList 進行排序，但 PList 本身並不具有排序功能。

SQLite 是一個完善的數據庫。文件大小（在 iPhone 上）基本上是無限的。內置排序功能。可以進行查詢和關係表設計。性能應與您可能想到的任何排序算法一樣好。

另一方面，sqlite 數據庫將只加載你請求的數據。我不確定您的數據是如何構建的，但您可以很容易地使用單個數據庫表創建鍵值對。（一鍵列和值列的一個表）。然後，也可以寫一個 Objective-C 類來包裝數據庫查詢，以便能夠寫簡單的語句，如：

NSString *welcomeText = [[MyData sharedData] dataWithKey:@"WelcomeText"];

獲取將數據放入數據庫中並不一定非常困難，可以使用命令行 sqlite3 實用程序來批量加載數據。有一個名爲 .import 的命令將從文本文件中導入數據。

另外，有非常多理由不建議使用 coreData 資料庫。

使用 Plist、xml 等「非 SQL 解決方案」和 SQL（無論是原始數據還是作為 Core Data 中的持久性選項之一）之間的區別在於，「非 SQL 解決方案」會將整個持久化文檔加載到記憶體中，而 SQL 僅會加載當前所需的內容。

如果是少量的資料，由於 Plist 等「非 SQL 解決方案」是一次性加載所有資料到記憶體，這時候非 SQL 解決方案的效能會比 SQL 好；反之，大量資料則是選擇 SQL 方案。

iOS 資料儲存的方法
https://codertw.com/ios/326714/
https://www.itread01.com/p/383654.html
https://www.itread01.com/p/366949.htm

http://www.iotec.tw/?paged=2&cat=12

沙盒機制的議題談的是存在哪裡？不同位置的特性不同

Bundle 和沙盒 (sandbox) 之間的區別：

Bundle：應用程式在手機中的安裝路徑。
sandbox：專門來儲存當前 APP 自己的資料的路徑。

沙盒機制：在 iOS 中每個 APP 都擁有自己的沙盒，APP 只能訪問對應沙盒中儲存的資料，iOS 是不允許跨越沙盒去訪問資料的，所有的資料都是儲存在該沙盒的三個子目錄下：
Document
Library (Library/Caches, Library/Preference)
temp

Document：一般在該目錄下儲存一些比較重要的資料，比如遊戲相關的資料，當連線 iTunes後會自動同步資料。

必須注意，如果將資料資源儲存到該目錄，上架可能會被拒絕 (解決方案是直接設定該資料夾不被 iTunes 備份)，總之，不能儲存從網上下載的資料，否則不能上架。

Library：儲存應用設定或者狀態資訊等，在該目錄下還有兩個子目錄：Caches 和 Preference

Library/Caches 存放快取檔案，iTunes 不會備份，因此檔案不會因 APP 退出而刪除 (一般使用SDWebImage 的快取資源都是儲存到這兒)

Library/Preference 儲存應用的所有偏好設定，iOS 的 Setting (設定) 會在該目錄查詢該應用的設定資訊，iTunes會同步資料。

temp：臨時檔案，iTunes不會備份該資料夾中的資料，這個資料夾中的資料，會因為應用的關閉而刪除。

============================================

Preference (偏好設定)

iOS開發中也會用到 (Preference) 偏好設定來儲存資料，比如儲存使用者的使用者名稱、密碼、字型大小等設定。通過 NSUserDefaults 來存取偏好設定。

用來儲存應用程式設定和屬性、使用者儲存的資料，使用者再次開啟程式或開機後這些資料仍然存在。

NSUserDefaults 可以儲存的資料型別包括：NSData、NSString、NSNumber、NSDate、NSArray、NSDictionary。如果要儲存其他型別，則需要轉換為前面的型別，才能用NSUserDefaults儲存。

Plist (屬性列表) 檔案

Plist 檔案的全名是 Property List (屬性列表) 檔案，屬性列表檔案的副檔名為 .plist，因此通常被稱為 plist 檔案。檔案是 xml 格式的。

plist 檔案能儲存字典和陣列，不能儲存物件。

資料歸檔/解檔 (序列化/反序列化) (NSKeyedArchiver)

NSKeyedArchiver 是一種輕量級儲存的持久化方案，資料化歸檔時經過二進位制處理的，所以安全性會比 plist 檔案明碼儲存稍微好一些些。

(無論是何種儲存方式，安全性最終都取決於資料是否加密後再持久化，而加密方式又有多種方案，比如說對稱金鑰、非對稱金鑰等等)

資料歸檔可以儲存一些複雜的物件，資料儲存前會經過二進位制處理。

採用歸檔的形式來儲存資料，該資料物件需要遵守 NSCoding 協議，並且該物件對應的類必須提供 encodeWithCoder 和 initWithCoder 方法。前一個方法告訴系統怎麼對物件進行編碼，而後一個方法則是告訴系統怎麼對物件進行解碼。

以歸檔的形式儲存資料，只能一次性歸檔儲存以及一次性解壓，所以只能用於少量資料，即便只想改動資料的某一部分，就需要解壓整個資料，再歸檔整個資料。

coreData

coreData 是蘋果官方在 iOS5 之後推出的綜合性資料庫，其使用了物件關係對映技術，將物件轉換成資料，將資料儲存在本地的資料庫中。

coreData為了提高效率，需要將資料儲存在不同的資料庫中，比如在使用的時候，最好是將本地的資料儲存到記憶體中，這樣的目的是訪問速度比較快。

蘋果內建框架，和 Xcode 深度結合，可以很方便進行 ORM；但其上手學習成本較高，不容易掌握。穩定性也堪憂，很容易 crash；多線程的支持也比較雞肋。

SQLite

注意：寫入資料庫，字串可以採用 char 方式，而從資料庫中取出 char 型別，當 char 型別有表示中文字元時，會出現亂碼。這是因為資料庫預設使用 ascII 編碼方式。所以要想正確從資料庫中取出中文，需要用 NSString 來接收從資料庫取出的字串。

直接使用 SQLite 資料庫的缺點包括了它沒有提供資料庫的建立方式；它的底層是基於 C 語言框架設計的，沒有面向物件的 API，用起來非常麻煩；複雜的資料模型的資料建表，非常麻煩。

在實際開發中，往往都是使用第三方開源、基於 SQLite 封裝的面向物件的框架。

在 iOS 裡操作 SQLite：

https://dotblogs.com.tw/toysboy21/2014/04/21/144813

Android 資料儲存的方法
https://codertw.com/android-%E9%96%8B%E7%99%BC/335701/

SharePreference

適用於儲存一些鍵值對。無法進行條件查詢。只能儲存 boolean, int, float, long, string 五種資料型別。
儲存少量的資料，且這些資料的格式非常簡單：字串型、基本型別的值。比如應用程式的各種配置資訊，是否開啟音效、是否使用震動效果、小遊戲的玩家積分等

檔案儲存

適用於儲存一些簡單的文字資料或者二進位制資料。
可以在裝置本身的儲存裝置或者外接的儲存裝置中建立用於儲存資料的檔案。
在預設的狀態下，檔案不能在不同的程式間共享。

SQLite

SQLite Database 資料庫。Android 對資料庫的支援很好，它本身整合了 SQLite 資料庫，每個應用都可以方便的使用它，或者更確切的說，Android 完全依賴於 SQLite 資料庫，它所有的系統資料和結構化資料都儲存在資料庫中。它具有以下優點：a. 效率出眾，這是無可否認的； b. 十分適合儲存結構化資料；c. 方便在不同的 Activity，甚至不同的應用之間傳遞資料。

Content Provider

Android 系統中能實現所有應用程式共享的一種資料儲存方式，由於資料通常在各應用間的是互相私密的，所以此儲存方式較少使用，但又是必不可少的一種儲存方式。
例如音訊，視訊，圖片和通訊錄，一般都採用此種方式進行儲存。
每個 ContentProvider 都會對外提供一個公共的 URI（包裝成 Uri 物件），如果應用程式有資料需要共享時，就需要使用 ContentProvider 為這些資料定義一個 URI，然後其他的應用程式就通過 Content Provider 傳入這個 URI 來對資料進行操作。

SQLite 是遵守 ACID 的關聯式資料庫管理系統，它包含在一個相對小的 C 程式庫中。與許多其它資料庫管理系統不同，SQLite 不是一個客戶端/伺服器結構的資料庫引擎，而是被整合在用戶程式中。

SQLite 遵守 ACID，實現了大多數 SQL 標準。作為嵌入式資料庫，是應用程式，如網頁瀏覽器，在本地/客戶端儲存資料的常見選擇。

它可能是最廣泛部署的資料庫引擎，因為它正在被一些流行的瀏覽器、作業系統、嵌入式系統所使用。同時，它有許多程式設計語言的語言繫結。

直接使用 SQLite 資料庫的缺點包括了它沒有提供資料庫的建立方式；它的底層是基於 C 語言框架設計的，沒有面向物件的 API，用起來非常麻煩；複雜的資料模型的資料建表，非常麻煩。

在實際開發中，往往都是使用第三方開源、基於 SQLite 封裝的面向物件的框架。

Wiki SQLite
https://en.wikipedia.org/wiki/SQLite

IOS DB 技術框架比較
https://www.twblogs.net/a/5d7dcd1fbd9eee541c3449db

綜合來說，比較優先考慮的框架是 SQLite.swift 或 WCDB.swift

SQLite 的優點
SQLite 是輕量級的，沒有客戶端和服務器端之分，並且是跨平臺的關係型數據庫。
SQLite 是一個單文件，可以 copy 出來在其他地方用。
有一個 SQLite.swift 框架非常好用。

SQLite 的缺點
SQLite 在併發的讀寫方面性能不是很好，數據庫有時候可能會被某個讀寫操作獨佔，可能會導致其他的讀寫操作被阻塞或者出錯。
不支持 SQL92 標準，有時候語法不嚴格也可以通過，會養成不好習慣，導致不會維護。
需要寫很多 SQL 拼接語句，寫很多膠水代碼，容易通過 SQL 注入惡意代碼。
效率很低：SQL 基於字符串，命令行愛好者甚喜之。但對於基於現代 IDE 的移動開發者，卻是一大痛。字符串得不到任何編譯器的檢查，業務開發往往心中一團熱火，奮筆疾書下幾百行代碼，滿心歡喜點下 Run 後才發現：出錯了！靜心下來逐步看 log、斷點後才發現，噢，SELECT 敲成 SLEECT 了。改正，再等待編譯完成，此時已過去十幾分鐘。

SQLite3
獨立程式用來查詢和管理 SQLite 資料庫檔案。SQLite 的使用者可以把這個程式當作如何寫 SQLite 應用程式的範例。

SQLite3 直接使用比較麻煩，如果使用 Swift 版本推薦使用 SQLite.swift，這是很好的框架，比使用 FMDB 簡單，代碼簡潔很多。SQLite.swift 對 SQLite 進行了全面的封裝，擁有全面的純swift 接口，即使你不會 SQL 語句也可以使用數據庫。作者採用了鏈式編程的寫法，讓數據庫的管理變得優雅，可讀性也很強。

SQLite.swift
https://github.com/stephencelis/SQLite.swift
https://www.itread01.com/p/386972.html

Realm

在各平臺封裝、優化的優勢，較受移動開發者青睞。對於iOS開發者，key-value 的實現直接易懂，可以像使用 NSDictionary 一樣使用 Realm。並且 ORM 徹底，省去了拼裝 Object 的過程。但其對代碼侵入性很強，Realm 要求類繼承 RLMObject 的基類。這對於單繼承的 Objective-C 意味着不能再繼承其他自定義的子類。同時，key-value 數據庫對較爲複雜的查詢場景也比較無力。

FMDB

眾多開發者長期的使用經驗後發現，有性能瓶頸，比如說某個用戶長期未登錄，在登錄時收到大量消息，由於 FMDB 不支持多線程的寫操作，會導致寫入很慢。

基於 SQLite 封裝，對於有 SQLite 和 Objective-C 基礎的開發者來說，簡單易懂，可以直接上手；而缺點也正是在此，FMDB 只是將 SQLite 的 C 接口封裝成了 Objective-C 接口，沒有做更多優化，即所謂的膠水代碼 (Glue Code)。使用過程需要用大量的代碼拼接 SQL、拼裝 Object，既不方便也難以防止 SQL 注入。

如何使用 FMDB： https://medium.com/@mikru168/c9a3dc148c3c

WCDB

貼一份評論

WCDB.swift 使用官方文檔：
https://github.com/Tencent/wcdb/wiki/Swift-%E5%A2%9E%E5%88%A0%E6%9F%A5%E6%94%B9

https://codertw.com/%E7%A8%8B%E5%BC%8F%E8%AA%9E%E8%A8%80/642941/
https://www.zhihu.com/question/60914681

https://codertw.com/%E7%A8%8B%E5%BC%8F%E8%AA%9E%E8%A8%80/608382/

https://codertw.com/%E7%A8%8B%E5%BC%8F%E8%AA%9E%E8%A8%80/608379/

https://www.twblogs.net/a/5c64f8f1bd9eee06ee22b624

MySQL 的 varchar 到底可以存幾個中文字或幾個英文字呢

utf8 編碼為 1~3 個字节
utf8mb4 編碼為 1~4 個字节

UTF-8：一个汉字 = 3个字节，英文是一个字节

varchar(n) 表示n个字符，无论汉字和英文，MySql都能存入 n 个字符，仅实际字节长度有所区别。

MySQL检查长度，可用SQL语言
SELECT LENGTH(fieldname) FROM tablename

以下為網友使用 UTF-8 的實驗

>> "我爱Ruby".size
=> 6
>> "I Love Ruby!".size
=> 12

mysql> select * from str_test;
+------------+--------------+
| name_chn | name_en |
+------------+--------------+
| 我爱Ruby | I Love Ruby! |
+------------+--------------+
1 row in set (0.02 sec)

mysql> select length(name_chn) from str_test;
+------------------+
| length(name_chn) |
+------------------+
| 10 |
+------------------+
1 row in set (0.01 sec)

3[一个汉字三字节] * 2 + 1[一个英文一字节] * 4 = 10

mysql> select length(name_en) from str_test;
+-----------------+
| length(name_en) |
+-----------------+
| 12 |
+-----------------+
1 row in set (0.00 sec)

10[一个英文一字节] * 1 + 2[空格一字节] * whitespace = 12

关于varchar 最多能存多少值

mysql的记录行长度是有限制的，不是无限长的，这个长度是64K，即65535个字节，对所有的表都是一样的。

MySQL对于变长类型的字段会有1-2个字节来保存字符长度。

当字符数小于等于255时，MySQL只用1个字节来记录，因为2的8次方减1只能存到255。

当字符数多余255时，就得用2个字节来存长度了。

在utf-8状态下的varchar，最大只能到 (65535 - 2) / 3 = 21844 余 1。

結論：

從 MySQL 8 開始，預設的 CHARSET 為 utf8mb4，COLLATE 為 utf8mb4_0900_ai_ci

所以 VARCHAR 理論上可以儲存大約 (65535-2)/4 = 16383，
實測後最大為 VARCHAR(16331)

參考資料：
https://21st.each1.net/2017/03/mysql.html
https://ruby-china.org/topics/24920

Golang 與 MySQL 數據類型的對應

BadgerDB

https://github.com/dgraph-io/badger

Badger v1.0於2017年11月發布。

Badger is stable and is being used to serve data sets worth hundreds of terabytes. Badger supports concurrent ACID transactions with serializable snapshot isolation (SSI) guarantees. A Jepsen-style bank test runs nightly for 8h, with --race flag and ensures maintainance of transactional guarantees. Badger has also been tested to work with filesystem level anomalies, to ensure persistence and consistency.

BadgerDB 相當穩定，已經被用於數百TB的數據集。Badger 支持具有可序列化快照隔離（SSI）保證的並發 ACID 事務。 Jepsen風格的銀行測試每晚運行8小時，帶有--race標誌並確保維護交易保證。Badger 還經過測試，可以處理文件系統級異常，以確保持久性和一致性。

一个高性能的 LSM K/V store
https://colobu.com/2017/10/11/badger-a-performant-k-v-store/

这是 dgraph.io 开发的一款基于 log structured merge (LSM) tree 的 key-value 本地数据库，使用 Go 开发。

事实上，市面上已经有一些知名的基于 LSM tree 的 k/v数据库，比如 leveldb、goleveldb、rocksdb，既然已经有了一些优秀的开源的LSM tree的项目，为什么dgraph还要创建一个新的轮子呢？

答案是: 更好的性能。

BoltDB

https://github.com/boltdb/bolt

BoltDB 為一個單機的 key-value 數據庫，模型異常簡單且漂亮，代碼簡潔，總共代碼 (除去測試代碼) 只有6K左右。

以 Golang 編程。

核心代碼大約只有 3000 行，便於學習。

對於想學習數據庫模型的同學，強烈推薦閱讀 BoltDB 源碼，理解 BoltDB 模型，閱讀其代碼，不只是學習，也是享受。

Bolt supports fully serializable ACID transactions.
BoltDB 支持完全可序列化的 ACID 事務。

ACID，是指資料庫管理系統（DBMS）在寫入或更新資料的過程中，為保證事務（transaction）是正確可靠的，所必須具備的四個特性：原子性（atomicity，或稱不可分割性）、一致性（consistency）、隔離性（isolation，又稱獨立性）、持久性（durability）。

在資料庫系統中，一個事務是指：由一系列資料庫操作組成的一個完整的邏輯過程。例如銀行轉帳，從原帳戶扣除金額，以及向目標帳戶添加金額，這兩個資料庫操作的總和，構成一個完整的邏輯過程，不可拆分。這個過程被稱為一個事務，具有 ACID 特性。

Atomicity（原子性）：一個事務（transaction）中的所有操作，或者全部完成，或者全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。即，事務不可分割、不可約簡。

Consistency（一致性）：在事務開始之前和事務結束以後，資料庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設約束、觸發器、級聯回滾等。

Isolation（隔離性）：資料庫允許多個並發事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務並發執行時由於交叉執行而導致數據的不一致。事務隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重複讀（repeatable read）和串行化（Serializable）。

Durability（持久性）：事務處理結束後，對數據的修改就是永久的，即便系統故障也不會丟失。

BoltDB 的優缺點
https://zhuanlan.zhihu.com/p/47214093

BoltDB 源碼相當清晰，沒有黑魔法，就是經典的B+Tree實現，在工程中非常可控，簡單可靠。

B+Tree 天然優勢，隨機查詢比 LSM tree (Log-structured merge-tree) 快。

BoltDB 為了規避 mmap 寫入性能下降的問題，只有在讀取的時候走 mmap，寫回是在 tx.go 的func (tx *Tx) Commit() error 然後跳到 tx.db.ops.writeAt。
原理可以搜索 unified buffer cache，用 mmap 寫大文件時需要留意此問題。

如果內存 > 數據庫 && 讀 > 寫 && 穩定 > 性能，可以考慮選擇 BoltDB。

基于 Go 的 K/V 数据库 BoltDB 簡介與使用說明
https://godoc.org/github.com/boltdb/bolt
https://studygolang.com/articles/10433
http://www.opscoder.info/boltdb_intro.html
https://zhuanlan.zhihu.com/p/27444660
https://iter01.com/442959.html

MySQL 在小內存 VPS 上的優化

https://blog.kuoruan.com/72.html
https://www.vpsee.com/2009/06/64mb-vps-optimize-mysql/
https://snippetinfo.net/mobile/media/1596
http://www.mysqlcalculator.com/
https://dev.mysql.com/doc/refman/5.7/en/memory-use.html

MySQL 允許外部連入

google "mysql 8 remote access"
google "mysql 8 remote access ssl"
google "gcp mysql 遠程連接"

MySQL 8 的新特性與解決方式
http://www.manongjc.com/article/13483.html

* 創建用戶與授權方式的改變
* 遠程連結強制加密
* 預設為 caching_sha2_password

Mysql 允許外部連入
https://contemplator.github.io/blog/2018/06/06/Mysql-%E5%85%81%E8%A8%B1%E5%A4%96%E9%83%A8%E9%80%A3%E5%85%A5/

* 修改 Mysql 設定檔 - /etc/mysql/mysql.conf.d/mysqld.cnf
註解原本的 bind-address

* 修改後需要重啟 mysql
$ sudo service mysql restart

* 開通防火牆
如果是 Google Cloud ，就是去開通防火牆端口，
如果是其他的 VPS ，則可能是參考如下：

$ /sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

eht0 是網路卡名稱，比如說查出來的結果是 ens3

查詢的方式，在 VPS server 輸入以下指令

$ ifconfig

* 測試是否可以連到 mysql，先不論帳號密碼和權限

telnet <your-mysql-ip> 3306

How to Enable SSL and Remote Connections for MySQL on CentOS 7
https://www.howtoforge.com/tutorial/how-to-enable-ssl-and-remote-connections-for-mysql-on-centos-7/

如何在 Google Compute Engine 上安裝最新版 MySQL

免費 Instance 的記憶體太小不足以運作 MySQL 8，需要先規劃虛擬記憶體 Swap 以增加記憶體，否則啟動 MySQL 伺服器時會失敗：
https://21st.each1.net/2018/03/swap.html

然後以官方文件為基礎：
https://cloud.google.com/solutions/setup-mysql?hl=zh-tw

再以下篇更完整的概念去客製化：
https://www.itread01.com/article/1535250863.html

在 CLI 查詢 MySQL/MariaDB 版本 (不需要登入 MySQL)：

mysql -V

以 CentOS 7 安裝 MySQL 8 為例：

# 先查出 CentOS 7 預設的資料庫 MariaDB、以及可能已安裝的 MySQL
rpm -qa | grep mysql
rpm -qa | grep mariadb

# 通過下面的命令進行刪除
sudo yum remove mysql-xxx-xxx-

然後，

1. 更新套件管理員以納入 MySQL。
sudo rpm -Uvh http://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

2. 安裝 MySQL。
sudo yum -y install mysql-community-server

3. 啟動 MySQL 伺服器。
sudo /usr/bin/systemctl start mysqld

# 啟動成功後可以檢視初始化密碼，這是在安裝時隨機生成的
sudo cat /var/log/mysqld.log | grep password

# 提升 MySQL 安裝的安全性
sudo mysql_secure_installation

配置檔
在 Windows 上，位置是
C:\ProgramData\MySQL\MySQL Server 8.0/my.ini

在 Linux 上，位置是
/etc/my.cnf

go 語言操作 MySQL 範例

MySQL 本身的相關知識：
http://www.runoob.com/mysql/mysql-transaction.html
https://blog.longwin.com.tw/2006/03/innodb_transaction_2006/
https://xyz.cinc.biz/2013/05/mysql-transaction.html

Golang 操作相關知識：
https://ithelp.ithome.com.tw/articles/10207409
https://segmentfault.com/a/1190000003036452

https://www.jianshu.com/p/bc8120bec94e
http://hopehook.com/2017/08/21/golang_transaction/
https://www.cnblogs.com/hupengcool/p/4143238.html
https://www.kancloud.cn/liupengjie/go/570128

* 事務處理 ( transaction )

tx, err := db.Begin()
...
...
...

tx.Commit()

* tx对象

一般查询使用的是db对象的方法，事务则是使用另外一个对象。sql.Tx对象。使用db的Begin方法可以创建tx对象。tx对象也有数据库交互的Query,Exec和Prepare方法。用法和db的相关用法类似。查询或修改的操作完毕之后，需要调用tx对象的Commit提交或者Rollback方法回滚。

一旦创建了tx对象，事务处理都依赖与tx对象，这个对象会从连接池中取出一个空闲的连接，接下来的sql执行都基于这个连接，直到commit或者rollback调用之后，才会把连接释放到连接池。

在事务处理的时候，不能使用db的查询方法，虽然后者可以获取数据，可是这不属于同一个事务处理，将不会接受commit和rollback的改变，一个简单的事务例子如下：

tx, err := db.Begin()
tx.Exec(query1)
tx.Exec(query2)
tx.Commit()

在tx中使用db是错误的：

tx, err := db.Begin()
db.Exec(query1)
tx.Exec(query2)
tx.commit()

上述代码在调用db的Eexc方法的时候，tx会绑定连接到事务中，db则是额外的一个连接，两者不是同一个事务。需要注意，Begin和Commit方法，与 sql 语句中的 BEGIN 或 COMMIT 语句没有关系。

關於事務的相關細節應該詳閱 https://www.jianshu.com/p/bc8120bec94e
涉及到 Transaction 事务、tx对象、事务与连接、事务并发、实践。

* 如果在rows遍历结束之前退出循环，必须手动关闭Resultset，并且接收error。

for rows.Next() {
// ...
break; // whoops, rows is not closed! memory leak...
}
// do the usual "if err = rows.Err()" [omitted here]...
// it's always safe to [re?]close here:
if err = rows.Close(); err != nil {
// but what should we do if there's an error?
log.Println(err)
}

* 如果id为1的不存在，err为sql.ErrNoRows，一般应用中不存在的情况都需要单独处理。此外，Query返回的错误都会延迟到Scan被调用，所以应该写成如下代码：

var name string
err = db.QueryRow("select name from users where id = ?", 1).Scan(&name)
if err != nil {
if err == sql.ErrNoRows {
// there were no rows, but otherwise no error occurred
// 把空结果当做Error处理是为了强行让程序员处理结果为空的情况
} else {
log.Fatal(err)
}
}
fmt.Println(name)

https://blog.csdn.net/jesseyoung/article/details/40398321
https://studygolang.com/articles/5402

go官方仅提供了database package，database package下有两个包sql，sql/driver。这两个包用来定义操作数据库的接口，这就保证了无论使用哪种数据库，他们的操作方式都是相同的。
但go官方并没有提供连接数据库的driver，如果要操作数据库，还需要第三方的driver 包，最常用的有：
https://github.com/Go-SQL-Driver/MySQL支持database/sql，全部采用go写。
https://github.com/ziutek/mymysql 支持database/sql，也支持自定义的接口，全部采用go写。
推荐使用前者，因为前者的效率更高一点，二者效率的对比可参考benchmark测试结果：https://github.com/go-sql-driver/sql-benchmark

go连接其他主流数据库的驱动介绍可参考：
https://code.google.com/p/go-wiki/wiki/SQLDrivers

user:password@tcp(localhost:5555)/dbname?charset=utf8&autocommit=true
user:password@tcp([de:ad:be:ef::ca:fe]:80)/dbname?charset=utf8mb4

golang 的包還不支援 MySQL 8 以上的一個新特性，所以需要如下措施：
https://blog.pilipala.co/articles/2019/02/14/1550128481833

mysql 在 8.0 之后将用户使用的 plugin 更新成 caching_sha2_password。而在之前用的是 mysql_native_password。

在 MySQL 內
use mysql;
select user,host,plugin from user;

可以看到相關資訊

接著利用修改密碼的語法順便將 root 從 caching_sha2_password 改回到 mysql_native_password

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

最後需要再將 golang 內的連結語法加上一個參數 &allowNativePasswords=true

user:password@tcp([de:ad:be:ef::ca:fe]:80)/dbname?charset=utf8mb4&allowNativePasswords=true

分庫分表之後，ID 主鍵如何產生

https://blog.csdn.net/qq_42882671/article/details/86478990
https://zhuanlan.zhihu.com/p/54838983

1. 數據庫自增 id
2. UUID
3. 獲取系統當前時間：將別的業務字段值跟當前時間拼接起來，組成一個全局唯一的編號。
4. snowflake 算法

關聯式資料庫 - 分庫分表的其中一種思維參考

分库分表？如何做到永不迁移数据和避免热点？
http://database.51cto.com/art/201904/595550.htm

文中提到的小伙伴们在开发的时候不要每次都去查询三张关联表，可以保存到缓存中(本地jvm缓存)，这样不会影响性能。

以 golang 做本地缓存的參考如下：
https://blog.csdn.net/lanyang123456/article/details/86766069

group和DB的关系

table和db的关系

TiDB 使用坑记录

https://blog.csdn.net/yajie_12/article/details/82773813

1、对硬盘要求很高，没上SSD硬盘的不建议使用

2、不支持分区，删除数据是个大坑。
解决方案：set @@session.tidb_batch_delete=1;

3、插入数据太大也会报错
解决方案：set @@session.tidb_batch_insert=1;

4、删除表数据时不支持别名
delete from 表名表别名 where 表别名.col = ‘1’ 会报错

5、内存使用有问题，GO语言导致不知道回收机制什么时候运作。内存使用过多会导致TIDB当机（这点完全不像MYSQL）
测试情况是，32G内存，在10分钟后才回收一半。

6、数据写入的时候，tidb压力很大, tikv的CPU也占用很高

7、不支持GBK

8、不支持存储过程

9、列数支持太少，只支持100列，和oralce/mysql的1000列少太多（Oracle 最大列数为 1000；MySQL对于每个表具有4096个列的硬限制, 其中InnoDB每个表的限制为1017列, 最大行大小限制为65,535字节）

其他文章的一些建议

3TiKV＋3PD＋2TiDB

在有了 TiSpark 之后，我们便利用 TiSpark 将中间表缓存为 Spark 的内存表，只需要将最后的数据落地回 TiDB，再执行 Merge 操作即可，这样省掉了很多中间数据的落地，大大节省了很多脚本执行的时间

在查询速度解决之后，我们发现脚本中会有很多针对中间表 update 和 delete 的语句。目前 TiSpark 暂时不支持 update 和 delete 的操作（和 TiSpark 作者沟通，后续会考虑支持这两个操作），
我们便尝试了两种方案，一部分执行类似于 Hive，采用 insert into 一张新表的方式来解决；另外一部分，我们引入了 Spark 中的 Snappydata 作为一部分内存表存储，
在 Snappydata 中进行 update 和 delete，以达到想要的目的。因为都是 Spark 的项目，因此在融合两个项目的时候还是比较轻松的。

最后，关于实时的调度工具，目前我们是和离线调度一起进行调度，这也带来了一些问题，每次脚本都会初始化一些 Spark 参数等，这也相当耗时。在未来，我们打算采用 Spark Streaming 作为调度工具，
每次执行完成之后记录时间戳，Spark Streaming 只需监控时间戳变化即可，能够避免多次初始化的耗时，通过 Spark 监控，我们也能够清楚的看到任务的延迟和一些状态，这一部分将在未来进行测试。

TiDB 架构的演进和开发哲学

TiDB 架构的演进和开发哲学
https://segmentfault.com/a/1190000008288188

十问 TiDB ：关于架构设计的一些思考
https://juejin.im/entry/5b29bde4518825749725c28f

其实并没有什么技术是完美和包治百病的，在存储领域更是如此，如果你的数据能够在一个 MySQL 装下并且服务器的压力不大，或者对复杂查询性能要求不高，其实分布式数据库并不是一个特别好的选择。选用分布式的架构就意味着引入额外的维护成本，而且这个成本对于特别小的业务来说是不太划算的，即使你说需要高可用的能力，那 MySQL 的主从复制 + GTID 的方案可能也基本够用，这不够的话，还有最近引入的 Group Replication。而且 MySQL 的社区足够庞大，你能 Google 找到几乎一切常见问题的答案。

我们做 TiDB 的初衷并不是想要在小数据量下取代 MySQL，而是尝试去解决基于单机数据库解决不了的一些本质的问题。

有很多朋友问我选择分布式数据库的一个比较合适的时机是什么？我觉得对于每个公司或者每个业务都不太一样，我并不希望一刀切的给个普适的标准（也可能这个标准并不存在），但是有一些事件开始出现的时候：比如是当你发现你的数据库已经到了你每天开始绞尽脑汁思考数据备份迁移扩容，开始隔三差五的想着优化存储空间和复杂的慢查询，或者你开始不自觉的调研数据库中间件方案时，或者人肉在代码里面做 sharding 的时候，这时给自己提个醒，看看 TiDB 是否能够帮助你，我相信大多数时候应该是可以的。

而且另一方面，选择 TiDB 和选择 MySQL 并不是一刀切的有你没他的过程，我们为了能让 MySQL 的用户尽可能减小迁移和改造成本，做了大量的工具能让整个数据迁移和灰度上线变得平滑，甚至从 TiDB 无缝的迁移回来，而且有些小数据量的业务你仍然可以继续使用 MySQL。所以一开始如果你的业务和数据量还小，大胆放心的用 MySQL 吧，MySQL still rocks，TiDB 在未来等你。

TiDB 安裝部署

https://php.wiki/tidb

TiDB 是新一代开源分布式 NewSQL 数据库，模型受 Google Spanner / F1 论文的启发，实现了自动的水平伸缩，强一致性的分布式事务，基于 Raft 算法的多副本复制等重要 NewSQL 特性。TiDB 结合了 RDBMS 和 NoSQL 的优点，部署简单，在线弹性扩容和异步表结构变更不影响业务，真正的异地多活及自动故障恢复保障数据安全，同时兼容 MySQL 协议，使迁移使用成本降到极低。

安装部署

TiDB 官方提供了支持 Linux 版本的二进制安装包，官方推荐使用 Redhat 7+、CentOS 7+ 以上版本的操作系统，不推荐在 Redhat 6、CentOS 6 上部署 TiDB 集群。

操作系统：Linux ( Redhat 7+，CentOS 7+ )

执行步骤：

下载压缩包

wget http://download.pingcap.org/tidb-latest-linux-amd64.tar.gz
wget http://download.pingcap.org/tidb-latest-linux-amd64.sha256

检查文件完整性

，返回 ok 则正确

sha256sum -c tidb-latest-linux-amd64.sha256

解开压缩包

tar -xzf tidb-latest-linux-amd64.tar.gz
cd tidb-latest-linux-amd64

单节点方式快速部署

在获取 TiDB 二进制文件包后，我们可以在单机上面，运行和测试 TiDB 集群，请按如下步骤依次启动 PD，TiKV，TiDB。

注意：以下启动各个应用程序组件实例的时候，请选择后台启动，避免前台失效后程序自动退出。

步骤一. 启动 PD：

./bin/pd-server --data-dir=pd --log-file=pd.log

步骤二. 启动 TiKV：

./bin/tikv-server --pd="127.0.0.1:2379" --data-dir=tikv --log-file=tikv.log

步骤三. 启动 TiDB：

./bin/tidb-server --store=tikv  --path="127.0.0.1:2379"   --log-file=tidb.log

步骤四. 使用 MySQL 客户端连接 TiDB:

mysql -h 127.0.0.1 -P 4000 -u root -D test

参考链接

MySQL vs. TiDB

https://zhuanlan.zhihu.com/p/25657799

https://db-engines.com/en/system/MySQL%3BTiDB

https://opensource.com/article/18/11/key-differences-between-mysql-and-tidb

https://www.zhihu.com/question/58767602
TiDB 具备如下 NewSQL 核心特性：

SQL支持（TiDB 是 MySQL 兼容的）---->基本上没有学习成本和开发改造成本，可以实现无缝后端存储迁移，这一点对开发哥来说太太太重要了。

水平线性弹性扩展 ---->无限的计算能力，无限的存储能力，虽然是理论上的，但是不觉得很牛逼的样子吗？

分布式事务 -->适配OLTP场景

跨数据中心数据强一致性保证 ---->某些场景下很有用

故障自恢复的高可用 ---->永远挂不了的系统，是不是很酷？

TiDB 的设计目标是 100% 的 OLTP 场景和 80% 的 OLAP 场景。

没有学习成本和接入成本，不需要运维，随意扩展，支持事务，原生高可用，mysql遇到的问题在tidb里基本都解决了。

TiDB 在小米的应用实践
https://zhuanlan.zhihu.com/p/51472404

目前 TiDB 主要应用在：

小米手机桌面负一屏的快递业务
商业广告交易平台素材抽审平台

这两个业务场景每天读写量均达到上亿级，上线之后，整个服务稳定运行；接下来我们计划逐步上线更多的业务场景，小米阅读目前正在积极的针对订单系统做迁移测试。

TiDB 结合了传统的 RDBMS 和 NoSQL 的最佳特性，兼容 MySQL 协议，支持无限的水平扩展，具备强一致性和高可用性。

具有如下的特性：

高度兼容 MySQL，大多数情况下无需修改代码即可从 MySQL 轻松迁移至 TiDB，即使已经分库分表的 MySQL 集群亦可通过 TiDB 提供的迁移工具进行实时迁移。

水平弹性扩展，通过简单地增加新节点即可实现 TiDB 的水平扩展，按需扩展吞吐或存储，轻松应对高并发、海量数据场景。

分布式事务，TiDB 100% 支持标准的 ACID 事务。

真正金融级高可用，相比于传统主从（M-S）复制方案，基于 Raft 的多数派选举协议可以提供金融级的 100% 数据强一致性保证，且在不丢失大多数副本的前提下，可以实现故障的自动恢复（auto-failover），无需人工介入。

TiDB 的架构及原理官网有详细介绍（https://pingcap.com/）。

TiDB 基础架构图

跟绝大数互联网公司一样，小米关系型存储数据库首选 MySQL，单机 2.6T 磁盘。由于小米手机销量的快速上升和 MIUI 负一屏用户量的快速增加，导致负一屏快递业务数据的数据量增长非常快，每天的读写量级均分别达到上亿级别，数据快速增长导致单机出现瓶颈，比如性能明显下降、可用存储空间不断降低、大表 DDL 无法执行等，不得不面临数据库扩展的问题。比如，我们有一个业务场景（智能终端），需要定时从几千万级的智能终端高频的向数据库写入各种监控及采集数据，MySQL 基于 Binlog 的单线程复制模式，很容易造成从库延迟，并且堆积越来越严重。

对于 MySQL 来讲，最直接的方案就是采用分库分表的水平扩展方式，综合来看并不是最优的方案，比如对于业务来讲，对业务代码的侵入性较大；对于 DBA 来讲提升管理成本，后续需要不断的拆分扩容，即使有中间件也有一定的局限性。同样是上面的智能终端业务场景，从业务需求看，需要从多个业务维度进行查询，并且业务维度可能随时进行扩展，分表的方案基本不能满足业务的需求。

了解到 TiDB 特点之后，DBA 与业务开发沟通确认当前 MySQL 的使用方式，并与 TiDB 的兼容性做了详细对比，经过业务压测之后，根据压测的结果，决定尝试将数据存储从 MySQL 迁移到 TiDB。经过几个月的线上考验，TiDB 的表现达到预期。

兼容性对比

TiDB 支持包括跨行事务、JOIN、子查询在内的绝大多数 MySQL 的语法，可以直接使用 MySQL 客户端连接；对于已用 MySQL 的业务来讲，基本可以无缝切换到 TiDB。
二者简单对比如下几方面：

1. 功能支持

TiDB 尚不支持如下几项：

增加、删除主键
非 UTF8 字符集
视图（即将支持）、存储过程、触发器、部分内置函数
Event
全文索引、空间索引

2. 默认设置

字符集、排序规则、sql_mode、lower_case_table_names 几项默认值不同。

3. 事务

TiDB 使用乐观事务模型，提交后注意检查返回值。
TiDB 限制单个事务大小，保持事务尽可能的小。

4. TiDB 支持绝大多数的 Online DDL。

5. 另，一些 MySQL 语法在 TiDB 中可以解析通过，不会产生任何作用，例如： create table 语句中 engine、partition 选项都是在解析后忽略。

6. 详细信息可以访问官网：https://pingcap.com/docs-cn/sql/mysql-compatibility/ 。

關於 LevelDB

https://juejin.im/post/5c22e049e51d45206d12568e

它是 Google 开源的 NOSQL 存储引擎库，是现代分布式存储领域的一枚原子弹。在它的基础之上，Facebook 开发出了另一个 NOSQL 存储引擎库 RocksDB，沿用了 LevelDB 的先进技术架构的同时还解决了 LevelDB 的一些短板。你可以将 RocksDB 比喻成氢弹，它比 LevelDB 的威力更大一些。现代开源市场上有很多数据库都在使用 RocksDB 作为底层存储引擎，比如大名鼎鼎的 TiDB。

LevelDB vs. MySQL

https://db-engines.com/en/system/LevelDB%3BMySQL

MySQL Replication 主從式架構

https://blog.toright.com/posts/5062/

資料庫可以說是應用程式的核心，但關聯式資料庫 (RDBMS) 基於 ACID 設計原則，傳統架構導致在運算能力的擴展上，變得不容易實現。擴充上除了典型的 Scale-up (擴充單一硬體能力) 之外，最常使用的就是 Replication 技術，Replication 泛指資料複製模式，會把資料完整的複製到另一個 MySQL 節點上，這樣的方式並不是分散儲存喔。

目前常使用且成熟的關聯式資料庫，一般官方都會提供 Replication 機制，通常官方內建的 Replication 機制已經很成熟，不像幾年前 Open Source RDBMS 都要自己編譯第三方 Replication 功能，當然也有第三方像是 Percona 這樣的套件。透過 Replication 將資料同步複製到其他主機，可以實現運算的擴充與高可用架構。大部分的資料庫應用情境都是 SELECT 居多，最常用到的就是讀寫分離的「主從架構」，如下圖所示：

主要的 MySql Master 節點負責「讀寫」，寫資料會透過 Binlog Relay 機制，以非同步模式複製 Binlog 到每一台 Slave 執行 (正確一點的說法應該是 Slave 透過 REPLICATION 權限連線到 Master 去抓 Binlog 回來執行)，運作時就好像讓 Slave 與 Master 進行資料同步。一般來說，Slave 不提供寫入功能，只負責分散 SELECT 運算，這也是最典型的擴充架構。實作時有一個地方很麻煩，就是應用程式必須自行實作「讀寫分離」的機制 (有些 Framework ORM 會內建)，自行再程式邏輯將 SELECT Query 分散到 Slave 執行，除非有使用商業的 Proxy Connection Pool 機制，才有提供自動讀寫分離的機制。如果是使用 MariaDB 的話也可以搭配 MaxScale 實現讀寫分離機制，但 MaxScale 我沒玩過，只是打打嘴砲。

此外，建構 Replication 還有一個很重要的好處，就是可以順便備份！一般的情況我們都是用 mysqldump 進行資料備份對吧！？但是如果你的資料已經大到一定的程度，想要 Dump DB 絕對是 GG，搞不好跑個日備份就超過一天了....... 這時候就要借助 Replication 的能力。

透過 HAProxy 實現 MySQL 主備自動切換

上述說明的主從架構只能分散運算，並無法實現 HA 能力，如果採用 Master/Master 的配置方式就可以實現了，但是應用程式要如何判斷其他一台機器出問題呢？我們可以藉由 HAProxy 這個套件來實現。將其中一台機器上線，另一台基器備援 (Replication)，當第一台機器發生問題時，自動由 HAProxy 轉接連線至備援機，以實現 HA 模式。HAProxy 的設定方式在 GitHub 上：https://github.com/samejack/blog-content/blob/master/mysql-replication/haproxy.cfg

MySQL 的日常管理

status;
show create database 資料庫名稱 ;
show create table 資料表名稱 ;
show table status ( from 資料庫名稱 ) ;
show columns from 資料表名稱 ;

select version();
show variables like 'have%ssl%';
show variables like 'port';
show variables like 'datadir';
sudo find / -name my.cnf
sudo mysql --help | grep my.cnf

檢視當前的工作狀態
mysql> show processlist
mysql> show full processlist

如果需要刪掉某個工作
mysql> kill 該工作的Id ;

檢視當前配置的最大連線數、當前使用過的最大連線數。
mysql> show variables like "max_c%";
mysql> show global status like "Max_%";

最大連線數設定為：使用過最大連線數/配置最大連線數≈85% 為比較合適，但使用過的最大連線數都受到配置最大連線數的應用，所以在配置最大連線數過少的情況下，儘可能的先配置大一點的連線數，過段時間獲取使用過的最大連線數後，再設定最優的最大連線數。

mysql> show variables like "%timeout%";

http://jeremy.zawodny.com/blog/archives/011421.html
無論如何都必須修改的四個 MySQL 預設值
max_connect_errors = 1844674407370954751
(並 running an occasional FLUSH HOSTS command)
connect_timeout = 20
skip-name-resolve

設置 max_connect_errors 的目的是預防暴力破解密碼，
所以是否應該如上建議的修改調高這個預設值，其實仍得端視實際應用場景的整體規劃，
比如防火牆是否有開放外部連結的埠號等等，
同理，調整每個預設值之前，應仔細思考實際應用場景的特殊需求，整體規劃考量。

mysql>
set GLOBAL max_connect_errors=1844674407370954751;
set GLOBAL max_connections=5000;
set GLOBAL connect_timeout=20;
set wait_timeout=600;
set interactive_timeout=600;

wait_timeout 預設值為 28800（8小時）
太長了會吃掉 mysql 的連線數

wait_timeout 和 interactive_timeout
這兩個參數要同時設定才會生效

mysql> show status
Show Status官網詳細文件：
https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Aborted_clients

MySQL 加密傳輸

以 OpenSSL 實作 MySQL 加密傳輸：
http://www.netadmin.com.tw/article_content.aspx?sn=1609080004

Linux 使用 Let’s Encrypt 實現 SSL：
https://teddysun.com/454.html

https://teddysun.com/517.html

MySQL 5.7 啟用 SSL 之實作與評估： https://tw.saowen.com/a/74b5871c8e4e380b9dbec9b7698a77339a9bfea6d4fe0770982dceedb92d995f

雖然 SSL方式使得安全性提高了，但是卻相對使得 MySQL 5.7 Queries per second (QPS) 降低 23% 左右，所以：

1、對於非常敏感核心的資料，或者 QPS 本來就不高的核心資料，建議採用 SSL方式保障資料安全。

2、對於採用短連結、要求高效能的應用，或者不產生核心敏感資料的應用，當效能和可用性才是首要時，建議不要採用SSL方式。

訂閱：文章 (Atom)