Ii. SQL optimization used in the work

Keep good SQL statement writing in addition to indexing table.

1, the way to set parameters by variable

such as the dynamic query, as far as possible to write

Good: string strSQL=SELECT*fromWHERE p.id=? ";
Bad: String strsql= "SELECT * FROMM people P WHERE p.id=" +ID;

SQL parsing and execution of the database is saved in the cache, and the SQL needs to be re-parsed as long as it changes. The "where p.id=" +id is a time-consuming way to re-parse SQL when the ID value changes.

2. Try not to use SELECT *

Good: string strSQL=Select from people ";
Bad: String strsql= "Slect I from People";

SELECT * Increases the time of SQL parsing and queries the data that is not needed, which is a waste of time.

3. Use fuzzy query sparingly

Good: string strSQL=Select*fromwhereto like'parm%  ' ";
Bad: String strsql= "select * from people p where p.id like '%parm% '";

When the fuzzy match starts with%, the index of this column will be completely invalidated, resulting in a full table scan, and the index of this column is valid if it does not start with%.

4. Use UNION ALL first, avoid union

Good: string strSQL=SelectfromUnionallselect from Teacher "; bad: string strSQL=SelectfromUnionSelectfrom teacher ";

Because union compares the subset records of each query, it is much slower than union all.

5. Avoid the calculation of indexed fields in the where statement or the order BY statement.

Good: string strSQL=Selectfromwhere date=? "; bad: string strSQL=Select from People wehre trunc (date)=?";

6. Always set an ID for each table

Each table in the database should have an ID as the primary key, and it is an int type, it is recommended to use unsigned, and set the flag of the automatically added Auto_increment.

Even if your people table has a field called name for the primary key, you don't have to make it a primary key, and using the varchar type as the primary key can degrade performance.

Ii. SQL optimization used in the work