SQL query optimization

One, the database design angle

1. Avoid full table queries as much as possible, first consider indexing on the column of the Where level order by.

2. Try to avoid null judgments in the WHERE clause, causing the engine to discard the index for a full table scan. Columns such as: Select ID from t where num is NULL, can be set to 0 in num, ensure no null appears

3. Not all indexes are valid for queries, and if the fields contain large amounts of duplicate data, SQL optimizes the query and does not use the index

4. Indexing improves select efficiency, but decreases insert and update because indexing is not better than 6 indexes in a table

5. Avoid updating indexed data columns

6. Use numeric fields as much as possible

7. Use Varchar/nvarchar instead of Char/uchar

8. Try to use table variables instead of temporary tables

9. Avoid frequent creation and deletion of temporary tables

Two-SQL aspect

1. Try to avoid using in the WHERE clause! =,<, which causes the engine to discard the index for a full table scan

2. Try to avoid using or in the WHERE clause causes the engine to discard the index for full table scan

Select ID from t where id = 1 or id = 2 can use Select ID from t where ID union ALL select ID from t where id = 2

3.in and not in also use caution will cause a full table scan, for continuous values can be used between

Select ID from where ID in (between) can use Select ID from where ID 1 and 3

SQL query optimization