資料模型(Data Modeling)

設計 => ER models, 正規化, Primary Key, 時區, Index 一致性 => ACID, Race condition, isolation level Backup Migration

Query
- SQL queries don't start with SELECT
- Most memorable graphic Figure 4-3. SELECT phases from @Apress "The Definitive Guide to SQLite"
- SQL Lesson 12: Order of execution of a Query
- [MySQL / MariaDB] 優化查詢語句OFFSET越大時間越久的問題
- 《Effective SQL》讀後分享 | Yuanchieh's Blog
- How to Calculate the Difference Between Two Rows in SQL
- :star:The Best Medium-Hard Data Analyst SQL Interview Questions
- mysql - Select from same table as an Insert or Update - Stack Overflow
- Mysql inconsistent number of rows count(*) vs table.table_rows in information_schema - Stack Overflow
  - MySQL :: MySQL 8.0 Reference Manual :: 15.23 InnoDB Restrictions and Limitations
    - SHOW TABLE STATUS does not provide accurate statistics for InnoDB tables except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization.
    - InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction.
    - MySQL :: MySQL 8.0 Reference Manual :: 12.20.1 Aggregate Function Descriptions
- Is offset pagination dead? Why cursor pagination is taking over | by Megan C. | UX Collective
Query(Advence)
- SQL 窗口函數 Window Function：三大應用快速教學 • 好豪筆記
- Transformation layer: SQL 進階語法 - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天
ACID
- :star:資料庫層的難題 - 單機『故障』一致性難題
  - 天災人禍(故障與併發)的生存之道
  - ADI => C
- :star:select...for update再insert造成deadlock的陷阱
- 如何理解資料庫中一致性的概念
- Transaction 筆記
- 資料庫高併發的第一步 — 事務隔離
- Lock
Design(Practice)
Design(Basic)
Design(Advance)
Migration
Error
- mysql - Temporary table is full - Database Administrators Stack Exchange
Microsoft 資料架構指南
Race Condition
安全
- How SQL Injection attack works
交換
- What is the Parquet File Format and Why You Should Use It
工具
- dbdiagram 筆記 - HackMD
High Availability
其他

設計

需求 - 商業 - 資料量級 - 安全

收集與梳理資訊 - 現場使用的人 - 現有表單(invoices, timesheets, surveys) - 現有資料系統

what kinds of data the database will include where that data comes from how it will be used

ERD Primary Key Cardinality

Index B+ Tree => 只有最下面的節點有資料，其它上面的節點只存索引

Clustered Index => 在innodb中, 它就是這份資料實際上儲存的結構(B+ Tree)

Secondary Index => 可以自行建立的索引, 最基本使用 secondary Index 的步驟, 至 secondary Index 尋找 PK, 再至 clustered Index 取得完整資料

看看候選欄位的基數大小, 基數就是指這個欄位的值的可能性

SELECT COUNT(DISTINCT {field}/COUNT(*))

一般索引(覆蓋索引) 連合索引 : 常常需要與其它欄位一起查詢，適合建立。但是要注意先後順序，基本最常查詢的與基數高的適合放最左邊前綴索引 : 這種大致上用在如果要針對某段文字進行前綴搜尋的情況

資料模型(Data Modeling)

設計

Keyword search

Redis

ElasticSearch

Hadoop ecosystem

Dbeaver