Web development performance optimization-Database

1. The queried data volume is too large (you can use multiple queries to reduce the data volume in other ways ), try to query data by page 2. Lock or deadlock (this is also the most common problem of slow query and is a defect in programming) 3. if unnecessary rows and columns are returned, use the OR clause to separate them into multiple queries and use the UNION link to link multiple queries. Their speed is only

1. The queried data volume is too large (you can use multiple queries to reduce the data volume in other ways ), try to query data by page 2. Lock or deadlock (this is also the most common problem of slow query and is a defect in programming) 3. if unnecessary rows and columns are returned, use the OR clause to separate them into multiple queries and use the UNION link to link multiple queries. Their speed is only

1,The queried data volume is too large (multiple queries can be used, and other methods can be used to reduce the data volume). Try to query data by page.

2. Lock or deadlock (this is also the most common problem of slow query and is a defect in programming)

3. Unnecessary rows and columns are returned.

The OR clause can be used to separate multiple queries and link multiple queries through UNION. Their speed is only related to whether an index is used. If you need to use a joint index for a query, it is more efficient to use UNION all.

4. If you use like for query, you cannot simply use index, but the full-text index consumes space. Like 'a % 'when the index like' % a' is used and like '% a %' is not used for the query, the query time is proportional to the total length of the field value, so the CHAR type cannot be used, but VARCHAR. Create a full-text index for a long field value.

5, Try to process data on the server to reduce network overhead, such as using stored procedures. The stored procedure is compiled, optimized, organized into an execution plan, and stored in the database SQL statement (the stored procedure is a program on the database server ), it is a collection of control flow languages, and the speed is certainly fast.

6. Pre-calculate the results to be queried and place them in the table. Select the results when querying. This was the most important method before SQL7.0. For example, calculate the subtotal for product purchase.

7. Do not use DISTINCT or order by when necessary. These actions can be executed on the client. They increase additional overhead. This is the same as UNION and union all.

8. Updating multiple records at a time is faster than updating multiple records at a time, that is, batch processing is good.

9. Use a temporary Table and replace it with a result set and a Table-type variable whenever possible. Table-type variables are better than temporary tables.

10. Database Design: indexes are added to all table structures in the database.

Adjustment reason:

Recently, the database is under great pressure. After some big data tables are queried slowly, the CPU of the database server continues to be 90%-100%. After these tables are indexed, the CPU becomes normal quickly.

Create an index based on the query conditions, optimize the index, optimize the access mode, and limit the data volume of the result set. Note that the fill factor should be appropriate (preferably the default value 0 ). The index should be as small as possible. Use a column with a small number of bytes to create an index (refer to the index creation). Do not create a single index for fields with a limited number of values, such as gender fields.

11. Database sharding and partitioning of big data tables:

The procedure is as follows:

1) separate large data tables from the primary database, create a new database, and partition these tables;

2) insert data into the message queue, and use windows scheduled tasks in the background for execution (once every 5 minutes) C # the console program inserts data in the message queue in batches (50000 records in the message queue are inserted into the data table at one time) into the corresponding data table;

Adjustment reason:

For example, when a user accesses a page in a user access log, the previous operation is to directly insert data into the database, which is too large for database access and operations, it seriously affects the efficiency of other data insertion and query. The advantage of using database shards, partitions, and message queues is that users do not directly perform database operations when accessing the page, instead, a certain amount of data is accumulated in the message queue and then inserted into the database in batches. Only one database operation is performed. Due to database separation, other queries and inserts are not affected;

This article is a summary of my actual work experience and collection. If you cannot write it, please give your valuable comments. Thank you.