MySQL optimizes the query speed when processing massive data.

Source: Internet
Author: User

MySQL optimizes the query speed when processing massive data.
Reasons for slow query speed

1. No index or no index is used (this is the most common problem of slow query and is a defect in programming)

2. Low I/O throughput, resulting in a bottleneck effect.

3. the query is not optimized because no computing column is created.

4. Insufficient memory

5. slow network speed

6. The queried data volume is too large (multiple queries can be used to reduce the data volume in other ways)

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

8. sp_lock and sp_who are active users. The reason is that they read and write competing resources.

9. Unnecessary rows and columns are returned.

10. The query statement is not good and is not optimized.

Optimization Methods for 30 SQL query statements:

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

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 or in the where clause to connect to the condition. Otherwise, the engine will discard the index and perform a full table scan, for example:
4. To optimize the query, try to avoid full table scanning. First, consider creating an index on the columns involved in where and order.

 

select id from t where num = 10 or num = 20;

  

You can query it as follows:

select id from t where num = 10union allselect id from t where num = 20;

  


5. The following query will also cause a full table scan: (the percentage sign cannot be prefixed)

 

select id from t where name like '%abc%';

  


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

6. Use in and not in with caution. Otherwise, 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;

  

Select xx, phone FROM send a JOIN (select '000000' phone union select '000000 '............ Union select '000000') bon a. Phone = B. phone -- replace in ('000000', '000000', '000000' ......) when many of the following data are separated '............)

  

 
7. If a parameter is used in the where clause, a full table scan is performed. Because SQL parses local variables only at run time, but the optimizer cannot select the access plan to run; it must be selected at compile time. However, if the value of the variable is unknown during the resume access plan compilation, the variable cannot be used as an input for the 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. This 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'-name; // idselect id starting with abc 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 >= ’2005-11-30′ and createdate < ’2005-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 indexes.

11. When using an index field as a condition, if the index is a composite index, you must use the first field of 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 perform meaningless queries. For example, 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. Replacing in with exists is often 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. The more indexes, the better. indexes can improve the select efficiency, but also reduce the insert and update efficiency, because the insert or update indexes may be rebuilt, 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.
15. Not all indexes are valid for queries. SQL queries are optimized based on the data in the table. When a large amount of data is duplicated 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.

16. Do not update the clustered index data column whenever 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 sequence of the entire table record will be adjusted, which will consume considerable 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, the query and connection performance will be reduced and the storage overhead will be increased. 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. Use varchar/nvarchar as much as possible to replace char/nchar, because the first step is to reduce the storage space of variable-length fields, which can save storage space. Secondly, for queries, searching in a relatively small field is obviously more efficient.

19. Do not use it anywhereselect * from t, Instead of using the specific field list*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. truncate the table first, and then drop the table, this prevents system tables from being 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 this is allowed during development, 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 returning large data volumes to the client whenever possible. If the data volume is too large, consider whether the appropriate requirements are reasonable.

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.