How to optimize for MySQL

1, this is relatively simple: in the phpMyAdmin has provided

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/43/6B/wKiom1PaXc3AwjhsAAH4kuIwJcU151.jpg "title=" 1.jpg " alt= "Wkiom1paxc3awjhsaah4kuiwjcu151.jpg"/>

2. When the table is designed to consider what kind of storage engine, MyISAM does not support transactions, but the query speed, but now generally used are InnoDB, can meet 95% of project requirements. Specifically, you can look at the difference between the two.

3. To avoid the operation of the whole table query, this online has information.

4. Index The field above the where and order by, of course, the index is not much better, will make the insert and update speed slow

5, try not to use a null value, because the search engine will spend a lot of time here, and there are differences in storage, it is recommended to use NOT NULL.

Selection of 6.varchar and Char. Can save a lot of space.

7. The field is as short as possible, with a meaningful name, in accordance with the naming convention.

8. Avoid using <> or! = in the Where condition, as this will cause a full table scan.

9, make a separate table

10. Build a view instead of a complex query in a program.

11. Setting up the cache

12.like less efficient, try to use different SQL queries

13. Try not to use functions in MySQL, such as lower (), in the fields that follow the where.

14.between and in, the former better

15, when writing SQL statements, think about whether to return so much data, limit can be limited

16. Delete Table using truncate do not use Delete, because delete will be deleted in one row.

17. If some of the SQL statements are the same, but the variables are not the same, you can combine the SQL statements, using preprocessing, you can save the compilation time.

18. Use of database connection pooling

19, using cluster

20, the unused resources are released immediately.

21. Splitting large DELETE or INSERT statements and committing SQL statements in batches

22.usleep Avoid server downtime

23,select from the server, modifying the data on the primary server

If there is anything else, add it later.

This article from "Little Just Blog" blog, declined reprint!