web开发者应该了解的数据库优化知识

数据库软件是server端数据存储、查询的抽象层，是数据与计算分离的设计典范。由于其实现的专业化和复杂度，如何正确使用或者优化数据库的访问对大多数web开发者都是一个极大的挑战。这里尝试从应用程序开发者的角度总结一下数据库使用和优化需要注意的一些问题，不求大而全，但求准确有效。

数据库性能影响因素

Five steps to postgres performance 总结归纳的很好，从5个层次来分析影响数据库性能的因素。

服务器应用开发者经常接触的是application和middleware两个层面，这也是我关注的重点，其他简单涉猎一下。

应用和中间件

这部分其实就是web开发者直接控制的部分，在编程访问数据库的时候到底要写代码才能做到高效呢？从应用层面，我们可以注意以下这些地方：

• 数据库表的设计要合理
  这是最体验架构功力的地方，不一样的问题不一样的设计。这里没法总结通用法则，只能提醒自己设计出来的表考虑好性能与满足1/2/3NF/BCNF范式做好平衡，具体数据库的设计范式是个让人头大的知识点，贴个链接经常翻翻吧，数据库范式。尽量往高级的范式靠拢以达到尽量小的数据冗余，这样经常会导致很多小表的联合查询，如果实在影响性能可以增加冗余来获得快速的查询。

• 合理使用索引
  索引是个性能利器，但要用之有度，滥用有害：

  • foreign和unique一般自动索引

  • 查询语句中出现的域一般加索引

  • 考虑使用不同类型的索引：expressions, full text, partial

  • 更新、插入、删除等写操作频繁时慎用索引

  • 小表没必要索引

  • 取值范围小的域加索引意义不大

• 分表、分库和分区
  分表和分库类似，只是操作的实体不同。策略上分为水平切分和垂直切分，但都需要application做相应的适配。具体实际操作自行google吧。分区还是保持单个表的存在，只是把表分成很多小块，然后可以把这些块存在不同的硬盘区域。分区一般是DBA做的，对应用开发透明，不需要应用来适配。

• sql操作的优化

  • 尽量合并操作

  • 慎用外键，看是否程序保持关联更高效

  • 拒绝select *,指定需要的列名

  • 用in代替or

  • 如果使用ORM (object relational mapping)框架，可以转成sql语句再调性能

• 使用cache
  cache用的好可以极大的提高服务响应速度，减轻数据库的压力，因为你根本没去查数据库。cache说开了去，很多地方都可以应用，这里单看一下server应用程序这一块。

  • 应用程序大量使用cache，减少访问db，可以cache整个页面、函数或单个对象或数据

  • 使能数据库cache

• 控制管理数据库的连接数
  每个进程与数据库server通信都通过一个独占的连接，连接是有开销的，再多进程编程中要注意控制这个开销。

数据库、操作系统和硬件层面的优化

大多数情况下，web开发者不关心或者也没必要涉及到这部分内容。作为一个完整的整体，了解这部分内容有助于理解整个系统是怎样工作的。

• 硬件
  web服务器软件和数据库软件，对于硬件资源的消耗主要是CPU、RAM、本地IO和网络方面。在搭建自己的服务器时，根据自己的可提供的服务规模可以大体估算出需要什么型号和多大的CPU、RAM、磁盘、网络带宽。满足定义的最大容量的基础上，考虑多一些冗余。从性能上讲，要考虑CPU IO在各级的性能表现，贴一个大概的IO表现。

• 操作系统和文件系统
  一般的web服务器程序不用考虑对操作系统和文件系统做特殊的调整或优化，但有些重度依赖数据的应用比如数据仓库会考虑做这方面的工作。

  • 文件系统选XFS & JFS或者Ext3, 减少log “-data=writeback, noatime, nodiratime”

  • 数据库log文件使用单独的文件路径甚至磁盘

  • 调大shmmax/shmall in kernel以获得更多共享内存

  • OS版本2.6.9的表现不好

  • 设置开启性能监控，监控CPU/ram/disk/network的使用情况

• 数据库配置
  这是DBA(数据库管理员)的本职工作，不同的数据库配置也不尽相同。其实这部分过于复杂，直接跳过。一般情况下使用默认的推荐配置，有问题再查资料调整。随手贴个oracle的架构，帮助我理解一次数据读写大概怎样完成的。

数据库优化方法论

有必要强调一下方法论，避免头痛医脚、南辕北辙的笑话。你走到数据库优化这一步，务必确定是你benchmark并分析过认定数据库的操作是瓶颈，否则不要想当然的认为这里有问题，很可能只是你的想象，并不真正解决问题。这种事情一直再发生，我自己也经常犯这样的混。
如果确实是数据库的瓶颈，我们以postgresql为例看有哪些有效的方法呢。

benchmark->分析->修改尝试->benchmark->...不断的重复这个闭环。最重要的是第一步，postgresql performance monitoring这里总结了很多方法。

数据库高性能若干规则

前面提到了不少建议和规则，更多的可以参考阅读：
Best Practices for Speeding Up Your Web Site
20 database design rules
Database optimization techniques you can actually use
面向程序员的数据库访问性能优化法则
high-performance-mysql