Optimization of the database

Optimization of the database

1). SQL Statement optimization

A try to avoid using the! = or <> operator in the WHERE clause, otherwise the engine discards the use of the index for a full table scan.

b You should try to avoid null values for the field in the WHERE clause, otherwise it will cause the engine to abandon using the index for a full table scan, such as:

Select ID from t where num is null

You can set the default value of 0 on NUM, make sure that the NUM column in the table does not have a null value, and then query:

Select ID from t where num=0

C many times replacing in with exists is a good choice.

D Replace the HAVING clause with a WHERE clause because the having will only filter the result set after retrieving all records

2). Index Optimization

A avoid operations on columns

Any action on a column can result in a full table scan, where the so-called operations include database functions, calculation expressions, and so on, to move the operation to the right of the equation whenever possible, or even to remove the function. Example: 300,000 rows of data

SELECT * FROM record where SUBSTRB (cardno,1,4) = ' 5378 ' (13 seconds)

Optimized: SELECT * from record where Cardno like ' 5378% ' (< 1 seconds)
SELECT * from record where amount/30< 1000 (11 seconds)

Optimized: SELECT * from record where amount< 1000*30 (< 1 seconds)
SELECT * FROM record where TO_CHAR (Actiontime, ' yyyymmdd ') = ' 19991201 ' (10 seconds)

Optimized: SELECT * from record whereactiontime= to_date (' 19991201 ', ' YYYYMMDD ') (<1 sec)

b Avoid unnecessary type conversions

It is important to avoid potential data type conversions as much as possible. If you compare character data with numeric data, Oracle automatically converts the character type with the To_number () function, which results in a full table scan.

For example: The column col1 in table Tab1 is a character type (char), and the following statement has a type conversion:

Select Col1,col2from tab1 where col1>10,

Should be written as: Select Col1,col2 from Tab1 where col1> ' 10 '.

C increase the scope limit of the query

Increase the scope of the query to avoid a full range of searches.

For example, the following query table record has a time actiontime less than March 1, 2001 data:

SELECT * FromRecord where Actiontime < To_date (' 20010301 ', ' yyyymm ')

The query plan indicates that the above query has a full table scan of the table, and if we know that the earliest data in the table is January 1, 2001, then you can add a minimum time to make the query within a full scope.

Modify the following: SELECT * from record where

Actiontime <to_date (' 20010301 ', ' yyyymm ')

and Actiontime > To_date (' 20010101 ', ' yyyymm ')

The latter SQL statement will take advantage of the index on the Actiontime field to improve query efficiency. Replace ' 20010301 ' with a variable, which can be more than half the chance to improve efficiency, depending on the probability of the value being taken. Similarly, for queries that are larger than a certain value, you can also add "and column name <max (maximum)" in the WHERE clause if you know the current maximum possible value.

3). Database structure Optimization

A) Paradigm optimization: such as eliminating redundancy (space saving). ）

b) Inverse paradigm optimization: such as appropriate redundancy (reduce join)

c) Split table: Partitioning separates data physically, and data from different partitions can be stored in data files on different disks. In this way, when querying this table, only need to scan the table partition, instead of full table scan, significantly shorten the query time, the other partition on different disks will be the data transfer to the table of different disk I/O, a well-provisioned partition can transfer data to disk i/ o The competition is evenly dispersed. This method can be used for a time table with a large amount of data. Table partitions can be automatically built by month.

Split in fact split vertically and horizontally split

Here's an example: the simple shopping system is set up in the following table: 1. Product table (data volume 10w, Stable) 2. Order table (data volume 200w, and growth trend) 3. User tables (data volume 100w, and growth trend), with MySQL as an example of horizontal split and vertical split, MySQL can tolerate an order of magnitude in millions of static data can go to millions

Vertical split : Solves the problem of IO contention between tables and tables

Place the product and user tables on a single server, with the order table placed on a single server

Horizontal split : Solve the stress problem of increasing the amount of data in a single table

User table by gender split into male user table and female user table, order form split into completed orders and unfinished orders, product table not completed orders placed on a server, completed Orders table and male user table put on a server, completed order form and female user table put on a server (female love shopping haha)

4). Server hardware Optimization

How much does this cost?

Optimization of the database