February 03
Understanding the large data volume MySQL query optimization MySQL is not commented on yet»1. Optimize the query, avoid full table scanning as much as possible, and 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, make sure that the num column in the table has no null value, and then query: 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 conditions. Otherwise, the engine will discard the index and perform full table scanning, for example:
{Select ID from t where num = 10 or num = 20 -- this can be queried 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 (, 3) for continuous values, 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, full table scan is also performed. Because SQL parses local variables only at runtime, but 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 the index selection input. The following statement performs a full table scan:
{Select ID from t where num = @ num -- you can force the 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 to: 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 and perform full table scanning. For example:
{Select ID from t where substring (name, 2005) = 'abc' -- id select 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> = '2017-11-30' and createdate <'2017-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 an index field is used as a condition, if the index is a composite index, the system must use the first field in the index as a condition to ensure that the index is used. Otherwise, the index it 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. Replacing in with exists is often a good choice:
{Select num from a where num in (select num from B) -- Replace with 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 a large amount of data is duplicated in the index column, SQL queries may not use indexes, such the table contains almost half of the sex, male, and female fields, so even if you create an index on sex, it does not play a role in query efficiency.
15. The more indexes, the better. indexes can certainly improve the efficiency of the SELECT statement, but also reduce the efficiency of insert and update. Because insert or update may re-build 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. Do not update the clustered index data column as much as possible, because the order of the clustered index data column is the physical storage order of the table record. Once the column value changes, the record sequence of the entire table is adjustments 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, 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 needs to compare once for the number type.
18. Use varchar/nvarchar to replace Char/nchar as much as possible, because the first step is to reduce the size of the field storage space, save storage space, and then search in a relatively small field for queries. the efficiency is obviously higher.
19. Do not use select * from t anywhere, replace "*" with a specific field list, or 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 and can be used appropriately to make some routines more effective. For example, when you need to repeatedly reference a large table or a data set in a common table. However, for one-time events, it is best to use the export table.
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 a large number of logs and increase the speed; if the data volume is small, create table and insert to ease system table resources.
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 can avoid a long time for the system table. locked.
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, cursors are not unavailable. Using a fast_forward cursor for a small dataset is generally 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, the cursor-based method, and the set-based method, you can try to see which method is 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 and improve system concurrency.
30. Avoid returning a large amount of data to the client. If the data volume is too large, consider whether the appropriate requirements are reasonable.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
