排序应该在数据库还是在应用程序中进行?

  • 冯大辉

2008 年 9 月 15 日

话题:数据库MySQL架构DevOps语言 & 开发

在网站开发中,究竟是在数据库(DB)中排序好,还是在应用程序中排序更优,这一直是个很有趣的话题。DBANotes.net博主,在数据库方面比较有研究的冯大辉就这一问题日前和读者明灵(Dragon)做了探讨,本文是关于该问题的总结。

问:请列出在 PHP 中执行排序要优于在 MySQL 中排序的原因?

答:通常来说,执行效率需要考虑 CPU、内存和硬盘等的负载情况,假定 MySQL 服务器和 PHP 的服务器都已经按照最适合的方式来配置,那么系统的可伸缩性(Scalability)和用户感知性能(User-perceived Performance)是我们追求的主要目标。在实际运行中,MySQL 中数据往往以 HASHtables、BTREE 等方式存贮于内存,操作速度很快;同时 INDEX 已经进行了一些预排序;很多应用中,MySQL 排序是首选。而在应用层(PHP)中排序,也必然在内存中进行,与 MySQL 相比具有如下优势:

  1. 考虑整个网站的可伸缩性和整体性能,在应用层(PHP)中排序明显会降低数据库的负载,从而提升整个网站的扩展能力。而数据库的排序,实际上成本是非常高的,消耗内存、CPU,如果并发的排序很多,DB 很容易到瓶颈。
  2. 如果在应用层 (PHP) 和 MySQL 之间还存在数据中间层,合理利用的话,PHP 会有更好的收益。
  3. PHP 在内存中的数据结构专门针对具体应用来设计,比数据库更为简洁、高效;
  4. PHP 不用考虑数据灾难恢复问题,可以减少这部分的操作损耗;
  5. PHP 不存在表的锁定问题;
  6. MySQL 中排序,请求和结果返回还需要通过网络连接来进行,而 PHP 中排序之后就可以直接返回了,减少了网络 IO。

至于执行速度,差异应该不会很大,除非应用设计有问题,造成大量不必要的网络 IO。另外,应用层要注意 PHP 的 Cache 设置,如果超出会报告内部错误;此时要根据应用做好评估,或者调整 Cache。具体选择,将取决于具体的应用。

问:请提供一些必须在 MySQL 中排序的实例?

答:在 PHP 中执行排序更优的情况举例如下:

  1. 数据源不在 MySQL 中,存在硬盘、内存或者来自网络的请求等;
  2. 数据存在 MySQL 中,量不大,而且没有相应的索引,此时把数据取出来用 PHP 排序更快;
  3. 数据源来自于多个 MySQL 服务器,此时从多个 MySQL 中取出数据,然后在 PHP 中排序更快;
  4. 除了 MySQL 之外,存在其他数据源,比如硬盘、内存或者来自网络的请求等,此时不适合把这些数据存入 MySQL 后再排序。

必须在 MySQL 中排序的实例如下:

  1. MySQL 中已经存在这个排序的索引;
  2. MySQL 中数据量较大,而结果集需要其中很小的一个子集,比如 1000000 行数据,取 TOP10;
  3. 对于一次排序、多次调用的情况,比如统计聚合的情形,可以提供给不同的服务使用,那么在 MySQL 中排序是首选的。另外,对于数据深度挖掘,通常做法是在应用层做完排序等复杂操作,把结果存入 MySQL 即可,便于多次使用。
  4. 不论数据源来自哪里,当数据量大到一定的规模后,由于占用内存 /Cache 的关系,不再适合 PHP 中排序了;此时把数据复制、导入或者存在 MySQL,并用 INDEX 优化,是优于 PHP 的。不过,用 Java,甚至 C++ 来处理这类操作会更好。

从网站整体考虑,就必须加入人力和成本的考虑。假如网站规模和负载较小,而人力有限(人数和能力都可能有限),此时在应用层(PHP)做排序要做不少开发和调试工作,耗费时间,得不偿失;不如在 DB 中处理,简单快速。对于大规模的网站,电力、服务器的费用很高,在系统架构上精打细算,可以节约大量的费用,是公司持续发展之必要;此时如果能在应用层 (PHP) 进行排序并满足业务需求,尽量在应用层进行。

数据库MySQL架构DevOps语言 & 开发