PHP developers often make 10 MySQL error correction profiling _php Tips

1. Use MyISAM instead of InnoDB

To be completely wrong or to refute a reason:

First of all, the original said MyISAM is used by default, and actually to the MySQL 5.5.x,innodb has become the default table engine.

In addition, the simple use of InnoDB is not a solution to all problems, blind use can even reduce the application performance by 10% or even 40%.

The best way is to specifically deal with specific business, such as forum table, News classification table, a variety of code tables, such as a long time does not operate the table, or to use the MyISAM engine with excellent performance.

and need to use transactions such as users, accounts, water and other stringent requirements of data integrity and timing, you need to use the InnoDB engine, and the application should also use a good transaction processing mechanism. Of course, transaction processing entails a lot of performance loss, but this is necessary for simple, high concurrency applications.

Finally, foreign-key constraints are generally not used in public web Internet applications because they can severely affect performance. Data integrity is maintained by the robustness of the programmer or the application architecture itself. And the formal third paradigm is only used in the enterprise internal MIS system and 12306 such websites.

　　 2. Using the MySQL method of PHP

Not completely wrong, but to choose as appropriate:

Mysqli is good, but not all servers compile mysqli support for PHP.

Mysqli is the best option when your application is able to determine only the server you are deploying, and the application is fully developed yourself.

But once your application is likely to be deployed on a virtual host or deployed by someone else (such as a distributed project), use the MySQL function set properly, encapsulate it, or use a mature framework to eliminate SQL injection.

　　 3. Do not filter user input

Needless to say, it is either magicquote or a mature framework. SQL injected into the old topic.

　　 4. Do not use UTF-8

Most of the cases are right, but seriously consider:

To be aware, a UTF-8 character occupies 3 bytes, so it is 33% larger than other encoded files such as GBK. In other words, the same Web page with the UTF-8 code if it is 100KB, then replace the GBK code only 66KB. So even if your PHP is determined to use UTF-8, the front-end page also needs to choose the code you want. However, if PHP with UTF-8, front-end template is GBK, plus the template engine is not strong, then transcoding work enough for you to suffer. So as far as possible to choose their own needs of the code, rather than simply choose UTF-8.

Last words: UTF-8: strlen ("i") = 3, and GBK under: strlen ("I") =2

　　 5. Use PHP where SQL is used

Also consider, as appropriate:

For example, some people are accustomed to building a table, the default value to fill in current_timestamp, to achieve the registration time, post time effect. Or, in the SQL statement of the time judgment, write a similar select X from Tab1 WHERE regdate The correct approach is to not use any of the time functions of MySQL, but to compute the time in the application. If it is a distributed application, there must be a time server to unify the management time.

And the text of some of the MySQL mathematical functions, but also to be used with caution. Because in large applications, the burden of databases is often the largest, and complex where statements are the culprits for slow queries. So, try to put the computation as low as possible on an application server that does not affect global stability, not on the core database.

　　 6. Do not optimize the query

Needless to say, large applications do not even allow the use of a variety of joins, even if the students write two queries, search back in the use of PHP to merge data.

　　 7. Using the wrong data type

Int,tinyint,varchar,char,text The rational selection of these field types is understandable.

The three types of date, DateTime, and timestamp are definitely not available in large applications, but instead of int (a) unsigned.

One is performance, and the other is the application of PHP in particular, Unix_timestamp time stamp conversion is too convenient. It's troublesome to use date to output various time formats.

　　 8. Use of * in select query

Share

　　 9. Insufficient index or excessive index

Indexes are required, but if the index can not solve the query, consider memcache or NoSQL solution.

　　 10. Do not back up

Is this the author dine?

　　 11. In addition: Do not consider other databases

This one is quite right. In the application, we should not only choose other databases for the application, but also use many kinds of databases in the same application for the specific business type. Even if it is not a database, but a variety of other cache, memory storage and other solutions.