Describes in detail the practical methods to improve database query efficiency and the performance of foreign keys.

 

1. To optimize the query, try to avoid full table scanning. First, consider creating 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 t 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 t 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 t where num = 10 or num = 20

You can query it as follows:

Select ID from t where num = 10

Union all

Select ID from t where num = 20

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

Select ID from t where num in (1, 2, 3)

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

Select ID from t where num between 1 and 3

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

Select ID from t where name like '% ABC %'

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 t where num = @ num

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

Select ID from T with (index name) where num = @ num

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 = 100

Should be changed:

Select ID from t where num = 100*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-11-30 ') = 0 -- '2017-11-30' generated ID

Should be changed:

Select ID from t where name like 'abc %'

Select ID from t where createdate> = '2014-11-30 'and createdate <'2014-12-1'

10. do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause. Otherwise, the system may not be able to correctly use the index.

11. when using an index field as a condition, if the index 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 the field order should be consistent with the index order as much as possible.

12. Do not write meaningless queries. If you need to generate an empty table structure:

Select col1, col2 into # T from t where 1 = 0

This type of code will not return any result set, but will consume system resources, should be changed to this:

Create Table # T (...)

13. In many cases, replacing in with exists is a good choice:

Select num from a where num in (select num from B)

Replace the following statement:

Select num from a where exists (select 1 from B where num = A. Num)

14. not all indexes are valid for queries. SQL queries are optimized based on the data in the table. When there is a large number of duplicate data in the index column, SQL queries may not use indexes, for example, if a table contains sex fields, male and female are almost half of each other, indexing sex does not play a role in query efficiency.

15. the more indexes, the better. Although the index can improve the efficiency of the SELECT statement, it also reduces the efficiency of insert and update, because the insert or update statements may recreate the index, therefore, you need to carefully consider how to create an index, depending on the actual situation. It is recommended that the number of indexes in a table be no more than 6. If there are too many indexes, consider whether the indexes on some columns that are not frequently used are necessary.

16. update the clustered index data column should be avoided as much as possible, because the order of the clustered index data column is the physical storage order of the table records. Once the column value changes, the order of the entire table record will be adjusted, it will consume a considerable amount of resources. If the application system needs to frequently update the clustered index data column, consider whether to create the index as a clustered index.

17. use numeric fields whenever possible. If fields containing only numerical information are not designed as numeric fields, this will reduce query and connection performance and increase storage overhead. This is because the engine compares each character in the string one by one during query and connection processing, and only one comparison is required for the number type.

18. try to use varchar/nvarchar instead of Char/nchar, because the first step is to reduce the storage space of the variable-length field, which can save storage space. Secondly, for queries, searching in a relatively small field is obviously more efficient.

19. Do not use select * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not used.

20. Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, note that the index is very limited (only the primary key index ).

21. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.

22. Temporary tables are not unavailable. Using them appropriately can make some routines more effective. For example, when you need to reference large tables or a data set in common tables repeatedly. However, it is best to use the export table for one-time events.

23. when creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid creating a large number of logs to increase the speed. If the data volume is small, to ease system table resources, create table first and then insert.

24. if a temporary table is used, you must explicitly delete all temporary tables at the end of the stored procedure. First truncate the table and then drop the table, so that the system table can be locked for a long time.

25. Avoid using a cursor whenever possible, because the efficiency of the cursor is poor. If the cursor operation has more than 10 thousand rows of data, you should consider rewriting.

26. before using the cursor-based or temporary table method, you should first find a set-based solution to solve the problem. The set-based method is generally more effective.

27. Like a temporary table, the cursor is not unavailable. Using a fast_forward cursor for a small dataset is usually better than other row-by-row processing methods, especially when several tables must be referenced to obtain the required data. A routine that includes "sum" in the result set is usually faster than a cursor. If the development time permits, you can try both the cursor-based method and the set-based method to see which method works better.

28. Set nocount on at the beginning of all stored procedures and triggers, and set nocount off at the end. You do not need to send the done_in_proc message to the client after executing each statement of the stored procedure and trigger.

29. Avoid large transaction operations as much as possible to improve the system concurrency capability.

30. Avoid returning a large amount of data to the client as much as possible. If the data volume is too large, consider whether the corresponding requirements are reasonable.

Certificate -----------------------------------------------------------------------------------------------------------------------------------

Quote: original Post published by newkid

I remember I have been here to compete with others for this issue more than once.
1. If your program is more rigorous, you may also encounter bugs. If you decide on your own, you should leave it to the database for judgment. It is fast and good.
Most people do not consider concurrency issues. Once you have considered it, you have to manually lock it, and the efficiency is very low.
Data may bypass your application and enter the database.
2. Performance problems: Is there no overhead if you do it yourself?
When a foreign key is determined to be apportioned to the transaction level, the overhead can be ignored, and the user is not aware of it at all.
If you want to import data in batches, You can temporarily block the foreign key and use the novalidate option to quickly restore it, provided that your data is clean.
3. Let's take a look at the troublesome example?
The foreign key constraint is designed to prevent you from messing around. This is a protection for you.
Is driving a seat belt troublesome? Sometimes it can save your life.
4. nyfor said that latency constraints can be used. However, based on my experience, it is unnecessary. If there is a foreign key, the father's data must be Mr Cheng. For example, if your warehouse receiving ticket and warehouse receiving details require this ticket number, then the father table (warehouse receiving ticket) must be Mr. Cheng.

In addition, foreign keys provide important information to CBO to generate an optimal plan.

Inverse: Qingyun. The main points are as follows:

Quote: the original post was published by Qingyun
I don't like foreign keys very much,
Cause:
1. Program Logic and integrity. I will make a rigorous judgment in the storage process or package;
2. performance problems, which is the key reason I do not like most. For example, a business flow meter frequently inserts data. If the table contains three foreign keys, insert one at a time, you must search for the three tables corresponding to the three foreign keys to determine whether there is any corresponding data. If the three tables are large, therefore, the Judgment time of these three tables is very common. Although the field of the associated table pointed to by the foreign key must be an index, I think many times, this judgment was originally controlled in the program. Another judgment through the foreign key is to reduce performance. In fact, it doesn't matter if the judgment is not made in some places, but the foreign key is used, it is necessary to determine the time. No matter how quickly Oracle optimizes the foreign key's data retrieval speed, it is always a small consumption;

3. maintenance is troublesome. Software of many companies is customized. This kind of customization is relatively random. During project development and implementation, it is necessary to repair and supplement tables frequently; there are bugs or other situations in the business logic, which require frequent manual data maintenance and complicated foreign key Association, which is very troublesome;

4. the foreign key determines the sequence of data generation between the two tables. The most common is the document Master/Slave table. Sometimes, when generating the document, it is Mr Ming and then generate the master table; if the foreign key is pinned, this cannot be implemented;

Of course, foreign keys are required for some key services;

Why is this topic? After I set up the project database today, I used a few foreign keys. Colleagues around me said that the data was not rigorous, I am reluctant to create complex Foreign keys with the same field names;

For example, the database has a total of 100 tables. According to their ideas, there may be 300 foreign keys; I am dizzy, too dogmatism;
If I want to build 300 indexes, I would be happy to say that, because the operation efficiency is improved, the foreign key is only for detection rigor, and the database operation efficiency is only reduced, there is no possibility of improvement;

In fact, this is just a matter of design habits. If you are interested, just talk about your habits.

There is also a voice of the opposite party with unique insights for your reference.

Quote: original Post published by ruideliang
Foreign keys are exposed, and programs are closed. They are also tested programs and foreign key constraints. The possibility of failure of constraints caused by human factors is obvious, therefore, foreign keys are opposed because they conflict with the high availability of the system.

For your security, please only open the URL with reliable source

Cancel website opening

From:
Http://hi.baidu.com/domygood/blog/item/a8121240b05d9b1f9313c68f.html