SQL优化

• Test Your Changes: Different databases handle SQL differently so it's not possible to lay down hard and fast rules that apply in every situation. What you can do, however, is look here for ideas to try and use an execution profiler to perform timing tests.

   

• Check the Execution Plan: Most databases provide some facility to display the optimization strategy or plan that will be used. This will tell you if a fast index or primary key search will be used instead of a slow full table scan. For Sybase and Microsoft SQL Server use ISQL and the set showplan on command. For Watcom SQL use the plan() function as follows:

     select plan ( 'select * from customer where id = 101' )
       from sys.dummy;
  
     Estimate 2 I/O operations
     Scan customer using primary key for rows where id equals 101
     Estimate getting here 1 times
  
     select plan ( 'select * from customer where fname = ''Beth''' )
       from sys.dummy;
  
     Estimate 10 I/O operations
     Scan customer sequentially
     Estimate getting here 126 times
  

   

• Use Indexes: Carefully chosen indexes speed up SQL queries without slowing updates too much. Almost all tables can benefit from an index, and experience has shown that the "ideal index" is almost never the primary key (even though a primary key index may be required to preserve referential integrity). Be aware, however, that many databases only store the first few bytes (e.g., 10 for Watcom SQL) of each entry in the actual index. And with Watcom SQL it is not necessary to define indexes for primary and foreign keys because they are automatically created; if you use ERwin or a similar tool be sure to disable the creation of indexes for Watcom primary and foreign keys.

   

• Avoid Not: If you can rewrite your queries to use the positive exists, in and like operators instead of not exists, not in or not like the database may be able to stop looking as soon as it finds an entry that satisfies the condition rather than proving that no entries exist.

   

• Use Exists instead of Count: The DBMS may be able to stop looking as soon as it finds a row that satisfies the exists condition whereas with count it must process all matching rows.

  See also Exists Versus Count(*).

   

• Try >= instead of >: If there is an index on column try select * from table where column >= 4 instead of where column > 3. Instead of looking in the index for the first row with column = 3 and then scanning forward for the first value that is > 3, the DBMS may jump directly to the first entry that is = 4.

   

• Try Union instead of Or: The database may do a better job of optimizing two selects connected via union rather than one select with an or operator. For example,

     select * from a, b where a.p = b.q
      union
     select * from a, b where a.x = b.y
  

  may run faster than

     select * from a, b where a.p = b.q or a.x = b.y
  

   

• Avoid Where Column Like '%string': On the other hand where column like 'string%' may run quite quickly especially if there's an index on column.

   

• Use Like Instead of Substr: If there is an index on column the database may process where column like 'x%' faster than where substr ( column, 1, 1 ) = 'x'.

   

• Split Procedure to Create Then Select: If a stored procedure creates a temporary table and then selects from it, the database may not be able to optimize the select properly. Try moving the select to a second stored procedure which is called from the first.

   

• Split Procedure to Turn a Variable Into a Parameter: The database may do a better job of optimizing where column = @var if @var is a stored procedure parameter rather than a local variable. Try moving the select to a second procedure which is called from the first procedure with @var passed as a parameter to the second procedure.

   

• Consider Indexes for Max() and Min() The max ( column ) and min ( column ) aggregate functions may run faster if there is an index on column. Other restrictions may apply; try putting the function all by itself in a separate select rather than combining it with other expressions. The presence of a group by or where clause may also inhibit optimization.

   

• Make Indexes Unique When They Are: The database may do a better job with unique indexes than with non-unique ones. So if an index is truly unique, declare it as such.

   

• Avoid Correlated Subselects: A correlated subselect is a nested select that refers to a column from the outer select. Here is an example that uses product.id as a correlation column to find all products that have no sales orders:

     select product.id
       from product
      where not exists (
     select sales_order_items.id
       from sales_order_items
      where sales_order_items.prod_id = product.id )
  

  Correlated subselects can be very slow if the inner result set is re-selected for each and every candidate row in the outer result set. Alternative SQL can sometimes look rather bizarre but it's usually worth the effort. In Watcom SQL the following select runs almost 4 times faster by using an outer join instead of a correlated subselect:

  select product.id
    from product left outer join sales_order_items
      on product.id = sales_order_items.prod_id
   where IfNull ( sales_order_items.id, 1, 0 ) = 1