8 MySQL Performance Optimization Tips _ MySQL

8 MySQL Performance optimization skills bitsCN.com

8 MySQL Performance optimization skills

You have completed your brand's new application, and everything works like a charm. To use your network. Everyone is happy.

Then, suddenly, an explosive user killed your MySQL server and your website was closed. What's wrong? How can you stop it?

The following are some tips for MySQL performance optimization, which will help you and your database.

Focus on

In the early stages of development, you should know the expected number of users of your application. If you want many users, you should think about it. from the very beginning, we plan to replicate, scale, and performance.

However, if you optimize your SQL code, architecture, and indexing policies, you may not need the big environment. You must always think twice about performance and scalability.

Be sure to use EXPLAIN

The EXPLAIN statement can be used as a way to obtain information. the alias for how MySQL executes the SELECT statement is DESCRIBE.

When the previous keyword explain select statement is used, the query execution plan of MySQL display information is optimized. That is to say, MySQL describes how it processes the SELECT statement, including the order in which the table is added. You can use the EXPLAIN extension to provide additional information.

Select the correct data type

It is usually stored on disks (except for some databases and memory databases, it is stored in memory ). This means that, in order to obtain information for your database, it must read the information from the disk and convert it into a result set. you can use it. Disk I/O is extremely slow, especially when comparing other forms of data storage.

When your database grows, it takes a long time to start reading. Poorly designed databases handle this problem by allocating more space than the disks they actually need. This means that the disk occupied by the database is inefficient.

Selecting the correct data type helps ensure that the data we store is as small as possible. Therefore, we only select the data type we need.

Use persistent connection

The reason for using permanent connections is that reducing the number of connections is quite expensive, even if they are faster with MySQL and most other databases.

There are some arguments about this topic. on the network, mysqli extension has disabled the persistent connection function, so I will write