Database Optimization Experience

Here, as a just into the IT industry not long before the hair of the boy, I can only publish a little personal humble see, if there is inappropriate, please forgive me.

Database I most often use Oracle and MySQL, today we do not talk about the difference between the two, we talk about in the actual business, how to improve the database access speed, optimize database execution efficiency.

(a) One thing everyone knows: index

Indexing a table is indeed a great way to optimize the database, although the use of indexes can improve the efficiency of queries, but we must also pay attention to its cost. The index needs space to store, and it needs to be maintained regularly, whenever there are records in the table or the index column is modified, The index itself is also modified. This means that each record's insert, DELETE, and update will pay more than 4, 5 disk I/O. Because indexes require additional storage space and processing, those unnecessary indexes can slow query response time. It is necessary to periodically refactor the index.: ALTER index <INDEXNAME> REBUILD <TABLESPACENAME>

Not suitable for indexing:

1. If a column has a null value, it does not improve performance even if the column is indexed. Any statement optimizer that uses is null or is not NULL in the WHERE clause is not allowed to use the index.

2. For a joined column, the optimizer will not use the index, even if the last join value is a static value.

3. If the index column is part of a function. The optimizer will use a full table scan without using an index

When the 4.sql statement contains not keywords

(b) The SQL statement is all uppercase.

　　In particular, column names and table names are capitalized. In particular , the caching capabilities of SQL commands require uniform capitalization.

(c) can not design foreign keys do not have foreign keys

There are foreign key constraints that affect insert and delete performance, and if the program guarantees data integrity, the foreign key is removed when the database is designed.

(d) Table splitting

When designing a table you can consider whether a single table can be split into multiple table operations, but avoid meaningless two-column tables, which is a waste of the table.

(v) Avoid using the * number in the SELECT clause

During the parsing process, Oracle converts the * to all column names, which is done by querying the data dictionary, which means more time is spent

(vi) Use the alias of the table

When a SQL statement joins multiple tables, use the alias of the table and prefix the alias to each column. This reduces the time to parse and reduces the syntax errors caused by column ambiguity.

(vii) Replace in with exists instead of not exists instead of in

In a subquery, the NOT IN clause performs an internal sort and merge. In either case, not in is the least effective (because it performs a full table traversal of the table in the subquery). To avoid using not, we can change it to an outer join (Outer Joins) or not EXISTS.

(eight) Optimize GROUP by

The efficiency of the group BY statement can be improved by filtering out unwanted records before group by.
Low efficiency:
The SELECT job, AVG (SAL) from the EMP GROUP by job has the job = ' President '
Efficient:
SELECT job, AVG (SAL) from EMP WHERE job = ' President ' GROUP by job

Database Optimization Experience