Let's talk about the 10 MySQL mistakes that PHP developers often make

Let's talk about the 10 MySQL mistakes that PHP developers often make_PHP Tutorial

WBOY

Release： 2016-07-13 17:48:25

Original

776 people have browsed it

I recently read an article: "10 MySQL Mistakes PHP Developers Commonly Make" and found that a lot of the content in the article is outdated and becomes inapplicable as time goes by and technology develops and changes. In order to prevent misleading novices, this article is written in the spirit of advancing with the times. It is by no means disrespectful to the author of the original article.

1. Use MyISAM instead of InnoDB
Totally wrong. Refutation reason:
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 method is to deal with specific businesses specifically, such as forum table, news classification table, 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.
Those that require transaction processing, such as users, accounts, pipelines, etc., that strictly require data integrity and timing, 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
Not entirely wrong, but use discretion:
Mysqli is good, but not all servers have compiled support for mysqli for PHP.
If your application can be determined to only use the server deployed by yourself, and the application is completely developed by yourself, 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 is 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 it carefully:
You should know that a UTF-8 character occupies 3 bytes, so it is 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 wordy sentence: UTF-8: strlen("I")=3, and GBK: strlen("I")=2

5. Use PHP where SQL should be used
Also considered where 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 < UNIX_TIMESTAMP(). Then I can only say that you have buried a very hidden BUG in the system. Because the database and the application are often not on the same machine, time can easily deviate. Big problems can arise when the time reference for your set of applications is inaccurate.
The correct approach 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.
Some of the 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 use PHP to merge the data.

7. Using wrong data types
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 must not be used in large applications. Instead, INT(10) UNSIGNED must be used 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 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 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.