1. Use MyISAM instead of InnoDB
MySQL has a lot of database engines, which generally use MyISAM and InnoDB.
MyISAM is used by default. But unless you’re building a very simple database or just experimenting with it, most of the time that choice is wrong.
MyISAM does not support foreign key constraints, which is the essence of data integrity. In addition, MyISAM will lock the entire table when adding or updating data, which will cause great performance problems in future expansion.
The solution is simple: use InnoDB. But I usually try MyISAM here
2. MySQL method using PHP
PHP has provided MySQL function library from the beginning. Many programs rely on MySQL_ connect、mysql_ query、mysql_ fetch_ Assoc and so on, but the PHP manual recommends:
If you are using a MySQL version after 4.1.3, the mysqli extension is highly recommended.
Mysqli, or the advanced extension of MySQL, has some advantages
- There is an object-oriented interface
- Prepared statements (preprocessing statements can effectively prevent SQL injection attacks and improve performance)
- Supports multiple statements and transactions
In addition, if you want to support multiple databases, you should consider PDO.
3. Do not filter user input
$username = $\_POST\["name"\]; $password = $\_POST\["password"\]; $sql = "SELECT userid FROM usertable WHERE username='$username'AND password='$password';"; // run query...
Such a code, if the user enters “admin”; “, is equivalent to the following:
SELECT userid FROM usertable WHERE username='admin';
In this way, the intruder can log in through the admin identity without entering the password.
4. Do not use UTF-8
Those users in Britain and the United States seldom consider the language, so many products cannot be used in other places. There are also some GBK coding, there will be a lot of trouble.
UTF-8 solves many problems of internationalization. Although PHP6 can solve this problem perfectly, it doesn’t prevent you from setting the MySQL character set to UTF-8.
5. Use PHP where SQL should be used
If you are new to MySQL, sometimes when you solve a problem, you may first consider using a language you are familiar with. This may cause some waste and poor performance. For example, when calculating the average value, MySQL’s native AVG () method is not applicable. Instead, PHP is used to loop through all the values and accumulate them to calculate the average value.
Also pay attention to PHP loops in SQL queries. In general, it is more efficient to loop with PHP after all the results are obtained.
Generally, when dealing with a large number of data, the use of powerful database methods can improve the efficiency.
6. Do not optimize query
99% of PHP performance problems are caused by the database. A bad SQL statement may slow down your whole program. MySQL’s explain statement, query profiler and many other tools can help you find those naughty select.
7. Using wrong data type
MySQL provides a series of data types such as number, string and time. If you want to store dates, use the date or datetime type. Using plastic or string will make things more complicated.
Sometimes you want to use your own data type, for example, string to store serialized PHP objects. It may be easy to add a database, but in this way, MySQL will become very cumbersome and may cause some problems in the future.
8. Use in select query*
Don’t use * to return all the fields in the table, which can be very slow. You just need to retrieve the data fields you need. If you need to remove all the fields, your table may need to be changed.
9. Insufficient or excessive indexing
In general, all fields that appear after where in the select statement should be indexed.
For example, suppose our user table has a numeric ID (primary key) and email address. After logging in, MySQL should find the corresponding ID through email. Through the index, MySQL can quickly locate email through the search algorithm. If there is no index, MySQL needs to check every record until it is found.
In this case, you may want to add an index to every field, but the result is that when you update or add, the index will be redone. When there is a large amount of data, there will be performance problems. So, index only the fields you need.
10. No backup
It may not happen very often, but database damage, hard disk failure, service stop and so on will cause catastrophic damage to the data. So make sure you automatically back up your data or save copies.
More learning content can be accessedAs long as you can read it, your salary will go up a step
The above contents hope to help youA lot of PHPer always encounter some problems and bottlenecks when they are upgrading. They write too much business code and have no sense of direction. They don’t know where to start to improve. For this, I have sorted out some materials, including but not limited to:Distributed architecture, high scalability, high performance, high concurrency, server performance tuning, tp6, laravel, yii2, redis, spool, swoft, Kafka, MySQL optimization, shell script, docker, microservice, nginxAnd so on many knowledge points, advanced dry goods need can be free to share with you, need can join mePHP Technology Exchange Group953224940