Database optimization (Table optimization and SQL statement optimization) and SQL statements

Database optimization (Table optimization and SQL statement optimization) and SQL statements

It is mainly divided into two aspects: Table Design Optimization and SQL statement optimization.

First, Table Design Optimization:

1. The length of the Data row cannot exceed 8020 bytes. If the length is exceeded, the data occupies two rows, reducing the query efficiency.

2. Do not use the string type if it can be numeric. String type can reduce query efficiency and increase storage. The engine compares each character in the string one by one during query, and the corresponding value type only needs to be compared once.

3. For non-mutable char and varchar, both types are 8000 bytes. char queries are fast but consume storage space. varchar queries are relatively slow but save storage space. When designing fields, you can flexibly select them. For example, you can select CHAR for fields with minor Length changes such as user names and passwords, and VARCHAR for fields with large length changes such as comments.

4. The field should be as short as possible when the requirements are met, which can improve the query efficiency and reduce the resource consumption during index creation.

Query Optimization: avoid full table queries as much as possible.

1. Try to avoid null judgment in the where condition, so that the full table query will be performed without the index during the query.

2. Avoid using where statements as much as possible! =, <,> These symbols, otherwise, the full table scan will be performed without using the index. This is because the optimizer cannot use indexes to determine the number of rows to be hit. Therefore, you need to search all rows in the table.

3. Try to avoid using or in the where clause to connect to the condition. Otherwise, the full table scan will be performed without using the index.

For example:

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

　　

It should be changed:

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

4. Use in and not in with caution. Because in will make the system unable to use the index, but can only directly search the data in the table.

For example (this corresponds to a continuous value ):

select username from user where id in (1,2);　　　

It should be changed:

select username from user where id between 1 and 2;　　　.

5. Try to avoid using non-start letters to search for indexed character data. This also makes the engine unable to use the index.

See the following example:

1. Select * FROM T1 Where NAME LIKE ‘%L%’2. Select * FROM T1 Where SUBSTING(NAME,2,1)=’L’3. Select * FROM T1 Where NAME LIKE ‘L%’

Even if the NAME field has an index, the first two queries still cannot be accelerated using the index.
You have to perform operations on all data in the entire table one by one to complete the task. The third query can be accelerated using indexes.
Operation.

6. If necessary, force the query optimizer to use an index. If a parameter is used in the where clause, full table scan may occur. Because SQL parses local variables only at runtime, the optimizer cannot postpone the selection of the access plan to runtime; it must be selected at compilation. However, if an access plan is created during compilation, the value of the variable is still unknown and thus cannot be used as an input for index selection. As shown in the following statement, a full table scan can be performed: you can force the query to use the index:

Select id from t with (index name) where num = @ num.

7. Avoid performing expression operations on fields in the where clause as much as possible, which will result in dropping the index and using full table scan.

select * from user where math/2=45;　　

It should be changed:

select * from user where math=45*2;　　

That is, any operation on the column will cause the table to scan, including database functions, calculation expressions, etc. During the query, try to move the operation to the right of the equal sign.

8. Avoid performing function operations on fields in the where clause as much as possible. This will cause the engine to stop using the index for full table scanning.

9. Do not perform functions, arithmetic operations, or other expression operations on the left side of the where clause "=". Otherwise, the system cannot correctly use indexes.

10. when using an index as a field condition, if it is a composite index, you must use the first field in the index as the condition to ensure that the system uses the index. Otherwise, the index will not be used, and try to make the field order consistent with 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. Data Type incompatibility may make the optimizer unable to perform some optimization operations that can be performed originally.

13. You can use union all to avoid using unionunion all to not execute the Select DISTINCT function, which reduces unnecessary resources.

14. Try not to use the Select INTO statement. The Select INOT statement will lock the table and prevent other users from accessing the table..

2017-10-16