Analysis and correction of 10 MySQL mistakes commonly made by PHP developers_PHP Tutorial

1. Use MyISAM instead of InnoDB

Completely wrong, reasons to refute:

First of all, the original article says that MyISAM is used by default, but in fact, as of MySQL 5.5.x, InnoDB Has become the default table engine.

In addition, simply using InnoDB is not a solution to all problems. Blind use may even reduce application performance by 10% or even 40%.

The best way is to deal with specific businesses specifically, such as forum section tables, news classification tables, various code tables and other tables that are not operated for a long time, you still need to use the MyISAM engine with excellent performance.

For those that require transaction processing, such as users, accounts, pipelines, etc., which strictly require data integrity and timing, you need to use the InnoDB engine, and the application must also make good use of the transaction processing mechanism. Of course, transaction processing will inevitably bring a lot of performance losses, but this is necessary in simple high-concurrency applications.

Finally, foreign key constraints are generally not used in public web Internet applications because they will seriously affect performance. Data integrity still relies on programmers or the robustness of the application architecture itself to maintain it. The formal third paradigm is only used in corporate internal MIS systems and websites such as 12306.

　2. Use PHP’s mysql method

It’s not entirely wrong, but you should choose it as appropriate:

Mysqli is good, but not all servers can Mysqli support compiled for PHP.

If your application can be determined to only use servers deployed by yourself, and the application is completely developed by yourself, then mysqli is the best choice.

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

　3. Do not filter user input

　 Needless to say, this point needs to be either MagicQuote or a mature framework. SQL injection is an old topic.

　4. Do not use UTF-8

In most cases, yes, but you should also consider carefully:

You know, a UTF-8 Characters occupy 3 bytes, so they are 33% larger than files in other encodings such as GBK. In other words, if the same web page is 100KB in UTF-8 encoding, it will only be 66KB in GBK encoding. So even if your PHP is determined to use UTF-8, the front-end page must choose 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 the transcoding work will be enough for you. So try to use the encoding you need instead of simply choosing UTF-8.

The last sentence: UTF-8: strlen("I")=3, and GBK: strlen("I")=2

5. Should use SQL Where PHP is used

Also consider as appropriate:

For example, some people are used to filling in CURRENT_TIMESTAMP as the default value when creating a table to achieve the effect of registration time and posting time. Or in the time judgment SQL statement, write something like SELECT x FROM tab1 WHERE regdate. The correct way is: do not use any time function of MySQL, but calculate the time in the application. If it is a distributed application, there must be a time server to manage time uniformly.

And some MySQL mathematical functions mentioned in the article should also be used with caution. Because in large applications, the burden on the database is often the greatest, and complex WHERE statements are the culprit of slow queries. Therefore, calculations should be placed as much as possible on cheap application servers that do not affect global stability, rather than on the core database.

　6. Not optimizing the query

Needless to say, large applications do not even allow the use of various JOINs. Even if you write two queries, you can check them back. Using PHP to merge data.

　7. Using the wrong data type

　There is nothing wrong with the reasonable selection of field types such as INT, TinyINT, VARCHAR, CHAR, and TEXT.

The three types of Date, DateTime, and TIMESTAMP cannot be used in large applications. Instead, use INT(10) UNSIGNED instead.

One is performance, and the other is that it is very convenient for applications, especially PHP, to convert UNIX_TIMESTAMP timestamps. It is troublesome to use Date to output various time formats.

　8. Use * in SELECT query

To encourage each other

　9. Under-indexing or over-indexing

Indexes are necessary, but if the query cannot be solved by the index, consider memcache or nosql solutions.

　10. No backup

Is this the author just making up the numbers?

　11. In addition: other databases are not considered

This is quite correct. In applications, it is not only necessary to select other databases for the application, but also to use multiple databases in parallel in the same application for specific business types. Even if it is not a database, but other various caching, memory storage and other solutions.