Database

2022/04/11 (更新投影片及內容)
2022/05/15 (新增連結)
2022/05/31 (新增連結)
2022/06/18 (新增連結)
2024/02/26 (補充資料)
2024/06/26 (整理資料)
2025/03/24 (更新資料)
2025/03/28 (更新資料)

選擇資料庫

關聯式資料庫是目前在商業上最被廣泛應用的資料庫，可是，隨著資料量及使用人數的大幅增加，關聯式資料庫在某些情況下就會成為瓶頸，這時候非關聯式資料庫就派上用場了，但是，非關聯式資料庫也是有其限制，所以，一般而言，會將兩種資料庫各取所長混合使用。

• 聊聊 | 如何選擇合適的資料庫 (deeplink學長姐分享)

• 資料庫，你真的用對了嗎？—— SQL vs. NoSQL (2023)

  • SQL 適用情況：

    • 資料格式明確，未來不會大幅的變動

    • 資料之間的關聯很重要

    • 想要更有效率的讀取資料

    • 未來會大量使用到 JOIN 的功能

    • 更著重在資料操作的準確性與一致性 (ACID)

  • NoSQL 適用情況：

    • 想快速啟動小專案進行 POC

    • 資料格式未來很有可能調整

    • 資料之間沒有複雜的關聯、跨區統計需求較少

    • 大用量的查找需求

• Explaining SQL and NoSQL, to Grandma (member-only)

  • Schema

    • MySQL requires a defined and structured schema.

    • NoSQL allows the persistence of any data in the “document.”

  • Community

    • MySQL has a huge community supporting it.

    • NoSQL has a small and rapidly growing community.

  • Scalability

    • NoSQL features easy scalability.

    • MySQL needs more managed scalability.

  • SQL

    • MySQL utilizes SQL, which gets used in a multitude of database types.

    • NoSQL is a design-based database with popular implementations.

    • MySQL employs a structured query language (SQL).

    • NoSQL uses no structured query language.

  • Reporting tools

    • MySQL has many fantastic reporting tools.

    • NoSQL features few reporting tools that are difficult to standardize.

  • Performance

    • MySQL can offer performance problems for big data.

    • NoSQL delivers excellent performance on big data.

• 5 Database Scaling Solutions You Need to Know 

  • Cache Database Queries

  • Database Indexes

  • Session Storage

  • Master Slave Replication

  • Database Sharding

根據Stackoverflow 2024 Developer Survey，Database的調查結果，使用率前幾名 (firebase已經掉出前10名了):

• PostgreSQL 48.7%

• MySQL 40.3%

• SQLite 33.1%

• Microsoft SQL Server 25.3%

• MongoDB 24.8% (NoSQL)

• Redis 20% (NoSQL)

• Maria DB 17.2%

• Elasticsearch 12.5% (NoSQL)

• Oracle 10.1%

• DynamoDB 7.9% (NoSQL)

根據Stackoverflow 2023 Developer Survey，Database的調查結果，使用率前幾名 (firebase已經掉出前10名了):

• PostgreSQL 45.55%

• MySQL 41.09%

• SQLite 30.9%

• MongoDB 25.52% (NoSQL)

• Microsoft SQL Server 25.45%

• Redis 20.41% (NoSQL)

• Maria DB 17.61%

• Elasticsearch 13.39% (NoSQL)

• Oracle 9.8%

• DynamoDB 8.87% (NoSQL)

根據Stackoverflow 2021的Most popular technologies 調查結果，使用率前幾名

• MySQL 50.18%

• PostgreSQL 40.42%

• SQLite 32.18%

• MongoDB 27.7% (NoSQL)

• Microsoft SQL Server 26.87%

• Redis 20.69% (NoSQL)

• Maria DB 17.19%

• Firebase 16.17% (NoSQL)

• Elasticsearch 13.27% (NoSQL)

• Oracle 12.61%

• DynamoDB 7.3% (NoSQL)

• Cassandra 2.66% (NoSQL)

• IBM DB2 2.04%

• Couchbase 1.57% (NoSQL)

Relational database

比較常用的有Oracle database, SQL server, MySQL。

資料表設計原則

• 資料表和欄位的命名要一致且有意義

• 資料表名稱用單數

• 資料表名稱不要用空白，因為sql是不分大小寫的，如果是兩個字，習慣上以「_」分隔，如「Student_Course」

• 資料表名稱不要加不必要的前綴詞（prefix）或後綴詞（suffix），使用「Student_Course」而非「Student_Course_Table」

• 對於查詢比較頻繁的大資料表，要使用索引。可以使用分析工具來決定哪裡該使用索引。對於大範圍的資料記錄擷取，通常叢集索引比較好。對於單點查詢，通常非叢集索引比較好。

  • How Database Indexing Actually Works (hash index)

    • A database index is a good way to boost read queries.

    • Indexing a column is necessary for a uniqueness constraint. (只有primary key才會有這樣的限制，雖然primary key是一種index)

    • With every new index, more memory is going to be consumed.

    • Adding an index has an impact on operations for writing and updating.

  • What makes a good MySQL index? Part 1: Column Size 

  • What makes a good MySQL index? Part 2: Cardinality 

• 有必要就使用正規化來對效能最佳化，正規化不足會導致資料的重複，正規化太多會導致join太多的資料表，兩者都會引起效能低落。

  • 所有資料表最好是最適之正規化設計 (如：第三正規化)，如果沒有，也應該有解釋

• 資料表之間無不合理的關連（如：沒必要的一對一關連或多對多關連）

欄位設計原則

• 欄位的命名要一致且有意義

• 除非為了避免混淆 (例如:資料表裡有student_id及teacher_id)，欄位的命名可以不需要再加上資料表的名稱，如: age，而不是student_age

• 欄位型態要合理

  • 可以使用整數型別的id欄位。即使當前不需要id，以後也有可能會用到（例如在合併資料表或做索引的時候）

  • 選擇整數資料型別的欄位（或其變體）來做索引，資料量會比較小，以現在的索引技術，其實存取速度差異不大，速度的差異在於大量批次新增。

    • RDB主鍵(PK)的設計策略

  • 選擇bit欄位給布林值使用。整數或varchar沒有必要，而且也浪費儲存空間。另外，替這些欄位取名為”Is”…什麼的（例如IsTeacher來代表是或否）

  • 日期要使用日期型態而非varchar

  • 如果文字長度固定 (如:電話號碼)就不需要使用varchar。

  • Chapter 4. Optimizing Schema and Data Types 

• 欄位長度要合理，例如，電話不用varchar(255)

• 密碼要加密以維護安全。有必要才在應用程式端解密

• 要設定Primary Key (PK)

  • RDB主鍵(PK)的設計策略

• 盡量利用Foreign Key (FK)來維護資料的一致性

SQL

• 除非真的必要，否則避免使用”select *”這樣的查詢語句，使用”select [必要的欄位表列]”這樣效能比較好

• 避免使用distinct

  • SQL Like a Pro: Please Stop Using Distinct!! 

• 避免使用offset

  • You Are Doing SQL Pagination Wrong 

NoSQL

NoSQL (non SQL, non relational 或 not only SQL)不是一個產品，而是個概念，泛指不支援SQL的資料庫或非關聯性資料庫，這樣的資料庫通常不需要是先定義的資料欄位 (詳參: NoSQL Database Doesn’t Mean No Schema / 以MongoDB為例, Firebase Data Modeling, Structuring your Firebase Data correctly for a Complex App) 。

• How To Handle One-to-Many Relationships in NoSQL Databases 

  • Denormalization + Complex Attribute

  • Denormalizaton + Duplication

  • Composite Primary Key + Query

推薦的NoSQL是google的子公司firebase開發的firestore。

• Firebase (吳濟聰老師行動裝置程式設計課程教材)

  • 利用javascript去存取雲端上firestore的資料

  • 利用react/react native去存取雲端上firestore的資料

除了firestore之外，還有很多個種類的NoSQL (詳參:https://en.wikipedia.org/wiki/NoSQL)，所以，每一種NoSQL的用法都不太一樣。比較常見的有: MongoDB, Cassandra, Redis, HBase, Neo4j (詳參: Top NoSQL Database Engines 2016年資料)。 

• Key-Value

  • Redis 是一個in-memory的key value資料庫

    • 資料庫的好夥伴：Redis

  • Amazon DynamoDB (詳參: Amazon DynamoDB 產品詳細資訊 、DynamoDB Core Components) 

  • firebase realtime database (JSON Object) (詳參:Structure Your Database) 

• Document Store

  • Google Cloud Firestore (資料以文件的形式儲存，以集合的方式組織) (詳參:  Cloud Firestore Data Model)

    • Firebase Cloud Firestore 筆記

      • Cloud Firestore 增加了 Collection 概念。資料庫的 Root 為多個 Collection，每個 Collection 可裝載多個 Document，每個 Document 實體中也可包含 Collection

      • Cloud Firestore 支援了更多資料格式，比如：object, array, geopoint, reference。其中 reference 可指定到特定節點的 Document 實體

      • Cloud Firestore 可自定義 index，支援 multi-key index。可以簡易做到混合(compound)的排序(sorting)和條件過濾(filtering)

    • Get started with Cloud Firestore

      • Web (javascript)

      • iOS

      • Android Java

  • MongoDB

    • MongoDB 基礎入門教學：MongoDB Shell 篇

• Object database (也常被認為是document store)

  • Google datastore (已經改為 Cloud Firestore in Datastore mode)

    • 以下內容不再適用

    • 提供Kind，可以把Kind想成是Table或Class，每筆資料(entity)都屬於一個Kind，每筆資料都有一個key(系統產生的唯一值，不是RDB的primary key)，每筆資料都有一些欄位 (property)，每個欄位可以包括其他欄位，也可以有多個值。

• Wide Column Store

  • Cassandra

  • HBase

  • Amazon DynamoDB 還是有table及primary key的概念，但是除了primary key之外，table裡的每筆資料 (item)的欄位(attribute)不固定，欄位裡可以包括其他欄位，欄位裡的值也可以有多個值 。也可以像RDB一樣建立索引 (詳參: Amazon DynamoDB 產品詳細資訊 、DynamoDB Core Components) 。

• Graph Database

  • Neo4j

參考資料

Database

• Best Practices: How To Design a Database

• 10 Best Database Design Practices

• Interview Questions: How to improve the performance of your database 

• Interview Questions: System design solutions to improve database performance 

RDBMS

• 設計資料庫的20個最佳實踐 (原文: 20 Database Design Best Practices)

SQL

• Data science lesson 1: making a table using SQL

• Data science lesson 2: selecting data using SQL

• Data science lesson 3: filtering data using SQL

• Data science lesson 4: Grouping and aggregating data using SQL

• How to Optimize SQL Queries

• How to Optimize SQL Queries Part II

• Why You Shouldn’t Use OFFSET and LIMIT For Your Pagination

  • The higher your OFFSET, the longer the query will take.

  • Instead of storing current OFFSET and LIMIT locally and passing it with each request, you should be storing the last received primary key (usually an ID) and the LIMIT, so the query could end up being similar to this one.

• Take your SQL from Good to Great: Part 1 : WITH CTEs, you can go far.

  • Common Table Expressions

• Take your SQL from Good to Great: Part 2 : Dates & Times in SQL

• Take your SQL from Good to Great: Part 3 : Time to join the JOIN movement

• 12 Best Practices for Increasing the Database Performance 

• A Fundamental Guide to SQL Query Optimization 

• The 6 Steps of a SQL Select Statement Process 

  • Getting Data (From, Join)

  • Row Filter (Where)

  • Grouping (Group by)

  • Group Filter (Having)

  • Return Expressions (Select)

  • Order & Paging (Order by & Limit / Offset)

• 5 SQL tips to make your queries prettier and easier to read 

  • Be consistent with casing

  • Use indentation

  • Use numbers in your group by and order by clauses

  • Use Common Table Expressions

  • Use aliases, be descriptive

• Learning SQL 201: Optimizing Queries, Regardless of Platform

  • Query plans

  • Indexes and hinting

  • Disk writes

  • Subqueries

  • Temp tables

  • Network

  • CPU usage and algorithms

  • “Overhead”

  • Contention

  • $$$

• Five Best Practices for Writing Clean and Professional SQL Code 

• How To Avoid Writing Sloppy SQL 

  • SQL Style Guide 

MySQL / Maria DB

MySQL是一個開源的資料庫，原本是由MySQL AB開發，2008年被Sun收購，2009年Oracle收購Sun時，就變成Oracle的產品了，ˊ2022年4月的MySQL最新版本是8.0.28 (下載: MySQL Community Downloads)(詳參:MySQL)。

MariaDB是MySQL的一個分支，在2013年由MySQL的創始人Ulf Michael Widenius與其他人一起成立了MaribDB Foundation來主導MariaDB的開發。在MariaDB 5.5之前，MariaDB的版本都與MySQL同步，之後，MariaDB的版本號就由10.0開始(2012年)，也就與MySQL版本不再同步。2022年4月的最新穩定版本是10.6.7 (下載: Downloads) (詳參: MariaDB) **雖然有10.7.3，但是，是屬於Short Term Release，建議使用10.6.7**。 

• Databases: Explaining Data Normalization, Data Anomalies and DBMS Keys 

• Top five software applications to access MySQL/MariaDB servers

  • SQLyog

  • MySQL Workbench (Free, Windows, Linux, Mac) (網友說跟MariaDB 10.x不相容)

  • HeidiSQL (Free, only Windows versions)

  • Navicat for MySQL(Support MariaDB)

  • dbForge Studio for MySQL (Free, only Windows versions)

• 15 Best MySQL GUI Tools Roundup

• 20+ Best MySQL GUI Tools 2018

• Best Free GUI Tools for MySQL for live Environments

NoSQL

• 了解NoSQL不可不知的5項觀念

• 快速認識4類主流NoSQL資料庫

  • Key-Value Database、In-memory Database、Graph Database、Document Database。

• DB engine ranking

• NOSQL DATA MODELING TECHNIQUES

• 將 NoSQL 資料庫的文件資料模型化 (Azure Cosmos)

• 利用Class Diagram說明NoSQL的資料庫設計

  • NoSQL Database Design Using UML Conceptual Data Model Based on Peter Chen’s Framework

• 利用JSON Schema說明NoSQL的資料庫設計

  • JSON Schema