Database optimization principles and Optimization Principles

Database optimization principles and Optimization Principles
Recently, I have summarized the database optimization methods in the database course design and hope to help people who need them:
1. Optimize the query to avoid full table scanning. First, create an index on the columns involved in where and order.
2. Try to avoid null value determination on the field in the where clause. Otherwise, the engine will discard the index and perform full table scanning, for example:

    select id from p where num is null

You can set the default value 0 on num to make sure that the num column in the table does not have a null value, and then query it like this:

    select id from p where num=0

3. Try to avoid using it in the where clause! = Or <> operator. Otherwise, the engine will discard the index for full table scanning.

4. Try to avoid using or in the where clause to connect to the condition. Otherwise, the engine will discard the index and perform full table scanning, for example:

    select id from p where num=8 or num=12

You can query it as follows:

    select id from p where num=8　　  union all　　  select id from p where num=12

5. Use in and not in with caution. Otherwise, a full table scan may occur, for example:

    select id from p where num in(1，2，3)

For continuous values, you can use between instead of in:

    select id from p where num between 1 and 3

6. The following query will also cause a full table scan:

　  select id from p where name like '%abcd%'

To improve efficiency, you can consider full-text search.

7. If a parameter is used in the where clause, a full table scan is performed. 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. The following statement performs a full table scan:

    select id from p where num=@number

You can change it to force query to use the index:

Select id from p with (index name) where num = @ number

8. Avoid performing expression operations on fields in the where clause as much as possible. This will cause the engine to discard the use of indexes for full table scanning. For example:

    select id from t where num/2=10

Should be changed:

select id from t where num=10*2

9. Avoid performing function operations on fields in the where clause as much as possible, which will cause the engine to stop using the index for full table scanning. For example:
Select id from t where substring (name, 1, 3) = 'abc' -- id whose name starts with abc
Select id from t where datediff (day, createdate, '2017-12-30 ') = 0 -- '2017-12-30' generated id

10. the primary key is necessary. The primary key of SQL SERVER is a unique index at the same time. In practice, we often choose the smallest key combination as the primary key. Therefore, the primary key is usually suitable for table clustered indexes. In a table with multiple keys, the selection of the primary key is also important. Generally, the selection of keys with a small total length is faster, at the same time, a small key can reduce the B-tree hierarchy of the primary key. When selecting a primary key, pay attention to the field order of the primary key combination. For the primary key combination, the performance of the primary keys in different field order may vary greatly, generally, you should select a field with a low repetition rate, a single field, or a combination of query possibilities.

11. Use the numeric type as much as possible. The numeric type is much faster than the numeric type.

12. The data type should be as small as possible. The smaller value here refers to meeting the foreseeable future needs.

13. Do not allow NULL unless necessary. Use the default value instead.

14. Using less TEXT and IMAGE, binary fields are relatively slow to read and write, and there are not many reading methods. It is best not to use them in most cases.

15. Auto-increment fields should be used with caution, which is not conducive to data migration.

16. Applicable file groups can effectively distribute I/O operations to different physical hard disks to improve concurrency.

17. Do not add too many indexes to a table because the index affects the insert and update speeds.

18. Appropriate use of redundant anti-paradigm design can be very efficient sometimes with space for time.

19. When querying a table, remember to put the small result set in front and follow the principle that the small result set drives the big result set.

20. Use limit to reduce the number of returned rows and reduce data transmission time and bandwidth waste.