MySQL Development Advanced Article Series 5 SQL optimization

I. Using SQL hints

SQL hint (SQL hint) is an important means to optimize the database, is to add some artificial hints in the SQL statement to achieve the purpose of optimization operations.

1.1 Use Index
After the table name in the query statement, add use index to enforce MySQL using the index, regardless of other indexes.

SELECT *  from  Use INDEX WHERE city_id=1;

1.2 Ignore Index
After the table name in the query statement, add ignore index and use MySQL to ignore one or more indexes.

SELECT *  from INDEX WHERE  city_id=+;

1.3 Force Index
After the table name in the query statement, Add Force index, which forces the index to run when MySQL does not walk the index.

-- in some cases, there is an index but MySQL does not walk the index, forcing the use of SELECT *  from INDEX (PRIMARYWHERE  city_id>0;

Two. Optimizing Database objects

1. Optimize the data type of the table
In MySQL, you can use the Function procedure analyse () to analyze the currently applied table. Reasonable suggestions for improving the data types in the table columns can be considered by the user according to the actual situation.
For example, there is a menu table in the production library below, and the field type and length are as follows:

-- using procedure analyse () analysis SELECT *  from PROCEDURE Analyse (N.);

The maximum length of the value in the remark field below is 30 length, so the system suggests 30 lengths:

2. Improve the efficiency of table access by folding
The folding can be vertical split and horizontal split, this is a design idea, this article does not speak of.

3. Inverse normalization
Inverse normalization is also called improving the redundancy of the table, which is helpful to improve the query performance. This is a design idea, this article does not speak.

4. Use the intermediate table to increase the speed of statistical queries
For example, there is a large table records the customer's daily consumption records, the need for monthly statistics total consumption amount, can be put into the intermediate table, reduce the large table of frequent queries. This is a design idea, this article does not speak.

MySQL Development Advanced Article Series 5 SQL optimization