Introduction to SQL tuning and how to tune it

Guide: I had a feeling, no matter what tuning method, index is the most fundamental method, is the internal strength of all optimization techniques, so we

Some of the tuning methods related to indexing are discussed. Indexes are a common method of improving database performance, which allows the database server to retrieve specific rows at a much faster rate than without indexes, especially when the query contains Max (), MIN (), and the order of these commands.

Which fields should be indexed?

In general, the index should be based on those fields that will be used for join,where judgment and sort by order. Try not to index a field in the database that contains a large number of duplicate values. For a field of an enum type, there is a high likelihood of a large number of duplicate values

For example, "Province" in CustomerInfo. field, indexing on such a field will not help, and conversely, it may reduce the performance of the database. We can create the appropriate index at the same time when we create the table, or we can use altertable or CreateIndex to create the index later. In addition, MySQL supports full-text indexing and search starting from version 3.23.23. Full-text indexing is a fulltext type index in MySQL, but can only be used for tables of type MyISAM. For a large database, it is very fast to load the data into a table that does not have a fulltext index and then use altertable or CreateIndex to create the index. However, if you load the data into a table that already has a Fulltext index, the execution process will be very slow.

Indexes can be created, deleted, rebuilt.

Note: The tuning method described below is only an optimization method under some conventional conditions, the specific optimization effect is with the use of the DBMS and the characteristics of the data

Closely related, it is necessary to use different optimization techniques according to the specific situation, otherwise it may backfire.

①: Creating the necessary indexes

To create an index on a field that you often need to retrieve, such as to retrieve by name, you should create an index on the first name segment, if

When you frequently search by Employee department and employee post level, you should create an index on both the employee department and the employee post level fields. Invasive

The performance gains from indexing to retrieval are often huge, so the first thing you should think about when discovering a slow retrieval speed is to create an index.

②: Using Precompiled queries

In a program where SQL is executed dynamically based on the user's input, you should use parameterized SQL as much as possible, not only to avoid SQL injection vulnerabilities

Attack, the most important database compiles these parameterized SQL so that the DBMS queries the SQL statement for the first execution

and perform precompilation so that you can use the precompiled results directly when you execute the SQL later, which greatly increases the speed of execution.

③: Adjusting the order of connections in the WHERE clause

The DBMS generally uses a bottom-up sequence to parse where words, which are best written in the other where conditions before

Filter out the maximum number of records.

④: Try to compress multiple SQL statements into one sentence of SQL

Every time you execute SQL, you need to establish a network connection, check permissions, query optimization of SQL statements, send execution results, this process

is very time-consuming, so you should try to avoid too many execute SQL statements, can be compressed into a sentence of SQL execution do not use multiple lines to execute.

⑤: Replacing a HAVING clause with a WHERE clause

Avoid having a HAVING clause, because the having only filters the result set after retrieving all the records, and where is before the aggregation

Brush the record, if you can limit the number of records through the WHERE clause, that can reduce the cost of this. The conditions in having are generally used for aggregate functions

, in addition, the condition should be written in the WHERE clause.

⑥: Using Aliases for tables

When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the alias to each column name. This reduces the time to parse and decreases

The syntax error caused by the ambiguity of the friend column name.

⑦: Exists is normally used in in and exists, because in does not walk the index.

⑧: Avoid using calculations on indexes

In the WHERE clause, if the index column is part of a calculation or function, the DBMS's optimizer will not use the index instead of a full table query, and the function

One of the calculations

Low efficiency: SELECT * from the person where salary*12>25000 (salary is the index column)

High efficiency: SELECT * from the person where SALARY>25000/12 (salary is the index column)

⑨: Replace union with UNION all

When the SQL statement requires a union of two query result sets, even if there are no duplicate records in the results of the search, if you use the union two result set

It will also attempt to merge and then sort before outputting the final result, so if you can tell that there are no duplicate records in the results, you should

The union all, so the efficiency will be improved.

⑩: Avoid implicit type conversions in SQL

If an implicit type conversion occurs when an indexed field in a table is used as a where condition, the indexed field is not recognized because

An implicit type conversion is also a calculation, so the DBMS uses a full-table sweep face at this point.

Finally, it is important to note that the search scope is too wide to prevent

If the DBMS optimizer considers the retrieval scope to be too wide, it discards the index lookup and uses a full table scan. Some of the following may result in a wide range of search

Case

A, using is not null or not equal to judgment, may cause the optimizer to assume that the number of records matched is too many.

b, when using the LIKE operator, "a%" will use the index, while "a%c" and "%a" will use a full table scan, so "a%c" and "%a" cannot be

Evaluate the number of matches that are valid.

Introduction to SQL tuning and how to tune it