提升SQL应用性能：重构策略与实践

5星 · 超过95%的资源需积分: 9 186 浏览量更新于2024-07-25 收藏 2.27MB PDF 举报

"《SQL应用重构》是一本专注于提升数据库应用程序性能的书籍，提供了各种代码修改策略，旨在帮助开发者在不升级硬件的情况下优化数据库性能。书中包含实际案例，提供了简单问题的快速解决方案、复杂情况的深入解答以及针对大量问题的全面解决方案。" 在SQL应用重构中，作者探讨了如何识别和评估需要重构的代码，并强调了重构与性能之间的关键联系。以下是一些主要的知识点： 1. **性能提升的可能性评估**：学习如何判断何处可以期待性能提升，理解哪些部分的代码是性能瓶颈。 2. **快速修复技巧**：减少对数据库的调用，例如在存储函数和过程中限制对数据库的访问，可以显著提高性能。 3. **任务重构**：将应用代码替换为存储过程，或者用宽泛的SQL语句替换迭代的、过程化的语句，以提高效率。 4. **流程重构**：增加并行性，将业务诱导的处理从同步模式转变为异步模式，以改善系统响应时间。 5. **设计重构**：利用模式扩展、正规视图、物化视图、分区等技术改进数据库设计，进一步提升性能。 6. **比较和验证**：在进行代码修改后，通过对比修改前后的程序版本，确保结果的一致性，避免引入新的错误。 7. **SQL优化**：学习如何编写高效的SQL查询，避免全表扫描，使用索引和连接策略来优化查询速度。 8. **性能监控**：了解如何监控数据库性能，找出性能下降的原因，并制定相应的重构策略。 9. **持续学习资源**：O'Reilly出版社还提供了其他相关的SQL学习资源，如《SQL艺术》、《学习SQL》、《SQL袖珍指南》等，这些资源可以帮助读者更全面地掌握SQL知识。通过阅读《SQL应用重构》，开发者可以获取一套实用的工具集，不仅能够解决当前的性能问题，还能培养出对数据库性能优化的敏锐洞察力。书中的实践方法和案例分析有助于读者在实际工作中实现数据库应用程序的高效运行。

PREFACE xv

Every project needs a coordinator, and Mary Treseler, my editor, played this role on the

O’Reilly side. Mary selected a very fine team of reviewers, several of them authors. First

among them was Brand Hunt, who was the development editor for this book. My hearty

thanks go to Brand, who helped me give this book its final shape, but also to Dwayne

King, particularly for his attention both to prose and to code samples. David Noor, Roy

Owens, and Michael Blaha were also very helpful. I also want to thank two expert long-

time friends, Philippe Bertolino and Cyril Thankappan, who carefully reviewed my first

drafts as well.

Besides correcting some mistakes, all of these reviewers contributed remarks or clarifica-

tions that found their way into the final product, and made it better.

When the work is over for the author and the reviewers, it just starts for many O’Reilly

people: under the leadership of the production editor, copyediting, book designing, cover

designing, turning my lousy figures into something more compatible with the O’Reilly

standards, indexing—all of these tasks helped to give this book its final appearance. All of

my most sincere thanks to Rachel Monaghan, Audrey Doyle, Mark Paglietti, Karen

Montgomery, Marcia Friedman, Rob Romano, and Lucie Haskins.

2 CHAPTER ONE

Such behavior was the conventional wisdom for most of the 1980s, when SQL became

accepted as the language of choice for accessing corporate data. You can still find many peo-

ple who seem to think that the best, if not the only, way to improve database performance is

either to tweak a number of preferably obscure database parameters or to upgrade the hard-

ware. At a more advanced level, you can track full scans of big tables, and add indexes so as to

eliminate them. At an even more advanced level, you can try to tune SQL statements and

rewrite them so as to optimize their execution plan. Or you can reconsider the whole process.

This book focuses on the last three options, and explores various ways to achieve perfor-

mance improvements that are sometimes spectacular, independent of database parameter

tuning or hardware upgrades.

Before trying to define how you can confidently assess whether a particular piece of code

would benefit from refactoring, let’s take a simple but not too trivial example to illustrate

the difference between refactoring and tuning. The following example is artificial, but

inspired by some real-life cases.

WARNING

The tests in this book were carried out on different machines, usually with

out-of-the-box installations, and although the same program was used to

generate data in the three databases used—MySQL, Oracle, and SQL

Server—which was more convenient than transferring the data, the use of

random numbers resulted in identical global volumes but different data

sets with very different numbers of rows to process. Time comparisons are

therefore meaningless among the different products. What is meaningful,

however, is the relative difference between the programs for one product,

as well as the overall patterns.

A Simple Example

Suppose you have a number of “areas,” whatever that means, to which are attached

“accounts,” and suppose amounts in various currencies are associated with these accounts.

Each amount corresponds to a transaction. You want to check for one area whether any

amounts are above a given threshold for transactions that occurred in the 30 days preced-

ing a given date. This threshold depends on the currency, and it isn’t defined for all cur-

rencies. If the threshold is defined, and if the amount is above the threshold for the given

currency, you must log the transaction ID as well as the amount, converted to the local

currency as of a particular valuation date.

I generated a two-million-row transaction table for the purpose of this example, and I

used some Java

™

/JDBC code to show how different ways of coding can impact perfor-

mance. The Java code is simplistic so that anyone who knows a programming or scripting

language can understand its main line.

Let’s say the core of the application is as follows (date arithmetic in the following code

uses MySQL syntax), a program that I called FirstExample.java:

ASSESSMENT 3

1 try {

2 long txid;

3 long accountid;

4 float amount;

5 String curr;

6 float conv_amount;

8 PreparedStatement st1 = con.prepareStatement("select accountid"

9 + " from area_accounts"

10 + " where areaid = ?");

11 ResultSet rs1;

12 PreparedStatement st2 = con.prepareStatement("select txid,amount,curr"

13 + " from transactions"

14 + " where accountid=?"

15 + " and txdate >= date_sub(?, interval 30 day)"

16 + " order by txdate");

17 ResultSet rs2 = null;

18 PreparedStatement st3 = con.prepareStatement("insert into check_log(txid,"

19 + " conv_amount)"

20 + " values(?,?)");

22 st1.setInt(1, areaid);

23 rs1 = st1.executeQuery( );

24 while (rs1.next( )) {

25 accountid = rs1.getLong(1);

26 st2.setLong(1, accountid);

27 st2.setDate(2, somedate);

28 rs2 = st2.executeQuery( );

29 while (rs2.next( )) {

30 txid = rs2.getLong(1);

31 amount = rs2.getFloat(2);

32 curr = rs2.getString(3);

33 if (AboveThreshold(amount, curr)) {

34 // Convert

35 conv_amount = Convert(amount, curr, valuationdate);

36 st3.setLong(1, txid);

37 st3.setFloat(2, conv_amount);

38 dummy = st3.executeUpdate( );

39 }

40 }

41 }

42 rs1.close( );

43 st1.close( );

44 if (rs2 != null) {

45 rs2.close( );

46 }

47 st2.close( );

48 st3.close( );

49 } catch(SQLException ex){

50 System.err.println("==> SQLException: ");

51 while (ex != null) {

52 System.out.println("Message: " + ex.getMessage ( ));

53 System.out.println("SQLState: " + ex.getSQLState ( ));

54 System.out.println("ErrorCode: " + ex.getErrorCode ( ));

55 ex = ex.getNextException( );

56 System.out.println("");

57 }

58 }

剩余296页未读，继续阅读

huzhouhzy

粉丝: 83
资源: 1652

提升SQL应用性能：重构策略与实践

基于SQL Server的应用程序的优化.pdf

基于SQL Server的应用程序的优化 (1).pdf

数据库重构下部(PDF)

好书推荐之： 重构与模式.Refactoring.to.Patters.part2

好书推荐之： 重构与模式.Refactoring.to.Patters.part1

Wrox.Professional.Refactoring.in.CSharp.and.ASP.NET.Apr.2009.pdf

Wrox-Professional.Refactoring.in.Visual.Basic[www.TopSage.com].pdf

Refactoring.SQL.Applications

org.eclipse.ltk.core.refactoring..jar

addison.wesley.refactoring.ruby

最新资源

好书推荐之：重构与模式.Refactoring.to.Patters.part2

好书推荐之：重构与模式.Refactoring.to.Patters.part1