More than 30 mysql database optimization methods, with 10 million-level database records easily solved, mysql database

More than 30 mysql database optimization methods, with 10 million-level database records easily solved, mysql database

1. To optimize the query, try to avoid full table scanning. 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 scan,

SQL code: 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:

SQL code: 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 to the condition. Otherwise, the engine will discard the index and perform full table scan,

SQL code: select id from t where num = 10 or num = 20;

You can query it as follows:

SQL code: 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:

SQL code: select id from t where num in (1, 2, 3 );

For continuous values, you can use between instead of in:

SQL code: select id from t where num between 1 and 3;

6. The following query will also cause a full table scan:

SQL code: select id from t where name like '% c % ';

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

7. If a parameter is used in the where clause, a full table scan is performed. Because SQL parses local variables only at runtime, but the optimization program 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 an input for index selection. The following statement performs a full table scan:

SQL code: select id from t where num = @ num;

You can change it to force query to use the index:

SQL code: 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.

SQL code: select id from t where num/2 = 100;

You can query it as follows:

SQL code: 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 for full table scanning. For example:

SQL code: select id from t where substring (name, 1, 3) = 'abc'; # name id starting with abc

Should be changed:

SQL code: select id from t where name like 'abc % ';

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 using an index field as a condition, if the index is a composite index, you must use the first field in 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 write meaningless queries. If you need to generate an empty table structure:

SQL code: 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:

SQL code: create table # t (...);

13. in many cases, replacing in with exists is a good choice:

SQL code: select num from a where num in (select num from B );

Replace the following statement:

SQL code: 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 there is a large number of duplicate data in the index column, SQL queries may not use indexes, for example, if the table contains fields ***, male and female are almost half of each other, indexing does not play a role in query efficiency even if you create an index on.

15. the more indexes, the better. Although the index can improve the efficiency of the select statement, it also reduces the efficiency of insert and update, because the insert or update statements may recreate 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. update the clustered index data column should be avoided as much as 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 order of the entire table record will be adjusted, it will consume a considerable amount of 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 one comparison is required for the number type.

18. try to use varchar/nvarchar instead of char/nchar, because the first step is to reduce the storage space of the variable-length field, which can save storage space. Secondly, for queries, searching in a relatively small field is obviously more efficient.

19. Do not use select * from t anywhere, replace "*" with a specific field list, and 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. First truncate the table and then drop the table, so that the system table can be 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 large transaction operations as much as possible to improve the system concurrency capability. The SQL optimization method uses indexes to traverse tables faster. The index created by default is a non-clustered index, but sometimes it is not optimal. In a non-clustered index, data is physically stored on the data page randomly. Reasonable index design should be based on the analysis and prediction of various queries. Generally speaking:

A. You can create a cluster index for columns with a large number of duplicate values and frequent range queries (>,<,>=, <=) and order by and group;

B. Multiple columns are frequently accessed at the same time, and each column contains duplicate values. You can consider creating a composite index;

C. The composite index should try its best to make the key Query Form an index overwrite. Its leading column must be the most frequently used column. Although indexes can improve performance, the more indexes, the better. On the contrary, too many indexes will lead to low system efficiency. Each time you add an index to a table, you must update the index set.

30. Regular analysis table and checklist.

Analysis TABLE Syntax: ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [, tbl_name]...

The preceding statement is used to analyze and store the table's keyword distribution. The analysis result will allow the system to obtain accurate statistical information, so that the SQL statement can generate a correct execution plan. If you feel that the actual execution plan is not the expected execution plan, executing an analysis table may solve the problem. During the analysis, a read lock is used to lock the table. This applies to MyISAM, DBD, and InnoDB tables.

For example, to analyze a data table: analyze table table_name

Syntax of the checklist: check table tb1_name [, tbl_name]... [option]... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

The purpose of the checklist is to CHECK whether one or more tables are incorrect. check table is useful for MyISAM and InnoDB tables. For MyISAM tables, keyword statistics are updated.

Check table can also CHECK whether the view has an error. For example, the referenced TABLE in the view definition does not exist.

31. Regularly optimize the table.

OPTIMIZE the TABLE Syntax: OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [, tbl_name]...

If you delete a majority of tables, or if you have made more changes to a table that contains variable-length rows (tables that contain VARCHAR, BLOB, or TEXT columns, the optimize table command should be used for TABLE optimization. This command can merge the space fragments in the TABLE and eliminate space waste caused by deletion or update. However, the optimize table command only applies to MyISAM, BDB, and InnoDB tables.

Example: optimize table table_name

Note: during execution of analyze, check, and optimize, the table is locked. Therefore, be sure to perform related operations when the MySQL database is not busy.

Supplement:
1. Use as few formats as possible for massive queries.
2. order by and gropu by: Using order by and group by phrases, any index can improve SELECT performance.
3. Any operations on columns will cause table scanning, including database tutorial functions and calculation expressions. During query, try to move the operation to the right of the equal sign.
4. IN and OR clauses usually use worksheets to invalidate indexes. If a large number of duplicate values are not generated, consider splitting the clause. The split sub-sentence should contain the index.
5. Use a smaller data type as much as possible to meet your needs: for example, use MEDIUMINT instead of INT.
6. Try to set all columns as not null. If you want to save NULL, set it manually instead of setting it as the default value.
7. Use VARCHAR, TEXT, and BLOB types as little as possible
8. If your data is only a small amount of data you know. It is best to use the ENUM type
9. Create an index as described in graymice.
10. reasonably use the operation and partition tables to increase the speed of data storage and extraction.