mysql statement optimization suggestions mysql export sql statement mysql import sql statement mysql how to write sql language

data-id="1190000005008401" data-license="cc">

1. For frequently used fields (for example, these fields are often used for sorting or searching), it is best to set these fields to index.
2. Use int or tinyint types as much as possible for field types. In addition, use NOT NULL for fields as much as possible.
3. Of course, it is inevitable that some fields will use text, varchar and other character types. It is best to separate the text field into another table (associate it with the primary key)
4. The field type and length are one It is an aspect of developers' optimization skills that is very important. If the table data has a certain amount, you might as well use the PROCEDURE ANALYSE() command to get field optimization suggestions! (In phpmyadmin, you can click "Propose table structure" when viewing the table to view these suggestions) This can make your table field structure more perfect.
5. Select Use it as little as possible. Just select whatever fields you want. Don’t always use the number! In the same way, try to use LIMIT 1 when there is only one row of data. 6. Never use order by rand() easily, it may lead to a disaster in MySQL! !
7. Each table should be set with an ID primary key, preferably an INT type, and set the AUTO_INCREMENT flag that is automatically added. This should actually be the first thing to do when designing the table structure! !
8. Split large DELETE or INSERT statements. Because these two operations will lock the table, once the table is locked, no other operations can come in. As for me, sometimes I would rather use a for loop to perform these operations one by one.
9. Do not use permanent link mysql_pconnect(); unless you are really sure that your program will not cause accidents, it may also cause your mysql to die.
10. Never use complex mysql statements to show your cleverness. As far as I'm concerned, seeing a statement that associates three or four tables at once just makes people feel very unreliable.
11. Try to avoid using != or <> operators in the where clause, otherwise the engine will give up using the index and perform a full table scan.
12. You should try to avoid judging the null value of fields in the where clause, otherwise the engine will give up using the index and perform a full table scan.
13. Try to avoid using or in the where clause to connect conditions, otherwise it will cause The engine gives up using the index and performs a full table scan, such as:

<code>select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20 </code>

14. %XX% of fuzzy query will lead to a full table scan

15. In and not in should also be used with caution, otherwise it will lead to a full table scan. Try to use between and
16. You should try to avoid performing expression operations on fields in the where clause, which will cause the engine to give up using the index and perform a full table scan. Such as:

<code>select id from t where num/2=100
应改为:
select id from t where num=100*2 </code>

17. You should try to avoid performing functional operations on fields in the where clause. This will cause the engine to give up using indexes and perform full table scans. 18. The more indexes, the better. Of course, indexes can improve the corresponding performance. The efficiency of select, but it also reduces the efficiency of insert and update, because the index may be rebuilt during insert or update, so how to build the index needs to be carefully considered, depending on the specific situation. It is best not to have more than 6 indexes in a table. If there are too many, you should consider whether it is necessary to build indexes on some columns that are not commonly used. 19. Do not use select anywhere from t. Use a specific field list. Instead of "

", do not return any unused fields
20. Use varchar/nvarchar instead of char/nchar as much as possible, because first of all, variable length fields have small storage space and can save storage space. Secondly, for queries, in a Search efficiency within relatively small fields is obviously higher.
21. Try to use numeric fields. If the fields contain only numerical information, try not to design them as character fields. This will reduce the performance of query and connection, and increase storage overhead. This is because the engine will compare each character in the string one by one when processing queries and connections, and only one comparison is enough for numeric types.

List items

• The above introduces the mysql statement optimization suggestions, including Mysql and sql statement content. I hope it will be helpful to friends who are interested in PHP tutorials.