Introducing several commonly used optimization methods for mysql

1.Select the appropriate field attributes. For example, when defining the postal code field, if it is set to CHAR(255), it will obviously add unnecessary space to the database. Even using the VARCHAR type is redundant, because CHAR(6 ) can complete the task very well. Or use MEDIUMINT to define integer fields.

2.You should try to set the field to NOTNULL.

3. Use connection (JOIN) instead of subquery

4. Transaction processing

5, Lock table

6, use foreign key

7, use index

Which fields should be indexed?

Generally speaking, indexes should be built on fields that will be used for JOIN, WHERE judgment and ORDERBY sorting. Try not to index a field in the database that contains a large number of repeated values. For an ENUM type field, it is very likely that a large number of duplicate values ​​will appear

For example, the "province".. field in customerinfo, building an index on such a field will not be helpful; On the contrary, it is also possible to reduce the performance of the database. We can create appropriate indexes at the same time when creating the table, or we can use ALTERTABLE or CREATEINDEX to create indexes later. In addition, MySQL supports full-text indexing and search starting with version 3.23.23. The full-text index is a FULLTEXT type index in MySQL, but it can only be used for MyISAM type tables. For a large database, it will be very fast to load the data into a table without a FULLTEXT index and then use ALTERTABLE or CREATEINDEX to create the index. But if you load data into a table that already has a FULLTEXT index, the execution process will be very slow.

8. Optimized query statements

The above is the detailed content of Introducing several commonly used optimization methods for mysql. For more information, please follow other related articles on the PHP Chinese website!