How to optimize mysql

This article mainly introduces how to optimize mysql, which can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

First, choose the correct storage engine, support transactions for intensive write operations, and use InnoDB. Intensive read operations using MyISAM

Second, the design table part

1. Set a primary key id for each table

two。 The smaller the column, the faster the query is for fixed-length columns. It is recommended to use SMALLINT TINYINT as the primary key, the VARCHAR length only allocates the required space, and the IP address is stored as int.

3. Try to use NOT NULL. NULL needs extra space.

4. Mixed paradigm and anti-paradigm

Third, the inquiry part

1. Query only the required fields

two。 Index frequently searched fields

3. No column operations, no functions.

4. Use LIMIT 1 to query only one row of data

4. Special skills

1. Turn on query caching

two。 Split a big sql, and a big sql can block the whole library.

3. Be careful with in subquery, mysql executes the outer layer first and then the inner layer

Thank you for reading this article carefully. I hope it is helpful for everyone to share how mysql optimizes the content. At the same time, I also hope you can support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!