How mysql improves query speed

Welcome to the Linux community forum and interact with 2 million technical staff. 13. It is a good choice to replace in with exists many times: replace selectnumfromb with the following statement: selectnumfromawhereexists (select1frombwherenuma. num) 1

Welcome to the Linux community forum and interact with 2 million technical staff> enter 13. It is a good choice to replace in with exists many times: 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 =. num) 1

Welcome to the Linux community forum and interact with 2 million technicians>

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

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 the index may be rebuilt during insert or update operations, 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 six. 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, 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 select * from t anywhere. Use a specific field list instead of "*". 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, 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 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.

[1] [2]