MySQL statement optimization recommended MySQL export SQL statement MySQL Import SQL statement mysql how to write SQL

Data-id= "1190000005008401" data-license= "CC" >

1. It is a good idea to index fields that are often used (for example, to sort by these fields, or to search).
2, the type of the field as far as possible with int or tinyint types. In addition, the field is used as not NULL.
3, of course, there is no way to avoid some of the fields will use text, varchar and other character types, it is best to separate the text field out of another table (with the primary key associated well)
4, the type of the field, as well as the length, is a very elegant developer of the optimization of one aspect of the skill. If the table data has a certain amount, it is advisable to use the procedure analyse () command to get the field optimization suggestions! (In phpMyAdmin, you can view these suggestions by clicking on "Propose table structure" while viewing the table) so that you can refine your table field structure.
5, select as few as possible, you want to what field to select what field out do not always use the number! Similarly, LIMIT 1 is used as long as one row of data
6, absolutely do not easily use ORDER by rand (), it is likely to lead to the disaster of MySQL!!
7, each table should be set an ID primary key, the best is an int type, and set on the automatic addition of the AUTO_INCREMENT flag, which should be used as the design table structure of the first thing must do!!
8. Split the large DELETE or INSERT statement. Because these two operations will lock the table, the table is locked, and other operations are not in, as far as I would rather I would like to use a for loop to perform each of these operations.
9, do not use permanent link mysql_pconnect (); Unless you are really sure that your program will not be an accident, it will probably also cause your MySQL to die.
10. Never use complex MySQL statements to show your cleverness. As far as I'm concerned, seeing a three-and four-table statement at a time can only make people feel very unreliable.
11. Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.
12, should try to avoid the null value of the field in the Where clause to judge, otherwise it will cause the engine to abandon the use of the index for full table scan
13, should try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to abandon the use of the index for a full table scan, such as:

14, fuzzy query%xx% will cause the full table scan
15, in and not in also to use caution, otherwise it will cause a full table scan, try to use between and
16. You should try to avoid expression operations on the field in the Where clause, which causes the engine to discard full table scans using the index. Such as:

17, should try to avoid the field in the WHERE clause function operation, which will cause the engine to abandon the use of the index for full table scan
18, the index is not the more the better, although the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and UPDATE, because the INSERT or update when the index may be rebuilt, so how to build the index needs careful consideration, depending on the situation. The number of indexes in a table should not be more than 6, if too many should consider some of the less frequently used columns on the index is necessary
19. Do not use Select from t anywhere, replace "" with a specific field list, and do not return any fields that are not available
20, as far as possible to use Varchar/nvarchar instead of Char/nchar, because the first variable long field storage space is small, you can save storage space, and secondly for the query, in a relatively small field search efficiency is obviously higher.
21, try to use numeric fields, if only the value of the field is not designed as a character type, which will reduce the performance of query and connection, and increase storage overhead. This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once.

• List items

The above describes the MySQL statement optimization recommendations, including the content of the Mysql,sql statement, I hope that the PHP tutorial interested in a friend helpful.

