The first time you write a blog, you contribute this MySQL optimization experience.

How to write efficient SQL statements (from the build table to the query)

The following optimization suggestions can be drawn when building a table

1, the data type as far as possible with the digital type, the digital type is faster than the character type

2. Select the correct table engine

MyISAM is suitable for applications that require a large number of queries, but it is not very good for a lot of write operations. Even if you just need to update A field, the entire table will be locked and other processes will be unable to manipulate the read process until the read operation is complete. In addition,MyISAM 's calculations for SELECT COUNT (*) are extremely fast.  
The InnoDB trend will be a very complex storage engine, and for some small applications it will be slower than MyISAM . He is it supports " row lock " , so in the writing operation more time, will be more excellent. Also, he supports more advanced applications, such as: transactions.  

3. Select the appropriate data type.

If you have a field, such as"Sex","National","ethnic","Status"or"Department", you know that the values for these fields are limited and fixed, so you should use theENUMand notVARCHAR.  

4. Add not NULL to the field as much as possible

5, a table do not add too many indexes, because the index affects the speed of inserts and updates

6, appropriate use of redundant anti-paradigm design, space-changing time will sometimes be very efficient

Recommendations for optimization when querying:

7, try not to do operations in the database

8, the use of pre-processing statements. For example, using PDO to operate MySQL

In terms of performance, this gives you a considerable performance advantage when the same query is used multiple times. You can define some parameters for these Prepared statements( preprocessing statements ) , and MySQL will parse only once.

9. Do not query data using SELECT * from in the production environment Program . Querying only the columns that need to be used

10, query as far as possible using limit to reduce the number of rows returned, reduce data transmission time and bandwidth waste

11, all the SQL keyword capitalization, to avoid Duplication of SQL statements resulting in system resource waste

12. Turn on slow query log, and use explain or desc to optimize SQL statements in slow query periodically.

13, the first appearance of conditions, must be to filter and eliminate more results of the conditions, the second appeared.

14, to optimize the query, as far as possible to avoid full table scan. You should first consider establishing an index on the where and The columns involved in order BY.

15. Try to avoid null values for the fields in the where clause . This will perform a full table scan.

Select Id,name from the user where name is null;

16. Try to avoid expression operations on the field in the where clause. This causes the engine to discard full table scans using the index

SELECT id,name from user where AGE/12;

17. Use Connection (join) instead of subquery SELECT * from Custominfo where Customid in (select Customid from Saleinfo)

for an or clause, if you want to take advantage of an index, each condition column between OR is required to be indexed, and if there is no index, you should consider increasing the index.

The first time you write a blog, you contribute this MySQL optimization experience.