--- title: 資料模型(Data Modeling) tags: 軟體開發, 資料庫, 設計 --- # 資料模型(Data Modeling) 設計 => ER models, 正規化, Primary Key, 時區, Index 一致性 => ACID, Race condition, isolation level Backup Migration - Query - [SQL queries don't start with SELECT](https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with-select/) - [Most memorable graphic Figure 4-3. SELECT phases from @Apress "The Definitive Guide to SQLite"](https://twitter.com/ThougthsAsmbly/status/1179456440009986048/photo/1) - ![The Definitive Guide to SQLite](https://www.dropbox.com/s/0wdb796snvd9f52/The%20Definitive%20Guide%20to%20SQLite%20Figure%204-3%20SELECT%20phases.png?dl=1) - [SQL Lesson 12: Order of execution of a Query](https://sqlbolt.com/lesson/select_queries_order_of_execution) - [[MySQL / MariaDB] 優化查詢語句OFFSET越大時間越久的問題](https://notes.andywu.tw/2021/mysql-mariadb-%e5%84%aa%e5%8c%96%e6%9f%a5%e8%a9%a2%e8%aa%9e%e5%8f%a5offset%e8%b6%8a%e5%a4%a7%e6%99%82%e9%96%93%e8%b6%8a%e4%b9%85%e7%9a%84%e5%95%8f%e9%a1%8c/) - [《Effective SQL》讀後分享 | Yuanchieh's Blog](https://yuanchieh.page/posts/2021/2021-11-14-effective-sql%E8%AE%80%E5%BE%8C%E5%88%86%E4%BA%AB/) - [How to Calculate the Difference Between Two Rows in SQL](https://learnsql.com/blog/difference-between-two-rows-in-sql/) - :star:[The Best Medium-Hard Data Analyst SQL Interview Questions](https://quip.com/2gwZArKuWk7W) - [mysql - Select from same table as an Insert or Update - Stack Overflow](https://stackoverflow.com/questions/205190/select-from-same-table-as-an-insert-or-update) - [Mysql inconsistent number of rows count(\*) vs table.table\_rows in information\_schema - Stack Overflow](https://stackoverflow.com/questions/34003036/mysql-inconsistent-number-of-rows-count-vs-table-table-rows-in-information-sc) - [MySQL :: MySQL 8.0 Reference Manual :: 15.23 InnoDB Restrictions and Limitations](https://dev.mysql.com/doc/refman/8.0/en/innodb-restrictions-limitations.html) - > 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](https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_count) - [Is offset pagination dead? Why cursor pagination is taking over | by Megan C. | UX Collective](https://uxdesign.cc/why-facebook-says-cursor-pagination-is-the-greatest-d6b98d86b6c0) - Query(Advence) - [SQL 窗口函數 Window Function：三大應用快速教學 • 好豪筆記](https://haosquare.com/sql-window-function-intro/) - [Transformation layer: SQL 進階語法 - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天](https://ithelp.ithome.com.tw/articles/10335150) - ACID - :star:[資料庫層的難題 - 單機『 故障 』一致性難題](https://ithelp.ithome.com.tw/articles/10228238) - 天災人禍(故障與併發)的生存之道 - ADI => C - :star:[select...for update再insert造成deadlock的陷阱](https://notes.andywu.tw/2021/select-for-update%E5%86%8Dinsert%E9%80%A0%E6%88%90deadlock%E7%9A%84%E9%99%B7%E9%98%B1/) - [如何理解資料庫中一致性的概念](https://www.zhihu.com/question/31346392) - [Transaction 筆記](https://kkc.github.io/2017/10/08/transaction-note/) - [資料庫高併發的第一步 — 事務隔離](https://medium.com/johnliu-%E7%9A%84%E8%BB%9F%E9%AB%94%E5%B7%A5%E7%A8%8B%E6%80%9D%E7%B6%AD/%E8%B3%87%E6%96%99%E5%BA%AB%E9%AB%98%E4%BD%B5%E7%99%BC%E7%9A%84%E7%AC%AC%E4%B8%80%E6%AD%A5-%E4%BA%8B%E5%8B%99%E9%9A%94%E9%9B%A2-717f61b8254a) - Lock - :star: [淺談MySQL與PostgreSQL是如何處理併發比較 - AndyWu's Notes](https://notes.andywu.tw/2022/%e6%b7%ba%e8%ab%87mysql%e8%88%87postgresql%e6%98%af%e5%a6%82%e4%bd%95%e8%99%95%e7%90%86%e4%bd%b5%e7%99%bc%e6%af%94%e8%bc%83/) - [【MySQL】Lock 與 Index 關係和 Deadlock 分析 | Yuanchieh's Blog](https://yuanchieh.page/posts/2022/2022-04-25-mysqllock-%E8%88%87-index-%E9%97%9C%E4%BF%82%E5%92%8C-deadlock-%E5%88%86%E6%9E%90/) - [鎖( Lock )的介紹與死鎖分析. 討論 mysql 隔離級別實現時，常以鎖的類型 （ 讀鎖/寫鎖 ）| by Team PHP Laravel | TWJOIN 哲煜科技](https://blog.twjoin.com/%E9%8E%96-lock-%E7%9A%84%E4%BB%8B%E7%B4%B9%E8%88%87%E6%AD%BB%E9%8E%96%E5%88%86%E6%9E%90-19833c18baab) - Design(Practice) - :star:[Modeling Game Player Data with Amazon DynamoDB](https://amazon-dynamodb-labs.com/game-player-data.html) - :star:[Database Design in Practical Examples](https://www.vertabelo.com/blog/example_models/) - :star:[Database schema templates](https://drawsql.app/templates) - :star:[Redis Best Practices – Introduction and patterns](https://redis.com/redis-best-practices/introduction/) - [Database Design of a Restaurant Management System from user story to relational Model](https://dev.to/lontchi12/database-design-of-a-restaurant-management-system-from-user-story-to-relational-model-2jp1) - [Entity Relationship Modeling Examples](https://www.oreilly.com/library/view/learning-mysql/0596008643/ch04s04.html) - Design(Basic) - :star:[Database Structure and Design Tutorial](https://www.lucidchart.com/pages/database-diagram/database-design) - [11 important database designing rules which I follow](https://www.codeproject.com/Articles/359654/11-important-database-designing-rules-which-I-fo-2) - [Get started with SQL: Plan and design a database](https://www.oreilly.com/content/get-started-with-sql-plan-and-design-a-database/) - [A Quick-Start Tutorial on Relational Database Design](https://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html) - [Data Modeling 101](http://www.agiledata.org/essays/dataModeling101.html) - [Relational Database Schema Design Overview](https://medium.com/@kimtnguyen/relational-database-schema-design-overview-70e447ff66f9) - [Pros and Cons of Database Normalization](https://morpheusdata.com/blog/2015-02-17-pros-cons-db-normalization) - [The Process of Database Refactoring: Strategies for Improving Database Quality](http://www.agiledata.org/essays/databaseRefactoring.html) - [Introduction | The Analytics Setup Guidebook](https://www.holistics.io/books/setup-analytics/start-here-introduction/) - :star:[【產品】使用 ER 圖編寫清晰的軟體規格｜someone.tw | blog.someone.tw](https://medium.someone.tw/entity-relationship-diagram-bb2bbd777b6e) - [Stack Overflow Data | Kaggle](https://www.kaggle.com/datasets/stackoverflow/stackoverflow) - 正規化 - :star:[淺談資料庫正規化](https://notes.andywu.tw/2018/%e6%b7%ba%e8%ab%87%e8%b3%87%e6%96%99%e5%ba%ab%e6%ad%a3%e8%a6%8f%e5%8c%96/) - 例子舉得很棒! - :star:[Good RDB Design with the Concept of Normal Forms](https://tigercosmos.xyz/post/2018/10/let-build-dbms/16) - 誰是老大, 拆團 - [30-17之MongoDB的設計---正規與反正規化的戰爭](https://mark-lin.com/posts/20160917/) - MySQL - :star:[MySQL開發實戰](https://docs.google.com/presentation/d/1OzMzlh5hw2w7hzYAAbdncn6dWNxuvbhkxC9FG7t5Uog/edit#slide=id.g458f537d78_3_50) - [關於時區(一)](https://medium.com/@justinlee_78563/%E9%97%9C%E6%96%BC%E6%99%82%E5%8D%80-4bfada7bd23d) - [關於時區(二)](https://medium.com/@justinlee_78563/%E9%97%9C%E6%96%BC%E6%99%82%E5%8D%80-%E4%BA%8C-c29b879117fa) - [資料庫層的優化 - 表的設計](https://ithelp.ithome.com.tw/articles/10222452) - [淺談MySQL表結構設計](https://zhuanlan.zhihu.com/p/32382274) - [MySQL DB Schema 設計原則](https://blog.johnsonlu.org/mysql-db-schema-%e8%a8%ad%e8%a8%88%e5%8e%9f%e5%89%87/) - Guide - [SQL樣式指南 · SQL Style Guide](https://www.sqlstyle.guide/zh-tw/) - [Enterprise Data Models](http://www.databaseanswers.org/data_models/index.htm) - Design(Advance) - [恰如其分的 MySQL 設計技巧 - Modern Web 2016](https://www.slideshare.net/yftzeng/mysql-modern-web-2016) - [資料庫索引數據結構及主鍵設計](https://www.slideshare.net/yftzeng/btreepart-1) - fulltext search - [用mysql fulltext search建立簡易搜尋引擎](https://changhengliou.medium.com/%E7%94%A8mysql-fulltext-search%E5%BB%BA%E7%AB%8B%E7%B0%A1%E6%98%93%E6%90%9C%E5%B0%8B%E5%BC%95%E6%93%8E-80659c28ec19) - [Performance of LIKE queries on multmillion row tables, MySQL](https://stackoverflow.com/questions/11418932/performance-of-like-queries-on-multmillion-row-tables-mysql) - [What is Full Text Search vs LIKE](https://stackoverflow.com/questions/224714/what-is-full-text-search-vs-like) - [MySQL FULLTEXT query issue](https://stackoverflow.com/questions/49029656/mysql-fulltext-query-issue) - [Elastic search full text vs mysql full text?](https://stackoverflow.com/questions/41892179/elastic-search-full-text-vs-mysql-full-text) - partition - [MySQL Partitioning 優化之路. 17 APP 上線至今已經好幾年了，隨著時間過去 DB… | by Jiazhen | 17LIVE Tech Insight | Medium](https://medium.com/17live-tech/mysql-partitioning-%E5%84%AA%E5%8C%96%E4%B9%8B%E8%B7%AF-fd8e8480789b) - [Day.24 提升大數據資料管理 - 資料表分區 ( MYSQL Partition) - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天](https://ithelp.ithome.com.tw/articles/10276151) - [那些在MySQL上的分區方式 – Peter 工程日誌](https://peterli.website/%E9%82%A3%E4%BA%9B%E5%9C%A8mysql%E4%B8%8A%E7%9A%84%E5%88%86%E5%8D%80%E6%96%B9%E5%BC%8F/) - [What's MySQL partition](http://blog.kenyang.net/2017/06/11/whats-mysql-partition) - statistics - [mysql - Database table structure for storing statistics data - Stack Overflow](https://stackoverflow.com/questions/34332362/database-table-structure-for-storing-statistics-data) - bit mask relational database - [sql - Storing Combinatorial Values in a MySQL database - Bit masking, foreign keys, junction tables, or what? - Stack Overflow](https://stackoverflow.com/questions/31502363/storing-combinatorial-values-in-a-mysql-database-bit-masking-foreign-keys-ju) - [patterns and practices - Advantages and disadvantages of using bit masks in database - Software Engineering Stack Exchange](https://softwareengineering.stackexchange.com/questions/322271/advantages-and-disadvantages-of-using-bit-masks-in-database) - [sql - Any disadvantages to bit flags in database columns? - Stack Overflow](https://stackoverflow.com/questions/12270461/any-disadvantages-to-bit-flags-in-database-columns) - [sql - Optimize mysql query to use index on a Bitwise where clause - Stack Overflow](https://stackoverflow.com/questions/5352263/optimize-mysql-query-to-use-index-on-a-bitwise-where-clause) - [sql - MySQL doesn't use indexes when query over BIT field using bitwise functions - Stack Overflow](https://stackoverflow.com/questions/3560514/mysql-doesnt-use-indexes-when-query-over-bit-field-using-bitwise-functions) - how to store tags in mysql - [performance - Storing multiple tags on analytics database - Database Administrators Stack Exchange](https://dba.stackexchange.com/questions/176231/storing-multiple-tags-on-analytics-database) - [database design - SET or VARCHAR for list of tags in a MySQL photos table? - Database Administrators Stack Exchange](https://dba.stackexchange.com/questions/120787/set-or-varchar-for-list-of-tags-in-a-mysql-photos-table) - [mysql - SELECT items that has one or more specific TAGS - Database Administrators Stack Exchange](https://dba.stackexchange.com/questions/226670/select-items-that-has-one-or-more-specific-tags) - json field - [How to search JSON array in MySQL? - Stack Overflow](https://stackoverflow.com/questions/36249828/how-to-search-json-array-in-mysql) - [sql - MySQL How to Index a JSON array? - Stack Overflow](https://stackoverflow.com/questions/63478276/mysql-how-to-index-a-json-array) - [MySQL :: MySQL 8.0 Reference Manual :: 13.1.15 CREATE INDEX Statement - Multi-Valued Indexes](https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued) - [MySQL 8 split string by comma and convert it into JSON ARRAY - Stack Overflow](https://stackoverflow.com/questions/56958056/mysql-8-split-string-by-comma-and-convert-it-into-json-array) - others - [performance - SQL Database Schema Design For Large 3 Billion Relationship Database - Stack Overflow](https://stackoverflow.com/questions/4548757/sql-database-schema-design-for-large-3-billion-relationship-database) - [mysql - Database table with million of rows - Stack Overflow](https://stackoverflow.com/questions/51316492/database-table-with-million-of-rows) - [MySQL 中的 temporary table (臨時表) 觀念 - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天](https://ithelp.ithome.com.tw/articles/10230026) - Migration - [不停機 migration 避免鎖表的幾種操作](https://blog.niclin.tw/2019/08/10/migration-without-zero-downtime/) - [Evolutionary Database Design](https://www.martinfowler.com/articles/evodb.html) - [Breaking Free From the ORM: Decoupling Database Migrations](https://medium.com/building-the-system/breaking-free-from-the-orm-decoupling-database-migrations-e6e53d144cab) - [Decoupling database migrations from server startup: why and how](https://pythonspeed.com/articles/schema-migrations-server-startup/) - [資料庫也有版本控制 :: 2022 iThome 鐵人賽](https://ithelp.ithome.com.tw/users/20116987/ironman/5873) - [[MySQL] 資料表結構遷移的好幫手 pt-online-schema-change | 從入門到放棄](https://exfast.me/2020/07/pt-online-schema-change-is-good-tool-for-mysql-data-table-schema-migration/) - [GitHub 發展出來的 ALTER TABLE 方式 – Gea-Suan Lin's BLOG](https://blog.gslin.org/archives/2016/08/07/6716/github-%E7%99%BC%E5%B1%95%E5%87%BA%E4%BE%86%E7%9A%84-alter-table-%E6%96%B9%E5%BC%8F/) - [Eventbrite 的 MySQL 升級計畫 – Gea-Suan Lin's BLOG](https://blog.gslin.org/archives/2021/01/28/9940/eventbrite-%e7%9a%84-mysql-%e5%8d%87%e7%b4%9a%e8%a8%88%e7%95%ab/) - [PostgreSQL 上對應 pt-online-schema-change 的工具 pg-osc – Gea-Suan Lin's BLOG](https://blog.gslin.org/archives/2022/06/29/10767/postgresql-%e4%b8%8a%e5%b0%8d%e6%87%89-pt-online-schema-change-%e7%9a%84%e5%b7%a5%e5%85%b7-pg-osc/) - [sql - ALTER TABLE without locking the table? - Stack Overflow](https://stackoverflow.com/questions/463677/alter-table-without-locking-the-table) - Error - [mysql - Temporary table is full - Database Administrators Stack Exchange](https://dba.stackexchange.com/questions/287030/temporary-table-is-full) - Microsoft 資料架構指南 - [線上交易處理 OLTP](https://docs.microsoft.com/zh-tw/azure/architecture/data-guide/relational-data/online-transaction-processing#oltp-in-azure) - [線上分析處理 OLAP](https://docs.microsoft.com/zh-tw/azure/architecture/data-guide/relational-data/online-analytical-processing) - [非關聯式資料和 NoSQL](https://docs.microsoft.com/zh-tw/azure/architecture/data-guide/big-data/non-relational-data) - Race Condition - Real world - [JPMorgan Chase](https://chadscira.com/post/5fa269d46142ac544e013d6e/DISCLOSURE-Unlimited-Chase-Ultimate-Rewards-Points) - [Starbucks](http://sakurity.com/blog/2015/05/21/starbucks.html) - [github-race condition demo](https://github.com/search?p=3&q=race+condition+demo&type=Repositories) - [kkent030315/race-condition-demonstrate](https://github.com/kkent030315/race-condition-demonstrate) - [CyCTW/Database Race Condition Testing Demo](https://github.com/CyCTW/Database-Race-Condition-Testing-Demo) - [nsf/sqlrace](https://github.com/nsf/sqlrace) - [複習資料庫的 Isolation Level 與圖解五個常見的 Race Conditions](https://medium.com/@chester.yw.chu/%E8%A4%87%E7%BF%92%E8%B3%87%E6%96%99%E5%BA%AB%E7%9A%84-isolation-level-%E8%88%87%E5%B8%B8%E8%A6%8B%E7%9A%84%E4%BA%94%E5%80%8B-race-conditions-%E5%9C%96%E8%A7%A3-16e8d472a25c) - [淺談MySQL隔離層級為RR(可重復讀)時不能避免Phantom Read(幻讀)](https://notes.andywu.tw/2022/%e6%b7%ba%e8%ab%87mysql%e9%9a%94%e9%9b%a2%e5%b1%a4%e7%b4%9a%e7%82%ba%e5%8f%af%e9%87%8d%e5%be%a9%e8%ae%80%e6%99%82%e4%b8%8d%e8%83%bd%e9%81%bf%e5%85%8d%e5%b9%bb%e8%ae%80/) - [對於 MySQL Repeatable Read Isolation 常見的三個誤解](https://medium.com/@chester.yw.chu/%E5%B0%8D%E6%96%BC-mysql-repeatable-read-isolation-%E5%B8%B8%E8%A6%8B%E7%9A%84%E4%B8%89%E5%80%8B%E8%AA%A4%E8%A7%A3-7a9afbac65af) - [資料庫交易的 Isolation](https://medium.com/getamis/database-transaction-isolation-a1e448a7736e) - [MySQL Deadlock 問題排查與處理](https://yuanchieh.page/posts/2020/2020-12-26_mysql-deadlock-%E5%95%8F%E9%A1%8C%E6%8E%92%E6%9F%A5%E8%88%87%E8%99%95%E7%90%86/) - [All you need to know about Database transaction concurrency control — locks, Isolation level, MVCC | by LORY | Sep, 2023 | Medium](https://iorilan.medium.com/all-you-need-to-know-about-database-transaction-concurrency-control-locks-isolation-level-mvcc-ed1bacd9d1d2) - 安全 - [How SQL Injection attack works](https://blog.guilatrova.dev/how-sql-injection-attack-works-with-examples/) - 交換 - [What is the Parquet File Format and Why You Should Use It](https://www.upsolver.com/blog/apache-parquet-why-use) - 工具 - [dbdiagram 筆記 - HackMD](https://hackmd.io/@Kevin-Yang/SyYeiB5kt) - - 其他 - [<Database 一代代的演化和傳承> - Tachunn Publication](https://tachunwu.github.io/posts/db-history/) - [17LIVE Next-Gen Database選型分析. 宏觀上來看，各時期的資料庫並沒有強弱之分，都是為了滿足當前時代需求而演進的型態，… | by Pink Yu | 17LIVE Tech Insight | Medium](https://medium.com/17live-tech/17live-next-gen-database-%E9%81%B8%E5%9E%8B%E5%88%86%E6%9E%90-7a72b976cd2b) - [How to Efficiently Choose the Right Database for Your Applications](https://pingcap.com/blog/how-to-efficiently-choose-the-right-database-for-your-applications) - [Choosing between a relational database and a NoSQL database](https://docs.rackspace.com/support/how-to/choosing-between-rdbms-and-nosql/) - [Relational Database Vs NoSQL: A Comprehensive Analysis](https://hevodata.com/learn/relational-database-vs-nosql-a-deep-analysis/) - [RDBMS收費課程用的教材 - Triton Ho](https://www.facebook.com/groups/616369245163622/permalink/2354908371309692/) - [JSON in PostgreSQL，建議跟不建議 - Rubin](https://hackmd.io/80QqlRd1SLeIzCEvt-383A) - https://www.youtube.com/watch?v=l5Z_cE6jGxA - [該用 MySQL 或 MongoDB？選擇資料庫前你該了解的事](https://tw.alphacamp.co/blog/mysql-and-mongodb-comparison) - [mysql add foreign key constraint referencing a view](https://stackoverflow.com/questions/31340234/mysql-add-foreign-key-constraint-referencing-a-view) - [Database development mistakes made by application developers - Stack Overflow](https://stackoverflow.com/questions/621884/database-development-mistakes-made-by-application-developers/621891#621891) - [淺入淺出 MySQL Ep2 : 我的 SQL 怎麼執行我的查詢 | by vic | Medium](https://vicxu.medium.com/%E6%B7%BA%E5%85%A5%E6%B7%BA%E5%87%BA-mysql-ep2-%E6%88%91%E7%9A%84-sql-%E6%80%8E%E9%BA%BC%E5%9F%B7%E8%A1%8C%E6%88%91%E7%9A%84%E6%9F%A5%E8%A9%A2-b59906529f37) - [MySQL Explain分析與Index設定查詢優化 | Yuanchieh's Blog](https://yuanchieh.page/posts/2018/2018-07-30_mysql-explain/) - [MySQL有什么推荐的学习书籍？ - 知乎](https://www.zhihu.com/question/28385400) - [如何從關聯式資料庫轉型](https://www.dropbox.com/s/t8tsii7efbrl6k2/10%20-%20MDB%20Day%20Taipei%20-%20_%E8%BD%89%E5%9E%8B%E7%AD%96%E7%95%A5%EF%BC%9A%E5%A6%82%E4%BD%95%E5%BE%9E%E9%97%9C%E8%81%AF%E5%BC%8F%E8%B3%87%E6%96%99%E5%BA%AB%E8%BD%89%E5%9E%8B_%20-%20Tony.pdf?dl=0) - [使用 Docker Compose 建立啟用 X-Pack 的 Elasticsearch 與 Kibana - Yowko's Notes](https://blog.yowko.com/docker-compose-elasticsearch-kibana-x-pack/) - [A 5 years+ tech lead said they shard a database to scale but then he failed to answer this question | by LORY | Medium](https://iorilan.medium.com/a-5-years-tech-lead-said-they-shard-a-database-to-scale-but-then-he-failed-to-answer-this-question-8be39115dcb0) - [<Notion 的 Data Model & Sharding Postgres> - Tachunn Publication](https://tachunwu.github.io/posts/notion/) - [<OLEP：Online Event Processing 系統設計> - Tachunn Publication](https://tachunwu.github.io/posts/olep/) - [<Uber Schema-less 的系統起源> - Tachunn Publication](https://tachunwu.github.io/posts/schemaless/) - [DDIA 逐章精读](https://ddia.qtmuniao.com/#/) - [當代資料工程與資料分析 :: 2023 iThome 鐵人賽](https://ithelp.ithome.com.tw/users/20161869/ironman/6057?page=2) - gitlab - [關於 GitLab 的 SQL 設計 – Gea-Suan Lin's BLOG](https://blog.gslin.org/archives/2024/02/19/11671/%e9%97%9c%e6%96%bc-gitlab-%e7%9a%84-sql-%e8%a8%ad%e8%a8%88/) - [My Notes on GitLab Postgres Schema Design – Shekhar Gulati](https://shekhargulati.com/2022/07/08/my-notes-on-gitlabs-postgres-schema-design/) ## 設計 需求 - 商業 - 資料量級 - 安全 收集與梳理資訊 - 現場使用的人 - 現有表單(invoices, timesheets, surveys) - 現有資料系統 :::info 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 取得完整資料 看看候選欄位的基數大小, 基數就是指這個欄位的值的可能性 ``` sql SELECT COUNT(DISTINCT {field}/COUNT(*)) ``` 一般索引(覆蓋索引) 連合索引 : 常常需要與其它欄位一起查詢，適合建立。但是要注意先後順序，基本最常查詢的與基數高的適合放最左邊 前綴索引 : 這種大致上用在如果要針對某段文字進行前綴搜尋的情況 ### Keyword search - [用mysql fulltext search建立簡易搜尋引擎](https://medium.com/@changhengliou/%E7%94%A8mysql-fulltext-search%E5%BB%BA%E7%AB%8B%E7%B0%A1%E6%98%93%E6%90%9C%E5%B0%8B%E5%BC%95%E6%93%8E-80659c28ec19) - [Pattern Matching Queries vs. Full-Text Indexes](https://www.percona.com/blog/2018/04/04/pattern-matching-queries-vs-fulltext-indexes/) - [What is Full Text Search vs LIKE](https://stackoverflow.com/questions/224714/what-is-full-text-search-vs-like) - [Performance analysis of MySQL's FULLTEXT indexes and LIKE queries for full text search](https://makandracards.com/makandra/12813-performance-analysis-of-mysql-s-fulltext-indexes-and-like-queries-for-full-text-search) ## Redis - [Redis Fundamental](https://clhjoe.github.io/post/redis-fundamental/) - [Redis Developer](https://developer.redis.com/howtos) - [Redis Best Practices](https://redis.com/redis-best-practices/introduction/) - [使用 Redis 當作 API Rate limit 的三種方法](https://yuanchieh.page/posts/2020/2020-10-18-%E4%BD%BF%E7%94%A8-redis-%E7%95%B6%E4%BD%9C-api-rate-limit-%E7%9A%84%E4%B8%89%E7%A8%AE%E6%96%B9%E6%B3%95/) - [How to build a Rate Limiter using Redis](https://developer.redis.com/howtos/ratelimiting/) - [Redis Lock (Redlock) 分散式 lock 原理分析與實作](https://yuanchieh.page/posts/2020/2020-01-14_redis-lock-redlock-%E5%8E%9F%E7%90%86%E5%88%86%E6%9E%90%E8%88%87%E5%AF%A6%E4%BD%9C/) - [Finding non-expiring keys in Redis - Stack Overflow](https://stackoverflow.com/questions/9817951/finding-non-expiring-keys-in-redis) - [Redis Explained](https://architecturenotes.co/redis/) ## ElasticSearch - [Elasticsearch 系統介紹與評估 | Yuanchieh's Blog](https://yuanchieh.page/posts/2020/2020-07-08_elasticsearch-%E4%BB%8B%E7%B4%B9%E8%88%87%E8%A9%95%E4%BC%B0/) ## Hadoop ecosystem - [Hadoop ecosystem 工具簡介, 安裝教學與各種情境使用 :: 2018 iT 邦幫忙鐵人賽](https://ithelp.ithome.com.tw/users/20107349/ironman/1309?page=1) - [Day 15-HBase SQL工具介紹 - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天](https://ithelp.ithome.com.tw/articles/10192132) - [快速入門：Apache HBase 與 Apache Phoenix - Azure HDInsight | Microsoft Learn](https://learn.microsoft.com/zh-tw/azure/hdinsight/hbase/apache-hbase-query-with-phoenix) - [6大主流開源SQL引擎總結，遙遙領先的是誰？ | 帆軟軟體](https://www.finereport.com/tw/company/sql-2.html)
Last changed by  
 
j2hongming
1180
Published on HackMD