Shulou(Shulou.com)05/31 Report--

Editor to share with you how to improve the efficiency of the query in a tens of millions of database queries, I believe most people do not know much about it, so share this article for your reference. I hope you will learn a lot after reading this article. Let's learn about it!

In the actual project, when the data of the database reaches the level of 10 million, the general query efficiency drops sharply, and when more where conditions are used, the query efficiency is intolerable. If a taobao order query details to dozens of seconds, you can imagine how poor the user experience is.

Here are some optimization methods:

I. Database design

1. To optimize the query, we should try our best to avoid full table scanning. First, we should consider establishing indexes on the columns involved in where and order by.

2. Try to avoid judging the null value of the field in the where clause, otherwise it will cause the engine to give up using the index and perform a full table scan. For example, select id from t where num is null can set the default value 0 on num to ensure that the num column in the table has no null value, and then query it like this: select id from t where num = 0

3. Not all indexes are valid for the query. SQL optimizes the query according to the data in the table. When there are a lot of duplicate data in the index column, the query may not make use of the index. For example, if there are fields sex,male and female in a table, then even if the index is built on sex, it will not have an effect on query efficiency.

4, the index is not the more the better, the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and update, because insert or update may rebuild the index, so how to build the index needs to be carefully considered, depending on the specific situation. It is best to have no more than 6 indexes in a table. If there are too many indexes, you should consider whether it is necessary to build indexes on some infrequently used columns.

5. Updating index data columns should be avoided as much as possible, because the order of index data columns is the physical storage order of table records. Once the value of this column changes, it will lead to the adjustment of the order of the records of the whole table, which will consume a lot of resources. If the application system needs to update the index data column frequently, then you need to consider whether the index should be built as an index.

6. try to use numeric fields, and try not to design character fields that contain only numerical information, which will reduce the performance of queries and connections, and increase storage overhead. This is because the engine compares each character in the string one by one when processing queries and connections, while for numeric types, it only needs to be compared once.

7. Use varchar/nvarchar instead of char/nchar as much as possible, because first of all, the storage space of long fields is small, which can save storage space. Secondly, for queries, searching in a relatively small field is obviously more efficient.

8. 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)

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

Temporary tables are not unusable, and proper use of them can make some routines more efficient, for example, when you need to re-reference a dataset in a large or common table. However, for one-time events, it is best to use an export table

11. When creating a new temporary table, if you insert a large amount of data at one time, you can use select into instead of create table to avoid causing a large amount of log to improve speed; if the amount of data is small, in order to ease the resources of the system table, you should first create table, and then insert

12. If temporary tables are used, be sure to explicitly delete all temporary tables at the end of the stored procedure, first truncate table, and then drop table, so as to avoid locking the system tables for a long time.

Second, SQL sentence aspect

1. Try to avoid using the! = or operator in the where clause, otherwise the engine will give up using the index and do a full table scan

2. Try to avoid using or to join conditions in the where clause, otherwise it will cause the engine to abandon the use of indexes and perform full table scans. For example, select id from t where num=10 or num=20 can query as follows: select id from t where num=10 union all select id from t where num=20

3. In and not in should also be used with caution, otherwise it will lead to full table scanning, such as: select id from t where num in (1 and 2) for consecutive values, do not use in if you can use between: select id from t where num between 1 and 3

4. The following query will also cause a full table scan: select id from t where name like'% abc%'

5. If you use parameters in the where clause, it will also cause a full table scan. Because SQL parses local variables only at run time, the optimizer cannot defer the choice of an access plan until 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 cannot be used as an input to the index selection. The following statement will scan the full table: select id from t where num=@num can instead force the query to use the index: select id from t with (index (index name)) where num=@num

6. Try to avoid expression operations on fields in the where clause, which will cause the engine to give up using the index and do a full table scan. For example, select id from t where num/2=100 should be changed to: select id from t where num=100*2

7. Try to avoid functional operations on fields in the where clause, which will cause the engine to give up using the index and do a full table scan. For example, select id from t where substring (name,1,3) = 'abc'-name id,select id from t where datediff (day,createdate,'2005-11-30') starting with abc = 0 id generated on November 30, 2005 should be changed to: select id from t where name like 'abc%' select id from t where createdate > =' 2005-11-30 'and createdate

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.