大数据分页实现与性能优化

9/1/2015来源:SQL技巧人气:2925

大数据分页实现与性能优化

大数据分页实现与性能优化

摘要:Web 应用程序中经常使用数据分页技术,该技术是提高海量数据访问性能的主要手段。实现web数据分页有多种方案,本文通过实际项目的测试,对多种数据分页方案深入分析和比较,找到了一种更优的数据分页方案Row_number()二分法。它依靠二分思想,将整个待查询记录分为2部分,使扫描的记录量减少一半,进而还通过对数据表及查询条件进行优化,实现了存储过程的优化。根据Row_number()函数的特性,该方案不依赖于主键或者数字字段,大大提高了它在实际项目中的应用,使大数据的分页效率得到了更显著的提高。

关键词:数据分页;性能优化; web应用

中图分类号: 文献标识码: 文章编号:

Big Data Paging Implementation and Performance Optimization

WangLing

(College of information science and technology of Shijiazhuang Railway University, Hebei Shijiazhuang 050043)

Web applications often use data paging technology, which is to imPRove the massive primary means of data access performance. Web data paging to achieve a variety of programs, this test through practical projects, a variety of data paging scheme in-depth analysis and comparison found a better data paging scheme Row_number () dichotomy, it relies on two points thoughts, the entire subject to query log is divided into two parts, so that scanned records reduced by half, and then through a data table and query optimization, optimized to achieve a stored procedure. According Row_number () function characteristics, the program does not depend on the primary key or a numeric field, greatly improving its practical application in the project, so that large data paging efficiency has been more significant increase.

KeyWords: data paging ; web application ; performance optimization

0引言

在web应用程序开发过程中,不可避免的要频繁查询数据库中的数据。随着互联网的飞速发展,中大型系统的数据量变得庞大而复杂,要提高系统的响应性能,就需要降低客服端和服务器端数据的传输量,因此大数据分页的功能不可或缺。若选择一个不合理的数据分页方案,大数据在查询时就会引发网络资源严重浪费【1】,网站拥堵,查询界面等待时间过长等一系列严重影响系统性能的问题。所以,一个有效的大数据分页方案对于系统的性能而言至关重要。解决大数据分页的问题,不同的人会采用不同的方法,其访问性能各有优劣。笔者通过比较多种分页研究方案【2】,根据实际案例的测试结果,综合分析各种分页方案的利弊,扬长避短,最终找到一种更优于以往的分页方案,Row_number()二分法。二分思想在计算机中早有应用,二分查找算法就是二分思想的具体体现,将它引入到存储过程中,依靠二分法的思想,对Row_number()存储过程分页进行优化设计,从而加快查询速度,提高大数据的分页效率。

1 动态网页数据分页

Web数据分页是基于降低数据传输量来提高服务响应时间的分页方法。但是不同的数据分页方法,带给Web主机的系统I/O访问性能是不同的。无论是java平台,还是.NET平台,对数据的分页都提供了多种方法,主要分为2大类:一类是内存数据分页,一类是数据源分页。

11内存数据分页

所谓内存数据分页【3】就是当客户端向 Web服务器发出查询请求时,Web 服务器响应请求并构建 SQL 语句发送到数据库服务器,数据库服务器执行 SQL 语句并返回整个结果集给 Web 服务器,Web 服务器再执行内存数据分页操作并把该页数据发往客户端,完成一次查询。内存数据分页的流程如图1所示:

ipH58+eEhISAgIClpSWnFgQfAAAAOGZlZcVkMqWmpiowfMh9zs/PHz58aDQaX79+LX0p8AEAAIADlpaWfHx8ioqKLi4u1K6LVKxWa3Nzs06nGx4elrgI+AAAAECM9fV1Pz+/kpISgk9+KUZ7e7tOp5uYmJCSGXwAAABgl729veDg4OzsbA3ePZbI06dPvby83r9/7zAn+AAAAECYy8vLpKSk6Ojo09NTteviOlar9cGDB6GhocfHx+I5wQcAAADCtLS0eHt7f/z4Ue2KuMu3b99CQ0MLCgrEs4EPAAAABNjZ2TEajZ2dnWpXhAxzc3MIodnZWZE84AMAAAABcnJyoqOjr+5tAz75+fkWi0Vki8AHAAAAXD58+IAQmpqaUrsiJNnZ2dHpdCMjI/YygA8AAAC4lJaWRkZGXsUBpuLk5ubGx8fbmws+AAAA+ImzszMfH58XL16oXRHy4LsI9t6BCj4AAAD4iYmJCZqm9/b21K4IeaxWa0BAQFNTk+Bc8AEAAMBPVFZWRkdHq10LuSgoKEhOThacBT4AAAD4ifj4+IcPH6pdC7no7Oz08fERvDUCPgAAAPgJf3//jo4OtWshF2/fvkUICX4dAXwAAADwJ+fn5wih0dFRtSsiF1tbWwihlZUV/izwAQAAwJ8cHh4ihCS+EPQQ8uXLF3sPKoMPAG3x/v37N2/eqF2LK8bW1tb4+LjatbgmbG9vI4TsjcC5BqyvryOEWltb+bPAB4C2ePToEQJcRe3/3nVgb28PIVRXV6d2ReRicXERISR4gwR8AGiLy8tLhJDRaBR/8RaA2draCggIMJlMHR0d4AMiHBwcIIRKS0vVrohcjIyMIISGhob4s8AHgLawWq0IocLCQlCCQxgZZGRk9PT0gA+IgH1w7949tSsiF/X19QihsbEx/izwAaAtsA+sVisoQRy2DLKzs6F/QArsg6CgILUrIhdpaWngA+BqgH2AJ0AJ9uDIIDs7u62tDXxABOwDZP8lP1eas7Mzb29veX1wFceEwJAMbcL4wAZKsANfBuADgmAf+Pn5NTQ0qF0X8rx+/ZqmaXl9cKXHhBDZAwAp2D6wgRJ4CMoAfEAQ7IPMzMzg4OCLiwu1q0OYlJSUpKQkJKsPmDEhMzMzRFYoKzAkQ8twfGADJbCwJ4Ps7OzW1lYIZiJgH3R2dhoMhr6+PrWrQ5Ll5WWEUF9fn7w+YI8J0bgSYEiGxuH7wAZKsNlsojIAHxAE+2BoaKikpCQ4OPj09FTtGpHBarXevXs3ISFhbW1NCR8wjXZ6eprIaomzublpMplgSIaWEfSBzeOVIC4D8AFBGB+sr6/fuHGjpqZG7RqRYWBgQKfTzczMKOQD249GW1dXZ9UkPT09AQEBTIuCJqRB8LVHwVlWT1WCQxlAMBME+2BwcHB3d7erq4um6fn5ebUr5S67u7t+fn6VlZW7u7urq6sK+YCZ1jLQhFxjY2NDgVJEfGDzSCVIkYEnBzPxgzXbB7u7uzk5OUFBQYJvh74qnJ2dxcbGxsbGbm9vK+0Dm82GEDrTJO/fvwcfuExISEhWVtbJyYmspYj7wPZDCXFxcZeeQV5enkMZeHIwh4SEFBQUWMl9+B77YGBgYGdnZ2dnZ3Nz8/bt2zExMd++fSNVhJJcXl5mZ2cHBgYuLy/jLQIffAd84A64dxUWFra4uChfKZeOfGCz2axW6/7+vpJ9StURl4EnBzPePxkZGaSO1xwf7OzsrKyshIaGJiYmyn0yRJzLy8vi4mJfX9/p6Wlmc1TwwakmwTdSoAm5hsViyc/PT05Opmm6pqbm/PxcjlKk+MD246Mlxx7A+Pg4+EAEi8WSmJhoMpkiIyN3d3fdXyHjg20WCwsLoaGhcXFxh4eH7hehDOfn57m5ub6+vmNjY+xtWVlZAR+cnoIP3MNisdTU1JycnLS3t3t5eVkslrW1NeKlOOWDIw9gbGxMig9aWlo8M5hxWG5ubloslhs3biwsLLi5QkEfbG9vv3v3zmKxhIWFKXMjzU329/cTExMDAgKmp6c5GyK7DzhtGCF0oknAB+5gsViqq6vxSevq6uqdO3d0Ol1jY+Pl5SXBUsAHHMAH4jBh+eXLl7S0NL1e39vb684KsQ/6+/s/8nj//v29e/e8vb0FXxatHRYWFoKCgqKiohYWFvhbgZ9KU9QH3zQJvnAGTcg1zGbz48ePD3+wv7/f0NBgMBhiY2MJnjE55YNDD+D169fgAxHYYXlwcFBVVYUQqqqqcvk0RcQHHz9+3Nraqq2t1ev1OTk5+/v7ZLfFfc7OzmpqanQ6XX5+/ocPHwQ3AXzwHfCBO5jN5qqqqv2fmZ+fj4qKMhqNbW1tRMZ4XFxcSPmnnJ2dIYQOPADwgTj8sOzq6jIYDKmpqcfHxy6s8ODgACHU19e3ZZ+xsbGIiAg/P7+uri6y/WOXsVqt4+PjISEhAQEBL1++FKn8u3fvlPaBmjfg7IMvnEETcg2z2VxZWfmVx6dPnx4/fqzT6RITE3d2dtwsBXzAAXwgjmBYTk5OmkymiIiIjx8/OrvCAwk+2Nra+vDhw5MnT7y9vcPDw0dGRggOeHWBubm5xMREvV5fXFy8uroqXnMVfKD2RVdhcEcJmpBrmM3mioqKT3aYnJwMCwvz9vbu6elxp2045YN9D2B0dFSKD5qbmz0zmO2F5bt37yIjI/38/Jx9FT/2QW9v76YElpaW8AOS4eHhPT09J8oOSL28vBwdHY2Li6NpOj09fWZmRmKdwQdHR+AD9zCbzeXl5Tv22dzcLC0tpWk6NTXV5ec58Y0Bh9lOT08RQnseAPhAHJGw3NjYuH//vk6n6+7ulr5Cp3yAmZ+fLy0t9fX19fHxKSsrW1xclLu78OHDh9ra2oCAAIPB8ODBg6mpKem1VcEH6t6CswfHBx7bhFzDbDaXlZUJ3qFiMzQ0FBwc7OfnNzg46EKrAB9wAB+IIx6WW1tb+Oss5eXlEj9mwPhgw0lWV1ebmppiYmIQQkFBQRUVFZOTkwR7DOfn53Nzc7W1tREREQihsLCw6urqpaUlZ+u5uLgorw84fXzNXtjFF85w+8nMzHzw4AG+UAhIASH08OFDKScgq6urubm5eFfvOzkGwykf8G9mXD9evXrl0AeeHMxSwrKjo8NgMCQnJx9KeJrswFUfMMzMzFRXV8fExOh0Op1Od/v27YqKir6+vuXl5W/OPESNX6kwPDxcXV2dmJhoNBoRQhEREWVlZWNjYx8+fHCteuCD77B9kJ6ejp909wKkgU+yPkgGv03WZDI59V1S8AEHKT7w5GCWGJavXr0KCAgICwvb3NwUD62DgwOE0MuXL6WHuj3evXvX2dlZVFQUGxuLv1qMEPL394+Ojk5LSysoKKioqKipqamrq6urq3vy5EllZWVRUVF6enpMTExAQADObzAYIiMjc3NzW1pa5ubm3K/VwsKC0j5Q+yacMPjCGbsJdXd3rwGyMT8/j/dzQUHB0dGRlFhyygdfPADpPoBgFmd2djYyMhK/zEcktAj6gM36+vrMzEx3d3d9fX1JSUlmZmZSUlJMTIzFYomIiAgPDzebzdHR0YmJienp6YWFhU+ePHnx4sXk5OT79+/J1kR2H3DasGYv7OKOErsJdXZ2rgAy09LS4u/vHxQUJOUl1eADDtJ9AMHskMXFxbS0NJqm29vb7d3cwj7o6elZv6bMz8+DD/b2hHzw4sWLd4D8TE9P4694l5eXi99hk+iDk5MThNBnD2BkZESiDyCYpbC0tFReXk7TdElJieA7GcEH7sL3gdoXXYXh+6C9vX0RUISFhYX6+npvb+/Q0NBF+2/MdsoH9h6GuE4MDw9L9AEEs3Sam5u9vLwSExP3eeMdsA+6u7vfX1Pm5ubAB1+/CvmgtbV1DlCQV69e3blzh6bp6urqs7Mzh7FkD/AB3wcQzE7R29sbGBh48+bNDx8+sEMLfOAufB+ofdFVGHwjhd2EWlpa3gDKMjs7W1lZ6eXlZTabV1dXObGEHzx2GHKUsuyqh3QfQDA7y+joaGRkpJeX18TEBBNajA/Uvv8tF2/fvgUffPki5IPnz5/PAGowMDAQFRWl0+kaGhrYDwpp0wciT2XLzdDQkEQfQDC7wOTkZGpqKk3Tzc3N+A6zTD7AgSSEOgwq+EDtm3DC4Bsp7CbU1NQ0ZR/8DxNPsbeIlGXZKVKOSuwF//jjD5EqOaynFpicnCwpKdHr9bdv39748cZs8IHLPlAymCWuxGERUxoIZhyHNE0XFBScnZ1hH3R1da2SAFdefEJh3rx5o7QP1L7oKgy+cMZuQo2NjROiUBRlNptFjhT8/EwiP4P4XHs5Bevzr3/9i103e4tLqbO6dHV1hYeHGwyG1tbWy8tLZ31AJIAdlrKtHtJ9oHAwT/wcXSKzBMud0Fgw19fXe3l53blzZ2Njg6APVn8c99mw00mVIh3wwXf4Pqivr3/tDPhfyE8Rx2E2h6XwM/zv//6vSGZOudLXrAqvXr3KycnR6XQJCQmbm5sa9IHDtzbJx+DgoEQfKBbM/MWlxJVITGokmDs6OgIDAwMDA4n3D/jwZxEpTgqy+4BzTocQUvH+mwj4whm7CT19+vSVNAT/qfayiaeLT9tDMENWVpbIImFhYRKrrQXwG9kw0o/URALYYSlXwgeKBbPgLE4K+yc7FO2VrpFg7uvrw68JKi8vJ/gcHLvC7BT2hGLMzs4q7QMVr7eKwPdBbW3tsH2YfyEnRTAbfxHBdEFEVs5Pxz9///139ix79bSXQWv09fUlJycjhOLi4jToA/EPjMjKwMCARB8oE8z89dgrghPh/IAf1l4wP3r0iKZpmqZbWlqWCUHxYBLZE4oBPvgO3wdPnjwZFIX/v+TAzsbPz1mP