SQL optimization of database performance optimization

There are many scenarios for SQL optimization on the web, but many are disorganized. Recently leisure time to tidy up a bit, convenient for everyone after the inspection, if found that there are any problems and not all, welcome to the following correction and supplement:

1. For the performance optimization of SQL statements, mainly reflected in the optimization of query statements, to avoid full table scanning, the first thing to note is the where and order by the columns involved in the index, the role of the index is to tell the database directly from where to fetch data, do not have to find all over;

2. Use in and not, the advantage of using in write SQL is relatively easy to write and clear, but with in SQL statement execution is very inefficient, I have tried to give it 10,000 records in the in condition, the query result is very slow, here is an example:

Select D.eid from Dept D WHERE D.eid in (SELECT E.eid from employee e)

Using exists instead of in is a good choice:

Select D.eid from Dept D where EXISTS (select 1 from employee e where E.eid = D.eid)

In the same vein, not is substituted with not exists;

3. Try to avoid the null value of the field in the SQL statement, the judgment of the null value will cause the engine to abandon the use of the index to do a full table scan, the recommended practice is: with other functions of the operation of the same operations to replace; for example:

SELECT e.name, E.sex, e.addr from employee e WHERE E.eid are not NULL

Optimization options are: SELECT e.name, E.sex, e.addr from employee e WHERE e.eid > 0

In short, it is best not to leave a null value to the database, using NOT NULL to populate the database;

4. Try not to use between. And ..., using >= and <= instead:

SELECT D.did, D.dno, D.eid from Dept D WHERE D.did between 1 and 5

To be replaced by: SELECT D.did, D.dno, D.eid from dept D WHERE D.did >= 1 and D.did <= 5;

5. The LIKE operator can apply a wildcard query, the wildcard combination may reach almost arbitrary query, but if the use of bad can produce performance problems, such as the use of a statement containing the "%bc%" query without reference to the index, and the use of

Like ' bc% ' can use the index, although in many cases it may not be possible to avoid this situation, but must be in the heart of the bottom, the use of wildcards will reduce the query speed.

6. Try to avoid using or in the WHERE clause to join the condition, if one field is indexed and the other field is not indexed, it will cause the database engine to discard the full table scan using the index, for example:

SELECT d.dname from Dept d WHERE D.did = 1 OR d.eid = 1001

Can be replaced by: Select D.dname from dept d where d.did = 1 UNION SELECT d.dname from dept d where D.eid = 10017. When using group BY, it is better to eliminate the unused data, narrow the scope of the query, and improve the efficiency of the query. You can exclude extraneous data in the Where condition before you perform a group by operation.

8. Try to avoid the expression or function of the field in the Where clause, which will cause the engine to abandon the use of the index for full-table search;

9. If you use a parameter in the WHERE clause, it also causes a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot defer the selection of access plans to run time; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and therefore cannot be selected as an input for the index.

SELECT d.dname from Dept d WHERE D.did = @did

The workaround is to force the use of the index: SELECT d.dname from Dept D with (index name) WHERE D.did = @did
10. When using an indexed field as a condition, if the index is a composite index, you must use the first field in the index as a condition to guarantee 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.

11. The index is not the more the better, although the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and UPDATE, because the INSERT or update when the index may be rebuilt, so how to build the index needs careful consideration, depending on the situation. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary.

UPDATE statement, if you change only 1, 2 fields, do not Update all fields, otherwise frequent calls will cause significant performance consumption, and bring a large number of logs.

13. For the union operator, the union filters out duplicate records after the link is made, so the resulting set of results is sorted after the table link is deleted, and the duplicate records are removed and the results are returned. In most applications, duplicate records are not generated.

SELECT * FROM Dept UNION SELECT * from emp

This SQL takes out the results of two tables at run time, then sorts the duplicate records with the sort space, and finally returns the result set, which may cause the disk to be sorted if the table data volume is large. The recommended practice is to use the union ALL operator instead of union if it is known that there is no duplicate record, because the union all operation simply merges two results and returns. 14. Do not use SELECT * from "table name", with select "Column name" from "Table name", do not return fields that are not used. Unless the table field is not known for testing.

15. When the program makes a select, it is followed by a commit, which frees up the resources that are consumed.

16. Do not use cursors as much as possible, because cursors are less efficient, and cursors are not unusable, but if the cursor is manipulating more than 10,000 rows of data, it should be considered rewritten.

17. Complex SQL tends to consume a large amount of resources, which can be solved using functions.

18. Try to avoid the operation of large things, improve the system's concurrency ability.

SQL optimization of database performance optimization