52 SQL statements to teach you performance optimization

1. To optimize the query, try to avoid full table scans. First, consider creating indexes on the columns involved in where and order by.

2. Try to avoid null value judgment on fields in the where clause. NULL is the default value when creating a table, but most of the time you should use NOT NULL, or use a special value such as 0 or -1 as the default value.

3. Try to avoid using != or <> operators in the where clause. MySQL only uses indexes for the following operators: <, <=, =, >, >=, BETWEEN, IN, and LIKE in some cases.

4. Avoid using or to connect conditions in the where clause, otherwise the engine will abandon the use of indexes and perform a full table scan. You can use UNION to combine queries: 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 it will cause a full table scan. For consecutive values, use between instead of in: Select id from t where num between 1 and 3

6. The following queries will also result in a full table scan: select id from t where name like '%abc%' or select id from t where name like '%abc'. To improve efficiency, consider full-text retrieval. The index is used only when selecting id from t where name like 'abc%'

7. If parameters are used in the where clause, a full table scan will also be caused.

8. Avoid expression operations on fields in the where clause as much as possible, and avoid function operations on fields in the where clause as much as possible.

9. In many cases, it is a good idea to use exists instead of in: select num from a where num in(select num from b). Replace it with the following statement: select num from a where exists(select 1 from b where num=a.num)

10. Indexes can certainly improve the efficiency of corresponding selects, but they also reduce the efficiency of inserts and updates, because the index may be rebuilt during inserts or updates. Therefore, how to build the index requires careful consideration, depending on the specific situation. The number of indexes for a table should not exceed 6. If there are too many, you should consider whether indexes on columns that are not frequently used are necessary.

11. Avoid updating clustered index data columns as much as possible, because the order of clustered index data columns is the physical storage order of table records. Once the column value changes, the order of the entire table records will be adjusted, which will consume considerable resources. If the application system needs to frequently update clustered index data columns, you need to consider whether the index should be built as a clustered index.

12. Try to use numeric fields. If the field only contains numerical information, try not to design it as character type, as this will reduce the performance of queries and connections and increase storage overhead.

13. Use varchar/nvarchar instead of char/nchar whenever possible. First, variable-length fields take up less storage space, which can save storage space. Second, for queries, searching in a relatively small field is obviously more efficient.

14. It is best not to use ""Return all: select from t, replace "*" with a specific field list, and do not return any unused fields.

15. Try to avoid returning large amounts of data to the client. If the amount of data is too large, consider whether the corresponding demand is reasonable.

16. Use table aliases: When connecting multiple tables in a SQL statement, use table aliases and prefix the alias to each Column. This can reduce parsing time and reduce syntax errors caused by Column ambiguity.

17. Use "temporary tables" to store intermediate results

An important way to simplify SQL statements is to use temporary tables to temporarily store intermediate results. However, the benefits of temporary tables are far more than that. By temporarily storing temporary results in temporary tables, subsequent queries will be in tempdb, which can avoid scanning the main table multiple times in the program and greatly reduce the "shared lock" blocking the "update lock" during program execution, reducing blocking and improving concurrency performance.

18. Some SQL query statements should be added with nolock. Reading and writing will block each other. In order to improve concurrency performance, nolock can be added to some queries, so that writing can be allowed during reading. However, the disadvantage is that uncommitted dirty data may be read. There are three principles for using nolock. The query results used for "insert, delete, modify" cannot be added with nolock! The table being queried is one that frequently has page splits, so use nolock with caution! Using a temporary table can also save the "previous image of data", which has a function similar to Oracle's undo tablespace. If a temporary table can be used to improve concurrency performance, do not use nolock.

19. Common simplification rules are as follows: Do not have more than 5 table connections (JOIN), and consider using temporary tables or table variables to store intermediate results. Use subqueries less frequently and do not nest views too deeply. Generally, it is advisable not to nest more than 2 views.

20. Pre-calculate the results to be queried and put them in the table, and then select them when querying. This was the most important means before SQL7.0. For example, the calculation of hospitalization fees.

21. The OR clause can be decomposed into multiple queries and connected through UNION. Their speed is only related to whether the index is used. If the query needs to use a joint index, it is more efficient to use UNION all. Multiple OR clauses do not use indexes, so they are rewritten in the form of UNION and then try to match the index. A key question is whether to use indexes.

22. In the list of values ​​after IN, put the most frequently occurring value at the front and the least frequently occurring value at the end to reduce the number of judgments.

23. Try to place data processing on the server to reduce network overhead, such as using stored procedures. A stored procedure is a compiled, optimized SQL statement organized into an execution plan and stored in the database. It is a collection of control flow languages ​​and is of course fast. Dynamic SQL that is executed repeatedly can use a temporary stored procedure, which (temporary table) is placed in Tempdb.

24. When the server has enough memory, configure the number of threads = maximum number of connections + 5 to achieve maximum efficiency. Otherwise, use the number of configured threads < maximum number of connections to enable SQL SERVER's thread pool to solve the problem. If the number is still = maximum number of connections + 5, the server performance will be seriously damaged.

25. The order in which the query is associated with the write order

select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' (A = B ,B = 'Number')

select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' (A = B ,B = 'Number', A = 'Number')

select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' (B = 'Number', A = 'Number')

26. Try to use exists instead of select count(1) to determine whether a record exists. The count function is only used to count the number of rows in a table, and count(1) is more efficient than count(*).

27. Try to use “>=” instead of “>”.

28. Index usage specifications: The creation of indexes should be considered in conjunction with the application. It is recommended that large OLTP tables should not have more than 6 indexes. Use index fields as query conditions as much as possible, especially clustered indexes. If necessary, you can force the specified index through index index_name. Avoid table scans when querying large tables. Consider creating new indexes when necessary. When using index fields as conditions, if the index is a joint index, the first field in the index must be used as a condition to ensure that the system uses the index, otherwise the index will not be used. Pay attention to index maintenance, rebuild indexes periodically, and recompile stored procedures.

29. The columns in the following SQL conditional statements have appropriate indexes, but the execution speed is very slow:

SELECT * FROM record WHERE substrINg(card_no,1,4)='5378' (13 seconds)

SELECT * FROM record WHERE amount/30< 1000 (11 seconds)

SELECT * FROM record WHERE convert(char(10),date,112)='19991201' (10 seconds)

analyze:

The results of any operation on the column in the WHERE clause are calculated column by column when the SQL is running, so it has to perform a table search without using the index on the column; if these results can be obtained when the query is compiled, they can be optimized by the SQL optimizer to use the index and avoid table search, so the SQL is rewritten as follows:

SELECT * FROM record WHERE card_no like '5378%' (< 1 second)

SELECT * FROM record WHERE amount< 1000*30 (< 1 second)

SELECT * FROM record WHERE date= '1999/12/01' (< 1 second)

30. When inserting or updating a batch of data, use batch insert or batch update, and never update each record one by one!

31. Among all the stored procedures, I will never use loops to implement anything that can be implemented with SQL statements!

(For example, to list every day of the previous month, I would use connect by to recursively query, and would never use a loop from the first day of the previous month to the last day)

32. Select the most efficient table name order (valid only in rule-based optimizer):

Oracle's parser processes the table names in the FROM clause from right to left. The last table in the FROM clause (the base table driving table) will be processed first. If the FROM clause contains multiple tables, you must choose the table with the least number of records as the base table. If there are more than 3 tables to join, you need to select the intersection table as the base table. The intersection table refers to the table referenced by other tables.

33. Improve the efficiency of GROUP BY statements by filtering out unnecessary records before GROUP BY. The following two queries return the same results, but the second one is much faster.

Inefficiency:

SELECT JOB , AVG(SAL)

FROM EMP

GROUP BY JOB

HAVING JOB = 'PRESIDENT'

OR JOB = 'MANAGER'

Efficient:

SELECT JOB , AVG(SAL)

FROM EMP

WHERE JOB = 'PRESIDENT'

OR JOB = 'MANAGER'

GROUP BY JOB

34. SQL statements are written in uppercase because Oracle always parses SQL statements first, converts lowercase letters to uppercase before executing.

35. Use of aliases. Aliases are an application technique for large databases. Table names and column names are aliases with one letter in queries. The query speed is 1.5 times faster than creating a joined table.

36. Avoid deadlocks by always accessing the same table in the same order in your stored procedures and triggers; keep transactions as short as possible and minimize the amount of data involved in a transaction; never wait for user input in a transaction.

37. Avoid using temporary tables. Unless necessary, try to avoid using temporary tables. Instead, use table variables. Most of the time (99%), table variables reside in memory, so they are faster than temporary tables. Temporary tables reside in the TempDb database, so operations on temporary tables require cross-database communication, which is naturally slow.

38. It is best not to use triggers. Triggering a trigger and executing a trigger event is itself a resource-consuming process. If it can be achieved using constraints, try not to use triggers. Do not use the same trigger for different trigger events (Insert, Update, and Delete). Do not use transactional code in triggers.

39. Index creation rules:

The primary key and foreign key of the table must have indexes;

Tables with more than 300 data items should have indexes;

For tables that are often connected to other tables, indexes should be created on the connection fields;

Fields that frequently appear in the Where clause, especially those in large tables, should be indexed.

Indexes should be built on fields with high selectivity;

Indexes should be built on small fields. Do not build indexes on large text fields or even very long fields.

The creation of composite indexes requires careful analysis, and single-field indexes should be considered instead;

Correctly select the primary column field in the composite index, which is generally a field with good selectivity;

Do several fields of a composite index often appear in the Where clause in AND fashion? Are there few or no single-field queries? If yes, you can create a composite index; otherwise, consider a single field index;

If the fields included in the composite index often appear alone in the Where clause, they are decomposed into multiple single-field indexes;

If the composite index contains more than three fields, carefully consider its necessity and reduce the number of composite fields.

If there is both a single-field index and a composite index on these fields, you can generally delete the composite index;

Do not create too many indexes for tables that frequently perform data operations;

Delete useless indexes to avoid negative impact on the execution plan;

Each index created on a table will increase storage overhead, and the index will also increase processing overhead for insert, delete, and update operations. In addition, too many composite indexes are generally worthless when there are single-field indexes. On the contrary, they will reduce the performance when adding and deleting data, especially for tables that are frequently updated, where the negative impact is greater.

Try not to create an index on a field in your database that contains a large number of repeated values.

40. Summary of MySQL query optimization: Use slow query log to find slow queries, use execution plan to determine whether the query is running normally, and always test your queries to see if they are running in the best condition. Performance will always change over time. Avoid using count(*) on the entire table, which may lock the entire table. Keep the query consistent so that subsequent similar queries can use the query cache.

, use GROUP BY instead of DISTINCT where appropriate, use indexed columns in WHERE, GROUP BY, and ORDER BY clauses, keep indexes simple and do not include the same column in multiple indexes. Sometimes MySQL uses the wrong index. Use USE INDEX for this situation. Check for problems with using SQL_MODE=STRICT. For index fields with fewer than 5 records, use LIMIT instead of OR in UNION.

To avoid SELECT before UPDATE, use INSERT ON DUPLICATE KEY or INSERT IGNORE instead of UPDATE. Don't use MAX. Use indexed fields and ORDER BY clauses. LIMIT M, N can actually slow down queries in some cases. Use sparingly. Use UNION instead of subqueries in the WHERE clause. Before restarting MySQL, remember to warm up your database to ensure your data is in memory and queries are fast. Consider persistent connections instead of multiple connections to reduce overhead. Benchmark queries, including load on your server. Sometimes a simple query can affect other queries when load increases on your server. Use SHOW PROCESSLIST to see slow and problematic queries. Test all suspicious queries on mirrored data generated in a development environment.

41.MySQL backup process:

Take the backup from the secondary replication server. Stop replication while the backup is in progress to avoid inconsistencies in data dependencies and foreign key constraints. Stop MySQL completely and make a backup from the database files.

If you use MySQL dump for backup, back up the binary log files as well – to ensure that replication is not interrupted. Do not trust LVM snapshots, which are likely to produce data inconsistencies that will cause you trouble in the future. To make single table recovery easier, export data in table units – if the data is isolated from other tables.

Use --opt when using mysqldump. Check and optimize tables before backing them up. For faster imports, temporarily disable foreign key constraints during import.

To make the import faster, temporarily disable uniqueness checking during the import. Calculate the size of database, tables, and indexes after each backup to better monitor the growth of data size.

Monitor replication instances for errors and latency via automated scheduling scripts. Perform backups regularly.

42. The query buffer does not automatically process spaces. Therefore, when writing SQL statements, you should try to minimize the use of spaces, especially at the beginning and end of the SQL (because the query buffer does not automatically intercept the first and last spaces).

43. Is it convenient to query if member uses mid as the standard to divide the table? In general business needs, username is basically used as the query basis. Normally, the username should be used as a hash modulus to divide the table. If you want to split the table, the partition function of MySQL is used for this purpose, which is transparent to the code.

It seems unreasonable to implement it at the code level.

44. We should set an ID as the primary key for each table in the database, and it is best to use an INT type (UNSIGNED is recommended) and set the AUTO_INCREMENT flag to automatically increase.

45. Set SET NOCOUNT ON at the beginning of all stored procedures and triggers and SET NOCOUNT OFF at the end.

There is no need to send a DONE_IN_PROC message to the client after each statement in stored procedures and triggers is executed.

46. ​​MySQL queries can enable high-speed query cache. This is one of the effective MySQL optimization methods to improve database performance. When the same query is executed multiple times, it is much faster to retrieve data from the cache and return it directly from the database.

47. EXPLAIN SELECT query is used to track and view the effect

Using the EXPLAIN keyword allows you to see how MySQL processes your SQL statements. This can help you analyze the performance bottlenecks of your query statements or table structures. The EXPLAIN query results will also tell you how your index primary key is used, how your data table is searched and sorted... and so on.

48. Use LIMIT 1 when only one row of data is needed

Sometimes when you query a table, you already know that there will only be one result, but you may need to fetch the cursor, or you may want to check the number of records returned. In this case, adding LIMIT 1 can increase performance. In this way, the MySQL database engine will stop searching after finding a piece of data, instead of continuing to search for the next matching record.

49. Select the appropriate storage engine for the table:

MyISAM: It is mainly used for read and insert operations, with only a small amount of updates and deletions, and does not require high transaction integrity and concurrency.

Innodb: Transaction processing and data consistency requirements under concurrent conditions. In addition to inserts and queries, this includes a lot of updates and deletes. (Innodb effectively reduces the locks caused by deletes and updates). For InnoDB tables that support transactions, the main reason that affects the speed is that the AUTOCOMMIT setting is turned on by default, and the program does not explicitly call BEGIN to start the transaction, resulting in each inserted record being automatically committed, which seriously affects the speed. You can call begin before executing SQL, and multiple SQL statements can be combined into one transaction (even if autocommit is turned on), which will greatly improve performance.

50. Optimize the data type of the table and select the appropriate data type:

Principle: Smaller is usually better, simple is good, all fields must have default values, try to avoid null.

For example: When designing a database table, use a smaller integer type as much as possible to occupy less disk space. (Mediumint is more suitable than int)

For example, the time fields: datetime and timestamp, datetime occupies 8 bytes, while timestamp occupies 4 bytes, only half of it, and the range represented by timestamp is 1970-2037, which is suitable for update time

MySQL can well support the storage and access of large amounts of data, but generally speaking, the smaller the table in the database, the faster the queries executed on it will be.

Therefore, when creating a table, in order to obtain better performance, we can set the width of the fields in the table as small as possible. For example,

When defining the postal code field, if you set it to CHAR(255), this will obviously add unnecessary space to the database.

Even using VARCHAR is redundant, since CHAR(6) will do the job just fine. Likewise, if possible,

We should use MEDIUMINT instead of BIGIN to define integer fields.

You should try to set the field to NOT NULL so that the database does not have to compare NULL values ​​when executing queries in the future.

For some text fields, such as "province" or "gender", we can define them as ENUM type. Because in MySQL, the ENUM type is treated as numeric data.

Numeric data can be processed much faster than text data. In this way, we can improve the performance of the database again.

51. String data types: char, varchar, text selection differences

52. Any operation on a column will result in a table scan, including database functions, calculation expressions, etc. When querying, try to move the operation to the right side of the equal sign.

This concludes the article about 52 SQL statements to teach you performance optimization. For more relevant SQL language performance optimization content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!