Shulou(Shulou.com)06/01 Report--

What is the optimization method of the database? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

# Optimization method

1. Avoid using * when querying. Although it is convenient to write sql statements, it consumes more memory and time resources when executing sql statements.

2. When querying multiple tables, the attributes are checked with the table name, and it is best to give the table name an alias. This looks more concise and improves query efficiency.

3. When querying multiple tables, it is best to use left and right joins instead of full joins in where. Full connection is easy to cause data redundancy and reduce query efficiency.

4. It is easy to scan the whole table when using in or not in carefully.

5. Reduce the number of visits to the database, because accessing the database starts from creating connections, verifying, performing database operations, and closing connections. It consumes memory and time resources.

6. Try to avoid judging the null value of a field in the where clause, otherwise it will cause the engine to give up using the index and perform a full table scan, such as:

Select id from t where num is null

You can set the default value of 0 on num to ensure that there is no null value for the num column in the table, and then query it like this:

Select id from t where num=0

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

8. 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, such as:

Select id from t where num=10 or num=20

You can query it like this:

Select id from t where num=10 union all select id from t where num=20

9. Do not write meaningless queries, such as generating an empty table structure:

Select col1,col2 into # t from t where 1: 0

This type of code does not return any result sets, but consumes system resources, and should be changed to this:

Create table # t (...)

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.

12. 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 lot 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.

13. Not all indexes are valid for the query. SQL optimizes the query based on the data in the table. When there is a large amount of data duplication in the index column, the SQL query may not use the index.

If there are almost half of sex,male and half of female in a table, even indexing on sex will have no effect on query efficiency.

14, 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, and if there are too many, consider whether it is necessary to build indexes on some infrequently used columns.

15. Use numeric fields as much as possible, 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.

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

The answer to the question about how to optimize the database is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.