A must see! 10 MySQL Mistakes PHP Developers Make

1. Use MyISAM instead of InnoDB

MySQL has many database engines, and MyISAM and InnoDB are generally used.

MyISAM is used by default. But unless you are building a very simple database or just doing it experimentally, most of the time this is the wrong choice.

MyISAM does not support foreign key constraints, which is the essence of ensuring data integrity. In addition, MyISAM will lock the entire table when adding or updating data, which will cause a big problem in

future expansion performance.

The solution is simple: use InnoDB. But here I generally tried MyISAM

2, using PHP’s mysql method

PHP has provided the MySQL function library from the beginning. Many programs rely on mysql_connect, mysql_query, mysql_fetch_assoc, etc., but the PHP manual recommends:

If the MySQL version you are using is after 4.1.3, it is strongly recommended to use the mysqli extension .

Mysqli, or the advanced extension of MySQL, has some advantages:

It has an object-oriented interface

prepared statements (prepared statements, which can effectively prevent SQL-injection attacks , and can also improve performance)

Support multiple statements and transactions

In addition, if you want to support multiple databases, you should consider PDO.

3. Not filtering user input

It should be: Never trust user input. Use back-end PHP to verify and filter each input information. Don't trust Javascript. SQL statements like the following can easily be attacked:

$username = $_POST["name"];  
$password = $_POST["password"];  
$sql = "SELECT userid FROM usertable WHERE username='$username'AND password='$password';"; // run query...

Code like this, if the user enters "admin';", is equivalent to the following:

SELECT userid FROM usertable WHERE username='admin';

This way the intruder can log in as admin without entering a password. 4. Do not use UTF-8

Users in British and American countries rarely consider language issues, which results in many products that cannot be used in other places. There are also some GBK encodings that will cause a lot of trouble.

UTF-8 solves many internationalization problems. Although PHP6 can solve this problem more perfectly, it does not prevent you from setting the character set of MySQL to UTF-8.

5. Use PHP where SQL should be used

If you are new to MySQL, sometimes you may consider using it first when solving problems Solved in a language you are familiar with. This may cause some waste and poor performance

. For example: when calculating the average, the native MySQL AVG() method is not used. Instead, PHP is used to loop through all the values ​​and then accumulate them to calculate the average.

Also pay attention to PHP loops in SQL queries. It's usually more efficient to loop through PHP after all results have been obtained.

Generally, when processing large amounts of data, using powerful database methods can improve efficiency.

6. Not optimizing the query

99% of PHP performance problems are caused by the database. A bad SQL statement may bring down your entire program. very slow. MySQL's EXPLAIN statement, Query

Profiler, and many other tools can help you find those naughty SELECTs.

7. Using the wrong data type

MySQL provides a series of data types such as numbers, strings, time, etc. If you want to store dates, use the DATE or DATETIME type. Using integers or strings will make things more complicated.

Sometimes you want to use your own data type, for example, to use strings to store serialized PHP objects. Adding a database may be easy, but then MySQL will become unwieldy and may cause problems later.

8. Use *

in the SELECT query. Do not use * to return all fields in the table, which will be very slow. You only need to take out the data fields you need. If you need to remove all fields, then your table may need to be changed.

9. Under-indexing or over-indexing

Generally speaking, all fields that appear after WHERE in the SELECT statement should be indexed.

For example, if our user table has a numeric ID (primary key) and email address. After logging in, MySQL should find the corresponding ID via email. Through indexing, MySQL can

quickly locate emails through search algorithms. Without an index, MySQL would need to check every record until it is found.

In this case, you may want to add an index to each field, but the consequence of this is that when you update or add, the index will be redone. When the amount of data is large

, there will be performance problems. Therefore, only index the required fields.

10. Not backing up

It may not happen often, but database damage, hard disk failure, service shutdown, etc., will cause disaster to the data. of destruction. So you must make sure to automatically back up your data or

or save a copy.

The above is the detailed content of A must see! 10 MySQL Mistakes PHP Developers Make. For more information, please follow other related articles on the PHP Chinese website!