Analysis of 10 MySQL errors that PHP developers often make. 1. using MyISAM instead of InnoDB is totally incorrect. counterargument: The original article says MyISAM is used by default. In fact, InnoDB has become the default table engine in MySQL5.5.x. In addition
1. use MyISAM instead of InnoDB
Completely false. counterargument reason:
In the original article, MyISAM is used by default. In fact, InnoDB has become the default table engine in MySQL 5.5.x.
In addition, the simple use of InnoDB is not a solution to all problems. blind use may even reduce the application performance by 10% or even 40%.
The best way is to deal with specific services, such as Forum tables, news classification tables, various code tables, and other tables that are not operated for a long time, or use the MyISAM engine with excellent performance.
For transactions that require strict requirements on data integrity and timing, such as users, accounts, and transactions, the InnoDB engine must be used, and applications must also use the transaction processing mechanism. Of course, transaction processing will inevitably bring about a lot of performance loss, but this is necessary for simple and high-concurrency applications.
Finally, foreign key constraints are generally not used in public web internet applications because they seriously affect performance. Data integrity is maintained by the robustness of programmers or application architectures. The formal third paradigm is only used on the enterprise's internal MIS system and 12306 websites.
2. use the mysql method of PHP
Not completely wrong, but you should choose as appropriate:
Mysqli is good, but not all servers have compiled mysqli support for PHP.
Mysqli is the best choice if your application is only deployed on the server and the application is fully developed on its own.
However, once your application may be deployed on a VM or by someone else (such as a distributed project), you can still use the mysql function set honestly, encapsulate it or use a mature framework to prevent SQL injection.
3. do not filter user input
Needless to say, either MagicQuote or mature framework is used. SQL injection is an old topic.
4. do not use UTF-8
In most cases, consider the following:
You know, a UTF-8 character occupies 3 bytes, so it is 33% larger than other encoded files such as GBK. In other words, if the same web page is encoded in UTF-8 for KB, then the change to GBK encoding is only 66KB. So even if your PHP is determined to use the UTF-8, the front-end page should also select the encoding as needed. However, if PHP uses UTF-8, the front-end template is GBK, coupled with the template engine is not powerful, then transcoding work enough for you. So as much as possible to choose the encoding you need, rather than a simple choice of UTF-8.
Last long-winded: Under the UTF-8: strlen ("I") = 3, while under the GBK: strlen ("I") = 2
5. use PHP where SQL is used
Consider the following as appropriate:
For example, when creating a table, some people enter CURRENT_TIMESTAMP by default to achieve the registration time and posting time. Or in the SQL statement for time determination, the correct way to write a statement similar to SELECT x FROM tab1 WHERE regdate is: do not use any time function of MySQL, but calculate the time in the application. For distributed applications, you must have time servers for unified management of time.
Some MySQL mathematical functions mentioned in this article should also be used with caution. In large applications, the burden on databases is often the largest, and complicated WHERE statements are the culprit of slow queries. Therefore, we should try our best to put computing on a cheap application server that does not affect global stability, rather than a core database.
6. query not optimized
Needless to say, large applications do not even allow various Joins. even if two queries are generated, PHP is used to merge data.
7. use the wrong data type
Reasonable selection of INT, TinyINT, VARCHAR, CHAR, and TEXT fields is understandable.
However, the Date, DateTime, and TIMESTAMP types cannot be used in large applications. Instead, they must be replaced by INT (10) UNSIGNED.
One is performance, and the other is that the conversion of UNIX_TIMESTAMP timestamps in applications, especially PHP, is too convenient. It is difficult to output time formats using Date.
8. use * in SELECT query *
Sharing
9. insufficient or excessive indexing
Indexes are required, but if indexes cannot solve queries, consider the memcache or nosql solution.
10. do not back up
Is this the number of authors?
11. In addition: do not consider other databases
This is quite correct. In an application, you must select other databases for the application, or even use multiple databases in parallel for the specific service type in the same application. It is not a database, but a variety of other cache, memory storage and other solutions.
The reason for rejection is that MyISAM is used by default. in MySQL 5.5.x, InnoDB has become the default table engine. In addition...
