Database Optimization (table optimization and SQL statement optimization)

Here is mainly divided into table design optimization and SQL statement optimization two aspects to achieve.

The first is table design optimization:

1. Do not exceed 8020 bytes of data row length. If it exceeds this length, the data will take up two lines to reduce the efficiency of the query.

2. Do not use a string type if you can use a numeric type. String types can reduce the efficiency of queries and increase storage. Because the engine compares each character in a string one at a time while querying, the corresponding numeric type only needs to be compared once.

3. For non-mutable character types char and variable character type varchar are 8000 bytes, char queries fast, but consumes storage space, varchar queries are relatively slow but save storage space. In the design of the field can be flexible choice, such as user name, password, such as the length of the field can be selected char, for the comment, such as the length of a large variety of fields can choose VARCHAR.

4. Fields are as short as possible to meet the requirements, which can improve query efficiency and reduce resource consumption when indexing.

Query optimization: Just try to avoid the whole table query

1. Should try to avoid NULL in the where condition of the judgment, so that the query will discard the index when the full table query

2. You should try to avoid using in the WHERE clause! =,<,> these symbols, otherwise the full table scan will be discarded using the index. This is because the optimizer cannot determine the number of rows to be fatal by the index, so it needs to search all rows of that table.

3. You should try to avoid using or in the WHERE clause to join the condition, or it will cause a full table scan to be discarded using the index.

For example:

Select username from user where id=10 or id=20;

　　

should read:

Select username from user where id=10union allselect username form user where id=20;

4.in and not in should also be used with caution. Because in causes the system to not use the index, it can only search the data in the table directly.

such as (this is the corresponding and continuous value):

Select username from user where ID in (;

should read:

Select username from user where ID between 1 and 2; .

5. Try to avoid searching in indexed character data using non-heading letters. This also makes the engine unusable with indexes.

See the following example:

1. Select * from T1 Where NAME is like '%l% ' 2. Select * from T1 Where substing (name,2,1) = ' L ' 3. Select * from T1 Where NAME like ' l% '

Even though the NAME field is indexed, the first two queries are still not able to take advantage of the index completion to speed up the operation, the engine
You have to complete all the data on the table by operation. The third query can use an index to speed up
Operation.

6. Forcing the query optimizer to use an index if necessary, such as using parameters in the WHERE clause, can also cause a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot defer the selection of access plans to run time; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and therefore cannot be selected as an input for the index. The following statement will perform a full table scan: You can force the query to use the index instead:

Select ID from T with (index name) where [email protected]

7. You should try to avoid expression operations on the field in the Where clause, which will cause the index to be discarded and a full table scan to be used.

SELECT * from user where math/2=45;

should read:

SELECT * from user where math=45*2;

That is, any action on a column causes a table scan, which includes database functions, calculation expressions, and so on, to move the operation to the right of the equals sign whenever possible.

8. You should try to avoid function operations on the fields in the WHERE clause, which will cause the engine to discard the full table scan using the index.

9. Do not perform functions, arithmetic operations, or other expression operations on the left side of the WHERE clause, or the system will not use the index correctly.

10. When using an index as a field condition, if it is a composite index, the first field in the index must be used as a condition to guarantee that the system uses the index, otherwise the index will not be used and, if possible, match the order of the fields to the index order.

11. Replace in with exists

1.  elect num from a where num in (select num from B)

Select num from a where exists (select 1 from b where num=a.num)

12. Avoid using incompatible data types. For example, float and int, char and varchar, binary, and varbinary are incompatible. Incompatible data types may make the optimizer unable to perform some optimizations that could otherwise have been performed.

13. You can use UNION all to not use unionunion all to not execute the Select DISTINCT function, which will reduce a lot of unnecessary resources.

14. Try not to use the Select into statement. The Select inot statement causes the table to lock and prevent other users from accessing the table .

　　

　

Database Optimization (table optimization and SQL statement optimization)