10 common MySQL error correction profiling that PHP developers make

  • 2020-05-10 17:53:25
  • OfStack

1. Use MyISAM instead of InnoDB

Totally wrong, rebuttal:

MyISAM is the default, but MySQL 5.5.x, InnoDB has become the default table engine.

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

The best method is still specific to the specific business processing, such as forum section table, news classification table, various code table, such as long time not to operate the table, or to use the excellent performance of the MyISAM engine.

For users, accounts, and flows that require transaction processing, which strictly requires data integrity and timing, the InnoDB engine is required, and the transaction processing mechanism is also required for the application. Of course, transactions are bound to incur significant performance costs, but they are necessary for simple, high-concurrency applications.

Finally, the foreign key constraint is generally not used in public web Internet applications because it can seriously affect performance. Data integrity is still maintained by the robustness of the programmer or the application architecture itself. However, the formal third paradigm is only used on the MIS system and websites like 12306 within the enterprise.

2. Use the mysql method of PHP

Not entirely, but as appropriate:

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

mysqli is the best choice when your application is a server that you can be sure will only be deployed on your own, and your application is completely self-developed.

But once your application is deployed on a virtual host or by someone else (such as a distributed project), use the mysql set of functions, encapsulate it well, or use a mature framework to eliminate sql injection.

3. Do not filter user input

Needless to say, either MagicQuote or the mature framework. sql infuses old topics.

4. UTF-8 is not used

Most of the time, but think carefully:

Remember, one UTF-8 character takes three bytes, so it's 33% larger than other encodings like GBK. In other words, if the UTF-8 encoding for the same page is 100KB, the GBK encoding is only 66KB. So even if your PHP is sure to use UTF-8, then the front-end page should select the required encoding according to the situation. However, if PHP USES UTF-8, the front-end template is GBK, and the template engine is not powerful, then transcoding will be a pain in the neck. So choose your own code if you can, rather than simply UTF-8.

UTF-8: strlen(" I ")=3, GBK: strlen(" I ")=2

5. Use PHP where you should use SQL

Also consider, as appropriate:

For example, some people are used to filling in CURRENT_TIMESTAMP by default when creating a form, to achieve the effect of registration time and Posting time. Or in the SQL statement for time determination, write something like SELECT x FROM tab1 WHERE regdate. Instead of using any of MySQL's time functions, calculate the time in the application. If it is a distributed application, 1 must have a time server to manage 1's time.

The mathematical function MySQL mentioned in the article should be used with caution. Because in large applications, the database burden is often the greatest, and complex WHERE statements are the culprit for slow queries. So, keep computing as cheap as possible on application servers that don't compromise global stability, rather than on core databases.

6. Not optimizing queries

Needless to say, large applications don't even allow the use of various JOIN, even if you write two queries, look them up and then merge the data using PHP.

7. Using the wrong data type

There is nothing wrong with the proper choice of field types: INT, TinyINT, VARCHAR, CHAR, TEXT.

However, Date, DateTime and TIMESTAMP are not allowed to be used in large-scale applications. Instead, INT(10) UNSIGNED should be used instead.

One is performance, and the other is that the conversion of the UNIX_TIMESTAMP timestamp to UNIX_TIMESTAMP is very convenient in the application, especially PHP. Using Date is a hassle to output various time formats.

8. Use * in SELECT queries

'

9. Under-indexing or over-indexing

Indexes are required, but if you can't even solve a query with indexes, consider an memcache or nosql solution.

10. Don't backup

Is the author making up the Numbers?

11. Also: other databases are not considered

This one is quite right. The application should not only select other databases for the application, but also use multiple databases in the same set of applications in parallel for specific business types. Even if it's not a database, it's a variety of other caching, memory storage and other solutions.

Related articles: