MySQL performance optimization tips help your database

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. The mysqli extension has disabled the persistent connection feature on the network, so I will write more about this topic. The only drawback of persistent connections is that if you have multiple concurrent connections, you can achieve the max_connections setting. This is easy to change Apache settings, so I don't think this is the reason why you shouldn't use persistent connections.

Persistent connections are especially useful if you have a database server on another computer. Due to these disadvantages, use them wisely.

Query Cache
Query the SELECT statement text stored in the cache and send the result to the client. If the same statement is received, the server retrieves the result from the query cache instead of analyzing and executing the statement again. The query cache is between shared sessions. A client generated by such a result set can send the same query from another client.

The query cache environment may be useful. tables do not change frequently and the server receives many identical queries. This is a typical case where many Web servers generate database-based content for many dynamic pages.

The query cache does not return outdated data. When the query cache table is modified, any related entries are refreshed.

How did you find that my MySQL query cache is working or not?
For MySQL statistics, you only need to enter the following command at the mysql> prompt:Copy codeThe Code is as follows: mysql> show variables like 'query % ';

Do not use the index column function
The index on the column can be a great performance gain, but if you use the function in the column, the index has never been used.
You always try to rewrite the query without using the index column function.Copy codeThe Code is as follows: WHERE TO_DAYS (CURRENT_DATE)-TO_DAYS (event_date) <= 7

It may beCopy codeThe Code is as follows: WHERE event_date> = '2014/1/15'-INTERVAL 7 DAYS

Today's date is generated from PHP. In this way, the query cache of the index column EVENT_DATE can be stored and queried.
Learn about Zen SQL Coding
SQL code is the basis for optimizing database performance. Primary SQL encoding technology, such as rewriting subquery SQL statements using connections, eliminates connections and similar cursors.
By writing a huge SQL code database, the performance will be huge.
Use ON DUPLICATE KEY UPDATE
If you specify on duplicate key update, inserting a row will UPDATE the old row with the DUPLICATE value in a UNIQUE index or primary key.Copy codeThe Code is as follows: insert into wordcount (word, count) VALUES ('A _ word', 1) on duplicate key update count = count + 1;

You save the Access Server (then select update), clear your code, and delete all, if record_exists inserts other updates.
If you follow this prompt, the database will be very grateful to you.