好的设计让你上天堂，烂的设计让你下地狱。

如果公司有专门的 DBA，後端工程师就可以把心力放在资料逻辑的处理上；但笔者询问周围的朋友，似乎有不少公司都是让後端工程师兼任 DBA...

笔者在这块也并非专家，主要是分享自己实作中会注意的基础细节，避免犯下一些原则性错误。

大纲

1. 设计资料库时会考量哪些点？

   • 1.1 面试官为什麽会问？
   • 1.2 面试官想从答案确认什麽？
   • 1.3 笔者提供的简答

2. 回答问题所需具备的知识

   • 2.1 在 Table 建立有效的 Index
   • 2.2 资料库设计与优化
   • 2.3 MySQL 与 PostgreSQL 的区别及应用场景

3. 衍伸问题

   • 3.1 为什麽 Index 不能建立太多？
   • 3.2 写 DB SQL 时会注意什麽？
   • 3.3 有用过 NoSQL 吗？什麽应用情境下会使用呢？
   • 3.4 你会用哪些点来评估要使用关联式资料库 or 非关联式资料库？

1. 设计资料库时会考量哪些点？

1.1 面试官为什麽会问？

• 因为履历资讯中表明擅长多种资料库（Database）
  • MySQL、MSSQL、PostgreSQL
• 且履历资讯中还表明有优化资料库的经验。
  • XXX 资料库系统
    将 Visual Basic 的单机版程序改写成网页系统，整合过去冗赘资料表，优化使用者体验。

1.2 面试官想从答案确认什麽？

• 选用资料库前，会思考哪些问题
• 设计资料库时会注意哪些细节
• 同样都是关联式资料库，你知道他们间的差异吗？
• 有用过哪些方式优化资料库效能
• 你对 NoSQL 的认知有多少

1.3 笔者提供的简答

在决定要使用什麽资料库前，我会先分析专案的应用场景：

• 像是商城会有金流，所以资料稳定性与 Transaction 机制是很重要的。
• 如果要设计地图平台，就会选择对 GIS 支援度高的资料库。

在设计资料库时会画 ER Model 来分析资料间的关联与属性；在优化方面，为了要提高搜寻效率会在 Table 建立 Index，同时也会依据实际业务设计栏位适合的长度及 Type。

2. 回答问题所需具备的知识

2.1 在 Table 建立有效的 Index

我们知道在 Table 建立 Index 可以增加搜寻效率，但你知道自己建立的 Index 有没有派上用场吗？笔者将在这个小节透过实作，带大家了解 Coverage Index (覆盖索引)的意义。

• SETP 1：建立测试用的 tmp_user Table

  • id 为 primary key
  • name 为 index

  create table tmp_user (
      id bigint(20) not null auto_increment ,
      name varchar(255) not null,
      password varchar(255) ,
      index index_name (name),
      primary key (id))
      engine=innodb
      default character set=utf8 collate=utf8_general_ci;
  
  insert into tmp_user (name, password) values ("宝宝不说","123");
  

• SETP 2：下 SQL 指令了解是否使用 Index

  观察最後「Extra」栏位的资讯有否有 Using index

  • SQL A：explain select id from user_table where name= '宝宝不说';
    
    因为 name 有建立 index，所以在查询到 name 後可以直接与 id 对应返回结果；因为 name 已经覆盖了查询资料的需求，所以称为 「Coverage Index」。
  • SQL B：explain select password from user_table where name= '宝宝不说';
    
    透过 name 找到「宝宝不说」对应的 id，但接着要透过 id 这个 primary key，在 Table 重新轮询才能取得这一列的完整资料。

• SETP 3：建立 Coverage Index 优化 SQL B

  • 先将资料库 drop 掉：drop table tmp_user;

  • 重新建立测试资料以及「Coverage Index」
    为了避免用 primary key 再次从 Table 查询的耗能，我们可以把 select 与 where 用到的栏位(name、password)做联合索引，这样就能够直接使用索引查询，而不需用 primary key 重新比对，这就是「Coverage Index」的应用。

    create table tmp_user (
      id bigint(20) not null auto_increment ,
      name varchar(255) not null,
      password varchar(255) ,
      primary key (id))
      engine=innodb
      default character set=utf8 collate=utf8_general_ci;
    
    insert into tmp_user (name, password) values ("宝宝不说","123");
    
    alter table tmp_user add index name_password(name,password);
    

  • 再次执行 SQL B：explain select password from tmp_user where name = '宝宝不说';
    本次的查询就可以在「Extra」栏位看到 Using index 噜～
    

2.2 资料库设计与优化

• 使用 ER Model 做规画
  在需求规格出来後，我们可以先透过 ER Model 整理资料间的关联性，然後以此为基础设计资料库；向新人说明 Table 间的关联时，它也是一个非常棒的辅助工具。

• 设计合适的文字栏位

  • 如果不确定该栏位会填入的文字长度，会选择 VARCHAR 这种依据文字长度来使用储存空间的 Type。
  • 以时常变更文字的栏位来说，会选择 CHAR 而非 VARCHAR；因为 VARCHAR 的栏位在储存时会需要额外的计算。
  • 尽量避免使用 TEXT/BLOB 这类的 Type，它们在查询时会消耗更多的效能。
  • 根据实际业务需求设计栏位长度，

• 减少 SQL 写入次数
  以 Insert 来做举例，假设有 1000 笔资料要写入 Table；一次插入 1000 笔的执行时间会远远小於分 1000 次插入的时间。

• 适当的增加冗余栏位
  在设计资料库时要尽量符合三大正规化；但 Table 间的关联越复杂也会导致查询效率的下降，因此有时会适当的增加冗余栏位，用牺牲磁碟空间的方式换取更高的查询效率。

• 定期审视资料库的规划
  如果没有专门的 DBA，那资料库通常会有以下问题需要修正：

  • 许多栏位因为不确定实际业务需求，直接把栏位长度开到最大。
  • 因为新需求不断产生，在思虑不够周密的状况下容易新增许多无用栏位。
  • 在系统版本的更迭中，没有整理不符合实际业务需求的检视表（View）。
  • 後端存取 Table 的栏位改变，但 Table 的 Index 并没有一起调整，导致许多 Index 并没有发挥作用。

2.3 MySQL 与 PostgreSQL 的区别及应用场景

MySQL 是世界上最流行的资料库之一，世界上有非常多的 Web Application 都采用它作为解决方案；但在被收购後分出了非常多的版本，有免费也有收费的；而 PostgreSQL 基於 BSD/MIT 的 linecense，为完全开源的资料库。

• PostgreSQL 的优势

  • 不仅是关联式资料库，还支援 jsonb 的格式
    相比於 json，jsonb 以二进位格式储存且可以使用索引（Index），你可以利用它整合关联式资料与非关联式资料。
  • 有 PostGIS 让他成为很棒的空间资料库
    相比於 MySQL spatial extension，PostGIS 可以支持「二维、三维、曲线」的空间类型，并有丰富的空间操作函数。
  • 可以轻松与外部资料库关联
    透过 FDW（Foreign Data Wrapper），你可以把外部资料库（ex：MySQL、Oracle、CSV）当成自己资料库中的 Table 查询，让 PostgreSQL 成为联合资料库。

• MySQL 的优势

  • 会 MySQL 的工程师更多
    在招募工程师时更容易，遇到问题时也有丰富的社群资源提供解答。
  • MySQL 使用 Thread 而 PostgreSQL 使用 Process
    • Process 比 Thread 要花更多时间建立连线。
    • Process 因为缺失 Thread 实作的「共享」特性，使得每次连线建立时，都需要耗费较高的记忆体空间配置。

      PostgreSQL 可以搭配 Connection Pool 的外挂解决这个问题，但架构上就会变更复杂。

• 应用场景

  • MySQL
    以读取为主的 Web 专案，适合业务逻辑相对简单、较少处理资料一致化问题的网际网路场景。
  • PostgreSQL
    专案资料写入频繁，且资料库庞大而复杂，内容需要高度一致性，有 GIS 需求。

其实在 DB 的选择上，除了考虑专案适用的场景外，还要考量到团队内的工程师对哪个比较擅长；思考万一发生意外时有谁可以救场，不然灾难复原是一场恶梦。

3. 衍伸问题

3.1 为什麽 Index 不能建立太多？

考点：了解 Index 对资料库效能的影响

Index 的数量太多，会影向 DML（Insert、Update、Delete）的效能，因为 Table 资料更新时也要连带更新 Index。

3.2 写 DB SQL 时会注意什麽？

考点：确认求职者的 SQL 基本功

• 不要回传大量资料，要有条件的回传固定笔数（ex：limit）。
• Select 时尽量不要使用「*」，只抓取需要的栏位。
• Select 充分使用 Index 来做搜寻。
• 避免使用不兼容的型态做比较，像是 FLOAT 跟 INT、CHAR 跟 VARCHAR。

3.3 有用过 NoSQL 吗？什麽应用情境下会使用呢？

考点：确认求职者对 NoSQL 的认知

即便你的履历中没有写自己会 NoSQL，还是会遇到这类考题，所以建议至少有基础的认知。

• 资料表栏位常常变化
  像是商城这种拥有庞大资料量的系统，在关连式资料库中如果要 Update 栏位有很多顾虑；因为每个操作都需要重头到尾轮询一次 Table，非常消耗时间。
  如果使用 MongoDB 这类的 NoSQL，就可以很灵活的对栏位进行调整。
• 需要高速的读写
  像是 Redis 这类记忆体资料库，单一节点就能应付每秒 10 万次的读写请求。
• 资料量庞大，有扩充需求
  NoSQL 具备水平扩充能力，只要增加新的服务器节点就能扩充资料库容量；且对於服务器的性能要求较低，可使用较便宜的电脑进行扩充。

3.4 你会用哪些点来评估要使用关联式资料库 or 非关联式资料库？

考点：确认求职者是否有评估的能力与经验

我会考虑的点有资料量多寡、并发量、实时性、资料一致性、未来扩充性、资料稳定性、维护成本。

以企业内部管理系统来说，因为资料量少、并发数少、未来扩充需求较低，所以会选择关连式资料库；如果是游戏的排行榜页面，因为资料量大、并发数高、且要求高的更新频率，所以我会考虑使用记忆体资料库（ex：Redis）。

感谢大家的阅读，如果喜欢我的文章可以订阅接收通知；如果有帮助到你，按Like可以让我更有写文的动力，我们明天见～

参考资源

1. MySQL 资料库面试题
2. 你在 Table 建立的 Index 真的有效吗？用范例带你理解 Coverage Index 的意义(笔者部落格)
3. MySQL 与 PostgreSQL 相比哪个更好？

我在 Medium 平台 也分享了许多技术文章
❝ 主题涵盖「MIS & DEVOPS、资料库、前端、後端、MICROSFT 365、GOOGLE 云端应用、自我修炼」希望可以帮助遇到相同问题、想自我成长的人。❞