Talk about 10 MySQL mistakes that PHP developers often make
FMiT radio delivery
recently saw an article: "10 MySQL errors often made by PHP developers", finding that many of the contents are obsolete, and are not applicable as time goes on. In order to prevent misleading novice, Ben wrote the spirit of keeping pace with the times, not to respect the original author.
1. uses MyISAM rather than InnoDB
complete error, refutation:
first the original text says MyISAM is used by default, and actually to MySQL 5.5.x, InnoDB has become the default table engine.
in addition, simply using InnoDB is not a way to solve all problems. Blind use can even reduce performance by 10% or even 40%. The best way for
is to deal specifically with specific business, such as forum table tables, news classification tables, various bit tables that do not operate in a long time, or a MyISAM engine with excellent performance.
requires that transaction processing such as users, accounts, pipelining, and other strict requirements for data integrity and timing, requires the use of the InnoDB engine, and the application also uses a transaction processing mechanism. Of course, transaction processing is bound to bring a lot of performance loss, but this is necessary for simple and high concurrent applications.
finally, foreign key constraint is generally not used in public Web Internet applications, because it can seriously affect performance. Data integrity is maintained by the robustness of the programmer or application architecture itself. The formal third paradigm is only used in internal MIS systems and 12306 such websites.
2. uses PHP's MySQL method
not completely wrong, but choose to choose:
mysqli is good, but not all servers have compiled mysqli support for PHP.
mysqli is the best choice if your application is able to identify the server that you deploy only and its application is fully developed.
but once your application is likely to be deployed in a virtual host or deployed by other people (such as a distributed project), or use the MySQL function set honestly, wrap it up or use a mature framework to stop SQL injection.
3. does not filter user input
this needless to say, either MagicQuote or choose mature framework. SQL into the old topic.
4. does not use UTF-8
most of the case, but also seriously consider:
you know, a UTF-8 character accounts for 3 bytes, so it is 33% larger than any other encoded file such as GBK. In other words, if the same web page is encoded by UTF-8, if it is 100KB, then only 66KB will be changed to GBK. So even if your PHP determines that you want to use UTF-8, the front page must choose the coding you need based on the situation. However, if PHP uses UTF-8, the front end template is GBK, and the template engine is not strong enough, then transcoding will be enough for you. So choose the code you need as much as possible, instead of simply choosing UTF-8.
last wordy: UTF-8: strlen (.Quot;.Quot;) =3, and GBK: strlen (.Quot; I.Quot;) =2
consider: for example, some people are used to build a table, the default value is filled in to achieve the effect of registration time and post time. . Or in the SQL sentence of time judgment, write like SELECT x FROM tab1 WHERE regdate.Lt; UNIX_TIMESTAMP (). So I can only say that you buried a very hidden BUG for the system. Because databases and applications are often not on the same machine, time is prone to deviations. When your application time is inaccurately referenced, there will be a lot of problems. The correct way to do
is not to use any time function of MySQL, but to calculate time in the application. If it is a distributed application, there must be a time server to manage the time.
and some MySQL mathematical functions mentioned in this article should also be used with caution. Because in large applications, the burden of databases is always the biggest, and complex WHERE statements are the culprit of slow queries. Therefore, it is necessary to put the computation as low as possible on the application server which is not affecting the global stability, rather than the core database.
6. does not optimize the query
this needless to say, large applications do not even allow the use of all kinds of JOIN, which is afraid to write two queries, look back in the use of PHP to merge data.
7. uses the wrong data type
INT, TinyINT, VARCHAR, CHAR, TEXT, and the reasonable choice of these field types is understandable.
and Date, DateTime, TIMESTAMP these three types, in large applications is absolutely not to use, but to use INT (10) UNSIGNED instead.
one is performance. In other words, application, especially PHP, is very convenient for transforming UNIX_TIMESTAMP timestamp. It is troublesome to use Date to output a variety of time formats.
8. is required to use *
*
9. index or overindex
index, but if the index can't be solved, consider the Memcache or NoSQL solution.
10. does not backup
is this the number of authors?
11. plus: do not consider other databases
this is quite correct. In application, we should not only select other databases for applications, but also use multiple databases in the same set of applications according to specific business types. Even if it's not a database, it's a variety of other caching and memory storage solutions.